Bug 47520 - ODBC with sqliteodbc: primary key field mostly empty, broken scolling
Summary: ODBC with sqliteodbc: primary key field mostly empty, broken scolling
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
3.5.1 release
Hardware: Other All
: medium major
Assignee: Lionel Elie Mamane
URL:
Whiteboard: BSA target:3.6.0 target:3.5.5 target:...
Keywords: regression
Depends on: 48345
Blocks: 50575
  Show dependency treegraph
 
Reported: 2012-03-19 08:56 UTC by Heinz Repp
Modified: 2012-12-06 18:48 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
sql statements setting up demonstration SQLite3 database (809 bytes, text/plain)
2012-04-09 11:54 UTC, Terrence Enger
Details
demonstration sqlite3 database file (4.00 KB, application/octet-stream)
2012-04-09 11:55 UTC, Terrence Enger
Details
definition of ODBC data source (33.24 KB, image/png)
2012-04-09 11:56 UTC, Terrence Enger
Details
table with integer primary key displays data (38.78 KB, image/png)
2012-04-09 11:57 UTC, Terrence Enger
Details
table with timestamp primary key does not display data (32.26 KB, image/png)
2012-04-09 11:57 UTC, Terrence Enger
Details
testcase that shows the error (3.00 KB, application/octet-stream)
2012-04-19 07:43 UTC, Heinz Repp
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Heinz Repp 2012-03-19 08:56:11 UTC
Problem description:

I have multiple sqlite3 databases that I use:
- on Windows XP SP3 with Ch. Werner's sqliteodbc driver v. 0.85 and MDAC 2.8 Sp1
- on Ubuntu 10.04 Lucid with Ch. Werner's sqliteodbc driver v. 0.80 and unixodbc 2.2.11-21

On both systems I see with Libreoffice 3.5.1 and at least 5 different tables of the connected Base databases:

Opening the table (or using the preview windows) shows the primary key field mostly empty, to be exact: the 41th row has an entry, the following not; when scrolling down to later rows and then back the view gets complete out of sync, having sometimes alternating empty rows and one entry repeated every other row.

Those tables have in common:
all fields are defined "NOT NULL PRIMARY KEY" and are either char(n) or varchar(n) datatype, the Base database has them as "Text (varchar)"

All databases open flawlessly in Libreoffice 3.4.5. Seems something broke severely in the ODBC handling.

I experienced another issue, but that is probably a different bug:
Using a version newer than 0.85 of the sqliteodbc driver (only on Windows, there is no newer version than 0.80 for Lucid) results in some of my tables showing only 4 empty rows, and this happened already with OpenOffice 3 and later with Libreoffice 3.3 and 3.4. Those tables have a timedate field as primary key, and Christian Werner had changed the formatting of timedates between 0.85 and 0.86 (millionth of seconds I believe) and told me, that in this case the the handling of Base was in error, and I should avoid using timedates as primary key, but that was no option. Now new with Libreoffice 3.5.1: when I try to open one of those tables with a timedate primary key Libreoffice reproducibly crashes.

Steps to reproduce:
see above: I try to find a minimal database that shows this behavior, as the ones that do hold private data.

Platform (if different from the browser): presumably all, at least Ubuntu Lucid and Windows XP
              
Browser: Mozilla/5.0 (X11; Ubuntu; Linux i686; rv:11.0) Gecko/20100101 Firefox/11.0
Comment 1 Heinz Repp 2012-03-19 09:01:03 UTC
adding keyword regression (3.4.5->3.5.1) and setting importance major as data-loss is possible
Comment 2 Terrence Enger 2012-03-22 10:11:36 UTC
Heinz,

I think your "probably a different bug" deserves a bug report of its own.  If they turn out to be the same (but they look quite different to me), it is easy to mark one as a duplicate of the other.  Keeping two bugs separate in one discussion, takes ongoing effort.

