Bug 41577 - PIVOTTABLE: floating-point data from database getting converted to date
Summary: PIVOTTABLE: floating-point data from database getting converted to date
Status: RESOLVED DUPLICATE of bug 42169
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.4.3 release
Hardware: x86 (IA32) Windows (All)
: high normal
Assignee: Not Assigned
URL:
Whiteboard: BSA
Keywords:
Depends on:
Blocks:
 
Reported: 2011-10-07 15:53 UTC by raf32
Modified: 2011-11-27 02:35 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
inside the blue boundary, what calc gets from the datapilot fed from the database; inside the red boundary, what base sees in the same database (35.09 KB, image/png)
2011-10-07 15:53 UTC, raf32
Details

Note You need to log in before you can comment on or make changes to this bug.
Description raf32 2011-10-07 15:53:09 UTC
Created attachment 52102 [details]
inside the blue boundary, what calc gets from the datapilot fed from the database; inside the red boundary, what base sees in the same database

Problem description:
When LibreOffice calc retrieves floating-point data from a LibreOffice database (odb file) while having a locale setting with comma as the decimal separator, a floating-point number from the database may get changed by calc (and therefore lost from the spreadsheet) into a date. That is, a number like 2.12 gets changed into an integer (in this example, 40879) which yields 2/12/01 (December 2nd 2011) in date format. The floating-point data is then forever lost.

Steps to reproduce:
1. Create a txt file like the following (without the pounds of course):
##########################
numbers field
22,1 a
22 b
55,1 c
5,501 d
5,5001 e
5,50001 f
##########################

2. Fire up database and connect to that txt file for it to create a table from that txt file using space as field separator and comma as decimal separator. Save that odb file and register it to your data sources (so you can later access it from calc) 

2.a. check that in the database, everything seems ok; double-clicking the table you just created gives you:
#########################
numbers field
22,10 a
22,00 b
55,10 c
5,50 d
5,50 e
5,50 f
#########################
and right-clicking the table and selecting "edit" shows you that:
#########################
Field Name   Field Type
numbers     Decimal [ DECIMAL ]
field      Text [ VARCHAR ]
#########################

3. fire up calc, go to Data -> DataPilot -> Start... -> Data source registered in LibreOffice
In "Database", select your data source, this one you have just registered
In "Data Source", select the table with that test data you have just created
In "Type", select "sheet"
Confirm. Then, in the box that appears, drag "field" to the "Row fields" area, and then do the same with "numbers".


Current behavior:

A sheet is created in calc, with the following table:
##############################
 |    A     |       B
--------------------------
1| field    |    numbers
--------------------------
2|   a      | 22/01/11
--------------------------
3|   b      | 22
--------------------------
4|   c      | 55.1
--------------------------
5|   d      | 5501
--------------------------
6|   e      | 01/05/01
--------------------------
7|   f      | 5.50001
##############################
additionally, if I enter the following formulas in cells next to that table:
=ISNUMBER(B2)
=ISTEXT(B2)
=ISNONTEXT(B2)
and drag down to get information about the data types of whatever got inside calc, I get:
##########################################################
 |    A     |       B     |    c     |   d    |     e 
----------------------------------------------------------
1| field    |    numbers  | isnumber | istext | isnontext
----------------------------------------------------------
2|   a      | 22/01/11    |    1     | false  | 1
----------------------------------------------------------
3|   b      | 22          |    1     | false  | 1
----------------------------------------------------------
4|   c      | 55.1        | false    |   1    | false
----------------------------------------------------------
5|   d      | 5501        |    1     | false  | 1
----------------------------------------------------------
6|   e      | 01/05/01    |    1     | false  | 1
----------------------------------------------------------
7|   f      | 5.50001     | false    |   1    | false
##########################################################


Expected behavior:
A sheet is created in calc, with the following table:
##########################################################
 |    A     |       B     |    c     |   d    |     e 
----------------------------------------------------------
1| field    |    numbers  | isnumber | istext | isnontext
----------------------------------------------------------
2|   a      | 22,1        |    1     | false  | 1
----------------------------------------------------------
3|   b      | 22          |    1     | false  | 1
----------------------------------------------------------
4|   c      | 55,1        |    1     | false  | 1
----------------------------------------------------------
5|   d      | 5,501       |    1     | false  | 1
----------------------------------------------------------
6|   e      | 5,5001      |    1     | false  | 1
----------------------------------------------------------
7|   f      | 5,50001     |    1     | false  | 1
##########################################################

OBS: this appears to be a problem in the integration between base and calc, since base sees everything as floats and knows where the decimal separator is and calc simply converts data to whatever it thinks it is. Numbers come to calc with dots even though the locale is set to use commas both system-wide and LibreOffice-wide, and numbers within date range (between 1 and 31 for days and 1 and 12 for months) get converted to date. Notice that 55,1 and 5,50001 didn't get converted to date but got converted to text and 5,501 simply lost it's decimal information (maybe got converted to a year??)
This also happens if the txt is created with dots instead of commas and base is told to use dot as decimal separator. Base shows things as they should be, and calc shows them messed up. I think it is absurd to have the date autoformatting in DataPilots since the data is being retrieved from a database (and therefore cannot be a typo); it has been suggested before to have an option to disable the autoconverting of strings with numbers and dots into dates and I see here a good reason why the software should be told when it **doesn't** know better than the user. In DataPilots there shouldn't even exist an option to "autofix" things, since anything wrong there must be fixed in the database, which relies externally, and should those things be fixed automatically in the spreadsheet, mistakes in the database might get unnoticed.
As DataPilot cells are locked against formatting, cell formatting is not an option. Also, trying to change the style of text of the DataPilot entries doesn't work. Changing the locale of the whole LibreOffice suite to one that uses dot as decimal separator solves it, but that is **not** an option either (I have documents that must be published countries where comma is the standard for decimal separator) and this behaviour is not what it is supposed to be anyway; calc should be able to understand what data are strings and what are floats anywhere in the world.
Comment 1 Nikos 2011-10-11 02:20:04 UTC
Seems to be the same as this
https://bugs.freedesktop.org/show_bug.cgi?id=38327
(so definitely not only two people regard this as a serious bug)
Comment 2 Rainer Bielefeld Retired 2011-11-27 02:35:44 UTC
I believe it's a DUP of "Bug 42169 - Formatting oddity in pivot table output", but I definitively will not create a sample document.

@reporter:
@reporter:
Please feel free to reopen this Bug if you find evidence that we have an independent issue here (Problem still exists when you try with master).
If you reopen please attach test kit containing all documents instead of long descriptions how to create.

*** This bug has been marked as a duplicate of bug 42169 ***