field domain range in a gpkg reverts to field type integer and causes data corruption

[Jeroen-GroeneBij]

What is the bug or the crash?

When creating a new Range Domain in a gpkg and choosing Decimal (double) as field type, QGIS is showing this new Field Domain as an integer type. When this field domain is applied to a field with type Real, it is also not possible to enter decimal values in this field. If you apply the field domain to a Real Type field with existing decimal values, all values change to integer numbers, thus leading to data corruption.

Steps to reproduce the issue

Create a new gpkg with a single layer and at least one field with type Decimal. Create a random object and set a random value for your decimal field. Create a Range Field Domain with field type Decimal. In the browser panel in the FieldDomains you will see the new Field Domain is shown as integer: it has the 123 symbol in stead of the 1.2 symbol.

Above steps have been prepared in the attached gpkg. rangedomaintesting.zip

Apply the field domain to the decimal field. Open the attribute table and probably the original decimal value is still there. Click the edit button and the original decimal value changes to an integer.

Versions

QGIS version | 3.34.3-Prizren | QGIS code revision | 47373234ac -- | -- | -- | -- Qt version | 5.15.3 Python version | 3.9.18 GDAL/OGR version | 3.8.3 PROJ version | 9.3.1 EPSG Registry database version | v10.098 (2023-11-24) GEOS version | 3.12.1-CAPI-1.18.1 SQLite version | 3.41.1 PDAL version | 2.6.0 PostgreSQL client version | 15.2 SpatiaLite version | 5.1.0 QWT version | 6.1.6 QScintilla2 version | 2.13.4 OS version | Windows 10 Version 2009   |   |   |   Active Python plugins db_manager | 0.1.20 grassprovider | 2.12.99 MetaSearch | 0.3.6 processing | 2.12.99 QGIS version 3.34.3-Prizren QGIS code revision [47373234ac](https://github.com/qgis/QGIS/commit/47373234ac) Qt version 5.15.3 Python version 3.9.18 GDAL/OGR version 3.8.3 PROJ version 9.3.1 EPSG Registry database version v10.098 (2023-11-24) GEOS version 3.12.1-CAPI-1.18.1 SQLite version 3.41.1 PDAL version 2.6.0 PostgreSQL client version 15.2 SpatiaLite version 5.1.0 QWT version 6.1.6 QScintilla2 version 2.13.4 OS version Windows 10 Version 2009 Active Python plugins db_manager 0.1.20 grassprovider 2.12.99 MetaSearch 0.3.6 processing 2.12.99 ### Supported QGIS version - [X] I'm running a supported QGIS version according to [the roadmap](https://www.qgis.org/en/site/getinvolved/development/roadmap.html#release-schedule). ### New profile - [X] I tried with a new [QGIS profile](https://docs.qgis.org/latest/en/docs/user_manual/introduction/qgis_configuration.html#working-with-user-profiles) ### Additional context Might be related to #52318 When looking deep into the gpkg using an external DB viewer such as DB Browser for sqlite you can see the gpkg_data_column_constraints table sets the min and max fields, which are used in a Range Domain, as numeric type, not as real, decimal or integer. This is the standard behaviour whenever you create any type of Field Domain, even when only creating a Coded Values domain; the min and max columns are always created, always as numeric. Maybe QGIS has difficulty interpreting the numeric field type? I read somewhere that a numeric field type uses 0 as default for the number of decimal places, so maybe the gpkg_data_column_constraints is created wrongly? 

[Jeroen-GroeneBij]

See also the gpkg specs: http://www.geopackage.org/spec131/index.html#extension_template Search for the section with Table 25. Data Column Constraints Table Definition There is remark about the use of the Numeric field type. If I understand this correctly a Numeric filed type has been choosen because a range can both be applied to an integer field or a decimal field. However, if a numeric field type indeed uses 0 as default for the number of decimal places, just declaring Numeric the field will be treated as integer.

[Jeroen-GroeneBij]

Some additional information that might help solving this: QGIS interprets the range type Field Domain by creating a Range widget in the Attributes Form. However, when applying a Field Domain range of a decimal type (see picture) the Range widget has the Precision set to 0 (see second picture)

This Precision setting does not prevent me from entering a decimal value, but it does show the value in the attribute table as the rounded result of the original value, so a 4.7 is shown as 5 Calculations with that field use the original value 4.7

So it seems it is still possible to enter a decimal value and the decimal value is indeed stored in the attribute table. However, the precision setting results in a user experience where the decimal is converted to the rounded number; when adding a new feature and typing a decimal in the range field of the attribute form, it is shown as the rounded integer as soon as you click somewhere else.

So it would help if QGIS somehow finds the correct setting for precision from the Field Domains specification. The issue then is that I can't set a precision in the Field Domains specification. The minimum and maximum values always use 6 decimal places. I do not know why this is 6.

[Jeroen-GroeneBij]

To add to my last comment: I haven't been able to find a setting, or variable anywhere in the geopackage tables which would give a value for precision.

When creating field domains, this query is used to create the table that holds all coded values and ranges: CREATE TABLE gpkg_data_column_constraints (constraint_name TEXT NOT NULL,constraint_type TEXT NOT NULL,value TEXT,min NUMERIC,min_is_inclusive BOOLEAN,max NUMERIC,max_is_inclusive BOOLEAN,description TEXT,CONSTRAINT gdcc_ntv UNIQUE (constraint_name, constraint_type, value))

The min and max fields hold the values for each range you create. Since the min and max fields are of the Numeric type, they can hold both integer and real values. So probably the only way to recognize the precision setting needed for the widget is to find the number of decimals used in the corresponding min and max field.

Another way could be to use a Numeric (10,3) in stead of Numeric, but the arguments (10,3) used would be completely arbitrary, as there is no way for the user to declare them. I have also tested this quickly by using DB Browser for SQLite to create field domains with the min and max field declared as numeric 1(0,3) This does not (yet) change anything in the precision setting in the QGIS range widget.