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.

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.4
    • Fix Version/s: 10.4.7, 10.5.0
    • 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: