November 8, 2011 at 3:43 am
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
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 8, 2011 at 3:54 am
good question!!
November 8, 2011 at 4:19 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.
Not just me then
November 8, 2011 at 4:42 am
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.
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.
and returns distinct values.
True, but as far as I can tell not at all related to the bug that the question is about.
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.
November 8, 2011 at 5:36 am
I guessed the right answer but it is a version specific issue.
http://brittcluff.blogspot.com/
November 8, 2011 at 5:48 am
Hugo Kornelis (11/8/2011)
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.
The oldest SQL Server I have to hand is 2005 build 9.0.5254 (SP4 CU1). The query produces the 'severe error' with both "ORDER BY 2" and "ORDER BY name" - and trying to get an estimated plan is enough.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 8, 2011 at 5:53 am
Koen Verbeeck (11/8/2011)
Here's a screenshot (in attachment).
Got the same result. Sql 2K5 Sp3-ish, 32 bit.
November 8, 2011 at 6:19 am
Koen Verbeeck (11/8/2011)
SQLRNNR (11/8/2011)
Koen Verbeeck (11/8/2011)
SQLRNNR (11/7/2011)
Query runs just fine in SQL 2008 R2 as well.I ran it in SQL Server 2008R2, and I got the severe error.
The plot thickens... 🙂
Maybe it was fixed with SP1?? I see you are running RTM and I have SP1 installed...:hehe:
Ah, keen observation 🙂
Brand new development server, so SP1 isn't installed yet.
This reminds me that I should pick this up with the DBA's.
If this bug has been fixed ... fixed with which Service Pack ? ? ?
Got the error message using @@Version returned:
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86)
Apr 2 2010 15:53:02
Copyright (c) Microsoft Corporation
Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
November 8, 2011 at 7:25 am
I got it right, because I knew none of the other answers was correct, but I don't think the answer had the best wording.
I ran it on a dev server and the severe error scared the crap out of me.
Got the sever error on 2005 SP4 (9.00.5000.00) and 2008 SP2 (10.0.4064.0).
I don't have an R2 or 2012 server to try it against.
I'll echo the question, what version and service pack was this bug fixed in?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 8, 2011 at 7:29 am
Jack Corbett (11/8/2011)
I got it right, because I knew none of the other answers was correct, but I don't think the answer had the best wording.I ran it on a dev server and the severe error scared the crap out of me.
Got the sever error on 2005 SP4 (9.00.5000.00) and 2008 SP2 (10.0.4064.0).
I don't have an R2 or 2012 server to try it against.
I'll echo the question, what version and service pack was this bug fixed in?
My version info is below. The bug has been fixed on it, thus, I don't get any error when I run the statement.
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) Jun 17 2011 00:54:03 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
Kwex.
November 8, 2011 at 7:33 am
(*) 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.
On SQL Server 2005 (9.0.4053), running the query with ORDER BY name, even killed my instance (stopped and I had to start it again). The error was Msg 109, Level 20, State 0, Line 0
A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 - The pipe has been ended.)
November 8, 2011 at 7:44 am
For those with a SQL 2005 box you can test on, does the following result in the same error:
use master
select fileid [file_id], name from sysfiles intersect
select file_id, name from sys.master_files
where file_id in('1')
order by 2
November 8, 2011 at 7:48 am
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.
November 8, 2011 at 7:51 am
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.
November 8, 2011 at 7:51 am
SQLRNNR (11-8-2011)
Query runs just fine in SQL 2008 R2 as well.
What service pack are you on?
Viewing 15 posts - 16 through 30 (of 63 total)
You must be logged in to reply to this topic. Login to reply