Apache OpenOffice (AOO) Bugzilla – Issue 97948
Query designer does not include table alias in ORDER BY clause
Last modified: 2013-08-07 15:45:09 UTC
Queries seem to give the wrong answer when an alias name clashes with a field name (which is itself aliased). In the query SELECT "MainTable"."Id", "Rooms"."Collation" AS "LCollation", "Rooms_1"."Collation" AS "Collation" FROM "Rooms" AS "Rooms", "MainTable" AS "MainTable", "Rooms" AS "Rooms_1" WHERE "Rooms"."ID" = "MainTable"."List location" AND "MainTable"."Location" = "Rooms_1"."ID" ORDER BY "Collation" ASC it sorts by "Rooms"."Collation" and not "Rooms_1"."Collation" as required. Renaming the alias for "Rooms_1"."Collation" to "CollationA" solves the problem. This is an acceptable workaround, but as I understand it, the original query should sort as required. Or OO should give a warning at least. I'm attaching a database to show the problem. This might be tied up with issue 50175.
Created attachment 59296 [details] Test database
@ptoye - Actually the query, "Test wrong", in the bug doc is not giving erroneous results. A column used in an ORDER BY clause does not have to be part of the result set. Meaning that if an alias is created to match an existing column name in a query, and if these two identifiers can be found in multiple tables in that query, then the SQL should include the table alias in the ORDER BY clause in order to remove ambiguity. If this is not done then the parser (HSQLdb parser here) will select a suitable candidate and in this particular case that selection is not the one you wanted. OK - look at the query again: SELECT "MainTable"."Id", "Rooms"."Collation" AS "LCollation", "Rooms_1"."Collation" AS "Collation" FROM "MainTable" AS "MainTable", "Rooms" AS "Rooms", "Rooms" AS "Rooms_1" WHERE "MainTable"."List location" = "Rooms"."ID" AND "MainTable"."Location" = "Rooms_1"."ID" ORDER BY "Collation" ASC From the perspective of the query parser there are two candidates for this ORDER BY clause, Room.Collation and Room_1.Collation. The proper way to clear that up then would be to tell it which to use: ORDER BY "Room_1"."Collation" ASC The real problem here, IMO, is the fact that the Query Designer does not include the table name(alias) in the ORDER By clause - Checked this with both OO.o 3.0.1 RC1 and DEV300_m37. Changing the summary line from: Query gives wrong result when alias names clash with field names to: Query designer does not include table alias in ORDER BY clause
assign to developer
The column name used in the order by is unique and it should be 3rd one in this example. MySQL does it this way and the developer from HsqlDB says the same. So it is an issue in the hsqldb engine.