Boolean constraint regression between 3.7.7.1 and 3.8.6?

classic Classic list List threaded Threaded
13 messages Options
Reply | Threaded
Open this post in threaded view
|

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-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Boolean constraint regression between 3.7.7.1 and 3.8.6?

Stephan Beal-3
On Mon, Aug 17, 2015 at 2:12 PM, Olivier Barthelemy <
[hidden email]> 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-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Boolean constraint regression between 3.7.7.1 and 3.8.6?

Richard Hipp-3
In reply to this post by Olivier Barthelemy
On 8/17/15, Olivier Barthelemy <[hidden email]> 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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Boolean constraint regression between 3.7.7.1 and 3.8.6?

Olivier Barthelemy
In reply to this post by 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 <[hidden email]>:

> 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 <http://link.geovariances.com/eml-home>
Keep posted about Geovariances
<http://link.geovariances.com/eml-geowidget>
<http://link.geovariances.com/eml-linkedin-gv>
<http://link.geovariances.com/eml-linkedin>
<http://link.geovariances.com/eml-twitter>
<http://link.geovariances.com/eml-youtube>
<http://link.geovariances.com/eml-slideshare>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Boolean constraint regression between 3.7.7.1 and 3.8.6?

Simon Slavin-3

On 17 Aug 2015, at 4:19pm, Olivier Barthelemy <[hidden email]> 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-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Boolean constraint regression between 3.7.7.1 and 3.8.6?

Stephan Beal-3
In reply to this post by Olivier Barthelemy
On Mon, Aug 17, 2015 at 5:19 PM, Olivier Barthelemy <
[hidden email]> 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-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Boolean constraint regression between 3.7.7.1 and 3.8.6?

Simon Slavin-3

On 17 Aug 2015, at 4:50pm, Olivier Barthelemy <[hidden email]> 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

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

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Boolean constraint regression between 3.7.7.1 and 3.8.6?

Tim Streater-3
In reply to this post by Olivier Barthelemy
On 17 Aug 2015 at 16:50, Olivier Barthelemy <[hidden email]> 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:

  <http://www.sqlite.org/datatype3.html>

--
Cheers  --  Tim

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Boolean constraint regression between 3.7.7.1 and 3.8.6?

Olivier Barthelemy
In reply to this post by Simon Slavin-3
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 <[hidden email]>:

>
> On 17 Aug 2015, at 4:50pm, Olivier Barthelemy <[hidden email]>
> 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
>
> <https://www.sqlite.org/datatype3.html>
>
> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



2015-08-17 17:56 GMT+02:00 Simon Slavin <[hidden email]>:

>
> On 17 Aug 2015, at 4:50pm, Olivier Barthelemy <[hidden email]>
> 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
>
> <https://www.sqlite.org/datatype3.html>
>
> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]>:
>
>> On 17 Aug 2015, at 4:50pm, Olivier Barthelemy <[hidden email]>
>> 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
>>
>> <https://www.sqlite.org/datatype3.html>
>>
>> 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
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
> 2015-08-17 17:56 GMT+02:00 Simon Slavin <[hidden email]>:
>
>> On 17 Aug 2015, at 4:50pm, Olivier Barthelemy <[hidden email]>
>> 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
>>
>> <https://www.sqlite.org/datatype3.html>
>>
>> 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
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Boolean constraint regression between 3.7.7.1 and 3.8.6?

Simon Slavin-3
In reply to this post by Olivier Barthelemy

On 17 Aug 2015, at 5:08pm, Olivier Barthelemy <[hidden email]> 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-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Boolean constraint regression between 3.7.7.1 and 3.8.6?

James K. Lowden
In reply to this post by Olivier Barthelemy
On Mon, 17 Aug 2015 17:19:49 +0200
Olivier Barthelemy <[hidden email]> 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-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users