MySQL Bugs: #76623: JDBC driver misinterprets "--" as a comment, instead of "-- "

Bug #76623	JDBC driver misinterprets "--" as a comment, instead of "-- "
Submitted:	8 Apr 2015 18:59	Modified:	7 Apr 2022 21:03
Reporter:	Lukas Eder	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	5.1.34, 5.1.35	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
The MySQL docs clearly states that "--" is not the beginning of a comment (unlike in any other database and the SQL standard), but "-- " (and "--\t") is, i.e. "--" followed by a whitespace:
https://dev.mysql.com/doc/refman/5.7/en/ansi-diff-comments.html

While I personally think that this is completely nuts ;-) I still believe that the JDBC driver should behave like the database. It currently doesn't

How to repeat:
According to MySQL, the following statement holds two bind variables:

------------------------------------------------
Connection c = getConnection();
PreparedStatement s = c.prepareStatement(            
    " select 1--1 + ?\n"
  + "      , 2 + ?\n"
  + " from dual"
);

s.setInt(1, 10);
s.setInt(2, 20);
s.executeQuery();
------------------------------------------------

The result should be

1--1 + 10    2 + 20
-------------------
12           22

This can be verified by running the following query in MySQL Workbench, for instance:

------------------------------------------------
select 1--1 + 10
     , 2 + 20
from dual
------------------------------------------------

Instead, I'm getting an exception:

SQL [null]; Parameter index out of range (2 > number of parameters, which is 1).

Suggested fix:
In the short run: 
Require "--" to be followed by a whitespace when parsing SQL in the JDBC driver.

In the long run:
Please deprecate and remove that funny "feature" from MySQL. At least in the STRICT mode.

Hello Lukas Eder,

Thank you for the report.
Observed this with C/J 5.1.35.

Thanks,
Umesh

Test case

Attachment: Bug76623.java (text/x-java), 1.03 KiB.

Here is a test case with attached Bug76623.java class

output before fix

raminorujov@ro--ubuntu:~/Downloads$ javac -cp .:mysql-connector-java-5.1.38-SNAPSHOT-bin.jar Bug76623.java 
raminorujov@ro--ubuntu:~/Downloads$ java -cp .:mysql-connector-java-5.1.38-SNAPSHOT-bin.jar Bug76623
java.sql.SQLException: Parameter index out of range (2 > number of parameters, which is 1).
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:957)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:896)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:885)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860)
	at com.mysql.jdbc.PreparedStatement.checkBounds(PreparedStatement.java:3323)
	at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:3308)
	at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:3345)
	at com.mysql.jdbc.PreparedStatement.setInt(PreparedStatement.java:3298)
	at Bug76623.main(Bug76623.java:28)
raminorujov@ro--ubuntu:~/Downloads$ 

output after fix

raminorujov@ro--ubuntu:~/Downloads$ java -cp .:mysql-connector-java-5.1.38-SNAPSHOT-bin.jar Bug76623
12 22
raminorujov@ro--ubuntu:~/Downloads$ 

commited here
https://github.com/raminorujov/mysql-connector-j/commit/0aa49ef71370f95ac2fbf55671863e058f...

Posted by developer:
 
This bug is not reproducible with the latest Connector/J 8.0.

Connector/J 5.1 series came to EOL on Feb 9th, 2021, see https://www.mysql.com/support/eol-notice.html, so this bug will not be fixed there.

Posted by developer:
 
Fixed in Connector/J 8.0.27.

Related to Bug#71929 (18346501).

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

"Contrary to the the MySQL requirement for comments, Connector/J did not require a whitespace (or a control character such as a newline) after "--" to mark the         beginning of a comment within a SQL statement. This fix aligns Connector/J with the MySQL requirement."