Bug 104375 - Table Default value for Date entered via UI cannot be modified using SQL
Summary: Table Default value for Date entered via UI cannot be modified using SQL
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
5.2.3.3 release
Hardware: x86-64 (AMD64) Linux (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Database-Tables
  Show dependency tree / graph
 
Reported: 2016-12-03 15:14 UTC by Stang
Modified: 2022-12-31 03:19 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:

Attachments
Sample DB (3.89 KB, application/vnd.oasis.opendocument.database)
2016-12-03 15:17 UTC, Stang 		Details
View All Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.
Description Stang 2016-12-03 15:14:46 UTC 
Description:
Tested with:

Version: 5.2.3.3
Build ID: d54a8868f08a7b39642414cf2c8ef2f228f780cf
CPU Threads: 4; OS Version: Linux 4.4; UI Render: default; 
Locale: en-US (en_US.UTF-8); Calc: group

Also:

Version: 5.4.0.0.alpha0+
Build ID: 150afc29c951d5fc9c40ff8a72f5178c32383f8c
CPU Threads: 4; OS Version: Linux 4.4; UI Render: default; VCL: gtk2; 
TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:master, Time: 2016-12-03_01:02:49
Locale: en-US (en_US.UTF-8); Calc: group

This problem deals with default "DATE" field values in Base when creating tables.  Sample attached for reference.

It is also understood that a default value for a table created in Base is stored in the "content.xml" file of the .odb and not in the database.


Steps to Reproduce:

There are three tables in the sample.  Each has the same fields: ID(Integer - auto increment), NAME(text) and TESTDATE(Date).

In TABLE1 the default date was set to 12/03/16.  Upon saving the table and opening the table in Edit mode this date is now 12/05/16.  If you open the table for data entry, the default date is set to 01/02/12.

In TABLE2 the default date was set to 2016-12-03.  Upon saving the table and opening the table in Edit mode this date is now 12/05/16.  If you open the table for data entry, the default date is set to 01/02/12.

In TABLE3 the default date was left empty.



Actual Results:  
If an Alter statement is used to change the date field default (where x = table number):

	Alter Table "TESTx" Alter Column "TESTDATE" set default '2016-12-31'

changes the default value in the DB (view with TABLExSCHEMA Query) but the default is still set in "content.xml" and that overrides the default in the database.  This can be seen in TABLE2.

In TABLE3 the default was only set in the database and it works OK with the exception that the only way to see the default value is to display the schema.


Expected Results:
What should happen, the default should not be stored in "content.xml" (doesn't work correctly anyway) but rather in the DB and the default value in table->edit should display the default DB value.



Reproducible: Always

User Profile Reset: No

Additional Info:
The temporary solutions for correct default date are as follows:

	Don't set in creation but use "ALTER" statement
	If already created in embedded DB, delete default & set with "ALTER"
	If already created in split DB, delete default in "content.xml" & set with "ALTER" or delete field and re-establish (neither choice good)



User-Agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/54.0.2840.100 Safari/537.36
Comment 1 Stang 2016-12-03 15:17:49 UTC 
Created attachment 129281 [details]
Sample DB
Comment 2 Robert Großkopf 2016-12-03 16:44:34 UTC 
Please don't report more tan one buggy behavior for each bug you detecet. Nobody could be able to commit a patch for this bug-description.

See bug102945 for example for a date-bug in the tables.

There are different "default"-values. In the GUI you only set the standard for the GUI. It is misleading to name this "default". This could be reported as a bug. This standard-value writes the value into every row before you put any data into this row.

The default-value of the database will write the default-value into the row, if the field is left empty in the GUI. It's the value instead of NULL. The default-value of the database is also needed for relationships. So it could be a good enhancement to add a field for SQL-default-values into the dialog for creating a table.

Have a look, if you will report one bug-description for one buggy behavior. Please look for other date-related bugs in Base-tables before.
Comment 3 Stang 2016-12-04 15:14:29 UTC 
First, I'll admit that maybe the title of Bug should be "Table Default value for Date cannot be modified using SQL".

Second, this is only one bug being reported as can be seen in "Expected Results".  "Steps to Reproduce" mentions the creation of an incorrect date but that is not what is being requested as a fix but rather the process to get to the problem.

It is not misleading to use the word "Default" since this is the word used in the field description that is being dealt with.  What else can it be called?

The bottom line here is that the "Default value" of a table "Date" type field cannot be changed using SQL as other parts of the field can such as format, name, type etc.  Additionally, if this is a split database, there is no way for a user to "clear" this field or change it without modifying the "content.xml" file (except to actually delete the field and create a new one which is not practical).

Finally, this is absolutely no reason to have TWO default values as you suggest.  There is a default set or there is not. What could be the reasoning for two different defaults?
Comment 4 Stang 2016-12-04 17:43:50 UTC 
I should also point out, in the sample provided, if you edit table "TEST1" and look at field "TESTDATE" there is a default date of "12/05/16" (the actual value doesn't matter, just that there is something there).  With that, run the provided query "TABLE1SCHEMA" and you will see there is no default date set.

Of what value is there to create a table when what is specified is not done?  If this table is accessed outside of LO there is no default date!  Nothing to do with GUI, just the table definition itself.

It also creates questions as to what else is not being done in Base when adding fields or tables.
Comment 5 Robert Großkopf 2016-12-04 19:11:06 UTC 
This is the same behavior for every column in the table, not only for date-columns:

You could create a value, which is written in the table through the GUI. This is the "Default" of the GUI. It is the same behavior in a form: The fields of a form have such a default-value. Has nothing to do with SQL.

You could create a value through SQL. This is the default-value of the database. This is the default other users of a server-database will work with, too.

If the field of the table has no content and nothing has been changed in this field, there will be only an INSERT for the fields with content. If you delete the GUI-default, there will be submitted NULL to the database for this field. So it is impossible (for input in the table directly or with a GUI-default in a form) to get the default-value for the database. You could say: This is the first bug.

INSERT INTO "TEST2" ("NAME") VALUES ('Tim') 

would set the default for the column "TESTDATE". The SQL-default will be only set by a database, if the field isn't part of the INSERT-command.

What you wish: Set the default of SQL to the default of GUI. The values will be shown before inserting into the table. The SQL-default of the table will never been executed any more for fields, which are shown in the GUI. You could say this is the second bug.

... and the third bug is: The values for GUI-default of the date seem to be totally wrong between what is saved in the table-definition and shown in the table afterwords.
Comment 6 Stang 2016-12-04 22:51:30 UTC 
OK.  I've read your comments and can accept what you state about GUI vs a table field default.

To me, your "first bug" and "second bug" are one and the same.  I don't see any difference between the field default in the table and the GUI default.

As you previously mentioned, bug 104375 covers the "third bug". Actually that is partially where this whole thing started.  Of note, TABLE3 (default set by ALTER only) produces correct dates when entering records in GUI; it just doesn't display them until tabbing through the field.  So it seems this may also take care of "third bug" if GUI & field default were the same.

One other point.  Your last statement:

"The values for GUI-default of the date seem to be totally wrong between what is saved in the table-definition and shown in the table afterwords."

is somewhat the basis for this bug.  The referred to table-definition here is in the "content.xml" file.  This is certainly a point of confusion since it has absolutely nothing to do with the table in the database.  Just a point.

I can understand why this is not a simple change, nor is the list of bugs short but this is a bug and not an enhancement.  Fortunately there are ways to work around this for the time being as mentioned in "Additional Info".

Thank you for your patience and time thus far.

What if anything else is needed?
Comment 7 Stang 2016-12-04 22:54:42 UTC 
Sorry - bug mentioned in last comment should be bug 102945.
Comment 8 Alex Thurgood 2016-12-06 09:00:03 UTC 
With attached ODB test file:


CONFIRMING Table1 observations

CONFIRMING Table2 observations

CONFIRMING Table3 observations

The observations made with regard to Table1 are a duplicate
Comment 9 Alex Thurgood 2016-12-06 09:00:53 UTC 
Changed title to better reflect situation

Confirming 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 10 Alex Thurgood 2016-12-06 09:05:24 UTC 
It is clearly a bug to present to the user via the UI something which it thinks will provide a solution to SQL statements, and then not only not respect that choice, but in addition display an incorrect value instead of the one entered by the user in the UI. 

Either the UI has a role to fill in helping out the user, or it doesn't. If it does, IMHO, then it should work accordingly.
Comment 11 Alex Thurgood 2016-12-06 09:06:27 UTC 
Similarly, I would expect to see the default value that is coded via SQL to be displayed in the corresponding UI field of the the Edit Table UI.
Comment 12 Alex Thurgood 2016-12-06 09:07:29 UTC 
Somewhere in the back of my head, I have a feeling that this has been discussed in a previous bug report...
Comment 13 Robert Großkopf 2016-12-06 19:24:42 UTC 
(In reply to Alex Thurgood from comment #12)
> Somewhere in the back of my head, I have a feeling that this has been
> discussed in a previous bug report...

See bug 45606.
Comment 14 QA Administrators 2018-11-03 03:50:50 UTC Comment hidden (obsolete)
Comment 15 QA Administrators 2020-11-03 04:00:48 UTC Comment hidden (obsolete)
Comment 16 herman.viaene 2020-12-30 09:16:41 UTC 
I just want to state that this bug is still present in LO 6.7.4 with embedded Firebird 3.0.
Comment 17 QA Administrators 2022-12-31 03:19:30 UTC 
Dear Stang,

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