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 (https://blog.sqlauthority.com)