JOIN statement inside the DELETE query are broken

[Mikhail Gavrilov]

After upgrading MariaDB from 5.3.7 to 5.5.24 version, JOIN statement inside the DELETE query are broken.

All triggers containing:

  DELETE sales_tree_p t1 FROM sales_tree_p t1
   JOIN (SELECT * FROM sales_tree_p WHERE id_sale_in = 1999) t2 ON t2.id_sale = t1.id_sale
   JOIN (SELECT * FROM sales_tree_p WHERE id_sale = 2000) t3 ON (t3.id_sale_in = t1.id_sale_in);

are lost!!!!!

And when I manually try exec this query I receive error message
Error Code: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 't1 FROM sales_tree_p t1
   JOIN (SELECT * FROM sales_tree_p WHERE id_sale_in = 1' at line 1

After downgrade to version 5.3.7 all my triggers appear again and begins work.

Thus, I see two problems:

1) the main, broken JOIN statement inside the DELETE query.
2) when update must be reported that the break triggers and stored procedures.

[Mikhail Gavrilov (mikegav)]

Trigger example:

DELIMITER $$

USE `banktest`$$

DROP TRIGGER /*!50032 IF EXISTS */ `sales_update`$$

CREATE
    /*!50017 DEFINER = 'root'@'localhost' */
    TRIGGER `sales_update` AFTER UPDATE ON `sales`
    FOR EACH ROW BEGIN
 IF NOT OLD.id_parent <=> NEW.id_parent THEN
  /*find all records for which the vertices of underlying referenced above, facing the top and remove them */
  DELETE sales_tree_p t1 FROM sales_tree_p t1
   JOIN (SELECT * FROM sales_tree_p WHERE id_sale_in = OLD.id_sale) t2 ON t2.id_sale = t1.id_sale
   JOIN (SELECT * FROM sales_tree_p WHERE id_sale = OLD.id_parent) t3 ON (t3.id_sale_in = t1.id_sale_in);
  /* calculate new values for the pair of underlying vertices and add them to the table */
  INSERT INTO sales_tree_p (id_sale, id_sale_in)
   SELECT t1.id_sale, t2.id_sale_in FROM sales_tree_p t1
   JOIN (SELECT * FROM sales_tree_p WHERE id_sale = NEW.id_parent) t2
   WHERE t1.id_sale_in = OLD.id_sale;
  /* maybe used for OQGRAPH
  update mega_test set origid = NEW.id_sale, destid = NEW.id_parent
   WHERE origid = OLD.id_sale AND destid = OLD.id_parent; */
 END IF;
END;
$$

DELIMITER ;

[Elena Stepanova (elenst)]

Hi,

This is not related to JOINs, this statement does not work either, both in MySQL 5.5 and MariaDB 5.5.

DELETE sales_tree_p t1 FROM sales_tree_p t1

However, looking at MySQL manual, this syntax has never been actually supported, probably it was just luck that it worked in MySQL 5.1 and based on it MariaDB versions. See http://dev.mysql.com/doc/refman/5.1/en/delete.html -- the 2nd variant of the syntax, multi-table, is about your statements, and it should be

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    tbl_name[.*] [, tbl_name[.*]] ...
    FROM table_references
    [WHERE where_condition]

So, in your case, it's

DELETE t1 FROM sales_tree_p t1 ...

[Elena Stepanova (elenst)]

Assigned to Sergei so he could decide whether we want to provide the backward compatibility with the previously accepted syntax.

[Mikhail Gavrilov (mikegav)]

Ok, syntax "DELETE t1 FROM sales_tree_p t1 ..." like me more than "DELETE sales_tree_p t1 FROM sales_tree_p t1 ..." but in this case mysql_upgrade should warn me, what trigger's would be unavailable after update database.

[Elena Stepanova (elenst)]

>> mysql_upgrade should warn me, what trigger's would be unavailable after update database

mysql_upgrade has specific functions (http://dev.mysql.com/doc/refman/5.5/en/mysql-upgrade.html) -- it checks and repairs user and system tables, but it does not verify the syntax validity of every structure in the database.

Besides, a warning (somewhere) would be understandable expectation if the previous syntax was officially supported in previous versions, but in this case it wasn't. The change might as well have been a regular bugfix in the parser, to make it scrutinize the syntax more thoroughly.

[Elena Stepanova (elenst)]

Switching to 'Invalid' as Sergei confirmed it's not a bug