Bug Hunting Session
Bug 108209 - Autofill overwrites data with mixed empty and filled cells
Summary: Autofill overwrites data with mixed empty and filled cells
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium normal
Assignee: Eike Rathke
URL: https://help.libreoffice.org/6.2/en-U...
Whiteboard: target:6.4.0 target:6.3.0.1 target:6.2.7
Keywords: dataLoss
Depends on:
Blocks: AutoFill
  Show dependency treegraph
 
Reported: 2017-05-28 22:47 UTC by Pedro
Modified: 2019-10-18 12:59 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:


Attachments
Simple spreadsheet with data to test autofill error (8.52 KB, application/vnd.oasis.opendocument.spreadsheet)
2017-05-28 22:47 UTC, Pedro
Details
More to test autofill ODS (11.78 KB, application/x-vnd.oasis.opendocument.spreadsheet)
2019-07-02 08:19 UTC, Timur
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Pedro 2017-05-28 22:47:44 UTC
Created attachment 133684 [details]
Simple spreadsheet with data to test autofill error

To autofill a column with a value/string the user can double click on the small square on the bottom right corner of the cell selection. This should fill the cells down as far the next/previous column or until a filled cell is reached.

LibreOffice (as did/does OpenOffice.org/AOO) ignores cells containing values/strings and overwrites all column causing data loss.

How to replicate

1)In the attached file select cell A1 and double click on the small square on the bottom right corner of the cell selection.

Observed: Column A is filled with letter A until cell A10 thus overwriting information in cells A7 and A10
Expected: Column A is filled with letter A until cell A6

If Calc behaved as expected, after Step 1 selecting cell A7 and repeating the double click should result in cells A1 to A6 containing A, cells A7 to A9 containing B and cell A10 containing C

This is what is expected based on how other spreadsheets (e.g. Gnumeric, Excel) perform.

This was tested with LibreOffice 5.3.2.2 x64 and x86 (but also with AOO 4.1.3 and LO 3.3.4) under Windows 10 x64
Comment 1 Jacques Guilleron 2017-05-29 15:22:01 UTC
Hi Pedro,

I reproduce wirh
LO 5.5.0.0.alpha0+ Build ID: 0e6297932252403883a6057feee488e4ee2bc360
CPU threads: 2; OS: Windows 6.1; UI render: default; 
TinderBox: Win-x86@39, Branch:master, Time: 2017-05-23_00:14:17
Locale: fr-FR (fr_FR); Calc: CL

and with
LO  3.5.3.2 Version ID : 235ab8a-3802056-4a8fed3-2d66ea8-e241b80
so probably inherited from OOo.
Comment 2 Pedro 2017-05-29 16:17:57 UTC
Tested with LO 5.3.3.2 under Ubuntu 14.04.5
The bug also occurs so I'm changing OS to All
Comment 3 QA Administrators 2018-10-29 03:58:44 UTC Comment hidden (obsolete)
Comment 4 Roman Kuznetsov 2018-10-29 07:51:28 UTC
that behaviour still repro in

Version: 6.2.0.0.alpha0+
Build ID: cec31fdedd7c94f4ebf903a66456a75867db22b0
CPU threads: 4; OS: Windows 6.1; UI render: default; VCL: win; 
TinderBox: Win-x86@42, Branch:master, Time: 2018-10-21_22:54:44
Locale: ru-RU (ru_RU); Calc: threaded

but are we sure that it's a bug and is not a feature?

yes, behaviour in Calc in this case is different from behaviour in Excel. 

And my opinion -> NOTABUG, or need make from this bug an enhancement about adding warning for user about overwrites his data in cell.

