Re: change the order of FROM selection to make query work

[Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>]

Scott Marlowe <smarlowe(at)g2switchworks(dot)com> writes:
> On Tue, 2006-09-26 at 09:19, Tom Lane wrote:
>> I was aware that MySQL parses this sort of structure wrongly, but it's
>> disappointing to hear that sqlite does too :-(

> And I think MySQL fixed this abberant behaviour in the newest beta.

Actually they seem to have fixed it awhile ago --- I get this in 5.0.22:

mysql> create table t1(f1 int);
Query OK, 0 rows affected (0.02 sec)

mysql> create table t2(f2 int);
Query OK, 0 rows affected (0.01 sec)

mysql> select * from t1 c, t2 a join t1 b on (a.f2 = b.f1);
Empty set (0.00 sec)

mysql> select * from t1 c, t2 a join t1 b on (a.f2 = c.f1);
ERROR 1054 (42S22): Unknown column 'c.f1' in 'on clause'

mysql> select * from t1 c cross join t2 a join t1 b on (a.f2 = c.f1);
Empty set (0.01 sec)

which appears to conform to the letter of the standard.

FYI, PG 8.2 will produce a possibly more useful error message:

regression=# select * from t1 c, t2 a join t1 b on (a.f2 = c.f1);
ERROR:  invalid reference to FROM-clause entry for table "c"
LINE 1: select * from t1 c, t2 a join t1 b on (a.f2 = c.f1);
                                                      ^
HINT:  There is an entry for table "c", but it cannot be referenced from this part of the query.
regression=#


			regards, tom lane