Download it now!
Bug 124454 - Double negation with text in array context behaves like single -
Summary: Double negation with text in array context behaves like single -
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.0 all versions
Hardware: All All
: medium normal
Assignee: Eike Rathke
URL:
Whiteboard: target:7.1.0 target:7.0.0.2 target:6.4.6
Keywords:
Depends on:
Blocks:
 
Reported: 2019-03-31 15:08 UTC by dursojohn
Modified: 2020-07-20 19:51 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments
Spreadsheet with formula (11.44 KB, application/vnd.oasis.opendocument.spreadsheet)
2019-03-31 15:11 UTC, dursojohn
Details
The example file with the formula cut into pieces. (15.46 KB, application/vnd.oasis.opendocument.spreadsheet)
2019-03-31 21:45 UTC, m.a.riosv
Details
simple test (8.65 KB, application/vnd.oasis.opendocument.spreadsheet)
2019-04-01 08:29 UTC, GerardF
Details
file_with_some_more_conversion_curiosities (9.32 KB, application/vnd.oasis.opendocument.spreadsheet)
2020-01-24 08:01 UTC, b.
Details

Note You need to log in before you can comment on or make changes to this bug.
Description dursojohn 2019-03-31 15:08:18 UTC
Description:
Calc produces a different (Incorrect) result than Excel or Google Sheets in a formula that contains SUMPRODUCT and MOD. 

Steps to Reproduce:
1.Enter a 14 length text field of numbers in cell E5 (69765050007777)
2.Enter Formula "=MOD(10-MOD(SUMPRODUCT(--(MID(E5,{2,4,6,8,10,12,14},1)))*3+SUMPRODUCT(--(MID(E5,{1,3,5,7,9,11,13},1))),10),10)" in cell F5
3.

Actual Results:
Result 2

Expected Results:
Result in Excel & G Sheets 6


Reproducible: Always


User Profile Reset: No



Additional Info:
Calculate correctly.  This is my first time loading a bug, I do not see a method of attaching a spreadsheet with formulas in it.
Comment 1 dursojohn 2019-03-31 15:11:26 UTC
Created attachment 150427 [details]
Spreadsheet with formula
Comment 2 dursojohn 2019-03-31 15:16:56 UTC
Sorry In original example, actual result is 4 and expected is 6.  

I attached a spreadsheet and can open up in excel and calc and enter values and see differences in results of two (Excel is calculating correctly)
Comment 3 m.a.riosv 2019-03-31 21:45:41 UTC
Created attachment 150435 [details]
The example file with the formula cut into pieces.

With the formula cut into pieces I can't see the error.
I have not excel to test.
Maybe excel gives negative values for MOD() function over negatives input.
Comment 4 GerardF 2019-04-01 08:15:30 UTC
Error is caused by double negative sign which returns négative number.

Replacing "--" by "1*" gives expected results.
Comment 5 GerardF 2019-04-01 08:29:13 UTC
Created attachment 150442 [details]
simple test

simple test file to demonstrate the bug
Comment 6 m.a.riosv 2019-04-01 13:54:38 UTC
It is in relation about when LibreOffice does the conversion from text to numbers.

Automatic conversion doesn't work with arrays because only direct references to a cell can get the automatic conversion (with GeradF sample) SUM(B2;B3;B4;B5;B6) works but not SUM(B2:B6), except forcing the conversion with an expression like @GerardF comments.
Or replacing "--" by "--1*" gets the results.

Please take a look to Eike comment https://bugs.documentfoundation.org/show_bug.cgi?id=120577#c12

IMO this is not a bug.
Comment 7 Ming Hua 2019-04-02 10:07:48 UTC
(In reply to m.a.riosv from comment #6)
> It is in relation about when LibreOffice does the conversion from text to
> numbers.
> 
> Automatic conversion doesn't work with arrays because only direct references
> to a cell can get the automatic conversion (with GeradF sample)
> SUM(B2;B3;B4;B5;B6) works but not SUM(B2:B6), except forcing the conversion
> with an expression like @GerardF comments.
> Or replacing "--" by "--1*" gets the results.

The "--" thing is an explicit conversion, though, just like the "1*" is, unless text variables have a uni-operator "-" that I'm not aware of.

"--" is actually a common way to convert texts to numbers, and is taught in a lot of "Excel tips and tricks" kind of books.
Comment 8 b. 2019-04-05 09:21:05 UTC
may be off topic, but astonishing for me ... 

1 -> 1
+1 -> 1
=1 -> 1
=+1 -> 1
=++1 -> 1
-1 -> -1
=-1 -> -1
=--1 -> 1
-(-1) -> 1
--1 -> -1

last line conflicts with my 'simple minded user expectations' and with what i was taught in school. different evaluation of an expression if 'calculated' or or 'explicitly advised to be calculated', 

who the fu... invented things like this, did he ... ?

which user should spend how much time rtfm before using calc till getting this special knowledge and remembering it in cases where he would create faults with SIU 'simple intuitive use'? 

tested with ver. 
Version: 6.3.0.0.alpha0+ (x64)
Build ID: 9956cf0692058414ef3efdb0e8058fbb0b39f6bc
CPU threads: 8; OS: Windows 6.1; UI render: default; VCL: win; 
TinderBox: Win-x86_64@42, Branch:master, Time: 2019-04-04_02:20:31
Locale: de-DE (de_DE); UI-Language: en-US
Calc: threaded

reg. 

b.
Comment 9 dursojohn 2019-04-05 13:12:26 UTC
It appears Excel & Google Sheets treat "--" differently than Calc. 

As others have stated, Since "--" is actually a common way to convert texts to numbers, and is taught in a lot of "Excel tips and tricks" kind of books, this makes using Calc looking like it is calculating (to the norms) but not calculating to what is expected.

Therefore, either 1) there should be a warning when "--" is used that this calculates differently than the expected (at least on the first calculation of the spreadsheet) or 2) Calc should calculate the same as Excel and Google Sheets.
Comment 10 b. 2020-01-24 08:01:47 UTC
Created attachment 157384 [details]
file_with_some_more_conversion_curiosities


