🚀 go-pugleaf

Hi Dan,
behaviour of expressions containing nulls depends on current value of
ANSI_NULLS session setting.
If it is enabled nulls behave in the way you have noticed (SQL-92
conformance!!).
After switching it off, your queries will start to work according to
your expectations...

Regards,
Grzegorz Rymarz



Dan G wrote:

> Hello all,
>
> Take a moment and read test the below puzzle (could be a good lesson
> for
> some of us).
> Compare the results/performance (and query plans) and respond to the
> questions:
> Running on SQL 7 (SP1).
>
> -- case A) <> NULL examples.
>
> select
> count (*)
> from
> pos
> where
> cntc_name<>NULL
>
> select
> count (*)
> from
> pos
> where
> cntc_name NOT IN (NULL)
>
> select
> count (*)
> from
> pos
> where
> cntc_name IS NOT NULL
>
> Questions:
> A) why the execution plans are the same (same predicate) but the
> results are
> different (BTW last one is the correct one !).
> MS SQL 7  shouldn't allow the first two if they give wrong results
> ;-(.
>
> -- B) = NULL
>
> select
> count (*)
> from
> pos
> where
> cntc_name=NULL
>
> select
> count (*)
> from
> pos
> where
> cntc_name IN (NULL)
>
> select
> count (*)
> from
> pos
> where
> cntc_name IS NULL
>
> Questions:
> B) Same question as above + why the filtering is removed from the
> execution
> plan ?.
>
> All the best,
>
> Dan.