SQL SERVER – Email from Blog Reader – Not a Potential Bug in SQL – Puzzle

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 (http://blog.SQLAuthority.com)

About these ads

11 thoughts on “SQL SERVER – Email from Blog Reader – Not a Potential Bug in SQL – Puzzle

  1. 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!

    Like

  2. 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

    Like

  3. 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

    Like

  4. 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

    Like

  5. 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.

    Like

  6. 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.

    http://social.msdn.microsoft.com/forums/en-US/sqldatabaseengine/thread/568f01fc-abca-4658-8307-170b1284c60f

    Regards
    Srinivas Gudimella

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s