Bug 147730 - LO Base does not handle compound primary key if one field is empty ("") SQLite3 / ODBC
Summary: LO Base does not handle compound primary key if one field is empty ("") SQLit...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
(earliest affected) release
Hardware: All macOS (All)
: medium normal
Assignee: Not Assigned
Depends on:
Reported: 2022-03-02 10:33 UTC by PhilipK
Modified: 2022-03-03 18:15 UTC (History)
2 users (show)

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

SQLite3 database (one table) (11.16 MB, application/vnd.sqlite3)
2022-03-03 09:53 UTC, PhilipK
LO front-end (one form) to link to db (29.37 KB, application/vnd.oasis.opendocument.database)
2022-03-03 09:54 UTC, PhilipK

Note You need to log in before you can comment on or make changes to this bug.
Description PhilipK 2022-03-02 10:33:15 UTC
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.

Actual Results:
See description above.

Expected Results:
LO should not be confused when navigating such a table!

Reproducible: Always

User Profile Reset: No

Additional Info:
Version: / 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
Calc: threaded
Comment 1 Alex Thurgood 2022-03-02 15:04:00 UTC
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 ?
Comment 2 Alex Thurgood 2022-03-02 15:07:58 UTC
@PhilipK : how have you set up your ODBC connection to connect to the sqlite3 database ?

Are you using an Intel Mac ?
Comment 3 PhilipK 2022-03-02 20:58:50 UTC
(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:
  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/
Comment 4 QA Administrators 2022-03-03 03:40:55 UTC Comment hidden (obsolete)
Comment 5 Robert Großkopf 2022-03-03 08:03:04 UTC
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 on OpenSUSE 15.3 and the ODBC driver, which is packed for this Linux version.

Could you offer an example database for this?
Comment 6 PhilipK 2022-03-03 09:53:31 UTC
Created attachment 178632 [details]
SQLite3 database (one table)
Comment 7 PhilipK 2022-03-03 09:54:42 UTC
Created attachment 178633 [details]
LO front-end (one form) to link to db
Comment 8 PhilipK 2022-03-03 09:56:31 UTC
If opened in SQLite Studio, the table shows clearly where there are nulls and where there are blanks.
Comment 9 Alex Thurgood 2022-03-03 10:39:58 UTC
(In reply to PhilipK from comment #3)

Thansk for the information.

> The ODBC driver is sqlite3-odbc-0.93, installed from
> http://www.ch-werner.de/sqliteodbc/

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.
Comment 10 Robert Großkopf 2022-03-03 10:43:11 UTC
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
Comment 11 Alex Thurgood 2022-03-03 11:29:19 UTC
See also the discussion in bug 82688 about ODBC conformance and the actual support provided by Christian Werner's ODBC driver.
Comment 12 PhilipK 2022-03-03 12:14:41 UTC
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.
Comment 13 Alex Thurgood 2022-03-03 12:20:18 UTC
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.