Summary: | Different results when importing dates with JDBC than with direct connection (in all data engines) | ||
---|---|---|---|
Product: | LibreOffice | Reporter: | jcsanz |
Component: | Base | Assignee: | Not Assigned <libreoffice-bugs> |
Status: | UNCONFIRMED --- | ||
Severity: | normal | CC: | robert, serval2412 |
Priority: | medium | ||
Version: | 7.4.2.3 release | ||
Hardware: | All | ||
OS: | All | ||
See Also: | https://bugs.documentfoundation.org/show_bug.cgi?id=157909 | ||
Whiteboard: | |||
Crash report or crash signature: | Regression By: | ||
Attachments: |
Calc file with sample data
Both tables viewed in direct connection (MariaDB) Both tables viewed in JDBC connection (MariaDB) Both tables viewed in command line with mariadb prompt New test to delimite the range of erroneous dates Same error with HSQLDB embedded |
Description
jcsanz
2022-12-07 18:08:12 UTC
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. |