c#9: 

> It appears Excel & Google Sheets treat "--" differently than Calc. 

and compatibility is considered a strong argument in other decisions designing calc, sometimes even to keep bugs for compatibility, why isn't it respected here? 

> As others have stated, Since "--" is actually a common way to convert texts to numbers, and is taught in a lot of "Excel tips and tricks" kind of books, 

it's just a statement, i'm missing citations / references, on a quick search i found only one site (https://exceljet.net/the-double-negative-in-excel-formulas) about handling of "--" for logical values: ‚The first negative will convert TRUE to -1, and the second negative will convert 1 to 1‘ that is someway plausible, but what does calc do instead: the double negation converts a number – or number representating text – to its negative … 

> this makes using Calc looking like it is calculating (to the norms) but not calculating to what is expected.

‚calculating to the norm‘ … which norm? did i miss something? 

> Therefore, either 1) there should be a warning when "--" is used that this calculates differently than the expected (at least on the first calculation of the spreadsheet) or 2) Calc should calculate the same as Excel and Google Sheets.

yes! 

if double negation sign is not interpretet as two calculation operators but as one conversion sign both ‚-‘ signs should be away after conversion … handling of '--' by calc is using one of the '-' for conversion and leaving one for a - wrong - result. 

c#7: 

> the „minus minus“ thing is an explicit way to convert text to numbers, 

ok, but why should it convert 1 to -1? regardless of text or numbers. 

simple user expectations say the highlighted cells in the attachement provided are questionabe, thus i suggest to change that behaviour, 

especially the change from col. E to F, and col. G to H are 'not intuitively understandable'

i'm aware that a change would break compatibility into calc itself :-( thus would need a warning to users or one more options switch? :-( but it should be possible to find ways to do things like that otherwise most progress is blocked. 

reg. 

b.
Comment 11 Eike Rathke 2020-07-03 22:51:47 UTC
(In reply to b. from comment #8)
> may be off topic, but astonishing for me ... 
> --1 -> -1
That is clearly a bug, though not related to the formula expression behaviour discussed here otherwise, but of the input number scanner instead.

Anyway, I stumbled over the causing code and in search found this bug here and now created bug 134490 for that.
Comment 12 Eike Rathke 2020-07-08 16:29:46 UTC
The problem here is not applying the (negation) matrix operator on values that are converted from strings.
Comment 13 Commit Notification 2020-07-08 21:13:23 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/7ead920f7015bb8e7a4343b863333bb26187d9a3

Resolves: tdf#124454 exec the matrix operator on values converted from strings

It will be available in 7.1.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 15 Commit Notification 2020-07-08 23:05:27 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-7-0":

https://git.libreoffice.org/core/commit/ef0f60f28ac83c55f260f61346f03b5f928764d8

Resolves: tdf#124454 exec the matrix operator on values converted from strings

It will be available in 7.0.0.2.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 16 Commit Notification 2020-07-09 14:52:53 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-6-4":

https://git.libreoffice.org/core/commit/e2713a0a5677dd92e3a88edc534a2e35ce91a0fb

Resolves: tdf#124454 exec the matrix operator on values converted from strings

It will be available in 6.4.6.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 17 Commit Notification 2020-07-09 18:34:23 UTC
Xisco Fauli committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/96547693533aa0261ed46c19f922b3b8172591e3

tdf#124454: sc: Add unittest

It will be available in 7.1.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 18 b. 2020-07-10 19:38:47 UTC
hello @Eike, 

looks better now, 

thanks for fixing this issue :-)
Comment 19 BogdanB 2020-07-20 19:51:20 UTC
Solved:
now the value is 6

Version: 7.1.0.0.alpha0+
Build ID: abea0d6647c7f1f7e76c73c26cb80e6a67dc5111
CPU threads: 4; OS: Linux 5.4; UI render: default; VCL: gtk3
Locale: ro-RO (ro_RO.UTF-8); UI: en-US
Calc: threaded