7.3 interval casting broken (7.4 OK) - Mailing list pgsql-bugs
From | elein |
---|---|
Subject | 7.3 interval casting broken (7.4 OK) |
Date | |
Msg-id | 20031018112057.G9299@cookie.varlena.com Whole thread Raw |
Responses |
Re: 7.3 interval casting broken (7.4 OK)
|
List | pgsql-bugs |
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)
pgsql-bugs by date: