Hi, Sergei. See the new patch here: http://lists.askmonty.org/pipermail/commits/2016-November/010107.html Some comments, mostly answering your questions The patch was a model one, just to show how i'd solve this problem, not something to be pushed as it is.
+static Item_field *get_local_field (Item *field) +{ + Item *ri= field->real_item(); + return (ri->type() == Item::FIELD_ITEM + && !(field->used_tables() & OUTER_REF_TABLE_BIT) + && !((Item_field *)ri)->get_depended_from()) ? (Item_field *) ri : 0; +} Please fix indentation and add comments. Does this function do what is_local_field does, or there is some difference?
There's no difference in what it does from the is_local_field. Just seems more convenient to return the 'ri'. Rids us from a lot of field->real_item() calls. I think code will be nicer if we change is_local_field with this get_local_field calls. Didn't do that to keep the patch possible smaller.
+bool Item_bool_rowready_func2::add_extra_key_fields(THD *thd, + JOIN *join, KEY_FIELD **key_fields, + uint *and_level, + table_map usable_tables, + SARGABLE_PARAM **sargables) +{ + Item_field *f; + if ((f= field_in_sargable_func(args[0])) && args[1]->const_item())
What is the difference between add_key_fields and add_extra_key_fields? Any cases where one should call one but not the other?
The difference is obvious i guess :) The add_extra_key_fields handle that new 'reverse-function' opportunity. But yes, the add_extra_key_fields should ultimately disappear getting inside the add_key_fields. Only reason i made it separate for now is that it has the THD *thd argument which 'add_key_fields' doesn't. And again i didn't want to pollute this patch as that lot of 'add_key_fields' lines would have to be changed. Best regards. HF 02.11.2016 2:38, Sergey Petrunia пишет:
In-Reply-To: <20160928105123.6D948140DDC@nebo.localdomain> Hi Alexey,
Thanks for your patience in waiting for the review. Please find it below.
On Wed, Sep 28, 2016 at 02:50:19PM +0400, Alexey Botchkov wrote:
revision-id: 41a12f990519fb68eaa66ecc6860985471e6ba5a (mariadb-10.1.8-264-g41a12f9) parent(s): 28f441e36aaaec15ce7d447ef709fad7fbc7cf7d committer: Alexey Botchkov timestamp: 2016-09-28 14:48:54 +0400 message:
MDEV-8320 Allow index usage for DATE(datetime_column) = const.
Test for 'sargable functions' added.
First, t/range.test crashes after I apply the patch. MTR output is here: https://gist.github.com/spetrunia/d10165820664e0d18d4a667d44d226ee but I've got the crash on two different machines so should be easy to repeat
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index 6d432bd..516bb07 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -136,6 +136,14 @@ class Item_bool_func :public Item_int_func { protected: /* + Some functions modify it's arguments for the optimizer. + So for example the condition 'Func(fieldX) = constY' turned into + 'fieldX = cnuR(constY)' so that optimizer can use an index on fieldX. + */ What's cnuR? Ok, I eventually got it, but the comments should not have such puzzles.
+ Item *opt_args[3]; + uint opt_arg_count; + + /* +static Item_field *get_local_field (Item *field) +{ + Item *ri= field->real_item(); + return (ri->type() == Item::FIELD_ITEM + && !(field->used_tables() & OUTER_REF_TABLE_BIT) + && !((Item_field *)ri)->get_depended_from()) ? (Item_field *) ri : 0; +} Please fix indentation and add comments. Does this function do what is_local_field does, or there is some difference?
+ + +static Item_field *field_in_sargable_func(Item *fn) +{ + fn= fn->real_item(); + + if (fn->type() == Item::FUNC_ITEM && + strcmp(((Item_func *)fn)->func_name(), "cast_as_date") == 0) + + { + Item_date_typecast *dt= (Item_date_typecast *) fn; + return get_local_field(dt->arguments()[0]); + } + return 0; Please use NULL instead of 0, and !strcmp() instead of strcmp()=0.
@@ -5036,6 +5060,25 @@ Item_func_like::add_key_fields(JOIN *join, KEY_FIELD **key_fields, }
+bool Item_bool_rowready_func2::add_extra_key_fields(THD *thd, + JOIN *join, KEY_FIELD **key_fields, + uint *and_level, + table_map usable_tables, + SARGABLE_PARAM **sargables) +{ + Item_field *f; + if ((f= field_in_sargable_func(args[0])) && args[1]->const_item()) What is the difference between add_key_fields and add_extra_key_fields? Any cases where one should call one but not the other?
Please also do indentation as coding style specifies.
diff --git a/sql/item_timefunc.cc b/sql/item_timefunc.cc index 41dc967..3124444 100644 --- a/sql/item_timefunc.cc +++ b/sql/item_timefunc.cc @@ -2569,6 +2569,39 @@ bool Item_date_typecast::get_date(MYSQL_TIME *ltime, ulonglong fuzzy_date) }
+bool Item_date_typecast::create_reverse_func(enum Functype cmp_type, + THD *thd, Item *r_arg, uint *a_cnt, Item** a) +{ We need a specification of what exactly this function does, and a usage scenario in the comment. This function actually creates multiple (up to 3?) functions. If one has a condition
DATE(t1.d) < '2000-01-04'
then we get
(gdb) p ((Item*)cond)->opt_arg_count $37 = 3 (gdb) p dbug_print_item(((Item*)cond)->opt_args[0]) $38 = 0x555557083e20 <dbug_item_print_buf> "t1.d" (gdb) p dbug_print_item(((Item*)cond)->opt_args[1]) $39 = 0x555557083e20 <dbug_item_print_buf> "day_begin('2000-01-19')" (gdb) p dbug_print_item(((Item*)cond)->opt_args[2]) $40 = 0x555557083e20 <dbug_item_print_buf> "day_end('2000-01-19')"
which makes sense, but the description is lacking. Probably the name "create_reverse_func" is not good, because 1. multiple functions are created and 2. neither of them is the reverse. I can't suggest a better name at the moment, though. Let's both think about how to make this code cleared for an uninformed reader.
BR Sergei