A table stored in SQLite3 can have a compound primary key of two fields in which one of the fields is empty, provided that the empty field value is blank ("") and not NULL. Using an ODBC connection with LO Base as the front end, LO does not handle the records correctly, either as simple table display or within a form. It gets the number of records wrong (not counting those with blank values), but can scroll through the whole table. Equally, asking it to go (by pushbutton) to LAST or NEW typically returns the error "Failed to refetch row".
Steps to Reproduce:
1. See description above.
See description above.
LO should not be confused when navigating such a table!
User Profile Reset: No
Version: 184.108.40.206 / LibreOffice Community
Build ID: 499f9727c189e6ef3471021d6132d4c694f357e5
CPU threads: 8; OS: Mac OS X 10.13.6; UI render: default; VCL: osx
Locale: en-GB (en_GB.UTF-8); UI: en-US
Can't test as SQLite2/3 on macOS has been inaccessible to LO, either by ODBC or directly/JDBC, for many years now.
At a guess, ("") is being interpreted as NULL, thus any combination of one field with NULL is also intepreted as NULL ?
@PhilipK : how have you set up your ODBC connection to connect to the sqlite3 database ?
Are you using an Intel Mac ?
(In reply to Alex Thurgood from comment #2)
> @PhilipK : how have you set up your ODBC connection to connect to the
> sqlite3 database ?
> Are you using an Intel Mac ?
The Mac configuration is
Model Name: MacBook Pro
Model Identifier: MacBookPro11,2
Processor Name: Intel Core i7
Processor Speed: 2 GHz
Number of Processors: 1
Total Number of Cores: 4
L2 Cache (per Core): 256 KB
L3 Cache: 6 MB
Memory: 8 GB
Boot ROM Version: 220.127.116.11.0
SMC Version (system): 2.18f15
Serial Number (system): C02N12V3FD56
Hardware UUID: 463F327B-320D-599F-8880-D9C230574371
The ODBC driver is sqlite3-odbc-0.93, installed from http://www.ch-werner.de/sqliteodbc/
[Automated Action] NeedInfo-To-Unconfirmed
I have tried to get such a table in LO GUI. It is impossible to set a field as an empty string. It will be set to NULL. So I created it through Tools → SQL. I could see all rows in table view. The counting of the rows is right here with LO 18.104.22.168 on OpenSUSE 15.3 and the ODBC driver, which is packed for this Linux version.
Could you offer an example database for this?
Created attachment 178632 [details]
SQLite3 database (one table)
Created attachment 178633 [details]
LO front-end (one form) to link to db
If opened in SQLite Studio, the table shows clearly where there are nulls and where there are blanks.
(In reply to PhilipK from comment #3)
Thansk for the information.
> The ODBC driver is sqlite3-odbc-0.93, installed from
No version for Arm, so can't test at the moment.
Intel iodbc doesn't work on Apple Arm M1 with current LO releases due to an incorrect DYLD path search (bug 138990).
FWIW, in my experience, the sqlite ODBC driver for Mac has always been buggy with regard to LO.
I also seem to recall that LO requires correct scroll cursor management in order to display the dataset correctly, and I seem to recall that either sqlite, or the ODBC driver implementation for SQLite, only supported the FORWARD_SCROLL_CURSOR, which isn't enough to provide a fully working implementation.
I have tested this:
Opened the table and switch to last row. Won't get all rows then, only some of the rows which contain an empty string as combined primary key.
Opened a query and let me show the primary keys. If the query isn't editable (only one key) it will show all rows (141). If the query is editable, it will need pressing "last row" two times to switch to a wrong rows count (like 135 or something else).
Base will set empty fields to NULL, and this seems to be the problem for the GUI. I would never use such a table for input new data, but you could list this data when switching to direct SQL and it works. You could also use direct SQL (or macros) to input new data.
It is a bug to show the wrong number of rows, but I don't know how to handle this without throwing away all the features, which will work with NULL for an empty field. I would prefer to open a table write protected, if a primary key contains an empty string.
Tested with OpenSUSE 15.3 64bit rpm Linux and LO 22.214.171.124
See also the discussion in bug 82688 about ODBC conformance and the actual support provided by Christian Werner's ODBC driver.
Thank you both for your input!
I am getting a little out of my depth here. This table is part of a db composed of ten tables and now containing over 42,000 records. I have been accessing/augmenting it for over a year now with LO, and the ODBC has worked perfectly well for me hitherto, apart from this one area.
Incidentally, I have always used SQLIte Studio for table definition, and have never attempted to create or modify table structures through the LO interface (because that did seem to be problematic).
For me, I think that the simplest solution will be to put a dummy value ("-") in those empty fields, which will be slightly annoying but should avoid future problems if this material eventually gets into the public domain on another platform with another front end.
To avoid any confusion with what I previously wrote about cursors, I've just experimented again with a JDBC connection, and it is "TYPE_FORWARD_ONLY" cursors that are supported by SQLite, whereas Base wants some other kind of cursor (probably scroll insensitive) The ODBC driver must get around this requirement somehow, by telling LO that it supports scroll insensitive cursors (even if SQLite doesn't).
The scroll cursor management is one possible reason why you are seeing inconsistent results in the number of records displayed in the resultset. Another possible reason is that the ODBC function code within LO is incapable of handling combined field primary keys having possible NULL values.