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

12 files changed, 558 insertions, 167 deletions

diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py
index 87b675c5f..7ab680ca4 100644
--- a/lib/sqlalchemy/dialects/mssql/base.py
+++ b/lib/sqlalchemy/dialects/mssql/base.py
@@ -246,17 +246,29 @@ LIMIT/OFFSET Support
 --------------------
 
 MSSQL has no support for the LIMIT or OFFSET keywords. LIMIT is
-supported directly through the ``TOP`` Transact SQL keyword::
+supported directly through the ``TOP`` Transact SQL keyword.   A statement
+such as::
 
-    select.limit
+    select([some_table]).limit(5)
 
-will yield::
+will render similarly to::
 
-    SELECT TOP n
+    SELECT TOP 5 col1, col2.. FROM table
 
-If using SQL Server 2005 or above, LIMIT with OFFSET
-support is available through the ``ROW_NUMBER OVER`` construct.
-For versions below 2005, LIMIT with OFFSET usage will fail.
+LIMIT with OFFSET support is implemented using the using the ``ROW_NUMBER()``
+window function.   A statement such as::
+
+    select([some_table]).order_by(some_table.c.col3).limit(5).offset(10)
+
+will render similarly to::
+
+    SELECT anon_1.col1, anon_1.col2 FROM (SELECT col1, col2,
+    ROW_NUMBER() OVER (ORDER BY col3) AS
+    mssql_rn FROM table WHERE t.x = :x_1) AS
+    anon_1 WHERE mssql_rn > :param_1 AND mssql_rn <= :param_2 + :param_1
+
+Note that when using LIMIT and OFFSET together, the statement must have
+an ORDER BY as well.
 
 .. _mssql_isolation_level:
 
@@ -1603,8 +1615,8 @@ class MSSQLCompiler(compiler.SQLCompiler):
             # ODBC drivers and possibly others
             # don't support bind params in the SELECT clause on SQL Server.
             # so have to use literal here.
-            s += "TOP %d " % select._limit
-
+            kw["literal_execute"] = True
+            s += "TOP %s " % self.process(select._limit_clause, **kw)
         if s:
             return s
         else:
diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py
index 868c64ed3..768df7426 100644
--- a/lib/sqlalchemy/dialects/oracle/base.py
+++ b/lib/sqlalchemy/dialects/oracle/base.py
@@ -25,7 +25,7 @@ which affect the behavior of the dialect regardless of driver in use.
 * ``optimize_limits`` - defaults to ``False``. see the section on
   LIMIT/OFFSET.
 
-* ``use_binds_for_limits`` - defaults to ``True``.  see the section on
+* ``use_binds_for_limits`` - deprecated.  see the section on
   LIMIT/OFFSET.
 
 Auto Increment Behavior
@@ -71,30 +71,33 @@ lowercase names should be used on the SQLAlchemy side.
 LIMIT/OFFSET Support
 --------------------
 
-Oracle has no support for the LIMIT or OFFSET keywords.  SQLAlchemy uses
-a wrapped subquery approach in conjunction with ROWNUM.  The exact methodology
-is taken from
-http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html .
+Oracle has no direct support for LIMIT and OFFSET until version 12c.
+To achieve this behavior across all widely used versions of Oracle starting
+with the 8 series, SQLAlchemy currently makes use of ROWNUM to achieve
+LIMIT/OFFSET; the exact methodology is taken from
+https://blogs.oracle.com/oraclemagazine/on-rownum-and-limiting-results .
 
-There are two options which affect its behavior:
+There is currently a single option to affect its behavior:
 
-* the "FIRST ROWS()" optimization keyword is not used by default.  To enable
+* the "FIRST_ROWS()" optimization keyword is not used by default.  To enable
   the usage of this optimization directive, specify ``optimize_limits=True``
   to :func:`.create_engine`.
