Re: After upgrading MariaDB from 5.3.7 to 5.5.24 version, JOIN's in DELETE statement are broken
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 ;

Re: JOIN statement inside the DELETE query are broken
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 ...

Re: JOIN statement inside the DELETE query are broken
Assigned to Sergei so he could decide whether we want to provide the backward compatibility with the previously accepted syntax.

Re: JOIN statement inside the DELETE query are broken
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.

Re: JOIN statement inside the DELETE query are broken
>> 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.

Re: JOIN statement inside the DELETE query are broken
Switching to 'Invalid' as Sergei confirmed it's not a bug

Launchpad bug id: 1011543