diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/CMakeLists.txt | 4 | ||||
| -rw-r--r-- | sql/item.cc | 13 | ||||
| -rw-r--r-- | sql/item.h | 6 | ||||
| -rw-r--r-- | sql/item_cmpfunc.cc | 1 | ||||
| -rw-r--r-- | sql/item_cmpfunc.h | 13 | ||||
| -rw-r--r-- | sql/item_func.cc | 2 | ||||
| -rw-r--r-- | sql/item_func.h | 1 | ||||
| -rw-r--r-- | sql/item_timefunc.h | 4 | ||||
| -rw-r--r-- | sql/opt_rewrite_date_cmp.cc | 363 | ||||
| -rw-r--r-- | sql/opt_rewrite_date_cmp.h | 111 | ||||
| -rw-r--r-- | sql/sql_lex.h | 20 | ||||
| -rw-r--r-- | sql/sql_select.cc | 16 | ||||
| -rw-r--r-- | sql/sql_select.h | 1 | ||||
| -rw-r--r-- | sql/sql_yacc.yy | 3 |
14 files changed, 551 insertions, 7 deletions
diff --git a/sql/CMakeLists.txt b/sql/CMakeLists.txt index 688c29cbe90..5238f66c4e7 100644 --- a/sql/CMakeLists.txt +++ b/sql/CMakeLists.txt @@ -109,7 +109,9 @@ SET (SQL_SOURCE mf_iocache.cc my_decimal.cc mysqld.cc net_serv.cc keycaches.cc ../sql-common/client_plugin.c - opt_range.cc opt_sum.cc + opt_range.cc + opt_rewrite_date_cmp.cc + opt_sum.cc ../sql-common/pack.c parse_file.cc password.c procedure.cc protocol.cc records.cc repl_failsafe.cc rpl_filter.cc session_tracker.cc diff --git a/sql/item.cc b/sql/item.cc index 5bcddb5cd67..cf9ab6c7877 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -6941,7 +6941,8 @@ Item *Item_int::clone_item(THD *thd) } -void Item_datetime::set(longlong packed, enum_mysql_timestamp_type ts_type) +void Item_datetime::set_from_packed(longlong packed, + enum_mysql_timestamp_type ts_type) { unpack_time(packed, <ime, ts_type); } @@ -6957,6 +6958,16 @@ longlong Item_datetime::val_int() return TIME_to_ulonglong(<ime); } +void Item_datetime::print(String *str, enum_query_type query_type) +{ + Datetime dt(current_thd, this); + String dt_str; + dt.to_string(&dt_str, decimals); + str->append('\''); + str->append(dt_str); + str->append('\''); +} + int Item_decimal::save_in_field(Field *field, bool no_conversions) { field->set_notnull(); diff --git a/sql/item.h b/sql/item.h index 706360c48f1..9b0dd4fac34 100644 --- a/sql/item.h +++ b/sql/item.h @@ -2508,6 +2508,8 @@ public: { return this; } virtual Item *multiple_equality_transformer(THD *thd, uchar *arg) { return this; } + virtual Item* date_conds_transformer(THD *thd, uchar *arg) + { return this; } virtual bool expr_cache_is_needed(THD *) { return FALSE; } virtual Item *safe_charset_converter(THD *thd, CHARSET_INFO *tocs); bool needs_charset_converter(uint32 length, CHARSET_INFO *tocs) const @@ -4491,12 +4493,14 @@ public: int save_in_field(Field *field, bool no_conversions) override; longlong val_int() override; double val_real() override { return (double)val_int(); } - void set(longlong packed, enum_mysql_timestamp_type ts_type); + void set(const MYSQL_TIME *datetime) { ltime= *datetime; } + void set_from_packed(longlong packed, enum_mysql_timestamp_type ts_type); bool get_date(THD *thd, MYSQL_TIME *to, date_mode_t fuzzydate) override { *to= ltime; return false; } + void print(String *str, enum_query_type query_type) override; }; diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index 8a1e6d9ffc3..e480ae26903 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -7802,3 +7802,4 @@ Item *Item_equal::multiple_equality_transformer(THD *thd, uchar *arg) break; } } + diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index 1422afb0fd0..a0c54077902 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -25,6 +25,7 @@ #include "item_func.h" /* Item_int_func, Item_bool_func */ #include "item.h" +#include "opt_rewrite_date_cmp.h" extern Item_result item_cmp_type(Item_result a,Item_result b); inline Item_result item_cmp_type(const Item *a, const Item *b) @@ -790,6 +791,8 @@ public: friend class Arg_comparator; Item *get_copy(THD *thd) override { return get_item_copy<Item_func_eq>(thd, this); } + Item* date_conds_transformer(THD *thd, uchar *arg) override + { return do_date_conds_transformation(thd, this); } }; class Item_func_equal final :public Item_bool_rowready_func2 @@ -839,6 +842,8 @@ public: Item *negated_item(THD *thd) override; Item *get_copy(THD *thd) override { return get_item_copy<Item_func_ge>(thd, this); } + Item* date_conds_transformer(THD *thd, uchar *arg) override + { return do_date_conds_transformation(thd, this); } }; @@ -859,6 +864,8 @@ public: Item *negated_item(THD *thd) override; Item *get_copy(THD *thd) override { return get_item_copy<Item_func_gt>(thd, this); } + Item* date_conds_transformer(THD *thd, uchar *arg) override + { return do_date_conds_transformation(thd, this); } }; @@ -879,6 +886,8 @@ public: Item *negated_item(THD *thd) override; Item *get_copy(THD *thd) override { return get_item_copy<Item_func_le>(thd, this); } + Item* date_conds_transformer(THD *thd, uchar *arg) override + { return do_date_conds_transformation(thd, this); } }; @@ -899,6 +908,8 @@ public: Item *negated_item(THD *thd) override; Item *get_copy(THD *thd) override { return get_item_copy<Item_func_lt>(thd, this); } + Item* date_conds_transformer(THD *thd, uchar *arg) override + { return do_date_conds_transformation(thd, this); } }; @@ -1585,7 +1596,7 @@ public: { packed_longlong *val= reinterpret_cast<packed_longlong*>(base)+pos; Item_datetime *dt= static_cast<Item_datetime*>(item); - dt->set(val->val, type_handler()->mysql_timestamp_type()); + dt->set_from_packed(val->val, type_handler()->mysql_timestamp_type()); } friend int cmp_longlong(void *cmp_arg, packed_longlong *a,packed_longlong *b); }; diff --git a/sql/item_func.cc b/sql/item_func.cc index a07595cbbd8..3a55b8288aa 100644 --- a/sql/item_func.cc +++ b/sql/item_func.cc @@ -277,7 +277,6 @@ bool Item_func::check_argument_types_scalar(uint start, uint end) const return false; } - /* Resolve references to table column for a function and its argument @@ -362,6 +361,7 @@ Item_func::fix_fields(THD *thd, Item **ref) if (fix_length_and_dec(thd)) return TRUE; base_flags|= item_base_t::FIXED; + return FALSE; } diff --git a/sql/item_func.h b/sql/item_func.h index 520dbdc90c7..2d83eed4b94 100644 --- a/sql/item_func.h +++ b/sql/item_func.h @@ -78,6 +78,7 @@ public: JSON_EXTRACT_FUNC, JSON_VALID_FUNC, ROWNUM_FUNC, CASE_SEARCHED_FUNC, // Used by ColumnStore/Spider CASE_SIMPLE_FUNC, // Used by ColumnStore/spider, + DATE_FUNC, YEAR_FUNC }; static scalar_comparison_op functype_to_scalar_comparison_op(Functype type) { diff --git a/sql/item_timefunc.h b/sql/item_timefunc.h index a5f6d9307c6..953984e4c72 100644 --- a/sql/item_timefunc.h +++ b/sql/item_timefunc.h @@ -462,6 +462,7 @@ public: static LEX_CSTRING name= {STRING_WITH_LEN("year") }; return name; } + enum Functype functype() const override { return YEAR_FUNC; } enum_monotonicity_info get_monotonicity_info() const override; longlong val_int_endpoint(bool left_endp, bool *incl_endp) override; bool fix_length_and_dec(THD *thd) override @@ -1334,6 +1335,7 @@ public: { print_cast_temporal(str, query_type); } + enum Functype functype() const override { return DATE_FUNC; } bool get_date(THD *thd, MYSQL_TIME *ltime, date_mode_t fuzzydate) override; bool fix_length_and_dec(THD *thd) override { @@ -2025,6 +2027,4 @@ public: return false; } }; - - #endif /* ITEM_TIMEFUNC_INCLUDED */ diff --git a/sql/opt_rewrite_date_cmp.cc b/sql/opt_rewrite_date_cmp.cc new file mode 100644 index 00000000000..234dbb09311 --- /dev/null +++ b/sql/opt_rewrite_date_cmp.cc @@ -0,0 +1,363 @@ +/* + Copyright (c) 2023, MariaDB + + This program is free software; you can redistribute it and/or modify + it under the terms of the GNU General Public License as published by + the Free Software Foundation; version 2 of the License. + + This program is distributed in the hope that it will be useful, + but WITHOUT ANY WARRANTY; without even the implied warranty of + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + GNU General Public License for more details. + + You should have received a copy of the GNU General Public License + along with this program; if not, write to the Free Software + Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1335 USA */ + + +/** + @file + + @brief + Rewrites that make non-sargable date[time] comparisons sargable. +*/ + +#ifdef USE_PRAGMA_IMPLEMENTATION +#pragma implementation // gcc: Class implementation +#endif + +#include "mariadb.h" +#include "sql_priv.h" +#include "my_json_writer.h" +#include "opt_rewrite_date_cmp.h" + +Date_cmp_func_rewriter::Date_cmp_func_rewriter(THD* thd, + Item_func_eq *item_func) : + thd(thd), + result(item_func) +{ + if (!check_cond_match_and_prepare(item_func)) + return; + + /* + This is an equality. Do a rewrite like this: + "YEAR(col) = val" -> col BETWEEN year_start(val) AND year_end(val) + "DATE(col) = val" -> col BETWEEN day_start(val) AND day_end(val) + */ + Item *start_bound, *end_bound; + if (!(start_bound= create_start_bound()) || !(end_bound= create_end_bound())) + return; + Item *new_cond; + if (!(new_cond= new (thd->mem_root) Item_func_between(thd, field_ref, + start_bound, end_bound))) + return; + if (!new_cond->fix_fields(thd, &new_cond)) + result= new_cond; +} + + +Date_cmp_func_rewriter::Date_cmp_func_rewriter(THD* thd, + Item_func_ge *item_func) : + thd(thd), + result(item_func) +{ + if (!check_cond_match_and_prepare(item_func)) + return; + rewrite_le_gt_lt_ge(); +} + + +Date_cmp_func_rewriter::Date_cmp_func_rewriter(THD* thd, + Item_func_lt *item_func) : + thd(thd), + result(item_func) +{ + if (!check_cond_match_and_prepare(item_func)) + return; + rewrite_le_gt_lt_ge(); +} + + +Date_cmp_func_rewriter::Date_cmp_func_rewriter(THD* thd, + Item_func_gt *item_func) : + thd(thd), + result(item_func) +{ + if (!check_cond_match_and_prepare(item_func)) + return; + rewrite_le_gt_lt_ge(); +} + + +Date_cmp_func_rewriter::Date_cmp_func_rewriter(THD* thd, + Item_func_le*item_func) : + thd(thd), + result(item_func) +{ + if (!check_cond_match_and_prepare(item_func)) + return; + rewrite_le_gt_lt_ge(); +} + + +bool Date_cmp_func_rewriter::check_cond_match_and_prepare( + Item_bool_rowready_func2 *item_func) +{ + if (thd->lex->is_ps_or_view_context_analysis()) + { + DBUG_ASSERT(0); + return false; + } + + Item **args= item_func->arguments(); + rewrite_func_type= item_func->functype(); + bool condition_matches= false; + const Type_handler *comparison_type= item_func->get_comparator()-> + compare_type_handler(); + + /* + Check if this is "YEAR(indexed_col) CMP const_item" or + "DATE(indexed_col) CMP const_item" + */ + if ((field_ref= is_date_rounded_field(args[0], comparison_type, + &argument_func_type)) && + args[1]->basic_const_item()) + { + const_arg_value= args[1]; + condition_matches= true; + } + else + /* + Check if this is "const_item CMP YEAR(indexed_col)" or + "const_item CMP DATE(indexed_col)" + */ + if ((field_ref= is_date_rounded_field(args[1], comparison_type, + &argument_func_type)) && + args[0]->basic_const_item()) + { + /* + Ok, the condition has form like "const<YEAR(col)"/"const<DATE(col)". + Turn it around to be "YEAR(col)>const"/"DATE(col)>const" + */ + const_arg_value= args[0]; + + rewrite_func_type= item_func->rev_functype(); + condition_matches= true; + } + return condition_matches; +} + +/* + Check if the passed item is YEAR(key_col) or DATE(key_col). + + Also + - key_col must be covered by an index usable by the current query + - key_col must have a DATE[TIME] or TIMESTAMP type + - The value of the YEAR(..) or DATE(..) function must be compared using an a + appropriate comparison_type. + + @param item IN Item to check + @param comparison_type IN Which datatype is used to compare the item value + @param out_func_type OUT Function (is it YEAR or DATE) + + @return + key_col if the check suceeded + NULL otherwise +*/ +Item_field *Date_cmp_func_rewriter::is_date_rounded_field(Item* item, + const Type_handler *comparison_type, + Item_func::Functype *out_func_type) const +{ + if (item->type() != Item::FUNC_ITEM) + return nullptr; + + Item_func::Functype func_type= ((Item_func*)item)->functype(); + bool function_ok= false; + switch (func_type) { + case Item_func::YEAR_FUNC: + // The value of YEAR(x) must be compared as integer + if (comparison_type == &type_handler_slonglong) + function_ok= true; + break; + case Item_func::DATE_FUNC: + // The value of DATE(x) must be compared as dates. + if (comparison_type == &type_handler_newdate) + function_ok= true; + break; + default: + ;// do nothing + } + + if (function_ok) + { + Item* arg= ((Item_func*)item)->arguments()[0]; + // Check if the argument is a column that's covered by some index + if (arg->real_item()->type() == Item::FIELD_ITEM) + { + Item_field *item_field= (Item_field*)(arg->real_item()); + const key_map * used_indexes= + &item_field->field->table->keys_in_use_for_query; + enum_field_types field_type= item_field->field_type(); + if ((field_type == MYSQL_TYPE_DATE || + field_type == MYSQL_TYPE_DATETIME || + field_type == MYSQL_TYPE_NEWDATE || + field_type == MYSQL_TYPE_TIMESTAMP) && + item_field->field->part_of_key.is_overlapping(*used_indexes)) + { + *out_func_type= func_type; + return item_field; + } + } + } + return nullptr; +} + + +void Date_cmp_func_rewriter::rewrite_le_gt_lt_ge() +{ + if (rewrite_func_type == Item_func::LE_FUNC || + rewrite_func_type == Item_func::GT_FUNC) + { + const_arg_value= create_end_bound(); + } + else if (rewrite_func_type == Item_func::LT_FUNC || + rewrite_func_type == Item_func::GE_FUNC) + { + const_arg_value= create_start_bound(); + } + if (!const_arg_value) + return; + Item *repl= create_cmp_func(rewrite_func_type, field_ref, const_arg_value); + if (!repl) + return; + if (!repl->fix_fields(thd, &repl)) + result= repl; +} + + +Item *Date_cmp_func_rewriter::create_start_bound() +{ + Item_datetime *res; + MYSQL_TIME const_arg_ts; + memset(&const_arg_ts, 0, sizeof(const_arg_ts)); + const_arg_ts.time_type= MYSQL_TIMESTAMP_DATETIME; + switch (argument_func_type) { + case Item_func::YEAR_FUNC: + const_arg_ts.year= static_cast<unsigned int>(const_arg_value->val_int()); + const_arg_ts.month= 1; + const_arg_ts.day= 1; + if (check_datetime_range(&const_arg_ts)) + return nullptr; + res= new (thd->mem_root) Item_datetime(thd); + res->set(&const_arg_ts); + break; + case Item_func::DATE_FUNC: + if (field_ref->field->type() == MYSQL_TYPE_DATE) + return const_arg_value; + else + { + Datetime const_arg_dt(current_thd, const_arg_value); + if (!const_arg_dt.is_valid_datetime()) + return nullptr; + res= new (thd->mem_root) Item_datetime(thd); + const_arg_dt.copy_to_mysql_time(&const_arg_ts); + const_arg_ts.second_part= const_arg_ts.second= + const_arg_ts.minute= const_arg_ts.hour= 0; + const_arg_ts.time_type= MYSQL_TIMESTAMP_DATETIME; + res->set(&const_arg_ts); + } + break; + default: + DBUG_ASSERT(0); + res= nullptr; + break; + } + return res; +} + + +Item *Date_cmp_func_rewriter::create_end_bound() +{ + Item_datetime *res; + MYSQL_TIME const_arg_ts; + memset(&const_arg_ts, 0, sizeof(const_arg_ts)); + const_arg_ts.time_type= MYSQL_TIMESTAMP_DATETIME; + switch (argument_func_type) { + case Item_func::YEAR_FUNC: + const_arg_ts.year= static_cast<unsigned int>(const_arg_value->val_int()); + const_arg_ts.month= 12; + const_arg_ts.day= 31; + const_arg_ts.hour= 23; + const_arg_ts.minute= TIME_MAX_MINUTE; + const_arg_ts.second= TIME_MAX_SECOND; + const_arg_ts.second_part= TIME_MAX_SECOND_PART; + if (check_datetime_range(&const_arg_ts)) + return nullptr; + res= new (thd->mem_root) Item_datetime(thd); + res->set(&const_arg_ts); + break; + case Item_func::DATE_FUNC: + if (field_ref->field->type() == MYSQL_TYPE_DATE) + return const_arg_value; + else + { + res= new (thd->mem_root) Item_datetime(thd); + Datetime const_arg_dt(current_thd, const_arg_value); + if (!const_arg_dt.is_valid_datetime()) + return nullptr; + const_arg_dt.copy_to_mysql_time(&const_arg_ts); + const_arg_ts.hour= 23; + const_arg_ts.minute= TIME_MAX_MINUTE; + const_arg_ts.second= TIME_MAX_SECOND; + const_arg_ts.second_part=TIME_MAX_SECOND_PART; + const_arg_ts.time_type= MYSQL_TIMESTAMP_DATETIME; + res->set(&const_arg_ts); + } + break; + default: + DBUG_ASSERT(0); + res= nullptr; + break; + } + return res; +} + + +/* + Create an Item for "arg1 $CMP arg2", where $CMP is specified by func_type. +*/ +Item *Date_cmp_func_rewriter::create_cmp_func(Item_func::Functype func_type, + Item *arg1, Item *arg2) +{ + Item *res; + switch (func_type) { + case Item_func::GE_FUNC: + res= new (thd->mem_root) Item_func_ge(thd, arg1, arg2); + break; + case Item_func::GT_FUNC: + res= new (thd->mem_root) Item_func_gt(thd, arg1, arg2); + break; + case Item_func::LE_FUNC: + res= new (thd->mem_root) Item_func_le(thd, arg1, arg2); + break; + case Item_func::LT_FUNC: + res= new (thd->mem_root) Item_func_lt(thd, arg1, arg2); + break; + default: + DBUG_ASSERT(0); + res= NULL; + } + return res; +} + +void trace_date_item_rewrite(THD *thd, Item *new_item, Item *old_item) +{ + if (new_item != old_item) + { + Json_writer_object trace_wrapper(thd); + trace_wrapper.add("transformation", "date_conds_into_sargable") + .add("before", old_item) + .add("after", new_item); + } +} + diff --git a/sql/opt_rewrite_date_cmp.h b/sql/opt_rewrite_date_cmp.h new file mode 100644 index 00000000000..9f6e1d74ffa --- /dev/null +++ b/sql/opt_rewrite_date_cmp.h @@ -0,0 +1,111 @@ +/* + Copyright (c) 2023, MariaDB + + This program is free software; you can redistribute it and/or modify + it under the terms of the GNU General Public License as published by + the Free Software Foundation; version 2 of the License. + + This program is distributed in the hope that it will be useful, + but WITHOUT ANY WARRANTY; without even the implied warranty of + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + GNU General Public License for more details. + + You should have received a copy of the GNU General Public License + along with this program; if not, write to the Free Software + Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1335 USA */ + +#ifndef OPT_REWRITE_DATE_CMP_INCLUDED +#define OPT_REWRITE_DATE_CMP_INCLUDED + +class Item_func_eq; +class Item_func_ge; +class Item_func_gt; +class Item_func_le; +class Item_func_lt; +class Item_bool_rowready_func2; + +/* + @brief Class responsible for rewriting datetime comparison condition. + It rewrites non-sargable conditions into sargable. + + @detail + The intent of this class is to do equivalent rewrites as follows: + + YEAR(col) <= val -> col <= year_end(val) + YEAR(col) < val -> col < year_start(val) + YEAR(col) >= val -> col >= year_start(val) + YEAR(col) > val -> col > year_end(val) + YEAR(col) = val -> col >= year_start(val) AND col<=year_end(val) + + Also the same is done for comparisons with DATE(col): + + DATE(col) <= val -> col <= day_end(val) + + if col has a DATE type (not DATETIME), then the rewrite becomes: + + DATE(col) <= val -> col <= val + + @usage + Date_cmp_func_rewriter rwr(thd, item_func); + Item *new_item= rwr.get_rewrite_result(); + + Returned new_item points to an item that item_func was rewritten to. + new_item already has fixed fields (fix_fields() was called). + If no rewrite happened, new_item points to the initial item_func parameter + + @todo + Also handle conditions in form "YEAR(date_col) BETWEEN 2014 AND 2017" + and "YEAR(col) = c1 AND MONTH(col) = c2" +*/ +class Date_cmp_func_rewriter +{ +public: + Date_cmp_func_rewriter(THD* thd, Item_func_eq *item_func); + + Date_cmp_func_rewriter(THD* thd, Item_func_ge *item_func); + + Date_cmp_func_rewriter(THD* thd, Item_func_gt *item_func); + + Date_cmp_func_rewriter(THD* thd, Item_func_le *item_func); + + Date_cmp_func_rewriter(THD* thd, Item_func_lt *item_func); + + Item* get_rewrite_result() const { return result; } + + Date_cmp_func_rewriter() = delete; + Date_cmp_func_rewriter(const Date_cmp_func_rewriter&) = delete; + Date_cmp_func_rewriter(Date_cmp_func_rewriter&&) = delete; + +private: + bool check_cond_match_and_prepare(Item_bool_rowready_func2 *item_func); + Item_field *is_date_rounded_field(Item* item, + const Type_handler *comparison_type, + Item_func::Functype *out_func_type) const; + void rewrite_le_gt_lt_ge(); + Item *create_start_bound(); + Item *create_end_bound(); + Item *create_cmp_func(Item_func::Functype func_type, Item *arg1, Item *arg2); + + THD *thd= nullptr; + Item *const_arg_value= nullptr; + Item_func::Functype rewrite_func_type= Item_func::UNKNOWN_FUNC; + Item_func::Functype argument_func_type= Item_func::UNKNOWN_FUNC; + Item_field *field_ref= nullptr; + Item *result= nullptr; +}; + + +void trace_date_item_rewrite(THD *thd,Item *new_item, Item *old_item); + +template<typename T> +Item* do_date_conds_transformation(THD *thd, T *item) +{ + Date_cmp_func_rewriter rwr(thd, item); + /* If the rewrite failed for some reason, we get the original item */ + Item *new_item= rwr.get_rewrite_result(); + trace_date_item_rewrite(thd, new_item, item); + return new_item; +} + + +#endif diff --git a/sql/sql_lex.h b/sql/sql_lex.h index 95ed308103d..7d8895f1e6c 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -2320,6 +2320,20 @@ public: bool uses_stored_routines() const { return sroutines_list.elements != 0; } + void set_date_funcs_used_flag() + { + date_funcs_used_flag= true; + } + + /* + Returns TRUE if date functions such as YEAR(), MONTH() or DATE() + are used in this LEX + */ + bool are_date_funcs_used() const + { + return date_funcs_used_flag; + } + private: /** @@ -2360,6 +2374,12 @@ private: be accessed while executing a statement. */ uint32 stmt_accessed_table_flag; + + /* + Flag indicating that date functions such as YEAR(), MONTH() or DATE() are + used in this LEX + */ + bool date_funcs_used_flag= false; }; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 9b9d93d182e..12d4eb2cd79 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -2257,6 +2257,8 @@ JOIN::optimize_inner() } transform_in_predicates_into_equalities(thd); + if (thd->lex->are_date_funcs_used()) + transform_date_conds_into_sargable(); conds= optimize_cond(this, conds, join_list, ignore_on_expr, &cond_value, &cond_equal, OPT_LINK_EQUAL_FIELDS); @@ -31095,6 +31097,20 @@ bool JOIN::transform_in_predicates_into_equalities(THD *thd) /** @brief + Rewrite datetime comparison conditions into sargable. + See details in the description for class Date_cmp_func_rewriter +*/ + +bool JOIN::transform_date_conds_into_sargable() +{ + DBUG_ENTER("JOIN::transform_date_conds_into_sargable"); + DBUG_RETURN(transform_all_conds_and_on_exprs( + thd, &Item::date_conds_transformer)); +} + + +/** + @brief Transform all items in WHERE and ON expressions using a given transformer @param thd The context of the statement diff --git a/sql/sql_select.h b/sql/sql_select.h index fa4d373d556..5a750823828 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -1814,6 +1814,7 @@ private: void free_pushdown_handlers(List<TABLE_LIST>& join_list); void init_join_cache_and_keyread(); bool transform_in_predicates_into_equalities(THD *thd); + bool transform_date_conds_into_sargable(); bool transform_all_conds_and_on_exprs(THD *thd, Item_transformer transformer); bool transform_all_conds_and_on_exprs_in_join_list(THD *thd, diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index f0ed46646ae..38260d1007b 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -9865,6 +9865,7 @@ function_call_keyword: $$= new (thd->mem_root) Item_date_typecast(thd, $3); if (unlikely($$ == NULL)) MYSQL_YYABORT; + Lex->set_date_funcs_used_flag(); } | DAY_SYM '(' expr ')' { @@ -9927,6 +9928,7 @@ function_call_keyword: $$= new (thd->mem_root) Item_func_month(thd, $3); if (unlikely($$ == NULL)) MYSQL_YYABORT; + Lex->set_date_funcs_used_flag(); } | RIGHT '(' expr ',' expr ')' { @@ -9977,6 +9979,7 @@ function_call_keyword: $$= new (thd->mem_root) Item_func_year(thd, $3); if (unlikely($$ == NULL)) MYSQL_YYABORT; + Lex->set_date_funcs_used_flag(); } ; |