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 asFROM (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