Issue 114188

Summary:	Base query that references another query fails if subquery has order by statement
Product:	Base	Reporter:	ameliab <abbr>
Component:	code	Assignee:	AOO issues mailing list <issues>
Status:	CONFIRMED ---	QA Contact:
Severity:	Trivial
Priority:	P3	CC:	drewjensen.inbox, issues
Version:	OOo 3.2.1
Target Milestone:	---
Hardware:	Mac
OS:	All
Issue Type:	ENHANCEMENT	Latest Confirmation in:	---
Developer Difficulty:	---

Description ameliab 2010-08-29 22:58:10 UTC

Base's query designer allows you to reference another saved query, which is then
substituted in as a sub-query in the statement sent to the database.

However, if the sub-query contains an "order by" statement, this causes an SQL
error (at least with the embedded HSQL database, other RDBMS may tolerate this
syntax).

I've also had problems if the subquery is something I've created in SQL mode and
I've included a semi-colon at the end (again, this causes syntax errors when it
is nested as a sub-query).

The OpenOffice query parser needs to strip "Order by" clauses and terminal
semi-colons when nesting queries.

Comment 1 ameliab 2010-08-30 01:08:46 UTC

For examples, see the test database attached to issue 114187.

Comment 2 drewjensen.inbox 2010-08-30 01:31:02 UTC

Can confirm using Go-00 3.2.1 in the case of queries with escape prcocessing
disabled, the sort and filter functionality does not work.

If you open the query designer and execute the query in these cases the GUI
actually reflects this as the sort and filter buttons are not enabled.

If you run the query directly the GUI elements for sort and filter are enabled
but do not function.

For a test database see the file attached to issue 114187

Comment 3 drewjensen.inbox 2010-08-30 01:43:38 UTC

OK - seems that is what can happen with two browser windows open at the same
time - updated the wrong issue.

I am changing this one from a defect to an enhancement request.

Comment 4 ameliab 2010-08-30 19:39:48 UTC

I disagree with the classification of this as an enhancement, but I will leave
it up to an independent observer to make a final judgement.

This isn't a case of wanting some extra SQL behaviour that OO doesn't currently
support.  When creating queries using the wizard or design GUI, there is always
an option to sort the query, and there is always the option to base one query on
another query.  However, when you do both there is an error.  I'd call that a
defect. 

In particular, for someone who is unfamiliar with SQL and just using the GUI,
the final error message would be quite incomprehensible, because it complains
about an "order by" clause and the query they are trying to run might not have a
sort order of its own.

Comment 5 Frank Schönheit 2010-09-02 10:12:23 UTC

I agree to this being an enhancement, since it seems that the HSQLDB is
responsible here, by not accepting this particular statement having an ORDER BY
clause in a sub select.

That is, if you create a native query constituted by
  SELECT "ThrowNum", "Heads"
  FROM
  (
    SELECT "ThrowNum", "MoreThanHalf" AS "Heads"
    FROM "View"
    ORDER BY "Heads" ASC
  )
  WHERE "Heads" = True
an switch on "Direct SQL", then this already leads to an error. And in this
case, Base is not involved at all, it just passes the SQL to HSQLDB. So, it
really looks like a limitation of the latter, not the former.