- move LIMIT/OFFSET rendering to be as bind parameters, for all backends

which support it.  This includes SQLite, MySQL, Postgresql, Firebird,
Oracle (already used binds with ROW NUMBER OVER), MSSQL (when ROW NUMBER
is used, not TOP).   Not included are Informix, Sybase, MaxDB, Access
[ticket:805]
- LIMIT/OFFSET parameters need to stay as literals within SQL
constructs.  This because they may not be renderable as binds on
some backends.

8 files changed, 25 insertions, 17 deletions

diff --git a/lib/sqlalchemy/dialects/firebird/base.py b/lib/sqlalchemy/dialects/firebird/base.py
index da8bef8c0..04439afb9 100644
--- a/lib/sqlalchemy/dialects/firebird/base.py
+++ b/lib/sqlalchemy/dialects/firebird/base.py
@@ -263,9 +263,9 @@ class FBCompiler(sql.compiler.SQLCompiler):
 
         result = ""
         if select._limit:
-            result += "FIRST %d "  % select._limit
+            result += "FIRST %s "  % self.process(sql.literal(select._limit))
         if select._offset:
-            result +="SKIP %d "  %  select._offset
+            result +="SKIP %s "  %  self.process(sql.literal(select._offset))
         if select._distinct:
             result += "DISTINCT "
         return result
diff --git a/lib/sqlalchemy/dialects/maxdb/base.py b/lib/sqlalchemy/dialects/maxdb/base.py
index 487edc2ca..9a1e10f51 100644
--- a/lib/sqlalchemy/dialects/maxdb/base.py
+++ b/lib/sqlalchemy/dialects/maxdb/base.py
@@ -603,6 +603,7 @@ class MaxDBCompiler(compiler.SQLCompiler):
     def limit_clause(self, select):
         # The docs say offsets are supported with LIMIT.  But they're not.
         # TODO: maybe emulate by adding a ROWNO/ROWNUM predicate?
+        # TODO: does MaxDB support bind params for LIMIT / TOP ?
         if self.is_subquery():
             # sub queries need TOP
             return ''
diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py
index 88ca36dbd..089d2f71d 100644
--- a/lib/sqlalchemy/dialects/mssql/base.py
+++ b/lib/sqlalchemy/dialects/mssql/base.py
@@ -706,9 +706,12 @@ class MSSQLCompiler(compiler.SQLCompiler):
         if select._distinct or select._limit:
             s = select._distinct and "DISTINCT " or ""
             
+            # ODBC drivers and possibly others
+            # don't support bind params in the SELECT clause on SQL Server.
+            # so have to use literal here.
             if select._limit:
                 if not select._offset:
-                    s += "TOP %s " % (select._limit,)
+                    s += "TOP %d " % select._limit
             return s
         return compiler.SQLCompiler.get_select_precolumns(self, select)
 
@@ -738,10 +741,10 @@ class MSSQLCompiler(compiler.SQLCompiler):
 
             limitselect = sql.select([c for c in select.c if
                                         c.key!='mssql_rn'])
-            limitselect.append_whereclause("mssql_rn>%d" % _offset)
+            limitselect.append_whereclause("mssql_rn>%s" % self.process(sql.literal(_offset)))
             if _limit is not None:
-                limitselect.append_whereclause("mssql_rn<=%d" % 
-                                            (_limit + _offset))
+                limitselect.append_whereclause("mssql_rn<=%s" % 
+                                            (self.process(sql.literal(_limit + _offset))))
             return self.process(limitselect, iswrapper=True, **kwargs)
         else:
             return compiler.SQLCompiler.visit_select(self, select, **kwargs)
diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py
index a2d3748f3..d526d74e8 100644
--- a/lib/sqlalchemy/dialects/mysql/base.py
+++ b/lib/sqlalchemy/dialects/mysql/base.py
@@ -1226,10 +1226,12 @@ class MySQLCompiler(compiler.SQLCompiler):
             # artificial limit if one wasn't provided
             if limit is None:
                 limit = 18446744073709551615
-            return ' \n LIMIT %s, %s' % (offset, limit)
+            return ' \n LIMIT %s, %s' % (
+                                self.process(sql.literal(offset)), 
+                                self.process(sql.literal(limit)))
         else:
             # No offset provided, so just use the limit
-            return ' \n LIMIT %s' % (limit,)
+            return ' \n LIMIT %s' % (self.process(sql.literal(limit)),)
 
     def visit_update(self, update_stmt):
         self.stack.append({'from': set([update_stmt.table])})
diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index 89769b8c0..768fbcb4d 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -374,11 +374,11 @@ class PGCompiler(compiler.SQLCompiler):
     def limit_clause(self, select):
         text = ""
         if select._limit is not None:
-            text +=  " \n LIMIT " + str(select._limit)
+            text +=  " \n LIMIT " + self.process(sql.literal(select._limit))
         if select._offset is not None:
             if select._limit is None:
                 text += " \n LIMIT ALL"
-            text += " OFFSET " + str(select._offset)
+            text += " OFFSET " + self.process(sql.literal(select._offset))
         return text
 
     def get_select_precolumns(self, select):
diff --git a/lib/sqlalchemy/dialects/sqlite/base.py b/lib/sqlalchemy/dialects/sqlite/base.py
index b84b18e68..7bd6d51f3 100644
--- a/lib/sqlalchemy/dialects/sqlite/base.py
+++ b/lib/sqlalchemy/dialects/sqlite/base.py
@@ -222,13 +222,13 @@ class SQLiteCompiler(compiler.SQLCompiler):
     def limit_clause(self, select):
         text = ""
         if select._limit is not None:
-            text +=  " \n LIMIT " + str(select._limit)
+            text +=  "\n LIMIT " + self.process(sql.literal(select._limit))
         if select._offset is not None:
             if select._limit is None:
-                text += " \n LIMIT -1"
-            text += " OFFSET " + str(select._offset)
+                text += "\n LIMIT " + self.process(sql.literal(-1))
+            text += " OFFSET " + self.process(sql.literal(select._offset))
         else:
-            text += " OFFSET 0"
+            text += " OFFSET " + self.process(sql.literal(0))
         return text
 
     def for_update_clause(self, select):
diff --git a/lib/sqlalchemy/dialects/sybase/base.py b/lib/sqlalchemy/dialects/sybase/base.py
index b0b1bbff4..e3d0d582d 100644
--- a/lib/sqlalchemy/dialects/sybase/base.py
+++ b/lib/sqlalchemy/dialects/sybase/base.py
@@ -271,6 +271,8 @@ class SybaseSQLCompiler(compiler.SQLCompiler):
 
     def get_select_precolumns(self, select):
         s = select._distinct and "DISTINCT " or ""
+        # TODO: don't think Sybase supports
+        # bind params for FIRST / TOP
         if select._limit:
             #if select._limit == 1:
                 #s += "FIRST "
diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index fcff5e355..584e43a88 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -808,11 +808,11 @@ class SQLCompiler(engine.Compiled):
     def limit_clause(self, select):
         text = ""
         if select._limit is not None:
-            text +=  " \n LIMIT " + str(select._limit)
+            text +=  "\n LIMIT " + self.process(sql.literal(select._limit))
         if select._offset is not None:
             if select._limit is None:
-                text += " \n LIMIT -1"
-            text += " OFFSET " + str(select._offset)
+                text += "\n LIMIT -1"
+            text += " OFFSET " + self.process(sql.literal(select._offset))
         return text
 
     def visit_table(self, table, asfrom=False, ashint=False, fromhints=None, **kwargs):