FB 5.0.0.1306 - unexpected results using LEFT JOIN with When

[nagualul]

The following select should return one record, but, using FB5, it doesn't. In FB4 it works perfectly. I am using version 5.0.0.1306.0 of firebird server.

select aa.id, aa.id_contract, aa.numep, aa.prenume, aa.cnp, aa.marca, aa.nrctr, aa.dela_ctr, aa.panala_ctr, ab.ID_USER, ab.NUME_USER, ab.DEPARTAMENT from sal_inperioada2('xxxx', '15.05.2024', 1) aa left join user_cnp(aa.cnp, '15.05.2024') ab on ab.CNP_USER = aa.cnp where ab.ID_USER = 'xxxx'

[dyemanov]

Please re-try with the any recent snapshot build and report back. It would also be helpful to know what do you mean by "unexpected results" - more than one record is returned, zero records are returned, runtime error is raised?

[nagualul]

Sorry, I expected one or more records, but FB5 return zero records. No error.

On the other hand, I don't know where to download "recent snapshot build". I downloaded the server from the official page. Can it be downloaded elsewhere? Thank you.

[dyemanov]

https://github.com/FirebirdSQL/snapshots/releases/tag/snapshot-v5.0-release

[dyemanov]

If the snapshot build does not help, please provide a reproducible test case (or real database, if possible). In the meantime, you may also try setting OuterJoinConversion = false in firebird.conf.

[nagualul]

Thanks for the reply. I will be able to do these tests during the next night. I'll be back with feedback...

[nagualul]

using "OuterJoinConversion = false in firebird.conf", the result is the same... (no records)

[dyemanov]

What about these queries that IMO should be equivalent:

select ...
from sal_inperioada2('xxxx', '15.05.2024', 1) aa
join user_cnp(aa.cnp, '15.05.2024') ab on ab.CNP_USER = aa.cnp
where ab.ID_USER = 'xxxx'

select ...
from sal_inperioada2('xxxx', '15.05.2024', 1) aa
cross join user_cnp(aa.cnp, '15.05.2024') ab
where ab.ID_USER = 'xxxx'

select ...
from sal_inperioada2('xxxx', '15.05.2024', 1) aa
cross join lateral ( select ID_USER, NUME_USER, DEPARTAMENT from user_cnp(aa.cnp, '15.05.2024') where ID_USER = 'xxxx' ) ab

[nagualul]

LEFT JOIN: Includes all rows from the left table and only matching rows from the right table (or NULL if there are no matches). CROSS JOIN: Includes all possible combinations of rows between the two tables.

In addition, from what I have seen, this error only appears when I select with left join from SP, not from tables... In the FB5 server where I took over the database used in the FB4 server, and I have hundreds of stored procedures, and I think I have other such situations. There can be many others like me from the wide world. I prepared a test database with such a scenario. test.zip

Try the following select: select aa.id, ab.CNP_USER, ab.ID_USER from sal_inperioada2('7DC51501-0DF2-45BE-93E5-382A541505DE', '15.05.2024') aa left join user_cnp(aa.cnp, '15.05.2024') ab on ab.CNP_USER = aa.cnp /where ab.ID_USER = '04B23787-2C7F-451A-A12C-309F79D6F13A'/ Then uncomment "Where..."

[dyemanov]

It returns one row for me (5.0.1.1392)

[dyemanov]

LEFT JOIN: Includes all rows from the left table and only matching rows from the right table (or NULL if there are no matches).

Your WHERE clause makes LEFT JOIN useless because it skips NULLs generated for unmatched records. Thus it has no difference from the INNER JOIN.

[nagualul]

It returns one row for me (5.0.1.1392)

Perfect. I will update the server during the night...

[nagualul]

I updated the FB5 server to version 5.0.0.1306.0 and now the problem has disappeared... Thank you Dimitri. I have been using FB for ..., about 15 years ... The database is almost 1T. So, it is important for me that such inconveniences do not occur when changing versions.

Beautiful day...