Bug 56612 - EDITING - Base problem with boolean field that defaults to unintended value
Summary: EDITING - Base problem with boolean field that defaults to unintended value
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
3.6.2.2 release
Hardware: Other Linux (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
: 104072 (view as bug list)
Depends on:
Blocks: Database-Forms
  Show dependency treegraph
 
Reported: 2012-10-31 11:39 UTC by Emanuele Gissi
Modified: 2019-05-31 02:50 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
TestBooleanUnsetShouldBeNull (4.24 KB, application/vnd.oasis.opendocument.database)
2016-11-21 09:31 UTC, Alex Thurgood
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Emanuele Gissi 2012-10-31 11:39:56 UTC
1) Create a pgsql table in pgadmin like this:

CREATE TABLE data.book
(
  id bigserial NOT NULL,
  title character varying(64) NOT NULL,
  publisher_id bigint,
  active boolean NOT NULL DEFAULT true,
  CONSTRAINT book_pkey PRIMARY KEY (id ),
  CONSTRAINT book_publisher_id_fkey FOREIGN KEY (publisher_id)
      REFERENCES data.publisher (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT book_title_key UNIQUE (title )
)

2) Open Base and connect it to the pgsql database using the postgresql-sdbc driver

3) In Base create a form connected to the data.book table.

=> The form field "active" does not default to TRUE. The same happens in the table view.

If you create the same table from within Base, the boolean "active" field "default to TRUE" is considered, but not registered into the original db in Postgresql.
Comment 1 Lionel Elie Mamane 2013-04-17 01:19:41 UTC
Indeed, reproduced.

That's probably because the checkbox has no notion of "no value" (NULL) or disables that notion because the fiels is NOT NULL. This leads to the insert always explicitly giving "active" a value, while it should just leave it alone.
Comment 2 Robert Großkopf 2013-09-15 18:50:12 UTC
Looking for PostgreSQL-bugs I have noticed this one.
I wanted to test, but then had a look at the code:

"active boolean NOT NULL DEFAULT true,"

The default value would never be chosen which such a construction. The default of a database would only be chosen, when there is no value given in the insert. But this value should never be NULL - so it won't insert without a value.

I changed it to
"active boolean DEFAULT true,"
and set (in the form) the check-box to a tri-state-checkbox. So the checkbox would by default set NULL. Then I put data into the form and let the checkbox be NULL. It switches to "true", because I hadn't submitted a value to the table for this field.
This only works in the form, because there is no way to create a tri-state checkbox for the table ...

