CTE Compiles in Incorrect Order for Oracle Insert From Select

[sqlalchemy-bot]

Migrated issue, originally created by James Frick

Oracle 12c

cx_Oracle==6.2.1

SQLalchemy==1.2.8

Description:
Oracle CTEs used in an insert().from_select() compile in the wrong order. Oracle expects the INSERT clause to render before the WITH clause.

Example modified from here.

from sqlalchemy import Table, Column, String, MetaData, select

engine = get_oracle_engine(...)
metadata = MetaData()

foo = Table('foo', metadata, Column('name', String(1)))
baz = Table('baz', metadata, Column('name', String(1)))

cte_1 = select([foo.c.name]).cte('first_cte')
cte_2 = select([cte_1.c.name]).select_from(cte_1).cte('second_cte')
ins = baz.insert().from_select([baz.c.name], cte_2)
metadata.drop_all(clarity.engine)
metadata.create_all(clarity.engine)
print(ins.compile(engine))
engine.execute(ins)

Out:

WITH first_cte AS
(SELECT foo.name AS name
FROM foo)
 INSERT INTO baz (name) SELECT first_cte.name
FROM first_cte

Trace:

Traceback (most recent call last):
  File "*******\lib\site-packages\sqlalchemy\engine\base.py", line 1193, in _execute_context
    context)
  File "*******\lib\site-packages\sqlalchemy\engine\default.py", line 508, in do_execute
    cursor.execute(statement, parameters)
cx_Oracle.DatabaseError: ORA-00928: missing SELECT keyword

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "minimum_example.py", line 28, in <module>
    engine.execute(ins)
  File "*******\lib\site-packages\sqlalchemy\engine\base.p
y", line 2075, in execute
    return connection.execute(statement, *multiparams, **params)
  File "*******\lib\site-packages\sqlalchemy\engine\base.p
y", line 948, in execute
    return meth(self, multiparams, params)
  File "*******\lib\site-packages\sqlalchemy\sql\elements.py", line 269, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "*******\lib\site-packages\sqlalchemy\engine\base.py", line 1060, in _execute_clauseelement
    compiled_sql, distilled_params
  File "*******\lib\site-packages\sqlalchemy\engine\base.py", line 1200, in _execute_context
    context)
  File "*******\lib\site-packages\sqlalchemy\engine\base.py", line 1413, in _handle_dbapi_exception
    exc_info
  File "*******\lib\site-packages\sqlalchemy\util\compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "*******\lib\site-packages\sqlalchemy\util\compat.py", line 186, in reraise
    raise value.with_traceback(tb)
  File "*******\lib\site-packages\sqlalchemy\engine\base.py", line 1193, in _execute_context
    context)
  File "*******\lib\site-packages\sqlalchemy\engine\default.py", line 508, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) ORA-00928: missing SELECT keyword [SQL: 'WITH first_cte AS \n(SELECT foo.name AS name \nFROM foo)\n INSERT INTO baz (name) SELECT first_cte.name \nFROM first_cte'] (Background on this error at: http://sqlalche.me/e/4xp6)

Following the examples in test_compiler I think the following test case would work (if I'm understanding assert_compile correctly).

t = table('foo', column('name'))
cte_1 = select([t.c.name]).cte('first_cte')
cte_2 = select([cte_1.c.name]).select_from(cte_1).cte('second_cte')
ins = t.insert().from_select([cte_2.c.name], cte_2)

self.assert_compile(ins,
                    'INSERT INTO foo (name) '
                    'WITH first_cte AS '
                    '(SELECT foo.name AS name '
                    'FROM foo) '
                    'SELECT first_cte.name '
                    'FROM first_cte ')

Posted to the mailing list here

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

so for testing, do you know if Oracle supports CTE for UPDATE and DELETE also? most of the work here is building out the test suite to start exercising so-called "Backend" cte expressions against different databases.

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• removed labels: low priority

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• set milestone to "1.2.x"

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

(perfect bug report btw! :) )

[sqlalchemy-bot]

James Frick wrote:

Update syntax is the same:
https://stackoverflow.com/questions/40341125/cte-and-table-update-in-oracle

Delete syntax is similar, though the only way I could get a working example is with an ANY or EXISTS.
https://www.w3resource.com/sql/delete-statement/delete-with-subqueries.php

delete from foo
where col_a = any(
with cte1 as (select col_a from foo)
select distinct col_a from cte1
)

That would delete all rows successfully. Tested in Oracle 12c.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

OK I may need to break out the testing requirements markers a bit for that....this is all on my end

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

OK that UPDATE syntax is not the full blown CTE UPDATE postgresql supports, as that requires UPDATE..FROM. So I need to test that separately

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

OK....in order to move forward we're going to get the test support in, which is also #4230, and get INSERT ...WITH..SELECT working. the embedding of the WITH inside the subquery is a bigger issue that I'm going to leave out for now (doesn't work like that, CTE always goes to the top).

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

https://gerrit.sqlalchemy.org/#/q/I8ac337104d5c546dd4f0cd305632ffb56ac8bf90

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

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

→ 3619edc

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• changed status to closed

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

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
(cherry picked from commit 3619edc)

→ 6529c47