Question regarding 3.23.0 (pending) and TRUE/FALSE

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Question regarding 3.23.0 (pending) and TRUE/FALSE

Olivier Mascia
Dear,

3.23.0 (pending) cites:

> • Recognize TRUE and FALSE as constants. (For compatibility, if there are columns named "true" or "false", then the identifiers refer to the columns rather than Boolean constants.)
> • Support operators IS TRUE, IS FALSE, IS NOT TRUE, and IS NOT FALSE.

What values will be considered FALSE, and hence will TRUE be NOT FALSE or equality to some other specific value?
Will insert into T values(FALSE) actually store integer 0 no matter column affinity or will it follow affinity?

I guess there is a page of documentation being drafted to answer these, but attempting to find it using the search button on www.sqlite.org/draft, the request https://www.sqlite.org/draft/search?s=d&q=TRUE returns an "App Application Error" page.

Thanks,
--
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia


_______________________________________________
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: Question regarding 3.23.0 (pending) and TRUE/FALSE

Clemens Ladisch
Olivier Mascia wrote:
> What values will be considered FALSE

0, and they keyword "FALSE".

FALSE is an alias for the integer 0.

> and hence will TRUE be NOT FALSE or equality to some other specific value?

What exactly do you mean with "be" and "equality"?
The SQL = and IS operators work as specified in the standard (regarding NULLs).

> Will insert into T values(FALSE) actually store integer 0 no matter column
> affinity or will it follow affinity?

The storage of boolean values does not change.  (So when you read from
a table, nothing changes.)


Regards,
Clemens
_______________________________________________
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: Question regarding 3.23.0 (pending) and TRUE/FALSE

Richard Hipp-3
In reply to this post by Olivier Mascia
On 3/2/18, Olivier Mascia <[hidden email]> wrote:
>
> What values will be considered FALSE, and hence will TRUE be NOT FALSE or
> equality to some other specific value?

I have a note to provide additional documentation on this before the release.

In short, a value is FALSE is, when converted into a floating point
number via CAST(x AS REAL) it has a value of 0.0.

