Apache OpenOffice (AOO) Bugzilla – Full Text Issue Listing
|Summary:||Cannot see/remove query sorts from "order by" statement in query view|
|Component:||code||Assignee:||AOO issues mailing list <issues>|
|Status:||REOPENED ---||QA Contact:|
|Issue Type:||DEFECT||Latest Confirmation in:||---|
Description ameliab 2010-08-29 22:36:24 UTC
When viewing the results of a query, any sort order created in the query definition is applied "invisibly" -- it does not show up in the sort order dialog, you cannot remove it, and if you sort by any other column the new sort is added as a secondary sort (i.e., it often doesn't appear to do anything). The only sort that actually works as expected is if you reverse the original sort. Otherwise, it just appears to a user that the sort function doesn't work. It doesn't matter whether the "order by" function was added through the OpenOffice query design or by direct SQL to the underlying database (embedded HSQL in my case). The same problem does not exist with table views that contain "order by" statements -- you can sort the results normally when viewing data.
Comment 1 ameliab 2010-08-29 23:29:20 UTC
Clarification: When viewing the results of a query that uses direct SQL (i.e., the "run SQL command directly" button is checked), neither sort *nor* filter commands work. This is true even if there is no "order by" clause or conditions on the query. Again, there is no feedback to the user that these options aren't available -- you can press the buttons and fill in the dialog boxes, but nothing happens. This may be a separate issue from the above issue with "order by", but it seems related.
Comment 2 ameliab 2010-08-29 23:47:30 UTC
Created attachment 71467 [details] Test DB created when exploring this issue and issue 114188
Comment 3 ameliab 2010-08-30 00:57:50 UTC
Explanation of the test database: The same test database is relevant for issue 114187 (re-sorting sorted queries), issue 114188 (syntax errors in queries that reference a sorted query) and issue 114189 (Base crashes when using filters on columns created by correlated subqueries). (A) There is one original table, named "Numbers". It consists of: "PrimaryKey", an auto-generated primary key, and "RandNumber", a series of random numbers 0 <= "RandNumber" < 1 (B) There are three different versions of queries that operate directly on this table: "Query_OOSQL" runs through the OpenOffice SQL parser "Query_DirectSQL" has the "run SQL command directly" button selected "View" is a table view copy of Query_DirectSQL All three create three columns: "ID", the primary key repeated, "MoreThanHalf", a boolean based on whether or not "RandNumber" is greater than 0.5 (Side note -- I had to use some math and data type casting to recreate this result with the OpenOffice SQL parser, which doesn't accept a simple boolean statement as a column definition. Annoying.) and "ThrownNum" which uses a correlated subquery to determine the rank of this record when the records are sorted by primary key (i.e., counts the number of records with a smaller or equal primary key). These are the queries that are relevant to issue 114189 -- arguably the most important issue, since its the one that results in crashes (although probably fewer people are using correlated subqueries than are sorting queries!) Applying a filter to the results of "Query_OOSQL" causes a crash. Applying a filter or sort to the results of "Query_DirectSQL" causes a crash or hang. No problems when using the view. (C) Then, there are a series of queries and one view named "HeadsOrTails" etc., which basically regenerate the above results with different column alias, and various sort options. These are the queries that are relevant to (this) issue 114187, about being unable to sort already sorted results. Note that sorting/filtering these queries doesn't cause crashing, even though they are actually submitting a query with a subquery with a nested correlated subquery to the database. (The exception is the DirectSQL query, which of course can't reference another query, and so is based on the "View" defined above.) "HeadsOrTails-HeadsSort" is a query ordered by the boolean column "Heads". When viewing the results, sorting by the count column "ThrowNum" adds a secondary sort. Filtering works properly. "HeadsOrTails-ThrowSort" is a query ordered by the count column "ThrowNum". Trying to sort the results based on "Heads" appears to have no effect, since "ThrowNum" values are all unique. Again, filtering works properly. "HeadsOrTails-NoSort" is the same query without an ordered by clause. It can be sorted and filtered no problem. "HeadsOrTails-NoSort-DirectSQL" is an unsorted query, but run directly by the embedded database instead of using the OpenOffice SQL parser. Results cannot be sorted or filtered. "View_HeadsOrTails" is a sorted table view. Data can be re-sorted and filtered no problem. (D) The final set of queries filter the HeadsOrTails queries to only list those where "Heads" is true. These are the queries relevant to issue 114188 -- syntax errors when OpenOffice tries to use a sorted query as a subquery. "HeadsOnly-UsingUnsortedQuery" is defined based on the "HeadsOrTails-NoSort" query. It works fine. "HeadsOnly-UsingSortedQuery" is the same, but it is based on the "HeadsOrTails-HeadsSort" query. OpenOffice parses the query no problem, you can edit it in design mode, but when you actually try to view data the embedded database issues an error. "HeadsOnly-UsingView" is again the same idea, but based on the (sorted) "View_HeadsOrTails". It works fine. All in all, a couple examples of unexpected behaviour and one dangerous crash when using queries. I think I'll be using mostly views from now on, but that's not always an option when using an external database!
Comment 4 ameliab 2010-08-30 01:07:23 UTC
Re-reading this sentence I realize it isn't very clear without actually opening the database: "Then, there are a series of queries and one view named "HeadsOrTails" etc., which basically regenerate the above results with different column alias, and various sort options." What I meant is that the "HeadsOrTails" queries are created in design view by selecting the columns from the first set of queries / views. "ThrowNum" is selected and "MoreThanHalf" is selected and aliased as "Heads". No functions or new subqueries are defined, although OpenOffice of course creates a nested sub-query by substituting one query into the FROM clause of the other. But that doesn't seem to cause problems.
Comment 5 drewjensen.inbox 2010-08-30 01:44:35 UTC
Confirmed w/ go-00 3.2.1 Windows and Linux
Comment 6 Frank Schönheit 2010-09-02 06:33:31 UTC
cite: When viewing the results of a query that uses direct SQL (i.e., the "run SQL command directly" button is checked), neither sort *nor* filter commands work. This is true even if there is no "order by" clause or conditions on the query. Again, there is no feedback to the user that these options aren't available -- you can press the buttons and fill in the dialog boxes, but nothing happens." Do you say that the filter/sort buttons are enabled when viewing the "*_DirectSQL" queries? Or are those two paragraphs unrelated? (Here, the buttons are disabled, which is the expected situation for a Direct SQL query.)
Comment 7 Frank Schönheit 2010-09-02 06:40:26 UTC
So basically this issue boils down to: If you have a query containing an ORDER BY clause, say, SELECT * FROM <table> ORDER BY <column> , and display this query's data, then - the sort order dialog doesn't give you any clue that there already is a sort order applied - applying sort orders via the dialog might lead to confusing results, since the new explicit sort order is subordinate to the existing, implicit sort order, which the user might not know about I agree that this is a usability problem, though I am not sure how one would solve this. Giving the user control over the existing implicit sort orders is non-trivial, UX-wise, since then the "Remove Filter/Sort" button would need attention then (is this expected to remove only the implicit, or the implicit and the explicit sort orders?). Also, the sort order dialog would need an extension then, so it is not limited to three entries anymore (this would be a Good Thing (TM), anyway).
Comment 8 ameliab 2010-09-02 18:26:28 UTC
quote: Do you say that the filter/sort buttons are enabled when viewing the "*_DirectSQL" queries? Or are those two paragraphs unrelated? As atjensen noted in a comment that unfortunately got appended to the wrong issue, the buttons are disabled if you run the query while in edit mode, but not if you just open the saved query. In that case, the buttons and dialogs can be used, they just don't have any effect. Although it would be ideal in the long wrong to enable sorts and filters on these results, in the short term at least it would be expected to have these buttons disabled if they aren't going to do anything. Similarly, in the case of queries run through the OO SQL parser, I would suggest that even if the user cannot *change* the original sort or filter stored in the query, they should be able to at least have some feedback to know what is going on.
Comment 9 ameliab 2010-09-02 18:35:21 UTC
"in the long *term*" that should read. Not sure how Freud would interpret that slip! As for the long term, my first instinct is that it shouldn't be too complicated to implement it. Instead of sending a revised version of the original query (which OO wouldn't be able to do, since it isn't parsing the original query), OO could create a new query that selected from the original as a subquery -- SELECT * FROM (original query) WHERE (new filter conditions) ORDER BY (new sort order). You wouldn't be able to remove existing filter conditions, only filter amongst the results you have, but I think that is a reasonably intuitive behaviour. There's just one difficulty -- issue 114188 , at least when dealing with the embedded DB.
Comment 10 Frank Schönheit 2010-09-03 12:37:58 UTC
ah, I used the wrong version! In fact, in OOo 3.2.1, for both "HeadsOrTails-NoSort-DirectSQL" and "Query_DirectSQL", the filter/sort buttons are enabled when opening the queries by double-clicking them. In OOo 3.3 Beta 1, this is fixed - so seems this issue is gone meanwhile.
Comment 11 ocke.janssen 2010-11-24 10:08:39 UTC
@ameliab: Could you please check if this work for you in OOo 3.3 RCx. Thanks. Feel free to reopen this issue.
Comment 12 Frank Schönheit 2010-11-26 12:11:28 UTC
Yes, it still happens. See http://www.openoffice.org/issues/show_bug.cgi?id=114187#desc8.