View Issue Details

IDProjectCategoryView StatusLast Update
0036486FPCDatabasepublic2019-12-28 17:02
ReporterHartmutAssigned ToMichael Van Canneyt 
PrioritynormalSeverityminorReproducibilityalways
Status closedResolutionfixed 
Platformi386OSWindowsOS VersionWindows 7 32 bit
Product Version3.3.1Product Build43796 
Target VersionFixed in Version3.3.1 
Summary0036486: SQLite-DB: reads wrong values from big INTEGER columns
DescriptionIn SQLite, when you create a table via
   CREATE TABLE demo (col1 INTEGER)
then it is allowed (!) to store values > int32 in that column. But FPC reads them wrong. All other SQLite-Clients I know read them correct.

I found this bug in FPC 3.3.1 revision 43796 on Windows 7 (32 bit) and in FPC 3.0.4 on Ubuntu 18.04 (64 bit).
Steps To ReproduceCompile and run the attached demo program (you have to adapt the path to my database).There is a small demo database attached with values > int32. The 7 stored values are:
2123579006
2477462608
3304268252
4891088032
4992299380
6055641387
6597582327

But the program reads instead:
 2123579006
-1817504688
 -990699044
  596120736
  697332084
 1760674091
-1992352265

program SQLite_INT32_Error;

{$mode objfpc}{$H+}

uses sqlite3conn,sqldb,db;

var DBConnectionX: TSQLite3Connection;
    SQLQueryX: TSQLQuery;
    SQLTransactionX: TSQLTransaction;
    DataSourceX: TDataSource;

procedure sql_init(fspecDB: string);
   {complete initialization of the SQLite-database in file 'fspecDB'}
   begin
   DataSourceX:=TDataSource.Create(nil); // create all vars:
   DBConnectionX:=TSQLite3Connection.Create(nil);
   SQLQueryX:=TSQLQuery.Create(nil);
   SQLTransactionX:=TSQLTransaction.Create(nil);

   DBConnectionX.Transaction:=SQLTransactionX; // connect all vars:
   SQLQueryX.Database:=DBConnectionX;
   DataSourceX.Dataset:=SQLQueryX;

   DBConnectionX.DatabaseName:=fspecDB; // assign filespec
   end;

procedure sql_read(sql: string);
   {executes SQL-command 'sql' and reads all data into 'DataSourceX.Dataset'.
    For this demo all error-handling was deleted}
   begin
   SQLQueryX.Close;
   SQLQueryX.SQL.Text:=sql; // wanted SQL-command
   DBConnectionX.Connected:= True; // establish the connection to the DB
   SQLTransactionX.Active:= True; // activate the transaction
   SQLQueryX.Open; // open the SQL-query
   SQLQueryX.Last;
   end;

procedure sql_done;
   {closes the DB-Connection and all vars}
   begin
   SQLQueryX.Close;
   SQLTransactionX.Active:=False;
   DBConnectionX.Connected:=False;

   DataSourceX.Free;
   DBConnectionX.Free;
   SQLQueryX.Free;
   SQLTransactionX.Free;
   end;

var DS: TDataset;
    i: longint;

begin {main}
sql_init('d:\FPC\work\einmal\SQLite_INT32_Error.sqlite'); // init SQL-DB
sql_read('SELECT col1 FROM demo'); // read data into 'DataSourceX.Dataset'

DS:=DataSourceX.Dataset;
for i:=0 to DS.RecordCount-1 do // show the data in Column 'col1':
   begin
   if i=0 then DS.First else DS.Next;
   writeln(DS.Fields[0].AsLargeInt);
   end;

sql_done; // closes the DB-Connection and all vars
end.
Additional InformationI know there are 2 "workarounds" but they don't help me:
 - create the table via "CREATE TABLE demo (col1 BIGINT)" or "CREATE TABLE demo (col1 LARGEINT)"
 - or read the data via "SELECT CAST(col1 AS BIGINT) FROM demo"

They don't help me because:
1) my program is a common SQLite-Browser which I use to show *foreign* databases. So I have no influence how the tables were created. Most tables I have seen use INTEGER-columns and not BIGINT or LARGEINT.
2) my program reads all tables via "SELECT * from tablename". If I had to use CAST(), then I had to parse and analyze each table definition, which can be very complex, to detect:
 - the number of columns
 - the name of each column
 - the type of each column
to create a complex SELECT statement with a CAST() for every INTEGER-column. Extremely complex and high risk to errors.
3) the user can input in my program an arbitrary (complex) SELECT-statement whose result is displayed. I had to tell him always to use CAST() for big INTEGER-columns, which is a) very uncomfortable and b) if he forgets it, he will see completely wrong results without any warning.

