Bug report #5883

Erroneous query: zero-length delimited identifier at or near """"

Added by Sandro Santilli about 7 years ago. Updated almost 7 years ago.

Status:Closed
Priority:Normal
Assignee:Jürgen Fischer
Category:Data Provider/PostGIS
Affected QGIS version:1.8.0 Regression?:No
Operating System: Easy fix?:No
Pull Request or Patch supplied:No Resolution:
Crashes QGIS or corrupts data:No Copied to github as #:15339

Description

SQL window, retriving the data worked, loading as layer didn't (but didn't visually report any error)

Error/debug on the console:

Debug: src/providers/postgres/qgspostgresprovider.cpp: 2683: (getGeometryDetails) Getting geometry column: SELECT "geom" FROM (select (ST_Dump(ST_DelaunayTriangles(st_collect(cell)))).* FROM hexagrid
) AS "subQuery_0" LIMIT 0
Debug: src/providers/postgres/qgspostgresconn.cpp: 1063: (retrieveLayerTypes) Retrieving geometry types: SELECT DISTINCT CASE WHEN upper(geometrytype("geom")) IN ('POINT','MULTIPOINT','POINTM','MULTIPOINTM') THEN 'POINT' WHEN upper(geometrytype("geom")) IN ('LINESTRING','MULTILINESTRING','LINESTRINGM','MULTILINESTRINGM') THEN 'LINESTRING' WHEN upper(geometrytype("geom")) IN ('POLYGON','MULTIPOLYGON','POLYGONM','MULTIPOLYGONM') THEN 'POLYGON' END, st_srid("geom") FROM (SELECT "geom" FROM ""."(select (ST_Dump(ST_DelaunayTriangles(st_collect(cell)))).* FROM hexagrid
) AS ""subQuery_0""" WHERE "geom" IS NOT NULL LIMIT 100) AS t
Debug: src/core/qgsmessagelog.cpp: 41: (logMessage) 2012-06-27T18:03:17 PostGIS[0] Erroneous query: SELECT DISTINCT CASE WHEN upper(geometrytype("geom")) IN ('POINT','MULTIPOINT','POINTM','MULTIPOINTM') THEN 'POINT' WHEN upper(geometrytype("geom")) IN ('LINESTRING','MULTILINESTRING','LINESTRINGM','MULTILINESTRINGM') THEN 'LINESTRING' WHEN upper(geometrytype("geom")) IN ('POLYGON','MULTIPOLYGON','POLYGONM','MULTIPOLYGONM') THEN 'POLYGON' END, st_srid("geom") FROM (SELECT "geom" FROM ""."(select (ST_Dump(ST_DelaunayTriangles(st_collect(cell)))).* FROM hexagrid
) AS ""subQuery_0""" WHERE "geom" IS NOT NULL LIMIT 100) AS t returned 7 [ERROR:  zero-length delimited identifier at or near """" 
LINE 1: ...ON' END, st_srid("geom") FROM (SELECT "geom" FROM ""."(selec...
                                                             ^

Associated revisions

History

#1 Updated by Paolo Cavallini about 7 years ago

  • Assignee set to Giuseppe Sucameli

#2 Updated by Giuseppe Sucameli about 7 years ago

  • Category changed from DB Manager to Data Provider/PostGIS

It's a bug on the PG provider. The schema part is added also for queries (a check is missing).

#3 Updated by Jürgen Fischer about 7 years ago

SQL window, retriving the data worked, loading as layer didn't (but didn't visually report any error)

Can you reproduce the problem with something that doesn't need the latest postgis?

BTW you should "visually" see the error in the message log (and the popup over the in the main window status bar).

#4 Updated by James Stott about 7 years ago

I have changed my code slightly from the original code I posted on the mailing list and I get a slightly different error:

This query works and adds a layer into QGIS:

uri.setDataSource('',"(SELECT int4(row_number() OVER ()) AS id,ST_GeomFromText('POINT(451583 129144)',27700) As geom)", "geom",'',"id")

The following doesn't work:

uri.setDataSource('',"(SELECT * From \\"designations\\".\\"table\\" where ST_DWithin(ST_GeomFromText('POINT(451583 129144)',27700), \\"table\\".\\"geom\\", 2000)", "geom",'',"gid")

This gives the following error:

Unable to execute the query.
The error message from the database was:
ERROR: syntax error at or near "AS"
LINE 1: ... 129144)',27700), "listed_building"."geom", 2000) AS "subQue...
^
.
SQL: SELECT * FROM (SELECT * From "designations"."listed_building" where ST_DWithin(ST_GeomFromText('POINT(451583 129144)',27700), "listed_building"."geom", 2000) AS "subQuery_0" LIMIT 1

Does seem like there is an extra SELECT statement added to the from of the query as it says SELECT * FROM (SELECT * From. The from in my code is From not FROM.

#5 Updated by Jürgen Fischer about 7 years ago

James Stott wrote:

The following doesn't work:

uri.setDataSource('',"(SELECT * From \\"designations\\".\\"table\\" where ST_DWithin(ST_GeomFromText('POINT(451583 129144)',27700), \\"table\\".\\"geom\\", 2000)", "geom",'',"gid")

And that's not just because the closing paren is missing after "2000)"?

#6 Updated by James Stott about 7 years ago

Adding the ) leads to this error:

Unable to execute the query.
The error message from the database was:
ERROR: subquery in FROM must have an alias
LINE 1: SELECT * FROM (SELECT * From designations.listed_building wh...
^
HINT: For example, FROM (SELECT ...) [AS] foo.
.
SQL: SELECT * FROM (SELECT * From designations.listed_building where ST_DWithin(ST_GeomFromText('POINT',27700), listed_building.geom, 2000)), geom,'',gid) AS "subQuery_0" LIMIT 1

#7 Updated by Jürgen Fischer about 7 years ago

James Stott wrote:

SQL: SELECT * FROM (SELECT * From designations.listed_building where ST_DWithin(ST_GeomFromText('POINT',27700), listed_building.geom, 2000)), geom,'',gid) AS "subQuery_0" LIMIT 1

Please check the syntax again. Looks like you misplaced the " this time ;)

#8 Updated by James Stott about 7 years ago

Apologies, it was a syntax problem.

This worked:

query = "(SELECT * From \\"designations\\".\\"table\\"where ST_DWithin(ST_GeomFromText('POINT(" + str(self.dlg.getEasting()) + " " + str(self.dlg.getNorthing()) + ")',27700), \\"table\\".\\"geom\\","+ self.dlg.getBuffSize() +"))"
uri.setDataSource('', query, "geom",'',"gid")

#9 Updated by Paolo Cavallini about 7 years ago

  • Target version set to Version 2.0.0

#10 Updated by Giuseppe Sucameli almost 7 years ago

  • Status changed from Open to Feedback

Jürgen Fischer wrote:

SQL window, retriving the data worked, loading as layer didn't (but didn't visually report any error)

Can you reproduce the problem with something that doesn't need the latest postgis?

BTW you should "visually" see the error in the message log (and the popup over the in the main window status bar).

strk, could you please provide more info?

#11 Updated by Sandro Santilli almost 7 years ago

This query doesn't need the latest postgis, nor any table:

select (ST_Dump(ST_ConvexHull(st_collect('POINT(0 0)'::geometry)))).*

Try it, replacing "unioned" and "wrongsnap" with a valid geometry column and table name.
It's still failing as of:

QGIS version 1.9.0-Master
QGIS code revision fe8385e
Compiled against Qt 4.8.1
Running against Qt 4.8.1
Compiled against GDAL/OGR 2.0dev
Running against GDAL/OGR 2.0dev
GEOS Version 3.4.0dev
PostgreSQL Client Version 9.1.5
SpatiaLite Version 2.4.0
QWT Version 5.2.2
PROJ.4 Version 480

This copy of QGIS writes debugging output.

I still tried trough the DBManager, don't know if it's possible (or how) to reproduce directly against the PostGIS Data Provider. Maybe Giuseppe knows ?

#12 Updated by Giuseppe Sucameli almost 7 years ago

Sandro Santilli wrote:

I still tried trough the DBManager, don't know if it's possible (or how) to reproduce directly against the PostGIS Data Provider. Maybe Giuseppe knows ?

Try the following code from QGis Python Console:

uri = QgsDataSourceURI()
uri.setConnection(host, u"%s" % port, dbname, user, passw)
uri.setDataSource( "", u"(%s\
)" % query, geomcol, "", keycol )

vl = QgsVectorLayer( uri.uri(), "layername", "postgres" )
QgsMapLayerRegistry.instance().addMapLayer( vl )

#13 Updated by Jürgen Fischer almost 7 years ago

Giuseppe Sucameli wrote:

Try the following code from QGis Python Console:
[...]

Should that reproduce the problem? Because it doesn't here.

#14 Updated by Giuseppe Sucameli almost 7 years ago

Jürgen Fischer wrote:

Giuseppe Sucameli wrote:

Try the following code from QGis Python Console:
[...]

Should that reproduce the problem? Because it doesn't here.

I know, neither here. But that code is quite identical to the one used in DBManager.

#15 Updated by Sandro Santilli almost 7 years ago

I've no time to debug this. Can't you reproduce it there Giuseppe ?

#16 Updated by Giuseppe Sucameli almost 7 years ago

  • Assignee changed from Giuseppe Sucameli to Jürgen Fischer
  • Status changed from Feedback to Open

Sandro Santilli wrote:

I've no time to debug this. Can't you reproduce it there Giuseppe ?

Not reproduced yet, anyway I've found it.

The problem is at https://github.com/qgis/Quantum-GIS/blob/master/src/providers/postgres/qgspostgresconn.cpp#L1027, so you're using estimated metadata for the connection.

@Jef: I've assigned the ticket to you, I don't know how to handle estimated metadata option together with queries.

#17 Updated by Sandro Santilli almost 7 years ago

No way to estimate extent of a query. You must necessarely run it.

#19 Updated by Giuseppe Sucameli almost 7 years ago

select 1 as id, (ST_Dump(ST_ConvexHull(st_collect('POINT(0 0)'::geometry)))).* FROM "mytablename" 

It works until the "use table estimated metadata" option is checked in the connection settings, in that case I get the following error:

Erroneous query: SELECT DISTINCT CASE WHEN upper(geometrytype("geom")) IN ('POINT','MULTIPOINT','POINTM','MULTIPOINTM') THEN 'POINT' WHEN upper(geometrytype("geom")) IN ('LINESTRING','MULTILINESTRING','LINESTRINGM','MULTILINESTRINGM') THEN 'LINESTRING' WHEN upper(geometrytype("geom")) IN ('POLYGON','MULTIPOLYGON','POLYGONM','MULTIPOLYGONM') THEN 'POLYGON' END, st_srid("geom") FROM (SELECT "geom" FROM ""."(select 1 as id, (ST_Dump(ST_ConvexHull(st_collect('POINT'::geometry)))).* FROM ""province""
) AS ""subQuery_0""" WHERE "geom" IS NOT NULL LIMIT 100) AS t returned 7 [ERROR: zero-length delimited identifier at or near """"
LINE 1: ...ON' END, st_srid("geom") FROM (SELECT "geom" FROM ""."(selec...
^
]

EDIT:

the problem occurs also with very simple queries like:

SELECT 1 as "id", 'POINT(1 10)'::geometry as "geom" 

#20 Updated by Jürgen Fischer almost 7 years ago

  • Status changed from Open to Closed

Also available in: Atom PDF