Thread: 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=3D# 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>: > > The following bug has been logged online: > > Bug reference: =C2=A0 =C2=A0 =C2=A06079 > Logged by: =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Renat > Email address: =C2=A0 =C2=A0 =C2=A0renat.nasyrov@itv.ru > PostgreSQL version: 9.0.4 > Operating system: =C2=A0 Windows > Description: =C2=A0 =C2=A0 =C2=A0 =C2=A0Wrong 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 > =C2=A0ON foo > =C2=A0USING btree > =C2=A0(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=3D2 > > If we will replace foo_date_to_index to: > > CREATE INDEX foo_date_to_index > =C2=A0ON foo > =C2=A0USING btree > =C2=A0(date_to) > =C2=A0WHERE 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? > > -- > 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
Attachment
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