- The limit/offset keywords to select() as well

as the value passed to select.limit()/offset()
will be coerced to integer.  [ticket:2116]
(also in 0.6.7)
- Oracle dialect adds use_binds_for_limits=False
create_engine() flag, will render the LIMIT/OFFSET
values inline instead of as binds, reported to
modify the execution plan used by Oracle.
[ticket:2116] (Also in 0.6.7)

2 files changed, 62 insertions, 1 deletions

diff --git a/test/dialect/test_oracle.py b/test/dialect/test_oracle.py
index 26e46349e..f3cdcc3a9 100644
--- a/test/dialect/test_oracle.py
+++ b/test/dialect/test_oracle.py
@@ -132,6 +132,49 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
                             ':ROWNUM_1) WHERE ora_rn > :ora_rn_1 FOR '
                             'UPDATE')
 
+    def test_use_binds_for_limits_disabled(self):
+        t = table('sometable', column('col1'), column('col2'))
+        dialect = oracle.OracleDialect(use_binds_for_limits = False)
+
+        self.assert_compile(select([t]).limit(10),
+                "SELECT col1, col2 FROM (SELECT sometable.col1 AS col1, "
+                "sometable.col2 AS col2 FROM sometable) WHERE ROWNUM <= 10",
+                dialect=dialect)
+
+        self.assert_compile(select([t]).offset(10),
+                "SELECT col1, col2 FROM (SELECT col1, col2, ROWNUM AS ora_rn "
+                "FROM (SELECT sometable.col1 AS col1, sometable.col2 AS col2 "
+                "FROM sometable)) WHERE ora_rn > 10",
+                dialect=dialect)
+
+        self.assert_compile(select([t]).limit(10).offset(10),
+                "SELECT col1, col2 FROM (SELECT col1, col2, ROWNUM AS ora_rn "
+                "FROM (SELECT sometable.col1 AS col1, sometable.col2 AS col2 "
+                "FROM sometable) WHERE ROWNUM <= 20) WHERE ora_rn > 10",
+                dialect=dialect)
+
+    def test_use_binds_for_limits_enabled(self):
+        t = table('sometable', column('col1'), column('col2'))
+        dialect = oracle.OracleDialect(use_binds_for_limits = True)
+
+        self.assert_compile(select([t]).limit(10),
+                "SELECT col1, col2 FROM (SELECT sometable.col1 AS col1, "
+                "sometable.col2 AS col2 FROM sometable) WHERE ROWNUM "
+                "<= :ROWNUM_1",
+                dialect=dialect)
+
+        self.assert_compile(select([t]).offset(10),
+                "SELECT col1, col2 FROM (SELECT col1, col2, ROWNUM AS ora_rn "
+                "FROM (SELECT sometable.col1 AS col1, sometable.col2 AS col2 "
+                "FROM sometable)) WHERE ora_rn > :ora_rn_1",
+                dialect=dialect)
+
+        self.assert_compile(select([t]).limit(10).offset(10),
+                "SELECT col1, col2 FROM (SELECT col1, col2, ROWNUM AS ora_rn "
+                "FROM (SELECT sometable.col1 AS col1, sometable.col2 AS col2 "
+                "FROM sometable) WHERE ROWNUM <= :ROWNUM_1) WHERE ora_rn > "
+                ":ora_rn_1",
+                dialect=dialect)
 
     def test_long_labels(self):
         dialect = default.DefaultDialect()
diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py
index bfb282050..ce3e9003b 100644
--- a/test/sql/test_compiler.py
+++ b/test/sql/test_compiler.py
@@ -1,5 +1,5 @@
 from test.lib.testing import eq_, assert_raises, assert_raises_message
-import datetime, re, operator
+import datetime, re, operator, decimal
 from sqlalchemy import *
 from sqlalchemy import exc, sql, util
 from sqlalchemy.sql import table, column, label, compiler
@@ -106,6 +106,24 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL):
         assert_raises(exc.ArgumentError, select, table1)
         assert_raises(exc.ArgumentError, select, table1.c.myid)
 
+    def test_int_limit_offset_coercion(self):
+        for given, exp in [
+            ("5", 5),
+            (5, 5),
+            (5.2, 5),
+            (decimal.Decimal("5"), 5),
+            (None, None),
+        ]:
+            eq_(select().limit(given)._limit, exp)
+            eq_(select().offset(given)._offset, exp)
+            eq_(select(limit=given)._limit, exp)
+            eq_(select(offset=given)._offset, exp)
+
+        assert_raises(ValueError, select().limit, "foo")
+        assert_raises(ValueError, select().offset, "foo")
+        assert_raises(ValueError, select, offset="foo")
+        assert_raises(ValueError, select, limit="foo")
+
     def test_from_subquery(self):
         """tests placing select statements in the column clause of another select, for the
         purposes of selecting from the exported columns of that select."""