March 13, 2019 at 10:35 pm
Comments posted to this topic are about the item T-SQL correlated queries
March 13, 2019 at 10:40 pm
I had to cheat to get this and actually run the code. I got suspicious when I couldn't see the answer I was expecting "Invalid column name 'c1'."
Personally I think that's a bug, because the inner select returns no rows and should therefore be equivalent to
select c1
from dbo.t1
where c1 in ()
which returns a syntax error (but I can also understand an empty set as there's nothing inside the IN statement).
March 13, 2019 at 11:44 pm
Toby Harman - Wednesday, March 13, 2019 10:40 PMI had to cheat to get this and actually run the code. I got suspicious when I couldn't see the answer I was expecting "Invalid column name 'c1'."
Personally I think that's a bug, because the inner select returns no rows and should therefore be equivalent to
select c1
from dbo.t1
where c1 in ()
which returns a syntax error (but I can also understand an empty set as there's nothing inside the IN statement).
Nice question, thanks Evgeny.
Tony, this is not a bug, but a side effect of correlated subqueries.
all columns from the main SELECT are available for use in the subselect, so it will compile.
per this extract from Microsoft docs:
Important
If a column is referenced in a subquery that does not exist in the table referenced by the subquery's
FROMclause, but exists in a table referenced by the outer query'sFROMclause, the query executes without error. SQL Server implicitly qualifies the column in the subquery with the table name in the outer query.
____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
March 14, 2019 at 12:03 am
Thanks Stuart
So the subquery becomes (SELECT t1.c1 from t2) which makes even less sense to me!
I appreciate that t1 is in scope and therefore can be parsed and used, and that MS have documented this behaviour, but I doubt many would find it desirable!
March 14, 2019 at 2:18 am
Toby Harman - Thursday, March 14, 2019 12:03 AMThanks Stuart
So the subquery becomes (SELECT t1.c1 from t2) which makes even less sense to me!
I appreciate that t1 is in scope and therefore can be parsed and used, and that MS have documented this behaviour, but I doubt many would find it desirable!
Toby it might make more sense if you were doing something like this:
(SELECT t1.c1 * t2.c1 FROM t2)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 14, 2019 at 4:58 am
Nice example to demonstrate that you should name your tables and columns well, to avoid confusion.
March 14, 2019 at 5:48 am
Another reason why you should always prefix the column names with the table name (or alias) .
March 14, 2019 at 6:26 am
Jonathan AC Roberts - Thursday, March 14, 2019 5:48 AMAnother reason why you should always prefix the column names with the table name (or alias) .
Indeed!select t1.c1
from dbo.t1
where t1.c1 in (select t2.c1 from t2)
That would give the expected error.
I'll admit: if I had seen the error I expected in the options, I might not have looked at the code closely enough to get it right.
March 14, 2019 at 8:06 am
Thanks for this instructive question!
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
March 14, 2019 at 1:10 pm
Jonathan AC Roberts - Thursday, March 14, 2019 5:48 AMAnother reason why you should always prefix the column names with the table name (or alias) .
Definitely. Similar to why I always put brackets around logic statements to make it very clear the order of precedence otherwise one small change elsewhere can break an entire query...
March 14, 2019 at 6:05 pm
Stewart "Arturius" Campbell - Wednesday, March 13, 2019 11:44 PMNice question, thanks Evgeny.Tony, this is not a bug, but a side effect of correlated subqueries.
all columns from the main SELECT are available for use in the subselect, so it will compile.
per this extract from Microsoft docs:Important
If a column is referenced in a subquery that does not exist in the table referenced by the subquery's
FROMclause, but exists in a table referenced by the outer query'sFROMclause, the query executes without error. SQL Server implicitly qualifies the column in the subquery with the table name in the outer query.
Thanks Stewart for clear explanation and reference to Microsoft docs.
The explanation given in QotD is not accurate.
March 21, 2019 at 9:17 am
Toby Harman - Thursday, March 14, 2019 12:03 AMThanks Stuart
So the subquery becomes (SELECT t1.c1 from t2) which makes even less sense to me!
I appreciate that t1 is in scope and therefore can be parsed and used, and that MS have documented this behaviour, but I doubt many would find it desirable!
Many of us find it desirable when we're actually designing correlated subqueries. It certainly makes no sense to use a column from the outer query in the column list (unless it is in a complex calculation). But it makes sense when using them on other sections on the query. e.g. the alternative to the correct query.
SELECT c1
FROM dbo.t1
WHERE EXISTS ( SELECT * FROM dbo.t2
WHERE t1.c1 = t2.c2);
April 18, 2019 at 1:06 pm
This is a "Works As Designed" feature that makes me wonder just what the logic was for that design.
40 years of experience shows a pattern to this; there is a warning in the documentation which is a hint that it's just how it is, and by documenting it any weird behaviour becomes WAD instead of a bug.
Usually this kind of thing comes up during Beta testing, and we push the manufacturer to document it because it's so damn weird that no-one would expect it to happen.
April 18, 2019 at 1:18 pm
This is a "Works As Designed" feature that makes me wonder just what the logic was for that design. 40 years of experience shows a pattern to this; there is a warning in the documentation which is a hint that it's just how it is, and by documenting it any weird behaviour becomes WAD instead of a bug. Usually this kind of thing comes up during Beta testing, and we push the manufacturer to document it because it's so damn weird that no-one would expect it to happen.
Which aspect of correlated subquery activity are you criticizing here Paul?
It always seemed perfectly sensible to me, apart from an omission - when a column is available from either the inner or the outer query, SQL Server should require aliasing and error with "Ambigous columnname" where appropriate. This alone would eliminate most of the disputes over correlated subquery peccadilloes.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply