Bug 68153 - EDITING: Query input in foreignkey-field impossible, when two tables in the query.
Summary: EDITING: Query input in foreignkey-field impossible, when two tables in the q...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
3.3.0 release
Hardware: Other All
: medium enhancement
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Database-Queries
  Show dependency treegraph
 
Reported: 2013-08-15 15:29 UTC by Robert Großkopf
Modified: 2019-04-06 09:44 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments
Database with query - try the input: works in all fields except "town_ID" (4.26 KB, application/vnd.oasis.opendocument.base)
2013-08-15 15:29 UTC, Robert Großkopf
Details
Error appears, when input a new foreignkey (25.33 KB, image/png)
2013-08-15 15:30 UTC, Robert Großkopf
Details
Two queries in the database - query for two tables doesn't allow to change relation (4.53 KB, application/vnd.sun.xml.base)
2013-08-17 17:34 UTC, Robert Großkopf
Details
HSQLDB example demonstrating this issue: 2 tables & query w/ uneditable FK (4.10 KB, application/vnd.sun.xml.base)
2017-06-01 17:15 UTC, Howard Johnson
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Robert Großkopf 2013-08-15 15:29:00 UTC
Created attachment 84102 [details]
Database with query - try the input: works in all fields except "town_ID"

In a database exist two tables. One Table for "name" and one for "town". Table "name" has a forteignkey for table "town.
Table "name": "ID, "name", "town_ID"
Table "town": "ID", "town"

You could link this tables with a query:
SELECT "name"."ID", "name"."name", "name"."town_ID", "town"."ID" AS "t_ID", "town"."town" FROM "name", "town" WHERE "name"."town_ID" = "town"."ID"

The table could be edited. When you change "name", it is possible. When you change "town", it is possible. The only value, which couldn't be changed, is "town_ID". The GUI shows the changing but the SQL-code seems to be broken.

"Error updating the current record" appears.
Don't know, why it should be possible to change all values except those, which link the two tables to each other.

This had never worked in LO the right way.
Comment 1 Robert Großkopf 2013-08-15 15:30:28 UTC
Created attachment 84103 [details]
Error appears, when input a new foreignkey
Comment 2 Terrence Enger 2013-08-17 17:10:57 UTC
I can reproduce the error message with LibreOffice 4.1.0.3 64-bit, but
I am far from convinced that we should expect the data grid to accept
updates that implicitly change the value shown in other cells.  So, I
am leaving status UNCONFIRMED in the hope of collecting other opinions.

That said, it would be good for Base, when it cannot accept an update,
either to disallow keystrokes in the cell (as it actually does under
some circumstances) or to provide another level of detail in the error
message (as it actually does under some circumstances).

When LibreOffice does accept a change to a value which appears in
mutiple cells, sometimes those cells show the new value as soon as the
one row is updated, and sometimes it takes an <F5> to show the new
value.  Something here seems wrong to me, but I cannot decide whether
the wrongness is the delay before displaying the changed value or in
the fact that the data grid accepts the entry of a new value at all.
Comment 3 Robert Großkopf 2013-08-17 17:34:32 UTC
Created attachment 84180 [details]
Two queries in the database - query for two tables doesn't allow to change relation

The query works in the relation between 2 tables, when you add the content of the second table by a correlated subquery "name_town_correlated_subquery". Here is the input in the field for the foreign-key possible.

I don't think it should be a problem if it is allowed to change the relation. 
A bigger problem for 'normal users' could be, what is already fact when connecting of two tables in query "name_town": You could change the content of the second table and could so affect more than one row by changing a value. Example: 2 "names" are connected to "town" 'There'. I can change the content of "town" in the query "name_town" for one "name" to 'Never'. It would be changed for the other, too.
Comment 4 Dominik 2014-07-05 08:03:35 UTC
the thing is that editing / inserting into a select (view) result is very problematic at all (for the database behind base).

http://de.wikipedia.org/wiki/Sicht_%28Datenbank%29#Updates

