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

Constant from LEFT JOIN incorrectly folded when derived_merge=on

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Confirmed (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 5.5, 10.0, 10.1, 10.2, 10.3, 10.4.10, 10.4, 10.5
    • Fix Version/s: 10.2, 10.3, 10.4, 10.5
    • Component/s: Optimizer
    • Labels:
      None
    • Environment:
      10.4.10-MariaDB on Windows 10 (64-bit)

      Description

      On MariaDB 10.4.10, the SELECT query below returns the wrong result:

      CREATE TABLE tabX (pk_colXA nvarchar(255) NOT NULL, colXB_n nvarchar(255), PRIMARY KEY(pk_colXA));
      CREATE TABLE tabZ (pk_colZA nvarchar(255) NOT NULL, pk_colZB nvarchar(255) NOT NULL, PRIMARY KEY(pk_colZA, pk_colZB));
      INSERT INTO tabX (pk_colXA, colXB_n) VALUES ('X1', 'a');
      INSERT INTO tabX (pk_colXA, colXB_n) VALUES ('X4', 'b');
      INSERT INTO tabZ (pk_colZA, pk_colZB) VALUES ('Z1', 'X1');
       
      -- The bug appears if derived_merge=on, as is the default.
      -- SET optimizer_switch='derived_merge=off';
      SELECT * FROM (
        SELECT pk_colXA, pk_colZA, constCol
        FROM tabX LEFT JOIN (
          SELECT pk_colZA, pk_colZB, 1 AS constCol FROM tabZ
        ) t1 ON pk_colZB = pk_colXA
      ) t2 ORDER BY pk_colXA, pk_colZA;
      

      The query should return two rows, where constCol=null in the second row. Instead, constCol is 1 in both result rows. This is incorrect, because the second row corresponds to an unmatched row in the left join. Doing 'SET optimizer_switch='derived_merge=off'' causes the result to change and become correct. Similarly, removing the outer "SELECT * FROM" level will cause the result to become correct. So this must be a query optimizer bug.

      This bug was triggered by the test suite of Ultorg, a visual query interface for relational databases ( https://twitter.com/ultorg ). This test suite also runs on Oracle, SQL Server, PostgreSQL, and MySQL. The bug was only present on MariaDB. In particular, MySQL 8.0.23 does not exhibit the bug. Older Oracle versions had a similar bug, Oracle bug #6653652, which could be similarly sidestepped by setting 'alter session set "_simple_view_merging" = false'.

        Attachments

          Activity

            People

            Assignee:
            psergei Sergei Petrunia
            Reporter:
            Bakke Eirik Bakke
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration