Início / dba / Perguntas / 84460
Accepted
Pavel Nefyodov
Asked: 2014-12-06 08:55:27 +0800 CST

A alteração dos metadados altera o problema de saída da consulta [duplicado]

  • 772

Recentemente me deparei com o seguinte problema: alterar metadados altera a saída da consulta.

Aqui está como obtê-lo.

CREATE TABLE [dbo].[prod]
(
[ID] [tinyint] NOT NULL,
[values] [tinyint] NOT NULL
) 
GO

A consulta a seguir sempre retorna 'Este é o fim' (erro sempre levantado). Além disso, o filtro deve ser algum tipo de gerador aleatório.

BEGIN TRY
SELECT [id] FROM 
        (
        SELECT [id] as [id]
        FROM dbo.prod
        WHERE ([id]=1/0)
        ) t1
WHERE
-- Random generator 1 always returns false
(FLOOR(RAND()*(1))=1)
-- Random generator 2 does not always return false
/*
SUBSTRING( cast(NEWID() as varchar(max)), 1, 1)
IN ('1', '2', '3', '4', '5', '6', '7', '8', '9', '0', 'A', 'B', 'C', 'D', 'E', 'F')
*/
-- Random generator 3 does not always return false
/*
SUBSTRING(cast(cast( DATEPART(MILLISECOND,GETDATE()) as char(3)) as varchar(3)), 2, 1)
IN ('1', '2', '3', '4', '5', '6', '7', '8', '9', '0')
*/
END TRY
BEGIN CATCH
SELECT 'This is the end'
END CATCH;

Agora adicione restrição de chave primária. Nota: sem alterações de dados.

ALTER TABLE dbo.prod
ADD CONSTRAINT PK_1 PRIMARY KEY NONCLUSTERED(id);

E agora a mesma consulta

BEGIN TRY
SELECT [id] FROM 
        (
        SELECT [id] as [id]
        FROM dbo.prod
        WHERE ([id]=1/0)
        ) t1
WHERE
(FLOOR(RAND()*(1))=1)
END TRY
BEGIN CATCH
SELECT 'This is the end'
END CATCH;

nunca retorna um erro.

É compreensível que alterar os metadados possa alterar o plano de execução, mas foi inesperado que isso alterasse a saída da consulta. Esse comportamento está correto?

sql-server execution-plan

2 respostas

  1. Best Answer

    Esse comportamento está correto?

    É um comportamento intencional (por design); se é 'correto' ou não é mais uma questão de opinião.

    O ponto geral é: o SQL Server não garante o tempo ou o número de avaliações de expressões . Esse comportamento existe para dar ao otimizador de consulta a liberdade necessária para encontrar bons planos de execução.

    Como consequência, a otimização da consulta pode alterar a saída da consulta ou o comportamento do tempo de execução , se houver uma dependência no tempo ou número de avaliações de uma expressão (ou função escalar).

    Nos exemplos da pergunta, o comportamento do tempo de execução depende se o otimizador de consulta identifica cada expressão como uma constante de tempo de execução ou não. O mecanismo de execução pode decidir avaliar (e armazenar em cache) expressões constantes de tempo de execução antes do início da execução da consulta.

    Se o otimizador identificar a expressão 1/0como uma constante de tempo de execução, o formulário XML da saída do plano de exibição a mostrará rotulada como ConstExprxxxx:

    Fragmento de plano de exibição XML

    Constantes de tempo de execução como essa podem ser avaliadas uma vez antes do início da execução da consulta e o resultado armazenado em cache, conforme mencionado anteriormente. Quando isso ocorre, um erro de divisão por zero é retornado durante essa avaliação de expressão constante pré-consulta.

    Adicionar o índice à tabela acontece para seguir uma rota através da otimização de consulta que não resulta em 1/0ser rotulada como uma constante de tempo de execução (não ConstExprxxxxabaixo):

    Fragmento de plano de exibição XML

    Nesse caso específico, a presença de uma restrição de exclusividade significa que o otimizador decide que não há benefício em armazenar em cache o resultado de uma expressão que só será avaliada uma vez. O filtro de inicialização significa que a expressão com falha nunca é avaliada.

    Esses comportamentos de avaliação de expressão não são documentados ou completamente determinados pela presença de ConstExprrótulos em todos os casos. A resposta não é escrever código que dependa do tempo ou do número de avaliações de expressões pelo mecanismo de execução.

    • 5

  2. Interessante.

    Parece que isso se deve à adição da restrição exclusiva, que adiciona um índice no ID, o que, por sua vez, faz com que o otimizador use uma busca de índice em vez de uma varredura de tabela.

    Infelizmente, não tenho ideia de por que o índice de busca não parece gerar um erro de divisão por zero, pois mostra a divisão no predicado de busca. 

    DECLARE @X TABLE 
(
ID Int IDENTITY(1,1) NOT Null,
    Value Int NOT Null
)

DECLARE @Y TABLE 
(
    ID Int IDENTITY(1,1) NOT Null UNIQUE,
    Value Int NOT Null
)


SELECT  ID
FROM    @X
WHERE ID = 1/0
AND Rand() = -1

SELECT ID
FROM @Y
WHERE ID = 1/0
AND Rand() = -1

    Hmm, ainda mais estranho... Se você criar uma tabela e adicionar o índice, mas usar dicas de consulta para 'forçar' uma verificação da tabela, a consulta ainda será bem-sucedida:

    CREATE TABLE #X
(
    ID Int IDENTITY(1,1) NOT Null,
    Value Int NOT Null
)

--  Select from table, this errors on divide by zero
SELECT  ID
FROM    #X
WHERE ID = 1/0
AND Rand() = -1

--  Add a unique constraint to the table
ALTER TABLE #X ADD CONSTRAINT TEMP_X_CONSTRAINT UNIQUE (ID)

--  Select from table, this is successful
SELECT  ID
FROM    #X
WHERE ID = 1/0
AND Rand() = -1

--  Select from table forcing table scan, this is successful
SELECT ID
FROM #X WITH (INDEX(0))
WHERE ID = 1/0
AND Rand() = -1

    Acho que o SQL Server pode estar mentindo para nós sobre seu plano de execução real.

    • 3

relate perguntas