| 2011-01-20 11:41:02 |
Philip Stoev |
bug |
|
|
added bug |
| 2011-01-20 11:43:25 |
Philip Stoev |
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 . Curiously, converting the UPDATE into an equivalent SELECT returns an empty result set.
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;
The dataset will be attached shortly. Please load with storage-engine=InnoDB |
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 . Curiously, converting the UPDATE into an equivalent SELECT returns an empty result set.
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
|
|
| 2011-01-20 11:45:30 |
Philip Stoev |
maria: milestone |
|
5.1 |
|
| 2011-01-20 11:45:46 |
Philip Stoev |
maria: assignee |
|
Sergey Petrunia (sergefp) |
|
| 2011-01-21 07:45:00 |
Philip Stoev |
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 . Curiously, converting the UPDATE into an equivalent SELECT returns an empty result set.
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
|
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
|
|
