[Qgis-user] DB Manager: Different results from query in DB Manager and in Virtual Layer

[Bernd Vogelgesang]

Hi,

I have a point dataset with nesting boxes (443) and a non-spatial table
from QField where I filled in the already controlled nesting boxes.

Now trying to find out which bixes are still need to be controlled. To
filter the already boxes, I use this query

SELECT "Birdboxes_GTA_20190316".*
FROM "Birdboxes_GTA_20190316"
WHERE EXISTS (SELECT 1 FROM  "birdbox_kontrolle"  WHERE
"Birdboxes_GTA_20190316"."kasten_nr"= "birdbox_kontrolle"."kastennr")

in DB Manager, this returns the quiet low of 35 matches.

When I now create new Virtual Layer with exact the same query, a point
dataset is produced with the quite resonable amount of 262

Same the other way round: To get those not controlled boxes I use

SELECT "Birdboxes_GTA_20190316".*
FROM "Birdboxes_GTA_20190316"
WHERE NOT EXISTS (SELECT 1 FROM  "birdbox_kontrolle"  WHERE
"Birdboxes_GTA_20190316"."kasten_nr"= "birdbox_kontrolle"."kastennr")

which returns 408 rows, but the more resonable number that is produced
with the virtual layer is 181

I'm happy that all my points are generated correctly, but still I'm a
bit concerned that the query result in DB Manager is so awfully wrong.

Is this just due to my poor sql-knowledge or is it a bug?

Cheers,

Bernd