Asking for help to understand subtle nuances of randomised query.

I don't know whether this would be equivalent to your query, but if I run the slightly altered code below I get blocks of heads (or tails) with the same uniqueidentifier.

I'm off to the pub now so can't stop and think about it properly, but thought I'd just throw that out there in case it triggers an "Aha!" moment!

SELECT

[RandomCoinTosses].[Result]

,[RandomCoinTosses].new_id

FROM

[sys].[syscolumns] AS [base]

OUTER APPLY

(

SELECT TOP 1 [Result] = [Val] + LEFT( [base].[name] , 0 ), new_id

FROM (

SELECT TOP 1 [Val] = 'HEADS' ,newid() as new_id

UNION --ALL

SELECT 'TAILS' ,newid()

) AS [Source]

ORDER BY new_id

) AS [RandomCoinTosses]

Yes, that helps to show what is happening very well!

How on Earth does that query produce that output?

I'm not sure if this is the case but according to Microsoft, there is no guarentee that nondeterministic scalar functions will executed for every row.

I submitted a "bug" a while back and was told it was expected behavior:

http://connect.microsoft.com/SQLServer/feedback/details/327457/updates-a-variable-number-of-rows-on-a-table-varaible-unexpected

Here is the code that I was running, basically, I wanted to update random rows, but only a certain amount of rows, in this case 3. What happend is that if I ran this code over and over again I'd sometimes get soem number other that 3 rows updated (usually 2 or 4). DECLARE @Foo TABLE (ID INT IDENTITY(1, 1) PRIMARY KEY, Bar INT, Code CHAR(1))

DECLARE @Count INT

-- Setup data

INSERT @Foo

SELECT 1, NULL

UNION ALL SELECT 2, NULL

UNION ALL SELECT 3, NULL

UNION ALL SELECT 4, NULL

UNION ALL SELECT 5, NULL

UNION ALL SELECT 6, NULL

UNION ALL SELECT 7, NULL

-- Update 3 random rows

SET @Count = 3

-- Update 3 random rows setting the Code value to A

UPDATE Foo

SET Code = 'A'

FROM @Foo AS Foo

INNER JOIN

(

SELECT

ID,

ROW_NUMBER() OVER(ORDER BY NEWID()) AS RowNum

FROM

@Foo

) AS Temp

ON Foo.ID = Temp.ID

WHERE

Temp.RowNum <= @Count

-- Check updates.

SELECT *

FROM @FooUnfortunately, I am no longer seeing this behavior, so I'm not sure it applies in this case...

@Lamprey13 - yes that is probably the same thing as this - or at least they must be related.

So, I need to watch out for this one then.

I guess I can put it in the cupboard and mark it as "Not good practice".

Thanks

I've got a bad feeling this has to do with repeated name in syscolumn and the apply function. I'm not done chewing on this one yet, just haven't had a lot of free time.

Thanks Craig, I sure do appreciate your time. Don't worry too much though - as I said, it is idle curiosity driving this, not anything important.

Same for me, but it's more then an idle curiousity for me. It's annoying the crap out of me in the back of my head. 🙂 That incredibly sideways distribution has me very curious.

Oh ok, good then - because it confused the crap out of me too - but at least I can assume that it is because of my limited knowledge 😛

Posted the bug about something really similar (different application but same source issue methinks)

https://connect.microsoft.com/SQLServer/feedback/details/383641/in-variable-concatenation-and-order-by-filters-results-like-where-condition

It was closed as "by design" and if it is the same issue then any of the workarounds you currently have are not really workarounds...

I don't have workarounds - except doing it completely differently - but want to understand the strange behaviour.

Alright, let's start from scratch and get rid of the curiousities.

SELECT

t.n, [RandomCoinTosses].[Result], x

FROM

tempdb..Tally as t

OUTER APPLY

(SELECT TOP 1 [Result] = [Val], NewID() AS x

FROM( SELECT [Val] = 'HEADS' UNION ALL SELECT 'TAILS'

) AS [Source]

ORDER BY NEWID()

) AS [RandomCoinTosses]

