diff options
Diffstat (limited to 'mysql-test')
| -rw-r--r-- | mysql-test/r/derived.result | 14 | ||||
| -rw-r--r-- | mysql-test/t/derived.test | 19 |
2 files changed, 33 insertions, 0 deletions
diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result index 3a3b69f1fc7..d0f42c3d3ec 100644 --- a/mysql-test/r/derived.result +++ b/mysql-test/r/derived.result @@ -539,6 +539,7 @@ n d1 d2 result 2085 2012-01-01 00:00:00 2013-01-01 00:00:00 0 2084 2012-02-01 00:00:00 2013-01-01 00:00:00 0 drop table t1; +set @save_derived_optimizer_switch_bug=@@optimizer_switch; SET optimizer_switch = 'derived_merge=on,derived_with_keys=on,in_to_exists=on'; CREATE TABLE t1 (a INT) ENGINE=MyISAM; INSERT INTO t1 VALUES (8); @@ -553,4 +554,17 @@ id select_type table type possible_keys key key_len ref rows Extra Warnings: Note 1249 Select 4 was reduced during optimization DROP TABLE t1, t2; +set optimizer_switch=@save_derived_optimizer_switch_bug; +# +# MDEV-6163: Error while executing an update query that has the +# same table in a sub-query +# +set @save_derived_optimizer_switch_bug=@@optimizer_switch; +SET optimizer_switch = 'derived_merge=on'; +create table t1 (balance float, accountId varchar(64), primary key (accountId)); +insert into t1 (accountId,balance) values +('dealer-1',199354.0),('dealer-2',0),('dealer-3',0),('dealer-5',0),('FINANCE',-200000),('OPERATOR',0); +update t1 set balance=(select sum(balance) from (SELECT balance FROM t1 where accountId like 'dealer%') AS copied) where accountId = 'OPERATOR'; +set optimizer_switch=@save_derived_optimizer_switch_bug; +drop table t1; set optimizer_switch=@save_derived_optimizer_switch; diff --git a/mysql-test/t/derived.test b/mysql-test/t/derived.test index 4b1d7604b9d..61ae3695a1f 100644 --- a/mysql-test/t/derived.test +++ b/mysql-test/t/derived.test @@ -465,6 +465,7 @@ drop table t1; # # MDEV-5012 Server crashes in Item_ref::real_item on EXPLAIN with select subqueries or views, constant table, derived_merge+derived_with_keys # +set @save_derived_optimizer_switch_bug=@@optimizer_switch; SET optimizer_switch = 'derived_merge=on,derived_with_keys=on,in_to_exists=on'; CREATE TABLE t1 (a INT) ENGINE=MyISAM; INSERT INTO t1 VALUES (8); @@ -473,5 +474,23 @@ INSERT INTO t2 VALUES (1),(7); EXPLAIN SELECT * FROM (SELECT * FROM t1) AS table1, (SELECT DISTINCT * FROM t2) AS table2 WHERE b = a AND a <> ANY (SELECT 9); DROP TABLE t1, t2; +set optimizer_switch=@save_derived_optimizer_switch_bug; + +--echo # +--echo # MDEV-6163: Error while executing an update query that has the +--echo # same table in a sub-query +--echo # + +set @save_derived_optimizer_switch_bug=@@optimizer_switch; +SET optimizer_switch = 'derived_merge=on'; +create table t1 (balance float, accountId varchar(64), primary key (accountId)); + +insert into t1 (accountId,balance) values +('dealer-1',199354.0),('dealer-2',0),('dealer-3',0),('dealer-5',0),('FINANCE',-200000),('OPERATOR',0); + +update t1 set balance=(select sum(balance) from (SELECT balance FROM t1 where accountId like 'dealer%') AS copied) where accountId = 'OPERATOR'; +set optimizer_switch=@save_derived_optimizer_switch_bug; +drop table t1; + set optimizer_switch=@save_derived_optimizer_switch; |