Horton

Re: pg_upgrade failed if view contain natural left join condition

David G. Johnston <david.g.johnston@gmail.com>

View thread View on postgr.es
From: "David G. Johnston" <david.g.johnston@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: tushar <tushar.ahuja@enterprisedb.com>, PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2017-07-20T15:57:37Z
Lists: pgsql-hackers 
On Thu, Jul 20, 2017 at 6:53 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> tushar <tushar.ahuja@enterprisedb.com> writes:
> > postgres=# create table t(n int);
> > CREATE TABLE
> > postgres=# create table t1(a int);
> > CREATE TABLE
> > postgres=# create view ttt1 as SELECT e.n FROM t e NATURAL LEFT JOIN t1
> d;
> > CREATE VIEW
>
> You realize of course that that's a pretty useless join definition.
> Still, yes, we do need to reverse-list the view with correct syntax.
> Probably t LEFT JOIN t1 ON TRUE would do it.
>

Per the docs:

"If there are no common column names, NATURAL behaves like CROSS JOIN."

I'm being a bit pedantic here but since NATURAL is a replacement for
"ON/USING" it would seem more consistent to describe it, when no matching
columns are found, as "behaves like specifying ON TRUE" instead.   Maybe
"behaves like specifying ON TRUE, causing a CROSS JOIN to occur instead."

I find it a bit strange, though not surprising, that it doesn't devolve to
"ON FALSE".

David J.

Commits

  1. Doc: clarify description of degenerate NATURAL joins.

  2. Fix dumping of outer joins with empty qual lists.