[Sqlalchemy-tickets] Issue #4313: Second SELECT not correlated in SELECT ... EXCEPT ... subqueries (zzzeek/sqlalchemy)

[Julien D. <iss...@bi...>]

New issue 4313: Second SELECT not correlated in SELECT ... EXCEPT ... subqueries
https://bitbucket.org/zzzeek/sqlalchemy/issues/4313/second-select-not-correlated-in-select

Julien Demoor:

I'm trying to prevent updates to unchanged rows in large UPDATE statements using the following construct:

```
UPDATE a SET a.xxx = b.xxx FROM b WHERE a.id = b.id AND EXISTS (
   SELECT a.xxx EXCEPT b.xxx
);

```


It seems impossible to properly generate such queries with SQLAlchemy using the ``correlate()`` method, which I would expect to work. Code below.

```
from sqlalchemy import Table, MetaData, create_engine, Column, Integer, Unicode, update, select, exists

e = create_engine('postgres:///update_correlation')
c = e.connect()
trans = c.begin()
meta = MetaData(bind=c)
a = Table('a', meta,
          Column('id', Integer, primary_key=True),
          Column('name', Unicode))
b = Table('b', meta,
          Column('id', Integer, primary_key=True),
          Column('name', Unicode))
meta.create_all()
c.execute("insert into a (name) values ('foo');")

# No correlation: both SELECTs in the SELECT ... EXCEPT ... construct have a FROM clause
exists_select = select([a.c.name]).except_(select([b.c.name]))
stmt = update(a,
              values={a.c.name: b.c.name},
              whereclause=(b.c.id==a.c.id) & exists(exists_select))
print stmt

# Only the first SELECT is correlated, both should be
exists_select = select([a.c.name]).correlate(a).except_(select([b.c.name]).correlate(b))
stmt = update(a,
              values={a.c.name: b.c.name},
              whereclause=(b.c.id==a.c.id) & exists(exists_select))
print stmt

# Ugly workaround
def _ugly_hack(*args, **kwargs):
	return []
except_select = select([b.c.name])
except_select._get_display_froms = _ugly_hack
exists_select = select([a.c.name]).correlate(a).except_(except_select)
stmt = update(a,
              values={a.c.name: b.c.name},
              whereclause=(b.c.id==a.c.id) & exists(exists_select))
print stmt
```