A NULL Puzzle

[Steve Kass]

Without running this, predict the result:

set ansi_nulls off
go

create table T (
i int
)

insert into T values (1)
insert into T values (null)
go

declare @null int
set @null = cast(@null as int)

select 0, i, 'not (i = null)' from T where not (i = null)
union all
select 1, i, 'not (1 = i)' from T where not (1 = i)
union all
select 2, i, 'not (i <> null)' from T where not (i <> null)
union all
select 3, i, '-i = null' from T where -i = null
union all
select 4, i, 'i = -null' from T where i = -null
union all
select 5, i, 'i in (-null)' from T where i in (-null)
union all
select 6, i, '= @null' from T where i = @null
union all
select 7, i, '= cast(@null as int)' from T where i = cast(@null as int)
union all
select 8, i, 'i = i' from T where i = i
union all
select 9, i, 'i <> i' from T where i <> i
union all
select 10, i, 'i <> null' from T where i <> null
union all
select 11, i, '<= null' from T where i <= null
union all
select 12, i, 'null = null' from T where null = null
order by 1, 2
go

drop table T

Steve Kass
Drew University

[Greg Linwood]

I nearly went loopy looking at this... :)

(ok, I ran it!)

Regards,
Greg Linwood

"Steve Kass" <sk...@drew.edu> wrote in message
news:uhuu1IIvCHA.2876@TK2MSFTNGP09...

[Jacco Schalkwijk]

Interesting stuff Steve. Defnitely not going to use set ansi_nulls off (not
that I was). I think the result from 8 is the weirdest actually.

--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.

"Steve Kass" <sk...@drew.edu> wrote in message
news:uhuu1IIvCHA.2876@TK2MSFTNGP09...

[WangKhar]

just a guess, but nothing?

Steve Kass <sk...@drew.edu> wrote in message news:<uhuu1IIvCHA.2876@TK2MSFTNGP09>...

[Steve Kass]

For extra credit, give the result predicted by the documentation
in Books Online. ;)

Steve

[Joe Celko]

Nice work! Gotta save this one to re-post when someone decides to
violation standards.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Steve Kass]

Unless I'm doing something wrong, Ocelot seems to disallow

select * from T
where i = NULL

Is this illegal in SQL-92 or 99 ?

SK

[Jacco Schalkwijk]

"When SET ANSI_NULLS is OFF, comparisons of all data against a null value
evaluate to TRUE if the data value is NULL. " ...which I take to mean that
comparing a NULL value with itself will return TRUE, but apparently that
doesn't hold true if you compare a column with itself...

It works with variables though:
DECLARE @a varchar(4)
PRINT coalesce(@a, 'null')
SET ansi_nulls off
IF @a = @a
print 'equal'
else print 'not equal'

SET ANSI_NULLS OFF should mean: all NULLs are created equal and identical,
which as you have proved isn't the case in SQL 2000 and
SET ANSI_NULLS ON should mean: all NULLs are different from each other,
which is true in most cases in SQL 2000, but as there is only one NULL
allowed in a UNIQUE index, that's not totally true either....

--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.

"Steve Kass" <sk...@drew.edu> wrote in message

news:Orp8sQLvCHA.2124@TK2MSFTNGP11...

[Steve Kass]

The documentation also says silly things like NULL <> NULL should
return TRUE (!!!) if ANSI_NULLS is OFF (see the <> article).

I think ANSI_NULLS OFF is very restricted in its scope, and only
should change the behavior when comparing a table column to the
unadulterated literal NULL with the specific operators = or <>..

But it's obviously a bad idea to put energy into the question "What is
the correct non-standard behavior?"

SK

[Jacco Schalkwijk]

Yes, they messed up quite major there.
According to that bit NULL is equal to everything if ANSI_NULLS is OFF :-)

