[Sqlalchemy-tickets] Issue #4275: CTE Compiles in Incorrect Order for Oracle Insert From Select (zzzeek/sqlalchemy)

[James F. <iss...@bi...>]

New issue 4275: CTE Compiles in Incorrect Order for Oracle Insert From Select
https://bitbucket.org/zzzeek/sqlalchemy/issues/4275/cte-compiles-in-incorrect-order-for-oracle

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](https://stackoverflow.com/questions/45899044/sqlalchemy-with-clause-cte-with-insert-is-not-compiling-correctly-for-oracle).


```
#!python
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:
```
#!sql
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](https://bitbucket.org/zzzeek/sqlalchemy/src/master/test/dialect/oracle/test_compiler.py) I think the following test case would work (if I'm understanding assert_compile correctly).

```
#!python
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](https://groups.google.com/forum/#!topic/sqlalchemy/_90piNnGGwg)