Bug #14909 select '{"','}' fails with syntax error
Submitted: 13 Nov 2005 15:50 Modified: 16 Nov 2005 22:29
Reporter: Dave Pullin (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:JBDC driver version 3.1.11 OS:Windows (Windows 2000)
Assigned to: Mark Matthews CPU Architecture:Any

[13 Nov 2005 15:50] Dave Pullin
Description:

select '{"','}'

fails with

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '}'' at line 1

when run through the JBDC driver version 3.1.11. It works fine using the MySQL console.

(using MySQL 5.0.15-nt-max-log)

note that the select is
   quote curlybra doublequote quote comma quote curlyket quote

The bug goes away if:
1) any character within the quotes is changed. (the bug is specific to curly brackets! changing one or both to a letter or to square brackets eliminates the bug).
2) the doublequote is escaped (changed to backslash doublequote).
3) remove quote comma quote.

The bug continues to show with more complicated selects that involve culry brackets, non-escaped double quote, and single quotes.  (see the program below). (and fails with the very complicated real-life select that I distilled this bug from). 

This bug did not show with earlier driver versions (3.0.9).

How to repeat:
package test;
import java.sql.*;
public class JDBCbug {
    static String testSQL = "select '{\"','}'" ;
    // note that testSQL contains a java escaped doublequote (the backslash does not get through to JDBC)
    static String[] testSQLfails = new String[] {
        "select '{\"','}'",
        "select '{','\"','}'",
        "select '{','\"','A','}'",
        "select '{','x\"'||'A','}'",
        "select '{','x\"'||('A'),'}'",
        "select '{','x\"'||('A'||'z'),'}'",
        "select '{','x\"'||('A'='z'),'}'",
        "select '{','x\"'||('A'='z'||'x'),'}'",
         "select '{','x\"'||('A'='z'||'x'||'y'),'}'" ,
         "select '{','x\"'||if('A'='z','x','y'),'}'" };
    static String[] testSQLOK  = new String[] {
        "select 'A\"','}'",
        "select '[\"','}'",
        "select '{\"',']'" ,
        "select '{\"}'",
        "select '{','\\\"','}'",
        "select '{','\"','A'",
        "select 'A','\"','}'",
 };

    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        java.sql.Connection con = java.sql.DriverManager.getConnection("jdbc:mysql://127.0.0.1/test", "tester",null);
        Statement stmt = con.createStatement();
        ResultSet r;
        try {
            r = stmt.executeQuery(testSQL);
            System.out.println("TEST SUCCEEDED! "+testSQL);
        } catch (SQLException ex) {
            System.out.println("TEST FAILED! "+testSQL);
            System.out.println("TEST FAILED! "+ex.getMessage());
        }                
    }
}
[16 Nov 2005 22:29] Mark Matthews
Fixed for 3.1.12 and 5.0.0. See nightly snapshots after 00:00 GMT November 17th at http://downloads.mysql.com/snapshots.php#connector-j to test the fix (or wait for the release of 3.1.12).

Thanks for the bug report!