revision-id: 59ed5f3aa4bc5a02a65f93b1d054ccc0fb2cd248 (mariadb-10.4.4-26-g59ed5f3) parent(s): e7029e864f4b2c8fa88362677ee8150cc479f07f author: Igor Babaev committer: Igor Babaev timestamp: 2019-04-17 21:37:29 -0700 message: MDEV-19164 Assertion `fixed' failed in Item_func_inet_aton::val_int When pushing a condition from HAVING into WHERE the function st_select_lex::pushdown_from_having_into_where() transforms column references in the pushed condition then performs cleanup of items of the condition and finally calls fix_fields() for the condition items. The cleanup is performed by a call of the method walk() with cleanup_processor as the first parameter. Unfortunately this sequence of calls does not work if the condition contains cached items, because fix_fields() cannot go through Item_cache items and this leaves underlying items unfixed. The solution of this problem used in this patch is just does not allow to process Item_cache objects when performing cleanup of the pushed condition. In order to let the traversal procedure walk() not to process Item_cache objects the third parameter of the used call of walk() is set to a fictitious pointer (void *) 1. And Item_cache::walk() is changed to prevent any action when it gets such value as the third parameter. --- mysql-test/main/having_cond_pushdown.result | 76 +++++++++++++++++++++++++++++ mysql-test/main/having_cond_pushdown.test | 30 ++++++++++++ sql/item.h | 10 +++- sql/sql_lex.cc | 6 +-- 4 files changed, 117 insertions(+), 5 deletions(-) diff --git a/mysql-test/main/having_cond_pushdown.result b/mysql-test/main/having_cond_pushdown.result index 85ca034..d7c9d93 100644 --- a/mysql-test/main/having_cond_pushdown.result +++ b/mysql-test/main/having_cond_pushdown.result @@ -4657,3 +4657,79 @@ GROUP BY v1.pk HAVING (v1.pk = 1); DROP TABLE t1,t2,tmp1; DROP VIEW v1; +# +# MDEV-19164: pushdown of condition with cached items +# +create table t1 (d1 date); +insert into t1 values (null),('1971-03-06'),('1993-06-05'),('1998-07-08'); +select d1 from t1 +group by d1 +having d1 between (inet_aton('1978-04-27')) and '2018-08-26'; +d1 +explain extended select d1 from t1 +group by d1 +having d1 between (inet_aton('1978-04-27')) and '2018-08-26'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 100.00 Using where; Using temporary; Using filesort +Warnings: +Note 1003 select `test`.`t1`.`d1` AS `d1` from `test`.`t1` where `test`.`t1`.`d1` between <cache>(inet_aton('1978-04-27')) and <cache>('2018-08-26') group by `test`.`t1`.`d1` having 1 +explain format=json select d1 from t1 +group by d1 +having d1 between (inet_aton('1978-04-27')) and '2018-08-26'; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.d1", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 4, + "filtered": 100, + "attached_condition": "t1.d1 between <cache>(inet_aton('1978-04-27')) and <cache>('2018-08-26')" + } + } + } + } +} +delete from t1; +insert into t1 values ('2018-01-15'),('2018-02-20'); +select d1 from t1 +group by d1 +having d1 not between 0 AND exp(0); +d1 +2018-01-15 +2018-02-20 +Warnings: +Warning 1292 Truncated incorrect datetime value: '1' +explain extended select d1 from t1 +group by d1 +having d1 not between 0 AND exp(0); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary; Using filesort +Warnings: +Note 1003 select `test`.`t1`.`d1` AS `d1` from `test`.`t1` where `test`.`t1`.`d1` not between <cache>(0) and <cache>(exp(0)) group by `test`.`t1`.`d1` having 1 +explain format=json select d1 from t1 +group by d1 +having d1 not between 0 AND exp(0); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.d1", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 2, + "filtered": 100, + "attached_condition": "t1.d1 not between <cache>(0) and <cache>(exp(0))" + } + } + } + } +} +drop table t1; diff --git a/mysql-test/main/having_cond_pushdown.test b/mysql-test/main/having_cond_pushdown.test index 257e5cb..5088dad 100644 --- a/mysql-test/main/having_cond_pushdown.test +++ b/mysql-test/main/having_cond_pushdown.test @@ -1340,3 +1340,33 @@ HAVING (v1.pk = 1); DROP TABLE t1,t2,tmp1; DROP VIEW v1; + +--echo # +--echo # MDEV-19164: pushdown of condition with cached items +--echo # + +create table t1 (d1 date); +insert into t1 values (null),('1971-03-06'),('1993-06-05'),('1998-07-08'); + +let $q1= +select d1 from t1 + group by d1 + having d1 between (inet_aton('1978-04-27')) and '2018-08-26'; + +eval $q1; +eval explain extended $q1; +eval explain format=json $q1; + +delete from t1; +insert into t1 values ('2018-01-15'),('2018-02-20'); + +let $q2= +select d1 from t1 + group by d1 + having d1 not between 0 AND exp(0); + +eval $q2; +eval explain extended $q2; +eval explain format=json $q2; + +drop table t1; diff --git a/sql/item.h b/sql/item.h index 97d31e6..0d40735 100644 --- a/sql/item.h +++ b/sql/item.h @@ -714,6 +714,8 @@ class Item_const /****************************************************************************/ +#define STOP_PTR ((void *) 1) + class Item: public Value_source, public Type_all_attributes { @@ -1829,8 +1831,10 @@ class Item: public Value_source, /*========= Item processors, to be used with Item::walk() ========*/ virtual bool remove_dependence_processor(void *arg) { return 0; } virtual bool cleanup_processor(void *arg); - virtual bool cleanup_excluding_fields_processor(void *arg) { return cleanup_processor(arg); } - virtual bool cleanup_excluding_const_fields_processor(void *arg) { return cleanup_processor(arg); } + virtual bool cleanup_excluding_fields_processor (void *arg) + { return cleanup_processor(arg); } + virtual bool cleanup_excluding_const_fields_processor (void *arg) + { return cleanup_processor(arg); } virtual bool collect_item_field_processor(void *arg) { return 0; } virtual bool collect_outer_ref_processor(void *arg) {return 0; } virtual bool check_inner_refs_processor(void *arg) { return 0; } @@ -6535,6 +6539,8 @@ class Item_cache: public Item, virtual void set_null(); bool walk(Item_processor processor, bool walk_subquery, void *arg) { + if (arg == STOP_PTR) + return FALSE; if (example && example->walk(processor, walk_subquery, arg)) return TRUE; return (this->*processor)(arg); diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index f74541b..8ea0bc5 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -10112,7 +10112,7 @@ Item *remove_pushed_top_conjuncts_for_having(THD *thd, Item *cond) Multiple equalities are not removed but marked with DELETION_FL flag. They will be deleted later in substitite_for_best_equal_field() called for the HAVING condition. - 5. Unwrap fields wrapped in Item_ref wrappers contain in the condition + 5. Unwrap fields wrapped in Item_ref wrappers contained in the condition of attach_to_conds so the condition could be pushed into WHERE. @note @@ -10203,7 +10203,7 @@ Item *st_select_lex::pushdown_from_having_into_where(THD *thd, Item *having) join->having_equal= 0; /* - 5. Unwrap fields wrapped in Item_ref wrappers contain in the condition + 5. Unwrap fields wrapped in Item_ref wrappers contained in the condition of attach_to_conds so the condition could be pushed into WHERE. */ it.rewind(); @@ -10213,7 +10213,7 @@ Item *st_select_lex::pushdown_from_having_into_where(THD *thd, Item *having) &Item::field_transformer_for_having_pushdown, (uchar *)this); - if (item->walk(&Item::cleanup_processor, 0, 0) || + if (item->walk(&Item:: cleanup_processor, 0, STOP_PTR) || item->fix_fields(thd, NULL)) { attach_to_conds.empty();