Início / dba / Perguntas / 209084
Accepted
Heinzi
Asked: 2018-06-09 01:53:20 +0800 CST

O SQL Server divide A <> B em A < B OR A > B, produzindo resultados estranhos se B não for determinístico

  • 772

Encontramos um problema interessante com o SQL Server. Considere o seguinte exemplo de reprodução:

CREATE TABLE #test (s_guid uniqueidentifier PRIMARY KEY);
INSERT INTO #test (s_guid) VALUES ('7E28EFF8-A80A-45E4-BFE0-C13989D69618');

SELECT s_guid FROM #test
WHERE s_guid = '7E28EFF8-A80A-45E4-BFE0-C13989D69618'
  AND s_guid <> NEWID();

DROP TABLE #test;

violino

Por favor, esqueça por um momento que a s_guid <> NEWID()condição parece totalmente inútil - este é apenas um exemplo mínimo de reprodução. Como a probabilidade de NEWID()corresponder a um determinado valor constante é extremamente pequena, ela deve ser avaliada como TRUE todas as vezes.

Mas não. A execução dessa consulta geralmente retorna 1 linha, mas às vezes (com bastante frequência, mais de 1 vez em 10) retorna 0 linhas. Eu o reproduzi com o SQL Server 2008 no meu sistema e você pode reproduzi-lo on-line com o violino vinculado acima (SQL Server 2014).

Observar o plano de execução revela que o analisador de consultas aparentemente divide a condição em s_guid < NEWID() OR s_guid > NEWID():

captura de tela do plano de consulta

...o que explica completamente por que às vezes falha (se o primeiro ID gerado for menor e o segundo maior que o ID fornecido).

O SQL Server tem permissão para avaliar A <> Bcomo A < B OR A > B, mesmo se uma das expressões não for determinística? Se sim, onde está documentado? Ou encontramos um bug?

Curiosamente, AND NOT (s_guid = NEWID())produz o mesmo plano de execução (e o mesmo resultado aleatório).

Encontramos esse problema quando um desenvolvedor queria excluir opcionalmente uma linha específica e usou:

s_guid <> ISNULL(@someParameter, NEWID())

como um "atalho" para:

(@someParameter IS NULL OR s_guid <> @someParameter)

Estou procurando documentação e/ou confirmação de um bug. O código não é tão relevante, portanto, não são necessárias soluções alternativas.

sql-server optimization

