Storm does not support MySQL's multi-table join syntax

[Robert Jordens]

storm 0.16-1~ppa2.9.10
mysql Ver 14.14 Distrib 5.1.37, for debian-linux-gnu (x86_64) using EditLine wrapper
mysqldb.version_info = (1, 2, 2, 'final', 0)

Description:

MySQL (at least) does not tolerate un-parenthesized tables on the right hand of a join:

mysql> select * from (select 1) as c left join (select 2) as a, (select 3) as b on 1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' (select 3) as b on 1' at line 1

The following is correct:
mysql> select * from (select 1) as c left join ((select 2) as a, (select 3) as b) on 1;
+---+------+------+
| 1 | 2 | 3 |
+---+------+------+
| 1 | 2 | 3 |
+---+------+------+
1 row in set (0.02 sec)

Code to reproduce the compilation:

from storm.locals import *
from storm.expr import *
from storm.info import *
class S(Storm):
    __storm_table__ = "t"
    id = Int(primary=True)
S1 = ClassAlias(S)
S2 = ClassAlias(S)
print compile(Select(S.id, tables=LeftJoin(S, (S1, S2), S1.id==S2.id)))

yields:
SELECT t.id FROM t LEFT JOIN t AS "_1", t AS "_2" ON "_1".id = "_2".id

should be:
SELECT t.id FROM t LEFT JOIN (t AS "_1", t AS "_2") ON "_1".id = "_2".id

[James Henstridge (jamesh)]

I'm not sure either of the before or after versions of the SQL look correct. It certainly doesn't match the syntax in the PostgreSQL documentation: the use of commas to specify multiple tables is only defined for the top level -- not within join expressions.

I'm not even sure what "a LEFT JOIN (b, c) ON condition" means. How are you expecting "b" and "c" to be combined?

[Robert Jordens (jordens)]

At least for mysql this is well defined:
http://dev.mysql.com/doc/refman/5.1/en/join.html

quoting:
The syntax of table_factor is extended in comparison with the SQL Standard. The latter accepts only table_reference, not a list of them inside a pair of parentheses. This is a conservative extension if we consider each comma in a list of table_reference items as equivalent to an inner join. For example:
SELECT * FROM t1 LEFT JOIN (t2, t3, t4) ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)
is equivalent to:
SELECT * FROM t1 LEFT JOIN (t2 CROSS JOIN t3 CROSS JOIN t4) ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)

OTOH sqlite then exhibits its invisible columns bug:
http://www.sqlite.org/cvstrac/tktview?tn=2060

[James Henstridge (jamesh)]

Fair enough. I've marked this bug as wishlist importance, since it is extended syntax, and you can work around it by using standard SQL.

That sqlite bug is a known problem. We try to avoid it by omitting parentheses where the precedence rules allow it.