Oracle select sql generation

Setting lazy=False on the mappers triggers generation of invalid sql for Oracle. For example following (admittedly convoluted) setup is failing.

from sqlalchemy import *
from sqlalchemy.orm import mapper, relation, sessionmaker


engine = create_engine('oracle://tiger:scott@localhost', echo=False)
metadata = MetaData(engine)


class User(object): pass
class Address(object): pass
class AddressType(object): pass

def setup():
    roles = Table('roles', metadata,
            Column('id', Integer, primary_key=True),
            Column('descr', String(30), nullable=False),
            Column('user_id', None, ForeignKey('users.id')),
            )
    users = Table('users', metadata,
            Column('id', Integer, primary_key=True),
            Column('name', String(30), nullable=False),
            )
    address_types = Table('address_types', metadata,
            Column('id', Integer, primary_key=True),
            Column('name', String(30), nullable=False),
            )
    addresses = Table('addresses', metadata,
            Column('id', Integer, primary_key=True),
            Column('user_id', None, ForeignKey('users.id')),
            Column('address_type_id', Integer, ForeignKey('address_types.id')),
            Column('email_address', String(50), nullable=False))
    uroles = users.join(roles)

    mapper(AddressType, address_types)
    mapper(Address, addresses, properties={
        'address_type': relation(AddressType, lazy=False),
        })
    mapper(User, uroles, properties={
        'addresses': relation(Address, backref="user", lazy=False)
        })


    metadata.drop_all()
    metadata.create_all()

    address_types.insert().execute(
         dict(id=1, name='Work'),
         dict(id=2, name='Home'),
         )
    users.insert().execute(
         dict(id=44, name='Bob'),
         )
    addresses.insert().execute(
         dict(id=1, user_id=44, email_address='hello@world.com', address_type_id=1),
         )
    roles.insert().execute(
         dict(id=1, user_id=44, descr='CEO'),
         dict(id=2, user_id=44, descr='CAO'),
         )


def test():
    Session = sessionmaker(bind=engine, autoflush=True, transactional=True)
    session = Session()

    qry = session.query(User)
    bob = qry.filter_by(id=44).first()
    assert bob.name = 'Bob'
    assert bob.addresses[0](0).address_type.name == 'Work'


if __name__ == '__main__':
    setup()
    test()

SQL is missing parens from around FROM

SELECT   z_address_types.ID AS z_address_types_id,
         z_address_types.NAME AS z_address_types_name,
         z_addresses.ID AS z_addresses_id,
         z_addresses.user_id AS z_addresses_user_id,
         z_addresses.address_type_id AS z_addresses_address_type_id,
         z_addresses.email_address AS z_addresses_email_address,
         z_users.ID AS z_users_id, z_roles.ID AS z_roles_id,
         z_users.NAME AS z_users_name, z_roles.descr AS z_roles_descr,
         z_roles.user_id AS z_roles_user_id
    FROM SELECT z_users_id, z_roles_id
            FROM (SELECT   z_users.ID AS z_users_id, z_roles.ID AS z_roles_id,
                           z_users.ROWID AS z_users_oid,
                           ROW_NUMBER () OVER (ORDER BY z_users.ROWID)
                                                                    AS ora_rn
                      FROM z_users JOIN z_roles ON z_users.ID =
                                                               z_roles.user_id
                     WHERE z_users.ID = :z_users_id
                  ORDER BY z_users.ROWID)
           WHERE ora_rn > 0 AND ora_rn <= 1 tbl_row_count,
         z_users JOIN z_roles ON z_users.ID = z_roles.user_id
         LEFT OUTER JOIN z_addresses z_addresses
         ON z_users.ID = z_addresses.user_id
         LEFT OUTER JOIN z_address_types z_address_types
         ON z_address_types.ID = z_addresses.address_type_id
   WHERE z_users.ID = tbl_row_count.z_users_id
     AND z_roles.ID = tbl_row_count.z_roles_id
ORDER BY tbl_row_count.z_users_id, z_addresses.ID, z_address_types.ID;

It looks it got broken in r3049.

Comments (2)