union query fails in some case

I got an Exception with an union query with sqlite 0.9.x and sqlite, while which looks it should work. I could not figure out the root cause but managed to create a reproducable code below:

I tested on:

sqlalchemy 0.8.6, 0.9.0, 0.9.1, 0.9.2, 0.9.3, 0.9.4 with and without CEXT
sqlite on cygwin python2 and 3 and Ubuntu 14.04 python2 and 3
mysql on cygwin and Ubuntu

Errors with sqlalchemy 0.9.x and sqlite only. If I change the table name "a_b" to "ab", then the error won't be triggered.

from sqlalchemy import create_engine, Column, Integer, ForeignKey
from sqlalchemy.orm import sessionmaker
import sqlalchemy.ext.declarative
Base = sqlalchemy.ext.declarative.declarative_base()

class A(Base):
    __tablename__="a"
    id = Column(Integer, primary_key=True)
    b_id = Column(Integer, ForeignKey("a_b.id"))

class AB(Base):
    __tablename__="a_b"
    id = Column(Integer, primary_key=True)

#engine = create_engine("mysql://root:test@127.0.0.1/testdb", echo=True)
engine = create_engine("sqlite://", echo=True)
Base.metadata.create_all(engine)
session = sessionmaker(bind=engine)()
q1 = session.query(A, AB).select_from(A, AB).outerjoin(AB, A.b_id==AB.id)
q2 = session.query(A, AB).select_from(AB, A).outerjoin(A,  A.b_id==AB.id)
query = q1.union(q2)
print query.all()

result:

2014-05-20 23:41:29,145 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2014-05-20 23:41:29,145 INFO sqlalchemy.engine.base.Engine ()
2014-05-20 23:41:29,146 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2014-05-20 23:41:29,146 INFO sqlalchemy.engine.base.Engine ()
2014-05-20 23:41:29,147 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("a_b")
2014-05-20 23:41:29,147 INFO sqlalchemy.engine.base.Engine ()
2014-05-20 23:41:29,147 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("a")
2014-05-20 23:41:29,147 INFO sqlalchemy.engine.base.Engine ()
2014-05-20 23:41:29,148 INFO sqlalchemy.engine.base.Engine
CREATE TABLE a_b (
        id INTEGER NOT NULL,
        PRIMARY KEY (id)
)


2014-05-20 23:41:29,148 INFO sqlalchemy.engine.base.Engine ()
2014-05-20 23:41:29,148 INFO sqlalchemy.engine.base.Engine COMMIT
2014-05-20 23:41:29,148 INFO sqlalchemy.engine.base.Engine
CREATE TABLE a (
        id INTEGER NOT NULL,
        b_id INTEGER,
        PRIMARY KEY (id),
        FOREIGN KEY(b_id) REFERENCES a_b (id)
)


2014-05-20 23:41:29,149 INFO sqlalchemy.engine.base.Engine ()
2014-05-20 23:41:29,149 INFO sqlalchemy.engine.base.Engine COMMIT
2014-05-20 23:41:29,152 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2014-05-20 23:41:29,154 INFO sqlalchemy.engine.base.Engine SELECT anon_1.a_id AS anon_1_a_id, anon_1.a_b_id AS anon_1_a_b_id, anon_1.id_1 AS anon_1_id_1
FROM (SELECT a.id AS a_id, a.b_id AS a_b_id, a_b.id AS id_2
FROM a LEFT OUTER JOIN a_b ON a.b_id = a_b.id UNION SELECT a.id AS a_id, a.b_id AS a_b_id, a_b.id AS id_2
FROM a_b LEFT OUTER JOIN a ON a.b_id = a_b.id) AS anon_1
2014-05-20 23:41:29,154 INFO sqlalchemy.engine.base.Engine ()
Traceback (most recent call last):
  File "test_sqlite_union.py", line 22, in <module>
    print query.all()
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/query.py", line 2292, in all
    return list(self)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/query.py", line 2404, in __iter__
    return self._execute_and_instances(context)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/query.py", line 2419, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 720, in execute
    return meth(self, multiparams, params)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/elements.py", line 317, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 817, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 947, in _execute_context
    context)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1108, in _handle_dbapi_exception
    exc_info
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/util/compat.py", line 185, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 940, in _execute_context
    context)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 435, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (OperationalError) no such column: anon_1.id_1 u'SELECT anon_1.a_id AS anon_1_a_id, anon_1.a_b_id AS anon_1_a_b_id, anon_1.id_1 AS anon_1_id_1 \nFROM (SELECT a.id AS a_id, a.b_id AS a_b_id, a_b.id AS id_2 \nFROM a LEFT OUTER JOIN a_b ON a.b_id = a_b.id UNION SELECT a.id AS a_id, a.b_id AS a_b_id, a_b.id AS id_2 \nFROM a_b LEFT OUTER JOIN a ON a.b_id = a_b.id) AS anon_1' ()

Comments (3)