I think the whole ANSI_NULLS option should be removed in the next version,
but MS will probably keep it in for backward compatibility. (Although...
they did a decent job with removing a lot of old rubbish from VB.NET, so you
never know).

--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.

"Steve Kass" <sk...@drew.edu> wrote in message

news:#3nZUpLvCHA.440@TK2MSFTNGP12...

[Joe Celko]

>> SELECT * FROM T WHERE i = NULL;

Is this illegal in SQL-92 or 99? <<

No problem that I can see.

I get table T with one and NULL in it. I apply the predicate which
returns UNKNOWN for both rows. The * resolved to the single column i in
the SELECT. There are no rows that tested TRUE, so you get back an
empty result set.

[Steve Kass]

I just checked the standard, and in SQL-92 there is no
NULL literal. There is only a NULL keyword, which
can sometimes (but not always) be used where a literal
can be used. This makes good sense (what type would
the NULL literal be?).

The following works in Ocelot:

select * from T where i = cast(NULL as int)

SK

[Joe Celko]

>> I just checked the standard, and in SQL-92 there is no
NULL literal. <<

That is what I get for not having SQL-92 documents on my new XP laptop.
I also found that it is a bitch to move files to this thing, too, but
that is another story ...

[Steve Kass]

Joe,

You really should move your "files" using a single set-based INSERT.
You're
probably moving them one at a time with non-portable floppy diskettes, as if
nothing has changed in the last 20 years! ;)

SK

[Tibor Karaszi]

LOL

--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver

"Steve Kass" <sk...@drew.edu> wrote in message news:OLrodaMvCHA.2380@TK2MSFTNGP11...

[Alejandro Mesa]

Steve,

 

For me, the weirdest are 4 and 5.

 

select 4, i, 'i = -null' from T where i = -null

select 5, i, 'i in (-null)' from T where i in (-null)

 

because I always thought that:

 

where colA in (list_member_1,..., list_member_n)

 

could be rewritten as:

 

where colA = list_member_1 or

      ...

      colA = list_member_n

 

so, "i in (-null)" could be rewritten as "i = -null". Affter running the batch to check my guessing, I couldn't believe that number 4 returns no row and number 5 returns the row with null.

 

Very interesting and a good lesson to always keep in mind to avoid nulls when designing a database.

 

 

thanks,

 

 

 

AMB

[Jacco Schalkwijk]

I think the lesson is not to avoid NULLs (they are pretty legitimate), but to avoid SET ANSI_NULLS OFF (which isn't).

--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.

 

 

"Alejandro Mesa" <Alejan...@GardensAmerica.com> wrote in message news:u0aPzMWvCHA.2288@TK2MSFTNGP09...

[Alejandro Mesa]

Jacob,

 

You are right, avoid nulls when possible and never use SET ANSI_NULLS OFF.

 

 

 

 

AMB

[Alejandro Mesa]

Jacco,

 

I am sorry, I used a wrong name.

 

 

 

AMB

[Jacco Schalkwijk]

No problem, I saw Anith Sen being called Anita the other day and I think that was a lot worse :-)

--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.

 

 

"Alejandro Mesa" <Alejan...@GardensAmerica.com> wrote in message news:u#HY97WvCHA.2504@TK2MSFTNGP10...

[Eric]

two cents:

 

There is one case when using nullable columns is a big performance boost. When using "for xml auto,elements", null columns are missing from the output. In some cases the result is a much smaller output stream. With medium-big resultsets the difference is very significant. This is one case where using lots of nullable columns is a good decision.

 

But I can't think of a reason to use SET ANSI_NULLS OFF.

 

"Alejandro Mesa" <Alejan...@GardensAmerica.com> wrote in message news:u0aPzMWvCHA.2288@TK2MSFTNGP09...

[Ryan Simpson [MSFT]]

Great example!

Regards

Ryan

This posting is provided "AS IS" with no warranties, and confers no rights.
OR if you wish to include a script sample in your post please add "Use of
included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm