Обсуждение: 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

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