November 8, 2011 at 7:52 am
bopeavy (11/8/2011)
I know a lot of you have said that this relying on a bug, but is that the truth or merely how Microsoft designed it. I did my research although maybe not perfect, I can only test on the 3 2000 versions, 1 2005 version , 1 2008 version, 6 2008 R2 versions that I have available to me. It seemed to me it was an interesting issue and thought that I would share it with you, but you can't please everyone.
I like the question and what we learn with it. Just maybe a different choice of answer would be "better".
You're not the first one to be burned on this, and certainly not the last. Not that it's any fun!.. been there too
November 8, 2011 at 7:55 am
bopeavy (11/8/2011)
SQLRNNR (11-8-2011)
Query runs just fine in SQL 2008 R2 as well.What service pack are you on?
SP1
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 8, 2011 at 7:58 am
SQL Kiwi (11/8/2011)
vk-kirov (11/7/2011)
--------------------------------------------------------------------------------
Very interesting question, but 1) version-specific; 2) with wrong explanation.
Exactly. I was at a loss which answer to choose because they all seemed wrong. Turns out I was right
It's a poor question that relies on a bug in SQL Server which has long since been fixed. The 'explanation' is priceless too:
The Intersect command works much like the "and" operator and returns distinct values. In the query the order by clause causes a server error because the order by clause may only reference names or aliases from the left side query.
Rarely do I encounter so much nonsense in two short sentences.
Paul White
You can find the same Explantion right here http://msdn.microsoft.com/en-us/library/ms188055.aspx
November 8, 2011 at 8:01 am
SQLRNNR (11/8/2011)
bopeavy (11/8/2011)
--------------------------------------------------------------------------------
SQLRNNR (11-8-2011)
--------------------------------------------------------------------------------
Query runs just fine in SQL 2008 R2 as well.
What service pack are you on?
SP1
What Edition? It maybe related to edition, I have standard.
November 8, 2011 at 8:02 am
Ninja's_RGR'us (11/8/2011)
cfradenburg (11/8/2011)
For those with a SQL 2005 box you can test on, does the following result in the same error:
use masterselect fileid [file_id], name from sysfiles intersect
select file_id, name from sys.master_files
where file_id in('1')
order by 2
Yes
SP3-ish 32 bit.
Interesting. So specifying a named column to sort throws the same error and making it so the column names are the same throws the same error. That rules out the two bugs I thought the most likely. It makes me wonder what the bug is.
November 8, 2011 at 8:03 am
Ninja's_RGR'us (11/8/2011)
bopeavy (11/8/2011)
--------------------------------------------------------------------------------
I know a lot of you have said that this relying on a bug, but is that the truth or merely how Microsoft designed it. I did my research although maybe not perfect, I can only test on the 3 2000 versions, 1 2005 version , 1 2008 version, 6 2008 R2 versions that I have available to me. It seemed to me it was an interesting issue and thought that I would share it with you, but you can't please everyone.
I like the question and what we learn with it. Just maybe a different choice of answer would be "better".
You're not the first one to be burned on this, and certainly not the last. Not that it's any fun!.. been there too
I agree maybe I won't rely my answer so much on Microsoft Information next time.
November 8, 2011 at 8:05 am
bopeavy (11/8/2011)
Ninja's_RGR'us (11/8/2011)
bopeavy (11/8/2011)
--------------------------------------------------------------------------------
I know a lot of you have said that this relying on a bug, but is that the truth or merely how Microsoft designed it. I did my research although maybe not perfect, I can only test on the 3 2000 versions, 1 2005 version , 1 2008 version, 6 2008 R2 versions that I have available to me. It seemed to me it was an interesting issue and thought that I would share it with you, but you can't please everyone.
I like the question and what we learn with it. Just maybe a different choice of answer would be "better".
You're not the first one to be burned on this, and certainly not the last. Not that it's any fun!.. been there too
I agree maybe I won't rely my answer so much on Microsoft Information next time.
It's been known to fail.... more than once. way more. I'd have to say it's better to retest their code in BOL before doing anything with it.
November 8, 2011 at 8:07 am
bopeavy (11/8/2011)
I agree maybe I won't rely my answer so much on Microsoft Information next time.
You're not the first to have this happen. Both due to ambiguity and due to outright incorrect statements.
November 8, 2011 at 8:08 am
bopeavy (11/8/2011)
You can find the same Explantion right here http://msdn.microsoft.com/en-us/library/ms188055.aspx
See Hugo's reply for a detailed explanation of why your explanation is wrong. I'm not getting further involved.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 8, 2011 at 8:27 am
bopeavy (11/8/2011)
SQLRNNR (11/8/2011)
bopeavy (11/8/2011)
--------------------------------------------------------------------------------
SQLRNNR (11-8-2011)
--------------------------------------------------------------------------------
Query runs just fine in SQL 2008 R2 as well.
What service pack are you on?
SP1
What Edition? It maybe related to edition, I have standard.
Developer/Enterprise
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 8, 2011 at 8:32 am
Hugo Kornelis (11/8/2011)
Not a very good question, but I can forgive the author for that. Reading the reactions so far, there are various versions of SQL Server where this will blow up. However, if the author had decided to investigate until he found the cause of this error, he would probably have found that the problem is fixed.
First off any question is a good question.
But I do blame the author for submitting a totally inadequate explanation. I can see how he ran into a surprising issue, and decided to share his experience by making it into a QotD. But he really should not have submitted it until he had a good explanation instead of the nonsense he submitted. Allow me to disect the explanation.
The Intersect command works much like the "and" operator
No, it does not. The "and" operator can only be used between two logical expressions and returns the result of a boolean and between the two expression. The intersect operator appears between two queries and returns the result of intersecting the result sets of the left-hand and right-hand query. The only similarity between the two is that both sit between two other things.
Secondly, yes I did not give the best explantion. I could of said something more like "The Intersect command works much like the "and" operator to filter out necessary data" or what ever.
and returns distinct values
.
True, but as far as I can tell not at all related to the bug that the question is about.
Is it a bug has it been fixed? I have SQL Server 2008 R2 standard edition with SP1 which did produce the issue.
In the query the order by clause causes a server error because the order by clause may only reference names or aliases from the left side query.
No, not true (*). The text in Books Online reads: "Column names or aliases in ORDER BY clauses must reference column names returned by the left-side query", and that is definitely not the same as the rewording the question author uses. Books Online does not forbid the use of ordinals in the ORDER BY clause; it merely states that IF you use column names or aliases, they must match those in the left side query.
Also, restrictions like this don't typically lead to severe errors. If you want to see the error message given when this restriction is violated, run the query from the question but change the order by clause to ORDER BY file_id - you'll get an error, but not the severe error that the original query aparently gives on some versions of SQL Server.
(*) I only have SQL Server 2008 R2 with SP1 installed, so I can't reproduce the bug. Maybe someone who does have an older version with the bug still in the code base can run the script from the QotD but change "ORDER BY 2" to "ORDER BY name" - I suspect that the result will still be the same severe error, but if that's not the case, I'll have to swallow my words.
None of the explanation, nor anything in the Books Online article the author mentions as reference, explains in any way why this query bombs. Which is not a surprise, if you consider that this is in fact a bug, that has already been fixed in newer versions of SQL Server.
I understand you did not like how I worded what ever. The best way of dealing with that is to submit your on QoTD.
November 8, 2011 at 8:34 am
i too ran it in deanli and results returned but when tried on earlier versions, that is when it failed...
November 8, 2011 at 8:37 am
bopeavy (11/7/2011)
Comments posted to this topic are about the item <A HREF="/questions/Intersect/74762/">Intersect</A>
Confirmed. No bug in this version => Microsoft SQL Server "Denali" (CTP3) - 11.0.1515.0 (Intel X86)
Jul 11 2011 15:33:17
Copyright (c) Microsoft Corporation
Enterprise Evaluation Edition on Windows NT 6.0 <X86> (Build 6002: Service Pack 2)
November 8, 2011 at 8:55 am
bopeavy (11/8/2011)
SQL Kiwi (11/8/2011)
vk-kirov (11/7/2011)
--------------------------------------------------------------------------------
Very interesting question, but 1) version-specific; 2) with wrong explanation.
Exactly. I was at a loss which answer to choose because they all seemed wrong. Turns out I was right
It's a poor question that relies on a bug in SQL Server which has long since been fixed. The 'explanation' is priceless too:
The Intersect command works much like the "and" operator and returns distinct values. In the query the order by clause causes a server error because the order by clause may only reference names or aliases from the left side query.
Rarely do I encounter so much nonsense in two short sentences.
Paul White
You can find the same Explantion right here http://msdn.microsoft.com/en-us/library/ms188055.aspx
Where on that page is there even a suggestion that the restriction of column names and aliases in the order by clause of a select statement to those that exist in the resulting rowset could cause an error in a query where the order by clause contains no column names or aliases, let alone an outright statement that it does so in the case of intersect statements but not others.
You clearly didn't understand what was going on, apparently didn't even realise you were observing a bug because you somehow misinterpreted that BoL page so as to believe that it madated this nbuggy behaviour, and even after it has pointed out to you that your explanatiuon was wrong you still apparently believe that the explanation was correct.
The original mistake is excusable, not that big a problem; but with the above statement you have turned it into a big problem by refusing to accept that you made a mistake.
I believe the BoL page is absolutely clear; but if you have difficulty understanding it, read Hugo's detailed remarks on your explanation in his comment posted earlier today.
Tom
Viewing 15 posts - 31 through 45 (of 63 total)
You must be logged in to reply to this topic. Login to reply