BUG #6079: Wrong select result
The following bug has been logged online:
Bug reference: 6079
Logged by: Renat
Email address: renat.nasyrov@itv.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?
Hello
It working as expected on 9.0.4 Linux. Please, can you recheck your
application? Try to use a console - psql,
postgres=# select * from foo where date_to is null and date_to > '2011-01-01';
id | date_to
----+---------
(0 rows)
Regards
Pavel Stehule
2011/6/28 Renat <renat.nasyrov@itv.ru>:
Show quoted text
The following bug has been logged online:
Bug reference: 6079
Logged by: Renat
Email address: renat.nasyrov@itv.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 NULLThen:
SELECT * FROM foo where date_to is null and date_to > '2011-01-01'
Return: 0 rows
Please explain for me what happens?
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
2011/6/28 Renat <renat.nasyrov@itv.ru>:
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
I get the same error on HEAD too. An even simpler test case is this:
create table foo(a int);
create index foo_a_idx on foo(a);
insert into foo values (10),(NULL);
select 1 from foo where a is null and a > 10000;
?column?
----------
1
(1 row)
The problem seems to be in _bt_preprocess_keys(), which discards the
"a > 10000" predicate in favour of the "a is null" predicate on the
grounds that "null > 10000" in a nulls-last index.
It looks like a previous revision had the right check, based on the
logic that x IS NULL is incompatible with any other predicate.
Regards,
Dean
Attachments:
nbtutils.patchapplication/octet-stream; name=nbtutils.patchDownload+7-0
Dean Rasheed <dean.a.rasheed@gmail.com> writes:
2011/6/28 Renat <renat.nasyrov@itv.ru>:
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
The problem seems to be in _bt_preprocess_keys(), which discards the
"a > 10000" predicate in favour of the "a is null" predicate on the
grounds that "null > 10000" in a nulls-last index.
It looks like a previous revision had the right check, based on the
logic that x IS NULL is incompatible with any other predicate.
Yeah, seems to be my fault :-(. Will fix.
regards, tom lane