[MDEV-23174] SELECT with a complex WHERE clause is not using range access Created: 2020-07-14  Updated: 2024-02-07  Resolved: 2024-02-07

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.3.22
Fix Version/s: N/A

Type: Bug Priority: Critical
Reporter: Sergei Petrunia Assignee: Maria M Pflaum
Resolution: Incomplete Votes: 2
Labels: optimizer-hard


 Description   

A single-table SELECT with a complex WHERE clause uses range access in MariaDB 5.2 and full table scan in MariaDB 10.3.22.

(The query and table DDLs are available but they are customer' data. Dataset is not available but one doesn't need it to demonstrate the issue)

 Comments   
Comment by Sergei Petrunia [ 2020-07-14 ]

It happens, because statement_should_be_aborted() eventually returns true:

class RANGE_OPT_PARAM
 
{
 
  ...
 
  bool statement_should_be_aborted() const
 
  {
 
    return
 
      thd->killed ||
 
      thd->is_fatal_error ||
 
      thd->is_error() ||
 
      alloced_sel_args > SEL_ARG::MAX_SEL_ARGS;
 
  }
 
}

it does because alloced_sel_args > SEL_ARG::MAX_SEL_ARGS.

The code that calls this:

SEL_TREE *Item_cond_and::get_mm_tree(RANGE_OPT_PARAM *param, Item **cond_ptr)
 
