Download it now!
Bug 108375 - Query: Clicking 'Switch Design View On/Off' a few times messes up SQL JOINs
Summary: Query: Clicking 'Switch Design View On/Off' a few times messes up SQL JOINs
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
3.6.7.2 release
Hardware: All All
: high major
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2017-06-06 20:34 UTC by Howard Johnson
Modified: 2019-11-13 07:29 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
database to demonstrate the bug (4.01 KB, application/vnd.sun.xml.base)
2017-06-06 20:34 UTC, Howard Johnson
Details
Exampledatabase with dates - open the query for editing and switch designmode on and off (more than 2 times) (4.94 KB, application/vnd.oasis.opendocument.database)
2017-06-09 16:45 UTC, Robert Großkopf
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Howard Johnson 2017-06-06 20:34:59 UTC
Created attachment 133880 [details]
database to demonstrate the bug

To demonstrate this bug open the attached HSQLDB data base and Edit Query1.

Double click on each of the three links, and confirm join directions and note the comments saying which table gets 'ALL' and which table gets 'only'.

Initially the links are setup so:
  left  of the link 'ALL'  items          are selected, and 
  right of the link 'only' matching items are selected, 

or specifically:


    The Table3-Table2 link: 

        Right join

        Table2 | Table3

        Contains ALL records from table 'Table3' but only records from table
        'Table2' where the values in the related fields are matching.


    The Table2-Table1 link:

        Left join

        Tabel2 | Table1

        Contains ALL records from table 'Table2' but only records from table
        'Table1' where the values in the related fields are matching.


    The Table3-Table4 link:

        Left join

        Table3 | Table4

        Contains ALL records from table 'Table3' but only records from table
        'Table4' where the values in the related fields are matching."



Now click the Switch Design View On/Off to show SQL view.  Copy and save the SQL for later analysis.


Then click Switch Design View On/Off a 2nd time to come back to the GUI design to see that it's changed one of the JOINs.  Again double click on the links to show the join conditions.


Expected results: the links join conditions should be exactly the same as before, or as noted above.

Buggy results: Although the join direction specification ('LEFT' or 'RIGHT') don't seem to change, RANDOMLY the comments below do change.  Here is one such recorded incorrect Type and associated comment:


   The Table3-Table2 links reads: 

        Right join            (Note this is now Right when it was Left before!)

        Table3 | Table2       (Note this is swapped from before!)

        Contains ALL records from table 'Table3' but only records from table
        'Table2' where the values in the related fields are matching.


    The Table2-Table1 links reads:

        Left join

        Tabel1 | Table2       (Note this and below are swapped from before!)

        Contains ALL records from table 'Table1' but only records from table
        'Table2' where the values in the related fields are matching.

           ^^^ This is now wrong 


    The Table3-Table4 links reads:

        Left join

        Table3 | Table4

        Contains ALL records from table 'Table3' but only records from table
        'Table4' where the values in the related fields are matching."


!! I have also seen the Table3-Table4 link do the same thing, but not at the same time as the above result.  I can't tell why it happens sometimes one way, and another time the other way.



Now take a look at the SQL that you saved above: 

SELECT "Table3".*, "Table2"."Text2", "Table1"."Text1" FROM { oj "Table2" RIGHT OUTER JOIN "Table3" ON "Table2"."Table2 ID" = "Table3"."Table2 ID" LEFT OUTER JOIN "Table4" ON "Table3"."Table4 ID" = "Table4"."Table4 ID" LEFT OUTER JOIN "Table1" ON "Table2"."Table1 ID" = "Table1"."Table1 ID" }

or pretty printed this becomes:

...FROM { oj       "Table2" 
  RIGHT OUTER JOIN "Table3" ON "Table2"."Table2 ID" = "Table3"."Table2 ID" 
  LEFT  OUTER JOIN "Table4" ON "Table3"."Table4 ID" = "Table4"."Table4 ID" 
  LEFT  OUTER JOIN "Table1" ON "Table2"."Table1 ID" = "Table1"."Table1 ID" }

This is correct.  Simplified this is: ((Table3 -> Table2) -> Table4) -> Table1


