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.
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
Adding self to CC if not already on
Migrating Whiteboard tags to Keywords: (EasyHack DifficultyInteresting SkillCpp TopicUI)
JanI is default CC for Easy Hacks (Add Jan; remove LibreOffice Dev List from CC)
Please add keyword 'needsUXEval' and CC 'firstname.lastname@example.org' if input from UX is needed.
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.