SQLite

View Ticket
Login
Ticket Hash: 674385aeba91c774d47736f1aefd259b074dc5d3
Title: CAST('-0.0' AS NUMERIC) computes 0.0 rather than 0
Status: Fixed Type: Code_Defect
Severity: Minor Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2019-06-12 20:53:50
Version Found In:
User Comments:
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.

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?
  1. 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:

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.