This option is implemented primarily for Transact-SQL (Adaptive Server Enterprise) compatibility. The ansinull option affects
the results of comparison predicates with NULL constants, and also affects warnings issued for grouped queries over NULL values.
With ansinull set to On, ANSI three-valued logic is used for all comparison predicates in a WHERE or HAVING clause, or in
an On condition. Any comparisons with NULL using = or != evaluate to unknown.
Setting ansinull to Off means that SQL Anywhere uses two-valued logic for the following four conditions:
expr = NULL
expr != NULL
expr = @var // @var is a procedure variable, or a host variable
expr != @var
In each case, the predicate evaluates to either true or false—never unknown. In such comparisons, the NULL value is treated
as a special value in each domain, and an equality (=) comparison of two NULL values yields true. The expression expr must be a relatively simple expression, referencing only columns, variables, and literals; subqueries and functions are not
permitted.
With ansinull set to On, the evaluation of any aggregate function, except COUNT(*), on an expression that contains at least
one NULL value, may generate a warning (SQLSTATE=01003). See Null value eliminated in aggregate function.
With ansinull set to Off, this warning does not appear.
Note
Setting ansinull to Off affects only WHERE, HAVING, or ON predicates in SELECT, UPDATE, DELETE, and INSERT statements. The
semantics of comparisons in a CASE or IF statement, or in IF expressions, are unaffected.
Adaptive Server Enterprise 12.5 introduced a change in the behavior of LIKE predicates with a NULL pattern string when ansinull
is set to Off. In SQL Anywhere, LIKE predicates remain unaffected by the setting of ansinull.
Allowed values