Thread

Intersect with null fields

Slavica Stefic <izvori@iname.com> — 1999-11-29T23:28:08Z

============================================================================

                        POSTGRESQL BUG REPORT TEMPLATE
============================================================================



Your name               : Marko Mikulicic
Your email address      : mmikulicic@full-moon.com


System Configuration
---------------------
  Architecture (example: Intel Pentium)         : Intel Pentium

  Operating System (example: Linux 2.0.26 ELF)  : Linux 2.2.13 ELF

  PostgreSQL version (example: PostgreSQL-6.5.2):   PostgreSQL-6.5.2

  Compiler used (example:  gcc 2.8.0)           : pgcc-2.91.66


Please enter a FULL description of your problem:
------------------------------------------------
 The SQL "intersect" construct returns an empty query in some cases
when some fields are null.




Please describe a way to repeat the problem.   Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------

 create table buggy ( a int, b int);
insert into buggy values ( 1);
insert into buggy values ( 2);
insert into buggy values ( 3);
insert into buggy values ( 4);

-- this one is ok
select   a,b from buggy  where a = 2 intersect select a,b from buggy
where a  = 2;

-- this one gets an empty row
select   a,b from buggy  where a = 2 intersect select a,b from buggy
where a  != 3;

-- this also
select   a,b from buggy  intersect select a,b from buggy where a  = 3;

...