[Sqlalchemy-tickets] [sqlalchemy] #2754: Ordering by composite column gives sqlite3 OperationalError

[sqlalchemy <mi...@zz...>]

#2754: Ordering by composite column gives sqlite3 OperationalError
-------------------------------------+-------------------------------------
 Reporter:  sorcererofdm             |           Owner:  zzzeek
     Type:  defect                   |          Status:  new
 Priority:  medium                   |       Milestone:
Component:  declarative              |        Severity:  minor - half an
 Keywords:  sqlite, declarative,     |  hour
  composite columns, order_by        |  Progress State:  awaiting triage
  clause                             |
-------------------------------------+-------------------------------------
 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 = [((1, 2), (3, 4)),
            ((2, 3), (1, 5)),
            ((0, 5), (6, 3))]

     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]: <sqlalchemy.orm.query.Query at 0x3bc1f30>
 >>> str(q)
 Out[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]: <sqlalchemy.engine.result.ResultProxy at 0x3bc1d70>
 >>> _.fetchall()
 Out[1]: [(3, 0, 5, 6, 3), (1, 1, 2, 3, 4), (2, 2, 3, 1, 5)]
 }}}


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

 So it seems like a fairly simple bug.

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2754>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python