From: | kunert(at)cms(dot)hu-berlin(dot)de |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #12000: "CROSS JOIN" not equivalent to "," |
Date: | 2014-11-18 17:11:26 |
Message-ID: | 20141118171126.2479.55006@wrigleys.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 12000
Logged by: Andreas Kunert
Email address: kunert(at)cms(dot)hu-berlin(dot)de
PostgreSQL version: 9.3.5
Operating system: Ubuntu 14.04.1 LTS
Description:
Still not sure if this is really a bug, but I could not find a good
explanation for the following behaviour:
According to the documentation
http://www.postgresql.org/docs/9.3/static/queries-table-expressions.html
(7.2.1.1):
"FROM T1 CROSS JOIN T2 is equivalent to FROM T1, T2"
However, in the following example the SQL-Query using "CROSS JOIN" works,
while the usage of "," results in an invalid reference:
drop table if exists table1;
drop table if exists table2;
create table table1 (x integer);
create table table2 (y integer, z integer);
insert into table1 (x) values (1), (2);
insert into table2 (y,z) values (1,1), (1,2);
-- this query works:
select
a.x, b.x, c.y
from
table1 a
cross join table1 b
left outer join table2 c on a.x = y and b.x = z;
-- this does not:
select
a.x, b.x, c.y
from
table1 a
, table1 b
left outer join table2 c on a.x = y and b.x = z;
The error in the latter case is:
ERROR: invalid reference to FROM-clause entry for table "a"
LINE 29: left outer join table2 c on a.x = y and b.x = z;
^
HINT: There is an entry for table "a", but it cannot be referenced from
this part of the query.
********** Error **********
ERROR: invalid reference to FROM-clause entry for table "a"
SQL state: 42P01
Hint: There is an entry for table "a", but it cannot be referenced from this
part of the query.
Character: 447
Another example resulting in the same error but without using table
aliases:
drop table if exists table0;
drop table if exists table1;
drop table if exists table2;
create table table0 (x integer);
create table table1 (x integer);
create table table2 (y integer, z integer);
insert into table0 (x) values (1), (2);
insert into table1 (x) values (1), (2);
insert into table2 (y,z) values (1,1), (1,2);
-- works:
select
table0.x, table1.x, table2.y
from
table0
cross join table1
left outer join table2 on table0.x = y and table1.x = z;
-- does not work:
select
table0.x, table1.x, table2.y
from
table0
, table1
left outer join table2 on table0.x = y and table1.x = z;
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2014-11-18 17:29:03 | Re: BUG #12000: "CROSS JOIN" not equivalent to "," |
Previous Message | Sandro Santilli | 2014-11-18 11:38:34 | auto_explain makes CREATE EXTENSION unusable |