hawq-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From r...@apache.org
Subject [05/14] incubator-hawq git commit: HAWQ-917. Refactor feature tests for data type check with new googletest framework
Date Tue, 19 Jul 2016 02:50:30 GMT
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 <paulguo@gmail.com>
Authored: Tue Jul 12 14:12:19 2016 +0800
Committer: rlei <rlei@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;
+               ^



Mime
View raw message