SQLite REAL numbers displayed as too many digits in Attribute Table

[qgib]

Author Name: Kory Roberts (Kory Roberts)
Original Redmine Issue: 17861
Affected QGIS version: master
Redmine category:attribute_table
Assignee: Alessandro Pasotti

---

In an SQLite/SpatiaLite database I have fields as type REAL. These are displayed in the Attribute Table with too many digits (often many zeros or 9's). This bug seems to have just shown up (but I believe it was also temporarily in a previous version of QGIS as well).

Example 1:
SQLite value = 35.990605
QGIS Attribute Table display = 35.990605000000002

Example 2:
SQLite value = -90.248519
QGIS Attribute Table display = -90.248519000000002

In attempting to examine this bug more deeply, I created a SpatiaLite database from within QGIS (Layer -> Create Layer -> New SpatiaLite). It seems type decimal number are assigned as FLOAT in SQLite, but may be better (i.e. more correct by SQLite documentation) to assign as REAL.

It appears the QGIS attribute table is treating fields of type FLOAT and REAL with different display (only REAL has bug), but in SQLite there is no difference in affinity.

http://sqlite.org/datatype3.html

---

Related issue(s): #25773 (relates), #25903 (relates)
Redmine related issue(s): 17878, 18007

---

[qgib]

Author Name: Kory Roberts (Kory Roberts)

---

OK, turns out both FLOAT and REAL show the same bug after all (after file reload?).

[qgib]

Author Name: Alessandro Pasotti (@elpaso)

---

Can you please attach a small spatialite test file with your tests?

What is the expected behavior?

Is there a way to automatically determine what is the right number of digits that has to be shown?

I mean that floating point numbers representation is not exact, converting back and forth from the DB backend to QGIS internal representation is always going to have some small rounding errors.

---

• assigned_to_id was configured as Alessandro Pasotti

[qgib]

Author Name: Kory Roberts (Kory Roberts)

---

Can you please attach a small spatialite test file with your tests?

I guess not since even the smallest spatialite file seems to exceed 5MB. I recreated the behavior by...

Layer -> Create Layer -> New Spatialite File...

Geometry as point, add new decimal number field ("field_a").

Now add point to the map, enter 34.05 as field_a.

Open attribute table.

Cell displays as 34.0499999999999997.

Click on cell in edit mode...back to 34.05.

---

More testing...

QGIS DB Manager displays as 34.05.

spatialite_gui displays as 34.050000. <- Same "bug"????
(fresh database and table created by spatialite_gui shows same behavior)

sqlitemanager.xul (Firefox add-on) displays as 34.05.

DB Browser for SQLite displays as 34.05.

---

What is the expected behavior?

Consistency, I guess. The number of decimals can be used to indicate level of precision, particularly if a field contains lat/long data. 34.05 means less is known about lat for location than 34.04999, for example.

Both QGIS attribute table and spatialite_gui have the same thing going on for some reason, but nothing else I was able to test handles the data display in that way. For everything else, if 34.05 is entered, then 34.05 is displayed...which, in my view, should be the expected behavior.

[qgib]

Author Name: Alessandro Pasotti (@elpaso)

---

• relates was configured as QGIS 3 Vector data corruption when Adding a Point Feature #25903

[qgib]

Author Name: Alessandro Pasotti (@elpaso)

---

• relates was configured as QGIS 3 Vector Layer Fields Garbled when Clicking the Toggle Editing Icon #25773

[qgib]

Author Name: Anónimo (Anónimo)

---

Applied in changeset 1adc55f.

---

• status_id was changed from Open to Closed
• done_ratio was changed from 0 to 100

[qgib]

Author Name: Alessandro Pasotti (@elpaso)

---

Please test the latest commits, all tests should be done with a new project because the default widgets for fields have changed.

[qgib]

Author Name: Alessandro Pasotti (@elpaso)

---

• resolution was changed from to fixed/implemented