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

Incorrect results when subquery is materialized.

    XMLWordPrintable

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

              psergei Sergei Petrunia
              juan.vera Juan
              Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.