Sergei Petrunia added a comment - 2018-02-13 21:57 Stack traces of where the function is invoked Breakpoint 2, sp_head::execute (this=0x7fff84095028, thd=0x7fff84000b00, merge_da_on_success=true) at /home/psergey/dev-git/10.2/sql/sp_head.cc:1115 (gdb) wher #0 sp_head::execute (this=0x7fff84095028, thd=0x7fff84000b00, merge_da_on_success=true) at /home/psergey/dev-git/10.2/sql/sp_head.cc:1115 #1 0x0000555555ef90d7 in sp_head::execute_function (this=0x7fff84095028, thd=0x7fff84000b00, argp=0x0, argcount=0, return_value_fld=0x7fff84013938) at /home/psergey/dev-git/10.2/sql/sp_head.cc:1887 #2 0x0000555555e09a51 in Item_func_sp::execute_impl (this=0x7fff84011bb8, thd=0x7fff84000b00) at /home/psergey/dev-git/10.2/sql/item_func.cc:6705 #3 0x0000555555e097e2 in Item_func_sp::execute (this=0x7fff84011bb8) at /home/psergey/dev-git/10.2/sql/item_func.cc:6638 #4 0x0000555555e0dc3a in Item_func_sp::val_int (this=0x7fff84011bb8) at /home/psergey/dev-git/10.2/sql/item_func.h:2351 #5 0x0000555555da1cb9 in Item::save_in_field (this=0x7fff84011bb8, field=0x7fff84024d68, no_conversions=true) at /home/psergey/dev-git/10.2/sql/item.cc:6446 #6 0x0000555555d941da in Item::save_in_field_no_warnings (this=0x7fff84011bb8, field=0x7fff84024d68, no_conversions=true) at /home/psergey/dev-git/10.2/sql/item.cc:1436 #7 0x0000555555ed4bc4 in Item_bool_func::get_mm_leaf (this=0x7fff84013a28, param=0x7ffff44ab530, field=0x7fff84024d68, key_part=0x7fff84097808, type=Item_func::EQ_FUNC, value=0x7fff84011bb8) at /home/psergey/dev-git/10.2/sql/opt_range.cc:8018 #8 0x0000555555ed400f in Item_bool_func::get_mm_parts (this=0x7fff84013a28, param=0x7ffff44ab530, field=0x7fff84024d68, type=Item_func::EQ_FUNC, value=0x7fff84011bb8) at /home/psergey/dev-git/10.2/sql/opt_range.cc:7835 #9 0x0000555555ed3d4f in Item_equal::get_mm_tree (this=0x7fff84013a28, param=0x7ffff44ab530, cond_ptr=0x7fff84013628) at /home/psergey/dev-git/10.2/sql/opt_range.cc:7790 #10 0x0000555555ec8b74 in calculate_cond_selectivity_for_table (thd=0x7fff84000b00, table=0x7fff840091c0, cond=0x7fff84013628) at /home/psergey/dev-git/10.2/sql/opt_range.cc:3049 #11 0x0000555555b51404 in make_join_statistics (join=0x7fff84013220, tables_list=..., keyuse_array=0x7fff84013510) at /home/psergey/dev-git/10.2/sql/sql_select.cc:4411 #12 0x0000555555b47312 in JOIN::optimize_inner (this=0x7fff84013220) at /home/psergey/dev-git/10.2/sql/sql_select.cc:1525 #13 0x0000555555b45b35 in JOIN::optimize (this=0x7fff84013220) at /home/psergey/dev-git/10.2/sql/sql_select.cc:1101 #14 0x0000555555b4eb79 in mysql_select (thd=0x7fff84000b00, tables=0x7fff84011278, wild_num=1, fields=..., conds=0x7fff84012d58, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fff84013200, unit=0x7fff840046a0, select_lex=0x7fff84004dd8) at /home/psergey/dev-git/10.2/sql/sql_select.cc:3721 #15 0x0000555555b43382 in handle_select (thd=0x7fff84000b00, lex=0x7fff840045d8, result=0x7fff84013200, setup_tables_done_option=0) at /home/psergey/dev-git/10.2/sql/sql_select.cc:373 #16 0x0000555555b0f010 in execute_sqlcom_select (thd=0x7fff84000b00, all_tables=0x7fff84011278) at /home/psergey/dev-git/10.2/sql/sql_parse.cc:6456 #17 0x0000555555b05079 in mysql_execute_command (thd=0x7fff84000b00) at /home/psergey/dev-git/10.2/sql/sql_parse.cc:3467 #18 0x0000555555b129af in mysql_parse (thd=0x7fff84000b00, rawbuf=0x7fff84011088 "SELECT * FROM t1 WHERE a = f1()", length=31, parser_state=0x7ffff44ad210, is_com_multi=false, is_next_command=false) at /home/psergey/dev-git/10.2/sql/sql_parse.cc:7898 #19 0x0000555555b0095f in dispatch_command (command=COM_QUERY, thd=0x7fff84000b00, packet=0x7fff8400ca81 "SELECT * FROM t1 WHERE a = f1()", packet_length=31, is_com_multi=false, is_next_command=false) at /home/psergey/dev-git/10.2/sql/sql_parse.cc:1806 #20 0x0000555555aff2c2 in do_command (thd=0x7fff84000b00) at /home/psergey/dev-git/10.2/sql/sql_parse.cc:1360 #21 0x0000555555c4e1de in do_handle_one_connection (connect=0x55555869db50) at /home/psergey/dev-git/10.2/sql/sql_connect.cc:1335 #22 0x0000555555c4df6b in handle_one_connection (arg=0x55555869db50) at /home/psergey/dev-git/10.2/sql/sql_connect.cc:1241 #23 0x00007ffff650a182 in start_thread (arg=0x7ffff44ae700) at pthread_create.c:312 #24 0x00007ffff5a1730d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:111 (gdb) c Continuing. Breakpoint 2, sp_head::execute (this=0x7fff84095028, thd=0x7fff84000b00, merge_da_on_success=true) at /home/psergey/dev-git/10.2/sql/sp_head.cc:1115 (gdb) wher #0 sp_head::execute (this=0x7fff84095028, thd=0x7fff84000b00, merge_da_on_success=true) at /home/psergey/dev-git/10.2/sql/sp_head.cc:1115 #1 0x0000555555ef90d7 in sp_head::execute_function (this=0x7fff84095028, thd=0x7fff84000b00, argp=0x0, argcount=0, return_value_fld=0x7fff84013938) at /home/psergey/dev-git/10.2/sql/sp_head.cc:1887 #2 0x0000555555e09a51 in Item_func_sp::execute_impl (this=0x7fff84011bb8, thd=0x7fff84000b00) at /home/psergey/dev-git/10.2/sql/item_func.cc:6705 #3 0x0000555555e097e2 in Item_func_sp::execute (this=0x7fff84011bb8) at /home/psergey/dev-git/10.2/sql/item_func.cc:6638 #4 0x0000555555e0dc3a in Item_func_sp::val_int (this=0x7fff84011bb8) at /home/psergey/dev-git/10.2/sql/item_func.h:2351 #5 0x0000555555a40dc1 in Item::val_int_result (this=0x7fff84011bb8) at /home/psergey/dev-git/10.2/sql/item.h:1179 #6 0x0000555555dab691 in Item_cache_int::cache_value (this=0x7fff840158a8) at /home/psergey/dev-git/10.2/sql/item.cc:9593 #7 0x0000555555db42ba in Item_cache::has_value (this=0x7fff840158a8) at /home/psergey/dev-git/10.2/sql/item.h:5573 #8 0x0000555555dab891 in Item_cache_int::val_int (this=0x7fff840158a8) at /home/psergey/dev-git/10.2/sql/item.cc:9630 #9 0x0000555555dbbc69 in Arg_comparator::compare_int_signed (this=0x7fff840157f0) at /home/psergey/dev-git/10.2/sql/item_cmpfunc.cc:989 #10 0x0000555555dcec20 in Arg_comparator::compare (this=0x7fff840157f0) at /home/psergey/dev-git/10.2/sql/item_cmpfunc.h:87 #11 0x0000555555dbdf67 in Item_func_eq::val_int (this=0x7fff84015730) at /home/psergey/dev-git/10.2/sql/item_cmpfunc.cc:1784 #12 0x0000555555b74b9d in evaluate_join_record (join=0x7fff84013220, join_tab=0x7fff84014de0, error=0) at /home/psergey/dev-git/10.2/sql/sql_select.cc:18759 #13 0x0000555555b747d7 in sub_select (join=0x7fff84013220, join_tab=0x7fff84014de0, end_of_records=false) at /home/psergey/dev-git/10.2/sql/sql_select.cc:18664 #14 0x0000555555b73d73 in do_select (join=0x7fff84013220, procedure=0x0) at /home/psergey/dev-git/10.2/sql/sql_select.cc:18208 #15 0x0000555555b4e559 in JOIN::exec_inner (this=0x7fff84013220) at /home/psergey/dev-git/10.2/sql/sql_select.cc:3540 #16 0x0000555555b4da10 in JOIN::exec (this=0x7fff84013220) at /home/psergey/dev-git/10.2/sql/sql_select.cc:3335 #17 0x0000555555b4ec0c in mysql_select (thd=0x7fff84000b00, tables=0x7fff84011278, wild_num=1, fields=..., conds=0x7fff84012d58, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fff84013200, unit=0x7fff840046a0, select_lex=0x7fff84004dd8) at /home/psergey/dev-git/10.2/sql/sql_select.cc:3735 #18 0x0000555555b43382 in handle_select (thd=0x7fff84000b00, lex=0x7fff840045d8, result=0x7fff84013200, setup_tables_done_option=0) at /home/psergey/dev-git/10.2/sql/sql_select.cc:373 #19 0x0000555555b0f010 in execute_sqlcom_select (thd=0x7fff84000b00, all_tables=0x7fff84011278) at /home/psergey/dev-git/10.2/sql/sql_parse.cc:6456 #20 0x0000555555b05079 in mysql_execute_command (thd=0x7fff84000b00) at /home/psergey/dev-git/10.2/sql/sql_parse.cc:3467 #21 0x0000555555b129af in mysql_parse (thd=0x7fff84000b00, rawbuf=0x7fff84011088 "SELECT * FROM t1 WHERE a = f1()", length=31, parser_state=0x7ffff44ad210, is_com_multi=false, is_next_command=false) at /home/psergey/dev-git/10.2/sql/sql_parse.cc:7898 #22 0x0000555555b0095f in dispatch_command (command=COM_QUERY, thd=0x7fff84000b00, packet=0x7fff8400ca81 "", packet_length=31, is_com_multi=false, is_next_command=false) at /home/psergey/dev-git/10.2/sql/sql_parse.cc:1806 #23 0x0000555555aff2c2 in do_command (thd=0x7fff84000b00) at /home/psergey/dev-git/10.2/sql/sql_parse.cc:1360 #24 0x0000555555c4e1de in do_handle_one_connection (connect=0x55555869db50) at /home/psergey/dev-git/10.2/sql/sql_connect.cc:1335 #25 0x0000555555c4df6b in handle_one_connection (arg=0x55555869db50) at /home/psergey/dev-git/10.2/sql/sql_connect.cc:1241 #26 0x00007ffff650a182 in start_thread (arg=0x7ffff44ae700) at pthread_create.c:312 #27 0x00007ffff5a1730d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:111 (gdb)

