Thread: 7.3 interval casting broken (7.4 OK)

7.3 interval casting broken (7.4 OK)

From

elein

Date:

18 October 2003, 18:23:47

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)

Re: 7.3 interval casting broken (7.4 OK)

From

Tom Lane

Date:

18 October 2003, 19:15:09

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

Re: 7.3 interval casting broken (7.4 OK)

From

John Griffiths

Date:

20 October 2003, 11:46:57

Does this affect all versions of 7.3?

John Griffiths

Tom Lane wrote:

>>----------
>>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 Rule
>>
>>
>>
>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
>
>---------------------------(end of broadcast)---------------------------
>TIP 7: don't forget to increase your free space map settings
>
>