7.3 interval casting broken (7.4 OK)

Поиск
Список
Период
Сортировка
От elein
Тема 7.3 interval casting broken (7.4 OK)
Дата
Msg-id 20031018112057.G9299@cookie.varlena.com
обсуждение исходный текст
Ответы Re: 7.3 interval casting broken (7.4 OK)  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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 по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: data forma error in pgsql 7.1
Следующее
От: Tom Lane
Дата:
Сообщение: Re: 7.3 interval casting broken (7.4 OK)