November 8, 2011 at 9:01 am
thanks everyone for the discussion this morning. glad i wasn't the only one trying to make heads or tails of this QoD... :crazy:
November 8, 2011 at 9:10 am
L' Eomot Inversé (11/8/2011)
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.
No difficulty at all, I already said I did not do the best explaning it. The question of the day is about making you think, so it looks like it did just that. I did not say that the page mandated anything, there is obviously plenty going on because some have got the same error and some who did not. The issue was something that I faced a while back and agreed that I did not explan it the best.
November 8, 2011 at 9:33 am
SQL Kiwi (11/8/2011)
http://connect.microsoft.com/SQLServer/feedback/details/630087/intersect-with-order-by-abends-when-data-types-are-implicitly-converted-on-right-hand-side
Thanks. I was wondering what the bug was and couldn't track it down. Implicit conversion makes sense.
November 8, 2011 at 9:34 am
Question was fine. Got it right, and probably for the wrong reason. But after I ran it on my test data server it proved I was correct as far as what I have running.
Your explaination was fine. Made us all think. Thanks for pushing the gray matter to quit thinking about TV or football.
:-):-):-)
Not all gray hairs are Dinosaurs!
November 8, 2011 at 9:39 am
bopeavy (11/8/2011)
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.
In the context of "hey, I run into an issue I don't understand, can someone help me", I absolutely agree that all questions are good, and that the only dumb question is a question not asked.
But in the context of the QotD, which is like a knowledge quiz, I disagree. There absolutely have been very bad questions. But to make sure there is no misunderstanding: I don't consider this question to be a bad question. Just "not very good", for two reasons: (1) the "correct" answer is only true for certain versions and patching levels, but the question is worded as if it applies to all versions and patching levels, and (2) I don't generally like ANY questions about product bugs, because I believe the QotD should test the level of knowledge of the product, not the number of bugs one has accidentally ran into (but there have been questions about bugs before, so I guess Steve thinks they're okay - and Steve runs this site, so his will is law).
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.
I still completely disagree. INTERSECT and AND are two completely unrelated operators. I can't even describe why they are not similar, because they are too far removed to even consider similarities and differences. This is not like comparing apples to oranges (both fruit; a fair comparison), but like comparing apples to sheep. I lack the words to explain why comparing apples to sheep makes no sense, and I also lack the words to explain why AND and INTERSECT are completely disjunct.
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?
Hmmm, my wording was confusing. What I meant to say was that I fail to see how the distinct-ness of INTERSECT contributes to the error that your query produces on some versions of SQL Server.
I have SQL Server 2008 R2 standard edition with SP1 which did produce the issue.
That really surprises me. I have 2008R2 SP1 as well, and the query runs fone for me.
Also, in the bug report (see link in Paul's post) Microsoft has closed the bug as fixed, with a comment that the fix will be included in the next CU. This was in January 2011, well before SP1, so the bug fix should be in SP1 as well.
In other words, your tests on 2008R2 with SP1 should not reproduce this bug!
I understand you did not like how I worded what ever.
What I don't like is the total lack of an explanation. Your explanation gives us some (partially correct) information about INTERSECT, but nothing in that information explains why this happens. The correct explanation has now been given by Paul: the link to the Connect item where the bug is reported. This item tells us exactly what triggers the bug: the implicit convert on the right-hand side in an INTERSECT with ORDER BY. Removing the ORDER BY or the implicit conversion makes the query run; any other change doesn't.
That is the link you should have included in your explanation!
The best way of dealing with that is to submit your on QoTD.
<grin>
I would not dish out the way I do if I had not been on the receiving end already. I have had 17 questions published already, and received lots of feedback. Including feedback that pointed out exactly how much my questions sucked. I've taken that criticism to heart, and tried to improve my questions.
I now have three questions pending publication. They will all be published in November. You can be the judge to see if my newer questions still suck as much as my older questions.
PS: Please don't let the harsh criticism keep you from submitting more questions in the future. My feedback is not intended to scare you away, but to make you see where you could improve, just as I try to improve based on the feedback I have gotten.
November 8, 2011 at 9:41 am
To clarify what the following statement from BOL means:
Column names or aliases in ORDER BY clauses must reference column names returned by the left-side query.
That means if you try ordering by a column name specified only in the right side of the query, as below, you'll get an error:
select fileid [col1], name [col2] from sysfiles intersect
select file_id [col3], name [col4] from sys.master_files
where file_id in('1')
order by col3
The error is pretty specific as to what's going on:
Msg 207, Level 16, State 1, Line 5
Invalid column name 'col3'.
Msg 104, Level 16, State 1, Line 5
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.
November 8, 2011 at 9:45 am
What Hugo said. Again.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 8, 2011 at 10:08 am
I ran this query on 2008R2 SP1 and it worked just fine so i wasn't sure which answer to choose....:cool:
November 8, 2011 at 11:37 am
Hugo Kornelis (11/8/2011)
PS: Please don't let the harsh criticism keep you from submitting more questions in the future. My feedback is not intended to scare you away, but to make you see where you could improve, just as I try to improve based on the feedback I have gotten.
No i do not, I take the criticism lightly and try to learn from my mistakes. I have only been in the industry for 4 years and I am always learning that is the great thing about this industry..It never gets DULL.
November 8, 2011 at 7:20 pm
I got it right because the only thing that looked wrong for me in the query was the "2" in the "ORDER BY": there were no column names "2" 🙂
So I got it right for the wrong reason.
The query in QOD gave me the severe error message
I did some test with normal table on SQL 2008 and got no error message
====================================
use AdventureWorks2008;
go
CREATE TABLE dbo.T1(
[myID] [smallint] IDENTITY(1,1) NOT NULL,
[Name] [dbo].[Name] NOT NULL,
)
GO
CREATE TABLE dbo.T2(
[myID] [smallint] IDENTITY(1,1) NOT NULL,
[Name] [dbo].[Name] NOT NULL,
)
GO
INSERT INTO dbo.t1 (Name) values(10)
INSERT INTO dbo.t1 (Name) values(10)
INSERT INTO dbo.t1 (Name) values(10)
INSERT INTO dbo.t1 (Name) values(40)
INSERT INTO dbo.t2 (Name) values(10)
INSERT INTO dbo.t2 (Name) values(20)
INSERT INTO dbo.t2 (Name) values(10)
INSERT INTO dbo.t2 (Name) values(10)
select myID, name from dbo.T1
intersect
select myID, name from dbo.T2
where Name in('10')
order by 1 desc
drop table dbo.t1
drop table dbo.t2
======================
So, why a simple query works, but not the query in QOD? From the six pages of discussion, I understand that the reason of the error was a bug in most of SQL server versions. But the reason of the bug is because the query is done on the master or because there is "a non-explicit type conversion on the right hand side" (but I see no conversion in the question query)?:crazy:
Thanks for the question and the discussions. I learn at least that "ORDER BY 2" means "order by the second term in the select :laugh:
November 9, 2011 at 1:58 am
But the reason of the bug is because the query is done on the master or because there is "a non-explicit type conversion on the right hand side" (but I see no conversion in the question query)?:crazy:
It is the implicit conversion.
In the QotD query, there are a total of two implicit conversions. My instance of SQL Server already has the bug-fix, so I can't test which of the two conversions is the root cause of the error.
Conversion 1: fileid in sysfiles is smallint; file_id in sys.master_files is int. For the INTERSECT, they need to be the same type. Smallint will be converted to int.
Conversion 2: file_id in sys.master_files is int; '1' is varchar(1). For the NOT IN, they need to be the same type. Varchar(1) will be converted to int.
If conversion 1 is the root cause, explicitly casting fileid to int in the first query should remove the problem.
If conversion 2 is the root cause, changing NOT IN ('1') to NOT IN (1) should remove the problem.
November 9, 2011 at 6:13 pm
Hugo Kornelis (11/9/2011)
But the reason of the bug is because the query is done on the master or because there is "a non-explicit type conversion on the right hand side" (but I see no conversion in the question query)?:crazy:
It is the implicit conversion.
...
If conversion 1 is the root cause, explicitly casting fileid to int in the first query should remove the problem.
If conversion 2 is the root cause, changing NOT IN ('1') to NOT IN (1) should remove the problem.
those two query works fine e.g. casting file_id to int OR smallint.
I got severe error casting fileid.
select fileid , name from sysfiles intersect
select CAST(file_id as int), name from sys.master_files
where file_id in('1')
order by 2
select fileid, name from sysfiles intersect
select CAST(file_id as smallint), name from sys.master_files
where file_id in('1')
order by 2
Thanks a lot for the good explanation 🙂
November 9, 2011 at 6:35 pm
The query was formed correctly with the exception of a data-type mismatch caused by the 1st query (1st query run determines the types that the 2nd must adhere to)... so you have 2 choices:
1. cast the 2nd query to a matching data-type to conform with the smaller data-type returned by the 1st query.
------------
use master
select fileid,name from sysfiles intersect
select convert(smallint,file_id),name from sys.master_files
where file_id in('1')
order by 2
OR...
2. reverse the order of the queries to implicitly accomplish this because the data-type of what now becomes the 1st query is larger than the 2nd.
------------
use master
select file_id,name from sys.master_files
where file_id in('1') intersect
select fileid,name from sysfiles
order by 2
Incase you care; sys.master_files.file_id is int, sysfiles.fileid is smallint
In reality, forcing type conversions on all fields in both queries is an easier and more reliable way to deal with this type of error when you encounter it while intersecting large queries (as long as you don't care about the performance hit).
November 9, 2011 at 6:49 pm
Why the quey works fine if I remove the "order by 2" if its a conversion bug?
select fileid, name from sysfiles intersect
select file_id, name from sys.master_files
where file_id in('1')
November 10, 2011 at 2:40 am
tilew-948340 (11/9/2011)
Why the quey works fine if I remove the "order by 2" if its a conversion bug?
Because the bug is not introduced by conversion only, but by the combination of an INSTERSECT query with an ORDER BY and an implicit conversion in the right-hand side.
Did you follow the link Paul White posted earlier?
Viewing 15 posts - 46 through 60 (of 63 total)
You must be logged in to reply to this topic. Login to reply