BUT click Switch Design View On/Off twice more and then look at the SQL.  I'm now getting this:

...FROM { oj       "Table2"
  RIGHT OUTER JOIN "Table3" ON "Table2"."Table2 ID" = "Table3"."Table2 ID" 
  RIGHT OUTER JOIN "Table4" ON "Table4"."Table4 ID" = "Table3"."Table4 ID" 
  LEFT  OUTER JOIN "Table1" ON "Table2"."Table1 ID" = "Table1"."Table1 ID" }

This is wrong.  Simplified this is: (Table 4 -> (Table3 -> Table2)) -> Table1
The table 4 join is wrong.


So only by clicking the Switch Design View On/Off button a few times, SQL is changed and does not agree with the original GUI design.
Comment 1 Howard Johnson 2017-06-06 21:16:32 UTC
Until this is resolved, and in addition to this being resolved, and because this has created bugs in SQL, I think it would be a good idea to draw the links between data sources (tables or queries) in the GUI as an arrow rather than a simple line, so you can immediately see which way the join points.

A -> B   ==   A LEFT  JOIN B   or  B RIGHT JOIN A
A <- B   ==   A RIGHT JOIN B   or  B LEFT  JOIN A
Comment 2 Robert Großkopf 2017-06-09 16:45:49 UTC
Created attachment 133932 [details]
Exampledatabase with dates - open the query for editing and switch designmode on and off (more than 2 times)

I could confirm the buggy behavior. But it isn't easy to reproduce without data. So I have added Data and relationship to the first attachment. So you could see the changing when switching on and of query-design-mode run the query every time.

Could reproduce the bug with every LO- version here - also LO 3.6.7.2. Could be it is introduced by OOo.

My system: OpenSUSE 64bit rpm Linux.
Comment 3 Howard Johnson 2017-07-28 13:59:08 UTC
Because you can't edit any query in GUI mode, even if you don't change a single thing in the design, without it very often and quietly messing up your joins, resulting in silent data integrity consequences, I am requesting that this please be upgraded to MAJOR.


For example, you can't even open a query, change nothing at all, then save the design, without fear of it having messes up your joins.  So afterwards when you run the query, like in a report, the data is selected wrong and some records are left out, and you might not even notice it for some time!!!

The painful workaround for now:  Every single time you edit a query you have to manually check each and every join to make sure that it hasn't gotten messed up.  For queries with one join this isn't too much of an issue, but for queries with 5 or 10 joins, this is a pain in the neck to have to do each time you try to edit the query.

Other observations:

* I also noticed that some times a Right or Left join gets changed to an inner join.

* I have noticed that this bug occurs when the SQL is interpreted so it can be graphically visualized, either:

a) when the query is opened to be edited graphically, or
b) when the query is in SQL edit mode, and then switched to be edited graphically.

* Fortunately the bug is not when the query is saved, but only when it is opened for edit in GUI mode.  So at least there is a way to inspect and fix the design each time before it is saved.
Comment 4 Julien Nabet 2018-01-17 11:14:30 UTC
I agree the pb is important since:
- it concerns all env
- it changes data without warning

Lionel: I tried to investigate dbaccess/source/ui/querydesign/QueryDesignView.cxx (eg GetNextJoin, BuildJoin) dbaccess/source/ui/querydesign/JoinTableView.cxx but am a bit lost. If you have any idea where to dig...
Comment 5 Alex Thurgood 2018-05-31 06:52:25 UTC
Isn't this a duplicate of bug 83121 ?
Comment 6 QA Administrators 2019-06-01 02:49:35 UTC Comment hidden (obsolete)
Comment 7 Robert Großkopf 2019-06-01 08:10:49 UTC
Bug still exists in LO 6.2.4.2 on OpenSUSE 15 64bit rpm Linux.
Comment 8 userxyz 2019-11-13 07:29:31 UTC
(In reply to Robert Großkopf from comment #7)
> Bug still exists in LO 6.2.4.2 on OpenSUSE 15 64bit rpm Linux.

.... And still exists in LO 6.2.7.1 on OpenSUSE 15.1 LEAP 64bit rpm Linux 
I use a databaseconnection with postgres