[Commits] f04edf4: MDEV-19580 Unrelated JOINs corrupt usage of 'WHERE function() IN (subquery)'

9 Jun 2019

revision-id: f04edf4b68f6b9fd2722dca289a675266ec99a7a (mariadb-5.5.64-13-gf04edf4)
parent(s): cbb90f77cdbf57c02145dc6cd86acf8ebb8a88f0
author: Igor Babaev
committer: Igor Babaev
timestamp: 2019-06-09 10:39:52 -0700
message:

MDEV-19580 Unrelated JOINs corrupt usage of 'WHERE function() IN (subquery)'

Handling of top level conjuncts in WHERE whose used_tables() contained
RAND_TABLE_BIT in the function make_join_select() was incorrect.
As a result if such a conjunct referred to fields non of which belonged
to the last joined table  it was pushed twice. (This could be seen
for a test case from subselect.test whose output was changed after this
patch had been applied. In 10.1 when running EXPLAIN FORMAT=JSON for
the query from this test case we clearly see that one of the conjuncts
is pushed twice.) This fact by itself was not good. Besides, if such a
conjunct was pushed to a table that was the result of materialization
of a semi-join the query could return a wrong result set. In particular
we could watch it for queries with semi-join subqueries whose left parts
used stored functions without "deterministic' specifier.

---
 mysql-test/r/subselect.result             |  2 +-
 mysql-test/r/subselect_mat.result         | 89 +++++++++++++++++++++++++++++++
 mysql-test/r/subselect_no_mat.result      |  2 +-
 mysql-test/r/subselect_no_opts.result     |  2 +-
 mysql-test/r/subselect_no_scache.result   |  2 +-
 mysql-test/r/subselect_no_semijoin.result |  2 +-
 mysql-test/r/subselect_sj_mat.result      | 89 +++++++++++++++++++++++++++++++
 mysql-test/t/subselect_sj_mat.test        | 75 ++++++++++++++++++++++++++
 sql/sql_select.cc                         | 87 +++++++++++++++++++++++++-----
 9 files changed, 331 insertions(+), 19 deletions(-)

diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index cdedc02..2999f4c 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -6939,7 +6939,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 efc348a..0e10800 100644
--- a/mysql-test/r/subselect_mat.result
+++ b/mysql-test/r/subselect_mat.result
@@ -2453,6 +2453,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
 set @subselect_mat_test_optimizer_switch_value=null;
 set @@optimizer_switch='materialization=on,in_to_exists=off,semijoin=off';
diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result
index a729129..5c21e74 100644
--- a/mysql-test/r/subselect_no_mat.result
+++ b/mysql-test/r/subselect_no_mat.result
@@ -6937,7 +6937,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 c41fa1b..8ab1f1b 100644
--- a/mysql-test/r/subselect_no_opts.result
+++ b/mysql-test/r/subselect_no_opts.result
@@ -6934,7 +6934,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 1c18135..f0afa7e 100644
--- a/mysql-test/r/subselect_no_scache.result
+++ b/mysql-test/r/subselect_no_scache.result
@@ -6945,7 +6945,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 89c6712..0d5fbc3 100644
--- a/mysql-test/r/subselect_no_semijoin.result
+++ b/mysql-test/r/subselect_no_semijoin.result
@@ -6934,7 +6934,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 fd9435e..cb4b630 100644
--- a/mysql-test/r/subselect_sj_mat.result
+++ b/mysql-test/r/subselect_sj_mat.result
@@ -2493,4 +2493,93 @@ 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
diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test
index c82c1e7..9c3788a 100644
--- a/mysql-test/t/subselect_sj_mat.test
+++ b/mysql-test/t/subselect_sj_mat.test
@@ -2230,4 +2230,79 @@ 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
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 37f8292..d7ff92a 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -204,7 +204,8 @@ static COND *make_cond_for_table_from_pred(THD *thd, Item *root_cond,
                                            table_map used_table,
                                            int join_tab_idx_arg,
                                            bool exclude_expensive_cond,
-                                           bool retain_ref_cond);
+                                           bool retain_ref_cond,
+                                           bool is_top_and_level);
 
 static Item* part_of_refkey(TABLE *form,Field *field);
 uint find_shortest_key(TABLE *table, const key_map *usable_keys);
@@ -8922,12 +8923,6 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond)
                      RAND_TABLE_BIT;
       }
 
-      /*
-	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 + last_top_base_tab_idx)
-        current_map|= RAND_TABLE_BIT;
       used_tables|=current_map;
 
       if (tab->type == JT_REF && tab->quick &&
@@ -8968,6 +8963,20 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond)
         {
           tmp= make_cond_for_table(thd, cond, used_tables, current_map, i,
                                    FALSE, FALSE);
+          if (tab == join->join_tab + last_top_base_tab_idx)
+          {
+            /*
+              This pushes conjunctive conditions of WHERE condition such that:
+              - their used_tables() contain RAND_TABLE_BIT
+              - the conditions does not refer to any fields
+              (such like rand() > 0.5)
+            */
+            table_map rand_table_bit= (table_map) RAND_TABLE_BIT;
+            COND *rand_cond= make_cond_for_table(thd, cond, used_tables,
+                                                 rand_table_bit, -1,
+                                                 FALSE, FALSE);
+            add_cond_and_fix(thd, &tmp, rand_cond);
+          }
         }
         /* Add conditions added by add_not_null_conds(). */
         if (tab->select_cond)
