Bug report #17861
SQLite REAL numbers displayed as too many digits in Attribute Table
| 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.
Related issues
Associated revisions
[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).
History
#1
Updated by Kory Roberts over 6 years ago
OK, turns out both FLOAT and REAL show the same bug after all (after file reload?).
#2
Updated by Alessandro Pasotti about 6 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 about 6 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 about 6 years ago
- Related to Bug report #18007: QGIS 3 Vector data corruption when Adding a Point Feature added
#5
Updated by Alessandro Pasotti about 6 years ago
- Related to Bug report #17878: QGIS 3 Vector Layer Fields Garbled when Clicking the Toggle Editing Icon added
#6 Updated by Anonymous about 6 years ago
- Status changed from Open to Closed
- % Done changed from 0 to 100
Applied in changeset qgis|1adc55f376c93dcc986b714c55de5db0c4de7948.
#7
Updated by Alessandro Pasotti about 6 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 about 6 years ago
- Resolution set to fixed/implemented