Bug Hunting Session
Bug 96516 - Left Join not available in Base SQL parser (and thus in Query UI) with unixodbc and sqlite
Summary: Left Join not available in Base SQL parser (and thus in Query UI) with unixod...
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
5.0.3.2 release
Hardware: x86-64 (AMD64) All
: medium normal
Assignee: Lionel Elie Mamane
URL:
Whiteboard: target:5.3.0 target:5.2.0.1 target:5.1.4
Keywords:
Depends on:
Blocks:
 
Reported: 2015-12-15 17:19 UTC by tim
Modified: 2016-10-25 18:54 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments
Test files (3.01 KB, application/zip)
2015-12-21 21:00 UTC, tim
Details

Note You need to log in before you can comment on or make changes to this bug.
Description tim 2015-12-15 17:19:24 UTC
Sqlite supports Inner Joins and Left Joins, but not Right Joins.

As I understand it ODBC supports all 3 types.

In BASE, using ODBC, the available joins are Inner and Right Joins, but not Left Joins. 

I wonder if this is an error?  

I'd use jdbc if I could get it working with sqlite but so far I haven't found a version that works (I use jdbc and mysql successfully).

If this is a deliberate design choice it is strange, since it means that only Inner Joins can be used with BASE, odbc and sqlite.
Comment 1 tim 2015-12-18 13:58:18 UTC
To try and check that this isn't me being daft I installed everything on Windows 7 (64 bit).

Same problem.  sqlite3  only allows LEFT joints.  BASE only allows RIGHT joins via ODBC to sqlite.  BASE does support LEFT joins on mysql (native and JDBC) - I use them for another database.

This is not, unfortunately, a case where I can simply reverse the table order and use the other JOIN.  It is fundamental syntax mismatch.  Whilst any RIGHT JOIN can be converted to a LEFT JOIN by reversing the table order, or vice-versa, the syntax incompatibility prevents this.  I have to use LEFT JOIN because that's the only one sqlite supports, but BASE will not support this syntax.

I get the impression that someone knew sqlite only supported one of the directional JOINs, but restricted BASE to use the wrong one.

I may, of course, be completely wrong.

This problem is a real pain.  Constructing SQL statements to get round this is possible (it took me a while, but I found a way for single columns), but when it involves many columns it gets really complicated.

If posting an example would help I will, but I can post all the odbc stuff.  One doesn't need more that two tables in the BASE database.  The more onerous bit is to install sqlite3, odbc, and configure it all.
Comment 2 tim 2015-12-21 21:00:49 UTC
Created attachment 121480 [details]
Test files

I attach 4 files to demonstrate this. The txt file is really a zip.  odbc.ini and odbcinst.ini are from an ubuntu 14.04 system (in /etc) with unixodbc and libsqliteodbc installed.  

odbc.ini will need editing to point to test.sqlite.

test.sqlite has two tables, t1 with an id key and text field, t2 with an id2 key, id key as a foreign key to t1, and a text field.

test.odb uses odbc to access test.sqlite (I assume it will need this link resetting on installation).  There is a query called test that attempts to find all entries in t1, including those that don't have entries in t2, but it cannot be done.  I want a LEFT join from t1 to t2, but BASE only supports RIGHT joins, which sqlite rejects.  IF I switch the tables round to use a LEFT join using BASE it fails.  

I can use pure SQL and a LEFT join so BASE ignores the syntax but the resulting query can't then be filtered using forms and so on, so doesn't work with an application that is being used specifically to employ such filters.

Help!
Comment 3 tim 2015-12-27 10:47:46 UTC
There is a workaround, suggested to me on the LibreOffice forum.

I can create Views in the sqlite3 database itself, using LEFT JOINs.  These work,  BASE forms based on them do allow form filters to be used, and they seem to work (albeit only after a few short tests).

This effectively confirms that the issue is a fault in BASE - it should allow LEFT JOINs, not RIGHT JOINs, for sqlite3, but the urgency is low.

It'd be nice to have confirmation at some point, but I know it's holiday time :)
Comment 4 tim 2016-01-28 13:30:37 UTC
Any chance anyone will look at this?  I'd like it to be confirmed as an issue if possible.
Comment 5 Alex Thurgood 2016-05-20 08:01:33 UTC
@Tim : I get the following error when I try and run the query "test" from your test ODB on Linux Mint 17.3 with 

