Bug 96475 - Sorting of error results is incorrect, and once saved error results are reloaded as text content
Summary: Sorting of error results is incorrect, and once saved error results are reloa...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
unspecified
Hardware: All All
: medium normal
Assignee: Eike Rathke
URL:
Whiteboard: target:5.3.0
Keywords:
Depends on:
Blocks:
 
Reported: 2015-12-14 09:29 UTC by Ari Latvala
Modified: 2017-10-03 15:56 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:


Attachments
Test file demonstrating sorting problem (11.99 KB, application/vnd.oasis.opendocument.spreadsheet)
2015-12-14 09:29 UTC, Ari Latvala
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Ari Latvala 2015-12-14 09:29:07 UTC
Created attachment 121289 [details]
Test file demonstrating sorting problem

When searching with vlookup date, then copying data without formulas to te new sheet, sorting does not work correctly. Check the attached ODT test file with 3 sheets: "List" containing formulas, which are searching data from the "Inventory" sheet and "Summary" sheet is a result of "copy/paste special" of "List" with only Text, Numbers, Date&Time and Formats included. Now sorting "Summary" sheet by "Contact" seems to be incorrect:
- Cells with empty value have changed to show 30.12.1899
- non-existing "host" cells showing correctly #N/A
- Cells with existing Date value showing correctly

When trying to sort now by "Contact", only the cells with existing Date value seems to be sorted correctly. #N/A and originally empty but now with bogus date value filled cells doesn't seem to be sorting at all.
Comment 1 Ari Latvala 2015-12-14 09:35:03 UTC
Running LibreOffice 5.0.3.2 on Linux and LibreOffice 5.0.4.1 on 64-bit Windows 7.
Comment 2 Ari Latvala 2015-12-14 15:29:33 UTC
Now tested also under LibreOffice 4.2.8 running on Linux RHEL 6.7 64-bit, same behavior there. 

Difference between Linux and Windows seems to be that at least on 5.0.4.1 on Windows 7 64-bit those empty Date cells on the first sheet collected with VLOOKUP show up correctly as empty but under Linux versions 4.2.8 and 5.0.3.2 those already show date 30.12.1899 when cell is configured as Date or 0, if cell format is text/general.

After copy / paste special all versions have changed those empty cells incorrectly to 0 / 30.12.1899.
Comment 3 m.a.riosv 2015-12-15 00:51:04 UTC
Hi,

With Win maybe you have disable the option for show zero values in Menu/Tools/LibreOffice calc/View

From a VLOOKUP it's not possible to get an empty cell at most you can get a cell with an empty string.
List.B2
=IF(VLOOKUP(A2;Inventory.$A$1:$B$7;2;0);VLOOKUP(A2;Inventory.$A$1:$B$7;2;0);"")

Dates in calc are stored as integers being the zero 31/12/1899.

https://help.libreoffice.org/Calc/Date_and_Time_Functions
Comment 4 Ari Latvala 2015-12-15 05:45:17 UTC
You were correct about that "Show zero values" but the original problem with the sorting is still there, i.e. #N/A and zero values are treated equally, which should not be the case.
Comment 5 Jacques Guilleron 2015-12-15 08:39:42 UTC
Hi Ari,

You have to first examine the way LO treats empty cells.
https://help.libreoffice.org/Calc/Handling_of_Empty_Cells

