You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@hawq.apache.org by rl...@apache.org on 2016/07/19 02:50:29 UTC
[04/14] incubator-hawq git commit: HAWQ-917. Refactor feature tests
for data type check with new googletest framework
http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/f05c19ed/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/f05c19ed/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/f05c19ed/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/f05c19ed/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/f05c19ed/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/f05c19ed/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/f05c19ed/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/f05c19ed/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/f05c19ed/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/f05c19ed/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/f05c19ed/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/f05c19ed/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/f05c19ed/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/f05c19ed/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/f05c19ed/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/f05c19ed/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/f05c19ed/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/f05c19ed/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/f05c19ed/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/f05c19ed/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/f05c19ed/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/f05c19ed/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/f05c19ed/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)