[Sqlalchemy-tickets] Issue #3092: CTEs do not work in exists statements used in delete statements (zzzeek/sqlalchemy)

[mike_solomon <iss...@bi...>]

New issue 3092: CTEs do not work in exists statements used in delete statements
https://bitbucket.org/zzzeek/sqlalchemy/issue/3092/ctes-do-not-work-in-exists-statements-used

mike_solomon:

The code below :


```
#!python

from sqlalchemy import Table, Column, Integer, MetaData, select, and_, exists

metadata = MetaData()
foo = Table('foo', metadata,
                   Column('id', Integer, primary_key=True),
                   Column('val', Integer))

bar = Table('bar', metadata,
                   Column('id', Integer, primary_key=True),
                   Column('val', Integer))


first = select([bar.c.id.label('elt')]).where(bar.c.id == 0).\
          cte(name = 'first_row', recursive = True)
grow_me = first.alias(name = 'grow_me')
stmt = first.union_all(select([bar.c.val.label('next')]).\
     where(and_(grow_me.c.elt == bar.c.id,
                grow_me.c.elt != 1)))

print foo.delete().where(exists(select([stmt])))
```

generates the result below:


```
#!sql

DELETE FROM foo WHERE EXISTS (SELECT first_row.elt 
FROM first_row)
```
I would have expected it to generate this code:

```
#!sql

DELETE FROM foo WHERE EXISTS (
WITH RECURSIVE first_row(elt) AS 
(SELECT bar.id AS elt 
FROM bar 
WHERE bar.id = :id_1 UNION ALL SELECT bar.val AS next 
FROM bar, first_row AS grow_me 
WHERE grow_me.elt = bar.id AND grow_me.elt != :elt_1)
 SELECT first_row.elt 
FROM first_row)
```

The code above is legal SQL.  As a workaround for my project, I've created a simple string formatter that will insert the SELECT statement into the EXISTS statement, but it would be great if this could be done automatically.