- changed status to duplicate
CTEs do not work in exists statements used in delete statements
Issue #3092
duplicate
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)
-
repo owner -
repo owner going to call this as a dupe of
#2551for 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#2551calls for. - Log in to comment
Duplicate of
#2551.