I'm migrating my Delphi application from Firebird 2.5.9 to 5.0. This query works good on Firebird 2.5.9, but throws an exception on Firebird 5.
select A.ca1, A.ca2, B.cb1, B.cb2
from A, B left outer join C on C.cc1 = B.cc1 and B.cc2 = A.ca3
The exception is Unknown column A.ca3. But A.ca3 is a column of table A. It seems I can't use tabla A in the condition of the left outer join for the second and third tables. It makes no sense.
Note: This query is a simplification of the real query in order to focus only on the problem, that is, I can't use columns of tabla A in the condition of the left outer join.
The rules where changed in Firebird 3.0 to conform to the SQL standard. You're using a SQL-89 implicit cross join combined with a SQL-92 explicit outer join. Following SQL rules, the SQL-92 outer join is evaluated before the SQL-89 cross join. It's basically equivalent to A cross join (B left outer join C on ...), so the ON clause condition cannot reference columns from table A. Firebird 2.5 and older did this wrong. As a result, those versions could even produces "wrong" results, and the result could change depending on decisions made by the optimizer.
See Prohibit Edgy Mixing of Implicit/Explicit Joins and Support for Mixed-Syntax Joins is Gone in the Firebird 3.0 release notes.
You need to rewrite your query to use SQL-92 explicit joins all the way, or the condition B.cc2 = A.ca3 needs to be pushed down to the WHERE clause. The correct solution depends on which results need to be produced by the query.