so i'd say that the real bug here is, that it seems like you could change the data, and the UI should be improved to destroy that hope earlier.
Comment 5 Dominik 2014-07-05 08:10:20 UTC
pay special attention to:

...
In SQL-92 ist nur die Änderung reiner Selektionssichten erlaubt. ...  In SQL-99 wurde die Menge an änderbaren Sichten deutlich erweitert, bleibt aber immer noch hinter der theoretisch möglichen zurück.
Comment 6 Alex Thurgood 2014-10-19 14:44:28 UTC
Confirming behaviour, setting as rfe - whatever that might be, i.e preventing updates completely via the grid, and displaying appropriate message, or allowing this to work properly in line with user expectations
Comment 7 Alex Thurgood 2014-10-19 14:47:50 UTC
The problem, as we all recognise it, is that the user expects to be able to edit the data in the grid view because that is what happens in corresponding db UIs that have such a grid, e.g. Access, Lotus Approach, Filemaker Pro, etc
Comment 8 Alex Thurgood 2015-01-03 17:39:52 UTC Comment hidden (no-value)
Comment 9 Howard Johnson 2017-06-01 04:40:04 UTC
Hi guys,

Thanks for all of your work.

I think development of this missing Base functionality needs to get bumped up to a much higher priority.

What were talking about here is the ability to edit many-to-one relations in queries, and in forms based on those queries.

This is a fundamental, relational, functionality, that is sorely missing in Base.

Not only does the user expect to be able to update all fields, including a foreign key in a query, but a user NEEDS to be able to update a foreign key in a query.

Editable queries that can only operate on single unrelated tables is ancient, not just in my opinion, but in actual fact: Access has had this important many-to-one query functionality working for 20 years that I know of.  


--------------------------------------------------------------------------
What I observed:

* I have confirmed this lack of functionality in both HSQLDB 1.8 embedded and when connecting to MariaDB with MySQL/JDBC.

* I also re-confirmed that Access 2003 provides this functionality correctly on the exact same MariaDB.  Further, as I recall Access 97 also got this right and that's now 20 years old.  If my memory is correct I also think Access 95 provided this missing Base functionality.

* Because this functionality exists in Access when ODBC/MySQL linked (connected) to MariaDB, but not in Base when MySQL/JDBC connected, I think this is not a SQL lack of functionality, but rather an underlying Base lack of functionality.

* The necessary Base user interface is already fully in place.  

* It's the underlying Base query update functionality that's lacking.


--------------------------------------------------------------------------
Importance of this missing functionality:

* I think this lacking functionality keeps Base Queries and Forms from 
  being fully relational, as follows:

   + You can view a query of many variously related tables. 
       ==> Query viewing is fully relational.

   - You can insert, but not edit foreign keys in queries of more than
     one table.
       ==> Query editing is not yet fully relational.
             Many-to-one relationships ("is" relationships) are the 
             relationships I use most of the time in my databases.

   - You can also not edit foreign keys in a Form based on a query of more 
     than one table.
       ==> Forms aren't yet fully relational.  

   Note: Forms can use a subform to edit related one-to-many tables (has), but 
   this is not equivalent to many-to-one (is) functionality.)


* So in Base how can you currently edit many-to-one foreign keys???

   - You can edit a foreign key in a query based on a single table, and in 
     this case you can only enter the key (often an integer) which represents a
     record.  You also can't select the record key from it's content.  
       ==> primitive at best

   - You can edit a foreign key in a Form that is based on only a single 
     table, but this again is not fully relational.
       ==> ok only for the most elementary data



--------------------------------------------------------------------------
EXAMPLE APPLICATION THAT NEEDS THIS FUNCTIONALITY:

I am trying to port my simple shopping list from Access to Base.  The data for both applications is in a MariaDB MySQL server so I can use it from both applications at the same time.

There is a main table of items.  It includes a number of foreign keys to sub-tables to enumerate things like item category, frequency of purchase, who the item is for, etc.

I have two main editors for this shopping list:

1) The first editor allows individual records to be looked up and edited.  This works fine in Base and Access.

2) The second editor shows all records in a Form Table Control so I can view records side by side and make any needed changes, like for example selecting a different category for an item.  For the user this Table Control needs to be sorted by a variety of fields, so it's based on a query of two related tables, Items and sub-table ItemFrequencyOfPurchase.  

  For example I want the already checked (or selected) items to appear first, then I want items which I frequently purchase to appear next, and finally I want the list in the Table Control further sorted by item name.  (This makes it a little easier to look over the most important of over 400 items on the list before we head for town.)

This works perfectly in Access, but it does not work in Base.  In Base the records in the Table Control based on the query become un-editable.

But to get this to work the query needs to be editable.  Only a query without any foreign keys is editable.  This won't sort correctly on the form.


--------------------------------------------------------------------
My hope is that someone will take on this challenge of making Base fully relational, rather than just partially relational.

grateful
Comment 10 Lionel Elie Mamane 2017-06-01 10:42:00 UTC
(In reply to Howard Johnson from comment #9)

> I think development of this missing Base functionality needs to get bumped
> up to a much higher priority.

You seem to be under the impression that someone sets global priorities for Base (or LibreOffice more generally) development. There is not. The development that happens is the one that people do, according to their own decisions.

In general, if you think something should happen, do it or have it done by someone (e.g. by hiring that someone).

> What were talking about here is the ability to edit many-to-one relations in
> queries, and in forms based on those queries.

Not quite. I use several related data forms/subforms (within the same document window) to edit stuff based on JOIN. Don't perform the JOIN in the query itself, but through the "link master fields" and "link slave fields" of data forms & subforms.

> This is a fundamental, relational, functionality, that is sorely missing in
> Base.

Base is not a database engine. If the database engine that you use (such as embedded HSQLDB 1.8, MySQL, PostgreSQL, ...) cannot do something, Base will not do it for you.

> Not only does the user expect to be able to update all fields, including a
> foreign key in a query, but a user NEEDS to be able to update a foreign key
> in a query.

I'm curious what meaning you will ascribe to editing the foreign key.

In the example of comment 0, if both town_ID and town are modified, what happens?

E.g. the tables contents is:

name:
 5, Johns, 3
town:
 3, Fil
 7, Lon

So the query result is:

 5, Johns, 3, 3, Fil

Now, the user changes town_ID to 7 and town to 'Ess'.

What do you expect the tables to contain?

name:
 5, Johns, 7
town:
 3, Fil
 7, Ess

or

name:
 5, Johns, 7
town:
 3, Ess
 7, Lon


???

> Editable queries that can only operate on single unrelated tables is
> ancient, not just in my opinion, but in actual fact: Access has had this
> important many-to-one query functionality working for 20 years that I know
> of.

No, it doesn't. In a query, the join fields are not editable. I think you and I understand comment 0 differently. Very exactly, Access will not let you update neither "town_ID" nor "t_ID" in the query of comment 0.

> * I also re-confirmed that Access 2003 provides this functionality correctly
> on the exact same MariaDB.

See above.

> * Because this functionality exists in Access when ODBC/MySQL linked
> (connected) to MariaDB,

See above.

>    Note: Forms can use a subform to edit related one-to-many tables (has),
> but this is not equivalent to many-to-one (is) functionality.

How is it not? I use it both with one-to-one and in one-to-many relationships.
Comment 11 Lionel Elie Mamane 2017-06-01 10:43:16 UTC
(In reply to robert from comment #0)

> "Error updating the current record" appears.
> Don't know, why it should be possible to change all values except those,
> which link the two tables to each other.

As explained in my previous comment (by example in the "I'm curious ..." part), because the semantics of that are ill-defined.
Comment 12 Lionel Elie Mamane 2017-06-01 10:50:47 UTC
(In reply to Terrence Enger from comment #2)

> That said, it would be good for Base, when it cannot accept an update,
> either to disallow keystrokes in the cell (as it actually does under
> some circumstances)

Yes. In my testing (the default table control used when double-clicking on the query), it actually disallows keystrokes in t_ID, but allows them in town_ID. So the mechanism to not allow changes is definitely there, it is "just" used only on the side of the relation where the field is the primary key. It needs to be locked on both sides. Happy to take/review patches.
Comment 13 Lionel Elie Mamane 2017-06-01 11:06:42 UTC
(In reply to robert from comment #3)

> A bigger problem for 'normal users' could be, what is already fact when
> connecting of two tables in query "name_town": You could change the content
> of the second table and could so affect more than one row by changing a
> value. Example: 2 "names" are connected to "town" 'There'. I can change the
> content of "town" in the query "name_town" for one "name" to 'Never'. It
> would be changed for the other, too.

Yes, in my experience users will totally not grok that, which is why I *never* show them such a join in an editable way.
Comment 14 Howard Johnson 2017-06-01 17:15:59 UTC
Created attachment 133789 [details]
HSQLDB example demonstrating this issue: 2 tables & query w/ uneditable FK

Open the query.  Try to change the *FK field in an existing record from 0 to 1 or 1 to 0.  What should happen is that it should be changed.  What happens is you get an error message: "Error updating the current record".
Comment 15 Howard Johnson 2017-06-01 18:09:54 UTC
(In reply to Lionel Elie Mamane from comment #10)

> I'm curious what meaning you will ascribe to editing the foreign key.

I uploaded a new very simple demonstration of this issue.  Open the query.  Try to edit the FK field and change 0 to 1, or 1 to 0 and you see an error message.
Comment 16 Howard Johnson 2017-06-01 18:16:52 UTC
(In reply to Lionel Elie Mamane from comment #10)

Lionel, I'm sorry.  

It's true, there's a lot that I don't know about this code development process.  I appreciate you taking a look at this and kicking this around with me.  I don't personally have the skills to fix this, at least not yet.  

This is not the only bug I have noticed in this area, so if I can ever get into this stuff I hope to fix a number of things.  I've got another related bug to soon submit when I get the chance which will help deal with many-to-many relationships.

I suppose before anything like that can be done code-wise by me or anyone, it's my understanding that first the bugs need to be clearly fleshed out here in bugzilla, because what is changed is not up to me or any one person I don't think.  

So that's my goal today:  further document this issue and ask for any other feedback.  I do hope to move up to fixing code, and am working on getting there, but I'm not there yet.  For now I can only notice, investigate, and report it as I am capable.  Perhaps someday I can come back and fix this issue.  I sure hope to.
Comment 17 Howard Johnson 2017-06-01 18:19:17 UTC
> > What were talking about here is the ability to edit many-to-one relations in
> > queries, and in forms based on those queries.
> 
> Not quite. I use several related data forms/subforms (within the same
> document window) to edit stuff based on JOIN. Don't perform the JOIN in the
> query itself, but through the "link master fields" and "link slave fields"
> of data forms & subforms.

As I pointed out before, forms/subforms deal with "has" relationships (main table has children; where FKs are in the children and point to the main table), not "is" relationships (where FKs are in the parents and points to enumerations).

Also to be complete, you can use list-boxes in forms to edit "is" relationships, but because the underlying data is not what's getting edited that is another issue.



> ... If the database engine that you use (such as
> embedded HSQLDB 1.8, MySQL, PostgreSQL, ...) cannot do something, Base will
> not do it for you.

As I said before:  MySQL can do this, and I can prove it because Access, when connected to MySQL, to the very same database and tables, can do this.  Again to be clear I'm testing the exact same database and tables in Access as in LO Base.  Access edits the FK, Base does not.
Comment 18 Lionel Elie Mamane 2017-06-01 18:27:59 UTC
(In reply to Howard Johnson from comment #15)
> (In reply to Lionel Elie Mamane from comment #10)
> 
> > I'm curious what meaning you will ascribe to editing the foreign key.
> 
> I uploaded a new very simple demonstration of this issue.  Open the query. 
> Try to edit the FK field and change 0 to 1, or 1 to 0 and you see an error
> message.

That was not my question. Read my question again.
Comment 19 Lionel Elie Mamane 2017-06-01 19:38:16 UTC
(In reply to Howard Johnson from comment #17)
>>> What were talking about here is the ability to edit many-to-one relations in
>>> queries, and in forms based on those queries.

> As I pointed out before, forms/subforms deal with "has" relationships (main
> table has children; where FKs are in the children and point to the main
> table), not "is" relationships (where FKs are in the parents and points to
> enumerations).

I fail to see why subforms wouldn't work in both directions.

> As I said before:  MySQL can do this, and I can prove it because Access,
> when connected to MySQL, to the very same database and tables, can do this.

In my experience, Access doesn't do it. I attach an Access database. In it, Access refuses to change the value of the foreign key field.

Oh, I see now. Access accepts to do it when you change ONLY the FK field AND NO OTHER FIELD on the "many" side in a one-to-many join. It then immediately refreshes all fields that come from the "many" side, to avoid the ambiguity I described.

OK, I understand now what you mean.

Basically, Access does this through its Jet database engine, not through MySQL. So we are back to "Base is not its own database engine". I understand the cool feature you are used to on the Access side. It might be doable within the Base design. Valid feature request.

P.S.: other consequences of "Access has its own database engine" (which is able to "connect" to other engines through ODBC):

 * you write your queries in the SQL dialect understood by Access (Jet),
   *not* the one understood by e.g. MySQL (unless you do an "SQL direct"
   query but then you cannot edit data).
 * you can do a join between tables that come from *different* DBMS.

Whereas Base just sends the SQL whatever DBMS you are using. However, note that in a writer document having forms and subforms (alas not in a Base form document, that's a feature request of mine...) the different (sub)forms can pull data from different DBMS.
Comment 20 Howard Johnson 2017-06-02 14:26:13 UTC
(In reply to Lionel Elie Mamane from comment #18)
 
> That was not my question. Read my question again.

Ok, I see you ask a very excellent question!, one that will lead you to the solution in fact...

The answer is a riddle: neither, and both, but timing is everything.


When the user moves the focus after editing a FK (individual field) not just up or down, but also left or right, the query needs to update the pointed to record and re-display the line, thus updating the other fields as necessary.  So then if another non-FK field or several are edited, the edits occur in the correct sequence (timing).

>If edit FK-field
>Then update query before moving to next field

In other words, the query GUI is not a line editor except in the simple case of a simple query of only one table.  

In a query of a related records the query becomes simple fields and link fields, and each need to be treated differently.  Editing link fields immediately causes certain parts of the query to update.  Editing non-link fields only update the underlying record when up/down record motion occurs.


I didn't think of this myself; I looked at how Access did it.  I had been using it so long I didn't even know how it worked till yesterday, I just knew that it worked.
Comment 21 Howard Johnson 2017-06-02 14:46:27 UTC
(In reply to Lionel Elie Mamane from comment #19)
>> 
> Basically, Access does this through its Jet database engine, not through
> MySQL. So we are back to "Base is not its own database engine". I understand
> the cool feature you are used to on the Access side. It might be doable
> within the Base design. Valid feature request.

I'm using Access, but not JET.  I'm linking to MySQL from Access.

And there is no reason Base can't do the same thing.

The SQL is not one line of SQL per line in the query.

It's one line of SQL when a FK is updated, which immediately updates the query.

This is true when a form uses an underling query too.  A FK field change forces a re-query.


I know, it took me a few minutes to get my head around this too.  I had been using base too long now, and I was stuck in the one line = one SQL statement paradigm.

But they I realized that the query was only a GUI editor.  It could do much more than send SQL only when the entire line needed writing.

I'll be away for 2 days with my partner.  Will touch base when I get back.  

Thanks very much for this interaction!

:-)
Comment 22 Robert Großkopf 2017-06-02 15:04:37 UTC
And this is the bug I reported:

You couldn't change the foreign-key-field, but you could change the content of the table, which is linked to the first table by this foreign-key.

I would prefer:
You could change the foreign-key-field, but you could not change the content of the table, which is linked to the first table by this foreign-key. 
This would protect the user to unconscious change the content of many rows of the first table by changing the content of one field of the second table linked to the first by the foreign-key.
Comment 23 Howard Johnson 2017-06-04 15:34:11 UTC
(In reply to robert from comment #22)
> And this is the bug I reported:
> 
> You couldn't change the foreign-key-field, but you could change the content
> of the table, which is linked to the first table by this foreign-key.
> 
> I would prefer:
> You could change the foreign-key-field, but you could not change the content
> of the table, which is linked to the first table by this foreign-key. 
> This would protect the user to unconscious change the content of many rows
> of the first table by changing the content of one field of the second table
> linked to the first by the foreign-key.

Robert, I don't think you see what I'm trying to convey.  Let me try to explain it again.

First, there is no longer any reason that any field in a query should not be editable.  Consider this simple set of tables:

    +-----------+
    | Table1 ID |
    +-----------+        +-----------+
    | Table2 FK | -----> | Table2 ID |
    +-----------+        +-----------+
    | Name      |        | Title     |
    +-----------+        +-----------+
    | ...                | ...

    SELECT   Table1 ID, Table 2 FK, Name,   Table2 ID, Title 
    FROM Table1 Right Join Table2 ...

    +-----------+-----------+------+-----------+-------+
    | Table1 ID | Table2 FK | Name | Table2 ID | Title |
    +-----------+-----------+------+-----------+-------+
    |         0 |         0 | Mike |         0 | Clerk |
    +-----------+-----------+------+-----------+-------+

In order:
1) If you edit Table1 ID and move to up or down   
then Table 1 is updated.
   If you instead move right nothing is done.

2) If you edit Table2 FK from 0 to 1, and then move the focus to another cell,
   any prior changes to Table2 ID are pushed to the database, and then
   Name, Table2 ID and Title get updated from the new Table2 ID. 

3) If you edit Name nothing is done until

  If you edit
Comment 24 Howard Johnson 2017-06-04 15:36:58 UTC
(I accidentally moved my hand and hit save changes, but before I was not done typing.  First time that's happened.  Sorry.)
Comment 25 Howard Johnson 2017-06-04 16:10:55 UTC
(In reply to robert from comment #22)
> And this is the bug I reported:
> 
> You couldn't change the foreign-key-field, but you could change the content
> of the table, which is linked to the first table by this foreign-key.
> 
> I would prefer:
> You could change the foreign-key-field, but you could not change the content
> of the table, which is linked to the first table by this foreign-key. 
> This would protect the user to unconscious change the content of many rows
> of the first table by changing the content of one field of the second table
> linked to the first by the foreign-key.

Robert, perhaps I haven't described this adequately.  Let me try to explain it again.


Consider this simple set of tables:

    +-----------+
    | Table1_ID |
    +-----------+        +-----------+
    | Table2_FK | -----> | Table2_ID |
    +-----------+        +-----------+
    | Name      |        | Title     |
    +-----------+        +-----------+
    | ...                | ...


    SELECT   Table1_ID, Table2_FK, Name, ..   Table2_ID, Title ..
    FROM Table1 Right Join Table2 ...


    +-----------+-----------+------+.. +-----------+-------+..
    | Table1_ID | Table2_FK | Name |   | Table2_ID | Title |
    +-----------+-----------+------+.. +-----------+-------+..
    |         0 |         0 | Mike |   |         0 | Clerk |
    +-----------+-----------+------+.. +-----------+-------+..


If you edit Table2_FK and change it for example from 0 to 1, and then move the focus either left or right, to another cell on the same row, then any dirty Table2 fields are first saved, and the newly pointed to record in Table2 is displayed in it's place.

Because only one field can be edited at a time, this works beautifully, and it resolves the question Lionel had asked.


Now once you have that in hand, the next step is what new features does this make possible?

* It makes it so that forms that use a query for a data-source, are also now more often editable.  In particular you can edit joined tables, instead of just simple tables.  The form will also need to save and update fields just the same as the query does above.  In a table control this should be fairly easy to make this work I think.

Hope I have explained this better.  I'm excited about this becoming reality someday.