Missing FROM-clause when iterating over a difference ResultSet

[Michael Nelson]

Here's a demo:
{{{
>>> from storm.store import Store
>>> cprov = getUtility(IPersonSet).getByName('cprov')
>>> store = Store.of(cprov)
>>> from lp.registry.model.person import Person
>>> all_people = store.find(Person)
>>> all_people.count()
93
>>> cprov_resultset = store.find(Person, Person.name == 'cprov')
>>> cprov_resultset.count()
1
>>> diff = all_people.difference(cprov_resultset)
>>> diff.count()
92

>>> count = 0
>>> for person in diff:
... count += 1
...
Traceback (most recent call last):
  File "<stdin>", line 1, in ?
  File "/home/michael/canonical/lp-branches/p3a-subscription-ui-changes/utilities/../lib/storm/store.py", line 956, in __iter__
  File "/home/michael/canonical/lp-branches/p3a-subscription-ui-changes/utilities/../lib/storm/databases/postgres.py", line 257, in execute
  File "/home/michael/canonical/lp-branches/p3a-subscription-ui-changes/utilities/../lib/storm/database.py", line 200, in execute
  File "/home/michael/canonical/lp-branches/p3a-subscription-ui-changes/utilities/../lib/storm/databases/postgres.py", line 267, in raw_execute
  File "/home/michael/canonical/lp-branches/p3a-subscription-ui-changes/utilities/../lib/storm/database.py", line 281, in raw_execute
  File "/home/michael/canonical/lp-branches/p3a-subscription-ui-changes/utilities/../lib/storm/database.py", line 322, in _check_disconnect
psycopg2.ProgrammingError: missing FROM-clause entry for table "person"
LINE 1: ..., Person.name))) AS "_2" ORDER BY person_sort_key(Person.dis...
}}}

Thanks to therve for the workaround: do diff._order_by = Undef before the iteration and all is fine.

[James Henstridge (jamesh)]

So there are two things in play here:

 1. The table class in question has a default ordering set using an SQL() constant expression that references columns in "table.column" form.

 2. When using the SQLObject compatibility layer, the set expression methods on SQLObjectResultSet default to no ordering on the new result set, even if the table has a default order.

When moving to the native storm ResultSet APIs, the default order set on the table is used for the new result set and we get breakage because "Person" is not bound to anything at that level.

As the code in question is using SQL() here, there isn't much chance of us doing something smart here. That said, I don't think we would handle this any better if the default order was a simple list of column objects. Are there any cases where we successfully apply an order to set expressions?

[Michael Nelson (michael.nelson)]

Hi, I hit this again recently when actually wanted sorted results after a union.

I have one previous example where this is actually working - but the difference is that in that previous example the find_spec specifies just one table, whereas the current situation that I have returns a tuple of three classes/tables. Why does that affect things? Well, it seems that storm wraps union queries (at least this one) with a further 'select * from (...)':

{{{
EXECUTE: 'SELECT * FROM ((SELECT SourcePackagePublishingHistory.archive, ...) UNION (SELECT DISTINCT SourcePackagePublishingHistory.archive, ....)) AS "_10"
}}}

This means that it is not possible for the SQL order by to specify table names or else face:

{{{
ProgrammingError: missing FROM-clause entry for table "sourcepackagepublishinghistory"
LINE 1: ... = Build.id ORDER BY Build.id)) AS "_16" ORDER BY SourcePack...
}}}

An easy work-around when the column name is unique across the returned columns is to not specify the table name ('ORDER BY architecturetag'), but otherwise, doing something like 'ORDER BY id' when you're returning multiple id columns for multiple classes/tables, results in

{{{
ProgrammingError: ORDER BY "id" is ambiguous
LINE 1: ...story.archive = Build.archive)) AS "_20" ORDER BY id, archit...
}}}

The only work-around I've found so far is to order by a const int corresponding to the column index in the results (well, the only work-around that still uses the storm orm for the query - I guess I could also do the SQL union manually and then just fetch the results using storm etc.)

[Michael Nelson (michael.nelson)]

Actually, just to clarify, I've just tried the SQL and the issue in the previous comment exists whether or not the union is wrapped in the `select * from (...)` - it's apparently that when ordering a union the sql no longer has any notion of the original tables (it just seems that it should from my specific query where I'm unioning two identical sets of columns, but in the general case it doesn't make sense). Sorry for the noise.