Storm

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

Bug #520150 reported by Robert Jordens
6
This bug affects 1 person
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.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

Revision history for this message
James Henstridge (jamesh) wrote :

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?

Changed in storm:
status: New → Incomplete
Revision history for this message
Robert Jordens (jordens) wrote :

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)
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
Revision history for this message
James Henstridge (jamesh) wrote :

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.

Changed in storm:
importance: Undecided → Wishlist
status: Incomplete → Confirmed
To post a comment you must log in.
This report contains Public information  
Everyone can see this information.
Subscribing...

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.