Bug 77990 - MATCH function result changes between save and load
Summary: MATCH function result changes between save and load
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.2.0.0.alpha0+ Master
Hardware: Other All
: medium normal
Assignee: Kohei Yoshida
URL:
Whiteboard: target:4.3.0 target:4.2.5
Keywords: bibisected, regression
: 78314 (view as bug list)
Depends on:
Blocks:
 
Reported: 2014-04-27 11:03 UTC by Laurent BP
Modified: 2015-12-17 07:59 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
Test case with wrong results in column D (81.34 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-04-27 11:03 UTC, Laurent BP
Details
Bibisect log (2.54 KB, text/x-log)
2014-04-27 17:22 UTC, Laurent BP
Details
reduced test document (20.94 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-05-02 00:12 UTC, Kohei Yoshida
Details
Even more reduced test document. (20.25 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-05-02 00:17 UTC, Kohei Yoshida
Details
one ISO date (10.87 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-05-02 00:35 UTC, Kohei Yoshida
Details
Test case with #N/A result of MATCH function in A2 (12.19 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-05-06 19:19 UTC, Laurent BP
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Laurent BP 2014-04-27 11:03:29 UTC
Created attachment 98067 [details]
Test case with wrong results in column D

Problem description: MATCH function returns #N/A because it refers to a cell on a different sheet which is not updated.

Steps to reproduce:
1. Open attached file (a simplified version of http://trk.free.fr/calendrier/download.html)

Current behavior:
In sheet "Jours fériés", D5 (and other cells in the same column) display "#N/A".

Expected behavior:
If you open with LibO 4.1.5.3 the cells are empty.

Confirmed on qa@fr.libreoffice.org
http://nabble.documentfoundation.org/Calc-4-2-Probleme-de-mise-a-jour-des-fonctions-tt4106597.html
with:
- OpenSuse 13.1 Version 4.2.3.3 and fresh master build Version: 4.3.0.0.alpha1+
Build ID: c6c286f14468d341f5fd88edc39a37175a1b6caa
- Windows 7 Home Premium  Version 4.2.4.1 Build ID: d4c441391e20647b3d2e8dde4d20aa868e77e515 and
Version 4.3.0.0.alpha0+
Build ID: d2555ebb240fea2780f152e5ea39d145aab508fe
TinderBox: Win-x86@39, Branch:master, Time: 2014-04-14_07:59:5

The MATCH function returns #N/A. It refers to D1 which value comes from other sheet "Calendrier perpétuel".
Workaround: edit D1 and validate value, or Ctrl+Shift+F9 to force calculation.
May be resolution of bug 73113 forget some case?
Comment 1 Laurent BP 2014-04-27 16:48:10 UTC
Reproduce with Version: 4.2.0.4
Build ID: 05dceb5d363845f2cf968344d7adab8dcfb2ba71

Version: 4.2.0.0.beta1
Build ID: f4ca7b35f580827ad2c69ea6d29f7c9b48ebbac7

Version: 4.2.0.0.alpha1+
Build ID: d366c9b20ec86f3fe521812a0c22def3bfd1f05e
TinderBox: Win-x86@47-TDF, Branch:master, Time: 2013-11-14_07:51:04

Version: 4.2.0.0.alpha0+
Build ID: cc2a405915e82c4b332dd25457f76704dc536d7f
TinderBox: Win-x86@39, Branch:master, Time: 2013-10-15_15:51:52

NOT reproduce with Version: 4.2.0.0.alpha0+
Build ID: 2f6cbe13e61c44d4bab8192a4708b698d3d9da33
TinderBox: Win-x86@6-debug, Branch:master, Time: 2013-07-25_00:00:21

Update version to 4.2.0.0.alpha0+
Comment 2 Laurent BP 2014-04-27 17:21:18 UTC
Bibisected:
877eba427973656e85b272a072f2b3cc4827c2ac is the first bad commit
commit 877eba427973656e85b272a072f2b3cc4827c2ac
Author: Bjoern Michaelsen <bjoern.michaelsen@canonical.com>
Date:   Wed Nov 27 04:52:19 2013 +0000

    source-hash-dace560b350346b9f9a102ee602bb129a008bcfe
    
    commit dace560b350346b9f9a102ee602bb129a008bcfe
    Author:     Caolán McNamara <caolanm@redhat.com>
    AuthorDate: Fri Oct 11 20:45:22 2013 +0100
    Commit:     Caolán McNamara <caolanm@redhat.com>
    CommitDate: Fri Oct 11 21:10:05 2013 +0100
    
        CID#736194 out of bounds
    
        Change-Id: I236c2f51716bfebd0c132bbaed50c1f1ec811ffa

:100644 100644 6e761d476a5f193388cf49d4a235be357f596c59 868adcf81ad9e8b7bf1c5b607608e9fc40410cd1 M      autogen.log
:100644 100644 0df89570ac01925eb0d92bd5cea375a1a6102e2e a74bef738e29c8062a7b26e82f3f98f84047a8a7 M      ccache.log
:100644 100644 ac150765aec93a2b2b2265bb72fbfae58043c9d4 18f2ed94c2f58a4e7a3fd7655d900b46f821dc47 M      commitmsg
:100644 100644 d9b7bc1a1c9b626c52ad13f1a42e3d11390d8c83 ea6a328f2ef10523d7195d77a3d5a840b892afcc M      make.log
:040000 040000 4ed0bc78fa4d8a698b6800cf0e8ff4c8c23dc2cf d2ef3a0d29599a9448f75c589c4671b3ad83a219 M      opt
Comment 3 Laurent BP 2014-04-27 17:22:12 UTC
Created attachment 98086 [details]
Bibisect log

Log of bibisection
Comment 4 Laurent BP 2014-04-28 10:01:04 UTC
@Kohei: add you as CC 
Suggested commit http://cgit.freedesktop.org/libreoffice/core/commit/?id=7333881bb7b04f7e4e2a28638024ae82a9c14e81
Comment 5 Kohei Yoshida 2014-05-02 00:12:09 UTC
Created attachment 98318 [details]
reduced test document

I reduced the original test document to just 2 formula cells, on a single sheet.
Comment 6 Kohei Yoshida 2014-05-02 00:17:24 UTC
Created attachment 98319 [details]
Even more reduced test document.

Even more reduced test document.  Now it only has C9 as formula cell.

Open the document, do hard recalc (Ctrl-Shift-F9), save it, reload it, do hard recalc again, save it, reload it and on and on and on.  The value of C9 keeps changing.
Comment 7 Kohei Yoshida 2014-05-02 00:18:50 UTC
Actually why is the value of B1 changing on every reload?
Comment 8 Kohei Yoshida 2014-05-02 00:35:40 UTC
Created attachment 98321 [details]
one ISO date

B1 is supposed to be 1977-12-23.  When you load this doc in 4.0, it rightly displays 1977-12-13.  The file itself contains that date.  Yet, when you load this using the master build, it becomes 1973-12-22.
Comment 9 Kohei Yoshida 2014-05-02 00:36:39 UTC
This is a date related.  I'll put Eike on CC.  4.2 doesn't seem to have this problem.  Only master.
Comment 10 Laurent BP 2014-05-05 06:23:58 UTC
(In reply to comment #9)
> This is a date related.  I'll put Eike on CC.  4.2 doesn't seem to have this
> problem.  Only master.

Hi Kohei,

It seems there is two different bugs.
* Bug described in comment #0
I can NOT reproduce if I revert to commit 6255be7ca294d350143290c343673f264f42220c
Version: 4.2.0.0.alpha0+
Build ID: 10696a254f0cea548a9d06d62c20d37acde2c46f

If I cherry-pick next commit 7333881bb7b04f7e4e2a28638024ae82a9c14e81
I CAN reproduce. Then I confirm suggestion of comment #4 and it affect 4.2.*

* Bug described in comment #9
This bug does NOT affect any version I tested around the previous commit.
Comment 11 Eike Rathke 2014-05-05 11:28:54 UTC
@Kohei:
Did you by chance have the Tools->Options->Calc->Calculate Date set to 1904-01-01 when you loaded the document but not when you created it? Then I can reproduce the simple test case you attached (submitted as bug 78294), otherwise not. The error Laurent originally described seems to be something different and persists in any case.
Comment 12 Kohei Yoshida 2014-05-06 17:04:01 UTC
(In reply to comment #11)
> @Kohei:
> Did you by chance have the Tools->Options->Calc->Calculate Date set to
> 1904-01-01 when you loaded the document but not when you created it?

Nope, it's set to the default option of 12/30/1899.

 Then I
> can reproduce the simple test case you attached (submitted as bug 78294),
> otherwise not. The error Laurent originally described seems to be something
> different and persists in any case.

I get that.  But unfortunately the above date issue prevents me from even looking into it or even analyzing the test document.

It would be nice for someone to work on reducing the test document to a bare minimum that can still reproduce the problem.  Otherwise this one is a bit hard to work on.
Comment 13 Laurent BP 2014-05-06 18:05:05 UTC
(In reply to comment #12)
> It would be nice for someone to work on reducing the test document to a bare
> minimum that can still reproduce the problem.
I'm going to have a look at it tonight.
Comment 14 Laurent BP 2014-05-06 19:11:45 UTC
The bug occurs with the following conditions:
- MATCH function with type=0 (exact match)
- search criterion is formatted as date
- search criterion is a formula

Create a test case from scratch (see attached file):
- create a new spreadsheet
- format cell A1 as date
- insert formula in A1 "=B1"
- insert a text in B1 such as "test2"
- in C1:C3 insert text "test1" "test2" "test3"
- in A2 insert formula "=MATCH(A1;C1:C3;0)"
Result is 2 as expected
- save file
- File > Reload
=> MATCH function returns #N/A
Ctrl+Shift+F9 calculates correctly
Comment 15 Eike Rathke 2014-05-06 19:15:37 UTC
(In reply to comment #12)
> (In reply to comment #11)
> > Did you by chance have the Tools->Options->Calc->Calculate Date set to
> > 1904-01-01 when you loaded the document but not when you created it?
> 
> Nope, it's set to the default option of 12/30/1899.

That's completely odd. I could reproduce your case only when the date was 1904 (check on a newly created empty document) before loading the testcase. Maybe http://cgit.freedesktop.org/libreoffice/core/commit/?id=571cefca474e6b77d68f9fa31f805dcf692927fd also helps your problem anyhow..
Comment 16 Laurent BP 2014-05-06 19:19:02 UTC
Created attachment 98582 [details]
Test case with #N/A result of MATCH function in A2

In attachment 98067 [details] if you change format of search criterion D1 to General, then the bug disappear.
Comment 17 Eike Rathke 2014-05-06 19:20:28 UTC
Thanks Laurent! Great analysis!
Comment 18 Laurent BP 2014-05-06 19:27:14 UTC
To reproduce the bug, search criterion could be of *any number format* (not necessary date) different from General: percent, currency, text...
Other cell format (font, background, border, etc.) has no effect on the bug.
Comment 19 Kohei Yoshida 2014-05-06 20:19:33 UTC
The new test document looks great.  I'll take a look.
Comment 20 Commit Notification 2014-05-06 22:50:43 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=1899bd0c3b8a16ede2de0125e762b23ba013d81f

fdo#77990: Intern strings for hybrid strings during import.



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 21 Commit Notification 2014-05-06 22:50:59 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=740dac4d58a95709eb11fc7434a7904ff8fafbd8

fdo#77990: Write test for this, using the 2nd test document submitted.



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 22 Kohei Yoshida 2014-05-06 22:51:58 UTC
4.2 backport: https://gerrit.libreoffice.org/9264

BTW, bibisect doesn't seem to work well with this one (any many of my other commits).  It's far off.
Comment 23 Commit Notification 2014-05-07 03:51:06 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "libreoffice-4-2":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=bc74773e8c5d62f4fe932366f1fae5bebbd19f65&h=libreoffice-4-2

fdo#77990: Intern strings for hybrid strings during import.


It will be available in LibreOffice 4.2.5.

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 24 Kohei Yoshida 2014-05-07 04:00:10 UTC
Fixed.
Comment 25 Laurent BP 2014-05-07 11:08:54 UTC
Verified with fresh build of master Version: 4.3.0.0.alpha1+
Build ID: 6cc92a2fead337eef3d9cc3e5818cfcc505e4651

Thanks Kohei :)
Comment 26 Laurent BP 2014-05-07 14:20:13 UTC
Verified on source test file with Version: 4.2.5.0.0+
Build ID: bc74773e8c5d62f4fe932366f1fae5bebbd19f65
Comment 27 Jacques Guilleron 2014-05-08 05:43:41 UTC
*** Bug 78314 has been marked as a duplicate of this bug. ***
Comment 28 Robinson Tryon (qubit) 2015-12-17 07:59:25 UTC
Migrating Whiteboard tags to Keywords: (bibisected)
[NinjaEdit]