MySQL Bugs: #109243: Judge whether the returned result set of the sql statement is incorrect

Bug #109243	Judge whether the returned result set of the sql statement is incorrect
Submitted:	30 Nov 2022 6:40	Modified:	22 Mar 2023 17:10
Reporter:	haiming zeng	Email Updates:
Status:	Closed	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

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 .

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

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 (", "))".

Thank you for the feedback.
Verified as described.

regards,
Umesh

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?

When can this bug be fixed and released ?

Posted by developer:
 
Added the following entry to the C/J 8.0.33 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 in which there was no space after the comma between two common table expressions."