April 25, 2011 at 7:40 am
bitbucket-25253 (4/24/2011)
Oh well, another question that does not rely on the workings of the database engine, but rather english comprehension.
I would say you comprehended just fine.
I would also say I do not see a concept or point being displayed by the question or it's explination. After furthur review...
DECLARE @t TABLE
(nDex INT IDENTITY(1,1)
,valu VARCHAR(9)
,keey UNIQUEIDENTIFIER)
INSERT @t
VALUES ('a',NEWID()) , ('b',NEWID())
,('c',NEWID()) , ('d',NEWID())
,('e',NEWID()) , ('f',NEWID())
,('g',NEWID()) , ('h',NEWID())
,('i',NEWID()) , ('j',NEWID())
,('k',NEWID())
SELECT *
INTO #gT from @t;
DELETE t
OUTPUT DELETED.*
FROM @t AS t
INNER JOIN (SELECT TOP 9 nDex
FROM #gT
ORDER BY keey) AS b
ON b.ndex = t.nDex;
DROP TABLE #gT;
GO
Seems that if you ORDER BY the name or alias of the column holding the NEWID() values and join to a #temp table of the records instead of the table you are deleting from (as you delete) the code always returns as I would expect it too.
The other code would seem to never produce the same amount of joinable values to return...
So why is "none of the above" the wrong answer?
April 25, 2011 at 7:50 am
SanDroid (4/25/2011)
bitbucket-25253 (4/24/2011)
Oh well, another question that does not rely on the workings of the database engine, but rather english comprehension.I would say you comprehended just fine.
I would also say I do not see a concept or point being displayed by the question or it's explination. After furthur review...
...........................
Seems that if you ORDER BY the name or alias of the column holding the NEWID() values and join to a #temp table of the records instead of the table you are deleting from (as you delete) the code always returns as I would expect it too.
The other code would seem to never produce the same amount of joinable values to return...
So why is "none of the above" the wrong answer?
The behavior displayed here does not occur with temp tables, only table variables and NEWID(). That is what is being shown here.
The "logic" of the code makes one expect that nine rows are to be deleted at random and they are not. THIS IS MY POINT 😀
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgApril 25, 2011 at 7:57 am
I don't claim to be a SQL guru by any means, but I disagree with the answer to this question. Sure it could be any of the values presented, but you can't guarantee that it will be one of those three values as it very well could be 6, 8, or 10, for example. I think the problem most of us had with this question was the word choice: 'guarantee' implies that it would have to be one of the supplied answers and no others. Aside from that, good question.
April 25, 2011 at 7:58 am
Jason Selburg (4/25/2011)
**YES THIS IS A TRICK QUESTION**It's intent was to get you thinking, and spark conversation on the validity of Microsoft's statement that this is by design and not a bug.
Seems this only happens If you order the output of a radom set by a row of newid values being created during the same transaction to delete values from the set. I would call the script buggy and write it in a way it worked long before I engaged microsoft to resolve something that IMHO: is working as expected.
Unless I was trying to write a trick question that would get more than 90% wrong answers. Those seem to be real popular these days.
April 25, 2011 at 8:16 am
Jason Selburg (4/25/2011)
The behavior displayed here does not occur with temp tables, only table variables and NEWID(). That is what is being shown here.The "logic" of the code makes one expect that nine rows are to be deleted at random and they are not. THIS IS MY POINT 😀
The "logic" of the code would not pass Code analysis here becuase it is deleting from and joining to the same table while sorting it with a newly generated cloumn of values in the same transaction. However a join to a temp table, or using the existing NewID() column values works and seems very random.
Does this code not give the same result as ordering by a set of newly generated values using a derived table statement?
There are many differant code paterns that code analysis and good testing can be used to make sure code works as expected. I see this as another one one of them. I see this code executing as expected. I guess that is why when I got 8 rows in my result set "None of the above" was the right answer. Obviously someting is not working as expected. 😎
April 25, 2011 at 8:19 am
SanDroid (4/25/2011)
Seems this only happens If you order the output of a radom set by a row of newid values being created during the same transaction to delete values from the set. I would call the script buggy and write it in a way it worked long before I engaged microsoft to resolve something that IMHO: is working as expected.
It's actually a rather simple change, in this case, to get a consistent count.
DECLARE @t TABLE
(nDex INT IDENTITY(1,1)
,valu VARCHAR(9)
,keey UNIQUEIDENTIFIER)
INSERT @t
VALUES ('a',NEWID()) , ('b',NEWID())
,('c',NEWID()) , ('d',NEWID())
,('e',NEWID()) , ('f',NEWID())
,('g',NEWID()) , ('h',NEWID())
,('i',NEWID()) , ('j',NEWID())
,('k',NEWID())
DELETE t
OUTPUT DELETED.*
FROM @t AS t
INNER JOIN (SELECT TOP 9 nDex
FROM @t
ORDER BY keey) AS b
ON b.ndex = t.nDex
GO
Since it's a NEWID already just sort by keey. If there isn't a NEWID column already one could be added and then used, asssuming it doesn't have the same bug.
April 25, 2011 at 8:27 am
One more time. 🙂
Can you agree that both of these code snippets SHOULD do the exact same thing! (Delete a random set of 9 rows)
CREATE TABLE #t
(nDex INT IDENTITY(1,1)
,valu VARCHAR(9)
,keey UNIQUEIDENTIFIER)
INSERT #t
VALUES ('a',NEWID()) , ('b',NEWID())
,('c',NEWID()) , ('d',NEWID())
,('e',NEWID()) , ('f',NEWID())
,('g',NEWID()) , ('h',NEWID())
,('i',NEWID()) , ('j',NEWID())
,('k',NEWID())
DELETE t
OUTPUT DELETED.*
FROM #t AS t
INNER JOIN (SELECT TOP 9 nDex
FROM #t
ORDER BY NEWID()) AS b
ON b.ndex = t.nDex
DROP TABLE #t
GO
.. and ..
DECLARE @t TABLE
(nDex INT IDENTITY(1,1)
,valu VARCHAR(9)
,keey UNIQUEIDENTIFIER)
INSERT @t
VALUES ('a',NEWID()) , ('b',NEWID())
,('c',NEWID()) , ('d',NEWID())
,('e',NEWID()) , ('f',NEWID())
,('g',NEWID()) , ('h',NEWID())
,('i',NEWID()) , ('j',NEWID())
,('k',NEWID())
DELETE t
OUTPUT DELETED.*
FROM @t AS t
INNER JOIN (SELECT TOP 9 nDex
FROM @t
ORDER BY NEWID()) AS b
ON b.ndex = t.nDex
GO
but they do NOT. That is the point here. Not that the code can be written "better" or any other way. The point is, that ordering by NEWID in an inline query that uses a table variable is not consistent with what happens when you use a temp table.
** side note, the keey column is there for fluff and has no purpose.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgApril 25, 2011 at 9:33 am
I get the intent of this question, but it boils down to semantics and how the reader subjectively interprets the question. If most people mark the wrong answer, there is something fundamentally wrong with the question being posed.
April 25, 2011 at 9:34 am
Since you don't know what the number of items will be returned you can't guarantee 2, 9 or 11. So it's not "any of the above" and the correct answer must be "none of the above". 🙂
April 25, 2011 at 10:02 am
Interesting question. Although I didn't get it right, it did make me do more research on the topic.
April 25, 2011 at 10:43 am
I am definitely in the "none of the above" camp.
There is a syntactical ambiguity in the question posed. Should the question be interpreted as "Exactly how many rows will be returned by the OUTPUT statement?" or should it be interpreted as "What is the minimum number of rows which will be returned by the OUTPUT statement?"? (2, 9, or 11) is not a valid answer to the latter interpretation, and IN (2, 9, 11) is not a complete answer to the former interpretation. The given answer "any of the above" is inconsistent with the explanation, which gives the answer as "IN (0,1,...11)"
My natural language interpretation of the OUTPUT statement would be "For each row in t, randomly select nine rows of the table t. If the row is included in this selection, delete it. Output the rows deleted." In this light, "IN (0,1,...,11)" seems like the expected range of row counts. As such, this example does not really seem to demonstrate the reported bug.
Tim Bailey
April 25, 2011 at 11:01 am
*******I think the wording could be fixed. Since the question asks what is the quarenteed results it should be none of the above. A Quarentee is a certainty not a random set of rows. any of the above suggests it is limited to the three options. I think a lot of people got it wrong becuase logically none of the answers seem to fit if you are asking what the quarenteed outcome would be.******
Never mind I see that this has been discuss widly here already. I should have read the other posts first.
I guess I can see both sides but I hate seeing the incorrect on my screen. :crying:
I will pout in my corner now 😛
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
April 25, 2011 at 11:03 am
I would say this is an interesting question.
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
April 25, 2011 at 11:37 am
Jason Selburg (4/25/2011)
It is a question of semantics. You are guaranteed to have ANY of those values and NONE of them at the same time. There is no guarantee, and yet there is one.**YES THIS IS A TRICK QUESTION**
It's intent was to get you thinking, and spark conversation on the validity of Microsoft's statement that this is by design and not a bug.
I've asked Mr. Jones to mark both Any and All as valid answers. Unfortunately the QOTD engine does not allow multiple "possible" answers.
Let's take a poll...
88% of us think you're wrong :w00t:.
Thanks for the question... let's stop kicking that dead horse. We all got our points and let's move on :hehe:.
April 25, 2011 at 11:50 am
Jason Selburg (4/25/2011)
One more time. 🙂Can you agree that both of these code snippets SHOULD do the exact same thing! (Delete a random set of 9 rows)
but they do NOT. That is the point here.
I get your point. I just don't agree with it.
Even if I did agree with your point, the answer should be "none of the above" since 1 or 6 are a possible number of rows returned by your code.
As for your last code example, I see why they do not do the exact same thing. Similar code does not work reliably on other SQL platforms either.
It has been my understanding that a derived table statement is executed in line, not seperately. This may not be the perfect way to explain it, but it works for me.
I look at the code that "does not work" and see it is trying to update a derived table with new random values output from a system function and and then order the derived table on those values in the same statement that it is deleting values from the source table. Since this is executed in line with the delete statement, you get some very random unrepeatble output.
NEWID() can not be executed against every row of the derived table unless it is executed in a seperate SQL statement.
Viewing 15 posts - 16 through 30 (of 55 total)
You must be logged in to reply to this topic. Login to reply