- 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.

2 files changed, 17 insertions, 9 deletions

diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py
index b7e5d0953..09432e1d4 100644
--- a/test/sql/test_compiler.py
+++ b/test/sql/test_compiler.py
@@ -1271,7 +1271,6 @@ sq.myothertable_othername AS sq_myothertable_othername FROM (" + sqstring + ") A
 
         assert u1.corresponding_column(table2.c.otherid) is u1.c.myid
         
-        # TODO - why is there an extra space before the LIMIT ?
         self.assert_compile(
             union(
                 select([table1.c.myid, table1.c.name]),
@@ -1282,7 +1281,8 @@ sq.myothertable_othername AS sq_myothertable_othername FROM (" + sqstring + ") A
             ),
             "SELECT mytable.myid, mytable.name "
             "FROM mytable UNION SELECT myothertable.otherid, myothertable.othername "
-            "FROM myothertable ORDER BY myid  LIMIT 5 OFFSET 10"
+            "FROM myothertable ORDER BY myid LIMIT :param_1 OFFSET :param_2",
+            {'param_1':5, 'param_2':10}
         )
 
         self.assert_compile(
@@ -1330,7 +1330,9 @@ sq.myothertable_othername AS sq_myothertable_othername FROM (" + sqstring + ") A
         # self_group() is honored
         self.assert_compile(
             union(s.order_by("foo").self_group(), s.order_by("bar").limit(10).self_group()), 
-            "(SELECT foo, bar ORDER BY foo) UNION (SELECT foo, bar ORDER BY bar  LIMIT 10)"
+            "(SELECT foo, bar ORDER BY foo) UNION (SELECT foo, bar ORDER BY bar LIMIT :param_1)",
+            {'param_1':10}
+            
         )
         
     def test_compound_grouping(self):
@@ -1661,7 +1663,8 @@ sq.myothertable_othername AS sq_myothertable_othername FROM (" + sqstring + ") A
             ),
             "SELECT mytable.myid, mytable.name, mytable.description, myothertable.otherid, myothertable.othername FROM mytable "\
             "JOIN myothertable ON mytable.myid = myothertable.otherid WHERE myothertable.otherid IN (SELECT myothertable.otherid "\
-            "FROM myothertable ORDER BY myothertable.othername  LIMIT 10) ORDER BY mytable.myid"
+            "FROM myothertable ORDER BY myothertable.othername LIMIT :param_1) ORDER BY mytable.myid",
+            {'param_1':10}
         )
 
     def test_tuple(self):
diff --git a/test/sql/test_generative.py b/test/sql/test_generative.py
index 5457c7a79..26f9c1ad0 100644
--- a/test/sql/test_generative.py
+++ b/test/sql/test_generative.py
@@ -621,22 +621,27 @@ class ClauseAdapterTest(TestBase, AssertsCompiledSQL):
         s2 = select([s1]).limit(5).offset(10).alias()
 
         self.assert_compile(sql_util.ClauseAdapter(s2).traverse(s1),
-            "SELECT foo.col1, foo.col2, foo.col3 FROM (SELECT table1.col1 AS col1, table1.col2 AS col2, table1.col3 AS col3 FROM table1) AS foo  LIMIT 5 OFFSET 10")
+            "SELECT foo.col1, foo.col2, foo.col3 FROM (SELECT table1.col1 AS col1, table1.col2 AS col2, table1.col3 AS col3 FROM table1) AS foo LIMIT :param_1 OFFSET :param_2",
+            {'param_1':5, 'param_2':10}
+            )
 
         j = s1.outerjoin(t2, s1.c.col1==t2.c.col1)
         self.assert_compile(sql_util.ClauseAdapter(s2).traverse(j).select(),
             "SELECT anon_1.col1, anon_1.col2, anon_1.col3, table2.col1, table2.col2, table2.col3 FROM "\
             "(SELECT foo.col1 AS col1, foo.col2 AS col2, foo.col3 AS col3 FROM "\
-            "(SELECT table1.col1 AS col1, table1.col2 AS col2, table1.col3 AS col3 FROM table1) AS foo  LIMIT 5 OFFSET 10) AS anon_1 "\
-            "LEFT OUTER JOIN table2 ON anon_1.col1 = table2.col1")
+            "(SELECT table1.col1 AS col1, table1.col2 AS col2, table1.col3 AS col3 FROM table1) AS foo LIMIT :param_1 OFFSET :param_2) AS anon_1 "\
+            "LEFT OUTER JOIN table2 ON anon_1.col1 = table2.col1",
+            {'param_1':5, 'param_2':10}
+            )
 
         talias = t1.alias('bar')
         j = s1.outerjoin(talias, s1.c.col1==talias.c.col1)
         self.assert_compile(sql_util.ClauseAdapter(s2).traverse(j).select(),
             "SELECT anon_1.col1, anon_1.col2, anon_1.col3, bar.col1, bar.col2, bar.col3 FROM "\
             "(SELECT foo.col1 AS col1, foo.col2 AS col2, foo.col3 AS col3 FROM "\
-            "(SELECT table1.col1 AS col1, table1.col2 AS col2, table1.col3 AS col3 FROM table1) AS foo  LIMIT 5 OFFSET 10) AS anon_1 "\
-            "LEFT OUTER JOIN table1 AS bar ON anon_1.col1 = bar.col1")
+            "(SELECT table1.col1 AS col1, table1.col2 AS col2, table1.col3 AS col3 FROM table1) AS foo LIMIT :param_1 OFFSET :param_2) AS anon_1 "\
+            "LEFT OUTER JOIN table1 AS bar ON anon_1.col1 = bar.col1",
+            {'param_1':5, 'param_2':10})
     
     def test_functions(self):
         self.assert_compile(sql_util.ClauseAdapter(t1.alias()).traverse(func.count(t1.c.col1)), "count(table1_1.col1)")