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