diff options
| -rw-r--r-- | mysql-test/main/having_cond_pushdown.result | 76 | ||||
| -rw-r--r-- | mysql-test/main/having_cond_pushdown.test | 30 | ||||
| -rw-r--r-- | sql/item.h | 10 | ||||
| -rw-r--r-- | 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 85ca0342dee..d7c9d936627 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 257e5cb4875..5088dad734d 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 97d31e6ba34..0d407353d60 100644 --- a/sql/item.h +++ b/sql/item.h @@ -714,6 +714,8 @@ public: /****************************************************************************/ +#define STOP_PTR ((void *) 1) + class Item: public Value_source, public Type_all_attributes { @@ -1829,8 +1831,10 @@ public: /*========= 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 @@ public: 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 f74541b34a4..8ea0bc50956 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(); |