Download it now!
Bug 117897 - base/postgres seem to use ODBC brace construct, query fail (...FROM { oj "field"...)
Summary: base/postgres seem to use ODBC brace construct, query fail (...FROM { oj "fie...
Status: RESOLVED WORKSFORME
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
6.0.4.2 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2018-05-30 11:16 UTC by Peter Gervai
Modified: 2019-05-28 07:08 UTC (History)
1 user (show)

See Also:
Crash report or crash signature:


Attachments
screen shot of expert configuration settings postgres (79.46 KB, image/png)
2018-06-01 19:08 UTC, Drew Jensen
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Peter Gervai 2018-05-30 11:16:08 UTC
Description:
Using postgres connectot LO seems to use ODBC brace constructs, which causes the query fail.
It was triggered by creating a simple query joining two tables.

Seems " { oj " is some odbc macro wants to be expanded, which doesn't happen in the postgres driver.

The generated SQL:
SELECT "t3"."nev", "t3"."szam", "t3-nev"."AccountType", "t3-nev"."Description", "t3-nev"."AccountNumber", "t3-cim"."varos", "t3-cim"."utca", "t3"."jog1", "t3"."jog2", "t3"."jog3", "t3"."jog4" FROM { oj "public"."t3-cim" "t3-cim" RIGHT OUTER JOIN "public"."t3" "t3" ON "t3-cim"."szam" = "t3"."szam" LEFT OUTER JOIN "public"."t3-nev" "t3-nev" ON "t3-nev"."AccountName" = "t3"."nev" } ORDER BY "t3"."nev" ASC, "t3"."szam" ASC


Steps to Reproduce:
see above

Actual Results:  
error popup.

Expected Results:
running the query.


Reproducible: Always


User Profile Reset: No



Additional Info:


User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:62.0) Gecko/20100101 Firefox/62.0
Comment 1 Drew Jensen 2018-05-30 13:36:49 UTC
I don't have postgress running here so can't check this but;

That is (or was) controlled by an optional setting.

With Base open select Database->Advanced Settings->Special Settings

Remove the tick mark from the option:

Use Outer Join Syntax (OJ)

Save that and rerun your query.

That should fix it.
Comment 2 Alex Thurgood 2018-05-31 06:45:57 UTC
Setting to NEEDINFO pending feedback from original poster

@Peter : could you try Drew's suggestion and report back ?

I also seem to recall that issues have been reported in the past with some outer join constructs. A trawl through bugzilla might help here.
Comment 3 Alex Thurgood 2018-05-31 06:50:33 UTC
See, for example, bug 108375 of a situation where Outer Joins order gets messed up.
Comment 4 Alex Thurgood 2018-05-31 06:51:40 UTC
And bug 83121 which looks pretty similar.
Comment 5 Peter Gervai 2018-05-31 16:43:30 UTC
I really dislike this.

So, I have restarted everything a few times (due to other stuff), and I haven't saved the problematic query, it seem; since now no matter what order I try to click together I cannot reproduce it. 

Additionally I cannot select "Advanced", it's grayed out, but that may be due to Postgres connector not supporting that.

But I cannot reproduce anymore; and before reporting I upgraded (from 1.6.0.2) to see whether it's been fixed (and it wasn't). But now it have healed itself, which is pretty rude in the context of bugfixing.

If you need anything from me just ask; if you feel like that's too few to find the problem which doesn't seem to be reproducible anymore then feel free to close, and I'll reopen whenever I observe similar problem. (Honestly, I hardly ever use Base since it's pretty underdocumented, so it may be the reason I haven't observed the bug before. )
Comment 6 Drew Jensen 2018-06-01 19:08:17 UTC
Created attachment 142480 [details]
screen shot of expert configuration settings postgres

(In reply to Peter Gervai from comment #5)
...
> Additionally I cannot select "Advanced", it's grayed out, but that may be
> due to Postgres connector not supporting that.
...
> 
> But I cannot reproduce anymore; and before reporting I upgraded (from
> 1.6.0.2) to see whether it's been fixed (and it wasn't). But now it have
> healed itself, which is pretty rude in the context of bugfixing.
> 
...
> (Honestly, I hardly ever use Base since it's pretty underdocumented, so it
> may be the reason I haven't observed the bug before. )

Just to follow up with you, with the menu access to that configuration no longer available you can still change the couple of settings for that driver using the 'Expert Configuraton' tool. 

The screen shot attached is from Libo 6.0.4.

Select Tools->Options->Advanced
Click on "Expert Configuration" 
When that dialog open, enter 'postgres' and click on 'search'
The setting you want to check is 'EnableOuterJoinEscape'.

The default setting, as found after installing that, was 'false'. 

Otherwise, given that it seems to be working for you now I will go ahead and close as this as working. 

Thanks