Bug Hunting Session
Bug 123591 - Firebird - Incorrect Pasting of Numeric Data
Summary: Firebird - Incorrect Pasting of Numeric Data
Status: REOPENED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
6.1.5.2 release
Hardware: All All
: medium normal
Assignee: Tamas Bunth
URL:
Whiteboard: target:6.4.0 target:6.3.0.2 target:6.3.1
Keywords: bibisected, bisected
: 126281 126355 (view as bug list)
Depends on:
Blocks: Database-Firebird-Default
  Show dependency treegraph
 
Reported: 2019-02-20 10:36 UTC by perie_gut
Modified: 2019-09-19 10:26 UTC (History)
8 users (show)

See Also:
Crash report or crash signature:


Attachments
database sample (11.77 KB, application/vnd.sun.xml.base)
2019-02-20 10:37 UTC, perie_gut
Details
sample data (23.74 KB, application/vnd.oasis.opendocument.spreadsheet)
2019-02-20 10:38 UTC, perie_gut
Details
Screenshot shows how to assign columns from copy to table (36.48 KB, image/png)
2019-02-23 09:52 UTC, Robert Großkopf
Details
columns in source and target tables (10.81 KB, image/png)
2019-02-24 09:21 UTC, ribotb
Details
Sorted sample data (25.07 KB, application/vnd.oasis.opendocument.spreadsheet)
2019-07-10 08:54 UTC, Xisco Faulí
Details
Problematic file after Tamás' patch (13.96 KB, application/vnd.oasis.opendocument.spreadsheet)
2019-07-15 16:03 UTC, Xisco Faulí
Details
screencast showing the problem (1.67 MB, video/mp4)
2019-07-18 17:48 UTC, Xisco Faulí
Details
Firebird-File with two tables and Calc-File for testing (91.71 KB, application/zip)
2019-07-20 15:06 UTC, Robert Großkopf
Details

Note You need to log in before you can comment on or make changes to this bug.
Description perie_gut 2019-02-20 10:36:13 UTC
Posted numeric values are not correct due to inclusion of decimals.

How to generate the issue
1. Open the base file
2. Open the calc file
3. Copy the calc file data (including the Header)
4. paste the date to table t_invoice_list
5. check the posted values under the following columns: invoiceamount, localamount, reportamount.
Comment 1 perie_gut 2019-02-20 10:37:39 UTC
Created attachment 149438 [details]
database sample

* no real data
Comment 2 perie_gut 2019-02-20 10:38:09 UTC
Created attachment 149439 [details]
sample data
Comment 3 perie_gut 2019-02-20 10:39:39 UTC
Version: 6.2.0.3 (x64)
Build ID: 98c6a8a1c6c7b144ce3cc729e34964b47ce25d62
CPU threads: 4; OS: Windows 10.0; UI render: default; VCL: win; 
Locale: en-PH (en_PH); UI-Language: en-US
Calc: threaded
Comment 4 Robert Großkopf 2019-02-20 17:10:52 UTC
Seems Base/Firebird doesn't recognize the decimalseparator from the Calc-import-file. If set to 1 Decimal-Places (as in the example database) the shown value will be devided by 10.

Have tested this with LO 6.1.5.2 and OpenSUSE 15 64bit rpm Linux.
Comment 5 ribotb 2019-02-22 11:38:08 UTC
When I paste into to table t_invoice_list, an error message is displayed :

Avertissement
Incorrect type for setString
Voulez-vous vraiment poursuivre ?
Oui / Non

Version: 6.2.0.3 (x64)
Build ID: 98c6a8a1c6c7b144ce3cc729e34964b47ce25d62
Threads CPU : 4; OS : Windows 10.0; UI Render : par défaut; VCL: win; 
Locale : fr-FR (fr_FR); Langue IHM : fr-FR
Calc: threaded
Comment 6 Robert Großkopf 2019-02-23 09:52:24 UTC
Created attachment 149542 [details]
Screenshot shows how to assign columns from copy to table

The error message you reported appears if you don't assign the columns in the dialogue.

