[sqlite] Boolean constraint regression between 3.7.7.1 and 3.8.6?

[James K. Lowden]

On Mon, 17 Aug 2015 17:19:49 +0200
Olivier Barthelemy  wrote:

> CREATE TABLE VariableDetails (dtl_id INTEGER PRIMARY KEY ASC
> AUTOINCREMENT, storage_implicit BOOLEANCHECK (storage_implicit =
> 'true' OR storage_implicit = 'false'), storage_type TEXT NOT NULL);
> 
> Insert statement
> INSERT INTO VariableDetails (storage_implicit, storage_type) VALUES
> (true, INT_64);
> ( Values are not passed dirrectly in the statement. The boolean in
> particular is passed using sqlite3_bind_int() )

I think you got to the right place with this, but I'm not sure it was
made clear that the reason is evident in the above text.  

The constraint is against the strings 'true' and 'false'.  The insert
statement inserts "true", no quotes; as you say, it's a C symbol
interpreted by the compiler.  It's bound to the prepared statement with
sqlite3_bind_int.  The value in the bound location will be interpreted
as an integer, not as a pointer to a character array!  

--jkl

[sqlite] Boolean constraint regression between 3.7.7.1 and 3.8.6?

[Simon Slavin]

On 17 Aug 2015, at 5:08pm, Olivier Barthelemy  
wrote:

> As stated in the second question of my first message, shouldn't there be
> some check in sqlite that the type in the constraints are compatible with
> the fields, with an error at table creation?

SQLite allows what was done, on purpose.  For compatibility with other SQL 
engines.

The problem lies with the programmer who used a SQLite type of BOOLEAN but then 
allowed comparisons with strings.  Or if that SQL code was done automatically 
by some library or framework, the person who wrote that.

Simon.

[sqlite] Boolean constraint regression between 3.7.7.1 and 3.8.6?

[R.Smith]

On 2015-08-17 06:08 PM, Olivier Barthelemy wrote:
> OK,
> I already had removed my constraint as it is, since it is obviously invalid
>
> So for me :
> Am i right to leave a constraint, say CHECK (varname=0 OR varname=1), to
> avoid other integer values, or is the constraint useless? (i guess the
> answer will be 'it depends on your code' :-P)

The answer is nothing of the sort. SQL is an algebraic construct and the 
answer should always be 100% verifiable and correct. The CHECK 
constraints are designed exactly for the sort of thing you are trying to 
do. The fact that Qt interprets, or used to interpret it differently, is 
of no concern to SQL. What we can tell you is that in SQLite, the type 
specified as BOOLEAN will be regarded as a Numeric type and will think 
of values as TRUE or FALSE based on whether they are numerically equal 
to 1 or 0.  If the check constraint needs to check trueness/falseness, 
then that is how you need to phrase it. This is true now and in the 
past, although in the past Qt may have forwarded a value of 'true' as a 
1 or stored it as the actual string 'true' or some such.


>
> And for sqlite itself :
> As stated in the second question of my first message, shouldn't there be
> some check in sqlite that the type in the constraints are compatible with
> the fields, with an error at table creation?

SQLite is a Typeless Database system, this is one of its most charming 
features. You can put anything in a column of any kind. It does come 
with some nasty surprises for people used to database systems that 
enforces type compatibility or any other compatibility. For instance, 
people declare a table with a column with type VARCHAR(3) then insert 
the name 'Johnathan' into it and are very surprised when they query it 
back and it returns the whole of 'Jonathan' as opposed the expected 
'Joh' only (as a relaxed-setting MariaDB would) or indeed error out with 
a over-range message as MSSQL or PostGres would. This has many 
advantages especially if you use it as a local storage to other DB 
systems but it does require those check constraints to implicitly 
enforce value class compatibility.



>
> 2015-08-17 17:56 GMT+02:00 Simon Slavin :
>
>> On 17 Aug 2015, at 4:50pm, Olivier Barthelemy > geovariances.com>
>> wrote:
>>
 SQLite has no such type
>>> I see nothing in Qt code that converts BOOLEAN in NUMERIC or anything
>> else.
>>> The statement is passed as is to sqlite.
>> SQLite is interpreting BOOLEAN as NUMERIC.  See the last line of the table
>> in section 2.2 of
>>
>> 
>>
>> This is in most cases the right thing to do, since BOOLEAN values stored
>> by most SQL programming are really the numbers 0 and 1.  However your CHECK
>> constraints treat the values as if they're strings:
>>
>>> storage_implicit = 'true' OR
>>> storage_implicit = 'false'
>> and if the values are understood as NUMERIC, neither of those string
>> comparisons will ever by true, so all INSERT commands will fail their
>> constraint checks.
>>
>>> On previously created sqlite files, when i open then in sqlite manager
>>> addon of Firefox, the field is still displayed as BOOLEAN
>> FireFox is showing you the command that was used to create the table.
>> This is an (unfortunate ?) aspect of how SQLite works: it stores the
>> creation string rather than details of how it was understood.
>>
>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
> 2015-08-17 17:56 GMT+02:00 Simon Slavin :
>
>> On 17 Aug 2015, at 4:50pm, Olivier Barthelemy > geovariances.com>
>> wrote:
>>
 SQLite has no such type
>>> I see nothing in Qt code that converts BOOLEAN in NUMERIC or anything
>> else.
>>> The statement is passed as is to sqlite.
>> SQLite is interpreting BOOLEAN as NUMERIC.  See the last line of the table
>> in section 2.2 of
>>
>> 
>>
>> This is in most cases the right thing to do, since BOOLEAN values stored
>> by most SQL programming are really the numbers 0 and 1.  However your CHECK
>> constraints treat the values as if they're strings:
>>
>>> storage_implicit = 'true' OR
>>> storage_implicit = 'false'
>> and if the values are understood as NUMERIC, neither of those string
>> comparisons will ever by true, so all INSERT commands will fail their
>> constraint checks.
>>
>>> On previously created sqlite files, when i open then in sqlite manager
>>> addon of Firefox, the field is still displayed as BOOLEAN
>> FireFox is showing you the command that was used to create the table.
>> This is an (unfortunate ?) aspect of how SQLite works: it stores the
>> creation string rather than details of how it was understood.
>>
>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi

[sqlite] Boolean constraint regression between 3.7.7.1 and 3.8.6?

[Olivier Barthelemy]

OK,
I already had removed my constraint as it is, since it is obviously invalid

So for me :
Am i right to leave a constraint, say CHECK (varname=0 OR varname=1), to
avoid other integer values, or is the constraint useless? (i guess the
answer will be 'it depends on your code' :-P)

And for sqlite itself :
As stated in the second question of my first message, shouldn't there be
some check in sqlite that the type in the constraints are compatible with
the fields, with an error at table creation?

2015-08-17 17:56 GMT+02:00 Simon Slavin :

>
> On 17 Aug 2015, at 4:50pm, Olivier Barthelemy 
> wrote:
>
> >> SQLite has no such type
> >
> > I see nothing in Qt code that converts BOOLEAN in NUMERIC or anything
> else.
> > The statement is passed as is to sqlite.
>
> SQLite is interpreting BOOLEAN as NUMERIC.  See the last line of the table
> in section 2.2 of
>
> 
>
> This is in most cases the right thing to do, since BOOLEAN values stored
> by most SQL programming are really the numbers 0 and 1.  However your CHECK
> constraints treat the values as if they're strings:
>
> > storage_implicit = 'true' OR
> > storage_implicit = 'false'
>
> and if the values are understood as NUMERIC, neither of those string
> comparisons will ever by true, so all INSERT commands will fail their
> constraint checks.
>
> > On previously created sqlite files, when i open then in sqlite manager
> > addon of Firefox, the field is still displayed as BOOLEAN
>
> FireFox is showing you the command that was used to create the table.
> This is an (unfortunate ?) aspect of how SQLite works: it stores the
> creation string rather than details of how it was understood.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



2015-08-17 17:56 GMT+02:00 Simon Slavin :

>
> On 17 Aug 2015, at 4:50pm, Olivier Barthelemy 
> wrote:
>
> >> SQLite has no such type
> >
> > I see nothing in Qt code that converts BOOLEAN in NUMERIC or anything
> else.
> > The statement is passed as is to sqlite.
>
> SQLite is interpreting BOOLEAN as NUMERIC.  See the last line of the table
> in section 2.2 of
>
> 
>
> This is in most cases the right thing to do, since BOOLEAN values stored
> by most SQL programming are really the numbers 0 and 1.  However your CHECK
> constraints treat the values as if they're strings:
>
> > storage_implicit = 'true' OR
> > storage_implicit = 'false'
>
> and if the values are understood as NUMERIC, neither of those string
> comparisons will ever by true, so all INSERT commands will fail their
> constraint checks.
>
> > On previously created sqlite files, when i open then in sqlite manager
> > addon of Firefox, the field is still displayed as BOOLEAN
>
> FireFox is showing you the command that was used to create the table.
> This is an (unfortunate ?) aspect of how SQLite works: it stores the
> creation string rather than details of how it was understood.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Boolean constraint regression between 3.7.7.1 and 3.8.6?

[Olivier Barthelemy]

The missing space is a copy paste error. And the table IS getting created.

Only the insertion fails. sqlite return code is 19 (/* Abort due to
constraint violation */).

>  SQLite has no such type
I see nothing in Qt code that converts BOOLEAN in NUMERIC or anything else.
The statement is passed as is to sqlite.
On previously created sqlite files, when i open then in sqlite manager
addon of Firefox, the field is still displayed as BOOLEAN

[sqlite] Boolean constraint regression between 3.7.7.1 and 3.8.6?

[Stephan Beal]

On Mon, Aug 17, 2015 at 5:19 PM, Olivier Barthelemy <
barthelemy at geovariances.com> wrote:

> Create statement :
> CREATE TABLE VariableDetails (dtl_id INTEGER PRIMARY KEY ASC AUTOINCREMENT,
> storage_implicit BOOLEANCHECK (storage_implicit = 'true' OR
> storage_implicit = 'false'), storage_type TEXT NOT NULL);
>

If "BOOLEANCHECK" is not a copy/paste error, that might be the problem.
That might explain why this fails:


Insert statement
> INSERT INTO VariableDetails (storage_implicit, storage_type) VALUES (true,
> INT_64);
> ( Values are not passed dirrectly in the statement. The boolean in
> particular is passed using sqlite3_bind_int() )
>
> As far as i can see (debugging using debuggable version of Qt), it's at
> sqlite calls that the insert failure is detected, and no error is
> 'forgotten' by Qt at table creation.
>

What error code does the insertion return? Perhaps the table is not getting
created due to the missing space?

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf

[sqlite] Boolean constraint regression between 3.7.7.1 and 3.8.6?

[Olivier Barthelemy]

>From my logs :

Create statement :
CREATE TABLE VariableDetails (dtl_id INTEGER PRIMARY KEY ASC AUTOINCREMENT,
storage_implicit BOOLEANCHECK (storage_implicit = 'true' OR
storage_implicit = 'false'), storage_type TEXT NOT NULL);

Insert statement
INSERT INTO VariableDetails (storage_implicit, storage_type) VALUES (true,
INT_64);
( Values are not passed dirrectly in the statement. The boolean in
particular is passed using sqlite3_bind_int() )

As far as i can see (debugging using debuggable version of Qt), it's at
sqlite calls that the insert failure is detected, and no error is
'forgotten' by Qt at table creation.


2015-08-17 14:12 GMT+02:00 Olivier Barthelemy :

> Hi,
>
> I have been using sqlite through Qt.
>
> I have been using for a while the boolean constraint "CHECK
> (varname='true' OR varname='false')" when creating tables with boolean
> fields and had no issues in Qt 4.8.6 (sqlite 3.7.7.1)
>
> Now i am trying to port to Qt 5.4.2 (sqlite 3.8.6).
>
> I can load already created databases that have this boolean constraint, or
> create new tables with boolean fields that have this constraint, but the
> constraint now always fail at an insert.
>
> Removing that constraint, or using "CHECK (varname=0 OR varname=1)"
> instead makes the application work again
>
> Is it a regression that should be made to work again? Or was the
> constraint invalid, and in that case there is a bug because adding the
> constraint should fail?
>
>


-- 
[image: Geovariances]

Olivier BARTHELEMY *Software development engineer*
Geovariances, 49bis avenue Franklin Roosevelt - 77215 AVON CEDEX - FRANCE
| www.geovariances.com 
Keep posted about Geovariances

[sqlite] Boolean constraint regression between 3.7.7.1 and 3.8.6?

[Tim Streater]

On 17 Aug 2015 at 16:50, Olivier Barthelemy  
wrote:


>>  SQLite has no such type

> I see nothing in Qt code that converts BOOLEAN in NUMERIC or anything else.
> The statement is passed as is to sqlite.
> On previously created sqlite files, when i open then in sqlite manager
> addon of Firefox, the field is still displayed as BOOLEAN

Of course. But SQLite itself has no BOOLEAN type, that is the point. So if you 
declare a column as BOOLEAN in CREATE TABLE, that will in fact become a NUMERIC 
column. And just because the Firefox addon shows it as BOOLEAN, does not make 
it so.

See:

  

--
Cheers  --  Tim

[sqlite] Boolean constraint regression between 3.7.7.1 and 3.8.6?

[Simon Slavin]

On 17 Aug 2015, at 4:50pm, Olivier Barthelemy  
wrote:

