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

restore_prev_nj_state() doesn't update cur_sj_inner_tables correctly

    XMLWordPrintable

Details

    Description

      Discovered this while reviewing the patch

      Author: Michael Widenius <monty@mariadb.org>
      Date:   Sun May 15 15:46:29 2022 +0300
       
          greedy_search() and best_extension_by_limited_search() scrambled table order
          
          best_extension_by_limited_search() assumes that tables should be sorted
          according to size to be able to quickly disregard bad plans. However the
          current usage of swap_variables() will change the table order to a not
      

      The patch changes which join orders are considered, and potentially the order the join orders are enumerated.

      One of the .result changes in the patch:

      @@ -596,8 +596,8 @@ limit 10;
       id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
       1	PRIMARY	nation	ALL	PRIMARY	NULL	NULL	NULL	25	4.00	Using where; Using temporary; Using filesort
       1	PRIMARY	supplier	ref	PRIMARY,i_s_nationkey	i_s_nationkey	5	dbt3_s001.nation.n_nationkey	1	100.00	
      -1	PRIMARY	part	ALL	PRIMARY	NULL	NULL	NULL	200	7.03	Using where
      -1	PRIMARY	partsupp	eq_ref	PRIMARY,i_ps_partkey,i_ps_suppkey	PRIMARY	8	dbt3_s001.part.p_partkey,dbt3_s001.supplier.s_suppkey	1	100.00	Using where; FirstMatch(supplier)
      +1	PRIMARY	part	ALL	PRIMARY	NULL	NULL	NULL	200	7.03	Using where; Start temporary; Using join buffer (flat, BNL join)
      +1	PRIMARY	partsupp	eq_ref	PRIMARY,i_ps_partkey,i_ps_suppkey	PRIMARY	8	dbt3_s001.part.p_partkey,dbt3_s001.supplier.s_suppkey	1	100.00	Using where; End temporary
      

      That is

      -1  PRIMARY	part	... Using where
      +1  PRIMARY	part	... Using where; Start temporary; Using join buffer (flat, BNL join)
       
      -1  PRIMARY	partsupp ... Using where; FirstMatch(supplier)
      +1  PRIMARY	partsupp ... Using where; End temporary
      

      The join order is NOT changed while the semi-join strategy IS changed. This should not happen.

      Attachments

        Activity

          People

            psergei Sergei Petrunia
            psergei Sergei Petrunia
            Votes:
            0 Vote for this issue
            Watchers:
            1 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.