Bug report #19843

Update SQL Layer converts query to table name

Added by Etienne Racine over 5 years ago. Updated over 5 years ago.

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

Description

When using the query builder, queries get converted to a table name when updating the query (right-clic on the layer > Update SQL Layer... ).

It is something I observed in previous 3.x versions as well.
Here's my recipe to reproduce the issue in the QGIS 3.2.1-Bonn (1edf372fb8)

In the database manager: menu > Database > DB Manager > connect to a database
(I'm connected to a postgis database)
Once connected, open the SQL Window (F2)
Load the following Query (check `load new layer`, select the `geom` column and then clic load):
`select st_setsrid(st_point(0, 0), 4326) as geom`

Then edit the newly created layer (probably named QueryLayer) in the Layers panel:
right-clic on the layer > Update SQL Layer...

I get the following error:
An error occured when running a query:
relation "select st_setsrid(st_point(0, 0), 4326) as geom" does not exist
LINE 1: SELECT * FROM "select st_setsrid(st_point(0, 0), 4326) as ge...
^
Query:
SELECT * FROM "select st_setsrid(st_point(0, 0), 4326) as geom
"

The whole query was converted to a table name. Same thing with CTEs (WITH).

Related issues

Duplicates QGIS Application - Bug report #18244: DB Manager: "Update SQL layer" error Closed 2018-02-27
Duplicated by QGIS Application - Bug report #19926: Editing the expression for a SQL layer corrupts the SQL e... Closed 2018-09-24

Associated revisions

Revision a8bf1b8f
Added by Alessandro Pasotti over 5 years ago

[dbmanager] Fix Update SQL Layer converts query to table name

Fixes #19843

History

#1 Updated by Giovanni Manghi over 5 years ago

  • Status changed from Open to Feedback

Does it work as expected on 2.18?

#2 Updated by Alessandro Pasotti over 5 years ago

  • Assignee set to Alessandro Pasotti

#3 Updated by Alessandro Pasotti over 5 years ago

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

#4 Updated by Jérôme Guélat over 5 years ago

I guess this is the same #18244

Thanks for fixing it!

#5 Updated by Jürgen Fischer over 5 years ago

#6 Updated by Anonymous over 5 years ago

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

#7 Updated by Alessandro Pasotti over 5 years ago

  • Resolution set to fixed/implemented

#8 Updated by Alessandro Pasotti over 5 years ago

  • Duplicated by Bug report #19926: Editing the expression for a SQL layer corrupts the SQL expression added

#9 Updated by Lars Kellogg-Stedman over 5 years ago

Should this be fixed in 208571a? With that version of the code, selecting "Update SQL Layer..." still produces a database error, although a slightly different one:

near "(": syntax error

And instead of treating the existing query as a table name, it is now trying to surround the entire expression with parentheses:

(select ... from ...)

This appears to be an artifact of how the query expression is stored; looking at the layer source, I see:

dbname='lic.db' table="(select ... from ...)" (geom) sql=

Those parentheses result in the syntax error when trying to execute the expression in the DB manager.

#10 Updated by Giovanni Manghi over 5 years ago

Lars Kellogg-Stedman wrote:

Should this be fixed in 208571a? With that version of the code, selecting "Update SQL Layer..." still produces a database error, although a slightly different one:

this commit is more recent than the one of the fix. Please file a new ticket then.

#11 Updated by Lars Kellogg-Stedman over 5 years ago

Sure. I thought this was the same problem (e.g., the fix was not actually a complete fix) and was more appropriate here, but I'm happy to open a new ticket instead.

Also available in: Atom PDF