HAWQ-917. Refactor feature tests for data type check with new googletest framework
Project: http://git-wip-us.apache.org/repos/asf/incubator-hawq/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-hawq/commit/5506a228 Tree: http://git-wip-us.apache.org/repos/asf/incubator-hawq/tree/5506a228 Diff: http://git-wip-us.apache.org/repos/asf/incubator-hawq/diff/5506a228 Branch: refs/heads/master Commit: 5506a22819d58c18681b7a37de58fa4299026b4e Parents: ac03135 Author: Paul Guo <[email protected]> Authored: Tue Jul 12 14:12:19 2016 +0800 Committer: rlei <[email protected]> Committed: Fri Jul 15 17:36:19 2016 +0800 ---------------------------------------------------------------------- src/test/feature/catalog/ans/boolean.ans | 297 ++++++ src/test/feature/catalog/ans/char.ans | 136 +++ src/test/feature/catalog/ans/date.ans | 1178 +++++++++++++++++++++ src/test/feature/catalog/ans/float4.ans | 247 +++++ src/test/feature/catalog/ans/float8.ans | 428 ++++++++ src/test/feature/catalog/ans/int2.ans | 236 +++++ src/test/feature/catalog/ans/int4.ans | 323 ++++++ src/test/feature/catalog/ans/int8.ans | 329 ++++++ src/test/feature/catalog/ans/money.ans | 168 +++ src/test/feature/catalog/ans/name.ans | 135 +++ src/test/feature/catalog/ans/oid.ans | 112 ++ src/test/feature/catalog/ans/text.ans | 28 + src/test/feature/catalog/ans/time.ans | 97 ++ src/test/feature/catalog/ans/type_sanity.ans | 282 +++++ src/test/feature/catalog/ans/varchar.ans | 125 +++ src/test/feature/catalog/sql/boolean.sql | 149 +++ src/test/feature/catalog/sql/char.sql | 75 ++ src/test/feature/catalog/sql/date.sql | 271 +++++ src/test/feature/catalog/sql/float4.sql | 85 ++ src/test/feature/catalog/sql/float8.sql | 167 +++ src/test/feature/catalog/sql/int2.sql | 88 ++ src/test/feature/catalog/sql/int4.sql | 127 +++ src/test/feature/catalog/sql/int8.sql | 71 ++ src/test/feature/catalog/sql/money.sql | 68 ++ src/test/feature/catalog/sql/name.sql | 54 + src/test/feature/catalog/sql/oid.sql | 43 + src/test/feature/catalog/sql/text.sql | 15 + src/test/feature/catalog/sql/time.sql | 41 + src/test/feature/catalog/sql/type_sanity.sql | 223 ++++ src/test/feature/catalog/sql/varchar.sql | 66 ++ src/test/feature/catalog/test_type.cpp | 55 + src/test/regress/expected/boolean.out | 289 ----- src/test/regress/expected/char.out | 122 --- src/test/regress/expected/date.out | 1157 -------------------- src/test/regress/expected/float4.out | 241 ----- src/test/regress/expected/float8.out | 412 ------- src/test/regress/expected/int2.out | 230 ---- src/test/regress/expected/int4.out | 317 ------ src/test/regress/expected/int8.out | 325 ------ src/test/regress/expected/money.out | 158 --- src/test/regress/expected/name.out | 126 --- src/test/regress/expected/oid.out | 102 -- src/test/regress/expected/text.out | 25 - src/test/regress/expected/time.out | 86 -- src/test/regress/expected/type_sanity.out | 282 ----- src/test/regress/expected/varchar.out | 111 -- src/test/regress/sql/boolean.sql | 149 --- src/test/regress/sql/char.sql | 75 -- src/test/regress/sql/date.sql | 271 ----- src/test/regress/sql/float4.sql | 85 -- src/test/regress/sql/float8.sql | 167 --- src/test/regress/sql/int2.sql | 88 -- src/test/regress/sql/int4.sql | 127 --- src/test/regress/sql/int8.sql | 71 -- src/test/regress/sql/money.sql | 68 -- src/test/regress/sql/name.sql | 54 - src/test/regress/sql/oid.sql | 43 - src/test/regress/sql/text.sql | 15 - src/test/regress/sql/time.sql | 41 - src/test/regress/sql/type_sanity.sql | 223 ---- src/test/regress/sql/varchar.sql | 66 -- 61 files changed, 5719 insertions(+), 5526 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/5506a228/src/test/feature/catalog/ans/boolean.ans ---------------------------------------------------------------------- diff --git a/src/test/feature/catalog/ans/boolean.ans b/src/test/feature/catalog/ans/boolean.ans new file mode 100755 index 0000000..eab3cae --- /dev/null +++ b/src/test/feature/catalog/ans/boolean.ans @@ -0,0 +1,297 @@ +-- +-- BOOLEAN +-- +-- +-- sanity check - if this fails go insane! +-- +SELECT 1 AS one; + one +----- + 1 +(1 row) + +-- ******************testing built-in type bool******************** +-- check bool type-casting as well as and, or, not in qualifications-- +SELECT bool 't' AS true; + true +------ + t +(1 row) + +SELECT bool 'f' AS false; + false +------- + f +(1 row) + +SELECT bool 't' or bool 'f' AS true; + true +------ + t +(1 row) + +SELECT bool 't' and bool 'f' AS false; + false +------- + f +(1 row) + +SELECT not bool 'f' AS true; + true +------ + t +(1 row) + +SELECT bool 't' = bool 'f' AS false; + false +------- + f +(1 row) + +SELECT bool 't' <> bool 'f' AS true; + true +------ + t +(1 row) + +CREATE TABLE BOOLTBL1 (f1 bool); +CREATE TABLE +INSERT INTO BOOLTBL1 (f1) VALUES (bool 't'); +INSERT 0 1 +INSERT INTO BOOLTBL1 (f1) VALUES (bool 'True'); +INSERT 0 1 +INSERT INTO BOOLTBL1 (f1) VALUES (bool 'true'); +INSERT 0 1 +-- BOOLTBL1 should be full of true's at this point +SELECT '' AS t_3, BOOLTBL1.* FROM BOOLTBL1; + t_3 | f1 +-----+---- + | t + | t + | t +(3 rows) + +SELECT '' AS t_3, BOOLTBL1.* + FROM BOOLTBL1 + WHERE f1 = bool 'true'; + t_3 | f1 +-----+---- + | t + | t + | t +(3 rows) + +SELECT '' AS t_3, BOOLTBL1.* + FROM BOOLTBL1 + WHERE f1 <> bool 'false'; + t_3 | f1 +-----+---- + | t + | t + | t +(3 rows) + +SELECT '' AS zero, BOOLTBL1.* + FROM BOOLTBL1 + WHERE booleq(bool 'false', f1); + zero | f1 +------+---- +(0 rows) + +INSERT INTO BOOLTBL1 (f1) VALUES (bool 'f'); +INSERT 0 1 +SELECT '' AS f_1, BOOLTBL1.* + FROM BOOLTBL1 + WHERE f1 = bool 'false'; + f_1 | f1 +-----+---- + | f +(1 row) + +CREATE TABLE BOOLTBL2 (f1 bool); +CREATE TABLE +INSERT INTO BOOLTBL2 (f1) VALUES (bool 'f'); +INSERT 0 1 +INSERT INTO BOOLTBL2 (f1) VALUES (bool 'false'); +INSERT 0 1 +INSERT INTO BOOLTBL2 (f1) VALUES (bool 'False'); +INSERT 0 1 +INSERT INTO BOOLTBL2 (f1) VALUES (bool 'FALSE'); +INSERT 0 1 +-- This is now an invalid expression +-- For pre-v6.3 this evaluated to false - thomas 1997-10-23 +INSERT INTO BOOLTBL2 (f1) + VALUES (bool 'XXX'); +psql:/tmp/TestType_boolean.sql:79: ERROR: invalid input syntax for type boolean: "XXX" +LINE 2: VALUES (bool 'XXX'); + ^ +-- BOOLTBL2 should be full of false's at this point +SELECT '' AS f_4, BOOLTBL2.* FROM BOOLTBL2; + f_4 | f1 +-----+---- + | f + | f + | f + | f +(4 rows) + +SELECT '' AS tf_12, BOOLTBL1.*, BOOLTBL2.* + FROM BOOLTBL1, BOOLTBL2 + WHERE BOOLTBL2.f1 <> BOOLTBL1.f1; + tf_12 | f1 | f1 +-------+----+---- + | t | f + | t | f + | t | f + | t | f + | t | f + | t | f + | t | f + | t | f + | t | f + | t | f + | t | f + | t | f +(12 rows) + +SELECT '' AS tf_12, BOOLTBL1.*, BOOLTBL2.* + FROM BOOLTBL1, BOOLTBL2 + WHERE boolne(BOOLTBL2.f1,BOOLTBL1.f1); + tf_12 | f1 | f1 +-------+----+---- + | t | f + | t | f + | t | f + | t | f + | t | f + | t | f + | t | f + | t | f + | t | f + | t | f + | t | f + | t | f +(12 rows) + +SELECT '' AS ff_4, BOOLTBL1.*, BOOLTBL2.* + FROM BOOLTBL1, BOOLTBL2 + WHERE BOOLTBL2.f1 = BOOLTBL1.f1 and BOOLTBL1.f1 = bool 'false' ; + ff_4 | f1 | f1 +------+----+---- + | f | f + | f | f + | f | f + | f | f +(4 rows) + +SELECT '' AS tf_12_ff_4, BOOLTBL1.*, BOOLTBL2.* + FROM BOOLTBL1, BOOLTBL2 + WHERE BOOLTBL2.f1 = BOOLTBL1.f1 or BOOLTBL1.f1 = bool 'true' + ORDER BY BOOLTBL1.f1, BOOLTBL2.f1 ; + tf_12_ff_4 | f1 | f1 +------------+----+---- + | f | f + | f | f + | f | f + | f | f + | t | f + | t | f + | t | f + | t | f + | t | f + | t | f + | t | f + | t | f + | t | f + | t | f + | t | f + | t | f +(16 rows) + +-- +-- SQL92 syntax +-- Try all combinations to ensure that we get nothing when we expect nothing +-- - thomas 2000-01-04 +-- +SELECT '' AS "True", f1 + FROM BOOLTBL1 + WHERE f1 IS TRUE; + True | f1 +------+---- + | t + | t + | t +(3 rows) + +SELECT '' AS "Not False", f1 + FROM BOOLTBL1 + WHERE f1 IS NOT FALSE; + Not False | f1 +-----------+---- + | t + | t + | t +(3 rows) + +SELECT '' AS "False", f1 + FROM BOOLTBL1 + WHERE f1 IS FALSE; + False | f1 +-------+---- + | f +(1 row) + +SELECT '' AS "Not True", f1 + FROM BOOLTBL1 + WHERE f1 IS NOT TRUE; + Not True | f1 +----------+---- + | f +(1 row) + +SELECT '' AS "True", f1 + FROM BOOLTBL2 + WHERE f1 IS TRUE; + True | f1 +------+---- +(0 rows) + +SELECT '' AS "Not False", f1 + FROM BOOLTBL2 + WHERE f1 IS NOT FALSE; + Not False | f1 +-----------+---- +(0 rows) + +SELECT '' AS "False", f1 + FROM BOOLTBL2 + WHERE f1 IS FALSE; + False | f1 +-------+---- + | f + | f + | f + | f +(4 rows) + +SELECT '' AS "Not True", f1 + FROM BOOLTBL2 + WHERE f1 IS NOT TRUE; + Not True | f1 +----------+---- + | f + | f + | f + | f +(4 rows) + +-- +-- Clean up +-- Many tables are retained by the regression test, but these do not seem +-- particularly useful so just get rid of them for now. +-- - thomas 1997-11-30 +-- +DROP TABLE BOOLTBL1; +DROP TABLE +DROP TABLE BOOLTBL2; +DROP TABLE http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/5506a228/src/test/feature/catalog/ans/char.ans ---------------------------------------------------------------------- diff --git a/src/test/feature/catalog/ans/char.ans b/src/test/feature/catalog/ans/char.ans new file mode 100755 index 0000000..39d6c98 --- /dev/null +++ b/src/test/feature/catalog/ans/char.ans @@ -0,0 +1,136 @@ +-- +-- CHAR +-- +-- fixed-length by value +-- internally passed by value if <= 4 bytes in storage +SELECT char 'c' = char 'c' AS true; + true +------ + t +(1 row) + +-- +-- Build a table for testing +-- +CREATE TABLE CHAR_TBL(f1 char); +CREATE TABLE +INSERT INTO CHAR_TBL (f1) VALUES ('a'); +INSERT 0 1 +INSERT INTO CHAR_TBL (f1) VALUES ('A'); +INSERT 0 1 +-- any of the following three input formats are acceptable +INSERT INTO CHAR_TBL (f1) VALUES ('1'); +INSERT 0 1 +INSERT INTO CHAR_TBL (f1) VALUES (2); +INSERT 0 1 +INSERT INTO CHAR_TBL (f1) VALUES ('3'); +INSERT 0 1 +-- zero-length char +INSERT INTO CHAR_TBL (f1) VALUES (''); +INSERT 0 1 +-- try char's of greater than 1 length +INSERT INTO CHAR_TBL (f1) VALUES ('cd'); +psql:/tmp/TestType_char.sql:34: ERROR: value too long for type character(1) +INSERT INTO CHAR_TBL (f1) VALUES ('c '); +INSERT 0 1 +SELECT '' AS seven, * FROM CHAR_TBL; + seven | f1 +-------+---- + | a + | A + | 1 + | 2 + | 3 + | + | c +(7 rows) + +SELECT '' AS six, c.* + FROM CHAR_TBL c + WHERE c.f1 <> 'a'; + six | f1 +-----+---- + | A + | 1 + | 2 + | 3 + | + | c +(6 rows) + +SELECT '' AS one, c.* + FROM CHAR_TBL c + WHERE c.f1 = 'a'; + one | f1 +-----+---- + | a +(1 row) + +SELECT '' AS five, c.* + FROM CHAR_TBL c + WHERE c.f1 < 'a'; + five | f1 +------+---- + | A + | 1 + | 2 + | 3 + | +(5 rows) + +SELECT '' AS six, c.* + FROM CHAR_TBL c + WHERE c.f1 <= 'a'; + six | f1 +-----+---- + | a + | A + | 1 + | 2 + | 3 + | +(6 rows) + +SELECT '' AS one, c.* + FROM CHAR_TBL c + WHERE c.f1 > 'a'; + one | f1 +-----+---- + | c +(1 row) + +SELECT '' AS two, c.* + FROM CHAR_TBL c + WHERE c.f1 >= 'a'; + two | f1 +-----+---- + | a + | c +(2 rows) + +DROP TABLE CHAR_TBL; +DROP TABLE +-- +-- Now test longer arrays of char +-- +CREATE TABLE CHAR_TBL(f1 char(4)); +CREATE TABLE +INSERT INTO CHAR_TBL (f1) VALUES ('a'); +INSERT 0 1 +INSERT INTO CHAR_TBL (f1) VALUES ('ab'); +INSERT 0 1 +INSERT INTO CHAR_TBL (f1) VALUES ('abcd'); +INSERT 0 1 +INSERT INTO CHAR_TBL (f1) VALUES ('abcde'); +psql:/tmp/TestType_char.sql:75: ERROR: value too long for type character(4) +INSERT INTO CHAR_TBL (f1) VALUES ('abcd '); +INSERT 0 1 +SELECT '' AS four, * FROM CHAR_TBL; + four | f1 +------+------ + | a + | ab + | abcd + | abcd +(4 rows) + http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/5506a228/src/test/feature/catalog/ans/date.ans ---------------------------------------------------------------------- diff --git a/src/test/feature/catalog/ans/date.ans b/src/test/feature/catalog/ans/date.ans new file mode 100755 index 0000000..1fe3ad4 --- /dev/null +++ b/src/test/feature/catalog/ans/date.ans @@ -0,0 +1,1178 @@ +-- +-- DATE +-- +CREATE TABLE DATE_TBL (f1 date); +CREATE TABLE +INSERT INTO DATE_TBL VALUES ('1957-04-09'); +INSERT 0 1 +INSERT INTO DATE_TBL VALUES ('1957-06-13'); +INSERT 0 1 +INSERT INTO DATE_TBL VALUES ('1996-02-28'); +INSERT 0 1 +INSERT INTO DATE_TBL VALUES ('1996-02-29'); +INSERT 0 1 +INSERT INTO DATE_TBL VALUES ('1996-03-01'); +INSERT 0 1 +INSERT INTO DATE_TBL VALUES ('1996-03-02'); +INSERT 0 1 +INSERT INTO DATE_TBL VALUES ('1997-02-28'); +INSERT 0 1 +INSERT INTO DATE_TBL VALUES ('1997-02-29'); +psql:/tmp/TestType_date.sql:17: ERROR: date/time field value out of range: "1997-02-29" +INSERT INTO DATE_TBL VALUES ('1997-03-01'); +INSERT 0 1 +INSERT INTO DATE_TBL VALUES ('1997-03-02'); +INSERT 0 1 +INSERT INTO DATE_TBL VALUES ('2000-04-01'); +INSERT 0 1 +INSERT INTO DATE_TBL VALUES ('2000-04-02'); +INSERT 0 1 +INSERT INTO DATE_TBL VALUES ('2000-04-03'); +INSERT 0 1 +INSERT INTO DATE_TBL VALUES ('2038-04-08'); +INSERT 0 1 +INSERT INTO DATE_TBL VALUES ('2039-04-09'); +INSERT 0 1 +INSERT INTO DATE_TBL VALUES ('2040-04-10'); +INSERT 0 1 +SELECT f1 AS "Fifteen" FROM DATE_TBL ORDER BY 1; + Fifteen +------------ + 04-09-1957 + 06-13-1957 + 02-28-1996 + 02-29-1996 + 03-01-1996 + 03-02-1996 + 02-28-1997 + 03-01-1997 + 03-02-1997 + 04-01-2000 + 04-02-2000 + 04-03-2000 + 04-08-2038 + 04-09-2039 + 04-10-2040 +(15 rows) + +SELECT f1 AS "Nine" FROM DATE_TBL WHERE f1 < '2000-01-01' ORDER BY 1; + Nine +------------ + 04-09-1957 + 06-13-1957 + 02-28-1996 + 02-29-1996 + 03-01-1996 + 03-02-1996 + 02-28-1997 + 03-01-1997 + 03-02-1997 +(9 rows) + +SELECT f1 AS "Three" FROM DATE_TBL + WHERE f1 BETWEEN '2000-01-01' AND '2001-01-01' ORDER BY 1; + Three +------------ + 04-01-2000 + 04-02-2000 + 04-03-2000 +(3 rows) + +-- +-- Check all the documented input formats +-- +SET datestyle TO iso; -- display results in ISO +SET +SET datestyle TO ymd; +SET +SELECT date 'January 8, 1999'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '1999-01-08'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '1999-01-18'; + date +------------ + 1999-01-18 +(1 row) + +SELECT date '1/8/1999'; +psql:/tmp/TestType_date.sql:44: ERROR: date/time field value out of range: "1/8/1999" +LINE 1: SELECT date '1/8/1999'; + ^ +HINT: Perhaps you need a different "datestyle" setting. +SELECT date '1/18/1999'; +psql:/tmp/TestType_date.sql:45: ERROR: date/time field value out of range: "1/18/1999" +LINE 1: SELECT date '1/18/1999'; + ^ +HINT: Perhaps you need a different "datestyle" setting. +SELECT date '18/1/1999'; +psql:/tmp/TestType_date.sql:46: ERROR: date/time field value out of range: "18/1/1999" +LINE 1: SELECT date '18/1/1999'; + ^ +HINT: Perhaps you need a different "datestyle" setting. +SELECT date '01/02/03'; + date +------------ + 2001-02-03 +(1 row) + +SELECT date '19990108'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '990108'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '1999.008'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date 'J2451187'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date 'January 8, 99 BC'; +psql:/tmp/TestType_date.sql:52: ERROR: date/time field value out of range: "January 8, 99 BC" +LINE 1: SELECT date 'January 8, 99 BC'; + ^ +HINT: Perhaps you need a different "datestyle" setting. +SELECT date '99-Jan-08'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '1999-Jan-08'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '08-Jan-99'; +psql:/tmp/TestType_date.sql:56: ERROR: date/time field value out of range: "08-Jan-99" +LINE 1: SELECT date '08-Jan-99'; + ^ +HINT: Perhaps you need a different "datestyle" setting. +SELECT date '08-Jan-1999'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date 'Jan-08-99'; +psql:/tmp/TestType_date.sql:58: ERROR: date/time field value out of range: "Jan-08-99" +LINE 1: SELECT date 'Jan-08-99'; + ^ +HINT: Perhaps you need a different "datestyle" setting. +SELECT date 'Jan-08-1999'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '99-08-Jan'; +psql:/tmp/TestType_date.sql:60: ERROR: invalid input syntax for type date: "99-08-Jan" +LINE 1: SELECT date '99-08-Jan'; + ^ +SELECT date '1999-08-Jan'; +psql:/tmp/TestType_date.sql:61: ERROR: invalid input syntax for type date: "1999-08-Jan" +LINE 1: SELECT date '1999-08-Jan'; + ^ +SELECT date '99 Jan 08'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '1999 Jan 08'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '08 Jan 99'; +psql:/tmp/TestType_date.sql:65: ERROR: date/time field value out of range: "08 Jan 99" +LINE 1: SELECT date '08 Jan 99'; + ^ +HINT: Perhaps you need a different "datestyle" setting. +SELECT date '08 Jan 1999'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date 'Jan 08 99'; +psql:/tmp/TestType_date.sql:67: ERROR: date/time field value out of range: "Jan 08 99" +LINE 1: SELECT date 'Jan 08 99'; + ^ +HINT: Perhaps you need a different "datestyle" setting. +SELECT date 'Jan 08 1999'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '99 08 Jan'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '1999 08 Jan'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '99-01-08'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '1999-01-08'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '08-01-99'; +psql:/tmp/TestType_date.sql:74: ERROR: date/time field value out of range: "08-01-99" +LINE 1: SELECT date '08-01-99'; + ^ +HINT: Perhaps you need a different "datestyle" setting. +SELECT date '08-01-1999'; +psql:/tmp/TestType_date.sql:75: ERROR: date/time field value out of range: "08-01-1999" +LINE 1: SELECT date '08-01-1999'; + ^ +HINT: Perhaps you need a different "datestyle" setting. +SELECT date '01-08-99'; +psql:/tmp/TestType_date.sql:76: ERROR: date/time field value out of range: "01-08-99" +LINE 1: SELECT date '01-08-99'; + ^ +HINT: Perhaps you need a different "datestyle" setting. +SELECT date '01-08-1999'; +psql:/tmp/TestType_date.sql:77: ERROR: date/time field value out of range: "01-08-1999" +LINE 1: SELECT date '01-08-1999'; + ^ +HINT: Perhaps you need a different "datestyle" setting. +SELECT date '99-08-01'; + date +------------ + 1999-08-01 +(1 row) + +SELECT date '1999-08-01'; + date +------------ + 1999-08-01 +(1 row) + +SELECT date '99 01 08'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '1999 01 08'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '08 01 99'; +psql:/tmp/TestType_date.sql:83: ERROR: date/time field value out of range: "08 01 99" +LINE 1: SELECT date '08 01 99'; + ^ +HINT: Perhaps you need a different "datestyle" setting. +SELECT date '08 01 1999'; +psql:/tmp/TestType_date.sql:84: ERROR: date/time field value out of range: "08 01 1999" +LINE 1: SELECT date '08 01 1999'; + ^ +HINT: Perhaps you need a different "datestyle" setting. +SELECT date '01 08 99'; +psql:/tmp/TestType_date.sql:85: ERROR: date/time field value out of range: "01 08 99" +LINE 1: SELECT date '01 08 99'; + ^ +HINT: Perhaps you need a different "datestyle" setting. +SELECT date '01 08 1999'; +psql:/tmp/TestType_date.sql:86: ERROR: date/time field value out of range: "01 08 1999" +LINE 1: SELECT date '01 08 1999'; + ^ +HINT: Perhaps you need a different "datestyle" setting. +SELECT date '99 08 01'; + date +------------ + 1999-08-01 +(1 row) + +SELECT date '1999 08 01'; + date +------------ + 1999-08-01 +(1 row) + +SET datestyle TO dmy; +SET +SELECT date 'January 8, 1999'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '1999-01-08'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '1999-01-18'; + date +------------ + 1999-01-18 +(1 row) + +SELECT date '1/8/1999'; + date +------------ + 1999-08-01 +(1 row) + +SELECT date '1/18/1999'; +psql:/tmp/TestType_date.sql:96: ERROR: date/time field value out of range: "1/18/1999" +LINE 1: SELECT date '1/18/1999'; + ^ +HINT: Perhaps you need a different "datestyle" setting. +SELECT date '18/1/1999'; + date +------------ + 1999-01-18 +(1 row) + +SELECT date '01/02/03'; + date +------------ + 2003-02-01 +(1 row) + +SELECT date '19990108'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '990108'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '1999.008'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date 'J2451187'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date 'January 8, 99 BC'; + date +--------------- + 0099-01-08 BC +(1 row) + +SELECT date '99-Jan-08'; +psql:/tmp/TestType_date.sql:105: ERROR: date/time field value out of range: "99-Jan-08" +LINE 1: SELECT date '99-Jan-08'; + ^ +HINT: Perhaps you need a different "datestyle" setting. +SELECT date '1999-Jan-08'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '08-Jan-99'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '08-Jan-1999'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date 'Jan-08-99'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date 'Jan-08-1999'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '99-08-Jan'; +psql:/tmp/TestType_date.sql:111: ERROR: invalid input syntax for type date: "99-08-Jan" +LINE 1: SELECT date '99-08-Jan'; + ^ +SELECT date '1999-08-Jan'; +psql:/tmp/TestType_date.sql:112: ERROR: invalid input syntax for type date: "1999-08-Jan" +LINE 1: SELECT date '1999-08-Jan'; + ^ +SELECT date '99 Jan 08'; +psql:/tmp/TestType_date.sql:114: ERROR: date/time field value out of range: "99 Jan 08" +LINE 1: SELECT date '99 Jan 08'; + ^ +HINT: Perhaps you need a different "datestyle" setting. +SELECT date '1999 Jan 08'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '08 Jan 99'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '08 Jan 1999'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date 'Jan 08 99'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date 'Jan 08 1999'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '99 08 Jan'; +psql:/tmp/TestType_date.sql:120: ERROR: invalid input syntax for type date: "99 08 Jan" +LINE 1: SELECT date '99 08 Jan'; + ^ +SELECT date '1999 08 Jan'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '99-01-08'; +psql:/tmp/TestType_date.sql:123: ERROR: date/time field value out of range: "99-01-08" +LINE 1: SELECT date '99-01-08'; + ^ +HINT: Perhaps you need a different "datestyle" setting. +SELECT date '1999-01-08'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '08-01-99'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '08-01-1999'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '01-08-99'; + date +------------ + 1999-08-01 +(1 row) + +SELECT date '01-08-1999'; + date +------------ + 1999-08-01 +(1 row) + +SELECT date '99-08-01'; +psql:/tmp/TestType_date.sql:129: ERROR: date/time field value out of range: "99-08-01" +LINE 1: SELECT date '99-08-01'; + ^ +HINT: Perhaps you need a different "datestyle" setting. +SELECT date '1999-08-01'; + date +------------ + 1999-08-01 +(1 row) + +SELECT date '99 01 08'; +psql:/tmp/TestType_date.sql:132: ERROR: date/time field value out of range: "99 01 08" +LINE 1: SELECT date '99 01 08'; + ^ +HINT: Perhaps you need a different "datestyle" setting. +SELECT date '1999 01 08'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '08 01 99'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '08 01 1999'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '01 08 99'; + date +------------ + 1999-08-01 +(1 row) + +SELECT date '01 08 1999'; + date +------------ + 1999-08-01 +(1 row) + +SELECT date '99 08 01'; +psql:/tmp/TestType_date.sql:138: ERROR: date/time field value out of range: "99 08 01" +LINE 1: SELECT date '99 08 01'; + ^ +HINT: Perhaps you need a different "datestyle" setting. +SELECT date '1999 08 01'; + date +------------ + 1999-08-01 +(1 row) + +SET datestyle TO mdy; +SET +SELECT date 'January 8, 1999'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '1999-01-08'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '1999-01-18'; + date +------------ + 1999-01-18 +(1 row) + +SELECT date '1/8/1999'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '1/18/1999'; + date +------------ + 1999-01-18 +(1 row) + +SELECT date '18/1/1999'; +psql:/tmp/TestType_date.sql:148: ERROR: date/time field value out of range: "18/1/1999" +LINE 1: SELECT date '18/1/1999'; + ^ +HINT: Perhaps you need a different "datestyle" setting. +SELECT date '01/02/03'; + date +------------ + 2003-01-02 +(1 row) + +SELECT date '19990108'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '990108'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '1999.008'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date 'J2451187'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date 'January 8, 99 BC'; + date +--------------- + 0099-01-08 BC +(1 row) + +SELECT date '99-Jan-08'; +psql:/tmp/TestType_date.sql:156: ERROR: date/time field value out of range: "99-Jan-08" +LINE 1: SELECT date '99-Jan-08'; + ^ +HINT: Perhaps you need a different "datestyle" setting. +SELECT date '1999-Jan-08'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '08-Jan-99'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '08-Jan-1999'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date 'Jan-08-99'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date 'Jan-08-1999'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '99-08-Jan'; +psql:/tmp/TestType_date.sql:162: ERROR: invalid input syntax for type date: "99-08-Jan" +LINE 1: SELECT date '99-08-Jan'; + ^ +SELECT date '1999-08-Jan'; +psql:/tmp/TestType_date.sql:163: ERROR: invalid input syntax for type date: "1999-08-Jan" +LINE 1: SELECT date '1999-08-Jan'; + ^ +SELECT date '99 Jan 08'; +psql:/tmp/TestType_date.sql:165: ERROR: invalid input syntax for type date: "99 Jan 08" +LINE 1: SELECT date '99 Jan 08'; + ^ +SELECT date '1999 Jan 08'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '08 Jan 99'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '08 Jan 1999'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date 'Jan 08 99'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date 'Jan 08 1999'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '99 08 Jan'; +psql:/tmp/TestType_date.sql:171: ERROR: invalid input syntax for type date: "99 08 Jan" +LINE 1: SELECT date '99 08 Jan'; + ^ +SELECT date '1999 08 Jan'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '99-01-08'; +psql:/tmp/TestType_date.sql:174: ERROR: date/time field value out of range: "99-01-08" +LINE 1: SELECT date '99-01-08'; + ^ +HINT: Perhaps you need a different "datestyle" setting. +SELECT date '1999-01-08'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '08-01-99'; + date +------------ + 1999-08-01 +(1 row) + +SELECT date '08-01-1999'; + date +------------ + 1999-08-01 +(1 row) + +SELECT date '01-08-99'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '01-08-1999'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '99-08-01'; +psql:/tmp/TestType_date.sql:180: ERROR: date/time field value out of range: "99-08-01" +LINE 1: SELECT date '99-08-01'; + ^ +HINT: Perhaps you need a different "datestyle" setting. +SELECT date '1999-08-01'; + date +------------ + 1999-08-01 +(1 row) + +SELECT date '99 01 08'; +psql:/tmp/TestType_date.sql:183: ERROR: date/time field value out of range: "99 01 08" +LINE 1: SELECT date '99 01 08'; + ^ +HINT: Perhaps you need a different "datestyle" setting. +SELECT date '1999 01 08'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '08 01 99'; + date +------------ + 1999-08-01 +(1 row) + +SELECT date '08 01 1999'; + date +------------ + 1999-08-01 +(1 row) + +SELECT date '01 08 99'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '01 08 1999'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '99 08 01'; +psql:/tmp/TestType_date.sql:189: ERROR: date/time field value out of range: "99 08 01" +LINE 1: SELECT date '99 08 01'; + ^ +HINT: Perhaps you need a different "datestyle" setting. +SELECT date '1999 08 01'; + date +------------ + 1999-08-01 +(1 row) + +RESET datestyle; +RESET +-- +-- Simple math +-- Leave most of it for the horology tests +-- +SELECT f1 - date '2000-01-01' AS "Days From 2K" FROM DATE_TBL ORDER BY 1; + Days From 2K +-------------- + -15607 + -15542 + -1403 + -1402 + -1401 + -1400 + -1037 + -1036 + -1035 + 91 + 92 + 93 + 13977 + 14343 + 14710 +(15 rows) + +SELECT f1 - date 'epoch' AS "Days From Epoch" FROM DATE_TBL ORDER BY 1; + Days From Epoch +----------------- + -4650 + -4585 + 9554 + 9555 + 9556 + 9557 + 9920 + 9921 + 9922 + 11048 + 11049 + 11050 + 24934 + 25300 + 25667 +(15 rows) + +SELECT date 'yesterday' - date 'today' AS "One day"; + One day +--------- + -1 +(1 row) + +SELECT date 'today' - date 'tomorrow' AS "One day"; + One day +--------- + -1 +(1 row) + +SELECT date 'yesterday' - date 'tomorrow' AS "Two days"; + Two days +---------- + -2 +(1 row) + +SELECT date 'tomorrow' - date 'today' AS "One day"; + One day +--------- + 1 +(1 row) + +SELECT date 'today' - date 'yesterday' AS "One day"; + One day +--------- + 1 +(1 row) + +SELECT date 'tomorrow' - date 'yesterday' AS "Two days"; + Two days +---------- + 2 +(1 row) + +-- +-- test extract! +-- +-- century +-- +SELECT EXTRACT(CENTURY FROM DATE '0101-12-31 BC'); -- -2 + date_part +----------- + -2 +(1 row) + +SELECT EXTRACT(CENTURY FROM DATE '0100-12-31 BC'); -- -1 + date_part +----------- + -1 +(1 row) + +SELECT EXTRACT(CENTURY FROM DATE '0001-12-31 BC'); -- -1 + date_part +----------- + -1 +(1 row) + +SELECT EXTRACT(CENTURY FROM DATE '0001-01-01'); -- 1 + date_part +----------- + 1 +(1 row) + +SELECT EXTRACT(CENTURY FROM DATE '0001-01-01 AD'); -- 1 + date_part +----------- + 1 +(1 row) + +SELECT EXTRACT(CENTURY FROM DATE '1900-12-31'); -- 19 + date_part +----------- + 19 +(1 row) + +SELECT EXTRACT(CENTURY FROM DATE '1901-01-01'); -- 20 + date_part +----------- + 20 +(1 row) + +SELECT EXTRACT(CENTURY FROM DATE '2000-12-31'); -- 20 + date_part +----------- + 20 +(1 row) + +SELECT EXTRACT(CENTURY FROM DATE '2001-01-01'); -- 21 + date_part +----------- + 21 +(1 row) + +SELECT EXTRACT(CENTURY FROM CURRENT_DATE)>=21 AS True; -- true + true +------ + t +(1 row) + +-- +-- millennium +-- +SELECT EXTRACT(MILLENNIUM FROM DATE '0001-12-31 BC'); -- -1 + date_part +----------- + -1 +(1 row) + +SELECT EXTRACT(MILLENNIUM FROM DATE '0001-01-01 AD'); -- 1 + date_part +----------- + 1 +(1 row) + +SELECT EXTRACT(MILLENNIUM FROM DATE '1000-12-31'); -- 1 + date_part +----------- + 1 +(1 row) + +SELECT EXTRACT(MILLENNIUM FROM DATE '1001-01-01'); -- 2 + date_part +----------- + 2 +(1 row) + +SELECT EXTRACT(MILLENNIUM FROM DATE '2000-12-31'); -- 2 + date_part +----------- + 2 +(1 row) + +SELECT EXTRACT(MILLENNIUM FROM DATE '2001-01-01'); -- 3 + date_part +----------- + 3 +(1 row) + +-- next test to be fixed on the turn of the next millennium;-) +SELECT EXTRACT(MILLENNIUM FROM CURRENT_DATE); -- 3 + date_part +----------- + 3 +(1 row) + +-- +-- decade +-- +SELECT EXTRACT(DECADE FROM DATE '1994-12-25'); -- 199 + date_part +----------- + 199 +(1 row) + +SELECT EXTRACT(DECADE FROM DATE '0010-01-01'); -- 1 + date_part +----------- + 1 +(1 row) + +SELECT EXTRACT(DECADE FROM DATE '0009-12-31'); -- 0 + date_part +----------- + 0 +(1 row) + +SELECT EXTRACT(DECADE FROM DATE '0001-01-01 BC'); -- 0 + date_part +----------- + 0 +(1 row) + +SELECT EXTRACT(DECADE FROM DATE '0002-12-31 BC'); -- -1 + date_part +----------- + -1 +(1 row) + +SELECT EXTRACT(DECADE FROM DATE '0011-01-01 BC'); -- -1 + date_part +----------- + -1 +(1 row) + +SELECT EXTRACT(DECADE FROM DATE '0012-12-31 BC'); -- -2 + date_part +----------- + -2 +(1 row) + +-- +-- some other types: +-- +-- on a timestamp. +SELECT EXTRACT(CENTURY FROM NOW())>=21 AS True; -- true + true +------ + t +(1 row) + +SELECT EXTRACT(CENTURY FROM TIMESTAMP '1970-03-20 04:30:00.00000'); -- 20 + date_part +----------- + 20 +(1 row) + +-- on an interval +SELECT EXTRACT(CENTURY FROM INTERVAL '100 y'); -- 1 + date_part +----------- + 1 +(1 row) + +SELECT EXTRACT(CENTURY FROM INTERVAL '99 y'); -- 0 + date_part +----------- + 0 +(1 row) + +SELECT EXTRACT(CENTURY FROM INTERVAL '-99 y'); -- 0 + date_part +----------- + 0 +(1 row) + +SELECT EXTRACT(CENTURY FROM INTERVAL '-100 y'); -- -1 + date_part +----------- + -1 +(1 row) + +-- +-- test trunc function! +-- +SELECT DATE_TRUNC('MILLENNIUM', TIMESTAMP '1970-03-20 04:30:00.00000'); -- 1001 + date_trunc +-------------------------- + Thu Jan 01 00:00:00 1001 +(1 row) + +SELECT DATE_TRUNC('MILLENNIUM', DATE '1970-03-20'); -- 1001-01-01 + date_trunc +------------------------------ + Thu Jan 01 00:00:00 1001 PST +(1 row) + +SELECT DATE_TRUNC('CENTURY', TIMESTAMP '1970-03-20 04:30:00.00000'); -- 1901 + date_trunc +-------------------------- + Tue Jan 01 00:00:00 1901 +(1 row) + +SELECT DATE_TRUNC('CENTURY', DATE '1970-03-20'); -- 1901 + date_trunc +------------------------------ + Tue Jan 01 00:00:00 1901 PST +(1 row) + +SELECT DATE_TRUNC('CENTURY', DATE '2004-08-10'); -- 2001-01-01 + date_trunc +------------------------------ + Mon Jan 01 00:00:00 2001 PST +(1 row) + +SELECT DATE_TRUNC('CENTURY', DATE '0002-02-04'); -- 0001-01-01 + date_trunc +------------------------------ + Mon Jan 01 00:00:00 0001 PST +(1 row) + +SELECT DATE_TRUNC('CENTURY', DATE '0055-08-10 BC'); -- 0100-01-01 BC + date_trunc +--------------------------------- + Tue Jan 01 00:00:00 0100 PST BC +(1 row) + +SELECT DATE_TRUNC('DECADE', DATE '1993-12-25'); -- 1990-01-01 + date_trunc +------------------------------ + Mon Jan 01 00:00:00 1990 PST +(1 row) + +SELECT DATE_TRUNC('DECADE', DATE '0004-12-25'); -- 0001-01-01 BC + date_trunc +--------------------------------- + Sat Jan 01 00:00:00 0001 PST BC +(1 row) + +SELECT DATE_TRUNC('DECADE', DATE '0002-12-31 BC'); -- 0011-01-01 BC + date_trunc +--------------------------------- + Mon Jan 01 00:00:00 0011 PST BC +(1 row) + http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/5506a228/src/test/feature/catalog/ans/float4.ans ---------------------------------------------------------------------- diff --git a/src/test/feature/catalog/ans/float4.ans b/src/test/feature/catalog/ans/float4.ans new file mode 100755 index 0000000..c53059c --- /dev/null +++ b/src/test/feature/catalog/ans/float4.ans @@ -0,0 +1,247 @@ +-- +-- FLOAT4 +-- +CREATE TABLE FLOAT4_TBL (f1 float4); +CREATE TABLE +INSERT INTO FLOAT4_TBL(f1) VALUES (' 0.0'); +INSERT 0 1 +INSERT INTO FLOAT4_TBL(f1) VALUES ('1004.30 '); +INSERT 0 1 +INSERT INTO FLOAT4_TBL(f1) VALUES (' -34.84 '); +INSERT 0 1 +INSERT INTO FLOAT4_TBL(f1) VALUES ('1.2345678901234e+20'); +INSERT 0 1 +INSERT INTO FLOAT4_TBL(f1) VALUES ('1.2345678901234e-20'); +INSERT 0 1 +-- test for over and under flow +INSERT INTO FLOAT4_TBL(f1) VALUES ('10e40'); +psql:/tmp/TestType_float4.sql:17: ERROR: value out of range: overflow +INSERT INTO FLOAT4_TBL(f1) VALUES ('-10e40'); +psql:/tmp/TestType_float4.sql:18: ERROR: value out of range: overflow +INSERT INTO FLOAT4_TBL(f1) VALUES ('10e-40'); +INSERT 0 1 +INSERT INTO FLOAT4_TBL(f1) VALUES ('-10e-40'); +INSERT 0 1 +-- bad input +INSERT INTO FLOAT4_TBL(f1) VALUES (''); +psql:/tmp/TestType_float4.sql:23: ERROR: invalid input syntax for type real: "" +INSERT INTO FLOAT4_TBL(f1) VALUES (' '); +psql:/tmp/TestType_float4.sql:24: ERROR: invalid input syntax for type real: " " +INSERT INTO FLOAT4_TBL(f1) VALUES ('xyz'); +psql:/tmp/TestType_float4.sql:25: ERROR: invalid input syntax for type real: "xyz" +INSERT INTO FLOAT4_TBL(f1) VALUES ('5.0.0'); +psql:/tmp/TestType_float4.sql:26: ERROR: invalid input syntax for type real: "5.0.0" +INSERT INTO FLOAT4_TBL(f1) VALUES ('5 . 0'); +psql:/tmp/TestType_float4.sql:27: ERROR: invalid input syntax for type real: "5 . 0" +INSERT INTO FLOAT4_TBL(f1) VALUES ('5. 0'); +psql:/tmp/TestType_float4.sql:28: ERROR: invalid input syntax for type real: "5. 0" +INSERT INTO FLOAT4_TBL(f1) VALUES (' - 3.0'); +psql:/tmp/TestType_float4.sql:29: ERROR: invalid input syntax for type real: " - 3.0" +INSERT INTO FLOAT4_TBL(f1) VALUES ('123 5'); +psql:/tmp/TestType_float4.sql:30: ERROR: invalid input syntax for type real: "123 5" +-- special inputs +SELECT 'NaN'::float4; + float4 +-------- + NaN +(1 row) + +SELECT 'nan'::float4; + float4 +-------- + NaN +(1 row) + +SELECT ' NAN '::float4; + float4 +-------- + NaN +(1 row) + +SELECT 'infinity'::float4; + float4 +---------- + Infinity +(1 row) + +SELECT ' -INFINiTY '::float4; + float4 +----------- + -Infinity +(1 row) + +-- bad special inputs +SELECT 'N A N'::float4; +psql:/tmp/TestType_float4.sql:39: ERROR: invalid input syntax for type real: "N A N" +LINE 1: SELECT 'N A N'::float4; + ^ +SELECT 'NaN x'::float4; +psql:/tmp/TestType_float4.sql:40: ERROR: invalid input syntax for type real: "NaN x" +LINE 1: SELECT 'NaN x'::float4; + ^ +SELECT ' INFINITY x'::float4; +psql:/tmp/TestType_float4.sql:41: ERROR: invalid input syntax for type real: " INFINITY x" +LINE 1: SELECT ' INFINITY x'::float4; + ^ +SELECT 'Infinity'::float4 + 100.0; + ?column? +---------- + Infinity +(1 row) + +SELECT 'Infinity'::float4 / 'Infinity'::float4; + ?column? +---------- + NaN +(1 row) + +SELECT 'nan'::float4 / 'nan'::float4; + ?column? +---------- + NaN +(1 row) + +SELECT '' AS five, * FROM FLOAT4_TBL ORDER BY 2; + five | f1 +------+------------- + | -34.84 + | -1e-39 + | 0 + | 1e-39 + | 1.23457e-20 + | 1004.3 + | 1.23457e+20 +(7 rows) + +SELECT '' AS four, f.* FROM FLOAT4_TBL f WHERE f.f1 <> '1004.3' ORDER BY 2; + four | f1 +------+------------- + | -34.84 + | -1e-39 + | 0 + | 1e-39 + | 1.23457e-20 + | 1.23457e+20 +(6 rows) + +SELECT '' AS one, f.* FROM FLOAT4_TBL f WHERE f.f1 = '1004.3' ORDER BY 2; + one | f1 +-----+-------- + | 1004.3 +(1 row) + +SELECT '' AS three, f.* FROM FLOAT4_TBL f WHERE '1004.3' > f.f1 ORDER BY 2; + three | f1 +-------+------------- + | -34.84 + | -1e-39 + | 0 + | 1e-39 + | 1.23457e-20 +(5 rows) + +SELECT '' AS three, f.* FROM FLOAT4_TBL f WHERE f.f1 < '1004.3' ORDER BY 2; + three | f1 +-------+------------- + | -34.84 + | -1e-39 + | 0 + | 1e-39 + | 1.23457e-20 +(5 rows) + +SELECT '' AS four, f.* FROM FLOAT4_TBL f WHERE '1004.3' >= f.f1 ORDER BY 2; + four | f1 +------+------------- + | -34.84 + | -1e-39 + | 0 + | 1e-39 + | 1.23457e-20 + | 1004.3 +(6 rows) + +SELECT '' AS four, f.* FROM FLOAT4_TBL f WHERE f.f1 <= '1004.3' ORDER BY 2; + four | f1 +------+------------- + | -34.84 + | -1e-39 + | 0 + | 1e-39 + | 1.23457e-20 + | 1004.3 +(6 rows) + +SELECT '' AS three, f.f1, f.f1 * '-10' AS x FROM FLOAT4_TBL f + WHERE f.f1 > '0.0' ORDER BY 2; + three | f1 | x +-------+-------------+-------------- + | 1e-39 | -1e-38 + | 1.23457e-20 | -1.23457e-19 + | 1004.3 | -10043 + | 1.23457e+20 | -1.23457e+21 +(4 rows) + +SELECT '' AS three, f.f1, f.f1 + '-10' AS x FROM FLOAT4_TBL f + WHERE f.f1 > '0.0' ORDER BY 2; + three | f1 | x +-------+-------------+------------- + | 1e-39 | -10 + | 1.23457e-20 | -10 + | 1004.3 | 994.3 + | 1.23457e+20 | 1.23457e+20 +(4 rows) + +SELECT '' AS three, f.f1, f.f1 / '-10' AS x FROM FLOAT4_TBL f + WHERE f.f1 > '0.0' ORDER BY 2; + three | f1 | x +-------+-------------+-------------- + | 1e-39 | -9.99995e-41 + | 1.23457e-20 | -1.23457e-21 + | 1004.3 | -100.43 + | 1.23457e+20 | -1.23457e+19 +(4 rows) + +SELECT '' AS three, f.f1, f.f1 - '-10' AS x FROM FLOAT4_TBL f + WHERE f.f1 > '0.0' ORDER BY 2; + three | f1 | x +-------+-------------+------------- + | 1e-39 | 10 + | 1.23457e-20 | 10 + | 1004.3 | 1014.3 + | 1.23457e+20 | 1.23457e+20 +(4 rows) + +-- test divide by zero +SELECT '' AS bad, f.f1 / '0.0' from FLOAT4_TBL f; +psql:/tmp/TestType_float4.sql:75: ERROR: division by zero +SELECT '' AS five, * FROM FLOAT4_TBL ORDER BY 2; + five | f1 +------+------------- + | -34.84 + | -1e-39 + | 0 + | 1e-39 + | 1.23457e-20 + | 1004.3 + | 1.23457e+20 +(7 rows) + +-- test the unary float4abs operator +SELECT '' AS five, f.f1, @f.f1 AS abs_f1 FROM FLOAT4_TBL f ORDER BY 2; + five | f1 | abs_f1 +------+-------------+------------- + | -34.84 | 34.84 + | -1e-39 | 1e-39 + | 0 | 0 + | 1e-39 | 1e-39 + | 1.23457e-20 | 1.23457e-20 + | 1004.3 | 1004.3 + | 1.23457e+20 | 1.23457e+20 +(7 rows) + +-- MPP doesn't support this yet. +--UPDATE FLOAT4_TBL +-- SET f1 = FLOAT4_TBL.f1 * '-1' +-- WHERE FLOAT4_TBL.f1 > '0.0'; +--SELECT '' AS five, * FROM FLOAT4_TBL ORDER BY 2; http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/5506a228/src/test/feature/catalog/ans/float8.ans ---------------------------------------------------------------------- diff --git a/src/test/feature/catalog/ans/float8.ans b/src/test/feature/catalog/ans/float8.ans new file mode 100755 index 0000000..a1c6dab --- /dev/null +++ b/src/test/feature/catalog/ans/float8.ans @@ -0,0 +1,428 @@ +-- +-- FLOAT8 +-- +CREATE TABLE FLOAT8_TBL(i INT DEFAULT 1, f1 float8); +CREATE TABLE +INSERT INTO FLOAT8_TBL(f1) VALUES (' 0.0 '); +INSERT 0 1 +INSERT INTO FLOAT8_TBL(f1) VALUES ('1004.30 '); +INSERT 0 1 +INSERT INTO FLOAT8_TBL(f1) VALUES (' -34.84'); +INSERT 0 1 +INSERT INTO FLOAT8_TBL(f1) VALUES ('1.2345678901234e+200'); +INSERT 0 1 +INSERT INTO FLOAT8_TBL(f1) VALUES ('1.2345678901234e-200'); +INSERT 0 1 +-- test for underflow and overflow handling +SELECT '10e400'::float8; +psql:/tmp/TestType_float8.sql:17: ERROR: "10e400" is out of range for type double precision +LINE 1: SELECT '10e400'::float8; + ^ +SELECT '-10e400'::float8; +psql:/tmp/TestType_float8.sql:18: ERROR: "-10e400" is out of range for type double precision +LINE 1: SELECT '-10e400'::float8; + ^ +SELECT '10e-400'::float8; +psql:/tmp/TestType_float8.sql:19: ERROR: "10e-400" is out of range for type double precision +LINE 1: SELECT '10e-400'::float8; + ^ +SELECT '-10e-400'::float8; +psql:/tmp/TestType_float8.sql:20: ERROR: "-10e-400" is out of range for type double precision +LINE 1: SELECT '-10e-400'::float8; + ^ +-- bad input +INSERT INTO FLOAT8_TBL(f1) VALUES (''); +psql:/tmp/TestType_float8.sql:23: ERROR: invalid input syntax for type double precision: "" +INSERT INTO FLOAT8_TBL(f1) VALUES (' '); +psql:/tmp/TestType_float8.sql:24: ERROR: invalid input syntax for type double precision: " " +INSERT INTO FLOAT8_TBL(f1) VALUES ('xyz'); +psql:/tmp/TestType_float8.sql:25: ERROR: invalid input syntax for type double precision: "xyz" +INSERT INTO FLOAT8_TBL(f1) VALUES ('5.0.0'); +psql:/tmp/TestType_float8.sql:26: ERROR: invalid input syntax for type double precision: "5.0.0" +INSERT INTO FLOAT8_TBL(f1) VALUES ('5 . 0'); +psql:/tmp/TestType_float8.sql:27: ERROR: invalid input syntax for type double precision: "5 . 0" +INSERT INTO FLOAT8_TBL(f1) VALUES ('5. 0'); +psql:/tmp/TestType_float8.sql:28: ERROR: invalid input syntax for type double precision: "5. 0" +INSERT INTO FLOAT8_TBL(f1) VALUES (' - 3'); +psql:/tmp/TestType_float8.sql:29: ERROR: invalid input syntax for type double precision: " - 3" +INSERT INTO FLOAT8_TBL(f1) VALUES ('123 5'); +psql:/tmp/TestType_float8.sql:30: ERROR: invalid input syntax for type double precision: "123 5" +-- special inputs +SELECT 'NaN'::float8; + float8 +-------- + NaN +(1 row) + +SELECT 'nan'::float8; + float8 +-------- + NaN +(1 row) + +SELECT ' NAN '::float8; + float8 +-------- + NaN +(1 row) + +SELECT 'infinity'::float8; + float8 +---------- + Infinity +(1 row) + +SELECT ' -INFINiTY '::float8; + float8 +----------- + -Infinity +(1 row) + +-- bad special inputs +SELECT 'N A N'::float8; +psql:/tmp/TestType_float8.sql:39: ERROR: invalid input syntax for type double precision: "N A N" +LINE 1: SELECT 'N A N'::float8; + ^ +SELECT 'NaN x'::float8; +psql:/tmp/TestType_float8.sql:40: ERROR: invalid input syntax for type double precision: "NaN x" +LINE 1: SELECT 'NaN x'::float8; + ^ +SELECT ' INFINITY x'::float8; +psql:/tmp/TestType_float8.sql:41: ERROR: invalid input syntax for type double precision: " INFINITY x" +LINE 1: SELECT ' INFINITY x'::float8; + ^ +SELECT 'Infinity'::float8 + 100.0; + ?column? +---------- + Infinity +(1 row) + +SELECT 'Infinity'::float8 / 'Infinity'::float8; + ?column? +---------- + NaN +(1 row) + +SELECT 'nan'::float8 / 'nan'::float8; + ?column? +---------- + NaN +(1 row) + +SELECT '' AS five, f1 FROM FLOAT8_TBL ORDER BY 2; + five | f1 +------+---------------------- + | -34.84 + | 0 + | 1.2345678901234e-200 + | 1004.3 + | 1.2345678901234e+200 +(5 rows) + +SELECT '' AS four, f.f1 FROM FLOAT8_TBL f WHERE f.f1 <> '1004.3' ORDER BY 2; + four | f1 +------+---------------------- + | -34.84 + | 0 + | 1.2345678901234e-200 + | 1.2345678901234e+200 +(4 rows) + +SELECT '' AS one, f.f1 FROM FLOAT8_TBL f WHERE f.f1 = '1004.3' ORDER BY 2; + one | f1 +-----+-------- + | 1004.3 +(1 row) + +SELECT '' AS three, f.f1 FROM FLOAT8_TBL f WHERE '1004.3' > f.f1 ORDER BY 2; + three | f1 +-------+---------------------- + | -34.84 + | 0 + | 1.2345678901234e-200 +(3 rows) + +SELECT '' AS three, f.f1 FROM FLOAT8_TBL f WHERE f.f1 < '1004.3' ORDER BY 2; + three | f1 +-------+---------------------- + | -34.84 + | 0 + | 1.2345678901234e-200 +(3 rows) + +SELECT '' AS four, f.f1 FROM FLOAT8_TBL f WHERE '1004.3' >= f.f1 ORDER BY 2; + four | f1 +------+---------------------- + | -34.84 + | 0 + | 1.2345678901234e-200 + | 1004.3 +(4 rows) + +SELECT '' AS four, f.f1 FROM FLOAT8_TBL f WHERE f.f1 <= '1004.3' ORDER BY 2; + four | f1 +------+---------------------- + | -34.84 + | 0 + | 1.2345678901234e-200 + | 1004.3 +(4 rows) + +SELECT '' AS three, f.f1, f.f1 * '-10' AS x + FROM FLOAT8_TBL f + WHERE f.f1 > '0.0' ORDER BY 2; + three | f1 | x +-------+----------------------+----------------------- + | 1.2345678901234e-200 | -1.2345678901234e-199 + | 1004.3 | -10043 + | 1.2345678901234e+200 | -1.2345678901234e+201 +(3 rows) + +SELECT '' AS three, f.f1, f.f1 + '-10' AS x + FROM FLOAT8_TBL f + WHERE f.f1 > '0.0' ORDER BY 2; + three | f1 | x +-------+----------------------+---------------------- + | 1.2345678901234e-200 | -10 + | 1004.3 | 994.3 + | 1.2345678901234e+200 | 1.2345678901234e+200 +(3 rows) + +SELECT '' AS three, f.f1, f.f1 / '-10' AS x + FROM FLOAT8_TBL f + WHERE f.f1 > '0.0' ORDER BY 2; + three | f1 | x +-------+----------------------+----------------------- + | 1.2345678901234e-200 | -1.2345678901234e-201 + | 1004.3 | -100.43 + | 1.2345678901234e+200 | -1.2345678901234e+199 +(3 rows) + +SELECT '' AS three, f.f1, f.f1 - '-10' AS x + FROM FLOAT8_TBL f + WHERE f.f1 > '0.0' ORDER BY 2; + three | f1 | x +-------+----------------------+---------------------- + | 1.2345678901234e-200 | 10 + | 1004.3 | 1014.3 + | 1.2345678901234e+200 | 1.2345678901234e+200 +(3 rows) + +SELECT '' AS one, f.f1 ^ '2.0' AS square_f1 + FROM FLOAT8_TBL f where f.f1 = '1004.3'; + one | square_f1 +-----+------------ + | 1008618.49 +(1 row) + +-- absolute value +SELECT '' AS five, f.f1, @f.f1 AS abs_f1 + FROM FLOAT8_TBL f ORDER BY 2; + five | f1 | abs_f1 +------+----------------------+---------------------- + | -34.84 | 34.84 + | 0 | 0 + | 1.2345678901234e-200 | 1.2345678901234e-200 + | 1004.3 | 1004.3 + | 1.2345678901234e+200 | 1.2345678901234e+200 +(5 rows) + +-- truncate +SELECT '' AS five, f.f1, trunc(f.f1) AS trunc_f1 + FROM FLOAT8_TBL f ORDER BY 2; + five | f1 | trunc_f1 +------+----------------------+---------------------- + | -34.84 | -34 + | 0 | 0 + | 1.2345678901234e-200 | 0 + | 1004.3 | 1004 + | 1.2345678901234e+200 | 1.2345678901234e+200 +(5 rows) + +-- round +SELECT '' AS five, f.f1, round(f.f1) AS round_f1 + FROM FLOAT8_TBL f ORDER BY 2; + five | f1 | round_f1 +------+----------------------+---------------------- + | -34.84 | -35 + | 0 | 0 + | 1.2345678901234e-200 | 0 + | 1004.3 | 1004 + | 1.2345678901234e+200 | 1.2345678901234e+200 +(5 rows) + +-- ceil / ceiling +select ceil(f1) as ceil_f1 from float8_tbl f ORDER BY 1; + ceil_f1 +---------------------- + -34 + 0 + 1 + 1005 + 1.2345678901234e+200 +(5 rows) + +select ceiling(f1) as ceiling_f1 from float8_tbl f ORDER BY 1; + ceiling_f1 +---------------------- + -34 + 0 + 1 + 1005 + 1.2345678901234e+200 +(5 rows) + +-- floor +select floor(f1) as floor_f1 from float8_tbl f ORDER BY 1; + floor_f1 +---------------------- + -35 + 0 + 0 + 1004 + 1.2345678901234e+200 +(5 rows) + +-- sign +select sign(f1) as sign_f1 from float8_tbl f ORDER BY 1; + sign_f1 +--------- + -1 + 0 + 1 + 1 + 1 +(5 rows) + +-- square root +SELECT sqrt(float8 '64') AS eight; + eight +------- + 8 +(1 row) + +SELECT |/ float8 '64' AS eight; + eight +------- + 8 +(1 row) + +SELECT '' AS three, f.f1, |/f.f1 AS sqrt_f1 + FROM FLOAT8_TBL f + WHERE f.f1 > '0.0' ORDER BY 2; + three | f1 | sqrt_f1 +-------+----------------------+----------------------- + | 1.2345678901234e-200 | 1.11111110611109e-100 + | 1004.3 | 31.6906926399535 + | 1.2345678901234e+200 | 1.11111110611109e+100 +(3 rows) + +-- power +SELECT power(float8 '144', float8 '0.5'); + power +------- + 12 +(1 row) + +-- take exp of ln(f.f1) +SELECT '' AS three, f.f1, exp(ln(f.f1)) AS exp_ln_f1 + FROM FLOAT8_TBL f + WHERE f.f1 > '0.0' ORDER BY 2; + three | f1 | exp_ln_f1 +-------+----------------------+----------------------- + | 1.2345678901234e-200 | 1.23456789012339e-200 + | 1004.3 | 1004.3 + | 1.2345678901234e+200 | 1.23456789012338e+200 +(3 rows) + +-- cube root +SELECT ||/ float8 '27' AS three; + three +------- + 3 +(1 row) + +SELECT '' AS five, f.f1, ||/f.f1 AS cbrt_f1 FROM FLOAT8_TBL f ORDER BY 2; + five | f1 | cbrt_f1 +------+----------------------+---------------------- + | -34.84 | -3.26607421344208 + | 0 | 0 + | 1.2345678901234e-200 | 2.3112042409018e-67 + | 1004.3 | 10.014312837827 + | 1.2345678901234e+200 | 4.97933859234765e+66 +(5 rows) + +SELECT '' AS five, f1 FROM FLOAT8_TBL ORDER BY 2; + five | f1 +------+---------------------- + | -34.84 + | 0 + | 1.2345678901234e-200 + | 1004.3 + | 1.2345678901234e+200 +(5 rows) + +UPDATE FLOAT8_TBL + SET f1 = FLOAT8_TBL.f1 * '-1' + WHERE FLOAT8_TBL.f1 > '0.0'; +psql:/tmp/TestType_float8.sql:129: ERROR: Update append-only table statement not supported yet +SELECT '' AS bad, f.f1 * '1e200' from FLOAT8_TBL f; +psql:/tmp/TestType_float8.sql:131: ERROR: value out of range: overflow +SELECT '' AS bad, f.f1 ^ '1e200' from FLOAT8_TBL f; +psql:/tmp/TestType_float8.sql:133: ERROR: value out of range: overflow +SELECT '' AS bad, ln(f.f1) from FLOAT8_TBL f where f.f1 = '0.0' ; +psql:/tmp/TestType_float8.sql:135: ERROR: cannot take logarithm of zero +SELECT '' AS bad, ln(f.f1) from FLOAT8_TBL f where f.f1 < '0.0' ; +psql:/tmp/TestType_float8.sql:137: ERROR: cannot take logarithm of a negative number +SELECT '' AS bad, exp(f.f1) from FLOAT8_TBL f; +psql:/tmp/TestType_float8.sql:139: ERROR: value out of range: overflow +SELECT '' AS bad, f.f1 / '0.0' from FLOAT8_TBL f; +psql:/tmp/TestType_float8.sql:141: ERROR: division by zero +SELECT '' AS five, f1 FROM FLOAT8_TBL ORDER BY 2; + five | f1 +------+---------------------- + | -34.84 + | 0 + | 1.2345678901234e-200 + | 1004.3 + | 1.2345678901234e+200 +(5 rows) + +-- test for over- and underflow +INSERT INTO FLOAT8_TBL(f1) VALUES ('10e400'); +psql:/tmp/TestType_float8.sql:146: ERROR: "10e400" is out of range for type double precision +INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e400'); +psql:/tmp/TestType_float8.sql:148: ERROR: "-10e400" is out of range for type double precision +INSERT INTO FLOAT8_TBL(f1) VALUES ('10e-400'); +psql:/tmp/TestType_float8.sql:150: ERROR: "10e-400" is out of range for type double precision +INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e-400'); +psql:/tmp/TestType_float8.sql:152: ERROR: "-10e-400" is out of range for type double precision +-- maintain external table consistency across platforms +-- delete all values and reinsert well-behaved ones +DELETE FROM FLOAT8_TBL; +psql:/tmp/TestType_float8.sql:157: ERROR: Delete append-only table statement not supported yet +INSERT INTO FLOAT8_TBL(f1) VALUES ('0.0'); +INSERT 0 1 +INSERT INTO FLOAT8_TBL(f1) VALUES ('-34.84'); +INSERT 0 1 +INSERT INTO FLOAT8_TBL(f1) VALUES ('-1004.30'); +INSERT 0 1 +INSERT INTO FLOAT8_TBL(f1) VALUES ('-1.2345678901234e+200'); +INSERT 0 1 +INSERT INTO FLOAT8_TBL(f1) VALUES ('-1.2345678901234e-200'); +INSERT 0 1 +SELECT '' AS five, f1 FROM FLOAT8_TBL ORDER BY 2; + five | f1 +------+----------------------- + | -1.2345678901234e+200 + | -1004.3 + | -34.84 + | -34.84 + | -1.2345678901234e-200 + | 0 + | 0 + | 1.2345678901234e-200 + | 1004.3 + | 1.2345678901234e+200 +(10 rows) + http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/5506a228/src/test/feature/catalog/ans/int2.ans ---------------------------------------------------------------------- diff --git a/src/test/feature/catalog/ans/int2.ans b/src/test/feature/catalog/ans/int2.ans new file mode 100755 index 0000000..cf9dc07 --- /dev/null +++ b/src/test/feature/catalog/ans/int2.ans @@ -0,0 +1,236 @@ +-- +-- INT2 +-- NOTE: int2 operators never check for over/underflow! +-- Some of these answers are consequently numerically incorrect. +-- +CREATE TABLE INT2_TBL(f1 int2); +CREATE TABLE +INSERT INTO INT2_TBL(f1) VALUES ('0 '); +INSERT 0 1 +INSERT INTO INT2_TBL(f1) VALUES (' 1234 '); +INSERT 0 1 +INSERT INTO INT2_TBL(f1) VALUES (' -1234'); +INSERT 0 1 +INSERT INTO INT2_TBL(f1) VALUES ('34.5'); +psql:/tmp/TestType_int2.sql:18: ERROR: invalid input syntax for integer: "34.5" +-- largest and smallest values +INSERT INTO INT2_TBL(f1) VALUES ('32767'); +INSERT 0 1 +INSERT INTO INT2_TBL(f1) VALUES ('-32767'); +INSERT 0 1 +-- bad input values -- should give errors +INSERT INTO INT2_TBL(f1) VALUES ('100000'); +psql:/tmp/TestType_int2.sql:26: ERROR: value "100000" is out of range for type smallint +INSERT INTO INT2_TBL(f1) VALUES ('asdf'); +psql:/tmp/TestType_int2.sql:27: ERROR: invalid input syntax for integer: "asdf" +INSERT INTO INT2_TBL(f1) VALUES (' '); +psql:/tmp/TestType_int2.sql:28: ERROR: invalid input syntax for integer: " " +INSERT INTO INT2_TBL(f1) VALUES ('- 1234'); +psql:/tmp/TestType_int2.sql:29: ERROR: invalid input syntax for integer: "- 1234" +INSERT INTO INT2_TBL(f1) VALUES ('4 444'); +psql:/tmp/TestType_int2.sql:30: ERROR: invalid input syntax for integer: "4 444" +INSERT INTO INT2_TBL(f1) VALUES ('123 dt'); +psql:/tmp/TestType_int2.sql:31: ERROR: invalid input syntax for integer: "123 dt" +INSERT INTO INT2_TBL(f1) VALUES (''); +psql:/tmp/TestType_int2.sql:32: ERROR: invalid input syntax for integer: "" +SELECT '' AS five, * FROM INT2_TBL order by f1; + five | f1 +------+-------- + | -32767 + | -1234 + | 0 + | 1234 + | 32767 +(5 rows) + +SELECT '' AS four, i.* FROM INT2_TBL i WHERE i.f1 <> int2 '0' order by f1; + four | f1 +------+-------- + | -32767 + | -1234 + | 1234 + | 32767 +(4 rows) + +SELECT '' AS four, i.* FROM INT2_TBL i WHERE i.f1 <> int4 '0' order by f1; + four | f1 +------+-------- + | -32767 + | -1234 + | 1234 + | 32767 +(4 rows) + +SELECT '' AS one, i.* FROM INT2_TBL i WHERE i.f1 = int2 '0' order by f1; + one | f1 +-----+---- + | 0 +(1 row) + +SELECT '' AS one, i.* FROM INT2_TBL i WHERE i.f1 = int4 '0' order by f1; + one | f1 +-----+---- + | 0 +(1 row) + +SELECT '' AS two, i.* FROM INT2_TBL i WHERE i.f1 < int2 '0' order by f1; + two | f1 +-----+-------- + | -32767 + | -1234 +(2 rows) + +SELECT '' AS two, i.* FROM INT2_TBL i WHERE i.f1 < int4 '0' order by f1; + two | f1 +-----+-------- + | -32767 + | -1234 +(2 rows) + +SELECT '' AS three, i.* FROM INT2_TBL i WHERE i.f1 <= int2 '0' order by f1; + three | f1 +-------+-------- + | -32767 + | -1234 + | 0 +(3 rows) + +SELECT '' AS three, i.* FROM INT2_TBL i WHERE i.f1 <= int4 '0' order by f1; + three | f1 +-------+-------- + | -32767 + | -1234 + | 0 +(3 rows) + +SELECT '' AS two, i.* FROM INT2_TBL i WHERE i.f1 > int2 '0' order by f1; + two | f1 +-----+------- + | 1234 + | 32767 +(2 rows) + +SELECT '' AS two, i.* FROM INT2_TBL i WHERE i.f1 > int4 '0' order by f1; + two | f1 +-----+------- + | 1234 + | 32767 +(2 rows) + +SELECT '' AS three, i.* FROM INT2_TBL i WHERE i.f1 >= int2 '0' order by f1; + three | f1 +-------+------- + | 0 + | 1234 + | 32767 +(3 rows) + +SELECT '' AS three, i.* FROM INT2_TBL i WHERE i.f1 >= int4 '0' order by f1; + three | f1 +-------+------- + | 0 + | 1234 + | 32767 +(3 rows) + +-- positive odds +SELECT '' AS one, i.* FROM INT2_TBL i WHERE (i.f1 % int2 '2') = int2 '1' order by f1; + one | f1 +-----+------- + | 32767 +(1 row) + +-- any evens +SELECT '' AS three, i.* FROM INT2_TBL i WHERE (i.f1 % int4 '2') = int2 '0' order by f1; + three | f1 +-------+------- + | -1234 + | 0 + | 1234 +(3 rows) + +SELECT '' AS five, i.f1, i.f1 * int2 '2' AS x FROM INT2_TBL i order by f1; +psql:/tmp/TestType_int2.sql:67: ERROR: smallint out of range +SELECT '' AS five, i.f1, i.f1 * int2 '2' AS x FROM INT2_TBL i +WHERE abs(f1) < 16384 order by f1; + five | f1 | x +------+-------+------- + | -1234 | -2468 + | 0 | 0 + | 1234 | 2468 +(3 rows) + +SELECT '' AS five, i.f1, i.f1 * int4 '2' AS x FROM INT2_TBL i order by f1; + five | f1 | x +------+--------+-------- + | -32767 | -65534 + | -1234 | -2468 + | 0 | 0 + | 1234 | 2468 + | 32767 | 65534 +(5 rows) + +SELECT '' AS five, i.f1, i.f1 + int2 '2' AS x FROM INT2_TBL i order by f1; +psql:/tmp/TestType_int2.sql:74: ERROR: smallint out of range +SELECT '' AS five, i.f1, i.f1 + int2 '2' AS x FROM INT2_TBL i +WHERE f1 < 32766 order by f1; + five | f1 | x +------+--------+-------- + | -32767 | -32765 + | -1234 | -1232 + | 0 | 2 + | 1234 | 1236 +(4 rows) + +SELECT '' AS five, i.f1, i.f1 + int4 '2' AS x FROM INT2_TBL i order by f1; + five | f1 | x +------+--------+-------- + | -32767 | -32765 + | -1234 | -1232 + | 0 | 2 + | 1234 | 1236 + | 32767 | 32769 +(5 rows) + +SELECT '' AS five, i.f1, i.f1 - int2 '2' AS x FROM INT2_TBL i order by f1; +psql:/tmp/TestType_int2.sql:81: ERROR: smallint out of range +SELECT '' AS five, i.f1, i.f1 - int2 '2' AS x FROM INT2_TBL i +WHERE f1 > -32767 order by f1; + five | f1 | x +------+-------+------- + | -1234 | -1236 + | 0 | -2 + | 1234 | 1232 + | 32767 | 32765 +(4 rows) + +SELECT '' AS five, i.f1, i.f1 - int4 '2' AS x FROM INT2_TBL i order by f1; + five | f1 | x +------+--------+-------- + | -32767 | -32769 + | -1234 | -1236 + | 0 | -2 + | 1234 | 1232 + | 32767 | 32765 +(5 rows) + +SELECT '' AS five, i.f1, i.f1 / int2 '2' AS x FROM INT2_TBL i order by f1; + five | f1 | x +------+--------+-------- + | -32767 | -16383 + | -1234 | -617 + | 0 | 0 + | 1234 | 617 + | 32767 | 16383 +(5 rows) + +SELECT '' AS five, i.f1, i.f1 / int4 '2' AS x FROM INT2_TBL i order by f1; + five | f1 | x +------+--------+-------- + | -32767 | -16383 + | -1234 | -617 + | 0 | 0 + | 1234 | 617 + | 32767 | 16383 +(5 rows) + http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/5506a228/src/test/feature/catalog/ans/int4.ans ---------------------------------------------------------------------- diff --git a/src/test/feature/catalog/ans/int4.ans b/src/test/feature/catalog/ans/int4.ans new file mode 100755 index 0000000..fde23db --- /dev/null +++ b/src/test/feature/catalog/ans/int4.ans @@ -0,0 +1,323 @@ +-- +-- INT4 +-- WARNING: int4 operators never check for over/underflow! +-- Some of these answers are consequently numerically incorrect. +-- +CREATE TABLE INT4_TBL(f1 int4); +CREATE TABLE +INSERT INTO INT4_TBL(f1) VALUES (' 0 '); +INSERT 0 1 +INSERT INTO INT4_TBL(f1) VALUES ('123456 '); +INSERT 0 1 +INSERT INTO INT4_TBL(f1) VALUES (' -123456'); +INSERT 0 1 +INSERT INTO INT4_TBL(f1) VALUES ('34.5'); +psql:/tmp/TestType_int4.sql:18: ERROR: invalid input syntax for integer: "34.5" +-- largest and smallest values +INSERT INTO INT4_TBL(f1) VALUES ('2147483647'); +INSERT 0 1 +INSERT INTO INT4_TBL(f1) VALUES ('-2147483647'); +INSERT 0 1 +-- bad input values -- should give errors +INSERT INTO INT4_TBL(f1) VALUES ('1000000000000'); +psql:/tmp/TestType_int4.sql:26: ERROR: value "1000000000000" is out of range for type integer +INSERT INTO INT4_TBL(f1) VALUES ('asdf'); +psql:/tmp/TestType_int4.sql:27: ERROR: invalid input syntax for integer: "asdf" +INSERT INTO INT4_TBL(f1) VALUES (' '); +psql:/tmp/TestType_int4.sql:28: ERROR: invalid input syntax for integer: " " +INSERT INTO INT4_TBL(f1) VALUES (' asdf '); +psql:/tmp/TestType_int4.sql:29: ERROR: invalid input syntax for integer: " asdf " +INSERT INTO INT4_TBL(f1) VALUES ('- 1234'); +psql:/tmp/TestType_int4.sql:30: ERROR: invalid input syntax for integer: "- 1234" +INSERT INTO INT4_TBL(f1) VALUES ('123 5'); +psql:/tmp/TestType_int4.sql:31: ERROR: invalid input syntax for integer: "123 5" +INSERT INTO INT4_TBL(f1) VALUES (''); +psql:/tmp/TestType_int4.sql:32: ERROR: invalid input syntax for integer: "" +SELECT '' AS five, * FROM INT4_TBL order by f1; + five | f1 +------+------------- + | -2147483647 + | -123456 + | 0 + | 123456 + | 2147483647 +(5 rows) + +SELECT '' AS four, i.* FROM INT4_TBL i WHERE i.f1 <> int2 '0' order by f1; + four | f1 +------+------------- + | -2147483647 + | -123456 + | 123456 + | 2147483647 +(4 rows) + +SELECT '' AS four, i.* FROM INT4_TBL i WHERE i.f1 <> int4 '0' order by f1; + four | f1 +------+------------- + | -2147483647 + | -123456 + | 123456 + | 2147483647 +(4 rows) + +SELECT '' AS one, i.* FROM INT4_TBL i WHERE i.f1 = int2 '0' order by f1; + one | f1 +-----+---- + | 0 +(1 row) + +SELECT '' AS one, i.* FROM INT4_TBL i WHERE i.f1 = int4 '0' order by f1; + one | f1 +-----+---- + | 0 +(1 row) + +SELECT '' AS two, i.* FROM INT4_TBL i WHERE i.f1 < int2 '0' order by f1; + two | f1 +-----+------------- + | -2147483647 + | -123456 +(2 rows) + +SELECT '' AS two, i.* FROM INT4_TBL i WHERE i.f1 < int4 '0' order by f1; + two | f1 +-----+------------- + | -2147483647 + | -123456 +(2 rows) + +SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 <= int2 '0' order by f1; + three | f1 +-------+------------- + | -2147483647 + | -123456 + | 0 +(3 rows) + +SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 <= int4 '0' order by f1; + three | f1 +-------+------------- + | -2147483647 + | -123456 + | 0 +(3 rows) + +SELECT '' AS two, i.* FROM INT4_TBL i WHERE i.f1 > int2 '0' order by f1; + two | f1 +-----+------------ + | 123456 + | 2147483647 +(2 rows) + +SELECT '' AS two, i.* FROM INT4_TBL i WHERE i.f1 > int4 '0' order by f1; + two | f1 +-----+------------ + | 123456 + | 2147483647 +(2 rows) + +SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 >= int2 '0' order by f1; + three | f1 +-------+------------ + | 0 + | 123456 + | 2147483647 +(3 rows) + +SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 >= int4 '0' order by f1; + three | f1 +-------+------------ + | 0 + | 123456 + | 2147483647 +(3 rows) + +-- positive odds +SELECT '' AS one, i.* FROM INT4_TBL i WHERE (i.f1 % int2 '2') = int2 '1' order by f1; + one | f1 +-----+------------ + | 2147483647 +(1 row) + +-- any evens +SELECT '' AS three, i.* FROM INT4_TBL i WHERE (i.f1 % int4 '2') = int2 '0' order by f1; + three | f1 +-------+--------- + | -123456 + | 0 + | 123456 +(3 rows) + +SELECT '' AS five, i.f1, i.f1 * int2 '2' AS x FROM INT4_TBL i order by f1; +psql:/tmp/TestType_int4.sql:67: ERROR: integer out of range +SELECT '' AS five, i.f1, i.f1 * int2 '2' AS x FROM INT4_TBL i +WHERE abs(f1) < 1073741824 order by f1; + five | f1 | x +------+---------+--------- + | -123456 | -246912 + | 0 | 0 + | 123456 | 246912 +(3 rows) + +SELECT '' AS five, i.f1, i.f1 * int4 '2' AS x FROM INT4_TBL i order by f1; +psql:/tmp/TestType_int4.sql:72: ERROR: integer out of range +SELECT '' AS five, i.f1, i.f1 * int4 '2' AS x FROM INT4_TBL i +WHERE abs(f1) < 1073741824 order by f1; + five | f1 | x +------+---------+--------- + | -123456 | -246912 + | 0 | 0 + | 123456 | 246912 +(3 rows) + +SELECT '' AS five, i.f1, i.f1 + int2 '2' AS x FROM INT4_TBL i order by f1; +psql:/tmp/TestType_int4.sql:77: ERROR: integer out of range +SELECT '' AS five, i.f1, i.f1 + int2 '2' AS x FROM INT4_TBL i +WHERE f1 < 2147483646 order by f1; + five | f1 | x +------+-------------+------------- + | -2147483647 | -2147483645 + | -123456 | -123454 + | 0 | 2 + | 123456 | 123458 +(4 rows) + +SELECT '' AS five, i.f1, i.f1 + int4 '2' AS x FROM INT4_TBL i order by f1; +psql:/tmp/TestType_int4.sql:82: ERROR: integer out of range +SELECT '' AS five, i.f1, i.f1 + int4 '2' AS x FROM INT4_TBL i +WHERE f1 < 2147483646 order by f1; + five | f1 | x +------+-------------+------------- + | -2147483647 | -2147483645 + | -123456 | -123454 + | 0 | 2 + | 123456 | 123458 +(4 rows) + +SELECT '' AS five, i.f1, i.f1 - int2 '2' AS x FROM INT4_TBL i order by f1; +psql:/tmp/TestType_int4.sql:87: ERROR: integer out of range +SELECT '' AS five, i.f1, i.f1 - int2 '2' AS x FROM INT4_TBL i +WHERE f1 > -2147483647 order by f1; + five | f1 | x +------+------------+------------ + | -123456 | -123458 + | 0 | -2 + | 123456 | 123454 + | 2147483647 | 2147483645 +(4 rows) + +SELECT '' AS five, i.f1, i.f1 - int4 '2' AS x FROM INT4_TBL i order by f1; +psql:/tmp/TestType_int4.sql:92: ERROR: integer out of range +SELECT '' AS five, i.f1, i.f1 - int4 '2' AS x FROM INT4_TBL i +WHERE f1 > -2147483647 order by f1; + five | f1 | x +------+------------+------------ + | -123456 | -123458 + | 0 | -2 + | 123456 | 123454 + | 2147483647 | 2147483645 +(4 rows) + +SELECT '' AS five, i.f1, i.f1 / int2 '2' AS x FROM INT4_TBL i order by f1; + five | f1 | x +------+-------------+------------- + | -2147483647 | -1073741823 + | -123456 | -61728 + | 0 | 0 + | 123456 | 61728 + | 2147483647 | 1073741823 +(5 rows) + +SELECT '' AS five, i.f1, i.f1 / int4 '2' AS x FROM INT4_TBL i order by f1; + five | f1 | x +------+-------------+------------- + | -2147483647 | -1073741823 + | -123456 | -61728 + | 0 | 0 + | 123456 | 61728 + | 2147483647 | 1073741823 +(5 rows) + +-- +-- more complex expressions +-- +-- variations on unary minus parsing +SELECT -2+3 AS one; + one +----- + 1 +(1 row) + +SELECT 4-2 AS two; + two +----- + 2 +(1 row) + +SELECT 2- -1 AS three; + three +------- + 3 +(1 row) + +SELECT 2 - -2 AS four; + four +------ + 4 +(1 row) + +SELECT int2 '2' * int2 '2' = int2 '16' / int2 '4' AS true; + true +------ + t +(1 row) + +SELECT int4 '2' * int2 '2' = int2 '16' / int4 '4' AS true; + true +------ + t +(1 row) + +SELECT int2 '2' * int4 '2' = int4 '16' / int2 '4' AS true; + true +------ + t +(1 row) + +SELECT int4 '1000' < int4 '999' AS false; + false +------- + f +(1 row) + +SELECT 4! AS twenty_four; + twenty_four +------------- + 24 +(1 row) + +SELECT !!3 AS six; + six +----- + 6 +(1 row) + +SELECT 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 AS ten; + ten +----- + 10 +(1 row) + +SELECT 2 + 2 / 2 AS three; + three +------- + 3 +(1 row) + +SELECT (2 + 2) / 2 AS two; + two +----- + 2 +(1 row) + http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/5506a228/src/test/feature/catalog/ans/int8.ans ---------------------------------------------------------------------- diff --git a/src/test/feature/catalog/ans/int8.ans b/src/test/feature/catalog/ans/int8.ans new file mode 100755 index 0000000..5f1e2f5 --- /dev/null +++ b/src/test/feature/catalog/ans/int8.ans @@ -0,0 +1,329 @@ +-- +-- INT8 +-- Test int8 64-bit integers. +-- +CREATE TABLE INT8_TBL(q1 int8, q2 int8); +CREATE TABLE +INSERT INTO INT8_TBL VALUES(' 123 ',' 456'); +INSERT 0 1 +INSERT INTO INT8_TBL VALUES('123 ','4567890123456789'); +INSERT 0 1 +INSERT INTO INT8_TBL VALUES('4567890123456789','123'); +INSERT 0 1 +INSERT INTO INT8_TBL VALUES('4567890123456789','4567890123456789'); +INSERT 0 1 +INSERT INTO INT8_TBL VALUES('4567890123456789','-4567890123456789'); +INSERT 0 1 +-- bad inputs +INSERT INTO INT8_TBL(q1) VALUES (' '); +psql:/tmp/TestType_int8.sql:17: ERROR: invalid input syntax for integer: " " +INSERT INTO INT8_TBL(q1) VALUES ('xxx'); +psql:/tmp/TestType_int8.sql:18: ERROR: invalid input syntax for integer: "xxx" +INSERT INTO INT8_TBL(q1) VALUES ('3908203590239580293850293850329485'); +psql:/tmp/TestType_int8.sql:19: ERROR: value "3908203590239580293850293850329485" is out of range for type bigint +INSERT INTO INT8_TBL(q1) VALUES ('-1204982019841029840928340329840934'); +psql:/tmp/TestType_int8.sql:20: ERROR: value "-1204982019841029840928340329840934" is out of range for type bigint +INSERT INTO INT8_TBL(q1) VALUES ('- 123'); +psql:/tmp/TestType_int8.sql:21: ERROR: invalid input syntax for integer: "- 123" +INSERT INTO INT8_TBL(q1) VALUES (' 345 5'); +psql:/tmp/TestType_int8.sql:22: ERROR: invalid input syntax for integer: " 345 5" +INSERT INTO INT8_TBL(q1) VALUES (''); +psql:/tmp/TestType_int8.sql:23: ERROR: invalid input syntax for integer: "" +SELECT * FROM INT8_TBL ; + q1 | q2 +------------------+------------------- + 123 | 456 + 123 | 4567890123456789 + 4567890123456789 | 123 + 4567890123456789 | 4567890123456789 + 4567890123456789 | -4567890123456789 +(5 rows) + +SELECT '' AS five, q1 AS plus, -q1 AS minus FROM INT8_TBL ; + five | plus | minus +------+------------------+------------------- + | 123 | -123 + | 123 | -123 + | 4567890123456789 | -4567890123456789 + | 4567890123456789 | -4567890123456789 + | 4567890123456789 | -4567890123456789 +(5 rows) + +SELECT '' AS five, q1, q2, q1 + q2 AS plus FROM INT8_TBL ; + five | q1 | q2 | plus +------+------------------+-------------------+------------------ + | 123 | 456 | 579 + | 123 | 4567890123456789 | 4567890123456912 + | 4567890123456789 | 123 | 4567890123456912 + | 4567890123456789 | 4567890123456789 | 9135780246913578 + | 4567890123456789 | -4567890123456789 | 0 +(5 rows) + +SELECT '' AS five, q1, q2, q1 - q2 AS minus FROM INT8_TBL ; + five | q1 | q2 | minus +------+------------------+-------------------+------------------- + | 123 | 456 | -333 + | 123 | 4567890123456789 | -4567890123456666 + | 4567890123456789 | 123 | 4567890123456666 + | 4567890123456789 | 4567890123456789 | 0 + | 4567890123456789 | -4567890123456789 | 9135780246913578 +(5 rows) + +SELECT '' AS three, q1, q2, q1 * q2 AS multiply FROM INT8_TBL ; +psql:/tmp/TestType_int8.sql:31: ERROR: bigint out of range +SELECT '' AS three, q1, q2, q1 * q2 AS multiply FROM INT8_TBL + WHERE q1 < 1000 or (q2 > 0 and q2 < 1000) ; + three | q1 | q2 | multiply +-------+------------------+------------------+-------------------- + | 123 | 456 | 56088 + | 123 | 4567890123456789 | 561850485185185047 + | 4567890123456789 | 123 | 561850485185185047 +(3 rows) + +SELECT '' AS five, q1, q2, q1 / q2 AS divide FROM INT8_TBL ; + five | q1 | q2 | divide +------+------------------+-------------------+---------------- + | 123 | 456 | 0 + | 123 | 4567890123456789 | 0 + | 4567890123456789 | 123 | 37137318076884 + | 4567890123456789 | 4567890123456789 | 1 + | 4567890123456789 | -4567890123456789 | -1 +(5 rows) + +SELECT '' AS five, q1, float8(q1) FROM INT8_TBL ; + five | q1 | float8 +------+------------------+---------------------- + | 123 | 123 + | 123 | 123 + | 4567890123456789 | 4.56789012345679e+15 + | 4567890123456789 | 4.56789012345679e+15 + | 4567890123456789 | 4.56789012345679e+15 +(5 rows) + +SELECT '' AS five, q2, float8(q2) FROM INT8_TBL ; + five | q2 | float8 +------+-------------------+----------------------- + | 456 | 456 + | 4567890123456789 | 4.56789012345679e+15 + | 123 | 123 + | 4567890123456789 | 4.56789012345679e+15 + | -4567890123456789 | -4.56789012345679e+15 +(5 rows) + +SELECT '' AS five, 2 * q1 AS "twice int4" FROM INT8_TBL ; + five | twice int4 +------+------------------ + | 246 + | 246 + | 9135780246913578 + | 9135780246913578 + | 9135780246913578 +(5 rows) + +SELECT '' AS five, q1 * 2 AS "twice int4" FROM INT8_TBL ; + five | twice int4 +------+------------------ + | 246 + | 246 + | 9135780246913578 + | 9135780246913578 + | 9135780246913578 +(5 rows) + +-- TO_CHAR() +-- +SELECT '' AS to_char_1, to_char(q1, '9G999G999G999G999G999'), to_char(q2, '9,999,999,999,999,999') + FROM INT8_TBL ; + to_char_1 | to_char | to_char +-----------+------------------------+------------------------ + | 123 | 456 + | 123 | 4,567,890,123,456,789 + | 4,567,890,123,456,789 | 123 + | 4,567,890,123,456,789 | 4,567,890,123,456,789 + | 4,567,890,123,456,789 | -4,567,890,123,456,789 +(5 rows) + +SELECT '' AS to_char_2, to_char(q1, '9G999G999G999G999G999D999G999'), to_char(q2, '9,999,999,999,999,999.999,999') + FROM INT8_TBL ; + to_char_2 | to_char | to_char +-----------+--------------------------------+-------------------------------- + | 123.000,000 | 456.000,000 + | 123.000,000 | 4,567,890,123,456,789.000,000 + | 4,567,890,123,456,789.000,000 | 123.000,000 + | 4,567,890,123,456,789.000,000 | 4,567,890,123,456,789.000,000 + | 4,567,890,123,456,789.000,000 | -4,567,890,123,456,789.000,000 +(5 rows) + +SELECT '' AS to_char_3, to_char( (q1 * -1), '9999999999999999PR'), to_char( (q2 * -1), '9999999999999999.999PR') + FROM INT8_TBL ; + to_char_3 | to_char | to_char +-----------+--------------------+------------------------ + | <123> | <456.000> + | <123> | <4567890123456789.000> + | <4567890123456789> | <123.000> + | <4567890123456789> | <4567890123456789.000> + | <4567890123456789> | 4567890123456789.000 +(5 rows) + +SELECT '' AS to_char_4, to_char( (q1 * -1), '9999999999999999S'), to_char( (q2 * -1), 'S9999999999999999') + FROM INT8_TBL ; + to_char_4 | to_char | to_char +-----------+-------------------+------------------- + | 123- | -456 + | 123- | -4567890123456789 + | 4567890123456789- | -123 + | 4567890123456789- | -4567890123456789 + | 4567890123456789- | +4567890123456789 +(5 rows) + +SELECT '' AS to_char_5, to_char(q2, 'MI9999999999999999') FROM INT8_TBL ; + to_char_5 | to_char +-----------+------------------- + | 456 + | 4567890123456789 + | 123 + | 4567890123456789 + | -4567890123456789 +(5 rows) + +SELECT '' AS to_char_6, to_char(q2, 'FMS9999999999999999') FROM INT8_TBL ; + to_char_6 | to_char +-----------+------------------- + | +456 + | +4567890123456789 + | +123 + | +4567890123456789 + | -4567890123456789 +(5 rows) + +SELECT '' AS to_char_7, to_char(q2, 'FM9999999999999999THPR') FROM INT8_TBL ; + to_char_7 | to_char +-----------+-------------------- + | 456TH + | 4567890123456789TH + | 123RD + | 4567890123456789TH + | <4567890123456789> +(5 rows) + +SELECT '' AS to_char_8, to_char(q2, 'SG9999999999999999th') FROM INT8_TBL ; + to_char_8 | to_char +-----------+--------------------- + | + 456th + | +4567890123456789th + | + 123rd + | +4567890123456789th + | -4567890123456789 +(5 rows) + +SELECT '' AS to_char_9, to_char(q2, '0999999999999999') FROM INT8_TBL ; + to_char_9 | to_char +-----------+------------------- + | 0000000000000456 + | 4567890123456789 + | 0000000000000123 + | 4567890123456789 + | -4567890123456789 +(5 rows) + +SELECT '' AS to_char_10, to_char(q2, 'S0999999999999999') FROM INT8_TBL ; + to_char_10 | to_char +------------+------------------- + | +0000000000000456 + | +4567890123456789 + | +0000000000000123 + | +4567890123456789 + | -4567890123456789 +(5 rows) + +SELECT '' AS to_char_11, to_char(q2, 'FM0999999999999999') FROM INT8_TBL ; + to_char_11 | to_char +------------+------------------- + | 0000000000000456 + | 4567890123456789 + | 0000000000000123 + | 4567890123456789 + | -4567890123456789 +(5 rows) + +SELECT '' AS to_char_12, to_char(q2, 'FM9999999999999999.000') FROM INT8_TBL ; + to_char_12 | to_char +------------+----------------------- + | 456.000 + | 4567890123456789.000 + | 123.000 + | 4567890123456789.000 + | -4567890123456789.000 +(5 rows) + +SELECT '' AS to_char_13, to_char(q2, 'L9999999999999999.000') FROM INT8_TBL ; + to_char_13 | to_char +------------+------------------------ + | 456.000 + | 4567890123456789.000 + | 123.000 + | 4567890123456789.000 + | -4567890123456789.000 +(5 rows) + +SELECT '' AS to_char_14, to_char(q2, 'FM9999999999999999.999') FROM INT8_TBL ; + to_char_14 | to_char +------------+-------------------- + | 456. + | 4567890123456789. + | 123. + | 4567890123456789. + | -4567890123456789. +(5 rows) + +SELECT '' AS to_char_15, to_char(q2, 'S 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 . 9 9 9') FROM INT8_TBL ; + to_char_15 | to_char +------------+------------------------------------------- + | +4 5 6 . 0 0 0 + | +4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 . 0 0 0 + | +1 2 3 . 0 0 0 + | +4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 . 0 0 0 + | -4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 . 0 0 0 +(5 rows) + +SELECT '' AS to_char_16, to_char(q2, E'99999 "text" 9999 "9999" 999 "\\"text between quote marks\\"" 9999') FROM INT8_TBL ; + to_char_16 | to_char +------------+----------------------------------------------------------- + | text 9999 "text between quote marks" 456 + | 45678 text 9012 9999 345 "text between quote marks" 6789 + | text 9999 "text between quote marks" 123 + | 45678 text 9012 9999 345 "text between quote marks" 6789 + | -45678 text 9012 9999 345 "text between quote marks" 6789 +(5 rows) + +SELECT '' AS to_char_17, to_char(q2, '999999SG9999999999') FROM INT8_TBL ; + to_char_17 | to_char +------------+------------------- + | + 456 + | 456789+0123456789 + | + 123 + | 456789+0123456789 + | 456789-0123456789 +(5 rows) + +-- check min/max values +select '-9223372036854775808'::int8; + int8 +---------------------- + -9223372036854775808 +(1 row) + +select '-9223372036854775809'::int8; +psql:/tmp/TestType_int8.sql:72: ERROR: value "-9223372036854775809" is out of range for type bigint +LINE 1: select '-9223372036854775809'::int8; + ^ +select '9223372036854775807'::int8; + int8 +--------------------- + 9223372036854775807 +(1 row) + +select '9223372036854775808'::int8; +psql:/tmp/TestType_int8.sql:74: ERROR: value "9223372036854775808" is out of range for type bigint +LINE 1: select '9223372036854775808'::int8; + ^
