SQLite and Boolean value of -1

[rvk]

Has anybody else found a problem with Boolean fields in SQLite in combination with Lazarus??

Following example:

Code: [Select]

procedure TForm1.FormCreate(Sender: TObject);
var
  SQL: string;
begin
  Deletefile('test.db'); // remove if you want to keep de db
  if not FileExists('test.db') then
  begin
    SQL := 'CREATE TABLE testtable(id int PRIMARY KEY ASC, name string, bol boolean);';
    SQLite3Connection1.ExecuteDirect(SQL);
    SQL := 'INSERT INTO testtable VALUES (1, ''first'',0);';
    SQLite3Connection1.ExecuteDirect(SQL);
    SQL := 'INSERT INTO testtable VALUES (2, ''second'',0);';
    SQLite3Connection1.ExecuteDirect(SQL);
    SQL := 'INSERT INTO testtable VALUES (3, ''third'',1);';
    SQLite3Connection1.ExecuteDirect(SQL);
  end;
  SQLite3Connection1.Connected := True;
  SQLQuery1.Upda]"]>Blockedde := upWhereAll; // <---- This won't work because asBoolean = -1 (see later)

  SQLQuery1.Upda]"]>Blockedde := upWhereKeyOnly;

  SQLQuery1.Open;
  SQLQuery1.Edit;
  SQLQuery1.FieldByName('name').AsString := 'changed';
  SQLQuery1.FieldByName('bol').AsBoolean := true;
  SQLQuery1.Post;
  SQLQuery1.ApplyUpdates;
  SQLite3Connection1.Transaction.CommitRetaining;

  // now record 1 has -1 in the bol field according to sqliteman.exe

end;

This example should create a table with 3 records. I set Upda]"]>Blockedde to upWhereKeyOnly and have a Primary index.

Now I change the first record field bol to asBoolean := true;
The field bol should contain 1 in the database but when I look with sqliteman.exe it has -1.
(it should be 0 or 1 according to https://www.sqlite.org/datatype3.html and never -1)


With the same problem, when having 0 and 1 in the database (for bol values) when using Upda]"]>Blockedde upWhereAll, it won't work because Lazarus checks for OLD_bol = -1 while the database is filled with 1.

Has anybody ever seen this?
If not, I'll open a bug-report.


Edit:
I think the problem lies in the fact that this results in -1:

Code: [Select]

var
  b1: boolean;
  b2: wordbool;
begin
  b1 := true;
  b2 := true;
  Showmessage('Boolean true = ' + inttostr(ord(b1)) + ' Wordbool true = ' + inttostr(ord(b2)));
Result:

Code: [Select]

Boolean true = 1 Wordbool true = -1
And:

Code: [Select]

procedure TBooleanField.SetAsBoolean(AValue: Boolean);
var b : wordbool;
begin
  b := AValue;
  SetData(@b);
end;

Is this also true in other DB engines where Boolean is translated to a integer-field internally?

Should this be reported as a bug in SQLite (sqldb.pp for not overriding SetAsBoolean) or for the global fields.inc for not doing Boolean = true = 1 ??

[taazz]

make it an unsigned number and you will get your answer. In most languages that do not have a boolean type and they use a integer type (regardless of size) they do it that way 0 = false max(int) = true. It so happens that Max(int) in an signed type is always -1. But this should be a problem with fpc and the AsBoolean property not sqlite's fault. Some one decided that the C default of -1 for true instead of the pascal's 1 is more appropriate for sqlite for some reason I suppose.

[rvk]

Some one decided that the C default of -1 for true instead of the pascal's 1 is more appropriate for sqlite for some reason I suppose.

