Download it now!
Bug 126268 - FIREBIRD: Migration of DECIMAL creates unusable values
Summary: FIREBIRD: Migration of DECIMAL creates unusable values
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
6.3.0.0.alpha1+
Hardware: x86-64 (AMD64) Linux (All)
: medium normal
Assignee: Xisco Faulí
URL:
Whiteboard: target:6.4.0 target:6.3.0.2 target:6.2.6
Keywords:
: 116893 126358 126401 126924 (view as bug list)
Depends on:
Blocks: Database-Firebird-Migration
  Show dependency treegraph
 
Reported: 2019-07-07 14:32 UTC by Robert Großkopf
Modified: 2019-09-12 09:26 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments
Shows table before and after migration - unusable values in Decimal-field (39.29 KB, application/pdf)
2019-07-07 14:32 UTC, Robert Großkopf
Details
Database for testing migration of Decimal-values (4.36 KB, application/vnd.oasis.opendocument.database)
2019-07-07 14:33 UTC, Robert Großkopf
Details
Small test database with NUMERIC, DECIMAL and DOUBLE data (3.66 KB, application/vnd.sun.xml.base)
2019-09-05 13:12 UTC, Lars Jødal
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Robert Großkopf 2019-07-07 14:32:15 UTC
Created attachment 152620 [details]
Shows table before and after migration - unusable values in Decimal-field

Open the attached *.pdf-file. It will show a table before and after migration by the migation-wizard from HSQLDB to Firebird. 
Time-bug is already supported. 
Migrating of Decimal values is problematic, as shown in bug 118043. The screenshot will show: Migration of decimal values is unusable here.

Open the attached *.odb-file and try it yourself.
Comment 1 Robert Großkopf 2019-07-07 14:33:40 UTC
Created attachment 152621 [details]
Database for testing migration of Decimal-values

All is tested with LO 6.3.0.0 beta2 on OpenSUSE 15 64bit rpm Linux
Comment 2 MM 2019-07-07 19:55:44 UTC
Confirmed on mint 19.1 x64 with Version: 6.4.0.0.alpha0+
Build ID: 7440ca952c51a756bdc49d436abff1af25fe745b
CPU threads: 2; OS: Linux 4.15; UI render: default; VCL: gtk3; 
TinderBox: Linux-rpm_deb-x86_64@86-TDF, Branch:master, Time: 2019-06-27_16:34:10
Locale: en-US (en_US.UTF-8); UI-Language: en-US
Calc: threaded

and

Version: 6.3.0.0.alpha1+
Build ID: 77ae0abe21f672cf4b7d2e069f1d40d20edc49a7
CPU threads: 2; OS: Linux 4.15; UI render: default; VCL: gtk3; 
TinderBox: Linux-rpm_deb-x86_64@86-TDF, Branch:master, Time: 2019-05-31_15:33:33
Locale: en-US (en_US.UTF-8); UI-Language: en-US
Calc: threaded
Comment 3 Xisco Faulí 2019-07-10 12:07:15 UTC
Values are fine up to 25, if it's 26 or higher, then we get the weird value...
Comment 4 Xisco Faulí 2019-07-10 14:15:19 UTC
*** Bug 116893 has been marked as a duplicate of this bug. ***
Comment 5 Xisco Faulí 2019-07-10 17:26:54 UTC
Patch in gerrit -> https://gerrit.libreoffice.org/#/c/75375/
Comment 6 Commit Notification 2019-07-11 13:24:16 UTC
Xisco Fauli committed a patch related to this issue.
It has been pushed to "master":

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

tdf#126268: first byte is not used for the sign symbol

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 7 Xisco Faulí 2019-07-12 11:07:32 UTC
Patch in gerrit for negative values, fixed in a general way -> https://gerrit.libreoffice.org/#/c/75488/
Comment 8 Xisco Faulí 2019-07-12 11:08:01 UTC
*** Bug 126358 has been marked as a duplicate of this bug. ***
Comment 9 Commit Notification 2019-07-12 17:18:29 UTC
Xisco Fauli committed a patch related to this issue.
It has been pushed to "master":

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

tdf#126268: Add support for negative decimal

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 10 Xisco Faulí 2019-07-15 13:04:31 UTC
*** Bug 126401 has been marked as a duplicate of this bug. ***
Comment 11 Lars Jødal 2019-07-16 10:38:23 UTC
Problem with numbers are not limited to the DECIMAL type but applies also the DOUBLE type; attachment 135786 [details] can be used as test case.

The current situation seems to be:
- It is known that timestamps are not migrated correctly
- Problems with DECIMAL and DOUBLE may or may not be solved
- In general, the user should take a series of special cautions as described in https://wiki.documentfoundation.org/Documentation/HowTo/MigrateFromHSQLDB

The latest patch, tdf#126268: Add support for negative decimal, has not yet reached the available master builds, so I have not tested if that solves problems with both DECIMAL and DOUBLE, but even if it solves all problems with numbers, it does not seem targeted at timestamps.

Given this situation, why not delay depreciation of HSQL until 6.4 or whenever the migration is considerably more stable? Right now, it seems quite risky both for user data and for users' confidence in Base to have Base suggest migration from HSQL to Firebird.
Comment 12 Commit Notification 2019-07-17 08:46:21 UTC
Xisco Fauli committed a patch related to this issue.
It has been pushed to "libreoffice-6-3":

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

tdf#126268: Check for the sign value at the right place

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 13 Commit Notification 2019-07-17 21:12:41 UTC
Xisco Fauli committed a patch related to this issue.
It has been pushed to "libreoffice-6-2":

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

tdf#126268: Check for the sign value at the right place

