July 25, 2011 at 6:22 am
It was apparent to me that the author wanted to test on precedence. That's the way I took it, anyway. It would have been helpful to have a table definition and perhaps test data.
The two ID fields, ID and ModelID are confusing because there is no table definition. I took it to mean that ID was an auto ID key and ModelID was an index. Although, there's nothing in the question to lead me that way.
Thanks for the reminder on the proper placement of parenthesis.
July 25, 2011 at 6:31 am
OCTom (7/25/2011)
It was apparent to me that the author wanted to test on precedence. That's the way I took it, anyway. It would have been helpful to have a table definition and perhaps test data.The two ID fields, ID and ModelID are confusing because there is no table definition. I took it to mean that ID was an auto ID key and ModelID was an index. Although, there's nothing in the question to lead me that way.
Thanks for the reminder on the proper placement of parenthesis.
Just like last week when the title of a QOTD said something about the Case statement when the author was really testing to see if people noticed the extra UNION ALL. Nothing is apparent to me and I answer all QOTD's with the thought that there's some trick to the question.
July 25, 2011 at 6:52 am
Richard Warr (7/25/2011)
martin catherall (7/25/2011)
It's just a question. Not a test of your psychic ability 🙂True, although common sense can often save despair and heartache.
I always loved his questions. I think it,s the first time he lets something like this split by. I think I'll let it slide :-).
Oh wait, it just made the question even easier :hehe:.
July 25, 2011 at 6:56 am
martin catherall (7/24/2011)
I got the question correct, but I answered it on the basis that the two where clauses were different.Where ID = 20 OR modelID = 20 --- NOTE the use of the ID column in one and modelID in the other
Same here... I saw that and went "ummm no?"
July 25, 2011 at 7:05 am
Precedence has always and will always mater on all versions, flavors, and RDBMS servers that support SQL.
Precedence and the fact the WHERE clause was ddifferentmeans that the answer is NO.
I hope after the killing another QOTD author got for not following his questions subject matter that the label Precedence would have told you what it was about before answering. If not my advice is "start READing now". 😎
If you looked at those SQL statements and thought the answer could be yes then read a book on SQL. :hehe: :w00t:
July 25, 2011 at 7:06 am
Richard Warr (7/25/2011)
martin catherall (7/25/2011)
It's just a question. Not a test of your psychic ability 🙂True, although common sense can often save despair and heartache.
😎
LOL... That and good reading comprehension can save lives! :w00t:
July 25, 2011 at 7:37 am
Agree with the others that "it depends" because the data could return the same results for both queries. We don't know for sure. However, if the system were used somewhat normally and the data were spread out as we'd tend to expect, the result sets would eventually diverge.
In any case, it's a good reminder to be aware of your parentheses.
July 25, 2011 at 7:52 am
SanDroid (7/25/2011)
Precedence has always and will always mater on all versions, flavors, and RDBMS servers that support SQL.
True, but that's not the cause of the discussion here.
Precedence and the fact the WHERE clause was ddifferentmeans that the answer is NO.
Not necessarily. For example, as mentioned in an earlier post, if there are no rows in the table, both statements will return the same results (i.e, none). Furthermore, if the only rows where Color = 'Red' have a ModelID of 21 (and either ID is actually ModelID or ID is 20 whenever ModelID is 20) then the statements will also return the same results. Finally, it's also possible that the number of rows where ID = 20 is exactly equal to the number of rows where ModelID = 20 and Color = 'Red', meaning that the answer to the original question (do they return the same number of rows) is still yes, even though the specific rows returned may not be the same.
I hope after the killing another QOTD author got for not following his questions subject matter that the label Precedence would have told you what it was about before answering. If not my advice is "start READing now". 😎
No-one's killing anyone here. But our business is built on a specific grammar and specific logic, and anything that may be considered an authority on that grammar and logic needs to be held to a high standard of correctness. One of the reasons I visit this site is that I find the discussions tend to provide that correctness when the original post fails in some way, and to be (generally) downright polite in doing so compared to other sites.
If you looked at those SQL statements and thought the answer could be yes then read a book on SQL. :hehe: :w00t:
I've read several, and I've told you under exactly what circumstances the answer could be yes.
July 25, 2011 at 8:04 am
sknox (7/25/2011)
SanDroid (7/25/2011)
Precedence has always and will always mater on all versions, flavors, and RDBMS servers that support SQL.True, but that's not the cause of the discussion here.
WRONG. :w00t:
The topic or subject of the QOTD was Precedence.
This discussion is about that quesiton on SQL Precedence.
I have no idea what the rest of your rant like post is about. I stopped reading it when I got to the totally WRONG part.
Maybe your post was for pointing out that you did not understand the QOTD topic, this discussions topic, or my post. 😛
July 25, 2011 at 8:26 am
SanDroid (7/25/2011)
sknox (7/25/2011)
SanDroid (7/25/2011)
Precedence has always and will always mater on all versions, flavors, and RDBMS servers that support SQL.True, but that's not the cause of the discussion here.
WRONG. :w00t:
The topic or subject of the QOTD was Precedence.
This discussion is about that quesiton on SQL Precedence.
I have no idea what the rest of your rant like post is about. I stopped reading it when I got to the totally WRONG part.
Maybe your post was for pointing out that you did not understand the QOTD topic, this discussions topic, or my post. 😛
Discussions can diverge from their original topic. Trolls, apparently, cannot.
July 25, 2011 at 8:27 am
I think the general complaint is that the question was made ambiguous when it didn't need to be. Leaving the table structure and contents undefined did not lend anything to testing anybody's understanding of precedence. Using ID and ModelID in the WHERE clause further obfuscated the question when there was no need for it. The knowledge being tested was relevant, but the question was poorly delivered.
-- Give a table definition - as simple as needed for the problem
CREATE TABLE Product (ID INT, ModelID INT, Color VARCHAR(10))
INSERT INTO Product (ID, ModelID, Color) [rows of data to insert elided]
-- Option 1
SELECT ID, ModelID, Color
FROM @Product
WHERE ModelID = 20 OR ModelID = 21 AND Color = 'Red'
-- Option 2
SELECT ID, ModelID, Color
FROM @Product
WHERE (ModelID = 20 OR ModelID = 21) AND Color = 'Red'
July 25, 2011 at 8:38 am
pkeeler (7/25/2011)
I think the general complaint is that the question was made ambiguous when it didn't need to be. Leaving the table structure and contents undefined did not lend anything to testing anybody's understanding of precedence. Using ID and ModelID in the WHERE clause further obfuscated the question when there was no need for it. The knowledge being tested was relevant, but the question was poorly delivered.-- Give a table definition - as simple as needed for the problem
CREATE TABLE Product (ID INT, ModelID INT, Color VARCHAR(10))
INSERT INTO Product (ID, ModelID, Color) [rows of data to insert elided]
-- Option 1
SELECT ID, ModelID, Color
FROM @Product
WHERE ModelID = 20 OR ModelID = 21 AND Color = 'Red'
-- Option 2
SELECT ID, ModelID, Color
FROM @Product
WHERE (ModelID = 20 OR ModelID = 21) AND Color = 'Red'
I'd bet that he chose not to do this because the picture of the screen wold have been to big. I know he preffers people to think rather than just run the statements blindly, hence the picture choice.
July 25, 2011 at 8:43 am
Okay, do a screenshot of a grid capture of the table contents, then. Anything that shows the data in the system being evaluated. Otherwise it's not guaranteed that the two statements will return a different number of rows - as other posters have noted, for example, both could return 0 rows.
July 25, 2011 at 8:54 am
"it depends on the data" is true. but ...
The safe answer should be NO because it is obvious that the two WHERE clauses are not equivalent. An SQL statement should not depend on the data to be valid or not.
July 25, 2011 at 9:09 am
It is a shame that so many people are more interested in the point that they may or may not have gotten than the lesson from the QOD. It definitely is apparhent that in the QOD the intent is the precedence rather than a typo which almost everyone is ready to crucify the individual for. As for the table schema, would that really aid in the answer to the question?
Let's try this:
/****** Script for SelectTopNRows command from SSMS ******/
SELECT *
FROM [databasename].[schemaname].[tablename] WITH (NOLOCK)
WHERE number = 1 OR number = 7
AND Date = '2011-07-14 00:00:00.000'
vs
/****** Script for SelectTopNRows command from SSMS ******/
SELECT *
FROM [databasename].[schemaname].[tablename] WITH (NOLOCK)
WHERE (number = 1 OR number = 7)
AND Date = '2011-07-14 00:00:00.000'
In one run against a table with 700M rows, I receive 4513066 rows covering all dates where number = 1. In the other I receive only 13935 records for the specific date but both numbers. Do you need the data as well or can you figure out the answer? One person made a comment about T-SQL following the rules of mathematics, and he is close to correct but why???
I hope that there are no typo's in my example or this will go on and on and on - but then it will. Too bad.
To the writer of the question - good question, great idea behind the question. Have seen too many times where developers did this and didn't get the results that they were expecting.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
Viewing 15 posts - 16 through 30 (of 47 total)
You must be logged in to reply to this topic. Login to reply