Commented by: Roy Damman (royd)

Database backup where the testqueries can be performed to reproduce the problems.

Commented by: @asfernandes

AFAIK Firebird v3 is correctly.

Using parenthesis, you have:

SELECT *
FROM RELATIE a
join SETTING s
on (s.SETTINGID=1)
LEFT JOIN ADRESRELATIE b
ON (b.RELATIEID=a.RELATIEID and (b.ADRESTYPEID=s.ADRESTYPEIDMAIN))
LEFT JOIN (
TELEFOON d
JOIN TELEFOONRELATIE e
ON ((e.TELEFOONID=d.TELEFOONID) and (e.RELATIEID=a.RELATIEID)
and (e.TELEFOONTYPEID=s.TELEFOONTYPEIDMAIN))
)
ON (d.ADRESID=b.ADRESID)
;

And you can't refer to S alias inside that level of the join.

Commented by: Roy Damman (royd)

Why can't I refer to alias S on that level? In version 2.5.x and earlier it was possible. It's a major change in behaviour. And I think it is not SQL standard conform in Firebird 3.0. Please, can you explain me how I should rewrite query to conform to the new behaviour?

Commented by: Roy Damman (royd)

Correction description issue: Missing 'not': it does not give the desired resultset:

Commented by: @asfernandes

I let for Dmitry better explanation details, but the new behavior IS the SQL standard and AFAIK is documented in our Release Notes.

I understand that a block of joins should be self-evaluated, and in this case you're evaluating a block of joins using alias from a previous block.

Can't you use this:

SELECT *
FROM RELATIE a
join SETTING s
on (s.SETTINGID=1)
LEFT JOIN ADRESRELATIE b
ON (b.RELATIEID=a.RELATIEID and (b.ADRESTYPEID=s.ADRESTYPEIDMAIN))
LEFT JOIN (
TELEFOON d
JOIN TELEFOONRELATIE e
ON (e.TELEFOONID=d.TELEFOONID)
)
ON (d.ADRESID=b.ADRESID) and (e.RELATIEID=a.RELATIEID) and (e.TELEFOONTYPEID=s.TELEFOONTYPEIDMAIN)
;

Commented by: @asfernandes

This one may also be what you want:

SELECT *
FROM RELATIE a
join SETTING s
on (s.SETTINGID=1)
LEFT JOIN ADRESRELATIE b
ON (b.RELATIEID=a.RELATIEID and (b.ADRESTYPEID=s.ADRESTYPEIDMAIN))
LEFT JOIN TELEFOON d
ON (d.ADRESID=b.ADRESID)
LEFT JOIN TELEFOONRELATIE e
ON (e.TELEFOONID=d.TELEFOONID) and (e.TELEFOONTYPEID=s.TELEFOONTYPEIDMAIN)
;

Commented by: Roy Damman (royd)

Adriano, first, thank you for your efforts. The first query might work (I have to investigate it further), the second query gives wrong results. You are referring to the release notes. The release notes have indeed a note about joins. It refers to CORE2812 Prohibit any improper mixture of explicit and implicit joins. As CORE2812 states, it's about prohibiting mixture of explicit and implicit joins, not about explicit joins in explicit joins hierarchically. So the current behaviour is beyond the goals stated in CORE2812. I understand that with some optimizations and improvements, you cannot preserve all the (non-standard) options. But I don't understand to remove legacy features, only because they may be non-standard, which I still dispute in this case. Important for an OSS project is to broaden the user base. Scaring off legacy users, by removing legacy options, is the opposite way: The project will lose users and evangelists (which I was and hope to be in the future (mainly because of the Multi Version Concurrency and legacy with Interbase)). I like the speed improvements in version 3.0.x but I am very sad about the join changes. The example query I have given to reproduce the results is an enormous simplification of the very large, complex and dynamically build SQL statements as used in my applications.

Commented by: @dyemanov

The standard says that (A JOIN B) is a derived table, so it cannot reference any outer streams. Firebird v3 fixes the legacy misbehaviour. But it's not just about pleasing the standard. There are known cases when improper field references (be them in mixed implicit/explicit joins or in nested sub-joins as in your case) may produce errors or unexpected results. Whether you're lucky or not depends on the chosen optimizer plan, meaning some users may be affected and some others may not. We failed to fix those tricky cases and the easiest solution was to close that can of worms completely, by enforcing the standard requirements. Sad but true, there were no better choice. And we did it in a major version update which is expected to introduce incompatibilities, so that users have chances to find issues and invent some workarounds.