@@ -9283,8 +9292,24 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond)
             psergey: have put the -1 below. It's bad, will need to fix it.
           */
           COND *tmp_cond= make_cond_for_table(thd, on_expr, used_tables2,
-                                              current_map, /*(tab - first_tab)*/ -1,
-					      FALSE, FALSE);
+                                              current_map,
+                                              /*(tab - first_tab)*/ -1,
+                                              FALSE, FALSE);
+          if (tab == last_tab)
+          {
+            /*
+              This pushes conjunctive conditions of ON expression of an outer
+              join such that:
+              - their used_tables() contain RAND_TABLE_BIT
+              - the conditions does not refer to any fields
+              (such like rand() > 0.5)
+            */
+            table_map rand_table_bit= (table_map) RAND_TABLE_BIT;
+            COND *rand_cond= make_cond_for_table(thd, on_expr, used_tables2,
+                                                 rand_table_bit, -1,
+                                                 FALSE, FALSE);
+            add_cond_and_fix(thd, &tmp_cond, rand_cond);
+          }
           bool is_sjm_lookup_tab= FALSE;
           if (tab->bush_children)
           {
@@ -18824,7 +18849,7 @@ make_cond_for_table(THD *thd, Item *cond, table_map tables,
   return make_cond_for_table_from_pred(thd, cond, cond, tables, used_table,
                                        join_tab_idx_arg,
                                        exclude_expensive_cond,
-                                       retain_ref_cond);
+                                       retain_ref_cond, true);
 }
 
 
@@ -18834,9 +18859,12 @@ make_cond_for_table_from_pred(THD *thd, Item *root_cond, Item *cond,
                               int join_tab_idx_arg,
                               bool exclude_expensive_cond __attribute__
                               ((unused)),
-                              bool retain_ref_cond)
+                              bool retain_ref_cond,
+                              bool is_top_and_level)
 
 {
+  table_map rand_table_bit= (table_map) RAND_TABLE_BIT;
+
   if (used_table && !(cond->used_tables() & used_table))
     return (COND*) 0;				// Already checked
 
@@ -18852,11 +18880,28 @@ make_cond_for_table_from_pred(THD *thd, Item *root_cond, Item *cond,
       Item *item;
       while ((item=li++))
       {
+        /*
+          Special handling of top level conjuncts with RAND_TABLE_BIT:
+          if such a conjunct contains a reference to a field that is not
+          an outer field then it is pushed to the corresponding table by
+          the same rule as all other conjuncts. Otherwise, if the conjunct
+          is used in WHERE is is pushed to the last joined table, if is it
+          is used in ON condition of an outer join it is pushed into the
+          last inner table of the outer join. Such conjuncts are pushed in
+          a call of make_cond_for_table_from_pred() with the
+          parameter 'used_table' equal to PSEUDO_TABLE_BITS.
+        */
+        if (is_top_and_level && used_table == rand_table_bit &&
+            (item->used_tables() & ~OUTER_REF_TABLE_BIT) != rand_table_bit)
+        {
+          /* The conjunct with RAND_TABLE_BIT has been allready pushed */
+          continue;
+        }
 	Item *fix=make_cond_for_table_from_pred(thd, root_cond, item, 
                                                 tables, used_table,
-						join_tab_idx_arg,
+                                                join_tab_idx_arg,
                                                 exclude_expensive_cond,
-                                                retain_ref_cond);
+                                                retain_ref_cond, false);
 	if (fix)
 	  new_cond->argument_list()->push_back(fix);
       }
@@ -18880,6 +18925,13 @@ make_cond_for_table_from_pred(THD *thd, Item *root_cond, Item *cond,
     }
     else
     {						// Or list
+      if (is_top_and_level && used_table == rand_table_bit &&
+          (cond->used_tables() & ~OUTER_REF_TABLE_BIT) != rand_table_bit)
+      {
+        /* This top level formula with RAND_TABLE_BIT has been already pushed */
+        return (COND*) 0;
+      }
+
       Item_cond_or *new_cond=new Item_cond_or;
       if (!new_cond)
 	return (COND*) 0;			// OOM /* purecov: inspected */
@@ -18891,7 +18943,7 @@ make_cond_for_table_from_pred(THD *thd, Item *root_cond, Item *cond,
                                                 tables, 0L,
                                                 join_tab_idx_arg,
                                                 exclude_expensive_cond,
-                                                retain_ref_cond);
+                                                retain_ref_cond, false);
 	if (!fix)
 	  return (COND*) 0;			// Always true
 	new_cond->argument_list()->push_back(fix);
@@ -18908,6 +18960,13 @@ make_cond_for_table_from_pred(THD *thd, Item *root_cond, Item *cond,
     }
   }
 
+  if (is_top_and_level && used_table == rand_table_bit &&
+      (cond->used_tables() & ~OUTER_REF_TABLE_BIT) != rand_table_bit)
+  {
+    /* This top level formula with RAND_TABLE_BIT has been already pushed */
+    return (COND*) 0;
+  }
+
   /*
     Because the following test takes a while and it can be done
     table_count times, we mark each item that we have examined with the result

    

[Commits] f04edf4: MDEV-19580 Unrelated JOINs corrupt usage of 'WHERE function() IN (subquery)'

IgorBabaev