[QGIS-Developer] qgis / ogr converts INT & DATE values from spatial view to STRING

[Even Rouault]

Benjamin,

>
> Explicit casts like "(CAST(SUM(D.QAI) as INTEGER) as n_qai" did not 
> solve my problem.

Yeah, I recently came across a similar issue, and the finding is that it 
is related to the SQLite version. Such casts apparently work since 
SQLite 3.42. So if your QGIS build uses an older SQLite version, they 
won't be honored.

$ ogr2ogr poly.gpkg autotest/ogr/data/poly.shp
$ ogrinfo poly.gpkg -sql "create view v as select fid as ogc_fid, geom, 
cast(1 as int) as my_int, cast(1 as real) as my_real from poly"

$ LD_LIBRARY_PATH=/home/even/install-sqlite-3.41.0/lib:$LD_LIBRARY_PATH 
ogrinfo poly.gpkg -al -so | grep my_
my_int: String (0.0)
my_real: String (0.0)

vs

$ LD_LIBRARY_PATH=/home/even/install-sqlite-3.42.0/lib:$LD_LIBRARY_PATH 
ogrinfo poly.gpkg -al -so | grep my_
my_int: Integer64 (0.0)
my_real: Real (0.0)


This is really a SQLite3 issue as demonstrated by:

$ ~/install-sqlite-3.41.0/bin/sqlite3 poly.gpkg "pragma table_info(v)"
0|ogc_fid|INTEGER|0||0
1|geom|POLYGON|0||0
2|my_int|NUM|0||0
3|my_real|NUM|0||0

vs

$ ~/install-sqlite-3.42.0/bin/sqlite3 poly.gpkg "pragma table_info(v)"
0|ogc_fid|INTEGER|0||0
1|geom|POLYGON|0||0
2|my_int|INT|0||0
3|my_real|REAL|0||0

*However* this only works for TEXT, INTEGER and REAL. As there is no 
SQLite native DATETIME "affinity" (cf 
https://www.sqlite.org/datatype3.html), there's no way currently to 
force view columns to DATETIME

>
> Interestingly, the OGRDataSource::ExecuteSQL does returns correct 
> integer value for COUNT and SUM results. DATE(...) results are 
> returned as STRINGS as well.
>
> with ds.ExecuteSQL('SELECT * FROM ard_data_byTile') as lyr:

yes, that's because in that mode, the GPKG driver uses the value of the 
first row to infer the data type, but this isn't super bullet proof 
(particularly if the first row contains NULL values)

Even

-- 
http://www.spatialys.com
My software is free, but my time generally not.