Bug report #17861

SQLite REAL numbers displayed as too many digits in Attribute Table

Added by Kory Roberts over 2 years ago. Updated over 2 years ago.

Status:Closed
Priority:Normal
Assignee:Alessandro Pasotti
Category:Attribute table
Affected QGIS version:master Regression?:Yes
Operating System:Linux Mint 18.3 Cinnamon Easy fix?:No
Pull Request or Patch supplied:No Resolution:fixed/implemented
Crashes QGIS or corrupts data:No Copied to github as #:25756

Description

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 issues

Related to QGIS Application - Bug report #18007: QGIS 3 Vector data corruption when Adding a Point Feature Closed 2018-01-30
Related to QGIS Application - Bug report #17878: QGIS 3 Vector Layer Fields Garbled when Clicking the Togg... Closed 2018-01-17

Associated revisions

Revision 1adc55f3
Added by Alessandro Pasotti over 2 years ago

[bugfix] Lower priority of range widget for doubles

This was the root cause of several critical bugs with
data corruption in the attribute table and forms:
SpinBox has a fixed number of decimal places, which
makes it not ideal for floating points because most
of the times it rounds the number changing it when
in edit mode even if the user has not really modified
the value by writing in the spin box or using the
controls.

So, the defult is now back to the line edit (which has
bugs in the validator, but that's another less
critical and separate issue).

Partially fixes #17861
Partially fixes #18007

History

#1 Updated by Kory Roberts over 2 years ago

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

#2 Updated by Alessandro Pasotti over 2 years ago

  • Assignee set to Alessandro Pasotti

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.

#3 Updated by Kory Roberts over 2 years ago

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.

#4 Updated by Alessandro Pasotti over 2 years ago

  • Related to Bug report #18007: QGIS 3 Vector data corruption when Adding a Point Feature added

#5 Updated by Alessandro Pasotti over 2 years ago

  • Related to Bug report #17878: QGIS 3 Vector Layer Fields Garbled when Clicking the Toggle Editing Icon added

#6 Updated by Anonymous over 2 years ago

  • Status changed from Open to Closed
  • % Done changed from 0 to 100

#7 Updated by Alessandro Pasotti over 2 years ago

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

#8 Updated by Alessandro Pasotti over 2 years ago

  • Resolution set to fixed/implemented

Also available in: Atom PDF