[QGIS-Developer] Unexpected results from GPKG table query using executeSql()

[Raymond Nijssen]

For a workaround I found that selecting this way:

SELECT
   edit_id + 0 AS edit_id,
   timestamp,
   name
FROM
   ...

The +0 seem to makes QGIS (or GDAL?) unaware of edit_id being the PK 
field and it does return it. Works both in 3.10 and 3.25.

Raymond



On 31-03-2022 18:15, Alessandro Pasotti wrote:
> Hi Raymond,
> 
> your code looks good.
> 
> Can you provide a test file?
> 
> There are some test in core here: 
> https://github.com/qgis/QGIS/blob/master/tests/src/python/test_qgsproviderconnection_ogr_gpkg.py 
> <https://github.com/qgis/QGIS/blob/master/tests/src/python/test_qgsproviderconnection_ogr_gpkg.py>
> 
> 
> On Thu, Mar 31, 2022 at 6:09 PM Raymond Nijssen via QGIS-Developer 
> <qgis-developer at lists.osgeo.org <mailto:qgis-developer at lists.osgeo.org>> 
> wrote:
> 
>     Hi devs,
> 
>     I'm working on a plugin that connects to a custom table (without
>     geometry) in a GPKG. When I'm sending queries to it, I keep on getting
>     unexpected results. Somehow the id field is missing, sometimes it
>     appears twice (!). And this seems to be different between QGIS 3.10 and
>     3.25. (I think it changed since 3.22).
> 
>     Here is a piece of my code:
> 
> 
>     fn = '/path/to/test.gpkg'
>     md = QgsProviderRegistry.instance().providerMetadata('ogr')
>     conn = md.createConnection(fn, {})
> 
>     q = 'select * from test_table;'
>     qr = conn.executeSql(q)
>     print(qr) # No id field in result
> 
> 
> 
>     Before diving into the QGIS code I'd like to check if this is the
>     way to
>     go. Or should i use another way to query that GPKG (SQLite) db?
> 
> 
>     Kind regards,
>     Raymond
>     _______________________________________________
>     QGIS-Developer mailing list
>     QGIS-Developer at lists.osgeo.org <mailto:QGIS-Developer at lists.osgeo.org>
>     List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer
>     <https://lists.osgeo.org/mailman/listinfo/qgis-developer>
>     Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer
>     <https://lists.osgeo.org/mailman/listinfo/qgis-developer>
> 
> 
> 
> -- 
> Alessandro Pasotti
> QCooperative: www.qcooperative.net <https://www.qcooperative.net>
> ItOpen: www.itopen.it <http://www.itopen.it>