Oracle + "long column names" + "limit query" = NoSuchColumnError

Following setup

from sqlalchemy import *
from sqlalchemy.orm import mapper, sessionmaker
meta = MetaData()


gmasl_security = Table('gmasl_security',
    meta,
    Column('security_role_id', String(length=20), primary_key=True, nullable=False),
    Column('security_desc', String(length=500)),
    schema='gmas')


class Group(object):
    pass

mapper(Group, gmasl_security,
    properties={
        'id': gmasl_security.c.security_role_id,
        'name': gmasl_security.c.security_desc,
        },
    )


def test_oracle_limit_query(bind):
    meta.bind = bind
    meta.create_all()
    sess = sessionmaker()()
    qry = sess.query(Group)
    res = qry.all()
    res = qry.first()


if __name__ == '__main__':
    connstr  = 'oracle://scott:tiger@127.0.0.1:1521'
    engine = create_engine(connstr)
    engine.echo = True
    test_oracle_limit_query(engine)

will fail with NoSuchColumnError on the second query. See attached traceback for details.

After spelunking for couple hours in the source code I have concluded that the culprit is most likely visit_select function (source:///sqlalchemy/trunk/lib/sqlalchemy/databases/oracle.py line 655) that does not update result_map in proper places.

Unfortunately I did not manage to come up with a patch

Comments (3)