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"
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 ?
> 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.
(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
(In reply to comment #3) As the OP, _*hasn't*_ said what the backend db was, or how he was connecting to it,
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
(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
(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 ?
[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
needinfo keyword redundant by needinfo status.
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.
(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"]
LibO-Dev Beta3: exactly the same results as above.
Changed version to LibO 3.5.0 Beta 3.
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.
(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.
(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.