SELECT WHERE IN (Invalid SQL Statement)

Question

SELECT WHERE IN (Invalid SQL Statement)

McDonB

SSC Veteran

Points: 294
More actions
February 25, 2004 at 8:12 am

#109059

We found this while testing, and accidently hitting production instead of development. I have a SELECT Statement that is using an WHERE IN (SELECT Query) clause to return rows, the column I am testing on does not exists in the table. Instead of the expected error "Invalid Column Name", it returns results. I think it may be a bug that needs to be reported, but wanted to see if anyone else had seen this.
I assume it is using the valid column from the first table, instead of the one in the subquery. In the query below, s00_company_name does not have a column called WFL_ID.
Query:
Select WFL_ID,WFL_Desc,WFL_Project
from W11_WORKFLOW
where WFL_ID in (Select DISTINCT wfl_id from s00_company_name)
order by wfl_id

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

I have seen that before and YES you are correct is using the filed name of the wrong table:

One of the Best practices that MS is distributing with the SQL Server Best practices Analyzer is to Qualify the TAble name to EACH field on the Select Part. If you would have done that you would have gotten the Error

For more on SSBPA go here

* Noel

McDonB SSC Veteran Points: 294 More actions · Answer 2

Thanks for the response.

Would agree that is a "Best Practice", but also would think the correct way to process this query would be to validate each SQL statement, starting with the Sub-Queries.

noeld SSC Guru Points: 96590 More actions · Answer 3

Because the column does exists SS is trying to find out what is the purpose of the query an generated a plan!

IN SS 2000 (FROM BOL)

Referencing Objects

SQL Server allows the creation of stored procedures that reference tables that do not yet exist. At creation time, only syntax checking is done ...

I know that the syntax check could had been better but I have no clue on what plan it came up with in this case. you may be able to verify that!

HTH

* Noel

linto SSC Eights! Points: 851 More actions · Answer 4

I got an error while trying to run a query in a similiar way..

Whats the difference??

I got this error message

Server: Msg 207, Level 16, State 3, Line 1

Invalid column name 'XXXXXX'.

Let me know what you are doing... so that i can try myself to reproduce it!!

Thanks

Linto

noeld SSC Guru Points: 96590 More actions · Answer 5

IF You want to reproduce it

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

IF OBJECT_ID('W11_WORKFLOW') IS NOT NULL

DROP TABLE W11_WORKFLOW

IF OBJECT_ID('s00_company_name') IS NOT NULL

DROP TABLE s00_company_name

IF OBJECT_ID('prc_Test') IS NOT NULL

DROP PROCEDURE prc_Test

CREATE TABLE W11_WORKFLOW(WFL_ID int ,WFL_Desc varchar(100),WFL_Project int)

GO

CREATE TABLE s00_company_name(WFL int, DESCR varchar(100))

GO

CREATE PROCEDURE prc_Test

AS

Select WFL_ID,WFL_Desc,WFL_Project

from W11_WORKFLOW

where WFL_ID in (Select DISTINCT wfl_id from s00_company_name)

order by wfl_id

GO

INSERT INTO W11_WORKFLOW(WFL_ID ,WFL_Desc ,WFL_Project) VALUES (1,'this is a test1',1)

INSERT INTO W11_WORKFLOW(WFL_ID ,WFL_Desc ,WFL_Project) VALUES (2,'this is a test2',2)

INSERT INTO W11_WORKFLOW(WFL_ID ,WFL_Desc ,WFL_Project) VALUES (3,'this is a test3',3)

INSERT INTO s00_company_name(WFL , DESCR) VALUES (1,'this is a test')

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

(Verify that the SP Gets created and that it RUNS )

* Noel

wz700 SSCertifiable Points: 7133 More actions · Answer 6

Only happen when outside table column name (exists)= inside column name (not exists)

select * from from table1 where colname in (select colname from table2)

It is correct for following

select * from from table1 where colname in (select table2.colname from table2)

SQL Server confuses on colname as which tables it belongs.