Bug 125162 - SQL Syntax incorrect for LEFT / RIGHT JOIN when migrating from Base to Firebird
Summary: SQL Syntax incorrect for LEFT / RIGHT JOIN when migrating from Base to Firebird
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
(earliest affected) release
Hardware: All All
: medium normal
Assignee: Not Assigned
Depends on:
Blocks: Database-Firebird-Migration
  Show dependency treegraph
Reported: 2019-05-07 16:35 UTC by Silvain Dupertuis
Modified: 2019-11-26 09:49 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:

Test base with 3 tables and queries with INNER JOINS (83.44 KB, application/vnd.oasis.opendocument.database)
2019-05-22 09:10 UTC, Silvain Dupertuis
Example of modifications of a query when migrating to Firebird (1.36 KB, text/plain)
2019-11-25 14:47 UTC, Silvain Dupertuis

Note You need to log in before you can comment on or make changes to this bug.
Description Silvain Dupertuis 2019-05-07 16:35:03 UTC
When I migrated a database from HSQLBD to Firebird, I had many queries who could not load. I tried different things until I discovered that the problem came from the syntax for joins.
The syntax 
FROM { oj "..." RIGHT OUTER JOIN "...." ON "..."."..." = ...}
coming from queries build previously with HSQLBD
is not recognised and the query refuses to load
On just needs to get rid of "oj" and replaces braces with parenthesis or remove them to get.
FROM ( "..." RIGHT OUTER JOIN "...." ON "..."."..." = ...)
or even
FROM ( "..." RIGHT OUTER JOIN "...." ON "..."."..." = ...)

Maybe the conversion tool could take care of correcting the syntax - or else the user should get information about how to correct his queries.
Comment 1 Julien Nabet 2019-05-07 18:48:22 UTC
You might be interested in this link:
Comment 2 Silvain Dupertuis 2019-05-13 11:00:28 UTC
For the FROM { oj ... } but, I tried to apply the regex
FROM (\1)
it works if there is no embedding similar construct
Comment 3 Silvain Dupertuis 2019-05-22 09:07:40 UTC
Migrating to Firbird, transformation of queries with JOIN works well with the graphic editor:
replaced by

But using the graphic query editor on such queries breaks the syntax
Either the additional table is ignored
Either the editer reconstruct the old syntax and breaks the query

So one has to use exclusively the SQL editor for queries with OUTER JOIN
Comment 4 Silvain Dupertuis 2019-05-22 09:10:26 UTC
Created attachment 151588 [details]
Test base with 3 tables and queries with INNER JOINS

The base contains a persones table and a list table related with a intermediate table for a many-to-many relationship.
Comment 5 Silvain Dupertuis 2019-05-22 09:11:59 UTC
I meant: works well with the SQL editer, not the graphic editor...
Comment 6 Julien Nabet 2019-05-22 09:20:41 UTC
Uncc myself since I can't help here.
Comment 7 Alex Thurgood 2019-05-27 12:06:47 UTC

Can you provide us please with the corresponding hsqldb before migration so that we can try and get some debug information ?
Comment 8 Alex Thurgood 2019-05-27 12:08:09 UTC
I seem to recall that there was a recent-ish change that might have affected the {OJ} parsing.
Comment 9 Alex Thurgood 2019-05-28 07:07:32 UTC
@Silvain :

the "{oj" construct is a leftover from ODBC compliant processing.

There used to be a property under Advanced Properties of the database connection which could be toggled on/off. This Advanced Properties dialog appears to have been moved to the Advanced Configuration tool.

I would suggest 2 things :

- check in your original hsqldb ODB file whether the Advanced Property ODBC compliant strings is ticked - if it is, deactivate it and then try the migration to Firebird ;

- check the Advanced Properties of your Firebird database to see whether this option is also present, and try switching it off.

If you can't find these properties under the Advanced Properties dialog of the ODB, try setting them via the Advanced Configuration tool (if at all possible). Quite frankly IMO, the whole "removing the Advanced Properties" dialog by the UI/UX group to make the UI easier to use was a completely pointless exercise, and only serves to frustrate database admins.
Comment 10 Alex Thurgood 2019-05-28 07:10:11 UTC
How to find the advanced configuration tool:

Select Tools->Options->Advanced
Click on "Expert Configuration" 
When that dialog opens, enter 'hsqldb' or 'firebird' and click on 'search'
The setting you want to verify is 'EnableOuterJoinEscape'.
Comment 11 QA Administrators 2019-11-25 03:31:23 UTC
Dear Silvain Dupertuis,

This bug has been in NEEDINFO status with no change for at least
6 months. Please provide the requested information as soon as
possible and mark the bug as UNCONFIRMED. Due to regular bug
tracker maintenance, if the bug is still in NEEDINFO status with
no change in 30 days the QA team will close the bug as INSUFFICIENTDATA
due to lack of needed information.

For more information about our NEEDINFO policy please read the
wiki located here:

If you have already provided the requested information, please
mark the bug as UNCONFIRMED so that the QA team knows that the
bug is ready to be confirmed.
Thank you for helping us make LibreOffice even better for everyone!

Warm Regards,
QA Team

Comment 12 Silvain Dupertuis 2019-11-25 14:47:41 UTC
Created attachment 156098 [details]
Example of modifications of a query when migrating to Firebird
Comment 13 QA Administrators 2019-11-26 03:33:05 UTC
[Automated Action] NeedInfo-To-Unconfirmed
Comment 14 Alex Thurgood 2019-11-26 09:49:28 UTC
@Sylvain : without an original hsqldb ODB file, we are getting nowhere with this.

Please provide an embedded HSQLDB filed with which we can test the migration to confirm the problem, and potentially debug the problematic code.