Controls the interpretation of NULL values.
On, Off
On
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. Note that 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 the warning null value eliminated in aggregate function (SQLSTATE=01003). With ansinull set to Off, this warning does not appear.
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.
| Discuss this page in DocCommentXchange. Send feedback about this page using email. |
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 |