Regards
Comment 6 m.a.riosv 2015-12-15 08:52:49 UTC
(In reply to Ari Latvala from comment #4)
> You were correct about that "Show zero values" but the original problem with
> the sorting is still there, i.e. #N/A and zero values are treated equally,
> which should not be the case.

How then?
Comment 7 Ari Latvala 2015-12-15 13:44:20 UTC
I would prefer similar behavior than on Excel 2013. There that date sorting on "Summary" will arrange empty dates as oldest (0.1.00), then actual dates on the correct order, as does LibreOffice and then all those #N/A fields are a the end.
Comment 8 Elmer 2016-01-27 16:30:06 UTC
sorting a column by date wwworks if dates are all same year. sorting dates that span more than one year does not sort.
really, people... this is a release version? and a very basic necessary function and it doesn't work???
Comment 9 Ari Latvala 2016-03-02 22:16:27 UTC
(In reply to Elmer from comment #8)
> sorting a column by date wwworks if dates are all same year. sorting dates
> that span more than one year does not sort.
> really, people... this is a release version? and a very basic necessary
> function and it doesn't work???

Just tested with LO 5.2 nightly version 2.3.2016 build on Windows 7 SP1 and noticed interesting behaviour. Before saving sorting on the Summary tab does not make difference between empty and non-existing (#N/A) date information but once saved as ODS and re-opened, then working as expected, i.e. #N/A sorted last and empty dates at the beginning, existing dates in between and even different years seems to sort nicely.
Comment 10 Buovjaga 2016-10-21 17:18:02 UTC
Ari: can this be closed as WORKSFORME? You are happy?
Comment 11 Ari Latvala 2016-10-21 19:46:01 UTC
No,I am not happy. Sorting still works diffetently before and after the Save. After Save it works as expected but before #N/A and zero values are treated equally,
Comment 12 Buovjaga 2016-10-25 16:50:41 UTC
(In reply to Ari Latvala from comment #9)
> Just tested with LO 5.2 nightly version 2.3.2016 build on Windows 7 SP1 and
> noticed interesting behaviour. Before saving sorting on the Summary tab does
> not make difference between empty and non-existing (#N/A) date information
> but once saved as ODS and re-opened, then working as expected, i.e. #N/A
> sorted last and empty dates at the beginning, existing dates in between and
> even different years seems to sort nicely.

#N/A is sorted last and empty dates at the beginning for me in attachment 121289 [details] before saving. Is there something I'm missing? Maybe you need to provide a clean file in addition to your "end result" file?

Arch Linux 64-bit, KDE Plasma 5
Version: 5.3.0.0.alpha1+
Build ID: cd7b306e614a938b7b39a66784899c22ee6f6c64
CPU Threads: 8; OS Version: Linux 4.8; UI Render: default; VCL: kde4; 
Locale: fi-FI (fi_FI.UTF-8); Calc: group
Built on October 25th 2016
Comment 13 Ari Latvala 2016-10-26 19:41:21 UTC
Problem is that sorting works differently (incorrectly) right after the "Paste special" before Save, when those #N/A fields are still "Error: Value not available" results (green, aligned to right) and not differentiated on the sort from the empty cells but sorting works correctly after saving and re-opening file, because they have changed at that point to normal text (black, aligned left).
Comment 14 Eike Rathke 2016-11-15 13:21:20 UTC
Actually this is a combination of several problems..
1) pasting formula error results as values creates an internal formula
   cell to preserve the error value (as can be seen by the "highlight"
   green value)
   1a) this does not have a leading '=' though
2) such error values seem to be not treated correctly during sort;
   actually they should be sorted before other values, just as when
   sorting the original formula range
3) when saved, due to 1a the formula is stored without the leading '='
   but cell type string, extended type error, which so far is correct
4) when loaded, this combination results in a string being set at the
   cell, which is not an error value and thus sorts differently
5) VLOOKUP may return empty cells *for display only*, but these when
   pasting as values will be pasted as 0 values and if formatted as date
   result in the null date 1899-12-30
6) similar, they are stored as an empty but 0 formatted value thus when
   reloading in this case display as null date
Comment 15 Eike Rathke 2016-11-15 19:00:25 UTC
(In reply to Eike Rathke from comment #14)
> 2) such error values seem to be not treated correctly during sort;
>    actually they should be sorted before other values

I messed that up, errors should be sorted to the end, after any other values (numeric,text,...) and before blank cells.
Comment 16 Commit Notification 2016-11-15 22:53:53 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

tdf#96475 sort error result between text and empty cell

It will be available in 5.3.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 17 Commit Notification 2016-11-15 22:53:58 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

tdf#96475 PutFormulaCell: any other cell than formula is utter nonsense

It will be available in 5.3.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 18 Commit Notification 2016-11-16 11:26:05 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

tdf#96475 PasteSpecial EmptyDisplayedAsString as empty

It will be available in 5.3.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 19 Commit Notification 2016-11-16 17:25:54 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

tdf#96475 restore the EmptyDisplayedAsString condition during load

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