Boolean, True != True

[umpf]

Hello,
I have a problem with SQLite-Admin 0.8.3.2 .

I create a table in SQL Query - Tab that looks like this:

CREATE TABLE [Event] (
[IDX] INTEGER DEFAULT '1' PRIMARY KEY NOT NULL,
[B1] BOOLEAN DEFAULT 'False' NULL,
[B2] BOOLEAN DEFAULT 'False' NULL,
[B3] BOOLEAN DEFAULT 'True' NULL
);

Then I add some (7) items into the database that looks like this:

insert into Event ( B1, B2, B3) Values ( 'True', 'False', 'False') ;

After adding items I create a SQL Query that looks like this:

select * from event where B1 = 'True';

The result includes all seven items I added before.
So far , so good ... but

In the Edit Data- Tab I changed item 7 the value from B1 to false and update the database.
The query "select * from event where B1 = 'True';" returns 6 items, it's ok as well.

in the Edit Data- Tab, I change item 7, the value from B1 to true and update the database.
The query "select * from event where B1 = 'True';" gives back 6 items.

I repeat the procedure whit item 1.
now the result is 5 items long.

Why???

I have read in the forum, that 0 / 1 is standard, and should be used in place of True / False.
By using 0/1, there is no different, to the previous behavior.

Is this an (known) bug, or there is another explanation for this.


Thanks for the help / explanation.

[bangfauzan]

try this :
insert into Event ( B1, B2, B3) Values ( 'Y', 'N', 'N') ;

[umpf]

Thanks, it works with 'Y' and "N".
Obviously, these values are used in SQLite Administrator.
Y & N, solve the problem only partly.
I have made a test.

--- Step 1. Creating a database with SQLite Admin ---
CREATE TABLE [TAB] (
[IDX] INTEGER DEFAULT '1' NOT NULL PRIMARY KEY,
[B1] BOOLEAN NULL
);

--- Step 2. Fill the table with values. ---
insert into TAB ( B1) Values ( 'Y') ;
insert into TAB ( B1) Values ( 'N') ;
insert into TAB ( B1) Values ( 'y') ;
insert into TAB ( B1) Values ( 'n') ;
insert into TAB ( B1) Values ( '1') ;
insert into TAB ( B1) Values ( '0') ;
insert into TAB ( B1) Values ( 'True') ;
insert into TAB ( B1) Values ( 'False') ;
insert into TAB ( B1) Values ( 'true') ;
insert into TAB ( B1) Values ( 'false') ;
insert into TAB ( B1) Values ( 1) ;
insert into TAB ( B1) Values ( 0) ;


--- Step 3. Query the database with PHP, using the command: 'SELECT * FROM TAB;'---
query : SELECT * FROM TAB;
result:
IDX:1, B1:Y;
IDX:2, B1:N;
IDX:3, B1:y;
IDX:4, B1:n;
IDX:5, B1:1;
IDX:6, B1:0;
IDX:7, B1:True;
IDX:8, B1:False;
IDX:9, B1:true;
IDX:10, B1:false;
IDX:11, B1:1;
IDX:12, B1:0;
we now recognize why the query with 'SELECT * FROM TAB WHERE B1 ='True;' is not, working properly.

--- Step 4. Export the database with SQLite Admin as *. csv.---
IDX;B1;
1;True;
2;False;
3;True;
4;False;
5;True;
6;False;
7;True;
8;False;
9;True;
10;False;
11;True;
12;False;

As you can see, SQLite Admin Exports the values correctly with 'True' and 'False'.
How must look the query string to get a correct result, as with SQLite Admin
I have tested many combinations, they always give incorrect results.
query : SELECT * FROM TAB WHERE B1 < 'Y';
query : SELECT * FROM TAB WHERE B1 = 'Y';
query : SELECT * FROM TAB WHERE B1 != 'Y';
query : SELECT * FROM TAB WHERE B1 > 'Y';
query : SELECT * FROM TAB WHERE B1 = 'True';
....

Can someone give me a tip?