Bug 79249 - #N/A value is shown as "={nan}" in formular bar and "Err:512" as cell value
Summary: #N/A value is shown as "={nan}" in formular bar and "Err:512" as cell value
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.2.0.4 release
Hardware: All All
: high major
Assignee: Eike Rathke
URL:
Whiteboard: target:4.5.0 target:4.4.0.0.beta3 tar...
Keywords: bibisected, regression
Depends on:
Blocks:
 
Reported: 2014-05-26 08:26 UTC by Kevin Suo
Modified: 2020-03-16 08:11 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments
test xlsx file with #N/A value (12.22 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2014-05-26 08:26 UTC, Kevin Suo
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Kevin Suo 2014-05-26 08:26:15 UTC
Created attachment 99844 [details]
test xlsx file with #N/A value

Problem description:

The attached xlsx file has "#N/A" value in cell A1:A4. The file was created by MSO 2010. The "N/A" value was generated by VLOOKUP function and pasted special only values (thus there should be no formular in any of the cells)

When open in Calc, it shows value "Err:512" rather than "N/A", while in the formular bar it shows "={nan}".

Steps:
1. Just open the attached xlsx file in LibreOffice.

Current Behaviour:
It is showing "Err:512" in A1:A4, and "={nan}" in formular bar.

Expected:
It should show "#N/A" in A1:A4; there is no formular in the cells, so it should show "#N/A" in the formular bar.

OS: reproducible in windows xp, ubuntu 13.04 lts, and Fedora 20. So: ALL.
LibreOffice: 4.2.4.2.
Still reproducible in 4.3 beta 1.
Comment 1 Kevin Suo 2014-05-26 08:27:51 UTC
PossibleRegression.

See Also:
Bug 79180: VLOOKUP with #N/A value is showing blank when save as xlsx
Comment 2 Kevin Suo 2014-05-26 08:30:21 UTC
Just for information, when unzip the xlsx file, it show the following in /xl/worksheets/sheet1.xml:

<sheetData>
- <row r="1" spans="1:1" x14ac:dyDescent="0.2">
- <c r="A1" s="2" t="e">
  <v>#N/A</v> 
  </c>
  </row>
- <row r="2" spans="1:1" x14ac:dyDescent="0.2">
- <c r="A2" s="2" t="e">
  <v>#N/A</v> 
  </c>
  </row>
- <row r="3" spans="1:1" x14ac:dyDescent="0.2">
- <c r="A3" s="2" t="e">
  <v>#N/A</v> 
  </c>
  </row>
- <row r="4" spans="1:1" x14ac:dyDescent="0.2">
- <c r="A4" s="2" t="e">
  <v>#N/A</v> 
  </c>
  </row>
</sheetData>
Comment 3 m_a_riosv 2014-05-26 13:40:29 UTC
Hi Suokunlong, thanks for reporting.

Reproducible with:
Win7x64
Version: 4.2.0.4 Build ID: 05dceb5d363845f2cf968344d7adab8dcfb2ba71
Version: 4.2.4.2 Build ID: 63150712c6d317d27ce2db16eb94c2f3d7b699f8
Version: 4.4.0.0.alpha0+ Build ID: 16ba360f2a526654cedfcd4cda230442c3a8a522
   TinderBox: Win-x86@39, Branch:master, Time: 2014-05-25_07:43:52

Last working for me:
Version: 4.1.6.2 Build ID: 40ff705089295be5be0aae9b15123f687c05b0a
They are opened as matrix of one cell.

Set up status as new, keywords as regression, and the version to the first where I find the issue.
Comment 4 Kevin Suo 2014-11-06 05:04:27 UTC
 196cdde47dac6d5c1a8d2bd7056cae26ee505575 is the first bad commit
