Обсуждение: Bug with view definition?

Hello all,

why is the last definition of a view not working, although the
documentation says all three are equal?

Testcase:

CREATE SCHEMA one;
CREATE SCHEMA two;

CREATE TABLE one.one (
    id SERIAL PRIMARY KEY
);

CREATE TABLE two.two (
    id SERIAL PRIMARY KEY
);

CREATE TABLE join1 (
    id SERIAL PRIMARY KEY
);

CREATE OR REPLACE VIEW working AS
    SELECT one.*
    FROM one.one
    JOIN two.two ON TRUE
    JOIN join1 ON join1.id = one.id;

CREATE OR REPLACE VIEW also_working AS
    SELECT one.*
    FROM one.one
    CROSS JOIN two.two
    JOIN join1 ON join1.id = one.id;

CREATE OR REPLACE VIEW not_working AS
    SELECT one.*
    FROM one.one, two.two
    JOIN join1 ON join1.id = one.id;

Thanks in advance

Sebastian

Sebastian Böck wrote:
> Hello all,
>
> why is the last definition of a view not working, although the
> documentation says all three are equal?
>

>
> CREATE OR REPLACE VIEW not_working AS
>     SELECT one.*
>     FROM one.one, two.two
>     JOIN join1 ON join1.id = one.id;

I think it's trying to join "two" to "join1" - try
   ...FROM two.two, one.one
   JOIN join1...

--
   Richard Huxton
   Archonet Ltd

Richard Huxton wrote:
> Sebastian Böck wrote:
>
>>Hello all,
>>
>>why is the last definition of a view not working, although the
>>documentation says all three are equal?
>>
>
>
>>CREATE OR REPLACE VIEW not_working AS
>>    SELECT one.*
>>    FROM one.one, two.two
>>    JOIN join1 ON join1.id = one.id;
>
>
> I think it's trying to join "two" to "join1" - try
>    ...FROM two.two, one.one
>    JOIN join1...

Sure, but the problem still exists if you want to join with table one
and table two.

Forgot to say that this also applies for normal selects (of course!).

Sebastian

=?ISO-8859-1?Q?Sebastian_B=F6ck?= <sebastianboeck@freenet.de> writes:
> why is the last definition of a view not working, although the
> documentation says all three are equal?

The documentation says no such thing...

> CREATE OR REPLACE VIEW not_working AS
>     SELECT one.*
>     FROM one.one, two.two
>     JOIN join1 ON join1.id = one.id;

JOIN binds tighter than comma in FROM-lists, so that means

    FROM one.one CROSS JOIN (two.two JOIN join1 ON join1.id = one.id);

which of course is illegal because the JOIN/ON condition refers to
something that's not within the current JOIN.  Your preceding example
parenthesizes as

    FROM (one.one CROSS JOIN two.two) JOIN join1 ON join1.id = one.id;

which is OK.

            regards, tom lane

Tom Lane wrote:
> =?ISO-8859-1?Q?Sebastian_B=F6ck?= <sebastianboeck@freenet.de> writes:
>
>>why is the last definition of a view not working, although the
>>documentation says all three are equal?
>
>
> The documentation says no such thing...

So I misinterpreted the following:

http://www.postgresql.org/docs/8.0/interactive/queries-table-expressions.html

that says:

FROM T1 CROSS JOIN T2 is equivalent to FROM T1, T2. It is also
equivalent to FROM T1 INNER JOIN T2 ON TRUE

>
>
>>CREATE OR REPLACE VIEW not_working AS
>>    SELECT one.*
>>    FROM one.one, two.two
>>    JOIN join1 ON join1.id = one.id;
>
>
> JOIN binds tighter than comma in FROM-lists, so that means
>
>     FROM one.one CROSS JOIN (two.two JOIN join1 ON join1.id = one.id);
>
> which of course is illegal because the JOIN/ON condition refers to
> something that's not within the current JOIN.  Your preceding example
> parenthesizes as
>
>     FROM (one.one CROSS JOIN two.two) JOIN join1 ON join1.id = one.id;
>
> which is OK.

Thanks for clarification

Sebastian

Sebastian Böck wrote:
> Richard Huxton wrote:
>
>> Sebastian Böck wrote:
>>
>>> Hello all,
>>>
>>> why is the last definition of a view not working, although the
>>> documentation says all three are equal?
>>>
>>
>>
>>> CREATE OR REPLACE VIEW not_working AS
>>>    SELECT one.*
>>>    FROM one.one, two.two
>>>    JOIN join1 ON join1.id = one.id;
>>
>>
>>
>> I think it's trying to join "two" to "join1" - try
>>    ...FROM two.two, one.one
>>    JOIN join1...
>
>
> Sure, but the problem still exists if you want to join with table one
> and table two.

Sorry - hadn't read the initial post carefully enough, and didn't see
the unconstrained join on one,two. Since "JOIN" has a high precedence
you'll want to force the issue with a subselect:

SELECT *
FROM (
   SELECT one.* FROM one.one, two.two
) AS dummy
JOIN join1 ON join1.id = dummy.id

--
   Richard Huxton
   Archonet Ltd