[MDEV-15306] Wrong/Unexpected result with the value optimizer_use_condition_selectivity set to 4 Created: 2018-02-13  Updated: 2018-08-29  Resolved: 2018-08-29

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.3
Fix Version/s: 10.0.37, 10.1.36, 10.2.18, 10.3.10, 10.4.0

Type: Bug Priority: Major
Reporter: Varun Gupta (Inactive) Assignee: Varun Gupta (Inactive)
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-15253 Default optimizer setting changes for... Closed
Sprint: 10.3.6-1

 Description   

Here is the mtr test, 

CREATE TABLE t1 (a INT);
 
INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
 
set optimizer_use_condition_selectivity=4;
 
CREATE FUNCTION f1() RETURNS INT DETERMINISTIC
 
BEGIN
 
SET @cnt := @cnt + 1;
 
RETURN 1;
 
END;|
 
SET @cnt := 0;
 
SELECT * FROM t1 WHERE a = f1();
 
a
 
1
 
SELECT @cnt;
 
@cnt
 
2
 
SET @cnt := 0;
 
set optimizer_use_condition_selectivity=1;
 
SELECT * FROM t1 WHERE a = f1();
 
a
 
1
 
drop table t1;
 
drop function f1;

cc igorpsergey

 Comments   
Comment by Sergei Petrunia [ 2018-02-13 ]

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)

Comment by Sergei Petrunia [ 2018-02-13 ]

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?

Comment by Sergei Petrunia [ 2018-02-14 ]

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.

Comment by Varun Gupta (Inactive) [ 2018-03-28 ]

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
Comment by Sergei Petrunia [ 2018-03-29 ]

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.
Comment by Varun Gupta (Inactive) [ 2018-03-29 ]

Patch
http://lists.askmonty.org/pipermail/commits/2018-March/012159.html

Comment by Sergei Petrunia [ 2018-08-27 ]

Review feedback sent.

Comment by Sergei Petrunia [ 2018-08-28 ]

More review input provided over slack. Ok to push after addressed.

Generated at Thu Feb 08 08:20:16 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.