Alias error not being reported when within an IN operator by aberrated_grey_cells

Closed

as By Design Help for as By Design

Type:	Bug
ID:	772612
Opened:	11/28/2012 3:58:20 PM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

When we query a column name that has been aliased with its old variable name, then an error is returned which is correct but if you use the same query within an IN operator, SQL does not return any error. Please look at steps to reproduce

Details (expand)

English

SQL Server 2012 - Enterprise Edition

SQL Engine

Windows 7 Professional

US English

For example

STEP 1: Create a temporary table TABLE1 using column COL1 from another temp table TAB and alias the existing column name from COL1 to NBR

SELECT COL1 AS NBR
INTO #TABLE1 FROM #TAB

STEP 2: Query from TABLE1 using the old column name COL1.

select distinct COL1 from #TABLE1

STEP 3: Now use the same query albeit within an IN operator in a select statement

select COL1 from TABLE2
where COL1 in (select distinct COL1 from #TABLE1)

Here there is no error returned.

STEP 1: Table is created
STEP 2: An error "Invalid column name 'COL1' is returned which is correct
STEP 3: No error message

Step 3) An error message should be returned stating
"Invalid column name 'COL1' .

In this scenario, regardless of where I place the query from STEP 2 it should always return me an error

0 attachments

Posted by Microsoft on 12/5/2012 at 11:37 AM

Hello,

The behavior you are seeing is by design. The SELECT statement behavior conforms to the ANSI SQL specifications and pretty much every database system implements it the same way.

Basically, if you have a column reference in a correlated sub-query then we will try to match it first to the tables in the FROM clause of the sub-query and if we cannot find the column there we will try to resolve to the FROM clause in the outer scope. You can also find this documented at:

http://support.microsoft.com/kb/298674

To resolve these type of ambiguities, you have to use table aliases and use them in the column references.

--
Umachandar, SQL Programmability Team

Posted by Martin Smith on 12/2/2012 at 9:37 AM

It is not an error. It is referencing COL1 from TABLE2.

It is perfectly valid for a sub query to reference columns from the outer query. Get into the habit of using qualified column names if you would like it to raise an error

select TABLE2.COL1
from TABLE2
where TABLE2.COL1 in (select distinct #TABLE1.COL1 from #TABLE1)

would raise an error that there is no such column in #TABLE1

Microsoft