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)