Sorry, I was too fast with sending.
With the three values mentioned before:
a) 3.255
b) 3.25499999999999
c) 3.254999999999999893418589635
Both SQLite and MySQL (however, I used MariaDB) return these values on a simple
SELECT b:
a) 3.255
b) 3.25499999999999
c) 3.255
And ROUND(b,2) returns:
a) 3.26 for MariaDB, 3.25 for SQLite
b) 3.25 for both
c) 3.26 for MariaDB, 3.25 for SQLite
For MariaDB, this is consistent, for SQlite, it ain't.
----- Original Message -----
From: Richard Hipp <d...@sqlite.org>
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Sent: Friday, May 24, 2019, 19:06:00
Subject: [sqlite] round function inconsistent
On 5/24/19, Jose Isaias Cabrera <jic...@outlook.com> wrote:
> FWIW, I went to sqlfiddle [1] and these are the answers for this SQL
> command:
> SELECT round(3.255,2), round(3.2549999999999998,2);
I also went to sqlfiddle and did a slightly more realistic scenario:
CREATE TABLE t1(a INT, b DOUBLE PRECISION);
INSERT INTO t1(a,b) VALUES(1,3.255);
INSERT INTO t1(a,b) VALUES(2,3.254999999999999893418589635);
SELECT a, b FROM t1;
In other words, I made the value actually pass through the database.
For MySQL I got:
1 3.26
2 3.26
For SQL Server I got:
1 3.25
2 3.25
The query does not work on PostgreSQL, because PG wisely prohibits
using the two-argument around() function on binary floating-point
values, perhaps to prevent discussions such as this one. In order to
get this to work on PG I had to modify the query as follows:
SELECT a, round(CAST(b AS NUMERIC),2) from t1
And the result is then:
1 3.26
2 3.26
--
D. Richard Hipp
d...@sqlite.org
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
