Scott Marlowe <smarlowe@xxxxxxxxxxxxxxxxx> 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
