April 26, 2010 at 4:56 pm
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
April 26, 2010 at 5:06 pm
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);April 26, 2010 at 5:20 pm
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
April 26, 2010 at 5:37 pm
Because when it is a subquery the column is a valid column of the main table/cte.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);April 26, 2010 at 5:43 pm
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);April 26, 2010 at 5:57 pm
Now that makes more sense. You've satisfied my curiosity and answered my question. Thank you once again.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply