question

SQLcuriosity avatar image
SQLcuriosity asked

Complicated problem with temp table column aliases used in a subquery

Came across a problem relating to temp table column alias used within a subquery. What it boils down to is I can reference a column name which doesn't exist within the temp table used in the subquery, and the outer query returns ALL rows regardless of the erroneous predicate. This may be to do with the window function used within the temp table because I haven't been able to reproduce the problem by simply renaming/aliasing an existing column. My prod server at work is SQL 2012 but I've just tested on my SQL 2016 developer install at home and the issue is still present. I will provide sample SQL below. Please don't read too much into *what* I'm trying to do - all I've done is create a vastly simplified version of the data I'm working with just to make this easier to explain: --Create database USE MASTER; IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = 'DB1') BEGIN DROP DATABASE DB1 END; CREATE DATABASE DB1 GO USE DB1; --Create and populate table CREATE TABLE dbo.People ( ID INT IDENTITY, PersonName VARCHAR(20), Gender CHAR(1), DoB DATE); INSERT dbo.People (PersonName, Gender, DoB) VALUES ('Bob','M', '1-Jun-1981'), ('Dave','M', '1-jul-1981'), ('Tim','M','1-aug-1981'), ('Jenny','F','1-sep-1981'), ('Becky','F','1-oct-1981'), ('Sarah','F','1-nov-1981'); --Create and populate temp table --This determines the max DoB for males and females IF OBJECT_ID('tempdb..#tmp') IS NOT NULL BEGIN DROP TABLE #tmp END; SELECT DISTINCT Gender, MAX(DoB) OVER (PARTITION BY Gender) AS maxdob INTO #tmp FROM dbo.People; --Now here are the queries --The first query should produce an error because there is no column called DoB in #tmp table --Verify by running the subquery on it's own --Instead it returns ALL rows from the table (could be embarrassing if the subquery is used as part of a DELETE statement) SELECT * FROM dbo.People WHERE DoB IN (SELECT DoB FROM #tmp); --The second query works exactly as expected SELECT * FROM dbo.People WHERE DoB IN (SELECT maxdob FROM #tmp); My question - is my assumption correct that the first query should produce an error message? Or is there something I'm missing?
sub-querytemporary-tablealias
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
No, the Query should not return an error message, because in the scope of the subquery, the name DoB is valid. It is taken from the outer Query. The subquery will, for each row in #tmp, select DoB from the outer Query current row. This is why you want to use table aliasing Always. This would produce the expected error: SELECT P.* FROM dbo.People AS P WHERE DoB IN (SELECT T.DoB FROM #tmp AS T)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

SQLcuriosity avatar image
SQLcuriosity answered
Thank you for the explanation, didn't occur to me to alias the table inside the subquery. Table aliasing will now be used in all of my queries! Thank you
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.