Thread: Discrepancy in query output
My following query sometimes gives 72 rows and sometimes gives 71 rows as output. How could it misses one?
SELECT distinct on (st.gid) sv.timing, st.gid, st.area
FROM servers sv
INNER JOIN sites st
ON (st_distance(sv.geom, st.geom)<8.5) where
sv.workmode = 1 AND sv.timing >= timestamp '2018-02-02 00:00:00'
and sv.timing <= timestamp '2018-02-02 23:59:59'
Specially, if I remove the timing condition and want to select all from servers then it misses one. How is it possible?
On Thu, Feb 22, 2018 at 3:20 PM, Lahari Sengupta <jhinik8@gmail.com> wrote:
My following query sometimes gives 72 rows and sometimes gives 71 rows as output. How could it misses one?SELECT distinct on (st.gid) sv.timing, st.gid, st.areaFROM servers svINNER JOIN sites stON (st_distance(sv.geom, st.geom)<8.5) wheresv.workmode = 1 AND sv.timing >= timestamp '2018-02-02 00:00:00'and sv.timing <= timestamp '2018-02-02 23:59:59'
Specially, if I remove the timing condition and want to select all from servers then it misses one. How is it possible?
st_distance(sv.geom, st.geom)<8.5) where
Apparently st_distance() is non-deterministic in its output of float representations. Its seems likely (though I'm by no means fluent on floating-point concerns) to be a problem specific to the implementation of that function and thus better asked on a PostGIS list. You should identify the "extra" row and see if you can include it in a self-contained query that demonstrates occasionally returning false for the condition when normally it returns true.
David J.
Ok. I tried to find out the extra row. And single with it returns true.
On Thu, Feb 22, 2018 at 5:54 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
Specially, if I remove the timing condition and want to select all from servers then it misses one. How is it possible?st_distance(sv.geom, st.geom)<8.5) whereApparently st_distance() is non-deterministic in its output of float representations. Its seems likely (though I'm by no means fluent on floating-point concerns) to be a problem specific to the implementation of that function and thus better asked on a PostGIS list. You should identify the "extra" row and see if you can include it in a self-contained query that demonstrates occasionally returning false for the condition when normally it returns true.David J.