Description:
When performing a LEFT OUTER JOIN with a subquery that has a WHERE FALSE condition, the result should always exclude the joined data. However, the query produces different results depending on whether an ORDER BY clause is present, which is unexpected and incorrect.
How to repeat:
Create Test Table and Insert Sample Data:
CREATE TABLE `t1` (
`c1` int
);
INSERT INTO `t1` VALUES (2);
CREATE TABLE `t2` (
`c2` int
);
INSERT INTO `t2` VALUES (1);
Execute the Following Queries:
Query 1: With ORDER BY Clause
select
subq_1.c_0 as c_0,
subq_1.c_1 as c_1
from
(select
subq_0.c_0 as c_0,
subq_0.c_1 as c_1
from
(t1 as ref_0
left outer join (select
2 as c_0,
ref_1.c2 as c_1
from
t2 as ref_1
where false) as subq_0
on (false))
order by c_0 desc, c_1 asc) as subq_1
Result:
+-----+------+
| c_0 | c_1 |
+-----+------+
| 2 | NULL |
+-----+------+
1 row in set (0.00 sec)
this behavior is incorrect because the WHERE FALSE condition in the subquery should result in no rows being joined from the right table, and thus no rows should be returned in the outer query.
Query 2: Without ORDER BY Clause
select
subq_1.c_0 as c_0,
subq_1.c_1 as c_1
from
(select
subq_0.c_0 as c_0,
subq_0.c_1 as c_1
from
(t1 as ref_0
left outer join (select
2 as c_0,
ref_1.c2 as c_1
from
t2 as ref_1
where false) as subq_0
on (false))
) as subq_1
Result:
+-----+------+
| c_0 | c_1 |
+-----+------+
| NULL | NULL |
+-----+------+
1 row in set (0.00 sec)
When the ORDER BY clause is removed, the query correctly returns NULL for both c_0 and c_1 because no rows should be returned due to the WHERE FALSE condition.
Description: When performing a LEFT OUTER JOIN with a subquery that has a WHERE FALSE condition, the result should always exclude the joined data. However, the query produces different results depending on whether an ORDER BY clause is present, which is unexpected and incorrect. How to repeat: Create Test Table and Insert Sample Data: CREATE TABLE `t1` ( `c1` int ); INSERT INTO `t1` VALUES (2); CREATE TABLE `t2` ( `c2` int ); INSERT INTO `t2` VALUES (1); Execute the Following Queries: Query 1: With ORDER BY Clause select subq_1.c_0 as c_0, subq_1.c_1 as c_1 from (select subq_0.c_0 as c_0, subq_0.c_1 as c_1 from (t1 as ref_0 left outer join (select 2 as c_0, ref_1.c2 as c_1 from t2 as ref_1 where false) as subq_0 on (false)) order by c_0 desc, c_1 asc) as subq_1 Result: +-----+------+ | c_0 | c_1 | +-----+------+ | 2 | NULL | +-----+------+ 1 row in set (0.00 sec) this behavior is incorrect because the WHERE FALSE condition in the subquery should result in no rows being joined from the right table, and thus no rows should be returned in the outer query. Query 2: Without ORDER BY Clause select subq_1.c_0 as c_0, subq_1.c_1 as c_1 from (select subq_0.c_0 as c_0, subq_0.c_1 as c_1 from (t1 as ref_0 left outer join (select 2 as c_0, ref_1.c2 as c_1 from t2 as ref_1 where false) as subq_0 on (false)) ) as subq_1 Result: +-----+------+ | c_0 | c_1 | +-----+------+ | NULL | NULL | +-----+------+ 1 row in set (0.00 sec) When the ORDER BY clause is removed, the query correctly returns NULL for both c_0 and c_1 because no rows should be returned due to the WHERE FALSE condition.