Controls the interpretation of NULL values.

Allowed values

On, Off

Default

On

Remarks

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.