I've run into the following problem a couple of times and am submitting a
patch to address it.
Both PostgreSQL and SQL Server require that any expression used in an ORDER
BY be included in the column list if the DISTINCT modifier is used. Cayenne
fails for me when I use case insensitive ordering which generates ORDER BY
UPPER(table.column) because the "UPPER(table.column)" does not appear in the
column list of the generated SELECT. I think there are probably other
circumstances where the issue would arise - such as ordering on a column in
a join'd table - e.g., SELECT a.* FROM a, b WHERE ... ORDER BY b.column.
Although the order by column is typically in the column list these two
databases require the exact expression used in the ORDER BY to appear in the
column list - i.e., SELECT DISTINCT ... UPPER(table.column) FROM table ...
ORDER BY UPPER(table.column).
The patch I have included will accumulate the expressions added by the
specified orderings and then if the DISTINCT modifier is used, it will add
those expressions to the column list in the generated SQL. This seems like
it should be harmless even to databases that don't require this. It will,
in some cases, cause the same column expression to appear twice in the
generated query. I suppose a more sophisticated approach would be to
accumulate all of the column expressions to include in the select and then
only generate a unique set so no expression is duplicated. If you agree, I
could revise and resubmit.
The patches are against the 10/9 nightly snapshot.
It would be nice if these were also applied to the stable release as a
maintenance release.
Scott Finnerty
This archive was generated by hypermail 2.0.0 : Fri Oct 10 2003 - 13:24:56 EDT