[sqlite] SELECT CAST('' AS INTEGER) returns zero instead of null

[Simon Slavin]

On 14 Dec 2015, at 2:26pm, Anthony Damico  wrote:

> hi, sql standard says to strip whitespace and then convert.  "" coercing to
> zero instead of NULL strikes me as very odd..  thanks

In your command

SELECT CAST('' AS INTEGER)

you explicitly tell it to

CAST('' AS INTEGER)

which means it has to return an integer.  The integer most like a zero-length 
string is zero.  NULL is a different type of value.

In SQL a zero-length string has nothing to do with NULL.  A zero-length string 
is just another string.  NULL means something like "value missing" or "value 
unknown" or "no such value".  SQLite never confuses the two.

Simon.

[sqlite] SELECT CAST('' AS INTEGER) returns zero instead of null

[Anthony Damico]

hi, sql standard says to strip whitespace and then convert.  "" coercing to
zero instead of NULL strikes me as very odd..  thanks



some code here-

https://github.com/rstats-db/RSQLite/issues/127