Bug #99874 JDBC connects to MySQL database and uses with keyword to report errors.

• Submitted: 15 Jun 2020 10:12
• Modified: 15 Jul 2020 12:49
• Reporter: sunnykandy sunny
• Status: No Feedback
• Category: Connector / J
• Severity: S2 (Serious)
• Version: 8.0.20
• OS: Any
• CPU Architecture: Any

[15 Jun 2020 10:12] sunnykandy sunny

Description:
In MySQL server version 8.0.20, JDBC connects to MySQL database and uses with keyword to report errors.

eg.
   with temp as (select 1 as n ) select * from temp

   When executing this statement in sqloy tool, it is perfect and no error is reported. However, when mysql-connector-java-8.0.20.jar is used to connect to the database and execute this statement, an error is reported.
   
   ### Error querying database.  Cause: java.sql.SQLSyntaxErrorException: 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 'temp as (
            select 1 as n
        ) select * from temp' at line 1

How to repeat:
email: [e-mail]

[15 Jun 2020 12:49] MySQL Verification Team

Hello sunny,

Thank you for the report.
I tried to reproduce this issue at my end but not seeing any issues. Could you please provide a java test case which demonstrate this issue? Thank you.

-
public class Bug99874 {

    public static void main(String[] args) throws SQLException{

        String url = "jdbc:mysql://localhost/test";
        String userName = "username";
        String userPassword = "";      
        Connection conn = DriverManager.getConnection(url, userName, userPassword);
        Runtime.Version version = Runtime.version();
        System.out.println("JDK version: " + version);
        DatabaseMetaData meta = conn.getMetaData();
        System.out.println("MySQL Server: " + meta.getDatabaseProductVersion());
        System.out.println("Driver Name & Ver: " + meta.getDriverName() + meta.getDriverVersion());
        PreparedStatement stmt = null;
        stmt = conn.prepareStatement("with temp as (select 1 as n ) select * from temp");
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
          int i = rs.getInt(1);
          System.out.println(i);
        }
    
    }
}
==

ant -f C:\\Work\\MySQLJava\\Bug99874 -Dnb.internal.action.name=run run
init:
Deleting: C:\Work\MySQLJava\Bug99874\build\built-jar.properties
deps-jar:
Updating property file: C:\Work\MySQLJava\Bug99874\build\built-jar.properties
Compiling 1 source file to C:\Work\MySQLJava\Bug99874\build\classes
compile:
run:
JDK version: 11.0.3+12-LTS
MySQL Server: 8.0.20
Driver Name & Ver: MySQL Connector/Jmysql-connector-java-8.0.20 (Revision: afc0a13cd3c5a0bf57eaa809ee0ee6df1fd5ac9b)
1
BUILD SUCCESSFUL (total time: 2 seconds)

regards,
Umesh

[16 Jul 2020 1:00] Bugs System

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".