Wrong feature selection in Spatialite views

[feldmrob]

[3.10.14-A Coruña, Windows 10 and 7.]

When working with virtual or spatialite view data resulting from SQL join, GUI and spatial methods (mouse click, select-by-location) highlight and return the wrong feature -- if ONE feature is clicked/selected, then the canvas and attribute table highlight the FIRST record rather than the intended. Click and drag on 2, features and get FID #1, 2; click and drag on 3 features, get FID #1,2,3.

But export as new file and all works fine.

In this illustration, I have clicked on (apologies for not knowing how to capture pointer with screen shot) parcel 03-02-0081.010 (FID 85763), yet qGIS selects FID 1:

[gioman]

@feldmrob same as https://github.com/qgis/QGIS/issues/36291 ?

[feldmrob]

@feldmrob same as #36291 ?

gioman thank you but I think not. The two cases appear to be distinctly different in pretty much every aspect.

[gioman]

gioman thank you but I think not. The two cases appear to be distinctly different in pretty much every aspect.

@feldmrob please attach a sample project with data.

[feldmrob]

Thank you again, will post as soon as I can. (Dataset is 60k+ records, so it will be a little while before I can winnow it down.)

[feldmrob]

Sample project with SQLite data: 41672.zip

[gioman]

gioman thank you but I think not. The two cases appear to be distinctly different in pretty much every aspect.

@feldmrob actually I'm very much positive they are exactly the same (selection/identify wrong on spatialite and geopackage views) with the exception that for spatilite this https://github.com/qgis/QGIS/issues/36291#issuecomment-625854699 does not work.

And it is not even related to a join. If your dataset you create d view with

SELECT * FROM rcparcel;

or

SELECT "ROWID" AS "ogc_fid",* FROM rcparcel;

you have the same problem.

[feldmrob]

gioman thank you but I think not. The two cases appear to be distinctly different in pretty much every aspect.

@feldmrob actually I'm very much positive they are exactly the same (selection/identify wrong on spatialite and geopackage views) with the exception that for spatilite this #36291 (comment) does not work.

And it is not even related to a join. If your dataset you create d view with

SELECT * FROM rcparcel;

or

SELECT "ROWID" AS "ogc_fid",* FROM rcparcel;

you have the same problem.

Please, can you tell me what you have done to enable feature selection with this data?

I have been working on this on-and-off all day, trying to learn from your instruction and the other issue report but the result is always the same. For example, according to your kind assistance above, the following SQL should resolve the problem:

'SELECT "geometry" from rcparcel;'

But that does not work any better. Nor can I find anyplace where I have referenced the reserved ID field. (Though "ogc_fid" does pop up in the "parcels" source table, which itself is an imported shapefile. I have tried and cannot seem to get rid of it.)

[gioman]

For example, according to your kind assistance above, the following SQL should resolve the problem:

@feldmrob nope, if you read this ticket and specifically this comment

https://github.com/qgis/QGIS/issues/36291#issuecomment-625854699

you'll see that if in your view you don't have a "ogc_fid" column with a unique value, then selection and identify won't work as expected. But apparently that works for geopackages and not for spatialite.

[Pedro-Murteira]

Still valid on QGIS 3.22.3.