From 89661c1a218b7117c1835698dbb81836e72015ae Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Tue, 9 Nov 2021 15:02:44 -0500 Subject: 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 --- test/sql/test_cte.py | 48 ++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 48 insertions(+) (limited to 'test/sql') 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 -- cgit v1.2.1