Bug 58586 - EDITING : Autofilter complete ignored, when content is defined by a Subselect
Summary: EDITING : Autofilter complete ignored, when content is defined by a Subselect
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
3.6.4.3 release
Hardware: Other Linux (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Database-Forms
  Show dependency tree / graph
 
Reported: 2012-12-20 20:56 UTC by mibof
Modified: 2023-06-21 06:05 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:

Attachments
troubles with AutoFilter and quick Sort buttons (35.50 KB, application/vnd.oasis.opendocument.base)
2012-12-20 20:56 UTC, mibof 		Details
empty grid control and buggy buttons (77.02 KB, image/png)
2012-12-20 21:04 UTC, mibof 		Details
bug report file after migration to Firebird db (36.44 KB, application/vnd.oasis.opendocument.database)
2018-08-09 16:05 UTC, Drew Jensen 		Details
View All Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.
Description mibof 2012-12-20 20:56:53 UTC 
Created attachment 71871 [details]
troubles with AutoFilter and quick Sort buttons

This bug occurs in grid controls included in some formularies with request contents.

In the attachement, see the formulary "Browse users" : in the grid control, any filtering ans sorting features work fine, except for the column "LastSubscription" : for this one, the buttons "ascending sort", "descending sort" and "autofilter" empty the grid control. There is no way to go back except closing and reopening the formulary.

This behaviour looks to be related to the structure of the request "R_UserAndLastSubscription".

I found an half-functional workaround, using 2 requests instead of one : see the request ""R_UserAndLastSubscriptionWORKAROUND" used in the formulary "Browse users with workaround" : all sorting and filtering features work fine, but the user can't modify the fields of the table "T_user" as before.
Comment 1 mibof 2012-12-20 21:04:15 UTC 
Created attachment 71873 [details]
empty grid control and buggy buttons
Comment 2 Robert Großkopf 2012-12-21 20:06:58 UTC 
The problem is, that there isn't a primary-key for the second table of your query and you want to edit the query. If you try to open the query, Base doesn't understand the query and opens in the SQL-mode.
Second problem I saw was, that the User with ID 0 appears very often in the result of the executed query.

Try the following query:

SELECT "UserId", "FirstName", "LastName", "Adress", "Zip", "City", "Mail", ((SELECT MAX( "Year" ) AS "LastSubscription" FROM "T_Subscription" WHERE "UserId" = "a"."UserId"  GROUP BY "UserId")) AS "LastSubscription" FROM "T_User" AS "a"

With this subselsct it works. If you don't use two brackets, the filter doesn't work,  because it tries to solve the subselect without brackets in the WHERE-clause.

This isn't a problem from the tablecontrol but a problem, that you try code, which isn't compatible with the GUI. A query, which doesn't work correct in the query-editor, couldn't work correct in the tablecontrol of a form.
Comment 3 Robert Großkopf 2012-12-22 09:22:30 UTC 
I can confirm this behavior.
Could be a good idea to separate the problems. You have reported two problems. For a developer it would be better to look for one problem in one bug.

I have opened another bug for the problem of comment 2 (brackets were removed by the autofilter): https://bugs.freedesktop.org/show_bug.cgi?id=58644
Comment 4 Robert Großkopf 2012-12-23 07:56:02 UTC 
This bug isn't a bug of the grid control, as reported first. It's a bug of setting sorting-order or filter in a query and anywhere else.

Try the query "R_UserAndLastSubscription" of https://bugs.freedesktop.org/attachment.cgi?id=71871 . The query works and could be edited. 
Try to sort this query by the button sort. You could set the sorting order of the field "LastSubscription". It works.
Try to sort "LastSubscription" by the button "sort ascending" or "sort descending". It doesen't work.
Try to filter "LastSubscription". It doesn't work with "Auto Filter" and doesn't work with "Standard Filter".

The query is constructed, as the GUI constucts queries, when you will link a query and a table in a new query:
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"
The content of the subquery inside the brackets, declared after declaring the first table, is complete ignored by the quick sort buttons and all filter buttons.
Comment 5 Lionel Elie Mamane 2013-07-10 01:23:25 UTC 
This is partially a DUP of the other "removes parentheses around subqueries" bug, but also partially other issues piled on top of it.

In short, LibreOffice gets a bit lost in the structure

SELECT * FROM tbl1 INNER JOIN (SELECT foo, bar FROM tbl2) AS tbl3

It does not properly see that tbl3 contributes fields "foo" and "bar" to the *.

You can help it by writing:

SELECT "tbl1".*, "tbl3"."foo", "tbl3"."bar" FROM tbl1 INNER JOIN (SELECT foo, bar FROM tbl2) AS tbl3

and then it works.

The intermediary variant:

SELECT "tbl1".*, "foo", "bar" FROM tbl1 INNER JOIN (SELECT foo, bar FROM tbl2) AS tbl3

(assuming "tbl1" has no fields "foo" or "bar") does not work either, but that is a HSQLDB bug. It might work with other drivers / database types. When LibreOffice asks HSQLDB what table "bar" comes from, HSQLDB replies SYSTEM_SUBQUERY instead of tbl3. If it just replied "I don't know", it would work alright...


See http://cgit.freedesktop.org/libreoffice/core/commit/?id=83076c22e1ffbad06f4b250b69cd239758fbb1ba for implementation direction to fix the LibreOffice problem here.
Comment 6 Alex Thurgood 2015-01-03 17:40:23 UTC Comment hidden (no-value)
Comment 7 QA Administrators 2016-01-17 20:02:50 UTC Comment hidden (obsolete)
Comment 8 Robert Großkopf 2016-01-20 17:56:22 UTC 
Bug of the filtering still exists with LO 5.1.0.2, OpenSUSE 42.1 Leap, 64bit rpm Linux. Quicksort is working now with attachment https://bugs.documentfoundation.org/attachment.cgi?id=71871
and the field "LastSubscription". Have changed the title to the new behavior.
Comment 9 QA Administrators 2017-03-06 13:59:12 UTC Comment hidden (obsolete)
Comment 10 Robert Großkopf 2017-03-06 16:09:49 UTC 
Bug of Autofilter still exists in LO 5.3.1.1, OpenSUSE Leap 42.1 64bit rpm Linux.
Comment 11 Xisco Faulí 2017-07-13 11:26:20 UTC 
Setting Assignee back to default. Please assign it back to yourself if you're
still working on this issue
Comment 12 QA Administrators 2018-07-14 02:45:29 UTC Comment hidden (obsolete)
Comment 13 Drew Jensen 2018-08-09 16:05:08 UTC 
Created attachment 144066 [details]
bug report file after migration to Firebird db

Using Ubuntu 18.04 and Libo 6.1 RC3 the problem continues to exhibit when using HSQLdb embedded Base files.

After conversion to Firebird the problem kind of goes away because the query which gives rise to it does not function the same under Firebird (in fact it is down right odd) Run it, unchanged, in query designer and you get one result that doesn't actually have the proper data in the last column (rather it duplicates a column). Use the query as is in the first form and the displayed values differ again and the last column is the correct column now, but completely empty. Set run SQL direct on that bothersome query and the data is displayed as it was with HSQLdb but now of course the resultset is not live (can not be updated) and the filter settings GUI controls do not activate therefore.

Anwyay, it is certainly still there for HSQL and I've uploaded the converted Firebird file if anyone wants to see if they duplicate the bug by re-working the problem query.
Comment 14 Robert Großkopf 2018-08-09 17:17:12 UTC 
(In reply to Drew Jensen from comment #13)
> Created attachment 144066 [details]
> bug report file after migration to Firebird db
> 
> Using Ubuntu 18.04 and Libo 6.1 RC3 the problem continues to exhibit when
> using HSQLdb embedded Base files.
> 
This is the query, which will work under Firebird also:
SELECT "T_User".*, "T_Year"."LastSubscription" 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"

... and if I will filter "LastSubscription" it doesn't work, too.

The query should have named the table if there is asked for all fields (*).

Tested with LO 6.1.0.3, OpenSUSE 15, 64bit rpm Linux
Comment 15 Drew Jensen 2018-08-09 17:36:55 UTC 
it probably makes sense to run a test using the API calls into the singlequery(In reply to robert from comment #14)
> (In reply to Drew Jensen from comment #13)
> > Created attachment 144066 [details]
> > bug report file after migration to Firebird db
> > 
> > Using Ubuntu 18.04 and Libo 6.1 RC3 the problem continues to exhibit when
> > using HSQLdb embedded Base files.
> > 
> This is the query, which will work under Firebird also:
> SELECT "T_User".*, "T_Year"."LastSubscription" 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"
> 
> ... and if I will filter "LastSubscription" it doesn't work, too.
> 
> The query should have named the table if there is asked for all fields (*).
> 
> Tested with LO 6.1.0.3, OpenSUSE 15, 64bit rpm Linux

Yup that does it for the change to the query.

Firebird and HSQL still act differently (between the original query in HSQL and the changed query in Firebird) in that under HSQL you get an empty grid while under firedbird it generates an error box and does not change the actual grid. 

The error displayed, with FB engine, under 6.1 RC3 here is "Syntax error in SQL statement".

That would be generated I suppose by either the SingleSelectQueryComposer or SingleSelectQueryAnalyzer service when the filter parameter is set - it could be worth a little time to check this with a script working against the service provider, setting the filter parameters, via the API. Might be able to narrow down which of those (the analyzer or composer) is throwing the error. I don't mind working at that this evening.
Comment 16 QA Administrators 2019-09-07 05:57:31 UTC Comment hidden (obsolete)
Comment 17 Stéphane Guillou (stragu) 2021-06-20 08:02:48 UTC 
Testing with:

Version: 7.3.0.0.alpha0+ / LibreOffice Community
Build ID: 94d552f94b427f884c004dba5d4619ecf729d605
CPU threads: 8; OS: Linux 4.15; UI render: default; VCL: gtk3
Locale: en-AU (en_AU.UTF-8); UI: en-US
TinderBox: Linux-rpm_deb-x86_64@86-TDF, Branch:master, Time: 2021-06-18_13:30:27
Calc: threaded

I noticed that, opening the form "Browse users", and selecting columns, the buttons (sort ascending, sort descending, autofilter) are greyed out. However, I can first select the whole table (click on top left margin cell to select all), and then select a column: the three buttons are now active.

Once they are active, it is true that using "autofilter" on the "LastSubscription" column makes the whole table disappear, and I can't see a way to remove the filter.
Comment 18 QA Administrators 2023-06-21 03:13:48 UTC Comment hidden (obsolete)
Comment 19 Robert Großkopf 2023-06-21 06:05:13 UTC 
Bug still exits in LO 7.5.4.2 on OpenSUSE 15.4 64bit rpm Linux.