My actual work query was an inner join, but this simple example with cross join seems to nearly always reproduce the problem.
SELECT *
FROM (
SELECT 1 UNION ALL
SELECT 2
) AA ( A )
CROSS JOIN (
SELECT NEWID() TEST_ID
) BB ( B )
With my inner join I had many rows for which I added to each a GUID using the NEWID() function, and for about 9 out of 10 such rows the multiplication with the 2-row virtual table produced the expected results, just 2 copies of the same GUID, while 1 out of 10 would produce different results. This was unexpected to say the least and gave me a really hard time trying to find this bug in my test data generation script.
If you take a look at the following queries using as well non-deterministic getdate and sysdatetime functions, you won't see this, I don't anyway-I always see the same datetime value in both final result rows.
SELECT *
FROM (
SELECT 1 UNION ALL
SELECT 2
) AA ( A )
CROSS JOIN (
SELECT GETDATE() TEST_ID
) BB ( B )
SELECT *
FROM (
SELECT 1 UNION ALL
SELECT 2
) AA ( A )
CROSS JOIN (
SELECT SYSDATETIME() TEST_ID
) BB ( B )
I'm currently using SQL Server 2008 and my work around for now is to load my rows with GUIDs into a table variable before finishing out my random data generation script. Once I have them as values in a table as opposed to virtual table, the problem goes away.
I have a workaround, but I'm looking for the ways to workaround without actual tables or table variables.
While writing this I tried without success these possibilities: 1) placing the newid() into a nested virtual table:
SELECT *
FROM (
SELECT 1 UNION ALL
SELECT 2
) AA ( A )
CROSS JOIN (
SELECT TEST_ID
FROM (
SELECT NEWID() TEST_ID
) TT
) BB ( B )
2) wrapping the newid() within a cast expression such as:
SELECT CAST(NEWID() AS VARCHAR(100)) TEST_ID
3) reversing the order of appearance of the virtual tables within the join expression
SELECT *
FROM (
SELECT NEWID() TEST_ID
) BB ( B )
CROSS JOIN (
SELECT 1 UNION ALL
SELECT 2
) AA ( A )
4) using uncorrelated cross apply
SELECT *
FROM (
SELECT NEWID() TEST_ID
) BB ( B )
CROSS APPLY (
SELECT 1 UNION ALL
SELECT 2
) AA ( A )
Just before finally posting this question, now I tried this with success it seems, a correlated cross apply:
SELECT *
FROM (
SELECT NEWID() TEST_ID
) BB ( B )
CROSS APPLY (
SELECT A
FROM (
SELECT 1 UNION ALL
SELECT 2
) TT ( A )
WHERE BB.B IS NOT NULL
) AA ( A )
Anyone have any other more elegant, simpler workaround? I really don't want to use cross apply or correlation for a simple row multiplication if I don't have to.