IS NULL bug(?)

Hello,
My problem is; weird results with a IS NULL clause in a query.
I have a sql2000 dbase, table, 2 columns and the following query:
dt_column =
type: datetime
computed: no
length:8
nullable: no
TrimTrailingBlanks:n/a
FixedLenNullnSource:n/a
Collation: null
vc_column =
type:varchar
computed: no
length:15
nullable: yes
TrimTrailingBlanks:no
FixedLenNullnSource:no
Collation: SQL_Latin1_General_CP1_CI_AS
q1: select dt_column,vc_colomn from mytable where vc_column is null
->result; >20.000 records where vc_column indeed is null
but, if i expand the query with a date clause like so:
q2: select dt_column,vc_column from mytable where vc_column is null and
year(dt_column) = '2006'
->result; <20.000 records where SOME of the results DO have a value for
the vc_column!
This is strange isnt it ?
I already have a solution to overcome this; but I do not see why i have
to do this:
When I cast the vc_column to a varchar like so:
q3: select dt_column,vc_column from mytable where cast(vc_column as
varchar) and year(dt_column) ='2006'
->the result is good! only null values for the vc_column !
but why do i have to cast a varchar column to a varchar before the is
null will work ??
Thanks very much,
Jeroen
This sounds a bit familiar. I ran into a similar problem where unexpected
results were returned from a query (that was a bit more complex than the one
you posted).
What version of SQL Server 2000 are you running?
execute this within Query Analyzer:
SELECT @.@.version
and compare the results to the build list here:
http://www.aspfaq.com/sql2000Builds.asp
In our case we experienced the error on SP4 (SP3 worked fine). Applying the
..2187 hotfix solved our problem. Do you have a test system that you can use
to reproduce the probem and test various patches to see if they solve your
problem?
Keith Kratochvil
"Shuwi" <jeroen.schoenmakers@.gmail.com> wrote in message
news:1160054422.157730.217980@.c28g2000cwb.googlegr oups.com...
> Hello,
> My problem is; weird results with a IS NULL clause in a query.
> I have a sql2000 dbase, table, 2 columns and the following query:
> dt_column =
> type: datetime
> computed: no
> length:8
> nullable: no
> TrimTrailingBlanks:n/a
> FixedLenNullnSource:n/a
> Collation: null
> vc_column =
> type:varchar
> computed: no
> length:15
> nullable: yes
> TrimTrailingBlanks:no
> FixedLenNullnSource: no
> Collation: SQL_Latin1_General_CP1_CI_AS
> q1: select dt_column,vc_colomn from mytable where vc_column is null
> ->result; >20.000 records where vc_column indeed is null
> but, if i expand the query with a date clause like so:
> q2: select dt_column,vc_column from mytable where vc_column is null and
> year(dt_column) = '2006'
> ->result; <20.000 records where SOME of the results DO have a value for
> the vc_column!
> This is strange isnt it ?
> I already have a solution to overcome this; but I do not see why i have
> to do this:
> When I cast the vc_column to a varchar like so:
> q3: select dt_column,vc_column from mytable where cast(vc_column as
> varchar) and year(dt_column) ='2006'
> ->the result is good! only null values for the vc_column !
> but why do i have to cast a varchar column to a varchar before the is
> null will work ??
> Thanks very much,
> Jeroen
>
|||Hi Keith,
Thanks for the quick reply;
The version of sql server is:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise
Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
on the > http://www.aspfaq.com/sql2000Builds.asp website I can see no
hotfixes available for this release...?
(seems to stop at build 2187)...
Maybe the 2187 hotfix works here to; I'll test; thanks again.
Jeroen
Keith Kratochvil wrote:[vbcol=seagreen]
> This sounds a bit familiar. I ran into a similar problem where unexpected
> results were returned from a query (that was a bit more complex than the one
> you posted).
> What version of SQL Server 2000 are you running?
> execute this within Query Analyzer:
> SELECT @.@.version
> and compare the results to the build list here:
> http://www.aspfaq.com/sql2000Builds.asp
> In our case we experienced the error on SP4 (SP3 worked fine). Applying the
> .2187 hotfix solved our problem. Do you have a test system that you can use
> to reproduce the probem and test various patches to see if they solve your
> problem?
>
> --
> Keith Kratochvil
>
> "Shuwi" <jeroen.schoenmakers@.gmail.com> wrote in message
> news:1160054422.157730.217980@.c28g2000cwb.googlegr oups.com...
|||Hi
You are currently on service pack 3a, you can apply service pack 4
http://www.microsoft.com/downloads/d...displaylang=en
followed by the subsequent hot fixes. I would recommend at least going to
build 2040. This is the link for the 2187 download:
http://www.microsoft.com/downloads/d...displaylang=en
John
"Shuwi" wrote:

> Hi Keith,
> Thanks for the quick reply;
> The version of sql server is:
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
> 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise
> Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> on the > http://www.aspfaq.com/sql2000Builds.asp website I can see no
> hotfixes available for this release...?
> (seems to stop at build 2187)...
> Maybe the 2187 hotfix works here to; I'll test; thanks again.
> Jeroen
> Keith Kratochvil wrote:
>
|||Don't be confused by "Service Pack 4" in the results. That is the WINDOWS
service pack, not the SQL Server Service pack.
Here is another select statement that returns database service pack level:
select serverproperty('productlevel')
By the way,
..760 is SP3a
There are a few publically available hotfixes for SP3:
.818
.878
You could upgrade to SP4 (version 2039)
There are two publically available hotfixes for SP4: 2040 and 2187
Keith Kratochvil
"Shuwi" <jeroen.schoenmakers@.gmail.com> wrote in message
news:1160056347.136146.322120@.h48g2000cwc.googlegr oups.com...
> Hi Keith,
> Thanks for the quick reply;
> The version of sql server is:
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
> 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise
> Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> on the > http://www.aspfaq.com/sql2000Builds.asp website I can see no
> hotfixes available for this release...?
> (seems to stop at build 2187)...
> Maybe the 2187 hotfix works here to; I'll test; thanks again.
> Jeroen
> Keith Kratochvil wrote:
>
|||Thanks all;
Seems I have some upgrading to do...
Kind regards,
Jeroen
Keith Kratochvil wrote:[vbcol=seagreen]
> Don't be confused by "Service Pack 4" in the results. That is the WINDOWS
> service pack, not the SQL Server Service pack.
> Here is another select statement that returns database service pack level:
> select serverproperty('productlevel')
> By the way,
> .760 is SP3a
> There are a few publically available hotfixes for SP3:
> .818
> .878
> You could upgrade to SP4 (version 2039)
> There are two publically available hotfixes for SP4: 2040 and 2187
> --
> Keith Kratochvil
>
> "Shuwi" <jeroen.schoenmakers@.gmail.com> wrote in message
> news:1160056347.136146.322120@.h48g2000cwc.googlegr oups.com...