[BUGS] 7.3 interval casting broken (7.4 OK)

#1

The interval casting in 7.3 is ignoring precision.

This is the test script followed by the 7.3 and 7.4
output.

--------------------------------------------------------------------------
Repro script
--------------------------------------------------------------------------
#!/bin/bash

psql -e << END
drop table intervalbug;
create table intervalbug (
ts timestamp,
age interval);

insert into intervalbug (ts) values ( now() );
END

psql -ec "insert into intervalbug (ts) values ( now() );";
sleep 3;
psql -ec "insert into intervalbug (ts) values ( now() );";
sleep 3;
psql -ec "insert into intervalbug (ts) values ( now() );";
sleep 3;
psql -ec "insert into intervalbug (ts) values ( now() );";

psql -ec "update intervalbug set age=( (now() - ts )::interval );";

psql -e << SELECT
select ts, age, age::interval(0) from intervalbug;
select ts, age, age::interval(1) from intervalbug;
select ts, age, age::interval(2) from intervalbug;
select ts, age, age::interval(3) from intervalbug;
select ts, age, age::interval(4) from intervalbug;
SELECT
--------------------------------------------------------------------------
7.3 output
--------------------------------------------------------------------------
drop table intervalbug;
DROP TABLE
create table intervalbug (
ts timestamp,
age interval);
CREATE TABLE
insert into intervalbug (ts) values ( now() );
INSERT 3885035 1
insert into intervalbug (ts) values ( now() );
INSERT 3885036 1
insert into intervalbug (ts) values ( now() );
INSERT 3885037 1
insert into intervalbug (ts) values ( now() );
INSERT 3885038 1
insert into intervalbug (ts) values ( now() );
INSERT 3885039 1
update intervalbug set age=( (now() - ts )::interval );
UPDATE 5
select ts, age, age::interval(0) from intervalbug;
ts | age | age
----------------------------+-----------------+-----------------
2003-10-18 11:15:55.932153 | 00:00:09.150609 | 00:00:09.150609
2003-10-18 11:15:55.95468 | 00:00:09.128082 | 00:00:09.128082
2003-10-18 11:15:58.982379 | 00:00:06.100383 | 00:00:06.100383
2003-10-18 11:16:02.022363 | 00:00:03.060399 | 00:00:03.060399
2003-10-18 11:16:05.062344 | 00:00:00.020418 | 00:00:00.020418
(5 rows)

select ts, age, age::interval(1) from intervalbug;
ts | age | age
----------------------------+-----------------+-----------------
2003-10-18 11:15:55.932153 | 00:00:09.150609 | 00:00:09.150609
2003-10-18 11:15:55.95468 | 00:00:09.128082 | 00:00:09.128082
2003-10-18 11:15:58.982379 | 00:00:06.100383 | 00:00:06.100383
2003-10-18 11:16:02.022363 | 00:00:03.060399 | 00:00:03.060399
2003-10-18 11:16:05.062344 | 00:00:00.020418 | 00:00:00.020418
(5 rows)

select ts, age, age::interval(2) from intervalbug;
ts | age | age
----------------------------+-----------------+-----------------
2003-10-18 11:15:55.932153 | 00:00:09.150609 | 00:00:09.150609
2003-10-18 11:15:55.95468 | 00:00:09.128082 | 00:00:09.128082
2003-10-18 11:15:58.982379 | 00:00:06.100383 | 00:00:06.100383
2003-10-18 11:16:02.022363 | 00:00:03.060399 | 00:00:03.060399
2003-10-18 11:16:05.062344 | 00:00:00.020418 | 00:00:00.020418
(5 rows)

select ts, age, age::interval(3) from intervalbug;
ts | age | age
----------------------------+-----------------+-----------------
2003-10-18 11:15:55.932153 | 00:00:09.150609 | 00:00:09.150609
2003-10-18 11:15:55.95468 | 00:00:09.128082 | 00:00:09.128082
2003-10-18 11:15:58.982379 | 00:00:06.100383 | 00:00:06.100383
2003-10-18 11:16:02.022363 | 00:00:03.060399 | 00:00:03.060399
2003-10-18 11:16:05.062344 | 00:00:00.020418 | 00:00:00.020418
(5 rows)

select ts, age, age::interval(4) from intervalbug;
ts | age | age
----------------------------+-----------------+-----------------
2003-10-18 11:15:55.932153 | 00:00:09.150609 | 00:00:09.150609
2003-10-18 11:15:55.95468 | 00:00:09.128082 | 00:00:09.128082
2003-10-18 11:15:58.982379 | 00:00:06.100383 | 00:00:06.100383
2003-10-18 11:16:02.022363 | 00:00:03.060399 | 00:00:03.060399
2003-10-18 11:16:05.062344 | 00:00:00.020418 | 00:00:00.020418
(5 rows)