It will be available in 6.2.6.

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 14 Alex Thurgood 2019-08-16 12:40:59 UTC
*** Bug 126924 has been marked as a duplicate of this bug. ***
Comment 15 Alex Thurgood 2019-08-16 12:44:27 UTC
(In reply to Commit Notification from comment #12)


> 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.


@Xisco : there is a report in bug 126924 of double migrations still failing in LO6304 - did your fix definitely make it into the 6304 release ?
Comment 16 Lars Jødal 2019-08-20 09:45:37 UTC
Testing with the file from comment 11 in LO 6.3.0.4, the outcome is unchanged: After migration, TIMESTAMP data are shifted one hour, and DOUBLE data are corrupted.

Version: 6.3.0.4 (x64)
Build ID: 057fc023c990d676a43019934386b85b21a9ee99
CPU tråde: 4; Styresystem: Windows 10.0; Gengiver af brugergrænseflade: GL; VCL: win; 
Lokalisering: da-DK (da_DK); Sprog for brugergrænseflade: da-DK
Calc: threaded

The bugfix from comment 12 appeared to be related to the DECIMAL type. As such, should it at all be expected to solve problems with migration of DOUBLE and TIMESTAMP?

Firebird itself may be mature and stable, but migration from HSQL to Firebird seems to be far from mature. As such, why not push migration back to being an experimental feature?
Comment 17 Xisco Faulí 2019-08-20 10:20:07 UTC
(In reply to Lars Jødal from comment #16)
> Testing with the file from comment 11 in LO 6.3.0.4, the outcome is
> unchanged: After migration, TIMESTAMP data are shifted one hour, and DOUBLE
> data are corrupted.
> 
> Version: 6.3.0.4 (x64)
> Build ID: 057fc023c990d676a43019934386b85b21a9ee99
> CPU tråde: 4; Styresystem: Windows 10.0; Gengiver af brugergrænseflade: GL;
> VCL: win; 
> Lokalisering: da-DK (da_DK); Sprog for brugergrænseflade: da-DK
> Calc: threaded
> 
> The bugfix from comment 12 appeared to be related to the DECIMAL type. As
> such, should it at all be expected to solve problems with migration of
> DOUBLE and TIMESTAMP?
> 
> Firebird itself may be mature and stable, but migration from HSQL to
> Firebird seems to be far from mature. As such, why not push migration back
> to being an experimental feature?

Hi Lars,
Check bug 119675 for the timestamp problem.
Comment 18 Brent Warkentin 2019-08-20 13:52:16 UTC
I agree with this statement... the migration process is an issue and does not seem to be market ready for the average user... certainly, as a business user, I was not happy with the amount of time I had to expend in moving a relatively small database to Firebird....


Firebird itself may be mature and stable, but migration from HSQL to Firebird seems to be far from mature. As such, why not push migration back to being an experimental feature?
Comment 19 Lars Jødal 2019-09-05 13:12:08 UTC
Created attachment 153917 [details]
Small test database with NUMERIC, DECIMAL and DOUBLE data

This sample base with embedded HSQLDB contains very simple data of type NUMERIC (5 decimals), DECIMAL and DOUBLE:

ID  MyNumeric   MyDecimal   MyDouble
1   1.00000     2.00000     3.00000
2   1.10000     2.10000     3.10000
3   1.12000     2.12000     3.12000
4   1.12300     2.12300     3.12300
5   1.12340     2.12340     3.12340
6   1.12345     2.12345     3.12345

If opened in LO 6.3.0 or 6.3.1, the system asks if the database should migrated to FireBird. Answering yes zero's the DECIMAL and corrupts the DOUBLE data:

ID  MyNumeric   MyDecimal   MyDouble
1   1.00000     0.00000     0.00000
2   1.10000     0.00000     (immensely large negative number)
3   1.12000     0.00000     (another immensely large negative number)
4   1.12300     0.00000     0.00000
5   1.12340     0.00000     (an immensely large positive number)
6   1.12345     0.00000     0.00000

Created and migrated with 6.3.1.2:

Version: 6.3.1.2 (x64)
Build ID: b79626edf0065ac373bd1df5c28bd630b4424273
CPU tråde: 4; Styresystem: Windows 10.0; Gengiver af brugergrænseflade: GL; VCL: win; 
Lokalisering: da-DK (da_DK); Sprog for brugergrænseflade: da-DK
Calc: threaded
Comment 20 Alex Thurgood 2019-09-12 08:39:27 UTC
Seems to be fixed to me in daily build :

Version: 6.4.0.0.alpha0+
Build ID: f0c832acb53326ccc9a8c1a47401fbc9e1081feb
CPU threads: 8; OS: Mac OS X 10.14.6; UI render: GL; VCL: osx; 
TinderBox: MacOSX-x86_64@49-TDF, Branch:master, Time: 2019-09-11_05:45:47
Locale: fr-FR (fr_FR.UTF-8); UI-Language: en-US
Calc: threaded


The migration process correctly copies over the Numeric, Decimal and Double values.

NUMERIC datatype is converted to NUMERIC
FLOAT datatype is converted to DOUBLE PRECISION
DOUBLE datatype is converted to DOUBLE PRECISION
Comment 21 Lars Jødal 2019-09-12 09:26:14 UTC
Great!

I have just tested 6.3.2.1 (the RC1 version of the upcoming 6.3.2). Migration is still buggy in that version, but unlike 6.3.0 and 6.3.1, migration is only suggested if experimental features are chosen. 

So from 6.3.2+ onwards, only those users who deliberately choose to use potentially unstable new features will experience the bug. And from 6.4.x the root of the problem appears to be solved.