summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/derived_view.result38
-rw-r--r--mysql-test/r/limit_rows_examined.result5
-rw-r--r--mysql-test/r/subselect_mat_cost_bugs.result43
-rw-r--r--mysql-test/r/subselect_sj.result41
-rw-r--r--mysql-test/r/subselect_sj2_mat.result23
-rw-r--r--mysql-test/r/subselect_sj_jcl6.result41
-rw-r--r--mysql-test/t/derived_view.test30
-rw-r--r--mysql-test/t/subselect_mat_cost_bugs.test44
-rw-r--r--mysql-test/t/subselect_sj.test32
-rw-r--r--mysql-test/t/subselect_sj2_mat.test20
-rw-r--r--sql/item_subselect.cc26
-rw-r--r--sql/item_subselect.h8
-rw-r--r--sql/item_sum.h1
-rw-r--r--sql/opt_subselect.cc80
-rw-r--r--sql/opt_sum.cc9
-rw-r--r--sql/rpl_mi.cc6
-rw-r--r--sql/sql_lex.cc1
-rw-r--r--sql/sql_lex.h5
-rw-r--r--sql/sql_select.cc18
19 files changed, 452 insertions, 19 deletions
diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result
index 4ee1a3849f5..cfc60345ced 100644
--- a/mysql-test/r/derived_view.result
+++ b/mysql-test/r/derived_view.result
@@ -2591,5 +2591,43 @@ Handler_read_rnd_deleted 0
Handler_read_rnd_next 27
deallocate prepare stmt1;
drop table t1,t2;
+#
+# Bug mdev-12670: mergeable derived / view with subqueries
+# subject to semi-join optimizations
+# (actually this is a 5.3 bug.)
+#
+create table t1 (a int) engine=myisam;
+insert into t1 values (5),(3),(2),(7),(2),(5),(1);
+create table t2 (b int, index idx(b)) engine=myisam;
+insert into t2 values (2),(3),(2),(1),(3),(4);
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+analyze table t1,t2;
+Table Op Msg_type Msg_text
+test.t1 analyze status OK
+test.t2 analyze status OK
+explain select a from t1 where a in (select b from t2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 7 Using where
+1 PRIMARY t2 ref idx idx 5 test.t1.a 140 Using index; FirstMatch(t1)
+explain select * from (select a from t1 where a in (select b from t2)) t;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 7 Using where
+1 PRIMARY t2 ref idx idx 5 test.t1.a 140 Using index; FirstMatch(t1)
+create view v1 as select a from t1 where a in (select b from t2);
+explain select * from v1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 7 Using where
+1 PRIMARY t2 ref idx idx 5 test.t1.a 140 Using index; FirstMatch(t1)
+drop view v1;
+drop table t1,t2;
set optimizer_switch=@exit_optimizer_switch;
set join_cache_level=@exit_join_cache_level;
diff --git a/mysql-test/r/limit_rows_examined.result b/mysql-test/r/limit_rows_examined.result
index 1f829d545f6..0b3bc196a31 100644
--- a/mysql-test/r/limit_rows_examined.result
+++ b/mysql-test/r/limit_rows_examined.result
@@ -426,7 +426,7 @@ c1
bb
cc
Warnings:
-Warning 1931 Query execution was interrupted. The query examined at least 18 rows, which exceeds LIMIT ROWS EXAMINED (16). The query result may be incomplete.
+Warning 1931 Query execution was interrupted. The query examined at least 17 rows, which exceeds LIMIT ROWS EXAMINED (16). The query result may be incomplete.
select * from v1 LIMIT ROWS EXAMINED 11;
c1
bb
@@ -439,7 +439,8 @@ from (select * from t1
where c1 IN (select * from t2 where c2 > ' ' LIMIT ROWS EXAMINED 0)) as tmp
LIMIT ROWS EXAMINED 11;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where
+1 PRIMARY t1 ALL NULL NULL NULL NULL 4
+1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 2 func 1
3 MATERIALIZED t2 ALL NULL NULL NULL NULL 4 Using where
select *
from (select * from t1
diff --git a/mysql-test/r/subselect_mat_cost_bugs.result b/mysql-test/r/subselect_mat_cost_bugs.result
index 9bfd5bd67b5..dba4d049da3 100644
--- a/mysql-test/r/subselect_mat_cost_bugs.result
+++ b/mysql-test/r/subselect_mat_cost_bugs.result
@@ -379,6 +379,7 @@ drop table t3, t4, t5;
#
# LP BUG#858038 The result of a query with NOT IN subquery depends on the state of the optimizer switch
#
+set @optimizer_switch_save= @@optimizer_switch;
create table t1 (c1 char(2) not null, c2 char(2));
create table t2 (c3 char(2), c4 char(2));
insert into t1 values ('a1', 'b1');
@@ -400,6 +401,7 @@ id select_type table type possible_keys key key_len ref rows Extra
select * from t1 where c1 = 'a2' and (c1, c2) not in (select * from t2);
c1 c2
drop table t1, t2;
+set optimizer_switch= @optimizer_switch_save;
#
# MDEV-12673: cost-based choice between materialization and in-to-exists
#
@@ -442,3 +444,44 @@ id select_type table type possible_keys key key_len ref rows Extra
2 DEPENDENT SUBQUERY t3 const PRIMARY PRIMARY 4 const 1
2 DEPENDENT SUBQUERY t2 index NULL i2 11 NULL 2 Using where; Using index
DROP TABLE t1,t2,t3;
+#
+# MDEV-7599: in-to-exists chosen after min/max optimization
+#
+set @optimizer_switch_save= @@optimizer_switch;
+CREATE TABLE t1 (a INT, KEY(a)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (b INT, c INT) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1,6),(2,4), (8,9);
+SELECT * FROM t2 WHERE b != ALL (SELECT MIN(a) FROM t1, t2 WHERE t2.c = t2.b);
+b c
+EXPLAIN EXTENDED SELECT * FROM t2 WHERE b != ALL (SELECT MIN(a) FROM t1, t2 WHERE t2.c = t2.b);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00 Using where
+2 MATERIALIZED t1 index NULL a 5 NULL 2 100.00 Using index
+2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join)
+Warnings:
+Note 1003 select `test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where (not(<expr_cache><`test`.`t2`.`b`>(<in_optimizer>(`test`.`t2`.`b`,`test`.`t2`.`b` in ( <materialize> (select min(`test`.`t1`.`a`) from `test`.`t1` join `test`.`t2` where (`test`.`t2`.`c` = `test`.`t2`.`b`) ), <primary_index_lookup>(`test`.`t2`.`b` in <temporary table> on distinct_key where ((`test`.`t2`.`b` = `<subquery2>`.`MIN(a)`))))))))
+set optimizer_switch= 'materialization=off';
+SELECT * FROM t2 WHERE b != ALL (SELECT MIN(a) FROM t1, t2 WHERE t2.c = t2.b);
+b c
+EXPLAIN EXTENDED SELECT * FROM t2 WHERE b != ALL (SELECT MIN(a) FROM t1, t2 WHERE t2.c = t2.b);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00 Using where
+2 DEPENDENT SUBQUERY t1 index NULL a 5 NULL 2 100.00 Using index
+2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join)
+Warnings:
+Note 1003 select `test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where (not(<expr_cache><`test`.`t2`.`b`>(<in_optimizer>(`test`.`t2`.`b`,<exists>(select min(`test`.`t1`.`a`) from `test`.`t1` join `test`.`t2` where (`test`.`t2`.`c` = `test`.`t2`.`b`) having trigcond((<cache>(`test`.`t2`.`b`) = <ref_null_helper>(min(`test`.`t1`.`a`)))))))))
+set optimizer_switch= @optimizer_switch_save;
+DROP TABLE t1,t2;
+CREATE TABLE t1 (f1 varchar(10)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('foo'),('bar');
+CREATE TABLE t2 (f2 varchar(10), key(f2)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES ('baz'),('qux');
+CREATE TABLE t3 (f3 varchar(10)) ENGINE=MyISAM;
+INSERT INTO t3 VALUES ('abc'),('def');
+SELECT * FROM t1
+WHERE f1 = ALL( SELECT MAX(t2a.f2)
+FROM t2 AS t2a INNER JOIN t2 t2b INNER JOIN t3
+ON (f3 = t2b.f2) );
+f1
+DROP TABLE t1,t2,t3;
diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result
index 325578b27fd..51956da1ae3 100644
--- a/mysql-test/r/subselect_sj.result
+++ b/mysql-test/r/subselect_sj.result
@@ -1652,9 +1652,9 @@ CREATE VIEW v1 AS SELECT 1;
EXPLAIN
SELECT * FROM t1 INNER JOIN t2 ON t2.a != 0 AND t2.a IN (SELECT * FROM v1);
id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY <derived3> system NULL NULL NULL NULL 1
1 PRIMARY t1 ALL NULL NULL NULL NULL 2
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join)
-2 MATERIALIZED <derived3> system NULL NULL NULL NULL 1
3 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used
SELECT * FROM t1 INNER JOIN t2 ON t2.a != 0 AND t2.a IN (SELECT * FROM v1);
a a
@@ -3062,4 +3062,43 @@ project_number
aaa
drop table t1, t2, t3;
set optimizer_switch= @tmp_mdev6859;
+#
+# MDEV-12675: subquery subject to semi-join optimizations
+# in ON expression of INNER JOIN
+#
+set @tmp_mdev12675=@@optimizer_switch;
+set optimizer_switch=default;
+create table t1 (a int) engine=myisam;
+insert into t1 values (5),(3),(2),(7),(2),(5),(1);
+create table t2 (b int, index idx(b)) engine=myisam;
+insert into t2 values (2),(3),(2),(1),(3),(4);
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+analyze table t1,t2;
+Table Op Msg_type Msg_text
+test.t1 analyze status OK
+test.t2 analyze status OK
+explain
+select a from t1, t2 where b between 1 and 2 and a in (select b from t2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 7 Using where
+1 PRIMARY t2 ref idx idx 5 test.t1.a 256 Using index; FirstMatch(t1)
+1 PRIMARY t2 range idx idx 5 NULL 2 Using where; Using index; Using join buffer (flat, BNL join)
+explain
+select a from t1 join t2 on b between 1 and 2 and a in (select b from t2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 7 Using where
+1 PRIMARY t2 ref idx idx 5 test.t1.a 256 Using index; FirstMatch(t1)
+1 PRIMARY t2 range idx idx 5 NULL 2 Using where; Using index; Using join buffer (flat, BNL join)
+drop table t1,t2;
+set optimizer_switch= @tmp_mdev12675;
set optimizer_switch=@subselect_sj_tmp;
diff --git a/mysql-test/r/subselect_sj2_mat.result b/mysql-test/r/subselect_sj2_mat.result
index 08a97175487..c00ce7ec227 100644
--- a/mysql-test/r/subselect_sj2_mat.result
+++ b/mysql-test/r/subselect_sj2_mat.result
@@ -1589,3 +1589,26 @@ i1
DROP TABLE t1,t2,t3;
set join_cache_level= @save_join_cache_level;
set optimizer_switch=@save_optimizer_switch;
+#
+# mdev-7791: materialization of a semi-join subquery +
+# RAND() in WHERE
+# (materialized table is accessed last)
+#
+set @save_optimizer_switch=@@optimizer_switch;
+set optimizer_switch='materialization=on';
+create table t1(i int);
+insert into t1 values (1), (2), (3), (7), (9), (10);
+create table t2(i int);
+insert into t2 values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+select * from t1 where (rand() < 0) and i in (select i from t2);
+i
+explain extended
+select * from t1 where (rand() < 0) and i in (select i from t2);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00
+2 MATERIALIZED t2 ALL NULL NULL NULL NULL 10 100.00
+Warnings:
+Note 1003 select `test`.`t1`.`i` AS `i` from `test`.`t1` semi join (`test`.`t2`) where ((rand() < 0))
+drop table t1,t2;
+set optimizer_switch=@save_optimizer_switch;
diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result
index 595252daafe..e959753ee42 100644
--- a/mysql-test/r/subselect_sj_jcl6.result
+++ b/mysql-test/r/subselect_sj_jcl6.result
@@ -1665,9 +1665,9 @@ CREATE VIEW v1 AS SELECT 1;
EXPLAIN
SELECT * FROM t1 INNER JOIN t2 ON t2.a != 0 AND t2.a IN (SELECT * FROM v1);
id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY <derived3> system NULL NULL NULL NULL 1
1 PRIMARY t1 ALL NULL NULL NULL NULL 2
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join)
-2 MATERIALIZED <derived3> system NULL NULL NULL NULL 1
3 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used
SELECT * FROM t1 INNER JOIN t2 ON t2.a != 0 AND t2.a IN (SELECT * FROM v1);
a a
@@ -3076,6 +3076,45 @@ project_number
aaa
drop table t1, t2, t3;
set optimizer_switch= @tmp_mdev6859;
+#
+# MDEV-12675: subquery subject to semi-join optimizations
+# in ON expression of INNER JOIN
+#
+set @tmp_mdev12675=@@optimizer_switch;
+set optimizer_switch=default;
+create table t1 (a int) engine=myisam;
+insert into t1 values (5),(3),(2),(7),(2),(5),(1);
+create table t2 (b int, index idx(b)) engine=myisam;
+insert into t2 values (2),(3),(2),(1),(3),(4);
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+analyze table t1,t2;
+Table Op Msg_type Msg_text
+test.t1 analyze status OK
+test.t2 analyze status OK
+explain
+select a from t1, t2 where b between 1 and 2 and a in (select b from t2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 7 Using where
+1 PRIMARY t2 ref idx idx 5 test.t1.a 256 Using index; FirstMatch(t1)
+1 PRIMARY t2 range idx idx 5 NULL 2 Using where; Using index; Using join buffer (flat, BNL join)
+explain
+select a from t1 join t2 on b between 1 and 2 and a in (select b from t2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 7 Using where
+1 PRIMARY t2 ref idx idx 5 test.t1.a 256 Using index; FirstMatch(t1)
+1 PRIMARY t2 range idx idx 5 NULL 2 Using where; Using index; Using join buffer (flat, BNL join)
+drop table t1,t2;
+set optimizer_switch= @tmp_mdev12675;
set optimizer_switch=@subselect_sj_tmp;
#
# BUG#49129: Wrong result with IN-subquery with join_cache_level=6 and firstmatch=off
diff --git a/mysql-test/t/derived_view.test b/mysql-test/t/derived_view.test
index d017f847af9..cdddaf8f9d8 100644
--- a/mysql-test/t/derived_view.test
+++ b/mysql-test/t/derived_view.test
@@ -1881,6 +1881,36 @@ deallocate prepare stmt1;
drop table t1,t2;
+--echo #
+--echo # Bug mdev-12670: mergeable derived / view with subqueries
+--echo # subject to semi-join optimizations
+--echo # (actually this is a 5.3 bug.)
+--echo #
+
+create table t1 (a int) engine=myisam;
+insert into t1 values (5),(3),(2),(7),(2),(5),(1);
+create table t2 (b int, index idx(b)) engine=myisam;
+insert into t2 values (2),(3),(2),(1),(3),(4);
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+analyze table t1,t2;
+
+explain select a from t1 where a in (select b from t2);
+explain select * from (select a from t1 where a in (select b from t2)) t;
+create view v1 as select a from t1 where a in (select b from t2);
+explain select * from v1;
+
+drop view v1;
+drop table t1,t2;
+
# The following command must be the last one the file
set optimizer_switch=@exit_optimizer_switch;
set join_cache_level=@exit_join_cache_level;
diff --git a/mysql-test/t/subselect_mat_cost_bugs.test b/mysql-test/t/subselect_mat_cost_bugs.test
index 316ac707bef..9e3ac603ec6 100644
--- a/mysql-test/t/subselect_mat_cost_bugs.test
+++ b/mysql-test/t/subselect_mat_cost_bugs.test
@@ -406,6 +406,8 @@ drop table t3, t4, t5;
--echo # LP BUG#858038 The result of a query with NOT IN subquery depends on the state of the optimizer switch
--echo #
+set @optimizer_switch_save= @@optimizer_switch;
+
create table t1 (c1 char(2) not null, c2 char(2));
create table t2 (c3 char(2), c4 char(2));
@@ -425,6 +427,8 @@ select * from t1 where c1 = 'a2' and (c1, c2) not in (select * from t2);
drop table t1, t2;
+set optimizer_switch= @optimizer_switch_save;
+
--echo #
--echo # MDEV-12673: cost-based choice between materialization and in-to-exists
--echo #
@@ -463,3 +467,43 @@ SELECT * FROM t1 WHERE i1 NOT IN (
);
DROP TABLE t1,t2,t3;
+
+--echo #
+--echo # MDEV-7599: in-to-exists chosen after min/max optimization
+--echo #
+
+set @optimizer_switch_save= @@optimizer_switch;
+
+CREATE TABLE t1 (a INT, KEY(a)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1),(2);
+
+CREATE TABLE t2 (b INT, c INT) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1,6),(2,4), (8,9);
+
+let $q=
+SELECT * FROM t2 WHERE b != ALL (SELECT MIN(a) FROM t1, t2 WHERE t2.c = t2.b);
+
+eval $q;
+eval EXPLAIN EXTENDED $q;
+set optimizer_switch= 'materialization=off';
+eval $q;
+eval EXPLAIN EXTENDED $q;
+set optimizer_switch= @optimizer_switch_save;
+
+DROP TABLE t1,t2;
+
+CREATE TABLE t1 (f1 varchar(10)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('foo'),('bar');
+
+CREATE TABLE t2 (f2 varchar(10), key(f2)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES ('baz'),('qux');
+
+CREATE TABLE t3 (f3 varchar(10)) ENGINE=MyISAM;
+INSERT INTO t3 VALUES ('abc'),('def');
+
+SELECT * FROM t1
+ WHERE f1 = ALL( SELECT MAX(t2a.f2)
+ FROM t2 AS t2a INNER JOIN t2 t2b INNER JOIN t3
+ ON (f3 = t2b.f2) );
+
+DROP TABLE t1,t2,t3;
diff --git a/mysql-test/t/subselect_sj.test b/mysql-test/t/subselect_sj.test
index b26d5a71e46..52f13a970d2 100644
--- a/mysql-test/t/subselect_sj.test
+++ b/mysql-test/t/subselect_sj.test
@@ -2773,5 +2773,37 @@ WHERE ( SELECT z.country
drop table t1, t2, t3;
set optimizer_switch= @tmp_mdev6859;
+--echo #
+--echo # MDEV-12675: subquery subject to semi-join optimizations
+--echo # in ON expression of INNER JOIN
+--echo #
+
+set @tmp_mdev12675=@@optimizer_switch;
+set optimizer_switch=default;
+create table t1 (a int) engine=myisam;
+insert into t1 values (5),(3),(2),(7),(2),(5),(1);
+create table t2 (b int, index idx(b)) engine=myisam;
+insert into t2 values (2),(3),(2),(1),(3),(4);
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+insert into t2 select b+10 from t2;
+analyze table t1,t2;
+
+explain
+select a from t1, t2 where b between 1 and 2 and a in (select b from t2);
+explain
+select a from t1 join t2 on b between 1 and 2 and a in (select b from t2);
+
+drop table t1,t2;
+set optimizer_switch= @tmp_mdev12675;
+
# The following command must be the last one the file
set optimizer_switch=@subselect_sj_tmp;
diff --git a/mysql-test/t/subselect_sj2_mat.test b/mysql-test/t/subselect_sj2_mat.test
index 61d9b09edff..0f2892ae2dc 100644
--- a/mysql-test/t/subselect_sj2_mat.test
+++ b/mysql-test/t/subselect_sj2_mat.test
@@ -263,3 +263,23 @@ DROP TABLE t1,t2,t3;
set join_cache_level= @save_join_cache_level;
set optimizer_switch=@save_optimizer_switch;
+--echo #
+--echo # mdev-7791: materialization of a semi-join subquery +
+--echo # RAND() in WHERE
+--echo # (materialized table is accessed last)
+--echo #
+
+set @save_optimizer_switch=@@optimizer_switch;
+set optimizer_switch='materialization=on';
+
+create table t1(i int);
+insert into t1 values (1), (2), (3), (7), (9), (10);
+create table t2(i int);
+insert into t2 values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+
+select * from t1 where (rand() < 0) and i in (select i from t2);
+explain extended
+select * from t1 where (rand() < 0) and i in (select i from t2);
+
+drop table t1,t2;
+set optimizer_switch=@save_optimizer_switch;
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index e87db62bd98..70b730a5a33 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -1381,8 +1381,9 @@ Item_in_subselect::Item_in_subselect(Item * left_exp,
st_select_lex *select_lex):
Item_exists_subselect(), left_expr_cache(0), first_execution(TRUE),
in_strategy(SUBS_NOT_TRANSFORMED),
- pushed_cond_guards(NULL), is_jtbm_merged(FALSE), is_jtbm_const_tab(FALSE),
- is_flattenable_semijoin(FALSE), is_registered_semijoin(FALSE),
+ pushed_cond_guards(NULL), do_not_convert_to_sj(FALSE), is_jtbm_merged(FALSE),
+ is_jtbm_const_tab(FALSE), is_flattenable_semijoin(FALSE),
+ is_registered_semijoin(FALSE),
upper_item(0)
{
DBUG_ENTER("Item_in_subselect::Item_in_subselect");
@@ -2512,6 +2513,27 @@ bool Item_in_subselect::inject_in_to_exists_cond(JOIN *join_arg)
DBUG_ENTER("Item_in_subselect::inject_in_to_exists_cond");
DBUG_ASSERT(thd == join_arg->thd);
+ if (select_lex->min_max_opt_list.elements)
+ {
+ /*
+ MIN/MAX optimizations have been applied to Item_sum objects
+ of the subquery this subquery predicate in opt_sum_query().
+ Injection of new condition invalidates this optimizations.
+ Thus those optimizations must be rolled back.
+ */
+ List_iterator_fast<Item_sum> it(select_lex->min_max_opt_list);
+ Item_sum *item;
+ while ((item= it++))
+ {
+ item->clear();
+ item->reset_forced_const();
+ }
+ if (where_item)
+ where_item->update_used_tables();
+ if (having_item)
+ having_item->update_used_tables();
+ }
+
if (where_item)
{
List<Item> *and_args= NULL;
diff --git a/sql/item_subselect.h b/sql/item_subselect.h
index 492a3d7dda5..ad43215e437 100644
--- a/sql/item_subselect.h
+++ b/sql/item_subselect.h
@@ -490,6 +490,8 @@ public:
Item *left_expr_orig;
/* Priority of this predicate in the convert-to-semi-join-nest process. */
int sj_convert_priority;
+ /* May be TRUE only for the candidates to semi-join conversion */
+ bool do_not_convert_to_sj;
/*
Types of left_expr and subquery's select list allow to perform subquery
materialization. Currently, we set this to FALSE when it as well could
@@ -580,8 +582,8 @@ public:
Item_in_subselect()
:Item_exists_subselect(), left_expr_cache(0), first_execution(TRUE),
in_strategy(SUBS_NOT_TRANSFORMED),
- pushed_cond_guards(NULL), func(NULL), is_jtbm_merged(FALSE),
- is_jtbm_const_tab(FALSE), upper_item(0) {}
+ pushed_cond_guards(NULL), func(NULL), do_not_convert_to_sj(FALSE),
+ is_jtbm_merged(FALSE), is_jtbm_const_tab(FALSE), upper_item(0) {}
void cleanup();
subs_type substype() { return IN_SUBS; }
void reset()
@@ -635,6 +637,8 @@ public:
*/
int get_identifier();
+ void block_conversion_to_sj () { do_not_convert_to_sj= TRUE; }
+
bool test_strategy(uchar strategy)
{ return MY_TEST(in_strategy & strategy); }
diff --git a/sql/item_sum.h b/sql/item_sum.h
index 09a20487226..f4be2108e1b 100644
--- a/sql/item_sum.h
+++ b/sql/item_sum.h
@@ -454,6 +454,7 @@ public:
used_tables_cache= 0;
forced_const= TRUE;
}
+ void reset_forced_const() { forced_const= FALSE; }
virtual bool const_item() const { return forced_const; }
virtual bool const_during_execution() const { return false; }
virtual void print(String *str, enum_query_type query_type);
diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc
index 564a108c766..46199c06a59 100644
--- a/sql/opt_subselect.cc
+++ b/sql/opt_subselect.cc
@@ -1001,6 +1001,25 @@ bool check_for_outer_joins(List<TABLE_LIST> *join_list)
}
+void find_and_block_conversion_to_sj(Item *to_find,
+ List_iterator_fast<Item_in_subselect> &li)
+{
+ if (to_find->type() != Item::SUBSELECT_ITEM ||
+ ((Item_subselect *) to_find)->substype() != Item_subselect::IN_SUBS)
+ return;
+ Item_in_subselect *in_subq;
+ li.rewind();
+ while ((in_subq= li++))
+ {
+ if (in_subq == to_find)
+ {
+ in_subq->block_conversion_to_sj();
+ return;
+ }
+ }
+}
+
+
/*
Convert semi-join subquery predicates into semi-join join nests
@@ -1053,7 +1072,6 @@ bool convert_join_subqueries_to_semijoins(JOIN *join)
Query_arena *arena, backup;
Item_in_subselect *in_subq;
THD *thd= join->thd;
- List_iterator<TABLE_LIST> ti(join->select_lex->leaf_tables);
DBUG_ENTER("convert_join_subqueries_to_semijoins");
if (join->select_lex->sj_subselects.is_empty())
@@ -1071,6 +1089,60 @@ bool convert_join_subqueries_to_semijoins(JOIN *join)
subq_sel->update_used_tables();
}
+ /*
+ Check all candidates to semi-join conversion that occur
+ in ON expressions of outer join. Set the flag blocking
+ this conversion for them.
+ */
+ TABLE_LIST *tbl;
+ List_iterator<TABLE_LIST> ti(join->select_lex->leaf_tables);
+ while ((tbl= ti++))
+ {
+ TABLE_LIST *embedded;
+ TABLE_LIST *embedding= tbl;
+ do
+ {
+ embedded= embedding;
+ if (MY_TEST(embedded->outer_join))
+ {
+ Item *cond= embedded->on_expr;
+ if (!cond)
+ ;
+ else if (cond->type() != Item::COND_ITEM)
+ find_and_block_conversion_to_sj(cond, li);
+ else if (((Item_cond*) cond)->functype() ==
+ Item_func::COND_AND_FUNC)
+ {
+ Item *item;
+ List_iterator<Item> it(*(((Item_cond*) cond)->argument_list()));
+ while ((item= it++))
+ {
+ find_and_block_conversion_to_sj(item, li);
+ }
+ }
+ }
+ embedding= embedded->embedding;
+ }
+ while (embedding &&
+ embedding->nested_join->join_list.head() == embedded);
+ }
+
+ /*
+ Block conversion to semi-joins for those candidates that
+ are encountered in the WHERE condition of the multi-table view
+ with CHECK OPTION if this view is used in UPDATE/DELETE.
+ (This limitation can be, probably, easily lifted.)
+ */
+ li.rewind();
+ while ((in_subq= li++))
+ {
+ if (in_subq->emb_on_expr_nest != NO_JOIN_NEST &&
+ in_subq->emb_on_expr_nest->effective_with_check)
+ {
+ in_subq->block_conversion_to_sj();
+ }
+ }
+
li.rewind();
/* First, convert child join's subqueries. We proceed bottom-up here */
while ((in_subq= li++))
@@ -1089,8 +1161,10 @@ bool convert_join_subqueries_to_semijoins(JOIN *join)
if (convert_join_subqueries_to_semijoins(child_join))
DBUG_RETURN(TRUE);
+
+
in_subq->sj_convert_priority=
- MY_TEST(in_subq->emb_on_expr_nest != NO_JOIN_NEST) * MAX_TABLES * 2 +
+ MY_TEST(in_subq->do_not_convert_to_sj) * MAX_TABLES * 2 +
in_subq->is_correlated * MAX_TABLES + child_join->outer_tables;
}
@@ -1123,7 +1197,7 @@ bool convert_join_subqueries_to_semijoins(JOIN *join)
bool remove_item= TRUE;
/* Stop processing if we've reached a subquery that's attached to the ON clause */
- if (in_subq->emb_on_expr_nest != NO_JOIN_NEST)
+ if (in_subq->do_not_convert_to_sj)
break;
if (in_subq->is_flattenable_semijoin)
diff --git a/sql/opt_sum.cc b/sql/opt_sum.cc
index 1ff1f4a6449..5a5e17dda6d 100644
--- a/sql/opt_sum.cc
+++ b/sql/opt_sum.cc
@@ -254,6 +254,8 @@ int opt_sum_query(THD *thd,
int error= 0;
DBUG_ENTER("opt_sum_query");
+ thd->lex->current_select->min_max_opt_list.empty();
+
if (conds)
where_tables= conds->used_tables();
@@ -447,7 +449,14 @@ int opt_sum_query(THD *thd,
item_sum->aggregator_clear();
}
else
+ {
item_sum->reset_and_add();
+ /*
+ Save a reference to the item for possible rollback
+ of the min/max optimizations for this select
+ */
+ thd->lex->current_select->min_max_opt_list.push_back(item_sum);
+ }
item_sum->make_const();
recalc_const_item= 1;
break;
diff --git a/sql/rpl_mi.cc b/sql/rpl_mi.cc
index bff0abe8198..bdc88625c0d 100644
--- a/sql/rpl_mi.cc
+++ b/sql/rpl_mi.cc
@@ -558,7 +558,7 @@ file '%s')", fname);
mi->connect_retry= (uint) connect_retry;
mi->ssl= (my_bool) ssl;
mi->ssl_verify_server_cert= ssl_verify_server_cert;
- mi->heartbeat_period= master_heartbeat_period;
+ mi->heartbeat_period= MY_MIN(SLAVE_MAX_HEARTBEAT_PERIOD, master_heartbeat_period);
}
DBUG_PRINT("master_info",("log_file_name: %s position: %ld",
mi->master_log_name,
@@ -675,8 +675,8 @@ int flush_master_info(Master_info* mi,
contents of file). But because of number of lines in the first line
of file we don't care about this garbage.
*/
- char heartbeat_buf[sizeof(mi->heartbeat_period) * 4]; // buffer to suffice always
- sprintf(heartbeat_buf, "%.3f", mi->heartbeat_period);
+ char heartbeat_buf[FLOATING_POINT_BUFFER];
+ my_fcvt(mi->heartbeat_period, 3, heartbeat_buf, NULL);
my_b_seek(file, 0L);
my_b_printf(file,
"%u\n%s\n%s\n%s\n%s\n%s\n%d\n%d\n%d\n%s\n%s\n%s\n%s\n%s\n%d\n%s\n%s\n%s\n%s\n%d\n%s\n%s\n"
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index de450b8ede8..9351b7f61a9 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -1865,6 +1865,7 @@ void st_select_lex::init_query()
leaf_tables_prep.empty();
leaf_tables.empty();
item_list.empty();
+ min_max_opt_list.empty();
join= 0;
having= prep_having= where= prep_where= 0;
olap= UNSPECIFIED_OLAP_TYPE;
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index c4fd109009f..d28546edf10 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -704,6 +704,11 @@ public:
*/
List<Item_func_match> *ftfunc_list;
List<Item_func_match> ftfunc_list_alloc;
+ /*
+ The list of items to which MIN/MAX optimizations of opt_sum_query()
+ have been applied. Used to rollback those optimizations if it's needed.
+ */
+ List<Item_sum> min_max_opt_list;
JOIN *join; /* after JOIN::prepare it is pointer to corresponding JOIN */
List<TABLE_LIST> top_join_list; /* join list of the top level */
List<TABLE_LIST> *join_list; /* list for the currently parsed join */
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 20456931136..6a281a4583e 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -9487,12 +9487,20 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond)
/*
Step #2: Extract WHERE/ON parts
*/
+ uint i;
+ for (i= join->top_join_tab_count - 1; i >= join->const_tables; i--)
+ {
+ if (!join->join_tab[i].bush_children)
+ break;
+ }
+ uint last_top_base_tab_idx= i;
+
table_map save_used_tables= 0;
used_tables=((select->const_tables=join->const_table_map) |
OUTER_REF_TABLE_BIT | RAND_TABLE_BIT);
JOIN_TAB *tab;
table_map current_map;
- uint i= join->const_tables;
+ i= join->const_tables;
for (tab= first_depth_first_tab(join); tab;
tab= next_depth_first_tab(join, tab), i++)
{
@@ -9530,7 +9538,7 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond)
Following force including random expression in last table condition.
It solve problem with select like SELECT * FROM t1 WHERE rand() > 0.5
*/
- if (tab == join->join_tab + join->top_join_tab_count - 1)
+ if (tab == join->join_tab + last_top_base_tab_idx)
current_map|= RAND_TABLE_BIT;
used_tables|=current_map;
@@ -9569,10 +9577,10 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond)
save_used_tables= 0;
}
else
- {
- tmp= make_cond_for_table(thd, cond, used_tables, current_map, i,
+ {
+ tmp= make_cond_for_table(thd, cond, used_tables, current_map, i,
FALSE, FALSE);
- }
+ }
/* Add conditions added by add_not_null_conds(). */
if (tab->select_cond)
add_cond_and_fix(thd, &tmp, tab->select_cond);
View Lorry Controller Status