Bug #109243 Judge whether the returned result set of the sql statement is incorrect
Submitted: 30 Nov 2022 6:40 Modified: 13 Jan 2:25
Reporter: haiming zeng Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / J Severity:S1 (Critical)
Version:8.0.27,8.028,8.0.29, 8.0.31 OS:Any
Assigned to: CPU Architecture:Any
Tags: Detect whether there is a result set

[30 Nov 2022 6:40] haiming zeng
Description:
At in the com.mysql.cj.QueryInfo class, the method com.mysql.cj.QueryInfo#isReadOnlySafeQuery 、com.mysql.cj.QueryInfo#getQueryReturnType

Finally, call the logic of the com.mysql.cj.QueryInfo# getContextForWithStatement method.

Find the keywords DELETE, UPDATE, SELECT, TABLE, VALUES, etc.

When the query statement is as follows:

"With as temp (xxxx), temp2 (xxxx) select * from table where xxxx"

Therefore, it is false in the later judgment logic to determine whether the result set is generated. But the method calls

com.mysql.cj.jdbc.StatementImpl # executeQuery method, which determines whether there is a result set query in line 1133. Therefore, an exception will be thrown.

Exception information: Statement executeQuery() cannot issue statements that do not produce result sets.

How to repeat:
Query Scenario

"with as temp (xxxx) ,temp2 (xxxx) select * from table , temp,temp2 where xxxx"

Call the com.mysql.cj.jdbc.StatementImpl#executeQuery method. Executing the above statement will throw an exception .

Suggested fix:
Modify the implementation logic of the com.mysql.cj.QueryInfo # getContextForWithStatement method can return "select" instead of ", temp2" in the sql "with as temp (xxxx), temp2 (xxxx) select * from table, temp, temp2 where xxxx" scenario. Finally, in com.mysql.cj QueryInfo # getQueryReturnType method returns QueryReturnType.PRODUCES_ RESULT_ SET .
[30 Nov 2022 9:58] MySQL Verification Team
Hello haiming zeng,

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

Related - Bug #108195

regards,
Umesh
[1 Dec 2022 1:59] haiming zeng
test case:
"with temp1 as (select 1 a from dual) ,temp2 as (select 2 b from dual) select a,b from temp1,temp2".
When there is no space between ",temp2" characters, there will be an error. It is correct if there are spaces.
If there is no space between ",temp2", the "com.mysql.cj. QueryInfo#getContextForWithStatement" method returns the result ",temp2", resulting in an error in judging the final result; If there is a space between ", temp2", the "com.mysql.cj.QueryInfo#getContextForWithStatement" method returns the result "select", and the final result is judged to be normal. Therefore, it is recommended to change the code 689 line from "if (section. equalsIgnoreCase (", "))" to "if (section. contains (", "))".
[1 Dec 2022 6:20] MySQL Verification Team
Thank you for the feedback.
Verified as described.

regards,
Umesh
[5 Dec 2022 7:59] haiming zeng
According to the above description, what is the verification result? Are you sure it is a bug? If the bug is confirmed, when will it be repaired?
[13 Jan 2:25] haiming zeng
When can this bug be fixed and released ?