On 28 Oct 2015, at 10:34pm, SQLite mailing list <sqlite-users at mailinglists.sqlite.org> wrote:
> This explains the deficiency in the SQLite print function, but it doesn't > have to be that way. I'm with a previous poster. SQLite is primarily a database system. Its primary jobs are storage and retrieval. It shouldn't really be used to print at all and putting unusual effort into its print functions may not be wise. However, I would support improvement in its floating point calculations, including implementing 'slop' in testing for equality. This is not only for use when expressions include the equal sign, but also for cases where comparing two numbers is done in important internal operations, like checking that primary keys do not include duplicate entries. However, I just tried to create some relevant problems: SQLite version 3.8.10.2 2015-05-20 18:17:19 Enter ".help" for usage hints. sqlite> CREATE TABLE t(r REAL PRIMARY KEY,t TEXT); sqlite> INSERT INTO t VALUES (1,'one'),(2,'two'); sqlite> INSERT INTO t VALUES (1.4,'one point four'),(1.5,'one point five'),(1.6,'one point six');; sqlite> SELECT * FROM t; 1.0|one 2.0|two 1.4|one point four 1.5|one point five 1.6|one point six sqlite> INSERT INTO t VALUES (1.6,'attempt duplication'); Error: UNIQUE constraint failed: t.r sqlite> INSERT INTO t VALUES (1.3+0.3,'attempt duplication'); Error: UNIQUE constraint failed: t.r sqlite> INSERT INTO t VALUES (1.2+0.2,'attempt duplication'); Error: UNIQUE constraint failed: t.r sqlite> INSERT INTO t VALUES (0.2+0.8,'attempt duplication'); Error: UNIQUE constraint failed: t.r sqlite> INSERT INTO t VALUES (0.3+0.7,'attempt duplication'); Error: UNIQUE constraint failed: t.r sqlite> SELECT 0.3+0.7, (0.3+0.7) = (0.2+0.8); 1.0|1 sqlite> SELECT 0.3+0.7, (0.3+0.7) - 1, (0.3+0.7) = 1; 1.0|0.0|1 sqlite> SELECT (9.2+7.9+0+1.0+1.3+1.6),(9.2+7.9+0+1.0+1.3+1.6)-21,(9.2+7.9+0+1.0+1.3+1.6)=21.0; 21.0|3.5527136788005e-15|0 As you can see (remembering that 1 = TRUE), SQLite is correctly noticing the problem when it does pure comparisons, whether in internal collations or for other tests for equality. What it's not doing is checking for near equality when printing. And I'm happy with that. Simon Slavin.
