Query generated by ORM is not compatible with MySQL version >=5

[Former user]

MySQL (as of v5) does not treat comma and inner join as the same: comma has lower precedence (cf http://dev.mysql.com/doc/refman/5.1/en/join.html). This results in queries being generated with a FROM clause like:

SELECT a.col1, a.col2
FROM 
a, 
b 
INNER JOIN c ON c.col1 = a.col1

which yields:

OperationalError: (OperationalError) (1054, "Unknown column 'a.col1' in 'on clause'")

Changing SQLAlchemy-0.4.6-py2.5.egg/sqlalchemy/sql/compiler.py as below (add line 524)

    521         if froms:
    522             text += " \nFROM "
    523             #text += string.join(from_strings, ', ')
    524             text += string.join(from_strings, ' INNER JOIN ')
    525         else:

seems to solve the problem.

[Mike Bayer]

the SQL construct you are using is invalid. construct a three way join like this:

select([...](...)).select_from(
   tablea.join(tableb, tablea.c.foo==tableb.c.bar).
   join(tablec, tableb.c.foo==tablec.c.bar)
)

as opposed to:

select([...](...)).select_from(
    tablea, tableb.join(tablec, tableb.c.foo==tablec.c.bar)
)

also I hope its obvious why the "," SQLAlchemy places in a phrase like "FROM a, b, c" can't just be changed to read "INNER JOIN".

[Former user]

The SQL is valid for mysql and works fine under mysql4. Setting whether or not it's ANSI standard aside, I'm not constructing the invalid SQL, SqlAlchemy's ORM is. Given the complexity of demonstrating an ORM setup, I gave the SQL it generated as a simplification so as not to confuse the issue.

We have roughly the following setup:

Table A:
  id int primary key,
  someAData varchar,
  someAFlag tinyint(1)

Table B:
  id int primary key,
  -- target_* are for an implicit FK to several possible tables, including A
  -- These relationships are not modeled explicitly at the mysql or ORM levels.
  target_id int not null,
  target_table varchar not null,
  someBData varchar,
  someBFlag tinyint(1),
  c_id int not null -- FK to c

Table C:
  id int primary key,
  someCData varchar,
  someCFlag tinyint(1)

and this ORM query:

interesting = session.query( ClassA ).filter( ClassA.someAFlag = True ).filter( ClassB.target_table = 'A' ).filter( classB.target_id = ClassA.id ).join( ClassB.c ).filter( ClassC.someCFlag = True ).all()

This produces

SELECT a.col1, a.col2
FROM 
a, 
b 
INNER JOIN c ON b.c_id = c.id
WHERE ...

Perhaps I need to write the ORM query differently (using 0.4.6)? Adding join doesn't work, because it requires a property (which we don't add to this model because it's only sometimes there and "there" is a large number of possible tables). Adding add_entity still produces the same error as above.

[Mike Bayer]

Replying to guest:

Perhaps I need to write the ORM query differently (using 0.4.6)? Adding join doesn't work, because it requires a property (which we don't add to this model because it's only sometimes there and "there" is a large number of possible tables). Adding add_entity still produces the same error as above.

yes the advice is the same - Query does not generate JOIN for you, you have to do it manually. Your query should read:

interesting = session.query( ClassA ).filter( ClassA.someAFlag = True ).\
   join(ClassA.brelation, ClassB.crelation ).\
   filter( ClassB.target_table = 'A' ).\
   filter( ClassC.someCFlag = True ).all()

note that in 0.4 the join() call only accepts mapped relation as ON conditions. in 0.5, you can also specify these using specific column attributes - see the docs for 0.5 if you decide to upgrade (strongly recommended). If you need to specify joins manually in 0.4, you need to use select_from():

interesting = session.query( ClassA ).\
               select_from(atable.join(btable, atable.c.id==btable.c.target_id).join(ctable, btable.c.someid=ctable.c.id)).\
              filter( ClassA.someAFlag = True ).\
              filter( ClassB.target_table = 'A' ).\
              .filter( ClassC.someCFlag = True ).all()

since no bug has been demonstrated here may I close this ticket ?