[mapserver-users] Postgis query bug?

[pcreso at pcreso.com]

Hi,

I can't get an error with teh sql via psql, but only mapserver, so I'll ask here.

I'm using a mapfile with a DATA statement similar to the ones at:
http://mapserver.org/input/vector/postgis.html

The data statement is below...

The random() field, combined with order by & limit is to generate a random subset of records from each query.   

I can order by a "genuine" int field & get the expected result in mapserver (WFS) response. I can order by a calculated field 'random() as rand' and get the expected random sorted list back. I can impose a limit 5 on the fixed order by field, & get the 5 records back. All work with the SQL in the psql commandline & the mapfile.

However, if I try to order by rand then limit 5, it works fine from the command line, but returns no records/features via WFS/mapserver. 

So, with teh layer below, if I change the field to order by to atlas_id (an int in the view being queried) it works. If I leave the order by as rand ("random() as rand" in the view being queried) with no "limit 5" I get a valid response with some 250 records via WFS, if I use "limit n" with the datasets ordered by rand, I get zero records returned via WFS, but the metadata shows the data extent to be correct.

Any advice appreciated...

Brent Wood

    DATA "startp from ( select atlas_id, 
                             filename,
                             startp,
                             rand
                      from
                           t_station t,
                           V_atlas_id a
                      where t.trip_code = 'tan0906'
                        and t.station_no = 241
                        and t.station_no=a.station_no
                        and t.trip_code=a.trip_code 
                      order by rand
                      limit 5
                       ) as myquery
                       using unique atlas_id using srid=4326"