Início / dba / Perguntas / 80374
Accepted
Doug Lane
Asked: 2014-10-17 07:49:17 +0800 CST

Resultados inesperados com números aleatórios e tipos de junção

  • 772

Eu tenho um script simples que obtém quatro números aleatórios (1 a 4) e, em seguida, junta-se novamente para obter o número de database_id correspondente. Quando executo o script com LEFT JOIN, recebo quatro linhas de volta todas as vezes (o resultado esperado). No entanto, quando executo com um INNER JOIN, obtenho um número variável de linhas - às vezes duas, às vezes oito.

Logicamente, não deve haver nenhuma diferença porque sei que existem linhas com database_ids 1-4 em sys.databases. E como estamos selecionando na tabela de números aleatórios com quatro linhas (em vez de unir a ela), nunca deve haver mais de quatro linhas retornadas.

Isso acontece tanto no SQL Server 2012 quanto no 2014. O que está fazendo com que o INNER JOIN retorne números variados de linhas?

/* 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;
sql-server t-sql

2 respostas

  1. Isso pode fornecer algumas informações até que uma das pessoas mais inteligentes do site entre em contato.

    Eu coloco os resultados aleatórios em uma tabela temporária e sempre obtenho 4 resultados, independentemente do tipo de junção.

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

    Se eu comparar os planos de consulta entre sua segunda consulta e a variação com uma variável de tabela, posso ver que há uma diferença definitiva entre as duas. O X vermelho é No Join Predicatetão estranho para o meu cérebro desenvolvedor de homem das cavernas

    insira a descrição da imagem aqui

    Se eu eliminar o bit aleatório da consulta para uma constante 1 % (4), meu plano parecerá melhor, mas o Compute Scalar foi eliminado, o que me levou a olhar mais de perto

    insira a descrição da imagem aqui

    Está calculando a expressão para o número aleatório após a junção. Se isso é esperado, ainda deixo para os assistentes internos do site, mas pelo menos é por isso que você está obtendo resultados variáveis ​​em sua junção.

    2014

    Para aqueles que jogam em casa, os planos de consulta acima foram gerados a partir de uma instância 2008 R2. Os planos de 2014 parecem diferentes, mas a operação Compute Scalar permanece após a junção.

    Este é o plano de consulta para 2014 usando a expressão constante

    insira a descrição da imagem aqui

    Este é o plano de consulta para uma instância de 2014 usando a expressão newid.

    insira a descrição da imagem aqui

    Aparentemente, isso é intencional, problema de conexão aqui. Obrigado a @paulWhite por saber que existia.

    • 12
  2. Best Answer

    Ao adicionar o SELECT adicional, ele empurra a avaliação escalar de computação mais profundamente no plano e fornece o predicado de junção, o escalar de computação na parte superior faz referência ao anterior.

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

    Ainda investigando por que espera tão tarde para fazer isso, mas atualmente lendo esta postagem de Paul White ( https://sql.kiwi/2012/09/compute-scalars-expressions-and-execution-plan-performance.html ) . Talvez tenha algo a ver com o fato de NEWID não ser determinístico?

    • 9

relate perguntas