different results based solely on existence of index (no, seriously)

From: "Matthew Dennis" <mdennis(at)merfer(dot)net>
To: PGSQL <pgsql-general(at)postgresql(dot)org>
Subject: different results based solely on existence of index (no, seriously)
Date: 2008-08-12 03:35:26
Message-ID: e94d85500808112035y277ffcd0wd683cc45367ab00b@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-general

In reference to the script below (I know it can be rewritten, that's not the
point), I get 3 rows if the referenced index exists but only two rows if it
does not. This is observable and repeatable just by dropping/creating the
index. Drop the index and two rows are returned. Create the index, three
rows are returned. Drop the index, two rows again. In addition, in no case
does the selected column t2.c2 actually contain a value (it's always null).
Since in the 3 row case, it returns a row with t1.c1=2, I would have
expected a value from t2 (if you add t2.c1 to select clause you can see that
is null as well).

It's probably worth mentioning (since it actually took me a while to notice)
that the plans are subtlety different. Neither plan (with or without index
existing) actually uses the index, but in one case there is an extra filter
node.

version string is PostgreSQL 8.3.1 on i686-redhat-linux-gnu, compiled by GCC
gcc (GCC) 4.1.2 20070925 (Red Hat 4.1.2-33)

create table t1(c1 int);
create table t2(c1 int, c2 timestamp with time zone);

--problem index
create index someidx on t2 using btree(c2);

insert into t1 values (1),(2),(3);
insert into t2 values(2, now());

select
t1.c1,
t2.c2
from
t1
left join t2 on
t1.c1 = t2.c1
where
t2.c2 is null
or (
t2.c2 = (select max(c2) from t2 where t1.c1 = t2.c1)
and t2.c2 < now() - '1 day'::interval
);

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Bruce Momjian 2008-08-12 03:43:23 Re: BUG #4321: 8.3 installer shortens allowable BASE_DIR path length?
Previous Message Robert Treat 2008-08-12 02:48:36 Re: return query with set-returning functions

Browse pgsql-general by date

  From Date Subject
Next Message Dushyanth 2008-08-12 06:27:48 Re: [Postgresql 8.2.3] autovacuum starting up even after disabling ?
Previous Message Scott Marlowe 2008-08-12 01:52:49 Re: Running a PL/pgSQL function