Bug 39872 - OUTER JOIN does not seem to work
Summary: OUTER JOIN does not seem to work
Status: RESOLVED NOTOURBUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
3.5.0 Beta3
Hardware: All Windows (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2011-08-05 19:55 UTC by Pat Willener
Modified: 2013-11-24 22:14 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
Outer Join tests for bug 39872 (19.09 KB, application/vnd.oasis.opendocument.base)
2011-12-24 00:02 UTC, Pat Willener
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Pat Willener 2011-08-05 19:55:37 UTC
OUTER JOIN gives various error messages, and in some cases crashes LibreOffice.

Details to reproduce; create 2 tables
1) testA:
   sku INTEGER, PRIMARY KEY
   asin VARCHAR
2) testG
   sku INTEGER, PRIMARY KEY
   ean VARCHAR

Values testA:
7	4770029489
9	0870113356
10	0870113364
11	087011252X
12	087011252X
13	0870400010
14	051754914X
15	0834802236
16	0817455590
19	0571087094
20	4072314323

Values testG:
7	9784770029485
9	9780870113352
10	9780870113369
11	9780870112522
12	9780870112522
13	9780870400018
14	9780517549148
15	9780834802230
16	9780817455590
19	9780571087099
20	9784072314326

Query1; traditional JOIN, works well:
SELECT * FROM "testG", "testA" WHERE "testG"."sku" = "testA"."sku"

Query2; INNER JOIN, works well:
SELECT * FROM "testG" JOIN "testA" ON "testG"."sku" = "testA"."sku"

Query3a; OUTER JOIN, gives error "Unexpected token OUTER":
SELECT * FROM "testG" FULL OUTER JOIN "testA" ON "testG"."sku" = "testA"."sku"

Query3b; OUTER JOIN, gives error "Column not found: sku":
SELECT * FROM "testG" FULL JOIN "testA" ON "testG"."sku" = "testA"."sku"

Query4; OUTER JOIN, all the following queries crash LibreOffice:
SELECT * FROM "testG" RIGHT OUTER JOIN "testA" ON "testG"."sku" = "testA"."sku"
SELECT * FROM "testG" LEFT OUTER JOIN "testA" ON "testG"."sku" = "testA"."sku"
SELECT * FROM "testG" RIGHT JOIN "testA" ON "testG"."sku" = "testA"."sku"
SELECT * FROM "testG" LEFT JOIN "testA" ON "testG"."sku" = "testA"."sku"
Comment 1 Julien Nabet 2011-10-31 07:47:56 UTC
I tried to reproduce the pb with master (future 3.5) on Debian x86-32 + on 3.4.3 (Debian pacakges) and i reproduced some of the errors.
LEFT OUTER JOIN, LEFT JOIN, RIGHT OUTER JOIN and RIGHT JOIN worked for me.
FULL JOIN and FULL OUTER JOIN failed for me with the same errors (no error messages in console).
Precision : I haven't put all the data but it should be the same.

Pat: could you please attach the database, so tests can quickly be done ?
could you please test again on 3.4.3 to confirm the results I had ?
Comment 2 Pat Willener 2011-11-19 17:54:02 UTC
> Pat: could you please attach the database, so tests can quickly be done ?

