Few days ago, I received wonderful email from blog reader and it was like good puzzle. I enjoyed solving this puzzle. I did not write the name of the blog reader because I am not sure if he wants his name here or not. Please read this article and run the script described in email. You can download the SQL Script here. Also can any of you help this reader why SQL Server is behaving like this. I have already replied him with correct answer where I suggest that it is not bug and have explained him the reason for the same.
Email Details are like this:
Few days ago, I found a very strange thing with SQL SERVER and I strongly feel that THIS IS A SEVERE BUG in SQL SERVER which actually can create lots of data discrepancy.
To prove my point I have attached a SQL Script with this mail. I want you to run the script in SQL SERVER.
Details of the Script
Create new database.
CREATEÂ DATABASE DB_TEST
Use this Database.
USE DB_TEST
Create a table named tblENQUIRY
CREATEÂ TABLE tblENQUIRY
(
ENQUIRYID INTÂ PRIMARYÂ KEY,
ENQUIRYNAME VARCHAR(50)
)
Create another table named tblAPPLICANTS
CREATEÂ TABLE tblAPPLICANTS
(
APPLICANTID INTÂ PRIMARYÂ KEY,
ENQUIRYID INTÂ FOREIGNÂ KEYÂ REFERENCES tblENQUIRY(ENQUIRYID),
ISCOMPLETEDÂ BIT
)
Insert 5 rows in tblENQUIRY
INSERTÂ INTO tblENQUIRY VALUES (1,'Enquiry1')
INSERTÂ INTO tblENQUIRY VALUES (2,'Enquiry2')
INSERTÂ INTO tblENQUIRY VALUES (3,'Enquiry3')
INSERTÂ INTO tblENQUIRY VALUES (4,'Enquiry4')
INSERTÂ INTO tblENQUIRY VALUES (5,'Enquiry5')
Insert 10 rows in tblAPPLICANTS
INSERTÂ INTO tblAPPLICANTS VALUES (1,1,0)
INSERTÂ INTO tblAPPLICANTS VALUES (2,1,0)
INSERTÂ INTO tblAPPLICANTS VALUES (3,2,0)
INSERTÂ INTO tblAPPLICANTS VALUES (4,2,0)
INSERTÂ INTO tblAPPLICANTS VALUES (5,3,0)
INSERTÂ INTO tblAPPLICANTS VALUES (6,3,0)
INSERTÂ INTO tblAPPLICANTS VALUES (7,4,0)
INSERTÂ INTO tblAPPLICANTS VALUES (8,4,0)
INSERTÂ INTO tblAPPLICANTS VALUES (9,4,0)
INSERTÂ INTO tblAPPLICANTS VALUES (10,5,0)
Select statement to confirm the data is inserted or not.
SELECT *
FROM tblENQUIRY
SELECT *
FROM tblAPPLICANTS
Here is a select query which is not correct. The query tells select APPLICANTID from tblENQUIRY table where ENQUIRYID = 1. But the problem here is tblENQUIRY is not having a column named APPLICANTID. So this query throws an error and that’s a correct behaviour.
SELECT APPLICANTID
FROM tblENQUIRY
WHERE ENQUIRYID = 1
Here is the magic. I have used the above select query which is not correct as a inner query with a update statement. What to do you think? What will happen?    Update query should throw an error. Logically it should.I was thinking the same But just execute this update query and you will be shocked.
UPDATE tblAPPLICANTS SET ISCOMPLETED = 1
WHERE APPLICANTID IN (
SELECT APPLICANTID
FROM tblENQUIRY
WHERE ENQUIRYID = 1)
Oops, 10 rows affected. Surpised. All the data in ISCOMPLETED field is set to 1 but my inner query
(
SELECT APPLICANTID
FROM tblENQUIRY
WHERE ENQUIRYID = 1)
is wrong.
This is just a sample query but When I executed somewhat similar statement 3364 rows in my table was updated.
SELECT *
FROM tblENQUIRY
SELECT *
FROM tblAPPLICANTS
Fortunately It was not on the Live server otherwise things could have been worse for me.
I think this is a severe bug.You all are expert. Can anyone explain me why this is happening?
If I am correct, Please pass on to the Microsoft SQL SERVER Team.
Waiting for a favorable reply.
Thanks,
(Name of blog reader)
If you have directly jumped here without reading complete email I suggest that you read this email again and run the script associated with it and observe why more than one rows are updated.
It is correct observation of reader that when
SELECT APPLICANTID
FROM tblENQUIRY
WHERE ENQUIRYID = 1
is ran it gives error but when complete query is ran it runs fine because APPLICANTID of sub query is actually column of outer query. Effect query is actually following query and that is why it goes not give any error.
UPDATE tblAPPLICANTS SET ISCOMPLETED = 1
WHERE APPLICANTID IN (
SELECT tblAPPLICANTS.APPLICANTID
FROM tblENQUIRY
WHERE ENQUIRYID = 1)
Now because same column is used in outerquery and subquery it gives effective result like this.
UPDATE tblAPPLICANTS SET ISCOMPLETED = 1
WHERE 1 IN (1)
Due to same reason all the columns of the table are updated not only one.
You can verify that is same in subquery and outerquery running following query.
SELECT tblAPPLICANTS.APPLICANTID, t.APPLICANTID
FROM tblAPPLICANTS
INNERÂ JOIN (
SELECT tblAPPLICANTS.APPLICANTID
FROM tblENQUIRY
WHERE ENQUIRYID = 1) t ON tblAPPLICANTS.APPLICANTID = t.APPLICANTID
When you run above query you will find that both the APPLICANTID are the same.
I hope this clears the issue. If you have read till this point in this article, congratulations and leave a comment for me that you read. This way I can understand that my readers are interested in topics like this and in future I may have more articles like this.
Reference : Pinal Dave (https://blog.sqlauthority.com)
11 Comments. Leave new
Nice one Pinal!
I can see how this would catch a lot of people out. It’s certainly not clear on first look, as to what’s happening.
It’s a good argument for fully qualifying your columns in subqueries, I think.
Definitely have more articles like this one, please!
Dear Pinal,
Nice Article. I’m also come across the similar situation. I guess I found here, instead of using ‘IN’ clause, it is better recommended to use ‘EXISTS’ clause. And its performance also be favour to Server.
The UPDATE statement in the email can be better replaced by
UPDATE tblAPPLICANTS SET ISCOMPLETED=1 WHERE ENQUIRYID=1 AND EXISTS(SELECT 1 FROM tblENQUIRY WHERE ENQUIRYID=tblAPPLICANTS.ENQUIRYID)
or
UPDATE tblAPPLICANTS SET ISCOMPLETED=1 WHERE EXISTS(SELECT 1 FROM tblENQUIRY WHERE ENQUIRYID=tblAPPLICANTS.ENQUIRYID AND ENQUIRYID=1)
Thanks & Regds
Nandakumar M
Dear Pinal:
I have been reading your blog for some time now–I grab it on my Google Reader and share with friends. I really enjoy all the subjects, all the puzzles and problems including this one. I don’t yet have the expertise (not even remotely close) that you and many of your readers possess, but your blog helps me quite a bit. I just thought I’d let you know that to encourage more puzzles and solutions. Thanks for doing what you do. It helps those of us at the begging of this SQL journey tremendously.
Sincerely,
R.Scott
This is very interesting.
I am not your reader, but I think I will became one ;)
Pinal,
Thanks to the reader who found this issue and thanks to you for posting it. Please, please do post articles/puzzles like these. It is very helpful in understanding SQL Server behaviour.
Regards,
Mahi
I like this puzzle example, I’d like more, especially relating to achieving an “answer” maximum efficiency. Like giving one query, make it run x% faster.
Interesting.
Hi Friends,
There is one more SEVERE Bug according me in SQL Server which I posted in MSDN Forum (link below). I did not get a satisfactory answer. The issue is basically you can create database objects without specifying a name. Look at the link below and send your responses……I am not sure if this got rectified in SQL Server 2008 but for sure this is an issue till SQL Server 2005.
The only reply I got from the forum was not satisfactory. Why should I create DDL trigger to check for blank table names or procedure names? The system should automatically take care of this scenario.
Regards
Srinivas Gudimella
Hi Pinal,
I was the one who mailed you..
Thanks for the info.
Will meet you for sure as I live in ahmedabad only.
Its a good catch…You are doin a very good job Pinal…Ye dil maange more…