Bug report #20674

DB Manager - load sql query as layer with geom column

Added by Patrice V over 5 years ago. Updated over 5 years ago.

Status:Closed
Priority:High
Assignee:Alessandro Pasotti
Category:DB Manager
Affected QGIS version:3.4.2 Regression?:Yes
Operating System: Easy fix?:No
Pull Request or Patch supplied:Yes Resolution:fixed/implemented
Crashes QGIS or corrupts data:No Copied to github as #:28494

Description

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 - spatialite data base with test data (290 KB) Phillip Shelton, 2018-12-13 10:29 PM

Associated revisions

Revision b5181f2c
Added by Alessandro Pasotti over 5 years ago

sqlite accept aliased queries from db manager

Fixes #20674 - DB Manager - load sql query as layer with geom column

Well, not sure it really fixes that particular issue because it
is not really well described, but for sure this fixes the general
case of "SELECT * FROM my_table AS my_alias"

Revision 6f725b82
Added by Alessandro Pasotti over 5 years ago

sqlite accept aliased queries from db manager

Fixes #20674 - DB Manager - load sql query as layer with geom column

Well, not sure it really fixes that particular issue because it
is not really well described, but for sure this fixes the general
case of "SELECT * FROM my_table AS my_alias"

Revision d4439b25
Added by Alessandro Pasotti over 5 years ago

Fix spatialite exotic query layers (aliased, nested, joined ...)

Fixes #20674 (again)

“It does not matter how slowly you go as long as you do not stop.”
― Confucius

Revision 07d9d1db
Added by Alessandro Pasotti over 5 years ago

Fix views query tables in spatialite

There is still an old issue: views are only available
through DB manager.

Fixes #20674 - again

- Life is really simple, but we insist on making it complicated.
Confucius

History

#1 Updated by Giovanni Manghi over 5 years ago

  • Regression? changed from No to Yes

#2 Updated by Patrice V over 5 years ago

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,

#3 Updated by Phillip Shelton over 5 years ago

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.

#4 Updated by Alessandro Pasotti over 5 years ago

  • Assignee set to Alessandro Pasotti

#5 Updated by Alessandro Pasotti over 5 years ago

  • Pull Request or Patch supplied changed from No to Yes
  • Status changed from Open to In Progress

#6 Updated by Alessandro Pasotti over 5 years ago

  • % Done changed from 0 to 100
  • Status changed from In Progress to Closed

#7 Updated by Giovanni Manghi over 5 years ago

  • Resolution set to fixed/implemented

#8 Updated by Phillip Shelton over 5 years ago

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 a395affff8) I can also confirm that this is a regression as the same query does load in version 3.2 (QGIS code revision 9b176802e5)

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.

#9 Updated by Alessandro Pasotti over 5 years ago

  • Status changed from Closed to Reopened

#10 Updated by Alessandro Pasotti over 5 years ago

I'll have a look, but keep in mind that previous to 3.2 there were other ugly bugs that were fixed, like #19930, 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.

#11 Updated by Alessandro Pasotti over 5 years ago

  • Status changed from Reopened to Closed

#12 Updated by Phillip Shelton over 5 years ago

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

#13 Updated by Patrice V over 5 years ago

  • Status changed from Closed to Reopened

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 ... :/

#14 Updated by Alessandro Pasotti over 5 years ago

Yep, that's the effect of mixing things: I focused on aliases and joins and forgot about the views: fixed in https://github.com/qgis/QGIS/pull/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\\"))',

#15 Updated by Alessandro Pasotti over 5 years ago

  • Status changed from Reopened to In Progress

#16 Updated by Alessandro Pasotti over 5 years ago

  • Status changed from In Progress to Closed

#17 Updated by Patrice V over 5 years ago

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

Also available in: Atom PDF