SQLite

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

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'.

SELECT CAST('1.0' AS NUMERIC); -- 1
SELECT CAST('1' AS NUMERIC); -- 1

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.