Different number of rows updated with and without table_elimination

Bug #705369 reported by Philip Stoev
6
This bug affects 1 person
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_elimination=on and no rows with table_elimination=off .

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_elimination=on';
START TRANSACTION;
EXECUTE p1;
SELECT s_name FROM supplier;
ROLLBACK;
SET SESSION optimizer_switch = 'table_elimination=off';
START TRANSACTION;
EXECUTE p1;
SELECT s_name FROM supplier;
ROLLBACK;

dataset (load with storage-engine=InnoDB):

http://bazaar.launchpad.net/~randgen/randgen/rqg2/download/philips%40eve-20101203092302-30zu7xpf7uw0ub3a/dbt3s0.001.dump-20101203092242-l5ozx659nxhsvi0e-3/dbt3-s0.001.dump

description: updated
Changed in maria:
milestone: none → 5.1
assignee: nobody → Sergey Petrunia (sergefp)
Revision history for this message
Philip Stoev (philip-stoev) wrote :

Both mysql 5.1 and mysql 5.5 do not update the row in supplier. The UPDATE query reports "Rows matched: 5 Changed: 5"

description: updated
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers