Proof-of-concept implementation of supporting bindparam for offset and limit on a query.

6 files changed, 33 insertions, 13 deletions

diff --git a/lib/sqlalchemy/dialects/firebird/base.py b/lib/sqlalchemy/dialects/firebird/base.py
index 21db57b68..fdadb61c1 100644
--- a/lib/sqlalchemy/dialects/firebird/base.py
+++ b/lib/sqlalchemy/dialects/firebird/base.py
@@ -76,7 +76,7 @@ from sqlalchemy import exc, types as sqltypes, sql, util
 from sqlalchemy.sql import expression
 from sqlalchemy.engine import base, default, reflection
 from sqlalchemy.sql import compiler
-
+from sqlalchemy.sql.elements import _literal_as_binds
 
 from sqlalchemy.types import (BIGINT, BLOB, DATE, FLOAT, INTEGER, NUMERIC,
                               SMALLINT, TEXT, TIME, TIMESTAMP, Integer)
@@ -301,9 +301,9 @@ class FBCompiler(sql.compiler.SQLCompiler):
 
         result = ""
         if select._limit:
-            result += "FIRST %s " % self.process(sql.literal(select._limit))
+            result += "FIRST %s " % self.process(_literal_as_binds(select._limit))
         if select._offset:
-            result += "SKIP %s " % self.process(sql.literal(select._offset))
+            result += "SKIP %s " % self.process(_literal_as_binds(select._offset))
         if select._distinct:
             result += "DISTINCT "
         return result
diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index f69a6e010..d63e3ed87 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -346,6 +346,7 @@ from ... import sql, schema, exc, util
 from ...engine import default, reflection
 from ...sql import compiler, expression, operators
 from ... import types as sqltypes
+from sqlalchemy.sql.elements import _literal_as_binds
 
 try:
     from uuid import UUID as _python_UUID
@@ -1144,11 +1145,11 @@ class PGCompiler(compiler.SQLCompiler):
     def limit_clause(self, select):
         text = ""
         if select._limit is not None:
-            text += " \n LIMIT " + self.process(sql.literal(select._limit))
+            text += " \n LIMIT " + self.process(_literal_as_binds(select._limit))
         if select._offset is not None:
             if select._limit is None:
                 text += " \n LIMIT ALL"
-            text += " OFFSET " + self.process(sql.literal(select._offset))
+            text += " OFFSET " + self.process(_literal_as_binds(select._offset))
         return text
 
     def format_from_hint_text(self, sqltext, table, hint, iscrud):
diff --git a/lib/sqlalchemy/dialects/sqlite/base.py b/lib/sqlalchemy/dialects/sqlite/base.py
index 90df9c192..1ae565232 100644
--- a/lib/sqlalchemy/dialects/sqlite/base.py
+++ b/lib/sqlalchemy/dialects/sqlite/base.py
@@ -185,6 +185,7 @@ from ... import types as sqltypes, schema as sa_schema
 from ... import util
 from ...engine import default, reflection
 from ...sql import compiler
+from sqlalchemy.sql.elements import _literal_as_binds
 
 from ...types import (BLOB, BOOLEAN, CHAR, DATE, DECIMAL, FLOAT, INTEGER, REAL,
                       NUMERIC, SMALLINT, TEXT, TIMESTAMP, VARCHAR)
@@ -520,11 +521,11 @@ class SQLiteCompiler(compiler.SQLCompiler):
     def limit_clause(self, select):
         text = ""
         if select._limit is not None:
-            text += "\n LIMIT " + self.process(sql.literal(select._limit))
+            text += "\n LIMIT " + self.process(_literal_as_binds(select._limit))
         if select._offset is not None:
             if select._limit is None:
                 text += "\n LIMIT " + self.process(sql.literal(-1))
-            text += " OFFSET " + self.process(sql.literal(select._offset))
+            text += " OFFSET " + self.process(_literal_as_binds(select._offset))
         else:
             text += " OFFSET " + self.process(sql.literal(0))
         return text
diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index 31193ab17..b59e8a941 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -29,6 +29,7 @@ from .. import util, exc
 import decimal
 import itertools
 import operator
