Bug 92474 - Abnormal behavior of EXACT() function when cell is copied, False argument always called
Summary: Abnormal behavior of EXACT() function when cell is copied, False argument alw...
Status: RESOLVED WORKSFORME
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.4.4.2 rc
Hardware: x86-64 (AMD64) Linux (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2015-07-01 09:34 UTC by Yann Dumont
Modified: 2016-10-11 14:27 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
Test file 1 (ODS) (9.53 KB, application/vnd.oasis.opendocument.spreadsheet)
2015-07-02 07:10 UTC, Yann Dumont
Details
Test file 2 (CSV) (70 bytes, text/csv)
2015-07-02 07:11 UTC, Yann Dumont
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Yann Dumont 2015-07-01 09:34:13 UTC
While formatting data on different files, I copy-paste this formula from on file (ODS) to another (CSV I will save in ODS later) :

=SI(EXACT(GAUCHE(B3;10);"complement");CNUM(GAUCHE(DROITE(B3;18);7));CNUM(GAUCHE(B3;7)))

With English formula

=IF(EXACT(LEFT(B3;10);"complement");VALUE(LEFT(RIGHT(B3;18);7));VALUE(LEFT(B3;7)))

Then the results obtained when the SI/IF first argument is TRUE (B3 cell or other cells under contain "complement"), the results obtained are "Err:502".

Everything works as if the first argument of SI/IF returns FALSE, even if it may be TRUE. Since the string still contains text after processing by the third argument of SI/IF, "Err:502" is quite normal there.

I find a... by-pass ? (I can't remember the correct word in English) : adding spaces on each sides of the ";" of the SI/IF function reestablished the correct behaviour of SI/IF.

Version: 4.4.4.2
Build ID: 40m0(Build:2)
Locale : fr_FR.UTF-8

Debian unstable distribution
Not tested on other environment
Comment 1 Yann Dumont 2015-07-01 09:55:10 UTC
The bug happens only when I copy-paste
Comment 2 Yann Dumont 2015-07-01 09:57:34 UTC
The bug happens only when I copy-paste the cell containing the formula from one file to another, not when copy-pasting the formula itself. Copiing the formula in the first file and pasting it on the cell in the second works fine too.
Comment 3 Cor Nouws 2015-07-01 12:27:50 UTC
Hi Yann,

Thanks for describing the issue.
Can you please test what happens if you save the csv file as .ods first?
I think see some pattern in your comment 2..
Comment 4 Yann Dumont 2015-07-01 13:05:11 UTC
(In reply to Cor Nouws from comment #3)
> Hi Yann,
> 
> Thanks for describing the issue.
> Can you please test what happens if you save the csv file as .ods first?
> I think see some pattern in your comment 2..

Hi,

Thanks for asking.

First, I am not sure I will use the correct translation for the function I will talk about in this comment (everything between double quotes), sorry for the inconvenience if I don't.

Behaviour isn't changed at all. If I load the csv then save it in ods format, then copy paste the formula's cell, the bug happens. And if I close the now-ods file and re-opened it, and then copy the formula's cell, it happened too.


Moreover,
I do some further testing.
- The by-pass (I still don't remember the correct word) consist simply in modifying the formula. Simply adding/removing/changing one character anywhere in the string and then correcting the formula WITHOUT using "cancel last action" works too. But the "cancel last action" function restores the error.
- The same bug happened when I do a "copy" of the whole sheet on an other document (ods) : all the IF functions are messed up.
- if I save, close and then reload the ods, everything works fine !
- using "recalculate" doesn't work
- disabling "automatic recalculate", and then copy-pasting the formula's cell works fine !

Seems like the bug comes from loaded information in LibreOffice, not from incorrect formula. "Automatic recalculate" function may be faulty here.
Comment 5 m_a_riosv 2015-07-01 21:43:40 UTC
Please attach a minimal sample file, to test, function name is automatically translated to the language used.
Comment 6 Yann Dumont 2015-07-02 07:10:18 UTC
Created attachment 116982 [details]
Test file 1 (ODS)
Comment 7 Yann Dumont 2015-07-02 07:11:12 UTC
Created attachment 116984 [details]
Test file 2 (CSV)
Comment 8 Yann Dumont 2015-07-02 07:27:27 UTC
I add test files, but sorry, I did not notice that LO has gently put a capital letter to the "C" of "complement" in the test file 2 (csv)... if needed I'll re-upload one

To (try to) reproduce the bug:
-open test file 1
-open test file 2 (tabulation separated csv), and correct the B2 cell, change the upper case C of complement to a lower case C
-copy cell C from test file 1 (shown result of the formula may be 1234567)...
-... to cell C2 of test file 2

Result I obtain : "Err :502"
Expected results : "1212121"
Comment 9 m_a_riosv 2015-07-02 08:57:14 UTC
Something strange is happening here,

Copying the cell produce the commented result "Err :502".
After copy edit the cell, e.g. add a space to the end of cell, or editing with the wizard, shows the correct result.
Copying the content of the cell (not the cell) and paste, shows the correct result.

Seems the issue is when the cell is copied, the condition in the if is false and takes the second value not the first.

Has nothing to do with the Err value, changing the second value in the if prior to copy, this is showed after copy.
Comment 10 Yann Dumont 2015-07-02 09:57:29 UTC
I do more testing, again :
- I test if the bug is reproducible with other logic function. OR, AND, XOR, IFERROR and IFNA are not affected.
- behavior of formula =IF(TRUE();"true";"false") when copy-pasted is CORRECT
- behavior of formula =IF(EXACT(LEFT(B2;10);"complement");"true";"false") when copy-pasted is INCORRECT
- behavior of formula =IF(EXACT("same";"same");"true";"false") when copy-pasted is CORRECT
- behavior of formula =EXACT(LEFT(B2;10);"complement") when copy-pasted is INCORRECT
- behevior of formula =EXACT(LEFT(B2;10);"complement") when copy-pasted from one sheet to another from the same file, or simply copied in the same file, is CORRECT

So, bug come from EXACT(;) function, not from IF(;;) !

EXACT() returns systematically FALSE() when cell is copied or cell's sheet is copied from one file to another, but not in the same document.

From there, I think it is out of my competences.

I consider changing the title of the bug, but I am not really familiar with conditions needed to do so. Is it okay ? Does this need a higher importance, too ?

And is it confirmed on other versions of LO ? (simple test is using =IF(EXACT("same";"same");"true";"false") formula)
Comment 11 QA Administrators 2016-09-20 10:14:41 UTC Comment hidden (obsolete)
Comment 12 Yann Dumont 2016-10-11 13:46:23 UTC
I have now upgraded my LibreOffice installation to 4.4.4.3, the EXACT function works correctly when tis cell is copied.

I modified the title to match my problem.

May I now change the bug's status to resolved ?
Comment 13 m_a_riosv 2016-10-11 14:17:47 UTC
Please better as RESOLVED WORKSFORME.
Comment 14 Yann Dumont 2016-10-11 14:27:32 UTC
So be it.