Participants
Slavica Stefic
Slavica Stefic
Attachments
Show all attachments
Thread Outline
#1Slavica SteficSlavica Stefic
Nov 29, 1999

Intersect with null fields

Started by Slavica Steficover 26 years ago1 messagesbugs
Jump to latest
#1Slavica Stefic
Slavica Stefic
izvori@iname.com

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

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;

...

From bouncefilter Mon Nov 29 21:32:13 1999
Received: from candle.pha.pa.us (pgman@s5-03.ppp.op.net [209.152.195.67])
by hub.org (8.9.3/8.9.3) with ESMTP id VAA53925
for <pgsql-hackers@postgreSQL.org>;
Mon, 29 Nov 1999 21:31:18 -0500 (EST)
(envelope-from pgman@candle.pha.pa.us)
Received: (from pgman@localhost) by candle.pha.pa.us (8.9.0/8.9.0) id
VAA22088;
Mon, 29 Nov 1999 21:28:28 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <199911300228.VAA22088@candle.pha.pa.us>
Subject: Re: A bug in NOT IN (SELECT ...
In-Reply-To: <381E9CCB.A46CDF62@tm.ee> from Hannu Krosing at "Nov 2,
1999 08:11:55 am"
To: Hannu Krosing <hannu@tm.ee>
Date: Mon, 29 Nov 1999 21:28:28 -0500 (EST)
CC: Bruce Momjian <maillist@candle.pha.pa.us>,
PostgreSQL-development <pgsql-hackers@postgreSQL.org>
X-Mailer: ELM [version 2.4ME+ PL65 (25)]
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

Can you be more specific about the problem?

Hi,

Pleas post this to approproiate lists also, as I'm currently
rejected from lists due to address change.

I am running 6.5.2 on RH Linux 6.0 and I have a following bug
(the dump of two tables involved is attached)

hannu=> select title from document where subject not in (
hannu-> select full_path from group_directory);
title
-----
(0 rows)

hannu=> select title from document where not subject in (
hannu-> select full_path from group_directory);
testcert
.
.
.
tester
tester
lugu
hlhkllk
(26 rows)

What's even more scary is that a little after trying to get it
work right and doing the first query a lot, I got a server crash
with corrupted shared memory, that had to be cured with a reboot
(was faster than finding docs for ipcclean)

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026
← Back to Topics