Bug Hunting Session
Bug 67664 - query wizard in need of love
Summary: query wizard in need of love
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
4.2.0.0.alpha0+ Master
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords: difficultyInteresting, easyHack, skillCpp, topicUI
Depends on: 56750 59978
Blocks: Database-Queries
  Show dependency treegraph
 
Reported: 2013-08-02 10:41 UTC by Lionel Elie Mamane
Modified: 2017-10-23 20:44 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Lionel Elie Mamane 2013-08-02 10:41:15 UTC
The Query Wizard is in need of a serious redesign. Its implementation is a confusing mess, and it gets itself confused over far too many things:

 - There is a Java SQLQueryComposer class, which confusingly is *not*
   an implementation of the XSQLQueryComposer interface (nor the
   corresponding service). The latter uses an XSingleSelectQueryComposer,
   but in very partial ways, and sometimes resetting it, blurring what
   is the responsibility of the Java SQLQueryComposer and what of the
   XSingleSelectQueryComposer.

 - Confused columns with same name but in different tables

 - Confused about aliasing between result columns and table columns
   (shows e.g. in the ORDER BY clause)

 - Confused about the table column that is being aggregated
   and the *result* of the aggregation. Actually, does not
   have a clear notion of result column of aggregation.

   For example, aggregate both the SUM and the AVG of column "cl"
   (in that order).
   The "aliases" step proposes the "cl" column only once, and
   the generated query has only the sum, with the alias chosen
   for column "cl". It should generate *both* the SUM and AVG
   and the alias step should show SUM("cl") *and* AVG("cl"),
   but *not* "cl" itself.

 - Does not include the GROUP BY columns in the result, giving a
   generally not-that-useful result.

   E.g. imagine we want the SUM of the amounts of the invoices
   per customer (that is, the turnover per customer).
   The wizard makes a query that gives the turnover, but does not
   say for which customer each number is.

 - Gives only utterly non-useful choices for HAVING

   It proposes only the (unadulterated) GROUP BY columns. Selecting
   those in the WHERE clause gives *exactly* the same result.

   It should propose the RESULTS of the aggregations.

 - mishandles dates (see bug 59978)


The wizard itself is in wizards/com/sun/star/wizards/query, but a lot of the work is done in wizards/com/sun/star/wizards/db and wizards/com/sun/star/wizards/ui.
Comment 1 Björn Michaelsen 2013-10-04 18:47:25 UTC
adding LibreOffice developer list as CC to unresolved EasyHacks for better visibility.

see e.g. http://nabble.documentfoundation.org/minutes-of-ESC-call-td4076214.html for details
Comment 2 Alex Thurgood 2015-01-03 17:38:33 UTC Comment hidden (no-value)
Comment 3 Robinson Tryon (qubit) 2015-12-14 05:00:15 UTC Comment hidden (obsolete)
Comment 4 Robinson Tryon (qubit) 2016-02-18 14:51:45 UTC Comment hidden (obsolete)