Error in msPostGISLayerWhichShapes() using !BOX! in layer query

[lcivik]

Hi, I'using java mapscript api to generate map image form mapserver, using source code currently available on svn. When a layer contains a data query with !BOX!, i obtain an error like this "msPostGISLayerWhichShapes(): Query error. Error (ERROR: syntax error at end of input LINE 1: ...9663895349 51.7856435305264))',4326)) as foo where ....", but the query is right and when i visualize the map through http request all works fine. It seems that !BOX! variable isn't handled correctly and that a "where" clausule is added to the original query. In the attach you can see the layer that causes the error.

[pramsey]

Can you turn log_statement to all in pgsql and capture the actual SQL sent to the database please? and provide the java mapscript calls you are making? Thanks.

[lcivik]

I have attached the part of pgsql log referred to error. It seems that mapscript api adds an unrequired where clausule. The java code I use is the following:

String gisMap="/home/mapserver/maps/France/france.map"
mapObj map;
imageObj image;
map = new mapObj(gisMap);
map.setExtent(xStart, yEnd, xEnd, yStart);
map.setSize(tileSize * cropMult, tileSize * cropMult);
image = map.draw();

[pramsey]

I'm sure that I actually do see the error... what happens when you plug the offending SQL into psql directly?

select * from (                                                                                     
SELECT 
distinct a.id, b.gid as gid, a.featid as above_nw, c.frc, 
  ST_Intersection(ST_Buffer(b.geom, 4E-4), d.geom) as geom         
FROM mn_se a, mn_ps b, mn_nw c, mn_nw d
where a.feattyp = 4110 and a.elev = 1 and a.id = b.id and a.featid = c.id and 
  ST_Touches(ST_EndPoint(c.geom), d.geom) and 
  (c.geom && GeomFromText('POLYGON((0 0,0 0,0 0,0 0,0 0))',4326)) and 
  (b.geom && GeomFromText('POLYGON((0 0,0 0,0 0,0 0,0 0))',4326))
) as foo where false limit 0

[lcivik]

The above SQL statement runs successfully in psql.

[pramsey]

Can you attach a less edited version of the log? It makes no sense that a SQL statement sent from mapserver would cause an error while that same statement run locally would not. Try the cut/paste tricks yourself and see if you can find the exact SQL statement that fails.

[lcivik]

Hi, I attached a more readable version of pgsql log file. The error appears at tghe last two lines:

2010-02-19 10:41:02 CET ERROR:  syntax error at end of input at character 942
2010-02-19 10:41:02 CET STATEMENT:  select "frc",encode(AsBinary(force_collection(force_2d("geom")),'NDR'),'hex') as geom,"gid" from (         SELECT distinct a.id, b.gid as gid, a.featid as above_nw, c.frc, ST_Intersection(ST_Buffer(b.geom, 4E-4), d.geom) as geom         FROM mn_se a,               mn_ps b,              mn_nw c,            mn_nw d         where a.feattyp = 4110         and a.elev = 1         and a.id = b.id and a.featid = c.id         and ST_Touches(ST_EndPoint(c.geom), d.geom)         and (c.geom && GeomFromText('POLYGON((-5.67519663895349 51.7856435305264,-5.67519663895349 51.8617433222587,-5.55207438828265 51.8617433222587,-5.55207438828265 51.7856435305264,-5.67519663895349 51.7856435305264))',4326))         and (b.geom && GeomFromText('POLYGON((-5.67519663895349 51.7856435305264,-5.67519663895349 51.8617433222587,-5.55207438828265 51.8617433222587,-5.55207438828265 51.7856435305264,-5.67519663895349 51.7856435305264))',4326))) as foo where 

It seems that query misses "false limit 0" after "where" keyword. This happens only when I use mapscript api, not when I visualize the map through http call (e.g ​http://100.43.36.26/cgi-bin/mapserv.fcgi). Maybe there is something wrong in the api.

[pramsey]

Right, and not sure what you're doing to your script, but the log file says the client disconnects suddenly... crashing? Which would speak not to a syntax error but a more subtle memory error triggered by the mapscript API. If you could re-write your code using Perl mapscript to trigger it, and provide a small example data set and map file, I might be able to fix it.