Selecting non-existent row in temporary table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Selecting non-existent row in temporary table

Author

Topic

consulBanana
Starting Member

2 Posts

Posted - 2004-03-26 : 16:59:01

Okay, this has me stumped. I thought I had a serious bug in a stored procedure that was deleting every row in a table whenever it ran, but instead the query in question wasn't even running. The weird part is that I made a mistake in the subquery referencing a row in a temporary table that doesn't even exist! Can anyone verify that this script should definitely not work, or hand me the cluex4 so I can mash my own head in?

/*
@@VERSION

Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
*/

USE Northwind
GO
--Create a little table with car themed columns

CREATE TABLE MyCar
(
hornID UNIQUEIDENTIFIER,
brakeID UNIQUEIDENTIFIER
)
GO

--Insert a test row

INSERT MyCar(hornID, brakeID)
VALUES(NEWID(), NEWID())

--Use MyCar to create the temporary table MyShoe,
--with shoelaceID as a column

SELECT
brakeID shoelaceID
INTO #MyShoe
FROM MyCar

--Validate that #MyShoe has only one column, shoelaceID

SELECT *
FROM #MyShoe

--Try to select a column from #MyShoe that doesn't exist

SELECT *
FROM MyCar
WHERE hornID IN
(
SELECT hornID --!!! Why doesn't this give me an error? !!!
FROM #MyShoe
)
GO

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-03-26 : 17:10:04

This behavior has been seen before:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=31055&SearchTerms=,error,subquery

SELECT *
FROM MyCar
WHERE hornID IN
(
SELECT s.hornID --!!! Why doesn't this give me an error? !!!
FROM #MyShoe s
)

consulBanana
Starting Member

2 Posts

Posted - 2004-03-26 : 17:36:06

Truly I do see the light. Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources