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

Hi,

I'm have the following query:
select count(*) from delivery where "creation_date" <= TIMESTAMP
'2003-04-01 00:00:00' and "creation_date" > TIMESTAMP '2003-03-01
00:00:00';

without any index the range query returns the correct result namely
272394, when i create an index on creation_date,
I get 10371 as a result.

I'm using 7.3.3 on Linux (gentoo).

Any help appreciated, if you need more information I'm happy to provide
it.

Here is a transcript:

gaiaperformance=> select count(*) from delivery where "creation_date"
<= TIMESTAMP '2003-04-01 00:00:00' and "creation_date" > TIMESTAMP
'2003-03-01 00:00:00';
count
--------
272394
(1 row)

gaiaperformance=> create index creation_date_ind on delivery
(creation_date);
CREATE INDEX
gaiaperformance=> select count(*) from delivery where "creation_date"
<= TIMESTAMP '2003-04-01 00:00:00' and "creation_date" > TIMESTAMP
'2003-03-01 00:00:00';
count
-------
10371
(1 row)

christian

----------------------------------------------
Christian van der Leeden
Logic United GmbH
Tel: 089-189488-66 Mob: 0163-3747111
www.logicunited.com

Just a followup:

the reason for this misbehaviour was an invalid timestamp value.
I've tried to dump/restore the db and the restore choked on a
"incorrect timestamp" namely:
4714-11--2147483624 -2147483648:-2147483648:-2147483648 BC
(out of the dump file)

After I've elimnated the lines containing this value, and successfully
restoring the db, the transcript below worked fine (w/o/ problems)

Don't know how the values got there in the first place (everything in
the db was
created through a java app through JDBC)

Christian
P.S.: The db was created with 7.2.3 and then upgraded to 7.3.3 (now
7.3.4)

On Thursday, August 7, 2003, at 08:52 AM, Christian van der Leeden
wrote:

> Hi,
>
> I'm have the following query:
> select count(*) from delivery where "creation_date" <= TIMESTAMP
> '2003-04-01 00:00:00' and "creation_date" > TIMESTAMP '2003-03-01
> 00:00:00';
>
> without any index the range query returns the correct result namely
> 272394, when i create an index on creation_date,
> I get 10371 as a result.
>
> I'm using 7.3.3 on Linux (gentoo).
>
> Any help appreciated, if you need more information I'm happy to
> provide it.
>
> Here is a transcript:
>
> gaiaperformance=> select count(*) from delivery where "creation_date"
> <= TIMESTAMP '2003-04-01 00:00:00' and "creation_date" > TIMESTAMP
> '2003-03-01 00:00:00';
> count
> --------
> 272394
> (1 row)
>
> gaiaperformance=> create index creation_date_ind on delivery
> (creation_date);
> CREATE INDEX
> gaiaperformance=> select count(*) from delivery where "creation_date"
> <= TIMESTAMP '2003-04-01 00:00:00' and "creation_date" > TIMESTAMP
> '2003-03-01 00:00:00';
> count
> -------
> 10371
> (1 row)
>
>
> christian
>
>
>
> ----------------------------------------------
> Christian van der Leeden
> Logic United GmbH
> Tel: 089-189488-66 Mob: 0163-3747111
> www.logicunited.com
> <Christian van der Leeden.vcf>
> ----------------------------------------------
> Christian van der Leeden
> Logic United GmbH
> Tel: 089-189488-66 Mob: 0163-3747111
> www.logicunited.com
> <Christian van der Leeden.vcf>
>
----------------------------------------------
Christian van der Leeden
Logic United GmbH
Tel: 089-189488-66 Mob: 0163-3747111
www.logicunited.com

Christian van der Leeden <lists(at)logicunited(dot)com> writes:
> without any index the range query returns the correct result namely
> 272394, when i create an index on creation_date,
> I get 10371 as a result.

This is a tad hard to believe :-(.

Could we see the full schema for the table? ("pg_dump -s -t delivery"
would be best.)

regards, tom lane

Christian van der Leeden <lists(at)logicunited(dot)com> writes:
> the reason for this misbehaviour was an invalid timestamp value.
> I've tried to dump/restore the db and the restore choked on a
> "incorrect timestamp" namely:
> 4714-11--2147483624 -2147483648:-2147483648:-2147483648 BC

Hmm ... I'm suspecting that that was a "minus infinity" under the hood.
Is your installation built with integer timestamps, or floating point?
(If you're not sure, try "pg_config --configure" and see if it mentions
--enable-integer-datetimes.) Also, is the column in question of type
timestamp, or timestamp with time zone?

regards, tom lane

Hi,

I've put the database dump here:
http://www.vanderleeden.de/test/databasedumps.tar
(about 16 MB)

It contains the ascii dump of pg_dump and the
pg_dump --format c of the database.

I've only got the dumps left of the original problem,
since during my tries to remedy the problem
(upgrade to 7.3.4 with initdb and restore)
the restore failed and I don't have the original
db saved...

The db itself (only speaking for the current 7.3.4 build),
is not configured with enabled-integer-datetimes.
Creation date is defined as:
creation_date | timestamp without time zone | not null

CU

Christian

On Thursday, August 7, 2003, at 04:23 PM, Tom Lane wrote:

> Christian van der Leeden <lists(at)logicunited(dot)com> writes:
>> the reason for this misbehaviour was an invalid timestamp value.
>> I've tried to dump/restore the db and the restore choked on a
>> "incorrect timestamp" namely:
>> 4714-11--2147483624 -2147483648:-2147483648:-2147483648 BC
>
> Hmm ... I'm suspecting that that was a "minus infinity" under the hood.
> Is your installation built with integer timestamps, or floating point?
> (If you're not sure, try "pg_config --configure" and see if it mentions
> --enable-integer-datetimes.) Also, is the column in question of type
> timestamp, or timestamp with time zone?
>
> regards, tom lane
>
----------------------------------------------
Christian van der Leeden
Logic United GmbH
Tel: 089-189488-66 Mob: 0163-3747111
www.logicunited.com

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