So that someone was obviously wrong (in SQLite's case). According to the documentation of SQLite it should always be 0 or 1. I understand it could be different in other database-engine. So changing it in fields.inc (for all engines) might not be a good idea. When the connection with SQLite was written, the SetAsBoolean (and GetAsBoolean) should have been overridden for SQLite to use 1 and 0 (instead of -1 and 0). Now the biggest problem is, that a lot of databases written by FPC already have -1 in it because this is a longstanding bug in the code. So changing it would cause problems too.

So if anybody comes across an existing correct database (with 1 for true) they'll need to use a workaround to check for both 1 and -1 (where the -1 is written for true by FPC):
WHERE (Abc(bol) = :param_bol)

[LacaK]

IMO problem is deeper in how boolean variants are handling.

Try this program:

Code: [Select]

program bug_varBoolean;
{$APPTYPE CONSOLE}

uses
  SysUtils,
  Variants;

var
  b: boolean;
  wb: wordbool;
  v: variant;
  i: integer;

begin
  b:=True;
  v:=b;
  i:=v;
  writeln('varType=',varType(v),', value=', varToStr(v), ', AsInteger=', i);
  if varType(v)=varBoolean then writeln('varBoolean');

  wb:=True;
  v:=wb;
  i:=v;
  writeln('varType=',varType(v),', value=', varToStr(v), ', AsInteger=', i);
  if varType(v)=varBoolean then writeln('varBoolean');

  readln;
end.

As you see, when you assign "True" to variant, then this variant to integer, you get "-1"
It is bit surprise for me 

And in TParam is value stored as variant, so when you bind boolean parameter and later convert it to integer, you get -1.
It can be fixed  in TSQLite3Connection in method TSQLite3Cursor.bindparams , but as you wrote it will break backward compatibility.
No problem for me. IMO it is worth to fix it, with minor risk.

Btw in SQLite works: SELECT * FROM table WHERE 1
and also SELECT * FROM table WHERE -1 (same result)
SELECT * FROM table WHERE 0 (no rows)

So seems to me 0=False, other value=True ?

[rvk]

As you see, when you assign "True" to variant, then this variant to integer, you get "-1"

Yes, apparently variants have the same "problem" as wordbool. Although, like taazz mentioned, this might be expected behavior (because of the max(type) conversion) for booleans and variants and like you say further on (0=False and other value=True). But when a database dictates that 1 is true it should be followed as such (regardless what any language would dictate as TRUE).

So seems to me 0=False, other value=True ?

Yes, that's the correct definition I've found, too. Except for SQLite (and maybe other DB-engines). When storing -1 as boolean (/integer) it will not find it when checking for 1. So SELECT * FROM table WHERE boolean_value = -1 will not give the records with 1 (and visa versa). And that's a problem when you have an existing database with correct 1 values. Now for hardcoded SQL this wouldn't be a problem because you could say "boolean_value <> 0" (like the definition in Lazarus for boolean) but...

This problem would also exists for a prameter-query:
SELECT * FROM table WHERE boolean_value = :param
Although Lazarus might implement the boolean parameter as "(value <> 0)", the database engine has not. And the "WHERE boolean_value = :param" is check on the server-side. So having 1 or -1 (or -99 or 99 for that matter) is a problem for database-engines because you can't check for booleans with ONE parameterized statement. You would always need to have 2 statements:
SELECT * FROM table WHERE boolean_value = 0
SELECT * FROM table WHERE boolean_value <> 0

Of course, the storing of 1 for TRUE should only be changed if you're sure every database (which stores a boolean as an integer) does expect a 1 (instead of a -1). Otherwise these changes should only be implemented for the SQLite-library. (How many SQL-engines store an integer for boolean value??)

Finally, for all those having multiple values (1 and -1) for boolean already in their database, checking for ABS(boolean_value) <> 0 is the safest course. So everyone using check booleans in databases with "integer as boolean" should be aware of this change, so they can check it like that (ABS(X)), too. Or they should run a "fix" on their databases to change all the -1 back to the correct 1's.


(I didn't even mention other programs querying the database and expecting 1 for TRUE)

[LuizAmérico]

Technically is not difficult, in sqlite*dataset, it was changed to store 1 sometime ago: http://svn.freepascal.org/cgi-bin/viewvc.cgi?view=revision&revision=14441

[rvk]

Technically is not difficult, in sqlite*dataset, it was changed to store 1 sometime ago: http://svn.freepascal.org/cgi-bin/viewvc.cgi?view=revision&revision=14441

I see the code is still there:

Code: [Select]

   ftBoolean, ftWord:
      begin
        //ensure that boolean True value is stored as 1
        if Field.DataType = ftBoolean then
          TempStr := IfThen(Boolean(Buffer^), '1', '0')
        else
          Str(Word(Buffer^), TempStr);
        EditItem^.Row[FieldOffset] := StrAlloc(Length(TempStr) + 1);
        Move(PAnsiChar(TempStr)^, (EditItem^.Row[FieldOffset])^, Length(TempStr) + 1);
      end; 

But why does it not store 1 for a boolean TRUE but -1 ??

O, wait. Yes, it's because I'm using TSQLQuery with a TSQLite3Connection. And TSqlite3Dataset probably works correctly. Wow. So TSqlite3Dataset stores -1 and TSQLQuery/TSQLite3Connection stores 1. Yeah, This definitely needs to be corrected

[taazz]

Did you install any sqlite packages or you used the sqlquery and sqlconnection components? If you used sqldb then the code you look is irrelevant.

[rvk]

Did you install any sqlite packages or you used the sqlquery and sqlconnection components? If you used sqldb then the code you look is irrelevant.

Yes, I already saw that.

I used the TSQLite3Connection from the sqldb-tab in combination with TSQLQuery. That one stores -1 as boolean = TRUE in a SQLite3 database (incorrectly) . I'm aware of the completely separate TSqlite3Dataset (from which code I quoted).

My point is that if TSQLQuery stores a -1 for boolean = true and TSqlite3Dataset stores a 1 (which is the correct one) you have two components which do this differently and when switching from one to the other you'll get really strange results if querying booleans.

Before you only had to contend with other program's maybe writing 1 (while TSQLQuery writes -1) but now you have 2 components within Lazarus/FPC itself which handle the boolean differently. And the -1 of TSQLQeury is incorrect (according to SQLite).

[taazz]

Did you install any sqlite packages or you used the sqlquery and sqlconnection components? If you used sqldb then the code you look is irrelevant.

Yes, I already saw that.

I used the TSQLite3Connection from the sqldb-tab in combination with TSQLQuery. That one stores -1 as boolean = TRUE in a SQLite3 database (incorrectly) . I'm aware of the completely separate TSqlite3Dataset (from which code I quoted).

My point is that if TSQLQuery stores a -1 for boolean = true and TSqlite3Dataset stores a 1 (which is the correct one) you have two components which do this differently and when switching from one to the other you'll get really strange results if querying booleans.

Before you only had to contend with other program's maybe writing 1 (while TSQLQuery writes -1) but now you have 2 components within Lazarus/FPC itself which handle the boolean differently. And the -1 of TSQLQeury is incorrect (according to SQLite).

The solution is to enhance the existing tsqliteconnection to properly support the boolean type. That is not very difficult to do I guess, but I do not know the proper way to do it. I would guess that a custom TSQLiteBooleanField class would do the trick its a bit elaborate but it makes sense to have a custom field enforcing the value range than to add any kind of case/if or rule on any other place I guess. I would report a bug.

[rvk]

The solution is to enhance the existing tsqliteconnection to properly support the boolean type. That is not very difficult to do I guess, but I do not know the proper way to do it. I would guess that a custom TSQLiteBooleanField class would do the trick its a bit elaborate but it makes sense to have a custom field enforcing the value range than to add any kind of case/if or rule on any other place I guess. I would report a bug.

Yes, I'll report the bug, because I'm now sure it would need to be addressed (especially because TSqlite3Dataset does have this fixed and we can't have two different understanding of booleans for SQLite).

Before doing so I'll check if I can see what needs to be done (learning experience for me ).

Are there any other DB-engines that have no real notion of TRUE and FALSE and store boolean as an integer?

[eny]

Are there any other DB-engines that have no real notion of TRUE and FALSE and store boolean as an integer?

SQL*Server

[rvk]

SQL*Server

And where TRUE is described as 1 (one) and not "not 0"?

I know MySQL, for example, does also store an integer but in their documentation it is stated that TRUE is if the integer is "not 0".

[LuizAmérico]

See how i did in my sqliteconnection fork (i forgot about it):

https://code.google.com/p/luipack/source/detail?r=1059&path=/trunk/sqlite/extensions/sqlite3slimconn.pp

[LacaK]

Yes it is right solution. I would use:
        ftboolean: checkerror(sqlite3_bind_int(fstatement,I,ord(p.AsBoolean)));
I will commit this and write comment to User changes.

There is no need touch others DB.