Hi,
I’ve some problems with queries having left join with subselect.
Let me explain with a simple example.
Let dummy(a int) and dummy2(a int) two tables.
Let’s insert the value 0 into the table dummy.
mysql> select * from dummy;
+------+
| a |
+------+
| 0 |
+------+
The following query returns correctly the value
mysql> select * from dummy a left join dummy2 as b on a.a=b.a;
+------+------+
| a | a |
+------+------+
| 0 | NULL |
+------+------+
while this one
mysql> select * from dummy a left join (select a from dummy2) as b on a.a=b.a;
Empty set (0.03 sec)
gives empty set, that’s should be wrong.
I think that this could be a bug.
I did implement the following workaround
mysql> select * from dummy a left join (select -1 as a from dummy union all select a from dummy2) as b on a.a=b.a;
+------+------+
| a | a |
+------+------+
| 0 | NULL |
+------+------+
I’ve tried the same queries with another dbms and it works.
Best greetings,
Salvo Nicotra
