Bug 83121 - BASE QUERY EDITOR: silently changes one-to-many relationships in complex query, impossible to set reliable outer join relationships
Summary: BASE QUERY EDITOR: silently changes one-to-many relationships in complex quer...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
(earliest affected) release
Hardware: Other All
: high major
Assignee: Not Assigned
Depends on:
Blocks: Database-Queries
  Show dependency treegraph
Reported: 2014-08-27 02:53 UTC by Doug
Modified: 2021-08-20 03:52 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:

demonstration document - use suggested steps in Query1 (6.35 KB, application/vnd.oasis.opendocument.database)
2014-08-27 02:53 UTC, Doug

Note You need to log in before you can comment on or make changes to this bug.
Description Doug 2014-08-27 02:53:22 UTC
Created attachment 105312 [details]
demonstration document - use suggested steps in Query1

1.  Create a complex, cascade-type query such as the one in the attached in Query1.  This query is to have one-to-many/all-to-any relationships between all tables from left to right (in this case from 'owners' all the way to the various 'keys' and 'id's for fields in tables).

2.  Check and change all of the table relationships to enforce this one-to-many, left-to-right-ness.  That is, all first-tier relationships left to right should have left-most table 'owners' on the 'one'/'all' side, and the other tables moving right on the 'many'/'any' side.  Next tier tables moving to the right should be on the 'one'/'all' side, and the keys running off of those tables moving still farther to the right of the GUI should be on the 'many'/'any' side.

3.  The critical aspect of the sample Query1 appears to be that the middle 'branch' of the relationships is three tables deep (rather than two).  The third table on that branch appears to trigger the following problem (but not crystal clear). 

4.  Once user establishes the relationships, save query, close, reopen, and check the relationships again.  That already has been done in sample.

5.  Those relationships have changed.  That is, the one-to-many relationship of one or more of the tables has changed so that the table that was supposed to be on the 'many'/'any' side has switched to the 'one'/'all'.  That is reflected in the text description of the join, where the first referenced table is now reversed in the description.  The alteration means that, for example, the non-existence of a particular key that was supposed to be optional in the desired query, will block display of entire row in the query as modified (effect similar to an INNER JOIN).

EXPECTED BEHAVIOR:  The one-to-many relationships only should change when the user specifically directs.

ACTUAL BEHAVIOR:  This is relationship whack-a-mole; when one relationship is manually changed by the user, another relationship automatically and silently changes to an adverse setting.  Because there is no visual display of the relationship in the GUI (without multiple clicks), this is invisible except the data results are not consistent with expectations.  Query builder GUI might even be failing to correctly render the relationships, I can't really tell.   The result is dramatically adverse with the right/wrong data set, subtler here with the sample set.

ALTERNATIVE EXPECTED BEHAVIOR:  If Base is unable to render this query/determine correct relationships, it should throw an error.  However, the syntax seems capable of supporting it.

SUGGESTION:  Base query builder is defining the relationship as 'LEFT' or 'RIGHT', but that only has meaning if you tell the query what is on the left or what is on the right.  Since the GUI does not allow the user to specify this option, instead, consider using terminology 'ONLY' and 'ANY' (or similar) which will provide unambiguous, non-context-based directions from GUI for how to draft queries.

REASON WHY THIS IS A 'MAJOR' BUG:  Bug changes data that is displayed, makes output of query unreliable, the boundaries of the bug are unclear (it happens with this query, why not another), and one can spend a really long time doing this relationship whack-a-mole before realizing Base has a problem.

Earlier made related enhancement suggestion in < https://bugs.freedesktop.org/show_bug.cgi?id=82325 >

RELATED UNEXPECTED FUNCTIONALITY:  Query Builder GUI appears to save the relationships between tables separate from the query SQL so that if user sets up relationships in SQL text (notwithstanding removal by base of all text line ends after every close/reopen), and then switches into GUI, relationships are revised automatically by Query Builder GUI and need to be altered through GUI to conform to desired SQL syntax.  In other words, it is not clear that using SQL text feature is a workaround for this problem.  Appears to block using Query Builder for more than simplest queries.

Using JDBC connector to MariaDB, had additional problem that 'preview' of query would display eight or ten null rows; on close and execution of query out of preview mode it would render without completely null rows but still with anomalous/undesired joins noted above.

Encountered OpenSuse13.1/LO 4.1.6 Windows 7/LO
Comment 1 Robert Großkopf 2014-08-27 18:32:00 UTC
Excuse me, but I have created many databases and also the Base-Handbuch (in German), but I can't follow this description. I have read it more than one time, have downloaded the database.
Hope there is somebody else, who will have a look on this. I had never created such a query in the query-builder. Made such a thing directly in the SQL-Mode with LEFT JOIN ...


Comment 2 Doug 2014-08-27 21:53:59 UTC
Yes, in direct SQL mode Query1 actually very simple, should be all LEFT JOINS from the leftmost table.  Although there are a lot of joins, it is a real-world query structure (not real-world data) that displays the descriptions of table values, which otherwise simply are stored as keys.

