revision-id: e71c5614080f5cf6db2a581837f20dc31d7ddf6a (mariadb-10.2.22-19-ge71c5614080) parent(s): 80c3fd184d4eeb66cd520079c3d23595e52cfdc0 author: Galina Shalygina committer: Galina Shalygina timestamp: 2019-02-23 22:16:33 +0300 message: MDEV-18383: Missing rows with pushdown condition defined with IF-function using Item_cond This bug is similar with MDEV-16765. It appears because of the wrong pushdown into HAVING clause while this pushdown shouldn't be made at all. This happens because function that checks if Item_cond can be pushed always returns that it can be pushed. To fix it new method Item_cond::excl_dep_on_table() was added. --- mysql-test/r/derived_cond_pushdown.result | 21 +++++++++++++++++++++ mysql-test/t/derived_cond_pushdown.test | 22 ++++++++++++++++++++++ sql/item_cmpfunc.cc | 17 +++++++++++++++++ sql/item_cmpfunc.h | 1 + 4 files changed, 61 insertions(+) diff --git a/mysql-test/r/derived_cond_pushdown.result b/mysql-test/r/derived_cond_pushdown.result index 14c8e4d5e8f..00acdf241a6 100644 --- a/mysql-test/r/derived_cond_pushdown.result +++ b/mysql-test/r/derived_cond_pushdown.result @@ -10480,4 +10480,25 @@ EXPLAIN } DROP VIEW v1,v2; DROP TABLE t1; +# +# MDEV-18383: pushdown condition with the IF structure +# defined with Item_cond item +# +CREATE TABLE t1(a INT, b INT); +CREATE TABLE t2(c INT, d INT); +INSERT INTO t1 VALUES (1,2),(3,4),(5,6); +INSERT INTO t2 VALUES (1,3),(3,7),(5,1); +SELECT * +FROM t1, +( +SELECT MAX(d) AS max_d,c +FROM t2 +GROUP BY c +) AS tab +WHERE t1.a=tab.c AND +IF(2,t1.a=1 OR t1.b>5,1=1); +a b max_d c +1 2 3 1 +5 6 1 5 +DROP TABLE t1,t2; # End of 10.2 tests diff --git a/mysql-test/t/derived_cond_pushdown.test b/mysql-test/t/derived_cond_pushdown.test index 25cb29e13db..313d77d2332 100644 --- a/mysql-test/t/derived_cond_pushdown.test +++ b/mysql-test/t/derived_cond_pushdown.test @@ -2102,4 +2102,26 @@ eval EXPLAIN FORMAT=JSON $q2; DROP VIEW v1,v2; DROP TABLE t1; +--echo # +--echo # MDEV-18383: pushdown condition with the IF structure +--echo # defined with Item_cond item +--echo # + +CREATE TABLE t1(a INT, b INT); +CREATE TABLE t2(c INT, d INT); +INSERT INTO t1 VALUES (1,2),(3,4),(5,6); +INSERT INTO t2 VALUES (1,3),(3,7),(5,1); + +SELECT * +FROM t1, +( + SELECT MAX(d) AS max_d,c + FROM t2 + GROUP BY c +) AS tab +WHERE t1.a=tab.c AND + IF(2,t1.a=1 OR t1.b>5,1=1); + +DROP TABLE t1,t2; + --echo # End of 10.2 tests diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index 7357e57733a..badb0191471 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -4994,6 +4994,23 @@ Item *Item_cond::build_clone(THD *thd, MEM_ROOT *mem_root) } +bool Item_cond::excl_dep_on_table(table_map tab_map) +{ + if (used_tables() & OUTER_REF_TABLE_BIT) + return false; + if (!(used_tables() & ~tab_map)) + return true; + List_iterator_fast<Item> li(list); + Item *item; + while ((item= li++)) + { + if (!item->excl_dep_on_table(tab_map)) + return false; + } + return true; +} + + bool Item_cond::excl_dep_on_grouping_fields(st_select_lex *sel) { List_iterator_fast<Item> li(list); diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index 1b119c743d3..d083248f6cd 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -2229,6 +2229,7 @@ class Item_cond :public Item_bool_func Item_transformer transformer, uchar *arg_t); bool eval_not_null_tables(void *opt_arg); Item *build_clone(THD *thd, MEM_ROOT *mem_root); + bool excl_dep_on_table(table_map tab_map); bool excl_dep_on_grouping_fields(st_select_lex *sel); };