Bug report #10066

layer from query - data type is not recognized when using operand in query

Added by Josef Källgården about 6 years ago. Updated over 5 years ago.

Status:Closed
Priority:Normal
Assignee:-
Category:DB Manager
Affected QGIS version:2.2.0 Regression?:No
Operating System:Ubuntu Easy fix?:No
Pull Request or Patch supplied:No Resolution:up/downstream
Crashes QGIS or corrupts data:No Copied to github as #:18542

Description

  1. connect to the attached db
  2. query in db manager:
    select pkuid, text_col, int_col, 2 * int_col as twice_int, cast((2 * int_col) as real) as cast_twice_int, real_col, 2 * real_col as twice_real, cast((2 * real_col) as real) as cast_twice_real, geometry from pointlyr01
    
  3. add result from query as a layer in qgis

result: fields twice_int, cast_twice_int, twice_real, cast_twice_real are interpreted as text
expected result: these particular fields should be interpreted as double

If creating a table (see tab_fr_db_manager) by the exact same query and then adding the table to qgis, then the data types are double as expected for all relevant fields:

create table tab_fr_db_manager as select pkuid, text_col, int_col, 2 * int_col as twice_int, cast((2 * int_col) as real) as cast_twice_int, real_col, 2 * real_col as twice_real, cast((2 * real_col) as real) as cast_twice_real,  geometry from pointlyr01;
SELECT RecoverGeometryColumn('tab_fr_db_manager', 'geometry', 4326, 'POINT', 'XY');

splite_fr_qgis.sqlite.zip (312 KB) Josef Källgården, 2014-04-14 01:34 PM

History

#1 Updated by Jürgen Fischer over 5 years ago

  • Resolution set to up/downstream
  • Status changed from Open to Closed

Apparently SQLite uses dynamic run-time typing and doesn't offer a way to retrieve the type of a column based on the query definition, but only on the individual values.

Also available in: Atom PDF