-* the values passed for the limit/offset are sent as bound parameters.   Some
-  users have observed that Oracle produces a poor query plan when the values
-  are sent as binds and not rendered literally.   To render the limit/offset
-  values literally within the SQL statement, specify
-  ``use_binds_for_limits=False`` to :func:`.create_engine`.
-
-Some users have reported better performance when the entirely different
-approach of a window query is used, i.e. ROW_NUMBER() OVER (ORDER BY), to
-provide LIMIT/OFFSET (note that the majority of users don't observe this).
-To suit this case the method used for LIMIT/OFFSET can be replaced entirely.
-See the recipe at
-http://www.sqlalchemy.org/trac/wiki/UsageRecipes/WindowFunctionsByDefault
-which installs a select compiler that overrides the generation of limit/offset
-with a window function.
+
+.. versionchanged:: 1.4
+    The Oracle dialect renders limit/offset integer values using a "post
+    compile" scheme which renders the integer directly before passing the
+    statement to the cursor for execution.   The ``use_binds_for_limits`` flag
+    no longer has an effect.
+
+    .. seealso::
+
+        :ref:`change_4808`.
+
+Support for changing the row number strategy, which would include one that
+makes use of the ``row_number()`` window function as well as one that makes
+use of the Oracle 12c  "FETCH FIRST N ROW / OFFSET N ROWS" keywords may be
+added in a future release.
+
 
 .. _oracle_returning:
 
@@ -364,6 +367,7 @@ from ...types import CHAR
 from ...types import CLOB
 from ...types import FLOAT
 from ...types import INTEGER
+from ...types import Integer
 from ...types import NCHAR
 from ...types import NVARCHAR
 from ...types import TIMESTAMP
@@ -855,17 +859,9 @@ class OracleCompiler(compiler.SQLCompiler):
             limit_clause = select._limit_clause
             offset_clause = select._offset_clause
             if limit_clause is not None or offset_clause is not None:
-                # See http://www.oracle.com/technology/oramag/oracle/06-sep/\
-                # o56asktom.html
-                #
-                # Generalized form of an Oracle pagination query:
-                #   select ... from (
-                #     select /*+ FIRST_ROWS(N) */ ...., rownum as ora_rn from
-                #       (  select distinct ... where ... order by ...
-                #     ) where ROWNUM <= :limit+:offset
-                #   ) where ora_rn > :offset
-                # Outer select and "ROWNUM as ora_rn" can be dropped if
-                # limit=0
+                # currently using form at:
+                # https://blogs.oracle.com/oraclemagazine/\
+                # on-rownum-and-limiting-results
 
                 kwargs["select_wraps_for"] = orig_select = select
                 select = select._generate()
@@ -896,8 +892,17 @@ class OracleCompiler(compiler.SQLCompiler):
                     and self.dialect.optimize_limits
                     and select._simple_int_limit
                 ):
+                    param = sql.bindparam(
+                        "_ora_frow",
+                        select._limit,
+                        type_=Integer,
+                        literal_execute=True,
+                        unique=True,
+                    )
                     limitselect = limitselect.prefix_with(
-                        "/*+ FIRST_ROWS(%d) */" % select._limit
+                        expression.text(
+                            "/*+ FIRST_ROWS(:_ora_frow) */"
+                        ).bindparams(param)
                     )
 
                 limitselect._oracle_visit = True
@@ -913,14 +918,20 @@ class OracleCompiler(compiler.SQLCompiler):
 
                 # If needed, add the limiting clause
                 if limit_clause is not None:
-                    if not self.dialect.use_binds_for_limits:
-                        # use simple int limits, will raise an exception
-                        # if the limit isn't specified this way
+                    if select._simple_int_limit and (
+                        offset_clause is None or select._simple_int_offset
+                    ):
                         max_row = select._limit
 
                         if offset_clause is not None:
                             max_row += select._offset
-                        max_row = sql.literal_column("%d" % max_row)
+                        max_row = sql.bindparam(
+                            None,
+                            max_row,
+                            type_=Integer,
+                            literal_execute=True,
+                            unique=True,
+                        )
                     else:
                         max_row = limit_clause
                         if offset_clause is not None:
@@ -969,10 +980,15 @@ class OracleCompiler(compiler.SQLCompiler):
                             adapter.traverse(elem) for elem in for_update.of
                         ]
 
-                    if not self.dialect.use_binds_for_limits:
-                        offset_clause = sql.literal_column(
-                            "%d" % select._offset
+                    if select._simple_int_offset:
+                        offset_clause = sql.bindparam(
+                            None,
+                            select._offset,
+                            Integer,
+                            literal_execute=True,
+                            unique=True,
                         )
+
                     offsetselect = offsetselect.where(
                         sql.literal_column("ora_rn") > offset_clause
                     )
@@ -1150,11 +1166,21 @@ class OracleDialect(default.DefaultDialect):
         (sa_schema.Index, {"bitmap": False, "compress": False}),
     ]
 
+    @util.deprecated_params(
+        use_binds_for_limits=(
+            "1.4",
+            "The ``use_binds_for_limits`` Oracle dialect parameter is "
+            "deprecated. The dialect now renders LIMIT /OFFSET integers "
+            "inline in all cases using a post-compilation hook, so that the "
+            "value is still represented by a 'bound parameter' on the Core "
+            "Expression side.",
+        )
+    )
     def __init__(
         self,
         use_ansi=True,
         optimize_limits=False,
-        use_binds_for_limits=True,
+        use_binds_for_limits=None,
         use_nchar_for_unicode=False,
         exclude_tablespaces=("SYSTEM", "SYSAUX"),
         **kwargs
@@ -1163,7 +1189,6 @@ class OracleDialect(default.DefaultDialect):
         self._use_nchar_for_unicode = use_nchar_for_unicode
         self.use_ansi = use_ansi
         self.optimize_limits = optimize_limits
-        self.use_binds_for_limits = use_binds_for_limits
         self.exclude_tablespaces = exclude_tablespaces
 
     def initialize(self, connection):
diff --git a/lib/sqlalchemy/dialects/oracle/cx_oracle.py b/lib/sqlalchemy/dialects/oracle/cx_oracle.py
index 90d049aa0..fbacb622e 100644
--- a/lib/sqlalchemy/dialects/oracle/cx_oracle.py
+++ b/lib/sqlalchemy/dialects/oracle/cx_oracle.py
@@ -518,13 +518,13 @@ class OracleCompiler_cx_oracle(OracleCompiler):
             quote is True
             or quote is not False
             and self.preparer._bindparam_requires_quotes(name)
+            and not kw.get("post_compile", False)
         ):
