[mapserver-users] Mapfile - filter by parameter - full scan

[Lane, Michael]

Hi,

Experiencing something not expected with the filter condition in mapfile.

I add this code in my mapFile to have the possibility to void the filter if no parameter is given:

VALIDATION
       no_diag '^[0-9]{1,2}$'
       default_no_diag '0'
 END
 DATA "geom_surf from LOCTS_PARC_AGRI_AN_COUR using srid 4326"
  FILTER  (([no_diag]='%no_diag%') or ('%no_diag%' = '0'))
I compare my execution time without parameter and it’s going to an execution time of 3 seconds before filter to one of 3 minutes with the filter.

After debugging, I realize that the « where clause » generated by MapServe were bad with the filter and that generate a full table scan :
WHERE   ( '0' = '0' ) OR ( no_diag = '0' )  AND …
I was expecting something more like :
WHERE   (( '0' = '0' ) OR ( no_diag = '0' ))  AND …
So I had to add more parenthesis to the filter to get it correct
((('%no_diag%' = '0') or ([no_diag]='%no_diag%')))
Is it normal that I have to do that? The examples I found on the internet, have always single parenthesis. Do I code it right?

I use Mapserver 7 with database Oracle 12c. My parameter no_diag is a number.

Thanks

Michael Lane
Technicien en informatique

Direction des solutions d'affaires
La Financière agricole du Québec
1400, boul. Guillaume-Couture
Lévis (Québec) G6W 8K7
Tél. : 418 838-5614, poste 6034
michael.lane at fadq.qc.ca<mailto:Michael.Lane at fadq.qc.ca>

[cid:image001.jpg at 01D27723.BAB8F550]
[cid:image002.jpg at 01D27723.BAB8F550]
[cid:image003.jpg at 01D27723.BAB8F550]
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20170125/11633952/attachment.htm>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.jpg
Type: image/jpeg
Size: 1767 bytes
Desc: image001.jpg
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20170125/11633952/attachment.jpg>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image002.jpg
Type: image/jpeg
Size: 2681 bytes
Desc: image002.jpg
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20170125/11633952/attachment-0001.jpg>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image003.jpg
Type: image/jpeg
Size: 2022 bytes
Desc: image003.jpg
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20170125/11633952/attachment-0002.jpg>