Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitbac2ad3

Browse files
committed
Change AdjustIntervalForTypmod to not discard higher-order field values on the
grounds that they don't fit into the specified interval qualifier (typmod).This behavior, while of long standing, is clearly wrong per spec --- forexample the value INTERVAL '999' SECOND means 999 seconds and should not bereduced to less than 60 seconds.In some cases there could be grounds to raise an error if higher-order fieldvalues are not given as zero; for example '1 year 1 month'::INTERVAL MONTHshould arguably be taken as an error rather than equivalent to 13 months.However our internal representation doesn't allow us to do that in a fashionthat would consistently reject all and only the cases that a strict readingof the spec would suggest. Also, seeing that for example INTERVAL '13' MONTHwill print out as '1 year 1 mon', we have to be careful not to create asituation where valid data will fail to dump and reload. The present patchtherefore takes the attitude of not throwing an error in any such case.We might want to revisit that in future but it would take more redesignthan seems prudent in late beta.Per a complaint from Sebastien Flaesch and subsequent discussion. Whileat other times we might have just postponed such an issue to the nextdevelopment cycle, 8.4 already has changed the parsing of interval literalsquite a bit in an effort to accept all spec-compliant cases correctly.This seems like a change that should be part of that rather than comingalong later.
1 parentb3b89fd commitbac2ad3

File tree

3 files changed

+114
-83
lines changed

3 files changed

+114
-83
lines changed

‎src/backend/utils/adt/timestamp.c

Lines changed: 27 additions & 53 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/utils/adt/timestamp.c,v 1.199 2009/05/26 02:17:50 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/utils/adt/timestamp.c,v 1.200 2009/06/01 23:55:15 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -955,13 +955,32 @@ AdjustIntervalForTypmod(Interval *interval, int32 typmod)
955955

