SQLite

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>

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"?

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>

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.