Infer types in BindParameter when expanding=True

Enhanced the "expanding" feature used for :meth:`_sql.ColumnOperators.in_`
operations to infer the type of expression from the right hand list of
elements, if the left hand side does not have any explicit type set up.
This allows the expression to support stringification among other things.
In 1.3, "expanding" was not automatically used for
:meth:`_sql.ColumnOperators.in_` expressions, so in that sense this change
fixes a behavioral regression.

Fixes: #6222
Change-Id: Icdfda1e2c226a21896cafd6d8f251547794451c2

3 files changed, 60 insertions, 3 deletions

diff --git a/test/sql/test_compare.py b/test/sql/test_compare.py
index 02dd0661a..21a349d76 100644
--- a/test/sql/test_compare.py
+++ b/test/sql/test_compare.py
@@ -692,12 +692,32 @@ class CoreFixtures(object):
             column("y").in_(
                 bindparam(
                     "q",
+                    # note that a different cache key is created if
+                    # the value given to the bindparam is [], as the type
+                    # cannot be inferred for the empty list but can
+                    # for the non-empty list as of #6222
+                    random_choices(range(10), k=random.randint(1, 7)),
+                    expanding=True,
+                )
+            ),
+            column("y2").in_(
+                bindparam(
+                    "q",
+                    # for typed param, empty and not empty param will have
+                    # the same type
                     random_choices(range(10), k=random.randint(0, 7)),
+                    type_=Integer,
                     expanding=True,
                 )
             ),
-            column("z").in_(random_choices(range(10), k=random.randint(0, 7))),
-            column("x") == random.randint(1, 10),
+            # don't include empty for untyped, will create different cache
+            # key
+            column("z").in_(random_choices(range(10), k=random.randint(1, 7))),
+            # empty is fine for typed, will create the same cache key
+            column("z2", Integer).in_(
+                random_choices(range(10), k=random.randint(0, 7))
+            ),
+            column("x") == random.randint(0, 10),
         )
     ]
 
diff --git a/test/sql/test_operators.py b/test/sql/test_operators.py
index 0d7f331e0..878360b9d 100644
--- a/test/sql/test_operators.py
+++ b/test/sql/test_operators.py
@@ -1989,6 +1989,23 @@ class InTest(fixtures.TestBase, testing.AssertsCompiledSQL):
             "SELECT t.a FROM t WHERE t.a IN (SELECT scan)",
         )
 
+    def test_type_inference_one(self):
+        expr = column("q").in_([1, 2, 3])
+        is_(expr.right.type._type_affinity, Integer)
+
+        self.assert_compile(expr, "q IN (1, 2, 3)", literal_binds=True)
+
+    def test_type_inference_two(self):
+        expr = column("q").in_([])
+        is_(expr.right.type, sqltypes.NULLTYPE)
+
+        self.assert_compile(
+            expr,
+            "q IN (SELECT 1 WHERE 1!=1)",
+            literal_binds=True,
+            dialect="default_enhanced",
+        )
+
 
 class MathOperatorTest(fixtures.TestBase, testing.AssertsCompiledSQL):
     __dialect__ = "default"
@@ -3075,6 +3092,26 @@ class TupleTypingTest(fixtures.TestBase):
 
         self._assert_types(expr.right.type.types)
 
+    # since we want to infer "binary"
+    @testing.requires.python3
+    def test_tuple_type_expanding_inference(self):
+        a, b, c = column("a"), column("b"), column("c")
+
+        t1 = tuple_(a, b, c)
+        expr = t1.in_([(3, "hi", b"there"), (4, "Q", b"P")])
+
+        eq_(len(expr.right.value), 2)
+
+        self._assert_types(expr.right.type.types)
+
+    @testing.requires.python3
+    def test_tuple_type_plain_inference(self):
+        a, b, c = column("a"), column("b"), column("c")
+
+        t1 = tuple_(a, b, c)
+        expr = t1 == (3, "hi", b"there")
+        self._assert_types(expr.right.type.types)
+
 
 class InSelectableTest(fixtures.TestBase, testing.AssertsCompiledSQL):
     __dialect__ = "default"
diff --git a/test/sql/test_selectable.py b/test/sql/test_selectable.py
index b98487933..291c3f36e 100644
--- a/test/sql/test_selectable.py
+++ b/test/sql/test_selectable.py
@@ -3013,7 +3013,7 @@ class AnnotationsTest(fixtures.TestBase):
 class ReprTest(fixtures.TestBase):
     def test_ensure_repr_elements(self):
         for obj in [
-            elements.Cast(1, 2),
+            elements.Cast(1, Integer()),
             elements.TypeClause(String()),
             elements.ColumnClause("x"),
             elements.BindParameter("q"),