NULL em Postgres: por que seus cálculos silenciosamente entregam o resultado errado

NULL não é um valor. É um marcador para "desconhecido", e essa distinção, aparentemente filosófica, é a origem de uma classe inteira de bugs que passam por todos os testes e só aparecem em produção, quando alguém pergunta por que o relatório está com o total zerado. O artigo de Christopher Winslett no blog da Crunchy Data, Postgres Calculations and the Ambiguity of NULL, percorre com precisão os pontos onde essa ambiguidade contamina comparações, aritmética, agregações, funções de janela, concatenação e ordenação.

O ponto de partida é uma assimetria que todo DBA já observou sem necessariamente ter parado para nomear: dividir por zero levanta erro (division by zero), converter 'abc' para inteiro levanta erro, mas dividir por NULL executa normalmente e devolve NULL. O banco não está falhando: está aplicando a lógica de três valores do padrão SQL↳SQL64 conteúdosSQL Server – Como evitar SQL Injection?Data · mai 2019Azure SQL DB Managed InstanceData · abr 2019SQL Server – Como evitar SQL Injection? Pare de utilizar Query Dinâmica como EXEC(@Query)Data · abr 2019Ver tudo em Data →, na qual true, false e unknown convivem. O resultado é bem definido. Só não é o que o desenvolvedor tinha em mente.

A lógica de três valores e o WHERE que descarta linhas

A regra que Winslett coloca como fundamental: operadores de comparação (=, <>, <, >) retornam NULL sempre que um dos lados é desconhecido. Por isso NULL = NULL não é true, é NULL, e por isso SQL tem IS NULL em vez de = NULL. Não se pode saber se dois desconhecidos são iguais.

O perigo prático mora no WHERE e no HAVING: eles mantêm apenas linhas cuja expressão é true. Tanto false quanto unknown são descartados. O exemplo do artigo é didático justamente por parecer inofensivo:

CREATE TABLE flags (id int, active boolean);
INSERT INTO flags VALUES (1, true), (2, false), (3, NULL);

-- Retorna apenas linhas 1 e 2. A linha 3 (NULL) é filtrada.
SELECT * FROM flags WHERE active OR NOT active;

Em lógica clássica, active OR NOT active é uma tautologia: sempre verdadeira. Em SQL não é, porque NULL OR NOT NULL colapsa em unknown, e unknown some. As saídas de emergência são explícitas: COALESCE(active, false), active IS NOT TRUE, active IS UNKNOWN ou active IS DISTINCT FROM true. As formas IS TRUE, IS NOT TRUE, IS FALSE, IS UNKNOWN têm a virtude de nunca retornarem NULL, sempre true ou false.

Para igualdade, o equivalente é IS NOT DISTINCT FROM, que trata NULL como se fosse um valor: dois desconhecidos são indistinguíveis, logo NULL IS NOT DISTINCT FROM NULL é true. Detalhe que costuma passar despercebido em migrações: um JOIN ON a.x = b.x descarta as linhas em que ambos os lados são NULL. Se a intenção é que duas chaves ausentes contem como par, a cláusula correta é ON a.x IS NOT DISTINCT FROM b.x.

A armadilha do NOT IN, a mais destrutiva da lista

De todas as pegadinhas, essa é a que apaga um resultado inteiro sem aviso. IN e NOT IN são reescritos como cadeias de igualdade. Como NOT IN vira uma cadeia de AND, basta um único NULL na lista para o predicado desabar em unknown:

x NOT IN (1, 2, NULL)
 ≡ x <> 1 AND x <> 2 AND x <> NULL
 ≡ (true/false) AND (true/false) AND NULL
 ≡ NULL

O efeito prático é brutal: se a subquery de um NOT IN retornar sequer um NULL, o resultado é vazio. No exemplo do artigo, uma tabela discontinued com valores (2), (NULL) faz com que SELECT ... WHERE id NOT IN (SELECT product_id FROM discontinued) não retorne nenhum produto, mesmo os que claramente não estão descontinuados. O banco não consegue provar que eles não estão na lista porque não sabe o que aquele NULL representa.

A reescrita confiável é NOT EXISTS, que opera com igualdade dentro do WHERE e nunca trata comparação com NULL como correspondência:

SELECT p.name
FROM products p
WHERE NOT EXISTS (
 SELECT 1 FROM discontinued d WHERE d.product_id = p.id
);

O anti-join com LEFT JOIN ... WHERE d.product_id IS NULL produz o mesmo significado e costuma ser, aliás, o plano de execução que o planner prefere. Winslett aponta o texto "Rise of the Anti-Join", de Paul Ramsey, para o lado de performance. A leitura de um DBA aqui: NOT IN já é um mau padrão por semântica antes mesmo de você abrir o EXPLAIN. Se for inevitável mantê-lo, filtre os NULLs na subquery com WHERE product_id IS NOT NULL, mas isso só é correto se ignorar o NULL for realmente a regra de negócio desejada, e NOT EXISTS torna essa intenção explícita.

Agregações ignoram NULL, e é aí que a média mente

A regra é simples de enunciar e fácil de esquecer: COUNT(*) conta linhas; COUNT(coluna) conta apenas valores não nulos; SUM, AVG, MIN e MAX pulam NULL. O problema aparece quando o desenvolvedor mistura as contagens:

AVG(x) é SUM(x) / COUNT(coluna), nunca SUM(x) / COUNT(*). E há a armadilha de tipos: SUM(rating) e COUNT(rating) são inteiros, então / trunca a menos que se faça cast para numeric. Winslett também esclarece um mal-entendido comum sobre FILTER: escrever AVG(rating) FILTER (WHERE rating IS NOT NULL) não muda o resultado, porque AVG já pula NULL. O FILTER serve para deixar a regra visível na query, não para alterá-la. Quem quer mudar o número usa COALESCE.

Funções de janela e a novidade do Postgres 19

Em janelas, SUM e AVG continuam pulando NULL como no GROUP BY, e ROW_NUMBER() continua contando a linha. O descompasso está em lag, lead, first_value, last_value e nth_value: essas funções olham para uma posição específica no frame. Se aquela posição contém NULL, o retorno é NULL, sem caçar o valor real mais próximo. Numa série de temperaturas com um sensor que perdeu uma leitura, lag(temp) na linha seguinte à falha devolve o NULL da falha, não os 20 graus anteriores.

A solução, até aqui, exigia subquery ou DISTINCT ON filtrado. O artigo registra que o Postgres 19 planeja adicionar a cláusula padrão SQL de tratamento de nulos: RESPECT NULLS (o comportamento atual) e IGNORE NULLS, posicionada entre os argumentos da função e o OVER:

SELECT ts, temp,
 lag(temp) IGNORE NULLS OVER (ORDER BY ts) AS prev_ignore
FROM readings;

Com IGNORE NULLS, a função caminha para trás (ou para frente, no lead) até achar um argumento não nulo e aplica o offset apenas sobre as linhas reais. Vale só para as cinco funções de posição citadas: funções de ranking e agregações de janela seguem usando FILTER (WHERE ... IS NOT NULL).

Concatenação e ordenação: dois detalhes que corrompem saída

O operador || é aritmética para strings: NULL entra, NULL sai. 'Hello, ' || NULL || '!' resulta em NULL, e um nome de exibição montado com first_name || ' ' || middle_name some por inteiro quando o nome do meio é nulo. Já concat e concat_ws tratam NULL como string vazia, e concat_ws ainda pula os NULLs ao posicionar o separador, evitando espaços duplos. O alerta de Winslett é operacional: não troque um pelo outro sem revisar. Código de aplicação que concatena em SQL e depois testa IS NULL para significar "todas as partes ausentes" vai falhar se migrado de || para concat.

Na ordenação, o Postgres trata NULL como maior que qualquer valor não nulo. ORDER BY x ASC joga NULLs para o fim; ORDER BY x DESC os traz para o topo. Um ORDER BY points DESC num placar coloca quem não tem pontuação em primeiro lugar. A correção é explícita: NULLS LAST ou NULLS FIRST, que funcionam também na definição de índices, ponto que o DBA precisa casar entre query e índice para não perder o uso do índice na ordenação. E vale notar a incoerência interna: no ORDER BY, NULL fica acima do maior valor, mas MAX(points) o ignora e devolve o maior real. Ordenação e agregação não compartilham a regra de NULL.

O que isso muda para quem constrói

A lição de fundo, que Winslett deixa como subtítulo não oficial, é que restrições NOT NULL são coisa séria. A forma mais barata de nunca lidar com essas sete armadilhas é não armazenar NULL onde a coluna deveria sempre ter valor. Isso é decisão de modelagem: definir no schema que uma coluna é obrigatória, ou usar CHECK constraints, impede que o desconhecido chegue à aritmética. Quando NULL é legítimo (um preço unitário ainda não preenchido, por exemplo), a regra é decidir o significado dele no ponto exato onde a regra de negócio é conhecida, geralmente com COALESCE na fronteira do cálculo.

Há ainda um aviso que merece destaque para quem herda bancos antigos: o parâmetro transform_null_equals, criado entre as versões 6.5 e 7.1 para agradar formulários do Microsoft Access que geravam expr = NULL, reescreve = NULL como IS NULL quando ligado. Está desligado por padrão desde o Postgres 7.2, e Winslett é enfático:

De todas as configurações para mudar no Postgres, por favor não mude transform_null_equals.

Christopher Winslett, Crunchy Data

Para o desenvolvedor brasileiro que está migrando ou escalando banco em produção, o checklist final do artigo funciona como diagnóstico: linhas sumindo no WHERE, resultado vazio em NOT IN, total em branco, média fora do esperado, lag retornando NULL, nomes faltando ou valores nulos no topo de um DESC. Todos apontam para o mesmo lugar. Antes de escalar hardware ou reescrever o plano de execução, vale confirmar se o cálculo simplesmente esbarrou num desconhecido que ninguém decidiu o que significa.

Fonte: Planet PostgreSQL