Issue 114187

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.

Created attachment 71467 [details]
Test DB created when exploring this issue and issue 114188

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!

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.

Confirmed w/ go-00 3.2.1 Windows and Linux

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.)

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).

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.

"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.

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.

@ameliab: Could you please check if this work for you in OOo 3.3 RCx. Thanks.

Feel free to reopen this issue.

Yes, it still happens. See
http://www.openoffice.org/issues/show_bug.cgi?id=114187#desc8.