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.
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.
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 .
(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.
(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.
Adding self to CC if not already on
** Please read this message in its entirety before responding ** 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 on a currently supported version of LibreOffice (5.0.4 or later) https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the version of LibreOffice and your operating system, and any changes you see in the bug behavior If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a short comment that includes your version of LibreOffice and Operating System 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) 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: http://webchat.freenode.net/?channels=libreoffice-qa Thank you for your help! -- The LibreOffice QA Team This NEW Message was generated on: 2016-01-17
*** Bug 104072 has been marked as a duplicate of this bug. ***
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
Created attachment 128912 [details] TestBooleanUnsetShouldBeNull
Setting Assignee back to default. Please assign it back to yourself if you're still working on this issue
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
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 https://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://web.libera.chat/?settings=#libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug