diff options
Diffstat (limited to 'mysql-test')
24 files changed, 524 insertions, 49 deletions
diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result index b5a9485d9db..a4d7386c715 100644 --- a/mysql-test/r/derived.result +++ b/mysql-test/r/derived.result @@ -1,4 +1,3 @@ -drop table if exists t1,t2,t3; set @save_derived_optimizer_switch=@@optimizer_switch; set optimizer_switch='derived_merge=off,derived_with_keys=off'; select * from (select 2 from DUAL) b; diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result index 8c6752aef53..74c4dd68468 100644 --- a/mysql-test/r/derived_view.result +++ b/mysql-test/r/derived_view.result @@ -2978,7 +2978,7 @@ DROP TABLE t1, t2; set optimizer_switch=@exit_optimizer_switch; set join_cache_level=@exit_join_cache_level; # -# Bug mdev-12812: EXPLAIN for query with many expensive derived +# Bug mdev-18479: EXPLAIN for query with many expensive derived # CREATE TABLE t1 (id int auto_increment primary key, @@ -3279,15 +3279,15 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY p10 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join) 1 PRIMARY <derived17> ALL NULL NULL NULL NULL 50328437500000 Using where; Using join buffer (incremental, BNL join) 1 PRIMARY <derived14> ALL NULL NULL NULL NULL 27680640625000000 Using where; Using join buffer (incremental, BNL join) -1 PRIMARY <derived7> ALL NULL NULL NULL NULL 7798774269472204800 Using where; Using join buffer (incremental, BNL join) -1 PRIMARY <derived8> ALL NULL NULL NULL NULL 7798774269472204800 Using where; Using join buffer (incremental, BNL join) -1 PRIMARY <derived9> ALL NULL NULL NULL NULL -3222391729959550976 Using where; Using join buffer (incremental, BNL join) -1 PRIMARY <derived10> ALL NULL NULL NULL NULL -3222391729959550976 Using where; Using join buffer (incremental, BNL join) -1 PRIMARY <derived11> ALL NULL NULL NULL NULL -3222391729959550976 Using where; Using join buffer (incremental, BNL join) -1 PRIMARY <derived12> ALL NULL NULL NULL NULL -3222391729959550976 Using where; Using join buffer (incremental, BNL join) -1 PRIMARY <derived13> ALL NULL NULL NULL NULL -3222391729959550976 Using where; Using join buffer (incremental, BNL join) -1 PRIMARY <derived15> ALL NULL NULL NULL NULL -3222391729959550976 Using where; Using join buffer (incremental, BNL join) -1 PRIMARY <derived16> ALL NULL NULL NULL NULL -3222391729959550976 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY <derived9> ALL NULL NULL NULL NULL 15224352343750000640 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY <derived10> ALL NULL NULL NULL NULL 15224352343750000640 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY <derived11> ALL NULL NULL NULL NULL 15224352343750000640 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY <derived12> ALL NULL NULL NULL NULL 15224352343750000640 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY <derived13> ALL NULL NULL NULL NULL 15224352343750000640 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY <derived15> ALL NULL NULL NULL NULL 15224352343750000640 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY <derived16> ALL NULL NULL NULL NULL 15224352343750000640 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY <derived7> ALL NULL NULL NULL NULL 18446744073709551615 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY <derived8> ALL NULL NULL NULL NULL 18446744073709551615 Using where; Using join buffer (incremental, BNL join) 17 DERIVED t2 system NULL NULL NULL NULL 1 17 DERIVED p4 ALL NULL NULL NULL NULL 550 Using where 17 DERIVED p5 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (flat, BNL join) diff --git a/mysql-test/r/join.result b/mysql-test/r/join.result index bd07fc58a88..c112843caef 100644 --- a/mysql-test/r/join.result +++ b/mysql-test/r/join.result @@ -1139,7 +1139,7 @@ SELECT 1 FROM v1 right join v1 AS v2 ON RAND(); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where -1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Note 1003 select 1 AS `1` from `test`.`t1` left join `test`.`t1` `t2` on(1 = 1) left join (`test`.`t1` left join `test`.`t1` `t2` on(1 = 1)) on(rand()) where 1 diff --git a/mysql-test/r/join_nested.result b/mysql-test/r/join_nested.result index 917a31e2a79..0f9b99d1e76 100644 --- a/mysql-test/r/join_nested.result +++ b/mysql-test/r/join_nested.result @@ -1966,3 +1966,36 @@ Note 1003 select 1 AS `K1`,'T1Row1' AS `Name`,`t2a`.`K2` AS `K2`,`t2a`.`K1r` AS DROP VIEW v1; DROP TABLE t1,t2; set optimizer_search_depth= @tmp_mdev621; +# +# MDEV-19588: Nested left joins using optimized join cache +# +set optimizer_switch='optimize_join_buffer_size=on'; +set @save_join_cache_level= @@join_cache_level; +set join_cache_level=2; +CREATE TABLE t1 (i1 int, c1 varchar(20), pk int) engine=myisam; +CREATE TABLE t2 (pk int, c1 varchar(20), i1 int) engine=myisam; +INSERT INTO t2 VALUES (7,'a',-912),(8,'a',5); +CREATE TABLE t3 (pk int, c1 varchar(20), i1 int) engine=myisam; +INSERT INTO t3 VALUES +(1,'a',-145),(2,'a',6),(3,'a',1),(7,'a',NULL),(8,'a',889),(9,'a',146), +(10,'a',177),(16,'a',-433),(17,'a',NULL),(18,'a',2),(19,'a',3),(20,'a',5), +(21,'a',-484),(22,'a',369),(23,'a',-192),(24,'a',-163),(25,'a',5),(26,'a',NULL); +SELECT t3.* +FROM t3 LEFT JOIN t1 LEFT JOIN t2 ON t1.i1 = t2.i1 ON t3.i1 = t1.i1 +WHERE t2.pk < 13 OR t3.i1 IS NULL; +pk c1 i1 +7 a NULL +17 a NULL +26 a NULL +explain extended SELECT t3.* +FROM t3 LEFT JOIN t1 LEFT JOIN t2 ON t1.i1 = t2.i1 ON t3.i1 = t1.i1 +WHERE t2.pk < 13 OR t3.i1 IS NULL; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t3 ALL NULL NULL NULL NULL 18 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 0 0.00 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`t3`.`pk` AS `pk`,`test`.`t3`.`c1` AS `c1`,`test`.`t3`.`i1` AS `i1` from `test`.`t3` left join (`test`.`t1` left join `test`.`t2` on(`test`.`t2`.`i1` = `test`.`t3`.`i1`)) on(`test`.`t1`.`i1` = `test`.`t3`.`i1`) where `test`.`t2`.`pk` < 13 or `test`.`t3`.`i1` is null +DROP TABLE t1,t2,t3; +set join_cache_level= @save_join_cache_level; +set optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/r/join_nested_jcl6.result b/mysql-test/r/join_nested_jcl6.result index cf8d49930c8..f9e1c49b03c 100644 --- a/mysql-test/r/join_nested_jcl6.result +++ b/mysql-test/r/join_nested_jcl6.result @@ -1977,6 +1977,39 @@ Note 1003 select 1 AS `K1`,'T1Row1' AS `Name`,`t2a`.`K2` AS `K2`,`t2a`.`K1r` AS DROP VIEW v1; DROP TABLE t1,t2; set optimizer_search_depth= @tmp_mdev621; +# +# MDEV-19588: Nested left joins using optimized join cache +# +set optimizer_switch='optimize_join_buffer_size=on'; +set @save_join_cache_level= @@join_cache_level; +set join_cache_level=2; +CREATE TABLE t1 (i1 int, c1 varchar(20), pk int) engine=myisam; +CREATE TABLE t2 (pk int, c1 varchar(20), i1 int) engine=myisam; +INSERT INTO t2 VALUES (7,'a',-912),(8,'a',5); +CREATE TABLE t3 (pk int, c1 varchar(20), i1 int) engine=myisam; +INSERT INTO t3 VALUES +(1,'a',-145),(2,'a',6),(3,'a',1),(7,'a',NULL),(8,'a',889),(9,'a',146), +(10,'a',177),(16,'a',-433),(17,'a',NULL),(18,'a',2),(19,'a',3),(20,'a',5), +(21,'a',-484),(22,'a',369),(23,'a',-192),(24,'a',-163),(25,'a',5),(26,'a',NULL); +SELECT t3.* +FROM t3 LEFT JOIN t1 LEFT JOIN t2 ON t1.i1 = t2.i1 ON t3.i1 = t1.i1 +WHERE t2.pk < 13 OR t3.i1 IS NULL; +pk c1 i1 +7 a NULL +17 a NULL +26 a NULL +explain extended SELECT t3.* +FROM t3 LEFT JOIN t1 LEFT JOIN t2 ON t1.i1 = t2.i1 ON t3.i1 = t1.i1 +WHERE t2.pk < 13 OR t3.i1 IS NULL; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t3 ALL NULL NULL NULL NULL 18 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 0 0.00 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`t3`.`pk` AS `pk`,`test`.`t3`.`c1` AS `c1`,`test`.`t3`.`i1` AS `i1` from `test`.`t3` left join (`test`.`t1` left join `test`.`t2` on(`test`.`t2`.`i1` = `test`.`t3`.`i1`)) on(`test`.`t1`.`i1` = `test`.`t3`.`i1`) where `test`.`t2`.`pk` < 13 or `test`.`t3`.`i1` is null +DROP TABLE t1,t2,t3; +set join_cache_level= @save_join_cache_level; +set optimizer_switch=@save_optimizer_switch; CREATE TABLE t5 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b)); CREATE TABLE t6 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b)); CREATE TABLE t7 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b)); diff --git a/mysql-test/r/mdev13607.result b/mysql-test/r/mdev13607.result index 08848bc645b..f5edfa6a8d6 100644 --- a/mysql-test/r/mdev13607.result +++ b/mysql-test/r/mdev13607.result @@ -76,21 +76,21 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY p7 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) 1 PRIMARY p8 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) 1 PRIMARY p9 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) -1 PRIMARY <derived3> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join) -1 PRIMARY <derived4> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join) -1 PRIMARY <derived5> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join) -1 PRIMARY <derived6> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join) -1 PRIMARY <derived7> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join) -1 PRIMARY <derived8> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join) -1 PRIMARY <derived9> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join) -1 PRIMARY <derived10> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join) -1 PRIMARY <derived11> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join) -1 PRIMARY <derived12> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join) -1 PRIMARY <derived13> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join) -1 PRIMARY <derived14> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join) -1 PRIMARY <derived15> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join) -1 PRIMARY <derived16> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join) -1 PRIMARY <derived17> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join) +1 PRIMARY <derived3> ALL NULL NULL NULL NULL 18446744073709551615 Using join buffer (incremental, BNL join) +1 PRIMARY <derived4> ALL NULL NULL NULL NULL 18446744073709551615 Using join buffer (incremental, BNL join) +1 PRIMARY <derived5> ALL NULL NULL NULL NULL 18446744073709551615 Using join buffer (incremental, BNL join) +1 PRIMARY <derived6> ALL NULL NULL NULL NULL 18446744073709551615 Using join buffer (incremental, BNL join) +1 PRIMARY <derived7> ALL NULL NULL NULL NULL 18446744073709551615 Using join buffer (incremental, BNL join) +1 PRIMARY <derived8> ALL NULL NULL NULL NULL 18446744073709551615 Using join buffer (incremental, BNL join) +1 PRIMARY <derived9> ALL NULL NULL NULL NULL 18446744073709551615 Using join buffer (incremental, BNL join) +1 PRIMARY <derived10> ALL NULL NULL NULL NULL 18446744073709551615 Using join buffer (incremental, BNL join) +1 PRIMARY <derived11> ALL NULL NULL NULL NULL 18446744073709551615 Using join buffer (incremental, BNL join) +1 PRIMARY <derived12> ALL NULL NULL NULL NULL 18446744073709551615 Using join buffer (incremental, BNL join) +1 PRIMARY <derived13> ALL NULL NULL NULL NULL 18446744073709551615 Using join buffer (incremental, BNL join) +1 PRIMARY <derived14> ALL NULL NULL NULL NULL 18446744073709551615 Using join buffer (incremental, BNL join) +1 PRIMARY <derived15> ALL NULL NULL NULL NULL 18446744073709551615 Using join buffer (incremental, BNL join) +1 PRIMARY <derived16> ALL NULL NULL NULL NULL 18446744073709551615 Using join buffer (incremental, BNL join) +1 PRIMARY <derived17> ALL NULL NULL NULL NULL 18446744073709551615 Using join buffer (incremental, BNL join) 17 DERIVED r1 ALL NULL NULL NULL NULL 2 17 DERIVED d1 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) 17 DERIVED r2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) diff --git a/mysql-test/r/multi_update.result b/mysql-test/r/multi_update.result index 11823941e56..a5c60d6dfc2 100644 --- a/mysql-test/r/multi_update.result +++ b/mysql-test/r/multi_update.result @@ -413,7 +413,6 @@ c2_id c2_p_id c2_note c2_active 1 1 A Note 1 drop table t1, t2; connect root,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK; -connection root; create database mysqltest; create table mysqltest.t1 (a int, b int, primary key (a)); create table mysqltest.t2 (a int, b int, primary key (a)); @@ -422,7 +421,6 @@ create user mysqltest_1@localhost; grant select on mysqltest.* to mysqltest_1@localhost; grant update on mysqltest.t1 to mysqltest_1@localhost; connect user1,localhost,mysqltest_1,,mysqltest,$MASTER_MYPORT,$MASTER_MYSOCK; -connection user1; update t1, t2 set t1.b=1 where t1.a=t2.a; update t1, t2 set t1.b=(select t3.b from t3 where t1.a=t3.a) where t1.a=t2.a; connection root; @@ -457,13 +455,10 @@ create table t2 (a int); insert into t2 values (10), (20), (30); create view v1 as select a as b, a/10 as a from t2; connect locker,localhost,root,,test; -connection locker; lock table t1 write; connect changer,localhost,root,,test; -connection changer; alter table t1 add column c int default 100 after a; connect updater,localhost,root,,test; -connection updater; update t1, v1 set t1.b=t1.a+t1.b+v1.b where t1.a=v1.a; connection locker; unlock tables; @@ -1024,4 +1019,27 @@ triggered triggered drop table t1,t2, t3; drop user foo; -end of 5.5 tests +create table t1 (a int, b int); +create table t2 (c int, d int); +insert t1 values (1,2),(3,4); +insert t2 values (5,6),(7,8); +create table t0 (x int); +insert t0 values (11), (22); +create trigger tr1 before update on t2 for each row insert t0 values (new.c); +connect con1, localhost, root; +lock table t0 write; +connection default; +update t1 join t2 on (a=c+4) set b=d; +disconnect con1; +drop table t1, t2, t0; +create table t1 (a int, b varchar(50), c varchar(50)); +insert t1 (a,b) values (1,'1'), (2,'2'), (3,'3'); +create function f1() returns varchar(50) return 'result'; +create trigger tr before update on t1 for each row set new.c = (select f1()); +create table t2 select a, b from t1; +update t1 join t2 using (a) set t1.b = t2.b; +drop table t1, t2; +drop function f1; +# +# end of 5.5 tests +# diff --git a/mysql-test/r/multi_update_debug.result b/mysql-test/r/multi_update_debug.result new file mode 100644 index 00000000000..799b6821e48 --- /dev/null +++ b/mysql-test/r/multi_update_debug.result @@ -0,0 +1,16 @@ +create table t1 (a int, b int); +create table t2 (c int, d int); +insert t1 values (1,2),(3,4); +insert t2 values (5,6),(7,8); +create table t0 (x int); +insert t0 values (11), (22); +create trigger tr1 before update on t1 for each row insert t0 values (new.b); +set debug_sync='open_tables_after_open_and_process_table WAIT_FOR cont'; +update t1 join t2 on (a=c+4) set b=d; +connect con1, localhost, root; +set debug_sync='mdl_acquire_lock_wait SIGNAL cont'; +lock table t1 write, t0 write; +disconnect con1; +connection default; +drop table t1, t2, t0; +set debug_sync='reset'; diff --git a/mysql-test/r/multi_update_innodb.result b/mysql-test/r/multi_update_innodb.result index 294ebfcebdf..2ec7eb3065e 100644 --- a/mysql-test/r/multi_update_innodb.result +++ b/mysql-test/r/multi_update_innodb.result @@ -67,6 +67,23 @@ SELECT * FROM t2; col_int_key pk_1 pk_2 col_int 1 2 3 4 DROP TABLE t1,t2; +create table t1 (id serial, size int(11)) engine=innodb; +create table t2 (id serial, size int, account_id int) engine=innodb; +create table t3 (id serial, size int, article_id int) engine=innodb; +create table t4 (id serial, file_id int, article_id int) engine=innodb; +insert t1 values(null, 400); +insert t2 values(null, 0, 1), (null, 1, 1); +insert t3 values(null, 100, 1); +insert t4 values(null, 1, 2); +create trigger file_update_article before update on t3 for each row +update t2 set t2.size = new.size where t2.id = new.article_id; +create trigger article_update_account before update on t2 for each row +update t1 set t1.size = t1.size + new.size where t1.id = new.account_id; +update t3 join t4 on t4.file_id =t3.id and t4.article_id=2 set t3.size=t3.size + 2; +drop table t1, t2, t3, t4; +# +# end of 5.5 tests +# # Bug mdev-5970 # Bug#13256831 - ERROR 1032 (HY000): CAN'T FIND RECORD diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 1c75aa66287..20745db928a 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -6974,7 +6974,7 @@ WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 1 PRIMARY t2 ALL b NULL NULL NULL 2 Using where -1 PRIMARY t3 ref d d 5 test.t2.b 2 Using where; Using index +1 PRIMARY t3 ref d d 5 test.t2.b 2 Using index 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away set @tmp_mdev410=@@global.userstat; set global userstat=on; diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result index 993d7f0f003..9b3e4901fe4 100644 --- a/mysql-test/r/subselect_mat.result +++ b/mysql-test/r/subselect_mat.result @@ -2484,6 +2484,95 @@ SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1); 1 drop table t1,t2; drop view v1; +# +# MDEV-19580: function invocation in the left part of IN subquery +# +create table t1 (id int, a varchar(50), b int); +insert into t1 values +(1,'mrs',2), (2,'joe',2), (3,'paul',1), (4,'art',1); +create table t2 (id int, a varchar(50), x int); +insert into t2 values +(1,'grand',1),(2,'average',1),(3,'serf',0); +create table t3 (d1 date, d2 date, t1_id int, t2_id int ); +insert into t3 values +('1972-01-01','1988-12-31',3,1), ('1972-01-01','1988-12-31',4,1), +('1972-01-01','1988-12-31',1,2), ('1972-01-01','1988-12-31',2,3); +create table t4 ( id int, a varchar(50) ); +insert into t4 values +(1,'songwriter'),(2,'song character'); +create function f1(who int, dt date) returns int +deterministic +begin +declare result int; +select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who; +return result; +end$$ +create function f2(who int, dt date) returns int +begin +declare result int; +select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who; +return result; +end$$ +# Deterministic function in left part of IN subquery: semi-join is OK +select * from t1 +left join t4 on t1.b = t4.id +where f1(t1.id, '1980-01-01') in (select id from t2 where x=1); +id a b id a +3 paul 1 1 songwriter +4 art 1 1 songwriter +1 mrs 2 2 song character +explain extended select * from t1 +left join t4 on t1.b = t4.id +where f1(t1.id, '1980-01-01') in (select id from t2 where x=1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 Using where +1 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) left join `test`.`t4` on(`test`.`t4`.`id` = `test`.`t1`.`b`) where `test`.`t2`.`x` = 1 and `f1`(`test`.`t1`.`id`,'1980-01-01') = `test`.`t2`.`id` +# Non-deterministic function in left part of IN subq: semi-join is OK +select * from t1 +left join t4 on t1.b = t4.id +where f2(t1.id, '1980-01-01') in (select id from t2 where x=1); +id a b id a +3 paul 1 1 songwriter +4 art 1 1 songwriter +1 mrs 2 2 song character +explain extended select * from t1 +left join t4 on t1.b = t4.id +where f2(t1.id, '1980-01-01') in (select id from t2 where x=1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 Using where +1 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) left join `test`.`t4` on(`test`.`t4`.`id` = `test`.`t1`.`b`) where `test`.`t2`.`x` = 1 and `f2`(`test`.`t1`.`id`,'1980-01-01') = `test`.`t2`.`id` +select t1.*, t4.*, +(select max(t4.id) from t4 where t4.id=t1.b and sleep(0) = 0) as s +from t1 left join t4 on t1.b = t4.id +where f2(t1.id, '1980-01-01') in (select id from t2 where x=1); +id a b id a s +3 paul 1 1 songwriter 1 +4 art 1 1 songwriter 1 +1 mrs 2 2 song character 2 +explain extended select t1.*, t4.*, +(select max(t4.id) from t4 where t4.id=t1.b and sleep(0) = 0) as s +from t1 left join t4 on t1.b = t4.id +where f2(t1.id, '1980-01-01') in (select id from t2 where x=1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 Using where +1 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +3 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where +2 DEPENDENT SUBQUERY t4 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1276 Field or reference 'test.t1.b' of SELECT #2 was resolved in SELECT #1 +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`a` AS `a`,(select max(`test`.`t4`.`id`) from `test`.`t4` where `test`.`t4`.`id` = `test`.`t1`.`b` and sleep(0) = 0) AS `s` from `test`.`t1` semi join (`test`.`t2`) left join `test`.`t4` on(`test`.`t4`.`id` = `test`.`t1`.`b`) where `test`.`t2`.`x` = 1 and `f2`(`test`.`t1`.`id`,'1980-01-01') = `test`.`t2`.`id` +drop function f1; +drop function f2; +drop table t1,t2,t3,t4; # End of 5.5 tests # # MDEV-7220: Materialization strategy is not used for REPLACE ... SELECT diff --git a/mysql-test/r/subselect_no_exists_to_in.result b/mysql-test/r/subselect_no_exists_to_in.result index 0bd14a535e0..ed1d2e3517d 100644 --- a/mysql-test/r/subselect_no_exists_to_in.result +++ b/mysql-test/r/subselect_no_exists_to_in.result @@ -6974,7 +6974,7 @@ WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 1 PRIMARY t2 ALL b NULL NULL NULL 2 Using where -1 PRIMARY t3 ref d d 5 test.t2.b 2 Using where; Using index +1 PRIMARY t3 ref d d 5 test.t2.b 2 Using index 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away set @tmp_mdev410=@@global.userstat; set global userstat=on; diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index a3b7871bb7e..59e067aed78 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -6968,7 +6968,7 @@ WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 1 PRIMARY t2 ALL b NULL NULL NULL 2 Using where -1 PRIMARY t3 ref d d 5 test.t2.b 2 Using where; Using index +1 PRIMARY t3 ref d d 5 test.t2.b 2 Using index 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away set @tmp_mdev410=@@global.userstat; set global userstat=on; diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result index 9dcc690448f..287b5a3974a 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -6965,7 +6965,7 @@ WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 1 PRIMARY t2 ALL b NULL NULL NULL 2 Using where -1 PRIMARY t3 ref d d 5 test.t2.b 2 Using where; Using index +1 PRIMARY t3 ref d d 5 test.t2.b 2 Using index 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away set @tmp_mdev410=@@global.userstat; set global userstat=on; diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result index 7b3c001f6b3..28815eb8c81 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -6980,7 +6980,7 @@ WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 1 PRIMARY t2 ALL b NULL NULL NULL 2 Using where -1 PRIMARY t3 ref d d 5 test.t2.b 2 Using where; Using index +1 PRIMARY t3 ref d d 5 test.t2.b 2 Using index 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away set @tmp_mdev410=@@global.userstat; set global userstat=on; diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index 55d408c54fc..7ca37a16490 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -6965,7 +6965,7 @@ WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 1 PRIMARY t2 ALL b NULL NULL NULL 2 Using where -1 PRIMARY t3 ref d d 5 test.t2.b 2 Using where; Using index +1 PRIMARY t3 ref d d 5 test.t2.b 2 Using index 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away set @tmp_mdev410=@@global.userstat; set global userstat=on; diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result index f6fba1a7c96..52ab6933cb6 100644 --- a/mysql-test/r/subselect_sj_mat.result +++ b/mysql-test/r/subselect_sj_mat.result @@ -2524,6 +2524,95 @@ SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1); 1 drop table t1,t2; drop view v1; +# +# MDEV-19580: function invocation in the left part of IN subquery +# +create table t1 (id int, a varchar(50), b int); +insert into t1 values +(1,'mrs',2), (2,'joe',2), (3,'paul',1), (4,'art',1); +create table t2 (id int, a varchar(50), x int); +insert into t2 values +(1,'grand',1),(2,'average',1),(3,'serf',0); +create table t3 (d1 date, d2 date, t1_id int, t2_id int ); +insert into t3 values +('1972-01-01','1988-12-31',3,1), ('1972-01-01','1988-12-31',4,1), +('1972-01-01','1988-12-31',1,2), ('1972-01-01','1988-12-31',2,3); +create table t4 ( id int, a varchar(50) ); +insert into t4 values +(1,'songwriter'),(2,'song character'); +create function f1(who int, dt date) returns int +deterministic +begin +declare result int; +select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who; +return result; +end$$ +create function f2(who int, dt date) returns int +begin +declare result int; +select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who; +return result; +end$$ +# Deterministic function in left part of IN subquery: semi-join is OK +select * from t1 +left join t4 on t1.b = t4.id +where f1(t1.id, '1980-01-01') in (select id from t2 where x=1); +id a b id a +3 paul 1 1 songwriter +4 art 1 1 songwriter +1 mrs 2 2 song character +explain extended select * from t1 +left join t4 on t1.b = t4.id +where f1(t1.id, '1980-01-01') in (select id from t2 where x=1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 Using where +1 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) left join `test`.`t4` on(`test`.`t4`.`id` = `test`.`t1`.`b`) where `test`.`t2`.`x` = 1 and `f1`(`test`.`t1`.`id`,'1980-01-01') = `test`.`t2`.`id` +# Non-deterministic function in left part of IN subq: semi-join is OK +select * from t1 +left join t4 on t1.b = t4.id +where f2(t1.id, '1980-01-01') in (select id from t2 where x=1); +id a b id a +3 paul 1 1 songwriter +4 art 1 1 songwriter +1 mrs 2 2 song character +explain extended select * from t1 +left join t4 on t1.b = t4.id +where f2(t1.id, '1980-01-01') in (select id from t2 where x=1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 Using where +1 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) left join `test`.`t4` on(`test`.`t4`.`id` = `test`.`t1`.`b`) where `test`.`t2`.`x` = 1 and `f2`(`test`.`t1`.`id`,'1980-01-01') = `test`.`t2`.`id` +select t1.*, t4.*, +(select max(t4.id) from t4 where t4.id=t1.b and sleep(0) = 0) as s +from t1 left join t4 on t1.b = t4.id +where f2(t1.id, '1980-01-01') in (select id from t2 where x=1); +id a b id a s +3 paul 1 1 songwriter 1 +4 art 1 1 songwriter 1 +1 mrs 2 2 song character 2 +explain extended select t1.*, t4.*, +(select max(t4.id) from t4 where t4.id=t1.b and sleep(0) = 0) as s +from t1 left join t4 on t1.b = t4.id +where f2(t1.id, '1980-01-01') in (select id from t2 where x=1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 Using where +1 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +3 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where +2 DEPENDENT SUBQUERY t4 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1276 Field or reference 'test.t1.b' of SELECT #2 was resolved in SELECT #1 +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`a` AS `a`,(select max(`test`.`t4`.`id`) from `test`.`t4` where `test`.`t4`.`id` = `test`.`t1`.`b` and sleep(0) = 0) AS `s` from `test`.`t1` semi join (`test`.`t2`) left join `test`.`t4` on(`test`.`t4`.`id` = `test`.`t1`.`b`) where `test`.`t2`.`x` = 1 and `f2`(`test`.`t1`.`id`,'1980-01-01') = `test`.`t2`.`id` +drop function f1; +drop function f2; +drop table t1,t2,t3,t4; # End of 5.5 tests # # MDEV-7220: Materialization strategy is not used for REPLACE ... SELECT diff --git a/mysql-test/t/derived.test b/mysql-test/t/derived.test index 6c51f23c51e..d1853cdfcc0 100644 --- a/mysql-test/t/derived.test +++ b/mysql-test/t/derived.test @@ -1,7 +1,4 @@ # Initialize ---disable_warnings -drop table if exists t1,t2,t3; ---enable_warnings set @save_derived_optimizer_switch=@@optimizer_switch; set optimizer_switch='derived_merge=off,derived_with_keys=off'; diff --git a/mysql-test/t/derived_view.test b/mysql-test/t/derived_view.test index 849ada947f3..9f5cee15ade 100644 --- a/mysql-test/t/derived_view.test +++ b/mysql-test/t/derived_view.test @@ -1954,7 +1954,7 @@ set optimizer_switch=@exit_optimizer_switch; set join_cache_level=@exit_join_cache_level; --echo # ---echo # Bug mdev-12812: EXPLAIN for query with many expensive derived +--echo # Bug mdev-18479: EXPLAIN for query with many expensive derived --echo # CREATE TABLE t1 diff --git a/mysql-test/t/join_nested.test b/mysql-test/t/join_nested.test index e60b7827f75..cfb24a63304 100644 --- a/mysql-test/t/join_nested.test +++ b/mysql-test/t/join_nested.test @@ -1380,3 +1380,37 @@ DROP VIEW v1; DROP TABLE t1,t2; set optimizer_search_depth= @tmp_mdev621; + +--echo # +--echo # MDEV-19588: Nested left joins using optimized join cache +--echo # + +set optimizer_switch='optimize_join_buffer_size=on'; + +set @save_join_cache_level= @@join_cache_level; +set join_cache_level=2; + +CREATE TABLE t1 (i1 int, c1 varchar(20), pk int) engine=myisam; + +CREATE TABLE t2 (pk int, c1 varchar(20), i1 int) engine=myisam; +INSERT INTO t2 VALUES (7,'a',-912),(8,'a',5); + +CREATE TABLE t3 (pk int, c1 varchar(20), i1 int) engine=myisam; +INSERT INTO t3 VALUES +(1,'a',-145),(2,'a',6),(3,'a',1),(7,'a',NULL),(8,'a',889),(9,'a',146), +(10,'a',177),(16,'a',-433),(17,'a',NULL),(18,'a',2),(19,'a',3),(20,'a',5), +(21,'a',-484),(22,'a',369),(23,'a',-192),(24,'a',-163),(25,'a',5),(26,'a',NULL); + +let $q= +SELECT t3.* +FROM t3 LEFT JOIN t1 LEFT JOIN t2 ON t1.i1 = t2.i1 ON t3.i1 = t1.i1 +WHERE t2.pk < 13 OR t3.i1 IS NULL; + +eval $q; +eval explain extended $q; + +DROP TABLE t1,t2,t3; + +set join_cache_level= @save_join_cache_level; + +set optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/t/multi_update.test b/mysql-test/t/multi_update.test index 900d0bd4283..b5328f74e70 100644 --- a/mysql-test/t/multi_update.test +++ b/mysql-test/t/multi_update.test @@ -360,7 +360,6 @@ drop table t1, t2; # connect (root,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK); -connection root; create database mysqltest; create table mysqltest.t1 (a int, b int, primary key (a)); create table mysqltest.t2 (a int, b int, primary key (a)); @@ -369,7 +368,6 @@ create user mysqltest_1@localhost; grant select on mysqltest.* to mysqltest_1@localhost; grant update on mysqltest.t1 to mysqltest_1@localhost; connect (user1,localhost,mysqltest_1,,mysqltest,$MASTER_MYPORT,$MASTER_MYSOCK); -connection user1; update t1, t2 set t1.b=1 where t1.a=t2.a; update t1, t2 set t1.b=(select t3.b from t3 where t1.a=t3.a) where t1.a=t2.a; connection root; @@ -425,15 +423,12 @@ insert into t2 values (10), (20), (30); create view v1 as select a as b, a/10 as a from t2; connect (locker,localhost,root,,test); -connection locker; lock table t1 write; connect (changer,localhost,root,,test); -connection changer; send alter table t1 add column c int default 100 after a; connect (updater,localhost,root,,test); -connection updater; # Wait till "alter table t1 ..." of session changer is in work. # = There is one session waiting. let $wait_condition= select count(*)= 1 from information_schema.processlist @@ -1014,4 +1009,35 @@ select * from t2; drop table t1,t2, t3; drop user foo; ---echo end of 5.5 tests +# +# Another test on not-opening tables unnecessary +# +create table t1 (a int, b int); +create table t2 (c int, d int); +insert t1 values (1,2),(3,4); +insert t2 values (5,6),(7,8); +create table t0 (x int); +insert t0 values (11), (22); +create trigger tr1 before update on t2 for each row insert t0 values (new.c); +connect con1, localhost, root; +lock table t0 write; +connection default; +update t1 join t2 on (a=c+4) set b=d; +disconnect con1; +drop table t1, t2, t0; + +# +# MDEV-19521 Update Table Fails with Trigger and Stored Function +# +create table t1 (a int, b varchar(50), c varchar(50)); +insert t1 (a,b) values (1,'1'), (2,'2'), (3,'3'); +create function f1() returns varchar(50) return 'result'; +create trigger tr before update on t1 for each row set new.c = (select f1()); +create table t2 select a, b from t1; +update t1 join t2 using (a) set t1.b = t2.b; +drop table t1, t2; +drop function f1; + +--echo # +--echo # end of 5.5 tests +--echo # diff --git a/mysql-test/t/multi_update_debug.test b/mysql-test/t/multi_update_debug.test new file mode 100644 index 00000000000..2da376e1b87 --- /dev/null +++ b/mysql-test/t/multi_update_debug.test @@ -0,0 +1,27 @@ +# +# test MDL backoff-and-retry during multi-update +# +source include/have_debug_sync.inc; +create table t1 (a int, b int); +create table t2 (c int, d int); +insert t1 values (1,2),(3,4); +insert t2 values (5,6),(7,8); +create table t0 (x int); +insert t0 values (11), (22); +create trigger tr1 before update on t1 for each row insert t0 values (new.b); + +set debug_sync='open_tables_after_open_and_process_table WAIT_FOR cont'; +send update t1 join t2 on (a=c+4) set b=d; + +connect con1, localhost, root; +let $wait_condition= select count(*) from information_schema.processlist where state = ' debug sync point: open_tables_after_open_and_process_table' +source include/wait_condition.inc; +set debug_sync='mdl_acquire_lock_wait SIGNAL cont'; +lock table t1 write, t0 write; +let $wait_condition= select count(*) from information_schema.processlist where state = 'Waiting for table metadata lock' +source include/wait_condition.inc; +disconnect con1; +connection default; +reap; +drop table t1, t2, t0; +set debug_sync='reset'; diff --git a/mysql-test/t/multi_update_innodb.test b/mysql-test/t/multi_update_innodb.test index 2e46ee06d4d..04736482011 100644 --- a/mysql-test/t/multi_update_innodb.test +++ b/mysql-test/t/multi_update_innodb.test @@ -76,6 +76,28 @@ SELECT * FROM t2; DROP TABLE t1,t2; +# +# MDEV-19491 update query stopped working after mariadb upgrade 10.2.23 -> 10.2.24 +# +create table t1 (id serial, size int(11)) engine=innodb; +create table t2 (id serial, size int, account_id int) engine=innodb; +create table t3 (id serial, size int, article_id int) engine=innodb; +create table t4 (id serial, file_id int, article_id int) engine=innodb; +insert t1 values(null, 400); +insert t2 values(null, 0, 1), (null, 1, 1); +insert t3 values(null, 100, 1); +insert t4 values(null, 1, 2); +create trigger file_update_article before update on t3 for each row + update t2 set t2.size = new.size where t2.id = new.article_id; +create trigger article_update_account before update on t2 for each row + update t1 set t1.size = t1.size + new.size where t1.id = new.account_id; +update t3 join t4 on t4.file_id =t3.id and t4.article_id=2 set t3.size=t3.size + 2; +drop table t1, t2, t3, t4; + +--echo # +--echo # end of 5.5 tests +--echo # + --echo --echo # Bug mdev-5970 --echo # Bug#13256831 - ERROR 1032 (HY000): CAN'T FIND RECORD diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test index 4bd8dfdf058..12c32b53aa6 100644 --- a/mysql-test/t/subselect_sj_mat.test +++ b/mysql-test/t/subselect_sj_mat.test @@ -2238,6 +2238,81 @@ explain SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1); SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1); drop table t1,t2; drop view v1; + + +--echo # +--echo # MDEV-19580: function invocation in the left part of IN subquery +--echo # + +create table t1 (id int, a varchar(50), b int); +insert into t1 values +(1,'mrs',2), (2,'joe',2), (3,'paul',1), (4,'art',1); + +create table t2 (id int, a varchar(50), x int); +insert into t2 values +(1,'grand',1),(2,'average',1),(3,'serf',0); + +create table t3 (d1 date, d2 date, t1_id int, t2_id int ); +insert into t3 values +('1972-01-01','1988-12-31',3,1), ('1972-01-01','1988-12-31',4,1), +('1972-01-01','1988-12-31',1,2), ('1972-01-01','1988-12-31',2,3); + +create table t4 ( id int, a varchar(50) ); +insert into t4 values +(1,'songwriter'),(2,'song character'); + +delimiter $$; + +create function f1(who int, dt date) returns int +deterministic +begin + declare result int; + select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who; + return result; +end$$ + +create function f2(who int, dt date) returns int +begin + declare result int; + select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who; + return result; +end$$ + +delimiter ;$$ + +--echo # Deterministic function in left part of IN subquery: semi-join is OK + +let $q1= +select * from t1 + left join t4 on t1.b = t4.id + where f1(t1.id, '1980-01-01') in (select id from t2 where x=1); + +eval $q1; +eval explain extended $q1; + +--echo # Non-deterministic function in left part of IN subq: semi-join is OK + +let $q2= +select * from t1 + left join t4 on t1.b = t4.id + where f2(t1.id, '1980-01-01') in (select id from t2 where x=1); + +eval $q2; +eval explain extended $q2; + +let $q3= +select t1.*, t4.*, + (select max(t4.id) from t4 where t4.id=t1.b and sleep(0) = 0) as s + from t1 left join t4 on t1.b = t4.id + where f2(t1.id, '1980-01-01') in (select id from t2 where x=1); + +eval $q3; +eval explain extended $q3; + +drop function f1; +drop function f2; +drop table t1,t2,t3,t4; + --echo # End of 5.5 tests --echo # --echo # MDEV-7220: Materialization strategy is not used for REPLACE ... SELECT |