Issue 97948

Summary: Query designer does not include table alias in ORDER BY clause
Product: Base Reporter: ptoye <oo>
Component: codeAssignee: AOO issues mailing list <issues>
Status: CONFIRMED --- QA Contact:
Severity: Trivial    
Priority: P3 CC: drewjensen.inbox, issues
Version: OOo 3.0   
Target Milestone: ---   
Hardware: PC   
OS: Windows XP   
Issue Type: DEFECT Latest Confirmation in: ---
Developer Difficulty: ---
Attachments:
Description Flags
Test database none

Description ptoye 2009-01-11 16:32:56 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.
Comment 1 ptoye 2009-01-11 16:34:26 UTC 
Created attachment 59296 [details]
Test database
Comment 2 drewjensen.inbox 2009-01-12 06:36:29 UTC 
@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
Comment 3 drewjensen.inbox 2009-01-12 06:37:00 UTC 
assign to developer
Comment 4 ocke.janssen 2009-01-19 11:22:00 UTC 
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.