-            if kw.get("expanding", False):
-                raise exc.CompileError(
-                    "Can't use expanding feature with parameter name "
-                    "%r on Oracle; it requires quoting which is not supported "
-                    "in this context." % name
-                )
+            # interesting to note about expanding parameters - since the
+            # new parameters take the form <paramname>_<int>, at least if
+            # they are originally formed from reserved words, they no longer
+            # need quoting :).    names that include illegal characters
+            # won't work however.
             quoted_name = '"%s"' % name
             self._quoted_bind_names[name] = quoted_name
             return OracleCompiler.bindparam_string(self, quoted_name, **kw)
diff --git a/lib/sqlalchemy/dialects/sybase/base.py b/lib/sqlalchemy/dialects/sybase/base.py
index 71ff62976..532affb4a 100644
--- a/lib/sqlalchemy/dialects/sybase/base.py
+++ b/lib/sqlalchemy/dialects/sybase/base.py
@@ -513,17 +513,12 @@ class SybaseSQLCompiler(compiler.SQLCompiler):
 
     def get_select_precolumns(self, select, **kw):
         s = select._distinct and "DISTINCT " or ""
-        # TODO: don't think Sybase supports
-        # bind params for FIRST / TOP
-        limit = select._limit
-        if limit:
-            # if select._limit == 1:
-            # s += "FIRST "
-            # else:
-            # s += "TOP %s " % (select._limit,)
-            s += "TOP %s " % (limit,)
-        offset = select._offset
-        if offset:
+
+        if select._simple_int_limit and not select._offset:
+            kw["literal_execute"] = True
+            s += "TOP %s " % self.process(select._limit_clause, **kw)
+
+        if select._offset:
             raise NotImplementedError("Sybase ASE does not support OFFSET")
         return s
 
diff --git a/lib/sqlalchemy/engine/default.py b/lib/sqlalchemy/engine/default.py
index fb1728eab..eac593125 100644
--- a/lib/sqlalchemy/engine/default.py
+++ b/lib/sqlalchemy/engine/default.py
@@ -695,8 +695,10 @@ class DefaultExecutionContext(interfaces.ExecutionContext):
 
         processors = compiled._bind_processors
 
-        if compiled.contains_expanding_parameters:
-            positiontup = self._expand_in_parameters(compiled, processors)
+        if compiled.literal_execute_params:
+            positiontup = self._literal_execute_parameters(
+                compiled, processors
+            )
         elif compiled.positional:
             positiontup = self.compiled.positiontup
 
@@ -744,21 +746,34 @@ class DefaultExecutionContext(interfaces.ExecutionContext):
 
         return self
 
