JOIN statement inside the DELETE query are broken
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Invalid
|
Undecided
|
Unassigned |
Bug Description
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.
summary: |
- After upgrading MariaDB from 5.3.7 to 5.5.24 version, JOIN's in DELETE - statement are broken + JOIN statement inside the DELETE query are broken |
description: | updated |
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 ;