Issue 107767 - left/right join in designer returns an error
Summary: left/right join in designer returns an error
Alias: None
Product: Base
Classification: Application
Component: code (show other issues)
Version: OOO320m7
Hardware: Unknown Windows 7
: P3 Trivial (vote)
Target Milestone: ---
Assignee: dbaneedsconfirm
QA Contact: issues@dba
Depends on:
Reported: 2009-12-18 12:25 UTC by lucianpricop
Modified: 2010-12-02 14:07 UTC (History)
1 user (show)

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


Note You need to log in before you can comment on or make changes to this issue.
Description lucianpricop 2009-12-18 12:25:44 UTC
I have JDBC connection to a PostgreSQL database using org.postgresql.Driver.
On this database I want to left/right join 2 tables using the designer to build 
the query:

I add the two tables to the work area, drag the column from the first over the 
column of the other to create a join, then right click on the connecting line, 
click on edit and choose instead of the default type "inner join", "left join".
When I try to run the query I get:

SQL Status: 42601
ERROR: syntax error at or near "{"
  Position: 75

The query in the sql editor looks like:

SELECT "table1"."bla", "table2"."blabla" FROM { OJ "public"."table2" AS "table2" 
LEFT OUTER JOIN "public"."table1" AS "table1" ON "table2"."refid" = 
"table1"."refid" } 

Which of course is incorrect syntax. Correcting the query make is work:

SELECT "table1"."bla", "table2"."blabla" FROM ( "public"."table2" AS "table2" 
LEFT OUTER JOIN "public"."table1" AS "table1" ON "table2"."refid" = 
"table1"."refid" )
Comment 1 lucianpricop 2009-12-18 12:27:32 UTC
This issue can be observed on Windows XP on OOO311 as well.
Comment 2 drewjensen.inbox 2010-01-03 15:23:48 UTC
 @ lucianpricop If you use the Advanced Properties dialog (Edit>DataBase>Advanced 
Properties) to disable the option:
"User outer join syntax {OJ}"

Does this rectify the situation? Thanks
Comment 3 lucianpricop 2010-01-04 08:21:27 UTC
Yes, that rectifies the situation. But perhaps it would be better if instead of
a nasty error, the designer would suggest this fix. Thank you
Comment 4 ocke.janssen 2010-12-02 14:07:14 UTC