Re: Follow up: range query with timestamp returns different result with index than without (7.3.3) - Mailing list pgsql-bugs

Christian van der Leeden <lists@logicunited.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