When I attempted to switch to direct SQL mode, every time tried to view back in GUI the relationships became corrupted again, so bug impacts reliability in all circumstances.
Comment 3 Julien Nabet 2014-10-11 14:09:54 UTC
Doug: Reading that even Robert couldn't follow your example, I must recognize I didn't even try.

Please provide a more simple step by step process to reproduce this perhaps with a dabatase file more near the point where's the problem if needed.

You must have in mind that there are a lot of bugs to deal with so the more help we may have, the better it is.

BTW: it could be useful you try with last stable LO version 4.3.2
Comment 4 Alex Thurgood 2015-01-03 17:40:06 UTC Comment hidden (no-value)
Comment 5 Julien Nabet 2015-02-08 22:20:45 UTC
We're now in 4.3.5, any update?
Comment 6 QA Administrators 2015-09-04 02:55:45 UTC Comment hidden (obsolete)
Comment 7 Doug 2015-09-04 04:15:30 UTC
This continues to be unresolved in Version: Build ID: 00m0(Build:2) Locale: en-US (en_US.UTF-8).

Step-by-step to reproduce.  

1.  Open the sample document, Query 1, in edit mode. 
2.  Inspect the GUI table relationships part of the window.  You will see that the window has basically three columns of tables from left to right.

3.  Right mouse click on every relationship to ensure that the relationships are all one-to-many with the 'one' side being the tables on the LEFT graphically (although some are defined by the GUI as 'RIGHT JOIN') and the 'many' side being the tables on the RIGHT.  This setup, although complex, is common when using look-up tables.

4.  In the course of inspecting, perhaps change one of the relationships to the wrong setting and then change it to the right setting.

5. Execute, save, and close.

6.  Even close the whole database.

7.  Now reopen.  When I do so now, there are two problems.  

First, at least one of the one-to-many relationships has reversed.  Instead of going left-to-right, the relationship is backwards (right-to-left).  This breaks the query. 

Second, I get a new SQL error on the GUI-generated query:

SQL Status: 37000
Error code: -16

Wrong data type: java.lang.NumberFormatException: For input string: "samsung" in statement [SELECT "owners"."owners", "owners"."owner_name", "car_key"."car_text", "addr"."addr_text", "clocks_key"."clocks_text", "smartphone_key"."smartphone_key" FROM      "owners" LEFT OUTER JOIN "clocks" ON "owners"."owners" = "clocks"."owner_id" RIGHT OUTER JOIN "clocks_key" ON "clocks_key"."clocks_key" = "clocks"."clocks" RIGHT OUTER JOIN "clocks_num_key" ON "clocks_num_key"."id" = "clocks_key"."num_type_code" LEFT OUTER JOIN "cars" ON "owners"."owners" = "cars"."owner_id" RIGHT OUTER JOIN "addr" ON "addr"."addr_key" = "cars"."reg_addr" RIGHT OUTER JOIN "car_key" ON "car_key"."car_key" = "cars"."car" LEFT OUTER JOIN "smartphones" ON "owners"."owners" = "smartphones"."owner_id" RIGHT OUTER JOIN "smartphone_key" ON "smartphone_key"."smartphone_text" = "smartphones"."smartphone_id"  ]

Still a bug.
Comment 8 Alex Thurgood 2015-09-11 11:57:19 UTC
Confirming on LO 5012 OSX 10.10.5.

1) Open Doug's file.

2) Open Query1 in SQL Edit mode, change all of the right outer join instructions to left outer join instructions. Save the query.

3) Execute the query - it produces a result

4) Close the query window.

5) Now re-open the query in Edit (GUI) mode.

6) Right mouse button click on a relation, choose Edit and then switch LEFT JOIN to RIGHT JOIN, and then back to LEFT JOIN.

7) Now try to execute the query - it fails with the error message Doug has given.
Comment 9 QA Administrators 2016-09-20 10:32:23 UTC Comment hidden (obsolete)
Comment 10 Robert Großkopf 2016-10-01 10:38:44 UTC
Have opened the attachment. When I run the query I get
"Wrong data type: java.lang.NumberFormatException: For input string: "samsung" in statement ..." 
Opened the query for editing: smartphone.smartphone_id is connected to smartphone_key.smartphone_text. First is an integer-field, the second a varchar-field. This exception has nothing to do with changing left or right join in the GUI.

Couldn't find any buggy behavior whith teh query, when I define the connection the right way to smartphone_key.smartphone_key. Could change right join to left join. Could execute the query. Could change back. could execute the query.

Can't find the bug here with LO and OpenSUSE 42.1 64bit rpm Linux.
Comment 11 QA Administrators 2017-10-23 14:01:36 UTC Comment hidden (obsolete)
Comment 12 Alex Thurgood 2018-05-31 06:57:58 UTC
Judging by bug 108375 and bug 117897, which both appear to be duplicates of this report, I would say that this is still open.
Comment 13 oksthegreat 2019-08-20 11:29:38 UTC Comment hidden (spam)
Comment 14 QA Administrators 2021-08-20 03:52:14 UTC
Dear Doug,

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://kiwiirc.com/nextclient/irc.freenode.net/#libreoffice-qa

Thank you for helping us make LibreOffice even better for everyone!

Warm Regards,
QA Team