SPM: create binding failed

[ChenPeng2013]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

use test;
drop table if exists t;
create table t(a int);
select count(*) from t t1 join t t2;
create global binding for select count(*) from t t1 join t t2 using select count(*) from t t1 join t t2;

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

create global binding successed

3. What did you see instead (Required)

mysql> create global binding for select count(*) from t t1 join t t2 using select count(*) from t t1 join t t2;
ERROR 1064 (42000): 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 74 near "select count(*) from t t1 join t t2"

4. What is your TiDB version? (Required)

Release Version: v4.0.0-beta.2-1702-g0c3c4c588
Edition: Community
Git Commit Hash: 0c3c4c588aa52b5bc79edfed9d6f1431d9cd2e2b
Git Branch: master
UTC Build Time: 2020-12-03 02:55:39
GoVersion: go1.15.3
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

[eurekaka]

Hmmm, this is the same problem as DELETE USING. Here is the rule for JOIN:

JoinTable:
    TableRef CrossOpt TableRef %prec tableRefPriority
    {
    }
|   TableRef CrossOpt TableRef "USING" '(' ColumnNameList ')'
    {
    }

The parser is supposed to know the second rule cannot apply because there is no ( after USING in the query, and then apply rule 1 for the select statement, thus the USING is resolved to be the syntax of CREATE BINDING. However, the parser does not work as our expectation now, which behaves same as DELETE USING cases. We have discussed this problem before, and had no conclusion on how to fix it.

Before we figure out a solution for these kind of cases, we can mark this case as a known issue.

For the workaround for this query, both of the bellowing 2 commands work:

create global binding for select count(*) from t t1 join t t2 using (a) using select count(*) from t t1 join t t2 using (a);
create global binding for select count(*) from t t1, t t2 using select count(*) from t t1, t t2;

[qw4990]

Since essentially it's a problem about Parser and we have workarounds of it, we set it severity/minor and priority/backlog.