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

[19 Aug 2022 8:21] Tibor Varga
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;
}
[19 Aug 2022 9:01] MySQL Verification Team
Hello Tibor Varga,

Thank you for the report and test case.

regards,
Umesh
[11 Nov 2022 20:38] Filipe Silva
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();
[5 Dec 2022 7:31] Lukas Eder
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
[7 Dec 2022 12:21] MySQL Verification Team
Bug #109303 marked as duplicate of this one
[20 Dec 2022 23:40] Daniel So
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."