different results based solely on existence of index (no, seriously)
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
);
reproduced it on:
"PostgreSQL 8.3.3 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.2.3
(Ubuntu 4.2.3-2ubuntu7)"
3 rows with index, 2 rows without.
can not reproduce it on:
- "PostgreSQL 8.1.10 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.1.3
20070831 (prerelease) (Ubuntu 4.1.2-16ubuntu1)"
- "PostgreSQL 8.2.6 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2
(mingw-special)"
- "PostgreSQL 8.2.7 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.2.3
(Ubuntu 4.2.3-2ubuntu4)"
they allways return 2 rows.
hth
WBL
On Aug 12, 2008, at 3:53 AM, Willy-Bas Loos wrote:
reproduced it on:
"PostgreSQL 8.3.3 on i486-pc-linux-gnu, compiled by GCC cc (GCC)
4.2.3 (Ubuntu 4.2.3-2ubuntu7)"
3 rows with index, 2 rows without.can not reproduce it on:
- "PostgreSQL 8.1.10 on i486-pc-linux-gnu, compiled by GCC cc (GCC)
4.1.3 20070831 (prerelease) (Ubuntu 4.1.2-16ubuntu1)"
- "PostgreSQL 8.2.6 on i686-pc-mingw32, compiled by GCC gcc.exe
(GCC) 3.4.2 (mingw-special)"
- "PostgreSQL 8.2.7 on i486-pc-linux-gnu, compiled by GCC cc (GCC)
4.2.3 (Ubuntu 4.2.3-2ubuntu4)"
they allways return 2 rows.hth
WBL
reproduced on:
PostgreSQL 8.3.1 on i386-apple-darwin9.4.0, compiled by GCC i686-apple-
darwin9-gcc-4.0.1 (GCC) 4.0.1 (Apple Inc. build 5465)
3rows with index, 2 rows without
Ries
On Aug 12, 2008, at 3:53 AM, Willy-Bas Loos wrote:
reproduced it on:
"PostgreSQL 8.3.3 on i486-pc-linux-gnu, compiled by GCC cc (GCC)
4.2.3 (Ubuntu 4.2.3-2ubuntu7)"
3 rows with index, 2 rows without.can not reproduce it on:
- "PostgreSQL 8.1.10 on i486-pc-linux-gnu, compiled by GCC cc (GCC)
4.1.3 20070831 (prerelease) (Ubuntu 4.1.2-16ubuntu1)"
- "PostgreSQL 8.2.6 on i686-pc-mingw32, compiled by GCC gcc.exe
(GCC) 3.4.2 (mingw-special)"
- "PostgreSQL 8.2.7 on i486-pc-linux-gnu, compiled by GCC cc (GCC)
4.2.3 (Ubuntu 4.2.3-2ubuntu4)"
they allways return 2 rows.hth
WBL
reproduced on:
PostgreSQL 8.3.1 on i386-apple-darwin9.4.0, compiled by GCC i686-apple-
darwin9-gcc-4.0.1 (GCC) 4.0.1 (Apple Inc. build 5465)
3rows with index, 2 rows without
Ries
On Aug 12, 8:17 am, p...@rvt.dds.nl (ries van Twisk) wrote:
On Aug 12, 2008, at 3:53 AM, Willy-Bas Loos wrote:
reproduced it on:
"PostgreSQL 8.3.3 on i486-pc-linux-gnu, compiled by GCC cc (GCC)
4.2.3 (Ubuntu 4.2.3-2ubuntu7)"
3 rows with index, 2 rows without.can not reproduce it on:
- "PostgreSQL 8.1.10 on i486-pc-linux-gnu, compiled by GCC cc (GCC)
4.1.3 20070831 (prerelease) (Ubuntu 4.1.2-16ubuntu1)"
- "PostgreSQL 8.2.6 on i686-pc-mingw32, compiled by GCC gcc.exe
(GCC) 3.4.2 (mingw-special)"
- "PostgreSQL 8.2.7 on i486-pc-linux-gnu, compiled by GCC cc (GCC)
4.2.3 (Ubuntu 4.2.3-2ubuntu4)"
they allways return 2 rows.hth
WBLreproduced on:
PostgreSQL 8.3.1 on i386-apple-darwin9.4.0, compiled by GCC i686-apple-
darwin9-gcc-4.0.1 (GCC) 4.0.1 (Apple Inc. build 5465)
3rows with index, 2 rows withoutRies
FWIW, reproduced (3 rows w/index, 2 w/o) on:
PostgreSQL 8.3.0 on i686-pc-linux-gnu, compiled by GCC gcc (GCC)
4.1.2 (Ubuntu 4.1.2-0ubuntu4)
On Mon, Aug 11, 2008 at 10:35:26PM -0500, Matthew Dennis wrote:
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)
I have reproduced it on 8.3.3.
Just FYI, a bug isn't a bug unless you can reproduce it on the latest
minor version, in this case 8.3.3, of the major version, in this case
8.3, that the bug appears in.
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
"Matthew Dennis" <mdennis@merfer.net> writes:
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.
I don't see any failure in 8.3 branch tip. I think the bug was fixed
here:
http://archives.postgresql.org/pgsql-committers/2008-06/msg00336.php
regards, tom lane