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

Regression in IN subquery results in MariaDB 10.5 and 10.6

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Confirmed (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.5, 10.6, 10.5.12, 10.6.4
    • Fix Version/s: 10.5, 10.6
    • Component/s: None
    • Labels:
      None
    • Environment:
      OpenBSD/amd64 and Windows/x86_64

      Description

      There is a regression in MariaDB 10.5 and 10.6 affecting subqueries accessing tables that use foreign key constraints, possibly limited to queries that use window functions. I've tested that this issue does not affect 10.2, 10.3, and 10.4. If foreign key constraints are enabled, the IN predicate filters rows it shouldn't filter in MariaDB 10.5 and 10.6. If foreign key constraints are not enabled, the IN predicate operates correctly.

      See attached foreign_key.sql and no_foreign_key.sql files. The only difference between the two is in the use of FOREIGN KEY constraints when creating the tables. On MariaDB 10.2, 10.3, and 10.4, the final query in both files returns:

      ```
      id name
      1 Ar
      2 Ar
      ```

      On MariaDB 10.5 and 10.6, the final query in the no_foreign_key.sql file returns both records, but the final query in foreign_key.sql returns only one of the records:

      ```
      id name
      1 Ar
      ```

      There are a total of 5 select queries in both files, with the first four queries showing the same results in both the foreign key and non-foreign key cases.

      1) The 1st query shows that there are 2 entries in the artists table.
      2) The 2nd query shows that the inner-most subquery of the common table expression returns the expected results.
      3) The 3rd query shows that another of the subqueries in the common table expression returns the expected results.
      4) The 4th query shows that the common table expression itself returns the expected results of ids for both rows in the artists table.
      5) The 5th query shows the use of the common table expression in an IN subquery, with unexpected results of only a single row in MariaDB 10.5 and 10.6.

      Inlining the common table expression does not change the behavior, so this issue doesn't appear to be related to the use of common table expressions.

      Both files return expected results (the same results as MariaDB 10.2, 10.3, and 10.4) on SQLite 3.35.5.

      This issue was discovered during testing of Sequel, a database library for the Ruby programming language.

        Attachments

          Activity

            People

            Assignee:
            psergei Sergei Petrunia
            Reporter:
            jeremyevans Jeremy Evans
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration