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

Incorrect results when subquery is materialized.

    Details

      Description

      When set optimizer_switch='materialization=on', the following query returns incorrect results. The problem starts with version 10.2.10 (results are correct when materialization=on on versions 10.2.9 and below) and continues through 10.3.6

      set optimizer_switch='materialization=off';
       
      SELECT touter.id
      FROM touter
      INNER JOIN tinner1
          ON touter.id = tinner1.id
      WHERE touter.type = 2
      AND touter.id IN (
          SELECT tref.ref_id
          FROM tref
          INNER JOIN tinner1
              ON tref.id = tinner1.id
          WHERE tref.type = 'incident'
      );
       
      +----------------------------------+
      | id                               |
      +----------------------------------+
      | ffffffb61e68ffffff2302003d4f2b49 |
      | cb9763cda2dd48bbc751130045aa3ea9 |
      | cb97631ea2dd48bbc7511300d96afaa9 |
      | ce61030eb96d45a3d71002008d0a4f77 |
      +----------------------------------+
      4 rows in set (0.00 sec)
       
      set optimizer_switch='materialization=on';
       
      SELECT touter.id
      FROM touter
      INNER JOIN tinner1
          ON touter.id = tinner1.id
      WHERE touter.type = 2
      AND touter.id IN (
          SELECT tref.ref_id
          FROM tref
          INNER JOIN tinner1
              ON tref.id = tinner1.id
          WHERE tref.type = 'incident'
      );
       
      +----------------------------------+
      | id                               |
      +----------------------------------+
      | ce61030eb96d45a3d71002008d0a4f77 |
      +----------------------------------+
      1 row in set (0.00 sec)
      

      The attached SQL file will create the database 'matbug', populate it, and run the test query with the materialization switch both on and off.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                psergey Sergei Petrunia
                Reporter:
                juan.vera Juan
              • Votes:
                0 Vote for this issue
                Watchers:
                6 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: