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

qgib · 2012-06-27T07:09:07Z

Author Name: Sandro Santilli (@strk)
Original Redmine Issue: 5883
Affected QGIS version: 1.8.0
Redmine category:data_provider/postgis
Assignee: Jürgen Fischer

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...
                                                             ^

The text was updated successfully, but these errors were encountered:

qgib · 2012-06-27T07:12:31Z

Author Name: Paolo Cavallini (@pcav)

assigned_to_id was configured as Giuseppe Sucameli

qgib · 2012-06-27T23:08:40Z

Author Name: Giuseppe Sucameli (@brushtyler)

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

category_id was changed from DB Manager to Data Provider/PostGIS

qgib · 2012-06-27T23:23:53Z

Author Name: Jürgen Fischer (@jef-n)

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).

qgib · 2012-06-27T23:34:12Z

Author Name: James Stott (@jamesstott)

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.

qgib · 2012-06-28T01:08:55Z

Author Name: Jürgen Fischer (@jef-n)

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)"?

qgib · 2012-06-28T01:51:54Z

Author Name: James Stott (@jamesstott)

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(451583 129144)',27700), listed_building.geom, 2000)), geom,'',gid) AS "subQuery_0" LIMIT 1

qgib · 2012-06-28T02:28:17Z

Author Name: Jürgen Fischer (@jef-n)

James Stott wrote:

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

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

qgib · 2012-06-28T02:37:56Z

Author Name: James Stott (@jamesstott)

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")@

qgib · 2012-09-04T10:06:57Z

Author Name: Paolo Cavallini (@pcav)

fixed_version_id was configured as Version 2.0.0

qgib · 2012-10-01T14:59:37Z

Author Name: Giuseppe Sucameli (@brushtyler)

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?

status_id was changed from Open to Feedback

qgib · 2012-10-01T22:38:12Z

Author Name: Sandro Santilli (@strk)

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 ?

qgib · 2012-10-02T05:59:31Z

Author Name: Giuseppe Sucameli (@brushtyler)

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 )

qgib · 2012-10-02T06:21:39Z

Author Name: Jürgen Fischer (@jef-n)

Giuseppe Sucameli wrote:

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

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

qgib · 2012-10-02T06:47:15Z

Author Name: Giuseppe Sucameli (@brushtyler)

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.

qgib · 2012-10-02T07:00:00Z

Author Name: Sandro Santilli (@strk)

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

qgib · 2012-10-02T07:27:59Z

Author Name: Giuseppe Sucameli (@brushtyler)

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.

assigned_to_id was changed from Giuseppe Sucameli to Jürgen Fischer
status_id was changed from Feedback to Open

qgib · 2012-10-02T07:34:15Z

Author Name: Sandro Santilli (@strk)

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

qgib · 2012-10-02T07:36:07Z

Author Name: Sandro Santilli (@strk)

See also http://trac.osgeo.org/postgis/ticket/510

qgib · 2012-10-03T13:19:06Z

Author Name: Giuseppe Sucameli (@brushtyler)

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(0 0)'::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"

qgib · 2012-10-04T02:16:04Z

Author Name: Jürgen Fischer (@jef-n)

Fixed in changeset "6aa7524482f176aaf603ff47b171a1c3e3c2fc62".

status_id was changed from Open to Closed

…5339)

qgib added Bug Either a bug report, or a bug fix. Let's hope for the latter! Data Provider Related to specific vector, raster or mesh data providers labels May 24, 2019

qgib added this to the Version 2.0.0 milestone May 24, 2019

qgib closed this as completed May 24, 2019

This was referenced May 25, 2019

gdalwarp issues with the "te" parameter #24203

Closed

Processing: error when to trying to reproject layer #24425

Closed

JamesShaeffer pushed a commit to JamesShaeffer/QGIS that referenced this issue Nov 20, 2019

[processing] don't apply -te and -te_srs if not requested (fix qgis#1…

8884e59

…5339)

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

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

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

qgib commented Jun 27, 2012

qgib commented Jun 27, 2012

qgib commented Jun 27, 2012

qgib commented Jun 27, 2012

qgib commented Jun 27, 2012

qgib commented Jun 28, 2012

qgib commented Jun 28, 2012

qgib commented Jun 28, 2012

qgib commented Jun 28, 2012

qgib commented Sep 4, 2012

qgib commented Oct 1, 2012

qgib commented Oct 1, 2012

qgib commented Oct 2, 2012

qgib commented Oct 2, 2012

qgib commented Oct 2, 2012

qgib commented Oct 2, 2012

qgib commented Oct 2, 2012

qgib commented Oct 2, 2012

qgib commented Oct 2, 2012

qgib commented Oct 3, 2012

qgib commented Oct 4, 2012

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

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

Comments

qgib commented Jun 27, 2012

qgib commented Jun 27, 2012

qgib commented Jun 27, 2012

qgib commented Jun 27, 2012

qgib commented Jun 27, 2012

qgib commented Jun 28, 2012

qgib commented Jun 28, 2012

qgib commented Jun 28, 2012

qgib commented Jun 28, 2012

qgib commented Sep 4, 2012

qgib commented Oct 1, 2012

qgib commented Oct 1, 2012

qgib commented Oct 2, 2012

qgib commented Oct 2, 2012

qgib commented Oct 2, 2012

qgib commented Oct 2, 2012

qgib commented Oct 2, 2012

qgib commented Oct 2, 2012

qgib commented Oct 2, 2012

qgib commented Oct 3, 2012

qgib commented Oct 4, 2012