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

LP:752992 - Wrong results for a subquery with 'semijoin=on'

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • None
    • None
    • None

    Description

      Create and populate tables t1 and t2 with the following commands:

      CREATE TABLE t1 (pk INTEGER PRIMARY KEY, i INTEGER NOT NULL);
      INSERT INTO t1 VALUES (11,0);
      INSERT INTO t1 VALUES (12,5);
      INSERT INTO t1 VALUES (15,0);
      CREATE TABLE t2 (pk INTEGER PRIMARY KEY, i INTEGER NOT NULL);
      INSERT INTO t2 VALUES (11,1);
      INSERT INTO t2 VALUES (12,2);
      INSERT INTO t2 VALUES (15,4);

      The subquery
      SELECT * FROM t1
      WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON it.i=it.i WHERE t1.i);

      returns wrong results with the setting
      set optimizer_switch='semijoin=on'; :

      MariaDB [test]> set optimizer_switch='semijoin=on';
      Query OK, 0 rows affected (0.00 sec)

      MariaDB [test]> SELECT * FROM t1
      -> WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON it.i=it.i WHERE t1.i);
      -----+

      pk i

      -----+

      12 5
      12 5
      12 5

      -----+

      With the setting:
      set optimizer_switch='semijoin=off';
      the result is correct:

      MariaDB [test]> set optimizer_switch='semijoin=off';
      Query OK, 0 rows affected (0.00 sec)

      MariaDB [test]> SELECT * FROM t1
      -> WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON it.i=it.i WHERE t1.i);
      -----+

      pk i

      -----+

      12 5

      -----+
      1 row in set (0.01 sec)

      The bug is reproducible on both 5.3 tree and 5.3-subqueries-mwl90

      Attachments

        Activity

          People

            psergei Sergei Petrunia
            igor Igor Babaev
            Votes:
            0 Vote for this issue
            Watchers:
            0 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.