Bug 35923 - Calc - Standard Filter produces wrong display (Selection range is automatically expanded)
Summary: Calc - Standard Filter produces wrong display (Selection range is automatical...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.3.0 RC4
Hardware: Other All
: medium minor
Assignee: Not Assigned
URL:
Whiteboard: target:3.5 Confirmed:4.2.0.1:Linux
Keywords:
: 85773 (view as bug list)
Depends on:
Blocks: Data-Filter
  Show dependency treegraph
 
Reported: 2011-04-03 03:05 UTC by d00m3d.olw
Modified: 2021-06-19 13:36 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments
Document to reproduce the problem. (13.18 KB, application/vnd.oasis.opendocument.spreadsheet)
2011-04-03 03:05 UTC, d00m3d.olw
Details
Step 1 (28.87 KB, image/png)
2011-04-03 03:06 UTC, d00m3d.olw
Details
Step 2 (42.62 KB, image/png)
2011-04-03 03:06 UTC, d00m3d.olw
Details
Step 3 (43.16 KB, image/png)
2011-04-03 03:07 UTC, d00m3d.olw
Details
Step 4 - Correct Display (20.15 KB, image/png)
2011-04-03 03:07 UTC, d00m3d.olw
Details
Step 4 - Wrong Display (23.43 KB, image/png)
2011-04-03 03:08 UTC, d00m3d.olw
Details

Note You need to log in before you can comment on or make changes to this bug.
Description d00m3d.olw 2011-04-03 03:05:14 UTC
Created attachment 45171 [details]
Document to reproduce the problem.

Dear LibO developers

I found a problem in Calc, in which the issue seems to be related to data range
selection.

Platform:  Seems independent, both windoze and Linux versions have same behaviour.  Problem appears in version later than LibO 3.3.0RC3 (excluding RC3).

Steps to reproduce error display.

Step 1:
Select data range A1:C16

Step 2:
From pull down menu, choose Data -> Filter -> AutoFilter

Then, goto cell A1, click the down arrow button and choose Standard Filter

Note the background of shaded cells, data range A1:C18 is selected even nothing
has been filled in the "Standard Filter" dialog box.

Step 3:
I intended to filter out Level 3 and Level 4, only show Level 1 and 2.  Fill in the proper criteria in "Standard Filter" dialog box.

Step 4: Check Display
In LibO 3.3.0RC3, despite shaded range seems incorrect, at least the displayed result is correct.  Row 17 and Row 18 are shown.

In LibO 3.3.2, Row 17 and Row 18 are disappeared.  They are incorrectly filtered
because they are wrongly grouped into the filter data range.

This bug prevented me from upgrading LibO since versions after 3.3.0RC3.

Document to reproduce the problem and screen shots are supplied as attachments.

I would be appreciated if this bug could be fixed shortly.
Comment 1 d00m3d.olw 2011-04-03 03:06:24 UTC
Created attachment 45172 [details]
Step 1
Comment 2 d00m3d.olw 2011-04-03 03:06:55 UTC
Created attachment 45173 [details]
Step 2
Comment 3 d00m3d.olw 2011-04-03 03:07:18 UTC
Created attachment 45174 [details]
Step 3
Comment 4 d00m3d.olw 2011-04-03 03:07:48 UTC
Created attachment 45175 [details]
Step 4 - Correct Display
Comment 5 d00m3d.olw 2011-04-03 03:08:16 UTC
Created attachment 45176 [details]
Step 4 - Wrong Display
Comment 6 Jan Holesovsky 2011-04-07 11:11:47 UTC
Kohei: Is that a regression, please?
Comment 7 Kohei Yoshida 2011-04-07 11:33:34 UTC
No, this behavior has been the same, not a regression.  It hasn't changed between 3.3.x releases AFAIK.

But the intention I think is to keep the selection when there is a pre-selected range, if not, select the whole data range.

Meanwhile, the workaround is to insert an empty row at row 17.  Then the selection won't go beyond the data range.
Comment 8 d00m3d.olw 2011-04-07 15:29:20 UTC
That's true, such behaviour does not change between 3.3.x releases.  That's the reason why I keep 3.3.0RC3 because 3.3.0RC4 begins to have this problem.

I know the workaround trick but I don't like it because I used to do cost analysis in a big spreadsheet and I am worried manual insertion could cause trouble in messing up certain formulas among cells.

I would be highly appreciated if this bug could be corrected.

Thanks again for your attention.

(In reply to comment #7)
> No, this behavior has been the same, not a regression.  It hasn't changed
> between 3.3.x releases AFAIK.
> 
> But the intention I think is to keep the selection when there is a pre-selected
> range, if not, select the whole data range.
> 
> Meanwhile, the workaround is to insert an empty row at row 17.  Then the
> selection won't go beyond the data range.
Comment 9 Kohei Yoshida 2011-04-07 16:02:16 UTC
(In reply to comment #8)

> I know the workaround trick but I don't like it because I used to do cost
> analysis in a big spreadsheet and I am worried manual insertion could cause
> trouble in messing up certain formulas among cells.

No it doesn't.  Insertion of cells should never mess up formulas.  If it does, please file a bug.
Comment 10 d00m3d.olw 2011-04-07 16:59:03 UTC
Well, I meant manual mistakes when I used functions like vlookup and forgot to put $ cell references.

(In reply to comment #9)
> (In reply to comment #8)
> 
> > I know the workaround trick but I don't like it because I used to do cost
> > analysis in a big spreadsheet and I am worried manual insertion could cause
> > trouble in messing up certain formulas among cells.
> 
> No it doesn't.  Insertion of cells should never mess up formulas.  If it does,
> please file a bug.
Comment 11 Kohei Yoshida 2011-04-19 20:35:20 UTC
Well, I looked into this, and the change we inherited from OOo to always extend the data area was intentional (the change you saw during the 3.3 RC phase).  The bad news is that, this area of the code is pretty complex, and making small changes here and there will not solve this issue completely without the risk of introducing other weirdness.

So, I'd like to tackle this post 3.4.x.  I'm planning to clean up this area on master (for 3.5), so I'll take care of this then.
Comment 12 d00m3d.olw 2011-04-20 23:04:45 UTC
(In reply to comment #11)

Debugging is always not an easy task.  Thanks again for looking it into matter and I look forward to seeing this bug be closed in 3.5.

Cheers!
Comment 13 Marco 2011-08-23 13:09:43 UTC
In LibreOffice Calc versions 3.3.4 and 3.4.2 I had a similar problem. If I create the table below

    |  A     |   B
  --+--------+----------------------+
  1 | Name   | Value                |
  --+--------+----------------------+
  2 | a      | 1                    |
  --+--------+----------------------+
  3 | a      | 3                    |
  --+--------+----------------------+
  4 | b      | 2                    |
  --+--------+----------------------+
  5 | a      | 5                    |
  --+--------+----------------------+
  6 | b      | 2                    |
  --+--------+----------------------+
  7 | Total  | =SUBTOTAL(9;B2:B6)   |
  --+--------+----------------------+

select A1:B6 and create a AutoFilter (Data -> Filter -> AutoFilter), the filter is created for the region A1:B7. In the OpenOffice 3.2.1, the AutoFilter works as expected, creating a filter for region A1:B6.
Comment 14 Zeki Bildirici 2014-01-18 22:16:44 UTC
Still reproducable on Ubuntu 13.10 x64, LibreOffice Version: 4.2.0.1
Build ID: 420m0(Build:1)

Best regards,
Zeki
Comment 15 Joel Madero 2015-05-02 15:42:08 UTC Comment hidden (obsolete)
Comment 16 Buovjaga 2015-06-20 14:59:03 UTC
Repro.

Win 7 Pro 64-bit Version: 5.1.0.0.alpha1+
Build ID: 3ecef8cedb215e49237a11607197edc91639bfcd
TinderBox: Win-x86@62-merge-TDF, Branch:MASTER, Time: 2015-06-19_23:16:58
Locale: fi-FI (fi_FI)
Comment 17 Dave Shiffman 2016-08-12 16:08:14 UTC
Comment on attachment 45171 [details]
Document to reproduce the problem.

Problem 1 - selecting Data > Filter.  Both "AutoFilter" and "More Filters" use "F" as the selection character.  I saw the "Hide AutoFilter" under the "More Filters" selection, but could not find how to invoke it.

Problem 2 - When "Standard Filter" is selected, it displays a Filter criteria panel that allows multiple rows of criteria to be entered, with different operators, fields, conditions, and values. Formerly, I could TAB to each column, and in the Values column, TAB once more to see the list of values and simple criteria, such as "Empty" or "Not Empty".  Now TAB does not advance to values list, and I need to click the selection arrow to see the list.
Comment 18 Kevin Suo 2016-10-26 09:42:55 UTC
The issue is related to standard filter, not auto filter. Adjust summary accordingly.
Comment 19 Severo Raz 2018-03-08 18:47:30 UTC Comment hidden (obsolete)
Comment 20 Severo Raz 2018-03-08 18:47:31 UTC Comment hidden (obsolete)
Comment 21 QA Administrators 2019-03-09 03:42:12 UTC Comment hidden (obsolete)
Comment 22 joonaskaljulaid 2020-03-23 07:45:48 UTC
Dear Developers

In the Calc version 6.4.2.2 the bug is still present. I noticed that even if one defines a range in Data -> Define range, when selecting Filter or AutoFilter the range is redefined automatically. I do not have the skills to go into more details, however as little as I have studied programming this may be the root cause for filtering not working properly. Is this issue still somewhere on the list?
Comment 23 b. 2020-05-11 16:54:30 UTC
for the Bug Hunting Session 7.0.0.0.a1+: 

bug still as described by the OP, 

It's an ambiguous decision: a normal - inexperienced - user would of course like automatic range selection and newly added data to be filtered or sorted as well, but the same - inexperienced - user would not like his column-totals or footnotes to be filtered or sorted. 

It is difficult for a more algorithmic than intelligent program to meet both requirements. 

I think there have been long discussions and lonely decisions about this ... 

Are my observations correct: 

sort considers in the following order: 

1. the current selection in the sheet, if it is larger than one cell, 

2. an "__Anonymous_Sheet_DB__0" - automatic database area - if it is left over from the previous creation of a filter and the focus is in this area, if this area is divided by empty columns or rows only contiguous areas are taken? (the 'anonymous' areas are never deleted but only redefined once they are on a sheet?) 

3. a defined 'data range' if the focus is in it, 

3a. overlapping database areas are evaluated a little bit strange, with focus in the first (older or upper or 'left') area this is selected, with focus in the overlapping area also, with focus in the second (newer, deeper or 'righter') database area both areas are combined?

4. otherwise sort itself defines a rectangular area bordered by empty cells, 

and if this includes only the one selected cell ... 

5. just sorts this one cell, 

6. sort has an option to 'sort by row' - horizontally - and then re-arranges columns instead of rows, 

whereas filters - all filters? -  

1. simply always according to sort-4. define a filter range by themself and ignore all specifications from the user? 

2. have no 'horizontal' option and therefore should neither evaluate tags 'table:orientation="column"' from the files nor handle parameters 'bByRow' or 'bByColumn' in the evaluations for the filtering (UI and basic / macros) or let them influence the field assignments? 

- puhhh that was long, is that described somewhere as 'target behaviour'? - 

my approach would be: 

- sort is much more user friendly than the filters, 

- sort should not consider the automatic data area, which is not intuitive or manageable for the user, 

and filter should either: 

- behave as sort, and respect the user's specifications, 

or: 

- have a query in the filter definition: 'which area do you want to edit? "defined" B3:F165, or "automatic" B2:H182'

but there may be more ideas, and they may become difficult once excel compatibility is taken into account?
Comment 24 stragu 2021-06-19 13:36:44 UTC
*** Bug 85773 has been marked as a duplicate of this bug. ***