Bug 115351 - ISLOGICAL function depends on column formatting EDITING
Summary: ISLOGICAL function depends on column formatting EDITING
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium normal
Assignee: Eike Rathke
URL:
Whiteboard: target:6.1.0 target:6.0.3
Keywords:
Depends on:
Blocks:
 
Reported: 2018-01-31 21:05 UTC by C Hemingway
Modified: 2018-03-15 16:04 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
ISLOGICAL before XLS (with Boolean formatting) (10.29 KB, application/vnd.oasis.opendocument.spreadsheet)
2018-02-01 16:44 UTC, C Hemingway
Details
ISLOGICAL after XLS (boolean formatting lost completely) (8.00 KB, application/vnd.ms-excel)
2018-02-01 16:47 UTC, C Hemingway
Details

Note You need to log in before you can comment on or make changes to this bug.
Description C Hemingway 2018-01-31 21:05:25 UTC
Description:
ISLOGICAL function does not always return TRUE when the column is not set to precisely BOOLEAN
For instance, Setting a Custom format of T;;F will always return false.  Also, ISLOGICAL does not change after the column format changes until the document is reloaded or the formula in the target cell is modified.

Steps to Reproduce:
1. Create spreadsheet in LO 6
2. Set column A to custom format T;;F
3. Set Column B to Logical
3a. Set Column C to Number/Generic
4. Set the value =TRUE() in A1, B1 and C1
5. Set the value =FALSE() in A2, B2, and C2
6. Reference 6 cells with ISLOGICAL to the cells A1, B1, C1, A2, B2, C2.
7. Note items in column A are considered "False"
8. Note items in column B are considered "True" (Truthy?)
9. Note items in column C are considered "False"

Actual Results:  
Functions with custom T/F header are always considered FALSE() by ISLOGICAL().  ISLOGICAL should evaluate the function and not the header.

Expected Results:
ISLOGICAL should evaluate the function in the cell, not the cell format (okay, a TEXT cell I *might* understand)


Reproducible: Always


User Profile Reset: Yes



Additional Info:
XLS/XLSX bug! When saving a spreadsheet to XLS/XLSX format BOOLEAN is converted to TRUE;TRUE;FALSE, and all conditional formatting the references that column & ISLOGICAL are broken. (Assuming that the CF is not stripped away altogether).

Oldest version that this still applies to v4.4.7.2


User-Agent: Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:58.0) Gecko/20100101 Firefox/58.0
Comment 1 C Hemingway 2018-02-01 16:44:21 UTC
Created attachment 139507 [details]
ISLOGICAL before XLS (with Boolean formatting)
Comment 2 C Hemingway 2018-02-01 16:47:41 UTC
Created attachment 139508 [details]
ISLOGICAL after XLS (boolean formatting lost completely)
Comment 3 Buovjaga 2018-02-24 15:25:57 UTC
Ok, the example file does not match the description, but anyway: column D has this formatting code:
[>0]"T";[<0]"T";"F"

ISLOGICAL seems to think it is broken and thus says FALSE for all the column D values.

I want to check with Eike before doing anything.
Comment 4 Eike Rathke 2018-02-27 16:50:25 UTC
It is like it is. Calc does not have a distinct logical data type, but formatting as BOOLEAN displays 0 as FALSE and any other value as TRUE. Asking a cell for ISLOGICAL() therefore has to query the cell format whether it is bool.

Excel has a distinct logical type, but does not (or does it meanwhile?) handle the ==0=>FALSE, <>0=>TRUE behaviour with a boolean format, so we have to export a "TRUE";"TRUE";"FALSE" number format for that. That again is not a boolean format but just a specific format with implicit >0;<0;=0 conditions. We maybe could recognize that very format during import and convert to boolean for ISLOGICAL() to work as expected, but note that ISLOGICAL() in Excel with an exported numeric value doesn't work either. However, converting this specific number format to a true BOOLEAN format during import might be expected.

[>0]"T";[<0]"T";"F" is definitely a numeric user defined format and will never work in the way that ISLOGICAL() would recognize it.
Comment 5 C Hemingway 2018-02-27 17:51:07 UTC
Bug Clarification:

When I save an XLS or XLSX spreadsheet with a Logical field, when I later reload it, it loses the logical designation and becomes that [>0]"T";[<0]"T";"F" format.

Clarification - on that 4th column, I changed the display format of the Boolean field to T;;F.  When LibreOffice opens the file later, the boolean field has become numeric, and the column becomes [>0]"T";[<0]"T";"F".  Should assigning a custom display change the column type on reload?  For the ISLOGICAL test, is LibreOffice remembering (caching?) that the column is Boolean until the spreadsheet is reloaded?

My apologies if I am explaining this incorrectly.
Comment 6 Eike Rathke 2018-02-27 19:08:46 UTC
(In reply to C Hemingway from comment #5)
> When I save an XLS or XLSX spreadsheet with a Logical field, when I later
> reload it, it loses the logical designation and becomes that
> [>0]"T";[<0]"T";"F" format.
There is no boolean logical type for D2:D3, even in the file format the cell type is office:value-type="float" calcext:value-type="float". That is because the [>0]"T";[<0]"T";"F" format is of type number and not boolean.

> Clarification - on that 4th column, I changed the display format of the
> Boolean field to T;;F.
What do you expect? T;;F works only by accident (if T and F don't have some other meaning as format keywords) and then is the same as "T";;"F"
Which when re-loaded becomes [>0]"T";[<0]"";"F" that is identical in meaning. Note the second subformat has an empty string, not "T" as your other format. I don't know how you got to the [>0]"T";[<0]"T";"F"

> When LibreOffice opens the file later, the boolean
> field has become numeric
There never was a boolean "field" if you applied the T;;F format.

> and the column becomes [>0]"T";[<0]"T";"F". 
> Should assigning a custom display change the column type on reload?
There is no column type, there are cell formats which lead to numeric/date/boolean display cell types.

> For the
> ISLOGICAL test, is LibreOffice remembering (caching?) that the column is
> Boolean until the spreadsheet is reloaded?
No, as explained previously already, there is no distinct boolean type, it is all about number display formats.

Btw, there's an odd user-defined number format in that document which loads as

[>0]"T"[~jewish]YYYY"U"YY;[<0]"T"[~jewish]YYYY"U"YY;"FAL"S[~jewish]YY

listed as T5663U663 and currently displays a FAL0660 preview on an empty cell or 0. Seems to be some result of your previous experiments, but it illustrates how almost any combination of valid keywords yields some result ;-)
Comment 7 Commit Notification 2018-03-02 19:28:20 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

Resolves: tdf#115351 convert boolean equivalent format codes to proper Boolean

It will be available in 6.1.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 Eike Rathke 2018-03-02 19:44:26 UTC
Pending review https://gerrit.libreoffice.org/50645 for 6-0
Comment 9 Commit Notification 2018-03-15 16:04:44 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-6-0":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=feb3da3dc53c9b702e39ec12b037b07a67bedcbd&h=libreoffice-6-0

Resolves: tdf#115351 convert boolean equivalent format codes to proper Boolean

It will be available in 6.0.3.

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.