sql bug - something to watch out for 1

[espar]

Try this - (don't do it on any data you want !!)

create a temp table - then perform the following update query.

update tableA set columna=1 where columnb in (select columndoesntexist from #temptable)

Using SQL 2K - stored proc this query (which should return an error) actually updates ALL rows in tableA !!

ie. the sub query is selecting a column that doesn't exist.

Thankfully i did this on a dev server - phew....

PS also performs same nastiness through query analyzer

 

[clarkin]

in QA:

SET NOCOUNT ON

CREATE TABLE cTest (
 columnA int NULL,
 columnB int NULL
)
CREATE TABLE #temp (
 ignoreThis int NULL
)

INSERT INTO cTest VALUES (1,0)
INSERT INTO cTest VALUES (1,0)
INSERT INTO cTest VALUES (2,2)
INSERT INTO cTest VALUES (2,2)
INSERT INTO cTest VALUES (3,1)

SELECT * FROM cTest
SELECT * FROM #temp

UPDATE cTest SET columnA=1 WHERE columnB IN (SELECT columnDoesntExist FROM #temp)

DROP TABLE #temp
DROP TABLE cTest

Which correctly gives:
Server: Msg 207, Level 16, State 3, Line 20
Invalid column name 'columnDoesntExist'.

See if you can get yours to do it again with a script like above.


Posting code? Wrap it with code tags: [ignore]

[/ignore][code]CodeHere

[ignore][/code][/ignore].

 

[espar]

Thanks c,

you're code worked fine - returned errors fine.

Please try this code which still breaks for me, can't quite see what is causing this yet, but really want to know.

SET NOCOUNT ON
declare @serviceid int
set @serviceid=10002
create table service (serviceid int, locked int)
insert into service values (10000, 0)
insert into service values (10001, 0)
insert into service values (10002, 0)
insert into service values (10003, 0)

create table #moveme (
[order] int,
thisservicename varchar(100),
thisservicestatusname varchar(100),
thisserviceid int,
thisservicetypename varchar(100),
thisservicetype varchar(100),
thisserviceerrors varchar(100)
)

insert into #moveme (thisservicename, thisservicestatusname, thisserviceid, thisservicetypename, thisservicetype, thisserviceerrors)
values (null, null, @serviceid, 'hosting', 'virtual hosting', null)

select * from #moveme

select * from service
update service set locked = 1 where serviceid in (select serviceid from #moveme)

drop table #moveme
select * from service
drop table service

 

[clarkin]

ok here are some results and the offending line of code.

[tt]update service set locked = 1 where serviceid in (select serviceid from #moveme)[/tt]
-> updates all rows to 1, no error message

[tt]update service set locked = 1 where serviceid in (select thisserviceid from #moveme)[/tt]
-> updates correct row to 1, no error message

[tt]update service set locked = 1 where serviceid in (select someRandomColumn from #moveme)[/tt]
-> produces expected error message

and to explain it - in your subquery you can reference columns from your outer query.
[tt]update service set locked = 1 where serviceid in (select serviceid from #moveme)[/tt]
serviceid -> matches service.serviceid
serviceid -> first checks #moveme for a column match, then checks parent query and matches service.serviceid

This is useful for when you want to match stuff between subqueries and their parent queries. For you - just avoid column names from table service in your sub query.

good luck








Posting code? Wrap it with code tags: [ignore]

[/ignore][code]CodeHere

[ignore][/code][/ignore].

 

[espar]

Excellent thanks c,

I accept that this is what is happening...

except to be honest, i have joined on the where clause from a sub query, but to select from the sub query data from the parent into the clause of a where on the parent query ?? Why on earth would you want to do that - to compare the same objects - well duh they will be the same.

If you can give me an example of this being useful rather than just plain dangerous then you definitely get a star (you probably deserve one anyway but i would like a reason for this behaviour.)

Thanks

 

[clarkin]

Certainly I can even make your update easier using this behaviour - its a key way of linking subqueries to their parents.

[tt]update service set locked = 1 where exists (select thisserviceid from #moveme where thisserviceid = serviceid)[/tt]

I've used EXISTS instead of IN, but both use subqueries.

What happens? this updates your service table locked=1 where you have a matching thisserviceid in #moveme

I'm sure you can think of many applications of this. I know I use it constantly. (side note, try to explicitly specify table.columnname to avoid confusion)


Posting code? Wrap it with code tags: [ignore]

[/ignore][code]CodeHere

[ignore][/code][/ignore].

 

[espar]

Hi c,

yep, that is what i have used this for however notice your query:
update service set locked = 1 where exists (select thisserviceid from #moveme where thisserviceid = serviceid)

You have basically rewritten:
update where serviceid in (select thisserviceid from #moveme)
NOT given a reason/use for:
update where serviceid in (select serviceid from #moveme)

The second is completely nonsensical if it refers to the serviceid in the outer/ parent update.

I am happy someone gave you a star but you still haven't given a useful way of using this feature.

 

[clarkin]

The same column-searching logic is used in both IN and EXISTS as they both use sub queries - I can't think of why you would want [tt]update where serviceid in (select serviceid from #moveme)[/tt] to check the parent query but the fact is it does.

You can stop this kind of confusion if you use the table.column syntax.
So your original problematic line of code would be:
[tt]update service set service.locked = 1 where service.serviceid in (select #moveme.serviceid from #moveme)[/tt]
which, as you would expect, comes up with column not found.

I guess the moral is - refer to all columns with the table.column syntax. If you don't, just make sure there isn't any possible ambiguity of names: either between tables or between queries/subqueries.

Hope that helps


Posting code? Wrap it with code tags: [ignore]

[/ignore][code]CodeHere

[ignore][/code][/ignore].

 

[espar]

Yep, thanx c. A good one to be careful of.

I suppose i shouldn't be so lazy and not qualify my column names, but i know i'll probably be feeling lazy tomorrow...

he he.