To comment on the following update, log in, then open the issue:
http://www.openoffice.org/issues/show_bug.cgi?id=97948
User atjensen changed the following:
What |Old value |New value
================================================================================
CC|'' |'atjensen'
--------------------------------------------------------------------------------
Ever confirmed| |1
--------------------------------------------------------------------------------
Status|UNCONFIRMED |NEW
--------------------------------------------------------------------------------
Summary|Query gives wrong result w|Query designer does not in
|hen alias names clash with|clude table alias in ORDER
| field names | BY clause
--------------------------------------------------------------------------------
------- Additional comments from [email protected] Mon Jan 12 06:36:29
+0000 2009 -------
@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
---------------------------------------------------------------------
Please do not reply to this automatically generated notification from
Issue Tracker. Please log onto the website and enter your comments.
http://qa.openoffice.org/issue_handling/project_issues.html#notification
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
