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

Index affects query results

    XMLWordPrintable

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

          People

            igor Igor Babaev
            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.