Strange Behaviour in Null field = Not Null field evaluation

Question

Strange Behaviour in Null field = Not Null field evaluation

Peter Jones (BIDA) 81

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))
) 
;

Answer 1

Guoxiong 8,076

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 <>.

Peter Jones (BIDA) 81 Reputation points

2021-03-01T19:10:21.587+00:00
Hi Guoxiong ,

Strange. I have never heard of such a restriction and I have been dealing with relational databases nearly 40 years.

I tested this statement and it works.

So the equality test works when done as a case statement. It is likely defaulting to 0 for the else.

I am going to test on some other databases and see what they do.

Best Regards

Peter

or not ((1 = ( case when (zxt_dnn_sitelog_src.datetime = zxt_dnn_sitelog_m1.datetime) then 1 else 0 end )) or ( zxt_dnn_sitelog_src.datetime is null and zxt_dnn_sitelog_m1.datetime is null))
SM 16 Reputation points

2021-03-01T21:13:41.567+00:00

Odd that you have not discovered in 40 years that NULL means unknown and you cannot logically compare any unknown value to anything. In your example, the first comparison returns UNKNOWN if either column is null so the ELSE part of the case expression is returned. FWIW it is WAY past time to stop using old-style joins.
Peter Jones (BIDA) 81 Reputation points

2021-03-02T13:23:16.253+00:00

Hi SM,

it's because other databases resolve the query correctly.

I had a friend test on DB2 UDB and it resolves the query properly.

So I am testing on some other databases to see what it does on those databases.

With respect to NULLS?

Yes I am well aware of null arithmetic.

Most databases do not allow a comparison operator to resolve to "unknown".

They resolve to true or false.

When in doubt they resolve to false.

On the old join style?

Sure, there is a new join style. So?

The generated code comes from an ETL tool a pal of mine wrote.

The ETL software is free and open source so anyone can update it.

http://www.instantbi.com/freebies/downloads/

In other areas of the generated code it uses the new join functions for such things as left joins.

The much more important feature is this ETL tool cuts ETL development time by 50% or more.

Even if you go into production with Informatica/SSIS etc.

I wish you a good day!
Dan Guzman 7,306 Reputation points

2021-03-02T13:53:31.3+00:00

Most databases do not allow a comparison operator to resolve to "unknown".

All ISO SQL standard compliant relational databases must adhere to 3-value results TRUE, FALSE, UNKNOWN) for comparison operator results. NULL predicates (IS NULL or IS NOT NULL) are a pain but blame E. F. Codd.

Below is an excerpt from the DB2 LUW doc:

Two strings are equal if they are both empty or if all corresponding bytes are equal. If either operand is null, the result is unknown.
Peter Jones (BIDA) 81 Reputation points

2021-03-03T13:23:57.587+00:00

Hi Dan,

that's interesting because I had a friend test on DB2 and it processed the query as is properly.

Perhaps there is an option to turn on this feature?

I put up a comment to Tom mentioning that this behaviour has been standard since the 80s.

So perhaps many databases support the old way as an option.

But good for me to learn that this is how they are able to resolve this now. :-)

Best Regards

Peter
Peter Nolan 1 Reputation point

2021-03-06T11:44:08.33+00:00

Hi SM,
I am the guy Peter Jones is talking about.

The software he is using is a version of my software. There are many people using versions of my software now.

And yes, he is correct. I had not seen that behaviour in databases either and I WROTE a relational database back in 1985! LOL!

Back then the people writing RDBMSs had the discussion how (null field = not null field) should evaluate since we could not call it "null" which is what relational theory says it should be. There is a difference between "theory" and "the real world".

So yes, I was also surprised to see this behaviour. Ok?

Best Regards

Peter

Answer 2

Viorel 95,071

Try one more known method:

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

Peter Jones (BIDA) 81 Reputation points

2021-03-01T19:07:03.68+00:00

Hi Viorel,
this sort of thing is not suitable because this code is for delta detection large tables with millions of rows and sometimes up to 50-60 columns.

That is a lot of fields to test for changes so it needs to be efficient.

Best Regards

Peter
Erland Sommarskog 78,581 Reputation points MVP

2021-03-01T22:50:09.03+00:00

The solution with INTERSECT is much more compact than the alternatives you had where you compared explicitly for IS NULL. Wrapping with isnull is precarious, because you need to find some magic value to map the NUJLL to. Furthermore, isnull kills any index. That does not happen with INTERSECT - the optimizer is able to handle this well, it has a special operator which is not exposed in the languages.

(The ANSI standard has the operator IS [NOT] DISTINCT FROM for comparisons which covers NULL. But INTERSECT works well in SQL Server.)
Peter Jones (BIDA) 81 Reputation points

2021-03-02T13:36:27.747+00:00

Hi Erland,
the code that is being generated is to perform delta detection on sizable tables.

So there could be 100+ fields being checked to see if any of them changed.

