Bug 45606 - EDITING: Default-value in GUI doesn't work in SQL
Summary: EDITING: Default-value in GUI doesn't work in SQL
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
(earliest affected)
3.3.0 release
Hardware: Other All
: medium enhancement
Assignee: Not Assigned
: 94750 (view as bug list)
Depends on:
Blocks: Database-Tables
  Show dependency treegraph
Reported: 2012-02-03 23:54 UTC by Robert Großkopf
Modified: 2021-03-02 11:14 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:

Default_value - on table in GUI, second in SQL. Try to change relationship to "set default" (4.00 KB, application/vnd.sun.xml.base)
2012-02-03 23:54 UTC, Robert Großkopf
Shows the difference between default-value in GUI and in SQL (222.92 KB, application/pdf)
2012-03-17 02:41 UTC, Robert Großkopf
Way to test default-value in the GUI. Try in relationship to set the link to default. (68.46 KB, application/pdf)
2012-03-19 08:19 UTC, Robert Großkopf

Note You need to log in before you can comment on or make changes to this bug.
Description Robert Großkopf 2012-02-03 23:54:00 UTC
Created attachment 56592 [details]
Default_value - on table in GUI, second in SQL. Try to change relationship to "set default"

When editing the relationship between two tables you could choose: On update - default. I tried this with a foreign key edited as delault in the GUI. The GUI shows this default-value. The editing of the realtionship doesn't work. A popup appears: Missing DEFAULT value ...
When trying this with a table, where the default-value is set by SQL you could change the relationship to "On update - default".
Seem the default-value in the GUI is only set as a GUI-value. It appears when editing a new row during editing. When you don't whish the default-value in GUI you have to delete the default-entry in the row first.
The default-value created in SQL isn't shown in the GUI. The default is set when saving the new row and the field is already empty. This is the right behaviour of tables.
If you want to use a GUI-default you could better set the default in form-controls.
Comment 1 Drew Jensen 2012-02-04 10:12:44 UTC
Right - this is really more of a UX defect IMO.

As I think you have found the 'Default Value' property in the table editor is used only to feed the GUI controls and is not used when creating/editing the table DDL command. 

It's also then a fact that the Default Value entered in the table editor property can only be a string value, you can enter a string representation of a numeric value of course.

The reason, again IIRC, that this property ended up on the table property editor is that is must be available to all and any GUI controls in the applicaton - a dataview (dbl click a table, the beemer displayed in Calc/Writer, form wizard use, etc). It is also the situation that Base can work with datasources that are not full blow RDBM and may not have a built in default value feature.

Setting the value in the table definition property editor where all other setting are used to when defining the actual table structure in whatever back end used is however, IMO, also the big UX problem. When using a RDBMS that does support def-vals in column definitions the users think, naturally, that this value would be used for such purpose. This comes up very often when dealing with users.

The help text displayed in the property editor really isn't a lot of help here either: [English text follows]
"Enter a default value for this field.

When you later enter data in the table, this string will be used in each new record for the field selected. It should, therefore, correspond to the cell format that needs to be entered below."

It's technically accurate but doesn't really seem to impart the information in a way that helps. That and the layout doesn't really help to get the user to read it, as if most would anyway :)

So, I think it would be worthwhile to come better wording, tho I'm not at all convinced it would lesson the confusion on the users part substantially.
Comment 2 sasha.libreoffice 2012-03-17 01:01:16 UTC
Thanks for bugreport
Sorry, but I can not understand problem for reproduce it. As I understand exist some problem with default value, different for sql and UI. I unpacked attached database, but can not find difference there.

Please, write step-by-step instruction how to reproduce this problem, used sql instructions for create table and show description of table. And in step when problem appears: what expected and what actually produced.

Thanks in advance.
Best regards
Comment 3 Robert Großkopf 2012-03-17 02:41:42 UTC
Created attachment 58586 [details]
Shows the difference between default-value in GUI and in SQL
Comment 4 sasha.libreoffice 2012-03-17 03:06:58 UTC
Thanks for explanations and screenshots. Trying to understand ...
Comment 5 sasha.libreoffice 2012-03-19 05:46:28 UTC
Sorry, but I can not find how to produce error message, shown on 1-th page of screenshot. Please, write step-by-step instruction how to produce this error.
Comment 6 Robert Großkopf 2012-03-19 08:19:48 UTC
Created attachment 58689 [details]
Way to test default-value in the GUI. Try in relationship to set the link to default.

