Bug #82238 Various incorrect looking errors for multi-table DELETE statements

• Submitted: 15 Jul 2016 3:22
• Modified: 24 Dec 2019 14:46
• Reporter: Roel Van de Paar
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S1 (Critical)
• Version: 5.7.13, 5.5.50, 5.6.31
• OS: Any
• CPU Architecture: Any

[15 Jul 2016 3:22] Roel Van de Paar

Description:
mysql> DELETE FROM a1 USING t1 AS a1 JOIN t2 AS a2;
Query OK, 0 rows affected (0.00 sec)

yet: 

mysql> DELETE FROM a1 USING t1 AS a1 OUTER JOIN t2 AS a2;
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 'OUTER JOIN t2 AS a2' at line 1

mysql> DELETE FROM a2 USING t1 AS a1 OUTER JOIN t2 AS a2;
ERROR 1109 (42S02): Unknown table 'a2' in MULTI DELETE

and,

mysql> DELETE FROM a2 USING t1 AS a1 NATURAL JOIN t2 AS a2;
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM a2 USING t1 AS a1 NATURAL LEFT JOIN t2 AS a2;
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM a2 USING t1 AS a1 NATURAL RIGHT JOIN t2 AS a2;
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM a2 USING t1 AS a1 NATURAL RIGHT OUTER JOIN t2 AS a2;
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM a2 USING t1 AS a1 NATURAL LEFT OUTER JOIN t2 AS a2;
Query OK, 0 rows affected (0.00 sec)

yet:

mysql> DELETE FROM a2 USING t1 AS a1 LEFT OUTER JOIN t2 AS a2;
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 '' at line 1

mysql> DELETE FROM a2 USING t1 AS a1 RIGHT OUTER JOIN t2 AS a2;
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 '' at line 1

mysql> DELETE FROM a2 USING t1 AS a1 OUTER JOIN t2 AS a2;
ERROR 1109 (42S02): Unknown table 'a2' in MULTI DELETE

How to repeat:
CREATE TABLE t1 (c1 mediumint(9) DEFAULT NULL,c2 int(255) DEFAULT NULL,c3 float(1,1) unsigned DEFAULT NULL) ENGINE=InnoDB;
CREATE TABLE t2 (c1 varchar(2) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,c2 tinyint(1) unsigned zerofill DEFAULT NULL,c3 int(254) unsigned zerofill DEFAULT NULL,PRIMARY KEY (c1),UNIQUE KEY c1 (c1),UNIQUE KEY c2 (c2)) ENGINE=InnoDB;
DELETE FROM a1 USING t1 AS a1 OUTER JOIN t2 AS a2;
DELETE FROM a2 USING t1 AS a1 OUTER JOIN t2 AS a2;
DELETE FROM a2 USING t1 AS a1 LEFT OUTER JOIN t2 AS a2;
DELETE FROM a2 USING t1 AS a1 RIGHT OUTER JOIN t2 AS a2;
DELETE FROM a2 USING t1 AS a1 OUTER JOIN t2 AS a2;

[15 Jul 2016 6:05] MySQL Verification Team

Hello Roel,

Thank you for the report and test case.
Observed this with 5.5.50, 5.6.31 and 5.7.13 builds.

Thanks,
Umesh

[24 Dec 2019 14:46] Roy Lyseng

Posted by developer:
 
This is not a bug.

  DELETE FROM a1 USING t1 AS a1 OUTER JOIN t2 AS a2;
  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 'OUTER JOIN t2 AS a2' at line 1

OUTER JOIN must be prefixed with LEFT or RIGHT, but then OUTER can be omitted.
In addition, an OUTER JOIN specification requires a join condition.
Thus, this statement succeeds:

  DELETE FROM a1 USING t1 AS a1 LEFT JOIN t2 AS a2 ON TRUE;

This statement

  DELETE FROM a2 USING t1 AS a1 OUTER JOIN t2 AS a2;

can be rewritten as

  DELETE FROM a2 USING t1 AS a1 LEFT JOIN t2 AS a2 ON TRUE;

This statement:

  DELETE FROM a2 USING t1 AS a1 LEFT OUTER JOIN t2 AS a2;
  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 '' at line 1

lacks a join condition. And so does this:

  DELETE FROM a2 USING t1 AS a1 RIGHT OUTER JOIN t2 AS a2;
  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 '' at line 1