Comment 0 for bug 682989

We've got an important query in Launchpad doing a more extensive version of:

Foo = ClassAlias(Table, "Foo")
Bar = ClassAlias(Table, "Bar")
bar_ids = [1, 2, 3]
selection = Select(
    Foo.id,
    tables=[Foo, Bar],
    where=And(
        Foo.x == Bar.x,
        Bar.id.is_in(bar_ids),
        Foo.y == None,
        Bar.y == None
    ))
store.find(Foo, Foo.id.is_in(selection)).set(attribute=value)

This results in SQL of the form:

UPDATE Table
SET attribute = value
WHERE Table.id IN (
    SELECT Foo.id
    FROM Table AS Foo, Table AS Bar
    WHERE
        Foo.x = Bar.x AND
        Bar.id IN (1, 2, 3) AND
        Table.y IS NULL AND
        Table.y IS NULL
);

The conditions "Foo.y == None" and "Bar.y == None" are both translated as "Table.y IS NULL"—which refers to the row being updated, not the rows referenced in the subquery!

For Foo.y this is not a problem in this case, since id is a key and so the match on "Table.id IN (SELECT Foo.id)" guarantees that Table and Foo are actually the same row. But x is not a key (actually we have a more complex join condition here) and so I can't say the same for Bar.y!