Optimizer enhancement: Auto-convert LEFT JOIN to INNER JOIN for null-rejecting WHERE predicates

[gitnol]

Optimizer: Auto-convert LEFT JOIN to INNER JOIN when WHERE clause contains null-rejecting predicates

Problem

Firebird's optimizer does not recognize when a WHERE clause effectively converts a LEFT JOIN into an INNER JOIN. When filtering on non-nullable columns from the right table (e.g., B.ID = 1275), NULL rows from the outer join are eliminated, making the query semantically equivalent to an INNER JOIN. However, the optimizer still uses the less efficient outer join execution plan.

Impact

This causes severe performance degradation:

• Current behavior (LEFT JOIN): ~5 seconds
• Manual rewrite (INNER JOIN): <0.1 seconds
• Performance difference: >50x slower

This particularly affects:

• ORM-generated queries that default to LEFT JOINs
• Legacy codebases with suboptimal join types
• Ad-hoc reporting queries

Reproduction

-- Slow execution (~5s) - Optimizer uses outer join plan
-- PLAN JOIN (JOIN (JOIN (JOIN (A B NATURAL, A C INDEX (PK_BSM), A A INDEX (UNQ1_BSMZPO)), A D NATURAL), A E INDEX (PK_BMAND)), B INDEX (PK_BSM))

SELECT A.ID, A.BSM_MASKENKEY AS MASKENKEY
FROM V_BSMZPO A
LEFT JOIN BSM B ON A.BSMZ_BSM_ID_LINKKEY = B.ID
WHERE B.ID = 1275  -- This predicate eliminates NULLs, making it an INNER JOIN
  AND A.RUECKERF_SUMME < A.TAGIST - 0.25
  AND A.DATUM BETWEEN CURRENT_DATE - 40 AND CURRENT_DATE - 5
  AND A.TAGIST > 0

-- Fast execution (<0.1s) - Same query with explicit INNER JOIN
-- PLAN JOIN (JOIN (JOIN (JOIN (A C INDEX (PK_BSM), A B INDEX (REF_BSMZ_BSM_LINKKEY), A A INDEX (UNQ1_BSMZPO)), A D NATURAL), A E INDEX (PK_BMAND)), B INDEX (PK_BSM))

SELECT A.ID, A.BSM_MASKENKEY AS MASKENKEY
FROM V_BSMZPO A
INNER JOIN BSM B ON A.BSMZ_BSM_ID_LINKKEY = B.ID
WHERE B.ID = 1275
  AND A.RUECKERF_SUMME < A.TAGIST - 0.25
  AND A.DATUM BETWEEN CURRENT_DATE - 40 AND CURRENT_DATE - 5
  AND A.TAGIST > 0

Statistics

• V_BSMZPO: This is a view with 531534 rows right now.
• BSM: This ist a table with 5244 rows right now.

If you require further information, please let me know.

Proposed Solution

The optimizer should detect null-rejecting predicates in the WHERE clause and internally rewrite the join. A predicate is null-rejecting when:

• It references a column from the outer-joined table
• It cannot evaluate to TRUE when the column is NULL
• Examples: B.column = value, B.column > value, B.column IS NOT NULL

This optimization is already implemented in other databases (PostgreSQL, MySQL, Oracle, SQL Server).

Environment

• Firebird 4.0.3 (Currently, I am unable to test newer versions due to some issues that I cannot resolve.)
• Windows Server 2022

References

• Similar optimization in PostgreSQL: Join Elimination
• SQL:1999 standard allows this transformation when semantically equivalent

[aafemt]

Firebird's optimizer does not recognize when a WHERE clause effectively converts a LEFT JOIN into an INNER JOIN.

In versions 5+ it does.

[gitnol]

Good to know... bad for my environment ^^
(I have a mandatory product which is syncing data to MSSQL from Firebird and can not handle anything above 4.0.3 ... therefore i have to solve that first )

[mrotteveel]

See also issue #6992 (the Firebird 5.0 release notes only mention it in the Summary of New Features and it has no detailed section of its own).