I have tried to show it in pictures. When you link the tables in extras - relationship all seems to be OK. When you want to edit it and set the values to default (Update or delete the primary-key should set default) it does not work, because there is no default-value defined in the database.
The default value in the GUI is a shown value in the GUI and would be saved with saving the data. It is not integrated in the database. When it is integrated in the database with SQL it is not shown in the GUI, but will appear in the table after inserting a new row.
It is not only a problem in the relationship. Its also a problem with default in date- or timestamp-fields. In SQL you could set it to now(), the current date ore timestamp. In GUI you could set it only to a fix date. I do not know if there is anybody who uses a fix date as default ...

Comment 7 sasha.libreoffice 2012-03-19 08:39:12 UTC
Thanks for additional explanations. I have reproduced this.
IMHO happens this:
In attached database, when unarchived, we see xml files where saved GUI settings and binary files, where contains database itself. When we change something, using GUI, it saved inside of one of xml files (I can not find where). When changing using sql, changes saves in binary files.
Tools->Relationship window uses setting from binary file.
May be default value during GUI editing not saves somewhere at all. Functionality not implemented yet. Or it works only with some specific database driver.
Comment 8 sasha.libreoffice 2012-03-19 08:43:02 UTC
@ Lionel
Please, take look at this bugreport when will have time. One  setting from UI for table not saves to database.
Comment 9 Robert Großkopf 2013-08-12 06:59:10 UTC
I have had a look in the *.odb-file. The settings of the Default-value, when editing a table, are saved in content.xml. This is only saved for the Base-GUI, not reachable to the database.
Could be a better idea to save this by SQL in the database, when using the table-editor of the GUI. So you would be able, for example, to save a timestamp as default, which isn't possible yet.
You could get the same behavior for input values with a GUI-Default, when you use a form and set the fields of a form to a GUI-Default-value. The only difference is, that you couldn't see the GUI-default when opening a table.

I have changed the version to LO 3.3.0 (it never worked in another way in any version) and set it to "enhancement".
Comment 10 Alex Thurgood 2015-01-03 17:38:03 UTC Comment hidden (no-value)
Comment 11 Alex Thurgood 2015-10-06 13:33:14 UTC
*** Bug 94750 has been marked as a duplicate of this bug. ***
Comment 12 Robert Großkopf 2016-04-20 19:39:09 UTC
Nothings happens for this bug for a long time.
"Default" in a table isn't the same as "Default" in relationships (and databases).

The simplest way to solve this bug a little bit is to rename the "Default" in the table to "Proposal". This value will be created by the GUI, will be seen when creating a new row for the table.

"Default" in a database means: The value will be written, if there is no input (NULL) in a field while inserting new data.

Should we change the component to UI? Don't know where the decisions will be made to rename something in GUI.
Comment 13 Lionel Elie Mamane 2016-04-21 06:09:32 UTC
The fact that the "default" set in the table properties is not the database (sql) default is in my opinion confusing... When the underlying RDBMS does support default values, well, it should just be synchronised. But this means that LibreOffice cannot just consider it a string, it has to handle its proper typing.

On the other hand, I understand the need for a "LibreOffice GUI default" when the database does not support default values.

So maybe we should just "duplicate" this default property:
 * GUI Default
 * RDBMS default
Comment 14 Howard Johnson 2017-05-23 16:21:51 UTC
(In reply to Lionel Elie Mamane from comment #13)

> So maybe we should just "duplicate" this default property:
>  * GUI Default
>  * RDBMS default

I pretty much agree.  

  At the moment there are really two different defaults, but they are given one name: Default, which is confusing.

1) As a first solution step, and as Lionel suggests, there needs to be a different name for each of these two different Defaults, to help clarify that there are two of them and to allow separate descriptions of what they do, and how they can be set and used.

2) Then as an additional solution step, figuring out how to base the GUI Default on the RDBMS Default would also be a good thing I think, as it really makes no sense to me that there would exist a GUI Default, when there was already a RDBMS Default in action.  If no RDBMS default exists, then a GUI Default can emulate it.