I look forward to seeing your demonstration database.
Comment 3 Terrence Enger 2012-04-09 11:52:34 UTC
I have succeeded in demonstrating the first described problem with

    ubuntu-natty (11.04) 32-bit
    libsqliteodbc version 0.87.2
    libsqlite3-0 version 2.8.17-6build2

and LibreOffice master commit 1199713, pulled 2012-04-01 and
configured with

    --disable-mozilla
    --enable-symbols
    --enable-dbgutil
    --enable-crashdump
    --disable-build-mozilla
    --without-system-postgresql
    --enable-python=internal


Here are the steps. Of course, change the name of my working directory
"/home/terry/lo_hacking/notes/bug_047520/" to something that you find
convenient.

(1) Download the attached file init.sql to your working directory.

(2) In your working directory, use the attached file init.sql to
    create an example database:

        $ sqlite3 bug_047520.db
        SQLite version 3.7.4
        Enter ".help" for instructions
        Enter SQL statements terminated with a ";"
        sqlite> .read init.sql
        Error: near line 5: no such table: byNr
        Error: near line 6: no such table: byTs

    Alternatively, download the attached database, bug_047520.db.

(3) Observe the created tables.

        $ sqlite3 bug_047520.db
        SQLite version 3.7.4
        Enter ".help" for instructions
        Enter SQL statements terminated with a ";"
        sqlite> .mode column
        sqlite> select * from byNr;
        1           2012-04-06 12:34:56.654321  Friday    
        2           2012-04-05 13:45:57.123456  Thursday  
        sqlite> select * from byTs;
        1           2012-04-06 12:34:56.654321  Friday    
        2           2012-04-05 13:45:57.123456  Thursday  
        sqlite> .quit

(4) Define bug_047520 as an ODBC data source referencing the SQLite
    database bug_047520.db.  I attach screenshot from ODBCConfig.  The
    stanza in my .odbc.ini reads ...

        [bug_047520]
        Description=timestamp primary key
        Driver=SQLite3
        Database=/home/terry/lo_hacking/notes/bug_047520/bug_047520.db
        Timeout=100000
        StepAPI=No
        ShortNames=No
        FKSupport=No
        SyncPragma=NORMAL
        JournalMode=DELETE
        LoadExt=

(5) Create LO database file ...

    (a) From command line, source ooenv, and then `soffice.bin
        --base`.  Program displays Database Wizard Step 1 of 2 "Select
        Database" with default selection "what do you want to do?" =
        "Create a new database".

    (b) Select "Connect to an existing database".  Program extends the
        list of steps to four steps and enables the dropdown list of
        existing databases.

    (c) In the list of databases, select ODBC.  Program displays ODBC
        as the selected existing database.

    (d) Click "Next >>".  Program displays step 2 of 4 "Set up ODBC
        connection".

    (e) In "Name of the ODBC data source on your system", type
        "bug_047520", and click "Next >>".  Program displays step 3 of
        4 "Set up user authentication".

    (f) Click "Test Connection".  Program displays message box
        "Connection Test ... The connection was established
        successfully."

    (g) Click "OK".  Program returns focus to Database Wizard.

    (h) Click "Next >>".  Program displays step 4 of 4 "Save and
        proceed".

    (i) Select "No, do not register the database", leave "Open the
        database for editing" selected, and click "Finish".  Program
        presents Save dialog.

    (j) Type Name "Play1", navigate to your working directory, and
        click Save.  Program displays window play1.odb with "Tables"
        selected; the lower right pane shows tables byNr and byTs.

(6) Observe good results from the table with an integer key.

    (a) Double-click byNr.  Program displays window "byNr - play1 -
        ... Table Data View".  Observe two rows of data.  I attach
        screenshot.

    (b) Close the Data View.  Program displays window play1.odb.

(7) Observe bad results from the table with a timestamp key.

    (a) Double-click byTs.  Program displays window "byTs - play1 -
        ... Table Datata View".

        Actual result:  No data is shown.  I attach screenshot.

        Desired result:  Two rows of data shown.
Comment 4 Terrence Enger 2012-04-09 11:54:08 UTC
Created attachment 59688 [details]
sql statements setting up demonstration SQLite3 database
Comment 5 Terrence Enger 2012-04-09 11:55:16 UTC
Created attachment 59689 [details]
demonstration sqlite3 database file
Comment 6 Terrence Enger 2012-04-09 11:56:16 UTC
Created attachment 59690 [details]
definition of ODBC data source
Comment 7 Terrence Enger 2012-04-09 11:57:11 UTC
Created attachment 59691 [details]
table with integer primary key displays data
Comment 8 Terrence Enger 2012-04-09 11:57:51 UTC
Created attachment 59692 [details]
table with timestamp primary key does not display data
Comment 9 Heinz Repp 2012-04-19 07:41:01 UTC
Finally I arrive at providing a test case. Unfortunately, the case you, Terrence, provided outlines the second "probably different bug": no data shown when primary key is timestamp using sqliteodbc newer than 0.85 that you confirmed on Linux - I experienced it under Windows. This might have something to do with the changes in timestamp handling of sqlitodbc starting with version 0.86, and can be seen with older openoffice.org releases as well.

My case with this bug is different: it is new to LibreOffice 3.5, and occurs with presumably every sqliteodbc version. I stripped my case down to the essentials, and could reproduce it under Linux (Ubuntu Lucid, sqliteodbc 0.80) and Windows XP (sqliteodbc 0.94) and LibreOffice 3.5.1 and 3.5.2 with the following:

create a new sqlite3 database with the sqlite3 shell and the following lines:

CREATE TABLE "table1" ("number" varchar(50) NOT NULL,"name" varchar(50), PRIMARY KEY ("number"));
INSERT INTO "table1" VALUES('0123456789','name 1');
INSERT INTO "table1" VALUES('1234567890','name 2');
INSERT INTO "table1" VALUES('2345678901','name 3');
INSERT INTO "table1" VALUES('3456789012','name 4');
INSERT INTO "table1" VALUES('4567890123','name 5');
INSERT INTO "table1" VALUES('5678901234','name 6');
INSERT INTO "table1" VALUES('6789012345','name 7');
INSERT INTO "table1" VALUES('7890123456','name 8');
INSERT INTO "table1" VALUES('8901234567','name 9');
INSERT INTO "table1" VALUES('9012345678','name 0');

I attach the resulting sqlite file as "expose.sqlite".

Then use ODBCConfig (Linux) or the ODBC Data Source Administrator (Windows) to create a new sqlite3 data source using this file.

Then start the Base Wizard and choose: "Connect to an existing database", and then "ODBC", and choose the data source just created.

Choose where to store the ODB file, and then open it. Open the table1 table: the first column is empty.

expected result: you should see the data above (they are still there as can be seen by using any sqlite database tool).

I see this error with many of my tables, always with char(n) or varchar(n) data type as the primary key field (in the first column), in Base the type is shown as Text[wvarchar]. I wonder if the "w" (wide) has anything to do with error, but I don't think so: as I recall it, Libreoffice 3.4 / OpenOffice 3.3 did show this type also, and they did not exhibit this error.
Comment 10 Heinz Repp 2012-04-19 07:43:01 UTC
Created attachment 60314 [details]
testcase that shows the error
Comment 11 Lionel Elie Mamane 2012-05-31 09:25:08 UTC
In my developmen tree, based on libreoffice-3-5 (3.5.5 in preparation), the situation is slightly different:

 - Heinz's example: Only the first line has "primary key" field empty.
 - Terrence's example: timestamp primary key crashes LibO.

I suspect it is linked to the fix for bug 48345, so this gives me an idea where to look... Hopefully I'll have a solution soon.

Did someone file a separate bug for "table with timestamp primary field displays no data"?
Comment 12 Lionel Elie Mamane 2012-06-01 07:37:28 UTC
The problem comes from a weirdness of the ODBC spec: One cannot retrieve a (VAR)CHAR column twice in a row. The second time one tries, the driver answers "I gave you all data, no more data to give", so LibO has only an empty string. With sqliteodbc, if one asks a third time, one gets the data again, but that is not guaranteed by the spec.

