Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.3, 10.3.8, 10.3.9
    • Fix Version/s: 10.3.10
    • Component/s: Optimizer
    • Labels:
      None
    • Environment:
      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

            • Assignee:
              igor Igor Babaev
              Reporter:
              fhx Frank E
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: