Bug 93328 - Editing circular reference causes #VALUE! error
Summary: Editing circular reference causes #VALUE! error
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.0.0.3 release
Hardware: All Linux (All)
: high minor
Assignee: Dennis Francis
QA Contact:
URL:
Whiteboard: target:6.0.0 target:5.4.1
Keywords: bibisected, bisected, regression
Depends on:
Blocks: Cell-Reference
  Show dependency treegraph
 
Reported: 2015-08-10 15:22 UTC by martyjwolf
Modified: 2017-07-20 18:46 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:


Attachments
file with circular reference error (14.25 KB, application/vnd.oasis.opendocument.spreadsheet)
2015-08-10 15:22 UTC, martyjwolf
Details

Note You need to log in before you can comment on or make changes to this bug.
Description martyjwolf 2015-08-10 15:22:56 UTC
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).
Comment 1 raal 2015-08-21 06:41:46 UTC
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
Comment 2 raal 2015-09-23 08:57:47 UTC
not in this range bibisect-win32-5.0, regression must be younger
Comment 3 GerardF 2015-09-23 09:21:53 UTC
With LO 4.4.5, I get #VALUE! when I add +1 in G15.
But a hard recalc (Ctrl+Shift+F9) works.
Comment 4 Robinson Tryon (qubit) 2015-12-14 05:32:36 UTC Comment hidden (obsolete)
Comment 5 Joel Madero 2015-12-20 07:06:32 UTC
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
Comment 6 Aron Budea 2016-10-18 04:27:58 UTC
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.
Comment 7 Eike Rathke 2017-01-30 17:40:03 UTC
Seems got fixed, tried master, 5.3.0 and 5.2.5
Comment 8 Aron Budea 2017-01-30 20:39:36 UTC
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.
Comment 9 Kohei Yoshida 2017-03-24 22:44:46 UTC
Can someone remind me what would be the correct behavior here?
Comment 10 Kohei Yoshida 2017-03-24 22:51:16 UTC
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.
Comment 11 Aron Budea 2017-03-25 00:01:42 UTC
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.
Comment 12 Kohei Yoshida 2017-03-25 01:20:29 UTC
(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.
Comment 13 Aron Budea 2017-03-25 02:58:03 UTC
(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.
Comment 14 Xavier Van Wijmeersch 2017-07-10 17:51:51 UTC
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
Comment 15 Commit Notification 2017-07-19 17:46:10 UTC
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.
Comment 16 Commit Notification 2017-07-19 18:57:48 UTC
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.
Comment 17 Xavier Van Wijmeersch 2017-07-20 18:46:12 UTC
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