Bug 91814 - Problem when copying large table with floating-point values from Calc to Base (HSQLDB)
Summary: Problem when copying large table with floating-point values from Calc to Base...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
4.4.3.2 release
Hardware: x86-64 (AMD64) All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Paste Database-HSQLDB
  Show dependency treegraph
 
Reported: 2015-06-02 14:27 UTC by christian_kuhn
Modified: 2020-09-26 11:17 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
Calc document with example table (6.89 MB, application/vnd.oasis.opendocument.spreadsheet)
2015-06-02 14:27 UTC, christian_kuhn
Details

Note You need to log in before you can comment on or make changes to this bug.
Description christian_kuhn 2015-06-02 14:27:00 UTC
Created attachment 116239 [details]
Calc document with example table

Hello,

I have tried to copy a large table with floating point values from Calc to Base.
After a while, I have got the following message:

SQL-Status: S1000
Fehler-Code: 40

S1000 General error java.lang.NullPointerException in statement [INSERT INTO "Table" ( "Column 1","Column 2","Column 3","Column 4","Column 5","Column 6","Column 7","Column 8","Column 9","Column 10","Column 11","Column 12","Column 13","Column 14","Column 15","Column 16","Column 17","Column 18","Column 19","Column 20","Column 21","Column 22","Column 23","Column 24","Column 25","Column 26","Column 27","Column 28","Column 29","Column 30","Column 31","Column 32","Column 33","Column 34","Column 35","Column 36","Column 37","Column 38","Column 39","Column 40","Column 41","Column 42","Column 43","Column 44","Column 45","Column 46","Column 47","Column 48","Column 49","Column 50","Column 51","Column 52","Column 53","Column 54","Column 55","Column 56","Column 57","Column 58","Column 59","Column 60","Column 61","Column 62","Column 63","Column 64") VALUES ( ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)]


I habe attached a Calc document to reproduce this. Following steps:

1) Create a new database and connect it with the attached Calc document (table sheet as external data source).

2) Create a second database with internal HyperSQL database.

3) Drag the table of the database which is connected with the Calc document and drop it in the second database with HSQLDB. 

4) Confirm questions for column names and primary key and start the copy process.

After some seconds I have got a message, that an error occured during copy process.
Comment 1 Alex Thurgood 2015-06-02 17:00:12 UTC
On 

Version: 4.4.3.2
Build ID: 88805f81e9fe61362df02b9941de8e38a9b5fd16
Locale : fr_

OSX 10.10.3

if I choose to check the parameters of the fields before import I can cause screen corruption in the import dialog, with multiple selected column ids, two PK ID columns (whereas only one should appear) and a refusal for the wizard to go any further, even clicking "Create" cause nothing to happen, the wizard just seems to give up.

If I just click on Create in the wizard, OSX goes straight into spinning beachball mode...

Aparrently, the wizard / import / copy code can not handle an array that big


Confirming.
Comment 2 Alex Thurgood 2015-06-02 17:03:09 UTC
Eventually, I get the same error message as in the original report :
Statut SQL: S1000
Code d'erreur: 98

java.io.IOException: java.lang.NullPointerException in statement [INSERT INTO "Table" ( "ID","Column 1","Column 2","Column 3","Column 4","Column 5","Column 6","Column 7","Column 8","Column 9","Column 10","Column 11","Column 12","Column 13","Column 14","Column 15","Column 16","Column 17","Column 18","Column 19","Column 20","Column 21","Column 22","Column 23","Column 24","Column 25","Column 26","Column 27","Column 28","Column 29","Column 30","Column 31","Column 32","Column 33","Column 34","Column 35","Column 36","Column 37","Column 38","Column 39","Column 40","Column 41","Column 42","Column 43","Column 44","Column 45","Column 46","Column 47","Column 48","Column 49","Column 50","Column 51","Column 52","Column 53","Column 54","Column 55","Column 56","Column 57","Column 58","Column 59","Column 60","Column 61","Column 62","Column 63","Column 64") VALUES ( ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)]
Comment 3 Alex Thurgood 2015-06-02 17:10:13 UTC
If you decide to click on "continue" (several times) when you get the error message, you end up with a table in the new ODB file that can not display any data, with a new error message :

Statut SQL: S1000
Code d'erreur: 40

S1000 General error java.lang.NullPointerException in statement [SELECT * FROM "Table"]
Comment 4 Alex Thurgood 2015-06-02 17:25:16 UTC
In 

Version: 5.1.0.0.alpha1+
Build ID: 55431a84c264a8bcca593b9207aae0ad81d10f30
Locale : fr-FR (fr.UTF-8)

performance is noticeably worse when loading the Calc referenced ODB.

The array is 64*26432*<representation of number>, whatever that might be, but that seems to be quite a big ask of the wizard / copy code.
Comment 5 Robert Großkopf 2015-06-02 18:24:25 UTC
Base and the internal HSQLDB (and also I myself) doesn't know what kind of numbers that are. So there is created a varcharfield for every columns and reserved a length of 65535 characters for every field.
Base recognizes the fields as varchar-fields also when connecting the Calc-table directly to Base.
Have tried to import the first 1000 rows - works without any error. If I try to import the whole table at one time it doesn't work.

Don't know how the wizard works. But expected behavior would be: Every row must be copied and inserted to the new table. If there will appear an error all rows, which had been copied before, must be part of the new table. The new table hasn't to be empty.
Copying of the Calc-table to Base at once shows such an empty table.
Comment 6 christian_kuhn 2015-06-03 09:05:28 UTC
I could copy the Calc table to a SQLite database (via ODBC), but not to a Firebird database.
Comment 7 QA Administrators 2016-09-20 09:46:21 UTC Comment hidden (obsolete)
Comment 8 christian_kuhn 2016-09-28 19:49:42 UTC
I have tested it with LibreOffice 5.2.1.2 under Windows 10. The bug is still present.

Best regards,
Christian
Comment 9 QA Administrators 2018-07-07 02:39:24 UTC Comment hidden (obsolete)
Comment 10 christian_kuhn 2018-07-08 20:47:06 UTC
Hello,

the bug is still present in Base using HSQLDB:

Version: 6.0.5.2
Build-ID: 54c8cbb85f300ac59db32fe8a675ff7683cd5a16
CPU-Threads: 4; BS: Windows 6.3; UI-Render: Standard; 
Gebietsschema: de-DE (de_DE); Calc: Group.


Best regards,
Christian
Comment 11 QA Administrators 2019-07-09 02:44:29 UTC Comment hidden (obsolete)
Comment 12 christian_kuhn 2019-07-11 16:04:05 UTC
Hello,

I have downloaded the example and have tested it with

Version: 6.3.0.0.alpha1
Build-ID: 1:6.3.0~alpha1-2
CPU-Threads: 2; BS: Linux 4.19; UI-Render: Standard; VCL: kde5; 
Gebietsschema: de-DE (de_DE.UTF-8); UI-Sprache: de-DE
Calc: threaded

I have got the following results:

1) I have created a new database with internal HSQLDB (skipping question for migration to Firebird DB). After starting the copy process, the same error occurred, the bug is still present.

2) I have created a new database with the internal Firebird DB. Nothing happens, I could not start the copy process.

3) I have used LO Base with an external SQLite database connected by an ODBC driver. It seems to work, but it takes a long time (I have interrupted the copy process after several hours).

Best regards,
Christian
Comment 13 christian_kuhn 2019-07-12 23:23:15 UTC
Hello,

I have copied the table of this example to an external SQLite database successfully (via ODBC driver, duration approx. 45 minutes).

Best regards,
Christian