Skip site navigation (1) Skip section navigation (2)

Intersect with null fields

From: Slavica Stefic <izvori(at)iname(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Intersect with null fields
Date: 1999-11-29 23:28:08
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-bugs

                        POSTGRESQL BUG REPORT TEMPLATE

Your name               : Marko Mikulicic
Your email address      : mmikulicic(at)full-moon(dot)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

Next:From: Bruce MomjianDate: 1999-11-30 02:28:28
Subject: Re: A bug in NOT IN (SELECT ...
Previous:From: Emils KlotinsDate: 1999-11-29 17:33:10
Subject: Backend dies upon UPDATE of DATE field with CASE WHEN, 6.5.3

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group