Details
-
Bug
-
Status: Stalled (View Workflow)
-
Major
-
Resolution: Unresolved
-
11.4.2, 10.6, 10.11, 11.4, 10.5(EOL), 11.2(EOL)
-
None
-
ubuntu20.04
-
Q4/2025 Server Maintenance, Q1/2026 Server Maintenance
Description
Setup:
drop table t0; |
create table t0 (vkey int); |
insert into t0 (vkey) values (5); |
Problem query:
select 1 as c0 |
from |
((select |
0 as c_0 |
from |
t0
|
) as subq_2 |
right outer join t0 as ref_6 |
on (subq_2.c_0 = ref_6.vkey )) |
where exists ( |
select |
1
|
from |
t0
|
where (subq_2.c_0 <> t0.vkey)); |
which produces this result:
+----+ |
| c0 |
|
+----+ |
| 1 |
|
+----+ |
1 row in set (0.00 sec) |
However, it should return an empty set. The analysis is as follows:
select * |
from |
((select |
0 as c_0 |
from |
t0
|
) as subq_2 |
right outer join t0 as ref_6 |
on (subq_2.c_0 = ref_6.vkey )); |
This results in:
+-----+------+ |
| c_0 | vkey |
|
+-----+------+ |
| NULL | 5 | |
+-----+------+ |
1 row in set (0.00 sec) |
The condition subq_2.c_0 <> t0.vkey evaluates to NULL <> 5, which should return NULL. Therefore, the result of the EXISTS clause should be an empty set, as EXISTS(...) returns False:
select |
1
|
from |
t0
|
where (NULL<> 5); |
--Empty set (0.00 sec) |
Thus, the original SQL statement should be equivalent to this one, which returns an empty set:
select 1 as c0 |
from |
((select |
0 as c_0 |
from |
t0
|
) as subq_2 |
right outer join t0 as ref_6 |
on (subq_2.c_0 = ref_6.vkey )) |
where False; |