-    def _expand_in_parameters(self, compiled, processors):
-        """handle special 'expanding' parameters, IN tuples that are rendered
-        on a per-parameter basis for an otherwise fixed SQL statement string.
+    def _literal_execute_parameters(self, compiled, processors):
+        """handle special post compile parameters.
+
+        These include:
+
+        * "expanding" parameters -typically IN tuples that are rendered
+          on a per-parameter basis for an otherwise fixed SQL statement string.
+
+        * literal_binds compiled with the literal_execute flag.  Used for
+          things like SQL Server "TOP N" where the driver does not accommodate
+          N as a bound parameter.
 
         """
         if self.executemany:
             raise exc.InvalidRequestError(
-                "'expanding' parameters can't be used with " "executemany()"
+                "'literal_execute' or 'expanding' parameters can't be "
+                "used with executemany()"
             )
 
-        if self.compiled.positional and self.compiled._numeric_binds:
-            # I'm not familiar with any DBAPI that uses 'numeric'
+        if compiled.positional and compiled._numeric_binds:
+            # I'm not familiar with any DBAPI that uses 'numeric'.
+            # strategy would likely be to make use of numbers greater than
+            # the highest number present; then for expanding parameters,
+            # append them to the end of the parameter list.   that way
+            # we avoid having to renumber all the existing parameters.
             raise NotImplementedError(
-                "'expanding' bind parameters not supported with "
-                "'numeric' paramstyle at this time."
+                "'post-compile' bind parameters are not supported with "
+                "the 'numeric' paramstyle at this time."
             )
 
         self._expanded_parameters = {}
@@ -773,12 +788,21 @@ class DefaultExecutionContext(interfaces.ExecutionContext):
         to_update_sets = {}
 
         for name in (
-            self.compiled.positiontup
+            compiled.positiontup
             if compiled.positional
-            else self.compiled.binds
+            else compiled.bind_names.values()
         ):
-            parameter = self.compiled.binds[name]
-            if parameter.expanding:
+            parameter = compiled.binds[name]
+            if parameter in compiled.literal_execute_params:
+
+                if not parameter.expanding:
+                    value = compiled_params.pop(name)
+                    replacement_expressions[
+                        name
+                    ] = compiled.render_literal_bindparam(
+                        parameter, render_literal_value=value
+                    )
+                    continue
 
                 if name in replacement_expressions:
                     to_update = to_update_sets[name]
@@ -791,58 +815,25 @@ class DefaultExecutionContext(interfaces.ExecutionContext):
                     # param.
                     values = compiled_params.pop(name)
 
-                    if not values:
-                        to_update = to_update_sets[name] = []
-                        replacement_expressions[
-                            name
-                        ] = self.compiled.visit_empty_set_expr(
-                            parameter._expanding_in_types
-                            if parameter._expanding_in_types
-                            else [parameter.type]
-                        )
+                    leep = compiled._literal_execute_expanding_parameter
+                    to_update, replacement_expr = leep(name, parameter, values)
 
-                    elif isinstance(values[0], (tuple, list)):
-                        to_update = to_update_sets[name] = [
-                            ("%s_%s_%s" % (name, i, j), value)
-                            for i, tuple_element in enumerate(values, 1)
-                            for j, value in enumerate(tuple_element, 1)
-                        ]
-                        replacement_expressions[name] = (
-                            "VALUES " if self.dialect.tuple_in_values else ""
-                        ) + ", ".join(
-                            "(%s)"
-                            % ", ".join(
-                                self.compiled.bindtemplate
-                                % {
-                                    "name": to_update[
-                                        i * len(tuple_element) + j
-                                    ][0]
-                                }
-                                for j, value in enumerate(tuple_element)
-                            )
-                            for i, tuple_element in enumerate(values)
-                        )
-                    else:
-                        to_update = to_update_sets[name] = [
-                            ("%s_%s" % (name, i), value)
-                            for i, value in enumerate(values, 1)
-                        ]
-                        replacement_expressions[name] = ", ".join(
-                            self.compiled.bindtemplate % {"name": key}
-                            for key, value in to_update
-                        )
+                    to_update_sets[name] = to_update
+                    replacement_expressions[name] = replacement_expr
 
-                compiled_params.update(to_update)
-                processors.update(
-                    (key, processors[name])
-                    for key, value in to_update
-                    if name in processors
-                )
-                if compiled.positional:
-                    positiontup.extend(name for name, value in to_update)
-                self._expanded_parameters[name] = [
-                    expand_key for expand_key, value in to_update
-                ]
+                if not parameter.literal_execute:
+                    compiled_params.update(to_update)
+
+                    processors.update(
+                        (key, processors[name])
+                        for key, value in to_update
+                        if name in processors
+                    )
+                    if compiled.positional:
+                        positiontup.extend(name for name, value in to_update)
+                    self._expanded_parameters[name] = [
+                        expand_key for expand_key, value in to_update
+                    ]
             elif compiled.positional:
                 positiontup.append(name)
 
@@ -850,7 +841,7 @@ class DefaultExecutionContext(interfaces.ExecutionContext):
             return replacement_expressions[m.group(1)]
 
         self.statement = re.sub(
-            r"\[EXPANDING_(\S+)\]", process_expanding, self.statement
+            r"\[POSTCOMPILE_(\S+)\]", process_expanding, self.statement
         )
         return positiontup
 
@@ -1214,6 +1205,8 @@ class DefaultExecutionContext(interfaces.ExecutionContext):
 
         inputsizes = {}
         for bindparam in self.compiled.bind_names:
+            if bindparam in self.compiled.literal_execute_params:
+                continue
 
             dialect_impl = bindparam.type._unwrapped_dialect_impl(self.dialect)
             dialect_impl_cls = type(dialect_impl)
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
diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index fa7eeaecf..8df93a60b 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -36,6 +36,7 @@ from . import roles
 from . import schema
 from . import selectable
 from . import sqltypes
+from .base import NO_ARG
 from .. import exc
 from .. import util
 
@@ -463,14 +464,6 @@ class SQLCompiler(Compiled):
     columns with the table name (i.e. MySQL only)
     """
 
-    contains_expanding_parameters = False
-    """True if we've encountered bindparam(..., expanding=True).
-
-    These need to be converted before execution time against the
-    string statement.
-
-    """
-
     ansi_bind_rules = False
     """SQL 92 doesn't allow bind parameters to be used
     in the columns clause of a SELECT, nor does it allow
@@ -507,6 +500,8 @@ class SQLCompiler(Compiled):
 
     """
 