Version: 5.0.3.2
Build ID: 1:5.0.3~rc2-0ubuntu1~trusty2
Locale : fr-FR (fr_FR.UTF-8)

Error message :

Statut SQL: HY000
Code d'erreur: 1

[unixODBC][SQLite]RIGHT and FULL OUTER JOINs are not currently supported (1)

Is this the problem you are seeing ? If so, I will confirm.
Comment 6 Alex Thurgood 2016-05-20 08:19:49 UTC
@Tim :

be I've misunderstood, but if I use the following query :

SELECT 't1'.'b', 't2'.'c' FROM 't1' LEFT OUTER JOIN 't2' ON 't1'.'id' = 't2'.'id'

I get the following result :
test1 test5
test2 test3
test2 test4
Comment 7 Alex Thurgood 2016-05-20 08:22:45 UTC
(In reply to Alex Thurgood from comment #6)
> @Tim :
> 
> be I've misunderstood, but if I use the following query :
> 
> SELECT 't1'.'b', 't2'.'c' FROM 't1' LEFT OUTER JOIN 't2' ON 't1'.'id' =
> 't2'.'id'
> 
> I get the following result :
> test1 test5
> test2 test3
> test2 test4

Ah, I was running my LJ query in direct SQL mode. If I try to switch this to Query GUI, I get an error message.
Comment 8 tim 2016-05-20 09:33:05 UTC
(In reply to Alex Thurgood from comment #5)
> @Tim : I get the following error when I try and run the query "test" from
> your test ODB on Linux Mint 17.3 with 
> 
> Version: 5.0.3.2
> Build ID: 1:5.0.3~rc2-0ubuntu1~trusty2
> Locale : fr-FR (fr_FR.UTF-8)
> 
> Error message :
> 
> Statut SQL: HY000
> Code d'erreur: 1
> 
> [unixODBC][SQLite]RIGHT and FULL OUTER JOINs are not currently supported (1)
> 
> Is this the problem you are seeing ? If so, I will confirm.
Yes, sqlite3 doesn't support RIGHT JOINS, Only LEFT JOINS

But BASE doesn't allow LEFT JOINS with sqlite3 and odbc.  If you use Direct SQL then you bypass the BASE error and it works, but using Direct SQL creates other problems and limitations in what you can do.
Comment 9 Alex Thurgood 2016-05-20 11:10:49 UTC
Thanks Tim, confirming. The problem is the built-in SQL parser. Correcting title accordingly.
Comment 10 Lionel Elie Mamane 2016-05-20 11:52:50 UTC
LibreOffice doesn't know it is connecting to SQLite, so it is not taking any SQLite-specific measure. It relies on what the ODBC driver is telling. It seems that the ODBC driver says it supports NONE of full, left, right, nested, inner, ... join. So LibreOffice tries to disable *all* outer joins, *both* left and right. The way this is done is buggy and leads to only left being disabled.

I'm fixing that, but you won't like the result either, since it will disable both left and right join. The ODBC driver needs to be fixed and to return at least SQL_OJ_LEFT when SQLGetInf(..., SQL_OJ_CAPABILITIES, ...) is called. Since the LibreOffice API (SDBC which is basically JDBC), contrary to ODBC, doesn't have the granularity to test left vs right support, this will reenable *both* left *and* right joins in the LibreOffice UI query editor.
Comment 11 tim 2016-05-20 12:20:36 UTC
(In reply to Lionel Elie Mamane from comment #10)
> LibreOffice doesn't know it is connecting to SQLite, so it is not taking any
> SQLite-specific measure. It relies on what the ODBC driver is telling. It
> seems that the ODBC driver says it supports NONE of full, left, right,
> nested, inner, ... join. So LibreOffice tries to disable *all* outer joins,
> *both* left and right. The way this is done is buggy and leads to only left
> being disabled.
> 
> I'm fixing that, but you won't like the result either, since it will disable
> both left and right join. The ODBC driver needs to be fixed and to return at
> least SQL_OJ_LEFT when SQLGetInf(..., SQL_OJ_CAPABILITIES, ...) is called.
> Since the LibreOffice API (SDBC which is basically JDBC), contrary to ODBC,
> doesn't have the granularity to test left vs right support, this will
> reenable *both* left *and* right joins in the LibreOffice UI query editor.

Since the ODBC driver seems to be at fault, could this be done at user choice in BASE - ie something to allow all joins regardless of what ODBC says and let the user take the consequences of trying something that may not work?
Comment 12 Lionel Elie Mamane 2016-05-20 13:00:10 UTC
(In reply to tim from comment #11)

> Since the ODBC driver seems to be at fault, could this be done at user
> choice in BASE - ie something to allow all joins regardless of what ODBC
> says and let the user take the consequences of trying something that may not
> work?

Yes, we could add yet another "advanced setting" that says "use joins even though the driver says it is not supported". I'll review and apply patches to this effect.
Comment 13 tim 2016-05-20 13:23:36 UTC
(In reply to Lionel Elie Mamane from comment #12)
> (In reply to tim from comment #11)
> 
> > Since the ODBC driver seems to be at fault, could this be done at user
> > choice in BASE - ie something to allow all joins regardless of what ODBC
> > says and let the user take the consequences of trying something that may not
> > work?
> 
> Yes, we could add yet another "advanced setting" that says "use joins even
> though the driver says it is not supported". I'll review and apply patches
> to this effect.
Sounds good to me.
Comment 14 Commit Notification 2016-05-27 13:52:25 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=0df3760bc2035a54454db9fe2774dca58c58593f

tdf#96516 do not increment position counter when an entry is removed

It will be available in 5.3.0.

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 15 tim 2016-05-28 11:42:20 UTC
I'll test as soon as a pre-release is available on ubuntu (which may be a while yet).
Comment 16 Commit Notification 2016-05-31 09:50:17 UTC
Lionel Elie Mamane committed a patch related to this issue.
It has been pushed to "libreoffice-5-2":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=55f7843316b3ec24b008771cfe41afa5bcb5ea50&h=libreoffice-5-2

tdf#96516 do not increment position counter when an entry is removed

It will be available in 5.2.0.1.

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 17 Commit Notification 2016-05-31 09:50:24 UTC
Lionel Elie Mamane committed a patch related to this issue.
It has been pushed to "libreoffice-5-1":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=4fb26b371c52e856ef05d4c67e9219117eaf3225&h=libreoffice-5-1

tdf#96516 do not increment position counter when an entry is removed

It will be available in 5.1.4.

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 18 tim 2016-06-04 10:47:00 UTC
(In reply to Commit Notification from comment #17)
> Lionel Elie Mamane committed a patch related to this issue.
> It has been pushed to "libreoffice-5-1":
> 
> http://cgit.freedesktop.org/libreoffice/core/commit/
> ?id=4fb26b371c52e856ef05d4c67e9219117eaf3225&h=libreoffice-5-1
> 
> tdf#96516 do not increment position counter when an entry is removed
> 
> It will be available in 5.1.4.
> 
> 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.
I've just installed 5.1.4 rc1, and I can now do Left Joins.  Thanks very much!

I looked for a setting to allow such joins but didn't find one.  Did you find a way round having to have an extra setting?

I'll close this once I know if there's a setting I need to test.
Comment 19 Lionel Elie Mamane 2016-06-04 15:37:04 UTC
(In reply to tim from comment #18)

> I've just installed 5.1.4 rc1, and I can now do Left Joins.  Thanks very
> much!

I'm surprised. This is not the effect of my patch. As I wrote, it will keep you from doing left or right joins, unless you have a different version of the ODBC driver that does declare it is capable of doing left joins.

> I looked for a setting to allow such joins but didn't find one.  Did you
> find a way round having to have an extra setting?

No, I wrote the setting can be done, and "I take patches", but I didn't do it.
Comment 20 Commit Notification 2016-06-04 15:42:01 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=4005d61cd1bdd71bb74c316f3ad30d23ba459b90

tdf#96516 fixup: when removing entry, entry count does not stay constant

It will be available in 5.3.0.

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 21 tim 2016-06-04 16:07:18 UTC
(In reply to Commit Notification from comment #20)
> Lionel Elie Mamane committed a patch related to this issue.
> It has been pushed to "master":
> 
> http://cgit.freedesktop.org/libreoffice/core/commit/
> ?id=4005d61cd1bdd71bb74c316f3ad30d23ba459b90
> 
> tdf#96516 fixup: when removing entry, entry count does not stay constant
> 
> It will be available in 5.3.0.
> 
> 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.
Not sure what this means or how I would test it.
Comment 22 tim 2016-06-04 16:18:17 UTC
(In reply to Lionel Elie Mamane from comment #19)
> (In reply to tim from comment #18)
> 
> > I've just installed 5.1.4 rc1, and I can now do Left Joins.  Thanks very
> > much!
> 
> I'm surprised. This is not the effect of my patch. As I wrote, it will keep
> you from doing left or right joins, unless you have a different version of
> the ODBC driver that does declare it is capable of doing left joins.
> 
> > I looked for a setting to allow such joins but didn't find one.  Did you
> > find a way round having to have an extra setting?
> 
> No, I wrote the setting can be done, and "I take patches", but I didn't do
> it.
Ah Ha!  I had edited the raw sql to include a LEFT join.  When going back to the GUI, it then showed me that it was a left join, even though the drop-down didn't include it.  It isn't saying it is using 'Run SQL Command Directly', but it does work correctly.

When I re-edited the raw sql to take the Left option out it was then no longer seen on the GUI.

So, as I understand it, the current situation is that using just the GUI neither Left nor Right joins are available with ODBC.  If I want Left joins I can edit the raw SQL, and then it will show in the GUI and do what I need.

That sounds workable, but it might be useful for others if it was hinted at somewhere in the Help.
Comment 23 Lionel Elie Mamane 2016-06-05 07:41:34 UTC
(In reply to tim from comment #22)
> (In reply to Lionel Elie Mamane from comment #19)
>> (In reply to tim from comment #18)

>>> I've just installed 5.1.4 rc1, and I can now do Left Joins.  Thanks very
>>> much!

>> I'm surprised. This is not the effect of my patch. As I wrote, it will keep
>> you from doing left or right joins, unless you have a different version of
>> the ODBC driver that does declare it is capable of doing left joins.

> So, as I understand it, the current situation is that using just the GUI
> neither Left nor Right joins are available with ODBC.

In the GUI, neither left nor right joins are available with *this* ODBC driver for sqlite, because it reports that it supports neither. As soon as it is modified to report that it supports either, due to a limitation in the LibreOffice architecture (inherited from JDBC), *both* left and right joins will be enabled in the GUI, although right joins will obviously not work (with sqlite).
Comment 24 tim 2016-06-05 10:28:10 UTC
Just to be clear, the Left Join does display on the GUI when it has been set by editing the raw SQL and setting the join there.  It just isn't available in the drop-down.
Comment 25 Commit Notification 2016-06-06 08:33:38 UTC
Lionel Elie Mamane committed a patch related to this issue.
It has been pushed to "libreoffice-5-1":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=22ea5aef84996520062b2c5d58757cd6f00c98ee&h=libreoffice-5-1

tdf#96516 fixup: when removing entry, entry count does not stay constant

It will be available in 5.1.5.

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 26 Julien Nabet 2016-06-06 08:36:31 UTC Comment hidden (no-value)
Comment 27 tim 2016-06-06 08:54:45 UTC Comment hidden (no-value)
Comment 28 Julien Nabet 2016-06-06 09:01:56 UTC Comment hidden (no-value)
Comment 29 tim 2016-06-06 09:28:25 UTC Comment hidden (no-value)
Comment 30 Commit Notification 2016-06-07 08:05:41 UTC
Lionel Elie Mamane committed a patch related to this issue.
It has been pushed to "libreoffice-5-2":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=a74e3a5be5586e1cad7fb20a8bb903924f408df3&h=libreoffice-5-2

tdf#96516 fixup: when removing entry, entry count does not stay constant

It will be available in 5.2.0.1.

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 31 tim 2016-06-16 14:05:52 UTC
I wish I could try 5.2, but the fates are against me.  

The official Libreoffice collection of debs (i.e. not an ubuntu ppa) doesn't install properly on ubuntu 16.04 - there's some conflict in the installation that causes it to fail.  

There is a test 'snap' version which looks very interesting for the future, but it also has problems at present with some file accesses.

I guess I will have to wait for an ubuntu ppa version.
Comment 32 tim 2016-06-16 16:52:48 UTC
I managed to overcome the 5.2 installation bug.

The behaviour is now as you stated.  No Left or Right join is offered when editing the query with the GUI. A Left join can be specified in the underlying SQL, and it does work (without using Direct SQL).


Is it OK for me to close this now? I'm not sure about your entry count issues.