{  
  ...
 
  while ((item= li++))
 
  {
 
    SEL_TREE *new_tree= li.ref()[0]->get_mm_tree(param,li.ref());
 
    if (param->statement_should_be_aborted())
 
      DBUG_RETURN(NULL);

Comment by Sergei Petrunia [ 2020-07-14 ]

About the limit:

The limit is: MAX_SEL_ARGS =16000
If I raise it very high, the alloced_sel_args=255,673 (measured on 10.3)

Comment by Sergei Petrunia [ 2020-07-15 ]

The code that introduced the statement_should_be_aborted(): 

Author:	Tor Didriksen <tor.didriksen@oracle.com>  Tue Mar 19 20:09:17 2013
 
Committer:	Tor Didriksen <tor.didriksen@oracle.com>  Tue Mar 19 20:09:17 2013
 
 
 
Bug#13009341 CRASH IN STR_TO_DATETIME AFTER MISBEHAVING "BLOB" VALUE COMPARISON
 
 
 
The range optimizer uses 'save_in_field_no_warnings()' to verify properties of
 
'value <cmp> field' expressions.
 
If this execution yields an error, it should abort.

It was committed twice (a rebase?):
commit db8053e17347e561cb9e9471d39684c3f7f62075
commit 9b5fab5892e21c80b05f3f6ab4171e0d1a8520be

Comment by Sergei Petrunia [ 2020-07-15 ]

In 5.2.14, the execution also hits the param->alloced_sel_args=16000 limit
here:

  #1  0x00000000006aa25e in SEL_ARG::clone (this=0x7fffbc15bcb8, param=0x7ffff7ef6dd0, new_parent=0x7fffbc2a2248, next_arg=0x7ffff7ef6808) at opt_range.cc:1727
 
  #2  0x00000000006aa25e in SEL_ARG::clone (this=0x7fffbc15ff20, param=0x7ffff7ef6dd0, new_parent=0x7fffbc2a21e8, next_arg=0x7ffff7ef6808) at opt_range.cc:1727
 
  #3  0x00000000006aa25e in SEL_ARG::clone (this=0x7fffbc15f368, param=0x7ffff7ef6dd0, new_parent=0x7fffbc2a1ee8, next_arg=0x7ffff7ef6808) at opt_range.cc:1727
 
  #4  0x00000000006aa2fe in SEL_ARG::clone (this=0x7fffbc15ef80, param=0x7ffff7ef6dd0, new_parent=0x7fffbc2a1e88, next_arg=0x7ffff7ef6808) at opt_range.cc:1735
 
  #5  0x00000000006aa25e in SEL_ARG::clone (this=0x7fffbc15fb38, param=0x7ffff7ef6dd0, new_parent=0x7fffbc2a1e28, next_arg=0x7ffff7ef6808) at opt_range.cc:1727
 
  #6  0x00000000006aa25e in SEL_ARG::clone (this=0x7fffbc165148, param=0x7ffff7ef6dd0, new_parent=0x0, next_arg=0x7ffff7ef6808) at opt_range.cc:1727
 
  #7  0x00000000006aa33b in SEL_ARG::clone_tree (this=0x7fffbc162650, param=0x7ffff7ef6dd0) at opt_range.cc:1818
 
  #8  0x00000000006aba51 in key_and (param=0x7ffff7ef6dd0, key1=0x7fffbc162650, key2=0x7fffbc084908, clone_flag=3) at opt_range.cc:6414
 
  #9  0x00000000006ac37d in and_all_keys (param=0x7ffff7ef6dd0, key1=0x7fffbc0b9678, key2=0x7fffbc165148, clone_flag=3) at opt_range.cc:6360
 
  #10 0x00000000006aba22 in key_and (param=0x7ffff7ef6dd0, key1=0x7fffbc0b9678, key2=0x7fffbc165148, clone_flag=3) at opt_range.cc:6416
 
  #11 0x00000000006ac53c in tree_and (param=0x7ffff7ef6dd0, tree1=0x7fffbc05cee0, tree2=0x7fffbc158738) at opt_range.cc:6114
 
  #12 0x00000000006acfbb in get_mm_tree (param=0x7ffff7ef6dd0, cond=<optimized out>) at opt_range.cc:5430
 
  #13 0x00000000006adf91 in SQL_SELECT::test_quick_select (this=0x7fffbc075758, thd=0x21b2e80, keys_to_use=..., prev_tables=0, limit=<optimized out>, force_quick_range=<optimized out>) at opt_range.cc:2345

The difference is that after this, it still returns the tree that it has managed to build and apparently that is sufficient to get a decent query plan.

Comment by Sergei Petrunia [ 2020-07-15 ]

The code that introduced the statement_should_be_aborted():

This patch is not the cause of the issue. Running on 5.5.31 (the version before the patch was introduced) I get the same result - get_mm_tree() returns NULL.

Comment by Sergei Petrunia [ 2020-07-15 ]

If I change the logic around statement_should_be_aborted() call from "abort for all cases":

      thd->killed ||
 
      thd->is_fatal_error ||
 
      thd->is_error() ||
 
      alloced_sel_args > SEL_ARG::MAX_SEL_ARGS;

to "abort for the first three, but return the tree built so far if alloced_sel_args > SEL_ARG::MAX_SEL_ARGS " then the code produces some SEL_TREE objects. It difficult to tell if these are the same as ones in 5.2. The SEL_TREEs are very large so manual comparison is out of the question.

Comment by Sergei Petrunia [ 2020-07-17 ]

... alas, they seem to be different.

If I print records_in_range() calls made, I can see that:

Both 5.2 and fixed-10.3 print the same number of ranges for key=0.

For key=1:
10.3 gets 8 ranges in form:

key=1 min_key=8 min_key_flag=0, max_key=8, max_key_flag=3

5.2 gets 668 ranges in form 

 key=1 min_key=166 min_key_flag=0, max_key=166, max_key_flag=3

Also, 5.2 gets ranges for key=6 while 10.3 doesn't.
There are other minor differences.

Comment by Roel Van de Paar [ 2021-09-23 ]

julien.fritsch There is no need to, clearly psergei already understands the issue at hand. I confirmed it based on this. psergei can revert if necessary.

Comment by Sergei Petrunia [ 2023-10-11 ]

This patch was pushed to 10.6.16 and up:

commit d4347177c7f3f5b84f251feb00a8c796bd33dbf8
 
Author: Monty <monty@mariadb.org>
 
Date: Wed Sep 27 01:29:22 2023 +0300
 
 
 
Change SEL_ARG::MAX_SEL_ARGS to a user defined variable optimizer_max_sel_args
 
 
 
This allows a user to to change the default value of MAX_SEL_ARGS (16000)
 
in the rare case where they neeed more generated SEL_ARGS (as part of
 
the range optimizer)

It is not a fix for this issue but now one can try a workaround by setting optimizer_max_sel_args to a higher number.

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