[MsSQL] Vreemd resultaat bij vergelijking met NULL waarde

[Preaper]

Beste medetweakers,

Een query van mij reageert niet zoals ik zou verwachten. Ik maak gebruik van MsSQL 2000 met SP4. Ik heb mijn probleem vereenvoudigd naar de volgende query's:

SQL:

-- Vergelijkingen met een NULL waarde resulteren niet met deze optie niet standaard in FALSE
SET ANSI_NULLS OFF

PRINT 'Zou moeten resulteren in een 1 wanneer een naar INT gecaste NULL niet gelijk is aan de waarde 3'
SELECT 1
WHERE CAST(NULL AS INT) <> 3

PRINT 'Zou moeten resulteren in een 1 wanneer NULL niet gelijk is aan de waarde 3'
SELECT 1
WHERE NULL <> 3

dit is het resultaat van de query:

code:

Zou moeten resulteren in een 1 wanneer een naar INT gecaste NULL niet gelijk is aan de waarde 3
            
----------- 

(0 row(s) affected)

Zou moeten resulteren in een 1 wanneer NULL niet gelijk is aan de waarde 3
            
----------- 
1

(1 row(s) affected)

Waneer is de optie ANSI_NULLS uit staat, verwacht ik wanneer ik de waarde NULL vergelijk met de waarde 3 deze twee waardes al verschillend worden gezien. Dit doet MsSQL alleen bij de niet gecaste NULL. Erg vreemd, want een CAST(NULL as INT) is gewoon gelijk aan NULL.

Wat is hier de verklaring voor? Dit is mijns inziens niet in lijn met de documentatie zoals op http://msdn.microsoft.com...ary/aa259229(SQL.80).aspx

[RobIII]

Je moet sowieso geen =, >, < of <> operators op NULL los laten; daarvoor is de "IS (Not)" operator en de IsNull functie.
NULL is namelijk niet eens gelijk aan NULL (dus NULL = NULL geeft gewoon false). Zie ook:

quote: http://msdn.microsoft.com...ary/aa196106(SQL.80).aspx

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

Ik zie dan ook niet wat je met de Cast(Null as Int) wil bereiken.

[ Voor 58% gewijzigd door RobIII op 01-10-2008 15:39 ]

[P_de_B]

Om te testen of iets NULL is gebruik je idd de IS operator. Ik ben het echter wel eens met TS dat het gedrag niet consequent is. Waarom is wordt de returnvalue van CAST(NULL as INT) anders afgehandeld dan NULL?

In SQL2K5 overigens ook.

[ Voor 6% gewijzigd door P_de_B op 01-10-2008 15:39 ]

[whoami]

RobIII schreef op woensdag 01 oktober 2008 @ 15:33:
Je moet sowieso geen =, >, < of <> operators op NULL los laten; daarvoor is de "IS" operator.
NULL is namelijk niet eens gelijk aan NULL (dus NULL = NULL geeft gewoon false).

Daarom heeft hij ook 'ANSI_NULLS' afgezet

When SET ANSI_NULLS is OFF, the Equals (=) and Not Equal To (<>) comparison operators do not follow the SQL-92 standard. A SELECT statement that uses WHERE column_name = NULL returns the rows that have null values in column_name.

Dit levert dan weer wel het gewenste resultaat op:

SQL:

SET ANSI_NULLS OFF

DECLARE @melp INT
SELECT @melp = NULL

SELECT 1 
WHERE @melp <> 3

[ Voor 11% gewijzigd door whoami op 01-10-2008 15:42 ]

[RobIII]

Het gaat nu toch niet om het resultaat uit een colom maar een directe comparison op een int/literal

[ Voor 4% gewijzigd door RobIII op 01-10-2008 15:43 ]

[P_de_B]

Het vreemde is dat het resultaat uit CAST(NULL as INT) (wat gewoon 'NULL' is) anders wordt gezien als NULL. Het is niet hetzelfde als NULL ( ) maar de afhandeling zou toch gewoon hetzelfde moeten zijn?

[whoami]

Trouwens Preaper, kan je voor hetgeen ik denk je probeert te bereiken hier niet beter gewoon gebruik maken van de ISNULL functie ipv met die Ansi NULL's te gaan rommelen ?

code:

select *
from tabel
where ISNULL(somecolumn, <insert-afgesproken-waarde>) <> 3

[Preaper]

RobIII schreef op woensdag 01 oktober 2008 @ 15:33:
Je moet sowieso geen =, >, < of <> operators op NULL los laten; daarvoor is de "IS (Not)" operator en de IsNull functie.
NULL is namelijk niet eens gelijk aan NULL (dus NULL = NULL geeft gewoon false). Zie ook:

[...]

Ik zie dan ook niet wat je met de Cast(Null as Int) wil bereiken.

Hallo Roblll,

De context van mijn applicatie verreist dat ik =, >, < of <> operators moet gebruiken. Ik ben op de hoogte van de "IS" operator en de ISNULL functie, maar het gedrag van deze staat niet ter discussie.
Mijn cast van een NULL waarde naar INT dient geen doel, behalve het aantonen van de waarneming waarvoor ik een verklaring zoek.

[Preaper]

whoami schreef op woensdag 01 oktober 2008 @ 15:44:
Trouwens Preaper, kan je voor hetgeen ik denk je probeert te bereiken hier niet beter gewoon gebruik maken van de ISNULL functie ipv met die Ansi NULL's te gaan rommelen ?

code:

select *
from tabel
where ISNULL(somecolumn, <insert-afgesproken-waarde>) <> 3

Dat is zeker een oplossing(en deze heb ik in de praktijk ook toegepast). Ik ben echter niet op zoek naar een oplossing, maar naar een verklaring voor het probleem.

[matthijsln]

Nou de verklaring lijkt me dan simpel, of het is een bug of de documentatie klopt niet

Maar ik kan er niet echt wakker van liggen als het een bug is, die optie ANSI_NULLS geeft imo toch alleen maar meer mogelijkheid om jezelf in de voet te schieten alleen maar omdat je dan niet hoeft na te denken over NULL's.