Início / dba / Perguntas / 142675
Accepted
usr
Asked: 2016-07-01 05:34:40 +0800 CST

É legal o SQL Server preencher colunas PERSISTED com dados que não correspondem à definição?

  • 772

Estou acompanhando esta questão sobre valores estranhos em uma PERSISTEDcoluna computada. A resposta faz algumas suposições sobre como esse comportamento surgiu.

Estou perguntando o seguinte: isso não é um bug definitivo? As PERSISTEDcolunas podem se comportar dessa maneira?

DECLARE @test TABLE (
    Col1 INT,
    Contains2 AS CASE WHEN 2 IN (Col1) THEN 1 ELSE 0 END PERSISTED) --depends on Col1

INSERT INTO @test (Col1) VALUES
    (ABS(CHECKSUM(NEWID()) % 5)),
    (ABS(CHECKSUM(NEWID()) % 5)),
    (ABS(CHECKSUM(NEWID()) % 5)),
    (ABS(CHECKSUM(NEWID()) % 5)),
    (ABS(CHECKSUM(NEWID()) % 5))

SELECT * FROM @test --shows impossible data

UPDATE @test SET Col1 = Col1*1 --"fix" the data by rewriting it

SELECT * FROM @test --observe fixed data

/*
Col1    Contains2
2   0
2   0
0   1
4   0
3   0

Col1    Contains2
2   1
2   1
0   0
4   0
3   0
*/

Observe que os dados parecem "impossíveis" porque os valores da coluna calculada não correspondem à sua definição.

É bem conhecido que funções não determinísticas em consultas podem se comportar de maneira estranha, mas aqui isso parece violar o contrato de colunas computadas persistentes e, portanto, deveria ser ilegal.

Inserir números aleatórios pode ser um cenário artificial, mas e se estivéssemos inserindo NEWID()valores ou SYSUTCDATETIME()? Acho que essa é uma questão relevante que pode se manifestar de forma prática.

sql-server sql-server-2014

2 respostas

  1. Best Answer

    Isso é certamente um bug. O fato de os col1valores serem o resultado de uma expressão envolvendo números aleatórios claramente não altera o que o valor correto col2deveria ser. DBCC CHECKDBretorna um erro se for executado em uma tabela permanente.

    create table test (
    Col1 INT,
    Contains2 AS CASE WHEN 2 IN (Col1) THEN 1 ELSE 0 END PERSISTED);

INSERT INTO test (Col1) VALUES
    (ABS(CHECKSUM(NEWID()) % 5)),
    (ABS(CHECKSUM(NEWID()) % 5)),
    (ABS(CHECKSUM(NEWID()) % 5)),
    (ABS(CHECKSUM(NEWID()) % 5)),
    (ABS(CHECKSUM(NEWID()) % 5));

DBCC CHECKDB

    Dá (para o meu teste que tinha uma linha "impossível")

    Msg 2537, Level 16, State 106, Line 17
Table error: object ID 437576597, index ID 0, partition ID 72057594041008128, alloc unit ID 72057594046251008 (type In-row data), page (1:121), row 0. The record check (valid computed column) failed. The values are 2 and 0.
DBCC results for 'test'.
There are 5 rows in 1 pages for object "test".
CHECKDB found 0 allocation errors and 1 consistency errors in table 'test' (object ID 437576597).

    Também informa que

    repair_allow_data_loss é o nível mínimo de reparo para os erros encontrados pelo DBCC CHECKDB

    E, se for escolhida a opção de reparo, exclui sem cerimônia toda a linha, pois não há como saber qual coluna está corrompida.

    Anexar um depurador mostra que NEWID()está sendo avaliado duas vezes por linha inserida. Uma vez antes da CASEexpressão ser avaliada e uma vez dentro dela.

    insira a descrição da imagem aqui

    Uma possível solução alternativa pode ser usar

    INSERT INTO @test
            (Col1)
SELECT ( ABS(CHECKSUM(NEWID()) % 5) )
FROM   (VALUES (1),(1),(1),(1),(1)) V(X);

    O que, por um motivo ou outro, evita o problema e avalia a expressão apenas uma vez por linha.

    • 9

  2. De acordo com a conversa do comentário, o consenso parece ser que a resposta à pergunta do OP é que isso constitui um bug (ou seja, deveria ser ilegal).

    O OP faz referência à análise de Vladimir Baranov no StackOverflow, onde eles afirmam:

    "Primeira vez para Col1, segunda vez para a instrução CASE da coluna persistente.

    O otimizador não sabe ou não se importa nesse caso que NEWID é uma função não determinística e a chama duas vezes."

    Dito de outra forma, deve-se esperar que [o NEWID() dentro] col1 tenha o mesmo valor que você acabou de inserir quando fez o cálculo.

    Isso seria sinônimo do que está acontecendo com o bug, onde NEWID é criado para Col1 e depois criado novamente para a coluna persistida:

    INSERT INTO @Test (Col1, Contains2) VALUES
(NEWID(), CASE WHEN (NEWID()) LIKE '%2%' THEN 1 ELSE 0 END)

    Em meus testes, outras funções não determinísticas como RAND e valores de tempo não resultaram no mesmo bug.

    De acordo com Martin, isso foi enviado à Microsoft ( https://connect.microsoft.com/SQLServer/Feedback/Details/2751288 ), onde há comentários nesta página e na análise do StackOverflow (abaixo).

    • 2

relate perguntas