DB Manager - load sql query as layer with geom column

[qgib]

Author Name: Patrice V (Patrice V)
Original Redmine Issue: 20674
Affected QGIS version: 3.4.2
Redmine category:db_manager
Assignee: Alessandro Pasotti

---

Hi,
I'm on win7 64bits, on qgis 3.4.2

I use DB Manager with a spatialite file.
I use the request editor to make a sql request.
When I use "load as a new layer", it works when i don't check "geometry column", but it fails when i check it.
The same operation is working on 2.18.

Regards

---

• Flinders2.7z (Phillip Shelton)

[qgib]

Author Name: Giovanni Manghi (@gioman)

---

• regression was changed from 0 to 1

[qgib]

Author Name: Patrice V (Patrice V)

---

I forgot to tell this : it's working when i choose a "raw" table in spatialite db, but not when a choose a view...

Regards,

[qgib]

Author Name: Phillip Shelton (Phillip Shelton)

---

I too have seen this problem.
I crafted a sql select that used table alias's,

SELECT mt.field1, mt.field2, mt.geom FROM mytable AS mt

This query will only load as a non spatial table. However,

SELECT mytable.field1, mytable.field2, mytable.geom FROM mytable

will load as spatial table.

[qgib]

Author Name: Alessandro Pasotti (@elpaso)

---

• assigned_to_id was configured as Alessandro Pasotti

[qgib]

Author Name: Alessandro Pasotti (@elpaso)

---

#8630

---

• pull_request_patch_supplied was changed from 0 to 1
• status_id was changed from Open to In Progress

[qgib]

Author Name: Alessandro Pasotti (@elpaso)

---

Applied in changeset b5181f2.

---

• done_ratio was changed from 0 to 100
• status_id was changed from In Progress to Closed

[qgib]

Author Name: Giovanni Manghi (@gioman)

---

• resolution was changed from to fixed/implemented

[qgib]

Author Name: Phillip Shelton (Phillip Shelton)

---

Hi,

Thank you Alessandro for so quickly fixing the use case I pointed out. Unfortunately, that was only a simple case of the problem.

The following query still fails to load as a layer. I have tested this using the nightly builds (QGIS code revision a395aff) I can also confirm that this is a regression as the same query does load in version 3.2 (QGIS code revision 9b17680)

select fe.geometry, fe.A_node, fe.B_node, fe.ass_Volume as feab, fe1.ass_Volume as feba, fm.ass_Volume as fmab, fm1.ass_Volume as fmba,
(fe.ass_Volume + fe1.ass_Volume + fm.ass_Volume + fm1.ass_Volume) * 5 as daily
from (((FE36NLBase as fe join FE36NLBase as fe1 on
(fe.a_node = fe1.b_node and fe.b_node = fe1.a_node)) join FM36NnLBase as fm on
(fe.a_node = fm.a_node and fe.b_node = fm.b_node)) join FM36NnLBase as fm1 on
(fe.a_node = fm1.b_node and fe.b_node = fm1.a_node));

To test:

1. Open qgis
2. Open the database manager
3. connect the supplied spatialite database.
4. run the above query on this database
5. load as layer. (This last step will do nothing in the latest build, in version 3.2 the layer will appear on the canvas)

Sorry Giovanni, I am saying that for me this bug has not been resloved, and the status should be moved back to at least In Progress or even Open.

Is there any other file or data that I can supply that would help in tracking down the cause of this behaviour?

I am also going to open a new duplicate of this just in case being closed means that this thread is not being viewed anymore.

---

• 13963 was configured as Flinders2.7z

---

• Flinders2.7z (Phillip Shelton) - spatialite data base with test data

[qgib]

Author Name: Alessandro Pasotti (@elpaso)

---

• status_id was changed from Closed to Reopened

[qgib]

Author Name: Alessandro Pasotti (@elpaso)

---

I'll have a look, but keep in mind that previous to 3.2 there were other ugly bugs that were fixed, like #27753, this seems to me a minor problem if compared with the bigger issue of not being able to identify, filter or select features by id.

[qgib]

Author Name: Alessandro Pasotti (@elpaso)

---

Applied in changeset d4439b2.

---

• status_id was changed from Reopened to Closed

[qgib]

Author Name: Phillip Shelton (Phillip Shelton)

---

Thank you, I greatly appreciate your attention to this problem. (again)

[qgib]

Author Name: Patrice V (Patrice V)

---

Sorry... I downloaded the last qgis dev and the bug still not fixed. I'll try to explain my problem more accurately.
I got a spatialite dbase.
I create a table : test, with a geom column (named geom)
I create a view of this table : CREATE VIEW testview AS SELECT * FROM test

In db manager :
1- I query : SELECT * FROM testview - then "execute"
result is ok

2 - Then I check "load as a new layer"
result is ok but it is not a geometric layer

3 - Then I check "geometry column" and choose "geom"
It fails !! - it was working on 2.18..

NB : the log tell this :
" 2018-12-18T14:35:45 WARNING Erreur SQLite : no such column: test.ROWID
SQL: select srid("geom"), geometrytype("geom") from (SELECT "test".ROWID, "test".pk_test, * FROM testview
) as "subQuery_0" limit 1
"
Actually, the sql query seems to mix testview and test ... :/

---

• status_id was changed from Closed to Reopened

[qgib]

Author Name: Alessandro Pasotti (@elpaso)

---

Yep, that's the effect of mixing things: I focused on aliases and joins and forgot about the views: fixed in #8706

Btw, views are only supported in DB manager and are not available in the browser nor in the source select dialog, would you mind checking if there is already an issue for that and file a new one if not?

For the record: these are the (all passing) test cases:

            '(SELECT * FROM (SELECT * from \\"some view\\"))',
            '(SELECT * FROM \\"some view\\")',
            '(select sd.* from somedata as sd left join somedata as sd2 on ( sd2.name = sd.name ))',
            '(select sd.* from \\"somedata\\" as sd left join \\"somedata\\" as sd2 on ( sd2.name = sd.name ))',
            "(SELECT * FROM somedata as my_alias1\n)",
            "(SELECT * FROM somedata as my_alias2)",
            "(SELECT * FROM somedata AS my_alias3)",
            '(SELECT * FROM \\"somedata\\" as my_alias4\n)',
            '(SELECT * FROM (SELECT * FROM \\"somedata\\"))',
            '(SELECT my_alias5.* FROM (SELECT * FROM \\"somedata\\") AS my_alias5)',
            '(SELECT my_alias6.* FROM (SELECT * FROM \\"somedata\\" as my_alias\n) AS my_alias6)',
            '(SELECT my_alias7.* FROM (SELECT * FROM \\"somedata\\" as my_alias\n) AS my_alias7\n)',
            '(SELECT my_alias8.* FROM (SELECT * FROM \\"some data\\") AS my_alias8)',
            '(SELECT my_alias9.* FROM (SELECT * FROM \\"some data\\" as my_alias\n) AS my_alias9)',
            '(SELECT my_alias10.* FROM (SELECT * FROM \\"some data\\" as my_alias\n) AS my_alias10\n)',
            '(select sd.* from \\"some data\\" as sd left join \\"some data\\" as sd2 on ( sd2.name = sd.name ))',
            '(SELECT * FROM \\"some data\\" as my_alias11\n)',
            '(SELECT * FROM \\"some data\\" as my_alias12)',
            '(SELECT * FROM \\"some data\\" AS my_alias13)',
            '(SELECT * from \\"some data\\" AS my_alias14\n)',
            '(SELECT * FROM (SELECT * from \\"some data\\"))',

[qgib]

Author Name: Alessandro Pasotti (@elpaso)

---

• status_id was changed from Reopened to In Progress

[qgib]

Author Name: Alessandro Pasotti (@elpaso)

---

Applied in changeset 07d9d1d.

---

• status_id was changed from In Progress to Closed

[qgib]

Author Name: Patrice V (Patrice V)

---

Thanks a lot !!
it was blocking me for porting my plugin to qgis 3 !