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 4.3.0.4.
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 ... Regards Robert
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.
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
Adding self to CC if not already on
We're now in 4.3.5, any update?
Dear Bug Submitter, This bug has been in NEEDINFO status with no change for at least 6 months. Please provide the requested information as soon as possible and mark the bug as UNCONFIRMED. Due to regular bug tracker maintenance, if the bug is still in NEEDINFO status with no change in 30 days the QA team will close the bug as INVALID due to lack of needed information. For more information about our NEEDINFO policy please read the wiki located here: https://wiki.documentfoundation.org/QA/FDO/NEEDINFO If you have already provided the requested information, please mark the bug as UNCONFIRMED so that the QA team knows that the bug is ready to be confirmed. Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team This NEEDINFO message was generated on: 2015-09-03
This continues to be unresolved in Version: 5.0.1.2.0+ 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.
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.
** Please read this message in its entirety before responding ** 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 on a currently supported version of LibreOffice (5.1.5 or 5.2.1 https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the version of LibreOffice and your operating system, and any changes you see in the bug behavior If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a short comment that includes your version of LibreOffice and Operating System 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) http://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: http://webchat.freenode.net/?channels=libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug-20160920
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 5.2.2.2 and OpenSUSE 42.1 64bit rpm Linux.
** Please read this message in its entirety before responding ** 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 http://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 MassPing-UntouchedBug
Judging by bug 108375 and bug 117897, which both appear to be duplicates of this report, I would say that this is still open.
I have seen a bug once on an online website that sold varipous goods. I cost me money because I bought a product and then it turned out to be more expensive. I am just glad that this new website I am on doesn't have any bugs. It is https://www.jacars.net/vehicles/cars/manchester/ Jacars.net that sells and buys cars.
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 MassPing-UntouchedBug
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://web.libera.chat/?settings=#libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug