Bug 82372 - Base: Query builder deletes columns not visible that do not have constraint (unexpected loss of data)
Summary: Base: Query builder deletes columns not visible that do not have constraint (...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
4.3.0.4 release
Hardware: Other All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Database-Queries
  Show dependency treegraph
 
Reported: 2014-08-08 22:51 UTC by Doug
Modified: 2020-11-10 08:46 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
this is a screen shot showing what will be saved and what will be lost (6.81 KB, image/png)
2014-08-08 22:51 UTC, Doug
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Doug 2014-08-08 22:51:30 UTC
Created attachment 104322 [details]
this is a screen shot showing what will be saved and what will be lost

This bug probably is a functionality that has adverse implications not considered.  In query builder, can make column not visible by checking box on grid.  See screen shot.  Bug:  when the box is not checked and the query is saved, the column is deleted on close.  This leads to unexpected data loss.  The typical use case is the query just has a table column, so no real loss, however the query also can contain formulas; if user wants to delete the formula, can decide to do that deliberately.  Making column not visible and closing query builder leads to nonobvious deletion and data loss.

1.  Create query.
2.  Add fields.
3.  Make one or more fields not visible by unchecking box.
4.  Save and close.
5.  On reopen, columns will be gone, including any formulas.

Expected functionality:  on reopen, formulas will be available to be made visible again without re-drafting.

Consider adding a warning when columns are made not visible and the query plan will cause deletion of that information/column on close.

P.S. test document will just have missing columns.

P.P.S.:  I believe this bug may be connected to application instability, failure to close normally, but cannot reliably reproduce application instability.
Comment 1 Robert Großkopf 2014-08-12 16:18:22 UTC
You could hide columns, which shouldn't be shown.
Only columns would be saved, which should be visible or which have any function for the query, for example only sorting data or someone else.

I couldn't confirm the field is lost in the query-design, if it has a function, which would attach other fields. Have tested it with grouping by a non visible column and sum of the other column. Works as expected, shows also the non visible column in the GUI when I reopen the query for editing.

My System: OpenSUSE 12.3 64bit rpm Linux, tested with LO 4.3.0.4
Comment 2 Doug 2014-08-13 01:22:17 UTC
Yes, problem of unexpected loss of data would be solved by treating clicked "visible" box as request to "hide" column, rather than remove it from the query plan.  

And yes, bug does not impact query execution, only causes unexpected deletion of field contents being used to edit query.
Comment 3 Buovjaga 2014-11-15 10:16:45 UTC
Can't repro.

Win 7 64-bit Version: 4.4.0.0.alpha2+
Build ID: b021b5983c62e266b82d9f0c5c6d8d8900553827
TinderBox: Win-x86@39, Branch:master, Time: 2014-11-12_01:10:08
Comment 4 Robert Großkopf 2014-11-15 11:17:45 UTC
(In reply to Beluga from comment #3)
> Can't repro.
> 
1. Create a query.
2. Set one of the fields not visible.
3. Save the query.
4. Close the query.
5. Reopen the query for editing. 

If you can't reproduce the non visible field has been gone it seems there had something been change, special in Windows.

Here it has been gone, hasn't been saved together with the query. Have tested it with LO 4.4.0.0.alpha2+ OpenSUSE12.3 64bit Linux.

For me it is a ask for an enhancement. It is a special problem of Base: Every formatting in a query isn't been saved together with the *.odb-file. Only the SQL-code would be saved. And this code doesn't have any hint for non visible columns.
There should be a way to save such an information. There are some more formatting-informations, which aren't saved: Width of a column, hiding of a column, other formatting of a column ... All this is saved for tables, not for queries.
Comment 5 Buovjaga 2014-11-15 11:23:15 UTC
(In reply to robert from comment #4)
> (In reply to Beluga from comment #3)
> 1. Create a query.
> 2. Set one of the fields not visible.
> 3. Save the query.
> 4. Close the query.
> 5. Reopen the query for editing. 
> 
> If you can't reproduce the non visible field has been gone it seems there
> had something been change, special in Windows.

Yep, that was what I did and the field was in the editor with its visible checkbox unticked. Only change was it had moved to the right (switched places with one visible column).
Comment 6 raal 2014-12-29 18:38:29 UTC
Reproducible with LO 4.3.5, win7 .
I can confirm with Version: 4.5.0.0.alpha0+
Build ID: 7f476fea47f06a7f8cc961dd4f6595a524346fa5
TinderBox: Linux-rpm_deb-x86_64@46-TDF, Branch:master, Time: 2014-12-27_23:36:28

Setting as enhancement, see comment 4.
Comment 7 Alex Thurgood 2015-01-03 17:38:56 UTC
Adding self to CC if not already on
Comment 8 Alex Thurgood 2020-11-10 08:46:55 UTC
Bug still present in 

Version: 7.0.3.1
Build ID: d7547858d014d4cf69878db179d326fc3483e082
CPU threads: 8; OS: Mac OS X 10.15.7; UI render: default; VCL: osx
Locale: fr-FR (fr_FR.UTF-8); Langue IHM : fr-FR
Calc: threaded

Came across this yesterday when teaching my assistant to build a query using the UI designer and adding a field for filtering purposes that didn't need to appear in the query results (for mailmerge purposes).

Here is a practical busines example of the issue.
We need to merge data from our in-house database into a Writer document using the TextToTable function from a filtered query resultset.

You can't currently filter a query using a field that isn't present in the query definition. 

The logical apparent solution to this, when using the Query Designer UI, is therefore to include a field that will be used for filtering the data set of the returned query, and that field will be marked "invisible" in the UI. The Query Designer lets you do this and seemingly save the query, but that setting isn't actually saved in the ODB file.

Of course, one can simply add the field to be used as a filter into the Query Designer UI and make it visible, save the query design, and the resultset produced when executing the query will also include the filter field. However, when using such a query for mailmerge, you can't then exclude that column from the resultset and its content gets copied over, for example, when using the DataToText function of the mailmerge taskbar. 


I don't consider this an enhancement, rather this is a bug. 
The query designer UI offers a feature, the invisible flag, which isn't respected on saving the query design. If the feature doesn't work as designed despite seemingly allowing the user to make use of that feature, it is a bug that should be fixed.