O moido do NOT IN, o Postgres estaria mentindo, seria um bug ?

Esse problema-desafio foi exposto por Leonardo Campos, amigo e companheiro de trabalho, poderia ser mais uma questão de concurso público voltado para SGBD Postgres, estilo casca de banana.

Vamos ao Problema:

O moido dessa vez é saber pq o NOT IN não estava funcionando corretamente em alguns casos, no PG 9.0, seria um bug ou banco estava mentindo ?

A experiencia tem me mostrado que o banco não mente, o irônico é dizer isso no dia primeiro de abril, mas, cito a fato de que SQL tem base matemática na teoria dos conjuntos e algebra relacional, e dizer isso de um o banco SGBD Relacional em particular o Postgres, um projeto de muitos anos; é dá murro em ponta de faca.

Apresentando o desafio...

--Problema exposto 

DROP TABLE IF EXISTS tb1;
CREATE TEMP TABLE tb1 
( 
       id_tb1 integer PRIMARY KEY 
     , descricao varchar(28) NOT NULL
); 

DROP TABLE IF EXISTS tb2;
CREATE TEMP TABLE tb2 
(
       id_tb2 integer PRIMARY KEY 
     , descricao varchar(28) NOT NULL 
     , id_tb1 integer NULL 
     , CONSTRAINT fk_tb2_id_tb1 FOREIGN KEY (id_tb1) REFERENCES tb1 (id_tb1) 
);

INSERT INTO tb1 (id_tb1, descricao) VALUES (1,'TESTE1');
INSERT INTO tb1 (id_tb1, descricao) VALUES (2,'TESTE2');
INSERT INTO tb1 (id_tb1, descricao) VALUES (3,'TESTE3');
INSERT INTO tb1 (id_tb1, descricao) VALUES (4,'TESTE4');
INSERT INTO tb1 (id_tb1, descricao) VALUES (5,'TESTE5');
INSERT INTO tb1 (id_tb1, descricao) VALUES (6,'TESTE6');
INSERT INTO tb1 (id_tb1, descricao) VALUES (7,'TESTE7');

SELECT * FROM tb1;
/* Resultado 
 id_tb1 | descricao 
--------+-----------
      1 | TESTE1
      2 | TESTE2
      3 | TESTE3
      4 | TESTE4
      5 | TESTE5
      6 | TESTE6
      7 | TESTE7
(7 rows)
*/

INSERT INTO tb2 (id_tb2, descricao, id_tb1) VALUES (8,'TESTE1',1);
INSERT INTO tb2 (id_tb2, descricao, id_tb1) VALUES (9,'TESTE2',2);
INSERT INTO tb2 (id_tb2, descricao, id_tb1) VALUES (10,'TESTE3',3);
INSERT INTO tb2 (id_tb2, descricao, id_tb1) VALUES (11,'TESTE4',4);
INSERT INTO tb2 (id_tb2, descricao, id_tb1) VALUES (12,'TESTE5',5);
INSERT INTO tb2 (id_tb2, descricao, id_tb1) VALUES (13,'TESTE7',NULL);

SELECT * FROM tb2;
/* Resultado
 id_tb2 | descricao | id_tb1 
--------+-----------+--------
      8 | TESTE1    |      1
      9 | TESTE2    |      2
     10 | TESTE3    |      3
     11 | TESTE4    |      4
     12 | TESTE5    |      5
     13 | TESTE7    |   NULL   
(6 rows)

*/

--Query 1
     SELECT * 
       FROM tb1 
      WHERE id_tb1 IN 
                         (
                              SELECT id_tb1
                                FROM tb2
                         )
          ;
/* Resultado
 id_tb1 | descricao 
--------+-----------
      1 | TESTE1
      2 | TESTE2
      3 | TESTE3
      4 | TESTE4
      5 | TESTE5
(5 rows)
*/          

--Query 2 (Seria um bug do Postgres? Retornou nada!)      
     SELECT * 
       FROM tb1 
      WHERE id_tb1 NOT IN 
                         (
                              SELECT id_tb1
                                FROM tb2
                         )
          ;
--Nada, aparentemente o correto seria trazer os registros especificados, será o que SGBD (o banco) Postgres está mentindo ?
/* Resultado
 id_tb1 | descricao 
--------+-----------
(0 rows)
*/

--Análise 1, uma consulta equivalente com not in, apenas para testar
     SELECT * 
       FROM tb1 
      WHERE id_tb1 <> ALL
                         (
                              SELECT id_tb1
                                FROM tb2
                         )
          ;
--Idem ao problema apresentado na Query 2 
/* Resultado
 id_tb1 | descricao 
--------+-----------
(0 rows)
*/

--Análise 2, o que está havendo?
     SELECT 
   DISTINCT *
       FROM tb2
   ORDER BY 1
          ;
--Existe um campo NULL no final do resultado. Será que esse NULL se propaga no NOT IN, mas pq não no IN   ?
/* Resultado
 id_tb2 | descricao | id_tb1 
--------+-----------+--------
      8 | TESTE1    |      1
      9 | TESTE2    |      2
     10 | TESTE3    |      3
     11 | TESTE4    |      4
     12 | TESTE5    |      5
     13 | TESTE7    |   NULL   
(6 rows)
*/

-- Exemplo 1, Solução

     SELECT * 
       FROM tb1 
      WHERE id_tb1 NOT IN 
                         (
                              SELECT id_tb1
                                FROM tb2
                               WHERE id_tb1 IS NOT NULL
                         )
          ;
--Com a inclusão da condicional removendo os nulos, deu certo, pq?, Qual a explicação disso?
/* Resultado 
 id_tb1 | descricao 
--------+-----------
      6 | TESTE6
      7 | TESTE7
(2 rows)
*/

Conclusão

No Postgres o null se propaga, isto é, qualquer coisa concatenada com NULL se torna NULL exemplo:

SELECT 'NULL' || NULL AS teste1; 

SELECT 'Isto ' || 'é' ||' apenas '|| ' um ' || NULL || ' teste .... ' AS teste2; 

Esse mesmo conceito se aplica também ao NOT IN.

Então:

Deve ser observado que se o resultado da expressão à esquerda for nulo,
ou se não houver nenhum valor igual à direita e uma das linhas à direita tiver o valor nulo,
o resultado da construção NOT IN será nulo, e não verdade.
Isto está de acordo com as regras normais do SQL para combinações booleanas de valores nulos. [1]
Idem na versões demais versões do Postgres e a ultima 9.0 [2]

Concluindo isso não é um bug mas sim um comportamento padrão do NOT IN, que está em conformidade com as normas SQL/ANSI,
lembrando que uso de NOT IN não é recomendável quando se pensa em desempenho.

E finalmente o banco (SGBDR) não mente! :)

[1]
http://pgdocptbr.sourceforge.net/pg80/functions-subquery.html#FUNCTIONS-NOT-IN-SUBQ
[2]
http://www.postgresql.org/docs/9.0/static/functions-comparisons.html#AEN16842