956956
/*
957957
* Unspecified range and precision? Then not necessary to adjust. Setting
958-
* typmod to -1 is the convention for all types.
958+
* typmod to -1 is the convention for alldatatypes.
959959
*/
960960
if (typmod >=0)
961961
{
962962
intrange=INTERVAL_RANGE(typmod);
963963
intprecision=INTERVAL_PRECISION(typmod);
964964

965+
/*
966+
* Our interpretation of intervals with a limited set of fields
967+
* is that fields to the right of the last one specified are zeroed
968+
* out, but those to the left of it remain valid. Thus for example
969+
* there is no operational difference between INTERVAL YEAR TO MONTH
970+
* and INTERVAL MONTH. In some cases we could meaningfully enforce
971+
* that higher-order fields are zero; for example INTERVAL DAY could
972+
* reject nonzero "month" field. However that seems a bit pointless
973+
* when we can't do it consistently. (We cannot enforce a range limit
974+
* on the highest expected field, since we do not have any equivalent
975+
* of SQL's <interval leading field precision>.)
976+
*
977+
* Note: before PG 8.4 we interpreted a limited set of fields as
978+
* actually causing a "modulo" operation on a given value, potentially
979+
* losing high-order as well as low-order information. But there is
980+
* no support for such behavior in the standard, and it seems fairly
981+
* undesirable on data consistency grounds anyway. Now we only
982+
* perform truncation or rounding of low-order fields.
983+
*/
965984
if (range==INTERVAL_FULL_RANGE)
966985
{
967986
/* Do nothing... */
@@ -974,27 +993,21 @@ AdjustIntervalForTypmod(Interval *interval, int32 typmod)
974993
}
975994
elseif (range==INTERVAL_MASK(MONTH))
976995
{
977-
interval->month %=MONTHS_PER_YEAR;
978996
interval->day=0;
979997
interval->time=0;
980998
}
981999
/* YEAR TO MONTH */
9821000
elseif (range== (INTERVAL_MASK(YEAR) |INTERVAL_MASK(MONTH)))
9831001
{
984-
/* month is already year to month */
9851002
interval->day=0;
9861003
interval->time=0;
9871004
}
9881005
elseif (range==INTERVAL_MASK(DAY))
9891006
{
990-
interval->month=0;
9911007
interval->time=0;
9921008
}
9931009
elseif (range==INTERVAL_MASK(HOUR))
9941010
{
995-
interval->month=0;
996-
interval->day=0;
997-
9981011
#ifdefHAVE_INT64_TIMESTAMP
9991012
interval->time= (interval->time /USECS_PER_HOUR)*
10001013
USECS_PER_HOUR;
@@ -1004,42 +1017,21 @@ AdjustIntervalForTypmod(Interval *interval, int32 typmod)
10041017
}
10051018
elseif (range==INTERVAL_MASK(MINUTE))
10061019
{
1007-
TimeOffsethour;
1008-
1009-
interval->month=0;
1010-
interval->day=0;
1011-
10121020
#ifdefHAVE_INT64_TIMESTAMP
1013-
hour=interval->time /USECS_PER_HOUR;
1014-
interval->time-=hour*USECS_PER_HOUR;
10151021
interval->time= (interval->time /USECS_PER_MINUTE)*
10161022
USECS_PER_MINUTE;
10171023
#else
1018-
TMODULO(interval->time,hour, (double)SECS_PER_HOUR);
10191024
interval->time= ((int) (interval->time /SECS_PER_MINUTE))* (double)SECS_PER_MINUTE;
10201025
#endif
10211026
}
10221027
elseif (range==INTERVAL_MASK(SECOND))
10231028
{
1024-
TimeOffsetminute;
1025-
1026-
interval->month=0;
1027-
interval->day=0;
1028-
1029-
#ifdefHAVE_INT64_TIMESTAMP
1030-
minute=interval->time /USECS_PER_MINUTE;
1031-
interval->time-=minute*USECS_PER_MINUTE;
1032-
#else
1033-
TMODULO(interval->time,minute, (double)SECS_PER_MINUTE);
1034-
/* return subseconds too */
1035-
#endif
1029+
/* fractional-second rounding will be dealt with below */
10361030
}
10371031
/* DAY TO HOUR */
10381032
elseif (range== (INTERVAL_MASK(DAY) |
10391033
INTERVAL_MASK(HOUR)))
10401034
{
1041-
interval->month=0;
1042-
10431035
#ifdefHAVE_INT64_TIMESTAMP
10441036
interval->time= (interval->time /USECS_PER_HOUR)*
10451037
USECS_PER_HOUR;
@@ -1052,8 +1044,6 @@ AdjustIntervalForTypmod(Interval *interval, int32 typmod)
10521044
INTERVAL_MASK(HOUR) |
10531045
INTERVAL_MASK(MINUTE)))
10541046
{
1055-
interval->month=0;
1056-
10571047
#ifdefHAVE_INT64_TIMESTAMP
10581048
interval->time= (interval->time /USECS_PER_MINUTE)*
10591049
USECS_PER_MINUTE;
@@ -1066,15 +1056,13 @@ AdjustIntervalForTypmod(Interval *interval, int32 typmod)
10661056
INTERVAL_MASK(HOUR) |
10671057
INTERVAL_MASK(MINUTE) |
10681058
INTERVAL_MASK(SECOND)))
1069-
interval->month=0;
1070-
1059+
{
1060+
/* fractional-second rounding will be dealt with below */
1061+
}
10711062
/* HOUR TO MINUTE */
10721063
elseif (range== (INTERVAL_MASK(HOUR) |
10731064
INTERVAL_MASK(MINUTE)))
10741065
{
1075-
interval->month=0;
1076-
interval->day=0;
1077-
10781066
#ifdefHAVE_INT64_TIMESTAMP
10791067
interval->time= (interval->time /USECS_PER_MINUTE)*
10801068
USECS_PER_MINUTE;
@@ -1087,25 +1075,13 @@ AdjustIntervalForTypmod(Interval *interval, int32 typmod)
10871075
INTERVAL_MASK(MINUTE) |
10881076
INTERVAL_MASK(SECOND)))
10891077
{
1090-
interval->month=0;
1091-
interval->day=0;
1092-
/* return subseconds too */
1078+
/* fractional-second rounding will be dealt with below */
10931079
}
10941080
/* MINUTE TO SECOND */
10951081
elseif (range== (INTERVAL_MASK(MINUTE) |
10961082
INTERVAL_MASK(SECOND)))
10971083
{
1098-
TimeOffsethour;
1099-
1100-
interval->month=0;
1101-
interval->day=0;
1102-
1103-
#ifdefHAVE_INT64_TIMESTAMP
1104-
hour=interval->time /USECS_PER_HOUR;
1105-
interval->time-=hour*USECS_PER_HOUR;
1106-
#else
1107-
TMODULO(interval->time,hour, (double)SECS_PER_HOUR);
1108-
#endif
1084+
/* fractional-second rounding will be dealt with below */
11091085
}
11101086
else
11111087
elog(ERROR,"unrecognized interval typmod: %d",typmod);
@@ -1148,8 +1124,6 @@ AdjustIntervalForTypmod(Interval *interval, int32 typmod)
11481124
#endif
11491125
}
11501126
}
1151-
1152-
return;
11531127
}
11541128

