Bug report #11809

Datatype of computed columns falls back to text

Added by Samuel Ingarfield over 9 years ago. Updated about 5 years ago.

Status:Closed
Priority:Normal
Assignee:-
Category:Data Provider/SpatiaLite
Affected QGIS version:master Regression?:No
Operating System: Easy fix?:No
Pull Request or Patch supplied:No Resolution:end of life
Crashes QGIS or corrupts data:No Copied to github as #:20033

Description

When loading SpatiaLite layers, types of computed columns are incorrect. For example, consider a table with an integer id column. A layer is added with the following SQL expression:
SELECT id, MAX(id) AS max_id, geom FROM the_table
On examining the column datatypes in Layer Properties -> Fields, field id is integer type, but max_id is TEXT.

Further, explicitly casting the datatype doesn't work either. ie.
SELECT id, CAST(MAX(id) AS INTEGER), geom AS max_id FROM the_table
returns exactly the same datatypes.

This bug makes it impossible to apply (for example) graduated styles to the layer based on the computed column.

the_database.sqlite - Test Database (4.21 MB) Samuel Ingarfield, 2014-12-09 03:33 AM

code.py Magnifier - Python Code (311 Bytes) Samuel Ingarfield, 2014-12-09 03:33 AM

Related issues

Duplicated by QGIS Application - Bug report #12225: symbology size scale field problem in spatialite layer Closed 2015-02-18

History

#1 Updated by Giovanni Manghi over 9 years ago

  • Category changed from Data Provider/SpatiaLite to DB Manager
  • Status changed from Open to Feedback
  • Priority changed from High to Normal

I believe do you refer to the "load as new layer" function of DB Manager, correct?

#2 Updated by Samuel Ingarfield over 9 years ago

This happens both when
a) Using the 'Load as New Layer' function of DB Manager; and
b) Adding layer through Python (using QgsVectorLayer)

#3 Updated by Samuel Ingarfield over 9 years ago

Note that I have also just reproduced this on Linux (latest nightly) so we're not dealing with some weird windows only issue.

#4 Updated by Giovanni Manghi over 9 years ago

  • OS version deleted (7)
  • Category changed from DB Manager to Data Provider/SpatiaLite
  • Operating System deleted (Windows)

Samuel Ingarfield wrote:

This happens both when
a) Using the 'Load as New Layer' function of DB Manager; and
b) Adding layer through Python (using QgsVectorLayer)

did it happen in previous qgis releases?

#5 Updated by Samuel Ingarfield over 9 years ago

Yep, I've just now reproduced this behaviour in 2.6.1

#6 Updated by Matthias Kuhn over 9 years ago

Can you provide the python code you use and a test dataset?

#7 Updated by Giovanni Manghi over 9 years ago

Samuel Ingarfield wrote:

Yep, I've just now reproduced this behaviour in 2.6.1

the import bit here (to say if it is a regression or not) is if it the bug was present in older qgis releases (2.4/2.2/2.0.1/1.8).

#8 Updated by Samuel Ingarfield over 9 years ago

Matthias Kuhn wrote:

Can you provide the python code you use and a test dataset?

See attached.

Giovanni Manghi wrote:

the import bit here (to say if it is a regression or not) is if it the bug was present in older qgis releases (2.4/2.2/2.0.1/1.8).

Will test in an earlier version this afternoon.

#9 Updated by Samuel Ingarfield over 9 years ago

Have just now tested in 2.2 and 2.4, am experiencing the same behaviour in both versions.

#10 Updated by Giovanni Manghi over 9 years ago

  • Status changed from Feedback to Open

Samuel Ingarfield wrote:

Have just now tested in 2.2 and 2.4, am experiencing the same behaviour in both versions.

so it does not seems a regression.

#11 Updated by Giovanni Manghi almost 7 years ago

  • Easy fix? set to No
  • Regression? set to No

#12 Updated by Giovanni Manghi about 5 years ago

  • Status changed from Open to Closed
  • Resolution set to end of life

Also available in: Atom PDF