Re: [Sqlalchemy-tickets] [sqlalchemy] #2790: sqlite join rewriting fails on columns that assign alternate .key

[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  |
------------------------------+-------------------------------
Description changed by zzzeek:

Old 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())
>
> }}}

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')),
         Column('bid', Integer, ForeignKey('b.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())


 ca = c.alias()
 ba = b.alias()

 j = ca.join(ba)

 # with aliases, need to list out the column w the key
 s1 = select([a, ba.c.bid], use_labels=True).select_from(a.join(j))

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


 }}}

--

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