diff options
Diffstat (limited to 'src/test')
| -rw-r--r-- | src/test/regress/expected/interval.out | 64 | ||||
| -rw-r--r-- | src/test/regress/sql/interval.sql | 27 |
2 files changed, 91 insertions, 0 deletions
diff --git a/src/test/regress/expected/interval.out b/src/test/regress/expected/interval.out index 946c97ad92..f88f34550a 100644 --- a/src/test/regress/expected/interval.out +++ b/src/test/regress/expected/interval.out @@ -207,6 +207,70 @@ SELECT '' AS fortyfive, r1.*, r2.* | 34 years | 6 years (45 rows) +-- Test intervals that are large enough to overflow 64 bits in comparisons +CREATE TEMP TABLE INTERVAL_TBL_OF (f1 interval); +INSERT INTO INTERVAL_TBL_OF (f1) VALUES + ('2147483647 days 2147483647 months'), + ('2147483647 days -2147483648 months'), + ('1 year'), + ('-2147483648 days 2147483647 months'), + ('-2147483648 days -2147483648 months'); +-- these should fail as out-of-range +INSERT INTO INTERVAL_TBL_OF (f1) VALUES ('2147483648 days'); +ERROR: interval field value out of range: "2147483648 days" +LINE 1: INSERT INTO INTERVAL_TBL_OF (f1) VALUES ('2147483648 days'); + ^ +INSERT INTO INTERVAL_TBL_OF (f1) VALUES ('-2147483649 days'); +ERROR: interval field value out of range: "-2147483649 days" +LINE 1: INSERT INTO INTERVAL_TBL_OF (f1) VALUES ('-2147483649 days')... + ^ +INSERT INTO INTERVAL_TBL_OF (f1) VALUES ('2147483647 years'); +ERROR: interval out of range +LINE 1: INSERT INTO INTERVAL_TBL_OF (f1) VALUES ('2147483647 years')... + ^ +INSERT INTO INTERVAL_TBL_OF (f1) VALUES ('-2147483648 years'); +ERROR: interval out of range +LINE 1: INSERT INTO INTERVAL_TBL_OF (f1) VALUES ('-2147483648 years'... + ^ +SELECT r1.*, r2.* + FROM INTERVAL_TBL_OF r1, INTERVAL_TBL_OF r2 + WHERE r1.f1 > r2.f1 + ORDER BY r1.f1, r2.f1; + f1 | f1 +-------------------------------------------+------------------------------------------- + -178956970 years -8 mons +2147483647 days | -178956970 years -8 mons -2147483648 days + 1 year | -178956970 years -8 mons -2147483648 days + 1 year | -178956970 years -8 mons +2147483647 days + 178956970 years 7 mons -2147483648 days | -178956970 years -8 mons -2147483648 days + 178956970 years 7 mons -2147483648 days | -178956970 years -8 mons +2147483647 days + 178956970 years 7 mons -2147483648 days | 1 year + 178956970 years 7 mons 2147483647 days | -178956970 years -8 mons -2147483648 days + 178956970 years 7 mons 2147483647 days | -178956970 years -8 mons +2147483647 days + 178956970 years 7 mons 2147483647 days | 1 year + 178956970 years 7 mons 2147483647 days | 178956970 years 7 mons -2147483648 days +(10 rows) + +CREATE INDEX ON INTERVAL_TBL_OF USING btree (f1); +SET enable_seqscan TO false; +EXPLAIN (COSTS OFF) +SELECT f1 FROM INTERVAL_TBL_OF r1 ORDER BY f1; + QUERY PLAN +-------------------------------------------------------------------- + Index Only Scan using interval_tbl_of_f1_idx on interval_tbl_of r1 +(1 row) + +SELECT f1 FROM INTERVAL_TBL_OF r1 ORDER BY f1; + f1 +------------------------------------------- + -178956970 years -8 mons -2147483648 days + -178956970 years -8 mons +2147483647 days + 1 year + 178956970 years 7 mons -2147483648 days + 178956970 years 7 mons 2147483647 days +(5 rows) + +RESET enable_seqscan; +DROP TABLE INTERVAL_TBL_OF; -- Test multiplication and division with intervals. -- Floating point arithmetic rounding errors can lead to unexpected results, -- though the code attempts to do the right thing and round up to days and diff --git a/src/test/regress/sql/interval.sql b/src/test/regress/sql/interval.sql index cff9adab32..bc5537d1b9 100644 --- a/src/test/regress/sql/interval.sql +++ b/src/test/regress/sql/interval.sql @@ -59,6 +59,33 @@ SELECT '' AS fortyfive, r1.*, r2.* WHERE r1.f1 > r2.f1 ORDER BY r1.f1, r2.f1; +-- Test intervals that are large enough to overflow 64 bits in comparisons +CREATE TEMP TABLE INTERVAL_TBL_OF (f1 interval); +INSERT INTO INTERVAL_TBL_OF (f1) VALUES + ('2147483647 days 2147483647 months'), + ('2147483647 days -2147483648 months'), + ('1 year'), + ('-2147483648 days 2147483647 months'), + ('-2147483648 days -2147483648 months'); +-- these should fail as out-of-range +INSERT INTO INTERVAL_TBL_OF (f1) VALUES ('2147483648 days'); +INSERT INTO INTERVAL_TBL_OF (f1) VALUES ('-2147483649 days'); +INSERT INTO INTERVAL_TBL_OF (f1) VALUES ('2147483647 years'); +INSERT INTO INTERVAL_TBL_OF (f1) VALUES ('-2147483648 years'); + +SELECT r1.*, r2.* + FROM INTERVAL_TBL_OF r1, INTERVAL_TBL_OF r2 + WHERE r1.f1 > r2.f1 + ORDER BY r1.f1, r2.f1; + +CREATE INDEX ON INTERVAL_TBL_OF USING btree (f1); +SET enable_seqscan TO false; +EXPLAIN (COSTS OFF) +SELECT f1 FROM INTERVAL_TBL_OF r1 ORDER BY f1; +SELECT f1 FROM INTERVAL_TBL_OF r1 ORDER BY f1; +RESET enable_seqscan; + +DROP TABLE INTERVAL_TBL_OF; -- Test multiplication and division with intervals. -- Floating point arithmetic rounding errors can lead to unexpected results, |