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

Regression in 10.4 with InnoDB: indexed SELECT with join results in wrong results.

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.4
    • Fix Version/s: 10.5.0, 10.4.7
    • Component/s: Optimizer
    • Environment:
      GNU/Linux
      A simple textbook database with no partitions, no replication.
      MariaDB 10.4.6 was installed fresh.

      Description

      Upgraded from 10.3.16 to 10.4.6, and SELECTs are returning wrong results with even fairly simple queries! Specifically, the same query with additional OR terms returns fewer items.

      MariaDB [maildb]> SELECT * FROM route JOIN imap USING (userid)
      WHERE recipient IN ('chris@example.com', 'john@example.com');
      Empty set (0.001 sec)
      MariaDB [maildb]> SELECT * FROM route JOIN imap USING (userid)
      WHERE recipient IN ('chris@example.com');
      1 row in set (0.001 sec)

      I converted my database into a simple test script, attached below. Depending upon the order of INSERTs, the rows returned by SELECT are quite different. This will be immediately obvious when you read the test script. The regression occurs with InnoDB. It does not occur with Aria or MyISAM. I did not try other engine types. Regression occurs in both a fresh 10.4.6 database install as well as an upgrade from 10.3.16

      Expected Result:
      Output of "mysql -B -s < mariadb-10.4-test-script.sql" on a 10.3.16 server (correct):

      We should expect one record with "One Of These":
      One Of These
      Records returned: 1

      We should expect two records with "Two Of These":
      Two Of These
      Two Of These
      Records returned: 2

      We should get one "One Of These" and two "Two Of These":
      One Of These
      Two Of These
      Two Of These
      Records returned: 3

      Actual Results:
      The output of the same command against MariaDB 10.4:

      We should expect one record with "One Of These":
      One Of These
      Records returned: 1

      We should expect two records with "Two Of These":
      Records returned: 0

      We should get one "One Of These" and two "Two Of These":
      Records returned: 0

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                varun Varun Gupta
                Reporter:
                ktk Kris Karas
              • Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: