From 3619edcb8aa3ceef2a44925b85315fc0e90c5982 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Thu, 14 Jun 2018 22:17:00 -0400 Subject: render WITH clause after INSERT for INSERT..SELECT on Oracle, MySQL Fixed INSERT FROM SELECT with CTEs for the Oracle and MySQL dialects, where the CTE was being placed above the entire statement as is typical with other databases, however Oracle and MariaDB 10.2 wants the CTE underneath the "INSERT" segment. Note that the Oracle and MySQL dialects don't yet work when a CTE is applied to a subquery inside of an UPDATE or DELETE statement, as the CTE is still applied to the top rather than inside the subquery. Also adds test suite support CTEs against backends. Change-Id: I8ac337104d5c546dd4f0cd305632ffb56ac8bf90 Fixes: #4275 Fixes: #4230 --- lib/sqlalchemy/sql/compiler.py | 9 +++++++-- 1 file changed, 7 insertions(+), 2 deletions(-) (limited to 'lib/sqlalchemy/sql') diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py index a442c65fd..0b98dc51c 100644 --- a/lib/sqlalchemy/sql/compiler.py +++ b/lib/sqlalchemy/sql/compiler.py @@ -2105,7 +2105,12 @@ class SQLCompiler(Compiled): returning_clause = None if insert_stmt.select is not None: - text += " %s" % self.process(self._insert_from_select, **kw) + select_text = self.process(self._insert_from_select, **kw) + + if self.ctes and toplevel and self.dialect.cte_follows_insert: + text += " %s%s" % (self._render_cte_clause(), select_text) + else: + text += " %s" % select_text elif not crud_params and supports_default_values: text += " DEFAULT VALUES" elif insert_stmt._has_multi_parameters: @@ -2130,7 +2135,7 @@ class SQLCompiler(Compiled): if returning_clause and not self.returning_precedes_values: text += " " + returning_clause - if self.ctes and toplevel: + if self.ctes and toplevel and not self.dialect.cte_follows_insert: text = self._render_cte_clause() + text self.stack.pop(-1) -- cgit v1.2.1