Internal SQL give parser error for binary literal when analyze table

[guo-shaoge]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

1. git clone https://github.com/pingcap/tidb
2. cd tidb && vim session/sessiontest/session_test.go
3. Append the following code(need to set sql mode NO_BACKSLASH_ESCAPES).

func TestRandomBinary(t *testing.T) {
    store := testkit.CreateMockStoreWithSchemaLease(t, 1*time.Second)

    tk := testkit.NewTestKit(t, store)
    tk.MustExec("use test")

    ctx := kv.WithInternalSourceType(context.Background(), kv.InternalTxnStats)
    allBytes := [][]byte{
        {4, 0, 0, 0, 0, 0, 0, 4, '2'},
        {4, 0, 0, 0, 0, 0, 0, 4, '.'},
        {4, 0, 0, 0, 0, 0, 0, 4, '*'},
        {4, 0, 0, 0, 0, 0, 0, 4, '('},
        {4, 0, 0, 0, 0, 0, 0, 4, '\''},
        {4, 0, 0, 0, 0, 0, 0, 4, '!'},
        {4, 0, 0, 0, 0, 0, 0, 4, 29},
        {4, 0, 0, 0, 0, 0, 0, 4, 28},
        {4, 0, 0, 0, 0, 0, 0, 4, 23},
        {4, 0, 0, 0, 0, 0, 0, 4, 16},
    }
    sql := "insert into mysql.stats_top_n (table_id, is_index, hist_id, value, count) values "
    var val string
    for i, bytes := range allBytes {
        if i == 0 {
            val += sqlexec.MustEscapeSQL("(874, 0, 1, %?, 3)", bytes)
        } else {
            val += sqlexec.MustEscapeSQL(",(874, 0, 1, %?, 3)", bytes)
        }
    }
    sql += val
    fmt.Println("gjt debug fmt", sql)
	tk.MustExec("set sql_mode = 'NO_BACKSLASH_ESCAPES';");
    _, err := tk.Session().ExecuteInternal(ctx, sql)
    require.NoError(t, err)
}

4. go test . -run TestRandomBinary --tags=intest -v

2. What did you expect to see? (Required)

run ok

3. What did you see instead (Required)

