Bug 69309 - Other: Outer joins do not work with PostgreSQL
Summary: Other: Outer joins do not work with PostgreSQL
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
4.1.1.2 release
Hardware: Other macOS (All)
: medium normal
Assignee: Lionel Elie Mamane
URL:
Whiteboard: BSA target:4.3.0 target:4.2.3
Keywords:
Depends on:
Blocks:
 
Reported: 2013-09-13 09:34 UTC by Marc Balmer
Modified: 2014-02-24 15:57 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Marc Balmer 2013-09-13 09:34:35 UTC
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
Comment 1 Alex Thurgood 2013-09-13 13:03:20 UTC
Isn't this inherited behaviour from OOo 3 ? 

Or is this a regression over something that worked in previous versions of LibreOffice ?


Alex
Comment 2 Alex Thurgood 2013-09-13 13:05:12 UTC
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
Comment 3 Alex Thurgood 2013-09-13 13:11:16 UTC
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
Comment 4 Marc Balmer 2013-09-13 19:24:56 UTC
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.
Comment 5 Marc Balmer 2013-09-14 08:33:30 UTC
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.
Comment 6 Robert Großkopf 2013-09-14 17:41:48 UTC
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" }
Comment 7 Marc Balmer 2013-09-14 19:11:46 UTC
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.
Comment 8 Robert Großkopf 2013-09-15 07:14:23 UTC
(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.
Comment 9 Marc Balmer 2013-09-15 07:25:08 UTC
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.
Comment 10 Robert Großkopf 2013-09-15 07:39:06 UTC
(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 ...
Comment 11 Marc Balmer 2013-09-15 07:44:10 UTC
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.
Comment 12 Marc Balmer 2013-09-15 07:54:00 UTC
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...
Comment 13 Marc Balmer 2013-09-15 09:39:06 UTC
(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.
Comment 14 Robert Großkopf 2013-09-15 10:24:52 UTC
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?
Comment 15 Marc Balmer 2013-09-15 10:26:02 UTC
It's the one that comes bundled with LibreOffice, I had not to install a separate driver.
Comment 16 Lionel Elie Mamane 2013-09-15 12:41:43 UTC
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.
Comment 17 Lionel Elie Mamane 2013-09-15 12:59:27 UTC
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?
Comment 18 Lionel Elie Mamane 2013-09-15 13:07:09 UTC
(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.
Comment 19 Alex Thurgood 2014-02-22 09:30:01 UTC
As per Lionel's request in comment 17, setting to NEEDINFO
Comment 20 Alex Thurgood 2014-02-22 19:53:36 UTC
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.
Comment 21 Alex Thurgood 2014-02-22 20:05:12 UTC
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
Comment 22 Lionel Elie Mamane 2014-02-22 21:38:31 UTC
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"
Comment 23 Commit Notification 2014-02-22 21:44:16 UTC
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.
Comment 24 Lionel Elie Mamane 2014-02-22 21:48:49 UTC
(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).
Comment 25 Alex Thurgood 2014-02-23 17:06:42 UTC
(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
Comment 26 Alex Thurgood 2014-02-23 17:43:36 UTC
(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
Comment 27 Alex Thurgood 2014-02-23 18:17:19 UTC
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
Comment 28 Commit Notification 2014-02-24 15:57:30 UTC
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.