Simple, easy. Tally to duplicate and nothing in the inner query to do anything odd.

Results are utterly duplicated. The query is cached and ran for all rows. Answers question 1: Why are they duplicating? Cached, query is not re-run per row as described in BOL.

Next, we need to force it to rerun:

SELECT

t.n, [RandomCoinTosses].[Result], x

FROM

tempdb..Tally as t

OUTER APPLY

(SELECT TOP 1 [Result] = [Val], NewID() AS x

FROM(SELECT [Val] = 'HEADS' UNION ALL SELECT 'TAILS'

) AS [Source]

WHERE isnumeric(t.n) = 1

ORDER BY NEWID()

) AS [RandomCoinTosses]

Ah, good, we can see our NEWID()'s are regenerating properly. We have a reasonable distribution matrix. It's constantly being re-evaluated. The isnumeric was just to force it to do SOMETHING in regards to the external row. The mathmatics will be re-evaluated each time. Hm, but how to prove that?

SELECT

t.n, [RandomCoinTosses].[Result], x, y

FROM

(SELECT t.n %2 AS n

FROM tempdb..Tally as t) AS t

OUTER APPLY

(SELECT TOP 1 [Result] = [Val], NewID() AS x, NEWID() AS y

FROM (SELECT [Val] = 'HEADS' UNION ALL SELECT 'TAILS'

) AS [Source]

WHERE isnumeric(t.n) = 1

ORDER BY NEWID()

) AS [RandomCoinTosses]

Okay, confirmed caching on equivalent inbound values and that it won't re-create. It also shows that 0 repeats and 1 repeats. This isn't a logical failure but bad expectations of the Cross Apply and optimization mechanics occurring under the hood of SQL Server. Excellent, we're getting there.

Let's get back to the original question, with a minor change. Let's include the base.name in the result sets:

SELECT

base.name,

[RandomCoinTosses].[Result]

FROM

[sys].[syscolumns] AS [base]

OUTER APPLY

(

SELECT TOP 1 [Result] = [Val] + LEFT( [base].[name] , 0 )

FROM (

SELECT TOP 1 [Val] = 'HEADS'

UNION ALL

SELECT 'TAILS'

) AS [Source]

ORDER BY NEWID()

) AS [RandomCoinTosses]

This will conclude the findings above. The patterning you're seeing is due to the cache'ing of the cross/outer apply query against the source input in regards to the changes in the input.

EDIT: Keeps chewing up my formatting, sorry. Tried twice, it just doesn't like my tabs for some reason, at least not consistently.

Craig, Thank you - that explains a lot, incredibly well!

I have taken that on board and now see why it was working like that.

Now for the *twist*.....

I just tried it on Denali with interesting results....

It seems that something major has changed as this query works perfectly now:

SELECT

[RandomCoinTosses].[Result]

,[RandomCoinTosses].new_id

FROM

[sys].[syscolumns] AS [base]

OUTER APPLY

(

SELECT TOP 1 [Result] = [Val] , new_id

FROM (

SELECT [Val] = 'HEADS' ,newid() as new_id

UNION

SELECT 'TAILS' ,newid()

) AS [Source]

ORDER BY new_id,base.id,base.colid

) AS [RandomCoinTosses]

Notice, that I no longer need the TOP 1 inside the [Source] definition.

I have tested this same query on SQL 2005 - where it returns 1 row with values, the rest are NULLS.

I have tested it on SQL 2011 (Denali) using a database that was created on SQL 2000, then transferred to SQL 2008 - but kept in 2000 compatibility mode, then transferred to Denali and set to SQL 2005 compat. mode. - where it returns 1 row with values, the rest are NULLS.

I have tried setting that old database to Denali compat. mode - still doesn't work.

But the query works fine on a 100% denali database.

This raises the question "If this behaviour is by design, why has the design changed and what impact will that have on existing code?"

Additionally - it still produces the same 1 value for every row using CROSS APPLY.

Also

It still produces the same 1 value for every row if I use UNION ALL in the [Source] definition.