Sorry, I don't have LibreOffice any more; I have moved the project to Microsoft SQL Server.
Comment 3 Alex Thurgood 2011-11-20 01:18:07 UTC
(In reply to comment #1)
> I tried to reproduce the pb with master (future 3.5) on Debian x86-32 + on
> 3.4.3 (Debian pacakges) and i reproduced some of the errors.
> LEFT OUTER JOIN, LEFT JOIN, RIGHT OUTER JOIN and RIGHT JOIN worked for me.
> FULL JOIN and FULL OUTER JOIN failed for me with the same errors (no error
> messages in console).

Hi Julien,

Possibly linked to what was discovered here :

http://archives.postgresql.org/pgsql-jdbc/2003-11/msg00085.php

i.e. the insertion of the {OJ} token by LO (and OOo before that) into the query statement where the db driver can't parse it

and the problem has been reported before elsewhere :

https://issues.apache.org/ooo/show_bug.cgi?id=50912

https://issues.apache.org/ooo/show_bug.cgi?id=22355


What the OOo pepole did was introduce an advanced setting that you can turn on and off to avoid the default ODBC behaviour of inserting the token, so the solution might might be to play with the optional check box setting in Edit> Database > AdvancedSettings > Use Outer Join syntax {OJ}.

From what I understand of this problem, however, is that the driver/db must support SQL-2 syntax instead of ODBC syntax - if it doesn't, then I guess the setting will have no effect, and the default ODBC behaviour will still kick in.

As the OP, has said what the backend db was, or how he was connecting to it, we  only have your test results to go, which I presume were made with HSQLDB 1.8 ??


Alex
Comment 4 Alex Thurgood 2011-11-20 01:19:13 UTC
(In reply to comment #3)


As the OP, _*hasn't*_ said what the backend db was, or how he was connecting to it,
Comment 5 Alex Thurgood 2011-11-20 01:38:26 UTC
Hmmm,

Note also however, the fairly recent bug report in OOo :

https://issues.apache.org/ooo/show_bug.cgi?id=108079

The fix may not have made it into the LO source code, 

Alex
Comment 6 Alex Thurgood 2011-11-20 01:46:05 UTC
(In reply to comment #5)
> Hmmm,
> 
> Note also however, the fairly recent bug report in OOo :
> 
> https://issues.apache.org/ooo/show_bug.cgi?id=108079
> 
> The fix may not have made it into the LO source code, 
> 
> Alex

Well, I found it in git log so I guess it was in the code after all.


Alex
Comment 7 Julien Nabet 2011-12-04 13:25:57 UTC
(In reply to comment #3)
> (In reply to comment #1)
>...
>  only have your test results to go, which I presume were made with HSQLDB 1.8
> ?? 
> Alex
I don't know if it's ok now, your last comment seems to say so but for my information, how can we know the HSQLDB version we use ?
Comment 8 Björn Michaelsen 2011-12-23 12:22:31 UTC
[This is an automated message.]
This bug was filed before the changes to Bugzilla on 2011-10-16. Thus it
started right out as NEW without ever being explicitly confirmed. The bug is
changed to state NEEDINFO for this reason. To move this bug from NEEDINFO back
to NEW please check if the bug still persists with the 3.5.0 beta1 or beta2 prereleases.
Details on how to test the 3.5.0 beta1 can be found at:
http://wiki.documentfoundation.org/QA/BugHunting_Session_3.5.0.-1

more detail on this bulk operation: http://nabble.documentfoundation.org/RFC-Operation-Spamzilla-tp3607474p3607474.html
Comment 9 Björn Michaelsen 2011-12-23 17:02:20 UTC
needinfo keyword redundant by needinfo status.
Comment 10 Pat Willener 2011-12-24 00:02:15 UTC
Created attachment 54777 [details]
Outer Join tests for bug 39872

File test-39872.odb was created with LibO-Dev 3.5.0 Beta2.
It contains the two tables with data used for the test, as well as the original 8 queries.
Comment 11 Pat Willener 2011-12-24 00:04:32 UTC
(In reply to comment #8)

(In reply to comment #8)
I downloaded and installed LibO-Dev Beta2 [on Windows 7 x64]. The install was annoying, as the whole interaction was in Japanese. However, the install succeeded, and the actual product interface shows in English.

I recreated the tables from the original post, then ran the queries.

Result:
- Query1 and Query2 ran successfully, as expected.

- Query4 (Query4a, Query4b, Query4c, Query4d) ran successfully.

- Query3a produced the following error:
  SQL Status: 37000
  Error code: -11
  Unexpected token: OUTER in statement [SELECT * FROM "testG" FULL OUTER JOIN "testA" ON "testG"."sku" = "testA"."sku"]

- Query3b produced the following error:
  SQL Status: S0022
  Error code: -28
  Column not found: sku in statement [SELECT * FROM "testG" FULL JOIN "testA" ON "testG"."sku" = "testA"."sku"]
Comment 12 Pat Willener 2012-01-16 20:35:38 UTC
LibO-Dev Beta3: exactly the same results as above.
Comment 13 Pat Willener 2012-01-16 20:37:00 UTC
Changed version to LibO 3.5.0 Beta 3.
Comment 14 Lionel Elie Mamane 2012-01-17 03:07:53 UTC
LibreOffice is not a database engine in itself; as such you can use only the joins (and SQL syntax) supported by the database engine you connect to. Unless you enable "Run SQL command directly" (in a query) or disable "Analyze SQL command" (in a report), the SQL syntax is also constrained by what LibreOffice supports.

I understand you run your rests against the built-in-LibreOffice HSQLDB 1.8. As you can see on http://www.hsqldb.org/doc/1.8/guide/ch09.html#select-section, HSQLDB 1.8 has rather limited join support, and has a particularly perverse syntax for cross joins: "SELECT foo FROM bar CROSS JOIN qux ON TRUE". Yes, the "ON" clause is mandatory... And it seems no sensible WHERE clause will work. But that's HSQLDB's bug.

We should upgrade to HSQLDB 2.x, which has far better support for all kinds of things... http://hsqldb.org/doc/2.0/guide/dataaccess-chapt.html#dac_joined_table


If you run the same tests against a SQLite DB, you'll find that "RIGHT [OUTER] JOIN" is not supported, and neither is the "FULL" keyword (but the same feature is available with ","). See http://www.sqlite.org/syntaxdiagrams.html#join-op .

If you run the same tests against a PostgreSQL, MySQL/MariaDB/..., Microsoft SQL Server, ... database, I expect you'll find all your examples work correctly.


So I'm closing this bug as "not our bug".


If you find some SQL syntax that you'd like to be supported but is not supported by LibreOffice, or more generally something that is supported by the database, but does not work when used through LibreOffice, please file that as a separate bug. In this case, typically the query will work with "Run SQL command directly", but not without it. You can assign the bug directly to me so that I take a look at it, but do not put it in the "assigned" state (leave it as "UNCONFIRMED" or "NEW"). Alternatively, you can also put me in the CC list.
Comment 15 Pat Willener 2012-01-17 16:53:45 UTC
(In reply to comment #14)
> We should upgrade to HSQLDB 2.x, which has far better support for all kinds of
> things...
> http://hsqldb.org/doc/2.0/guide/dataaccess-chapt.html#dac_joined_table

Will that ever happen, or will users need to install a 3rd-party database engine to perform joins?

Anyway, thank you for your explanation.
Comment 16 Lionel Elie Mamane 2012-01-17 23:49:10 UTC
(In reply to comment #15)
> (In reply to comment #14)
>> We should upgrade to HSQLDB 2.x, which has far better support for all kinds of
>> things...
>> http://hsqldb.org/doc/2.0/guide/dataaccess-chapt.html#dac_joined_table

> Will that ever happen,

I expect it will happen, but I don't have a precise time estimate. I filed bug 44854 to track the progress of that.