Home Dashboard Directory Help
Search

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


Status: 

Closed
 as By Design Help for as By Design


1
2
Sign in
to vote
Type: Bug
ID: 772612
Opened: 11/28/2012 3:58:20 PM
Access Restriction: Public
0
Workaround(s)
view
0
User(s) can reproduce this bug

Description

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
Sign in to post a comment.
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
Sign in to post a workaround.