From 42710c9220f897487710424981b81a69a7da5def Mon Sep 17 00:00:00 2001
From: Mike Bayer <mike_mp@zzzcomputing.com>
Date: Tue, 18 Nov 2025 15:10:05 -0500
Subject: [PATCH] propagate _scalar_type() for SelectStatementGrouping

Fixed issue where using the :meth:`.ColumnOperators.in_` operator with a
nested :class:`.CompoundSelect` statement (e.g. an ``INTERSECT`` of
``UNION`` queries) would raise a :class:`NotImplementedError` when the
nested compound select was the first argument to the outer compound select.
The ``_scalar_type()`` internal method now properly handles nested compound
selects.

Fixes: #12987
Change-Id: I6aa1b38863588d371bbac74b3531b99ccd5fcaec
---
 doc/build/changelog/unreleased_20/12987.rst | 10 +++++
 lib/sqlalchemy/sql/selectable.py            |  3 ++
 test/sql/test_operators.py                  | 46 +++++++++++++++++++++
 3 files changed, 59 insertions(+)
 create mode 100644 doc/build/changelog/unreleased_20/12987.rst

diff --git a/doc/build/changelog/unreleased_20/12987.rst b/doc/build/changelog/unreleased_20/12987.rst
new file mode 100644
index 0000000000..ef061015b2
--- /dev/null
+++ b/doc/build/changelog/unreleased_20/12987.rst
@@ -0,0 +1,10 @@
+.. change::
+    :tags: bug, sql
+    :tickets: 12987
+
+    Fixed issue where using the :meth:`.ColumnOperators.in_` operator with a
+    nested :class:`.CompoundSelect` statement (e.g. an ``INTERSECT`` of
+    ``UNION`` queries) would raise a :class:`NotImplementedError` when the
+    nested compound select was the first argument to the outer compound select.
+    The ``_scalar_type()`` internal method now properly handles nested compound
+    selects.
diff --git a/lib/sqlalchemy/sql/selectable.py b/lib/sqlalchemy/sql/selectable.py
index bf60995245..b8ca7a5659 100644
--- a/lib/sqlalchemy/sql/selectable.py
+++ b/lib/sqlalchemy/sql/selectable.py
@@ -4007,6 +4007,9 @@ class SelectStatementGrouping(GroupedElement, SelectBase, Generic[_SB]):
     def _from_objects(self) -> List[FromClause]:
         return self.element._from_objects
 
+    def _scalar_type(self) -> TypeEngine[Any]:
+        return self.element._scalar_type()
+
     def add_cte(self, *ctes: CTE, nest_here: bool = False) -> Self:
         # SelectStatementGrouping not generative: has no attribute '_generate'
         raise NotImplementedError
diff --git a/test/sql/test_operators.py b/test/sql/test_operators.py
index 007563f367..908c6018e4 100644
--- a/test/sql/test_operators.py
+++ b/test/sql/test_operators.py
@@ -18,6 +18,7 @@ from sqlalchemy import Enum
 from sqlalchemy import exc
 from sqlalchemy import Float
 from sqlalchemy import Integer
+from sqlalchemy import intersect
 from sqlalchemy import join
 from sqlalchemy import LargeBinary
 from sqlalchemy import literal_column
@@ -2546,6 +2547,51 @@ class InTest(fixtures.TestBase, testing.AssertsCompiledSQL):
                 literal_binds=True,
             )
 
+    def test_in_scalar_grouping(self):
+        """test for :ticket:`12987`
+
+        Test that using in_() with a nested CompoundSelect works correctly.
+        This occurs when a CompoundSelect is the first argument to another
+        CompoundSelect.
+
+        """
+
+        t = self.table1
+
+        # Create nested compound selects
+        inner_compound_stmt = union(
+            select(t.c.myid).where(t.c.myid == 5),
+            select(t.c.myid).where(t.c.myid == 6),
+        )
+        simple_stmt = select(t.c.myid).where(t.c.myid == 7)
+
+        # When simple statement is first, should work
+        outer_compound_stmt = intersect(simple_stmt, inner_compound_stmt)
+        self.assert_compile(
+            select(t).where(t.c.myid.in_(outer_compound_stmt)),
+            "SELECT mytable.myid FROM mytable "
+            "WHERE mytable.myid IN ("
+            "SELECT mytable.myid FROM mytable WHERE mytable.myid = :myid_1 "
+            "INTERSECT (SELECT mytable.myid FROM mytable "
+            "WHERE mytable.myid = :myid_2 "
+            "UNION SELECT mytable.myid FROM mytable "
+            "WHERE mytable.myid = :myid_3))",
+        )
+
+        # When compound statement is first, previously raised
+        # NotImplementedError
+        outer_compound_stmt = intersect(inner_compound_stmt, simple_stmt)
+        self.assert_compile(
+            select(t).where(t.c.myid.in_(outer_compound_stmt)),
+            "SELECT mytable.myid FROM mytable "
+            "WHERE mytable.myid IN ("
+            "(SELECT mytable.myid FROM mytable WHERE mytable.myid = :myid_1 "
+            "UNION SELECT mytable.myid FROM mytable "
+            "WHERE mytable.myid = :myid_2) "
+            "INTERSECT SELECT mytable.myid FROM mytable "
+            "WHERE mytable.myid = :myid_3)",
+        )
+
     @testing.combinations(True, False, argnames="is_in")
     @testing.combinations(True, False, argnames="negate")
     def test_in_empty_tuple(self, is_in, negate):
-- 
2.47.3