revision-id: 6b2a7653d2b9a580ddc27f00461e7e6fb351b1bc (mariadb-10.2.16-53-g6b2a7653d2b) parent(s): f4eac2deeb046a68e4a26ed1a08f8df51bbf817b author: Galina Shalygina committer: Galina Shalygina timestamp: 2018-07-31 20:23:49 +0300 message: MDEV-16765: Missing rows with pushdown condition defined with CASE using Item_cond The bug appears because of the wrong pushdown into the WHERE clause of the materialized derived table/view work. For the excl_dep_on_grouping_fields() method that checks if the condition can be pushed into the WHERE clause the case when Item_cond is used is missing. For Item_cond elements this method always returns positive result (that condition can be pushed). So this condition is pushed even if is shouldn't be pushed. To fix it new Item_cond::excl_dep_on_grouping_fields() method is added. --- mysql-test/r/derived_cond_pushdown.result | 203 ++++++++++++++++++++++++++++++ mysql-test/t/derived_cond_pushdown.test | 58 +++++++++ sql/item_cmpfunc.cc | 13 ++ sql/item_cmpfunc.h | 1 + 4 files changed, 275 insertions(+) diff --git a/mysql-test/r/derived_cond_pushdown.result b/mysql-test/r/derived_cond_pushdown.result index 22b81ef48a5..9b64de41856 100644 --- a/mysql-test/r/derived_cond_pushdown.result +++ b/mysql-test/r/derived_cond_pushdown.result @@ -9874,3 +9874,206 @@ SELECT a FROM (SELECT "aa" a) t WHERE f1(t.a, (SELECT MAX('aa') FROM DUAL LIMIT a aa DROP FUNCTION f1; +# +# MDEV-16765: pushdown condition with the CASE structure +# defined with Item_cond item +# +CREATE TABLE t1(a INT, b INT); +INSERT INTO t1 VALUES (1,2), (3,4), (2,3); +SELECT * +FROM +( +SELECT CASE WHEN ((tab2.max_a=1) OR (tab2.max_a=2)) +THEN 1 ELSE 0 END AS max_a,b +FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2 +) AS tab1 +WHERE (tab1.max_a=1); +max_a b +1 2 +1 3 +EXPLAIN FORMAT=JSON SELECT * +FROM +( +SELECT CASE WHEN ((tab2.max_a=1) OR (tab2.max_a=2)) +THEN 1 ELSE 0 END AS max_a,b +FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2 +) AS tab1 +WHERE (tab1.max_a=1); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "<derived3>", + "access_type": "ALL", + "rows": 3, + "filtered": 100, + "attached_condition": "case when (tab2.max_a = 1 or tab2.max_a = 2) then 1 else 0 end = 1", + "materialized": { + "query_block": { + "select_id": 3, + "having_condition": "case when (max_a = 1 or max_a = 2) then 1 else 0 end = 1", + "filesort": { + "sort_key": "t1.b", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 3, + "filtered": 100 + } + } + } + } + } + } + } +} +SELECT * +FROM +( +SELECT CASE WHEN ((tab2.max_a=1) OR ((tab2.max_a>2) AND (tab2.max_a<4))) +THEN 1 ELSE 0 END AS max_a,b +FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2 +) AS tab1 +WHERE (tab1.max_a=1); +max_a b +1 2 +1 4 +EXPLAIN FORMAT=JSON SELECT * +FROM +( +SELECT CASE WHEN ((tab2.max_a=1) OR ((tab2.max_a>2) AND (tab2.max_a<4))) +THEN 1 ELSE 0 END AS max_a,b +FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2 +) AS tab1 +WHERE (tab1.max_a=1); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "<derived3>", + "access_type": "ALL", + "rows": 3, + "filtered": 100, + "attached_condition": "case when (tab2.max_a = 1 or tab2.max_a > 2 and tab2.max_a < 4) then 1 else 0 end = 1", + "materialized": { + "query_block": { + "select_id": 3, + "having_condition": "case when (max_a = 1 or max_a > 2 and max_a < 4) then 1 else 0 end = 1", + "filesort": { + "sort_key": "t1.b", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 3, + "filtered": 100 + } + } + } + } + } + } + } +} +SELECT * +FROM +( +SELECT CASE WHEN ((tab2.max_a>1) AND ((tab2.max_a=2) OR (tab2.max_a>2))) +THEN 1 ELSE 0 END AS max_a,b +FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2 +) AS tab1 +WHERE (tab1.max_a=1); +max_a b +1 3 +1 4 +EXPLAIN FORMAT=JSON SELECT * +FROM +( +SELECT CASE WHEN ((tab2.max_a>1) AND ((tab2.max_a=2) OR (tab2.max_a>2))) +THEN 1 ELSE 0 END AS max_a,b +FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2 +) AS tab1 +WHERE (tab1.max_a=1); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "<derived3>", + "access_type": "ALL", + "rows": 3, + "filtered": 100, + "attached_condition": "case when (tab2.max_a > 1 and (tab2.max_a = 2 or tab2.max_a > 2)) then 1 else 0 end = 1", + "materialized": { + "query_block": { + "select_id": 3, + "having_condition": "case when (max_a > 1 and (max_a = 2 or max_a > 2)) then 1 else 0 end = 1", + "filesort": { + "sort_key": "t1.b", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 3, + "filtered": 100 + } + } + } + } + } + } + } +} +SELECT * +FROM +( +SELECT CASE WHEN ((tab2.b=2) OR (tab2.b=4)) +THEN 1 ELSE 0 END AS max_a,b +FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2 +) AS tab1 +WHERE (tab1.max_a=1); +max_a b +1 2 +1 4 +EXPLAIN FORMAT=JSON SELECT * +FROM +( +SELECT CASE WHEN ((tab2.b=2) OR (tab2.b=4)) +THEN 1 ELSE 0 END AS max_a,b +FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2 +) AS tab1 +WHERE (tab1.max_a=1); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "<derived3>", + "access_type": "ALL", + "rows": 3, + "filtered": 100, + "attached_condition": "case when (tab2.b = 2 or tab2.b = 4) then 1 else 0 end = 1", + "materialized": { + "query_block": { + "select_id": 3, + "filesort": { + "sort_key": "t1.b", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 3, + "filtered": 100, + "attached_condition": "case when (t1.b = 2 or t1.b = 4) then 1 else 0 end = 1" + } + } + } + } + } + } + } +} +DROP TABLE t1; diff --git a/mysql-test/t/derived_cond_pushdown.test b/mysql-test/t/derived_cond_pushdown.test index d9d767f53ed..a8211ddd3e1 100644 --- a/mysql-test/t/derived_cond_pushdown.test +++ b/mysql-test/t/derived_cond_pushdown.test @@ -1920,3 +1920,61 @@ END;$$ DELIMITER ;$$ SELECT a FROM (SELECT "aa" a) t WHERE f1(t.a, (SELECT MAX('aa') FROM DUAL LIMIT 1)); DROP FUNCTION f1; + +--echo # +--echo # MDEV-16765: pushdown condition with the CASE structure +--echo # defined with Item_cond item +--echo # + +CREATE TABLE t1(a INT, b INT); +INSERT INTO t1 VALUES (1,2), (3,4), (2,3); + +LET $query= +SELECT * +FROM +( + SELECT CASE WHEN ((tab2.max_a=1) OR (tab2.max_a=2)) + THEN 1 ELSE 0 END AS max_a,b + FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2 +) AS tab1 +WHERE (tab1.max_a=1); +EVAL $query; +EVAL EXPLAIN FORMAT=JSON $query; + +LET $query= +SELECT * +FROM +( + SELECT CASE WHEN ((tab2.max_a=1) OR ((tab2.max_a>2) AND (tab2.max_a<4))) + THEN 1 ELSE 0 END AS max_a,b + FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2 +) AS tab1 +WHERE (tab1.max_a=1); +EVAL $query; +EVAL EXPLAIN FORMAT=JSON $query; + +LET $query= +SELECT * +FROM +( + SELECT CASE WHEN ((tab2.max_a>1) AND ((tab2.max_a=2) OR (tab2.max_a>2))) + THEN 1 ELSE 0 END AS max_a,b + FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2 +) AS tab1 +WHERE (tab1.max_a=1); +EVAL $query; +EVAL EXPLAIN FORMAT=JSON $query; + +LET $query= +SELECT * +FROM +( + SELECT CASE WHEN ((tab2.b=2) OR (tab2.b=4)) + THEN 1 ELSE 0 END AS max_a,b + FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2 +) AS tab1 +WHERE (tab1.max_a=1); +EVAL $query; +EVAL EXPLAIN FORMAT=JSON $query; + +DROP TABLE t1; diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index f176a0a8193..6834820c0b5 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -4970,6 +4970,19 @@ Item *Item_cond::build_clone(THD *thd, MEM_ROOT *mem_root) } +bool Item_cond::excl_dep_on_grouping_fields(st_select_lex *sel) +{ + List_iterator_fast<Item> li(list); + Item *item; + while ((item= li++)) + { + if (!item->excl_dep_on_grouping_fields(sel)) + return false; + } + return true; +} + + void Item_cond_and::mark_as_condition_AND_part(TABLE_LIST *embedding) { List_iterator<Item> li(list); diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index 41a51ee8d12..59ac5f56fe1 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -2224,6 +2224,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_grouping_fields(st_select_lex *sel); }; template <template<class> class LI, class T> class Item_equal_iterator;