diff options
Diffstat (limited to 'src/test')
| -rw-r--r-- | src/test/regress/expected/interval.out | 611 | ||||
| -rw-r--r-- | src/test/regress/sql/interval.sql | 184 |
2 files changed, 795 insertions, 0 deletions
diff --git a/src/test/regress/expected/interval.out b/src/test/regress/expected/interval.out index 9a7e2852f29..86c8d4bc990 100644 --- a/src/test/regress/expected/interval.out +++ b/src/test/regress/expected/interval.out @@ -928,6 +928,617 @@ select interval '0:0:0.7', interval '@ 0.70 secs', interval '0.7 seconds'; @ 0.7 secs | @ 0.7 secs | @ 0.7 secs (1 row) +-- test time fields using entire 64 bit microseconds range +select interval '2562047788.01521550194 hours'; + interval +----------------------------------- + @ 2562047788 hours 54.775807 secs +(1 row) + +select interval '-2562047788.01521550222 hours'; + interval +--------------------------------------- + @ 2562047788 hours 54.775808 secs ago +(1 row) + +select interval '153722867280.912930117 minutes'; + interval +----------------------------------- + @ 2562047788 hours 54.775807 secs +(1 row) + +select interval '-153722867280.912930133 minutes'; + interval +--------------------------------------- + @ 2562047788 hours 54.775808 secs ago +(1 row) + +select interval '9223372036854.775807 seconds'; + interval +----------------------------------- + @ 2562047788 hours 54.775807 secs +(1 row) + +select interval '-9223372036854.775808 seconds'; + interval +--------------------------------------- + @ 2562047788 hours 54.775808 secs ago +(1 row) + +select interval '9223372036854775.807 milliseconds'; + interval +----------------------------------- + @ 2562047788 hours 54.775807 secs +(1 row) + +select interval '-9223372036854775.808 milliseconds'; + interval +--------------------------------------- + @ 2562047788 hours 54.775808 secs ago +(1 row) + +select interval '9223372036854775807 microseconds'; + interval +----------------------------------- + @ 2562047788 hours 54.775807 secs +(1 row) + +select interval '-9223372036854775808 microseconds'; + interval +--------------------------------------- + @ 2562047788 hours 54.775808 secs ago +(1 row) + +select interval 'PT2562047788H54.775807S'; + interval +----------------------------------- + @ 2562047788 hours 54.775807 secs +(1 row) + +select interval 'PT-2562047788H-54.775808S'; + interval +--------------------------------------- + @ 2562047788 hours 54.775808 secs ago +(1 row) + +select interval 'PT2562047788:00:54.775807'; + interval +----------------------------------- + @ 2562047788 hours 54.775807 secs +(1 row) + +select interval 'PT2562047788.0152155019444'; + interval +----------------------------------- + @ 2562047788 hours 54.775807 secs +(1 row) + +select interval 'PT-2562047788.0152155022222'; + interval +--------------------------------------- + @ 2562047788 hours 54.775808 secs ago +(1 row) + +-- overflow each date/time field +select interval '2147483648 years'; +ERROR: interval field value out of range: "2147483648 years" +LINE 1: select interval '2147483648 years'; + ^ +select interval '-2147483649 years'; +ERROR: interval field value out of range: "-2147483649 years" +LINE 1: select interval '-2147483649 years'; + ^ +select interval '2147483648 months'; +ERROR: interval field value out of range: "2147483648 months" +LINE 1: select interval '2147483648 months'; + ^ +select interval '-2147483649 months'; +ERROR: interval field value out of range: "-2147483649 months" +LINE 1: select interval '-2147483649 months'; + ^ +select interval '2147483648 days'; +ERROR: interval field value out of range: "2147483648 days" +LINE 1: select interval '2147483648 days'; + ^ +select interval '-2147483649 days'; +ERROR: interval field value out of range: "-2147483649 days" +LINE 1: select interval '-2147483649 days'; + ^ +select interval '2562047789 hours'; +ERROR: interval field value out of range: "2562047789 hours" +LINE 1: select interval '2562047789 hours'; + ^ +select interval '-2562047789 hours'; +ERROR: interval field value out of range: "-2562047789 hours" +LINE 1: select interval '-2562047789 hours'; + ^ +select interval '153722867281 minutes'; +ERROR: interval field value out of range: "153722867281 minutes" +LINE 1: select interval '153722867281 minutes'; + ^ +select interval '-153722867281 minutes'; +ERROR: interval field value out of range: "-153722867281 minutes" +LINE 1: select interval '-153722867281 minutes'; + ^ +select interval '9223372036855 seconds'; +ERROR: interval field value out of range: "9223372036855 seconds" +LINE 1: select interval '9223372036855 seconds'; + ^ +select interval '-9223372036855 seconds'; +ERROR: interval field value out of range: "-9223372036855 seconds" +LINE 1: select interval '-9223372036855 seconds'; + ^ +select interval '9223372036854777 millisecond'; +ERROR: interval field value out of range: "9223372036854777 millisecond" +LINE 1: select interval '9223372036854777 millisecond'; + ^ +select interval '-9223372036854777 millisecond'; +ERROR: interval field value out of range: "-9223372036854777 millisecond" +LINE 1: select interval '-9223372036854777 millisecond'; + ^ +select interval '9223372036854775808 microsecond'; +ERROR: interval field value out of range: "9223372036854775808 microsecond" +LINE 1: select interval '9223372036854775808 microsecond'; + ^ +select interval '-9223372036854775809 microsecond'; +ERROR: interval field value out of range: "-9223372036854775809 microsecond" +LINE 1: select interval '-9223372036854775809 microsecond'; + ^ +select interval 'P2147483648'; +ERROR: interval field value out of range: "P2147483648" +LINE 1: select interval 'P2147483648'; + ^ +select interval 'P-2147483649'; +ERROR: interval field value out of range: "P-2147483649" +LINE 1: select interval 'P-2147483649'; + ^ +select interval 'P1-2147483647-2147483647'; +ERROR: interval out of range +LINE 1: select interval 'P1-2147483647-2147483647'; + ^ +select interval 'PT2562047789'; +ERROR: interval field value out of range: "PT2562047789" +LINE 1: select interval 'PT2562047789'; + ^ +select interval 'PT-2562047789'; +ERROR: interval field value out of range: "PT-2562047789" +LINE 1: select interval 'PT-2562047789'; + ^ +-- overflow with date/time unit aliases +select interval '2147483647 weeks'; +ERROR: interval field value out of range: "2147483647 weeks" +LINE 1: select interval '2147483647 weeks'; + ^ +select interval '-2147483648 weeks'; +ERROR: interval field value out of range: "-2147483648 weeks" +LINE 1: select interval '-2147483648 weeks'; + ^ +select interval '2147483647 decades'; +ERROR: interval field value out of range: "2147483647 decades" +LINE 1: select interval '2147483647 decades'; + ^ +select interval '-2147483648 decades'; +ERROR: interval field value out of range: "-2147483648 decades" +LINE 1: select interval '-2147483648 decades'; + ^ +select interval '2147483647 centuries'; +ERROR: interval field value out of range: "2147483647 centuries" +LINE 1: select interval '2147483647 centuries'; + ^ +select interval '-2147483648 centuries'; +ERROR: interval field value out of range: "-2147483648 centuries" +LINE 1: select interval '-2147483648 centuries'; + ^ +select interval '2147483647 millennium'; +ERROR: interval field value out of range: "2147483647 millennium" +LINE 1: select interval '2147483647 millennium'; + ^ +select interval '-2147483648 millennium'; +ERROR: interval field value out of range: "-2147483648 millennium" +LINE 1: select interval '-2147483648 millennium'; + ^ +select interval '1 week 2147483647 days'; +ERROR: interval field value out of range: "1 week 2147483647 days" +LINE 1: select interval '1 week 2147483647 days'; + ^ +select interval '-1 week -2147483648 days'; +ERROR: interval field value out of range: "-1 week -2147483648 days" +LINE 1: select interval '-1 week -2147483648 days'; + ^ +select interval '2147483647 days 1 week'; +ERROR: interval field value out of range: "2147483647 days 1 week" +LINE 1: select interval '2147483647 days 1 week'; + ^ +select interval '-2147483648 days -1 week'; +ERROR: interval field value out of range: "-2147483648 days -1 week" +LINE 1: select interval '-2147483648 days -1 week'; + ^ +select interval 'P1W2147483647D'; +ERROR: interval field value out of range: "P1W2147483647D" +LINE 1: select interval 'P1W2147483647D'; + ^ +select interval 'P-1W-2147483648D'; +ERROR: interval field value out of range: "P-1W-2147483648D" +LINE 1: select interval 'P-1W-2147483648D'; + ^ +select interval 'P2147483647D1W'; +ERROR: interval field value out of range: "P2147483647D1W" +LINE 1: select interval 'P2147483647D1W'; + ^ +select interval 'P-2147483648D-1W'; +ERROR: interval field value out of range: "P-2147483648D-1W" +LINE 1: select interval 'P-2147483648D-1W'; + ^ +select interval '1 decade 2147483647 years'; +ERROR: interval field value out of range: "1 decade 2147483647 years" +LINE 1: select interval '1 decade 2147483647 years'; + ^ +select interval '1 century 2147483647 years'; +ERROR: interval field value out of range: "1 century 2147483647 years" +LINE 1: select interval '1 century 2147483647 years'; + ^ +select interval '1 millennium 2147483647 years'; +ERROR: interval field value out of range: "1 millennium 2147483647 years" +LINE 1: select interval '1 millennium 2147483647 years'; + ^ +select interval '-1 decade -2147483648 years'; +ERROR: interval field value out of range: "-1 decade -2147483648 years" +LINE 1: select interval '-1 decade -2147483648 years'; + ^ +select interval '-1 century -2147483648 years'; +ERROR: interval field value out of range: "-1 century -2147483648 years" +LINE 1: select interval '-1 century -2147483648 years'; + ^ +select interval '-1 millennium -2147483648 years'; +ERROR: interval field value out of range: "-1 millennium -2147483648 years" +LINE 1: select interval '-1 millennium -2147483648 years'; + ^ +select interval '2147483647 years 1 decade'; +ERROR: interval field value out of range: "2147483647 years 1 decade" +LINE 1: select interval '2147483647 years 1 decade'; + ^ +select interval '2147483647 years 1 century'; +ERROR: interval field value out of range: "2147483647 years 1 century" +LINE 1: select interval '2147483647 years 1 century'; + ^ +select interval '2147483647 years 1 millennium'; +ERROR: interval field value out of range: "2147483647 years 1 millennium" +LINE 1: select interval '2147483647 years 1 millennium'; + ^ +select interval '-2147483648 years -1 decade'; +ERROR: interval field value out of range: "-2147483648 years -1 decade" +LINE 1: select interval '-2147483648 years -1 decade'; + ^ +select interval '-2147483648 years -1 century'; +ERROR: interval field value out of range: "-2147483648 years -1 century" +LINE 1: select interval '-2147483648 years -1 century'; + ^ +select interval '-2147483648 years -1 millennium'; +ERROR: interval field value out of range: "-2147483648 years -1 millennium" +LINE 1: select interval '-2147483648 years -1 millennium'; + ^ +-- overflowing with fractional fields - postgres format +select interval '0.1 millennium 2147483647 months'; +ERROR: interval field value out of range: "0.1 millennium 2147483647 months" +LINE 1: select interval '0.1 millennium 2147483647 months'; + ^ +select interval '0.1 centuries 2147483647 months'; +ERROR: interval field value out of range: "0.1 centuries 2147483647 months" +LINE 1: select interval '0.1 centuries 2147483647 months'; + ^ +select interval '0.1 decades 2147483647 months'; +ERROR: interval field value out of range: "0.1 decades 2147483647 months" +LINE 1: select interval '0.1 decades 2147483647 months'; + ^ +select interval '0.1 yrs 2147483647 months'; +ERROR: interval field value out of range: "0.1 yrs 2147483647 months" +LINE 1: select interval '0.1 yrs 2147483647 months'; + ^ +select interval '-0.1 millennium -2147483648 months'; +ERROR: interval field value out of range: "-0.1 millennium -2147483648 months" +LINE 1: select interval '-0.1 millennium -2147483648 months'; + ^ +select interval '-0.1 centuries -2147483648 months'; +ERROR: interval field value out of range: "-0.1 centuries -2147483648 months" +LINE 1: select interval '-0.1 centuries -2147483648 months'; + ^ +select interval '-0.1 decades -2147483648 months'; +ERROR: interval field value out of range: "-0.1 decades -2147483648 months" +LINE 1: select interval '-0.1 decades -2147483648 months'; + ^ +select interval '-0.1 yrs -2147483648 months'; +ERROR: interval field value out of range: "-0.1 yrs -2147483648 months" +LINE 1: select interval '-0.1 yrs -2147483648 months'; + ^ +select interval '2147483647 months 0.1 millennium'; +ERROR: interval field value out of range: "2147483647 months 0.1 millennium" +LINE 1: select interval '2147483647 months 0.1 millennium'; + ^ +select interval '2147483647 months 0.1 centuries'; +ERROR: interval field value out of range: "2147483647 months 0.1 centuries" +LINE 1: select interval '2147483647 months 0.1 centuries'; + ^ +select interval '2147483647 months 0.1 decades'; +ERROR: interval field value out of range: "2147483647 months 0.1 decades" +LINE 1: select interval '2147483647 months 0.1 decades'; + ^ +select interval '2147483647 months 0.1 yrs'; +ERROR: interval field value out of range: "2147483647 months 0.1 yrs" +LINE 1: select interval '2147483647 months 0.1 yrs'; + ^ +select interval '-2147483648 months -0.1 millennium'; +ERROR: interval field value out of range: "-2147483648 months -0.1 millennium" +LINE 1: select interval '-2147483648 months -0.1 millennium'; + ^ +select interval '-2147483648 months -0.1 centuries'; +ERROR: interval field value out of range: "-2147483648 months -0.1 centuries" +LINE 1: select interval '-2147483648 months -0.1 centuries'; + ^ +select interval '-2147483648 months -0.1 decades'; +ERROR: interval field value out of range: "-2147483648 months -0.1 decades" +LINE 1: select interval '-2147483648 months -0.1 decades'; + ^ +select interval '-2147483648 months -0.1 yrs'; +ERROR: interval field value out of range: "-2147483648 months -0.1 yrs" +LINE 1: select interval '-2147483648 months -0.1 yrs'; + ^ +select interval '0.1 months 2147483647 days'; +ERROR: interval field value out of range: "0.1 months 2147483647 days" +LINE 1: select interval '0.1 months 2147483647 days'; + ^ +select interval '-0.1 months -2147483648 days'; +ERROR: interval field value out of range: "-0.1 months -2147483648 days" +LINE 1: select interval '-0.1 months -2147483648 days'; + ^ +select interval '2147483647 days 0.1 months'; +ERROR: interval field value out of range: "2147483647 days 0.1 months" +LINE 1: select interval '2147483647 days 0.1 months'; + ^ +select interval '-2147483648 days -0.1 months'; +ERROR: interval field value out of range: "-2147483648 days -0.1 months" +LINE 1: select interval '-2147483648 days -0.1 months'; + ^ +select interval '0.5 weeks 2147483647 days'; +ERROR: interval field value out of range: "0.5 weeks 2147483647 days" +LINE 1: select interval '0.5 weeks 2147483647 days'; + ^ +select interval '-0.5 weeks -2147483648 days'; +ERROR: interval field value out of range: "-0.5 weeks -2147483648 days" +LINE 1: select interval '-0.5 weeks -2147483648 days'; + ^ +select interval '2147483647 days 0.5 weeks'; +ERROR: interval field value out of range: "2147483647 days 0.5 weeks" +LINE 1: select interval '2147483647 days 0.5 weeks'; + ^ +select interval '-2147483648 days -0.5 weeks'; +ERROR: interval field value out of range: "-2147483648 days -0.5 weeks" +LINE 1: select interval '-2147483648 days -0.5 weeks'; + ^ +select interval '0.01 months 9223372036854775807 microseconds'; +ERROR: interval field value out of range: "0.01 months 9223372036854775807 microseconds" +LINE 1: select interval '0.01 months 9223372036854775807 microsecond... + ^ +select interval '-0.01 months -9223372036854775808 microseconds'; +ERROR: interval field value out of range: "-0.01 months -9223372036854775808 microseconds" +LINE 1: select interval '-0.01 months -9223372036854775808 microseco... + ^ +select interval '9223372036854775807 microseconds 0.01 months'; +ERROR: interval field value out of range: "9223372036854775807 microseconds 0.01 months" +LINE 1: select interval '9223372036854775807 microseconds 0.01 month... + ^ +select interval '-9223372036854775808 microseconds -0.01 months'; +ERROR: interval field value out of range: "-9223372036854775808 microseconds -0.01 months" +LINE 1: select interval '-9223372036854775808 microseconds -0.01 mon... + ^ +select interval '0.1 weeks 9223372036854775807 microseconds'; +ERROR: interval field value out of range: "0.1 weeks 9223372036854775807 microseconds" +LINE 1: select interval '0.1 weeks 9223372036854775807 microseconds'... + ^ +select interval '-0.1 weeks -9223372036854775808 microseconds'; +ERROR: interval field value out of range: "-0.1 weeks -9223372036854775808 microseconds" +LINE 1: select interval '-0.1 weeks -9223372036854775808 microsecond... + ^ +select interval '9223372036854775807 microseconds 0.1 weeks'; +ERROR: interval field value out of range: "9223372036854775807 microseconds 0.1 weeks" +LINE 1: select interval '9223372036854775807 microseconds 0.1 weeks'... + ^ +select interval '-9223372036854775808 microseconds -0.1 weeks'; +ERROR: interval field value out of range: "-9223372036854775808 microseconds -0.1 weeks" +LINE 1: select interval '-9223372036854775808 microseconds -0.1 week... + ^ +select interval '0.1 days 9223372036854775807 microseconds'; +ERROR: interval field value out of range: "0.1 days 9223372036854775807 microseconds" +LINE 1: select interval '0.1 days 9223372036854775807 microseconds'; + ^ +select interval '-0.1 days -9223372036854775808 microseconds'; +ERROR: interval field value out of range: "-0.1 days -9223372036854775808 microseconds" +LINE 1: select interval '-0.1 days -9223372036854775808 microseconds... + ^ +select interval '9223372036854775807 microseconds 0.1 days'; +ERROR: interval field value out of range: "9223372036854775807 microseconds 0.1 days" +LINE 1: select interval '9223372036854775807 microseconds 0.1 days'; + ^ +select interval '-9223372036854775808 microseconds -0.1 days'; +ERROR: interval field value out of range: "-9223372036854775808 microseconds -0.1 days" +LINE 1: select interval '-9223372036854775808 microseconds -0.1 days... + ^ +-- overflowing with fractional fields - ISO8601 format +select interval 'P0.1Y2147483647M'; +ERROR: interval field value out of range: "P0.1Y2147483647M" +LINE 1: select interval 'P0.1Y2147483647M'; + ^ +select interval 'P-0.1Y-2147483648M'; +ERROR: interval field value out of range: "P-0.1Y-2147483648M" +LINE 1: select interval 'P-0.1Y-2147483648M'; + ^ +select interval 'P2147483647M0.1Y'; +ERROR: interval field value out of range: "P2147483647M0.1Y" +LINE 1: select interval 'P2147483647M0.1Y'; + ^ +select interval 'P-2147483648M-0.1Y'; +ERROR: interval field value out of range: "P-2147483648M-0.1Y" +LINE 1: select interval 'P-2147483648M-0.1Y'; + ^ +select interval 'P0.1M2147483647D'; +ERROR: interval field value out of range: "P0.1M2147483647D" +LINE 1: select interval 'P0.1M2147483647D'; + ^ +select interval 'P-0.1M-2147483648D'; +ERROR: interval field value out of range: "P-0.1M-2147483648D" +LINE 1: select interval 'P-0.1M-2147483648D'; + ^ +select interval 'P2147483647D0.1M'; +ERROR: interval field value out of range: "P2147483647D0.1M" +LINE 1: select interval 'P2147483647D0.1M'; + ^ +select interval 'P-2147483648D-0.1M'; +ERROR: interval field value out of range: "P-2147483648D-0.1M" +LINE 1: select interval 'P-2147483648D-0.1M'; + ^ +select interval 'P0.5W2147483647D'; +ERROR: interval field value out of range: "P0.5W2147483647D" +LINE 1: select interval 'P0.5W2147483647D'; + ^ +select interval 'P-0.5W-2147483648D'; +ERROR: interval field value out of range: "P-0.5W-2147483648D" +LINE 1: select interval 'P-0.5W-2147483648D'; + ^ +select interval 'P2147483647D0.5W'; +ERROR: interval field value out of range: "P2147483647D0.5W" +LINE 1: select interval 'P2147483647D0.5W'; + ^ +select interval 'P-2147483648D-0.5W'; +ERROR: interval field value out of range: "P-2147483648D-0.5W" +LINE 1: select interval 'P-2147483648D-0.5W'; + ^ +select interval 'P0.01MT2562047788H54.775807S'; +ERROR: interval field value out of range: "P0.01MT2562047788H54.775807S" +LINE 1: select interval 'P0.01MT2562047788H54.775807S'; + ^ +select interval 'P-0.01MT-2562047788H-54.775808S'; +ERROR: interval field value out of range: "P-0.01MT-2562047788H-54.775808S" +LINE 1: select interval 'P-0.01MT-2562047788H-54.775808S'; + ^ +select interval 'P0.1DT2562047788H54.775807S'; +ERROR: interval field value out of range: "P0.1DT2562047788H54.775807S" +LINE 1: select interval 'P0.1DT2562047788H54.775807S'; + ^ +select interval 'P-0.1DT-2562047788H-54.775808S'; +ERROR: interval field value out of range: "P-0.1DT-2562047788H-54.775808S" +LINE 1: select interval 'P-0.1DT-2562047788H-54.775808S'; + ^ +select interval 'PT2562047788.1H54.775807S'; +ERROR: interval field value out of range: "PT2562047788.1H54.775807S" +LINE 1: select interval 'PT2562047788.1H54.775807S'; + ^ +select interval 'PT-2562047788.1H-54.775808S'; +ERROR: interval field value out of range: "PT-2562047788.1H-54.775808S" +LINE 1: select interval 'PT-2562047788.1H-54.775808S'; + ^ +select interval 'PT2562047788H0.1M54.775807S'; +ERROR: interval field value out of range: "PT2562047788H0.1M54.775807S" +LINE 1: select interval 'PT2562047788H0.1M54.775807S'; + ^ +select interval 'PT-2562047788H-0.1M-54.775808S'; +ERROR: interval field value out of range: "PT-2562047788H-0.1M-54.775808S" +LINE 1: select interval 'PT-2562047788H-0.1M-54.775808S'; + ^ +-- overflowing with fractional fields - ISO8601 alternative format +select interval 'P0.1-2147483647-00'; +ERROR: interval field value out of range: "P0.1-2147483647-00" +LINE 1: select interval 'P0.1-2147483647-00'; + ^ +select interval 'P00-0.1-2147483647'; +ERROR: interval field value out of range: "P00-0.1-2147483647" +LINE 1: select interval 'P00-0.1-2147483647'; + ^ +select interval 'P00-0.01-00T2562047788:00:54.775807'; +ERROR: interval field value out of range: "P00-0.01-00T2562047788:00:54.775807" +LINE 1: select interval 'P00-0.01-00T2562047788:00:54.775807'; + ^ +select interval 'P00-00-0.1T2562047788:00:54.775807'; +ERROR: interval field value out of range: "P00-00-0.1T2562047788:00:54.775807" +LINE 1: select interval 'P00-00-0.1T2562047788:00:54.775807'; + ^ +select interval 'PT2562047788.1:00:54.775807'; +ERROR: interval field value out of range: "PT2562047788.1:00:54.775807" +LINE 1: select interval 'PT2562047788.1:00:54.775807'; + ^ +select interval 'PT2562047788:01.:54.775807'; +ERROR: interval field value out of range: "PT2562047788:01.:54.775807" +LINE 1: select interval 'PT2562047788:01.:54.775807'; + ^ +-- overflowing with fractional fields - SQL standard format +select interval '0.1 2562047788:0:54.775807'; +ERROR: interval field value out of range: "0.1 2562047788:0:54.775807" +LINE 1: select interval '0.1 2562047788:0:54.775807'; + ^ +select interval '0.1 2562047788:0:54.775808 ago'; +ERROR: interval field value out of range: "0.1 2562047788:0:54.775808 ago" +LINE 1: select interval '0.1 2562047788:0:54.775808 ago'; + ^ +select interval '2562047788.1:0:54.775807'; +ERROR: interval field value out of range: "2562047788.1:0:54.775807" +LINE 1: select interval '2562047788.1:0:54.775807'; + ^ +select interval '2562047788.1:0:54.775808 ago'; +ERROR: interval field value out of range: "2562047788.1:0:54.775808 ago" +LINE 1: select interval '2562047788.1:0:54.775808 ago'; + ^ +select interval '2562047788:0.1:54.775807'; +ERROR: invalid input syntax for type interval: "2562047788:0.1:54.775807" +LINE 1: select interval '2562047788:0.1:54.775807'; + ^ +select interval '2562047788:0.1:54.775808 ago'; +ERROR: invalid input syntax for type interval: "2562047788:0.1:54.775808 ago" +LINE 1: select interval '2562047788:0.1:54.775808 ago'; + ^ +-- overflowing using AGO with INT_MIN +select interval '-2147483648 months ago'; +ERROR: interval field value out of range: "-2147483648 months ago" +LINE 1: select interval '-2147483648 months ago'; + ^ +select interval '-2147483648 days ago'; +ERROR: interval field value out of range: "-2147483648 days ago" +LINE 1: select interval '-2147483648 days ago'; + ^ +select interval '-9223372036854775808 microseconds ago'; +ERROR: interval field value out of range: "-9223372036854775808 microseconds ago" +LINE 1: select interval '-9223372036854775808 microseconds ago'; + ^ +select interval '-2147483648 months -2147483648 days -9223372036854775808 microseconds ago'; +ERROR: interval field value out of range: "-2147483648 months -2147483648 days -9223372036854775808 microseconds ago" +LINE 1: select interval '-2147483648 months -2147483648 days -922337... + ^ +-- test that INT_MIN number is formatted properly +SET IntervalStyle to postgres; +select interval '-2147483648 months -2147483648 days -9223372036854775808 us'; + interval +-------------------------------------------------------------------- + -178956970 years -8 mons -2147483648 days -2562047788:00:54.775808 +(1 row) + +SET IntervalStyle to sql_standard; +select interval '-2147483648 months -2147483648 days -9223372036854775808 us'; + interval +--------------------------------------------------- + -178956970-8 -2147483648 -2562047788:00:54.775808 +(1 row) + +SET IntervalStyle to iso_8601; +select interval '-2147483648 months -2147483648 days -9223372036854775808 us'; + interval +----------------------------------------------------- + P-178956970Y-8M-2147483648DT-2562047788H-54.775808S +(1 row) + +SET IntervalStyle to postgres_verbose; +select interval '-2147483648 months -2147483648 days -9223372036854775808 us'; + interval +------------------------------------------------------------------------------ + @ 178956970 years 8 mons 2147483648 days 2562047788 hours 54.775808 secs ago +(1 row) + -- check that '30 days' equals '1 month' according to the hash function select '30 days'::interval = '1 month'::interval as t; t diff --git a/src/test/regress/sql/interval.sql b/src/test/regress/sql/interval.sql index 811b581e095..f05055e03a9 100644 --- a/src/test/regress/sql/interval.sql +++ b/src/test/regress/sql/interval.sql @@ -318,6 +318,190 @@ select interval '-10 mons -3 days +03:55:06.70'; select interval '1 year 2 mons 3 days 04:05:06.699999'; select interval '0:0:0.7', interval '@ 0.70 secs', interval '0.7 seconds'; +-- test time fields using entire 64 bit microseconds range +select interval '2562047788.01521550194 hours'; +select interval '-2562047788.01521550222 hours'; +select interval '153722867280.912930117 minutes'; +select interval '-153722867280.912930133 minutes'; +select interval '9223372036854.775807 seconds'; +select interval '-9223372036854.775808 seconds'; +select interval '9223372036854775.807 milliseconds'; +select interval '-9223372036854775.808 milliseconds'; +select interval '9223372036854775807 microseconds'; +select interval '-9223372036854775808 microseconds'; + +select interval 'PT2562047788H54.775807S'; +select interval 'PT-2562047788H-54.775808S'; + +select interval 'PT2562047788:00:54.775807'; + +select interval 'PT2562047788.0152155019444'; +select interval 'PT-2562047788.0152155022222'; + +-- overflow each date/time field +select interval '2147483648 years'; +select interval '-2147483649 years'; +select interval '2147483648 months'; +select interval '-2147483649 months'; +select interval '2147483648 days'; +select interval '-2147483649 days'; +select interval '2562047789 hours'; +select interval '-2562047789 hours'; +select interval '153722867281 minutes'; +select interval '-153722867281 minutes'; +select interval '9223372036855 seconds'; +select interval '-9223372036855 seconds'; +select interval '9223372036854777 millisecond'; +select interval '-9223372036854777 millisecond'; +select interval '9223372036854775808 microsecond'; +select interval '-9223372036854775809 microsecond'; + +select interval 'P2147483648'; +select interval 'P-2147483649'; +select interval 'P1-2147483647-2147483647'; +select interval 'PT2562047789'; +select interval 'PT-2562047789'; + +-- overflow with date/time unit aliases +select interval '2147483647 weeks'; +select interval '-2147483648 weeks'; +select interval '2147483647 decades'; +select interval '-2147483648 decades'; +select interval '2147483647 centuries'; +select interval '-2147483648 centuries'; +select interval '2147483647 millennium'; +select interval '-2147483648 millennium'; + +select interval '1 week 2147483647 days'; +select interval '-1 week -2147483648 days'; +select interval '2147483647 days 1 week'; +select interval '-2147483648 days -1 week'; + +select interval 'P1W2147483647D'; +select interval 'P-1W-2147483648D'; +select interval 'P2147483647D1W'; +select interval 'P-2147483648D-1W'; + +select interval '1 decade 2147483647 years'; +select interval '1 century 2147483647 years'; +select interval '1 millennium 2147483647 years'; +select interval '-1 decade -2147483648 years'; +select interval '-1 century -2147483648 years'; +select interval '-1 millennium -2147483648 years'; + +select interval '2147483647 years 1 decade'; +select interval '2147483647 years 1 century'; +select interval '2147483647 years 1 millennium'; +select interval '-2147483648 years -1 decade'; +select interval '-2147483648 years -1 century'; +select interval '-2147483648 years -1 millennium'; + +-- overflowing with fractional fields - postgres format +select interval '0.1 millennium 2147483647 months'; +select interval '0.1 centuries 2147483647 months'; +select interval '0.1 decades 2147483647 months'; +select interval '0.1 yrs 2147483647 months'; +select interval '-0.1 millennium -2147483648 months'; +select interval '-0.1 centuries -2147483648 months'; +select interval '-0.1 decades -2147483648 months'; +select interval '-0.1 yrs -2147483648 months'; + +select interval '2147483647 months 0.1 millennium'; +select interval '2147483647 months 0.1 centuries'; +select interval '2147483647 months 0.1 decades'; +select interval '2147483647 months 0.1 yrs'; +select interval '-2147483648 months -0.1 millennium'; +select interval '-2147483648 months -0.1 centuries'; +select interval '-2147483648 months -0.1 decades'; +select interval '-2147483648 months -0.1 yrs'; + +select interval '0.1 months 2147483647 days'; +select interval '-0.1 months -2147483648 days'; +select interval '2147483647 days 0.1 months'; +select interval '-2147483648 days -0.1 months'; + +select interval '0.5 weeks 2147483647 days'; +select interval '-0.5 weeks -2147483648 days'; +select interval '2147483647 days 0.5 weeks'; +select interval '-2147483648 days -0.5 weeks'; + +select interval '0.01 months 9223372036854775807 microseconds'; +select interval '-0.01 months -9223372036854775808 microseconds'; +select interval '9223372036854775807 microseconds 0.01 months'; +select interval '-9223372036854775808 microseconds -0.01 months'; + +select interval '0.1 weeks 9223372036854775807 microseconds'; +select interval '-0.1 weeks -9223372036854775808 microseconds'; +select interval '9223372036854775807 microseconds 0.1 weeks'; +select interval '-9223372036854775808 microseconds -0.1 weeks'; + +select interval '0.1 days 9223372036854775807 microseconds'; +select interval '-0.1 days -9223372036854775808 microseconds'; +select interval '9223372036854775807 microseconds 0.1 days'; +select interval '-9223372036854775808 microseconds -0.1 days'; + +-- overflowing with fractional fields - ISO8601 format +select interval 'P0.1Y2147483647M'; +select interval 'P-0.1Y-2147483648M'; +select interval 'P2147483647M0.1Y'; +select interval 'P-2147483648M-0.1Y'; + +select interval 'P0.1M2147483647D'; +select interval 'P-0.1M-2147483648D'; +select interval 'P2147483647D0.1M'; +select interval 'P-2147483648D-0.1M'; + +select interval 'P0.5W2147483647D'; +select interval 'P-0.5W-2147483648D'; +select interval 'P2147483647D0.5W'; +select interval 'P-2147483648D-0.5W'; + +select interval 'P0.01MT2562047788H54.775807S'; +select interval 'P-0.01MT-2562047788H-54.775808S'; + +select interval 'P0.1DT2562047788H54.775807S'; +select interval 'P-0.1DT-2562047788H-54.775808S'; + +select interval 'PT2562047788.1H54.775807S'; +select interval 'PT-2562047788.1H-54.775808S'; + +select interval 'PT2562047788H0.1M54.775807S'; +select interval 'PT-2562047788H-0.1M-54.775808S'; + +-- overflowing with fractional fields - ISO8601 alternative format +select interval 'P0.1-2147483647-00'; +select interval 'P00-0.1-2147483647'; +select interval 'P00-0.01-00T2562047788:00:54.775807'; +select interval 'P00-00-0.1T2562047788:00:54.775807'; +select interval 'PT2562047788.1:00:54.775807'; +select interval 'PT2562047788:01.:54.775807'; + +-- overflowing with fractional fields - SQL standard format +select interval '0.1 2562047788:0:54.775807'; +select interval '0.1 2562047788:0:54.775808 ago'; + +select interval '2562047788.1:0:54.775807'; +select interval '2562047788.1:0:54.775808 ago'; + +select interval '2562047788:0.1:54.775807'; +select interval '2562047788:0.1:54.775808 ago'; + +-- overflowing using AGO with INT_MIN +select interval '-2147483648 months ago'; +select interval '-2147483648 days ago'; +select interval '-9223372036854775808 microseconds ago'; +select interval '-2147483648 months -2147483648 days -9223372036854775808 microseconds ago'; + +-- test that INT_MIN number is formatted properly +SET IntervalStyle to postgres; +select interval '-2147483648 months -2147483648 days -9223372036854775808 us'; +SET IntervalStyle to sql_standard; +select interval '-2147483648 months -2147483648 days -9223372036854775808 us'; +SET IntervalStyle to iso_8601; +select interval '-2147483648 months -2147483648 days -9223372036854775808 us'; +SET IntervalStyle to postgres_verbose; +select interval '-2147483648 months -2147483648 days -9223372036854775808 us'; + -- check that '30 days' equals '1 month' according to the hash function select '30 days'::interval = '1 month'::interval as t; select interval_hash('30 days'::interval) = interval_hash('1 month'::interval) as t; |