| Summary: | EDITING: Query input in foreignkey-field impossible, when two tables in the query. | ||
|---|---|---|---|
| Product: | LibreOffice | Reporter: | robert |
| Component: | Database | Assignee: | Not Assigned <libreoffice-bugs> |
| Status: | NEW --- | QA Contact: | |
| Severity: | enhancement | ||
| Priority: | medium | CC: | d0m1n1k, iplaw67, lionel, lo_bugs, serval2412 |
| Version: | 3.3.0 release | ||
| Hardware: | Other | ||
| OS: | All | ||
| Whiteboard: | |||
| i915 platform: | i915 features: | ||
| Attachments: |
Database with query - try the input: works in all fields except "town_ID"
Error appears, when input a new foreignkey Two queries in the database - query for two tables doesn't allow to change relation |
||
Created attachment 84103 [details]
Error appears, when input a new foreignkey
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. 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.
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. 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. 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 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 Adding self to CC if not already on |
Use of freedesktop.org services, including Bugzilla, is subject to our Code of Conduct. How we collect and use information is described in our Privacy Policy.
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.