https://bugs.freedesktop.org/show_bug.cgi?id=83121
Priority: medium
Bug ID: 83121
Assignee: libreoffice-bugs@lists.freedesktop.org
Summary: BASE QUERY EDITOR: silently changes one-to-many
relationships in complex query, impossible to set
reliable outer join relationships
Severity: major
Classification: Unclassified
OS: All
Reporter: dougt901-2...@yahoo.com
Hardware: Other
Status: UNCONFIRMED
Version: 4.1.6.2 release
Component: Database
Product: LibreOffice
Created attachment 105312
--> https://bugs.freedesktop.org/attachment.cgi?id=105312&action=edit
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.
--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
http://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
