[Sqlalchemy-tickets] Issue #3335: ordering by a label present in an `alias` will create an invalid
Brought to you by:
zzzeek
|
From: jvanasco <iss...@bi...> - 2015-03-20 23:18:23
|
New issue 3335: ordering by a label present in an `alias` will create an invalid query https://bitbucket.org/zzzeek/sqlalchemy/issue/3335/ordering-by-a-label-present-in-an-alias jvanasco: this is a new issue with 1.0.0b2 and Postgres. i am not sure if other databases will fail. this is a bit of an confusing to describe, so bear with me. i made an example, but it is confusing too! https://gist.github.com/jvanasco/986c30d91095cef5526f the issue is in the interplay of aliased columns and an "ORDER BY" If the ordering column's name is the same as a column name in the aliased "select", then an ordering will be applied to the column in the aliased select -- which breaks a GROUP BY. if the column name is not in the aliased select, then the ordering will be generated as a bareword -- which is the valid query. * query_form_a this worked in 0.9.9, it does not work in 1.0.0b2 line 100 applies a 'event_timestamp' label over the 'event_timestamp' column. line 110 sorts on 'event_timestamp' This will output 0.9.9 `ORDER BY event_timestamp DESC` 1.0.0b2 `ORDER BY q_stream.event_timestamp DESC` * query form b this did not work in 0.9.9 and does not work in 1.0.0b2 just shown for comparison line 137 we select an 'event_timestamp' column line 147 sorts on 'event_timestamp' * query form c this works on 0.9.9 and 1.0.0b2 however it requires a column renaming line 174 we select the 'event_timestamp' column and rename it 'timestamp_renamed' line 184 sorts on 'timestamp_renamed' This will output 0.9.9 `ORDER BY timestamp_renamed DESC` 1.0.0b2 `ORDER BY timestamp_renamed DESC` note: i used the following sqlalchemy connection string so it would hit the DB and raise the query exceptions for postgres. i found that easier than inspecting the sql in a memory engine. user - sqlalchemy_test password - sqla database - sqlalchemy_test |