[2023/04/26 15:17:18.406 +08:00] [WARN] [session.go:1788] ["parse SQL failed"] [error="line 1 column 307 near \"\u0004\\0\\0\\0\\0\\0\\0\u0004!', 3),(874, 0, 1, _binary'\u0004\\0\\0\\0\\0\\0\\0\u0004\u001d', 3),(874, 0, 1, _binary'\u0004\\0\\0\\0\\0\\0\\0\u0004\u001c', 3),(874, 0, 1, _binary'\u0004\\0\\0\\0\\0\\0\\0\u0004\u0017', 3),(874, 0, 1, _binary'\u0004\\0\\0\\0\\0\\0\\0\u0004\u0010', 3)\" "] [errorVerbose="line 1 column 307 near \"\u0004\\0\\0\\0\\0\\0\\0\u0004!', 3),(874, 0, 1, _binary'\u0004\\0\\0\\0\\0\\0\\0\u0004\u001d', 3),(874, 0, 1, _binary'\u0004\\0\\0\\0\\0\\0\\0\u0004\u001c', 3),(874, 0, 1, _binary'\u0004\\0\\0\\0\\0\\0\\0\u0004\u0017', 3),(874, 0, 1, _binary'\u0004\\0\\0\\0\\0\\0\\0\u0004\u0010', 3)\" \ngithub.com/pingcap/errors.AddStack\n\t/home/guojiangtao/go/pkg/mod/github.com/pingcap/[email protected]/errors.go:174\ngithub.com/pingcap/errors.Trace\n\t/home/guojiangtao/go/pkg/mod/github.com/pingcap/[email protected]/juju_adaptor.go:15\ngithub.com/pingcap/tidb/parser.(*Parser).ParseSQL\n\t/home/guojiangtao/work/tidb/parser/yy_parser.go:170\ngithub.com/pingcap/tidb/session.(*session).ParseSQL\n\t/home/guojiangtao/work/tidb/session/session.go:1565\ngithub.com/pingcap/tidb/session.(*session).ParseWithParams\n\t/home/guojiangtao/work/tidb/session/session.go:1775\ngithub.com/pingcap/tidb/session.(*session).ExecuteInternal\n\t/home/guojiangtao/work/tidb/session/session.go:1677\ngithub.com/pingcap/tidb/session/session_test_test.TestRandomBinary\n\t/home/guojiangtao/work/tidb/session/session_test/session_test.go:4137\ntesting.tRunner\n\t/usr/local/go/src/testing/testing.go:1576\nruntime.goexit\n\t/usr/local/go/src/runtime/asm_amd64.s:1598"] [SQL="insert into mysql.stats_top_n (table_id, is_index, hist_id, value, count) values (874, 0, 1, _binary'\u0004\\0\\0\\0\\0\\0\\0\u00042', 3),(874, 0, 1, _binary'\u0004\\0\\0\\0\\0\\0\\0\u0004.', 3),(874, 0, 1, _binary'\u0004\\0\\0\\0\\0\\0\\0\u0004*', 3),(874, 0, 1, _binary'\u0004\\0\\0\\0\\0\\0\\0\u0004(', 3),(874, 0, 1, _binary'\u0004\\0\\0\\0\\0\\0\\0\u0004\\'', 3),(874, 0, 1, _binary'\u0004\\0\\0\\0\\0\\0\\0\u0004!', 3),(874, 0, 1, _binary'\u0004\\0\\0\\0\\0\\0\\0\u0004\u001d', 3),(874, 0, 1, _binary'\u0004\\0\\0\\0\\0\\0\\0\u0004\u001c', 3),(874, 0, 1, _binary'\u0004\\0\\0\\0\\0\\0\\0\u0004\u0017', 3),(874, 0, 1, _binary'\u0004\\0\\0\\0\\0\\0\\0\u0004\u0010', 3)"]
    session_test.go:4138:
        	Error Trace:	/home/guojiangtao/work/tidb/session/session_test/session_test.go:4138
        	Error:      	Received unexpected error:
        	            	[parser:1064]You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use line 1 column 307 near "\0\0\0\0\0\0!', 3),(874, 0, 1, _binary'\0\0\0\0\0\0', 3),(874, 0, 1, _binary'\0\0\0\0\0\0', 3),(874, 0, 1, _binary'\0\0\0\0\0\0', 3),(874, 0, 1, _binary'\0\0\0\0\0\0', 3)"
        	            	github.com/pingcap/errors.AddStack
        	            		/home/guojiangtao/go/pkg/mod/github.com/pingcap/[email protected]/errors.go:174
        	            	github.com/pingcap/errors.(*Error).GenWithStackByArgs
        	            		/home/guojiangtao/go/pkg/mod/github.com/pingcap/[email protected]/normalize.go:164
        	            	github.com/pingcap/tidb/util.SyntaxError
        	            		/home/guojiangtao/work/tidb/util/misc.go:159
        	            	github.com/pingcap/tidb/session.(*session).ParseWithParams
        	            		/home/guojiangtao/work/tidb/session/session.go:1790
        	            	github.com/pingcap/tidb/session.(*session).ExecuteInternal
        	            		/home/guojiangtao/work/tidb/session/session.go:1677
        	            	github.com/pingcap/tidb/session/session_test_test.TestRandomBinary
        	            		/home/guojiangtao/work/tidb/session/session_test/session_test.go:4137
        	            	testing.tRunner
        	            		/usr/local/go/src/testing/testing.go:1576
        	            	runtime.goexit
        	            		/usr/local/go/src/runtime/asm_amd64.s:1598
        	Test:       	TestRandomBinary

4. What is your TiDB version? (Required)

master cea3c86

[guo-shaoge]

Not a bug. SQL mode related issue

[guo-shaoge]

Not a bug. SQL mode related issue

Maybe we should change the way of []byte for internal sql. Like https://dev.mysql.com/doc/refman/8.0/en/hexadecimal-literals.html, aka we should use SQL like insert into stats_top_n (bin_col) values (X'ACDE') instead of insert into stats_top_n (bin_col) values (_binary'xxx')

[guo-shaoge]

Or we can just set a proper sql mode for internal sql, and user cannot touch the internal sql's sql mode.

[guo-shaoge]

This is from https://ask.pingcap.com/t/tidb-auto-analyze-returns-error-parser-1064/531