Return-Path: X-Original-To: archive-asf-public-internal@cust-asf2.ponee.io Delivered-To: archive-asf-public-internal@cust-asf2.ponee.io Received: from cust-asf.ponee.io (cust-asf.ponee.io [163.172.22.183]) by cust-asf2.ponee.io (Postfix) with ESMTP id 8C076200B64 for ; Tue, 19 Jul 2016 04:50:43 +0200 (CEST) Received: by cust-asf.ponee.io (Postfix) id 8A621160A87; Tue, 19 Jul 2016 02:50:43 +0000 (UTC) Delivered-To: archive-asf-public@cust-asf.ponee.io Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by cust-asf.ponee.io (Postfix) with SMTP id 3CB61160A5D for ; Tue, 19 Jul 2016 04:50:41 +0200 (CEST) Received: (qmail 94854 invoked by uid 500); 19 Jul 2016 02:50:40 -0000 Mailing-List: contact commits-help@hawq.incubator.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: dev@hawq.incubator.apache.org Delivered-To: mailing list commits@hawq.incubator.apache.org Received: (qmail 94845 invoked by uid 99); 19 Jul 2016 02:50:40 -0000 Received: from pnap-us-west-generic-nat.apache.org (HELO spamd3-us-west.apache.org) (209.188.14.142) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 19 Jul 2016 02:50:40 +0000 Received: from localhost (localhost [127.0.0.1]) by spamd3-us-west.apache.org (ASF Mail Server at spamd3-us-west.apache.org) with ESMTP id BE13B1812B0 for ; Tue, 19 Jul 2016 02:50:39 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at spamd3-us-west.apache.org X-Spam-Flag: NO X-Spam-Score: -4.646 X-Spam-Level: X-Spam-Status: No, score=-4.646 tagged_above=-999 required=6.31 tests=[KAM_ASCII_DIVIDERS=0.8, KAM_LAZY_DOMAIN_SECURITY=1, RCVD_IN_DNSWL_HI=-5, RCVD_IN_MSPIKE_H3=-0.01, RCVD_IN_MSPIKE_WL=-0.01, RP_MATCHES_RCVD=-1.426] autolearn=disabled Received: from mx1-lw-us.apache.org ([10.40.0.8]) by localhost (spamd3-us-west.apache.org [10.40.0.10]) (amavisd-new, port 10024) with ESMTP id waJo3OXbFoLv for ; Tue, 19 Jul 2016 02:50:30 +0000 (UTC) Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by mx1-lw-us.apache.org (ASF Mail Server at mx1-lw-us.apache.org) with SMTP id 5F3D25FD1B for ; Tue, 19 Jul 2016 02:50:27 +0000 (UTC) Received: (qmail 94178 invoked by uid 99); 19 Jul 2016 02:50:27 -0000 Received: from git1-us-west.apache.org (HELO git1-us-west.apache.org) (140.211.11.23) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 19 Jul 2016 02:50:27 +0000 Received: by git1-us-west.apache.org (ASF Mail Server at git1-us-west.apache.org, from userid 33) id D0BA8DFBAB; Tue, 19 Jul 2016 02:50:26 +0000 (UTC) Content-Type: text/plain; charset="us-ascii" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit From: rlei@apache.org To: commits@hawq.incubator.apache.org Date: Tue, 19 Jul 2016 02:50:27 -0000 Message-Id: <0f35e53733674be8ab891cfe5684ebb1@git.apache.org> In-Reply-To: References: X-Mailer: ASF-Git Admin Mailer Subject: [02/14] incubator-hawq git commit: HAWQ-917. Refactor feature tests for data type check with new googletest framework archived-at: Tue, 19 Jul 2016 02:50:43 -0000 http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/f05c19ed/src/test/regress/expected/name.out ---------------------------------------------------------------------- diff --git a/src/test/regress/expected/name.out b/src/test/regress/expected/name.out deleted file mode 100755 index 2a3ceb4..0000000 --- a/src/test/regress/expected/name.out +++ /dev/null @@ -1,126 +0,0 @@ --- --- 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); -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; - 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; http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/f05c19ed/src/test/regress/expected/oid.out ---------------------------------------------------------------------- diff --git a/src/test/regress/expected/oid.out b/src/test/regress/expected/oid.out deleted file mode 100755 index 2119262..0000000 --- a/src/test/regress/expected/oid.out +++ /dev/null @@ -1,102 +0,0 @@ --- --- 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 (''); -ERROR: invalid input syntax for type oid: "" -INSERT INTO OID_TBL(f1) VALUES (' '); -ERROR: invalid input syntax for type oid: " " -INSERT INTO OID_TBL(f1) VALUES ('asdfasd'); -ERROR: invalid input syntax for type oid: "asdfasd" -INSERT INTO OID_TBL(f1) VALUES ('99asdfasd'); -ERROR: invalid input syntax for type oid: "99asdfasd" -INSERT INTO OID_TBL(f1) VALUES ('5 d'); -ERROR: invalid input syntax for type oid: "5 d" -INSERT INTO OID_TBL(f1) VALUES (' 5d'); -ERROR: invalid input syntax for type oid: " 5d" -INSERT INTO OID_TBL(f1) VALUES ('5 5'); -ERROR: invalid input syntax for type oid: "5 5" -INSERT INTO OID_TBL(f1) VALUES (' - 500'); -ERROR: invalid input syntax for type oid: " - 500" -INSERT INTO OID_TBL(f1) VALUES ('32958209582039852935'); -ERROR: value "32958209582039852935" is out of range for type oid -INSERT INTO OID_TBL(f1) VALUES ('-23582358720398502385'); -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; http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/f05c19ed/src/test/regress/expected/text.out ---------------------------------------------------------------------- diff --git a/src/test/regress/expected/text.out b/src/test/regress/expected/text.out deleted file mode 100755 index 4d6347d..0000000 --- a/src/test/regress/expected/text.out +++ /dev/null @@ -1,25 +0,0 @@ --- --- 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); -INSERT INTO TEXT_TBL VALUES ('doh!'); -INSERT INTO TEXT_TBL VALUES ('hi de ho neighbor'); -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/regress/expected/time.out ---------------------------------------------------------------------- diff --git a/src/test/regress/expected/time.out b/src/test/regress/expected/time.out deleted file mode 100755 index 5881799..0000000 --- a/src/test/regress/expected/time.out +++ /dev/null @@ -1,86 +0,0 @@ --- --- 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'); -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; -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/regress/expected/type_sanity.out ---------------------------------------------------------------------- diff --git a/src/test/regress/expected/type_sanity.out b/src/test/regress/expected/type_sanity.out deleted file mode 100755 index aa81910..0000000 --- a/src/test/regress/expected/type_sanity.out +++ /dev/null @@ -1,282 +0,0 @@ --- --- 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', 'S', 'c', 't', 'v'); - 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/regress/expected/varchar.out ---------------------------------------------------------------------- diff --git a/src/test/regress/expected/varchar.out b/src/test/regress/expected/varchar.out deleted file mode 100755 index 48a77f5..0000000 --- a/src/test/regress/expected/varchar.out +++ /dev/null @@ -1,111 +0,0 @@ --- --- 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'); -ERROR: value too long for type character varying(1) -INSERT INTO VARCHAR_TBL (f1) VALUES ('c '); -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; --- --- 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'); -ERROR: value too long for type character varying(4) -INSERT INTO VARCHAR_TBL (f1) VALUES ('abcd '); -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/regress/sql/boolean.sql ---------------------------------------------------------------------- diff --git a/src/test/regress/sql/boolean.sql b/src/test/regress/sql/boolean.sql deleted file mode 100644 index 4282c15..0000000 --- a/src/test/regress/sql/boolean.sql +++ /dev/null @@ -1,149 +0,0 @@ --- --- 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/regress/sql/char.sql ---------------------------------------------------------------------- diff --git a/src/test/regress/sql/char.sql b/src/test/regress/sql/char.sql deleted file mode 100644 index fcaef7e..0000000 --- a/src/test/regress/sql/char.sql +++ /dev/null @@ -1,75 +0,0 @@ --- --- 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/regress/sql/date.sql ---------------------------------------------------------------------- diff --git a/src/test/regress/sql/date.sql b/src/test/regress/sql/date.sql deleted file mode 100644 index 7ed6e15..0000000 --- a/src/test/regress/sql/date.sql +++ /dev/null @@ -1,271 +0,0 @@ --- --- 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/regress/sql/float4.sql ---------------------------------------------------------------------- diff --git a/src/test/regress/sql/float4.sql b/src/test/regress/sql/float4.sql deleted file mode 100644 index f33c6d2..0000000 --- a/src/test/regress/sql/float4.sql +++ /dev/null @@ -1,85 +0,0 @@ --- --- 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/regress/sql/float8.sql ---------------------------------------------------------------------- diff --git a/src/test/regress/sql/float8.sql b/src/test/regress/sql/float8.sql deleted file mode 100644 index 7809203..0000000 --- a/src/test/regress/sql/float8.sql +++ /dev/null @@ -1,167 +0,0 @@ --- --- 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/regress/sql/int2.sql ---------------------------------------------------------------------- diff --git a/src/test/regress/sql/int2.sql b/src/test/regress/sql/int2.sql deleted file mode 100644 index 082bb5c..0000000 --- a/src/test/regress/sql/int2.sql +++ /dev/null @@ -1,88 +0,0 @@ --- --- 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/regress/sql/int4.sql ---------------------------------------------------------------------- diff --git a/src/test/regress/sql/int4.sql b/src/test/regress/sql/int4.sql deleted file mode 100644 index d1b4225..0000000 --- a/src/test/regress/sql/int4.sql +++ /dev/null @@ -1,127 +0,0 @@ --- --- 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/regress/sql/int8.sql ---------------------------------------------------------------------- diff --git a/src/test/regress/sql/int8.sql b/src/test/regress/sql/int8.sql deleted file mode 100644 index a545f54..0000000 --- a/src/test/regress/sql/int8.sql +++ /dev/null @@ -1,71 +0,0 @@ --- --- 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/regress/sql/money.sql ---------------------------------------------------------------------- diff --git a/src/test/regress/sql/money.sql b/src/test/regress/sql/money.sql deleted file mode 100644 index fd5921e..0000000 --- a/src/test/regress/sql/money.sql +++ /dev/null @@ -1,68 +0,0 @@ --- --- 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/regress/sql/name.sql ---------------------------------------------------------------------- diff --git a/src/test/regress/sql/name.sql b/src/test/regress/sql/name.sql deleted file mode 100644 index d603f57..0000000 --- a/src/test/regress/sql/name.sql +++ /dev/null @@ -1,54 +0,0 @@ --- --- 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/regress/sql/oid.sql ---------------------------------------------------------------------- diff --git a/src/test/regress/sql/oid.sql b/src/test/regress/sql/oid.sql deleted file mode 100644 index 5747b56..0000000 --- a/src/test/regress/sql/oid.sql +++ /dev/null @@ -1,43 +0,0 @@ --- --- 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/regress/sql/text.sql ---------------------------------------------------------------------- diff --git a/src/test/regress/sql/text.sql b/src/test/regress/sql/text.sql deleted file mode 100644 index c141e4b..0000000 --- a/src/test/regress/sql/text.sql +++ /dev/null @@ -1,15 +0,0 @@ --- --- 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/regress/sql/time.sql ---------------------------------------------------------------------- diff --git a/src/test/regress/sql/time.sql b/src/test/regress/sql/time.sql deleted file mode 100644 index 02c4b7e..0000000 --- a/src/test/regress/sql/time.sql +++ /dev/null @@ -1,41 +0,0 @@ --- --- 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/regress/sql/type_sanity.sql ---------------------------------------------------------------------- diff --git a/src/test/regress/sql/type_sanity.sql b/src/test/regress/sql/type_sanity.sql deleted file mode 100644 index e5531ba..0000000 --- a/src/test/regress/sql/type_sanity.sql +++ /dev/null @@ -1,223 +0,0 @@ --- --- 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', 'S', 'c', 't', 'v'); - --- 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/regress/sql/varchar.sql ---------------------------------------------------------------------- diff --git a/src/test/regress/sql/varchar.sql b/src/test/regress/sql/varchar.sql deleted file mode 100644 index 414c585..0000000 --- a/src/test/regress/sql/varchar.sql +++ /dev/null @@ -1,66 +0,0 @@ --- --- 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;