Bug 120245 - LibreOffice forms refuses to insert empty field with 'not null' database constraint (but filled by trigger or default value)
Summary: LibreOffice forms refuses to insert empty field with 'not null' database cons...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
6.1.2.1 release
Hardware: x86-64 (AMD64) All
: high major
Assignee: Julien Nabet
URL:
Whiteboard: target:6.1.3 target:6.2.0
Keywords: bibisectRequest, regression
: 120289 120337 120353 120704 (view as bug list)
Depends on:
Blocks:
 
Reported: 2018-10-01 17:41 UTC by Kamil Jońca
Modified: 2018-11-16 03:37 UTC (History)
11 users (show)

See Also:
Crash report or crash signature:


Attachments
form for testing (9.87 KB, application/vnd.oasis.opendocument.database)
2018-10-01 17:42 UTC, Kamil Jońca
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Kamil Jońca 2018-10-01 17:41:19 UTC
Description:
I  use debian packaged libreoffice with libreoffice-sdbc-postgresql driver. 
I do not know where the bug is.

I have simple form based on postgresql table.
In this table there is a column

name                type                                  nullable default value
utw                 timestamp without time zone           not null now()

This column does not have its field in form.
With package:

Package: libreoffice-base                
Version: 1:6.1.2-1
Package: libreoffice-sdbc-postgresql     
Version: 1:6.1.2-1

I cannot save filled form to database, because
"required utw field has no value"

When I drop "not null" from column, and restart libreoffice - form could
be saved to database.

When I tried with version: 1:6.1.1~rc1-2 of libreoffice packages its
works as expected.

PostgreSQL 10.5 (Debian 10.5-1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.2.0-3) 8.2.0, 64-bit


Steps to Reproduce:
1. in postgres do "create table x (id serial primary key, x text, u timestamp with time zone not null default now());" 

2. confgure connection to database with sdbc-postgresql driver
3. prepare form based on this table WITHOUT u field (example will be attached)
4. fill form
5. try to save record

Actual Results:
message: Input required in field 'u'. Please enter a value.

Expected Results:
insert a record


Reproducible: Always


User Profile Reset: No



Additional Info:
Comment 1 Kamil Jońca 2018-10-01 17:42:08 UTC
Created attachment 145303 [details]
form for testing
Comment 2 Alex Thurgood 2018-10-02 06:30:54 UTC
I can reproduce this with :

MacOS 10.13.6
postgres 9.5
LibreOffice 6.1.2.1
sdbc built-in postgres driver

If I enter the data for a record in Table data edit mode, then the date is added
automatically.

However, like you, filling in a simple form including the timestamp
field and leaving the timestamp field blank always throws an error when
I try to move to either a new record, or back to a previous record. What
should happen is that the timestamp value should be generated and the
record validated (as in Table data edit mode).
Comment 3 Julien Nabet 2018-10-02 06:35:46 UTC
If Postgres specifically, I never understood how Postgres worked.
=> Can't help here.
Comment 4 Lionel Elie Mamane 2018-10-02 09:03:08 UTC
This most probably comes from the fix of bug 75341.

First thought: What we should do is check whether the column has a default value set, and in this case accept that the form gives no value. But:

1) This will not work with PostgreSQL-sdbc because the driver doesn't quite implement reading/setting default values.
2) This is still not 100% correct; there may be a more subtle mechanism that makes not giving that column a value acceptable, although it is "NOT NULL" e.g. an INSERT trigger that will put a value, but no "default value" attached to the column.

So LibreOffice shouldn't try to double-guess the database. Just throw the values entered by the user, and let the database return an error if needs be. The form designer can always require a value with a validator, with having the control's "value required" property set or with a beforeUpdate macro.

Julien, please remove the "&& && rColInfo.nNullable != ColumnValue::NO_NULLS" from the commit that fixed bug 75341 completely. Thanks.
Comment 5 Lionel Elie Mamane 2018-10-02 09:04:02 UTC
Julien, please see comment 4. Thanks.
Comment 6 Julien Nabet 2018-10-02 09:33:33 UTC
(In reply to Lionel Elie Mamane from comment #5)
> Julien, please see comment 4. Thanks.

I take it.
Comment 7 Commit Notification 2018-10-03 03:13:55 UTC
Julien Nabet committed a patch related to this issue.
It has been pushed to "libreoffice-6-1":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=8dbcd72f83e2317dd40c0fa605fd26b3f6a2616b&h=libreoffice-6-1

tdf#120245: Accept to insert empty field with 'not null' database constraint

It will be available in 6.1.3.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 8 Commit Notification 2018-10-03 05:04:12 UTC
Julien Nabet committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=adb0030d99b90fc50e536c89fc8c7a80f057c6ac

tdf#120245: Accept to insert empty field with 'not null' database constraint

It will be available in 6.2.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 9 Alex Thurgood 2018-10-04 06:40:58 UTC
*** Bug 120289 has been marked as a duplicate of this bug. ***
Comment 10 Alex Thurgood 2018-10-05 10:10:16 UTC
*** Bug 120337 has been marked as a duplicate of this bug. ***
Comment 11 Julien Nabet 2018-10-06 07:23:09 UTC
*** Bug 120353 has been marked as a duplicate of this bug. ***
Comment 12 tim 2018-10-12 15:21:59 UTC Comment hidden (off-topic)
Comment 13 Julien Nabet 2018-10-13 13:56:46 UTC Comment hidden (off-topic)
Comment 14 tim 2018-10-13 16:16:20 UTC Comment hidden (off-topic)
Comment 15 tim 2018-10-13 16:18:34 UTC Comment hidden (off-topic)
Comment 16 Xisco Faulí 2018-10-15 08:03:25 UTC
*** Bug 120353 has been marked as a duplicate of this bug. ***
Comment 17 Peter Nowee 2018-10-15 10:35:21 UTC Comment hidden (obsolete)
Comment 18 Alex Thurgood 2018-10-19 14:38:57 UTC
*** Bug 120704 has been marked as a duplicate of this bug. ***
Comment 19 Alex Thurgood 2018-10-21 08:11:02 UTC
*** Bug 120712 has been marked as a duplicate of this bug. ***
Comment 20 Alex Thurgood 2018-11-06 11:07:30 UTC
*** Bug 121188 has been marked as a duplicate of this bug. ***
Comment 21 Alex Thurgood 2018-11-06 11:11:44 UTC
@Julien : in duplicate bug 1211888, there is a report that the problem is still present. 

Did your commit fix not make into 6.1.3.1rc1 ?
Comment 22 Julien Nabet 2018-11-06 15:07:03 UTC
(In reply to Alex Thurgood from comment #21)
> @Julien : in duplicate bug 1211888, there is a report that the problem is
> still present. 
> 
> Did your commit fix not make into 6.1.3.1rc1 ?

Don't know but 6.1.3.2 has been released, see https://bugs.documentfoundation.org/show_bug.cgi?id=121188#c5