|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: <pre> SELECT CAST('-0.0' AS NUMERIC); -- expected: 0, unexpected: 0.0 </pre> In similar situations, the value is converted to an INTEGER as expected: <pre> SELECT CAST('0.0' AS NUMERIC); -- 0 SELECT CAST('+0.0' AS NUMERIC); -- 0 SELECT CAST('-1.0' AS NUMERIC); -- -1 </pre>
drh added on 2019-06-12 13:25:38: (text/x-fossil-wiki)
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. Open questions: 1. Should the CAST operator convert -0.0 into 0 as a special case, in spite of the fact that the conversion losses information? 2. Should SQLite be enhanced to display a -0.0 as "-0.0" instead of (as it currently does) conflating the -0.0 and +0.0 values into a single display of just "0.0"?
mrigger added on 2019-06-12 14:12:25: (text/x-fossil-wiki)
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'. <pre> SELECT CAST('1.0' AS NUMERIC); -- 1 SELECT CAST('1' AS NUMERIC); -- 1 </pre>
drh added on 2019-06-12 20:53:50: (text/x-fossil-wiki)
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.