Strange Behaviour in Null field = Not Null field evaluation

Peter Jones (BIDA) 86 Reputation points
2021-03-01T15:27:39.747+00:00

Hi All,

Today I saw something that I really didn't believe until I tested it and proved it to myself on both SQL Server 2019 SE and SQL Server 2016 developer edition.

Consider the SQL at the bottom of the post.

The datetime field is set on the zxt_dnn_sitelog_src view but it is null in the zxt_dnn_sitelog_m1 view.

This statement evaluates to 'notequal'.

,( case when (zxt_dnn_sitelog_src.datetime = zxt_dnn_sitelog_m1.datetime) then 'equal' else 'notequal' end ) testval

However, the following constraint does not catch this condition.

not ((zxt_dnn_sitelog_src.datetime = zxt_dnn_sitelog_m1.datetime) or ( zxt_dnn_sitelog_src.datetime is null and zxt_dnn_sitelog_m1.datetime is null))

I had to include this extra condition to detect this situation.

or     ((zxt_dnn_sitelog_src.datetime is null and zxt_dnn_sitelog_m1.datetime is not null) or ( zxt_dnn_sitelog_src.datetime is not null and zxt_dnn_sitelog_m1.datetime is null))

This was tested on 3 machines with two running SQL Server 2019 SE and one running SQL Server 2016 developer edition.

I am very surprised that this is the case. Can anyone please explain why the first condition does not expose the record on SQL Server?

Clearly the two columns are NOT equal. One has a date in it and one is null.

This code is generated delta detection code and the tool that generates it was developed back in 2008 on Netezza.

I am really interested in understanding how the two fields which are not equal, and SQL server knows they are not equal, do not evaluate properly in the where clause.

Thanks

Peter

select
 zxt_dnn_sitelog_src.pk_ss_number
,zxt_dnn_sitelog_src.pk_sitelogid
,zxt_dnn_sitelog_src.datetime
,zxt_dnn_sitelog_m1.datetime
,( case when (zxt_dnn_sitelog_src.datetime = zxt_dnn_sitelog_m1.datetime) then 'equal' else 'notequal' end ) testval
,zxt_dnn_sitelog_src.dk_portalid
,zxt_dnn_sitelog_src.dk_userid
,zxt_dnn_sitelog_src.referrer
--,zxt_dnn_sitelog_src.url
,zxt_dnn_sitelog_src.useragent
,zxt_dnn_sitelog_src.userhostaddress
,zxt_dnn_sitelog_src.userhostname
,zxt_dnn_sitelog_src.dk_tabid
,zxt_dnn_sitelog_src.dk_affiliateid
,2

from ibihs_c003_stg.dbo.zxt_dnn_sitelog                                         zxt_dnn_sitelog_src
     ,ibihs_c003_stg.dbo.zxt_dnn_sitelog_m9                                     zxt_dnn_sitelog_m1

where 1=1
and zxt_dnn_sitelog_src.pk_ss_number                                            = zxt_dnn_sitelog_m1.pk_ss_number
and zxt_dnn_sitelog_src.pk_sitelogid                                            = zxt_dnn_sitelog_m1.pk_sitelogid
and zxt_dnn_sitelog_src.pk_sitelogid = 1172189 
and 
( 
   not ((zxt_dnn_sitelog_src.datetime = zxt_dnn_sitelog_m1.datetime) or ( zxt_dnn_sitelog_src.datetime is null and zxt_dnn_sitelog_m1.datetime is null))
or     ((zxt_dnn_sitelog_src.datetime is null and zxt_dnn_sitelog_m1.datetime is not null) or ( zxt_dnn_sitelog_src.datetime is not null and zxt_dnn_sitelog_m1.datetime is null))
) 
; 
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,586 questions
0 comments No comments
{count} votes

Accepted answer
  1. Guoxiong 8,201 Reputation points
    2021-03-01T15:51:31.107+00:00

    You can not use a equal sign (=) to compare values between two fields if one or two fields may have NULL value. Try this:

    DECLARE @s1 varchar(10) = NULL;
    DECLARE @s2 varchar(10) = NULL;
    
    -- Incorrect: Return nothing
    IF @s1 = @s2 
    BEGIN
     SELECT 1;
    END
    
    -- Correct: Return 1
    IF ISNULL(@s1, '') = ISNULL(@s2, '') 
    BEGIN
     SELECT 1;
    END
    
    -- Correct: Return 1
    IF @s1 IS NULL AND @s2 IS NULL
    BEGIN
     SELECT 1;
    END
    

    From w3schools, It is not possible to test for NULL values with comparison operators, such as =, <, or <>.

    1 person found this answer helpful.

7 additional answers

Sort by: Most helpful
  1. Viorel 114K Reputation points
    2021-03-01T16:22:30.36+00:00

    Try one more known method:

    . . .
    and not exists ( select zxt_dnn_sitelog_src.datetime intersect select zxt_dnn_sitelog_m1.datetime )
    ;
    

  2. Tom Phillips 17,721 Reputation points
    2021-03-02T15:18:00.757+00:00

    Please see:

    https://learn.microsoft.com/en-us/sql/t-sql/language-elements/null-and-unknown-transact-sql?view=sql-server-ver15

    A null value is different from an empty or zero value. No two null values are equal. Comparisons between two null values, or between a null value and any other value, return unknown because the value of each NULL is unknown.

    0 comments No comments

  3. Peter Jones (BIDA) 86 Reputation points
    2021-03-03T13:17:52.747+00:00

    Hi Tom,

    yes, I am well aware of these discussions. We were having them in the 80s.

    Set Theory says ( Not Null Field = Null Field ) evaluates to NULL.

    However, as RDBMSs were evolving there was no support for "UNKNOWN" as the resolution of a comparison operator.

    For example what did "or NULL" mean as one of the conditions in a where clause?

    So way back then a NULL result for a comparison operator as defined by set theory was defaulted to false in the emerging RDBMSs.

    And that has been the way of it since the 80s.

    This is why I am surprised to see that SQL Server did not behave like all the other databases have behaved over the years.

    But it's good to know this is how SQL Server acts.

    The changes to the code generator was just a few lines of code and the author got back to me with an update yesterday.

    He is looking in to providing more options around the delta detection piece of the tool around nulls since this problem came up, so that's good!

    Best Regards

    Peter

    0 comments No comments

  4. Joe Celko 16 Reputation points
    2021-03-08T21:43:30.703+00:00

    > The datetime field [sic] is set on the zxt_dnn_sitelog_src view but it is NULL in the zxt_dnn_sitelog_m1 view. <<

    You might want to actually read a book on SQL. In this language, the term "field" refers to part of the column. The most common example given in a textbook is {year, month, day} fields in a date column

    > This statement evaluates to 'notequal'.

    ,( CASE WHEN (zxt_dnn_sitelog_src.datetime = zxt_dnn_sitelog_m1.datetime) THEN 'equal' ELSE 'not equal' END) AS test_val <<

    The CASE expression is not a statement. An expression has to return a single scalar value. Then you don't seem to know that "datetime" is a reserved keyword in T-SQL so it should never be used as a column name. Finally, you don't know how NULLs work! You cannot use equality to test for nulls. You have to use the "<expression> IS [NOT] NULL" predicate

    >

    Clearly the two columns are NOT equal. One has a DATE in it and one is NULL. <<

    Please get a book on SQL and read the chapter on three-valued logic. In this language. A search condition can return true, false, or unknown as logical values. All comparisons to NULL return unknown. This is not the same as true or full. It says in the values missing. We can't make any determination about it whatsoever