Example:
SELECT *
FROM T1 LEFT JOIN T2 ON T1.ID = T2.ID
WHERE T2.FIELD1 = 0
In this case the condition T2.FIELD1 = 0 effectively removes all the "fake NULL" rows of T2, so the result is the same as for the INNER JOIN. However, the optimizer is forced to use the T1->T2 join order while T2->T1 could also be considered. Also, the optimizer cannot use hash/merge algorithms for joining. It makes sense to detect this case during join processing and internally replace LEFT with INNER before optimization starts.
This is primarily intended to improve "ad hoc" and machine-generated (e.g. ORM) queries. However, it may also somewhat (from the performance POV) break user applications, as many people used to "hint" the optimizer by writing LEFT intentionally, exactly to "pin" the manually chosen join order instead of relying on the optimizer. Usually, the join is performed by the PK/FK reference and the fake WHERE condition explicitly checks that PK (which is known to never be NULL by its own) to produce the INNER result.
The proposed improvement, however, may work around this compatibility issue. In particular, checks for NULL, e.g. WHERE T2.ID IS NOT NULL for the join example above, would not transform LEFT into INNER, because theoretically they may also be used for checking fake NULLs inside T2 after LEFT JOIN. However, regular comparisons that ignore NULLs by their nature, will cause the LEFT->INNER transformation. So only hints using very artificial checks like T2.ID > 0 will be affected.
This patch has been tested in production for a while and nobody stepped on problems yet. However, in order to minimize possible risks, perhaps it should be added to the major release (v5) only, or we could provide a per-database compatibility switch in firebird.conf.
Example:
In this case the condition
T2.FIELD1 = 0effectively removes all the "fake NULL" rows of T2, so the result is the same as for the INNER JOIN. However, the optimizer is forced to use the T1->T2 join order while T2->T1 could also be considered. Also, the optimizer cannot use hash/merge algorithms for joining. It makes sense to detect this case during join processing and internally replace LEFT with INNER before optimization starts.This is primarily intended to improve "ad hoc" and machine-generated (e.g. ORM) queries. However, it may also somewhat (from the performance POV) break user applications, as many people used to "hint" the optimizer by writing LEFT intentionally, exactly to "pin" the manually chosen join order instead of relying on the optimizer. Usually, the join is performed by the PK/FK reference and the fake WHERE condition explicitly checks that PK (which is known to never be NULL by its own) to produce the INNER result.
The proposed improvement, however, may work around this compatibility issue. In particular, checks for NULL, e.g.
WHERE T2.ID IS NOT NULLfor the join example above, would not transform LEFT into INNER, because theoretically they may also be used for checking fake NULLs inside T2 after LEFT JOIN. However, regular comparisons that ignore NULLs by their nature, will cause the LEFT->INNER transformation. So only hints using very artificial checks likeT2.ID > 0will be affected.This patch has been tested in production for a while and nobody stepped on problems yet. However, in order to minimize possible risks, perhaps it should be added to the major release (v5) only, or we could provide a per-database compatibility switch in firebird.conf.