Issue 108079 - query designer generates invalid SQL when multiple non-standard joins are used
Summary: query designer generates invalid SQL when multiple non-standard joins are used
Status: CLOSED FIXED
Alias: None
Product: Base
Classification: Application
Component: code (show other issues)
Version: OOo 3.1.1
Hardware: All All
: P3 Trivial (vote)
Target Milestone: OOo 3.3
Assignee: marc.neumann
QA Contact: issues@dba
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2010-01-05 09:33 UTC by Frank Schönheit
Modified: 2017-05-20 10:24 UTC (History)
1 user (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---

Attachments
document to reproduce the bug case (4.54 KB, application/vnd.sun.xml.base)
2010-01-05 09:33 UTC, Frank Schönheit 		no flags Details
Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this issue.
Description Frank Schönheit 2010-01-05 09:33:17 UTC 
- open the attached database document
- open the contained query in the graphical query designer ("Edit" from its
  context menu)
- change the type of both joins from "Inner" to "Left"
- execute the query by pressing F4
=> you get an error saying something like "Unexpected token ..."

If you examine the SQL statement, you find it to be something like the following
(shortened):

  SELECT ...
  FROM "more events"
  LEFT OUTER JOIN "events"
  LEFT OUTER JOIN "time stamps"
  ON "events"."when" = "time stamps"."when"
  ON "more events"."when" = "time stamps"."when"

, which of course is invalid SQL, the proper version would be something like

  SELECT ...
  FROM "time stamps"
  LEFT OUTER JOIN "events"
  ON "events"."when" = "time stamps"."when"
  LEFT OUTER JOIN "more events"
  ON "more events"."when" = "time stamps"."when"
Comment 1 Frank Schönheit 2010-01-05 09:33:58 UTC 
Created attachment 66989 [details]
document to reproduce the bug case
Comment 2 Frank Schönheit 2010-01-05 09:34:33 UTC 
targeting
Comment 3 ocke.janssen 2010-01-11 12:28:57 UTC 
Fixed in cws dba33f

You have to take care that the "time stamps" is on the left side of the
connection, otherwise you won't get the expected result.
Comment 4 ocke.janssen 2010-05-12 12:05:27 UTC 
The problem is the order in which you drop fields from table to another.
When looking at the sample you see that when changing to left join the current
version of hsqldb doesn't support the functionality to access table fields which
are in a nested join. For example Access creates the same SQL statement as we do
now. And other databases supports the nested feature as well. E.g. MySQL

To make the statement do what you want to you have to delete the joins and
create them again. But you have to take care that the "time stamps" table is on
the left side.

@msc: Please verify. Thanks
- Open sample
- Edit query and delete joins
- Create new joins and take for that "time stamps" is on the left side when you
double click the connection
- Choose left join
=> Execute
Comment 5 marc.neumann 2010-05-27 08:39:36 UTC 
verified in CWS dba33f

find more information about this CWS, like when it is available in the master
builds, in EIS, the Environment Information System:
http://eis.services.openoffice.org/EIS2/cws.ShowCWS?Path=DEV300%2Fdba33f