| 2010-11-30 08:13:49 |
Jeroen T. Vermeulen |
description |
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! |
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(Table, Table.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!
|
|
