Thread: 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