Bug report #16945

Wrong data type of fields when adding a Spatialite layer

Added by Etienne MORO over 3 years ago. Updated almost 2 years ago.

Status:Closed
Priority:Normal
Assignee:Julien Cabieces
Category:Data Provider/SpatiaLite
Affected QGIS version:2.18.11 Regression?:No
Operating System: Easy fix?:No
Pull Request or Patch supplied:No Resolution:fixed/implemented
Crashes QGIS or corrupts data:No Copied to github as #:24844

Description

Using the layer BATIMENT of my spatialite DB (file attached):
1) If you look at the field properties, you can see that the field "coordX" has the type QString-TEXT, whereas in the spatialite DB, the type is FLOAT, so it should be considered by QGis as a DOUBLE (or REAL or NUMERIC) but not as a TEXT. This bug causes lots of problems if I try to create expressions using that field.
2) If I try to add a new field choosing the type 'double', QGis save it with the type FLOAT, and I think this the origin of my first problem, because until then, QGis doesn't well recognize that type FLOAT.
This bug appears in v2.14, and v2.18.

Bati.zip (625 KB) Etienne MORO, 2017-07-31 06:55 PM

History

#1 Updated by Giovanni Manghi over 3 years ago

  • Subject changed from Problem managing types of fields using a spatialite layer to Wrong data type of fields when adding a Spatialite layer
  • Crashes QGIS or corrupts data changed from Yes to No
  • Operating System deleted (Windows 7)
  • Priority changed from High to Normal
  • Category changed from Attribute table to Data Provider/SpatiaLite

It happens also on Linux, and curiously enough it does NOT happen if adding the layer by d&d from outside the QGIS main Window into QGIS.

Any other way (standard "add SL layer", DB Manager, Browser) lead to the issue.

As it really does not corrupts the data I cannot tag this as "high priority" unless is a regression from a previous QGIS version (and it may be the case, but can't try right now).

#2 Updated by Etienne MORO over 3 years ago

After several tests, I think the real problem is that in a Spatialite DB, you've got a table named geometry_columns_field_infos which isn't correctly filled when you add a field in QGis.
When you create a new field of type REAL, in the table geometry_columns_field_infos, you should fill the field double_values with the total number of rows, instead of filling the field null_values, even if all the values are NULL (at the beginning). Because after, even if you fill all the rows of the new field with real values in QGis, the table named geometry_columns_field_infos is not changed (only the field null_values is filled).

I hope this explanation will help you to correct that problem. I understand it's not a blocking problem, but it's very annoying when you try to create new data in a spatialite DB with QGis.

#3 Updated by Julien Cabieces almost 2 years ago

  • Assignee set to Julien Cabieces
  • Status changed from Open to Closed

Fixed in 3.4.2

#4 Updated by Giovanni Manghi almost 2 years ago

  • Resolution set to fixed/implemented

Also available in: Atom PDF