Description: If it is imported a Calc table with certain dates, you get different results if the import is done via direct connection or ODBC than if it is done via JDBC. The data imported via JDBC are wrong. No matter which database engine is used (tested with MariaDB, PostgreSQL and Firebird) the results are the same, always with the same errors in the JDBC import. In addition, the dates shown are different if the connection is with JDBC than with direct connection or ODBC. Steps to Reproduce: 1. Create a database on the server (or a Firebird database file) to perform the test. 2. Establish a direct connection (with the internal driver) to the created database. 3. Simultaneously open the Calc attachment with the example data. 4. Select all the data from the Calc table and drag it to the Tables area of the database to import the data. 5. In the import steps, assign the new table a meaningful name, e.g. tbl_direct 6. Assign the imported fields the following types: - ID--> INTEGER and primary key. - TEXT--> VARCHAR(10) - DATES--> DATE 7. Check that the data has been imported correctly. 8. Save the database and close the connection (close Base). ---------- 9. Open Base and establish a new connection via JDBC with the previously created database or Firebird file. 10. Follow steps 4, 5 and 6 above to import the data into a new table, called for example tbl_jdbc. 11. Check that in this case the imported data does not match the original data. 12. Open simultaneously the tables tbl_direct and tbl_jdbc and verify that with the JDBC connection the data imported previously with the direct connection, now appear as erroneous, the same as those imported with JDBC. ----------- 13. Open the database again with the direct connection and open the two tables simultaneously. Check the data. The tbl_direct table imported directly shows the correct data, but the tbl_jdbc table imported with JDBC shows it incorrectly. ----------- 14. To check the data actually saved, connect directly to the database on the command line with isql (isql-fb), mariadb or psql, depending on the database used for testing. 15. Connect to the database 16. Make select * from tbl_direc (depending on the database you may need to add quotes to the table name) and check the saved data, the data is correct (transformed to Proleptic Gregorian calendar). 17. Make select * from tbl_jdbc and check the saved data, in this case the data are incorrect with both the Julian calendar and the Proleptic Gregorian calendar. Actual Results: 1. The data imported with the JDBC connection is incorrect. 2. The data in the tables are displayed differently if a direct connection is made than if it is made through JDBC. Expected Results: Imported data should be correct regardless of the connection used to import it. 2. The data in the tables should be displayed in the same way regardless of the connection used. Reproducible: Always User Profile Reset: No Additional Info: Version: 7.4.2.3 (x64) / LibreOffice Community Build ID: 382eef1f22670f7f4118c8c2dd222ec7ad009daf CPU threads: 4; OS: Windows 10.0 Build 19045; UI render: Skia/Raster; VCL: win Locale: es-ES (es_ES); UI: es-ES Calc: CL ---------------- Version: 7.4.2.3 / LibreOffice Community Build ID: 40(Build:3) CPU threads: 2; OS: Linux 5.19; UI render: default; VCL: gtk3 Locale: es-ES (es_ES.UTF-8); UI: es-ES Ubuntu package version: 1:7.4.2~rc3-0ubuntu1 Calc: threaded
Created attachment 184037 [details] Calc file with sample data The Calc table attached as an example, has the same data in the TEXT column as in the DATES column, but in the first one with text format and in the second one with date format. This is useful to be able to see if there is a modification in the dates as a result of the import.
Created attachment 184038 [details] Both tables viewed in direct connection (MariaDB)
Created attachment 184039 [details] Both tables viewed in JDBC connection (MariaDB)
Created attachment 184040 [details] Both tables viewed in command line with mariadb prompt
Created attachment 184041 [details] New test to delimite the range of erroneous dates
Seems JDBC interpreted the start of Gregorian calendar wrong. Have a look at start of Gregorian Calendar. Friday, 1582-10-15, followed Thursday, 1582-10-04. in that year. Get the same behavior with PostgreSQL and direct connection/JDBC connection. But what should LibreOffice do to set it right? It will be a JDBC/Java-problem.
(In reply to Robert Großkopf from comment #6) > > But what should LibreOffice do to set it right? It will be a > JDBC/Java-problem. IMHO It could be a problem related with jdbc internal driver, because is related with ALL tested connections (and I suppose every one use that driver), or maybe a java related problem...
Created attachment 184045 [details] Same error with HSQLDB embedded Tested with embedded HSQLDB seems to have the same problem. I think that HSQLDB also uses the internal jdbc driver, but I'm not sure
Just for info: from: https://www.ibm.com/docs/en/db2/11.5?topic=dttmddtija-date-time-timestamp-values-that-can-cause-problems-in-jdbc-sqlj-applications " Problems with using dates in the range October 5, 1582, through October 14, 1582 The Java java.util.Date and java.util.Timestamp classes use the Julian calendar for dates before October 4, 1582, and the Gregorian calendar for dates starting with October 4, 1582. In the Gregorian calendar, October 4, 1582, is followed by October 15, 1582. If a Java program encounters a java.util.Date or java.util.Timestamp value that is between October 5, 1582, and October 14, 1582, inclusive, Java adds 10 days to that date. Therefore, a DATE or TIMESTAMP value in a Db2® table that has a value between October 5, 1582, and October 14, 1582, inclusive, is retrieved in a Java program as a java.util.Date or java.util.Timestamp value between October 15, 1582, and October 24, 1582, inclusive. A java.util.Date or java.util.Timestamp value in a Java program that is between October 5, 1582, and October 14, 1582, inclusive, is stored in a Db2 table as a DATE or TIMESTAMP value between October 15, 1582, and October 24, 1582, inclusive."
(In reply to jcsanz from comment #7) > (In reply to Robert Großkopf from comment #6) > > > > But what should LibreOffice do to set it right? It will be a > > JDBC/Java-problem. > > IMHO It could be a problem related with jdbc internal driver, because is > related with ALL tested connections (and I suppose every one use that > driver), or maybe a java related problem... Tested the same with another connection, which has nothing to do with LibreOffice. Installed DBeaver. It connects to PostgreSQL by JDBC. Shows the same behavior while reading the table I have created by the direct connection. Sets 01-10-1582 to 21-10-1582. Will have a look if I could find other hints for Java and start of Gregorian calendar. Looks for me like NOTOURBG.
(In reply to Julien Nabet from comment #9) But: The problem for this bug is - there is no date between 05-10-1582 and 14-10-1582. Inserting 04-10-1582 will be changed to 24-10-1582 by JDBC-connection. Reading 04-10-1582 will be changed to 24-10-1582 by JDBC-connection. Seems it corrects the date in a wrong way. Not a difference of 10 days but a difference of 20 days. Changing the values for date back to "normal" values will be possible in dBeaver (JDBC), but won't work in Base with JDBC-connection. 01-10-1582, shown in dBeaver after changing value there will be shown as 21-09-1582 in Base.