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.
Created attachment 149438 [details] database sample * no real data
Created attachment 149439 [details] sample data
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
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.
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
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.
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.
Created attachment 149552 [details] columns in source and target tables
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
(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".
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
I don't see why 'perf' is needed here...
So I get the warning, I click Yes, but then nothing is in the table when I open it...
Created attachment 152703 [details] Sorted sample data
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
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...
I think I can fix it...
*** Bug 126281 has been marked as a duplicate of this bug. ***
Patch in gerrit -> https://gerrit.libreoffice.org/#/c/75346/
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.
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.
*** Bug 126355 has been marked as a duplicate of this bug. ***
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.
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.
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
Created attachment 152788 [details] Problematic file after Tamás' patch
(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
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?
(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?
Created attachment 152863 [details] screencast showing the problem
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.
(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 ?
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.
(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.
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.
Xisco Fauli committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/+/54028dc503fc08eb12e287919d5e2850cff05b73%5E%21 tdf#126511 Show only icon names instead of full path. 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.
Problem with attachment 152886 [details] is still reproducible...
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.
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.
Issue still existing. Version: 6.3.5.2 (x64) Build ID: dd0751754f11728f69b42ee2af66670068624673 CPU threads: 8; OS: Windows 10.0; UI render: default; VCL: win; Locale: en-PH (en_PH); UI-Language: en-US Calc: threaded
(In reply to perie_gut from comment #40) > Issue still existing. > > > Version: 6.3.5.2 (x64) > Build ID: dd0751754f11728f69b42ee2af66670068624673 > CPU threads: 8; OS: Windows 10.0; UI render: default; VCL: win; > Locale: en-PH (en_PH); UI-Language: en-US > Calc: threaded Any update on the issue?