--------------------------------------------------------------------------
7.4 output
--------------------------------------------------------------------------
drop table intervalbug;
DROP TABLE
create table intervalbug (
ts timestamp,
age interval);
CREATE TABLE
insert into intervalbug (ts) values ( now() );
INSERT 74509 1
insert into intervalbug (ts) values ( now() );
INSERT 74510 1
insert into intervalbug (ts) values ( now() );
INSERT 74511 1
insert into intervalbug (ts) values ( now() );
INSERT 74512 1
insert into intervalbug (ts) values ( now() );
INSERT 74513 1
update intervalbug set age=( (now() - ts )::interval );
UPDATE 5
select ts, age, age::interval(0) from intervalbug;
ts | age | age
----------------------------+-----------------+----------
2003-10-18 11:15:29.994162 | 00:00:09.128228 | 00:00:09
2003-10-18 11:15:30.014942 | 00:00:09.107448 | 00:00:09
2003-10-18 11:15:33.040811 | 00:00:06.081579 | 00:00:06
2003-10-18 11:15:36.072885 | 00:00:03.049505 | 00:00:03
2003-10-18 11:15:39.10362 | 00:00:00.01877 | 00:00:00
(5 rows)

select ts, age, age::interval(1) from intervalbug;
ts | age | age
----------------------------+-----------------+-------------
2003-10-18 11:15:29.994162 | 00:00:09.128228 | 00:00:09.10
2003-10-18 11:15:30.014942 | 00:00:09.107448 | 00:00:09.10
2003-10-18 11:15:33.040811 | 00:00:06.081579 | 00:00:06.10
2003-10-18 11:15:36.072885 | 00:00:03.049505 | 00:00:03
2003-10-18 11:15:39.10362 | 00:00:00.01877 | 00:00:00
(5 rows)

select ts, age, age::interval(2) from intervalbug;
ts | age | age
----------------------------+-----------------+-------------
2003-10-18 11:15:29.994162 | 00:00:09.128228 | 00:00:09.13
2003-10-18 11:15:30.014942 | 00:00:09.107448 | 00:00:09.11
2003-10-18 11:15:33.040811 | 00:00:06.081579 | 00:00:06.08
2003-10-18 11:15:36.072885 | 00:00:03.049505 | 00:00:03.05
2003-10-18 11:15:39.10362 | 00:00:00.01877 | 00:00:00.02
(5 rows)

select ts, age, age::interval(3) from intervalbug;
ts | age | age
----------------------------+-----------------+--------------
2003-10-18 11:15:29.994162 | 00:00:09.128228 | 00:00:09.128
2003-10-18 11:15:30.014942 | 00:00:09.107448 | 00:00:09.107
2003-10-18 11:15:33.040811 | 00:00:06.081579 | 00:00:06.082
2003-10-18 11:15:36.072885 | 00:00:03.049505 | 00:00:03.05
2003-10-18 11:15:39.10362 | 00:00:00.01877 | 00:00:00.019
(5 rows)

select ts, age, age::interval(4) from intervalbug;
ts | age | age
----------------------------+-----------------+---------------
2003-10-18 11:15:29.994162 | 00:00:09.128228 | 00:00:09.1282
2003-10-18 11:15:30.014942 | 00:00:09.107448 | 00:00:09.1074
2003-10-18 11:15:33.040811 | 00:00:06.081579 | 00:00:06.0816
2003-10-18 11:15:36.072885 | 00:00:03.049505 | 00:00:03.0495
2003-10-18 11:15:39.10362 | 00:00:00.01877 | 00:00:00.0188
(5 rows)

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

#2

Does this affect all versions of 7.3?

John Griffiths

Tom Lane wrote:

Quote:

----------
From: Tom Lane[SMTP:TGL (AT) SSS (DOT) PGH.PA.US]
Sent: Saturday, October 18, 2003 3:14:37 PM
To: elein
Cc: pgsql-bugs (AT) postgresql (DOT) org
Subject: Re: [BUGS] 7.3 interval casting broken (7.4 OK)
Auto forwarded by a Rule

elein <elein (AT) varlena (DOT) com> writes:

The interval casting in 7.3 is ignoring precision.

Yeah, see this fix:

2003-01-08 19:58 tgl

* src/include/catalog/pg_proc.h: Add missing pg_proc entry for
interval_scale(). The lack of this entry causes interval rounding
not to work as expected in 7.3, for example SELECT
'18:17:15.6'::interval(0) does not round the value. I did not
force initdb, but one is needed to install the added row.

We couldn't back-patch that into 7.3 without an initdb, unfortunately.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

[BUGS] 7.3 interval casting broken (7.4 OK)

mailing.database.pgsql-bugs mailing.database.pgsql-bugs

[BUGS] 7.3 interval casting broken (7.4 OK) - 10-18-2003 , 01:27 PM

Re: [BUGS] 7.3 interval casting broken (7.4 OK) - 10-20-2003 , 06:50 AM