11551129

‎src/test/regress/expected/interval.out

Lines changed: 78 additions & 30 deletions
Original file line numberDiff line numberDiff line change
@@ -358,12 +358,46 @@ SELECT '1:20:05 5 microseconds'::interval; -- error
358358
ERROR: invalid input syntax for type interval: "1:20:05 5 microseconds"
359359
LINE 1: SELECT '1:20:05 5 microseconds'::interval;
360360
^
361+
SELECT '1 day 1 day'::interval; -- error
362+
ERROR: invalid input syntax for type interval: "1 day 1 day"
363+
LINE 1: SELECT '1 day 1 day'::interval;
364+
^
361365
SELECT interval '1-2'; -- SQL year-month literal
362366
interval
363367
---------------
364368
1 year 2 mons
365369
(1 row)
366370

371+
SELECT interval '999' second; -- oversize leading field is ok
372+
interval
373+
----------
374+
00:16:39
375+
(1 row)
376+
377+
SELECT interval '999' minute;
378+
interval
379+
----------
380+
16:39:00
381+
(1 row)
382+
383+
SELECT interval '999' hour;
384+
interval
385+
-----------
386+
999:00:00
387+
(1 row)
388+
389+
SELECT interval '999' day;
390+
interval
391+
----------
392+
999 days
393+
(1 row)
394+
395+
SELECT interval '999' month;
396+
interval
397+
-----------------
398+
83 years 3 mons
399+
(1 row)
400+
367401
-- test SQL-spec syntaxes for restricted field sets
368402
SELECT interval '1' year;
369403
interval
@@ -472,49 +506,63 @@ ERROR: invalid input syntax for type interval: "1 2"
472506
LINE 1: SELECT interval '1 2' hour to minute;
473507
^
474508
SELECT interval '1 2:03' hour to minute;
475-
interval
476-
----------
477-
02:03:00
509+
interval
510+
----------------
511+
1 day02:03:00
478512
(1 row)
479513

480514
SELECT interval '1 2:03:04' hour to minute;
481-
interval
482-
----------
483-
02:03:00
515+
interval
516+
----------------
517+
1 day02:03:00
484518
(1 row)
485519

486520
SELECT interval '1 2' hour to second;
487521
ERROR: invalid input syntax for type interval: "1 2"
488522
LINE 1: SELECT interval '1 2' hour to second;
489523
^
490524
SELECT interval '1 2:03' hour to second;
491-
interval
492-
----------
493-
02:03:00
525+
interval
526+
----------------
527+
1 day02:03:00
494528
(1 row)
495529

496530
SELECT interval '1 2:03:04' hour to second;
497-
interval
498-
----------
499-
02:03:04
531+
interval
532+
----------------
533+
1 day02:03:04
500534
(1 row)
501535

