To comment on the following update, log in, then open the issue: http://www.openoffice.org/issues/show_bug.cgi?id=114187
------- Additional comments from [email protected] Sun Aug 29 23:57:50 +0000 2010 ------- 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! --------------------------------------------------------------------- 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]
