MySQL Bugs: #108195: Connector/J rejects UNION with CTE

Bug #108195	Connector/J rejects UNION with CTE
Submitted:	19 Aug 2022 8:21	Modified:	20 Dec 2022 23:40
Reporter:	Tibor Varga	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	8.0.30	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Consider the following query:

WITH cte AS (SELECT 0) (SELECT 1) UNION (SELECT 2)

This is a valid query that returns 1 and 2 on MySQL 8, so Connector/J should also return 1 and 2 when executing that statement.

What we get instead is an exception:

Exception in thread "main" 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.StatementImpl.executeQuery(StatementImpl.java:1134)
	at mysql.UnionWithCommonTableExpressionBugReport.runTest(UnionWithCommonTableExpressionBugReport.java:17)
	at com.mysql.cj.jdbc.util.BaseBugReport.run(BaseBugReport.java:138)
	at mysql.UnionWithCommonTableExpressionBugReport.main(UnionWithCommonTableExpressionBugReport.java:57)

The workaround for simple SELECTs that don't require parentheses is to omit them from around the first SELECT. When that's not possible because the parentheses are required, one could in theory move the CTEs into all SELECTs as a workaround, but that is far from ideal.

How to repeat:
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

import com.mysql.cj.jdbc.util.BaseBugReport;

public class UnionWithCommonTableExpressionBugReport extends BaseBugReport {

    @Override
    public void runTest() throws Exception {
        try (Connection connection = getConnection();
             Statement statement = connection.createStatement();
             ResultSet results = statement.executeQuery("WITH cte AS (SELECT 0) (SELECT 1) UNION (SELECT 2)")) {
            // empty
        }
    }

    @Override
    public void setUp() {
        // empty
    }

    @Override
    public void tearDown() {
        // empty
    }

    public static void main(String[] args) throws Exception {
        new UnionWithCommonTableExpressionBugReport().run();
    }
}

Suggested fix:
The com.mysql.cj.QueryInfo.getQueryReturnType(String, boolean) method seems to be missing "UNION" from the list of valid "contexts" for a WITH statement, in line 646 of Connector/J version 8.0.30, inside the condition that handles statements starting with "WITH":

if (context.equalsIgnoreCase("SELECT") || context.equalsIgnoreCase("TABLE") || context.equalsIgnoreCase("UNION") || context.equalsIgnoreCase("VALUES")) {
    return QueryReturnType.PRODUCES_RESULT_SET;
}

Hello Tibor Varga,

Thank you for the report and test case.

regards,
Umesh

Thanks again for taking the time to report this.

Until this is not fixed you can use this workaround:

statement.execute("WITH cte AS (SELECT 0) (SELECT 1) UNION (SELECT 2)");
ResultSet results = statement.getResultSet();

This also affects executino of such queries with third parties, see https://github.com/jOOQ/jOOQ/issues/14358

Another workaround seems to be to wrap the UNION query in a derived table as such:

WITH cte AS (SELECT 0) SELECT * FROM ((SELECT 1) UNION (SELECT 2)) t

Bug #109303 marked as duplicate of this one

Posted by developer:
 
Added the following entry to the  Connector/J 8.0.32 changelog: 

"Before executing a query with Statement.executeQuery(query), Connector/J checks if the query is going to produce results and rejects it if it cannot. The check wrongly rejected queries that had a WITH clause using a common table expression and a UNION operator, and had the first SELECT statement put between parentheses."