DISTINCT ON ignored for Union

[Gavin Panella]

A DISTINCT clause does not appear in the query that is executed by the
following (contrived) query:

  union = store.find(Distribution).union(
      store.find(Distribution), all=True)
  union.config(distinct=(Distribution.name,))
  list(union)

A DISTINCT clause does not appear if config(distinct=True) is used
either, although a straight UNION (i.e. without ALL) has the same
effect I think. However the newish DISTINCT ON syntax in PostgreSQL
has different properties.

The query should look something like:

  SELECT DISTINCT ON (_1.name) * FROM (
    (SELECT ... FROM Distribution)
  UNION ALL
    (SELECT ... FROM Distribution)
  ) AS "_1" ORDER BY name

[Jeroen T. Vermeulen (jtv)]

In postgres at least, a regular UNION implies a full DISTINCT (which the ALL option then removes). It removes duplicate rows within one member of the union as well as duplicates across the member queries.

So from Storm's perspective, this looks like an omission in the implementation of "DISTINCT ON" support. It might make more sense to deprecate the "all" parameter to the union method, and use distinct=False instead. The only wart on that is that a union would have a different default than a regular select.