Download it now!
Bug 130583 - switch() gives "wrong data type" error on reordering value-result pairs
Summary: switch() gives "wrong data type" error on reordering value-result pairs
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.4.7.2 release
Hardware: All All
: medium normal
Assignee: Julien Nabet
URL:
Whiteboard: target:7.0.0 target:6.4.4 target:6.3....
Keywords:
Depends on:
Blocks:
 
Reported: 2020-02-11 09:50 UTC by thatho
Modified: 2020-04-21 10:25 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:


Attachments
a minimal example: value-result pairs ordered and disordered (12.41 KB, application/vnd.oasis.opendocument.spreadsheet)
2020-02-11 09:50 UTC, thatho
Details
WIS (4.55 KB, image/png)
2020-02-11 09:51 UTC, thatho
Details
Example document to reproduce the bug (16.24 KB, application/vnd.oasis.opendocument.spreadsheet)
2020-04-10 18:30 UTC, Jürgen Kirsten
Details
Modified sample file. (13.32 KB, application/vnd.oasis.opendocument.spreadsheet)
2020-04-10 20:14 UTC, m.a.riosv
Details
My example in xlsx from Excel Online (11.72 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2020-04-14 15:16 UTC, Jürgen Kirsten
Details
Sceenshot from Excel Online (69.20 KB, image/png)
2020-04-14 15:18 UTC, Jürgen Kirsten
Details

Note You need to log in before you can comment on or make changes to this bug.
Description thatho 2020-02-11 09:50:26 UTC
Created attachment 157787 [details]
a minimal example: value-result pairs ordered and disordered

I ran into a strange behaviour, when switch() gives "wrong data type" error on reordering value-result pairs. I've found out that the value-result pairs shall be ordered ascending in the switch function not to receive an error. Otherwise, if the value-results pairs are disordered, some values are not recognised and pairs with their result.

It gives hard time to order the values and not to receive a #value error, but if it would be required, it shall be documented, too:
file:///C:/Program%20Files/LibreOffice/help/en-US/text/scalc/01/func_switch.html?DbPAR=CALC#bm_id571556244875552

Personally, I think the behaviour is a bug.

Version: 6.4.0.3 (x64)
Build ID: b0a288ab3d2d4774cb44b62f04d5d28733ac6df8
CPU threads: 8; OS: Windows 10.0 Build 18362; UI render: default; VCL: win; 
Locale: cs-CZ (cs_CZ); UI-Language: en-US
Calc: threaded
Comment 1 thatho 2020-02-11 09:51:16 UTC
Created attachment 157788 [details]
WIS
Comment 2 Oliver Brinzing 2020-02-11 18:53:38 UTC
reproducible with:

Version: 5.4.7.2 (x64)
Build-ID: c838ef25c16710f8838b1faec480ebba495259d0
CPU-Threads: 4; BS: Windows 6.19; UI-Render: Standard; 
Gebietsschema: de-DE (de_DE); Calc: single

https://help.libreoffice.org/7.0/en-US/text/scalc/01/func_switch.html?DbPAR=CALC#bm_id571556244875552
Comment 3 m.a.riosv 2020-02-12 08:25:56 UTC
It is not a matter of sort but data type.
In E:
=SWITCH(D3;"5";"a";1;"b";"2";"c";"3+4";"d") = b
"5" it's a number like D3

=SWITCH(D3&"";"1*";"a";1;"b";"2";"c";"3+4";"d") = b
D3&"" converts D3 in a text number.

looks the issue is searching for numbers (expression) on text (value1)
Comment 4 thatho 2020-02-12 12:13:29 UTC
OK, correct. Putting D3&"" for the expression works, independently on the format of the valueN "1" or 1, "2" or 2.

Strikes me that 
=SWITCH(D3;"1*";"a";1;"b";2;"c";"3+4";"d")
won't work then. That is comparing number with number does not work. But converting D to text numbers is OK.

Not that in the original example, the value-result pairs are only reordered:
SWITCH(A3;"1";"b";"2";"c";"1*";"a";"3+4";"d")			SWITCH(D3;"1*";"a";"1";"b";"2";"c";"3+4";"d")

But

SWITCH(G3;1;"a";2;"b";"3";"c";4;"d")	
code	decode
1	a
2	b
3	c
4	d
blah	#N/A

works as a charm. That is the other way around: comparing text number "3" with number 3.

So, how should one know, in which cases valueN is going to be interpreted as a text and in which cases as a number?
Comment 5 Jürgen Kirsten 2020-04-10 18:29:44 UTC
Hello community,

I had found the same error and also created a sample document.
In my example I always search for the number 1 in 3 value pairs.
No problem in the first example.  The three numbers are found. No matter what else I enter, the alternative value is displayed.
In the second example I have no number in Value. If I now search for a number, an error is displayed instead of the alternative value.
In the third and fourth example I mixed numbers and texts.  If my value pairs start with a number, it will still be found. But once the pairs are interrupted by a text, the number is no longer found.
All in all I suspect an error when searching for texts or numbers. If I search for numbers, I should also only get numbers as hits. You can adjust to this.
Comment 6 Jürgen Kirsten 2020-04-10 18:30:30 UTC
Created attachment 159477 [details]
Example document to reproduce the bug
Comment 7 m.a.riosv 2020-04-10 20:14:50 UTC
Created attachment 159478 [details]
Modified sample file.

Please rethink about what you are doing,
F8: =SWITCH(D8;B8;C8;B9;C9;B10;C10;C11)
searching for D8 in B column
F8: =SWITCH(D8;C8;B8;C9;B9;C10;B10;C11)
works fine for me.
Comment 8 Jürgen Kirsten 2020-04-11 08:53:08 UTC
Hello, everyone,
I've been thinking very carefully.
I have searched for the digit one in the column B (Value) in F8. Since there is no match here, the alternative value from cell C11 should actually be displayed. But it does not. We get a #VALUE error.

The way Miguel changed the formulas, this is exactly the same as example 1, where I already mentioned that there are no problems with the first example.

I don't understand the motivation why you changed my example. 
The function definitely does not work as expected.

Best wishes and a happy Easter.
Comment 9 Julien Nabet 2020-04-14 07:55:53 UTC
It seems type value is taken into account but it's not indicated in help:
see https://help.libreoffice.org/7.0/en-US/text/scalc/01/func_switch.html?DbPAR=CALC#bm_id571556244875552

It could be interesting someone tests this on Excel.
Indeed, https://opengrok.libreoffice.org/xref/core/sc/source/core/tool/interpr8.cxx?r=811b36e4#1919 ScInterpreter::ScSwitch_MS so I suppose it mimicks Excel.
https://help.libreoffice.org/7.0/en-US/text/scalc/01/func_switch.html?DbPAR=CALC#bm_id571556244875552

IMHO, either it doesn't mimick Excel enough and code of "switch" should be changed, or help should be amended.
Comment 10 Jürgen Kirsten 2020-04-14 15:16:46 UTC
Created attachment 159558 [details]
My example in xlsx from Excel Online

This works as I am expected. Checked in Excel (free) Online.
Comment 11 Jürgen Kirsten 2020-04-14 15:18:29 UTC
Created attachment 159559 [details]
Sceenshot from Excel Online

For Information: The Switch function called in German in Excel : ERSTERWERT()
Comment 12 Julien Nabet 2020-04-16 11:07:55 UTC
I gave a try with:
https://gerrit.libreoffice.org/c/core/+/92345
Comment 13 Commit Notification 2020-04-17 13:46:16 UTC
Julien Nabet committed a patch related to this issue.
It has been pushed to "master":

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

tdf#130583: fix Switch in Calc

It will be available in 7.0.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 14 Julien Nabet 2020-04-17 14:24:23 UTC
Backports waiting for review here:
- for 6.4 https://gerrit.libreoffice.org/c/core/+/92432
- for 6.3 https://gerrit.libreoffice.org/c/core/+/92433
Comment 15 Commit Notification 2020-04-17 19:24:44 UTC
Julien Nabet committed a patch related to this issue.
It has been pushed to "libreoffice-6-4":

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

tdf#130583: fix Switch in Calc

It will be available in 6.4.4.

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-04-17 19:24:54 UTC
Julien Nabet committed a patch related to this issue.
It has been pushed to "libreoffice-6-3":

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

tdf#130583: fix Switch in Calc

It will be available in 6.3.7.

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 Jürgen Kirsten 2020-04-18 15:05:16 UTC
I tested in daily Build 7.0.0.0

Perfect!

Version: 7.0.0.0.alpha0+ (x64)Build-ID: 5063669a17538ea353e34ab0990363399d34f4f4CPU-Threads: 8; BS: Windows 10.0 Build 18363; UI-Render: Skia/Vulkan; VCL: win; Gebietsschema: de-DE (de_DE); UI-Sprache: de-DECalc: threaded
Comment 18 m.a.riosv 2020-04-18 20:57:01 UTC
Then please Jürgen change the status from FIXED to VERIFIED.
Comment 19 thatho 2020-04-18 23:04:10 UTC
Tested successfully with Build 6.4.4.0.0
Comment 20 Commit Notification 2020-04-21 10:25:24 UTC
Julien Nabet committed a patch related to this issue.
It has been pushed to "libreoffice-6-3-6":

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

tdf#130583: fix Switch in Calc

It will be available in 6.3.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.