So the way most databases will resolve that is to select the smaller table and then compare row for row, field for field, to see if any field has changed.

Mostly what is being done is that the updated / inserted records from the operational system are sent into the delta detection processing and compared with the prior version to detect inserts and updates.

Those inserts and updates are then forwarded to the staging area.

Where we allow for delete detection we usually have a time restriction on the period over which deletes will be detected so that we do not scan years of data where no deletes will be done.

The code has to be able to run on any data warehouse database, not just sql server. We happen to be using SQL server for our work on this client.

These responses are short so I will do another one....
Peter Jones (BIDA) 81 Reputation points

2021-03-02T13:48:37.447+00:00

Hi Erland,

in our case we have built a staging area for a Navision client and we maintain their data warehouse on SQL Server.

We used this tool build the delta detection for the staging area for Navision in just 4 days. (quote below)

Navision has a timestamp field we used for comparison for delta detection so we never saw this problem until we worked on another set of data that had nulls in fields to be checked. It was an "end date" that went from null to not null that we found the problem with.

I mean, how often does it happen that just one field on a row changes from null to not null and no other field changes?

We are actually developing a new BI solution for Navision. It's an interesting project.

"We were able to build a completely new instance of the staging area in just 4 working days. This was 1,867 tables and 35,591 fields from a Navision 2009 instance."

www.businessintelligence-da.com/2019/07/28/bida0007-navision-staging-area-construction-details/
Viorel 95,071 Reputation points

2021-03-02T14:53:47.587+00:00
this sort of thing is not suitable because this code is for delta detection large tables with millions of rows and sometimes up to 50-60 columns.

If you must compare 50-60 columns, not just one datetime, then you can try a single expression like this:

not exists (select a1, a2, a3, … a50 intersect select b1, b2, b3, … b50)

which seems simpler than writing 50-60 expressions using your current approach, based on ‘or’ which is often not recommended.
Erland Sommarskog 78,581 Reputation points MVP

2021-03-02T22:22:37.84+00:00
Using INTERSECT is absolutely the most efficient way to make these comparisons, both in terms of performance and conciseness of code, on SQL Server.

But since Peter is targeting many different platforms, I am not sure how good bet this is. Not all RBDMSs may implement the INTERSECT operator. And how it is actually implemented can be different from product to product.

Then again, Peter needs to handle NULL correctly with the three-valued logic of SQL. And, yes, if you compare many columns enough, there will be changes from NULL to non-NULL or vice versa.

I don't have access to DB2, but I ran this on Postgres:

CREATE TABLE duo (a int NULL, b int NULL); INSERT INTO duo(a, b) VALUES(1, NULL); SELECT * FROM duo WHERE a = b; SELECT * FROM duo WHERE NOT (a = b); DROP TABLE duo;

None of the SELECT returned any rows. Nor should they.
Peter Jones (BIDA) 81 Reputation points

2021-03-03T13:07:33.377+00:00

Hi Viorel, I will take a closer look at how that statement will run on SQL server and other databases.

I am not sure how databases resolve such an intersect statement internally.

By that I mean I do not know the sequence of how the optimiser will optimise the statements.

In the end this is code generated by a tool supplied to me so it is not up to me to change it.

Thanks for the tip. I will pass it along to the author of the tool to take a look at.

Best Regards

Peter
Peter Jones (BIDA) 81 Reputation points

2021-03-03T13:36:00.793+00:00

Hi Erland,

Yes. The author of the software is trying to generate SQL that "runs on anything" so he does not use specific features of specific databases.

(For example it suggests to not use database generated integer keys like IDENTITY.)

Like me he has been around for years and back in the earlier days of RDBMs (not null field = null field) resolved to false, not "unknown".

He was surprised too.

In fact his ETL software was in C++ and the SQL Generation version was only first developed in 2009 on Netezza.

In C++ the delta detection utility got it right on all databases.

Netezza was the first machine powerful enough to perform ETL inside SQL.

So he has been extending his ETL software to do more in SQL.

ETL tools that do work inside a separate C++ based engine are going the way of the dodo.

Microsoft is re-purposing Informatica for example.

But this has been an interesting lesson.

Good for us old guys to learn about this! LOL!

Best Regards

Peter

Answer 3

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.

Answer 4

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

Answer 5

> 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

Peter Jones (BIDA) 81 Reputation points

2021-03-10T16:18:51.493+00:00

Hi Joe,

"Then you don't seem to know that "datetime" is a reserved keyword in T-SQL"

You do realise that is the name of the field in DNN, right?

DNN was highly touted by MSFT in the early 00s because of it's use of dot net.

If I were you?

I would not be so enthusiastic about being picky with other people.

This culture of endless criticism of other people and righteousness about trivial things is leading no where good.

Best Regards

Peter

Strange Behaviour in Null field = Not Null field evaluation

7 additional answers

Activity