There is a design-bug in this dialogue. Could be it appears only in LO together with gtk. The buttons for moving up and down the fields will show alphabetical sorting instead. I will report this as another bug to the bug Firebird import with decimal-places doesn't work right.
Comment 7 ribotb 2019-02-24 09:19:15 UTC
The columns of the target table are in the right order in the dialog, but the columns of the source table and the columns in the target table are not in the same order.
See attachment.
Comment 8 ribotb 2019-02-24 09:21:00 UTC
Created attachment 149552 [details]
columns in source and target tables
Comment 9 ribotb 2019-02-24 10:49:01 UTC
Version: 6.2.0.3 (x64)
Build ID: 98c6a8a1c6c7b144ce3cc729e34964b47ce25d62
Threads CPU : 4; OS : Windows 10.0; UI Render : par défaut; VCL: win; 
Locale : fr-FR (fr_FR); Langue IHM : fr-FR
Calc: threaded
Comment 10 Robert Großkopf 2019-02-24 15:49:16 UTC
(In reply to ribotb from comment #7)
> The columns of the target table are in the right order in the dialog, but
> the columns of the source table and the columns in the target table are not
> in the same order.
> See attachment.

Couldn't you change the order by the buttons? Note bug 119693 for the wrong display in the button. The upper buttons on the left will switch the marked fields of the left up. Same behaviour with all the other buttons which will show "A→Z" instead of "UP" and "DOWN".
Comment 11 ribotb 2019-02-24 19:29:07 UTC
By ordering the columns of the source table, the copy works.

And I confirm: Base/Firebird doesn't recognize the decimalseparator from the Calc import also in v6.2.0
Comment 12 Xisco Faulí 2019-07-10 07:50:55 UTC
I don't see why 'perf' is needed here...
Comment 13 Xisco Faulí 2019-07-10 08:44:20 UTC Comment hidden (obsolete)
Comment 14 Xisco Faulí 2019-07-10 08:54:04 UTC
Created attachment 152703 [details]
Sorted sample data
Comment 15 Xisco Faulí 2019-07-10 09:16:07 UTC
The wrong behaviour started after https://cgit.freedesktop.org/libreoffice/core/commit/?id=9ccc1f59446bc4a16b6d090a3b556e184eb5f159, however, I do not believe this is a regression, before the mentioned commit, the value displayed was 7.38E+17
Comment 16 Xisco Faulí 2019-07-10 10:45:12 UTC
I've investigated this issue a bit, the problem is the Numeric fields use 1 decimal place, so in setDouble < https://opengrok.libreoffice.org/xref/core/connectivity/source/drivers/firebird/PreparedStatement.cxx?r=70519a43#416 > we pass the values as they come from Calc without considering the decimal places.
Later on, makeNumericString < https://opengrok.libreoffice.org/xref/core/connectivity/source/drivers/firebird/ResultSet.cxx?r=31f04378#373 > takes the decimal places into account, thus 1 digit is removed...
Comment 17 Xisco Faulí 2019-07-10 10:56:57 UTC
I think I can fix it...
Comment 18 Xisco Faulí 2019-07-10 11:32:52 UTC
*** Bug 126281 has been marked as a duplicate of this bug. ***
Comment 19 Xisco Faulí 2019-07-10 11:33:37 UTC
Patch in gerrit -> https://gerrit.libreoffice.org/#/c/75346/
Comment 20 Commit Notification 2019-07-10 12:47:52 UTC
Xisco Fauli committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/+/fb52df57abe51a5923a49fb0818f4b58565dfc25%5E%21

tdf#123591: Firebird: take decimal places into account...

It will be available in 6.4.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 21 Commit Notification 2019-07-10 14:02:57 UTC
Xisco Fauli committed a patch related to this issue.
It has been pushed to "libreoffice-6-3":

https://git.libreoffice.org/core/+/b7c74a050bc0e1b5ac86dfe476d68a5029bada6e%5E%21

tdf#123591: Firebird: take decimal places into account...

It will be available in 6.3.0.2.

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 22 Robert Großkopf 2019-07-13 18:45:37 UTC
*** Bug 126355 has been marked as a duplicate of this bug. ***
Comment 23 Commit Notification 2019-07-13 19:22:12 UTC
Tamas Bunth committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/+/a0d85d526c7a4407ac555a35730afd5003243bcb%5E%21

tdf#123591: Firebird: Fix setting double values

It will be available in 6.4.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 24 Commit Notification 2019-07-15 01:51:18 UTC
Tamas Bunth committed a patch related to this issue.
It has been pushed to "libreoffice-6-3":

https://git.libreoffice.org/core/+/cc09dc2480cae51233538f7eede78906f9a5a2c9%5E%21

tdf#123591: Firebird: Fix setting double values

It will be available in 6.3.0.2.

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 25 Xisco Faulí 2019-07-15 09:41:14 UTC
Hi Tamas Bunth,
You're patch is not correct. Values like

0,25
1,25
1.000,25
10,25
100,25

are converted to

2,5
12,5
1.0002,5
102,5
1002,5
Comment 26 Xisco Faulí 2019-07-15 16:03:23 UTC
Created attachment 152788 [details]
Problematic file after Tamás' patch
Comment 27 Xisco Faulí 2019-07-16 10:30:30 UTC
(In reply to Xisco Faulí from comment #26)
> Created attachment 152788 [details]
> Problematic file after Tamás' patch

if we change the decimal places in calc to 1 before we paste it, the problem is not reproducible, so the problem happens when the decimal places in calc < decimal places in base
Comment 28 Tamas Bunth 2019-07-18 17:25:10 UTC
I couldn't reproduce it.

My steps were:
- Open attachment 152788 [details] (Calc file)
- Open attachment 149438 [details] (Base file)
- Select every cell in Calc file and copy it with Ctrl+C
- Open "Tables" tab in Base.
- Right click on table "t_invoice_list" and select "paste"
- Next and Create. (I did not change anything)
- A worning pop-up dialog appears complaining about incorrect type. Press Yes.

The results in column "reportamount" is:
0.2
1.2
1000.2
10.2
100.2
1000

Whereas the original values in Calc document:
0.25
1.25
1000.25
10.25
100.25
1000.00

Which is the correct behavior as far as I see. The last digit is truncated because the "reportamount" column was created as a NUMERIC with 1 decimal places.

In order to keep the data as is, you have to alter the table (for example with the GUI, right click on table, and Edit..) and change the "reportamount" column to have 2 decimal places.

What are the precise steps to reproduce the problem?
Comment 29 Xisco Faulí 2019-07-18 17:45:39 UTC
(In reply to Tamas Bunth from comment #28)

> What are the precise steps to reproduce the problem?

Same exact steps you described. I'll attach a screencast.

If you edit the table 'reportamount', it has 1 decimal place, right ? and what about the column 'reportamount' in calc, 2 decimal places, isn't it?
Comment 30 Xisco Faulí 2019-07-18 17:48:39 UTC
Created attachment 152863 [details]
screencast showing the problem
Comment 31 rich 2019-07-18 17:55:50 UTC
Comment on attachment 152863 [details]
screencast showing the problem

In my case the problem occurs when you specify the decimal positions for the columns in the database table during the import.  Otherwise the data comes over as text which looks fine but is not directly usable as numeric data.  Please see the attachments to my original bug report.  The middle attachment shows a screen shot of how to specify the incoming data as numeric.
Comment 32 Xisco Faulí 2019-07-18 18:59:40 UTC
(In reply to rich from comment #31)
> Comment on attachment 152863 [details]
> screencast showing the problem
> 
> In my case the problem occurs when you specify the decimal positions for the
> columns in the database table during the import.  Otherwise the data comes
> over as text which looks fine but is not directly usable as numeric data. 
> Please see the attachments to my original bug report.  The middle attachment
> shows a screen shot of how to specify the incoming data as numeric.

Which attachments do you mean ?
Comment 33 rich 2019-07-18 20:21:39 UTC
The attachments are in comments 4, 5 and 6 on my original case 126355 which was deemed a duplicate of 123591.  There is a comment to 126355 which explains each screenshot. I have since trashed the screenshots and the 6.2.4.8 instance which I used to create them so you will have to refer to 126355.  Sorry.
Comment 34 Xisco Faulí 2019-07-18 20:49:45 UTC
(In reply to rich from comment #33)
> The attachments are in comments 4, 5 and 6 on my original case 126355 which
> was deemed a duplicate of 123591.  There is a comment to 126355 which
> explains each screenshot. I have since trashed the screenshots and the
> 6.2.4.8 instance which I used to create them so you will have to refer to
> 126355.  Sorry.

oh, I see, thanks for the clarification.
I get the same problem if I create a new table in base instead of appending the content to an existing one.
Comment 35 Robert Großkopf 2019-07-20 15:06:17 UTC
Created attachment 152886 [details]
Firebird-File with two tables and Calc-File for testing

Extract the attachment. 
There is a Firebird-file and a Calc-file as datasource for testing
Firebird-file contains 2 tables. One has the right decimal-places for the values, which should be imported, the second only 4 decimal places for values with 5 decimal places.
While importing the values from Calc to Firebird no error appears but the last values with 5 decimal places are wrong in the fields for NUMERIC and DECIMAL:
11.2345 instead of 1.12345
21.2345 instead of 2.12345

There are two buggy behaviours now: 
No warning (values with too many decimal places couldn't be saved) and 
Wrong values if there aren't enough decimal places in the target table.
Comment 36 Xisco Faulí 2019-07-31 09:48:06 UTC Comment hidden (obsolete)
Comment 37 Xisco Faulí 2019-07-31 12:44:01 UTC
Problem with attachment 152886 [details] is still reproducible...
Comment 38 Xisco Faulí 2019-07-31 12:45:03 UTC
Xisco Fauli committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/+/54028dc503fc08eb12e287919d5e2850cff05b73%5E%21

tdf#123591: truncate and round to 'scale' number of decimal places

It will be available in 6.4.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 39 Commit Notification 2019-08-07 21:29:44 UTC
Xisco Fauli committed a patch related to this issue.
It has been pushed to "libreoffice-6-3":

https://git.libreoffice.org/core/+/b9a0dc13a02e71dc680ad6b3d1d9f55ae27e3217%5E%21

tdf#123591: truncate and round to 'scale' number of decimal places

It will be available in 6.3.1.

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.