+    literal_execute_params = frozenset()
+
     insert_prefetch = update_prefetch = ()
 
     def __init__(
@@ -1267,6 +1262,81 @@ class SQLCompiler(Compiled):
             % self.dialect.name
         )
 
+    def _literal_execute_expanding_parameter_literal_binds(
+        self, parameter, values
+    ):
+        if not values:
+            replacement_expression = self.visit_empty_set_expr(
+                parameter._expanding_in_types
+                if parameter._expanding_in_types
+                else [parameter.type]
+            )
+
+        elif isinstance(values[0], (tuple, list)):
+            replacement_expression = (
+                "VALUES " if self.dialect.tuple_in_values else ""
+            ) + ", ".join(
+                "(%s)"
+                % (
+                    ", ".join(
+                        self.render_literal_value(value, parameter.type)
+                        for value in tuple_element
+                    )
+                )
+                for i, tuple_element in enumerate(values)
+            )
+        else:
+            replacement_expression = ", ".join(
+                self.render_literal_value(value, parameter.type)
+                for value in values
+            )
+
+        return (), replacement_expression
+
+    def _literal_execute_expanding_parameter(self, name, parameter, values):
+        if parameter.literal_execute:
+            return self._literal_execute_expanding_parameter_literal_binds(
+                parameter, values
+            )
+
+        if not values:
+            to_update = []
+            replacement_expression = self.visit_empty_set_expr(
+                parameter._expanding_in_types
+                if parameter._expanding_in_types
+                else [parameter.type]
+            )
+
+        elif isinstance(values[0], (tuple, list)):
+            to_update = [
+                ("%s_%s_%s" % (name, i, j), value)
+                for i, tuple_element in enumerate(values, 1)
+                for j, value in enumerate(tuple_element, 1)
+            ]
+            replacement_expression = (
+                "VALUES " if self.dialect.tuple_in_values else ""
+            ) + ", ".join(
+                "(%s)"
+                % (
+                    ", ".join(
+                        self.bindtemplate
+                        % {"name": to_update[i * len(tuple_element) + j][0]}
+                        for j, value in enumerate(tuple_element)
+                    )
+                )
+                for i, tuple_element in enumerate(values)
+            )
+        else:
+            to_update = [
+                ("%s_%s" % (name, i), value)
+                for i, value in enumerate(values, 1)
+            ]
+            replacement_expression = ", ".join(
+                self.bindtemplate % {"name": key} for key, value in to_update
+            )
+
+        return to_update, replacement_expression
+
     def visit_binary(
         self, binary, override_operator=None, eager_grouping=False, **kw
     ):
@@ -1457,6 +1527,7 @@ class SQLCompiler(Compiled):
         within_columns_clause=False,
         literal_binds=False,
         skip_bind_expression=False,
+        literal_execute=False,
         **kwargs
     ):
 
@@ -1469,18 +1540,28 @@ class SQLCompiler(Compiled):
                     skip_bind_expression=True,
                     within_columns_clause=within_columns_clause,
                     literal_binds=literal_binds,
+                    literal_execute=literal_execute,
                     **kwargs
                 )
 
