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.
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.
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!
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 :)
Any chance anyone will look at this? I'd like it to be confirmed as an issue if possible.
@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.
@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
(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.
(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.
Thanks Tim, confirming. The problem is the built-in SQL parser. Correcting title accordingly.
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.
(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?
(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.
(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.
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.
I'll test as soon as a pre-release is available on ubuntu (which may be a while yet).
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.
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.
(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.
(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.
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.
(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.
(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.
(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).
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.
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.
Simplify targets a bit.
(In reply to Julien Nabet from comment #26) > Simplify targets a bit. I've no idea what you are talking about ('entries' - what entries, for what?), but it doesn't matter.
(In reply to tim from comment #27) > (In reply to Julien Nabet from comment #26) > > Simplify targets a bit. > I've no idea what you are talking about ('entries' - what entries, for > what?), but it doesn't matter. I didn't say "entries" but "targets". "Target" is a special word in Whiteboard field of the bugtracker. Instead of indicating that it would be ok from 5.1.4 and 5.1.5, I just indicate that it would be ok from 5.1.4 So it was just an "administrative" bugtracker change.
(In reply to Julien Nabet from comment #28) > (In reply to tim from comment #27) > > (In reply to Julien Nabet from comment #26) > > > Simplify targets a bit. > > I've no idea what you are talking about ('entries' - what entries, for > > what?), but it doesn't matter. > > I didn't say "entries" but "targets". > "Target" is a special word in Whiteboard field of the bugtracker. > Instead of indicating that it would be ok from 5.1.4 and 5.1.5, I just > indicate that it would be ok from 5.1.4 > So it was just an "administrative" bugtracker change. Sorry, I'm being spectacularly dim and ill-informed. I'll stop commenting.
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.
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.
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.