From 26b819e534bd51e32e31a1e3324d4a1e7b849d67 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Tue, 8 Mar 2022 13:40:12 -0500 Subject: [PATCH] pop the stack that we pushed Fixed regression caused by :ticket:`7760` where the new capabilities of :class:`.TextualSelect` were not fully implemented within the compiler properly, leading to issues with composed INSERT constructs such as "INSERT FROM SELECT" and "INSERT...ON CONFLICT" when combined with CTE and textual statements. Fixes: #7798 Change-Id: Ia2ce92507e574dd36fd26dd38ec9dd2713584467 (cherry picked from commit c36965ab211183764357456fff1640418586ed97) --- doc/build/changelog/unreleased_14/7798.rst | 9 +++++++++ lib/sqlalchemy/sql/compiler.py | 2 ++ test/dialect/postgresql/test_compiler.py | 22 ++++++++++++++++++++++ test/sql/test_cte.py | 21 +++++++++++++++++++++ 4 files changed, 54 insertions(+) create mode 100644 doc/build/changelog/unreleased_14/7798.rst diff --git a/doc/build/changelog/unreleased_14/7798.rst b/doc/build/changelog/unreleased_14/7798.rst new file mode 100644 index 0000000000..31a5bb2e42 --- /dev/null +++ b/doc/build/changelog/unreleased_14/7798.rst @@ -0,0 +1,9 @@ +.. change:: + :tags: bug, sql, regression + :tickets: 7798 + + Fixed regression caused by :ticket:`7760` where the new capabilities of + :class:`.TextualSelect` were not fully implemented within the compiler + properly, leading to issues with composed INSERT constructs such as "INSERT + FROM SELECT" and "INSERT...ON CONFLICT" when combined with CTE and textual + statements. diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py index 6be8ae281f..7780d3782a 100644 --- a/lib/sqlalchemy/sql/compiler.py +++ b/lib/sqlalchemy/sql/compiler.py @@ -1664,6 +1664,8 @@ class SQLCompiler(Compiled): nesting_level = len(self.stack) if not toplevel else None text = self._render_cte_clause(nesting_level=nesting_level) + text + self.stack.pop(-1) + return text def visit_null(self, expr, **kw): diff --git a/test/dialect/postgresql/test_compiler.py b/test/dialect/postgresql/test_compiler.py index 383a77c1d6..49ab15261e 100644 --- a/test/dialect/postgresql/test_compiler.py +++ b/test/dialect/postgresql/test_compiler.py @@ -2338,6 +2338,28 @@ class InsertOnConflictTest(fixtures.TestBase, AssertsCompiledSQL): ): meth() + def test_on_conflict_cte_plus_textual(self): + """test #7798""" + + bar = table("bar", column("id"), column("attr"), column("foo_id")) + s1 = text("SELECT bar.id, bar.attr FROM bar").columns( + bar.c.id, bar.c.attr + ) + s2 = ( + insert(bar) + .from_select(list(s1.selected_columns), s1) + .on_conflict_do_update( + index_elements=[s1.selected_columns.id], + set_={"attr": s1.selected_columns.attr}, + ) + ) + + self.assert_compile( + s2, + "INSERT INTO bar (id, attr) SELECT bar.id, bar.attr " + "FROM bar ON CONFLICT (id) DO UPDATE SET attr = bar.attr", + ) + def test_do_nothing_no_target(self): i = ( diff --git a/test/sql/test_cte.py b/test/sql/test_cte.py index 1afa5c8558..d146ae6066 100644 --- a/test/sql/test_cte.py +++ b/test/sql/test_cte.py @@ -1658,6 +1658,27 @@ class CTETest(fixtures.TestBase, AssertsCompiledSQL): "(SELECT id FROM baz)", ) + def test_textual_select_stack_correction(self): + """test #7798 , regression from #7760""" + + foo = table("foo", column("id")) + bar = table("bar", column("id"), column("attr"), column("foo_id")) + + s1 = text("SELECT id FROM foo").columns(foo.c.id) + s2 = text( + "SELECT bar.id, bar.attr FROM bar WHERE br.id IN " + "(SELECT id FROM baz)" + ).columns(bar.c.id, bar.c.attr) + s3 = bar.insert().from_select(list(s2.selected_columns), s2) + s4 = s3.add_cte(s1.cte(name="baz")) + + self.assert_compile( + s4, + "WITH baz AS (SELECT id FROM foo) INSERT INTO bar (id, attr) " + "SELECT bar.id, bar.attr FROM bar WHERE br.id IN " + "(SELECT id FROM baz)", + ) + def test_insert_uses_independent_cte(self): products = table("products", column("id"), column("price")) -- 2.47.2