+from sqlalchemy.sql.elements import _literal_as_binds
 
 RESERVED_WORDS = set([
     'all', 'analyse', 'analyze', 'and', 'any', 'array',
@@ -1625,11 +1626,11 @@ class SQLCompiler(Compiled):
     def limit_clause(self, select):
         text = ""
         if select._limit is not None:
-            text += "\n LIMIT " + self.process(elements.literal(select._limit))
+            text += "\n LIMIT " + self.process(_literal_as_binds(select._limit))
         if select._offset is not None:
             if select._limit is None:
                 text += "\n LIMIT -1"
-            text += " OFFSET " + self.process(elements.literal(select._offset))
+            text += " OFFSET " + self.process(_literal_as_binds(select._offset))
         return text
 
     def visit_table(self, table, asfrom=False, iscrud=False, ashint=False,
diff --git a/lib/sqlalchemy/sql/selectable.py b/lib/sqlalchemy/sql/selectable.py
index afcf437e9..5995c1f8a 100644
--- a/lib/sqlalchemy/sql/selectable.py
+++ b/lib/sqlalchemy/sql/selectable.py
@@ -1562,9 +1562,9 @@ class GenerativeSelect(SelectBase):
                 self._execution_options.union(
                   {'autocommit': autocommit})
         if limit is not None:
-            self._limit = util.asint(limit)
+            self._limit = limit
         if offset is not None:
-            self._offset = util.asint(offset)
+            self._offset = offset
         self._bind = bind
 
         if order_by is not None:
@@ -1644,14 +1644,14 @@ class GenerativeSelect(SelectBase):
         """return a new selectable with the given LIMIT criterion
         applied."""
 
-        self._limit = util.asint(limit)
+        self._limit = limit
 
     @_generative
     def offset(self, offset):
         """return a new selectable with the given OFFSET criterion
         applied."""
 
-        self._offset = util.asint(offset)
+        self._offset = offset
 
     @_generative
     def order_by(self, *clauses):
@@ -1712,6 +1712,12 @@ class GenerativeSelect(SelectBase):
             self._group_by_clause = ClauseList(*clauses)
 
 
+    def _copy_internals(self, clone=_clone, **kw):
+        if isinstance(self._limit, ClauseElement):
+            self._limit = clone(self._limit)
+        if isinstance(self._offset, ClauseElement):
+            self._offset = clone(self._offset)
+
 class CompoundSelect(GenerativeSelect):
     """Forms the basis of ``UNION``, ``UNION ALL``, and other
         SELECT-based set operations.
@@ -1930,6 +1936,7 @@ class CompoundSelect(GenerativeSelect):
                 "addition of columns to underlying selectables")
 
     def _copy_internals(self, clone=_clone, **kw):
+        super(CompoundSelect, self)._copy_internals(clone, **kw)
         self._reset_exported()
         self.selects = [clone(s, **kw) for s in self.selects]
         if hasattr(self, '_col_map'):
@@ -2380,6 +2387,7 @@ class Select(HasPrefixes, GenerativeSelect):
         return False
 
     def _copy_internals(self, clone=_clone, **kw):
+        super(Select, self)._copy_internals(clone, **kw)
 
         # Select() object has been cloned and probably adapted by the
         # given clone function.  Apply the cloning function to internal
diff --git a/test/orm/test_query.py b/test/orm/test_query.py
index 3b483b7c0..4b5e860e3 100644
--- a/test/orm/test_query.py
+++ b/test/orm/test_query.py
@@ -1275,6 +1275,15 @@ class FilterTest(QueryTest, AssertsCompiledSQL):
         assert [] == sess.query(User).order_by(User.id)[3:3]
         assert [] == sess.query(User).order_by(User.id)[0:0]
 
+    def test_select_with_bindparam_limit(self):
+        """Does a query allow bindparam for the limit?"""
+        sess = create_session()
+        users = []
+
+        q1 = sess.query(self.classes.User).order_by(self.classes.User.id).limit(sa.bindparam('n'))
+        for n in xrange(1,4):
+            users[:] = q1.params(n=n).all()
+            assert len(users) == n
 
     @testing.requires.boolean_col_expressions
     def test_exists(self):