Bug 147298 - Auto Calculate does not work on newly inserted columns in ods or xls formats
Auto Calculate does not work on newly inserted columns in ods or xls formats
 Status: VERIFIED FIXED None LibreOffice Unclassified Calc (show other bugs) 7.3.0.3 release All All high major Kohei Yoshida target:7.4.0 target:7.3.1 bibisected, bisected, regression (view as bug list)

 Reported: 2022-02-09 00:37 UTC by Herb B 2022-03-02 05:53 UTC (History) 12 users (show) aron.budea cbarring cloph erack ilmari.lauhakangas iqbalmy jo3emc kohei raal suokunlong tobyps xiscofauli

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.
 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.``` 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``` 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.``` 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.``` 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``` 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``` 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`.``` 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``` 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..``` 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.``` JO3EMC 2022-02-22 16:29:05 UTC `Sorry. Bug 147398.` JO3EMC 2022-02-22 16:45:00 UTC `この問題は、sum（）だけでなく、+演算子でも再現されました。` 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.``` 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.``` 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.``` Buovjaga 2022-02-23 07:32:15 UTC `Could someone please bibisect it on Windows https://wiki.documentfoundation.org/QA/Bibisect/Windows` 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 ***``` 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.``` 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``` 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``` 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``` Xisco Faulí 2022-02-23 13:04:27 UTC ```Regression introduced by: https://cgit.freedesktop.org/libreoffice/core/commit/?id=eb07a0e76fe240a184348d96a6cebf7c0a229ac0 author Kohei Yoshida 2021-11-01 14:01:22 -0400 committer Kohei Yoshida 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``` 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``` Xisco Faulí 2022-02-23 13:06:19 UTC `@Eike, I thought you might be interested in this issue` 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.``` 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.` Kohei Yoshida 2022-02-25 01:49:25 UTC `I'm looking.` 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.``` 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.``` 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.``` 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.` 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``` 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!``` 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.``` 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.` 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?``` 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"``` 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.``` 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.``` 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.``` 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!!``` 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.``` 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.``` 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.``` Buovjaga 2022-02-27 11:37:02 UTC `*** Bug 147684 has been marked as a duplicate of this bug. ***` 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.` 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.``` 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!``` 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?``` 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.``` 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.``` 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.``` Kevin Suo 2022-03-01 00:45:58 UTC `*** Bug 147623 has been marked as a duplicate of this bug. ***` 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!```