commit 196cdde47dac6d5c1a8d2bd7056cae26ee505575
Author: Bjoern Michaelsen <bjoern.michaelsen@canonical.com>
Date:   Wed Nov 27 11:30:42 2013 +0000

    source-hash-5b72931475de334acfe414eff8f3c9496a7477f6
    
    commit 5b72931475de334acfe414eff8f3c9496a7477f6
    Author:     yangzhang <yangzhang@multicorewareinc.com>
    AuthorDate: Tue Nov 5 16:39:27 2013 +0800
    Commit:     I-Jui (Ray) Sung <ray@multicorewareinc.com>
    CommitDate: Wed Nov 13 00:45:07 2013 -0600
    
        GPU Calc: unit test cases for ARCCOS
    
        Need open macro NO_FALLBACK_TO_SWINTERP in formulagroupcl.cxx for test
    
        AMLOEXT-113 BUG
    
        Change-Id: Ibccb2a40b7a9ffc49d30b694d2f0bab858db14c6
        Signed-off-by: haochen <haochen@multicorewareinc.com>
        Signed-off-by: I-Jui (Ray) Sung <ray@multicorewareinc.com>

suokunlong@KevinVirtualBox:~/lo/bisect/bibisect-42all$ git bisect log
# bad: [793dbf6f80f497dfe587d560d6257f42a24273f6] source-hash-1581b1fc3ac82a7bd62df968226e98604a4ca52d
# good: [65fd30f5cb4cdd37995a33420ed8273c0a29bf00] source-hash-d6cde02dbce8c28c6af836e2dc1120f8a6ef9932
git bisect start 'latest' 'oldest'
# good: [8092559c5013969ebda017d79200463b9b975038] source-hash-fd84daf696a368c2c7561b5253b32a63ecdeca4a
git bisect good 8092559c5013969ebda017d79200463b9b975038
# good: [0270ef1b76a6de423b30f7927362cc01c1a0fc38] source-hash-b1f7dd66b898b03cb4bd8d434b6370310ea95946
git bisect good 0270ef1b76a6de423b30f7927362cc01c1a0fc38
# skip: [ddb123cad22440994cd332d9985dd9558fd07e07] source-hash-647fb29f528b891a1c92846640f7865f5c1fbe7f
git bisect skip ddb123cad22440994cd332d9985dd9558fd07e07
# skip: [9d357dc6201f7cd91448595e0a3f89dfdae81946] source-hash-2304beaca33c63b94df99cb827716f00ce259f9a
git bisect skip 9d357dc6201f7cd91448595e0a3f89dfdae81946
# good: [ef72aa34cf4ee6399b192de28708d621c9680a50] source-hash-7e07a45500dcbb891a85f0bc9b7049cf4d50bba1
git bisect good ef72aa34cf4ee6399b192de28708d621c9680a50
# good: [2472598a0b04eef3038d56137f27dc6dc1edf9e5] source-hash-5050dfc73f194d1d59222cac72e69a917655d816
git bisect good 2472598a0b04eef3038d56137f27dc6dc1edf9e5
# skip: [b25c77bc097fa4cc84f59ebdd34011bafbb3a770] source-hash-660800d6f33a01ad53fc0f5717e1c33868440d2f
git bisect skip b25c77bc097fa4cc84f59ebdd34011bafbb3a770
# skip: [b25c77bc097fa4cc84f59ebdd34011bafbb3a770] source-hash-660800d6f33a01ad53fc0f5717e1c33868440d2f
git bisect skip b25c77bc097fa4cc84f59ebdd34011bafbb3a770
# skip: [b25c77bc097fa4cc84f59ebdd34011bafbb3a770] source-hash-660800d6f33a01ad53fc0f5717e1c33868440d2f
git bisect skip b25c77bc097fa4cc84f59ebdd34011bafbb3a770
# good: [89c7af972191d30321e9503b6454e8b1570ee3e8] source-hash-0aa9ced531b8d85ad067c1d156a9708eea628d78
git bisect good 89c7af972191d30321e9503b6454e8b1570ee3e8
# good: [89c7af972191d30321e9503b6454e8b1570ee3e8] source-hash-0aa9ced531b8d85ad067c1d156a9708eea628d78
git bisect good 89c7af972191d30321e9503b6454e8b1570ee3e8
# skip: [8bd734b6642f44d6876b227c0678f2be60425082] source-hash-c1f416feda0ef4ef215ca587b9bac03659b48c7a
git bisect skip 8bd734b6642f44d6876b227c0678f2be60425082
# bad: [9bce3024c675da4777cf206f8d913ebba8c7ed2b] source-hash-a1aea9418f860595641da8dcc971f656eaacf8a3
git bisect bad 9bce3024c675da4777cf206f8d913ebba8c7ed2b
# bad: [9bce3024c675da4777cf206f8d913ebba8c7ed2b] source-hash-a1aea9418f860595641da8dcc971f656eaacf8a3
git bisect bad 9bce3024c675da4777cf206f8d913ebba8c7ed2b
# good: [5ab5ade9fe4d1210ad3c08cadb699495c7f244e0] source-hash-a18a92b1b0c4ff591bd83f190f11c196ed676e86
git bisect good 5ab5ade9fe4d1210ad3c08cadb699495c7f244e0
# bad: [196cdde47dac6d5c1a8d2bd7056cae26ee505575] source-hash-5b72931475de334acfe414eff8f3c9496a7477f6
git bisect bad 196cdde47dac6d5c1a8d2bd7056cae26ee505575
# good: [4d0729fe3ad0839300ea972b482263f4a2a87c88] source-hash-4ac9fa7a887d09edf7f1fc38f155a93cff30ac97
git bisect good 4d0729fe3ad0839300ea972b482263f4a2a87c88
# first bad commit: [196cdde47dac6d5c1a8d2bd7056cae26ee505575] source-hash-5b72931475de334acfe414eff8f3c9496a7477f6
Comment 5 Markus Mohrhard 2014-12-14 17:55:30 UTC
(In reply to Kevin Suo from comment #0)
> Created attachment 99844 [details]
> test xlsx file with #N/A value
> 
> Problem description:
> 
> The attached xlsx file has "#N/A" value in cell A1:A4. The file was created
> by MSO 2010. The "N/A" value was generated by VLOOKUP function and pasted
> special only values (thus there should be no formular in any of the cells)
> 
> When open in Calc, it shows value "Err:512" rather than "N/A", while in the
> formular bar it shows "={nan}".
> 
> Steps:
> 1. Just open the attached xlsx file in LibreOffice.
> 
> Current Behaviour:
> It is showing "Err:512" in A1:A4, and "={nan}" in formular bar.
> 
> Expected:
> It should show "#N/A" in A1:A4; there is no formular in the cells, so it
> should show "#N/A" in the formular bar.
> 
> OS: reproducible in windows xp, ubuntu 13.04 lts, and Fedora 20. So: ALL.
> LibreOffice: 4.2.4.2.
> Still reproducible in 4.3 beta 1.


That file contains error cells which are not available in calc therefore we translate them to formulas. This is the closest that we can do.
Comment 6 Commit Notification 2014-12-16 01:02:55 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=994607b55104b9ae4554554c13b001b8d5d513b6

fdo#79249 call formula compiler with error string

It will be available in 4.5.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 7 Commit Notification 2014-12-16 01:02:59 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

construct grouped ScFormulaCell with bDirty=true, fdo#79249 related

It will be available in 4.5.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 8 Commit Notification 2014-12-16 01:31:37 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-4-4":

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

fdo#79249 call formula compiler with error string

It will be available in 4.4.0.0.beta3.

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 9 Commit Notification 2014-12-16 01:31:41 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-4-4":

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

construct grouped ScFormulaCell with bDirty=true, fdo#79249 related

It will be available in 4.4.0.0.beta3.

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 10 Eike Rathke 2014-12-16 01:33:22 UTC
Pending review https://gerrit.libreoffice.org/13490 for 4-3
Comment 11 Commit Notification 2015-01-07 11:43:17 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-4-3":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=11724301fb690cbd58581e468f3cd80e7becc07d&h=libreoffice-4-3

fdo#79249 call formula compiler with error string

It will be available in 4.3.6.

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 12 Commit Notification 2015-01-07 13:22:35 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-4-2":

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

fdo#79249 call formula compiler with error string

It will be available in 4.2.9.

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 13 Robinson Tryon (qubit) 2015-12-17 08:17:04 UTC
Migrating Whiteboard tags to Keywords: (bibisected)
[NinjaEdit]