[MDEV-27630] CLONE - wrong result when doing left join with constant false on condition Created: 2022-01-26  Updated: 2022-01-26  Resolved: 2022-01-26

Status: Closed
Project: MariaDB Server
Component/s: N/A
Affects Version/s: 10.4.22, 10.5.13, 10.6.5, 10.7.1
Fix Version/s: N/A

Type: Bug Priority: Critical
Reporter: yuweixing Assignee: Unassigned
Resolution: Duplicate Votes: 0
Labels: innodb

Issue Links:
Duplicate
duplicates MDEV-27624 Wrong result for nested left join usi... Closed

 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)



 Comments   
Comment by yuweixing [ 2022-01-26 ]

I drop the primary key of t1 and t2, you can see the plan has changed to table scan, the result are still 3 rows in MySQL.
And the result comes wright to 3 rows in MariaDB when I drop all the primary keys.

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.K
 
1r = 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 desc 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 ANN
 
D t2i.K2 IS NULL;
 
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------------------+
 
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                                          |
 
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------------------+
 
|  1 | SIMPLE      | t1a   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |    50.00 | Using where                                                    |
 
|  1 | SIMPLE      | t2a   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where; Using join buffer (Block Nested Loop)             |
 
|  1 | SIMPLE      | t1i   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | Using where; Using join buffer (Block Nested Loop)             |
 
|  1 | SIMPLE      | t2i   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where; Not exists; Using join buffer (Block Nested Loop) |
 
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------------------+
 
4 rows in set, 1 warning (0.00 sec)
 
 
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.K
 
1r = 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 t2ii
 
.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.00 sec)

Comment by yuweixing [ 2022-01-26 ] 

MariaDB [test]> alter table t1 drop primary key;
 
Query OK, 2 rows affected (0.046 sec)              
Records: 2  Duplicates: 0  Warnings: 0
 
 
MariaDB [test]> alter table t2 drop primary key;
 
Query OK, 3 rows affected (0.022 sec)              
Records: 3  Duplicates: 0  Warnings: 0
 
 
MariaDB [test]> desc 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;
 
+------+-------------+-------+------+---------------+------+---------+------+------+--------------------------------------------------------------------+
 
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                                              |
 
+------+-------------+-------+------+---------------+------+---------+------+------+--------------------------------------------------------------------+
 
|    1 | SIMPLE      | t1a   | ALL  | NULL          | NULL | NULL    | NULL | 2    | Using where                                                        |
 
|    1 | SIMPLE      | t2a   | ALL  | NULL          | NULL | NULL    | NULL | 3    | Using where; Using join buffer (flat, BNL join)                    |
 
|    1 | SIMPLE      | t1i   | ALL  | NULL          | NULL | NULL    | NULL | 2    | Using where; Using join buffer (incremental, BNL join)             |
 
|    1 | SIMPLE      | t2i   | ALL  | NULL          | NULL | NULL    | NULL | 3    | Using where; Not exists; Using join buffer (incremental, BNL join) |
 
+------+-------------+-------+------+---------------+------+---------+------+------+--------------------------------------------------------------------+
 
4 rows in set (0.016 sec)
 
 
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 |  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.010 sec)
 
 
MariaDB [test]>

Comment by yuweixing [ 2022-01-26 ] 

MariaDB [test]> alter table t1 add primary key(k1);
 
Query OK, 0 rows affected (0.023 sec)
 
Records: 0  Duplicates: 0  Warnings: 0
 
 
MariaDB [test]> alter table t2 add primary key(k2);
 
Query OK, 0 rows affected (0.007 sec)
 
Records: 0  Duplicates: 0  Warnings: 0
 
 
MariaDB [test]> desc 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;
 
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------------------------+
 
| id   | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra                   |
 
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------------------------+
 
|    1 | SIMPLE      | t1a   | const | PRIMARY       | PRIMARY | 4       | const | 1    |                         |
 
|    1 | SIMPLE      | t2a   | ALL   | NULL          | NULL    | NULL    | NULL  | 3    | Using where             |
 
|    1 | SIMPLE      | t1i   | const | PRIMARY       | PRIMARY | 4       | const | 1    | Using index             |
 
|    1 | SIMPLE      | t2i   | ALL   | NULL          | NULL    | NULL    | NULL  | 3    | Using where; Not exists |
 
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------------------------+
 
4 rows in set (0.001 sec)
 
 
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.001 sec)

Generated at Thu Feb 08 09:54:23 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.