Skip to content

MIN/MAX aggregates may badly affect the join order in queries with mixed INNER/LEFT joins #8488

@TechAnton

Description

@TechAnton

I restored 2 databases from 1 backup. One is for Firebird 2.5, and the other is for Firebird 5.0.3 (the latest version).
Then I executed a query.

SELECT CON.ID
FROM INV_CARD C
LEFT JOIN INV_BALANCE M ON C.ID = M.CARDKEY
INNER JOIN GD_CONTACT CON ON M.CONTACTKEY = CON.ID
WHERE M.BALANCE <> 0
      AND C.USR$FA_INVCARDKEY > 0   

In Firebird 2.5, everything works correctly.
In Firebird 5.0.3, it ignores the condition (the primary key) and retrieves all the data. The result of the execution is correct, but the speed is tens of times slower.

Execution plan for Firebird 2.5:

Image

Execution plan for Firebird 5.0.3 (latest version):

Image

Additionally:

If I change the join to a LEFT JOIN, everything starts to work as it should.

Modified query:

SELECT CON.ID
FROM INV_CARD C
LEFT JOIN INV_BALANCE M ON C.ID = M.CARDKEY
LEFT JOIN GD_CONTACT CON ON M.CONTACTKEY = CON.ID
WHERE M.BALANCE <> 0
      AND C.USR$FA_INVCARDKEY > 0
      AND CON.ID IS NOT NULL

Execution plan for the modified query on Firebird 5.0.3:

Image

P.S. The indexes have been recomputed.

Metadata

Metadata

Assignees

Type

Projects

No projects

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions