BUG Or What Am I Missing
-
SELECT Distinct(Name) FROM Upload WHERE Name Like 'ODY%'
Name
-----------------------------
ODYSSEY AMERICA RE
ODYSSEY REINSURANCE CORP.
(2 row(s) affected)
**********************************
SELECT Distinct(Name) FROM Original WHERE Name Like 'ODY%'
Name
----------------------------------
ODYSSEY AMERICAN REINSURANCE CO.
ODYSSEY RE (LONDON) LTD.
ODYSSEY REINSURANCE CORP.
(3 row(s) affected)
***********************************
select Distinct(Name) from upload where
Name not in (select name from Original)
Name
----------------------------------------
(0 row(s) affected)
***********************************
The Name field is Varchar(75) on both tables, I have tried this on 2 servers, in 3 different DBs
What's going on here, I use this technique all over the place. It just won't work for this one !!!
KlK -
Got it, and I am going to RTFM to make sure I understand, because I don't. But I solved it.
In the Original table there is a NULL row.
I will read BOL regardings nulls and write a 200 word essay explaining why this dun't work. If it explains it.
KlK -
What were you expecting? In your third query you should NOT have seen anything - which is what happened. Change the third query to: select Distinct(Name) from ORIGINAL where Name not in (select name from UPLOAD) and you should get one row returned. Upload has two NAMEs and both of them are in Original, which is why the third query didn't return anything. -SQLBill
-
Bill,
I think you need to re-read the third query.
Kevin,
I don't see why you would have a problem with your third query. I also didn't have time to create a script to test it. What collation are you running? What happens if you right the last query as a left join instead?
SELECT DISTINCT(u.Name)
FROM Upload u
LEFT JOIN Original o ON u.Name = o.Name
WHERE o.Name IS NULL
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB EngineerThis posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
-
Should result in 2 rows.
Is this on a multi-processor sqlserver-box ? try queryhint maxdop 1.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Yes first Bill look at the third query, it is attempting to find Names in upload that do NOT exist in Original.
Bad names for the tables, but I just threw this together to test.
As for collation etc, it is SQL2k Dev latest patches, and an MP. Although the samples above were done on my Laptop. SQL2K dev, but just a single processor.
I have read through BOL and don't really understand what the problem is either.
But I do know that it is related to a NULL row, or at least a row with a NULL name in the Original table. Delete it and everything works as expected. Or adding a "WHERE Name IS NOT NULL" to the subquery produces the expected results.
I will do some more reading over the weekend, but so far it appears when NULLs are involved there are 3 possible answers to a test. TRUE, FALSE, and UNKNOWN !!! I think it is this UNKNOWN that is causing the grief.
Gary, just noticed you are part of MS, so maybe you can show this to the SQL guys and get a better explaination, We all think this it is wrong. Even the MF DB2ers, see below.
I did rebuild it as a JOIN, and that's how I found the NULL row. But the developers code should have worked in my opinion.
Funny sideline, one of my fellow DBAs (MF DB2) was helping, and tested on the MF, it worked fine. He's like, I think you should have MS fix this (I get a lot of grief from the MFers about working on these Fischer Price DBMSs, even though I started there years before most of them). Once I determined it was a NULL row, he tried that and Bingo MF DB2 provide the same results as SQL Server did. That brought out a good laugh, particularly since I taught him IMS which lead him to DB2 !!
KlK -
It's not a bug, it's what SQL is supposed to do. Remember that NULL means "unknown". This is a major reason to avoid using NOT IN with subqueries. There was a thread here about this a few weeks ago, but I don't have time to find it.
Use NOT EXISTS with your subquery.
--Jonathan -
Keven,
Even though I work at MS I'm just a developer in same as you when it comes to stuff like this.
You stated that "In the Original table there is a NULL row". What do you mean by that? Do you mean that one of the records has a NULL value for the Name field? If so that record sure wouldn't come back as expected. I just ran the following and it works as expected (Win2k3 SQL2k SP3a Latin1_General_Bin collation)...
SET NOCOUNT ON
IF EXISTS(SELECT * FROM sysobjects WHERE id = object_id('Upload'))
DROP TABLE Upload
CREATE TABLE Upload
(Name nvarchar(255))
IF EXISTS(SELECT * FROM sysobjects WHERE id = object_id('Original'))
DROP TABLE Original
CREATE TABLE Original
(Name nvarchar(255))
INSERT INTO Upload
VALUES('ODYSSEY AMERICA RE')
INSERT INTO Upload
VALUES('ODYSSEY REINSURANCE CORP.')
INSERT INTO Original
VALUES('ODYSSEY AMERICAN REINSURANCE CO.')
INSERT INTO Original
VALUES('ODYSSEY RE (LONDON) LTD.')
INSERT INTO Original
VALUES('ODYSSEY REINSURANCE CORP.')
SELECT DISTINCT(Name)
FROM Upload
WHERE
Name NOT IN (SELECT Name FROM Original)
SELECT DISTINCT(u.Name)
FROM Upload u
LEFT JOIN Original o ON u.Name = o.Name
WHERE o.Name IS NULL
returns
Name
--------------------
ODYSSEY AMERICA RE
Name
--------------------
ODYSSEY AMERICA RE
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB EngineerThis posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
-
No problem Gary just giving you a hard time.
If you add a NULL row, or a row with the NAME field NULL to the ORIGINAL table. It breaks the query.
Although it does appear that is an ANSI SQL issue, as MF DB2 does exactly the same.
I know in my example I didn't show the NULL row, at the time I didn't realize it was there. I'm just supporting the developers. And at times they load data in that is bad. This system is also being developed somewhat on the fly, so this type of thing can happen.
Thanks
KlK -
Ahh. Now I see what you are talking about. And yes it is a function of the ANSI SQL begin unable to compare a NULL to anything. I don't think this is a bug but is as designed. It will also do this if you change the query to NOT EXISTS. I guess I'll stick with my Left Join syntax after all. At least it returns what I expect!
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB EngineerThis posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
-
> It will also do this if you change the query to NOT EXISTS.
use pubs
select *
from authors
where state not in
(select state
from publishers)
select *
from authors a
where not exists
(select *
from publishers
where state = a.state)
select a.*
from authors a left join publishers p on p.state = a.state
where p.state is null
--Jonathan -
You got me there Jonathan. However if you don't do it as a correlated subquery it'll return the same results as NOT IN.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB EngineerThis posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
-
an other 2ct.
The best things about nulls is you don't need to use them.
So only define null alowed when you need null, when it makes sence for your datamodel that you don't know the actual value and you need to know that as a fact.
As you may have noticed, nulls will bite you in the back at moments you are in troubles.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply