Bug 147298 - Auto Calculate does not work on newly inserted columns in ods or xls formats
Summary: Auto Calculate does not work on newly inserted columns in ods or xls formats
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
7.3.0.3 release
Hardware: All All
: high major
Assignee: Kohei Yoshida
URL:
Whiteboard: target:7.4.0 target:7.3.1
Keywords: bibisected, bisected, regression
: 147623 147684 (view as bug list)
Depends on:
Blocks:
 
Reported: 2022-02-09 00:37 UTC by Herb B
Modified: 2022-03-02 05:53 UTC (History)
12 users (show)

See Also:
Crash report or crash signature:
Regression By:


Attachments
Excel spreadsheet showing the auto calculate bug (5.27 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2022-02-12 15:21 UTC, Herb B
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Herb B 2022-02-09 00:37:06 UTC
Description:
Create a new spreadsheet. ods or xls. Insert some numbers in a couple of rows of say column B. Use sum function to add those numbers up. Do same in Column C. Sums are correct. Now insert a new column between B and C. Again put some numbers in a couple rows.  Sum those numbers. Now change any of the numbers in new column. Sum does not auto calculate. ctrl-shift-f9 will force sum to be correct.

Steps to Reproduce:
1. Create new spreadsheet or use any existing spreadsheet.  ods or xls
2. Put some numbers in Columns B and C.
3. Use Sum function to total the numbers  =sum(B3:B5)  =sum(C3:C5)
4. Insert new column between B and C.
5. Put some numbers in rows 3, 4, 5.
6. Sum up those numbers  =sum(c3:c5)  in col C row 6. 
7. Change any of the values in col C row 3, 4 or 5.  
8. Sum does not change.   F9 calculate or ctrl-shift-f9 will correct number but must be done each time a value changes in newly inserted column.

Actual Results:
 adding up 2+3+4 = 9  but then changing say 2 to 5 
will result in 5+3+4 = 9   NOT the 12 as expected
Verified AutoCalculate is turned on under Data..Calculate options.

Expected Results:
Produce the correct sum of the rows in the column of numbers.
Saving and opening spreadsheet seems to correct problem with that inserted column.
But then newly inserted columns will again have same problem.


Reproducible: Always


User Profile Reset: No



Additional Info:
Windows 11 with all latest patches installed. Intel core i5, 8GB ram, Lenovo T580
Version: 7.3.0.3 (x64) / LibreOffice Community
Build ID: 0f246aa12d0eee4a0f7adcefbf7c878fc2238db3
CPU threads: 8; OS: Windows 10.0 Build 22000; UI render: Skia/Vulkan; VCL: win
Locale: en-US (en_US); UI: en-US
Calc: threaded

I did a complete uninstall of LibreOffice, then did a full clean reinstall. Same issue happens every time.
Comment 1 Herb B 2022-02-12 15:21:45 UTC
Created attachment 178239 [details]
Excel spreadsheet showing the auto calculate bug

See instructions in workbook to easily recreate this bug.
bug also occurs in Opencalc ods file formats
Comment 2 Herb B 2022-02-12 15:22:58 UTC
Bug should probably be a 'major' bug since workbook values are incorrect.
Easily noticeable on very small spreadsheets but could be problematic on large workbooks.
Comment 3 Herb B 2022-02-13 16:55:58 UTC
Just retested and confirmed this bug is not in 7.2.5.2

Also downloaded and clean installed 7.3.1.1. This bug does exist in 
this version as well.
Comment 4 raal 2022-02-13 18:04:33 UTC
No repro with Version: 7.4.0.0.alpha0+ / LibreOffice Community
Build ID: 23846867ea32667ccf328c36142394dd6aaee8ba
CPU threads: 4; OS: Linux 5.11; UI render: default; VCL: gtk3
Locale: en-MY (cs_CZ.UTF-8); UI: en-US
Calc: threaded
Comment 5 Herb B 2022-02-13 21:17:25 UTC
Just downloaded and installed 7.4.0.0 alpha.  Windows 11 x64.
Bug still exists. Auto calculate not working. 

Version: 7.4.0.0.alpha0+ (x64) / LibreOffice Community
Build ID: 95b80365218f9406a5d952c1250d53222d319000
CPU threads: 8; OS: Windows 10.0 Build 22000; UI render: Skia/Vulkan; VCL: win
Locale: en-US (en_US); UI: en-US
Calc: threaded
Comment 6 Imran Iqbal 2022-02-17 12:16:24 UTC
Hitting the same problem ever since I upgraded to the following version:

```
Version: 7.3.0.3 / LibreOffice Community
Build ID: 30(Build:3)
CPU threads: 8; OS: Linux 5.4; UI render: default; VCL: x11
Locale: en-GB (en_GB.UTF-8); UI: en-GB
Ubuntu package version: 1:7.3.0~rc3-0ubuntu0.20.04.1~lo1
Calc: threaded
```

The only workaround I've found is hitting `Ctrl+Shift+F9`, i.e. `Data > Calculate > Recalculate Hard`.
Comment 7 JO3EMC 2022-02-22 15:27:22 UTC
It is reproduced in 7.3.0.3, 7.3.1.1, 7.4.0.0a0.
There is no problem with 7.2.5.2.
I think it's fatal.
Immediate response is desired.

I haven't been able to determine the reproduction conditions, but in my environment it seems that the same phenomenon can occur not only with column insertions, but also with simple copies.

Version: 7.3.0.3 (x64) / LibreOffice Community
Build ID: 0f246aa12d0eee4a0f7adcefbf7c878fc2238db3
CPU threads: 8; OS: Windows 10.0 Build 19044; UI render: default; VCL: win
Locale: ja-JP (ja_JP); UI: ja-JP
Calc: CL

Version: 7.3.1.1 (x64) / LibreOffice Community
Build ID: 349cd3ad57dce98d6b54b76f8e9f456ac7d7edb7
CPU threads: 8; OS: Windows 10.0 Build 19044; UI render: Skia/Vulkan; VCL: win
Locale: ja-JP (ja_JP); UI: ja-JP
Calc: CL

Version: 7.4.0.0.alpha0+ (x64) / LibreOffice Community
Build ID: 2bb10a827ac13d0caf009e8526ccd9f17dc71653
CPU threads: 8; OS: Windows 10.0 Build 19044; UI render: Skia/Raster; VCL: win
Locale: ja-JP (ja_JP); UI: ja-JP
Calc: CL
Comment 8 Herb B 2022-02-22 16:05:44 UTC
I agree the importance should be changed from normal to 'major' or 'critical'. .I dont have the authority to do that.
This problem is producing wrong results. Easily reproduceable. I also see problem on existing columns, but havent figured out to exactly reproduce that..yet..
Comment 9 JO3EMC 2022-02-22 16:28:20 UTC
Also, recalculation (F9) in the sheet full selection (Ctrl + A) state does not work.

I haven't verified it enough yet, but it may be the same problem as Bug 147 398. It seems that it has been fixed and reflected in 7.3.1.
Comment 10 JO3EMC 2022-02-22 16:29:05 UTC
Sorry. Bug 147398.
Comment 11 JO3EMC 2022-02-22 16:45:00 UTC
この問題は、sum()だけでなく、+演算子でも再現されました。
Comment 12 JO3EMC 2022-02-22 16:46:30 UTC
excuse me... for my post in Japanese.

This issue reproduced not only with sum(), but also with + operator.
Comment 13 Herb B 2022-02-22 16:51:31 UTC
Confirmed.  I just tried same test using + operator instead of sum. Same problem.
I also just put some values in some existing columns to the right of columns that already had some values in it.  Then copied over the sum formula. Changes in values do not result in correct sum. So problem is not confined to newly inserted columns.
Comment 14 JO3EMC 2022-02-23 01:24:24 UTC
Bug 147398 seems to have been fixed in 2022-02-17 in 7.4.0 and 7.3.2.
I tried today's daily build, but this issue doesn't seem to be fixed.
It may be another issue.
Comment 15 Buovjaga 2022-02-23 07:32:15 UTC
Could someone please bibisect it on Windows https://wiki.documentfoundation.org/QA/Bibisect/Windows
Comment 16 Xisco Faulí 2022-02-23 08:58:50 UTC
I created a unittest for bug 147398 and this one looks pretty much the same as that one.
This should be fixed in LibreOffice 7.3.1, due to be released next week.

*** This bug has been marked as a duplicate of bug 147398 ***
Comment 17 JO3EMC 2022-02-23 09:37:25 UTC
(In reply to Xisco Faulí from comment #16)
> I created a unittest for bug 147398 and this one looks pretty much the same
> as that one.
> This should be fixed in LibreOffice 7.3.1, due to be released next week.
> 
> *** This bug has been marked as a duplicate of bug 147398 ***

No.
7.3.2 and 7.4.0a0, which resolve Bug 147 398, do not resolve this Bug 148 298 issue.
I don't think it is expected to be resolved even in 7.3.1.
Comment 18 Xisco Faulí 2022-02-23 09:46:56 UTC
(In reply to JO3EMC from comment #17)
> (In reply to Xisco Faulí from comment #16)
> > I created a unittest for bug 147398 and this one looks pretty much the same
> > as that one.
> > This should be fixed in LibreOffice 7.3.1, due to be released next week.
> > 
> > *** This bug has been marked as a duplicate of bug 147398 ***
> 
> No.
> 7.3.2 and 7.4.0a0, which resolve Bug 147 398, do not resolve this Bug 148
> 298 issue.
> I don't think it is expected to be resolved even in 7.3.1.

Please, share the info from Help - About LibreOffice
Comment 19 JO3EMC 2022-02-23 09:56:30 UTC
(In reply to Xisco Faulí from comment #18)
> Please, share the info from Help - About LibreOffice

Version: 7.3.2.0.0+ (x64) / LibreOffice Community
Build ID: 9e32472d623998a1beaaf69342c53d1be5d1732f
CPU threads: 8; OS: Windows 10.0 Build 19044; UI render: Skia/Vulkan; VCL: win
Locale: ja-JP (ja_JP); UI: ja-JP
Calc: CL

Version: 7.4.0.0.alpha0+ (x64) / LibreOffice Community
Build ID: 4202ea2c932a14d216e74617bbb74a85030c9a59
CPU threads: 8; OS: Windows 10.0 Build 19044; UI render: Skia/Raster; VCL: win
Locale: ja-JP (ja_JP); UI: ja-JP
Calc: CL
Comment 20 Xisco Faulí 2022-02-23 12:55:06 UTC
Reproduced in

Version: 7.4.0.0.alpha0+ (x64) / LibreOffice Community
Build ID: b1fe159fa39b2fc5217848e14d68c674979c181c
CPU threads: 16; OS: Windows 6.3 Build 9600; UI render: default; VCL: win
Locale: en-GB (en_GB); UI: en-US
Calc: threaded

Using the attached file
Comment 21 Xisco Faulí 2022-02-23 13:04:27 UTC
Regression introduced by:

https://cgit.freedesktop.org/libreoffice/core/commit/?id=eb07a0e76fe240a184348d96a6cebf7c0a229ac0

author	Kohei Yoshida <kohei@libreoffice.org>	2021-11-01 14:01:22 -0400
committer	Kohei Yoshida <kohei@libreoffice.org>	2021-11-03 21:17:18 +0100
commit eb07a0e76fe240a184348d96a6cebf7c0a229ac0 (patch)
tree 23ab960b7a163696e4a7c1d4c4c20c1340fa14b3
parent 9b9f4a4487e9ada1885d45a8b1ba0234a4a9fc26 (diff)
Upgrade mdds and liborcus to 2.0.0 and 0.17.0, respectively.

Bisected with: bibisect-linux64-7.3

Adding Cc: to Kohei Yoshida
Comment 22 Xisco Faulí 2022-02-23 13:05:57 UTC
Also happening in

Version: 7.4.0.0.alpha0+ / LibreOffice Community
Build ID: 0723b41bed9bb4ad50d2993744a60177966d1a21
CPU threads: 8; OS: Linux 5.10; UI render: default; VCL: gtk3
Locale: es-ES (es_ES.UTF-8); UI: en-US
Calc: threaded

using the file attached

@Eike, I thought you might be interested in this issue
Comment 23 Xisco Faulí 2022-02-23 13:06:19 UTC
@Eike, I thought you might be interested in this issue
Comment 24 Herb B 2022-02-23 18:26:47 UTC
I just downloaded and tested this version:
Version: 7.2.6.1 (x64) / LibreOffice Community
Build ID: ce99d6a58f9368279ff1495b5b367eb64343b26c
CPU threads: 8; OS: Windows 10.0 Build 22000; UI render: Skia/Vulkan; VCL: win
Locale: en-US (en_US); UI: en-US
Calc: threaded

This bug does NOT seem to exist in this version.
Both sum and '+' operator auto calcing correctly on changed 
data values.
Comment 25 Eike Rathke 2022-02-23 20:03:06 UTC
If this is a regression of the upgrade to mdds 2.0 as comment 21 indicates then best Kohei would inspect.
Comment 26 Kohei Yoshida 2022-02-25 01:49:25 UTC
I'm looking.
Comment 27 Kohei Yoshida 2022-02-25 04:31:45 UTC
Something like this should fix it.

https://gerrit.libreoffice.org/c/core/+/130510

The fix was needed on the Calc side.
Comment 28 Kohei Yoshida 2022-02-25 13:19:11 UTC
(In reply to Kohei Yoshida from comment #27)
> Something like this should fix it.
> 
> https://gerrit.libreoffice.org/c/core/+/130510
> 
> The fix was needed on the Calc side.

Actually, hold on.  That may not be the right fix.
Comment 29 Kohei Yoshida 2022-02-25 14:39:26 UTC
(In reply to Kohei Yoshida from comment #28)

> Actually, hold on.  That may not be the right fix.

It is the right fix. Nevermind.
Comment 30 Kohei Yoshida 2022-02-25 14:41:54 UTC
Ok. I no longer have the ability to +2 and merge to master.  Someone will have to review and approve the above fix.
Comment 31 Xisco Faulí 2022-02-25 14:47:34 UTC
(In reply to Kohei Yoshida from comment #30)
> Ok. I no longer have the ability to +2 and merge to master.  Someone will
> have to review and approve the above fix.

I think you do, but since you changed the commit message the jenkins verification is gone. Just click on Verified+1 and then you can submit it
Comment 32 Kohei Yoshida 2022-02-25 14:50:30 UTC
(In reply to Xisco Faulí from comment #31)
> (In reply to Kohei Yoshida from comment #30)
> > Ok. I no longer have the ability to +2 and merge to master.  Someone will
> > have to review and approve the above fix.
> 
> I think you do, but since you changed the commit message the jenkins
> verification is gone. Just click on Verified+1 and then you can submit it

Ah, that was it. Thanks!
Comment 33 Commit Notification 2022-02-25 14:50:56 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/8f25d42938977b34f3281238a92fb34ebea5838c

tdf#147298: When swapping the cell stores, swap back the event handlers.

It will be available in 7.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 34 Kevin Suo 2022-02-25 22:42:20 UTC
I think this definitely should go to 7.3.1.3 before 7.3.1 is released.
Comment 35 Kohei Yoshida 2022-02-25 22:58:40 UTC
(In reply to Kevin Suo from comment #34)
> I think this definitely should go to 7.3.1.3 before 7.3.1 is released.

It's already here: https://gerrit.libreoffice.org/c/core/+/130464

and I gave 2+.  But can someone remind me what our backporting rules are?  Xisco created that backport ticket and I gave it 2+.  Is it okay for me to merge it, or should Xisco do it?  Or maybe it doesn't matter who does it?
Comment 36 Buovjaga 2022-02-25 23:12:18 UTC
(In reply to Kohei Yoshida from comment #35)
> (In reply to Kevin Suo from comment #34)
> > I think this definitely should go to 7.3.1.3 before 7.3.1 is released.
> 
> It's already here: https://gerrit.libreoffice.org/c/core/+/130464
> 
> and I gave 2+.  But can someone remind me what our backporting rules are? 
> Xisco created that backport ticket and I gave it 2+.  Is it okay for me to
> merge it, or should Xisco do it?  Or maybe it doesn't matter who does it?

Rules are here: https://wiki.documentfoundation.org/Development/Branches

Unfortunately 7.3.1 RC2 is already out. This means an extra RC would have to be cut.

Anyway, you should definitely merge the patch you reference ASAP. Then, as mentioned in the wiki article, pick it from 7-3 to 7-3-1: "Only cherry-picking from libreoffice-X-Y, the 2nd reviewer pushes"
Comment 37 Commit Notification 2022-02-25 23:17:27 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "libreoffice-7-3":

https://git.libreoffice.org/core/commit/7d64ec7d342da64e050ed9ac326b1af33d5c6955

tdf#147298: When swapping the cell stores, swap back the event handlers.

It will be available in 7.3.2.

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 38 Kohei Yoshida 2022-02-25 23:34:12 UTC
(In reply to Buovjaga from comment #36)

> Rules are here: https://wiki.documentfoundation.org/Development/Branches

Thanks!  That's the page I was looking for.

> Anyway, you should definitely merge the patch you reference ASAP.

Done!

> Then, as
> mentioned in the wiki article, pick it from 7-3 to 7-3-1:

https://gerrit.libreoffice.org/c/core/+/130571

Hopefully I did it right.  I know gerrit these days has a cherry-pick function, but I did it the old-fashioned way.
Comment 39 Commit Notification 2022-02-26 00:43:52 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/974bf22680b702b9474d4a91dbf1d06a785ff774

tdf#147298: Add a simple test case for formula cell tracking by column.

It will be available in 7.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 40 Herb B 2022-02-26 14:47:56 UTC
I retested this bug using test spreadsheet I uploaded to this ticket.

Both the sum operator and '+' operator are now working correctly.

I tested with the daily build of 7.3.2.0 built on 2/26/22.
Tested on Windows 11 Pro, build 22000, Lenovo i5 laptop.

Version: 7.3.2.0.0+ (x64) / LibreOffice Community
Build ID: 7d64ec7d342da64e050ed9ac326b1af33d5c6955
CPU threads: 8; OS: Windows 10.0 Build 22000; UI render: Skia/Vulkan; VCL: win
Locale: en-US (en_US); UI: en-US
Calc: threaded

Thanks for the quick fix!!
Comment 41 Kevin Suo 2022-02-26 15:02:21 UTC
(In reply to Buovjaga from comment #36)
> Unfortunately 7.3.1 RC2 is already out. This means an extra RC would have to be cut.

7.3.1 RC2 is out but 7.3.1 is not officially release to the public. I suggest we do a 7.3.1 RC3 (even though it was not scheduled) to incorporate this fix. Otherwise, users loose confidence to our product, given that this bug was in the 7.3.0 release and we did a lot of advertising on that major release.
Comment 42 Kevin Suo 2022-02-26 15:06:13 UTC
And yes, I do see there is a cherry-pick to libreoffice-7-3-1 on the way:
https://gerrit.libreoffice.org/c/core/+/130571

Thanks.
Comment 43 JO3EMC 2022-02-27 01:26:16 UTC
I confirmed that it does not reproduce with 7.3.2.0 and 7.4.0.0a0 daily build.
Thanks for all your quick efforts.

I hope that this result will be reflected in 7.3.1.
Comment 44 Buovjaga 2022-02-27 11:37:02 UTC
*** Bug 147684 has been marked as a duplicate of this bug. ***
Comment 45 Buovjaga 2022-02-28 09:14:58 UTC
I started a discussion about an extra RC in the dev chat, but sadly the idea did not get support. If you want to argue for it, please join now and say what you want to say or send an email to the dev list. Somehow the thought is that it should be decided in ESC meeting, which would lead to delaying the release, but I don't understand why the process should be so complicated.
Comment 46 Commit Notification 2022-02-28 11:21:14 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "libreoffice-7-3":

https://git.libreoffice.org/core/commit/d54dfc3d3d241b7430accf17a249406bc9cbfb6c

tdf#147298: Add a simple test case for formula cell tracking by column.

It will be available in 7.3.2.

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 47 Kevin Suo 2022-02-28 11:49:51 UTC
(In reply to Buovjaga from comment #45)
I did it just now in the IRC channel, but faced objection from tango-mike-lima[. I really don't understand it - we can delay the release for one or two days to incorporate this fix, but why do we insist to release a a bug fix version with such a serious bug? The release policy should be improved!
Comment 48 Aron Budea 2022-02-28 13:28:46 UTC
(In reply to Buovjaga from comment #45)
> I started a discussion about an extra RC in the dev chat, but sadly the idea
> did not get support. If you want to argue for it, please join now and say
> what you want to say or send an email to the dev list. Somehow the thought
> is that it should be decided in ESC meeting, which would lead to delaying
> the release, but I don't understand why the process should be so complicated.
Cloph, what would be your thoughts on this?
Comment 49 Commit Notification 2022-02-28 17:34:31 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "libreoffice-7-3-1":

https://git.libreoffice.org/core/commit/1c3c72bf20914b13960508ec337de96f82b8225b

tdf#147298: When swapping the cell stores, swap back the event handlers.

It will be available in 7.3.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 50 Herb B 2022-02-28 19:42:26 UTC
So, will I eventually see a  7.3.1.3? that includes this fix here at this site to test ??
https://download.documentfoundation.org/libreoffice/testing/7.3.1/win/x86_64/

If not, where would I find the official RC build to test that includes this fix
(not a LibreOfficeDEV build ... That I found and tested. )

(new to all this)
Thanks.
Comment 51 Buovjaga 2022-02-28 20:31:43 UTC
(In reply to Herb B from comment #50)
> So, will I eventually see a  7.3.1.3? that includes this fix here at this
> site to test ??
> https://download.documentfoundation.org/libreoffice/testing/7.3.1/win/x86_64/

Yes, when the build is made eventually.
Comment 52 Kevin Suo 2022-03-01 00:45:58 UTC
*** Bug 147623 has been marked as a duplicate of this bug. ***
Comment 53 Herb B 2022-03-01 17:10:40 UTC
I retested this bug using test spreadsheet I uploaded to this ticket.

Both the sum operator and '+' operator are now working correctly and as expected.

I tested with the 7.3.1.3 available here:
https://download.documentfoundation.org/libreoffice/testing/7.3.1/win/x86_64/
Tested on Windows 11 Pro, build 22000, Lenovo i5 laptop.

About box info from version I tested:
Version: 7.3.1.3 (x64) / LibreOffice Community
Build ID: a69ca51ded25f3eefd52d7bf9a5fad8c90b87951
CPU threads: 8; OS: Windows 10.0 Build 22000; UI render: Skia/Vulkan; VCL: win
Locale: en-US (en_US); UI: en-US
Calc: threaded

Thanks all!