[Libreoffice-bugs] [Bug 116893] New: Firebird - Error when transferring/ pasting NUMERIC or DECIMAL table data

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=116893

Bug ID: 116893
Summary: Firebird - Error when transferring/pasting NUMERIC or
DECIMAL table data
Product: LibreOffice
Version: 6.0.2.1 release
Hardware: All
OS: All
Status: UNCONFIRMED
Severity: normal
Priority: medium
Component: Base
Assignee: libreoffice-***@lists.freedesktop.org
Reporter: ***@yahoo.com

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

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=116893

Alex Thurgood <***@tuta.io> changed:

What |Removed |Added
----------------------------------------------------------------------------
Status|UNCONFIRMED |NEW
Ever confirmed|0 |1

--- Comment #1 from Alex Thurgood <***@tuta.io> ---
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.

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=116893

Alex Thurgood <***@tuta.io> changed:

What |Removed |Added
----------------------------------------------------------------------------
CC| |***@gmail.com,
| |***@mamane.lu,
| |***@yahoo.fr

--- Comment #2 from Alex Thurgood <***@tuta.io> ---
@Tamas : one for you ?

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=116893

--- Comment #3 from Alex Thurgood <***@tuta.io> ---
Or, is this an error in Calc's number converter ? ( which would more likely be
of interest to Eike ?)

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=116893

--- Comment #4 from Julien Nabet <***@yahoo.fr> ---
I don't understand copy from where to where?
Hsqldb->Firebird?
Calc->Firebird?
Firebird->Calc?
Other?

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=116893

--- Comment #5 from Stang <***@yahoo.com> ---
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

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=116893

--- Comment #6 from Julien Nabet <***@yahoo.fr> ---
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

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=116893

--- Comment #7 from Julien Nabet <***@yahoo.fr> ---
code pointer:
https://opengrok.libreoffice.org/xref/core/connectivity/source/drivers/firebird/PreparedStatement.cxx#226

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=116893

--- Comment #8 from Julien Nabet <***@yahoo.fr> ---
Created attachment 141273
--> https://bugs.documentfoundation.org/attachment.cgi?id=141273&action=edit
bt with debug symbols

Here's a bt from setString

(whereas I filled the origin table with non decimal values, I see "1.00"!)

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=116893

--- Comment #9 from Alex Thurgood <***@tuta.io> ---
(In reply to Julien Nabet from comment #6)

Post by b***@bugs.documentfoundation.org
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.

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=116893

--- Comment #10 from Lionel Elie Mamane <***@mamane.lu> ---
(In reply to Julien Nabet from comment #7)

Post by b***@bugs.documentfoundation.org
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.

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=116893

Xisco Faulí <***@libreoffice.org> changed:

What |Removed |Added
----------------------------------------------------------------------------
CC| |***@libreoffice.org
Blocks| |51780


Referenced Bugs:

https://bugs.documentfoundation.org/show_bug.cgi?id=51780
[Bug 51780] [META] Default to Firebird not HSQLDB in Base (for _new_ files)

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=116893

Alex Thurgood <***@tuta.io> changed:

What |Removed |Added
----------------------------------------------------------------------------
CC| |***@hotmail.fr

--- Comment #11 from Alex Thurgood <***@tuta.io> ---
*** Bug 120237 has been marked as a duplicate of this bug. ***

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=116893

--- Comment #12 from Alex Thurgood <***@tuta.io> ---
Seems that bug 53027 is also relevant here. @Julien, you looked into some of
the problems with the code there at the time.

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=116893

Alex Thurgood <***@tuta.io> changed:

What |Removed |Added
----------------------------------------------------------------------------
See Also| |https://bugs.documentfounda
| |tion.org/show_bug.cgi?id=53
| |027

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=116893

Alex Thurgood <***@tuta.io> changed:

What |Removed |Added
----------------------------------------------------------------------------
CC| |***@gmx.com

--- Comment #13 from Alex Thurgood <***@tuta.io> ---
*** Bug 120187 has been marked as a duplicate of this bug. ***

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=116893

--- Comment #14 from Julien Nabet <***@yahoo.fr> ---
(In reply to Lionel Elie Mamane from comment #10)

Post by b***@bugs.documentfoundation.org
...
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++)

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=116893

--- Comment #15 from Lionel Elie Mamane <***@mamane.lu> ---
(In reply to Julien Nabet from comment #14)

Post by b***@bugs.documentfoundation.org
(In reply to Lionel Elie Mamane from comment #10)

Post by b***@bugs.documentfoundation.org
...
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

Post by b***@bugs.documentfoundation.org
Then which scale value should we use here (since there's no "scale" arg)
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.

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=116893

Alex Thurgood <***@tuta.io> changed:

What |Removed |Added
----------------------------------------------------------------------------
CC| |***@gmail.com

--- Comment #16 from Alex Thurgood <***@tuta.io> ---
*** Bug 120693 has been marked as a duplicate of this bug. ***

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=116893

Justin L <***@mail.com> changed:

What |Removed |Added
----------------------------------------------------------------------------
CC| |***@mail.com

--- Comment #17 from Justin L <***@mail.com> ---
Created attachment 147342
--> https://bugs.documentfoundation.org/attachment.cgi?id=147342&action=edit
tdf121921_decimal1000.odb: HSQLDB decimal numbers migrate incorrectly.

(In reply to Alex Thurgood from comment #16)

Post by b***@bugs.documentfoundation.org
*** 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).

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=116893

--- Comment #18 from Drew Jensen <***@gmail.com> ---
Created attachment 147393
--> https://bugs.documentfoundation.org/attachment.cgi?id=147393&action=edit
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