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

LP:943543 - A query with LEFT JOIN returns a wrong result in mariadb 5.3

    XMLWordPrintable

Details

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

    Description

      The following slightly modified test case for mysql bug #58456 produces a wrong result in mariadb-5.3.5

      CREATE TABLE t1 (
      col_int INT,
      col_int_key INT,
      pk INT NOT NULL,
      PRIMARY KEY (pk),
      KEY col_int_key (col_int_key)
      ) ENGINE=InnoDB;

      INSERT INTO t1 VALUES (NULL,1,1), (6,2,2), (5,3,3), (NULL,4,4);
      INSERT INTO t1 VALUES (1,NULL,6), (8,5,7), (NULL,8,8), (8,NULL,5);

      CREATE TABLE t2 (
      pk INT PRIMARY KEY
      ) ENGINE=InnoDB;

      INSERT INTO t2 VALUES (3), (8), (5);

      SELECT t1.pk
      FROM t2 LEFT JOIN t1 ON t2.pk = t1.col_int
      WHERE t1.col_int_key BETWEEN 5 AND 6
      AND t1.pk IS NULL OR t1.pk IN (5)
      ORDER BY pk;

      DROP TABLE t1,t2;

      The SELECT of the test case returns an empty set though the expected result is:
      ----

      pk

      ----

      5

      ----

      Attachments

        Activity

          People

            igor Igor Babaev (Inactive)
            igor Igor Babaev (Inactive)
            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.