Bug report #19956

Update SQL layer results in invalid SQL expression

Added by Lars Kellogg-Stedman almost 2 years ago. Updated almost 2 years ago.

Status:Closed
Priority:Normal
Assignee:Alessandro Pasotti
Category:DB Manager
Affected QGIS version:3.3(master) Regression?:No
Operating System: Easy fix?:No
Pull Request or Patch supplied:Yes Resolution:
Crashes QGIS or corrupts data:No Copied to github as #:27778

Description

It looks like the fix for #19843 resulted in a new problem.

With revision 208571a, 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.

Associated revisions

Revision 94ded32f
Added by Alessandro Pasotti almost 2 years ago

[db-manager] Allow multiline filters in sql window

Fixes #19956 - Update SQL layer results in invalid SQL expression

History

#1 Updated by Alessandro Pasotti almost 2 years ago

  • Assignee set to Alessandro Pasotti

#2 Updated by Alessandro Pasotti almost 2 years ago

To be sure I can reproduce exactly your scenario, can you please describe the steps starting from an unfiltered layer?

#3 Updated by Lars Kellogg-Stedman almost 2 years ago

Sure.

  1. I started with the MASSGIS TOWNS_POLY.shp dataset from https://docs.digital.mass.gov/dataset/massgis-data-community-boundaries-towns
  2. I opened this shapefile in QGIS and saved it as a Spatialite datasource
  3. I used SpatialLite -> New Connection to open the resulting data source
  4. I double clicked on the "towns" table to add it to the project
  5. I selected "Update SQL Layer..." after right clicking on the "towns" layer
  6. I updated the query to look like:
    SELECT b.town as town, b.town_id as town_id, b.geometry as geometry 
    FROM 
    "towns" as a 
    join "towns" as b on b.town = 'AMHERST' or st_touches(a.geometry, b.geometry) 
    where 
    a.town = 'AMHERST'
    
  7. I selected "Update" and closed the database manager
  8. I once again selected "Update SQL Layer..." from the layer menu.
  9. This resulted in the syntax error, and the query now looks like:
    (SELECT b.town as town, b.town_id as town_id, b.geometry as geometry 
    FROM 
    "towns" as a 
    join "towns" as b on b.town = 'AMHERST' or st_touches(a.geometry, b.geometry) 
    where a.town = 'AMHERST'
    
    )
    

I've noticed something interesting: if I keep the entire sql expression on a single line like this:

SELECT b.town as town, b.town_id as town_id, b.geometry as geometry FROM "towns" as a join "towns" as b on b.town = 'AMHERST' or st_touches(a.geometry, b.geometry) where a.town = 'AMHERST'

...then I don't get the error when subsequently editing it, and it never gets those extra parentheses.

#4 Updated by Alessandro Pasotti almost 2 years ago

Thanks for the information!
I think I know where's the issue, working on it today.

#5 Updated by Alessandro Pasotti almost 2 years ago

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

#6 Updated by Anonymous almost 2 years ago

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

Also available in: Atom PDF