Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-21383

Possible range plan is not used under certain conditions

Details

    Description

      This is from the same customer issue as MDEV-21243.

      It is possible to construct a join query which will use full table scan for one of the tables, even if there is a possible range access for that table.

      I'm not sure if table definitions are public so I will not post the details in public until we've figured a general case to demonstrate the issue.

      The issue is reproducible on 10.3.{10,20} and 5.5.65. Not reproducible on 5.2.14

      Symptoms:
      The first range optimizer call constructs the range access.

      The problem shows up here in make_join_select

      	  /*
      		 
      	    We plan to scan all rows.
      		 
      	    Check again if we should use an index.
      		 
      	    We could have used an column from a previous table in
      		 
      	    the index if we are using limit and this is the first table
      		 
       
      		 
                  (1) - Don't switch the used index if we are using semi-join
      		 
                        LooseScan on this table. Using different index will not
      		 
                        produce the desired ordering and de-duplication.
      		 
      	  */
      		 
       
      		 
      	  if (!tab->table->is_filled_at_execution() &&
      		 
                    !tab->loosescan_match_tab &&              // (1)
      		 
                    ((cond && (!tab->keys.is_subset(tab->const_keys) && i > 0)) ||
      		 
                     (!tab->const_keys.is_clear_all() && i == join->const_tables &&
      		 
                      join->unit->select_limit_cnt <
      		 
                      join->best_positions[i].records_read &&
      		 
                      !(join->select_options & OPTION_FOUND_ROWS))))

      The if-branch is taken, inside it, SQL_SELECT::test_quick_select is called which doesn't construct the range access anymore (can't use multiple equalities?) and this is how we end up with a full table scan.

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-22450 Possible Range plan is not used with nested outer joins.

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-21243 Join buffer: condition is checked in wrong place for range access

          • Major - Major loss of function.
          • Closed

          Activity

            People

              psergei Sergei Petrunia
              psergei Sergei Petrunia
              Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.