Re: 7.3 interval casting broken (7.4 OK)

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)

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

Does this affect all versions of 7.3?

John Griffiths

Tom Lane wrote:

>>----------
>>From: Tom Lane[SMTP:TGL(at)SSS(dot)PGH(dot)PA(dot)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
>
>