Different number of rows updated with and without table_elimination
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
New
|
Undecided
|
Sergey Petrunia |
Bug Description
The following query:
UPDATE part RIGHT JOIN partsupp ON ( p_partkey = ps_partkey ) RIGHT JOIN supplier ON ( ps_suppkey = s_suppkey ) RIGHT JOIN nation ON ( s_nationkey = n_nationkey ) JOIN region ON ( n_regionkey = r_regionkey ) SET s_name = DEFAULT , r_comment = DEFAULT WHERE r_comment IS NOT NULL OR ps_suppkey BETWEEN 10 AND 14;
updates one row in supplier with table_eliminati
Test case:
PREPARE p1 FROM "UPDATE part RIGHT JOIN partsupp ON ( p_partkey = ps_partkey ) RIGHT JOIN supplier ON ( ps_suppkey = s_suppkey ) RIGHT JOIN nation ON ( s_nationkey = n_nationkey ) JOIN region ON ( n_regionkey = r_regionkey ) SET s_name = DEFAULT , r_comment = DEFAULT WHERE r_comment IS NOT NULL OR ps_suppkey BETWEEN 10 AND 14;";
SET AUTOCOMMIT=OFF;
SET SESSION optimizer_switch = 'table_
START TRANSACTION;
EXECUTE p1;
SELECT s_name FROM supplier;
ROLLBACK;
SET SESSION optimizer_switch = 'table_
START TRANSACTION;
EXECUTE p1;
SELECT s_name FROM supplier;
ROLLBACK;
dataset (load with storage-
description: | updated |
Changed in maria: | |
milestone: | none → 5.1 |
assignee: | nobody → Sergey Petrunia (sergefp) |
description: | updated |
Both mysql 5.1 and mysql 5.5 do not update the row in supplier. The UPDATE query reports "Rows matched: 5 Changed: 5"