Hi! As I stated previously in this thread, IN/ANY/ALL and its negated form works only in simple (existent before the boolean data type was introduced) expressions.
That happens because Firebird returns FALSE for some queries that should return NULL. There is special handling for NOT (IN/ANY/ALL expression), so this NOT <incorrect FALSE> still result in FALSE, making all simple expression work, as a <incorrect FALSE> embedded in AND, OR and NOT expressions in a WHERE clause will have the same treatment as NULL. To fix this case (have correct NULL result in value expressions) and some others with indexes following the old code appeared to be not simple, and also, the code used some trick layering (AnyBoolean) which I tought was not good to invest, so I did various changes in the way it works. It needs a careful review. Pull request: https://github.com/FirebirdSQL/firebird/pull/269 I ran Mark's test for CORE-6322 (and variant with index) and TCS. Would like to have the full test suite ran on it too. In the first commit there is only the necessary changes (and would be backported to v3 if approved). The second commit refactors req_null as a nice to have and is not for v3. I suppose it now give correct results and still use indexes when possible (thanks ConditionalStream). Now the main engine path for IN/ANY/ALL is ANY. ALL is early converted to NOT ANY. Internally it converts "val = any(select col from table where condition)" so the record source is "from table where condition and (col = val or col is null or val is null)". FilteredStream does not have special logic for them anymore. The ANY/ALL logic is now in RseBoolNode. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
