Skip to content

Second SELECT not correlated in SELECT ... EXCEPT ... subqueries #4313

Closed
@sqlalchemy-bot

Description

@sqlalchemy-bot
Collaborator

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

Activity

sqlalchemy-bot

sqlalchemy-bot commented on Aug 4, 2018

@sqlalchemy-bot
CollaboratorAuthor

Michael Bayer (@zzzeek) wrote:

not sure how UPDATE..FROM interacts with correlation, it looks like the same correlation in a SELECT works

stmt = select([a]).where(a.c.name == b.c.name & exists(exists_select))

SELECT a.id, a.name 
FROM a, b 
WHERE a.name = (b.name AND (EXISTS (SELECT a.name EXCEPT SELECT b.name)))

that would make this local to UPDATE..FROM and not as much the EXCEPT part of this

sqlalchemy-bot

sqlalchemy-bot commented on Aug 4, 2018

@sqlalchemy-bot
CollaboratorAuthor

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:

stmt = update(b,
              values={a.c.name: b.c.name},
              whereclause=(b.c.id == a.c.id) & exists(exists_select))

UPDATE b SET name=b.name FROM a WHERE b.id = a.id AND (EXISTS (SELECT a.name 
FROM a EXCEPT SELECT b.name))

sqlalchemy-bot

sqlalchemy-bot commented on Aug 4, 2018

@sqlalchemy-bot
CollaboratorAuthor

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

sqlalchemy-bot commented on Aug 4, 2018

@sqlalchemy-bot
CollaboratorAuthor

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

sqlalchemy-bot commented on Aug 5, 2018

@sqlalchemy-bot
CollaboratorAuthor

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 an
error 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 to
resolve.

Change-Id: Ie11eaad7e49af3f59df11691b104d6359341bdae
Fixes: #4313

abeea1d

sqlalchemy-bot

sqlalchemy-bot commented on Aug 5, 2018

@sqlalchemy-bot
CollaboratorAuthor

Changes by Michael Bayer (@zzzeek):

  • changed status to closed
sqlalchemy-bot

sqlalchemy-bot commented on Aug 5, 2018

@sqlalchemy-bot
CollaboratorAuthor

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 an
error 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 to
resolve.

Change-Id: Ie11eaad7e49af3f59df11691b104d6359341bdae
Fixes: #4313
(cherry picked from commit abeea1d)

8d2d412

sqlalchemy-bot

sqlalchemy-bot commented on Aug 5, 2018

@sqlalchemy-bot
CollaboratorAuthor

Julien Demoor (@jdkx) wrote:

Thank you very much!

added this to the 1.2.x milestone on Nov 27, 2018
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    bugSomething isn't workingsql

    Type

    No type

    Projects

    No projects

    Relationships

    None yet

      Development

      No branches or pull requests

        Participants

        @sqlalchemy-bot

        Issue actions

          Second SELECT not correlated in SELECT ... EXCEPT ... subqueries · Issue #4313 · sqlalchemy/sqlalchemy