Query: investigate result discrepancy between sqlserver and sqlite for compex query with multiple nested subqueries and max

[maumar]

Max_in_multi_level_nested_subquery, SqlServer and L2O return the same thing, Sqlite is different

[roji]

@maumar assuming this is about the recent translation of Math.Max into GREATEST... When when I dug a bit more into the nullability around GREATEST/LEAST (#32458), I discovered that on SQL Server/PG, these functions ignore NULL (do not propagate), whereas on SQLite/MariaDB they propagate NULL as usual (so the result of the function is NULL if anything argument is NULL). I was planning on diving a bit more into this - it means we need to tweak the recently-merged support a bit.

Could this be the source of the discrepancy?

[maumar]

I think it's something else, there is no LEAST/GREATEST in the SqlServer translation, but will investigate properly. Only filed it for tracking purposes to ublock #32542

[ranma42]

Apparently the issue was that Take(2) was applied without a .OrderBy().
The test was fixed in #33060; it can now be enabled on Sqlite as well (see #34128).