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

CLONE - wrong result when doing left join with constant false on condition

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Duplicate
    • 10.4.22, 10.5.13, 10.6.5, 10.7.1
    • N/A
    • N/A

    Description

      The following query returns wrong result:

      CREATE TABLE t1(
      K1 INT PRIMARY KEY,
      Name VARCHAR(15)
      );
      INSERT INTO t1 VALUES
      (1,'T1Row1'), (2,'T1Row2');
      CREATE TABLE t2(
      K2 INT PRIMARY KEY,
      K1r INT,
      rowTimestamp DATETIME,
      Event VARCHAR(15)
      );
       
      INSERT INTO t2 VALUES
      (1, 1, '2015-04-13 10:42:11' ,'T1Row1Event1'),
      (2, 1, '2015-04-13 10:42:12' ,'T1Row1Event2'),
      (3, 1, '2015-04-13 10:42:12' ,'T1Row1Event3');
       
      SELECT t1a.*, t2a.*,
      t2i.K2 AS K2B, t2i.K1r AS K1rB,
      t2i.rowTimestamp AS rowTimestampB, t2i.Event AS EventB
      FROM
      t1 t1a JOIN t2 t2a ON t2a.K1r = t1a.K1
      LEFT JOIN
      ( t1 t1i LEFT JOIN t2 t2i ON t2i.K1r = t1i.K1)
      ON (t1i.K1 = 1) AND
      (((t2i.K1r = t1a.K1 AND t2i.rowTimestamp > t2a.rowTimestamp ) OR
      (t2i.rowTimestamp = t2a.rowTimestamp AND t2i.K2 > t2a.K2))
      OR (t2i.K2 IS NULL))
      WHERE
      t2a.K1r = 1 AND t2i.K2 IS NULL;
      MariaDB [test]> SELECT t1a.*, t2a.*, t2i.K2 AS K2B, t2i.K1r AS K1rB, t2i.rowTimestamp AS rowTimestampB, t2i.Event AS EventB FROM t1 t1a JOIN t2 t2a ON t2a.K1r = t1a.K1 LEFT JOIN ( t1 t1i LEFT JOIN t2 t2i ON t2i.K1r = t1i.K1) ON (t1i.K1 = 1) AND (((t2i.K1r = t1a.K1 AND t2i.rowTimestamp > t2a.rowTimestamp ) OR (t2i.rowTimestamp = t2a.rowTimestamp AND t2i.K2 > t2a.K2)) OR (t2i.K2 IS NULL)) WHERE t2a.K1r = 1 AND t2i.K2 IS NULL;
      +----+--------+----+------+---------------------+--------------+------+------+---------------+--------+
      | K1 | Name   | K2 | K1r  | rowTimestamp        | Event        | K2B  | K1rB | rowTimestampB | EventB |
      +----+--------+----+------+---------------------+--------------+------+------+---------------+--------+
      |  1 | T1Row1 |  3 |    1 | 2015-04-13 10:42:12 | T1Row1Event3 | NULL | NULL | NULL          | NULL   |
      +----+--------+----+------+---------------------+--------------+------+------+---------------+--------+
      1 row in set (0.000 sec)
      

      it should be:

      mysql> SELECT t1a.*, t2a.*,
          -> t2i.K2 AS K2B, t2i.K1r AS K1rB,
          -> t2i.rowTimestamp AS rowTimestampB, t2i.Event AS EventB
          -> FROM
          -> t1 t1a JOIN t2 t2a ON t2a.K1r = t1a.K1
          -> LEFT JOIN
          -> ( t1 t1i LEFT JOIN t2 t2i ON t2i.K1r = t1i.K1)
          -> ON (t1i.K1 = 1) AND
          -> (((t2i.K1r = t1a.K1 AND t2i.rowTimestamp > t2a.rowTimestamp ) OR
          -> (t2i.rowTimestamp = t2a.rowTimestamp AND t2i.K2 > t2a.K2))
          -> OR (t2i.K2 IS NULL))
          -> WHERE
          -> t2a.K1r = 1 AND t2i.K2 IS NULL;
      +----+--------+----+------+---------------------+--------------+------+------+---------------+--------+
      | K1 | Name   | K2 | K1r  | rowTimestamp        | Event        | K2B  | K1rB | rowTimestampB | EventB |
      +----+--------+----+------+---------------------+--------------+------+------+---------------+--------+
      |  1 | T1Row1 |  1 |    1 | 2015-04-13 10:42:11 | T1Row1Event1 | NULL | NULL | NULL          | NULL   |
      |  1 | T1Row1 |  2 |    1 | 2015-04-13 10:42:12 | T1Row1Event2 | NULL | NULL | NULL          | NULL   |
      |  1 | T1Row1 |  3 |    1 | 2015-04-13 10:42:12 | T1Row1Event3 | NULL | NULL | NULL          | NULL   |
      +----+--------+----+------+---------------------+--------------+------+------+---------------+--------+
      3 rows in set (0.01 sec)
      
      

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              yutellite yuweixing
              Votes:
              0 Vote for this issue
              Watchers:
              1 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.