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
BEGIN:VCARD VERSION:3.0 N:van der Leeden;Christian;;; FN:Christian van der Leeden ORG:Logic United GmbH; TITLE:Software Engineering EMAIL;type=HOME;type=pref:[EMAIL PROTECTED] EMAIL;type=HOME:[EMAIL PROTECTED] TEL;type=WORK;type=pref:+49-89-189488-66 TEL;type=CELL:+49-163-3747111 ADR;type=WORK;type=pref:;;Brecherspitzstrasse 8;Muenchen;;81541;Germany URL:www.logicunited.com END:VCARD
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