Sergei Petrunia added a comment - 2018-02-13 22:17 - edited Debugging the first one, where the function is invoked from calculate_cond_selectivity_for_table() . we exit from get_mm_tree() with a SEL_TREE object desribing one interval. Then we enter records_in_column_ranges which calls get_column_range_cardinality . In that function, we take this if branch: double tab_records= ( double )table->stat_records();   if (!col_stats) return tab_records; then, after leaving records_in_column_ranges here rows= records_in_column_ranges(&param, idx, key); if (rows != DBL_MAX) key->field->cond_selectivity= rows/table_records; we get selectivity of 1. igor : so our question was: Does it make any sense to perform range analysis for a column for which col_stats = NULL ? I see two possible cases: case1. the one like in this bug, where we will infer that selectivity=100% case2: the case where the range optimizer can infer that the WHERE condition is impossible to meet (e.g. WHERE a<2 and a>5 ) case #1 is apparently not useful case #2 is useful, but is it worth performing range analysis on every column for?

Sergei Petrunia added a comment - 2018-02-14 16:49 It is not considered a problem that EITS code performs range analysis for columns that do not have statistics. The overhead of this is expected to be small The work on MDEV-11107 is expected to add some WHERE processing as well another suggestion: Add @optimizer_catch_contradictions variable and have it as FALSE.

Varun Gupta (Inactive) added a comment - 2018-03-28 15:10 - edited An idea that came up with discussion with psergey range analysis for column will only be performed if it has a record in mysql.column_stats. if it has none - no analysis if it has a record with min_value=max_value=NULL, that means perform range analysis. So having all columns as NULLs will mean "there is really no statistical data" but include this column in EITS computations anyway

Sergei Petrunia added a comment - 2018-03-29 08:14 Take-aways from yesterday's optimizer call: Contradictions will also be caught by MDEV-11107 For this MDEV, it is ok to not perform range analysis for columns that do not have statistics.

Varun Gupta (Inactive) added a comment - 2018-03-29 23:23 Patch http://lists.askmonty.org/pipermail/commits/2018-March/012159.html

Sergei Petrunia added a comment - 2018-08-27 09:38 Review feedback sent.

Sergei Petrunia added a comment - 2018-08-28 14:06 More review input provided over slack. Ok to push after addressed.