Description: I have a large table with a lot of constraints and data columns. After generating a pivot table I want to analyze the content by selecting a certain choice of constraint values. Observation: given a pivot table with a specific selection of filter values (constraints) of some columns, it is possible to apply a constraint value (value in the column filter) to an column which is not element of any cell in that column. 1. This is very bad because the population of filter values should be adapted to the current selection. 2. And, when applying this selection, the result is empty - which is correct, because the filter value is not element of any cell in the column. However, one can't undo that - neither by hitting Undo nor by unselecting the filter value. The only remedy is to close the file and reopen it. The bug is old. I selected release 6.1 because I believe I already saw it there. Steps to Reproduce: 1. Generate a pivot table 2. Apply some filter values such that there is one column (let's call it X) where one element is cancelled out. 3. Now filter column X by selecting the value that shouldn't be there (yes, that's possible) Actual Results: You will get an empty pivot table in a deadlocked situation. One has to delete the table and generate a new one in order to continue working or close the file (don't save, of course) and reload. Expected Results: The filter values should be updated each time any filter is applied in order to prevent selection of empty sets. Reproducible: Always User Profile Reset: No Additional Info: Version: 7.1.1.2 / LibreOffice Community Build ID: 10(Build:2) CPU threads: 8; OS: Linux 5.4; UI render: default; VCL: kf5 Locale: de-DE (de_DE.UTF-8); UI: en-US Ubuntu package version: 1:7.1.1~rc2-0ubuntu0.20.04.1~lo1 Calc: CL OpenCL is not an issue here.
Test spreadsheet: 1. Create a spreadsheet with the following entries Constraint 1 Constraint 2 Data a 1 d1 b 3 d2 c 1 d3 2. Create a pivot table by adding the Constraint columns to the section row fields and the data column to the section data field (by the way, with Libreoffice calc 7.1.1.2 I used here you will see an extra bug because column Data is diplayed twice - did not happen with 7.0.1) 3. In the pivot table exclude value b from column Constraint 1 4. Now you will still be able to select value 3 as filter value in column Constraint 2 - which is a bug. 5. With this small spreadsheet I am not able to reproduce the deadlock event though. This happens with a spreadsheet with about 0.5 million cells.
Created attachment 175750 [details] Spreadsheet and explanation of the reported bug I added the spreadsheet file to the ticket because the bug still persists and I still have not lost hope that somebody will look at it. I tested it with 7.2.1.2 release and 7.2.2.2 rc2 on Kubuntu 20.04 trying both, with and without OpenCL. Just to make it clear: this is a fundamental bug in the pivottable functionality of LibreOffice Calc! Version: 7.2.2.2 / LibreOffice Community Build ID: 20(Build:2) CPU threads: 8; OS: Linux 5.4; UI render: default; VCL: kf5 (cairo+xcb) Locale: de-DE (de_DE.UTF-8); UI: en-US Ubuntu package version: 1:7.2.2~rc2-0ubuntu0.20.04.1~lo1 Calc: CL Steps to reproduce bug 140760 1. Exclude value b from Constraint1 2. Open list of values for Constraint2 Result: a. - You will still be able to see value 3 (ERROR!) although it should not be there because value b was excluded from column Constraint1. - It is vital that the constraint values are valid subset and correspond to the current selection of constraints. Evidently, here is missing an update of the other constraint columns reflecting the last selection. b. - If you select value 3 only, you will get an empty set as result. c. - In Constraints1, select b again. Now all values should be selected. However,there is only one line shown, namely for Constraint1 = b (ERROR) - I believe this is caused by the missing update of constraints as well. c. - Try undoing operations with CTRL+Shift+Z. This causes even more strange effects.
To whom it may concern: I now have updated to Version: 7.3.0.3 / LibreOffice Community Build ID: 30(Build:3) CPU threads: 8; OS: Linux 5.4; UI render: default; VCL: kf5 (cairo+xcb) Locale: de-DE (de_DE.UTF-8); UI: en-US Ubuntu package version: 1:7.3.0~rc3-0ubuntu0.20.04.1~lo1 Calc: CL and the error still persists. What I wonder about is that the error still is in status UNCONFIRMED. How shall I proceed? Report another bug referring to the latest version?
Eike, you shouldn't change the status to NEW yourself, it should be someone another. Yes, I confirm the behavior and filters in the Pivot table work differently than Autofilters in Calc. But I'm not sure it's a bug. Mike, what's your opinion here?
1. Wrt only showing available elements in the filter dropdowns: I don't see a problem in making it consistent with how autofilter works - so if autofilter only shows the items available after other filters were applied, why not do the same here. 2. However, that would only be a convenience method, and would not prevent the result that is considered problematic - you only have to change the source, and update the pre-filtered pivot table, to get that. So the actual bug - that is still not explained how to reproduce - is that a "deadlock" can happen. There should not be a state when the pivot table can't be "unfiltered". Possibly that would happen using the "change source + update pivot table", when the source would not contain a single instance of the value that is only permitted in the filter?
(In reply to Eike from comment #1) > 5. With this small spreadsheet I am not able to reproduce the deadlock event > though. This happens with a spreadsheet with about 0.5 million cells. 6. Change B11 from 3 to 1 7. Right-click A1, and select Refresh => now you can't make the pivot table to show column for constraint 2, thus you will not be able to reset the filtering done there. Now the question is: which UI would be reasonable in this case? What does Excel provide here? Maybe it would have some fail-safe behavior preventing this situation in the first place?
MS Excel shows absolutely the same behavior
Reminds me on bug 117276. But I lost the overview with all the patches there. Samuel, Balazs, Eike R: what do you think?
I now have upgraded to LibreOffice 7.4.1.2 Using the sample file attached I ran a retest on Autofilter and Pivot Table. = Autofilter works as far as I can see. = To be precise, with Autofilter I tested setting - Constraint 1 to "b" and then the filter of Constraint 2 displayed "3" as the only possibility. - I tried selecting all other combinations of first Constraint 1 and then Constraint 2 or the other way round. MS Excel shows the same behavior. = Pivot Table does not work. = I set Constraint 1 to "b" and then the only rows displayed contains a "3" for Constraint 2. However, the filter of Constraint 2 displays "1,3". If one now sets the filter of Constraint 2 to "1", the result will be an empty Pivot Table. Further, the column of Constraint 2 vanishes and one can't even Undo that, i.e. the command Undo has no effect here as well as Refresh so one is effectively locked out. That's it and the game starts again. This is a bug because providing filter settings that cannot be applied to the given subset makes no sense at all. Just imagine having a table with 30 columns and 50k rows (as I deal with from time to time). After applying a filter on the first column, how am I able to find out the allowed set of filter values for another columns in order to avoid lockout? It's important to have the file saved before each step of filtering. = My workaround = Well, my solution to that problem (using Calc) is that after I applied the first filter step I copy the resulting table to a new sheet, create a second Pivot Table and then I can filter the second column and then its filter shows the correct subset of filter values. Then I go back to the first Pivot Table and apply these on column 2. This process I repeat for all other columnns I additionally need until I am done. Another solution of course would be to use a SQL database for example but that cannot always be the solution. So to make a long message short: Autofilter subsequently updates the filter values, so why doesn't Pivot Table do that but instead produced vanishing columnns with lockout-effect and what is the reason for such nasty behavior?
(In reply to Heiko Tietze from comment #8) > Reminds me on bug 117276. But I lost the overview with all the patches > there. Samuel, Balazs, Eike R: what do you think? Bug 117276 definitely shows buggy behavior. This is a proof that even Autofilter does not correctly refine the set of valid filter values (and my example file obviously is too simple) because reselecting Tesla doesn't provide for "Wert=7000" as Tesla has "Wert=22000" only. I think, there is a problem with keeping track of the proper order of constraints while applying filters.
Just to be more precise on my expectations on Autofilter using bug 117276 as example: The order of filtering is 1.column B 2.column I Let's do that step by step. First deselecting some cars in column B cancels out some possible selections in all the other columns. Deselecting I=8000 implies effects to all other colunms as well including column B. But there it only cancels out one line for VW. Then column B is updated by selecting "Tesla". This is only possible because I=22000 is not deselected or cancelled out before. If in the first step one deselects B=Porsche and step 2 is repeated, then one can't reselect B=Tesla because I=22000 is missing as Porsche is the only other car providing I=22000 This is exactly the behavior of MS Excel 365 (I just tested it). So I come to the conclusion that Calc has a problem with keeping the order of filters. Think of filters as conditions and there order is crucial. And I am convinced the Pivot Table is flawed due to the same reasons.
(In reply to Eike from comment #10) > Bug 117276 definitely shows buggy behavior. Whether this is a bug is at least questionable. Read through the comments.
(In reply to Heiko Tietze from comment #12) > Whether this is a bug is at least questionable. Read through the comments. That is exactly what I did and I realized that the discussion there lacks a proper understanding of the top-down approach of Autofilter. It is like dealing with multi-dimensional conditional probabilities - if that helps. It is interesting to see, that the reporting comment of bug 117276 already mentions the problem: it says in step 3: Wert=7000,9000 are not shown and not selectable and after removing Wert=8000 and adding Tesla, suddenly Wert=7000 (and 9000 by the way) are in the selection list. There is no row in this dataset for Tesla with Wert=7000,9000 so why should adding Tesla allow for such a selection? If you think the current behavior is questionable, why don't you stay professional and explain the strategy Autofilter is currently adhering to and explain why my comment doesn't hold instead of being vage as you did? Many thanks!
Just to extend the example a little bit more: after adding Tesla and then setting Wert = 7000 only (crossing out all other selections) the result will be a single line showing Opel. But Opel was already deselected in the first step.
I think I made a noteworthy observation that could relate the bug to the interface. It seems, the bug arises in the moment all filter items to a specific column are checked while other columns remain partially filtered. Then, suddenly the filter is populated with all pairwise different numbers of the column (those irrelevant displayed unchecked and those relevant checked) and not only those relevant to the given filter settings. For explanation, have a look at the original problem setting of bug 117276: 1. Uncheck Citroen, Fiat, Ford, Opel, Peugeot, Renault, Tesla from column B. The table displays the rows for the remaining carmakers. No row in the table contains Wert=7000 or 9000. 7000 and 9000 are also removed from the filter list (not only unchecked) so the filter list is populated by 8000, 10000, 12000, 15000, 18000, 20000, 22000 (all checked). 2a. Uncheck 8000 from column I. One row belonging to a VW disappears. The filter list shows 8000 (unchecked) and 10000, 12000, 15000, 18000, 20000, 22000 (all checked). 3a. Open filter to column B. You will see only Tesla unchecked. All other carmakers (Audi, BMW, MB, Porsche, VW) in the filter list are checked. Then check Tesla. One row belonging to Tesla appears in the table with Wert=22000 in column I. However the filter list of column I suddenly contains all pairwise different numbers of the column (nine different numbers): 10000, 12000, 15000, 18000, 20000, 22000 (all checked), 8000 (still unchecked), 7000 and 9000 newly added and unchecked. Column I consists only of those numbers. So the error is that 7000 (Opel) and 9000 (Renault) belong to carmakers deselected. In order to test my theory, I did another test, starting with a new step 2b 2b. In column I, instead of unchecking 8000 I unchecked 22000. This removes all rows with column B = Porsche,Tesla. The filter list contains Audi, BMW, MB, VW (all checked) and Fiat, Ford, Peugeot, Renault (all unchecked). 3b. In column B try checking up to any three of the four uncheck carmakers and you will find that the filter to column I behaves as expected, i.e. 7000 and 9000 are not displayed. But if you check all four (Fiat, Ford, Peugeot, Renault), the rows displayed are ok but not the filter list of column I. Once again it shows the same effect as in the first test, that is all possible numbers with the irrelevant entries being unchecked (7000, 9000, 22000 as they don't belong to any of the selected carmakers). But the error here once again is that some of them (7000, 22000) are even shown at all as I=7000 only belongs to Opel and 22000 belongs to Porsche and Tesla. I=9000 belongs to Renault as well as I=8000 which is checked. In step 3b I would expect the filter list of column I to not contain 7000 and 22000 like in step 3a where it should not contain 7000 and 9000. So please could somebody familiar with the relevant code check what exactly happens once all filter items are checked? The event "all items checked" seems to be treated unconditional but it should be treated conditional given the current filter settings instead. Or explain why it is reasonable to provide filter items not related to the filter settings.
Let's keep the discussion here to the point - tagged the comments regarding autofilter as off-topic. My take is that we should do the same at pivot filter. Meaning the list is updated after items got selected. Excel behaves here not consistent.