Details
Description
|
|
drop table t0; |
create table t0 (vkey int); |
insert into t0 (vkey) values (5); |
|
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)); |
|
+----+ |
| 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; |
|