view + explain + index scan -> bogus varno: 65001 (with some variations)

From: Jon Nelson <jnelson+pgsql(at)jamponi(dot)net>
To: pgsql-bugs(at)postgresql(dot)org
Subject: view + explain + index scan -> bogus varno: 65001 (with some variations)
Date: 2011-06-30 03:42:37
Message-ID: BANLkTin7T900fE+2RmZNayT-gNp1URW24Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The problem was observed on CentOS 5.6 using postgresql 8.4.7 and
Scientific Linux 6.0 also using postgresql 8.4.7.
The problem could not be replicated on openSUSE 11.4 which has postgresql 9.0.3.

With 8.4.7, I ran into an issue trying to explain a VIEW query.
After much effort, I distilled the query down and was able to
replicate the issue with a test script, included below.
A few notes:

1. if I set enable_indexscan to false, the problem goes away.
2. if I remove the "and table_date" clause, the problem goes away
3. I have also seen this error: ERROR: bogus varattno for OUTER var: 1
under the same conditions.
4. 9.0.3 on openSUSE 11.4 does *not* show the problem (at least, I'm
not able to replicate it there).

begin;

set enable_seqscan = false;

drop view if exists foobar;
drop table if exists foo;
drop table if exists bar;
drop table if exists baz;
create table foo (column1 int);
create index foo_column1_idx on foo (column1);
insert into foo select generate_series(1,100000);

create table bar (column1 int);
create index bar_column1_idx on bar (column1);
insert into bar select generate_series(100000,200000);

create table baz (column1 int);
create index baz_column1_idx on baz (column1);
insert into baz select generate_series(50000,150000);

create view FOOBAR AS
select *, DATE '2011-01-01' as table_date from foo UNION ALL select *,
DATE '2011-01-02' as table_date FROM bar ;

explain verbose SELECT foobar.* FROM foobar, baz
WHERE foobar.column1 = baz.column1 AND table_date >= now() ;

rollback;

--
Jon

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message heasley 2011-06-30 06:19:55 Re: BUG #5741: syslog line length
Previous Message Craig Ringer 2011-06-30 00:41:58 Re: BUG #6063: compatability issues