Problem description: When creating queries with more than one table and linking them using an outer join (by clicking on the line in the graphical query editor and setting the join type to outer join), the query does not work. It reates a syntax error, because LO inersts a "OJ" token in the query, which PostgreSQL can not handle. I found no possibility to turn of the "OJ" token inserting. Steps to reproduce: 1. Create a query with two tables that use an outer join 2. Execute the query 3. Look at the resulting error Current behavior: Syntax error Expected behavior: A working query and a result set. This is PostgreSQL 9.2, fwiw Operating System: Mac OS X Version: 4.1.1.2 release
Isn't this inherited behaviour from OOo 3 ? Or is this a regression over something that worked in previous versions of LibreOffice ? Alex
I have reset the importance to medium/normal because as far as I know this is historic. It would only really be a blocker if it caused data loss or could be considered as a serious regression (e.g. crash, data loss, profile corruption, etc). Alex
Well, a quick trawl on the AOO bugzilla produced these reports : https://issues.apache.org/ooo/show_bug.cgi?id=14296 https://issues.apache.org/ooo/show_bug.cgi?id=22355 https://issues.apache.org/ooo/show_bug.cgi?id=107767 See here : http://www.openoffice.org/dba/howto/OuterJoinSequence.html Use the Advanced Settings options in your ODB file to tell LO to work with the ODBC outer join syntax. If that option doesn't work, then it would appear that we have a bug. Alex
Actually the Advanced Settings Dialog is not available, so thetre is no way to disable this "OJ" fragment. Please try it yourself before changing this bug report to NOT A BUG.
I reset the severity to medium/normal. But still this means that not all data can be accessed from LibreOffice yet, so for PostgreSQL users this is actually a blocker if outer joins are needed.
Can't confirm this behavior for OpenSUSE Linux 64bit rpm. When I set the outer join it works right. I get, for example, this query without changing anything in the SQL: SELECT "Town"."Town", "person"."name" FROM "public"."person" AS "person" RIGHT OUTER JOIN "public"."Town" AS "Town" ON "person"."ID_Town" = "Town"."ID" LO-version 4.1.1.2, rpm-packages from SuSE-repositories, direct connection to PostgreSQL. When I switch to PostgreSQL-ODBC the following query is created and works, too: SELECT "Town"."Town", "person"."name" FROM { OJ "libretest"."public"."person" AS "person" RIGHT OUTER JOIN "libretest"."public"."Town" AS "Town" ON "person"."ID_Town" = "Town"."ID" }
You are using a different driver on a different OS. This bug report is for LibreOffice on Mac OS X, using the native SDBC driver. Using the ODBC driver on Linux is a different story. But it would be interesting to test the SDBC driver on platforms other than OS X.
(In reply to comment #7) > You are using a different driver on a different OS. This bug report is for > LibreOffice on Mac OS X, using the native SDBC driver. > > Using the ODBC driver on Linux is a different story. I have first tested with the native SDBC driver, then tested with ODBC. I haven't called it "native" but "direct connection". Only difference is the platform.
On a side note, I noticed that the "Advanced settings" menu entry is greyed out both on Mac OS X and on Windows. Maybe that should be a separate bug report.
(In reply to comment #9) > On a side note, I noticed that the "Advanced settings" menu entry is greyed > out both on Mac OS X and on Windows. Maybe that should be a separate bug > report. It's the same in Linux on my system with the native driver. I think all the special settings for PostgreSQL were made in the native driver. It doesn't need any advanced settings. So the native driver with greyed out "Advanced setting" produces the right code for PostgreSQL with OpenSUSE. When it produces the wrong code its a problem of the driver ...
I can now confirm that the same problem exists on Windows. As soon as the link between two tables is set to "outer join", it does not work anymore. Same bug as on OS X, it inserts an "OJ" token in the SQL command sent to the PostgreSQL server.
The problem also exists on Linux (Debian Squeeze, PostgreSQL 9.2, LibreOffice 4.1.1.2). Setting a join in the graphical editor to right join or full outer join results in an error message. Since I have this now repeated on three platforms, I am pretty sure the problem really exists...
(In reply to comment #1) > Isn't this inherited behaviour from OOo 3 ? > > Or is this a regression over something that worked in previous versions of > LibreOffice ? I don't know. I discovered it in 4.1.1.2 only, but I did not use outer joins in previous versions. We are makers of a point of sale solution that is based on PostgreSQL, and we thought it would be nice if our customers could use LibreOffice to create individual reports and such.
So it works only with OpenSUSE 12.3 64bit rpm? Why should the driver for rpm-packeges be not the same than for windows, *.deb or Mac? I have tested it in different versions, dircetly from LO or from OpenSUSE - all the same, works. Where did you get you PostgreSQL-driver from?
It's the one that comes bundled with LibreOffice, I had not to install a separate driver.
When using ODBC, need to disable "Use Outer Join syntax '{ OJ }' in Advanced Settings. When using native / direct connexion, it is forcefully disabled by the driver always and the UI to reenable it is disabled (because it would not make any sense, it is guaranteed not to work). If it is not anymore disabled by the driver, that is a regression bug.
Cannot reproduce on LibreOffice 4.1.0.4 (official TDF build) on Debian GNU/Linux amd64. Cannot reproduce on LibreOffice 4.1.3.0.0+ (my development tree - debug build of libreoffice-4-1 branch) on Debian GNU/Linux amd64. Test done: - open an existing .odb file that is configured for native PostgreSQL connection. - queries / create in design mode - add two tables - drag'n drop a field from one table to the other - double-click on the line between both - choose "full (outer)" join; also tested "left join" and "right join" - double-click on one field of each table to include them in the result - execute query, display its SQL no trace of "OJ". Also tested by creating a new .odb file ("connect to existing database" / PostgreSQL). Marc, could I see your .odb file? Just attach it here. My current theory is that the .odb file was created with another connection type (ODBC maybe?) and contains the setting "use ODBC { OJ }" syntax and this is not (properly) overridden by the native driver. Please also test: - create a fresh .odb file from scratch, using a native PostgreSQL connection from the beginning. Does it work then? - switch your .odb file to ODBC (any driver, any source; does not even ] need to make a successful connection). Use menu "edit / connection type" for that. Then change "advanced settings" to _disable_ using the ODBC { OJ } syntax. Save the .odb, and switch it back to native PostgreSQL. Does it work now?
(In reply to comment #10) > (In reply to comment #9) >> On a side note, I noticed that the "Advanced settings" menu entry is greyed >> out both on Mac OS X and on Windows. Maybe that should be a separate bug >> report. > It's the same in Linux on my system with the native driver. I think all the > special settings for PostgreSQL were made in the native driver. It doesn't > need any advanced settings. So the native driver with greyed out "Advanced > setting" produces the right code for PostgreSQL with OpenSUSE. When it > produces the wrong code its a problem of the driver ... Exactly. Most of / all these "advanced settings" are when different DBMS/driver combinations expect different behaviour from LibreOffice, but LibreOffice does not "know" what DBMS it is connecting to. E.g. through JDBC or ODBC. For native drivers, LibreOffice knows what DBMS it is connecting to, the choices can be hardcoded the way the DBMS expects it, so it makes no sense to even provide a GUI for the user to make the (wrong) choice.
As per Lionel's request in comment 17, setting to NEEDINFO
Tested on : Version: 4.2.0.3 Build ID: c63c03decdf780d8fb80823950665b782ec9ecd0 OSX Mountain Lion 10.8.5 Native postgres SBDC driver connecting to localhost postgres 9.3 server instance. Code d'erreur: 1 pq_driver: [PGRES_FATAL_ERROR]ERROR: syntax error at or near "{" LINE 1: ...er"."description", "character"."speechcount" FROM { OJ "publ... ^ (caused by statement 'SELECT "character".*, "character"."charname", "character"."abbrev", "character"."description", "character"."speechcount" FROM { OJ "public"."character_work" AS "character_work" FULL OUTER JOIN "public"."character" AS "character" ON "character_work"."charid" = "character"."charid" }') So this was with a FULL OUTER JOIN set up via the GUI query designer. Confirming.
Further info : Using the same query except for the join parameters, all being set via the GUI by clicking on the relation link between two tables and selecting the join option : INNER JOIN : executes and displays results LEFT JOIN : error, failure to execute query Code d'erreur: 1 pq_driver: [PGRES_FATAL_ERROR]ERROR: syntax error at or near "{" LINE 1: ...er"."speechcount", "character_work"."workid" FROM { OJ "publ... ^ (caused by statement 'SELECT "character_work"."charid", "character"."charname", "character"."abbrev", "character"."description", "character"."speechcount", "character_work"."workid" FROM { OJ "public"."character_work" AS "character_work" LEFT OUTER JOIN "public"."character" AS "character" ON "character_work"."charid" = "character"."charid" }') RIGHT JOIN : error, failure to execute query Code d'erreur: 1 pq_driver: [PGRES_FATAL_ERROR]ERROR: syntax error at or near "{" LINE 1: ...er"."speechcount", "character_work"."workid" FROM { OJ "publ... ^ (caused by statement 'SELECT "character_work"."charid", "character"."charname", "character"."abbrev", "character"."description", "character"."speechcount", "character_work"."workid" FROM { OJ "public"."character_work" AS "character_work" RIGHT OUTER JOIN "public"."character" AS "character" ON "character_work"."charid" = "character"."charid" }') CROSS JOIN : executes and displays results
I renamed the file share/registry/postgresqlsdbc.xcd to postgresql.xcd and it solved this bug. I'm not sure why, maybe something related to gbuildification and/or solver-to-workdir transition and/or to the change from having an "install" directory to just "instdir"
Lionel Elie Mamane committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=45b1211d575b680534d7848a39ccad9efd9549dc fdo#69309 rename share/registry/postgresqlsdbc.xcd to postgresql.xcd The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
(In reply to comment #20) > Tested on : > Version: 4.2.0.3 > Build ID: c63c03decdf780d8fb80823950665b782ec9ecd0 > Confirming. @alex: can you also reproduce in the 4.1.x line? Just checking whether I should also backport the fix to 4.1 (already requested review for 4.2 in gerrit).
(In reply to comment #24) > @alex: can you also reproduce in the 4.1.x line? Just checking whether I > should also backport the fix to 4.1 (already requested review for 4.2 in > gerrit). Hmm, not in LO 4.1.3.2 at least. LEFT OUTER JOIN : works RIGHT OUTER JOIN : works FULL OUTER JOIN : works so seems to be a regression introduced somewhere in 4.2 development
(In reply to comment #25) CORRECTION : I can reproduce on 4.1.3.2 when creating a new ODB file to the same datasource and using the same query (created from scratch). My tests in comment 24 were based on testing the ODB file I created in 4.2.0.3. Backporting to 4.1.x would be good if possible. Alex
Hmm, there's something weird going on. In comment 24, I reported that if I opened the file with the OJ query created in 4.2.0.3, I was able to open the same file in 4.1.3.2 and the OJ queries would now work. In comment 24, when I created a new ODB file in 4.1.3.2, the OJ queries failed to work. I shut down LO 4.1.3.2, restarted it and reloaded the ODB file I created in comment 24. OJ queries now work again !!! Also, if I re-open the file originally created in 4.2.0.3 with LO 4.2.0.3, and where OJ queries initially failed, they now work. No idea what is going on here. Alex
Lionel Elie Mamane committed a patch related to this issue. It has been pushed to "libreoffice-4-2": http://cgit.freedesktop.org/libreoffice/core/commit/?id=8682bd01aba5e04608ad1ca32b27ec06375a5b2c&h=libreoffice-4-2 fdo#69309 rename share/registry/postgresqlsdbc.xcd to postgresql.xcd It will be available in LibreOffice 4.2.3. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.