>> SQLite has no such type
> 
> I see nothing in Qt code that converts BOOLEAN in NUMERIC or anything else.
> The statement is passed as is to sqlite.

SQLite is interpreting BOOLEAN as NUMERIC.  See the last line of the table in 
section 2.2 of



This is in most cases the right thing to do, since BOOLEAN values stored by 
most SQL programming are really the numbers 0 and 1.  However your CHECK 
constraints treat the values as if they're strings:

> storage_implicit = 'true' OR
> storage_implicit = 'false'

and if the values are understood as NUMERIC, neither of those string 
comparisons will ever by true, so all INSERT commands will fail their 
constraint checks.

> On previously created sqlite files, when i open then in sqlite manager
> addon of Firefox, the field is still displayed as BOOLEAN

FireFox is showing you the command that was used to create the table.  This is 
an (unfortunate ?) aspect of how SQLite works: it stores the creation string 
rather than details of how it was understood.

Simon.

[sqlite] Boolean constraint regression between 3.7.7.1 and 3.8.6?

[Simon Slavin]

On 17 Aug 2015, at 4:19pm, Olivier Barthelemy  
wrote:

> Create statement :
> CREATE TABLE VariableDetails (dtl_id INTEGER PRIMARY KEY ASC AUTOINCREMENT,
> storage_implicit BOOLEANCHECK (storage_implicit = 'true' OR
> storage_implicit = 'false'), storage_type TEXT NOT NULL);

[I assume there is a space after 'BOOLEAN'.]

Whatever is generating the 'type' of BOOLEAN is wrong.  SQLite has no such 
type.  The type will be understood as NUMERIC.  Since the code then goes on to 
compare it with strings, there are several things here that may appear to work 
for a while but pile up problems for later.

Simon.

[sqlite] Boolean constraint regression between 3.7.7.1 and 3.8.6?

[Stephan Beal]

On Mon, Aug 17, 2015 at 2:12 PM, Olivier Barthelemy <
barthelemy at geovariances.com> wrote:

> I have been using for a while the boolean constraint "CHECK (varname='true'
> OR varname='false')" when creating tables with boolean fields and had no
> issues in Qt 4.8.6 (sqlite 3.7.7.1)
>

This looks like it was possibly (mis)handled by qt's driver internally, as
the sqlite docs say:

https://www.sqlite.org/datatype3.html

1.1 Boolean Datatype

SQLite does not have a separate Boolean storage class. Instead, Boolean
values are stored as integers 0 (false) and 1 (true).



-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf

[sqlite] Boolean constraint regression between 3.7.7.1 and 3.8.6?

[Olivier Barthelemy]

Hi,

I have been using sqlite through Qt.

I have been using for a while the boolean constraint "CHECK (varname='true'
OR varname='false')" when creating tables with boolean fields and had no
issues in Qt 4.8.6 (sqlite 3.7.7.1)

Now i am trying to port to Qt 5.4.2 (sqlite 3.8.6).

I can load already created databases that have this boolean constraint, or
create new tables with boolean fields that have this constraint, but the
constraint now always fail at an insert.

Removing that constraint, or using "CHECK (varname=0 OR varname=1)" instead
makes the application work again

Is it a regression that should be made to work again? Or was the constraint
invalid, and in that case there is a bug because adding the constraint
should fail?

[sqlite] Boolean constraint regression between 3.7.7.1 and 3.8.6?

[Richard Hipp]

On 8/17/15, Olivier Barthelemy  wrote:
> Hi,
>
> I have been using sqlite through Qt.
>
> I have been using for a while the boolean constraint "CHECK (varname='true'
> OR varname='false')" when creating tables with boolean fields and had no
> issues in Qt 4.8.6 (sqlite 3.7.7.1)
>
> Now i am trying to port to Qt 5.4.2 (sqlite 3.8.6).
>
> I can load already created databases that have this boolean constraint, or
> create new tables with boolean fields that have this constraint, but the
> constraint now always fail at an insert.
>
> Removing that constraint, or using "CHECK (varname=0 OR varname=1)" instead
> makes the application work again
>
> Is it a regression that should be made to work again? Or was the constraint
> invalid, and in that case there is a bug because adding the constraint
> should fail?

You did not supply us with SQL statements to test.  So I presume that
the SQL is actually being generated by some kind of behind-the-scenes
Qt magic.  That makes me suspect that the change in behavior is due to
changes in Qt, not due to changes in SQLite.

If you can provide specific SQL statements that demonstrate your
problem, that will be helpful in tracking down the cause.


-- 
D. Richard Hipp
drh at sqlite.org