set within_columns_clause=False for all sub-elements of select()

Fixed issue where using the feature of using a string label for ordering or
grouping described at :ref:`tutorial_order_by_label` would fail to function
correctly if used on a :class:`.CTE` construct, when the CTE were embedded
inside of an enclosing :class:`_sql.Select` statement that itself was set
up as a scalar subquery.

Fixes: #7269
Change-Id: Ied6048a1c9a622374a418230c8cfedafa8d3f87e

3 files changed, 62 insertions, 0 deletions

diff --git a/doc/build/changelog/unreleased_14/7269.rst b/doc/build/changelog/unreleased_14/7269.rst
new file mode 100644
index 000000000..6bbd12605
--- /dev/null
+++ b/doc/build/changelog/unreleased_14/7269.rst
@@ -0,0 +1,11 @@
+.. change::
+    :tags: bug, sql
+    :tickets: 7269
+
+    Fixed issue where using the feature of using a string label for ordering or
+    grouping described at :ref:`tutorial_order_by_label` would fail to function
+    correctly if used on a :class:`.CTE` construct, when the CTE were embedded
+    inside of an enclosing :class:`_sql.Select` statement that itself was set
+    up as a scalar subquery.
+
+
diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index 646b49f22..a2c4f9512 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -3216,6 +3216,9 @@ class SQLCompiler(Compiled):
         # passed in.  for ORM use this will convert from an ORM-state
         # SELECT to a regular "Core" SELECT.  other composed operations
         # such as computation of joins will be performed.
+
+        kwargs["within_columns_clause"] = False
+
         compile_state = select_stmt._compile_state_factory(
             select_stmt, self, **kwargs
         )
diff --git a/test/sql/test_cte.py b/test/sql/test_cte.py
index 2c8ef83a0..4a34a5b04 100644
--- a/test/sql/test_cte.py
+++ b/test/sql/test_cte.py
@@ -551,6 +551,54 @@ class CTETest(fixtures.TestBase, AssertsCompiledSQL):
             "SELECT cte.id, cte.manager_id, cte.id_1 FROM cte",
         )
 
+    @testing.combinations(True, False, argnames="use_object")
+    @testing.combinations("order_by", "group_by", argnames="order_by")
+    def test_order_by_group_by_label_w_scalar_subquery(
+        self, use_object, order_by
+    ):
+        """test issue #7269"""
+        t = table("test", column("a"))
+
+        b = t.c.a.label("b")
+
+        if use_object:
+            arg = b
+        else:
+            arg = "b"
+
+        if order_by == "order_by":
+            cte = select(b).order_by(arg).cte()
+        elif order_by == "group_by":
+            cte = select(b).group_by(arg).cte()
+        else:
+            assert False
+
+        stmt = select(select(cte.c.b).label("c"))
+
+        if use_object and order_by == "group_by":
+            # group_by(b) is de-references the label, due a difference in
+            # handling between coercions.GroupByImpl and coercions.OrderByImpl.
+            # "order by" makes use of the ClauseElement._order_by_label_element
+            # feature but group_by() doesn't.  it's not clear if group_by()
+            # could do the same thing order_by() does.
+            self.assert_compile(
+                stmt,
+                "WITH anon_1 AS "
+                "(SELECT test.a AS b FROM test GROUP BY test.a) "
+                "SELECT (SELECT anon_1.b FROM anon_1) AS c",
+            )
+        else:
+            self.assert_compile(
+                stmt,
+                "WITH anon_1 AS (SELECT test.a AS b FROM test %s b) "
+                "SELECT (SELECT anon_1.b FROM anon_1) AS c"
+                % ("ORDER BY" if order_by == "order_by" else "GROUP BY")
+                # prior to the fix, the use_object version came out as:
+                # "WITH anon_1 AS (SELECT test.a AS b FROM test "
+                # "ORDER BY test.a) "
+                # "SELECT (SELECT anon_1.b FROM anon_1) AS c"
+            )
+
     def test_wrecur_dupe_col_names_w_grouping(self):
         """test #6710