-        if literal_binds or (within_columns_clause and self.ansi_bind_rules):
-            if bindparam.value is None and bindparam.callable is None:
-                raise exc.CompileError(
-                    "Bind parameter '%s' without a "
-                    "renderable value not allowed here." % bindparam.key
-                )
-            return self.render_literal_bindparam(
+        if not literal_binds:
+            post_compile = (
+                literal_execute
+                or bindparam.literal_execute
+                or bindparam.expanding
+            )
+        else:
+            post_compile = False
+
+        if not literal_execute and (
+            literal_binds or (within_columns_clause and self.ansi_bind_rules)
+        ):
+            ret = self.render_literal_bindparam(
                 bindparam, within_columns_clause=True, **kwargs
             )
+            if bindparam.expanding:
+                ret = "(%s)" % ret
+            return ret
 
         name = self._truncate_bindparam(bindparam)
 
@@ -1508,13 +1589,38 @@ class SQLCompiler(Compiled):
 
         self.binds[bindparam.key] = self.binds[name] = bindparam
 
-        return self.bindparam_string(
-            name, expanding=bindparam.expanding, **kwargs
+        if post_compile:
+            self.literal_execute_params |= {bindparam}
+
+        ret = self.bindparam_string(
+            name,
+            post_compile=post_compile,
+            expanding=bindparam.expanding,
+            **kwargs
         )
+        if bindparam.expanding:
+            ret = "(%s)" % ret
+        return ret
+
+    def render_literal_bindparam(
+        self, bindparam, render_literal_value=NO_ARG, **kw
+    ):
+        if render_literal_value is not NO_ARG:
+            value = render_literal_value
+        else:
+            if bindparam.value is None and bindparam.callable is None:
+                raise exc.CompileError(
+                    "Bind parameter '%s' without a "
+                    "renderable value not allowed here." % bindparam.key
+                )
+            value = bindparam.effective_value
 
-    def render_literal_bindparam(self, bindparam, **kw):
-        value = bindparam.effective_value
-        return self.render_literal_value(value, bindparam.type)
+        if bindparam.expanding:
+            leep = self._literal_execute_expanding_parameter_literal_binds
+            to_update, replacement_expr = leep(bindparam, value)
+            return replacement_expr
+        else:
+            return self.render_literal_value(value, bindparam.type)
 
     def render_literal_value(self, value, type_):
         """Render the value of a bind parameter as a quoted literal.
@@ -1577,16 +1683,20 @@ class SQLCompiler(Compiled):
         return derived + "_" + str(anonymous_counter)
 
     def bindparam_string(
-        self, name, positional_names=None, expanding=False, **kw
+        self,
+        name,
+        positional_names=None,
+        post_compile=False,
+        expanding=False,
+        **kw
     ):
         if self.positional:
             if positional_names is not None:
                 positional_names.append(name)
             else:
                 self.positiontup.append(name)
-        if expanding:
-            self.contains_expanding_parameters = True
-            return "([EXPANDING_%s])" % name
+        if post_compile:
+            return "[POSTCOMPILE_%s]" % name
         else:
             return self.bindtemplate % {"name": name}
 
diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py
index 669519d1a..42e7522ae 100644
--- a/lib/sqlalchemy/sql/elements.py
+++ b/lib/sqlalchemy/sql/elements.py
@@ -211,6 +211,15 @@ class ClauseElement(roles.SQLRole, Visitable):
 
         return c
 
+    def _with_binary_element_type(self, type_):
+        """in the context of binary expression, convert the type of this
+        object to the one given.
+
+        applies only to :class:`.ColumnElement` classes.
+
+        """
+        return self
+
     def _cache_key(self, **kw):
         """return an optional cache key.
 
@@ -732,6 +741,14 @@ class ColumnElement(
     def type(self):
         return type_api.NULLTYPE
 
+    def _with_binary_element_type(self, type_):
+        cloned = self._clone()
+        cloned._copy_internals(
+            clone=lambda element: element._with_binary_element_type(type_)
+        )
+        cloned.type = type_
+        return cloned
+
     @util.memoized_property
     def comparator(self):
         try:
@@ -986,6 +1003,7 @@ class BindParameter(roles.InElementRole, ColumnElement):
         callable_=None,
         expanding=False,
         isoutparam=False,
+        literal_execute=False,
         _compared_to_operator=None,
         _compared_to_type=None,
     ):
@@ -1198,6 +1216,30 @@ class BindParameter(roles.InElementRole, ColumnElement):
 
             :func:`.outparam`
 
+        :param literal_execute:
+          if True, the bound parameter will be rendered in the compile phase
+          with a special "POSTCOMPILE" token, and the SQLAlchemy compiler will
+          render the final value of the parameter into the SQL statement at
+          statement execution time, omitting the value from the parameter
+          dictionary / list passed to DBAPI ``cursor.execute()``.  This
+          produces a similar effect as that of using the ``literal_binds``,
+          compilation flag,  however takes place as the statement is sent to
+          the DBAPI ``cursor.execute()`` method, rather than when the statement
+          is compiled.   The primary use of this
+          capability is for rendering LIMIT / OFFSET clauses for database
+          drivers that can't accommodate for bound parameters in these
+          contexts, while allowing SQL constructs to be cacheable at the
+          compilation level.
+
+          .. versionadded:: 1.4 Added "post compile" bound parameters
+
+            .. seealso::
+
+                :ref:`change_4808`.
+
+
+
+
         """
         if isinstance(key, ColumnClause):
             type_ = key.type
@@ -1235,6 +1277,7 @@ class BindParameter(roles.InElementRole, ColumnElement):
         self.isoutparam = isoutparam
         self.required = required
         self.expanding = expanding
+        self.literal_execute = literal_execute
 
         if type_ is None:
             if _compared_to_type is not None:
@@ -1643,14 +1686,17 @@ class TextClause(
 
         for bind in binds:
             try:
-                existing = new_params[bind.key]
+                # the regex used for text() currently will not match
+                # a unique/anonymous key in any case, so use the _orig_key
+                # so that a text() construct can support unique parameters
+                existing = new_params[bind._orig_key]
             except KeyError:
                 raise exc.ArgumentError(
                     "This text() construct doesn't define a "
-                    "bound parameter named %r" % bind.key
+                    "bound parameter named %r" % bind._orig_key
                 )
             else:
-                new_params[existing.key] = bind
+                new_params[existing._orig_key] = bind
 
         for key, value in names_to_values.items():
             try:
diff --git a/lib/sqlalchemy/sql/sqltypes.py b/lib/sqlalchemy/sql/sqltypes.py
index fd15d7c79..7829eb4d0 100644
--- a/lib/sqlalchemy/sql/sqltypes.py
+++ b/lib/sqlalchemy/sql/sqltypes.py
@@ -471,7 +471,7 @@ class Integer(_LookupExpressionAdapter, TypeEngine):
 
     def literal_processor(self, dialect):
         def process(value):
-            return str(value)
+            return str(int(value))
 
         return process
 
diff --git a/lib/sqlalchemy/testing/assertsql.py b/lib/sqlalchemy/testing/assertsql.py
index 00496d549..55f4dc2ab 100644
--- a/lib/sqlalchemy/testing/assertsql.py
+++ b/lib/sqlalchemy/testing/assertsql.py
@@ -38,11 +38,10 @@ class SQLMatchRule(AssertRule):
 
 
 class CursorSQL(SQLMatchRule):
-    consume_statement = False
-
-    def __init__(self, statement, params=None):
+    def __init__(self, statement, params=None, consume_statement=True):
         self.statement = statement
         self.params = params
+        self.consume_statement = consume_statement
 
     def process_statement(self, execute_observed):
         stmt = execute_observed.statements[0]
diff --git a/lib/sqlalchemy/testing/requirements.py b/lib/sqlalchemy/testing/requirements.py
index 62e600eaa..27def1004 100644
--- a/lib/sqlalchemy/testing/requirements.py
+++ b/lib/sqlalchemy/testing/requirements.py
@@ -45,6 +45,26 @@ class SuiteRequirements(Requirements):
         return exclusions.open()
 
     @property
+    def standard_cursor_sql(self):
+        """Target database passes SQL-92 style statements to cursor.execute()
+        when a statement like select() or insert() is run.
+
+        A very small portion of dialect-level tests will ensure that certain
+        conditions are present in SQL strings, and these tests use very basic
+        SQL that will work on any SQL-like platform in order to assert results.
+
+        It's normally a given for any pep-249 DBAPI that a statement like
+        "SELECT id, name FROM table WHERE some_table.id=5" will work.
+        However, there are dialects that don't actually produce SQL Strings
+        and instead may work with symbolic objects instead, or dialects that
+        aren't working with SQL, so for those this requirement can be marked
+        as excluded.
+
+        """
+
+        return exclusions.open()
+
+    @property
     def on_update_cascade(self):
         """"target database must support ON UPDATE..CASCADE behavior in
         foreign keys."""
diff --git a/lib/sqlalchemy/testing/suite/test_select.py b/lib/sqlalchemy/testing/suite/test_select.py
index dabb30f9b..02cdcf4f5 100644
--- a/lib/sqlalchemy/testing/suite/test_select.py
+++ b/lib/sqlalchemy/testing/suite/test_select.py
@@ -1,7 +1,10 @@
+from .. import AssertsCompiledSQL
+from .. import AssertsExecutionResults
 from .. import config
 from .. import fixtures
 from ..assertions import eq_
 from ..assertions import in_
+from ..assertsql import CursorSQL
 from ..schema import Column
 from ..schema import Table
 from ... import bindparam
@@ -14,6 +17,7 @@ from ... import null
 from ... import select
 from ... import String
 from ... import testing
+from ... import text
 from ... import true
 from ... import tuple_
 from ... import union
@@ -233,6 +237,59 @@ class LimitOffsetTest(fixtures.TablesTest):
             params={"l": 2, "o": 1},
         )
 
+    @testing.requires.sql_expression_limit_offset
+    def test_expr_offset(self):
+        table = self.tables.some_table
+        self._assert_result(
+            select([table])
+            .order_by(table.c.id)
+            .offset(literal_column("1") + literal_column("2")),
+            [(4, 4, 5)],
+        )
+
+    @testing.requires.sql_expression_limit_offset
+    def test_expr_limit(self):
+        table = self.tables.some_table
+        self._assert_result(
+            select([table])
+            .order_by(table.c.id)
+            .limit(literal_column("1") + literal_column("2")),
+            [(1, 1, 2), (2, 2, 3), (3, 3, 4)],
+        )
+
+    @testing.requires.sql_expression_limit_offset
+    def test_expr_limit_offset(self):
+        table = self.tables.some_table
+        self._assert_result(
+            select([table])
+            .order_by(table.c.id)
+            .limit(literal_column("1") + literal_column("1"))
+            .offset(literal_column("1") + literal_column("1")),
+            [(3, 3, 4), (4, 4, 5)],
+        )
+
+    @testing.requires.sql_expression_limit_offset
+    def test_simple_limit_expr_offset(self):
+        table = self.tables.some_table
+        self._assert_result(
+            select([table])
+            .order_by(table.c.id)
+            .limit(2)
+            .offset(literal_column("1") + literal_column("1")),
+            [(3, 3, 4), (4, 4, 5)],
+        )
+
+    @testing.requires.sql_expression_limit_offset
+    def test_expr_limit_simple_offset(self):
+        table = self.tables.some_table
+        self._assert_result(
+            select([table])
+            .order_by(table.c.id)
+            .limit(literal_column("1") + literal_column("1"))
+            .offset(2),
+            [(3, 3, 4), (4, 4, 5)],
+        )
+
 
 class CompoundSelectTest(fixtures.TablesTest):
     __backend__ = True
@@ -372,6 +429,127 @@ class CompoundSelectTest(fixtures.TablesTest):
         )
 
 
+class PostCompileParamsTest(
+    AssertsExecutionResults, AssertsCompiledSQL, fixtures.TablesTest
+):
+    __backend__ = True
+
+    __requires__ = ("standard_cursor_sql",)
+
+    @classmethod
+    def define_tables(cls, metadata):
+        Table(
+            "some_table",
+            metadata,
+            Column("id", Integer, primary_key=True),
+            Column("x", Integer),
+            Column("y", Integer),
+            Column("z", String(50)),
+        )
+
+    @classmethod
+    def insert_data(cls):
+        config.db.execute(
+            cls.tables.some_table.insert(),
+            [
+                {"id": 1, "x": 1, "y": 2, "z": "z1"},
+                {"id": 2, "x": 2, "y": 3, "z": "z2"},
+                {"id": 3, "x": 3, "y": 4, "z": "z3"},
+                {"id": 4, "x": 4, "y": 5, "z": "z4"},
+            ],
+        )
+
+    def test_compile(self):
+        table = self.tables.some_table
+
+        stmt = select([table.c.id]).where(
+            table.c.x == bindparam("q", literal_execute=True)
+        )
+
+        self.assert_compile(
+            stmt,
+            "SELECT some_table.id FROM some_table "
+            "WHERE some_table.x = [POSTCOMPILE_q]",
+            {},
+        )
+
+    def test_compile_literal_binds(self):
+        table = self.tables.some_table
+
+        stmt = select([table.c.id]).where(
+            table.c.x == bindparam("q", 10, literal_execute=True)
+        )
+
+        self.assert_compile(
+            stmt,
+            "SELECT some_table.id FROM some_table WHERE some_table.x = 10",
+            {},
+            literal_binds=True,
+        )
+
+    def test_execute(self):
+        table = self.tables.some_table
+
+        stmt = select([table.c.id]).where(
+            table.c.x == bindparam("q", literal_execute=True)
+        )
+
+        with self.sql_execution_asserter() as asserter:
+            with config.db.connect() as conn:
+                conn.execute(stmt, q=10)
+
+        asserter.assert_(
+            CursorSQL(
+                "SELECT some_table.id \nFROM some_table "
+                "\nWHERE some_table.x = 10",
+                () if config.db.dialect.positional else {},
+            )
+        )
+
+    def test_execute_expanding_plus_literal_execute(self):
+        table = self.tables.some_table
+
+        stmt = select([table.c.id]).where(
+            table.c.x.in_(bindparam("q", expanding=True, literal_execute=True))
+        )
+
+        with self.sql_execution_asserter() as asserter:
+            with config.db.connect() as conn:
+                conn.execute(stmt, q=[5, 6, 7])
+
+        asserter.assert_(
+            CursorSQL(
+                "SELECT some_table.id \nFROM some_table "
+                "\nWHERE some_table.x IN (5, 6, 7)",
+                () if config.db.dialect.positional else {},
+            )
+        )
+
+    @testing.requires.tuple_in
+    def test_execute_tuple_expanding_plus_literal_execute(self):
+        table = self.tables.some_table
+
+        stmt = select([table.c.id]).where(
+            tuple_(table.c.x, table.c.y).in_(
+                bindparam("q", expanding=True, literal_execute=True)
+            )
+        )
+
+        with self.sql_execution_asserter() as asserter:
+            with config.db.connect() as conn:
+                conn.execute(stmt, q=[(5, 10), (12, 18)])
+
+        asserter.assert_(
+            CursorSQL(
+                "SELECT some_table.id \nFROM some_table "
+                "\nWHERE (some_table.x, some_table.y) "
+                "IN (%s(5, 10), (12, 18))"
+                % ("VALUES " if config.db.dialect.tuple_in_values else ""),
+                () if config.db.dialect.positional else {},
+            )
+        )
+
+
 class ExpandingBoundInTest(fixtures.TablesTest):
     __backend__ = True
 
@@ -496,6 +674,18 @@ class ExpandingBoundInTest(fixtures.TablesTest):
             params={"q": [(2, "z2"), (3, "z3"), (4, "z4")]},
         )
 
+    @testing.requires.tuple_in
+    def test_bound_in_heterogeneous_two_tuple_text(self):
+        stmt = text(
+            "select id FROM some_table WHERE (x, z) IN :q ORDER BY id"
+        ).bindparams(bindparam("q", expanding=True))
+
+        self._assert_result(
+            stmt,
+            [(2,), (3,), (4,)],
+            params={"q": [(2, "z2"), (3, "z3"), (4, "z4")]},
+        )
+
     def test_empty_set_against_integer(self):
         table = self.tables.some_table