MySQL Bugs: #109303: Connector/J executeQuery fails for SQL with WITH clause: not produce result set

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

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);

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

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

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.)

Thank you for confirming.
Marking this as a duplicate for now.

regards,
Umesh