Re: JOINing subselects in FROM?

En Wed, 10 Apr 2002 11:02:08 -0400
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> escribió:

> Alvaro Herrera <alvherre(at)atentus(dot)com> writes:
> > I'm trying to JOIN two subselects-in-from clauses and can't get it
> > right:
>
> > SELECT foo1.mes, valor1999 AS "1999", valor2000 AS "2000" FROM
> > (SELECT valor AS valor1999, mes FROM datos WHERE año=1999) AS foo1
> > OUTER JOIN
> > (SELECT valor AS valor2000, mes FROM datos WHERE año=2000) AS foo2
> > ON foo1.mes=foo2.mes;
> > ERROR: parser: parse error at or near "OUTER"
>
> Should be LEFT JOIN or LEFT OUTER JOIN.

Gee. Sorry. Anyway, I corrected it and got the same error that for the
other queries:

SELECT foo1.mes, valor1999 AS "1999", valor2000 AS "2000" FROM
(SELECT valor AS valor1999, mes FROM silly WHERE año=1999) AS foo1
LEFT OUTER JOIN
(SELECT valor AS valor2000, mes FROM silly WHERE año=2000) AS foo2
ON foo1.mes=foo2.mes;
ERROR: flatten_join_alias_var: unexpected subtree type

> [ scratches head ... ] I do not get that. I think there must be
> something broken about your build; or perhaps there's a portability
> problem lurking in devel sources. Would you try a full rebuild (make
> distclean, configure, build, initdb) to eliminate the possibility of
> internal version mismatches?

I just updated CVS ("CVS tip" as you call it), make distclean, initdb'd
and recreated the test scenario. I get this "unexpected subtree type"
message.

I just got CVS REL7_2_STABLE and tested there; the LEFT OUTER JOIN works
fine, as I expected in the first place. The difference seems to lie en
the changes you made to src/backend/optimizer/util/var.c, but those are
non-trivial changes and I don't grok the code.

--
Alvaro Herrera (<alvherre[a]atentus.com>)
"Estoy de acuerdo contigo en que la verdad absoluta no existe...
El problema es que tu estás mintiendo y la mentira sí existe" (G. Lama)