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

[Even Rouault]

I've reported the issue to the OGC GeoPackage specification issue 
tracker: https://github.com/opengeospatial/geopackage/issues/683

Le 22/07/2024 à 15:57, Even Rouault via QGIS-Developer a écrit :
> 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.