You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@hawq.apache.org by rl...@apache.org on 2016/07/19 02:50:30 UTC
[05/14] incubator-hawq git commit: HAWQ-917. Refactor feature tests
for data type check with new googletest framework
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/f05c19ed
Tree: http://git-wip-us.apache.org/repos/asf/incubator-hawq/tree/f05c19ed
Diff: http://git-wip-us.apache.org/repos/asf/incubator-hawq/diff/f05c19ed
Branch: refs/heads/2.0.0.0-incubating
Commit: f05c19ed166850b5b7275cba2ce322ff380d3311
Parents: 2e1b093
Author: Paul Guo <pa...@gmail.com>
Authored: Tue Jul 12 14:12:19 2016 +0800
Committer: rlei <rl...@pivotal.io>
Committed: Tue Jul 19 10:49:42 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/f05c19ed/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/f05c19ed/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/f05c19ed/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/f05c19ed/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/f05c19ed/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/f05c19ed/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/f05c19ed/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/f05c19ed/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;
+ ^