https://bugs.documentfoundation.org/show_bug.cgi?id=174051
Bug ID: 174051
Summary: Design View: misleading error when a query uses a
subquery in the FROM clause
Product: LibreOffice
Version: unspecified
Hardware: All
OS: All
Status: UNCONFIRMED
Severity: normal
Priority: medium
Component: Base
Assignee: [email protected]
Reporter: [email protected]
Description:
Design View cannot show a query that has a subquery in the FROM clause (a
"derived table"). Each table window needs a real table, view or saved query.
That limitation is fine, but
how Base reports it is confusing:
- The message reads like an error in the user's SQL, although the query is
valid and runs fine.
- It names the wrong cause (e.g. "Join could not be processed").
- It differs depending on whether you open the query or switch from SQL View.
Steps to Reproduce:
1. Create a database (embedded Firebird or HSQLDB) with two tables:
- "T_User" ("UserId" INTEGER PRIMARY KEY, "Name" VARCHAR)
- "T_Subscription" ("Id" INTEGER PRIMARY KEY, "UserId" INTEGER, "Year"
INTEGER)
2. Create a query in SQL View, run it (it works), and save it:
SELECT * FROM "T_User" LEFT OUTER JOIN
( SELECT "UserId", MAX( "Year" ) AS "LastSubscription" FROM
"T_Subscription" GROUP BY "UserId" ) AS "T_Year"
ON "T_User"."UserId" = "T_Year"."UserId"
3. Click "Switch Design View On/Off".
4. Close the query, then reopen it with Edit (Design View).
5. Repeat steps 2–4 with a query that has only a subquery in FROM:
SELECT * FROM ( SELECT "UserId", "Name" FROM "T_User" ) AS "a"
Actual Results:
For the join query:
step 3: Join could not be processed at
...connectivity/source/commontools/dbexception.cxx:193
step 4: The query is based on an SQL command which could not be parsed. at
...dbaccess/source/ui/querydesign/querycontroller.cxx:771
For the query with the subquery
step 3: Nothing has been selected.
step 4: The query is based on an SQL command which could not be parsed.
The wording differs between the cases, and all of them are presented as errors.
Expected Results:
Clear, informational message whenever a query cannot be shown in Design View.
For example:
"The query is based on an SQL command which could not be parsed. Join could not
be processed. The query will be opened in SQL view."
for the join case and
"Subqueries in the FROM clause cannot be shown in Design View. The query stays
in SQL View. Alternatively, save the subquery as a separate query and use that
query in the FROM clause."
for the subqueries. The second sentence is the workaround users already use
(see tdf#96372 comment 7, tdf#158453 comment 0).
Reproducible: Always
User Profile Reset: No
Additional Info:
Since this is related to user experience I would like to get some feedback
before fixing it.
1. Should this be an information message rather than an error?
2. Is the suggested wording clear? Should it mention the workaround?
3. Is it OK to have two messages for those two cases or have a single one that
covers both?
--
You are receiving this mail because:
You are the assignee for the bug.
