Nieoczekiwane wyniki z liczbami losowymi i typami łączenia

Mam prosty skrypt, który pobiera cztery liczby losowe (od 1 do 4), a następnie dołącza z powrotem, aby uzyskać pasujący numer id_bazy_danych. Kiedy uruchamiam skrypt z LEFT JOIN, za każdym razem otrzymuję cztery wiersze (oczekiwany wynik). Kiedy jednak uruchamiam go z WEJŚCIEM WEWNĘTRZNYM, otrzymuję różną liczbę wierszy - czasem dwa, a czasem osiem.

Logicznie rzecz biorąc, nie powinno być żadnej różnicy, ponieważ wiem, że wiersze z id_bazy_danych 1-4 istnieją w sys.databases. A ponieważ wybieramy z tabeli liczb losowych z czterema wierszami (w przeciwieństwie do łączenia się z nią), nigdy nie powinno być zwracanych więcej niż cztery wiersze.

Dzieje się tak zarówno w SQL Server 2012, jak i 2014. Co powoduje, że INNER JOIN zwraca różną liczbę wierszy?

/* 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;

Dodając dodatkowy WYBÓR, wsuwa ocenę obliczeń skalarnych głębiej w plan i podaje predykat łączenia, skalar obliczeniowy u góry odwołuje się do wcześniejszego.

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)))

Nadal zastanawiam się, dlaczego tak późno na to czeka, ale obecnie czytam ten post przez Paula White'a ( https://sql.kiwi/2012/09/compute-scalars-expressions-and-execution-plan-performance.html ) . Być może ma to coś wspólnego z faktem, że NEWID nie jest deterministyczny?

Może to dać pewien wgląd, dopóki jeden z inteligentniejszych ludzi na stronie nie zadzwoni.

Umieszczam losowe wyniki w tabeli tymczasowej i konsekwentnie otrzymuję 4 wyniki niezależnie od typu złączenia.

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

DECLARE @Rando table
(
    RandomNumber int
);

INSERT INTO
    @Rando
(
    RandomNumber
)
-- This generates 4 random numbers from 1 to 4, endpoints inclusive
SELECT
    1 + ABS(CHECKSUM(NEWID())) % (4) AS RandomNumber
FROM
    sys.databases
WHERE
    database_id <= 4;

SELECT
    *
FROM
    @Rando AS R;

SELECT
    rando.RandomNumber
,   d.database_id
FROM 
    @Rando AS rando
    LEFT JOIN 
        sys.databases d 
        ON rando.RandomNumber = d.database_id
ORDER BY 1,2;


/* Returns a varying number of rows */

SELECT rando.RandomNumber, d.database_id
FROM 
    @Rando AS rando
    INNER JOIN 
        sys.databases d 
        ON rando.RandomNumber = d.database_id
ORDER BY 1,2;

/* Also returns a varying number of rows */

WITH rando AS 
(
    SELECT * FROM @Rando AS rando
)
SELECT r.RandomNumber, d.database_id
FROM 
    rando AS r
    INNER JOIN 
        sys.databases d 
        ON r.RandomNumber = d.database_id
ORDER BY 1,2;

Jeśli porównuję plany zapytań między drugim zapytaniem a odmianą ze zmienną tabelową, widzę wyraźną różnicę między nimi. Czerwony X jest No Join Predicatetak dziwny dla mojego mózgu programistów jaskiniowców

Jeśli wyeliminuję losowy bit zapytania do stałej 1 % (4), mój plan będzie wyglądał lepiej, ale Skalar obliczeniowy został wyeliminowany, co skłoniło mnie do bliższego przyjrzenia się

Oblicza wyrażenie liczby losowej po złączeniu. Bez względu na to, czy jest to oczekiwane, nadal pozostawiam wewnętrznym kreatorom na stronie, ale przynajmniej dlatego otrzymujesz zmienne wyniki w swoim dołączeniu.

2014

Dla osób grających w domu powyższe plany zapytań zostały wygenerowane na podstawie wystąpienia R2 z 2008 roku. Plany na 2014 r. Wyglądają inaczej, ale operacja Compal Scalar pozostaje po dołączeniu.

To jest plan zapytań na 2014 r. Wykorzystujący wyrażenie stałe

Jest to plan zapytań dla instancji z 2014 r. Używającej wyrażenia newid.

To najwyraźniej jest z założenia problem Connect tutaj. Dzięki @paulWhite za informację, że istniała.