Bug 67664 - Query wizard in need of love
Summary: Query wizard in need of love
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
(earliest affected) Master
Hardware: All All
: medium normal
Assignee: Omkar Nikam
Whiteboard: reviewed:2023
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: 2024-05-17 15:24 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


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

 - 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 (noise)
Comment 3 Robinson Tryon (qubit) 2015-12-14 05:00:15 UTC Comment hidden (noise)
Comment 4 Robinson Tryon (qubit) 2016-02-18 14:51:45 UTC Comment hidden (noise)
Comment 5 Xisco Faulí 2020-03-09 13:28:52 UTC
Please add keyword 'needsUXEval' and CC 'libreoffice-ux-advise@lists.freedesktop.org' if input from UX is needed.
Comment 6 Hossein 2023-04-19 06:48:56 UTC
Re-evaluating the EasyHack in 2023

This issue is still relevant. Most of the above mentioned problems still persist.

Problem 1: SQLQueryComposer class → 💔
Java SQLQueryComposer class is defined in wizards/com/sun/star/wizards/db/SQLQueryComposer.java. It has not been touched in the last 7 years:

Problem 2: Confused columns with same name in different tables: → 💔
The table name is used in the wizard itself, but not in the final output. So this is still an issue.

Problem 3: Confused aliasing results: → 💔
Still an issue.

Problem 4: Confused aggregation result names: → 💔
Still an issue.

Problem 5 and 6: Confusing result of the group by/having: → 💔
This is still confusing and not so useful.