Created attachment 117810 [details] file with circular reference error In the attached spreadsheet, when either the cell g15 or g27 is edited (say include a "+1"), a #VALUE! error is generated throughout the circular reference loop. Editing of other cells in the loop do not (seem to consistently) generate this error. This same error does not occur in OpenOffice 4.4.1 (something I have access to), nor did it happen in the previous version of LibreOffice (with a more complicated version of this spreadsheet that I have been using for over 10 years).
Reproducible with Version: 5.1.0.0.alpha1+ Build ID: 6b7354ae66db40246a09e00aa876443057655a43 TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:master, Time: 2015-08-19_01:05:16 No repro with LibreOffice 3.5.0 Build ID: d6cde02, regression
not in this range bibisect-win32-5.0, regression must be younger
With LO 4.4.5, I get #VALUE! when I add +1 in G15. But a hard recalc (Ctrl+Shift+F9) works.
Migrating Whiteboard tags to Keywords: (bibisectRequest) [NinjaEdit]
This was introduced way back in 4.0 (updating version). Bibisect below: Minor - can slow down professional quality work but will not prevent it (hard recalc solves it) High - bumped up from low - regression + although there is a workaround it's not entirely obvious + not a complex sheet at all therefore likely to impact potentially large portion of users. a429a2e082aeb9bff36833603d8deb55385c7905 is the first bad commit commit a429a2e082aeb9bff36833603d8deb55385c7905 Author: Bjoern Michaelsen <bjoern.michaelsen@canonical.com> Date: Mon Dec 10 02:53:27 2012 +0000 source-hash-b8fa8841c098f15ef2280aa4c82c55c4f96325c9 commit b8fa8841c098f15ef2280aa4c82c55c4f96325c9 Author: Caolán McNamara <caolanm@redhat.com> AuthorDate: Tue Jul 24 22:17:13 2012 +0100 Commit: Caolán McNamara <caolanm@redhat.com> CommitDate: Wed Jul 25 10:02:15 2012 +0100 Related: #i13451# regression test for Catalan dictionary word breakiterator Change-Id: I7785746b2cf4e5e054ced5b728dc69e6b1a966f2 :100644 100644 3b6340e7054d644b3f4c744194f55b01c622c77e a1aa384f172f56e07ad1a98da87bf9800fadcfe1 M autogen.log :100644 100644 d750f6a144e1a8b218aaa246691b7c7e074a8d89 6d9139d0e9558eafa600297087e574a225918d8c M ccache.log :100644 100644 69407ea42d702621d638eaf5f60858bd43c84edc 1d5a4cfa5ba9cda2273098087884a8798f1505c0 M commitmsg :100644 100644 04d043590033e906e1a862b26a47a1d0b30bac91 ff1c5125eb5df535100be2a9cd8f14f832da8593 M dev-install.log :100644 100644 7aa7340eab5dd436b0c0640450127c0c5ccb2c73 9444313a7bf13056d189aa5355719391385c2ecd M make.log :040000 040000 eed070b3058fe583fb54b09cd58411bb9bec9c13 32aaaa4eca5e6b98b564216006b4a426a11c011a M opt # bad: [423a84c4f7068853974887d98442bc2a2d0cc91b] source-hash-c15927f20d4727c3b8de68497b6949e72f9e6e9e # good: [65fd30f5cb4cdd37995a33420ed8273c0a29bf00] source-hash-d6cde02dbce8c28c6af836e2dc1120f8a6ef9932 git bisect start 'latest' 'oldest' # bad: [e02439a3d6297a1f5334fa558ddec5ef4212c574] source-hash-6b8393474974d2af7a2cb3c47b3d5c081b550bdb git bisect bad e02439a3d6297a1f5334fa558ddec5ef4212c574 # bad: [8f4aeaad2f65d656328a451154142bb82efa4327] source-hash-1885266f274575327cdeee9852945a3e91f32f15 git bisect bad 8f4aeaad2f65d656328a451154142bb82efa4327 # good: [369369915d3582924b3d01c9b01167268ed38f3b] source-hash-45295f3cdceb4c289553791071b5d7f4962d2ec4 git bisect good 369369915d3582924b3d01c9b01167268ed38f3b # good: [6fce03a944bf50e90cd31e2d559fe8705ccc993e] source-hash-47e4a33a6405eb1b5186027f55bd9cb99b0c1fe7 git bisect good 6fce03a944bf50e90cd31e2d559fe8705ccc993e # good: [da317333e5675622f55c9dda17396c659af65320] source-hash-15af925c254f27046427de70a59011e2ac3d6bdb git bisect good da317333e5675622f55c9dda17396c659af65320 # bad: [18518588d8414f446ece5591944766f5082ebef5] source-hash-82c25249e624cb54ca6d3293d1c3d0d8ebc208e0 git bisect bad 18518588d8414f446ece5591944766f5082ebef5 # good: [89740762f0af849e492932bd71e59149cdcd5a00] source-hash-06f20d73da21342046a480a6b22af69901351328 git bisect good 89740762f0af849e492932bd71e59149cdcd5a00 # bad: [a429a2e082aeb9bff36833603d8deb55385c7905] source-hash-b8fa8841c098f15ef2280aa4c82c55c4f96325c9 git bisect bad a429a2e082aeb9bff36833603d8deb55385c7905 # good: [173f32b96a0224f28f311adf21d65f4d4e98dfa1] source-hash-22cf0759547aa1803f77dbd3ee91774600dadc6f git bisect good 173f32b96a0224f28f311adf21d65f4d4e98dfa1 # first bad commit: [a429a2e082aeb9bff36833603d8deb55385c7905] source-hash-b8fa8841c098f15ef2280aa4c82c55c4f96325c9
Adding Cc: to Kohei Yoshida. Please take a look. This bug began with the following commit: https://cgit.freedesktop.org/libreoffice/core/commit/?id=3a8f10ddb4bfbbf76590102bbcaa10c9aa2c0af3 "Honor the configuration option in the interpreter. Change-Id: I686d1f3703cbb122e1b3ed010a727163be2e942a" Because rConfig.mbEmptyStringAsZero is false, the program gets to pFormatter->IsNumberFormat(rStr, nFIndex, fValue), which return false for empty strings.
Seems got fixed, tried master, 5.3.0 and 5.2.5
I'm afraid I'm still getting it in 5.3.0.3, and 5.4 daily build ( 2017-01-29_05:25:38). The #VALUE! error is only there if "+1" is added to G15 (the error disappears on a hard recalc), but even with G27 the sheet requires an additional recalc for the values to stabilize.
Can someone remind me what would be the correct behavior here?
Assuming that the formula chain contains a circular reference, wouldn't it be correct to see errors in those cells? Or is the VALUE error not the correct error type to show, or... ? Clarification is appreciated.
I think circular references to variables cancel each other out. But the bug isn't with handling circular references here, as it displays the correct result (at least I assume so, didn't check the numbers) after adding/removing the +1 if you: - do a hard recalc, or - tell Calc to treat empty cells as zero in Options.
(In reply to Aron Budea from comment #11) > I think circular references to variables cancel each other out. No, they don't. A presence of circular reference is a sure way to get a VALUE error, unless the iterative calculation option is turned on. Either that, or maybe our definition of "circular reference" is different, which is also a possibility.
(In reply to Kohei Yoshida from comment #12) > No, they don't. A presence of circular reference is a sure way to get a > VALUE error, unless the iterative calculation option is turned on. Indeed, I only took a look at that option now, and it is enabled in the spreadsheet.
i think the value error is due mixing number and currency format i tested with a help cell first as normal number(value error) and than set as currency, the value error was gone Version: 6.0.0.0.alpha0+ Build ID: 18909d45977a897cbd921d76d1dde4bf3a466271 CPU threads: 8; OS: Linux 4.9; UI render: default; VCL: kde4; Locale: nl-BE (en_US.UTF-8); Calc: group
Dennis Francis committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=5f2bcde100c278315609c221c48ff03aacdf9bdc tdf#93328 : Assign the ScInterpreter results back to ScFormulaCell... It will be available in 6.0.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Dennis Francis committed a patch related to this issue. It has been pushed to "libreoffice-5-4": http://cgit.freedesktop.org/libreoffice/core/commit/?id=f5661ec96ea4e30abea2ad0b218024c6c747071b&h=libreoffice-5-4 tdf#93328 : Assign the ScInterpreter results back to ScFormulaCell... It will be available in 5.4.1. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
I tested the fix and it works Version: 6.0.0.0.alpha0+ Build ID: ee299664940139f6f9543592ece3b3c0210b59f4 CPU threads: 8; OS: Linux 4.9; UI render: default; VCL: kde4; Locale: nl-BE (en_US.UTF-8); Calc: group
Zdeněk Crhonek committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=6ed8083ab4124b7469e567d1b24c1471f1688e62 uitest for bug tdf#93328 It will be available in 6.2.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Luboš Luňák committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=9373590415342bf75ddd1960c90510c1c7139636 Revert "uitest for bug tdf#93328" It will be available in 6.2.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
The test exist, set status to Verified.