It used to be (https://www.sqlite.org/src/info/36fae083b450e3af) that
sometimes FALSE was determined by CAST(x AS INTEGER).  This was
inconsistent.  Beginning with 3.23.0, FALSE is determined by CAST(x AS
REAL) in all cases.

So in other words, it used to be that 0.5 was sometimes considered
TRUE and other times considered FALSE, depending on context.  It is
now always considered TRUE.

Strings are TRUE or FALSE depending on whether or not they evaluate to
non-zero when converted.  '123xyz' is TRUE, but '0abc', 'abc', and ''
are all FALSE.


> Will insert into T values(FALSE) actually store integer 0 no matter column
> affinity or will it follow affinity?

No.  FALSE is merely an alias for 0.  Affinity still applies.  If the
column is of type TEXT, then it will store '0', not 0.

--
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: Question regarding 3.23.0 (pending) and TRUE/FALSE

J Decker
On Fri, Mar 2, 2018 at 4:19 AM, Richard Hipp <[hidden email]> wrote:

> On 3/2/18, Olivier Mascia <[hidden email]> wrote:
> >
> > What values will be considered FALSE, and hence will TRUE be NOT FALSE or
> > equality to some other specific value?
>
> I have a note to provide additional documentation on this before the
> release.
>
> In short, a value is FALSE is, when converted into a floating point
> number via CAST(x AS REAL) it has a value of 0.0.
>
> It used to be (https://www.sqlite.org/src/info/36fae083b450e3af) that
> sometimes FALSE was determined by CAST(x AS INTEGER).  This was
> inconsistent.  Beginning with 3.23.0, FALSE is determined by CAST(x AS
> REAL) in all cases.
>
> So in other words, it used to be that 0.5 was sometimes considered
> TRUE and other times considered FALSE, depending on context.  It is
> now always considered TRUE.
>
> Strings are TRUE or FALSE depending on whether or not they evaluate to
> non-zero when converted.  '123xyz' is TRUE, but '0abc', 'abc', and ''
> are all FALSE.
>
>
> > Will insert into T values(FALSE) actually store integer 0 no matter
> column
> > affinity or will it follow affinity?
>
> No.  FALSE is merely an alias for 0.  Affinity still applies.  If the
> column is of type TEXT, then it will store '0', not 0.
>

is NULL false?


>
> --
> D. Richard Hipp
> [hidden email]
> _______________________________________________
> 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: Question regarding 3.23.0 (pending) and TRUE/FALSE

Richard Hipp-3
On 3/2/18, J Decker <[hidden email]> wrote:

> On Fri, Mar 2, 2018 at 4:19 AM, Richard Hipp <[hidden email]> wrote:
>
>> On 3/2/18, Olivier Mascia <[hidden email]> wrote:
>> >
>> > What values will be considered FALSE, and hence will TRUE be NOT FALSE
>> > or
>> > equality to some other specific value?
>>
>> I have a note to provide additional documentation on this before the
>> release.
>>
>> In short, a value is FALSE is, when converted into a floating point
>> number via CAST(x AS REAL) it has a value of 0.0.
>>
>> It used to be (https://www.sqlite.org/src/info/36fae083b450e3af) that
>> sometimes FALSE was determined by CAST(x AS INTEGER).  This was
>> inconsistent.  Beginning with 3.23.0, FALSE is determined by CAST(x AS
>> REAL) in all cases.
>>
>> So in other words, it used to be that 0.5 was sometimes considered
>> TRUE and other times considered FALSE, depending on context.  It is
>> now always considered TRUE.
>>
>> Strings are TRUE or FALSE depending on whether or not they evaluate to
>> non-zero when converted.  '123xyz' is TRUE, but '0abc', 'abc', and ''
>> are all FALSE.
>>
>>
>> > Will insert into T values(FALSE) actually store integer 0 no matter
>> column
>> > affinity or will it follow affinity?
>>
>> No.  FALSE is merely an alias for 0.  Affinity still applies.  If the
>> column is of type TEXT, then it will store '0', not 0.
>>
>
> is NULL false?

No.  NULL is neither TRUE nor FALSE.  SQL (and SQLite) uses three-value logic.

NULL is TRUE -> FALSE
NULL is FALSE -> FALSE
NULL is NULL -> TRUE
TRUE is NULL -> FALSE
TRUE is TRUE -> TRUE
TRUE is FALSE -> FALSE
FALSE is NULL -> FALSE
FALSE is TRUE -> FALSE
FALSE is FALSE -> TRUE

Note the definition of the WHERE clause is that it causes all rows to
be returned where the condition is TRUE.  That means that it rejects
cases where the condition is either FALSE or NULL.  Likewise the CASE
statement groups NULL together with FALSE for its binary decisions.  I
didn't invent this - it is the way SQL works.

--
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: Question regarding 3.23.0 (pending) and TRUE/FALSE

Olivier Mascia
In reply to this post by Richard Hipp-3
> Le 2 mars 2018 à 13:19, Richard Hipp <[hidden email]> a écrit :
>
>> Will insert into T values(FALSE) actually store integer 0 no matter column
>> affinity or will it follow affinity?
>
> No.  FALSE is merely an alias for 0.  Affinity still applies.  If the
> column is of type TEXT, then it will store '0', not 0.

Thank you very much for the precise answer on that and others questions.
So FALSE is alias to 0, and then follows affinity.
In such a context of storing, is TRUE an alias to 1? And so leading to 1, '1' or 1.0 following affinity?
Looks like evidence, but want to be sure I got the model right.

--
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia


_______________________________________________
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: Question regarding 3.23.0 (pending) and TRUE/FALSE

Richard Hipp-3
On 3/2/18, Olivier Mascia <[hidden email]> wrote:
> is TRUE an alias to 1?

Yes.

Just to be clear, if it is possible to resolve TRUE to the name of a
column in a table, then it will refer to that column.  TRUE only
becomes an alias for 1 if there is no other way to resolve the name.
This is necessary for backwards compatibility.

So for example:

   CREATE TABLE t1(x, true, false);
   INSERT INTO t1 VALUES(1,2,2);
   SELECT * FROM t1 WHERE TRUE IS FALSE;

Non-intuitively, the last query above returns a single row.

To avoid this kind of confusion, avoid using TRUE or FALSE as the
names of columns in tables or views.
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users