Boolean casting

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

Boolean casting

Pavan Paolo
Dear all,
I use in a view the following snip of code to force the results to be considered as Boolean:
SELECT cast(start_processing_date is not null as BOOLEAN), ...

I need this solution since in this way I can detect in java the Boolean data type by querying the ResultSet metadata rs.getMetaData().getColumnType() and trigger a proper visualization in a table.

Anyway, this cause to get column name a bit messy, reporting the "CAST(non_compliant ..." itself as title of the column.
If I try to use an alias such as the following, I fix the title but I loose the Boolean data type.
select cast(start_processing_date is not null as BOOLEAN) as my_check_name, ...

Is this behavior expected? How I can work on it?

Thank you,
Paolo
_______________________________________________
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 casting

Simon Slavin-3
On 15 Mar 2018, at 4:43pm, Pavan Paolo <[hidden email]> wrote:

> I use in a view the following snip of code to force the results to be considered as Boolean:

SQLite does not have a BOOLEAN type.  If you use the word BOOLEAN where it expects to see a type name it will use an affinity of NUMBER.  See sections 2.1 and 3.1.1 of

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

> I need this solution since in this way I can detect in java the Boolean data type by querying the ResultSet metadata rs.getMetaData().getColumnType() and trigger a proper visualization in a table.

I presume you want to display the words 'TRUE' or "FALSE' rather than a number.

It's possible, with a lot of work, to figure out whether the columns in a table were defined as having type BOOLEAN.  IF you want to do this tell us and we'll tell you how.  However, if you are doing a calculation in a SELECT like

    SELECT name,(membershipType == 'y') FROM Members

The values returned will be 0 or 1 and there will be no way to tell that SQLite did a boolean calculation to return that result.

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 casting

Jens Alfke-2


> On Mar 15, 2018, at 11:20 AM, Simon Slavin <[hidden email]> wrote:
>
> It's possible, with a lot of work, to figure out whether the columns in a table were defined as having type BOOLEAN.  IF you want to do this tell us and we'll tell you how.  However, if you are doing a calculation in a SELECT like
>
>    SELECT name,(membershipType == 'y') FROM Members
>
> The values returned will be 0 or 1 and there will be no way to tell that SQLite did a boolean calculation to return that result.

To jump in here: This is also a problem for certain uses of the JSON1 extension, since JSON data does distinguish between true/false and 1/o. An application may need to know whether a query result is a JSON number or a boolean, for example. It’s on my to-do list to figure out a good way to do this.

I see that some limited boolean support is coming in 3.23; it would be nice if boolean could be elevated to a data type, or at least if integer column values in a query could be tagged to indicate that they originated as booleans.

—Jens
_______________________________________________
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 casting

Simon Slavin-3
On 15 Mar 2018, at 7:04pm, Jens Alfke <[hidden email]> wrote:

> it would be nice if boolean could be elevated to a data type

Agreed.  And I think it could be done without breaking backward compatibility [1], just by implementing the string 'BOOLEAN' as a type.  The value stored can be encoded the same as integers 0 and 1.

[1] Except for programmers who were specifically playing games with the truth value of values like 0.5.

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: [EXTERNAL] Boolean casting

Hick Gunter
In reply to this post by Pavan Paolo
Since there is no BOOLEAN dataype in SQLite, and BOOLEAN is assigned NUMERIC affinity, the sqlite3_column_type() function must be returning SQLITE_INTEGER (because storing 0 and 1 as integers is preferred over floats).

So how does the getColumnType() function determine a datatype of BOOLEAN? My guess is that it is parsing the column name returned by SQLite - which is in itself relying on undetermined behaviour - and assigning an alias will cause that to fail. Maybe the above function will "correctly" detect my_check_name_BOOLEAN?

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Pavan Paolo
Gesendet: Donnerstag, 15. März 2018 17:43
An: [hidden email]
Betreff: [EXTERNAL] [sqlite] Boolean casting

Dear all,
I use in a view the following snip of code to force the results to be considered as Boolean:
SELECT cast(start_processing_date is not null as BOOLEAN), ...

I need this solution since in this way I can detect in java the Boolean data type by querying the ResultSet metadata rs.getMetaData().getColumnType() and trigger a proper visualization in a table.

Anyway, this cause to get column name a bit messy, reporting the "CAST(non_compliant ..." itself as title of the column.
If I try to use an alias such as the following, I fix the title but I loose the Boolean data type.
select cast(start_processing_date is not null as BOOLEAN) as my_check_name, ...

Is this behavior expected? How I can work on it?

Thank you,
Paolo
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users