bug found in query

sql server 2000 versions 8.00.760 and 8.00.818
i found a strange bug. maybe it's not a bug, but it sure seems like one
to me. these tables are from a database for a cots product called
facility center.
the "bug" occurs down at the very bottom when you run the queries
specified. see my comments down there.
just to explain a little,
spbldinf_code is a building name
spbldrom_code is a room number
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[spbldrom__spcharge]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[spcharge] DROP CONSTRAINT spbldrom__spcharge
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[spbldrom__code]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[sprmutil] DROP CONSTRAINT spbldrom__code
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[spbldrom]') and OBJECTPROPERTY(id, N'IsUserTable'
) =
1)
drop table [dbo].[spbldrom]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[spcharge]') and OBJECTPROPERTY(id, N'IsUserTable'
) =
1)
drop table [dbo].[spcharge]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sprmutil]') and OBJECTPROPERTY(id, N'IsUserTable'
) =
1)
drop table [dbo].[sprmutil]
GO
CREATE TABLE [dbo].[spbldrom] (
[spbldinf_code] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT
NULL ,
[spbldrom_code] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT
NULL ,
[spfloors_code] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS N
OT
NULL ,
[splocation_code] [varchar] (72) COLLATE SQL_Latin1_General_CP1_CI_A
S
NULL ,
[spbldloc_code] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL
,
[saclass_code] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL
,
[saclstyp_code] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[description] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL
,
[phone] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[area] [decimal](12, 4) NULL ,
[notes] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[occ_vac] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[storageyn] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[location_code] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[subloc_type] [varchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL
,
[user_name_created] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI
_AS
NULL ,
[date_created] [datetime] NULL ,
[user_name_modified] [varchar] (12) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[date_modified] [datetime] NULL ,
[group_owned_by] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[soft_delete_flag] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_A
S
NULL ,
[spanlink_num] [int] NULL ,
[haz_mat] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[reservable_flag] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[spcharge] (
[sporg_code] [varchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[spcharge_seq_num] [int] NOT NULL ,
[spbldinf_code] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT
NULL ,
[spfloors_code] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL
,
[spbldrom_code] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[area] [decimal](12, 4) NULL ,
[pct] [decimal](12, 4) NULL ,
[user_name_created] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI
_AS
NULL ,
[date_created] [datetime] NULL ,
[user_name_modified] [varchar] (12) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[date_modified] [datetime] NULL ,
[group_owned_by] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[soft_delete_flag] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_A
S
NULL ,
[spanlink_num] [int] NULL ,
[owner_sporg_code] [varchar] (18) COLLATE SQL_Latin1_General_CP1_CI_
AS
NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[sprmutil] (
[spbldinf_code] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT
NULL ,
[spfloors_code] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS N
OT
NULL ,
[spbldrom_code] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT
NULL ,
[sprmutil_seq_num] [int] NOT NULL ,
[sporg_code] [varchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
[spprgrm_code] [varchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL
,
[spromuse_code] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL
,
[pct] [numeric](12, 4) NULL ,
[area] [numeric](12, 4) NULL ,
[user_name_created] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI
_AS
NULL ,
[spromsub_code] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL
,
[date_created] [datetime] NULL ,
[user_name_modified] [varchar] (12) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[date_modified] [datetime] NULL ,
[group_owned_by] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[soft_delete_flag] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_A
S
NULL ,
[spanlink_num] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[spbldrom] ADD
CONSTRAINT [DF__spbldrom__area__6FE99F9F] DEFAULT (0) FOR [area],
CONSTRAINT [DF__spbldrom__reserv__18427513] DEFAULT (0) FOR
[reservable_flag],
CONSTRAINT [xpkspbldrom] PRIMARY KEY NONCLUSTERED
(
[spbldinf_code],
[spbldrom_code]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [XIF226spbldrom] ON [dbo].[spbldrom]([spbldinf
_code],
[spbldloc_code]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [XIF228spbldrom] ON [dbo].[spbldrom]([spbldinf
_code])
WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [XIF229spbldrom] ON [dbo].[spbldrom]([spbldinf
_code],
[spfloors_code]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [XIF433spbldrom] ON [dbo].[spbldrom]([saclass_
code],
[saclstyp_code]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [XIF877spbldrom] ON [dbo].[spbldrom]([splocati
on_code])
WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[spcharge] ADD
CONSTRAINT [DF__spcharge__area__2CBDA3B5] DEFAULT (0) FOR [area],
CONSTRAINT [DF__spcharge__pct__2DB1C7EE] DEFAULT (0) FOR [pct],
CONSTRAINT [xpkspcharge] PRIMARY KEY NONCLUSTERED
(
[sporg_code],
[spcharge_seq_num],
[spbldinf_code]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [XIF236spcharge] ON [dbo].[spcharge]([sporg_co
de]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [XIF274spcharge] ON [dbo].[spcharge]([spbldinf
_code],
[spbldrom_code]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [XIF275spcharge] ON [dbo].[spcharge]([spbldinf
_code],
[spfloors_code]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [XIF276spcharge] ON [dbo].[spcharge]([spbldinf
_code])
WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[sprmutil] ADD
CONSTRAINT [XPKsprmutil] PRIMARY KEY NONCLUSTERED
(
[spbldinf_code],
[spfloors_code],
[spbldrom_code],
[sprmutil_seq_num]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [XIF1040sprmutil] ON [dbo].[sprmutil]([sporg_c
ode]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[spcharge] ADD
CONSTRAINT [spbldrom__spcharge] FOREIGN KEY
(
[spbldinf_code],
[spbldrom_code]
) REFERENCES [dbo].[spbldrom] (
[spbldinf_code],
[spbldrom_code]
)
GO
ALTER TABLE [dbo].[sprmutil] ADD
CONSTRAINT [spbldrom__code] FOREIGN KEY
(
[spbldinf_code],
[spbldrom_code]
) REFERENCES [dbo].[spbldrom] (
[spbldinf_code],
[spbldrom_code]
)
GO
insert into spbldrom (spbldinf_code, spbldrom_code, spfloors_code)
values ('1', '1', '1')
insert into spbldrom (spbldinf_code, spbldrom_code, spfloors_code)
values ('1', '2', '1')
insert into spbldrom (spbldinf_code, spbldrom_code, spfloors_code)
values ('1', '3', '1')
insert into spbldrom (spbldinf_code, spbldrom_code, spfloors_code)
values ('1', '4', '1')
insert into spbldrom (spbldinf_code, spbldrom_code, spfloors_code)
values ('1', '5', '1')
insert into spbldrom (spbldinf_code, spbldrom_code, spfloors_code)
values ('1', '6', '1')
insert into spcharge (spbldinf_code, spbldrom_code, sporg_code,
spcharge_seq_num) values ('1', '1', 'org', 1)
insert into spcharge (spbldinf_code, spbldrom_code, sporg_code,
spcharge_seq_num) values ('1', '2', 'org', 2)
insert into spcharge (spbldinf_code, spbldrom_code, sporg_code,
spcharge_seq_num) values ('1', '3', 'org', 3)
insert into spcharge (spbldinf_code, spbldrom_code, sporg_code,
spcharge_seq_num) values ('1', '4', 'org', 4)
insert into sprmutil (spbldinf_code, spbldrom_code, spfloors_code,
sprmutil_seq_num) values ('1', '1', '1', 1)
insert into sprmutil (spbldinf_code, spbldrom_code, spfloors_code,
sprmutil_seq_num) values ('1', '2', '1', 1)
insert into sprmutil (spbldinf_code, spbldrom_code, spfloors_code,
sprmutil_seq_num) values ('1', '3', '1', 1)
insert into sprmutil (spbldinf_code, spbldrom_code, spfloors_code,
sprmutil_seq_num) values ('1', '4', '1', 1)
go
select spbldinf_code, spbldrom_code from spbldrom
where spbldinf_code + spbldrom_code not in
(select spbldinf_code + spbldrom_code from sprmutil)
select spbldinf_code, spbldrom_code from spbldrom
where spbldinf_code + spbldrom_code not in
(select spbldinf_code + spbldrom_code from spcharge)
those two queries work properly. rooms 5 and 6 are returned because
they do not exist in the spcharge and/or sprmutil table.
insert into spcharge (spbldinf_code, spbldrom_code, sporg_code,
spcharge_seq_num) values ('1', NULL, 'org', 10)
set concat_null_yields_null off
select spbldinf_code, spbldrom_code from spbldrom
where spbldinf_code + spbldrom_code not in
(select spbldinf_code + spbldrom_code from sprmutil)
select spbldinf_code, spbldrom_code from spbldrom
where spbldinf_code + spbldrom_code not in
(select spbldinf_code + spbldrom_code from spcharge)
go
the two queries correctly return rooms 5 and 6.
set concat_null_yields_null on
select spbldinf_code, spbldrom_code from spbldrom
where spbldinf_code + spbldrom_code not in
(select spbldinf_code + spbldrom_code from sprmutil)
select spbldinf_code, spbldrom_code from spbldrom
where spbldinf_code + spbldrom_code not in
(select spbldinf_code + spbldrom_code from spcharge)
go
now the query for spcharge returns nothing. the one row with a null
value in the spcharge.spbldrom_code has prevented the query from
returning anything when it should still return rooms 5 and 6. i realize
that the concat_null_yields_null value is different, but imho it should
still return rooms 5 and 6.
if you run
set concat_null_yields_null on
select spbldinf_code + spbldrom_code from spcharge
set concat_null_yields_null off
select spbldinf_code + spbldrom_code from spcharge
you get record sets returned for both queries. the null value returned
in the top one apparently makes the not in clause in the previous
queries fail to work properly.
is there any way to report this to microsoft without paying for a
support call?> select spbldinf_code, spbldrom_code from spbldrom
> where spbldinf_code + spbldrom_code not in
> (select spbldinf_code + spbldrom_code from spcharge)
> go
> now the query for spcharge returns nothing. the one row with a null
> value in the spcharge.spbldrom_code has prevented the query from
> returning anything when it should still return rooms 5 and 6.
This isn't a bug it's standard SQL behaviour.
Because the subquery
(select spbldinf_code + spbldrom_code from spcharge)
includes a NULL, the predicate NOT IN returns UNKNOWN for every row in the
outer query, therefore nothing is returned. This is logical enough - if a
value in the set is UNKNOWN then you can't know whether any given value is
NOT IN the set.
I think the result you are looking for is this:
SELECT spbldinf_code, spbldrom_code
FROM spbldrom
WHERE spbldinf_code + spbldrom_code
NOT IN
(SELECT spbldinf_code + spbldrom_code
FROM spcharge
WHERE spbldinf_code + spbldrom_code IS NOT NULL)
David Portas
SQL Server MVP
--|||David Portas wrote:

> This is logical enough - if a
> value in the set is UNKNOWN then you can't know whether any given value is
> NOT IN the set.
that doesn't make any sense.
set A = 1, 2, 3
set B = 1, 2, NULL
if i'm trying to find all in A that aren't in B, then since
A.3 <> B.1 and
A.3 <> B.2 and
A.3 <> B.NULL
A.3 is not in set B
makes much more sense than saying "i can't figure out whether or not 3 is in
set B" when it's perfectly clear that there is no 3 in set B.|||> A.3 <> B.1 and
> A.3 <> B.2 and
> A.3 <> B.NULL
Yes, that's the logic. But the result of that last comparison is UNKNOWN not
TRUE (anything compared to NULL is UNKNOWN). Therefore the result of the NOT
IN predicate is also UNKNOWN. This is a common "gotcha" in SQL.
David Portas
SQL Server MVP
--