revision-id: a6029989cc289f80eed5da707815838c6848d24b (mariadb-5.5.64-13-ga602998) parent(s): cbb90f77cdbf57c02145dc6cd86acf8ebb8a88f0 author: Igor Babaev committer: Igor Babaev timestamp: 2019-06-01 13:07:09 -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 | 68 ++++++++++++++++++++++++ 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 | 68 ++++++++++++++++++++++++ mysql-test/t/subselect_sj_mat.test | 66 ++++++++++++++++++++++++ sql/sql_select.cc | 86 ++++++++++++++++++++++++++----- 9 files changed, 279 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..ae24485 100644 --- a/mysql-test/r/subselect_mat.result +++ b/mysql-test/r/subselect_mat.result @@ -2453,6 +2453,74 @@ 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`)) +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..9dde115 100644 --- a/mysql-test/r/subselect_sj_mat.result +++ b/mysql-test/r/subselect_sj_mat.result @@ -2493,4 +2493,72 @@ 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`)) +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..997e71a 100644 --- a/mysql-test/t/subselect_sj_mat.test +++ b/mysql-test/t/subselect_sj_mat.test @@ -2230,4 +2230,70 @@ 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; + +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..bc69817 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,27 @@ 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 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 RAND_TABLE_BIT. + */ + if (is_top_and_level && used_table == rand_table_bit && + item->used_tables() != 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 +18924,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() != 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 +18942,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 +18959,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() != 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