Sql-server – Unexpected results with random numbers and join types

sql servert-sql

I have a simple script that gets four random numbers (1 through 4) and then joins back to get the matching database_id number. When I run the script with a LEFT JOIN, I get four rows back every time (the expected result). However, when I run it with an INNER JOIN, I get a varying number of rows — sometimes two, sometimes eight.

Logically, there shouldn't be any difference because I know rows with database_ids 1-4 exist in sys.databases. And because we're selecting from the random number table with four rows (as opposed to joining to it), there should never be any more than four rows returned.

This happens in both SQL Server 2012 and 2014. What is causing the INNER JOIN to return varying numbers of rows?

/* Works as expected -- always four rows */

SELECT rando.RandomNumber, d.database_id
FROM 
  (SELECT 1 + ABS(CHECKSUM(NEWID())) % (4) AS RandomNumber 
   FROM sys.databases WHERE database_id <= 4) AS rando
LEFT JOIN sys.databases d ON rando.RandomNumber = d.database_id;


/* Returns a varying number of rows */

SELECT rando.RandomNumber, d.database_id
FROM 
  (SELECT 1 + ABS(CHECKSUM(NEWID())) % (4) AS RandomNumber 
   FROM sys.databases WHERE database_id <= 4) AS rando
INNER JOIN sys.databases d ON rando.RandomNumber = d.database_id;

/* Also returns a varying number of rows */

WITH rando AS (
  SELECT 1 + ABS(CHECKSUM(NEWID())) % (4) AS RandomNumber
  FROM sys.databases WHERE database_id <= 4
)

SELECT r.RandomNumber, d.database_id
FROM rando AS r
INNER JOIN sys.databases d ON r.RandomNumber = d.database_id;

Best Answer

By adding the additional SELECT it pushes the compute scalar evaluation deeper into the plan and gives the join predicate, the compute scalar at the top then references the earlier one.

SELECT rando.RandomNumber, d.database_id
FROM 
  (SELECT ( SELECT 1 + ABS(CHECKSUM(NEWID())) % (4)) AS RandomNumber 
   FROM sys.databases WHERE database_id <= 4) AS rando
INNER JOIN sys.databases d ON rando.RandomNumber = d.database_id

|--Compute Scalar(DEFINE:([Expr1071]=[Expr1070]))

|--Compute Scalar(DEFINE:([Expr1070]=(1)+abs(checksum(newid()))%(4)))

Still digging into just why it waits so late to do it, but currently reading this post by Paul White (https://sql.kiwi/2012/09/compute-scalars-expressions-and-execution-plan-performance.html). Perhaps it has something to do with the fact that NEWID is not deterministic?

Related Question