BUG #6079: Wrong select result

From: "Renat" <renat(dot)nasyrov(at)itv(dot)ru>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #6079: Wrong select result
Date: 2011-06-28 14:41:30
Message-ID: 201106281441.p5SEfU5x051446@wwwmaster.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs


The following bug has been logged online:

Bug reference: 6079
Logged by: Renat
Email address: renat(dot)nasyrov(at)itv(dot)ru
PostgreSQL version: 9.0.4
Operating system: Windows
Description: Wrong select result
Details:

INPUT:

create table foo (
id bigint not null,
date_to timestamp without time zone,
CONSTRAINT foo_pkey PRIMARY KEY (id)
);

CREATE INDEX foo_date_to_index
ON foo
USING btree
(date_to)

insert into foo (id, date_to) values (1, now());
insert into foo (id, date_to) values (2, NULL);

select * from foo where date_to is null and date_to > '2011-01-01'

Expected: 0 rows

But: it return 1 row with id=2

If we will replace foo_date_to_index to:

CREATE INDEX foo_date_to_index
ON foo
USING btree
(date_to)
WHERE date_to is NOT NULL

Then:

SELECT * FROM foo where date_to is null and date_to > '2011-01-01'

Return: 0 rows

Please explain for me what happens?

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Pavel Stehule 2011-06-28 17:16:14 Re: BUG #6079: Wrong select result
Previous Message Peter Eisentraut 2011-06-26 21:41:30 Re: BUG #6066: [PATCH] Mark more strings as c-format