Bug Hunting Session
Bug 58781 - FORMATTING: conditional formatting have weird result
Summary: FORMATTING: conditional formatting have weird result
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.6.4.3 release
Hardware: Other Windows (All)
: medium normal
Assignee: Markus Mohrhard
URL:
Whiteboard: BSA target:4.0.0.1 target:4.1.0
Keywords:
Depends on:
Blocks:
 
Reported: 2012-12-26 14:53 UTC by sugeng
Modified: 2013-01-10 22:09 UTC (History)
1 user (show)

See Also:
Crash report or crash signature:


Attachments
my document which has this problem (11.61 KB, application/x-vnd.oasis.opendocument.spreadsheet)
2012-12-26 14:53 UTC, sugeng
Details

Note You need to log in before you can comment on or make changes to this bug.
Description sugeng 2012-12-26 14:53:09 UTC
Created attachment 72140 [details]
my document which has this problem

Problem description: 
--------------------

I have 2 style for conditional formatting, one have green background, other have red background. When cell content is 'y', then the color is green, otherwise the color is red

Steps to reproduce:
-------------------
1. block the cells
2. use format - conditional formatting
3a. condition1 : cell value is same with 'y', apply style -> create new style with green background.
3b. condition2 : cell value is not same with 'y', apply style -> create new style with red background.

Current behavior:
-----------------
some cell has green background, and other is red. whether the value is y or not (it doesn't care).

When the cell contains y is clicked, the style with green background is highlighted (format and style window).
When empty or other than y is clicked, the style with red background is highlighted. 

Expected behavior:
------------------
After executing command, all cells contains y will have green background. All cells that does not contains y will have red background.

              
Operating System: Windows 7
Version: 3.6.4.3 release
Comment 1 Sören 2012-12-30 13:28:48 UTC
I can confirm that conditional formatting behaves incorrectly in the attached file in
Version 4.0.0.0.beta2 (Build ID: 4104d660979c57e1160b5135634f732918460a0)
on Ubuntu 12.04 (x86)
Recalculating doesn't change the behavior in my test
Comment 2 sugeng 2012-12-30 21:43:34 UTC
Now I know the behavior of conditional formatting. It only works on single cell. Also the character is enclosed with double quote.

I update the steps :
1. You must select 1 cell only.
2. Apply conditional formatting.
2a. condition1 : cell value is same with "y", apply style -> create new style with green background.
2b. condition2 : cell value is not same with "y", apply style -> create new style with red background.
3. Then copy it using format-brush to the other cells you want. 

The result is just as expected, all cells is green if contains "y". And red if not.
Comment 3 Not Assigned 2012-12-31 03:36:34 UTC
Markus Mohrhard committed a patch related to this issue.
It has been pushed to "libreoffice-4-0":

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

don't add a new cond format if we just want to edit one, related fdo#58781


It will be available in LibreOffice 4.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 4 Not Assigned 2012-12-31 03:36:51 UTC
Markus Mohrhard committed a patch related to this issue.
It has been pushed to "libreoffice-4-0":

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

handle strings correctly in ScCondFormatEntry::GetExpression, fdo#58781


It will be available in LibreOffice 4.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 5 Not Assigned 2012-12-31 03:43:01 UTC
Markus Mohrhard committed a patch related to this issue.
It has been pushed to "master":

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

don't add a new cond format if we just want to edit one, related fdo#58781



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 6 Not Assigned 2012-12-31 03:43:18 UTC
Markus Mohrhard committed a patch related to this issue.
It has been pushed to "master":

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

handle strings correctly in ScCondFormatEntry::GetExpression, fdo#58781



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 Cor Nouws 2013-01-02 19:16:57 UTC
@markus: do you think this can be added to 3.6 code too? Thanks,
Comment 8 Markus Mohrhard 2013-01-02 21:29:14 UTC
(In reply to comment #7)
> @markus: do you think this can be added to 3.6 code too? Thanks,

I have to check the 3.6 code before that.

However the document contains some errors in itself like the use of ='Y' instead of ="Y" which is the correct way to use strings in formulas.
Comment 9 sugeng 2013-01-03 13:00:09 UTC
(In reply to comment #8)

> However the document contains some errors in itself like the use of ='Y'
> instead of ="Y" which is the correct way to use strings in formulas.

the 'Y' I got from automatically from conditional formatting dialog. While "Y" I got from reading the manual. I think the dialog itself must give double quote if user give string (or character) as argument.
Comment 10 Markus Mohrhard 2013-01-04 03:14:17 UTC
(In reply to comment #9)
> (In reply to comment #8)
> 
> > However the document contains some errors in itself like the use of ='Y'
> > instead of ="Y" which is the correct way to use strings in formulas.
> 
> the 'Y' I got from automatically from conditional formatting dialog. While
> "Y" I got from reading the manual. I think the dialog itself must give
> double quote if user give string (or character) as argument.

No! 'Y' is also a valid formula and therefore valid input. It just means something different. There is no way to differentiate between 'Y' and "Y" for the formula compiler and you as user have to use the correct form.
Comment 11 Markus Mohrhard 2013-01-04 03:17:55 UTC
(In reply to comment #7)
> @markus: do you think this can be added to 3.6 code too? Thanks,

The bug fix is only for 4.0. In 3.6 the problem was not existing like that. There the only problem was the use of 'Y' vs "Y"
Comment 12 Cor Nouws 2013-01-04 08:40:37 UTC
(In reply to comment #11)
> (In reply to comment #7)
> > @markus: do you think this can be added to 3.6 code too? Thanks,
> 
> The bug fix is only for 4.0. In 3.6 the problem was not existing like that.
> There the only problem was the use of 'Y' vs "Y"

Hmm, I checked it before I asked, of course. And got the same wierd results while using double quotes...
But now cannot reproduce it. So I prolly did something wrong there. Sorry.
Comment 13 Cor Nouws 2013-01-04 08:51:00 UTC
(In reply to comment #10)
> No! 'Y' is also a valid formula and therefore valid input. It just means
> something different. There is no way to differentiate between 'Y' and "Y"
> for the formula compiler and you as user have to use the correct form.

Then when one chooses e.g "equal to" and just enters Y, without any quote, it is converted to 'Y'.
Is that what a user should expect?

Do you have any simple hint on difference 'Y'  and "Y" (looked for it via Google, but apparently not hard enough..)
Comment 14 Markus Mohrhard 2013-01-04 09:14:39 UTC
> 
> Then when one chooses e.g "equal to" and just enters Y, without any quote,
> it is converted to 'Y'.
> Is that what a user should expect?

That sounds strange. Y => range name/formula function, 'Y' => column label, "Y" => string. However there might be some nasty quirks in the formula parser that notices that this file contains column labels with the name 'Y' and therefore when it can't find Y as function and range name uses it as column label.

> 
> Do you have any simple hint on difference 'Y'  and "Y" (looked for it via
> Google, but apparently not hard enough..)

See above. It is not obvious but it is part of OpenFormula.
Comment 15 sugeng 2013-01-10 22:09:15 UTC
(In reply to comment #12)
> 
> 
> Hmm, I checked it before I asked, of course. And got the same wierd results
> while using double quotes...
> But now cannot reproduce it. So I prolly did something wrong there. Sorry.

When I read the manual, it says to select a cell. It means literally "a" cell, not many cell. So I select just one cell, apply cond format, then copy that cell format to other cell using "format paintbrush", the one in the right of "paste" button.