Обсуждение: view + explain + index scan -> bogus varno: 65001 (with some variations)

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

Jon Nelson <jnelson+pgsql@jamponi.net> writes:
> 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.

FWIW, I can't reproduce such a failure with current 8.4 branch (nor any
other).  It's possible this has been fixed since 8.4.7, but I don't
immediately see any commit log entries that look related.  Another
possibility is that there's something nondefault about your environment,
for instance nondefault values for planner cost parameters, and the
example depends on those to work (or rather, not work).

            regards, tom lane

On Fri, Jul 1, 2011 at 3:27 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Jon Nelson <jnelson+pgsql@jamponi.net> writes:
>> 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.
>
> FWIW, I can't reproduce such a failure with current 8.4 branch (nor any
> other). =C2=A0It's possible this has been fixed since 8.4.7, but I don't
> immediately see any commit log entries that look related. =C2=A0Another
> possibility is that there's something nondefault about your environment,
> for instance nondefault values for planner cost parameters, and the
> example depends on those to work (or rather, not work).

I managed to reproduce the problem on 9.0.3 as well.
Try adding:

set enable_mergejoin =3D false;

--=20
Jon

Jon Nelson <jnelson+pgsql@jamponi.net> writes:
> On Fri, Jul 1, 2011 at 3:27 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> FWIW, I can't reproduce such a failure with current 8.4 branch (nor any
>> other).

> Try adding:
> set enable_mergejoin = false;

Thanks, that did it.  I've repaired the symptom exposed here:
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=d3d3ec0d890412f3b39a70e8fd08edf17e3c9f7c
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=789d3d4541e95c6079a55196bd63a6ab90e57c7c

However, while I see how this could generate "bogus varno: 65001", I'm
not entirely sure that the same issue explains your other report of
"bogus varattno for OUTER var".  If you can still reproduce that,
please send a test case.

            regards, tom lane