And this bug is a trap for every FPC developer to fall in. It should be fixed to avoid, that faulty FPC programs are created.

As far as I can see, FPC should treat INTEGER-columns not other then BIGINT- or LARGEINT-columns when reading them from a SQLite-DB.

I would be very happy, if this bug could be fixed not only in trunc, but in FPC 3.2.0 too. Thanks a lot!
TagsNo tags attached.
Fixed in Revision43802
FPCOldBugId
FPCTarget3.2.0
Attached Files

Activities

Hartmut

2019-12-27 18:18

reporter  

project_and_demo_database.zip (2,107 bytes)

LacaK

2019-12-28 10:03

developer   ~0120096

Last edited: 2019-12-28 10:06

View 2 revisions

Problem is that SQLite uses dynamic typing concept, where column definition is not enforced on data types presented in individual rows. You can store any data in any column type. Not only INT64 in column defined as INT32, but you can store also character data into integer column.
On other side other SQL databases and Free Pascal also uses strong typing.
You must know data types when you create fields in client dataset before you fetch real data from database.
If in table definition is column described as INTEGER it is expected that TIntegerField is created, when column is described as BIGINT it is expected that TLargeintField is created.

What should we do if in INTEGER columnn will be CHARACTER data?
In that case we should all columns create as CHARACTER or BLOB to hold any possible data which can be presented in general SQLite table.

Hartmut

2019-12-28 10:41

reporter   ~0120097

> LacaK wrote:
> If in table definition is column described as INTEGER it is expected that TIntegerField is created,
> when column is described as BIGINT it is expected that TLargeintField is created.
That is what causes the errors and should be changed. INTEGER in SQLite does *not* mean int32. INTEGER may (!) contain int64 as well as BIGINT. They should be treated the same when reading them from a SQLite-DB.

> LacaK wrote:
> What should we do if in INTEGER columnn will be CHARACTER data?
This is a complete other case. I have nerver seen this in a real database. I think for this case no solution is needed at this time.

Marco van de Voort

2019-12-28 14:03

manager   ~0120102

Can the autoguessed dataformat be override in Lazarus (fielddefs?) before reading the loop?

Michael Van Canneyt

2019-12-28 14:48

administrator   ~0120103

There is no guessing. I have introduced a property at the connection level.
AlwaysUseBigint or so, which determines how to interpret the INT or INTEGER keyword.

But for backwards compatibility, this property will be false by default.
People who use persistent fields will get an error otherwise.

I still need to write a test, then I will commit.

Michael Van Canneyt

2019-12-28 15:14

administrator   ~0120104

Fixed.
 There is a published boolean property 'AlwaysUseBigint'. You can set the property, or you can specify it in params (the property just sets/reads the param):
Conn.Params.values['AlwaysUseBigint']:='1';
when set SMALLINT INT and INTEGER are treated as BIGINT: the field type for these will be ftLargeInt.

For backwards compatibility, the parameter is false by default (persistent fields would be created differently if it were true).
You can either always set it, or create a descendent that sets it in the constructor.

Hartmut

2019-12-28 17:02

reporter   ~0120108

I tested the fix with FPC 3.3.1 revision 43802 and it works perfectly. Thank you very much for fixing. If I saw it right, you have fixed it in FPC 3.2.0 too. Thank you so much again.

I had to search a little for the name of the class, in which the new property "AlwaysUseBigint" had been added. To help other beginners: the class is "TSQLite3Connection".

Issue History

Date Modified Username Field Change
2019-12-27 18:18 Hartmut New Issue
2019-12-27 18:18 Hartmut File Added: project_and_demo_database.zip
2019-12-27 22:58 Michael Van Canneyt Assigned To => Michael Van Canneyt
2019-12-27 22:58 Michael Van Canneyt Status new => assigned
2019-12-28 10:03 LacaK Note Added: 0120096
2019-12-28 10:06 LacaK Note Edited: 0120096 View Revisions
2019-12-28 10:41 Hartmut Note Added: 0120097
2019-12-28 14:03 Marco van de Voort Note Added: 0120102
2019-12-28 14:48 Michael Van Canneyt Note Added: 0120103
2019-12-28 15:14 Michael Van Canneyt Status assigned => resolved
2019-12-28 15:14 Michael Van Canneyt Resolution open => fixed
2019-12-28 15:14 Michael Van Canneyt Fixed in Version => 3.3.1
2019-12-28 15:14 Michael Van Canneyt Fixed in Revision => 43802
2019-12-28 15:14 Michael Van Canneyt FPCTarget => 3.2.0
2019-12-28 15:14 Michael Van Canneyt Note Added: 0120104
2019-12-28 17:02 Hartmut Status resolved => closed
2019-12-28 17:02 Hartmut Note Added: 0120108