May 11, 2006 at 4:42 pm
Try this code on both engines(2K & 2K5), note the difference. Perhaps a different “SET” option … curious, not sure.
SET NOCOUNT ON
CREATE TABLE a1 (a INT, b INT)
INSERT a1 SELECT 1, 1
UNION SELECT 2, 2
UNION SELECT 3, 3
SELECT a, *
FROM a1
ORDER BY a
Note that it works fine on on my 2K engine but on the 2K5 engine the error is "Msg 209, Level 16, State 1, Line ... Ambiguous column name 'a'.".
Now I can understand why I get the error (column [a] is both explict and implied), and I actually agree with it and the explicit naming it will force, I just never ran into it before. New versions are so much fun...
May 11, 2006 at 6:12 pm
That's interesting. Thanks for the update.
May 15, 2006 at 10:27 am
It really is interesting,
SELECT a, *
FROM a1
ORDER BY a1.a
GO
SELECT a, b
FROM a1
ORDER BY a
GO
SELECT a as [My a], *
FROM a1
ORDER BY a
all work fine.
But not
SELECT a, *
FROM a1
ORDER BY a
Prasad Bhogadi
www.inforaise.com
May 15, 2006 at 10:49 am
Huh, your example is even a bit more curious. I figured that the 2K5 engine does not like the "SELECT a, * " (original post) because it wants us to be explicit (good). But your first query (of the three),
SELECT a, *
FROM a1
ORDER BY a1.a
by qualifying the sort object (a1.a) does work. The odd thing is that there is a column "a" in both queries, so one would think that both would work. In reality, it seems that the query engine is saying it can not figure out which column "a" to use in the original query but with (in query 1 of 3 in your post) the qualifier in the order by it just chooses one, even though they are the same, and hence appears to demonstrate the very kind of implicit logic that is seems to be refusing to perform in the original case.
I know this is all a bit academic and we should all use well-qualified inserts, fully qualified names and generally be as explicit as possible (as demonstrated in the ANSI-92 article today), but still, the behaviour is rather curious.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply