Why is '0' a string OR BLOB?
(1) By Aask (AAsk1902) on 2022-11-05 15:59:47 [link] [source]
Abs(X) returns 0.0 if X is a string or blob that cannot be converted to a numeric value is from the documentation of the abs(x) function.
sqlite> with tbl(val) as (Values('0'),('0.0'),('char'),('-12'),('5')) select val,abs(val) from tbl;
┌──────┬──────────┐
│ val │ abs(val) │
├──────┼──────────┤
│ 0 │ 0.0 │
│ 0.0 │ 0.0 │
│ char │ 0.0 │
│ -12 │ 12.0 │
│ 5 │ 5.0 │
└──────┴──────────┘
Why could '0' and '0.0' (first two rows) NOT be converted to a numeric value (and get treated as string)?
(2) By MBL (UserMBL) on 2022-11-05 16:30:20 in reply to 1 [source]
I think the only question here can be be why text '0' was not converted into an integer but into a real value. This can be tricked out by adding zero:
sqlite> .mode qbox
sqlite> with tbl(val) as (Values('0'),(X'404142'),(NULL),(0),(0.0),('0.0'),('char'),('-12'),('5'))
...> select typeof(val), val, abs(val), val+0, abs(val+0), typeof(abs(val+0)) from tbl;
┌─────────────┬───────────┬──────────┬───────┬────────────┬────────────────────┐
│ typeof(val) │ val │ abs(val) │ val+0 │ abs(val+0) │ typeof(abs(val+0)) │
├─────────────┼───────────┼──────────┼───────┼────────────┼────────────────────┤
│ 'text' │ '0' │ 0.0 │ 0 │ 0 │ 'integer' │
│ 'blob' │ x'404142' │ 0.0 │ 0 │ 0 │ 'integer' │
│ 'null' │ NULL │ NULL │ NULL │ NULL │ 'null' │
│ 'integer' │ 0 │ 0 │ 0 │ 0 │ 'integer' │
│ 'real' │ 0.0 │ 0.0 │ 0.0 │ 0.0 │ 'real' │
│ 'text' │ '0.0' │ 0.0 │ 0.0 │ 0.0 │ 'real' │
│ 'text' │ 'char' │ 0.0 │ 0 │ 0 │ 'integer' │
│ 'text' │ '-12' │ 12.0 │ -12 │ 12 │ 'integer' │
│ 'text' │ '5' │ 5.0 │ 5 │ 5 │ 'integer' │
└─────────────┴───────────┴──────────┴───────┴────────────┴────────────────────┘
sqlite> select sqlite_version();
┌──────────────────┐
│ sqlite_version() │
├──────────────────┤
│ '3.39.4' │
└──────────────────┘
(3) By Simon Slavin (slavin) on 2022-11-05 16:39:04 in reply to 1 [link] [source]
sqlite> with tbl(val) as (Values(0),('0'),('0.0'),(0.4),('0.4'),(-0.4),('-0.4'),('char'),('-12'),('5')) select typeof(val),val,abs(val) from tbl;
┌─────────────┬──────┬──────────┐
│ typeof(val) │ val │ abs(val) │
├─────────────┼──────┼──────────┤
│ integer │ 0 │ 0 │
│ text │ 0 │ 0.0 │
│ text │ 0.0 │ 0.0 │
│ real │ 0.4 │ 0.4 │
│ text │ 0.4 │ 0.4 │
│ real │ -0.4 │ 0.4 │
│ text │ -0.4 │ 0.4 │
│ text │ char │ 0.0 │
│ text │ -12 │ 12.0 │
│ text │ 5 │ 5.0 │
└─────────────┴──────┴──────────┘
The values would be coerced into numbers if you put them into a table with a numeric column. But what you have in the WITH statement just passes a sequence of values. They are never stored in a table so they don't have to be coerced.
I'm not entirely sure about '0.0'. The string '0.0' cannot be converted into a number because if you convert it to a number, then convert it back to a string, you don't get the original string back. SQLite understands this as some of the original information having been lost, and doesn't consider the conversion as successful. I can't figure out whether what I see in the results is a result of that.