raise for same param name in expanding + non expanding

An informative error is raised if two individual :class:`.BindParameter`
objects share the same name, yet one is used within an "expanding" context
(typically an IN expression) and the other is not; mixing the same name in
these two different styles of usage is not supported and typically the
``expanding=True`` parameter should be set on the parameters that are to
receive list values outside of IN expressions (where ``expanding`` is set
by default).

Fixes: #8018
Change-Id: Ie707f29680eea16b9e421af93560ac1958e11a54

3 files changed, 39 insertions, 0 deletions

diff --git a/doc/build/changelog/unreleased_14/8018.rst b/doc/build/changelog/unreleased_14/8018.rst
new file mode 100644
index 000000000..c4aae3eea
--- /dev/null
+++ b/doc/build/changelog/unreleased_14/8018.rst
@@ -0,0 +1,11 @@
+.. change::
+    :tags: bug, sql
+    :tickets: 8018
+
+    An informative error is raised if two individual :class:`.BindParameter`
+    objects share the same name, yet one is used within an "expanding" context
+    (typically an IN expression) and the other is not; mixing the same name in
+    these two different styles of usage is not supported and typically the
+    ``expanding=True`` parameter should be set on the parameters that are to
+    receive list values outside of IN expressions (where ``expanding`` is set
+    by default).
diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index f774028f2..0eae31a1a 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -2860,6 +2860,15 @@ class SQLCompiler(Compiled):
                         "Bind parameter '%s' conflicts with "
                         "unique bind parameter of the same name" % name
                     )
+                elif existing.expanding != bindparam.expanding:
+                    raise exc.CompileError(
+                        "Can't reuse bound parameter name '%s' in both "
+                        "'expanding' (e.g. within an IN expression) and "
+                        "non-expanding contexts.  If this parameter is to "
+                        "receive a list/array value, set 'expanding=True' on "
+                        "it for expressions that aren't IN, otherwise use "
+                        "a different parameter name." % (name,)
+                    )
                 elif existing._is_crud or bindparam._is_crud:
                     if existing._is_crud and bindparam._is_crud:
                         # TODO: this condition is not well understood.
diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py
index bf31824e5..d9f137349 100644
--- a/test/sql/test_compiler.py
+++ b/test/sql/test_compiler.py
@@ -3662,6 +3662,25 @@ class BindParameterTest(AssertsCompiledSQL, fixtures.TestBase):
             s,
         )
 
+    def test_expanding_non_expanding_conflict(self):
+        """test #8018"""
+
+        s = select(
+            literal("x").in_(bindparam("q")),
+            bindparam("q"),
+        )
+
+        with expect_raises_message(
+            exc.CompileError,
+            r"Can't reuse bound parameter name 'q' in both 'expanding' "
+            r"\(e.g. within an IN expression\) and non-expanding contexts.  "
+            "If this parameter is to "
+            "receive a list/array value, set 'expanding=True' on "
+            "it for expressions that aren't IN, otherwise use "
+            "a different parameter name.",
+        ):
+            str(s)
+
     def test_unique_binds_no_clone_collision(self):
         """test #6824"""
         bp = bindparam("foo", unique=True)