Re: [Sqlalchemy-tickets] [sqlalchemy] #2790: sqlite join rewriting fails on columns that assign alternate .key (was: eagerload fails on SQLite on a N:N relationship)

[sqlalchemy <mi...@zz...>]

#2790: sqlite join rewriting fails on columns that assign alternate .key
------------------------------+-------------------------------
      Reporter:  rgg          |      Owner:  zzzeek
          Type:  defect       |     Status:  new
      Priority:  highest      |  Milestone:  0.9.0
     Component:  orm          |   Severity:  major - 1-3 hours
    Resolution:               |   Keywords:
Progress State:  in progress  |
------------------------------+-------------------------------
Changes (by zzzeek):

 * priority:  medium => highest
 * status_field:  awaiting triage => in progress
 * severity:  no triage selected yet => major - 1-3 hours


Old description:

> In SQLite, when we use a secondary table to establish a relationship
> between two objects, the eagerload of this relationship fails.
>
> I've tested it in PostgreSQL and the same code is OK.
>
> Attached to the ticket is a small example of the failure. I've tested it
> with the latest GitHub trunk release.
>
> Kind regards,
>

> Raul Garcia

New description:

 Here's a simple test, however something is also going on with an aliased
 version which only appears during the mechanics used by joinedload, need
 to isolate that:

 {{{
 #!python
 from sqlalchemy import *
 from sqlalchemy.orm import *

 metadata = MetaData()

 a = Table('a', metadata,
         Column('id', Integer, primary_key=True, key='aid'),
     )

 b = Table('b', metadata,
         Column('id', Integer, primary_key=True, key='bid'),
     )
 c = Table('c', metadata,
         Column('aid', Integer, ForeignKey('a.aid'), key='aid'),
         Column('bid', Integer, ForeignKey('b.bid'), key='bid'),
     )

 from sqlalchemy.dialects import sqlite

 s1 = select([a, b], use_labels=True).select_from(a.join(c.join(b)))

 print s1.compile(dialect=sqlite.dialect())

 }}}

--

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2790#comment:1>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python