Vitess does not support DML DELETE JOIN syntax

[aquarapid]

Not a common construct, but I am logging this anyway.

Scenario:

• Setup 101 examples:

$ cd examples/local/
$ sh 101_initial_cluster.sh
.
.

• Connect via mysql CLI and create a simple table and insert some rows:

$ mysql -u mysql_user -h 127.0.0.1 -P 15306
.
.
. 
mysql> create table t1 (c1 int, primary key (c1));                     
Query OK, 0 rows affected (0.05 sec)

mysql> insert into t1 (c1) values (1),(2),(3);                     
Query OK, 3 rows affected (0.01 sec)

mysql> select * from t1;                                       
+----+
| c1 |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.01 sec)

• Now, use the MySQL "join" DELETE syntax:

mysql> delete t1 from t1;
ERROR 1064 (42000): vtgate: http://localhost:15001/: target: commerce.0.master, used tablet: zone1-100 (localhost): vttablet: rpc error: code = InvalidArgument desc = 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 'limit 10001' at line 1 (errno 1064) (sqlstate 42000) (CallerID: userData1): Sql: "delete t1 from t1", BindVars: {}

• This fails because of the row limit we are blindly adding on the vttablet side makes no sense with a JOIN (even when there is only an implicit one such as in the above case).

• To show that this works fine in the underlying MySQL, let us connect directly to the underlying tablet MySQL and try again:

$ mysql -S ~/go/vtdataroot/vt_0000000100/mysql.sock -u root --binary-as-hex=false  vt_commerce
.
.
.
mysql> select * from t1; 
+----+
| c1 |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)

mysql> delete t1 from t1;
Query OK, 3 rows affected (0.00 sec)

mysql> select * from t1; 
Empty set (0.00 sec)

i.e. the delete worked fine.

To show that the using the limit with MySQL directly gives the error we saw, we try the following:

mysql> delete t1 from t1 limit 1;
ERROR 1064 (42000): 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 'limit 1' at line 1

Yes, that does seem to be the problem.

Note that the general case of this syntax is something like:

DELETE t1 from t1,t2 <join condition>

[harshit-gangal]

This is partially fixed by #8393

[harshit-gangal]

Support is added #14855