Ordering by composite column gives sqlite3 OperationalError

Right now query.order_by(composite) gives a sqlite3 operational error, because the rendered SQL is ORDER BY (composite_val1, composite_val2, composite_val3) instead of ORDER BY composite_val1, composite_val2, composite_val3. (The parenthesis is causing an error)

For example, consider the code below modified from the documentation.

from sqlalchemy.engine import create_engine
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.ext.declarative.api import declarative_base
from sqlalchemy.orm import relationship, composite
from sqlalchemy.orm.session import sessionmaker
from sqlalchemy.schema import Column, ForeignKey
from sqlalchemy.types import Integer, String
import itertools

Base = declarative_base()

class Point(object):
    def __init__(self, x, y):
        self.x = x
        self.y = y

    def __composite_values__(self):
        return self.x, self.y

    def __repr__(self):
        return "Point(x=%r, y=%r)" % (self.x, self.y)

    def __eq__(self, other):
        return isinstance(other, Point) and \
            other.x == self.x and \
            other.y == self.y

    def __ne__(self, other):
        return not self.__eq__(other)

class Vertex(Base):
    __tablename__ = 'vertice'

    id = Column(Integer, primary_key=True)
    x1 = Column(Integer)
    y1 = Column(Integer)
    x2 = Column(Integer)
    y2 = Column(Integer)

    start = composite(Point, x1, y1)
    end = composite(Point, x2, y2)

if __name__ == '__main__':
    engine = create_engine('sqlite:///:memory:')
    Session = sessionmaker(engine)
    session = Session()
    Base.metadata.create_all(engine)

    pts = [2), (3, 4)),
           ((2, 3), (1, 5)),
           ((0, 5), (6, 3))](((1,)

    session.add_all(itertools.starmap(
                        lambda a, b: Vertex(start=Point(*a), end=Point(*b)), 
                        pts))

We run the following in the console:

>>> q = session.query(Vertex).order_by(Vertex.start)
>>> q
Out[1](1): <sqlalchemy.orm.query.Query at 0x3bc1f30>
>>> str(q)
Out[1](1): 'SELECT vertice.id AS vertice_id, vertice.x1 AS vertice_x1, vertice.y1 AS vertice_y1, vertice.x2 AS vertice_x2, vertice.y2 AS vertice_y2 \nFROM vertice ORDER BY (vertice.x1, vertice.y1)'
>>> q.all()
Traceback (most recent call last):
  File "C:\Anaconda\Lib\site-packages\IPython\core\interactiveshell.py", line 2731, in run_code
    exec code_obj in self.user_global_ns, self.user_ns
  File "<ipython-input-1-511354a8265d>", line 1, in <module>
    q.all()
  File "C:\Python27\lib\site-packages\sqlalchemy-0.8.0-py2.7.egg\sqlalchemy\orm\query.py", line 2140, in all
    return list(self)
  File "C:\Python27\lib\site-packages\sqlalchemy-0.8.0-py2.7.egg\sqlalchemy\orm\query.py", line 2252, in __iter__
    return self._execute_and_instances(context)
  File "C:\Python27\lib\site-packages\sqlalchemy-0.8.0-py2.7.egg\sqlalchemy\orm\query.py", line 2267, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "C:\Python27\lib\site-packages\sqlalchemy-0.8.0-py2.7.egg\sqlalchemy\engine\base.py", line 664, in execute
    params)
  File "C:\Python27\lib\site-packages\sqlalchemy-0.8.0-py2.7.egg\sqlalchemy\engine\base.py", line 764, in _execute_clauseelement
    compiled_sql, distilled_params
  File "C:\Python27\lib\site-packages\sqlalchemy-0.8.0-py2.7.egg\sqlalchemy\engine\base.py", line 878, in _execute_context
    context)
  File "C:\Python27\lib\site-packages\sqlalchemy-0.8.0-py2.7.egg\sqlalchemy\engine\base.py", line 871, in _execute_context
    context)
  File "C:\Python27\lib\site-packages\sqlalchemy-0.8.0-py2.7.egg\sqlalchemy\engine\default.py", line 320, in do_execute
    cursor.execute(statement, parameters)
OperationalError: (OperationalError) near ",": syntax error u'SELECT vertice.id AS vertice_id, vertice.x1 AS vertice_x1, vertice.y1 AS vertice_y1, vertice.x2 AS vertice_x2, vertice.y2 AS vertice_y2 \nFROM vertice ORDER BY (vertice.x1, vertice.y1)' ()

Whereas, if we directly execute the correct SQL, without the parenthesis,

>>> session.execute(u'SELECT vertice.id AS vertice_id, vertice.x1 AS vertice_x1, vertice.y1 AS vertice_y1, vertice.x2 AS vertice_x2, vertice.y2 AS vertice_y2 \nFROM vertice ORDER BY vertice.x1, vertice.y1' )
Out[1](1): <sqlalchemy.engine.result.ResultProxy at 0x3bc1d70>
>>> _.fetchall()
Out[1](1): [0, 5, 6, 3), (1, 1, 2, 3, 4), (2, 2, 3, 1, 5)]((3,)

We get the right result back, albeit not wrapped in Vertex objects

So it seems like a fairly simple bug.

Comments (5)