SQLite

View Ticket
Login
Ticket Hash: 3c27b97e319a263a607253b34270f4747ef8fac6
Title: REAL rounding seems to depend on FROM clause
Status: Fixed Type: Code_Defect
Severity: Important Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2019-05-17 20:07:57
Version Found In: 3.28
User Comments:
mrigger added on 2019-05-16 17:52:14:

It seems that depending on the FROM clause, a different floating-point rounding is performed as demonstrated in the example below. Although programmers should not compare floating-point numbers using equality operators, the result should not depend on the FROM clause either. It is not certain that this can be reproduced on all machines.

CREATE TABLE t0 (c0);
CREATE TABLE t1 (c1 REAL);

INSERT INTO t1(c1) VALUES (8366271098608253588);
INSERT INTO t0(c0) VALUES ('a');

SELECT * FROM t1 WHERE (t1.c1 = CAST(8366271098608253588 AS REAL)); -- fetches row
SELECT * FROM t0, t1 WHERE (t1.c1 = CAST(8366271098608253588 AS REAL)); -- fetches no row
SELECT * FROM t0, t1 WHERE (t1.c1 >= CAST(8366271098608253588 AS REAL) AND t1.c1 <= CAST(8366271098608253588 AS REAL)); -- fetches row


dan added on 2019-05-16 19:02:42:

Same problem:

  CREATE TABLE t1(a REAL);
  INSERT INTO t1 VALUES( 836627109860825358 );
  SELECT * FROM t1 WHERE a = CAST(836627109860825358 AS REAL); -- returns 1 row

  CREATE INDEX i1 ON t1(a);
  SELECT * FROM t1 WHERE a = CAST(836627109860825358 AS REAL); -- same query now returns 0 rows