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.
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.
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
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.
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'
(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() )
(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.
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.
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).