BUG #12000: "CROSS JOIN" not equivalent to ","

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;

Responses

Browse pgsql-bugs by date

  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