Bug 116893 - Firebird - Error when transferring/pasting NUMERIC or DECIMAL table data
Summary: Firebird - Error when transferring/pasting NUMERIC or DECIMAL table data
Status: RESOLVED DUPLICATE of bug 126268
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
6.0.2.1 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
: 120187 120237 120693 (view as bug list)
Depends on:
Blocks: Database-Firebird-Default
  Show dependency treegraph
 
Reported: 2018-04-09 01:35 UTC by Stang
Modified: 2019-07-10 14:15 UTC (History)
10 users (show)

See Also:
Crash report or crash signature:


Attachments
bt with debug symbols (11.43 KB, text/plain)
2018-04-10 20:58 UTC, Julien Nabet
Details
tdf121921_decimal1000.odb: HSQLDB decimal numbers migrate incorrectly. (37.17 KB, application/vnd.oasis.opendocument.database)
2018-12-07 09:25 UTC, Justin L
Details
Results of importing data from Calc intro firebird (64.80 KB, image/jpeg)
2018-12-08 20:06 UTC, Drew Jensen
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Stang 2018-04-09 01:35:43 UTC
Description:
Any table containing a numeric or decimal data field creates an error and the data is not transferred.  This happens whether data originates from HSQL embedded, split, MySQL and other DB's.  Will work if just copying data within the same .odb.

Steps to Reproduce:
1.Copy source table 
2.Try to paste table in Firebird
3.

Actual Results:  
Message stating error.

Expected Results:
Data is copied


Reproducible: Always


User Profile Reset: Yes



Additional Info:


User-Agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/65.0.3325.181 Safari/537.36
Comment 1 Alex Thurgood 2018-04-09 07:43:04 UTC
Confirming with

Version: 6.0.3.2
Build ID: 8f48d515416608e3a835360314dac7e47fd0b821
Threads CPU : 4; OS : Mac OS X 10.13.4; UI Render : par défaut; 
Locale : fr-FR (fr_FR.UTF-8); Calc: group


The copy table wizard displays an error message that it can't interpret some fields (which are NUMERIC in the original tables) and then assigns these fields a DECIMAL field type. The attempt by the wizard to populate the tables with such field definitions fails and no data is copied.

If the user adapts the field types in the copy wizard dialog before final creation, and changes the field types to NUMERIC, an error message is displayed : 


Error message : 
Incorrect type for SetString

and the user asked if the wizard should continue. If the user accepts to continue, a table is created with no data.
Comment 2 Alex Thurgood 2018-04-09 07:43:36 UTC
@Tamas : one for you ?
Comment 3 Alex Thurgood 2018-04-09 07:44:30 UTC
Or, is this an error in Calc's number converter ? ( which would more likely be of interest to  Eike ?)
Comment 4 Julien Nabet 2018-04-10 19:38:03 UTC
I don't understand copy from where to where?
Hsqldb->Firebird?
Calc->Firebird?
Firebird->Calc?
Other?
Comment 5 Stang 2018-04-10 19:49:23 UTC
Copy table from .odb using HSQLDB embedded or split (so v1.8 thru 2.x)

   OR

Copy table from .odb using MySQL

   OR

Copy table from .odb using Firebird embedded

    TO

Another .odb using Firebird embedded
Comment 6 Julien Nabet 2018-04-10 20:41:42 UTC
Thank you Stang for your feedback, so the pb is when one's tries to copy a table with decimal/numeric from a database X to a database Y (X different from Y)
Alex: so why did you mention Calc? 

I created a table on a brand new file with embedded hsqldb 
id  integer, testnum numeric, testdec decimal
and filled 2 lines:
1, 1, 1
2, 2, 2

Then I tried to copy the table on a brand new Firebird embedded file and indeed got an error:
Incorrect type for setString
Comment 8 Julien Nabet 2018-04-10 20:58:15 UTC
Created attachment 141273 [details]
bt with debug symbols

Here's a bt from setString

