Bug 155529

Summary:	MariaDB/MySQL direct connection: Trying to insert NULL in NOT NULL fields will automatically changed in empty text or 0
Product:	LibreOffice	Reporter:	Robert Großkopf <robert>
Component:	Base	Assignee:	Not Assigned <libreoffice-bugs>
Status:	NEW ---
Severity:	normal	CC:	adamseskunas, iplaw67, lionel
Priority:	medium	Keywords:	bibisectRequest
Version:	6.3.0.4 release
Hardware:	x86-64 (AMD64)
OS:	All
See Also:	https://bugs.documentfoundation.org/show_bug.cgi?id=116253 https://bugs.documentfoundation.org/show_bug.cgi?id=157357 https://bugs.documentfoundation.org/show_bug.cgi?id=157909
Whiteboard:
Crash report or crash signature:		Regression By:
Bug Depends on:
Bug Blocks:	104790

Description Robert Großkopf 2023-05-28 08:00:07 UTC

Open a connection to MariaDB (or MySQL).
Create a table by Tools → SQL:

CREATE TABLE `tblTest` (
  `ID` int NOT NULL PRIMARY KEY,
  `Name` varchar(255) COLLATE latin1_german1_ci NOT NULL,
  `Number` int NOT NULL
);

Note: No field should be NULL!
Refresh table view.
Open this table for input data.
Type a value for "ID" and for "Name" and go to next record.
No error appears.
There will automatically added '0' for "Number".
Type a value for "ID" and for "Number" and go to next record.
No error appears.
There will automatically added '' for "Name".

Open query editor to see it is an empty text:
SELECT * FROM `tblTest` WHERE `Name` IS NULL
won't show any row.
SELECT * FROM `tblTest` WHERE `Name` = ''
will show the row where no input has been made.

Try the same with JDBC-connection. JDBC-Connection won't save any data if a field is empty, which shouldn't be NULL.

No driver should automatically add a value if no value has been set by the user. Blank text in a table is NULL by default, blank fields in a field for any other content is NULL.

If I create a form it will be possible to set the properties for an empty field. Default here is to send NULL to the database. But this won't work with internal driver for MySQL/MariaDB.

Tested here with LO 7.4.7.2 on OpenSUSE 15.4 64bit rpm Linux
Same behavior with LO 7.5.3.2.
Tested also with LO 6.3.0.4 - same behavior. This is the first version with direct connection to MariaDB integrated and possibility to input new data in this kind of table.

Comment 1 Adam664 2023-06-14 17:55:27 UTC

Confirming the bug is present when using a MariaDB direct connection in:

Version: 7.5.3.2 (X86_64)
Build ID: 50(Build:2)
CPU threads: 1; OS: Linux 6.3; UI render: default; VCL: gtk3
Locale: en-US (en_US.UTF-8); UI: en-US
Calc: threaded

and

Version: 7.7.0.0.alpha0+ (X86_64) / LibreOffice Community
Build ID: 5a2c6f4df7149f8c1f543f120fe19bd66abfc189
CPU threads: 1; OS: Linux 6.3; UI render: default; VCL: gtk3
Locale: en-US (en_US.UTF-8); UI: en-US
Calc: threaded

The bug is not present when using a JDBC connection, so this might have something to do with the MariaDB direct connection.

A JDBC connection will open the original table with blank fields, but will enforce the NOT NULL condition on newly input data.

Comment 2 Alex Thurgood 2023-06-15 09:04:49 UTC

I can reproduce this in a direct connection against a MySQL server instance with

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

Comment 3 Alex Thurgood 2023-06-15 09:09:20 UTC

I don't recall this behaviour in older versions of LO, but can not test them on macOS. This would appear to be a regression.

Comment 4 Robert Großkopf 2023-09-21 06:53:33 UTC

Tested also with a date: Will insert '0000-00-00', which is a wrong date and will be shown in Base frontend as '03.01.-1'

Comment 5 Alex Thurgood 2024-05-26 05:48:51 UTC

(In reply to Robert Großkopf from comment #4)
> Tested also with a date: Will insert '0000-00-00', which is a wrong date and
> will be shown in Base frontend as '03.01.-1'

Let me guess, is that date display the default date which is set in Calc?

This whole thing smells like a problem with the number formatter from Calc's dateutils.

In bug 157909, I wondered whether a related issue was due to the type conversion call in:

formattedcolumnvalue.cxx

lines 78-79:

FormattedColumnValue_Data()
            :m_aNullDate( DBTypeConversion::getStandardDate() )

Comment 6 Robert Großkopf 2024-05-26 06:16:05 UTC

(In reply to Alex Thurgood from comment #5)
> (In reply to Robert Großkopf from comment #4)
> > Tested also with a date: Will insert '0000-00-00', which is a wrong date and
> > will be shown in Base frontend as '03.01.-1'
> 
> Let me guess, is that date display the default date which is set in Calc?

Don't know where the formatting comes from. Just tested with LO 24.2.3.2 and the formatting changed a little bit. Now I get '02.01.1' instead. Last version with '03.01.-1' is LO 7.4.7.2 here.
Showing totally wrong content is another bug. Let's first get it working to send NULL for an empty field to the database.

Comment 7 Alex Thurgood 2024-05-26 07:17:49 UTC

Hmm, there was a previous discussion about mysql null dates in 

bug 59200

and

the discussion and patch provided by Lionel in

bug 85190#c34

I don't know whether that patch was integrated, or not, in the end.

Comment 8 Julien Nabet 2024-05-26 09:12:59 UTC

Just reading tdf#85190 comments with UNO and different behaviors between ODBC and JDBC made me understand I won't be able to help here.
=> uncc myself.
I think TDF should hire someone for Base (like they did for accessibility and RTL issues).