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?
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+
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
Created attachment 98086 [details] Bibisect log Log of bibisection
@Kohei: add you as CC Suggested commit http://cgit.freedesktop.org/libreoffice/core/commit/?id=7333881bb7b04f7e4e2a28638024ae82a9c14e81
Created attachment 98318 [details] reduced test document I reduced the original test document to just 2 formula cells, on a single sheet.
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.
Actually why is the value of B1 changing on every reload?
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.
This is a date related. I'll put Eike on CC. 4.2 doesn't seem to have this problem. Only master.
(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.
@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.
(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.
(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.
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
(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..
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.
Thanks Laurent! Great analysis!
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.
The new test document looks great. I'll take a look.
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.
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.
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.
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.
Fixed.
Verified with fresh build of master Version: 4.3.0.0.alpha1+ Build ID: 6cc92a2fead337eef3d9cc3e5818cfcc505e4651 Thanks Kohei :)
Verified on source test file with Version: 4.2.5.0.0+ Build ID: bc74773e8c5d62f4fe932366f1fae5bebbd19f65
*** Bug 78314 has been marked as a duplicate of this bug. ***
Migrating Whiteboard tags to Keywords: (bibisected) [NinjaEdit]