Bug #109303 Connector/J executeQuery fails for SQL with WITH clause: not produce result set
Submitted: 7 Dec 2022 10:49 Modified: 7 Dec 2022 12:21
Reporter: Andreas Hubold Email Updates:
Status: Duplicate Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:8.0.31 OS:Any
Assigned to: CPU Architecture:Any

[7 Dec 2022 10:49] Andreas Hubold
Description:
java.sql.PreparedStatement#executeQuery fails to execute a query that starts with a common table expression and has a UNION in the main query with the two parts of the UNION being parenthesized SELECTs. The connector treats such a statement as not producing a result set and throws an exception (see stacktrace further below).

The pattern of the not working query is (full example further below)

"WITH RECURSIVE cte (id) AS (...) (SELECT ...) UNION (SELECT ...)"

There seems to be a bug in com.mysql.cj.QueryInfo#getQueryReturnType, which returns DOES_NOT_PRODUCE_RESULT_SET for such queries.

For comparison, that method works correctly if the CTE is removed, or if the parenthesis around the SELECTs in the main query are removed:

"(SELECT ...) UNION (SELECT ...)" => PRODUCES_RESULT_SET
"WITH RECURSIVE cte (id) AS (...) (SELECT ...) UNION (SELECT ...)" => PRODUCES_RESULT_SET

The query that fails with #executeQuery can successfully be executed with PreparedStatement#execute, and the result set is then accessible via #getResultSet() - so that's also a workaround, if the SQL query itself cannot be changed.

Here's a full SQL query does triggers the error. This is a short query just for illustration, so please ignore the non-sense UNION:

WITH RECURSIVE cte (id) AS (SELECT t FROM T WHERE id = ? UNION SELECT t.id FROM T t, cte WHERE t.parent = cte.id) (SELECT * from cte) UNION (SELECT * from cte)

Such a query causes the following exception:

Caused by: java.sql.SQLException: Statement.executeQuery() cannot issue statements that do not produce result sets.
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:928)

How to repeat:
Call static method 

QueryInfo.getQueryReturnType("WITH RECURSIVE cte (id) AS (SELECT t FROM T WHERE id = ? UNION SELECT t.id FROM T t, cte WHERE t.parent = cte.id) (SELECT * from cte) UNION (SELECT * from cte)", false);
[7 Dec 2022 10:59] Andreas Hubold
Sorry, there's a mistake in the description (I haven't found a way to edit it). The parentheses in the second example of working queries should be removed:

"WITH RECURSIVE cte (id) AS (...) SELECT ... UNION SELECT ..." => PRODUCES_RESULT_SET
[7 Dec 2022 11:32] MySQL Verification Team
Hello Andreas Hubold,

Thank you for the report and feedback.
Could you please provide exact test case(create table, sample dump of data and actual query) to reproduce the issue at our end? Thank you.

This reminds me of Bug #108195 and most likely a duplicate of it.

regards,
Umesh
[7 Dec 2022 12:03] Andreas Hubold
Thank you, and yes, this really is a duplicate. Feel free close this ticket.
(I did a quick search before reporting, but didn't find the other one.)
[7 Dec 2022 12:21] MySQL Verification Team
Thank you for confirming.
Marking this as a duplicate for now.

regards,
Umesh