support bind expressions w/ expanding IN; apply to psycopg2

Fixed issue where "expanding IN" would fail to function correctly with
datatypes that use the :meth:`_types.TypeEngine.bind_expression` method,
where the method would need to be applied to each element of the
IN expression rather than the overall IN expression itself.

Fixed issue where IN expressions against a series of array elements, as can
be done with PostgreSQL, would fail to function correctly due to multiple
issues within the "expanding IN" feature of SQLAlchemy Core that was
standardized in version 1.4.  The psycopg2 dialect now makes use of the
:meth:`_types.TypeEngine.bind_expression` method with :class:`_types.ARRAY`
to portably apply the correct casts to elements.  The asyncpg dialect was
not affected by this issue as it applies bind-level casts at the driver
level rather than at the compiler level.

as part of this commit the "bind translate" feature has been
simplified and also applies to the names in the POSTCOMPILE tag to
accommodate for brackets.

Fixes: #7177
Change-Id: I08c703adb0a9bd6f5aeee5de3ff6f03cccdccdc5

3 files changed, 24 insertions, 21 deletions

diff --git a/lib/sqlalchemy/dialects/postgresql/asyncpg.py b/lib/sqlalchemy/dialects/postgresql/asyncpg.py
index dc3da224c..3d195e691 100644
--- a/lib/sqlalchemy/dialects/postgresql/asyncpg.py
+++ b/lib/sqlalchemy/dialects/postgresql/asyncpg.py
@@ -362,7 +362,6 @@ class AsyncAdapt_asyncpg_cursor:
         if not self._inputsizes:
             return tuple("$%d" % idx for idx, _ in enumerate(params, 1))
         else:
-
             return tuple(
                 "$%d::%s" % (idx, typ) if typ else "$%d" % idx
                 for idx, typ in enumerate(
diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index 2e28b45ca..c1a2cf81d 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -2047,6 +2047,15 @@ class ENUM(sqltypes.NativeForEmulated, sqltypes.Enum):
             self.drop(bind=bind, checkfirst=checkfirst)
 
 
+class _ColonCast(elements.Cast):
+    __visit_name__ = "colon_cast"
+
+    def __init__(self, expression, type_):
+        self.type = type_
+        self.clause = expression
+        self.typeclause = elements.TypeClause(type_)
+
+
 colspecs = {
     sqltypes.ARRAY: _array.ARRAY,
     sqltypes.Interval: INTERVAL,
@@ -2102,6 +2111,12 @@ ischema_names = {
 
 
 class PGCompiler(compiler.SQLCompiler):
+    def visit_colon_cast(self, element, **kw):
+        return "%s::%s" % (
+            element.clause._compiler_dispatch(self, **kw),
+            element.typeclause._compiler_dispatch(self, **kw),
+        )
+
     def visit_array(self, element, **kw):
         return "ARRAY[%s]" % self.visit_clauselist(element, **kw)
 
diff --git a/lib/sqlalchemy/dialects/postgresql/psycopg2.py b/lib/sqlalchemy/dialects/postgresql/psycopg2.py
index a71bdf760..4143dd041 100644
--- a/lib/sqlalchemy/dialects/postgresql/psycopg2.py
+++ b/lib/sqlalchemy/dialects/postgresql/psycopg2.py
@@ -473,6 +473,8 @@ import logging
 import re
 from uuid import UUID as _python_UUID
 
+from .array import ARRAY as PGARRAY
+from .base import _ColonCast
 from .base import _DECIMAL_TYPES
 from .base import _FLOAT_TYPES
 from .base import _INT_TYPES
@@ -490,7 +492,6 @@ from ... import processors
 from ... import types as sqltypes
 from ... import util
 from ...engine import cursor as _cursor
-from ...sql import elements
 from ...util import collections_abc
 
 
@@ -556,6 +557,11 @@ class _PGHStore(HSTORE):
             return super(_PGHStore, self).result_processor(dialect, coltype)
 
 
+class _PGARRAY(PGARRAY):
+    def bind_expression(self, bindvalue):
+        return _ColonCast(bindvalue, self)
+
+
 class _PGJSON(JSON):
     def result_processor(self, dialect, coltype):
         return None
@@ -638,25 +644,7 @@ class PGExecutionContext_psycopg2(PGExecutionContext):
 
 
 class PGCompiler_psycopg2(PGCompiler):
-    def visit_bindparam(self, bindparam, skip_bind_expression=False, **kw):
-
-        text = super(PGCompiler_psycopg2, self).visit_bindparam(
-            bindparam, skip_bind_expression=skip_bind_expression, **kw
-        )
-        # note that if the type has a bind_expression(), we will get a
-        # double compile here
-        if not skip_bind_expression and (
-            bindparam.type._is_array or bindparam.type._is_type_decorator
-        ):
-            typ = bindparam.type._unwrapped_dialect_impl(self.dialect)
-
-            if typ._is_array:
-                text += "::%s" % (
-                    elements.TypeClause(typ)._compiler_dispatch(
-                        self, skip_bind_expression=skip_bind_expression, **kw
-                    ),
-                )
-        return text
+    pass
 
 
 class PGIdentifierPreparer_psycopg2(PGIdentifierPreparer):
@@ -713,6 +701,7 @@ class PGDialect_psycopg2(PGDialect):
             sqltypes.JSON: _PGJSON,
             JSONB: _PGJSONB,
             UUID: _PGUUID,
+            sqltypes.ARRAY: _PGARRAY,
         },
     )