[HACKERS] Statement parsing problem ?
G'day, There seems to be a kind of statement parsing problem in 7.4.5 (from debian postgresql-7.4.5-3, i386). Either that, or I'm missing something... The following script: -- create table t1 ( foo1 integer, foo2 integer ); create table t2 ( foo3 integer ); create table t3 ( foo4 integer, foo5 integer ); create table t4 ( foo6 integer ); \echo \echo --- \echo this works \echo --- select 1 as OK from t1, t2, t3 join t4 on (t4.foo6 = t3.foo5) where t2.foo3 = t1.foo1 and t3.foo4 = t1.foo2 ; \echo \echo \echo Error, from simply swapping the order of t2 and t3 ??? \echo select 1 from t1, t3, t2 join t4 on (t4.foo6 = t3.foo5) where t2.foo3 = t1.foo1 and t3.foo4 = t1.foo2 ; \echo \echo \echo slightly different error, using a table alias \echo select 1 from t1, t3 a, t2 join t4 on (t4.foo6 = a.foo5) where t2.foo3 = t1.foo1 and a.foo4 = t1.foo2 ; -- produces the output: -- --- this works --- OK (0 rows) Error, from simply swapping the order of t2 and t3 ??? psql:/tmp/test.sql:32: NOTICE: adding missing FROM-clause entry for table t3 psql:/tmp/test.sql:32: ERROR: JOIN/ON clause refers to t3, which is not part of JOIN slightly different error, using a table alias psql:/tmp/test.sql:46: ERROR: relation a does not exist -- So is it me, or is this just a bit borken ? Cheers, Chris. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Statement parsing problem ?
On Sep 15, 2004, at 9:43 AM, Chris Dunlop wrote: Either that, or I'm missing something... From the SELECT docs ... A JOIN clause combines two FROM items. Use parentheses if necessary to determine the order of nesting. In the absence of parentheses, JOINs nest left-to-right. In any case JOIN binds more tightly than the commas separating FROM items. CROSS JOIN and INNER JOIN produce a simple Cartesian product, the same result as you get from listing the two items at the top level of FROM, but restricted by the join condition (if any). CROSS JOIN is equivalent to INNER JOIN ON (TRUE), that is, no rows are removed by qualification. These join types are just a notational convenience, since they do nothing you couldn't do with plain FROM and WHERE. --- Since you're doing a simple join, you'd be better off using form select 1 as OK from t1, t2, t3, t4 on where t4.foo6 = t3.foo5 and t2.foo3 = t1.foo1 and t3.foo4 = t1.foo2 ; and then you can vary the order of the and clauses any way you like. But using the FROM t1, t2, t3 JOIN t4 form binds left-to-right tigher than the comma separated list, so it is operating on exactly two tables (t3 and t4), not the t1, t2, t3 cartesian product joined with t4. James Robinson Socialserve.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Statement parsing problem ?
Replying to my own post, thanks to the assistance of Paul Bort... On Wed, Sep 15, 2004 at 11:43:47PM +1000, Chris Dunlop wrote: There seems to be a kind of statement parsing problem in 7.4.5 (from debian postgresql-7.4.5-3, i386). Either that, or I'm missing something... \echo \echo Error, from simply swapping the order of t2 and t3 ??? \echo select 1 from t1, t3, t2 join t4 on (t4.foo6 = t3.foo5) where t2.foo3 = t1.foo1 and t3.foo4 = t1.foo2 ; I'd always thought: FROM t1, t2 join t3 meant: FROM (t1, t2) join t3 but as Paul pointed out, it's actually: FROM t1, (t2 join t3) I.e. in the example above: t2 join t4 on (t4.foo6 = t3.foo5) doesn't work because there's no t3.foo5 on the left of the join. So is it me, or is this just a bit borken ? It was me! Cheers, Chris. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
