[Libreoffice-bugs] [Bug 108375] New: Query: Clicking 'Switch Design View On/Off' a few times messes up SQL JOINs

[bugzilla-daemon at bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=108375

            Bug ID: 108375
           Summary: Query: Clicking 'Switch Design View On/Off' a few
                    times messes up SQL JOINs
           Product: LibreOffice
           Version: 5.3.3.2 release
          Hardware: All
                OS: All
            Status: UNCONFIRMED
          Severity: normal
          Priority: medium
         Component: Base
          Assignee: libreoffice-bugs at lists.freedesktop.org
          Reporter: BridgeportContractor at gmail.com

Created attachment 133880
  --> https://bugs.documentfoundation.org/attachment.cgi?id=133880&action=edit
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.

-- 
You are receiving this mail because:
You are the assignee for the bug.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.freedesktop.org/archives/libreoffice-bugs/attachments/20170606/c227500f/attachment-0001.html>