Render LIMIT/OFFSET conditions after compile on select dialects

Added new "post compile parameters" feature.  This feature allows a
:func:`.bindparam` construct to have its value rendered into the SQL string
before being passed to the DBAPI driver, but after the compilation step,
using the "literal render" feature of the compiler.  The immediate
rationale for this feature is to support LIMIT/OFFSET schemes that don't
work or perform well as bound parameters handled by the database driver,
while still allowing for SQLAlchemy SQL constructs to be cacheable in their
compiled form.     The immediate targets for the new feature are the "TOP
N" clause used by SQL Server (and Sybase) which does not support a bound
parameter, as well as the "ROWNUM" and optional "FIRST_ROWS()" schemes used
by the Oracle dialect, the former of which has been known to perform better
without bound parameters and the latter of which does not support a bound
parameter.   The feature builds upon the mechanisms first developed to
support "expanding" parameters for IN expressions.   As part of this
feature, the Oracle ``use_binds_for_limits`` feature is turned on
unconditionally and this flag is now deprecated.

- adds limited support for "unique" bound parameters within
a text() construct.

- adds an additional int() check within the literal render
function of the Integer datatype and tests that non-int values
raise ValueError.

Fixes: #4808
Change-Id: Iace97d544d1a7351ee07db970c6bc06a19c712c6

1 files changed, 4 insertions, 3 deletions

diff --git a/lib/sqlalchemy/sql/coercions.py b/lib/sqlalchemy/sql/coercions.py
index 8a9f0b979..a7a856bba 100644
--- a/lib/sqlalchemy/sql/coercions.py
+++ b/lib/sqlalchemy/sql/coercions.py
@@ -294,11 +294,11 @@ class BinaryElementImpl(
 
     def _post_coercion(self, resolved, expr, **kw):
         if (
-            isinstance(resolved, elements.BindParameter)
+            isinstance(resolved, (elements.Grouping, elements.BindParameter))
             and resolved.type._isnull
+            and not expr.type._isnull
         ):
-            resolved = resolved._clone()
-            resolved.type = expr.type
+            resolved = resolved._with_binary_element_type(expr.type)
         return resolved
 
 
@@ -360,6 +360,7 @@ class InElementImpl(RoleImpl, roles.InElementRole):
                 element = element._with_expanding_in_types(
                     [elem.type for elem in expr]
                 )
+
             return element
         else:
             return element