Обсуждение: BUG #6330: Incorrect select results when using mutually exclusive conditions for nullable column with index

The following bug has been logged on the website:

Bug reference:      6330
Logged by:          Nikolay Gorshkov
Email address:      nikolay.gorshkov@gmail.com
PostgreSQL version: 9.0.4
Operating system:   Ubuntu 10.04.2 LTS
Description:=20=20=20=20=20=20=20=20


How to reproduce:

# create table test (uid varchar(255) primary key, dt date);
# insert into test values ('1', now()), ('2', now()), ('3', null);
# create index test_dt on test(dt);
# analyze test;
# select * from test where dt is null and dt >=3D '2011-01-01 +01:00:00';

I expect zero results from the selection since the conditions are mutually
exclusive. However, one row with NULL value in "dt" column is returned:

 uid | dt
-----+----
 3   |
(1 row)


If the index "test_dt" is dropped:

# drop index test_dt;

The same query return zero rows, as expected:

# select * from test where dt is null and dt >=3D '2011-01-01 +01:00:00';

 uid | dt
-----+----
(0 rows)



PostgreSQL version information:
# select version();
Result: PostgreSQL 9.0.4 on i486-pc-linux-gnu, compiled by GCC gcc-4.4.real
(Ubuntu 4.4.3-4ubuntu5) 4.4.3, 32-bit

Linux information:
# uname -a
Result: Linux hostname 2.6.32-21-generic-pae #32-Ubuntu SMP Fri Apr 16
09:39:35 UTC 2010 i686 GNU/Linux

On Wed, Dec 7, 2011 at 5:42 AM,  <nikolay.gorshkov@gmail.com> wrote:
> PostgreSQL version information:
> # select version();
> Result: PostgreSQL 9.0.4 on i486-pc-linux-gnu, compiled by GCC gcc-4.4.real
> (Ubuntu 4.4.3-4ubuntu5) 4.4.3, 32-bit
>
> Linux information:
> # uname -a
> Result: Linux hostname 2.6.32-21-generic-pae #32-Ubuntu SMP Fri Apr 16
> 09:39:35 UTC 2010 i686 GNU/Linux

FWIW, I could not reproduce it on

PostgreSQL 9.0.5 on x86_64-unknown-linux-gnu, compiled by GCC gcc
(GCC) 4.1.2 20080704 (Red Hat 4.1.2-50), 64-bit
Linux 2.6.18-238.12.1.el5 #1 SMP Tue May 31 13:22:04 EDT 2011 x86_64
x86_64 x86_64 GNU/Linux

or

PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc (GCC)
4.4.5 20110214 (Red Hat 4.4.5-6), 64-bit
Linux 2.6.32-71.29.1.el6.x86_64 #1 SMP Mon Jun 27 19:49:27 BST 2011
x86_64 x86_64 x86_64 GNU/Linux

bricklen <bricklen@gmail.com> writes:
> On Wed, Dec 7, 2011 at 5:42 AM,  <nikolay.gorshkov@gmail.com> wrote:
>> PostgreSQL version information:
>> Result: PostgreSQL 9.0.4 on i486-pc-linux-gnu, compiled by GCC gcc-4.4.real
>> (Ubuntu 4.4.3-4ubuntu5) 4.4.3, 32-bit

> FWIW, I could not reproduce it on
> PostgreSQL 9.0.5 on x86_64-unknown-linux-gnu, compiled by GCC gcc
> (GCC) 4.1.2 20080704 (Red Hat 4.1.2-50), 64-bit

Yeah, this is fixed as of 9.0.5:
http://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=bbfcc7149

            regards, tom lane