JOINing subselects in FROM?
Hello:
I'm playing around with the syntax of the SELECT.
I have a very simple table (this is a silly example):
create table datos (a�o int, mes text, valor int);
with a couple tuples:
insert into datos (a�o, mes, valor) values (1999, 'enero', 100);
insert into datos (a�o, mes, valor) values (2000, 'enero', 200);
insert into datos (a�o, mes, valor) values (1999, 'febrero', 300);
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"
If I try to do an INNER JOIN, I get instead
SELECT foo1.mes, valor1999 AS "1999", valor2000 AS "2000" FROM
(SELECT valor AS valor1999, mes FROM datos WHERE a�o=1999) AS foo1
INNER JOIN
(SELECT valor AS valor2000, mes FROM datos WHERE a�o=2000) AS foo2
ON foo1.mes=foo2.mes;
ERROR: flatten_join_alias_var: unexpected subtree type
But if I do it like
SELECT foo1.mes, valor1999 AS "1999", valor2000 AS "2000" FROM
(SELECT valor AS valor1999, mes FROM datos WHERE a�o=1999) AS foo1,
(SELECT valor AS valor2000, mes FROM datos WHERE a�o=2000) AS foo2
WHERE foo1.mes=foo2.mes;
it works (but I lose some tuples -- I expected to get NULL there in the
OUTER JOIN query).
Remember, this is a silly example, I'm not trying to use it for anything
but playing around with the syntax. But I wonder whether this is
supposed to work?
alvherre=> select version();
version
----------------------------------------------------------------
PostgreSQL 7.3devel on i686-pc-linux-gnu, compiled by GCC 2.96
(1 row)
On a related note, psql's "\h select" shows the syntax for select, but
it doesn't specify what are the terminal symbols for the join_type
non-terminal.
(this is a week-old or so CVS)
--
Alvaro Herrera (<alvherre[a]atentus.com>)
"Puedes vivir solo una vez, pero si lo haces bien, una vez es suficiente"
Alvaro Herrera <alvherre@atentus.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.
If I try to do an INNER JOIN, I get instead
SELECT foo1.mes, valor1999 AS "1999", valor2000 AS "2000" FROM
(SELECT valor AS valor1999, mes FROM datos WHERE a�o=1999) AS foo1
INNER JOIN
(SELECT valor AS valor2000, mes FROM datos 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?
regards, tom lane
En Wed, 10 Apr 2002 11:02:08 -0400
Tom Lane <tgl@sss.pgh.pa.us> escribi�:
Alvaro Herrera <alvherre@atentus.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)
Import Notes
Resolved by subject fallback
Alvaro Herrera <alvherre@atentus.com> writes:
[ in development sources ]
SELECT foo1.mes, valor1999 AS "1999", valor2000 AS "2000" FROM
(SELECT valor AS valor1999, mes FROM datos WHERE a�o=1999) AS foo1
INNER JOIN
(SELECT valor AS valor2000, mes FROM datos WHERE a�o=2000) AS foo2
ON foo1.mes=foo2.mes;
ERROR: flatten_join_alias_var: unexpected subtree type
I have committed changes to fix this bug. Thanks for the report!
regards, tom lane