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

Index affects query results

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.3.8, 10.3.9, 10.3(EOL)
    • 10.3.10
    • Optimizer
    • None
    • CentOS 7 3.10.0-862.9.1.el7.x86_64

    Description

      CREATE TABLE IF NOT EXISTS `test` (
      		 
        `n1` int(10) NOT NULL,
      		 
        `n2` int(10) NOT NULL,
      		 
        `c1` char(1) NOT NULL,
      		 
        KEY `c1` (`c1`) USING BTREE,
      		 
        KEY `n1_c1_n2` (`n1`,`c1`,`n2`) USING BTREE
      		 
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
      		 
       
      		 
      INSERT INTO `test` (`n1`, `n2`, `c1`) VALUES
      		 
      (0, 2, 'a'),
      		 
      (1, 3, 'a');
      		 
       
      		 
      SELECT test.n1
      		 
      FROM test
      		 
      INNER JOIN (
      		 
      	SELECT n1, n2
      		 
      	FROM test
      		 
      	WHERE
      		 
      		c1 = 'a'
      		 
      	GROUP BY n1
      		 
      	) t2
      		 
      	ON (t2.n1 = test.n1 AND t2.n2 = test.n2)
      		 
      WHERE c1 = 'a'
      		 
      GROUP BY n1

      Running the above query, as setup above, gives the incorrect result of:
      1

      Removing n2 from the multi-column index (`n1`,`c1`,`n2`), or rearranging that index to be (`n2`,`c1`,`n1`), or removing that index altogether, gives the correct query result of:
      0
      1

      The combination of that specific multi-column index with the single-column index of c1 seems to trigger the issue.

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          elenst Elena Stepanova added a comment -

          Thanks for the report and test case. Reproducible as described.

          The difference was apparently introduced in 10.3.4 by this revision:

          commit c5ac1f953bf6d4279967f03a8343303f715ad7aa
          		 
          Author: Igor Babaev
          		 
          Date:   Mon Jan 8 15:21:52 2018 -0800
          		 
           
          		 
              Fixed mdev-14880: Assertion `inj_cond_list.elements' failed
          		 
              in JOIN::inject_best_splitting_cond
          		 
              
              The value of SplM_opt_info::last_plan should be set to NULL
          		 
              before any search for a splitting plan for a splittable
          		 
              materialized table.

          elenst Elena Stepanova added a comment - Thanks for the report and test case. Reproducible as described. The difference was apparently introduced in 10.3.4 by this revision: commit c5ac1f953bf6d4279967f03a8343303f715ad7aa Author: Igor Babaev Date: Mon Jan 8 15:21:52 2018 -0800   Fixed mdev-14880: Assertion `inj_cond_list.elements' failed in JOIN::inject_best_splitting_cond The value of SplM_opt_info::last_plan should be set to NULL before any search for a splitting plan for a splittable materialized table.
          igor Igor Babaev (Inactive) added a comment - - edited

          The EXPLAIN output for the query shows the problem:

          MariaDB [test]> explain SELECT test.n1 FROM test INNER JOIN (   SELECT n1, n2   FROM test   WHERE     c1 = 'a'   GROUP BY n1   ) t2   ON (t2.n1 = test.n1 AND t2.n2 = test.n2) WHERE c1 = 'a' GROUP BY n1;
          		 
          +------+-----------------+------------+------+---------------+----------+---------+--------------+------+--------------------------------------------------------+
          		 
          | id   | select_type     | table      | type | possible_keys | key      | key_len | ref          | rows | Extra                                                  |
          		 
          +------+-----------------+------------+------+---------------+----------+---------+--------------+------+--------------------------------------------------------+
          		 
          |    1 | PRIMARY         | test       | ref  | c1,n1_c1_n2   | c1       | 3       | const        |    2 | Using index condition; Using temporary; Using filesort |
          		 
          |    1 | PRIMARY         | <derived2> | ALL  | NULL          | NULL     | NULL    | NULL         |    2 | Using where; Using join buffer (flat, BNL join)        |
          		 
          |    2 | LATERAL DERIVED | test       | ref  | c1,n1_c1_n2   | n1_c1_n2 | 4       | test.test.n1 |    1 | Using where; Using index                               |
          		 
          +------+-----------------+------------+------+---------------+----------+---------+--------------+------+--------------------------------------------------------+

          We can see from in execution plan that
          1. splitting technique is applied to join rows of the the derived table t2
          2. nested block loop is used to join t2.
          This is not possible because BNL assumes that t2 is looked through once for each filling of the join buffer with rows from table 'test' .

          igor Igor Babaev (Inactive) added a comment - - edited The EXPLAIN output for the query shows the problem: MariaDB [test]> explain SELECT test.n1 FROM test INNER JOIN ( SELECT n1, n2 FROM test WHERE c1 = 'a' GROUP BY n1 ) t2 ON (t2.n1 = test.n1 AND t2.n2 = test.n2) WHERE c1 = 'a' GROUP BY n1; +------+-----------------+------------+------+---------------+----------+---------+--------------+------+--------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-----------------+------------+------+---------------+----------+---------+--------------+------+--------------------------------------------------------+ | 1 | PRIMARY | test | ref | c1,n1_c1_n2 | c1 | 3 | const | 2 | Using index condition; Using temporary; Using filesort | | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2 | Using where; Using join buffer (flat, BNL join) | | 2 | LATERAL DERIVED | test | ref | c1,n1_c1_n2 | n1_c1_n2 | 4 | test.test.n1 | 1 | Using where; Using index | +------+-----------------+------------+------+---------------+----------+---------+--------------+------+--------------------------------------------------------+ We can see from in execution plan that 1. splitting technique is applied to join rows of the the derived table t2 2. nested block loop is used to join t2. This is not possible because BNL assumes that t2 is looked through once for each filling of the join buffer with rows from table 'test' .
          igor Igor Babaev (Inactive) added a comment -

          A fix for this bug was pushed into 10.3

          igor Igor Babaev (Inactive) added a comment - A fix for this bug was pushed into 10.3

          People

            igor Igor Babaev (Inactive)
            fhx Frank E
            Votes:
            0 Vote for this issue
            Watchers:
            4 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.