|Title:||CAST('-0.0' AS NUMERIC) computes 0.0 rather than 0|
|Last Modified:||2019-06-12 20:53:50|
|Version Found In:|
mrigger added on 2019-06-12 12:58:45:
In the example below, the value is not converted to an INTEGER, although the conversion would be lossless:
SELECT CAST('-0.0' AS NUMERIC); -- expected: 0, unexpected: 0.0
In similar situations, the value is converted to an INTEGER as expected:
SELECT CAST('0.0' AS NUMERIC); -- 0 SELECT CAST('+0.0' AS NUMERIC); -- 0 SELECT CAST('-1.0' AS NUMERIC); -- -1
drh added on 2019-06-12 13:25:38:
The IEEE754 floating point format distinguishes between +0.0 and -0.0 - those are different numbers. So the statement in the initial description of this ticket that "-0.0 can be losslessly converted into 0" is wrong. If -0.0 is converted into 0, then you lose the fact that you have a negative zero.
mrigger added on 2019-06-12 14:12:25:
I think that what "lossless" mean in this context is up to interpretation. In some sense, all conversions are lossful. For example, both '1.0' and '1' are converted to 1 and it is not possible to determine whether the original string was '1.0' or '1'.
SELECT CAST('1.0' AS NUMERIC); -- 1 SELECT CAST('1' AS NUMERIC); -- 1
drh added on 2019-06-12 20:53:50:
The CAST of '-0.0' into numeric now yields 0 as a special case, which resolves this ticket.
The issue of whether or not to display the minus sign when rendering a -0.0 value into text is still open. I observe that other database engines, and in particular PostgreSQL, do *not* display the minus sign. So, for now, SQLite will follow PostgreSQL's lead and behave the same.