no support of syntax `delete ignore t1.*`

[XuHuaiyu]

create table t11 (a int NOT NULL, b int, primary key (a));        
create table t12 (a int NOT NULL, b int, primary key (a));        
create table t2 (a int NOT NULL, b int, primary key (a));             
insert into t11 values (0, 10),(1, 11),(2, 12);    
insert into t12 values (33, 10),(0, 11),(2, 12);    
insert into t2 values (1, 21),(2, 12),(3, 23);      
select * from t11;         
select * from t12; 
select * from t2;

In MySQL:

mysql> delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b <> (select b from t2 where t11.a < t2.a);
ERROR 1242 (21000): Subquery returns more than 1 row

mysql> delete ignore t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b <> (select b from t2 where t11.a < t2.a);
Query OK, 2 rows affected, 1 warning (0.00 sec)

mysql> select * from t11;
+---+------+
| a | b    |
+---+------+
| 0 |   10 |
| 1 |   11 |
+---+------+
2 rows in set (0.00 sec)

mysql> delete ignore from t11 where t11.b <> (select b from t2 where t11.a < t2.a);
Query OK, 0 rows affected, 2 warnings (0.01 sec)

In TiDB:

tidb> delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b <> (select b from t2 where t11.a < t2.a);
ERROR 1105 (HY000): line 0 column 12 near ", t12.* from t11,t12 where t11.a = t12.a and t11.b <> (select b from t2 where t11.a < t2.a)" (total length 103)

tidb> delete ignore t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b <> (select b from t2 where t11.a < t2.a);
ERROR 1105 (HY000): line 0 column 19 near ", t12.* from t11,t12 where t11.a = t12.a and t11.b <> (select b from t2 where t11.a < t2.a)" (total length 110)

tidb> select * from t11;
+---+------+
| a | b    |
+---+------+
| 0 |   10 |
| 1 |   11 |
| 2 |   12 |
+---+------+
3 rows in set (0.00 sec)

tidb> delete ignore from t11 where t11.b <> (select b from t2 where t11.a < t2.a);
ERROR 1105 (HY000): subquery returns more than 1 row

4. What version of TiDB are you using (tidb-server -V)?

Git Commit Hash: 58dca67d6e576a50d45ce0dbd348ae485128e015
UTC Build Time: 2017-07-31 02:37:29

[kolbe]

It looks like this is fixed in master (c0d6185), but I'm not sure by which commit.

This is improved in master, but the ignore keyword is ignored :

drop table if exists t11, t12, t2;
create table t11 (a int NOT NULL, b int, primary key (a));        
create table t12 (a int NOT NULL, b int, primary key (a));        
create table t2 (a int NOT NULL, b int, primary key (a));             
insert into t11 values (0, 10),(1, 11),(2, 12);    
insert into t12 values (33, 10),(0, 11),(2, 12);    
insert into t2 values (1, 21),(2, 12),(3, 23);      
select * from t11;         
select * from t12; 
select * from t2;

delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b <> (select b from t2 where t11.a < t2.a);
delete ignore t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b <> (select b from t2 where t11.a < t2.a);
select * from t11;
delete ignore from t11 where t11.b <> (select b from t2 where t11.a < t2.a);

In TiDB:

..
mysql> delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b <> (select b from t2 where t11.a < t2.a);
ERROR 1105 (HY000): subquery returns more than 1 row
mysql> delete ignore t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b <> (select b from t2 where t11.a < t2.a);
ERROR 1105 (HY000): subquery returns more than 1 row
mysql> select * from t11;
+---+------+
| a | b    |
+---+------+
| 0 |   10 |
| 1 |   11 |
| 2 |   12 |
+---+------+
3 rows in set (0.00 sec)

mysql> delete ignore from t11 where t11.b <> (select b from t2 where t11.a < t2.a);
ERROR 1105 (HY000): subquery returns more than 1 row
mysql> SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v4.0.0-beta.2-893-g4e829aaee
Edition: Community
Git Commit Hash: 4e829aaee7b656aa807814708ae05af5233302af
Git Branch: master
UTC Build Time: 2020-08-04 12:40:52
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)

Vs MySQL 8.0.21:

..
mysql [localhost:8021] {msandbox} (test) > delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b <> (select b from t2 where t11.a < t2.a);
ERROR 1242 (21000): Subquery returns more than 1 row
mysql [localhost:8021] {msandbox} (test) > delete ignore t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b <> (select b from t2 where t11.a < t2.a);
Query OK, 2 rows affected, 2 warnings (0.01 sec)

mysql [localhost:8021] {msandbox} (test) > select * from t11;
+---+------+
| a | b    |
+---+------+
| 0 |   10 |
| 1 |   11 |
+---+------+
2 rows in set (0.00 sec)

mysql [localhost:8021] {msandbox} (test) > delete ignore from t11 where t11.b <> (select b from t2 where t11.a < t2.a);
Query OK, 0 rows affected, 2 warnings (0.00 sec)

[morgo]

It looks like it is only delete ignore which is unsupported. insert ignore and update ignore work as expected:

drop table if exists t11, t12, t2;
create table t11 (a int NOT NULL, b int, primary key (a));        
create table t12 (a int NOT NULL, b int, primary key (a));        
create table t2 (a int NOT NULL, b int, primary key (a));             
insert into t11 values (0, 10),(1, 11),(2, 12);    
insert into t12 values (33, 10),(0, 11),(2, 12);    
insert into t2 values (1, 21),(2, 12),(3, 23);      

delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b <> (select b from t2 where t11.a < t2.a); -- expected: error
delete ignore t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b <> (select b from t2 where t11.a < t2.a); -- expected: no error

insert into t2 values (1, 21); -- expected: error
insert ignore into t2 values (1, 21); -- expected: no error

update t2 set a=1 WHERE a = 2; -- expected: error
update ignore t2 set a=1 WHERE a = 2; -- expected: error