Query should fail, but doesn't. Possible bug?

Question

Post reply

Query should fail, but doesn't. Possible bug?

mdyoung

SSC Enthusiast

Points: 134
More actions
April 26, 2010 at 4:56 pm

#223931

I've come across an issue with a query that, to me, should fail, but it doesn't. I was wondering if someone could explain to me why it doesn't fail. This issue exists with SQL Server 2005 and 2008. Could this possibly be a bug?
Below, I've created some testing code to demonstrate my concern. It seems to me that when you create a CTE, specify an alias name on a field you plan on using to filter with in a WHERE ... IN (...) statement of your final query, and you try to reference the actual field name instead of the alias in your WHERE statement, the query runs just fine without any errors; however, it does not return any records. I hope that made sense.
If not, run the code below to see what I'm talking about. I discovered this on a query I was writing when I inadvertently specified the actual field name instead of the alias name in my subquery within the WHERE statement of my resulting query.
WITH cte_TestRecs
AS (
SELECT 1000 AS TestField1
, 1 AS IsEven
UNION ALL
SELECT TestField1 + 1
, TestField1 % 2
FROM cte_TestRecs
WHERE TestField1 < 1100
)
, cte_Filter
AS (
SELECT TestField1 AS EvenField
FROM cte_TestRecs
WHERE IsEven = 1
)
SELECT *
FROM cte_TestRecs
WHERE TestField1 NOT IN (
/* This is the part that should fail because TestField1 was aliased in
the CTE. Running the query like this will NOT return any records; nor
will it give you an error stating an invalid column name. Changing
this to EvenField will return a recordset as expected.
*/
SELECT TestField1
FROM cte_Filter
)
Thanks,
Micheal
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 1

Classic.

SQL "knows" about the field because it is in the base table cte_TestRecs that you are querying.

If you properly qualify the names of the columns in your select, then you get an error.

WITH cte_TestRecs

AS (

SELECT 1000 AS TestField1

, 1 AS IsEven

UNION ALL

SELECT TestField1 + 1

, TestField1 % 2

FROM cte_TestRecs

WHERE TestField1 < 1100

)

, cte_Filter

AS (

SELECT TestField1 AS EvenField

FROM cte_TestRecs

WHERE IsEven = 1

)

SELECT *

FROM cte_TestRecs

WHERE TestField1 NOT IN (

/* This is the part that should fail because TestField1 was aliased in

the CTE. Running the query like this will NOT return any records; nor

will it give you an error stating an invalid column name. Changing

this to EvenField will return a recordset as expected.

*/

SELECT cte_Filter.TestField1

FROM cte_Filter

)

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

mdyoung SSC Enthusiast Points: 134 More actions · Answer 2

Thank you, Mister.Magoo. This makes perfect sense. I'll make sure I start qualifying my table names from now on to help eliminate this type of issue.

However, if you were to take the subquery as I have written it, place it after the CTE declarations and run the query up to that point, you will receive an "Invalid column name" error. So I'm still left wondering why it will fail this way and not when it is the subquery of the WHERE statement.

Thanks,

Micheal

mister.magoo SSC-Forever Points: 47174 More actions · Answer 3

Because when it is a subquery the column is a valid column of the main table/cte.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

mister.magoo SSC-Forever Points: 47174 More actions · Answer 4

if you take this simple example

Table1 ( a int, b int)

Table2 ( c int )

select t.a,t.b

from Table1 as t

where t.a not in (select a from Table2)

To the optimiser, this is the same as

select t.a,t.b

from Table1 as t

where t.a not in (select t.a from Table2)

and the code

select t.a from Table2

is equivalent in this case to selecting X rows of the value t.a where X is the count of rows in Table2

This means you would be asking for a filter where t.a is not in (lots of copies of t.a) - which will always return nothing as t.a is explicitly always part of (lots of copies of t.a).....if you see what I mean.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

mdyoung SSC Enthusiast Points: 134 More actions · Answer 5

Now that makes more sense. You've satisfied my curiosity and answered my question. Thank you once again.