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
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
(earliest affected) release
Hardware: All All
: medium enhancement
Assignee: Not Assigned
Depends on:
Blocks: Database-Firebird-Migration
  Show dependency treegraph
Reported: 2019-05-07 16:35 UTC by Silvain Dupertuis
Modified: 2020-05-11 16:32 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:
Regression By:

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
Test database (2.62 MB, application/vnd.oasis.opendocument.database)
2020-04-07 17:26 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 Comment hidden (obsolete)
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 Comment hidden (obsolete)
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.
Comment 15 Silvain Dupertuis 2020-04-07 17:26:09 UTC
Created attachment 159398 [details]
Test database

A sample data base (with many tables, relations, queries, forms)
Comment 16 Silvain Dupertuis 2020-04-07 17:41:17 UTC
With my present version ( on Ubuntu 18.04), I no longer see any tool to migrate to Firebird and the migration is no longer suggested at start-up of a base document.

As for the problem I had with migration, I did find some workaround for the 3 things that prevented me to migrate. But it is still a lot of work:
1) get rid of all the {oj...} in the syntax of queries like "FROM { oj "Table1" LEFT OUTER JOIN "Table2"...}" - HSQL understand the query without the {oj} but stubbornly adds them when saving the query.
2) replace all the "CONCAT( aaa , bbb )" by the syntax "aaa || bbb"
3) replace the code I had made to output 1 or 0 in a complex query to check if one person is in a list (where the relationship is defined by a intermediate table to have a many to many relationship between lists and persons), so as to display which list a persons belongs to in a form. I had used a code with CONCAT to calculate this 0 or 1, and I can replace it with a CASE syntax (which works in both HSQL and Firebird)
The good thing is that for points 2) and 3), the new syntax valid for Firebird works also for HSQL
Comment 17 Robert Großkopf 2020-04-08 15:57:44 UTC
(In reply to Silvain Dupertuis from comment #16)
> With my present version ( on Ubuntu 18.04), I no longer see any tool
> to migrate to Firebird and the migration is no longer suggested at start-up
> of a base document.
You have to activate experimental functions. The migration tool has been set to experimental, because it doesn't work without problems and internal Firebird also isn't integrated into GUI as HSQLDB is.

You could find tips for the migrating to Firebird here:
Comment 18 Robert Großkopf 2020-04-08 16:09:21 UTC
One hint for the problem reported in the bug:
The with { oj .... } have created by the GUI and HSQLDB. When I create a query with RIGHT OUTER JOIN in the GUI with Firebird an LO it will work well. No brackets and no "oj" ist added.

Code in Firebird differs a little bit to code in HSQLDB. So I don't know if we should call this a bug. The migration tool doesn't solve any of the problems written down in the wiki. It must be solved by the user of the database.
Comment 19 Xisco Faulí 2020-05-11 14:03:46 UTC
Moving to NEW and changing to enhancement based on comment 18
Comment 20 Silvain Dupertuis 2020-05-11 16:32:41 UTC
I have prepared some REGEX transformation commands to solve the problems I have encountered so that I could made most of the adaptations by handling the internal text files of the ZIP odb file.

A - In queries and views - and the new queries all work fine in HSQLBD
1) get rid of the additional {of .... } in queries with OUTER JOIN
2) replace the CONCAT(X, CONCAT(' ', Y) form by X || ' ' || Y
3) replace the formulas I had found to display if somebody is in a list in a many-to-many relationships defined by an intermediate table by something of the kind

B - In Table descriptions
5) replace TIMESTAMP(0) by TIMESTAMP

It seems to me that these transfomrations (except #3) could be handled by the migration tool.

If anyone is interested, I can share these REGEX (which still needs checking, as I have tested them on one Database only, but the most complex one I have, and only as a preliminary test before I migrate the actuel Database used.