3 respostas

  1. Best Answer

    O SQL Server tem permissão para avaliar A <> Bcomo A < B OR A > B, mesmo se uma das expressões não for determinística?

    Este é um ponto um tanto controverso, e a resposta é um "sim" qualificado.

    A melhor discussão que conheço foi dada em resposta ao relatório de bug do Connect de Itzik Ben-Gan Bug with NEWID and Table Expressions , que foi fechado porque não será corrigido. O Connect foi aposentado, então o link é para um arquivo da web. Infelizmente, muito material útil foi perdido (ou ficou mais difícil de encontrar) com o fim do Connect. De qualquer forma, as citações mais úteis de Jim Hogg da Microsoft são:

    Isso atinge o cerne da questão - a otimização pode alterar a semântica de um programa? Ou seja: se um programa produz certas respostas, mas é executado lentamente, é legítimo que um Query Optimizer faça esse programa funcionar mais rápido, mas também altere os resultados fornecidos?

    Antes de gritar "NÃO!" (minha inclinação pessoal também :-), considere: a boa notícia é que, em 99% dos casos, as respostas são as mesmas. Portanto, a Otimização de Consultas é uma vitória clara. A má notícia é que, se a consulta contiver código de efeito colateral, planos diferentes PODEM produzir resultados diferentes. E NEWID() é uma dessas 'funções' de efeito colateral (não determinística) que expõe a diferença. [Na verdade, se você experimentar, você pode inventar outras - por exemplo, avaliação de curto-circuito de cláusulas AND: faça a segunda cláusula lançar uma divisão aritmética por zero - otimizações diferentes podem executar essa segunda cláusula ANTES da primeira cláusula] Isso reflete A explicação de Craig, em outro lugar neste tópico, que o SqlServer não garante quando os operadores escalares são executados.

    Então, temos uma escolha: se queremos garantir um certo comportamento na presença de código não determinístico (efeitos colaterais) - para que os resultados de JOINs, por exemplo, sigam a semântica de uma execução de loop aninhado - então pode usar OPÇÕES apropriadas para forçar esse comportamento - como UC aponta. Mas o código resultante será executado lentamente - esse é o custo de, na verdade, atrapalhar o Otimizador de consultas.

    Dito isso, estamos movendo o Query Optimizer na direção do comportamento "como esperado" para NEWID() - trocando desempenho por "resultados conforme o esperado".

    Um exemplo da mudança de comportamento a esse respeito ao longo do tempo é que NULLIF funciona incorretamente com funções não determinísticas, como RAND() . Existem também outros casos semelhantes usando, por exemplo, COALESCEuma subconsulta que pode produzir resultados inesperados e que também estão sendo abordados gradualmente.

    Jim continua:

    Fechando o ciclo. . . Eu discuti essa questão com a equipe de desenvolvimento. E, eventualmente, decidimos não alterar o comportamento atual, pelos seguintes motivos:

    1) O otimizador não garante tempo ou número de execuções de funções escalares. Este é um princípio estabelecido há muito tempo. É a 'margem' fundamental que permite ao otimizador liberdade suficiente para obter melhorias significativas na execução do plano de consulta.

    2) Esse "comportamento de uma vez por linha" não é um problema novo, embora não seja amplamente discutido. Começamos a ajustar seu comportamento na versão Yukon. Mas é muito difícil definir precisamente, em todos os casos, exatamente o que isso significa! Por exemplo, ele se aplica a linhas intermediárias calculadas 'no caminho' para o resultado final? - neste caso depende claramente do plano escolhido. Ou aplica-se apenas às linhas que eventualmente aparecerão no resultado concluído? - há uma recursão desagradável acontecendo aqui, como eu tenho certeza que você vai concordar!

    3) Como mencionei anteriormente, o padrão é "otimizar o desempenho" - o que é bom para 99% dos casos. O 1% dos casos em que isso pode alterar os resultados são bastante fáceis de detectar - 'funções' de efeito colateral, como NEWID - e fáceis de 'consertar' (perf de negociação, como consequência). Esse padrão para "otimizar o desempenho" novamente é estabelecido há muito tempo e aceito. (Sim, não é a postura escolhida pelos compiladores para linguagens de programação convencionais, mas que assim seja).

    Assim, nossas recomendações são:

    a) Evite confiar em tempo não garantido e semântica de número de execuções. b) Evite usar NEWID() profundamente em expressões de tabela. c) Use OPTION para forçar um comportamento específico (perf de negociação)

    Espero que esta explicação ajude a esclarecer nossas razões para fechar este bug como "não será corrigido".

    Curiosamente, AND NOT (s_guid = NEWID())produz o mesmo plano de execução

    Isso é uma consequência da normalização, que acontece muito cedo durante a compilação da consulta. Ambas as expressões compilam exatamente na mesma forma normalizada, de modo que o mesmo plano de execução é produzido.

    • 23

  2. Isso está documentado (mais ou menos) aqui:

    O número de vezes que uma função especificada em uma consulta é realmente executada pode variar entre os planos de execução criados pelo otimizador. Um exemplo é uma função invocada por uma subconsulta em uma cláusula WHERE. O número de vezes que a subconsulta e sua função são executadas pode variar com diferentes caminhos de acesso escolhidos pelo otimizador.

    Funções definidas pelo usuário

    Este não é o único formulário de consulta em que o plano de consulta executará NEWID() várias vezes e alterará o resultado. Isso é confuso, mas é realmente crítico para NEWID() ser útil para geração de chaves e classificação aleatória.

    O mais confuso é que nem todas as funções não determinísticas realmente se comportam assim. Por exemplo, RAND() e GETDATE() serão executados apenas uma vez por consulta.

    • 11

  3. Por que vale a pena, se você olhar para este antigo documento padrão SQL 92 , os requisitos em torno da desigualdade são descritos na seção " 8.2 <comparison predicate>" da seguinte forma:

    1) Sejam X e Y quaisquer dois <elemento construtor de valor de linha>s correspondentes. Sejam XV e YV os valores representados por X e Y, respectivamente.

    [...]

    ii) "X <> Y" é verdadeiro se e somente se XV e YV não forem iguais.

    [...]

    7) Sejam Rx e Ry os dois <construtores de valor de linha>s do <predicado de comparação> e sejam RXi e RYi os i-ésimo <elemento construtor de valor de linha>s de Rx e Ry, respectivamente. "Rx <comp op> Ry" é verdadeiro, falso ou desconhecido da seguinte forma:

    [...]

    b) "x <> Ry" é verdadeiro se e somente se RXi <> RYi para algum i.

    [...]

    h) "x <> Ry" é falso se e somente se "Rx = Ry" for verdadeiro.

    Nota: Eu incluí 7b e 7h para completar, pois eles falam sobre <>comparação - não acho que a comparação de construtores de valor de linha com vários valores seja implementada em T-SQL, a menos que eu esteja apenas entendendo mal o que isso diz - o que é bem possível

    Isso é um monte de lixo confuso. Mas se você quiser continuar mergulhando no lixo...

    Acho que 1.ii é o item que se aplica nesse cenário, já que estamos comparando os valores de "elementos construtores de valor de linha".

    ii) "X <> Y" é verdadeiro se e somente se XV e YV não forem iguais.

    Basicamente está dizendo que X <> Yé verdade se os valores representados por X e Y não forem iguais. Como X < Y OR X > Yé uma reescrita logicamente equivalente desse predicado, é totalmente legal para o otimizador usar isso.

    O padrão não coloca nenhuma restrição nessa definição relacionada à determinística (ou qualquer outra coisa, você entende) dos elementos construtores de valor de linha em ambos os lados do <>operador de comparação. É responsabilidade do código do usuário lidar com o fato de que uma expressão de valor em um lado pode ser não determinística.

    • 5

relate perguntas