Re: Follow up: range query with timestamp returns different result with index than without (7.3.3)

Christian van der Leeden <lists(at)logicunited(dot)com> writes:
> The db itself (only speaking for the current 7.3.4 build),
> is not configured with enabled-integer-datetimes.

Okay ... [experiments a bit...] ah-hah, I know what happened. Under the
hood, that value is a NaN. Observe:

-- just to ease experimenting
tsbug=# create cast (float8 as timestamp without time zone) without function;
CREATE CAST

tsbug=# select '1.8'::float8::timestamp;
timestamp
------------------------
2000-01-01 00:00:01.80
(1 row)

tsbug=# select 'NaN'::float8::timestamp;
timestamp
---------------------------------------------------------
4714-11--2147483625 2147483647:2147483647:2147483647 BC
(1 row)

NaNs behave funny in comparisons, which is doubtless what was fouling up
your index. btrees assume that the trichotomy law holds :-(.

I wonder how a NaN got in there? Anyway we probably ought to add some
defenses against it ... at least enough to ensure that timestamp indexes
stay sane.

regards, tom lane