Issue 22355 - left outer joins fail w/ JDBC
Summary: left outer joins fail w/ JDBC
Status: CLOSED DUPLICATE of issue 14296
Alias: None
Product: Base
Classification: Application
Component: code (show other issues)
Version: OOo 1.1
Hardware: Other FreeBSD
: P3 Trivial (vote)
Target Milestone: ---
Assignee: ocke.janssen
QA Contact: issues@dba
Depends on:
Reported: 2003-11-11 17:02 UTC by girgen
Modified: 2013-08-07 15:45 UTC (History)
1 user (show)

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


Note You need to log in before you can comment on or make changes to this issue.
Description girgen 2003-11-11 17:02:23 UTC
When using JDBC connecting to postgresql-7.3.4, the Query Designer creates bad
SQL when connecting using JDBC. I have gotten info indicating the this is
correct for ODBC, where the driver modifies the SQL depending on how the db
server handles this kind of SQL construct.

Problem is when using left outer joins. The SQL created contains

... FROM { OJ table_a alias_a left outer join table_b alias_b }

the "{ OJ" part is plain wrong SQL, but as I said, is probably rewritten by the
ODBC driver. I want to use JDBC instead, since it is soo much easier, esp on unix.

This patch seems to fix it for JDBC, but may break ODBC?

--- dbaccess/source/ui/querydesign/QueryDesignView.cxx.orig	Tue Nov 11 16:57:22 2003
+++ dbaccess/source/ui/querydesign/QueryDesignView.cxx	Tue Nov 11 16:57:48 2003
@@ -1141,9 +1141,9 @@
-						::rtl::OUString aStr = ::rtl::OUString::createFromAscii("{ OJ ");
+						::rtl::OUString aStr = ::rtl::OUString::createFromAscii("( ");
 						aStr += aJoin;
-						aStr += ::rtl::OUString::createFromAscii(" },");
+						aStr += ::rtl::OUString::createFromAscii(" ),");
 						aTableListStr += aStr;

A good fix probably includes conditionally handling both JDBC and ODBC
SQL-statements, I guess?

FreeBSD 4.9-RELEASE, postgresq-7.3.4, se-openoffice-1.1.0_1 from FreeBSD ports
Comment 1 marc.neumann 2003-11-12 16:17:32 UTC

send to the right developer, set target, confirm.

please see also issue 14296

bye Marc
Comment 2 ocke.janssen 2003-11-14 08:37:28 UTC

we have to investigate if the normal SQL syntax can be used for all
databases now. 
E.g. from a outer join b on =

Could please test if this also works for pstregesql? Every help is

Thanks in advance.

Best regards,

Comment 3 Frank Schönheit 2003-11-14 15:44:02 UTC
Girgen, thanks for this patch :). Additionally to what Ocke already
said: Possible solutions I see:

- make this dependent on the data source. There already are a number
of settings per data source (stored in the Info property) which define
some behavior. We could introduce an additional flag/enum/whatever
which specifies how to create JOIN clauses. This could be defaulted so
that for ODBC, the OJ-notation is used, and for everything else, the
other notation.
(Ocke, does our parser understand the second notation?)

- well, always use your notation :), but only after we're *sure* that
it works with all relevant databases.

Since being sure is really hard to achieve (since there are databases
which we cannot test, not to talk about driver versions and such), I
would propose the first way. Would you be interested to extend your
patch this way? We could give you pointers to the code in question,
plus any additional help you need for this. Would be great :)
Comment 4 girgen 2003-11-14 15:48:26 UTC
You must say "left", "right" or "full" as well, but I guess that's
what you meant?

from a left outer join b on =   OK
from a right outer join b on =  OK
from a full outer join b on =   OK
from a outer join b on =   <--- BAD

this is with postgresql-7.1 or higher, outer joins where introduced in
7.1, release 2001-04-13. Syntax has always been this one.

On the other hand, I got info that the JDBC standard allows for
constructs like the one discussed here:

»11.1 SQL Escape Syntax
JDBC supports the same DBMS-independent escape syntax as ODBC for
stored procedures, scalar functions, dates, times, and outer joins. A
driver maps this escape syntax into DBMSspecific syntax, allowing
portability of application programs that require these features. The
DBMS-independent syntax is based on an escape clause demarcated by
curly braces and a keyword:
 	{keyword ... parameters ...}
This ODBC-compatible escape syntax is in general not the same as has
been adopted by ANSI in SQL-2 Transitional Level for the same
functionality. In cases where all of the desired DBMSs support the
standard SQL-2 syntax, the user is encouraged to use that syntax
instead of these escapes. When enough DBMSs support the more advanced
SQL-2 syntax and semantics these escapes should no longer be necessary.«

This indicates that postgresql's jdbc implementation is responsible
for it, unless we now agree that "enough DBMSs support the more
advanced SQL-2 syntax and semantics".

Postgresql does. :)
Comment 5 Frank Schönheit 2004-01-05 09:59:08 UTC
I don't think that "enough" JDBC drivers support the ODBC syntax - for this term
the percentage should be very near to 100 :). I think we should introduce a
per-data source option to switch of the ODBC syntax, and by default use it for
JDBC data sources.
Comment 6 Frank Schönheit 2004-01-05 10:21:57 UTC
Comment 7 Frank Schönheit 2004-01-05 12:51:39 UTC
Comment 8 Frank Schönheit 2004-01-05 12:53:14 UTC
Basically, what I wanted to say: Would be nice if we had an option to use the
SQL-2 syntax instead of the ODBC syntax - I suppose for quite a while, there
will be enough drivers supporting only one of both, so we need to work with both.
Comment 9 ocke.janssen 2004-01-29 12:18:05 UTC
set this one as duplicate

*** This issue has been marked as a duplicate of 14296 ***
Comment 10 hans_werner67 2004-02-02 12:56:31 UTC
change subcomponent to 'none'
Comment 11 ocke.janssen 2006-10-31 14:10:53 UTC
Closing it.