ASA Database Administration Guide
  Database Options
    Alphabetical list of options

ANSINULL option [compatibility]

Function 

Controls the interpretation of NULL values.

Allowed values 

ON, OFF

Default 

ON

Description 

This option is implemented primarily for Transact-SQL (Adaptive Server Enterprise) compatibility. ANSINULL affects the results of comparison predicates with NULL constants, and also affects warnings issued for grouped queries over NULL values.

With ANSINULL 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 Adaptive Server 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 will yield 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 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 OFF, this warning does not appear.

Limitations 

• 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 behavior for LIKE predicates with a NULL pattern string when ANSINULL is OFF. In Adaptive Server Anywhere, LIKE predicates remain unaffected by the setting of ANSINULL.