1. Michael Bayer
  2. sqlalchemy
  3. Issues

Issues

Issue #3092 duplicate

CTEs do not work in exists statements used in delete statements

mike_solomon
created an issue

The code below :

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:

DELETE FROM foo WHERE EXISTS (SELECT first_row.elt 
FROM first_row)

I would have expected it to generate this code:

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.

Comments (2)

  1. Michael Bayer repo owner

    going to call this as a dupe of #2551 for now, I know you're on SQlite / Postgresql, SQlite seems to support PG's syntax as well: http://sqlite.org/lang_delete.html, that is, the CTEs are all pushed up to the top of the statement even if a DELETE or similar. Reopen if you disagree w/ rationale why the CTE needs to be nested within the EXISTS vs. up at the top as #2551 calls for.

  2. Log in to comment