Bug #88105 rewriteBatchedStatements=true not work if name of table contains "SELECT"
Submitted: 16 Oct 2017 5:27 Modified: 16 Oct 2017 8:31
Reporter: Shicong Huang Email Updates:
Status: Duplicate Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:5.1.44 OS:Any
Assigned to: Assigned Account CPU Architecture:Any
Tags: parser, REWRITEBATCHEDSTATEMENTS

[16 Oct 2017 5:27] Shicong Huang
Description:
Hi,

I found that if the table(or column) name contains "SELECT", rewriteBatchedStatements=true won't work, which means the driver will send multiple INSERT queries one by one instead of rewriting the queries and sending only one INSERT. 

How to repeat:
Code to repeat:

// CREATE TABLE `NORMAL_TABLE` (
        //  `COL` varchar(10) NOT NULL
        // )

        // CREATE TABLE `TABLE_WITH_SELECT` (
        //  `COL` varchar(10) NOT NULL
        // )

        Class.forName("com.mysql.jdbc.Driver").newInstance();
        String jdbcString = "jdbc:mysql://yourURL/yourDB";

        Properties props = new Properties();
        props.setProperty("user", "username");
        props.setProperty("password", "password");
        props.setProperty("rewriteBatchedStatements", "true");

        Connection conn = DriverManager.getConnection(jdbcString, props);

        conn.setAutoCommit(false);

        PreparedStatement stmt = conn.prepareStatement("INSERT INTO NORMAL_TABLE (COL) VALUES (?)");
        for (int i = 0; i < 10; i++) {
            stmt.setString(1, "" + i);
            stmt.addBatch();
        }
        stmt.executeBatch();
        conn.commit();

        stmt = conn.prepareStatement("INSERT INTO TABLE_WITH_SELECT (COL) VALUES (?)");
        for (int i = 0; i < 10; i++) {
            stmt.setString(1, "" + i);
            stmt.addBatch();
        }
        stmt.executeBatch();
        conn.commit();

        conn.close();

Logs in general_log:

143624 Query    /* mysql-connector-java-5.1.44 ( Revision: b3cda4f864902ffdde495b9df93937c3e20009be ) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS charact
er_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@init_connect A
S init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_buffer_length AS net_buffer_length, @@n
et_write_timeout AS net_write_timeout, @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@tx_isolation A
S tx_isolation, @@wait_timeout AS wait_timeout
                143624 Query    SET NAMES latin1
                143624 Query    SET character_set_results = NULL
                143624 Query    SET autocommit=1
                143624 Query    SET sql_mode='STRICT_TRANS_TABLES'
                143624 Query    SET autocommit=0
                143624 Query    select @@session.tx_read_only
                143624 Query    INSERT INTO NORMAL_TABLE (COL) VALUES ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9')
                143624 Query    commit
                143624 Query    select @@session.tx_read_only
                143624 Query    select @@session.tx_read_only
                143624 Query    INSERT INTO TABLE_WITH_SELECT (COL) VALUES ('0');
                143624 Query    INSERT INTO TABLE_WITH_SELECT (COL) VALUES ('1');
                143624 Query    INSERT INTO TABLE_WITH_SELECT (COL) VALUES ('2');
                143624 Query    INSERT INTO TABLE_WITH_SELECT (COL) VALUES ('3');
                143624 Query    INSERT INTO TABLE_WITH_SELECT (COL) VALUES ('4');
                143624 Query    INSERT INTO TABLE_WITH_SELECT (COL) VALUES ('5');
                143624 Query    INSERT INTO TABLE_WITH_SELECT (COL) VALUES ('6');
                143624 Query    INSERT INTO TABLE_WITH_SELECT (COL) VALUES ('7');
                143624 Query    INSERT INTO TABLE_WITH_SELECT (COL) VALUES ('8');
                143624 Query    INSERT INTO TABLE_WITH_SELECT (COL) VALUES ('9')
                143624 Query    commit

Suggested fix:
I feel this should be a bug in parser for the query. A workaround I found is using back quote to wrap the table and column name like this:

stmt = conn.prepareStatement("INSERT INTO `TABLE_WITH_SELECT` (COL) VALUES (?)");
[16 Oct 2017 5:57] MySQL Verification Team
Hello Shicong Huang,

Thank you for the report and feedback.

Thanks,
Umesh
[16 Oct 2017 5:57] MySQL Verification Team
test results

Attachment: 88105.results (application/octet-stream, text), 9.81 KiB.

[16 Oct 2017 8:31] Alexander Soklakov
Hi Shicong, Umesh,

It's a known issue, see Bug#81468. But thank you for a workaround.

Closed as duplicate of Bug#81468.