Default in Base-GUI isn't the same as in any database. This so called default is shown in the table or in the form, before you have done any input to the table or form. See https://bugs.freedesktop.org/show_bug.cgi?id=45606 .
Comment 3 Lionel Elie Mamane 2013-09-16 17:27:06 UTC
(In reply to comment #2)
> Looking for PostgreSQL-bugs I have noticed this one.
> I wanted to test, but then had a look at the code:

> "active boolean NOT NULL DEFAULT true,"

> The default value would never be chosen which such a construction.

That's wrong.

> The default of a database would only be chosen, when there is no value
> given in the insert.

Right. So the default is used in the above when doing something like:

INSERT INTO data.book (id, title, publisher_id) VALUES (5445, 'Tintin', 18);

> But this value should never be NULL - so it won't insert without
> a value.

Sure it will. That's what the default value is for: it will use the default value. "NOT NULL" means that the following will fail with an error message:

INSERT INTO data.book (id, title, publisher_id, active) VALUES (5445, 'Tintin', 18, NULL);


> I changed it to "active boolean DEFAULT true,"

In this case, the default (true) will *still* be used by

INSERT INTO data.book (id, title, publisher_id) VALUES (5445, 'Tintin', 18);

NULL is now allowed but has to be explicitly set, as in:

INSERT INTO data.book (id, title, publisher_id, active) VALUES (5445, 'Tintin', 18, NULL);


You misunderstood this bug. It is about LibreOffice "overriding" the database default with the default of the form control (checkbox). The submitter basically says that the default of the form control (checkbox) should, unless he has changed it, align with the database default.

There is an easy work-around: set the default of the form control to whatever you want; in this example, true.

> and set (in the form) the check-box to a tri-state-checkbox. So the checkbox
> would by default set NULL. Then I put data into the form and let the
> checkbox be NULL. It switches to "true", because I hadn't submitted a value
> to the table for this field.

That's yet another bug then... If the user set the tri-state-checkbox to NULL, the insert *must* specify a NULL.

> Default in Base-GUI isn't the same as in any database. This so called
> default is shown in the table or in the form, before you have done any input
> to the table or form.

Exactly; the reporter would wish that the default in the form control be initialised with the default from the database.
Comment 4 Robert Großkopf 2013-09-16 18:19:39 UTC
(In reply to comment #3)

> 
> > Default in Base-GUI isn't the same as in any database. This so called
> > default is shown in the table or in the form, before you have done any input
> > to the table or form.
> 
> Exactly; the reporter would wish that the default in the form control be
> initialised with the default from the database.

Then the importance should be set to enhancement. If I chose a "GUI-default" I have to delete the default and input new data, if the content should be other than default. Could be that users wish to have the old behavior back and so a property in the formcontrol has to be added like "show default [yes][no]".

Also the version should be changed to "inherited from OOo".

When I read the title I think this would be a special bug for PostgreSQL with boolean fields - but it isn't. It is also described in Bug45606, but with another intention.
Comment 5 Alex Thurgood 2015-01-03 17:39:05 UTC Comment hidden (no-value)
Comment 6 QA Administrators 2016-01-17 20:04:03 UTC Comment hidden (obsolete)
Comment 7 Alex Thurgood 2016-11-21 09:01:31 UTC
*** Bug 104072 has been marked as a duplicate of this bug. ***
Comment 8 Alex Thurgood 2016-11-21 09:27:59 UTC
Removed postgresql from title as not just limited to postgres, I can reproduce this with hsqldb. A sample database with accompanying queries is attached.

When designing the table, I deliberately left the UI settings of the "active" field as "UNKNOWN". This should leave the field as NULL when new data is entered into the table (via the UI), providing the user does not click anywhere in the tristate box field in table data edit mode. However, as can be seen from the sample db queries, this is not the case.

The query with IS EMPTY returns no results, whereas it should have returned 2 results, and the query IS NOT EMPTY returns all results.


Tested with 

Version: 5.2.3.3
Build ID: d54a8868f08a7b39642414cf2c8ef2f228f780cf
Threads CPU : 2; Version de l'OS :Mac OS X 10.12.1; UI Render : par défaut; 
Locale : fr-FR (fr_FR.UTF-8); Calc: group
Comment 9 Alex Thurgood 2016-11-21 09:31:03 UTC
Created attachment 128912 [details]
TestBooleanUnsetShouldBeNull
Comment 10 Xisco Faulí 2017-07-13 12:24:14 UTC
Setting Assignee back to default. Please assign it back to yourself if you're
still working on this issue
Comment 11 QA Administrators 2019-05-31 02:50:50 UTC
Dear Emanuele Gissi,

To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year.

There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present.

If you have time, please do the following:

Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/

If the bug is present, please leave a comment that includes the information from Help - About LibreOffice.
 
If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice.

Please DO NOT

Update the version field
Reply via email (please reply directly on the bug tracker)
Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not 
appropriate in this case)


If you want to do more to help you can test to see if your issue is a REGRESSION. To do so:
1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from http://downloadarchive.documentfoundation.org/libreoffice/old/

2. Test your bug
3. Leave a comment with your results.
4a. If the bug was present with 3.3 - set version to 'inherited from OOo';
4b. If the bug was not present in 3.3 - add 'regression' to keyword


Feel free to come ask questions or to say hello in our QA chat: https://kiwiirc.com/nextclient/irc.freenode.net/#libreoffice-qa

Thank you for helping us make LibreOffice even better for everyone!

Warm Regards,
QA Team

MassPing-UntouchedBug