CAST AS STRING always returns 0 for STRING columns

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

CAST AS STRING always returns 0 for STRING columns

Ben Caine
CAST AS STRING always returns 0 for columns that are already of STRING type.

Steps to reproduce:

sqlite> CREATE TABLE A(col1 STRING);
sqlite> INSERT INTO A VALUES('asdf');
sqlite> SELECT CAST(col1 AS STRING) FROM A;
0

We are generating SQL code programmatically, and it would be useful to not
have to special-case casting based on column type.
--
--

Ben Caine | Software Engineer | Verily
_______________________________________________
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: CAST AS STRING always returns 0 for STRING columns

Jay Kreibich

> On Sep 4, 2018, at 9:30 AM, Ben Caine <[hidden email]> wrote:
>
> CAST AS STRING always returns 0 for columns that are already of STRING type.

“STRING” is not a known type affinity.  Use “TEXT”

https://www.sqlite.org/lang_expr.html#castexpr <https://www.sqlite.org/lang_expr.html#castexpr>



>
> Steps to reproduce:
>
> sqlite> CREATE TABLE A(col1 STRING);
> sqlite> INSERT INTO A VALUES('asdf');
> sqlite> SELECT CAST(col1 AS STRING) FROM A;
> 0
>
> We are generating SQL code programmatically, and it would be useful to not
> have to special-case casting based on column type.
> --
> --
>
> Ben Caine | Software Engineer | Verily
> _______________________________________________
> 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: CAST AS STRING always returns 0 for STRING columns

Keith Medcalf
In reply to this post by Ben Caine

"STRING" is not a known affinity and equates to NUMERIC affinity/storage class.  That means that your code is the equivalent of:

create table a(col1 NUMERIC);
insert into a values ('asdf');
select cast(col1 as NUMERIC) from a;

Which will, of course, return the value 0.  This is because:

1)  You create the table a with the col1 column having an affinity of NUMERIC.
2)  You attempted to insert TEXT into that column.
The TEXT you provided could not be losslessly converted to the affinity of the column, so it was stored as TEXT.  (That means that when the text value was converted to a numeric value, that value could not then be converted back to the original input string).
3)  When you select the data you are converting the TEXT into NUMERIC without regard for the "losslessly" part.  It is assumed that you know what you are doing.  The result of the conversion is 0, which is correct.

The valid datatypes (affinities and storage classes) are:  INTEGER/REAL/TEXT/BLOB/NUMERIC.  When you declare a "datatype" or "affinity" using a sequence of characters ("STRING" being the sequence of characters you chose), the rules outlined here are used to determine the actual datatype/storage class/affinity:  https://www.sqlite.org/datatype3.html#affname

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.

>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Ben Caine
>Sent: Tuesday, 4 September, 2018 08:31
>To: [hidden email]
>Subject: [sqlite] CAST AS STRING always returns 0 for STRING columns
>
>CAST AS STRING always returns 0 for columns that are already of
>STRING type.
>
>Steps to reproduce:
>
>sqlite> CREATE TABLE A(col1 STRING);
>sqlite> INSERT INTO A VALUES('asdf');
>sqlite> SELECT CAST(col1 AS STRING) FROM A;
>0
>
>We are generating SQL code programmatically, and it would be useful
>to not
>have to special-case casting based on column type.
>--
>--
>
>Ben Caine | Software Engineer | Verily
>_______________________________________________
>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