What *is* guaranteed by the spec is that if one requests e.g. column 1, then column 2, and then column 1 again, one gets the beginning (or whole if it fits in the limits) of column 1 again. But the capacity to retrieve a *lower* column (e.g. 1) after a higher column (e.g. 2) has been retrieved is *optional* and does not have to be supported by all drivers.

Sigh... So I don't know of a robust solution to this. I wanted to change connectivity;;odbc::OTools::getStringValue (and the other functions that use SQLGetData) to retrieve another column and then the column one truly wants, but this is impossible in face of the constraints above; I have tested that retrieving an invalid column does not reset the "give me fresh data" flag :-(

The only real solution seems to be to be very cautious not to retrieve the same data twice. I'm going to try to do that.
Comment 13 Not Assigned 2012-06-01 08:03:10 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=228c360e7d53a74d9908e9b164b12ace316cd34e

fdo#47520 use the already retrieved row instead of driver row
Comment 14 Terrence Enger 2012-06-01 09:10:57 UTC
I am in the process of building with the referenced patch.  Before I
noticed your comment with the patch, I was writing ...

> Aha!  So while
> 
>     select number, name from table1
> 
> gives blanks in the number in the first row, 
> 
>     select cast( number as char ), name from table1
> 
> shows the data.  Similarly, changing the type of number from
> varchar(50) to char(50) in the table definition also lets LO show the
> data.

Do you really intend the reference to CHAR in the commit message?
Comment 15 Lionel Elie Mamane 2012-06-01 10:14:35 UTC
(In reply to comment #14)
> I am in the process of building with the referenced patch.  Before I
> noticed your comment with the patch, I was writing ...

>> (...) changing the type of number from
>> varchar(50) to char(50) in the table definition also lets LO show the
>> data.

> Do you really intend the reference to CHAR in the commit message?

You are right, as CHAR is a fixed-length datatype, it won't show that problem. Which makes me realise that LibO fetches CHAR data incorrectly, because it tries to use "retrieve in parts" semantics (if there is more than 2048 bytes), and this is forbidden... Although it *may* work with some/most/all drivers anyway, as a "be extra nice" extension, not sure.

For (W)CHAR data, we have to allocate ONE buffer that is big enough ("easy" since the length of the data is known...) and then do *one* SQLGetData call, not multiple as with variable-length data.


Actually, the commit message is even more wrong than that. <sigh> It will *not* cause LibO to use the "already retrieved row", but to issue a fresh request "SELECT * FROM table WHERE primary_key=value" to fetch the other columns. But I realised this only later :-(
Comment 16 Not Assigned 2012-06-05 04:30:17 UTC
Lionel Elie Mamane committed a patch related to this issue.
It has been pushed to "libreoffice-3-5":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=16910edcae542e8eb8a5f9b4278aceaa39262b44&g=libreoffice-3-5

fdo#47520 use the already retrieved row instead of driver row


It will be available in LibreOffice 3.5.5.
Comment 17 Terrence Enger 2012-06-07 06:32:56 UTC
On the system described in comment 3, and with master commit 0752710
pulled around 2012-06-06 12:50 GMT, I can successfully open Heinz'
table1.

Thank you.
Comment 18 Not Assigned 2012-12-06 18:41:50 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=82555fdad50b68bdfe7912c58a1ec42889c86177

fdo#47520 ODBC: overhaul data fetching



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 19 Not Assigned 2012-12-06 18:48:15 UTC
Lionel Elie Mamane committed a patch related to this issue.
It has been pushed to "libreoffice-4-0":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=570cb8ffd4e174aa936f15217fd4dbe163c44e6a&g=libreoffice-4-0

fdo#47520 ODBC: overhaul data fetching


It will be available in LibreOffice 4.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.