Thread: BUG #6330: Incorrect select results when using mutually exclusive conditions for nullable column with index
BUG #6330: Incorrect select results when using mutually exclusive conditions for nullable column with index
From
nikolay.gorshkov@gmail.com
Date:
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
Re: BUG #6330: Incorrect select results when using mutually exclusive conditions for nullable column with index
From
bricklen
Date:
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
Re: BUG #6330: Incorrect select results when using mutually exclusive conditions for nullable column with index
From
Tom Lane
Date:
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