insert value with quote ' fail

[ltylty]

Steps to reproduce the behavior (Required)

1. using jdbc insert success

2. value add quote ' fail

StarRocks version (Required)

2.2.2 e27e2aa

[ltylty]

any answer?

[ltylty]

这么严重的bug，官方不解答一下？

[rickif]

Sorry for the delay, I'm investigating it.

[rickif]

It would be very nice if you could provide the test code and the table schema. @ltylty

[ltylty]

ddl

CREATE TABLE `test` (
  `x_id` int(11) NULL COMMENT "",
  `x_uid` int(11) NULL COMMENT "",
  `nickname` varchar(65533) NULL COMMENT "",
  `x_time` datetime NULL COMMENT ""
) ENGINE=OLAP 
DUPLICATE KEY(`x_id`, `x_uid`)
COMMENT "OLAP"
DISTRIBUTED BY HASH(`x_uid`) BUCKETS 10 
PROPERTIES (
"replication_num" = "1",
"in_memory" = "false",
"storage_format" = "DEFAULT"
);

test JAVA code

public class InsertRecords {
    // JDBC driver name and database URL
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static final String DB_URL = "";

    //  Database credentials
    static final String USER = "";
    static final String PASS = "";

    public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        try{
            //STEP 2: Register JDBC driver
            Class.forName("com.mysql.jdbc.Driver");

            //STEP 3: Open a connection
            System.out.println("Connecting to a selected database...");
            conn = DriverManager.getConnection(DB_URL, USER, PASS);
            System.out.println("Connected database successfully...");

            //STEP 4: Execute a query
            System.out.println("Inserting records into the table...");


            String sql = "INSERT INTO test (x_id,x_uid,nickname,
x_time) VALUES(?, ?, ?, ?);";
            PreparedStatement preparedStatement = conn.prepareStatement(sql);
            preparedStatement.setInt(1, 1);
            preparedStatement.setInt(2, 1);         
            preparedStatement.setString(3, "tes't");
            preparedStatement.setString(4, "2022-09-09");
            preparedStatement.addBatch();
            preparedStatement.executeBatch();


            System.out.println("Inserted records into the table...");

        }catch(SQLException se){
            //Handle errors for JDBC
            se.printStackTrace();
        }catch(Exception e){
            //Handle errors for Class.forName
            e.printStackTrace();
        }finally{
            //finally block used to close resources
            try{
                if(stmt!=null)
                    conn.close();
            }catch(SQLException se){
            }// do nothing
            try{
                if(conn!=null)
                    conn.close();
            }catch(SQLException se){
                se.printStackTrace();
            }//end finally try
        }//end try
        System.out.println("Goodbye!");
    }//end main
}

[rickif]

fe.log

2022-09-21 17:09:29,272 WARN (starrocks-mysql-nio-pool-2|202) [ConnectProcessor.handleQuery():306] Process one query failed because.
com.starrocks.common.AnalysisException: extraneous input 't' expecting {',', ')'}
        at com.starrocks.qe.ConnectProcessor.handleQuery(ConnectProcessor.java:266) ~[starrocks-fe.jar:?]
        at com.starrocks.qe.ConnectProcessor.dispatch(ConnectProcessor.java:432) ~[starrocks-fe.jar:?]
        at com.starrocks.qe.ConnectProcessor.processOnce(ConnectProcessor.java:668) ~[starrocks-fe.jar:?]
        at com.starrocks.mysql.nio.ReadListener.lambda$handleEvent$0(ReadListener.java:55) ~[starrocks-fe.jar:?]
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [?:1.8.0_202]
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [?:1.8.0_202]
        at java.lang.Thread.run(Thread.java:748) [?:1.8.0_202]

fe.audit.log

2022-09-21 17:09:29,272 [query] |Client=127.0.0.1:37530|User=root|Db=|State=ERR|Time=1|ScanBytes=0|ScanRows=0|ReturnRows=0|CpuCostNs=0|MemCostBytes=0|StmtId=34|QueryId=191ac529-398d-11ed-887e-0242ae4d4cfc|IsQuery=false|feIp=172.26.92.139|Stmt=INSERT INTO db0.test (x_id,x_uid,nickname,x_time) VALUES(1, 1, 'tes\'t', '2022-09-09');|Digest=

[ltylty]

replace "tes't" to "test" insert success . I guess sqlParser‘s bug.

[rickif]

replace "tes't" to "test" insert success . I guess sqlParser‘s bug.

I agree. The SQL printed in audit log INSERT INTO db0.test (x_id,x_uid,nickname,x_time) VALUES(1, 1, 'tes\'t', '2022-09-09') is wrong.

[packy92]

has been fixed in this pr Fix embedded quotation contains backslash bug

[ltylty]

which version will fix this?

[packy92]

You can try this version 2.2.6