On 29/05/2020 16:43, Mark Rotteveel wrote: > I was just testing some claims in the Firebird Language Reference > against Firebird 3 and 4, and ran into the following oddity: > > The result of `null in (non-empty list or query)` and `null = any > (non-empty query)` should be `null` (see SQL:2016-2, 8.4 <in predicate> > and 8.9 <quantified comparison predicate>). >
It seems we have a problem with the generated BLR, that by the nature of restricted boolean expressions (before boolean data type was introduced) works, but does not work in the new cases. With the restricted expressions, the result of "null in (subquery)" does make a difference from FALSE only when it's prefixed with NOT, i.e. "not null in (subquery)". But this condition is also handled at BLR generation. In all others conditions (in an expression with AND/OR the bug does not have a difference. But the problem is not only in the select list. This example show the problem in the WHERE expression: -- no rows select 1 from rdb$database where (null in (select 'a' from RDB$DATABASE)) is null; -- one row select 1 from rdb$database where (null in (select 'a' from RDB$DATABASE)) = false; Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
