hawq-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From r...@apache.org
Subject [3/4] incubator-hawq git commit: HAWQ-917. Refactor feature tests for data type check with new googletest framework
Date Fri, 15 Jul 2016 09:36:48 GMT
http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/5506a228/src/test/feature/catalog/ans/money.ans
----------------------------------------------------------------------
diff --git a/src/test/feature/catalog/ans/money.ans b/src/test/feature/catalog/ans/money.ans
new file mode 100755
index 0000000..907366f
--- /dev/null
+++ b/src/test/feature/catalog/ans/money.ans
@@ -0,0 +1,168 @@
+--
+-- MONEY
+--
+CREATE TABLE MONEY_TBL (f1  money);
+CREATE TABLE
+INSERT INTO MONEY_TBL(f1) VALUES ('    0.0');
+INSERT 0 1
+INSERT INTO MONEY_TBL(f1) VALUES ('1004.30   ');
+INSERT 0 1
+INSERT INTO MONEY_TBL(f1) VALUES ('     -34.84    ');
+INSERT 0 1
+INSERT INTO MONEY_TBL(f1) VALUES ('123456789012345.67');
+INSERT 0 1
+-- test money over and under flow
+SELECT '12345678901234567890.12'::money = '-13639628150831692.60'::money as x;
+ x 
+---
+ t
+(1 row)
+
+SELECT '123.001'::money = '123'::money as x;
+ x 
+---
+ t
+(1 row)
+
+-- bad input
+INSERT INTO MONEY_TBL(f1) VALUES ('xyz');
+psql:/tmp/TestType_money.sql:20: ERROR:  invalid input syntax for type money: "xyz"
+INSERT INTO MONEY_TBL(f1) VALUES ('5.0.0');
+psql:/tmp/TestType_money.sql:21: ERROR:  invalid input syntax for type money: "5.0.0"
+INSERT INTO MONEY_TBL(f1) VALUES ('5 . 0');
+psql:/tmp/TestType_money.sql:22: ERROR:  invalid input syntax for type money: "5 . 0"
+INSERT INTO MONEY_TBL(f1) VALUES ('5.   0');
+psql:/tmp/TestType_money.sql:23: ERROR:  invalid input syntax for type money: "5.   0"
+INSERT INTO MONEY_TBL(f1) VALUES ('123            5');
+psql:/tmp/TestType_money.sql:24: ERROR:  invalid input syntax for type money: "123            5"
+-- queries
+SELECT '' AS five, * FROM MONEY_TBL ORDER BY 2;
+ five |           f1            
+------+-------------------------
+      |                 -$34.84
+      |                   $0.00
+      |               $1,004.30
+      | $123,456,789,012,345.67
+(4 rows)
+
+SELECT '' AS four, f.* FROM MONEY_TBL f WHERE f.f1 <> '1004.3' ORDER BY 2;
+ four |           f1            
+------+-------------------------
+      |                 -$34.84
+      |                   $0.00
+      | $123,456,789,012,345.67
+(3 rows)
+
+SELECT '' AS one, f.* FROM MONEY_TBL f WHERE f.f1 = '1004.3' ORDER BY 2;
+ one |    f1     
+-----+-----------
+     | $1,004.30
+(1 row)
+
+SELECT '' AS three, f.* FROM MONEY_TBL f WHERE '1004.3' > f.f1 ORDER BY 2;
+ three |   f1    
+-------+---------
+       | -$34.84
+       |   $0.00
+(2 rows)
+
+SELECT '' AS three, f.* FROM MONEY_TBL f WHERE  f.f1 < '1004.3' ORDER BY 2;
+ three |   f1    
+-------+---------
+       | -$34.84
+       |   $0.00
+(2 rows)
+
+SELECT '' AS four, f.* FROM MONEY_TBL f WHERE '1004.3' >= f.f1 ORDER BY 2;
+ four |    f1     
+------+-----------
+      |   -$34.84
+      |     $0.00
+      | $1,004.30
+(3 rows)
+
+SELECT '' AS four, f.* FROM MONEY_TBL f WHERE  f.f1 <= '1004.3' ORDER BY 2;
+ four |    f1     
+------+-----------
+      |   -$34.84
+      |     $0.00
+      | $1,004.30
+(3 rows)
+
+SELECT '' AS three, f.f1, f.f1 * '-10' AS x FROM MONEY_TBL f
+   WHERE f.f1 > '0.0' ORDER BY 2;
+ three |           f1            |             x              
+-------+-------------------------+----------------------------
+       |               $1,004.30 |                -$10,043.00
+       | $123,456,789,012,345.67 | -$1,234,567,890,123,456.80
+(2 rows)
+
+SELECT '' AS three, f.f1, f.f1 + '-10' AS x FROM MONEY_TBL f
+   WHERE f.f1 > '0.0' ORDER BY 2;
+ three |           f1            |            x            
+-------+-------------------------+-------------------------
+       |               $1,004.30 |                 $994.30
+       | $123,456,789,012,345.67 | $123,456,789,012,335.67
+(2 rows)
+
+SELECT '' AS three, f.f1, f.f1 / '-10' AS x FROM MONEY_TBL f
+   WHERE f.f1 > '0.0' ORDER BY 2;
+ three |           f1            |            x            
+-------+-------------------------+-------------------------
+       |               $1,004.30 |                -$100.43
+       | $123,456,789,012,345.67 | -$12,345,678,901,234.57
+(2 rows)
+
+SELECT '' AS three, f.f1, f.f1 - '-10' AS x FROM MONEY_TBL f
+   WHERE f.f1 > '0.0' ORDER BY 2;
+ three |           f1            |            x            
+-------+-------------------------+-------------------------
+       |               $1,004.30 |               $1,014.30
+       | $123,456,789,012,345.67 | $123,456,789,012,355.67
+(2 rows)
+
+SELECT SUM(f.f1) AS x FROM MONEY_TBL f;
+            x            
+-------------------------
+ $123,456,789,013,315.13
+(1 row)
+
+-- test divide by zero
+SELECT '' AS bad, f.f1 / '0.0' from MONEY_TBL f;
+psql:/tmp/TestType_money.sql:56: ERROR:  division by zero
+SELECT '' AS five, * FROM MONEY_TBL ORDER BY 2;
+ five |           f1            
+------+-------------------------
+      |                 -$34.84
+      |                   $0.00
+      |               $1,004.30
+      | $123,456,789,012,345.67
+(4 rows)
+
+-- parquet table
+CREATE TABLE MONEY_TBL_P (f1 money) with (appendonly=true, orientation=parquet);
+CREATE TABLE
+INSERT INTO MONEY_TBL_P(f1) VALUES ('    0.0');
+INSERT 0 1
+INSERT INTO MONEY_TBL_P(f1) VALUES ('1004.30   ');
+INSERT 0 1
+INSERT INTO MONEY_TBL_P(f1) VALUES ('     -34.84    ');
+INSERT 0 1
+INSERT INTO MONEY_TBL_P(f1) VALUES ('123456789012345.67');
+INSERT 0 1
+SELECT f1 FROM MONEY_TBL_P f
+   ORDER BY f1;
+           f1            
+-------------------------
+                 -$34.84
+                   $0.00
+               $1,004.30
+ $123,456,789,012,345.67
+(4 rows)
+
+SELECT sum(f1) AS x, min(f1) as y, max(f1) as z FROM MONEY_TBL_P AS f;
+            x            |    y    |            z            
+-------------------------+---------+-------------------------
+ $123,456,789,013,315.13 | -$34.84 | $123,456,789,012,345.67
+(1 row)
+

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/5506a228/src/test/feature/catalog/ans/name.ans
----------------------------------------------------------------------
diff --git a/src/test/feature/catalog/ans/name.ans b/src/test/feature/catalog/ans/name.ans
new file mode 100755
index 0000000..23e81d8
--- /dev/null
+++ b/src/test/feature/catalog/ans/name.ans
@@ -0,0 +1,135 @@
+--
+-- NAME
+-- all inputs are silently truncated at NAMEDATALEN-1 (63) characters
+--
+-- fixed-length by reference
+SELECT name 'name string' = name 'name string' AS "True";
+ True 
+------
+ t
+(1 row)
+
+SELECT name 'name string' = name 'name string ' AS "False";
+ False 
+-------
+ f
+(1 row)
+
+--
+--
+--
+CREATE TABLE NAME_TBL(f1 name);
+CREATE TABLE
+INSERT INTO NAME_TBL(f1) VALUES ('1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQR');
+INSERT 0 1
+INSERT INTO NAME_TBL(f1) VALUES ('1234567890abcdefghijklmnopqrstuvwxyz1234567890abcdefghijklmnopqr');
+INSERT 0 1
+INSERT INTO NAME_TBL(f1) VALUES ('asdfghjkl;');
+INSERT 0 1
+INSERT INTO NAME_TBL(f1) VALUES ('343f%2a');
+INSERT 0 1
+INSERT INTO NAME_TBL(f1) VALUES ('d34aaasdf');
+INSERT 0 1
+INSERT INTO NAME_TBL(f1) VALUES ('');
+INSERT 0 1
+INSERT INTO NAME_TBL(f1) VALUES ('1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ');
+INSERT 0 1
+SELECT '' AS seven, * FROM NAME_TBL order by f1;
+ seven |                               f1                                
+-------+-----------------------------------------------------------------
+       | 
+       | 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQ
+       | 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQ
+       | 1234567890abcdefghijklmnopqrstuvwxyz1234567890abcdefghijklmnopq
+       | 343f%2a
+       | asdfghjkl;
+       | d34aaasdf
+(7 rows)
+
+SELECT '' AS six, c.f1 FROM NAME_TBL c WHERE c.f1 <> '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQR' order by f1;
+ six |                               f1                                
+-----+-----------------------------------------------------------------
+     | 
+     | 1234567890abcdefghijklmnopqrstuvwxyz1234567890abcdefghijklmnopq
+     | 343f%2a
+     | asdfghjkl;
+     | d34aaasdf
+(5 rows)
+
+SELECT '' AS one, c.f1 FROM NAME_TBL c WHERE c.f1 = '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQR' order by f1;
+ one |                               f1                                
+-----+-----------------------------------------------------------------
+     | 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQ
+     | 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQ
+(2 rows)
+
+SELECT '' AS three, c.f1 FROM NAME_TBL c WHERE c.f1 < '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQR' order by f1;
+ three | f1 
+-------+----
+       | 
+(1 row)
+
+SELECT '' AS four, c.f1 FROM NAME_TBL c WHERE c.f1 <= '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQR' order by f1;
+ four |                               f1                                
+------+-----------------------------------------------------------------
+      | 
+      | 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQ
+      | 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQ
+(3 rows)
+
+SELECT '' AS three, c.f1 FROM NAME_TBL c WHERE c.f1 > '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQR' order by f1;
+ three |                               f1                                
+-------+-----------------------------------------------------------------
+       | 1234567890abcdefghijklmnopqrstuvwxyz1234567890abcdefghijklmnopq
+       | 343f%2a
+       | asdfghjkl;
+       | d34aaasdf
+(4 rows)
+
+SELECT '' AS four, c.f1 FROM NAME_TBL c WHERE c.f1 >= '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQR' order by f1;
+ four |                               f1                                
+------+-----------------------------------------------------------------
+      | 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQ
+      | 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQ
+      | 1234567890abcdefghijklmnopqrstuvwxyz1234567890abcdefghijklmnopq
+      | 343f%2a
+      | asdfghjkl;
+      | d34aaasdf
+(6 rows)
+
+SELECT '' AS seven, c.f1 FROM NAME_TBL c WHERE c.f1 ~ '.*' order by f1;
+ seven |                               f1                                
+-------+-----------------------------------------------------------------
+       | 
+       | 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQ
+       | 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQ
+       | 1234567890abcdefghijklmnopqrstuvwxyz1234567890abcdefghijklmnopq
+       | 343f%2a
+       | asdfghjkl;
+       | d34aaasdf
+(7 rows)
+
+SELECT '' AS zero, c.f1 FROM NAME_TBL c WHERE c.f1 !~ '.*' order by f1;
+ zero | f1 
+------+----
+(0 rows)
+
+SELECT '' AS three, c.f1 FROM NAME_TBL c WHERE c.f1 ~ '[0-9]' order by f1;
+ three |                               f1                                
+-------+-----------------------------------------------------------------
+       | 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQ
+       | 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQ
+       | 1234567890abcdefghijklmnopqrstuvwxyz1234567890abcdefghijklmnopq
+       | 343f%2a
+       | d34aaasdf
+(5 rows)
+
+SELECT '' AS two, c.f1 FROM NAME_TBL c WHERE c.f1 ~ '.*asdf.*' order by f1;
+ two |     f1     
+-----+------------
+     | asdfghjkl;
+     | d34aaasdf
+(2 rows)
+
+DROP TABLE NAME_TBL;
+DROP TABLE

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/5506a228/src/test/feature/catalog/ans/oid.ans
----------------------------------------------------------------------
diff --git a/src/test/feature/catalog/ans/oid.ans b/src/test/feature/catalog/ans/oid.ans
new file mode 100755
index 0000000..a8013ce
--- /dev/null
+++ b/src/test/feature/catalog/ans/oid.ans
@@ -0,0 +1,112 @@
+--
+-- OID
+--
+CREATE TABLE OID_TBL(f1 oid);
+CREATE TABLE
+INSERT INTO OID_TBL(f1) VALUES ('1234');
+INSERT 0 1
+INSERT INTO OID_TBL(f1) VALUES ('1235');
+INSERT 0 1
+INSERT INTO OID_TBL(f1) VALUES ('987');
+INSERT 0 1
+INSERT INTO OID_TBL(f1) VALUES ('-1040');
+INSERT 0 1
+INSERT INTO OID_TBL(f1) VALUES ('99999999');
+INSERT 0 1
+INSERT INTO OID_TBL(f1) VALUES ('5     ');
+INSERT 0 1
+INSERT INTO OID_TBL(f1) VALUES ('   10  ');
+INSERT 0 1
+-- leading/trailing hard tab is also allowed
+INSERT INTO OID_TBL(f1) VALUES ('	  15 	  ');
+INSERT 0 1
+-- bad inputs 
+INSERT INTO OID_TBL(f1) VALUES ('');
+psql:/tmp/TestType_oid.sql:21: ERROR:  invalid input syntax for type oid: ""
+INSERT INTO OID_TBL(f1) VALUES ('    ');
+psql:/tmp/TestType_oid.sql:22: ERROR:  invalid input syntax for type oid: "    "
+INSERT INTO OID_TBL(f1) VALUES ('asdfasd');
+psql:/tmp/TestType_oid.sql:23: ERROR:  invalid input syntax for type oid: "asdfasd"
+INSERT INTO OID_TBL(f1) VALUES ('99asdfasd');
+psql:/tmp/TestType_oid.sql:24: ERROR:  invalid input syntax for type oid: "99asdfasd"
+INSERT INTO OID_TBL(f1) VALUES ('5    d');
+psql:/tmp/TestType_oid.sql:25: ERROR:  invalid input syntax for type oid: "5    d"
+INSERT INTO OID_TBL(f1) VALUES ('    5d');
+psql:/tmp/TestType_oid.sql:26: ERROR:  invalid input syntax for type oid: "    5d"
+INSERT INTO OID_TBL(f1) VALUES ('5    5');
+psql:/tmp/TestType_oid.sql:27: ERROR:  invalid input syntax for type oid: "5    5"
+INSERT INTO OID_TBL(f1) VALUES (' - 500');
+psql:/tmp/TestType_oid.sql:28: ERROR:  invalid input syntax for type oid: " - 500"
+INSERT INTO OID_TBL(f1) VALUES ('32958209582039852935');
+psql:/tmp/TestType_oid.sql:29: ERROR:  value "32958209582039852935" is out of range for type oid
+INSERT INTO OID_TBL(f1) VALUES ('-23582358720398502385');
+psql:/tmp/TestType_oid.sql:30: ERROR:  value "-23582358720398502385" is out of range for type oid
+SELECT '' AS six, * FROM OID_TBL order by 1, 2;
+ six |     f1     
+-----+------------
+     |          5
+     |         10
+     |         15
+     |        987
+     |       1234
+     |       1235
+     |   99999999
+     | 4294966256
+(8 rows)
+
+SELECT '' AS one, o.* FROM OID_TBL o WHERE o.f1 = 1234 order by 1, 2;
+ one |  f1  
+-----+------
+     | 1234
+(1 row)
+
+SELECT '' AS five, o.* FROM OID_TBL o WHERE o.f1 <> '1234' order by 1,2;
+ five |     f1     
+------+------------
+      |          5
+      |         10
+      |         15
+      |        987
+      |       1235
+      |   99999999
+      | 4294966256
+(7 rows)
+
+SELECT '' AS three, o.* FROM OID_TBL o WHERE o.f1 <= '1234' order by 1,2;
+ three |  f1  
+-------+------
+       |    5
+       |   10
+       |   15
+       |  987
+       | 1234
+(5 rows)
+
+SELECT '' AS two, o.* FROM OID_TBL o WHERE o.f1 < '1234' order by 1,2;
+ two | f1  
+-----+-----
+     |   5
+     |  10
+     |  15
+     | 987
+(4 rows)
+
+SELECT '' AS four, o.* FROM OID_TBL o WHERE o.f1 >= '1234'  order by 1,2;
+ four |     f1     
+------+------------
+      |       1234
+      |       1235
+      |   99999999
+      | 4294966256
+(4 rows)
+
+SELECT '' AS three, o.* FROM OID_TBL o WHERE o.f1 > '1234' order by 1,2;
+ three |     f1     
+-------+------------
+       |       1235
+       |   99999999
+       | 4294966256
+(3 rows)
+
+DROP TABLE OID_TBL;
+DROP TABLE

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/5506a228/src/test/feature/catalog/ans/text.ans
----------------------------------------------------------------------
diff --git a/src/test/feature/catalog/ans/text.ans b/src/test/feature/catalog/ans/text.ans
new file mode 100755
index 0000000..9507957
--- /dev/null
+++ b/src/test/feature/catalog/ans/text.ans
@@ -0,0 +1,28 @@
+--
+-- TEXT
+--
+SELECT text 'this is a text string' = text 'this is a text string' AS true;
+ true 
+------
+ t
+(1 row)
+
+SELECT text 'this is a text string' = text 'this is a text strin' AS false;
+ false 
+-------
+ f
+(1 row)
+
+CREATE TABLE TEXT_TBL (f1 text);
+CREATE TABLE
+INSERT INTO TEXT_TBL VALUES ('doh!');
+INSERT 0 1
+INSERT INTO TEXT_TBL VALUES ('hi de ho neighbor');
+INSERT 0 1
+SELECT '' AS two, * FROM TEXT_TBL order by f1;
+ two |        f1         
+-----+-------------------
+     | doh!
+     | hi de ho neighbor
+(2 rows)
+

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/5506a228/src/test/feature/catalog/ans/time.ans
----------------------------------------------------------------------
diff --git a/src/test/feature/catalog/ans/time.ans b/src/test/feature/catalog/ans/time.ans
new file mode 100755
index 0000000..ddeea98
--- /dev/null
+++ b/src/test/feature/catalog/ans/time.ans
@@ -0,0 +1,97 @@
+--
+-- TIME
+--
+CREATE TABLE TIME_TBL (f1 time(2));
+CREATE TABLE
+INSERT INTO TIME_TBL VALUES ('00:00');
+INSERT 0 1
+INSERT INTO TIME_TBL VALUES ('01:00');
+INSERT 0 1
+-- as of 7.4, timezone spec should be accepted and ignored
+INSERT INTO TIME_TBL VALUES ('02:03 PST');
+INSERT 0 1
+INSERT INTO TIME_TBL VALUES ('11:59 EDT');
+INSERT 0 1
+INSERT INTO TIME_TBL VALUES ('12:00');
+INSERT 0 1
+INSERT INTO TIME_TBL VALUES ('12:01');
+INSERT 0 1
+INSERT INTO TIME_TBL VALUES ('23:59');
+INSERT 0 1
+INSERT INTO TIME_TBL VALUES ('11:59:59.99 PM');
+INSERT 0 1
+INSERT INTO TIME_TBL VALUES ('2003-03-07 15:36:39 America/New_York');
+INSERT 0 1
+INSERT INTO TIME_TBL VALUES ('2003-07-07 15:36:39 America/New_York');
+INSERT 0 1
+-- this should fail (the timezone offset is not known)
+INSERT INTO TIME_TBL VALUES ('15:36:39 America/New_York');
+psql:/tmp/TestType_time.sql:23: ERROR:  invalid input syntax for type time: "15:36:39 America/New_York"
+SELECT f1 AS "Time" FROM TIME_TBL ORDER BY 1;
+    Time     
+-------------
+ 00:00:00
+ 01:00:00
+ 02:03:00
+ 11:59:00
+ 12:00:00
+ 12:01:00
+ 15:36:39
+ 15:36:39
+ 23:59:00
+ 23:59:59.99
+(10 rows)
+
+SELECT f1 AS "Three" FROM TIME_TBL WHERE f1 < '05:06:07' ORDER BY 1;
+  Three   
+----------
+ 00:00:00
+ 01:00:00
+ 02:03:00
+(3 rows)
+
+SELECT f1 AS "Five" FROM TIME_TBL WHERE f1 > '05:06:07' ORDER BY 1;
+    Five     
+-------------
+ 11:59:00
+ 12:00:00
+ 12:01:00
+ 15:36:39
+ 15:36:39
+ 23:59:00
+ 23:59:59.99
+(7 rows)
+
+SELECT f1 AS "None" FROM TIME_TBL WHERE f1 < '00:00' ORDER BY 1;
+ None 
+------
+(0 rows)
+
+SELECT f1 AS "Eight" FROM TIME_TBL WHERE f1 >= '00:00' ORDER BY 1;
+    Eight    
+-------------
+ 00:00:00
+ 01:00:00
+ 02:03:00
+ 11:59:00
+ 12:00:00
+ 12:01:00
+ 15:36:39
+ 15:36:39
+ 23:59:00
+ 23:59:59.99
+(10 rows)
+
+--
+-- TIME simple math
+--
+-- We now make a distinction between time and intervals,
+-- and adding two times together makes no sense at all.
+-- Leave in one query to show that it is rejected,
+-- and do the rest of the testing in horology.sql
+-- where we do mixed-type arithmetic. - thomas 2000-12-02
+SELECT f1 + time '00:01' AS "Illegal" FROM TIME_TBL;
+psql:/tmp/TestType_time.sql:44: ERROR:  operator is not unique: time without time zone + time without time zone
+LINE 1: SELECT f1 + time '00:01' AS "Illegal" FROM TIME_TBL;
+                  ^
+HINT:  Could not choose a best candidate operator. You may need to add explicit type casts.

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/5506a228/src/test/feature/catalog/ans/type_sanity.ans
----------------------------------------------------------------------
diff --git a/src/test/feature/catalog/ans/type_sanity.ans b/src/test/feature/catalog/ans/type_sanity.ans
new file mode 100755
index 0000000..518e3cd
--- /dev/null
+++ b/src/test/feature/catalog/ans/type_sanity.ans
@@ -0,0 +1,282 @@
+--
+-- TYPE_SANITY
+-- Sanity checks for common errors in making type-related system tables:
+-- pg_type, pg_class, pg_attribute.
+--
+-- None of the SELECTs here should ever find any matching entries,
+-- so the expected output is easy to maintain ;-).
+-- A test failure indicates someone messed up an entry in the system tables.
+--
+-- NB: we assume the oidjoins test will have caught any dangling links,
+-- that is OID or REGPROC fields that are not zero and do not match some
+-- row in the linked-to table.  However, if we want to enforce that a link
+-- field can't be 0, we have to check it here.
+-- **************** pg_type ****************
+-- Look for illegal values in pg_type fields.
+SELECT p1.oid, p1.typname
+FROM pg_type as p1
+WHERE p1.typnamespace = 0 OR
+    (p1.typlen <= 0 AND p1.typlen != -1 AND p1.typlen != -2) OR
+    (p1.typtype not in ('b', 'c', 'd', 'p')) OR
+    NOT p1.typisdefined OR
+    (p1.typalign not in ('c', 's', 'i', 'd')) OR
+    (p1.typstorage not in ('p', 'x', 'e', 'm'));
+ oid | typname 
+-----+---------
+(0 rows)
+
+-- Look for "pass by value" types that can't be passed by value.
+SELECT p1.oid, p1.typname
+FROM pg_type as p1
+WHERE p1.typbyval AND
+    (p1.typlen != 1 OR p1.typalign != 'c') AND
+    (p1.typlen != 2 OR p1.typalign != 's') AND
+    (p1.typlen != 4 OR p1.typalign != 'i') AND
+	(p1.typlen != 8 OR p1.typalign != 'd') ;
+ oid | typname 
+-----+---------
+(0 rows)
+
+-- Look for "toastable" types that aren't varlena.
+SELECT p1.oid, p1.typname
+FROM pg_type as p1
+WHERE p1.typstorage != 'p' AND
+    (p1.typbyval OR p1.typlen != -1);
+ oid | typname 
+-----+---------
+(0 rows)
+
+-- Look for complex types that do not have a typrelid entry,
+-- or basic types that do.
+SELECT p1.oid, p1.typname
+FROM pg_type as p1
+WHERE (p1.typtype = 'c' AND p1.typrelid = 0) OR
+    (p1.typtype != 'c' AND p1.typrelid != 0);
+ oid | typname 
+-----+---------
+(0 rows)
+
+-- Look for basic types that don't have an array type.
+-- NOTE: as of 8.0, this check finds smgr and unknown.
+SELECT p1.oid, p1.typname
+FROM pg_type as p1
+WHERE p1.typtype in ('b') AND p1.typname NOT LIKE E'\\_%' AND NOT EXISTS
+    (SELECT 1 FROM pg_type as p2
+     WHERE p2.typname = ('_' || p1.typname)::name AND
+           p2.typelem = p1.oid);
+ oid | typname 
+-----+---------
+ 210 | smgr
+ 705 | unknown
+(2 rows)
+
+-- Text conversion routines must be provided.
+SELECT p1.oid, p1.typname
+FROM pg_type as p1
+WHERE (p1.typinput = 0 OR p1.typoutput = 0);
+ oid | typname 
+-----+---------
+(0 rows)
+
+-- Check for bogus typinput routines
+SELECT p1.oid, p1.typname, p2.oid, p2.proname
+FROM pg_type AS p1, pg_proc AS p2
+WHERE p1.typinput = p2.oid AND p1.typtype in ('b', 'p') AND NOT
+    ((p2.pronargs = 1 AND p2.proargtypes[0] = 'cstring'::regtype) OR
+     (p2.pronargs = 3 AND p2.proargtypes[0] = 'cstring'::regtype AND
+      p2.proargtypes[1] = 'oid'::regtype AND
+      p2.proargtypes[2] = 'int4'::regtype));
+ oid | typname | oid | proname 
+-----+---------+-----+---------
+(0 rows)
+
+-- As of 8.0, this check finds refcursor, which is borrowing
+-- other types' I/O routines
+SELECT p1.oid, p1.typname, p2.oid, p2.proname
+FROM pg_type AS p1, pg_proc AS p2
+WHERE p1.typinput = p2.oid AND p1.typtype in ('b', 'p') AND NOT
+    (p1.typelem != 0 AND p1.typlen < 0) AND NOT
+    (p2.prorettype = p1.oid AND NOT p2.proretset)
+ORDER BY 1;
+ oid  |  typname  | oid | proname 
+------+-----------+-----+---------
+ 1790 | refcursor |  46 | textin
+(1 row)
+
+-- Varlena array types will point to array_in
+-- Exception as of 8.1: int2vector and oidvector have their own I/O routines
+SELECT p1.oid, p1.typname, p2.oid, p2.proname
+FROM pg_type AS p1, pg_proc AS p2
+WHERE p1.typinput = p2.oid AND p1.typtype in ('b', 'p') AND
+    (p1.typelem != 0 AND p1.typlen < 0) AND NOT
+    (p2.oid = 'array_in'::regproc)
+ORDER BY 1;
+ oid |  typname   | oid |   proname    
+-----+------------+-----+--------------
+  22 | int2vector |  40 | int2vectorin
+  30 | oidvector  |  54 | oidvectorin
+(2 rows)
+
+-- Check for bogus typoutput routines
+-- As of 8.0, this check finds refcursor, which is borrowing
+-- other types' I/O routines
+SELECT p1.oid, p1.typname, p2.oid, p2.proname
+FROM pg_type AS p1, pg_proc AS p2
+WHERE p1.typoutput = p2.oid AND p1.typtype in ('b', 'p') AND NOT
+    (p2.pronargs = 1 AND
+     (p2.proargtypes[0] = p1.oid OR
+      (p2.oid = 'array_out'::regproc AND
+       p1.typelem != 0 AND p1.typlen = -1)))
+ORDER BY 1;
+ oid  |  typname  | oid | proname 
+------+-----------+-----+---------
+ 1790 | refcursor |  47 | textout
+(1 row)
+
+SELECT p1.oid, p1.typname, p2.oid, p2.proname
+FROM pg_type AS p1, pg_proc AS p2
+WHERE p1.typoutput = p2.oid AND p1.typtype in ('b', 'p') AND NOT
+    (p2.prorettype = 'cstring'::regtype AND NOT p2.proretset);
+ oid | typname | oid | proname 
+-----+---------+-----+---------
+(0 rows)
+
+-- Check for bogus typreceive routines
+SELECT p1.oid, p1.typname, p2.oid, p2.proname
+FROM pg_type AS p1, pg_proc AS p2
+WHERE p1.typreceive = p2.oid AND p1.typtype in ('b', 'p') AND NOT
+    ((p2.pronargs = 1 AND p2.proargtypes[0] = 'internal'::regtype) OR
+     (p2.pronargs = 3 AND p2.proargtypes[0] = 'internal'::regtype AND
+      p2.proargtypes[1] = 'oid'::regtype AND
+      p2.proargtypes[2] = 'int4'::regtype));
+ oid | typname | oid | proname 
+-----+---------+-----+---------
+(0 rows)
+
+-- As of 7.4, this check finds refcursor, which is borrowing
+-- other types' I/O routines
+SELECT p1.oid, p1.typname, p2.oid, p2.proname
+FROM pg_type AS p1, pg_proc AS p2
+WHERE p1.typreceive = p2.oid AND p1.typtype in ('b', 'p') AND NOT
+    (p1.typelem != 0 AND p1.typlen < 0) AND NOT
+    (p2.prorettype = p1.oid AND NOT p2.proretset)
+ORDER BY 1;
+ oid  |  typname  | oid  | proname  
+------+-----------+------+----------
+ 1790 | refcursor | 2414 | textrecv
+(1 row)
+
+-- Varlena array types will point to array_recv
+-- Exception as of 8.1: int2vector and oidvector have their own I/O routines
+SELECT p1.oid, p1.typname, p2.oid, p2.proname
+FROM pg_type AS p1, pg_proc AS p2
+WHERE p1.typreceive = p2.oid AND p1.typtype in ('b', 'p') AND
+    (p1.typelem != 0 AND p1.typlen < 0) AND NOT
+    (p2.oid = 'array_recv'::regproc)
+ORDER BY 1;
+ oid |  typname   | oid  |    proname     
+-----+------------+------+----------------
+  22 | int2vector | 2410 | int2vectorrecv
+  30 | oidvector  | 2420 | oidvectorrecv
+(2 rows)
+
+-- Array types should have same typdelim as their element types
+SELECT p1.oid, p1.typname, p2.oid, p2.typname
+FROM pg_type p1, pg_type p2
+WHERE p1.typelem = p2.oid and p1.typdelim != p2.typdelim
+  AND p1.typname like E'\\_%';
+ oid | typname | oid | typname 
+-----+---------+-----+---------
+(0 rows)
+
+-- Suspicious if typreceive doesn't take same number of args as typinput
+SELECT p1.oid, p1.typname, p2.oid, p2.proname, p3.oid, p3.proname
+FROM pg_type AS p1, pg_proc AS p2, pg_proc AS p3
+WHERE p1.typinput = p2.oid AND p1.typreceive = p3.oid AND
+    p2.pronargs != p3.pronargs;
+ oid | typname | oid | proname | oid | proname 
+-----+---------+-----+---------+-----+---------
+(0 rows)
+
+-- Check for bogus typsend routines
+-- As of 7.4, this check finds refcursor, which is borrowing
+-- other types' I/O routines
+SELECT p1.oid, p1.typname, p2.oid, p2.proname
+FROM pg_type AS p1, pg_proc AS p2
+WHERE p1.typsend = p2.oid AND p1.typtype in ('b', 'p') AND NOT
+    (p2.pronargs = 1 AND
+     (p2.proargtypes[0] = p1.oid OR
+      (p2.oid = 'array_send'::regproc AND
+       p1.typelem != 0 AND p1.typlen = -1)))
+ORDER BY 1;
+ oid  |  typname  | oid  | proname  
+------+-----------+------+----------
+ 1790 | refcursor | 2415 | textsend
+(1 row)
+
+SELECT p1.oid, p1.typname, p2.oid, p2.proname
+FROM pg_type AS p1, pg_proc AS p2
+WHERE p1.typsend = p2.oid AND p1.typtype in ('b', 'p') AND NOT
+    (p2.prorettype = 'bytea'::regtype AND NOT p2.proretset);
+ oid | typname | oid | proname 
+-----+---------+-----+---------
+(0 rows)
+
+-- **************** pg_class ****************
+-- Look for illegal values in pg_class fields
+SELECT p1.oid, p1.relname
+FROM pg_class as p1
+WHERE p1.relkind NOT IN ('r', 'i', 'S', 'u', 't', 'o', 'b', 'v', 'c');
+ oid | relname 
+-----+---------
+(0 rows)
+
+-- Indexes should have an access method, others not.
+SELECT p1.oid, p1.relname
+FROM pg_class as p1
+WHERE (p1.relkind = 'i' AND p1.relam = 0) OR
+    (p1.relkind != 'i' AND p1.relam != 0);
+ oid | relname 
+-----+---------
+(0 rows)
+
+-- **************** pg_attribute ****************
+-- Look for illegal values in pg_attribute fields
+SELECT p1.attrelid, p1.attname
+FROM pg_attribute as p1
+WHERE p1.attrelid = 0 OR p1.atttypid = 0 OR p1.attnum = 0 OR
+    p1.attcacheoff != -1 OR p1.attinhcount < 0 OR
+    (p1.attinhcount = 0 AND NOT p1.attislocal);
+ attrelid | attname 
+----------+---------
+(0 rows)
+
+-- Cross-check attnum against parent relation
+SELECT p1.attrelid, p1.attname, p2.oid, p2.relname
+FROM pg_attribute AS p1, pg_class AS p2
+WHERE p1.attrelid = p2.oid AND p1.attnum > p2.relnatts;
+ attrelid | attname | oid | relname 
+----------+---------+-----+---------
+(0 rows)
+
+-- Detect missing pg_attribute entries: should have as many non-system
+-- attributes as parent relation expects
+SELECT p1.oid, p1.relname
+FROM pg_class AS p1
+WHERE p1.relnatts != (SELECT count(*) FROM pg_attribute AS p2
+                      WHERE p2.attrelid = p1.oid AND p2.attnum > 0);
+ oid | relname 
+-----+---------
+(0 rows)
+
+-- Cross-check against pg_type entry
+-- NOTE: we allow attstorage to be 'plain' even when typstorage is not;
+-- this is mainly for toast tables.
+-- UNDONE: Turn this off until we can figure out why the new system columns cause a bunch of rows to be generated here???
+-- SELECT p1.attrelid, p1.attname, p2.oid, p2.typname
+-- FROM pg_attribute AS p1, pg_type AS p2
+-- WHERE p1.atttypid = p2.oid AND
+--    (p1.attlen != p2.typlen OR
+--     p1.attalign != p2.typalign OR
+--     p1.attbyval != p2.typbyval OR
+--     (p1.attstorage != p2.typstorage AND p1.attstorage != 'p'));

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/5506a228/src/test/feature/catalog/ans/varchar.ans
----------------------------------------------------------------------
diff --git a/src/test/feature/catalog/ans/varchar.ans b/src/test/feature/catalog/ans/varchar.ans
new file mode 100755
index 0000000..1c10b15
--- /dev/null
+++ b/src/test/feature/catalog/ans/varchar.ans
@@ -0,0 +1,125 @@
+--
+-- VARCHAR
+--
+CREATE TABLE VARCHAR_TBL(f1 varchar(1));
+CREATE TABLE
+INSERT INTO VARCHAR_TBL (f1) VALUES ('a');
+INSERT 0 1
+INSERT INTO VARCHAR_TBL (f1) VALUES ('A');
+INSERT 0 1
+-- any of the following three input formats are acceptable 
+INSERT INTO VARCHAR_TBL (f1) VALUES ('1');
+INSERT 0 1
+INSERT INTO VARCHAR_TBL (f1) VALUES (2);
+INSERT 0 1
+INSERT INTO VARCHAR_TBL (f1) VALUES ('3');
+INSERT 0 1
+-- zero-length char 
+INSERT INTO VARCHAR_TBL (f1) VALUES ('');
+INSERT 0 1
+-- try varchar's of greater than 1 length 
+INSERT INTO VARCHAR_TBL (f1) VALUES ('cd');
+psql:/tmp/TestType_varchar.sql:25: ERROR:  value too long for type character varying(1)
+INSERT INTO VARCHAR_TBL (f1) VALUES ('c     ');
+INSERT 0 1
+SELECT '' AS seven, * FROM VARCHAR_TBL;
+ seven | f1 
+-------+----
+       | a
+       | A
+       | 1
+       | 2
+       | 3
+       | 
+       | c
+(7 rows)
+
+SELECT '' AS six, c.*
+   FROM VARCHAR_TBL c
+   WHERE c.f1 <> 'a';
+ six | f1 
+-----+----
+     | A
+     | 1
+     | 2
+     | 3
+     | 
+     | c
+(6 rows)
+
+SELECT '' AS one, c.*
+   FROM VARCHAR_TBL c
+   WHERE c.f1 = 'a';
+ one | f1 
+-----+----
+     | a
+(1 row)
+
+SELECT '' AS five, c.*
+   FROM VARCHAR_TBL c
+   WHERE c.f1 < 'a';
+ five | f1 
+------+----
+      | A
+      | 1
+      | 2
+      | 3
+      | 
+(5 rows)
+
+SELECT '' AS six, c.*
+   FROM VARCHAR_TBL c
+   WHERE c.f1 <= 'a';
+ six | f1 
+-----+----
+     | a
+     | A
+     | 1
+     | 2
+     | 3
+     | 
+(6 rows)
+
+SELECT '' AS one, c.*
+   FROM VARCHAR_TBL c
+   WHERE c.f1 > 'a';
+ one | f1 
+-----+----
+     | c
+(1 row)
+
+SELECT '' AS two, c.*
+   FROM VARCHAR_TBL c
+   WHERE c.f1 >= 'a';
+ two | f1 
+-----+----
+     | a
+     | c
+(2 rows)
+
+DROP TABLE VARCHAR_TBL;
+DROP TABLE
+--
+-- Now test longer arrays of char
+--
+CREATE TABLE VARCHAR_TBL(f1 varchar(4));
+CREATE TABLE
+INSERT INTO VARCHAR_TBL (f1) VALUES ('a');
+INSERT 0 1
+INSERT INTO VARCHAR_TBL (f1) VALUES ('ab');
+INSERT 0 1
+INSERT INTO VARCHAR_TBL (f1) VALUES ('abcd');
+INSERT 0 1
+INSERT INTO VARCHAR_TBL (f1) VALUES ('abcde');
+psql:/tmp/TestType_varchar.sql:66: ERROR:  value too long for type character varying(4)
+INSERT INTO VARCHAR_TBL (f1) VALUES ('abcd    ');
+INSERT 0 1
+SELECT '' AS four, * FROM VARCHAR_TBL;
+ four |  f1  
+------+------
+      | a
+      | ab
+      | abcd
+      | abcd
+(4 rows)
+

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/5506a228/src/test/feature/catalog/sql/boolean.sql
----------------------------------------------------------------------
diff --git a/src/test/feature/catalog/sql/boolean.sql b/src/test/feature/catalog/sql/boolean.sql
new file mode 100644
index 0000000..4282c15
--- /dev/null
+++ b/src/test/feature/catalog/sql/boolean.sql
@@ -0,0 +1,149 @@
+--
+-- BOOLEAN
+--
+
+--
+-- sanity check - if this fails go insane!
+--
+SELECT 1 AS one;
+
+
+-- ******************testing built-in type bool********************
+
+-- check bool type-casting as well as and, or, not in qualifications--
+
+SELECT bool 't' AS true;
+
+SELECT bool 'f' AS false;
+
+SELECT bool 't' or bool 'f' AS true;
+
+SELECT bool 't' and bool 'f' AS false;
+
+SELECT not bool 'f' AS true;
+
+SELECT bool 't' = bool 'f' AS false;
+
+SELECT bool 't' <> bool 'f' AS true;
+
+
+CREATE TABLE BOOLTBL1 (f1 bool);
+
+INSERT INTO BOOLTBL1 (f1) VALUES (bool 't');
+
+INSERT INTO BOOLTBL1 (f1) VALUES (bool 'True');
+
+INSERT INTO BOOLTBL1 (f1) VALUES (bool 'true');
+
+
+-- BOOLTBL1 should be full of true's at this point 
+SELECT '' AS t_3, BOOLTBL1.* FROM BOOLTBL1;
+
+
+SELECT '' AS t_3, BOOLTBL1.*
+   FROM BOOLTBL1
+   WHERE f1 = bool 'true';
+
+
+SELECT '' AS t_3, BOOLTBL1.* 
+   FROM BOOLTBL1
+   WHERE f1 <> bool 'false';
+
+SELECT '' AS zero, BOOLTBL1.*
+   FROM BOOLTBL1
+   WHERE booleq(bool 'false', f1);
+
+INSERT INTO BOOLTBL1 (f1) VALUES (bool 'f');
+
+SELECT '' AS f_1, BOOLTBL1.* 
+   FROM BOOLTBL1
+   WHERE f1 = bool 'false';
+
+
+CREATE TABLE BOOLTBL2 (f1 bool);
+
+INSERT INTO BOOLTBL2 (f1) VALUES (bool 'f');
+
+INSERT INTO BOOLTBL2 (f1) VALUES (bool 'false');
+
+INSERT INTO BOOLTBL2 (f1) VALUES (bool 'False');
+
+INSERT INTO BOOLTBL2 (f1) VALUES (bool 'FALSE');
+
+-- 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');  
+
+-- BOOLTBL2 should be full of false's at this point 
+SELECT '' AS f_4, BOOLTBL2.* FROM BOOLTBL2;
+
+
+SELECT '' AS tf_12, BOOLTBL1.*, BOOLTBL2.*
+   FROM BOOLTBL1, BOOLTBL2
+   WHERE BOOLTBL2.f1 <> BOOLTBL1.f1;
+
+
+SELECT '' AS tf_12, BOOLTBL1.*, BOOLTBL2.*
+   FROM BOOLTBL1, BOOLTBL2
+   WHERE boolne(BOOLTBL2.f1,BOOLTBL1.f1);
+
+
+SELECT '' AS ff_4, BOOLTBL1.*, BOOLTBL2.*
+   FROM BOOLTBL1, BOOLTBL2
+   WHERE BOOLTBL2.f1 = BOOLTBL1.f1 and BOOLTBL1.f1 = bool 'false' ;
+
+
+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 ;
+
+--
+-- 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;
+
+SELECT '' AS "Not False", f1
+   FROM BOOLTBL1
+   WHERE f1 IS NOT FALSE;
+
+SELECT '' AS "False", f1
+   FROM BOOLTBL1
+   WHERE f1 IS FALSE;
+
+SELECT '' AS "Not True", f1
+   FROM BOOLTBL1
+   WHERE f1 IS NOT TRUE;
+
+SELECT '' AS "True", f1
+   FROM BOOLTBL2
+   WHERE f1 IS TRUE;
+
+SELECT '' AS "Not False", f1
+   FROM BOOLTBL2
+   WHERE f1 IS NOT FALSE;
+
+SELECT '' AS "False", f1
+   FROM BOOLTBL2
+   WHERE f1 IS FALSE;
+
+SELECT '' AS "Not True", f1
+   FROM BOOLTBL2
+   WHERE f1 IS NOT TRUE;
+
+--
+-- 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  BOOLTBL2;

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/5506a228/src/test/feature/catalog/sql/char.sql
----------------------------------------------------------------------
diff --git a/src/test/feature/catalog/sql/char.sql b/src/test/feature/catalog/sql/char.sql
new file mode 100644
index 0000000..fcaef7e
--- /dev/null
+++ b/src/test/feature/catalog/sql/char.sql
@@ -0,0 +1,75 @@
+--
+-- CHAR
+--
+
+-- fixed-length by value
+-- internally passed by value if <= 4 bytes in storage
+
+SELECT char 'c' = char 'c' AS true;
+
+--
+-- Build a table for testing
+--
+
+CREATE TABLE CHAR_TBL(f1 char);
+
+INSERT INTO CHAR_TBL (f1) VALUES ('a');
+
+INSERT INTO CHAR_TBL (f1) VALUES ('A');
+
+-- any of the following three input formats are acceptable 
+INSERT INTO CHAR_TBL (f1) VALUES ('1');
+
+INSERT INTO CHAR_TBL (f1) VALUES (2);
+
+INSERT INTO CHAR_TBL (f1) VALUES ('3');
+
+-- zero-length char 
+INSERT INTO CHAR_TBL (f1) VALUES ('');
+
+-- try char's of greater than 1 length 
+INSERT INTO CHAR_TBL (f1) VALUES ('cd');
+INSERT INTO CHAR_TBL (f1) VALUES ('c     ');
+
+
+SELECT '' AS seven, * FROM CHAR_TBL;
+
+SELECT '' AS six, c.*
+   FROM CHAR_TBL c
+   WHERE c.f1 <> 'a';
+
+SELECT '' AS one, c.*
+   FROM CHAR_TBL c
+   WHERE c.f1 = 'a';
+
+SELECT '' AS five, c.*
+   FROM CHAR_TBL c
+   WHERE c.f1 < 'a';
+
+SELECT '' AS six, c.*
+   FROM CHAR_TBL c
+   WHERE c.f1 <= 'a';
+
+SELECT '' AS one, c.*
+   FROM CHAR_TBL c
+   WHERE c.f1 > 'a';
+
+SELECT '' AS two, c.*
+   FROM CHAR_TBL c
+   WHERE c.f1 >= 'a';
+
+DROP TABLE CHAR_TBL;
+
+--
+-- Now test longer arrays of char
+--
+
+CREATE TABLE CHAR_TBL(f1 char(4));
+
+INSERT INTO CHAR_TBL (f1) VALUES ('a');
+INSERT INTO CHAR_TBL (f1) VALUES ('ab');
+INSERT INTO CHAR_TBL (f1) VALUES ('abcd');
+INSERT INTO CHAR_TBL (f1) VALUES ('abcde');
+INSERT INTO CHAR_TBL (f1) VALUES ('abcd    ');
+
+SELECT '' AS four, * FROM CHAR_TBL;

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/5506a228/src/test/feature/catalog/sql/date.sql
----------------------------------------------------------------------
diff --git a/src/test/feature/catalog/sql/date.sql b/src/test/feature/catalog/sql/date.sql
new file mode 100644
index 0000000..7ed6e15
--- /dev/null
+++ b/src/test/feature/catalog/sql/date.sql
@@ -0,0 +1,271 @@
+--
+-- DATE
+--
+
+CREATE TABLE DATE_TBL (f1 date);
+
+INSERT INTO DATE_TBL VALUES ('1957-04-09');
+INSERT INTO DATE_TBL VALUES ('1957-06-13');
+INSERT INTO DATE_TBL VALUES ('1996-02-28');
+INSERT INTO DATE_TBL VALUES ('1996-02-29');
+INSERT INTO DATE_TBL VALUES ('1996-03-01');
+INSERT INTO DATE_TBL VALUES ('1996-03-02');
+INSERT INTO DATE_TBL VALUES ('1997-02-28');
+INSERT INTO DATE_TBL VALUES ('1997-02-29');
+INSERT INTO DATE_TBL VALUES ('1997-03-01');
+INSERT INTO DATE_TBL VALUES ('1997-03-02');
+INSERT INTO DATE_TBL VALUES ('2000-04-01');
+INSERT INTO DATE_TBL VALUES ('2000-04-02');
+INSERT INTO DATE_TBL VALUES ('2000-04-03');
+INSERT INTO DATE_TBL VALUES ('2038-04-08');
+INSERT INTO DATE_TBL VALUES ('2039-04-09');
+INSERT INTO DATE_TBL VALUES ('2040-04-10');
+
+SELECT f1 AS "Fifteen" FROM DATE_TBL ORDER BY 1;
+
+SELECT f1 AS "Nine" FROM DATE_TBL WHERE f1 < '2000-01-01' ORDER BY 1;
+
+SELECT f1 AS "Three" FROM DATE_TBL
+  WHERE f1 BETWEEN '2000-01-01' AND '2001-01-01' ORDER BY 1;
+
+--
+-- Check all the documented input formats
+--
+SET datestyle TO iso;  -- display results in ISO
+
+SET datestyle TO ymd;
+
+SELECT date 'January 8, 1999';
+SELECT date '1999-01-08';
+SELECT date '1999-01-18';
+SELECT date '1/8/1999';
+SELECT date '1/18/1999';
+SELECT date '18/1/1999';
+SELECT date '01/02/03';
+SELECT date '19990108';
+SELECT date '990108';
+SELECT date '1999.008';
+SELECT date 'J2451187';
+SELECT date 'January 8, 99 BC';
+
+SELECT date '99-Jan-08';
+SELECT date '1999-Jan-08';
+SELECT date '08-Jan-99';
+SELECT date '08-Jan-1999';
+SELECT date 'Jan-08-99';
+SELECT date 'Jan-08-1999';
+SELECT date '99-08-Jan';
+SELECT date '1999-08-Jan';
+
+SELECT date '99 Jan 08';
+SELECT date '1999 Jan 08';
+SELECT date '08 Jan 99';
+SELECT date '08 Jan 1999';
+SELECT date 'Jan 08 99';
+SELECT date 'Jan 08 1999';
+SELECT date '99 08 Jan';
+SELECT date '1999 08 Jan';
+
+SELECT date '99-01-08';
+SELECT date '1999-01-08';
+SELECT date '08-01-99';
+SELECT date '08-01-1999';
+SELECT date '01-08-99';
+SELECT date '01-08-1999';
+SELECT date '99-08-01';
+SELECT date '1999-08-01';
+
+SELECT date '99 01 08';
+SELECT date '1999 01 08';
+SELECT date '08 01 99';
+SELECT date '08 01 1999';
+SELECT date '01 08 99';
+SELECT date '01 08 1999';
+SELECT date '99 08 01';
+SELECT date '1999 08 01';
+
+SET datestyle TO dmy;
+
+SELECT date 'January 8, 1999';
+SELECT date '1999-01-08';
+SELECT date '1999-01-18';
+SELECT date '1/8/1999';
+SELECT date '1/18/1999';
+SELECT date '18/1/1999';
+SELECT date '01/02/03';
+SELECT date '19990108';
+SELECT date '990108';
+SELECT date '1999.008';
+SELECT date 'J2451187';
+SELECT date 'January 8, 99 BC';
+
+SELECT date '99-Jan-08';
+SELECT date '1999-Jan-08';
+SELECT date '08-Jan-99';
+SELECT date '08-Jan-1999';
+SELECT date 'Jan-08-99';
+SELECT date 'Jan-08-1999';
+SELECT date '99-08-Jan';
+SELECT date '1999-08-Jan';
+
+SELECT date '99 Jan 08';
+SELECT date '1999 Jan 08';
+SELECT date '08 Jan 99';
+SELECT date '08 Jan 1999';
+SELECT date 'Jan 08 99';
+SELECT date 'Jan 08 1999';
+SELECT date '99 08 Jan';
+SELECT date '1999 08 Jan';
+
+SELECT date '99-01-08';
+SELECT date '1999-01-08';
+SELECT date '08-01-99';
+SELECT date '08-01-1999';
+SELECT date '01-08-99';
+SELECT date '01-08-1999';
+SELECT date '99-08-01';
+SELECT date '1999-08-01';
+
+SELECT date '99 01 08';
+SELECT date '1999 01 08';
+SELECT date '08 01 99';
+SELECT date '08 01 1999';
+SELECT date '01 08 99';
+SELECT date '01 08 1999';
+SELECT date '99 08 01';
+SELECT date '1999 08 01';
+
+SET datestyle TO mdy;
+
+SELECT date 'January 8, 1999';
+SELECT date '1999-01-08';
+SELECT date '1999-01-18';
+SELECT date '1/8/1999';
+SELECT date '1/18/1999';
+SELECT date '18/1/1999';
+SELECT date '01/02/03';
+SELECT date '19990108';
+SELECT date '990108';
+SELECT date '1999.008';
+SELECT date 'J2451187';
+SELECT date 'January 8, 99 BC';
+
+SELECT date '99-Jan-08';
+SELECT date '1999-Jan-08';
+SELECT date '08-Jan-99';
+SELECT date '08-Jan-1999';
+SELECT date 'Jan-08-99';
+SELECT date 'Jan-08-1999';
+SELECT date '99-08-Jan';
+SELECT date '1999-08-Jan';
+
+SELECT date '99 Jan 08';
+SELECT date '1999 Jan 08';
+SELECT date '08 Jan 99';
+SELECT date '08 Jan 1999';
+SELECT date 'Jan 08 99';
+SELECT date 'Jan 08 1999';
+SELECT date '99 08 Jan';
+SELECT date '1999 08 Jan';
+
+SELECT date '99-01-08';
+SELECT date '1999-01-08';
+SELECT date '08-01-99';
+SELECT date '08-01-1999';
+SELECT date '01-08-99';
+SELECT date '01-08-1999';
+SELECT date '99-08-01';
+SELECT date '1999-08-01';
+
+SELECT date '99 01 08';
+SELECT date '1999 01 08';
+SELECT date '08 01 99';
+SELECT date '08 01 1999';
+SELECT date '01 08 99';
+SELECT date '01 08 1999';
+SELECT date '99 08 01';
+SELECT date '1999 08 01';
+
+RESET datestyle;
+
+--
+-- 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;
+
+SELECT f1 - date 'epoch' AS "Days From Epoch" FROM DATE_TBL ORDER BY 1;
+
+SELECT date 'yesterday' - date 'today' AS "One day";
+
+SELECT date 'today' - date 'tomorrow' AS "One day";
+
+SELECT date 'yesterday' - date 'tomorrow' AS "Two days";
+
+SELECT date 'tomorrow' - date 'today' AS "One day";
+
+SELECT date 'today' - date 'yesterday' AS "One day";
+
+SELECT date 'tomorrow' - date 'yesterday' AS "Two days";
+
+--
+-- test extract!
+--
+-- century
+--
+SELECT EXTRACT(CENTURY FROM DATE '0101-12-31 BC'); -- -2
+SELECT EXTRACT(CENTURY FROM DATE '0100-12-31 BC'); -- -1
+SELECT EXTRACT(CENTURY FROM DATE '0001-12-31 BC'); -- -1
+SELECT EXTRACT(CENTURY FROM DATE '0001-01-01');    --  1
+SELECT EXTRACT(CENTURY FROM DATE '0001-01-01 AD'); --  1
+SELECT EXTRACT(CENTURY FROM DATE '1900-12-31');    -- 19
+SELECT EXTRACT(CENTURY FROM DATE '1901-01-01');    -- 20
+SELECT EXTRACT(CENTURY FROM DATE '2000-12-31');    -- 20
+SELECT EXTRACT(CENTURY FROM DATE '2001-01-01');    -- 21
+SELECT EXTRACT(CENTURY FROM CURRENT_DATE)>=21 AS True;     -- true
+--
+-- millennium
+--
+SELECT EXTRACT(MILLENNIUM FROM DATE '0001-12-31 BC'); -- -1
+SELECT EXTRACT(MILLENNIUM FROM DATE '0001-01-01 AD'); --  1
+SELECT EXTRACT(MILLENNIUM FROM DATE '1000-12-31');    --  1
+SELECT EXTRACT(MILLENNIUM FROM DATE '1001-01-01');    --  2
+SELECT EXTRACT(MILLENNIUM FROM DATE '2000-12-31');    --  2
+SELECT EXTRACT(MILLENNIUM FROM DATE '2001-01-01');    --  3
+-- next test to be fixed on the turn of the next millennium;-)
+SELECT EXTRACT(MILLENNIUM FROM CURRENT_DATE);         --  3
+--
+-- decade
+--
+SELECT EXTRACT(DECADE FROM DATE '1994-12-25');    -- 199
+SELECT EXTRACT(DECADE FROM DATE '0010-01-01');    --   1
+SELECT EXTRACT(DECADE FROM DATE '0009-12-31');    --   0
+SELECT EXTRACT(DECADE FROM DATE '0001-01-01 BC'); --   0
+SELECT EXTRACT(DECADE FROM DATE '0002-12-31 BC'); --  -1
+SELECT EXTRACT(DECADE FROM DATE '0011-01-01 BC'); --  -1
+SELECT EXTRACT(DECADE FROM DATE '0012-12-31 BC'); --  -2
+--
+-- some other types:
+--
+-- on a timestamp.
+SELECT EXTRACT(CENTURY FROM NOW())>=21 AS True;       -- true
+SELECT EXTRACT(CENTURY FROM TIMESTAMP '1970-03-20 04:30:00.00000'); -- 20
+-- on an interval
+SELECT EXTRACT(CENTURY FROM INTERVAL '100 y');  -- 1
+SELECT EXTRACT(CENTURY FROM INTERVAL '99 y');   -- 0
+SELECT EXTRACT(CENTURY FROM INTERVAL '-99 y');  -- 0
+SELECT EXTRACT(CENTURY FROM INTERVAL '-100 y'); -- -1
+--
+-- test trunc function!
+--
+SELECT DATE_TRUNC('MILLENNIUM', TIMESTAMP '1970-03-20 04:30:00.00000'); -- 1001
+SELECT DATE_TRUNC('MILLENNIUM', DATE '1970-03-20'); -- 1001-01-01
+SELECT DATE_TRUNC('CENTURY', TIMESTAMP '1970-03-20 04:30:00.00000'); -- 1901
+SELECT DATE_TRUNC('CENTURY', DATE '1970-03-20'); -- 1901
+SELECT DATE_TRUNC('CENTURY', DATE '2004-08-10'); -- 2001-01-01
+SELECT DATE_TRUNC('CENTURY', DATE '0002-02-04'); -- 0001-01-01
+SELECT DATE_TRUNC('CENTURY', DATE '0055-08-10 BC'); -- 0100-01-01 BC
+SELECT DATE_TRUNC('DECADE', DATE '1993-12-25'); -- 1990-01-01
+SELECT DATE_TRUNC('DECADE', DATE '0004-12-25'); -- 0001-01-01 BC
+SELECT DATE_TRUNC('DECADE', DATE '0002-12-31 BC'); -- 0011-01-01 BC

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/5506a228/src/test/feature/catalog/sql/float4.sql
----------------------------------------------------------------------
diff --git a/src/test/feature/catalog/sql/float4.sql b/src/test/feature/catalog/sql/float4.sql
new file mode 100644
index 0000000..f33c6d2
--- /dev/null
+++ b/src/test/feature/catalog/sql/float4.sql
@@ -0,0 +1,85 @@
+--
+-- FLOAT4
+--
+
+CREATE TABLE FLOAT4_TBL (f1  float4);
+
+INSERT INTO FLOAT4_TBL(f1) VALUES ('    0.0');
+INSERT INTO FLOAT4_TBL(f1) VALUES ('1004.30   ');
+INSERT INTO FLOAT4_TBL(f1) VALUES ('     -34.84    ');
+INSERT INTO FLOAT4_TBL(f1) VALUES ('1.2345678901234e+20');
+INSERT INTO FLOAT4_TBL(f1) VALUES ('1.2345678901234e-20');
+
+-- test for over and under flow 
+INSERT INTO FLOAT4_TBL(f1) VALUES ('10e40');
+INSERT INTO FLOAT4_TBL(f1) VALUES ('-10e40');
+INSERT INTO FLOAT4_TBL(f1) VALUES ('10e-40');
+INSERT INTO FLOAT4_TBL(f1) VALUES ('-10e-40');
+
+-- bad input
+INSERT INTO FLOAT4_TBL(f1) VALUES ('');
+INSERT INTO FLOAT4_TBL(f1) VALUES ('       ');
+INSERT INTO FLOAT4_TBL(f1) VALUES ('xyz');
+INSERT INTO FLOAT4_TBL(f1) VALUES ('5.0.0');
+INSERT INTO FLOAT4_TBL(f1) VALUES ('5 . 0');
+INSERT INTO FLOAT4_TBL(f1) VALUES ('5.   0');
+INSERT INTO FLOAT4_TBL(f1) VALUES ('     - 3.0');
+INSERT INTO FLOAT4_TBL(f1) VALUES ('123            5');
+
+-- special inputs
+SELECT 'NaN'::float4;
+SELECT 'nan'::float4;
+SELECT '   NAN  '::float4;
+SELECT 'infinity'::float4;
+SELECT '          -INFINiTY   '::float4;
+-- bad special inputs
+SELECT 'N A N'::float4;
+SELECT 'NaN x'::float4;
+SELECT ' INFINITY    x'::float4;
+
+SELECT 'Infinity'::float4 + 100.0;
+SELECT 'Infinity'::float4 / 'Infinity'::float4;
+SELECT 'nan'::float4 / 'nan'::float4;
+
+
+SELECT '' AS five, * FROM FLOAT4_TBL ORDER BY 2;
+
+SELECT '' AS four, f.* FROM FLOAT4_TBL f WHERE f.f1 <> '1004.3' ORDER BY 2;
+
+SELECT '' AS one, f.* FROM FLOAT4_TBL f WHERE f.f1 = '1004.3' ORDER BY 2;
+
+SELECT '' AS three, f.* FROM FLOAT4_TBL f WHERE '1004.3' > f.f1 ORDER BY 2;
+
+SELECT '' AS three, f.* FROM FLOAT4_TBL f WHERE  f.f1 < '1004.3' ORDER BY 2;
+
+SELECT '' AS four, f.* FROM FLOAT4_TBL f WHERE '1004.3' >= f.f1 ORDER BY 2;
+
+SELECT '' AS four, f.* FROM FLOAT4_TBL f WHERE  f.f1 <= '1004.3' ORDER BY 2;
+
+SELECT '' AS three, f.f1, f.f1 * '-10' AS x FROM FLOAT4_TBL f
+   WHERE f.f1 > '0.0' ORDER BY 2;
+
+SELECT '' AS three, f.f1, f.f1 + '-10' AS x FROM FLOAT4_TBL f
+   WHERE f.f1 > '0.0' ORDER BY 2;
+
+SELECT '' AS three, f.f1, f.f1 / '-10' AS x FROM FLOAT4_TBL f
+   WHERE f.f1 > '0.0' ORDER BY 2;
+
+SELECT '' AS three, f.f1, f.f1 - '-10' AS x FROM FLOAT4_TBL f
+   WHERE f.f1 > '0.0' ORDER BY 2;
+
+-- test divide by zero
+SELECT '' AS bad, f.f1 / '0.0' from FLOAT4_TBL f;
+
+SELECT '' AS five, * FROM FLOAT4_TBL ORDER BY 2;
+
+-- test the unary float4abs operator 
+SELECT '' AS five, f.f1, @f.f1 AS abs_f1 FROM FLOAT4_TBL f ORDER BY 2;
+
+-- MPP doesn't support this yet.
+--UPDATE FLOAT4_TBL
+--   SET f1 = FLOAT4_TBL.f1 * '-1'
+--   WHERE FLOAT4_TBL.f1 > '0.0';
+
+--SELECT '' AS five, * FROM FLOAT4_TBL ORDER BY 2;
+

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/5506a228/src/test/feature/catalog/sql/float8.sql
----------------------------------------------------------------------
diff --git a/src/test/feature/catalog/sql/float8.sql b/src/test/feature/catalog/sql/float8.sql
new file mode 100644
index 0000000..7809203
--- /dev/null
+++ b/src/test/feature/catalog/sql/float8.sql
@@ -0,0 +1,167 @@
+--
+-- FLOAT8
+--
+
+CREATE TABLE FLOAT8_TBL(i INT DEFAULT 1, f1 float8);
+
+INSERT INTO FLOAT8_TBL(f1) VALUES ('    0.0   ');
+INSERT INTO FLOAT8_TBL(f1) VALUES ('1004.30  ');
+INSERT INTO FLOAT8_TBL(f1) VALUES ('   -34.84');
+INSERT INTO FLOAT8_TBL(f1) VALUES ('1.2345678901234e+200');
+INSERT INTO FLOAT8_TBL(f1) VALUES ('1.2345678901234e-200');
+
+-- test for underflow and overflow handling
+SELECT '10e400'::float8;
+SELECT '-10e400'::float8;
+SELECT '10e-400'::float8;
+SELECT '-10e-400'::float8;
+
+-- bad input
+INSERT INTO FLOAT8_TBL(f1) VALUES ('');
+INSERT INTO FLOAT8_TBL(f1) VALUES ('     ');
+INSERT INTO FLOAT8_TBL(f1) VALUES ('xyz');
+INSERT INTO FLOAT8_TBL(f1) VALUES ('5.0.0');
+INSERT INTO FLOAT8_TBL(f1) VALUES ('5 . 0');
+INSERT INTO FLOAT8_TBL(f1) VALUES ('5.   0');
+INSERT INTO FLOAT8_TBL(f1) VALUES ('    - 3');
+INSERT INTO FLOAT8_TBL(f1) VALUES ('123           5');
+
+-- special inputs
+SELECT 'NaN'::float8;
+SELECT 'nan'::float8;
+SELECT '   NAN  '::float8;
+SELECT 'infinity'::float8;
+SELECT '          -INFINiTY   '::float8;
+-- bad special inputs
+SELECT 'N A N'::float8;
+SELECT 'NaN x'::float8;
+SELECT ' INFINITY    x'::float8;
+
+SELECT 'Infinity'::float8 + 100.0;
+SELECT 'Infinity'::float8 / 'Infinity'::float8;
+SELECT 'nan'::float8 / 'nan'::float8;
+
+SELECT '' AS five, f1 FROM FLOAT8_TBL ORDER BY 2;
+
+SELECT '' AS four, f.f1 FROM FLOAT8_TBL f WHERE f.f1 <> '1004.3' ORDER BY 2;
+
+SELECT '' AS one, f.f1 FROM FLOAT8_TBL f WHERE f.f1 = '1004.3' ORDER BY 2;
+
+SELECT '' AS three, f.f1 FROM FLOAT8_TBL f WHERE '1004.3' > f.f1 ORDER BY 2;
+
+SELECT '' AS three, f.f1 FROM FLOAT8_TBL f WHERE  f.f1 < '1004.3' ORDER BY 2;
+
+SELECT '' AS four, f.f1 FROM FLOAT8_TBL f WHERE '1004.3' >= f.f1 ORDER BY 2;
+
+SELECT '' AS four, f.f1 FROM FLOAT8_TBL f WHERE  f.f1 <= '1004.3' ORDER BY 2;
+
+SELECT '' AS three, f.f1, f.f1 * '-10' AS x 
+   FROM FLOAT8_TBL f
+   WHERE f.f1 > '0.0' ORDER BY 2;
+
+SELECT '' AS three, f.f1, f.f1 + '-10' AS x
+   FROM FLOAT8_TBL f
+   WHERE f.f1 > '0.0' ORDER BY 2;
+
+SELECT '' AS three, f.f1, f.f1 / '-10' AS x
+   FROM FLOAT8_TBL f
+   WHERE f.f1 > '0.0' ORDER BY 2;
+
+SELECT '' AS three, f.f1, f.f1 - '-10' AS x
+   FROM FLOAT8_TBL f
+   WHERE f.f1 > '0.0' ORDER BY 2;
+
+SELECT '' AS one, f.f1 ^ '2.0' AS square_f1
+   FROM FLOAT8_TBL f where f.f1 = '1004.3';
+
+-- absolute value 
+SELECT '' AS five, f.f1, @f.f1 AS abs_f1 
+   FROM FLOAT8_TBL f ORDER BY 2;
+
+-- truncate 
+SELECT '' AS five, f.f1, trunc(f.f1) AS trunc_f1
+   FROM FLOAT8_TBL f ORDER BY 2;
+
+-- round 
+SELECT '' AS five, f.f1, round(f.f1) AS round_f1
+   FROM FLOAT8_TBL f ORDER BY 2;
+
+-- ceil / ceiling
+select ceil(f1) as ceil_f1 from float8_tbl f ORDER BY 1;
+select ceiling(f1) as ceiling_f1 from float8_tbl f ORDER BY 1;
+
+-- floor
+select floor(f1) as floor_f1 from float8_tbl f ORDER BY 1;
+
+-- sign
+select sign(f1) as sign_f1 from float8_tbl f ORDER BY 1;
+
+-- square root 
+SELECT sqrt(float8 '64') AS eight;
+
+SELECT |/ float8 '64' AS eight;
+
+SELECT '' AS three, f.f1, |/f.f1 AS sqrt_f1
+   FROM FLOAT8_TBL f
+   WHERE f.f1 > '0.0' ORDER BY 2;
+
+-- power
+SELECT power(float8 '144', float8 '0.5');
+
+-- 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;
+
+-- cube root 
+SELECT ||/ float8 '27' AS three;
+
+SELECT '' AS five, f.f1, ||/f.f1 AS cbrt_f1 FROM FLOAT8_TBL f ORDER BY 2;
+
+
+SELECT '' AS five, f1 FROM FLOAT8_TBL ORDER BY 2;
+
+UPDATE FLOAT8_TBL
+   SET f1 = FLOAT8_TBL.f1 * '-1'
+   WHERE FLOAT8_TBL.f1 > '0.0';
+
+SELECT '' AS bad, f.f1 * '1e200' from FLOAT8_TBL f;
+
+SELECT '' AS bad, f.f1 ^ '1e200' from FLOAT8_TBL f;
+
+SELECT '' AS bad, ln(f.f1) from FLOAT8_TBL f where f.f1 = '0.0' ;
+
+SELECT '' AS bad, ln(f.f1) from FLOAT8_TBL f where f.f1 < '0.0' ;
+
+SELECT '' AS bad, exp(f.f1) from FLOAT8_TBL f;
+
+SELECT '' AS bad, f.f1 / '0.0' from FLOAT8_TBL f;
+
+SELECT '' AS five, f1 FROM FLOAT8_TBL ORDER BY 2;
+
+-- test for over- and underflow 
+INSERT INTO FLOAT8_TBL(f1) VALUES ('10e400');
+
+INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e400');
+
+INSERT INTO FLOAT8_TBL(f1) VALUES ('10e-400');
+
+INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e-400');
+
+-- maintain external table consistency across platforms
+-- delete all values and reinsert well-behaved ones
+
+DELETE FROM FLOAT8_TBL;
+
+INSERT INTO FLOAT8_TBL(f1) VALUES ('0.0');
+
+INSERT INTO FLOAT8_TBL(f1) VALUES ('-34.84');
+
+INSERT INTO FLOAT8_TBL(f1) VALUES ('-1004.30');
+
+INSERT INTO FLOAT8_TBL(f1) VALUES ('-1.2345678901234e+200');
+
+INSERT INTO FLOAT8_TBL(f1) VALUES ('-1.2345678901234e-200');
+
+SELECT '' AS five, f1 FROM FLOAT8_TBL ORDER BY 2;
+

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/5506a228/src/test/feature/catalog/sql/int2.sql
----------------------------------------------------------------------
diff --git a/src/test/feature/catalog/sql/int2.sql b/src/test/feature/catalog/sql/int2.sql
new file mode 100644
index 0000000..082bb5c
--- /dev/null
+++ b/src/test/feature/catalog/sql/int2.sql
@@ -0,0 +1,88 @@
+--
+-- INT2
+-- NOTE: int2 operators never check for over/underflow!
+-- Some of these answers are consequently numerically incorrect.
+--
+
+CREATE TABLE INT2_TBL(f1 int2);
+
+INSERT INTO INT2_TBL(f1) VALUES ('0   ');
+
+INSERT INTO INT2_TBL(f1) VALUES ('  1234 ');
+
+INSERT INTO INT2_TBL(f1) VALUES ('    -1234');
+
+INSERT INTO INT2_TBL(f1) VALUES ('34.5');
+
+-- largest and smallest values
+INSERT INTO INT2_TBL(f1) VALUES ('32767');
+
+INSERT INTO INT2_TBL(f1) VALUES ('-32767');
+
+-- bad input values -- should give errors
+INSERT INTO INT2_TBL(f1) VALUES ('100000');
+INSERT INTO INT2_TBL(f1) VALUES ('asdf');
+INSERT INTO INT2_TBL(f1) VALUES ('    ');
+INSERT INTO INT2_TBL(f1) VALUES ('- 1234');
+INSERT INTO INT2_TBL(f1) VALUES ('4 444');
+INSERT INTO INT2_TBL(f1) VALUES ('123 dt');
+INSERT INTO INT2_TBL(f1) VALUES ('');
+
+
+SELECT '' AS five, * FROM INT2_TBL order by f1;
+
+SELECT '' AS four, i.* FROM INT2_TBL i WHERE i.f1 <> int2 '0' order by f1;
+
+SELECT '' AS four, i.* FROM INT2_TBL i WHERE i.f1 <> int4 '0' order by f1;
+
+SELECT '' AS one, i.* FROM INT2_TBL i WHERE i.f1 = int2 '0' order by f1;
+
+SELECT '' AS one, i.* FROM INT2_TBL i WHERE i.f1 = int4 '0' order by f1;
+
+SELECT '' AS two, i.* FROM INT2_TBL i WHERE i.f1 < int2 '0' order by f1;
+
+SELECT '' AS two, i.* FROM INT2_TBL i WHERE i.f1 < int4 '0' order by f1;
+
+SELECT '' AS three, i.* FROM INT2_TBL i WHERE i.f1 <= int2 '0' order by f1;
+
+SELECT '' AS three, i.* FROM INT2_TBL i WHERE i.f1 <= int4 '0' order by f1;
+
+SELECT '' AS two, i.* FROM INT2_TBL i WHERE i.f1 > int2 '0' order by f1;
+
+SELECT '' AS two, i.* FROM INT2_TBL i WHERE i.f1 > int4 '0' order by f1;
+
+SELECT '' AS three, i.* FROM INT2_TBL i WHERE i.f1 >= int2 '0' order by f1;
+
+SELECT '' AS three, i.* FROM INT2_TBL i WHERE i.f1 >= int4 '0' order by f1;
+
+-- positive odds 
+SELECT '' AS one, i.* FROM INT2_TBL i WHERE (i.f1 % int2 '2') = int2 '1' order by f1;
+
+-- any evens 
+SELECT '' AS three, i.* FROM INT2_TBL i WHERE (i.f1 % int4 '2') = int2 '0' order by f1;
+
+SELECT '' AS five, i.f1, i.f1 * int2 '2' AS x FROM INT2_TBL i order by f1;
+
+SELECT '' AS five, i.f1, i.f1 * int2 '2' AS x FROM INT2_TBL i
+WHERE abs(f1) < 16384 order by f1;
+
+SELECT '' AS five, i.f1, i.f1 * int4 '2' AS x FROM INT2_TBL i order by f1;
+
+SELECT '' AS five, i.f1, i.f1 + int2 '2' AS x FROM INT2_TBL i order by f1;
+
+SELECT '' AS five, i.f1, i.f1 + int2 '2' AS x FROM INT2_TBL i
+WHERE f1 < 32766 order by f1;
+
+SELECT '' AS five, i.f1, i.f1 + int4 '2' AS x FROM INT2_TBL i order by f1;
+
+SELECT '' AS five, i.f1, i.f1 - int2 '2' AS x FROM INT2_TBL i order by f1;
+
+SELECT '' AS five, i.f1, i.f1 - int2 '2' AS x FROM INT2_TBL i
+WHERE f1 > -32767 order by f1;
+
+SELECT '' AS five, i.f1, i.f1 - int4 '2' AS x FROM INT2_TBL i order by f1;
+
+SELECT '' AS five, i.f1, i.f1 / int2 '2' AS x FROM INT2_TBL i order by f1;
+
+SELECT '' AS five, i.f1, i.f1 / int4 '2' AS x FROM INT2_TBL i order by f1;
+

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/5506a228/src/test/feature/catalog/sql/int4.sql
----------------------------------------------------------------------
diff --git a/src/test/feature/catalog/sql/int4.sql b/src/test/feature/catalog/sql/int4.sql
new file mode 100644
index 0000000..d1b4225
--- /dev/null
+++ b/src/test/feature/catalog/sql/int4.sql
@@ -0,0 +1,127 @@
+--
+-- INT4
+-- WARNING: int4 operators never check for over/underflow!
+-- Some of these answers are consequently numerically incorrect.
+--
+
+CREATE TABLE INT4_TBL(f1 int4);
+
+INSERT INTO INT4_TBL(f1) VALUES ('   0  ');
+
+INSERT INTO INT4_TBL(f1) VALUES ('123456     ');
+
+INSERT INTO INT4_TBL(f1) VALUES ('    -123456');
+
+INSERT INTO INT4_TBL(f1) VALUES ('34.5');
+
+-- largest and smallest values
+INSERT INTO INT4_TBL(f1) VALUES ('2147483647');
+
+INSERT INTO INT4_TBL(f1) VALUES ('-2147483647');
+
+-- bad input values -- should give errors
+INSERT INTO INT4_TBL(f1) VALUES ('1000000000000');
+INSERT INTO INT4_TBL(f1) VALUES ('asdf');
+INSERT INTO INT4_TBL(f1) VALUES ('     ');
+INSERT INTO INT4_TBL(f1) VALUES ('   asdf   ');
+INSERT INTO INT4_TBL(f1) VALUES ('- 1234');
+INSERT INTO INT4_TBL(f1) VALUES ('123       5');
+INSERT INTO INT4_TBL(f1) VALUES ('');
+
+
+SELECT '' AS five, * FROM INT4_TBL  order by f1;
+
+SELECT '' AS four, i.* FROM INT4_TBL i WHERE i.f1 <> int2 '0'  order by f1;
+
+SELECT '' AS four, i.* FROM INT4_TBL i WHERE i.f1 <> int4 '0'  order by f1;
+
+SELECT '' AS one, i.* FROM INT4_TBL i WHERE i.f1 = int2 '0'  order by f1;
+
+SELECT '' AS one, i.* FROM INT4_TBL i WHERE i.f1 = int4 '0'  order by f1;
+
+SELECT '' AS two, i.* FROM INT4_TBL i WHERE i.f1 < int2 '0'  order by f1;
+
+SELECT '' AS two, i.* FROM INT4_TBL i WHERE i.f1 < int4 '0'  order by f1;
+
+SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 <= int2 '0'  order by f1;
+
+SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 <= int4 '0'  order by f1;
+
+SELECT '' AS two, i.* FROM INT4_TBL i WHERE i.f1 > int2 '0'  order by f1;
+
+SELECT '' AS two, i.* FROM INT4_TBL i WHERE i.f1 > int4 '0'  order by f1;
+
+SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 >= int2 '0'  order by f1;
+
+SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 >= int4 '0'  order by f1;
+
+-- positive odds
+SELECT '' AS one, i.* FROM INT4_TBL i WHERE (i.f1 % int2 '2') = int2 '1'  order by f1;
+
+-- any evens
+SELECT '' AS three, i.* FROM INT4_TBL i WHERE (i.f1 % int4 '2') = int2 '0'  order by f1;
+
+SELECT '' AS five, i.f1, i.f1 * int2 '2' AS x FROM INT4_TBL i  order by f1;
+
+SELECT '' AS five, i.f1, i.f1 * int2 '2' AS x FROM INT4_TBL i
+WHERE abs(f1) < 1073741824 order by f1;
+
+SELECT '' AS five, i.f1, i.f1 * int4 '2' AS x FROM INT4_TBL i order by f1;
+
+SELECT '' AS five, i.f1, i.f1 * int4 '2' AS x FROM INT4_TBL i
+WHERE abs(f1) < 1073741824 order by f1;
+
+SELECT '' AS five, i.f1, i.f1 + int2 '2' AS x FROM INT4_TBL i order by f1;
+
+SELECT '' AS five, i.f1, i.f1 + int2 '2' AS x FROM INT4_TBL i
+WHERE f1 < 2147483646 order by f1;
+
+SELECT '' AS five, i.f1, i.f1 + int4 '2' AS x FROM INT4_TBL i order by f1;
+
+SELECT '' AS five, i.f1, i.f1 + int4 '2' AS x FROM INT4_TBL i
+WHERE f1 < 2147483646 order by f1;
+
+SELECT '' AS five, i.f1, i.f1 - int2 '2' AS x FROM INT4_TBL i order by f1;
+
+SELECT '' AS five, i.f1, i.f1 - int2 '2' AS x FROM INT4_TBL i
+WHERE f1 > -2147483647 order by f1;
+
+SELECT '' AS five, i.f1, i.f1 - int4 '2' AS x FROM INT4_TBL i order by f1;
+
+SELECT '' AS five, i.f1, i.f1 - int4 '2' AS x FROM INT4_TBL i
+WHERE f1 > -2147483647 order by f1;
+
+SELECT '' AS five, i.f1, i.f1 / int2 '2' AS x FROM INT4_TBL i order by f1;
+
+SELECT '' AS five, i.f1, i.f1 / int4 '2' AS x FROM INT4_TBL i order by f1;
+
+--
+-- more complex expressions
+--
+
+-- variations on unary minus parsing
+SELECT -2+3 AS one;
+
+SELECT 4-2 AS two;
+
+SELECT 2- -1 AS three;
+
+SELECT 2 - -2 AS four;
+
+SELECT int2 '2' * int2 '2' = int2 '16' / int2 '4' AS true;
+
+SELECT int4 '2' * int2 '2' = int2 '16' / int4 '4' AS true;
+
+SELECT int2 '2' * int4 '2' = int4 '16' / int2 '4' AS true;
+
+SELECT int4 '1000' < int4 '999' AS false;
+
+SELECT 4! AS twenty_four;
+
+SELECT !!3 AS six;
+
+SELECT 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 AS ten;
+
+SELECT 2 + 2 / 2 AS three;
+
+SELECT (2 + 2) / 2 AS two;

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/5506a228/src/test/feature/catalog/sql/int8.sql
----------------------------------------------------------------------
diff --git a/src/test/feature/catalog/sql/int8.sql b/src/test/feature/catalog/sql/int8.sql
new file mode 100644
index 0000000..a545f54
--- /dev/null
+++ b/src/test/feature/catalog/sql/int8.sql
@@ -0,0 +1,71 @@
+--
+-- INT8
+-- Test int8 64-bit integers.
+--
+CREATE TABLE INT8_TBL(q1 int8, q2 int8);
+
+INSERT INTO INT8_TBL VALUES('  123   ','  456');
+INSERT INTO INT8_TBL VALUES('123   ','4567890123456789');
+INSERT INTO INT8_TBL VALUES('4567890123456789','123');
+INSERT INTO INT8_TBL VALUES('4567890123456789','4567890123456789');
+INSERT INTO INT8_TBL VALUES('4567890123456789','-4567890123456789');
+
+-- bad inputs
+INSERT INTO INT8_TBL(q1) VALUES ('      ');
+INSERT INTO INT8_TBL(q1) VALUES ('xxx');
+INSERT INTO INT8_TBL(q1) VALUES ('3908203590239580293850293850329485');
+INSERT INTO INT8_TBL(q1) VALUES ('-1204982019841029840928340329840934');
+INSERT INTO INT8_TBL(q1) VALUES ('- 123');
+INSERT INTO INT8_TBL(q1) VALUES ('  345     5');
+INSERT INTO INT8_TBL(q1) VALUES ('');
+
+SELECT * FROM INT8_TBL ;
+
+SELECT '' AS five, q1 AS plus, -q1 AS minus FROM INT8_TBL ;
+
+SELECT '' AS five, q1, q2, q1 + q2 AS plus FROM INT8_TBL ;
+SELECT '' AS five, q1, q2, q1 - q2 AS minus FROM INT8_TBL ;
+SELECT '' AS three, q1, q2, q1 * q2 AS multiply FROM INT8_TBL ;
+SELECT '' AS three, q1, q2, q1 * q2 AS multiply FROM INT8_TBL
+ WHERE q1 < 1000 or (q2 > 0 and q2 < 1000) ;
+SELECT '' AS five, q1, q2, q1 / q2 AS divide FROM INT8_TBL ;
+
+SELECT '' AS five, q1, float8(q1) FROM INT8_TBL ;
+SELECT '' AS five, q2, float8(q2) FROM INT8_TBL ;
+
+SELECT '' AS five, 2 * q1 AS "twice int4" FROM INT8_TBL ;
+SELECT '' AS five, q1 * 2 AS "twice int4" FROM INT8_TBL ;
+
+-- TO_CHAR()
+--
+SELECT '' AS to_char_1, to_char(q1, '9G999G999G999G999G999'), to_char(q2, '9,999,999,999,999,999') 
+	FROM INT8_TBL  ;
+
+SELECT '' AS to_char_2, to_char(q1, '9G999G999G999G999G999D999G999'), to_char(q2, '9,999,999,999,999,999.999,999') 
+	FROM INT8_TBL  ;
+
+SELECT '' AS to_char_3, to_char( (q1 * -1), '9999999999999999PR'), to_char( (q2 * -1), '9999999999999999.999PR') 
+	FROM INT8_TBL  ;
+
+SELECT '' AS to_char_4, to_char( (q1 * -1), '9999999999999999S'), to_char( (q2 * -1), 'S9999999999999999') 
+	FROM INT8_TBL  ;
+
+SELECT '' AS to_char_5,  to_char(q2, 'MI9999999999999999')     FROM INT8_TBL  ;
+SELECT '' AS to_char_6,  to_char(q2, 'FMS9999999999999999')    FROM INT8_TBL  ;
+SELECT '' AS to_char_7,  to_char(q2, 'FM9999999999999999THPR') FROM INT8_TBL ;
+SELECT '' AS to_char_8,  to_char(q2, 'SG9999999999999999th')   FROM INT8_TBL ;
+SELECT '' AS to_char_9,  to_char(q2, '0999999999999999')       FROM INT8_TBL ;
+SELECT '' AS to_char_10, to_char(q2, 'S0999999999999999')      FROM INT8_TBL ;
+SELECT '' AS to_char_11, to_char(q2, 'FM0999999999999999')     FROM INT8_TBL ;
+SELECT '' AS to_char_12, to_char(q2, 'FM9999999999999999.000') FROM INT8_TBL ;
+SELECT '' AS to_char_13, to_char(q2, 'L9999999999999999.000')  FROM INT8_TBL ;
+SELECT '' AS to_char_14, to_char(q2, 'FM9999999999999999.999') FROM INT8_TBL ;
+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 ;
+SELECT '' AS to_char_16, to_char(q2, E'99999 "text" 9999 "9999" 999 "\\"text between quote marks\\"" 9999') FROM INT8_TBL ;
+SELECT '' AS to_char_17, to_char(q2, '999999SG9999999999')     FROM INT8_TBL ;
+
+-- check min/max values
+select '-9223372036854775808'::int8;
+select '-9223372036854775809'::int8;
+select '9223372036854775807'::int8;
+select '9223372036854775808'::int8;

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/5506a228/src/test/feature/catalog/sql/money.sql
----------------------------------------------------------------------
diff --git a/src/test/feature/catalog/sql/money.sql b/src/test/feature/catalog/sql/money.sql
new file mode 100644
index 0000000..fd5921e
--- /dev/null
+++ b/src/test/feature/catalog/sql/money.sql
@@ -0,0 +1,68 @@
+--
+-- MONEY
+--
+
+CREATE TABLE MONEY_TBL (f1  money);
+
+INSERT INTO MONEY_TBL(f1) VALUES ('    0.0');
+INSERT INTO MONEY_TBL(f1) VALUES ('1004.30   ');
+INSERT INTO MONEY_TBL(f1) VALUES ('     -34.84    ');
+INSERT INTO MONEY_TBL(f1) VALUES ('123456789012345.67');
+
+-- test money over and under flow
+SELECT '12345678901234567890.12'::money = '-13639628150831692.60'::money as x;
+SELECT '123.001'::money = '123'::money as x;
+
+-- bad input
+INSERT INTO MONEY_TBL(f1) VALUES ('xyz');
+INSERT INTO MONEY_TBL(f1) VALUES ('5.0.0');
+INSERT INTO MONEY_TBL(f1) VALUES ('5 . 0');
+INSERT INTO MONEY_TBL(f1) VALUES ('5.   0');
+INSERT INTO MONEY_TBL(f1) VALUES ('123            5');
+
+-- queries
+SELECT '' AS five, * FROM MONEY_TBL ORDER BY 2;
+
+SELECT '' AS four, f.* FROM MONEY_TBL f WHERE f.f1 <> '1004.3' ORDER BY 2;
+
+SELECT '' AS one, f.* FROM MONEY_TBL f WHERE f.f1 = '1004.3' ORDER BY 2;
+
+SELECT '' AS three, f.* FROM MONEY_TBL f WHERE '1004.3' > f.f1 ORDER BY 2;
+
+SELECT '' AS three, f.* FROM MONEY_TBL f WHERE  f.f1 < '1004.3' ORDER BY 2;
+
+SELECT '' AS four, f.* FROM MONEY_TBL f WHERE '1004.3' >= f.f1 ORDER BY 2;
+
+SELECT '' AS four, f.* FROM MONEY_TBL f WHERE  f.f1 <= '1004.3' ORDER BY 2;
+
+SELECT '' AS three, f.f1, f.f1 * '-10' AS x FROM MONEY_TBL f
+   WHERE f.f1 > '0.0' ORDER BY 2;
+
+SELECT '' AS three, f.f1, f.f1 + '-10' AS x FROM MONEY_TBL f
+   WHERE f.f1 > '0.0' ORDER BY 2;
+
+SELECT '' AS three, f.f1, f.f1 / '-10' AS x FROM MONEY_TBL f
+   WHERE f.f1 > '0.0' ORDER BY 2;
+
+SELECT '' AS three, f.f1, f.f1 - '-10' AS x FROM MONEY_TBL f
+   WHERE f.f1 > '0.0' ORDER BY 2;
+
+SELECT SUM(f.f1) AS x FROM MONEY_TBL f;
+
+-- test divide by zero
+SELECT '' AS bad, f.f1 / '0.0' from MONEY_TBL f;
+
+SELECT '' AS five, * FROM MONEY_TBL ORDER BY 2;
+
+-- parquet table
+CREATE TABLE MONEY_TBL_P (f1 money) with (appendonly=true, orientation=parquet);
+
+INSERT INTO MONEY_TBL_P(f1) VALUES ('    0.0');
+INSERT INTO MONEY_TBL_P(f1) VALUES ('1004.30   ');
+INSERT INTO MONEY_TBL_P(f1) VALUES ('     -34.84    ');
+INSERT INTO MONEY_TBL_P(f1) VALUES ('123456789012345.67');
+
+SELECT f1 FROM MONEY_TBL_P f
+   ORDER BY f1;
+
+SELECT sum(f1) AS x, min(f1) as y, max(f1) as z FROM MONEY_TBL_P AS f;

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/5506a228/src/test/feature/catalog/sql/name.sql
----------------------------------------------------------------------
diff --git a/src/test/feature/catalog/sql/name.sql b/src/test/feature/catalog/sql/name.sql
new file mode 100644
index 0000000..d603f57
--- /dev/null
+++ b/src/test/feature/catalog/sql/name.sql
@@ -0,0 +1,54 @@
+--
+-- NAME
+-- all inputs are silently truncated at NAMEDATALEN-1 (63) characters
+--
+
+-- fixed-length by reference
+SELECT name 'name string' = name 'name string' AS "True";
+
+SELECT name 'name string' = name 'name string ' AS "False";
+
+--
+--
+--
+
+CREATE TABLE NAME_TBL(f1 name);
+
+INSERT INTO NAME_TBL(f1) VALUES ('1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQR');
+
+INSERT INTO NAME_TBL(f1) VALUES ('1234567890abcdefghijklmnopqrstuvwxyz1234567890abcdefghijklmnopqr');
+
+INSERT INTO NAME_TBL(f1) VALUES ('asdfghjkl;');
+
+INSERT INTO NAME_TBL(f1) VALUES ('343f%2a');
+
+INSERT INTO NAME_TBL(f1) VALUES ('d34aaasdf');
+
+INSERT INTO NAME_TBL(f1) VALUES ('');
+
+INSERT INTO NAME_TBL(f1) VALUES ('1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ');
+
+
+SELECT '' AS seven, * FROM NAME_TBL order by f1;
+
+SELECT '' AS six, c.f1 FROM NAME_TBL c WHERE c.f1 <> '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQR' order by f1;
+
+SELECT '' AS one, c.f1 FROM NAME_TBL c WHERE c.f1 = '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQR' order by f1;
+
+SELECT '' AS three, c.f1 FROM NAME_TBL c WHERE c.f1 < '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQR' order by f1;
+
+SELECT '' AS four, c.f1 FROM NAME_TBL c WHERE c.f1 <= '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQR' order by f1;
+
+SELECT '' AS three, c.f1 FROM NAME_TBL c WHERE c.f1 > '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQR' order by f1;
+
+SELECT '' AS four, c.f1 FROM NAME_TBL c WHERE c.f1 >= '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQR' order by f1;
+
+SELECT '' AS seven, c.f1 FROM NAME_TBL c WHERE c.f1 ~ '.*' order by f1;
+
+SELECT '' AS zero, c.f1 FROM NAME_TBL c WHERE c.f1 !~ '.*' order by f1;
+
+SELECT '' AS three, c.f1 FROM NAME_TBL c WHERE c.f1 ~ '[0-9]' order by f1;
+
+SELECT '' AS two, c.f1 FROM NAME_TBL c WHERE c.f1 ~ '.*asdf.*' order by f1;
+
+DROP TABLE NAME_TBL;

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/5506a228/src/test/feature/catalog/sql/oid.sql
----------------------------------------------------------------------
diff --git a/src/test/feature/catalog/sql/oid.sql b/src/test/feature/catalog/sql/oid.sql
new file mode 100644
index 0000000..5747b56
--- /dev/null
+++ b/src/test/feature/catalog/sql/oid.sql
@@ -0,0 +1,43 @@
+--
+-- OID
+--
+
+CREATE TABLE OID_TBL(f1 oid);
+
+INSERT INTO OID_TBL(f1) VALUES ('1234');
+INSERT INTO OID_TBL(f1) VALUES ('1235');
+INSERT INTO OID_TBL(f1) VALUES ('987');
+INSERT INTO OID_TBL(f1) VALUES ('-1040');
+INSERT INTO OID_TBL(f1) VALUES ('99999999');
+INSERT INTO OID_TBL(f1) VALUES ('5     ');
+INSERT INTO OID_TBL(f1) VALUES ('   10  ');
+-- leading/trailing hard tab is also allowed
+INSERT INTO OID_TBL(f1) VALUES ('	  15 	  ');
+
+-- bad inputs 
+INSERT INTO OID_TBL(f1) VALUES ('');
+INSERT INTO OID_TBL(f1) VALUES ('    ');
+INSERT INTO OID_TBL(f1) VALUES ('asdfasd');
+INSERT INTO OID_TBL(f1) VALUES ('99asdfasd');
+INSERT INTO OID_TBL(f1) VALUES ('5    d');
+INSERT INTO OID_TBL(f1) VALUES ('    5d');
+INSERT INTO OID_TBL(f1) VALUES ('5    5');
+INSERT INTO OID_TBL(f1) VALUES (' - 500');
+INSERT INTO OID_TBL(f1) VALUES ('32958209582039852935');
+INSERT INTO OID_TBL(f1) VALUES ('-23582358720398502385');
+
+SELECT '' AS six, * FROM OID_TBL order by 1, 2;
+
+SELECT '' AS one, o.* FROM OID_TBL o WHERE o.f1 = 1234 order by 1, 2;
+
+SELECT '' AS five, o.* FROM OID_TBL o WHERE o.f1 <> '1234' order by 1,2;
+
+SELECT '' AS three, o.* FROM OID_TBL o WHERE o.f1 <= '1234' order by 1,2;
+
+SELECT '' AS two, o.* FROM OID_TBL o WHERE o.f1 < '1234' order by 1,2;
+
+SELECT '' AS four, o.* FROM OID_TBL o WHERE o.f1 >= '1234'  order by 1,2;
+
+SELECT '' AS three, o.* FROM OID_TBL o WHERE o.f1 > '1234' order by 1,2;
+
+DROP TABLE OID_TBL;

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/5506a228/src/test/feature/catalog/sql/text.sql
----------------------------------------------------------------------
diff --git a/src/test/feature/catalog/sql/text.sql b/src/test/feature/catalog/sql/text.sql
new file mode 100644
index 0000000..c141e4b
--- /dev/null
+++ b/src/test/feature/catalog/sql/text.sql
@@ -0,0 +1,15 @@
+--
+-- TEXT
+--
+
+SELECT text 'this is a text string' = text 'this is a text string' AS true;
+
+SELECT text 'this is a text string' = text 'this is a text strin' AS false;
+
+CREATE TABLE TEXT_TBL (f1 text);
+
+INSERT INTO TEXT_TBL VALUES ('doh!');
+INSERT INTO TEXT_TBL VALUES ('hi de ho neighbor');
+
+SELECT '' AS two, * FROM TEXT_TBL order by f1;
+

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/5506a228/src/test/feature/catalog/sql/time.sql
----------------------------------------------------------------------
diff --git a/src/test/feature/catalog/sql/time.sql b/src/test/feature/catalog/sql/time.sql
new file mode 100644
index 0000000..02c4b7e
--- /dev/null
+++ b/src/test/feature/catalog/sql/time.sql
@@ -0,0 +1,41 @@
+--
+-- TIME
+--
+
+CREATE TABLE TIME_TBL (f1 time(2));
+
+INSERT INTO TIME_TBL VALUES ('00:00');
+INSERT INTO TIME_TBL VALUES ('01:00');
+-- as of 7.4, timezone spec should be accepted and ignored
+INSERT INTO TIME_TBL VALUES ('02:03 PST');
+INSERT INTO TIME_TBL VALUES ('11:59 EDT');
+INSERT INTO TIME_TBL VALUES ('12:00');
+INSERT INTO TIME_TBL VALUES ('12:01');
+INSERT INTO TIME_TBL VALUES ('23:59');
+INSERT INTO TIME_TBL VALUES ('11:59:59.99 PM');
+
+INSERT INTO TIME_TBL VALUES ('2003-03-07 15:36:39 America/New_York');
+INSERT INTO TIME_TBL VALUES ('2003-07-07 15:36:39 America/New_York');
+-- this should fail (the timezone offset is not known)
+INSERT INTO TIME_TBL VALUES ('15:36:39 America/New_York');
+
+SELECT f1 AS "Time" FROM TIME_TBL ORDER BY 1;
+
+SELECT f1 AS "Three" FROM TIME_TBL WHERE f1 < '05:06:07' ORDER BY 1;
+
+SELECT f1 AS "Five" FROM TIME_TBL WHERE f1 > '05:06:07' ORDER BY 1;
+
+SELECT f1 AS "None" FROM TIME_TBL WHERE f1 < '00:00' ORDER BY 1;
+
+SELECT f1 AS "Eight" FROM TIME_TBL WHERE f1 >= '00:00' ORDER BY 1;
+
+--
+-- TIME simple math
+--
+-- We now make a distinction between time and intervals,
+-- and adding two times together makes no sense at all.
+-- Leave in one query to show that it is rejected,
+-- and do the rest of the testing in horology.sql
+-- where we do mixed-type arithmetic. - thomas 2000-12-02
+
+SELECT f1 + time '00:01' AS "Illegal" FROM TIME_TBL;

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/5506a228/src/test/feature/catalog/sql/type_sanity.sql
----------------------------------------------------------------------
diff --git a/src/test/feature/catalog/sql/type_sanity.sql b/src/test/feature/catalog/sql/type_sanity.sql
new file mode 100644
index 0000000..14cc3b1
--- /dev/null
+++ b/src/test/feature/catalog/sql/type_sanity.sql
@@ -0,0 +1,223 @@
+--
+-- TYPE_SANITY
+-- Sanity checks for common errors in making type-related system tables:
+-- pg_type, pg_class, pg_attribute.
+--
+-- None of the SELECTs here should ever find any matching entries,
+-- so the expected output is easy to maintain ;-).
+-- A test failure indicates someone messed up an entry in the system tables.
+--
+-- NB: we assume the oidjoins test will have caught any dangling links,
+-- that is OID or REGPROC fields that are not zero and do not match some
+-- row in the linked-to table.  However, if we want to enforce that a link
+-- field can't be 0, we have to check it here.
+
+-- **************** pg_type ****************
+
+-- Look for illegal values in pg_type fields.
+
+SELECT p1.oid, p1.typname
+FROM pg_type as p1
+WHERE p1.typnamespace = 0 OR
+    (p1.typlen <= 0 AND p1.typlen != -1 AND p1.typlen != -2) OR
+    (p1.typtype not in ('b', 'c', 'd', 'p')) OR
+    NOT p1.typisdefined OR
+    (p1.typalign not in ('c', 's', 'i', 'd')) OR
+    (p1.typstorage not in ('p', 'x', 'e', 'm'));
+
+-- Look for "pass by value" types that can't be passed by value.
+
+SELECT p1.oid, p1.typname
+FROM pg_type as p1
+WHERE p1.typbyval AND
+    (p1.typlen != 1 OR p1.typalign != 'c') AND
+    (p1.typlen != 2 OR p1.typalign != 's') AND
+    (p1.typlen != 4 OR p1.typalign != 'i') AND
+	(p1.typlen != 8 OR p1.typalign != 'd') ;
+
+-- Look for "toastable" types that aren't varlena.
+
+SELECT p1.oid, p1.typname
+FROM pg_type as p1
+WHERE p1.typstorage != 'p' AND
+    (p1.typbyval OR p1.typlen != -1);
+
+-- Look for complex types that do not have a typrelid entry,
+-- or basic types that do.
+
+SELECT p1.oid, p1.typname
+FROM pg_type as p1
+WHERE (p1.typtype = 'c' AND p1.typrelid = 0) OR
+    (p1.typtype != 'c' AND p1.typrelid != 0);
+
+-- Look for basic types that don't have an array type.
+-- NOTE: as of 8.0, this check finds smgr and unknown.
+
+SELECT p1.oid, p1.typname
+FROM pg_type as p1
+WHERE p1.typtype in ('b') AND p1.typname NOT LIKE E'\\_%' AND NOT EXISTS
+    (SELECT 1 FROM pg_type as p2
+     WHERE p2.typname = ('_' || p1.typname)::name AND
+           p2.typelem = p1.oid);
+
+-- Text conversion routines must be provided.
+
+SELECT p1.oid, p1.typname
+FROM pg_type as p1
+WHERE (p1.typinput = 0 OR p1.typoutput = 0);
+
+-- Check for bogus typinput routines
+
+SELECT p1.oid, p1.typname, p2.oid, p2.proname
+FROM pg_type AS p1, pg_proc AS p2
+WHERE p1.typinput = p2.oid AND p1.typtype in ('b', 'p') AND NOT
+    ((p2.pronargs = 1 AND p2.proargtypes[0] = 'cstring'::regtype) OR
+     (p2.pronargs = 3 AND p2.proargtypes[0] = 'cstring'::regtype AND
+      p2.proargtypes[1] = 'oid'::regtype AND
+      p2.proargtypes[2] = 'int4'::regtype));
+
+-- As of 8.0, this check finds refcursor, which is borrowing
+-- other types' I/O routines
+SELECT p1.oid, p1.typname, p2.oid, p2.proname
+FROM pg_type AS p1, pg_proc AS p2
+WHERE p1.typinput = p2.oid AND p1.typtype in ('b', 'p') AND NOT
+    (p1.typelem != 0 AND p1.typlen < 0) AND NOT
+    (p2.prorettype = p1.oid AND NOT p2.proretset)
+ORDER BY 1;
+
+-- Varlena array types will point to array_in
+-- Exception as of 8.1: int2vector and oidvector have their own I/O routines
+SELECT p1.oid, p1.typname, p2.oid, p2.proname
+FROM pg_type AS p1, pg_proc AS p2
+WHERE p1.typinput = p2.oid AND p1.typtype in ('b', 'p') AND
+    (p1.typelem != 0 AND p1.typlen < 0) AND NOT
+    (p2.oid = 'array_in'::regproc)
+ORDER BY 1;
+
+-- Check for bogus typoutput routines
+
+-- As of 8.0, this check finds refcursor, which is borrowing
+-- other types' I/O routines
+SELECT p1.oid, p1.typname, p2.oid, p2.proname
+FROM pg_type AS p1, pg_proc AS p2
+WHERE p1.typoutput = p2.oid AND p1.typtype in ('b', 'p') AND NOT
+    (p2.pronargs = 1 AND
+     (p2.proargtypes[0] = p1.oid OR
+      (p2.oid = 'array_out'::regproc AND
+       p1.typelem != 0 AND p1.typlen = -1)))
+ORDER BY 1;
+
+SELECT p1.oid, p1.typname, p2.oid, p2.proname
+FROM pg_type AS p1, pg_proc AS p2
+WHERE p1.typoutput = p2.oid AND p1.typtype in ('b', 'p') AND NOT
+    (p2.prorettype = 'cstring'::regtype AND NOT p2.proretset);
+
+-- Check for bogus typreceive routines
+
+SELECT p1.oid, p1.typname, p2.oid, p2.proname
+FROM pg_type AS p1, pg_proc AS p2
+WHERE p1.typreceive = p2.oid AND p1.typtype in ('b', 'p') AND NOT
+    ((p2.pronargs = 1 AND p2.proargtypes[0] = 'internal'::regtype) OR
+     (p2.pronargs = 3 AND p2.proargtypes[0] = 'internal'::regtype AND
+      p2.proargtypes[1] = 'oid'::regtype AND
+      p2.proargtypes[2] = 'int4'::regtype));
+
+-- As of 7.4, this check finds refcursor, which is borrowing
+-- other types' I/O routines
+SELECT p1.oid, p1.typname, p2.oid, p2.proname
+FROM pg_type AS p1, pg_proc AS p2
+WHERE p1.typreceive = p2.oid AND p1.typtype in ('b', 'p') AND NOT
+    (p1.typelem != 0 AND p1.typlen < 0) AND NOT
+    (p2.prorettype = p1.oid AND NOT p2.proretset)
+ORDER BY 1;
+
+-- Varlena array types will point to array_recv
+-- Exception as of 8.1: int2vector and oidvector have their own I/O routines
+SELECT p1.oid, p1.typname, p2.oid, p2.proname
+FROM pg_type AS p1, pg_proc AS p2
+WHERE p1.typreceive = p2.oid AND p1.typtype in ('b', 'p') AND
+    (p1.typelem != 0 AND p1.typlen < 0) AND NOT
+    (p2.oid = 'array_recv'::regproc)
+ORDER BY 1;
+
+-- Array types should have same typdelim as their element types
+SELECT p1.oid, p1.typname, p2.oid, p2.typname
+FROM pg_type p1, pg_type p2
+WHERE p1.typelem = p2.oid and p1.typdelim != p2.typdelim
+  AND p1.typname like E'\\_%';
+
+
+-- Suspicious if typreceive doesn't take same number of args as typinput
+SELECT p1.oid, p1.typname, p2.oid, p2.proname, p3.oid, p3.proname
+FROM pg_type AS p1, pg_proc AS p2, pg_proc AS p3
+WHERE p1.typinput = p2.oid AND p1.typreceive = p3.oid AND
+    p2.pronargs != p3.pronargs;
+
+-- Check for bogus typsend routines
+
+-- As of 7.4, this check finds refcursor, which is borrowing
+-- other types' I/O routines
+SELECT p1.oid, p1.typname, p2.oid, p2.proname
+FROM pg_type AS p1, pg_proc AS p2
+WHERE p1.typsend = p2.oid AND p1.typtype in ('b', 'p') AND NOT
+    (p2.pronargs = 1 AND
+     (p2.proargtypes[0] = p1.oid OR
+      (p2.oid = 'array_send'::regproc AND
+       p1.typelem != 0 AND p1.typlen = -1)))
+ORDER BY 1;
+
+SELECT p1.oid, p1.typname, p2.oid, p2.proname
+FROM pg_type AS p1, pg_proc AS p2
+WHERE p1.typsend = p2.oid AND p1.typtype in ('b', 'p') AND NOT
+    (p2.prorettype = 'bytea'::regtype AND NOT p2.proretset);
+
+-- **************** pg_class ****************
+
+-- Look for illegal values in pg_class fields
+
+SELECT p1.oid, p1.relname
+FROM pg_class as p1
+WHERE p1.relkind NOT IN ('r', 'i', 'S', 'u', 't', 'o', 'b', 'v', 'c');
+
+-- Indexes should have an access method, others not.
+
+SELECT p1.oid, p1.relname
+FROM pg_class as p1
+WHERE (p1.relkind = 'i' AND p1.relam = 0) OR
+    (p1.relkind != 'i' AND p1.relam != 0);
+
+-- **************** pg_attribute ****************
+
+-- Look for illegal values in pg_attribute fields
+
+SELECT p1.attrelid, p1.attname
+FROM pg_attribute as p1
+WHERE p1.attrelid = 0 OR p1.atttypid = 0 OR p1.attnum = 0 OR
+    p1.attcacheoff != -1 OR p1.attinhcount < 0 OR
+    (p1.attinhcount = 0 AND NOT p1.attislocal);
+
+-- Cross-check attnum against parent relation
+
+SELECT p1.attrelid, p1.attname, p2.oid, p2.relname
+FROM pg_attribute AS p1, pg_class AS p2
+WHERE p1.attrelid = p2.oid AND p1.attnum > p2.relnatts;
+
+-- Detect missing pg_attribute entries: should have as many non-system
+-- attributes as parent relation expects
+
+SELECT p1.oid, p1.relname
+FROM pg_class AS p1
+WHERE p1.relnatts != (SELECT count(*) FROM pg_attribute AS p2
+                      WHERE p2.attrelid = p1.oid AND p2.attnum > 0);
+
+-- Cross-check against pg_type entry
+-- NOTE: we allow attstorage to be 'plain' even when typstorage is not;
+-- this is mainly for toast tables.
+-- UNDONE: Turn this off until we can figure out why the new system columns cause a bunch of rows to be generated here???
+-- SELECT p1.attrelid, p1.attname, p2.oid, p2.typname
+-- FROM pg_attribute AS p1, pg_type AS p2
+-- WHERE p1.atttypid = p2.oid AND
+--    (p1.attlen != p2.typlen OR
+--     p1.attalign != p2.typalign OR
+--     p1.attbyval != p2.typbyval OR
+--     (p1.attstorage != p2.typstorage AND p1.attstorage != 'p'));

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/5506a228/src/test/feature/catalog/sql/varchar.sql
----------------------------------------------------------------------
diff --git a/src/test/feature/catalog/sql/varchar.sql b/src/test/feature/catalog/sql/varchar.sql
new file mode 100644
index 0000000..414c585
--- /dev/null
+++ b/src/test/feature/catalog/sql/varchar.sql
@@ -0,0 +1,66 @@
+--
+-- VARCHAR
+--
+
+CREATE TABLE VARCHAR_TBL(f1 varchar(1));
+
+INSERT INTO VARCHAR_TBL (f1) VALUES ('a');
+
+INSERT INTO VARCHAR_TBL (f1) VALUES ('A');
+
+-- any of the following three input formats are acceptable 
+INSERT INTO VARCHAR_TBL (f1) VALUES ('1');
+
+INSERT INTO VARCHAR_TBL (f1) VALUES (2);
+
+INSERT INTO VARCHAR_TBL (f1) VALUES ('3');
+
+-- zero-length char 
+INSERT INTO VARCHAR_TBL (f1) VALUES ('');
+
+-- try varchar's of greater than 1 length 
+INSERT INTO VARCHAR_TBL (f1) VALUES ('cd');
+INSERT INTO VARCHAR_TBL (f1) VALUES ('c     ');
+
+
+SELECT '' AS seven, * FROM VARCHAR_TBL;
+
+SELECT '' AS six, c.*
+   FROM VARCHAR_TBL c
+   WHERE c.f1 <> 'a';
+
+SELECT '' AS one, c.*
+   FROM VARCHAR_TBL c
+   WHERE c.f1 = 'a';
+
+SELECT '' AS five, c.*
+   FROM VARCHAR_TBL c
+   WHERE c.f1 < 'a';
+
+SELECT '' AS six, c.*
+   FROM VARCHAR_TBL c
+   WHERE c.f1 <= 'a';
+
+SELECT '' AS one, c.*
+   FROM VARCHAR_TBL c
+   WHERE c.f1 > 'a';
+
+SELECT '' AS two, c.*
+   FROM VARCHAR_TBL c
+   WHERE c.f1 >= 'a';
+
+DROP TABLE VARCHAR_TBL;
+
+--
+-- Now test longer arrays of char
+--
+
+CREATE TABLE VARCHAR_TBL(f1 varchar(4));
+
+INSERT INTO VARCHAR_TBL (f1) VALUES ('a');
+INSERT INTO VARCHAR_TBL (f1) VALUES ('ab');
+INSERT INTO VARCHAR_TBL (f1) VALUES ('abcd');
+INSERT INTO VARCHAR_TBL (f1) VALUES ('abcde');
+INSERT INTO VARCHAR_TBL (f1) VALUES ('abcd    ');
+
+SELECT '' AS four, * FROM VARCHAR_TBL;

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/5506a228/src/test/feature/catalog/test_type.cpp
----------------------------------------------------------------------
diff --git a/src/test/feature/catalog/test_type.cpp b/src/test/feature/catalog/test_type.cpp
new file mode 100644
index 0000000..f8bed88
--- /dev/null
+++ b/src/test/feature/catalog/test_type.cpp
@@ -0,0 +1,55 @@
+#include "gtest/gtest.h"
+
+#include "lib/sql_util.h"
+
+using std::string;
+
+class TestType: public ::testing::Test
+{
+	public:
+		TestType() {};
+		~TestType() {};
+};
+
+#define TEST_F_FILE(TestName, basePath, testcase)	\
+TEST_F(TestName, testcase)							\
+{													\
+	hawq::test::SQLUtility util;					\
+	string SqlFile(basePath);						\
+	string AnsFile(basePath);						\
+	SqlFile += "/sql/" #testcase ".sql";			\
+	AnsFile += "/ans/" #testcase ".ans";			\
+	util.execSQLFile(SqlFile, AnsFile);				\
+}
+
+#define TEST_F_FILE_TYPE(testcase) TEST_F_FILE(TestType, "catalog", testcase)
+
+TEST_F_FILE_TYPE(boolean)
+
+TEST_F_FILE_TYPE(char)
+
+TEST_F_FILE_TYPE(date)
+
+TEST_F_FILE_TYPE(float4)
+
+TEST_F_FILE_TYPE(float8)
+
+TEST_F_FILE_TYPE(int2)
+
+TEST_F_FILE_TYPE(int4)
+
+TEST_F_FILE_TYPE(int8)
+
+TEST_F_FILE_TYPE(money)
+
+TEST_F_FILE_TYPE(name)
+
+TEST_F_FILE_TYPE(oid)
+
+TEST_F_FILE_TYPE(text)
+
+TEST_F_FILE_TYPE(time)
+
+TEST_F_FILE_TYPE(type_sanity)
+
+TEST_F_FILE_TYPE(varchar)


Mime
View raw message