Closed
Description
Migrated issue, originally created by Julien Demoor (@jdkx)
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
Metadata
Metadata
Assignees
Labels
Type
Projects
Relationships
Development
No branches or pull requests
Activity
sqlalchemy-bot commentedon Aug 4, 2018
Michael Bayer (@zzzeek) wrote:
not sure how UPDATE..FROM interacts with correlation, it looks like the same correlation in a SELECT works
that would make this local to UPDATE..FROM and not as much the EXCEPT part of this
sqlalchemy-bot commentedon Aug 4, 2018
Michael Bayer (@zzzeek) wrote:
yeah it's the additional FROMs in the UPDATE aren't being read, should be able to link that up
the primary target of the UPDATE is correlated:
sqlalchemy-bot commentedon Aug 4, 2018
Michael Bayer (@zzzeek) wrote:
this is a major bug which I'm going to try to release in 1.2, however this is risky as applications relying upon silent failure of this will start seeing different results. usually I'd push this to 1.3 but it is pretty egregiously wrong right now.
sqlalchemy-bot commentedon Aug 4, 2018
Michael Bayer (@zzzeek) wrote:
starting at https://gerrit.sqlalchemy.org/#/c/zzzeek/sqlalchemy/+/837. if the openstack tests fail on this that will be my cue that this happens in the wild a little more than I expected.
sqlalchemy-bot commentedon Aug 5, 2018
Michael Bayer (@zzzeek) wrote:
Include UPDATE/DELETE extra_froms in correlation
Fixed bug where the multi-table support for UPDATE and DELETE statements
did not consider the additional FROM elements as targets for correlation,
when a correlated SELECT were also combined with the statement. This
change now includes that a SELECT statement in the WHERE clause for such a
statement will try to auto-correlate back to these additional tables in the
parent UPDATE/DELETE or unconditionally correlate if
:meth:
.Select.correlate
is used. Note that auto-correlation raises anerror if the SELECT statement would have no FROM clauses as a result, which
can now occur if the parent UPDATE/DELETE specifies the same tables in its
additional set of tables ; specify :meth:
.Select.correlate
explicitly toresolve.
Change-Id: Ie11eaad7e49af3f59df11691b104d6359341bdae
Fixes: #4313
→ abeea1d
sqlalchemy-bot commentedon Aug 5, 2018
Changes by Michael Bayer (@zzzeek):
sqlalchemy-bot commentedon Aug 5, 2018
Michael Bayer (@zzzeek) wrote:
Include UPDATE/DELETE extra_froms in correlation
Fixed bug where the multi-table support for UPDATE and DELETE statements
did not consider the additional FROM elements as targets for correlation,
when a correlated SELECT were also combined with the statement. This
change now includes that a SELECT statement in the WHERE clause for such a
statement will try to auto-correlate back to these additional tables in the
parent UPDATE/DELETE or unconditionally correlate if
:meth:
.Select.correlate
is used. Note that auto-correlation raises anerror if the SELECT statement would have no FROM clauses as a result, which
can now occur if the parent UPDATE/DELETE specifies the same tables in its
additional set of tables ; specify :meth:
.Select.correlate
explicitly toresolve.
Change-Id: Ie11eaad7e49af3f59df11691b104d6359341bdae
Fixes: #4313
(cherry picked from commit abeea1d)
→ 8d2d412
sqlalchemy-bot commentedon Aug 5, 2018
Julien Demoor (@jdkx) wrote:
Thank you very much!