I have two statements that seem the same that look for the same rows with the same null values. One returns the correct results and one returns 0 results.

With this table having some null values:

CREATE TABLE "AACoupons" (
"AACouponID" INTEGER NOT NULL DEFAULT AUTOINCREMENT,
"CouponAmount" INTEGER NULL,
"AAItemID" INTEGER NULL,
PRIMARY KEY ( "AACouponID" ASC )
) ;

INSERT INTO "AACoupons" ("AACouponID","CouponAmount","AAItemID") VALUES(300,2,3);
INSERT INTO "AACoupons" ("AACouponID","CouponAmount","AAItemID") VALUES(301,4,3);
INSERT INTO "AACoupons" ("AACouponID","CouponAmount","AAItemID") VALUES(302,6,3); 
INSERT INTO "AACoupons" ("AACouponID","CouponAmount","AAItemID") VALUES(500,162,NULL);
INSERT INTO "AACoupons" ("AACouponID","CouponAmount","AAItemID") VALUES(501,NULL,NULL);
INSERT INTO "AACoupons" ("AACouponID","CouponAmount","AAItemID") VALUES(506,NULL,NULL);

Why is it that this statement returns 3 results:

select * from AACoupons where AAItemID = null;

But this block returns 0 results:

begin
declare @NoVal int;
set @NoVal = null;
select * from AACoupons where AAItemID = @NoVal;
end;

I realize the variable is an intermediate step, but the variable is null on initializing and then it is explicitly set to null so I do not understand the difference.

asked 03 Dec '10, 19:51

Siger%20Matt's gravatar image

Siger Matt
3.1k486493
accept rate: 13%

edited 03 Dec '10, 21:54


I suspect that the ansinull option is set to ON on your connection when you ran your first statement, and your first statement, by itself without any other information, is treating the "AAItemID = null" as a TSQL comparison and therefore is using TSQL semantics. As such, the test for null will do what you appear to want - that is check for null values - and will return three rows.

In your second example, the existence of the "begin ... end" block is telling SQL Anywhere that you are using the Watcom SQL dialect and hence is using ANSI semantics. As such the predicate "AAItemID = @NoVal" or even "AAItemID = null" will never match any rows because nothing equals null in ANSI-land.

Please see the NULL value documentation for more information about the treatment of NULL in SQL Anywhere.

As Ron has pointed out, if you are wanting to find null values, the correct method is to use "AAItemID IS NULL".

permanent link

answered 03 Dec '10, 21:02

Mark%20Culp's gravatar image

Mark Culp
22.9k9129269
accept rate: 41%

edited 03 Dec '10, 22:04

Your suspicions are correct. ansinull was set to ON for both but I incorrectly assumed that "= null" would be the same as "is null" and typed them both with = to keep them as similar as possible, assuming that the variable was somehow to blame.

(03 Dec '10, 21:51) Siger Matt

Mark, correct me if I'm wrong, but "nothing equals null in ANSI-land" also means that NULL doesn't even equal NULL... which means that a predicate of "WHERE NULL = NULL" will return the same result as "WHERE 1 = 2"

(04 Dec '10, 04:58) Ron Hiner

@Ron: IMHO, that's not completely true: 1=2 returns FALSE whereas NULL = NULL returns UNKNOWN. However, in a WHERE clause, only conditions evaluating to TRUE are returned, so in your example, both comparisons are not TRUE, and as such, they are filtered out.

(04 Dec '10, 10:47) Volker Barth

@Ron: To add: Note that for CHECK constraints, the logic is different: They are only violated by conditions that evaluate to FALSE. Therefore, something like CHECK(@value = 1) will accept both a value of 1 and the NULL value. So this is different from WHERE clauses.

(06 Dec '10, 17:04) Volker Barth

I haven't tested your code, but the first place I'd look it to see what exactly is in the @noVal variable.

Null is not a value, it is the absence of a value. You are probably treading in very dangerous territory here of unpredictable results.

If it your intent to look for nulls, use IS NULL.

 select * from AACoupons where AAItemID IS NULL;

Since it is not a value, the concept of equating null to another value doesn't make sense.

One of the many things I'm grateful for in SQLA is that it allows me to divide by zero to return a null, rather than crashing and burning on a div by zero error. (It's an option,and not default behavior since when, maybe release 4.something?)

permanent link

answered 03 Dec '10, 20:11

Ron%20Hiner's gravatar image

Ron Hiner
880202427
accept rate: 9%

The divide by 0 trick is a good one to know. I'm not looking for null explicitly. The variable value is filled in dynamically, but if it is null I would like it to return the corresponding rows that have a null value, but if the value is 3 then return the rows that have a value of 3.

I would think having set the variable = null would settle what was in it.

I just wondered why.

(03 Dec '10, 20:50) Siger Matt

If you want to have a comparison that returns TRUE in both cases:

  • not-null-value = the-same-not-null-value

and

  • null = null

and you're using SA12, you can use the new (and ANSI-compliant) NOT DISTINCT FROM search condition.

I.e.

col1 not distinct from col2

is sematically the same as

col1 = col2 or (col1 is null and col2 is null)

However, the performance for NOT DISTINCT FROM should be better, as it is sargable.

permanent link

answered 04 Dec '10, 10:57

Volker%20Barth's gravatar image

Volker Barth
30.3k300452659
accept rate: 32%

Another new one that is good to know.

(05 Dec '10, 01:21) Siger Matt
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×18
×10
×7

question asked: 03 Dec '10, 19:51

question was seen: 1,520 times

last updated: 04 Dec '10, 10:57