Eike, may be you have some opinion about this?
Comment 5 Timur 2018-11-08 17:11:36 UTC
Help says "You can double-click the fill handle to automatically fill all empty columns of the current data block."
Not sure why it says "columns". But if not empty, it shouldn't fill it.
Comment 6 Eike Rathke 2019-06-20 13:08:06 UTC
Taking.
Comment 7 Eike Rathke 2019-06-20 15:52:37 UTC
The "fill all empty columns" probably should read something like "fill all empty cells in the selected columns below the selection up to the next data or where the neighbouring data ends, whatever comes first". That's at least what it should do, but I guess that's too complicated for a help text ;-)
Comment 8 Pedro 2019-06-20 16:17:59 UTC
(In reply to Eike Rathke from comment #7)
> The "fill all empty columns" probably should read something like "fill all
> empty cells in the selected columns below the selection up to the next data
> or where the neighbouring data ends, whatever comes first". That's at least
> what it should do, but I guess that's too complicated for a help text ;-)

Yes, that is what it should say and what is expected.

Thank you for reviving this issue!

Is there any chance your patch can be dual licensed so that it can be incorporated in OpenOffice as well?
Comment 9 Commit Notification 2019-06-20 19:35:32 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/+/7dd57a914be5f8fc2b53b7725c16625887cf7439%5E%21

Resolves: tdf#108209 let auto fill handle double click stop at existing data

It will be available in 6.4.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 10 Commit Notification 2019-06-20 21:51:31 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-6-3":

https://git.libreoffice.org/core/+/7778ace84cbeb5ee8481b9de8b7bc63778470075%5E%21

Resolves: tdf#108209 let auto fill handle double click stop at existing data

It will be available in 6.3.0.1.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 11 Pedro 2019-06-21 06:30:48 UTC
(In reply to Commit Notification from comment #10)
> Eike Rathke committed a patch related to this issue.

> It will be available in 6.3.0.1.
> 
> The patch should be included in the daily builds available at
> https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
> information about daily builds can be found at:
> https://wiki.documentfoundation.org/Testing_Daily_Builds
> 
> Affected users are encouraged to test the fix and report feedback.

Thank you for looking into this. Looking forward to test the fix!

Unfortunately there are no Daily builds for branch 6.3 and Linux builds are 5 days old.
Can someone please fix this?

Thanks!
Comment 12 p199999991 2019-06-25 14:01:18 UTC
(In reply to Eike Rathke from comment #7)
> The "fill all empty columns" probably should read something like "fill all
> empty cells in the selected columns below the selection up to the next data
> or where the neighbouring data ends, whatever comes first". That's at least
> what it should do, but I guess that's too complicated for a help text ;-)

This does not align it to Excel's behaviour, and takes away a possible use of the fill-handle, which is to update formulas for all records in a previously filled range of formulas. I think Excel's implementation is to take the min of the number of consecutive non-empty cells in each column beginning with just below the selection (*), but if these numbers are 0, take the max of that for the adjacent columns.

But maybe even more useful would be to ignore 0 at (*), so it fills whenever it makes sense.
Comment 13 p199999991 2019-06-25 14:09:51 UTC
Oops, sorry, that implementation wouldn't work in this specific case. It should be  "number of cells until it changes from empty to non-empty or vice versa" rather than just "number of consecutive non-zeros". So:

This does not align it to Excel's behaviour, and takes away a possible use of the fill-handle, which is to update formulas for all records in a previously filled range of formulas. I think Excel's implementation is to take the min of the number of cells until it changes from empty to non-empty or vice versa (0 if not found) in each column beginning with just below the selection (*), but if these numbers are all 0, take the max of that for the adjacent columns.

But maybe even more useful would be to ignore 0 at (*), so it fills whenever it makes sense.
Comment 14 Buovjaga 2019-06-25 16:58:42 UTC
Just tested with Excel 365 and now LibreOffice behaves like it.
Comment 15 p199999991 2019-07-01 15:08:36 UTC
I just tested Office online and it behaves like Excel 2010 and all earlier versions of Excel. When double-clicking the fill handle, the cells will be copied down to adjacent cells, even overwriting cells (which we want it to do, since we see the overwritten cells directly in front of us), up to the biggest block that it thinks it should reach. There's a tutorial on this: https://youtu.be/3L5DTFQEOP4?t=43 . The tutorial seems to be made with Excel 2013, and so, I have shown that we have the same behaviour there.

If Excel 365 does not have this function, then that's a step backwards.
Comment 16 Buovjaga 2019-07-02 04:14:10 UTC
(In reply to p199999991 from comment #15)
> I just tested Office online and it behaves like Excel 2010 and all earlier
> versions of Excel. When double-clicking the fill handle, the cells will be
> copied down to adjacent cells, even overwriting cells (which we want it to
> do, since we see the overwritten cells directly in front of us), up to the
> biggest block that it thinks it should reach. There's a tutorial on this:
> https://youtu.be/3L5DTFQEOP4?t=43 . The tutorial seems to be made with Excel
> 2013, and so, I have shown that we have the same behaviour there.
> 
> If Excel 365 does not have this function, then that's a step backwards.

The behaviour of Excel matches LibreOffice in the case of attachment 133684 [details] now after the fix. When you have a empty cells between the cell data in A column and cells with contents in B column, Excel only fills the empty cells.
Comment 17 Timur 2019-07-02 08:19:11 UTC
Created attachment 152498 [details]
More to test autofill ODS

From what I see, this bug was resolved as reported so I'll mark Verified. 
What p199999991 mentions is good observation, but it looks like another enhancement request that I'll report separately. 

I attach ODS spreadsheet with some more data. 
double click on A1 fills only up to A6 – OK, solved here
double click on D1 fills up to D10 – OK from before
double click on H1 fills up to H2 instead of H10 - works in MSO since 2013
double click on M1 doesn’t fill to M10 - also works in MSO
Comment 18 Timur 2019-07-02 08:58:43 UTC
p199999991 already opened Bug 66890 for overwriting. 
So please no more comments here.
Comment 19 Pedro 2019-08-07 18:08:19 UTC
(In reply to Commit Notification from comment #10)
> Eike Rathke committed a patch related to this issue.
> It has been pushed to "libreoffice-6-3":
> 
> https://git.libreoffice.org/core/+/
> 7778ace84cbeb5ee8481b9de8b7bc63778470075%5E%21
> 
> Resolves: tdf#108209 let auto fill handle double click stop at existing data
> 
> It will be available in 6.3.0.1.

Can this patch please be cherry picked to branch 6.2? 

It can still be included in 6.2.6.2
Comment 20 Eike Rathke 2019-08-08 15:19:30 UTC
Too late for 6.2.6.2, but pending review for 6-2 (to-be 6.2.7)
https://gerrit.libreoffice.org/77157
Comment 21 Commit Notification 2019-08-09 08:00:45 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-6-2":

https://git.libreoffice.org/core/+/d3bbac9d0c75eaf59ab78b5f98f47d722874f194%5E%21

Resolves: tdf#108209 let auto fill handle double click stop at existing data

It will be available in 6.2.7.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 22 kabilo 2019-08-09 09:39:24 UTC
sorry, this correction has changed the filling of filled cells. Is it possible to check this 126767 ? Thanks
Comment 23 Pedro 2019-08-09 10:42:10 UTC
(In reply to Eike Rathke from comment #20)
> Too late for 6.2.6.2, but pending review for 6-2 (to-be 6.2.7)
> https://gerrit.libreoffice.org/77157

Thank you Eike!