502536
SELECT interval '1 2' minute to second;
503537
ERROR: invalid input syntax for type interval: "1 2"
504538
LINE 1: SELECT interval '1 2' minute to second;
505539
^
506540
SELECT interval '1 2:03' minute to second;
507-
interval
508-
----------
509-
00:02:03
541+
interval
542+
----------------
543+
1 day00:02:03
510544
(1 row)
511545

512546
SELECT interval '1 2:03:04' minute to second;
513-
interval
514-
----------
515-
00:03:04
547+
interval
548+
----------------
549+
1 day 02:03:04
516550
(1 row)
517551

552+
SELECT interval '123 11' day to hour; -- ok
553+
interval
554+
-------------------
555+
123 days 11:00:00
556+
(1 row)
557+
558+
SELECT interval '123 11' day; -- not ok
559+
ERROR: invalid input syntax for type interval: "123 11"
560+
LINE 1: SELECT interval '123 11' day;
561+
^
562+
SELECT interval '123 11'; -- not ok, too ambiguous
563+
ERROR: invalid input syntax for type interval: "123 11"
564+
LINE 1: SELECT interval '123 11';
565+
^
518566
-- test syntaxes for restricted precision
519567
SELECT interval(0) '1 day 01:23:45.6789';
520568
interval
@@ -585,31 +633,31 @@ ERROR: invalid input syntax for type interval: "1 2.345"
585633
LINE 1: SELECT interval '1 2.345' hour to second(2);
586634
^
587635
SELECT interval '1 2:03.45678' hour to second(2);
588-
interval
589-
-------------
590-
00:02:03.46
636+
interval
637+
-------------------
638+
1 day00:02:03.46
591639
(1 row)
592640

593641
SELECT interval '1 2:03:04.5678' hour to second(2);
594-
interval
595-
-------------
596-
02:03:04.57
642+
interval
643+
-------------------
644+
1 day02:03:04.57
597645
(1 row)
598646

599647
SELECT interval '1 2.3456' minute to second(2);
600648
ERROR: invalid input syntax for type interval: "1 2.3456"
601649
LINE 1: SELECT interval '1 2.3456' minute to second(2);
602650
^
603651
SELECT interval '1 2:03.5678' minute to second(2);
604-
interval
605-
-------------
606-
00:02:03.57
652+
interval
653+
-------------------
654+
1 day00:02:03.57
607655
(1 row)
608656

609657
SELECT interval '1 2:03:04.5678' minute to second(2);
610-
interval
611-
-------------
612-
00:03:04.57
658+
interval
659+
-------------------
660+
1 day 02:03:04.57
613661
(1 row)
614662

615663
-- test inputting and outputting SQL standard interval literals

‎src/test/regress/sql/interval.sql

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -130,7 +130,13 @@ SELECT '1 second 2 seconds'::interval; -- error
130130
SELECT'10 milliseconds 20 milliseconds'::interval;-- error
131131
SELECT'5.5 seconds 3 milliseconds'::interval;-- error
132132
SELECT'1:20:05 5 microseconds'::interval;-- error
133+
SELECT'1 day 1 day'::interval;-- error
133134
SELECT interval'1-2';-- SQL year-month literal
135+
SELECT interval'999' second;-- oversize leading field is ok
136+
SELECT interval'999' minute;
137+
SELECT interval'999' hour;
138+
SELECT interval'999' day;
139+
SELECT interval'999' month;
134140

135141
-- test SQL-spec syntaxes for restricted field sets
136142
SELECT interval'1' year;
@@ -159,6 +165,9 @@ SELECT interval '1 2:03:04' hour to second;
159165
SELECT interval'1 2' minute to second;
160166
SELECT interval'1 2:03' minute to second;
161167
SELECT interval'1 2:03:04' minute to second;
168+
SELECT interval'123 11' day to hour;-- ok
169+
SELECT interval'123 11' day;-- not ok
170+
SELECT interval'123 11';-- not ok, too ambiguous
162171

163172
-- test syntaxes for restricted precision
164173
SELECTinterval(0)'1 day 01:23:45.6789';

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp