PostGIS provider appears to be double-quoting geometry columns

[jbirch]

This appears to be a problem in the filter application, and is quite noticible with MapGuide. MapGuide attempts a three-phase query, the last of which is an unfiltered selection. When querying PostGIS, the first two queries fail with a warning like this:

DECLARE crsselcmd_118158d9f60b36a01e666071e02bf774 CURSOR FOR SELECT "public"."county_bnd".geom FROM "public"."county_bnd" WHERE  ( geom && GeomFromWKB(decode(''01030000000100000005000000A8101DFEC4070041FDFFFF7F519C3641D6BB780834242641FDFFFF7F519C3641D6BB78083424264103000020B75F3D41A8101DFEC407004103000020B75F3D41A8101DFEC4070041FDFFFF7F519C3641'', 'hex'),26767) AND Intersects(geom,GeomFromWKB(decode(''01030000000100000005000000A8101DFEC4070041FDFFFF7F519C3641D6BB780834242641FDFFFF7F519C3641D6BB78083424264103000020B75F3D41A8101DFEC407004103000020B75F3D41A8101DFEC4070041FDFFFF7F519C3641'', 'hex'),26767)) ) 
Number of parameters: 0
23:57:14:946 (0x00000210) :    ERROR: SQL command failed: [PGRES_FATAL_ERROR] ERROR:  syntax error at or near "01030000000100000005000000"
LINE 1: ...county_bnd" WHERE  ( geom && GeomFromWKB(decode(''0103000000...

This appears to be a problem that was introduced with the new automatic quoting of the geometry hex value. It appears that there may be extra quotes added on the following lines:

​http://trac.osgeo.org/fdo/browser/trunk/Providers/PostGIS/Src/Provider/FilterProcessor.cpp#L311

​http://trac.osgeo.org/fdo/browser/trunk/Providers/PostGIS/Src/Provider/FilterProcessor.cpp#L435

I suppose it is possible there are more?

[mloskot]

Oh, Mom! This bug clearly looks like caused by patches paplied for ticket #104. OK, let's leave the #104 closed and stick to fixing this one.

The problem is that hex string with geometry is quoted first time by ExpressionProcessor, for examplein ExpressionProcessor::ProcessGeometryValue(), and next it's passed to FilterProcessor where geometry string is quoted again with single-quotes. In result, geometry string encoded in HEX is double quoted with single-quotes.

[jbirch]

Patch to remove "double" single quotes.

[jbirch]

This simple patch seems to fix the problem in my testing.

[mloskot]

The patch has been applied to the trunk (r3504). Closing this ticket.