(whereas I filled the origin table with non decimal values, I see "1.00"!)
Comment 9 Alex Thurgood 2018-04-11 07:22:52 UTC
(In reply to Julien Nabet from comment #6)
> Thank you Stang for your feedback, so the pb is when one's tries to copy a
> table with decimal/numeric from a database X to a database Y (X different
> from Y)
> Alex: so why did you mention Calc? 
> 

Hi Julien,

I mentioned the Calc number interpreter because it was my understanding that this is used by the data copy assistant to map the data on import.
Comment 10 Lionel Elie Mamane 2018-04-11 09:40:45 UTC
(In reply to Julien Nabet from comment #7)
> code pointer:
> https://opengrok.libreoffice.org/xref/core/connectivity/source/drivers/
> firebird/PreparedStatement.cxx#226

That select/case statement will need to be extended with new case clause(s) for DECIMAL and NUMERIC. OPreparedStatement::setObjectWithInfo around line 603 contains code that deals with DECIMAL/NUMERIC values.
Comment 11 Alex Thurgood 2018-10-01 12:08:47 UTC
*** Bug 120237 has been marked as a duplicate of this bug. ***
Comment 12 Alex Thurgood 2018-10-10 07:56:04 UTC
Seems that bug 53027 is also relevant here. @Julien, you looked into some of the problems with the code there at the time.
Comment 13 Alex Thurgood 2018-10-10 08:01:55 UTC
*** Bug 120187 has been marked as a duplicate of this bug. ***
Comment 14 Julien Nabet 2018-10-10 19:47:00 UTC
(In reply to Lionel Elie Mamane from comment #10)
> ...
> That select/case statement will need to be extended with new case clause(s)
> for DECIMAL and NUMERIC. OPreparedStatement::setObjectWithInfo around line
> 603 contains code that deals with DECIMAL/NUMERIC values.

The main pb is we don't have sqlType here so "if(sqlType == DataType::DECIMAL || sqlType == DataType::NUMERIC)" isn't possible

Then which scale value should we use here (since there's no "scale" arg)
for this line:
for(sal_Int32 i=sValue.copy(sValue.indexOf('.')+1).getLength(); i<scale;i++)
Comment 15 Lionel Elie Mamane 2018-10-11 09:09:05 UTC
(In reply to Julien Nabet from comment #14)
> (In reply to Lionel Elie Mamane from comment #10)
> > ...
> > That select/case statement will need to be extended with new case clause(s)
> > for DECIMAL and NUMERIC. OPreparedStatement::setObjectWithInfo around line
> > 603 contains code that deals with DECIMAL/NUMERIC values.
> 
> The main pb is we don't have sqlType here so "if(sqlType ==
> DataType::DECIMAL || sqlType == DataType::NUMERIC)" isn't possible

You probably have to add cases for
SQL_SHORT
SQL_LONG
SQL_FLOAT
SQL_DOUBLE
SQL_D_DOUBLE
SQL_INT64

> Then which scale value should we use here (since there's no "scale" arg)
> for this line:
> for(sal_Int32 i=sValue.copy(sValue.indexOf('.')+1).getLength(); i<scale;i++)

Err... Just convert the string to a number (integer or float, depending on the SQL_foo type) and call the corresponding setFloat/setDouble/setLong/...? In these cases, the

if (str.getLength() > pVar->sqllen)
    str = str.copy(0, pVar->sqllen);

will (I think) also do the wrong thing.
Comment 16 Alex Thurgood 2018-10-19 06:52:54 UTC
*** Bug 120693 has been marked as a duplicate of this bug. ***
Comment 17 Justin L 2018-12-07 09:25:01 UTC
Created attachment 147342 [details]
tdf121921_decimal1000.odb: HSQLDB decimal numbers migrate incorrectly.

(In reply to Alex Thurgood from comment #16)
> *** Bug 120693 has been marked as a duplicate of this bug. ***

I was not able to reproduce problems with the numbers 3,4,5, but in my "Main" table, the DECIMAL field "pledgeAmount" migrates values 655.4 thru 1310 as negative numbers (-167116.76 thru -166462.16).
Comment 18 Drew Jensen 2018-12-08 20:06:36 UTC
Created attachment 147393 [details]
Results of importing data from Calc intro firebird

Today on the QA irc channel a report was made concerning importing data from Calc into Base with firebird db. The results of doing this using drag drop from calc to base, using the data from the users report as decimal, then double, then numeric datatypes on the Base side are shown in the graphic.

The data in the calc sheet is:
9000.00   0.00 9000.00
 200.00   0.00 9200.00
   0.00 100.00 9100.00

If the auto detect function is used in the import wizard DOUBLE is the selected datatype for the new table columns and in that case all the data comes in properly.

For DECIMAL and NUMERIC the data does not come in properly, but is consistent in both cases. 
900.00   0.00 900.00
 20.00   0.00 920.00
   0.00 10.00 910.00
Comment 19 sdritchey 2019-02-11 02:53:23 UTC
Moving my comments from bug Bug 120693 to this thread since it is the active bug thread.  Tested this conversion behavior again in just released 6.2.0 and NUMERIC data is still corrupted.


Description:
In testing a copy of my existing database that has been migrated to Firebird, I've noticed some apparently random Number [ NUMERIC ] field data being corrupted. Typical entries for my Table would look something like (0.50, 3.00, 10.00, 55.00 or similar).

Post migration all entries with the previous value of 3.00 are showing -652.36, previous values of 4.00 are showing -651.36, and previous values of 5.00 are showing -650.36. All other numeric entries appear to be correct.

Steps to Reproduce:
1. Convert existing HSQLDB to Firebird
2. Open data Table
3. View NUMERIC field data and errors

Actual Results:
NUMERIC Table fields showed errors

Expected Results:
NUMERIC Table fields should show correct NUMERIC data.


Reproducible: Always
Comment 20 JD 2019-02-14 00:24:23 UTC
I can verify sdritchey@gmail.com, comment #19. Migration of any table containing numeric/decimal data gets corrupted.

I would consider this a VERY CRITICAL problem since it makes using Firebird migration totally unusable and loss of confidence in the whole process.
Comment 21 nikant 2019-03-02 10:15:32 UTC
From LO 6.0.7 stable we tried to go to 6.1.5 stable and turned on "experimental features" in order to try conversion from HSQLDB embedded to Firebird db embedded. Unfortunately most of our DECIMAL data got corrupted and turned into -16xxxx.xx values.

This is highly critical for us since 6.1.5 was considered stable release. It is understandable that Firebird is still experimental feature. We also tried the migration wizard in an 6.2.x portable version of LO but the problem was reproduced again and the conversion was erroneous.
Comment 22 Xisco Faulí 2019-07-10 14:15:19 UTC
(In reply to Drew Jensen from comment #18)
> Created attachment 147393 [details]
> Results of importing data from Calc intro firebird
> 
> Today on the QA irc channel a report was made concerning importing data from
> Calc into Base with firebird db. The results of doing this using drag drop
> from calc to base, using the data from the users report as decimal, then
> double, then numeric datatypes on the Base side are shown in the graphic.
> 
> The data in the calc sheet is:
> 9000.00   0.00 9000.00
>  200.00   0.00 9200.00
>    0.00 100.00 9100.00
> 
> If the auto detect function is used in the import wizard DOUBLE is the
> selected datatype for the new table columns and in that case all the data
> comes in properly.
> 
> For DECIMAL and NUMERIC the data does not come in properly, but is
> consistent in both cases. 
> 900.00   0.00 900.00
>  20.00   0.00 920.00
>    0.00 10.00 910.00

This is fixed in bug 123591.
Regarding the DECIMAL migration, this is already covered by bug 126268.
Closing as RESOLVED DUPLICATED

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