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

Activity

Show:
Chris Small
August 19, 2011, 9:34 AM

Not sure why I can't edit the issue, slight typo in the formula (table name was wrong). It should read:

<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 issue means the OrderBy (both LINQ provider and QueryOver) does not order by correctly, the order by is essentially ignored.

Julian Maughan
December 28, 2011, 6:34 AM

Chris, I've updated your description.

Alex Zaytsev
July 13, 2018, 10:16 PM

Moved here.

Assignee

Unassigned

Reporter

Chris Small

Components

Affects versions

Priority

Major
Configure