Ordering by a formula column, redeclares the formula

Description

(SQL Server 2008 - SQL2005 Dialect)

If you declare a nullable DateTime property as a formula:

<property name="CompletionDate" formula="(SELECT TOP 1 events.eventDate FROM events WHERE events.docId=ID AND events.eventType=2 ORDER BY events.eventDate DESC)" type="System.Nullable`1[[System.DateTime, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]], mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />

This is correctly declared as sub-select formula_0_4 in the SQL. However if you order by this column, the formula is redeclared in the ORDER BY statement, e.g.

...ORDER BY (SELECT TOP 1 events.eventDate FROM history_items WHERE events.docId=ID AND events.eventType=2 ORDER BY events.eventDate DESC)

This works correctly for simple statements, however isn't correct for larger joins. It should be re-using the formula alias it originally declared for the column alias, e.g.:

...ORDER BY formula_0_4

Environment

None

Assignee

Unassigned

Reporter

Chris Small

Components

Affects versions

Priority

Major
Configure