Bug 157909 - All unfilled date fields in form are implicitly filled 1.1.1900
Summary: All unfilled date fields in form are implicitly filled 1.1.1900
Status: RESOLVED INSUFFICIENTDATA
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
24.2.0.0 alpha0+
Hardware: x86-64 (AMD64) Windows (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2023-10-24 13:36 UTC by Petr Poruban
Modified: 2024-05-26 05:54 UTC (History)
1 user (show)

See Also:
Crash report or crash signature:


Attachments
Filled in on transfer through MariaDB connector C to Base. (1.74 MB, image/jpeg)
2023-10-27 06:54 UTC, Petr Poruban
Details
Images from MariaDB and Base (68.17 KB, image/png)
2023-10-27 11:34 UTC, Petr Poruban
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Petr Poruban 2023-10-24 13:36:27 UTC
Description:
All unfilled date fields in my form in Base are implicitly filled with the minimum date from the dates range, i.e. 1.1.1900. 
I connect to MariaDB running on windows 2012 server through MariaDB Connector C. There was communication bug. Now solved in latest daily build see detals Bug 157826. So to replicate current issue it is nessesary to use latest daily build. On older versions of Libre Office MariaDB Connector is not working properly at all.
When connecting via ODBC, the date fields remain blank when transferring to the form in Libre Office Base from the same database. 
Empty fields or fields with a null value should not be filled with the minimum date value (1.1.1900) when connected through Maria Connector C but should remain truly empty, as is the case with the ODBC connector.

Steps to Reproduce:
1. Connect from Libre Office Base to MariaDB through MariaDB conncecor C on win 2012 probably any windows. Latest daily build is needed see description.
2. All date fields with null or empty value in MariaDB shows 1.1.1900 in Base
3.

Actual Results:
It possible to delete through puting some real value of date in field and then to delete it.

Expected Results:
All date fields with null or empty value in MariaDB shows 1.1.1900 in Base


Reproducible: Always


User Profile Reset: No

Additional Info:
Please see bug Bug 157826 for context information. I was recommended there to report this issue separately.
Comment 1 Petr Poruban 2023-10-27 06:54:00 UTC
Created attachment 190445 [details]
Filled in on transfer through MariaDB connector C to Base.

Filled in on transfer through MariaDB connector C to Base. 1.1.1800 apeared only, when transfering with empty date fields. When you consequently create new rew in Base, date fields stay empty.
Comment 2 Alex Thurgood 2023-10-27 08:26:01 UTC
@Petr: are these date fields defined as "DEFAULT NULL" in the database, or otherwise defined as NULL being allowable ?

Is the default LO configuration base date stored in the database, or does it just appear in the form display ?

For example, in the table containing the dates, does the data entry grid view also show the default LO date for NULL date fields ? When you navigate from one data entry to the next in any given tuple, does the default LO date get written to the database server ?
Comment 3 Alex Thurgood 2023-10-27 08:34:26 UTC
For example, I don't reproduce the described behaviour with a form I created many years ago, containing several date fields which can be NULL, and have no default value set, in 

Version: 7.6.2.1 (AARCH64) / LibreOffice Community
Build ID: 56f7684011345957bbf33a7ee678afaf4d2ba333
CPU threads: 8; OS: Mac OS X 14.0; UI render: Skia/Raster; VCL: osx
Locale: fr-FR (fr_FR.UTF-8); UI: fr-FR
Calc: threaded

against a MySQL Server 
8.0.27 (MySQL Community Server - GPL)

using the MariaDB C native connector.
Comment 4 Alex Thurgood 2023-10-27 08:40:45 UTC
(In reply to Alex Thurgood from comment #3)
> For example, I don't reproduce the described behaviour with a form I created
> many years ago, containing several date fields which can be NULL, and have
> no default value set, in 
> 
> Version: 7.6.2.1 (AARCH64) / LibreOffice Community
> Build ID: 56f7684011345957bbf33a7ee678afaf4d2ba333
> CPU threads: 8; OS: Mac OS X 14.0; UI render: Skia/Raster; VCL: osx
> Locale: fr-FR (fr_FR.UTF-8); UI: fr-FR
> Calc: threaded
> 
> against a MySQL Server 
> 8.0.27 (MySQL Community Server - GPL)
> 
> using the MariaDB C native connector.

I also created a new form against the same database, to see whether your problem might be due to a change in the way forms are created, but there was still no difference, my NULL date fields remain empty in the form until I add a date.
Comment 5 Petr Poruban 2023-10-27 11:34:05 UTC
Created attachment 190451 [details]
Images from MariaDB and Base

0000-00-00 filed probably during transfer from Libre Office Calc...
Comment 6 Petr Poruban 2023-10-27 11:45:56 UTC
Thank you for your response. 
Perhaps it's not a bug. I don't know. I looked into the MariaDB database and Base again and noticed that null fields really display nothing. When I had created a new row in the table, as seen in photo, 1/24 and 2/24 rows, the date field remained empty, and in MariaDB, the field is marked as NULL. I converted tables from the original Base internal database to MariaDB using Calc, and it seems that empty fields were replaced with the form "0000-00-00," which generates a minimum date of January 1, 1800 in the cells. Whether this is a bug, you will have to assess. When I converted data in the same way and used the Base ODBC connector to access MariaDB, the fields remained empty. I dont chect at that time if the fields were 0000-00-00 becouse there was no probem...
Comment 7 Alex Thurgood 2023-10-27 15:14:18 UTC
(In reply to Petr Poruban from comment #6)


> converted tables from the original Base internal database to MariaDB using
> Calc, and it seems that empty fields were replaced with the form
> "0000-00-00," which generates a minimum date of January 1, 1800 in the
> cells. Whether this is a bug, you will have to assess. When I converted data
> in the same way and used the Base ODBC connector to access MariaDB, the
> fields remained empty. I dont chect at that time if the fields were
> 0000-00-00 becouse there was no probem...

Data Migration
The now infamous Calc drag'n'drop / copy-paste to Base wizard is a well known culprit for screwing things up, unfortunately.

The situation appears to have been compounded here because your NULL DATE fields seem to have been represented initially as "0000-00-00" when you copied the data into Calc. Calc's date converter unit converts "0000-00-00" to the default date from which all dates are counted, as defined under LibreOffice > Preferences > Calc > Calculations.

So, when you copy data via Calc into a database table, the wizard takes the default zero date as the date to be inserted in the mariadb table. Not pretty, and probably should be considered a bug, IMHO.

I have a vague recollection that this might already have been flagged up in another bug report, but would have to go looking for it.

DATE DISPLAY IN A CONTROL (grid view, table data entry mode, etc)
There could also be a separate bug in the display of the date in the grid control with regard to unfilled date values, leading to a default proposed display of 01/01/1900. Currently, there is already a bug report about Mariadb connections showing data from the previous tuple in a table grid view when entering a new tuple / resultset, but that displayed data isn't saved to the table.
Comment 8 Alex Thurgood 2023-10-27 15:17:11 UTC
See, for example:

https://bugs.documentfoundation.org/show_bug.cgi?id=155529#c4
Comment 9 Alex Thurgood 2023-10-27 15:59:47 UTC
Wondering if the culprit is in:

formattedcolumnvalue.cxx

lines 78-79:

FormattedColumnValue_Data()
            :m_aNullDate( DBTypeConversion::getStandardDate() )
Comment 10 QA Administrators 2024-04-25 03:16:31 UTC Comment hidden (obsolete)
Comment 11 QA Administrators 2024-05-26 03:15:10 UTC Comment hidden (obsolete)
Comment 12 Alex Thurgood 2024-05-26 05:54:42 UTC
@Pter: This is probably actually a duplicate of the underlying behavior exposed in bug 155529.

Unfortunately, without a detailed step-by-step description of how to reproduce the reported problem here, you bug report has been closed.

If you could provide such a detailed description, the bug report could be reopened as Unconfirmed, for further testing.