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@varlena.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:
Show quoted text
----------
From: Tom Lane[SMTP:TGL@SSS.PGH.PA.US]
Sent: Saturday, October 18, 2003 3:14:37 PM
To: elein
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] 7.3 interval casting broken (7.4 OK)
Auto forwarded by a Ruleelein <elein@varlena.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