Storm does not support MySQL's multi-table join syntax
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Storm |
Confirmed
|
Wishlist
|
Unassigned |
Bug Description
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.
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(
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
summary: |
- multiple tables on the right side of a join should be put in parentheses + Storm does not support MySQL's multi-table join syntax |
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?