Intersect with null fields - Mailing list pgsql-bugs

From Slavica Stefic
Subject Intersect with null fields
Date
Msg-id 38430C07.F0879C7C@iname.com
Whole thread Raw
List pgsql-bugs
============================================================================

                        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;

...




pgsql-bugs by date:

Previous
From: Tom Lane
Date:
Subject: Re: Backend dies upon UPDATE of DATE field with CASE WHEN, 6.5.3
Next
From: Bruce Momjian
Date:
Subject: Re: [HACKERS] Problems in 6.5.3 with Multi-Byte encoding