Description: 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. 2. 3. 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: 7.2.5.2 / 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
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: 162.0.0.0.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 7.3.1.2 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 > 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.
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 7.3.1.2
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.
Dear PhilipK, To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the information from Help - About LibreOffice. If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice. Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from https://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to 'inherited from OOo'; 4b. If the bug was not present in 3.3 - add 'regression' to keyword Feel free to come ask questions or to say hello in our QA chat: https://web.libera.chat/?settings=#libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug