[Sqlalchemy-tickets] Issue #4224: Query API slice, imit, offset seem broken when using python-future datatypes on Python2 (zzzeek/sqlalchemy)

[dheeraj_gupta <iss...@bi...>]

New issue 4224: Query API slice, imit, offset seem broken when using python-future datatypes on Python2
https://bitbucket.org/zzzeek/sqlalchemy/issues/4224/query-api-slice-imit-offset-seem-broken

dheeraj_gupta:

I am trying to port my existing Python2 compatible code to Py2/Py3 form using python-future. Most of SQLAlchemy related features work OK (atleast my tests pass), but trying to pass python-future's datatypes breaks query API's slice, limit and offset functions when using Python2.

Small code that fails when using sqlalchemy-1.1.18, future-0.16.0 on both Python2.7 and Python2.6


```
#!python

from __future__ import print_function

from sqlalchemy.orm import sessionmaker
from sqlalchemy import (Column,
                        engine_from_config,
                        Integer,
                        Text)
from sqlalchemy.ext.declarative import declarative_base

from builtins import *     # This is after installing python-future

Base = declarative_base()


class MyTest(Base):
    __table_args__ = {'mysql_charset': 'utf8', 'mysql_engine': 'InnoDB'}
    __tablename__ = "mytest"

    id_ = Column("id", Integer, primary_key=True)
    description = Column(Text)
    quantity = Column(Integer)


_ENGINE = engine_from_config({"url": "mysql+mysqldb://root:root123@localhost/test"}, prefix="")
_SESSION = sessionmaker(bind=_ENGINE)
session = _SESSION()

Base.metadata.create_all(bind=_ENGINE)

session.add(MyTest(description="foo", quantity=1))
session.add(MyTest(description="bar", quantity=2))
session.add(MyTest(description="baz", quantity=3))
```

Now using the `int` type from python-future to do something like

```
print(session.query(MyTest).slice(0,int(2)).count())
```

gives me the following traceback


```
#!python

Traceback (most recent call last):
  File "/home/test/Desktop/sqlalbug.py", line 40, in <module>
    print(session.query(MyTest).slice(0,int(2)).count())
  File "/home/test/envs/vdbenv/lib/python2.6/site-packages/sqlalchemy/orm/query.py", line 3089, in count
    return self.from_self(col).scalar()
  File "/home/test/envs/vdbenv/lib/python2.6/site-packages/sqlalchemy/orm/query.py", line 2843, in scalar
    ret = self.one()
  File "/home/test/envs/vdbenv/lib/python2.6/site-packages/sqlalchemy/orm/query.py", line 2814, in one
    ret = self.one_or_none()
  File "/home/test/envs/vdbenv/lib/python2.6/site-packages/sqlalchemy/orm/query.py", line 2784, in one_or_none
    ret = list(self)
  File "/home/test/envs/vdbenv/lib/python2.6/site-packages/sqlalchemy/orm/query.py", line 2855, in __iter__
    return self._execute_and_instances(context)
  File "/home/test/envs/vdbenv/lib/python2.6/site-packages/sqlalchemy/orm/query.py", line 2878, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "/home/test/envs/vdbenv/lib/python2.6/site-packages/sqlalchemy/engine/base.py", line 945, in execute
    return meth(self, multiparams, params)
  File "/home/test/envs/vdbenv/lib/python2.6/site-packages/sqlalchemy/sql/elements.py", line 263, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/home/test/envs/vdbenv/lib/python2.6/site-packages/sqlalchemy/engine/base.py", line 1053, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/home/test/envs/vdbenv/lib/python2.6/site-packages/sqlalchemy/engine/base.py", line 1189, in _execute_context
    context)
  File "/home/test/envs/vdbenv/lib/python2.6/site-packages/sqlalchemy/engine/base.py", line 1402, in _handle_dbapi_exception
    exc_info
  File "/home/test/envs/vdbenv/lib/python2.6/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/home/test/envs/vdbenv/lib/python2.6/site-packages/sqlalchemy/engine/base.py", line 1182, in _execute_context
    context)
  File "/home/test/envs/vdbenv/lib/python2.6/site-packages/sqlalchemy/engine/default.py", line 470, in do_execute
    cursor.execute(statement, parameters)
  File "/home/test/envs/vdbenv/lib/python2.6/site-packages/MySQLdb/cursors.py", line 205, in execute
    self.errorhandler(self, exc, value)
  File "/home/test/envs/vdbenv/lib/python2.6/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
sqlalchemy.exc.ProgrammingError: (_mysql_exceptions.ProgrammingError) (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2') AS anon_1' at line 4") [SQL: u'SELECT count(*) AS count_1 \nFROM (SELECT mytest.id AS mytest_id, mytest.description AS mytest_description, mytest.quantity AS mytest_quantity \nFROM mytest \n LIMIT %s) AS anon_1'] [parameters: (2,)]
```

The same traceback is there if I use `limit` or `offset` instead of `slice`

Other query methods (such as filter) seem unaffected


```
#!python

print(session.query(MyTest).filter(MyTest.quantity==1).first().description)
foo

print(session.query(MyTest).filter(MyTest.quantity==int(1)).first().description)
foo
```

The type information for `int`


```
#!python

print(type(int(1)))
<class 'future.types.newint.newint'>
```

To work-around this, I cast the modified types back into original types using the `native` function provided by future.


```
#!python

from future.utils import native
print(session.query(MyTest).slice(0,native(int(2))).count())
```
 works correctly