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 74536200B64 for ; Tue, 19 Jul 2016 04:50:46 +0200 (CEST) Received: by cust-asf.ponee.io (Postfix) id 72D5D160A65; Tue, 19 Jul 2016 02:50:46 +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 266F6160A5D for ; Tue, 19 Jul 2016 04:50:44 +0200 (CEST) Received: (qmail 95293 invoked by uid 500); 19 Jul 2016 02:50:43 -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 95284 invoked by uid 99); 19 Jul 2016 02:50:43 -0000 Received: from pnap-us-west-generic-nat.apache.org (HELO spamd2-us-west.apache.org) (209.188.14.142) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 19 Jul 2016 02:50:43 +0000 Received: from localhost (localhost [127.0.0.1]) by spamd2-us-west.apache.org (ASF Mail Server at spamd2-us-west.apache.org) with ESMTP id AEAC71A0573 for ; Tue, 19 Jul 2016 02:50:42 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at spamd2-us-west.apache.org X-Spam-Flag: NO X-Spam-Score: -4.645 X-Spam-Level: X-Spam-Status: No, score=-4.645 tagged_above=-999 required=6.31 tests=[KAM_ASCII_DIVIDERS=0.8, KAM_LAZY_DOMAIN_SECURITY=1, LOTS_OF_MONEY=0.001, 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 mx2-lw-eu.apache.org ([10.40.0.8]) by localhost (spamd2-us-west.apache.org [10.40.0.9]) (amavisd-new, port 10024) with ESMTP id JWca0GIb4ZPN for ; Tue, 19 Jul 2016 02:50:31 +0000 (UTC) Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by mx2-lw-eu.apache.org (ASF Mail Server at mx2-lw-eu.apache.org) with SMTP id 190C160CE6 for ; Tue, 19 Jul 2016 02:50:27 +0000 (UTC) Received: (qmail 94183 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 D6516DFE65; 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:28 -0000 Message-Id: <34ede5bfe262461e8762cccc6b68c7ee@git.apache.org> In-Reply-To: References: X-Mailer: ASF-Git Admin Mailer Subject: [03/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:46 -0000 http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/f05c19ed/src/test/regress/expected/boolean.out ---------------------------------------------------------------------- diff --git a/src/test/regress/expected/boolean.out b/src/test/regress/expected/boolean.out deleted file mode 100755 index 8c6b5d2..0000000 --- a/src/test/regress/expected/boolean.out +++ /dev/null @@ -1,289 +0,0 @@ --- --- BOOLEAN --- --- --- sanity check - if this fails go insane! --- -SELECT 1 AS one; - one ------ - 1 -(1 row) - --- ******************testing built-in type bool******************** --- check bool type-casting as well as and, or, not in qualifications-- -SELECT bool 't' AS true; - true ------- - t -(1 row) - -SELECT bool 'f' AS false; - false -------- - f -(1 row) - -SELECT bool 't' or bool 'f' AS true; - true ------- - t -(1 row) - -SELECT bool 't' and bool 'f' AS false; - false -------- - f -(1 row) - -SELECT not bool 'f' AS true; - true ------- - t -(1 row) - -SELECT bool 't' = bool 'f' AS false; - false -------- - f -(1 row) - -SELECT bool 't' <> bool 'f' AS true; - true ------- - t -(1 row) - -CREATE TABLE BOOLTBL1 (f1 bool); -NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'f1' as the Greenplum Database data distribution key for this table. -HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. -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; - t_3 | f1 ------+---- - | t - | t - | t -(3 rows) - -SELECT '' AS t_3, BOOLTBL1.* - FROM BOOLTBL1 - WHERE f1 = bool 'true'; - t_3 | f1 ------+---- - | t - | t - | t -(3 rows) - -SELECT '' AS t_3, BOOLTBL1.* - FROM BOOLTBL1 - WHERE f1 <> bool 'false'; - t_3 | f1 ------+---- - | t - | t - | t -(3 rows) - -SELECT '' AS zero, BOOLTBL1.* - FROM BOOLTBL1 - WHERE booleq(bool 'false', f1); - zero | f1 -------+---- -(0 rows) - -INSERT INTO BOOLTBL1 (f1) VALUES (bool 'f'); -SELECT '' AS f_1, BOOLTBL1.* - FROM BOOLTBL1 - WHERE f1 = bool 'false'; - f_1 | f1 ------+---- - | f -(1 row) - -CREATE TABLE BOOLTBL2 (f1 bool); -NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'f1' as the Greenplum Database data distribution key for this table. -HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. -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'); -ERROR: invalid input syntax for type boolean: "XXX" -LINE 2: VALUES (bool 'XXX'); - ^ --- BOOLTBL2 should be full of false's at this point -SELECT '' AS f_4, BOOLTBL2.* FROM BOOLTBL2; - f_4 | f1 ------+---- - | f - | f - | f - | f -(4 rows) - -SELECT '' AS tf_12, BOOLTBL1.*, BOOLTBL2.* - FROM BOOLTBL1, BOOLTBL2 - WHERE BOOLTBL2.f1 <> BOOLTBL1.f1; - tf_12 | f1 | f1 --------+----+---- - | t | f - | t | f - | t | f - | t | f - | t | f - | t | f - | t | f - | t | f - | t | f - | t | f - | t | f - | t | f -(12 rows) - -SELECT '' AS tf_12, BOOLTBL1.*, BOOLTBL2.* - FROM BOOLTBL1, BOOLTBL2 - WHERE boolne(BOOLTBL2.f1,BOOLTBL1.f1); - tf_12 | f1 | f1 --------+----+---- - | t | f - | t | f - | t | f - | t | f - | t | f - | t | f - | t | f - | t | f - | t | f - | t | f - | t | f - | t | f -(12 rows) - -SELECT '' AS ff_4, BOOLTBL1.*, BOOLTBL2.* - FROM BOOLTBL1, BOOLTBL2 - WHERE BOOLTBL2.f1 = BOOLTBL1.f1 and BOOLTBL1.f1 = bool 'false' ; - ff_4 | f1 | f1 -------+----+---- - | f | f - | f | f - | f | f - | f | f -(4 rows) - -SELECT '' AS tf_12_ff_4, BOOLTBL1.*, BOOLTBL2.* - FROM BOOLTBL1, BOOLTBL2 - WHERE BOOLTBL2.f1 = BOOLTBL1.f1 or BOOLTBL1.f1 = bool 'true' - ORDER BY BOOLTBL1.f1, BOOLTBL2.f1 ; - tf_12_ff_4 | f1 | f1 -------------+----+---- - | f | f - | f | f - | f | f - | f | f - | t | f - | t | f - | t | f - | t | f - | t | f - | t | f - | t | f - | t | f - | t | f - | t | f - | t | f - | t | f -(16 rows) - --- --- SQL92 syntax --- Try all combinations to ensure that we get nothing when we expect nothing --- - thomas 2000-01-04 --- -SELECT '' AS "True", f1 - FROM BOOLTBL1 - WHERE f1 IS TRUE; - True | f1 -------+---- - | t - | t - | t -(3 rows) - -SELECT '' AS "Not False", f1 - FROM BOOLTBL1 - WHERE f1 IS NOT FALSE; - Not False | f1 ------------+---- - | t - | t - | t -(3 rows) - -SELECT '' AS "False", f1 - FROM BOOLTBL1 - WHERE f1 IS FALSE; - False | f1 --------+---- - | f -(1 row) - -SELECT '' AS "Not True", f1 - FROM BOOLTBL1 - WHERE f1 IS NOT TRUE; - Not True | f1 -----------+---- - | f -(1 row) - -SELECT '' AS "True", f1 - FROM BOOLTBL2 - WHERE f1 IS TRUE; - True | f1 -------+---- -(0 rows) - -SELECT '' AS "Not False", f1 - FROM BOOLTBL2 - WHERE f1 IS NOT FALSE; - Not False | f1 ------------+---- -(0 rows) - -SELECT '' AS "False", f1 - FROM BOOLTBL2 - WHERE f1 IS FALSE; - False | f1 --------+---- - | f - | f - | f - | f -(4 rows) - -SELECT '' AS "Not True", f1 - FROM BOOLTBL2 - WHERE f1 IS NOT TRUE; - Not True | f1 -----------+---- - | f - | f - | f - | f -(4 rows) - --- --- Clean up --- Many tables are retained by the regression test, but these do not seem --- particularly useful so just get rid of them for now. --- - thomas 1997-11-30 --- -DROP TABLE BOOLTBL1; -DROP TABLE BOOLTBL2; http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/f05c19ed/src/test/regress/expected/char.out ---------------------------------------------------------------------- diff --git a/src/test/regress/expected/char.out b/src/test/regress/expected/char.out deleted file mode 100755 index a0ba3d4..0000000 --- a/src/test/regress/expected/char.out +++ /dev/null @@ -1,122 +0,0 @@ --- --- CHAR --- --- fixed-length by value --- internally passed by value if <= 4 bytes in storage -SELECT char 'c' = char 'c' AS true; - true ------- - t -(1 row) - --- --- Build a table for testing --- -CREATE TABLE CHAR_TBL(f1 char); -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'); -ERROR: value too long for type character(1) -INSERT INTO CHAR_TBL (f1) VALUES ('c '); -SELECT '' AS seven, * FROM CHAR_TBL; - seven | f1 --------+---- - | a - | A - | 1 - | 2 - | 3 - | - | c -(7 rows) - -SELECT '' AS six, c.* - FROM CHAR_TBL c - WHERE c.f1 <> 'a'; - six | f1 ------+---- - | A - | 1 - | 2 - | 3 - | - | c -(6 rows) - -SELECT '' AS one, c.* - FROM CHAR_TBL c - WHERE c.f1 = 'a'; - one | f1 ------+---- - | a -(1 row) - -SELECT '' AS five, c.* - FROM CHAR_TBL c - WHERE c.f1 < 'a'; - five | f1 -------+---- - | A - | 1 - | 2 - | 3 - | -(5 rows) - -SELECT '' AS six, c.* - FROM CHAR_TBL c - WHERE c.f1 <= 'a'; - six | f1 ------+---- - | a - | A - | 1 - | 2 - | 3 - | -(6 rows) - -SELECT '' AS one, c.* - FROM CHAR_TBL c - WHERE c.f1 > 'a'; - one | f1 ------+---- - | c -(1 row) - -SELECT '' AS two, c.* - FROM CHAR_TBL c - WHERE c.f1 >= 'a'; - two | f1 ------+---- - | a - | c -(2 rows) - -DROP TABLE CHAR_TBL; --- --- 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'); -ERROR: value too long for type character(4) -INSERT INTO CHAR_TBL (f1) VALUES ('abcd '); -SELECT '' AS four, * FROM CHAR_TBL; - four | f1 -------+------ - | a - | ab - | abcd - | abcd -(4 rows) - http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/f05c19ed/src/test/regress/expected/date.out ---------------------------------------------------------------------- diff --git a/src/test/regress/expected/date.out b/src/test/regress/expected/date.out deleted file mode 100755 index 33b5745..0000000 --- a/src/test/regress/expected/date.out +++ /dev/null @@ -1,1157 +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'); -ERROR: date/time field value out of range: "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; - Fifteen ------------- - 04-09-1957 - 06-13-1957 - 02-28-1996 - 02-29-1996 - 03-01-1996 - 03-02-1996 - 02-28-1997 - 03-01-1997 - 03-02-1997 - 04-01-2000 - 04-02-2000 - 04-03-2000 - 04-08-2038 - 04-09-2039 - 04-10-2040 -(15 rows) - -SELECT f1 AS "Nine" FROM DATE_TBL WHERE f1 < '2000-01-01' ORDER BY 1; - Nine ------------- - 04-09-1957 - 06-13-1957 - 02-28-1996 - 02-29-1996 - 03-01-1996 - 03-02-1996 - 02-28-1997 - 03-01-1997 - 03-02-1997 -(9 rows) - -SELECT f1 AS "Three" FROM DATE_TBL - WHERE f1 BETWEEN '2000-01-01' AND '2001-01-01' ORDER BY 1; - Three ------------- - 04-01-2000 - 04-02-2000 - 04-03-2000 -(3 rows) - --- --- Check all the documented input formats --- -SET datestyle TO iso; -- display results in ISO -SET datestyle TO ymd; -SELECT date 'January 8, 1999'; - date ------------- - 1999-01-08 -(1 row) - -SELECT date '1999-01-08'; - date ------------- - 1999-01-08 -(1 row) - -SELECT date '1999-01-18'; - date ------------- - 1999-01-18 -(1 row) - -SELECT date '1/8/1999'; -ERROR: date/time field value out of range: "1/8/1999" -LINE 1: SELECT date '1/8/1999'; - ^ -HINT: Perhaps you need a different "datestyle" setting. -SELECT date '1/18/1999'; -ERROR: date/time field value out of range: "1/18/1999" -LINE 1: SELECT date '1/18/1999'; - ^ -HINT: Perhaps you need a different "datestyle" setting. -SELECT date '18/1/1999'; -ERROR: date/time field value out of range: "18/1/1999" -LINE 1: SELECT date '18/1/1999'; - ^ -HINT: Perhaps you need a different "datestyle" setting. -SELECT date '01/02/03'; - date ------------- - 2001-02-03 -(1 row) - -SELECT date '19990108'; - date ------------- - 1999-01-08 -(1 row) - -SELECT date '990108'; - date ------------- - 1999-01-08 -(1 row) - -SELECT date '1999.008'; - date ------------- - 1999-01-08 -(1 row) - -SELECT date 'J2451187'; - date ------------- - 1999-01-08 -(1 row) - -SELECT date 'January 8, 99 BC'; -ERROR: date/time field value out of range: "January 8, 99 BC" -LINE 1: SELECT date 'January 8, 99 BC'; - ^ -HINT: Perhaps you need a different "datestyle" setting. -SELECT date '99-Jan-08'; - date ------------- - 1999-01-08 -(1 row) - -SELECT date '1999-Jan-08'; - date ------------- - 1999-01-08 -(1 row) - -SELECT date '08-Jan-99'; -ERROR: date/time field value out of range: "08-Jan-99" -LINE 1: SELECT date '08-Jan-99'; - ^ -HINT: Perhaps you need a different "datestyle" setting. -SELECT date '08-Jan-1999'; - date ------------- - 1999-01-08 -(1 row) - -SELECT date 'Jan-08-99'; -ERROR: date/time field value out of range: "Jan-08-99" -LINE 1: SELECT date 'Jan-08-99'; - ^ -HINT: Perhaps you need a different "datestyle" setting. -SELECT date 'Jan-08-1999'; - date ------------- - 1999-01-08 -(1 row) - -SELECT date '99-08-Jan'; -ERROR: invalid input syntax for type date: "99-08-Jan" -LINE 1: SELECT date '99-08-Jan'; - ^ -SELECT date '1999-08-Jan'; -ERROR: invalid input syntax for type date: "1999-08-Jan" -LINE 1: SELECT date '1999-08-Jan'; - ^ -SELECT date '99 Jan 08'; - date ------------- - 1999-01-08 -(1 row) - -SELECT date '1999 Jan 08'; - date ------------- - 1999-01-08 -(1 row) - -SELECT date '08 Jan 99'; -ERROR: date/time field value out of range: "08 Jan 99" -LINE 1: SELECT date '08 Jan 99'; - ^ -HINT: Perhaps you need a different "datestyle" setting. -SELECT date '08 Jan 1999'; - date ------------- - 1999-01-08 -(1 row) - -SELECT date 'Jan 08 99'; -ERROR: date/time field value out of range: "Jan 08 99" -LINE 1: SELECT date 'Jan 08 99'; - ^ -HINT: Perhaps you need a different "datestyle" setting. -SELECT date 'Jan 08 1999'; - date ------------- - 1999-01-08 -(1 row) - -SELECT date '99 08 Jan'; - date ------------- - 1999-01-08 -(1 row) - -SELECT date '1999 08 Jan'; - date ------------- - 1999-01-08 -(1 row) - -SELECT date '99-01-08'; - date ------------- - 1999-01-08 -(1 row) - -SELECT date '1999-01-08'; - date ------------- - 1999-01-08 -(1 row) - -SELECT date '08-01-99'; -ERROR: date/time field value out of range: "08-01-99" -LINE 1: SELECT date '08-01-99'; - ^ -HINT: Perhaps you need a different "datestyle" setting. -SELECT date '08-01-1999'; -ERROR: date/time field value out of range: "08-01-1999" -LINE 1: SELECT date '08-01-1999'; - ^ -HINT: Perhaps you need a different "datestyle" setting. -SELECT date '01-08-99'; -ERROR: date/time field value out of range: "01-08-99" -LINE 1: SELECT date '01-08-99'; - ^ -HINT: Perhaps you need a different "datestyle" setting. -SELECT date '01-08-1999'; -ERROR: date/time field value out of range: "01-08-1999" -LINE 1: SELECT date '01-08-1999'; - ^ -HINT: Perhaps you need a different "datestyle" setting. -SELECT date '99-08-01'; - date ------------- - 1999-08-01 -(1 row) - -SELECT date '1999-08-01'; - date ------------- - 1999-08-01 -(1 row) - -SELECT date '99 01 08'; - date ------------- - 1999-01-08 -(1 row) - -SELECT date '1999 01 08'; - date ------------- - 1999-01-08 -(1 row) - -SELECT date '08 01 99'; -ERROR: date/time field value out of range: "08 01 99" -LINE 1: SELECT date '08 01 99'; - ^ -HINT: Perhaps you need a different "datestyle" setting. -SELECT date '08 01 1999'; -ERROR: date/time field value out of range: "08 01 1999" -LINE 1: SELECT date '08 01 1999'; - ^ -HINT: Perhaps you need a different "datestyle" setting. -SELECT date '01 08 99'; -ERROR: date/time field value out of range: "01 08 99" -LINE 1: SELECT date '01 08 99'; - ^ -HINT: Perhaps you need a different "datestyle" setting. -SELECT date '01 08 1999'; -ERROR: date/time field value out of range: "01 08 1999" -LINE 1: SELECT date '01 08 1999'; - ^ -HINT: Perhaps you need a different "datestyle" setting. -SELECT date '99 08 01'; - date ------------- - 1999-08-01 -(1 row) - -SELECT date '1999 08 01'; - date ------------- - 1999-08-01 -(1 row) - -SET datestyle TO dmy; -SELECT date 'January 8, 1999'; - date ------------- - 1999-01-08 -(1 row) - -SELECT date '1999-01-08'; - date ------------- - 1999-01-08 -(1 row) - -SELECT date '1999-01-18'; - date ------------- - 1999-01-18 -(1 row) - -SELECT date '1/8/1999'; - date ------------- - 1999-08-01 -(1 row) - -SELECT date '1/18/1999'; -ERROR: date/time field value out of range: "1/18/1999" -LINE 1: SELECT date '1/18/1999'; - ^ -HINT: Perhaps you need a different "datestyle" setting. -SELECT date '18/1/1999'; - date ------------- - 1999-01-18 -(1 row) - -SELECT date '01/02/03'; - date ------------- - 2003-02-01 -(1 row) - -SELECT date '19990108'; - date ------------- - 1999-01-08 -(1 row) - -SELECT date '990108'; - date ------------- - 1999-01-08 -(1 row) - -SELECT date '1999.008'; - date ------------- - 1999-01-08 -(1 row) - -SELECT date 'J2451187'; - date ------------- - 1999-01-08 -(1 row) - -SELECT date 'January 8, 99 BC'; - date ---------------- - 0099-01-08 BC -(1 row) - -SELECT date '99-Jan-08'; -ERROR: date/time field value out of range: "99-Jan-08" -LINE 1: SELECT date '99-Jan-08'; - ^ -HINT: Perhaps you need a different "datestyle" setting. -SELECT date '1999-Jan-08'; - date ------------- - 1999-01-08 -(1 row) - -SELECT date '08-Jan-99'; - date ------------- - 1999-01-08 -(1 row) - -SELECT date '08-Jan-1999'; - date ------------- - 1999-01-08 -(1 row) - -SELECT date 'Jan-08-99'; - date ------------- - 1999-01-08 -(1 row) - -SELECT date 'Jan-08-1999'; - date ------------- - 1999-01-08 -(1 row) - -SELECT date '99-08-Jan'; -ERROR: invalid input syntax for type date: "99-08-Jan" -LINE 1: SELECT date '99-08-Jan'; - ^ -SELECT date '1999-08-Jan'; -ERROR: invalid input syntax for type date: "1999-08-Jan" -LINE 1: SELECT date '1999-08-Jan'; - ^ -SELECT date '99 Jan 08'; -ERROR: date/time field value out of range: "99 Jan 08" -LINE 1: SELECT date '99 Jan 08'; - ^ -HINT: Perhaps you need a different "datestyle" setting. -SELECT date '1999 Jan 08'; - date ------------- - 1999-01-08 -(1 row) - -SELECT date '08 Jan 99'; - date ------------- - 1999-01-08 -(1 row) - -SELECT date '08 Jan 1999'; - date ------------- - 1999-01-08 -(1 row) - -SELECT date 'Jan 08 99'; - date ------------- - 1999-01-08 -(1 row) - -SELECT date 'Jan 08 1999'; - date ------------- - 1999-01-08 -(1 row) - -SELECT date '99 08 Jan'; -ERROR: invalid input syntax for type date: "99 08 Jan" -LINE 1: SELECT date '99 08 Jan'; - ^ -SELECT date '1999 08 Jan'; - date ------------- - 1999-01-08 -(1 row) - -SELECT date '99-01-08'; -ERROR: date/time field value out of range: "99-01-08" -LINE 1: SELECT date '99-01-08'; - ^ -HINT: Perhaps you need a different "datestyle" setting. -SELECT date '1999-01-08'; - date ------------- - 1999-01-08 -(1 row) - -SELECT date '08-01-99'; - date ------------- - 1999-01-08 -(1 row) - -SELECT date '08-01-1999'; - date ------------- - 1999-01-08 -(1 row) - -SELECT date '01-08-99'; - date ------------- - 1999-08-01 -(1 row) - -SELECT date '01-08-1999'; - date ------------- - 1999-08-01 -(1 row) - -SELECT date '99-08-01'; -ERROR: date/time field value out of range: "99-08-01" -LINE 1: SELECT date '99-08-01'; - ^ -HINT: Perhaps you need a different "datestyle" setting. -SELECT date '1999-08-01'; - date ------------- - 1999-08-01 -(1 row) - -SELECT date '99 01 08'; -ERROR: date/time field value out of range: "99 01 08" -LINE 1: SELECT date '99 01 08'; - ^ -HINT: Perhaps you need a different "datestyle" setting. -SELECT date '1999 01 08'; - date ------------- - 1999-01-08 -(1 row) - -SELECT date '08 01 99'; - date ------------- - 1999-01-08 -(1 row) - -SELECT date '08 01 1999'; - date ------------- - 1999-01-08 -(1 row) - -SELECT date '01 08 99'; - date ------------- - 1999-08-01 -(1 row) - -SELECT date '01 08 1999'; - date ------------- - 1999-08-01 -(1 row) - -SELECT date '99 08 01'; -ERROR: date/time field value out of range: "99 08 01" -LINE 1: SELECT date '99 08 01'; - ^ -HINT: Perhaps you need a different "datestyle" setting. -SELECT date '1999 08 01'; - date ------------- - 1999-08-01 -(1 row) - -SET datestyle TO mdy; -SELECT date 'January 8, 1999'; - date ------------- - 1999-01-08 -(1 row) - -SELECT date '1999-01-08'; - date ------------- - 1999-01-08 -(1 row) - -SELECT date '1999-01-18'; - date ------------- - 1999-01-18 -(1 row) - -SELECT date '1/8/1999'; - date ------------- - 1999-01-08 -(1 row) - -SELECT date '1/18/1999'; - date ------------- - 1999-01-18 -(1 row) - -SELECT date '18/1/1999'; -ERROR: date/time field value out of range: "18/1/1999" -LINE 1: SELECT date '18/1/1999'; - ^ -HINT: Perhaps you need a different "datestyle" setting. -SELECT date '01/02/03'; - date ------------- - 2003-01-02 -(1 row) - -SELECT date '19990108'; - date ------------- - 1999-01-08 -(1 row) - -SELECT date '990108'; - date ------------- - 1999-01-08 -(1 row) - -SELECT date '1999.008'; - date ------------- - 1999-01-08 -(1 row) - -SELECT date 'J2451187'; - date ------------- - 1999-01-08 -(1 row) - -SELECT date 'January 8, 99 BC'; - date ---------------- - 0099-01-08 BC -(1 row) - -SELECT date '99-Jan-08'; -ERROR: date/time field value out of range: "99-Jan-08" -LINE 1: SELECT date '99-Jan-08'; - ^ -HINT: Perhaps you need a different "datestyle" setting. -SELECT date '1999-Jan-08'; - date ------------- - 1999-01-08 -(1 row) - -SELECT date '08-Jan-99'; - date ------------- - 1999-01-08 -(1 row) - -SELECT date '08-Jan-1999'; - date ------------- - 1999-01-08 -(1 row) - -SELECT date 'Jan-08-99'; - date ------------- - 1999-01-08 -(1 row) - -SELECT date 'Jan-08-1999'; - date ------------- - 1999-01-08 -(1 row) - -SELECT date '99-08-Jan'; -ERROR: invalid input syntax for type date: "99-08-Jan" -LINE 1: SELECT date '99-08-Jan'; - ^ -SELECT date '1999-08-Jan'; -ERROR: invalid input syntax for type date: "1999-08-Jan" -LINE 1: SELECT date '1999-08-Jan'; - ^ -SELECT date '99 Jan 08'; -ERROR: invalid input syntax for type date: "99 Jan 08" -LINE 1: SELECT date '99 Jan 08'; - ^ -SELECT date '1999 Jan 08'; - date ------------- - 1999-01-08 -(1 row) - -SELECT date '08 Jan 99'; - date ------------- - 1999-01-08 -(1 row) - -SELECT date '08 Jan 1999'; - date ------------- - 1999-01-08 -(1 row) - -SELECT date 'Jan 08 99'; - date ------------- - 1999-01-08 -(1 row) - -SELECT date 'Jan 08 1999'; - date ------------- - 1999-01-08 -(1 row) - -SELECT date '99 08 Jan'; -ERROR: invalid input syntax for type date: "99 08 Jan" -LINE 1: SELECT date '99 08 Jan'; - ^ -SELECT date '1999 08 Jan'; - date ------------- - 1999-01-08 -(1 row) - -SELECT date '99-01-08'; -ERROR: date/time field value out of range: "99-01-08" -LINE 1: SELECT date '99-01-08'; - ^ -HINT: Perhaps you need a different "datestyle" setting. -SELECT date '1999-01-08'; - date ------------- - 1999-01-08 -(1 row) - -SELECT date '08-01-99'; - date ------------- - 1999-08-01 -(1 row) - -SELECT date '08-01-1999'; - date ------------- - 1999-08-01 -(1 row) - -SELECT date '01-08-99'; - date ------------- - 1999-01-08 -(1 row) - -SELECT date '01-08-1999'; - date ------------- - 1999-01-08 -(1 row) - -SELECT date '99-08-01'; -ERROR: date/time field value out of range: "99-08-01" -LINE 1: SELECT date '99-08-01'; - ^ -HINT: Perhaps you need a different "datestyle" setting. -SELECT date '1999-08-01'; - date ------------- - 1999-08-01 -(1 row) - -SELECT date '99 01 08'; -ERROR: date/time field value out of range: "99 01 08" -LINE 1: SELECT date '99 01 08'; - ^ -HINT: Perhaps you need a different "datestyle" setting. -SELECT date '1999 01 08'; - date ------------- - 1999-01-08 -(1 row) - -SELECT date '08 01 99'; - date ------------- - 1999-08-01 -(1 row) - -SELECT date '08 01 1999'; - date ------------- - 1999-08-01 -(1 row) - -SELECT date '01 08 99'; - date ------------- - 1999-01-08 -(1 row) - -SELECT date '01 08 1999'; - date ------------- - 1999-01-08 -(1 row) - -SELECT date '99 08 01'; -ERROR: date/time field value out of range: "99 08 01" -LINE 1: SELECT date '99 08 01'; - ^ -HINT: Perhaps you need a different "datestyle" setting. -SELECT date '1999 08 01'; - date ------------- - 1999-08-01 -(1 row) - -RESET datestyle; --- --- Simple math --- Leave most of it for the horology tests --- -SELECT f1 - date '2000-01-01' AS "Days From 2K" FROM DATE_TBL ORDER BY 1; - Days From 2K --------------- - -15607 - -15542 - -1403 - -1402 - -1401 - -1400 - -1037 - -1036 - -1035 - 91 - 92 - 93 - 13977 - 14343 - 14710 -(15 rows) - -SELECT f1 - date 'epoch' AS "Days From Epoch" FROM DATE_TBL ORDER BY 1; - Days From Epoch ------------------ - -4650 - -4585 - 9554 - 9555 - 9556 - 9557 - 9920 - 9921 - 9922 - 11048 - 11049 - 11050 - 24934 - 25300 - 25667 -(15 rows) - -SELECT date 'yesterday' - date 'today' AS "One day"; - One day ---------- - -1 -(1 row) - -SELECT date 'today' - date 'tomorrow' AS "One day"; - One day ---------- - -1 -(1 row) - -SELECT date 'yesterday' - date 'tomorrow' AS "Two days"; - Two days ----------- - -2 -(1 row) - -SELECT date 'tomorrow' - date 'today' AS "One day"; - One day ---------- - 1 -(1 row) - -SELECT date 'today' - date 'yesterday' AS "One day"; - One day ---------- - 1 -(1 row) - -SELECT date 'tomorrow' - date 'yesterday' AS "Two days"; - Two days ----------- - 2 -(1 row) - --- --- test extract! --- --- century --- -SELECT EXTRACT(CENTURY FROM DATE '0101-12-31 BC'); -- -2 - date_part ------------ - -2 -(1 row) - -SELECT EXTRACT(CENTURY FROM DATE '0100-12-31 BC'); -- -1 - date_part ------------ - -1 -(1 row) - -SELECT EXTRACT(CENTURY FROM DATE '0001-12-31 BC'); -- -1 - date_part ------------ - -1 -(1 row) - -SELECT EXTRACT(CENTURY FROM DATE '0001-01-01'); -- 1 - date_part ------------ - 1 -(1 row) - -SELECT EXTRACT(CENTURY FROM DATE '0001-01-01 AD'); -- 1 - date_part ------------ - 1 -(1 row) - -SELECT EXTRACT(CENTURY FROM DATE '1900-12-31'); -- 19 - date_part ------------ - 19 -(1 row) - -SELECT EXTRACT(CENTURY FROM DATE '1901-01-01'); -- 20 - date_part ------------ - 20 -(1 row) - -SELECT EXTRACT(CENTURY FROM DATE '2000-12-31'); -- 20 - date_part ------------ - 20 -(1 row) - -SELECT EXTRACT(CENTURY FROM DATE '2001-01-01'); -- 21 - date_part ------------ - 21 -(1 row) - -SELECT EXTRACT(CENTURY FROM CURRENT_DATE)>=21 AS True; -- true - true ------- - t -(1 row) - --- --- millennium --- -SELECT EXTRACT(MILLENNIUM FROM DATE '0001-12-31 BC'); -- -1 - date_part ------------ - -1 -(1 row) - -SELECT EXTRACT(MILLENNIUM FROM DATE '0001-01-01 AD'); -- 1 - date_part ------------ - 1 -(1 row) - -SELECT EXTRACT(MILLENNIUM FROM DATE '1000-12-31'); -- 1 - date_part ------------ - 1 -(1 row) - -SELECT EXTRACT(MILLENNIUM FROM DATE '1001-01-01'); -- 2 - date_part ------------ - 2 -(1 row) - -SELECT EXTRACT(MILLENNIUM FROM DATE '2000-12-31'); -- 2 - date_part ------------ - 2 -(1 row) - -SELECT EXTRACT(MILLENNIUM FROM DATE '2001-01-01'); -- 3 - date_part ------------ - 3 -(1 row) - --- next test to be fixed on the turn of the next millennium;-) -SELECT EXTRACT(MILLENNIUM FROM CURRENT_DATE); -- 3 - date_part ------------ - 3 -(1 row) - --- --- decade --- -SELECT EXTRACT(DECADE FROM DATE '1994-12-25'); -- 199 - date_part ------------ - 199 -(1 row) - -SELECT EXTRACT(DECADE FROM DATE '0010-01-01'); -- 1 - date_part ------------ - 1 -(1 row) - -SELECT EXTRACT(DECADE FROM DATE '0009-12-31'); -- 0 - date_part ------------ - 0 -(1 row) - -SELECT EXTRACT(DECADE FROM DATE '0001-01-01 BC'); -- 0 - date_part ------------ - 0 -(1 row) - -SELECT EXTRACT(DECADE FROM DATE '0002-12-31 BC'); -- -1 - date_part ------------ - -1 -(1 row) - -SELECT EXTRACT(DECADE FROM DATE '0011-01-01 BC'); -- -1 - date_part ------------ - -1 -(1 row) - -SELECT EXTRACT(DECADE FROM DATE '0012-12-31 BC'); -- -2 - date_part ------------ - -2 -(1 row) - --- --- some other types: --- --- on a timestamp. -SELECT EXTRACT(CENTURY FROM NOW())>=21 AS True; -- true - true ------- - t -(1 row) - -SELECT EXTRACT(CENTURY FROM TIMESTAMP '1970-03-20 04:30:00.00000'); -- 20 - date_part ------------ - 20 -(1 row) - --- on an interval -SELECT EXTRACT(CENTURY FROM INTERVAL '100 y'); -- 1 - date_part ------------ - 1 -(1 row) - -SELECT EXTRACT(CENTURY FROM INTERVAL '99 y'); -- 0 - date_part ------------ - 0 -(1 row) - -SELECT EXTRACT(CENTURY FROM INTERVAL '-99 y'); -- 0 - date_part ------------ - 0 -(1 row) - -SELECT EXTRACT(CENTURY FROM INTERVAL '-100 y'); -- -1 - date_part ------------ - -1 -(1 row) - --- --- test trunc function! --- -SELECT DATE_TRUNC('MILLENNIUM', TIMESTAMP '1970-03-20 04:30:00.00000'); -- 1001 - date_trunc --------------------------- - Thu Jan 01 00:00:00 1001 -(1 row) - -SELECT DATE_TRUNC('MILLENNIUM', DATE '1970-03-20'); -- 1001-01-01 - date_trunc ------------------------------- - Thu Jan 01 00:00:00 1001 PST -(1 row) - -SELECT DATE_TRUNC('CENTURY', TIMESTAMP '1970-03-20 04:30:00.00000'); -- 1901 - date_trunc --------------------------- - Tue Jan 01 00:00:00 1901 -(1 row) - -SELECT DATE_TRUNC('CENTURY', DATE '1970-03-20'); -- 1901 - date_trunc ------------------------------- - Tue Jan 01 00:00:00 1901 PST -(1 row) - -SELECT DATE_TRUNC('CENTURY', DATE '2004-08-10'); -- 2001-01-01 - date_trunc ------------------------------- - Mon Jan 01 00:00:00 2001 PST -(1 row) - -SELECT DATE_TRUNC('CENTURY', DATE '0002-02-04'); -- 0001-01-01 - date_trunc ------------------------------- - Mon Jan 01 00:00:00 0001 PST -(1 row) - -SELECT DATE_TRUNC('CENTURY', DATE '0055-08-10 BC'); -- 0100-01-01 BC - date_trunc ---------------------------------- - Tue Jan 01 00:00:00 0100 PST BC -(1 row) - -SELECT DATE_TRUNC('DECADE', DATE '1993-12-25'); -- 1990-01-01 - date_trunc ------------------------------- - Mon Jan 01 00:00:00 1990 PST -(1 row) - -SELECT DATE_TRUNC('DECADE', DATE '0004-12-25'); -- 0001-01-01 BC - date_trunc ---------------------------------- - Sat Jan 01 00:00:00 0001 PST BC -(1 row) - -SELECT DATE_TRUNC('DECADE', DATE '0002-12-31 BC'); -- 0011-01-01 BC - date_trunc ---------------------------------- - Mon Jan 01 00:00:00 0011 PST BC -(1 row) - http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/f05c19ed/src/test/regress/expected/float4.out ---------------------------------------------------------------------- diff --git a/src/test/regress/expected/float4.out b/src/test/regress/expected/float4.out deleted file mode 100755 index f0847a0..0000000 --- a/src/test/regress/expected/float4.out +++ /dev/null @@ -1,241 +0,0 @@ --- --- FLOAT4 --- -CREATE TABLE FLOAT4_TBL (f1 float4); -NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'f1' as the Greenplum Database data distribution key for this table. -HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. -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'); -ERROR: value out of range: overflow -INSERT INTO FLOAT4_TBL(f1) VALUES ('-10e40'); -ERROR: value out of range: overflow -INSERT INTO FLOAT4_TBL(f1) VALUES ('10e-40'); -INSERT INTO FLOAT4_TBL(f1) VALUES ('-10e-40'); --- bad input -INSERT INTO FLOAT4_TBL(f1) VALUES (''); -ERROR: invalid input syntax for type real: "" -INSERT INTO FLOAT4_TBL(f1) VALUES (' '); -ERROR: invalid input syntax for type real: " " -INSERT INTO FLOAT4_TBL(f1) VALUES ('xyz'); -ERROR: invalid input syntax for type real: "xyz" -INSERT INTO FLOAT4_TBL(f1) VALUES ('5.0.0'); -ERROR: invalid input syntax for type real: "5.0.0" -INSERT INTO FLOAT4_TBL(f1) VALUES ('5 . 0'); -ERROR: invalid input syntax for type real: "5 . 0" -INSERT INTO FLOAT4_TBL(f1) VALUES ('5. 0'); -ERROR: invalid input syntax for type real: "5. 0" -INSERT INTO FLOAT4_TBL(f1) VALUES (' - 3.0'); -ERROR: invalid input syntax for type real: " - 3.0" -INSERT INTO FLOAT4_TBL(f1) VALUES ('123 5'); -ERROR: invalid input syntax for type real: "123 5" --- special inputs -SELECT 'NaN'::float4; - float4 --------- - NaN -(1 row) - -SELECT 'nan'::float4; - float4 --------- - NaN -(1 row) - -SELECT ' NAN '::float4; - float4 --------- - NaN -(1 row) - -SELECT 'infinity'::float4; - float4 ----------- - Infinity -(1 row) - -SELECT ' -INFINiTY '::float4; - float4 ------------ - -Infinity -(1 row) - --- bad special inputs -SELECT 'N A N'::float4; -ERROR: invalid input syntax for type real: "N A N" -LINE 1: SELECT 'N A N'::float4; - ^ -SELECT 'NaN x'::float4; -ERROR: invalid input syntax for type real: "NaN x" -LINE 1: SELECT 'NaN x'::float4; - ^ -SELECT ' INFINITY x'::float4; -ERROR: invalid input syntax for type real: " INFINITY x" -LINE 1: SELECT ' INFINITY x'::float4; - ^ -SELECT 'Infinity'::float4 + 100.0; - ?column? ----------- - Infinity -(1 row) - -SELECT 'Infinity'::float4 / 'Infinity'::float4; - ?column? ----------- - NaN -(1 row) - -SELECT 'nan'::float4 / 'nan'::float4; - ?column? ----------- - NaN -(1 row) - -SELECT '' AS five, * FROM FLOAT4_TBL ORDER BY 2; - five | f1 -------+------------- - | -34.84 - | -1e-39 - | 0 - | 1e-39 - | 1.23457e-20 - | 1004.3 - | 1.23457e+20 -(7 rows) - -SELECT '' AS four, f.* FROM FLOAT4_TBL f WHERE f.f1 <> '1004.3' ORDER BY 2; - four | f1 -------+------------- - | -34.84 - | -1e-39 - | 0 - | 1e-39 - | 1.23457e-20 - | 1.23457e+20 -(6 rows) - -SELECT '' AS one, f.* FROM FLOAT4_TBL f WHERE f.f1 = '1004.3' ORDER BY 2; - one | f1 ------+-------- - | 1004.3 -(1 row) - -SELECT '' AS three, f.* FROM FLOAT4_TBL f WHERE '1004.3' > f.f1 ORDER BY 2; - three | f1 --------+------------- - | -34.84 - | -1e-39 - | 0 - | 1e-39 - | 1.23457e-20 -(5 rows) - -SELECT '' AS three, f.* FROM FLOAT4_TBL f WHERE f.f1 < '1004.3' ORDER BY 2; - three | f1 --------+------------- - | -34.84 - | -1e-39 - | 0 - | 1e-39 - | 1.23457e-20 -(5 rows) - -SELECT '' AS four, f.* FROM FLOAT4_TBL f WHERE '1004.3' >= f.f1 ORDER BY 2; - four | f1 -------+------------- - | -34.84 - | -1e-39 - | 0 - | 1e-39 - | 1.23457e-20 - | 1004.3 -(6 rows) - -SELECT '' AS four, f.* FROM FLOAT4_TBL f WHERE f.f1 <= '1004.3' ORDER BY 2; - four | f1 -------+------------- - | -34.84 - | -1e-39 - | 0 - | 1e-39 - | 1.23457e-20 - | 1004.3 -(6 rows) - -SELECT '' AS three, f.f1, f.f1 * '-10' AS x FROM FLOAT4_TBL f - WHERE f.f1 > '0.0' ORDER BY 2; - three | f1 | x --------+-------------+-------------- - | 1e-39 | -1e-38 - | 1.23457e-20 | -1.23457e-19 - | 1004.3 | -10043 - | 1.23457e+20 | -1.23457e+21 -(4 rows) - -SELECT '' AS three, f.f1, f.f1 + '-10' AS x FROM FLOAT4_TBL f - WHERE f.f1 > '0.0' ORDER BY 2; - three | f1 | x --------+-------------+------------- - | 1e-39 | -10 - | 1.23457e-20 | -10 - | 1004.3 | 994.3 - | 1.23457e+20 | 1.23457e+20 -(4 rows) - -SELECT '' AS three, f.f1, f.f1 / '-10' AS x FROM FLOAT4_TBL f - WHERE f.f1 > '0.0' ORDER BY 2; - three | f1 | x --------+-------------+-------------- - | 1e-39 | -9.99995e-41 - | 1.23457e-20 | -1.23457e-21 - | 1004.3 | -100.43 - | 1.23457e+20 | -1.23457e+19 -(4 rows) - -SELECT '' AS three, f.f1, f.f1 - '-10' AS x FROM FLOAT4_TBL f - WHERE f.f1 > '0.0' ORDER BY 2; - three | f1 | x --------+-------------+------------- - | 1e-39 | 10 - | 1.23457e-20 | 10 - | 1004.3 | 1014.3 - | 1.23457e+20 | 1.23457e+20 -(4 rows) - --- test divide by zero -SELECT '' AS bad, f.f1 / '0.0' from FLOAT4_TBL f; -ERROR: division by zero (seg0 slice1 localhost:50001 pid=1829) -SELECT '' AS five, * FROM FLOAT4_TBL ORDER BY 2; - five | f1 -------+------------- - | -34.84 - | -1e-39 - | 0 - | 1e-39 - | 1.23457e-20 - | 1004.3 - | 1.23457e+20 -(7 rows) - --- test the unary float4abs operator -SELECT '' AS five, f.f1, @f.f1 AS abs_f1 FROM FLOAT4_TBL f ORDER BY 2; - five | f1 | abs_f1 -------+-------------+------------- - | -34.84 | 34.84 - | -1e-39 | 1e-39 - | 0 | 0 - | 1e-39 | 1e-39 - | 1.23457e-20 | 1.23457e-20 - | 1004.3 | 1004.3 - | 1.23457e+20 | 1.23457e+20 -(7 rows) - --- MPP doesn't support this yet. ---UPDATE FLOAT4_TBL --- SET f1 = FLOAT4_TBL.f1 * '-1' --- WHERE FLOAT4_TBL.f1 > '0.0'; ---SELECT '' AS five, * FROM FLOAT4_TBL ORDER BY 2; http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/f05c19ed/src/test/regress/expected/float8.out ---------------------------------------------------------------------- diff --git a/src/test/regress/expected/float8.out b/src/test/regress/expected/float8.out deleted file mode 100755 index 34b2ca8..0000000 --- a/src/test/regress/expected/float8.out +++ /dev/null @@ -1,412 +0,0 @@ --- --- FLOAT8 --- -CREATE TABLE FLOAT8_TBL(i INT DEFAULT 1, f1 float8); -NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Greenplum Database data distribution key for this table. -HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. -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; -ERROR: "10e400" is out of range for type double precision -LINE 1: SELECT '10e400'::float8; - ^ -SELECT '-10e400'::float8; -ERROR: "-10e400" is out of range for type double precision -LINE 1: SELECT '-10e400'::float8; - ^ -SELECT '10e-400'::float8; -ERROR: "10e-400" is out of range for type double precision -LINE 1: SELECT '10e-400'::float8; - ^ -SELECT '-10e-400'::float8; -ERROR: "-10e-400" is out of range for type double precision -LINE 1: SELECT '-10e-400'::float8; - ^ --- bad input -INSERT INTO FLOAT8_TBL(f1) VALUES (''); -ERROR: invalid input syntax for type double precision: "" -INSERT INTO FLOAT8_TBL(f1) VALUES (' '); -ERROR: invalid input syntax for type double precision: " " -INSERT INTO FLOAT8_TBL(f1) VALUES ('xyz'); -ERROR: invalid input syntax for type double precision: "xyz" -INSERT INTO FLOAT8_TBL(f1) VALUES ('5.0.0'); -ERROR: invalid input syntax for type double precision: "5.0.0" -INSERT INTO FLOAT8_TBL(f1) VALUES ('5 . 0'); -ERROR: invalid input syntax for type double precision: "5 . 0" -INSERT INTO FLOAT8_TBL(f1) VALUES ('5. 0'); -ERROR: invalid input syntax for type double precision: "5. 0" -INSERT INTO FLOAT8_TBL(f1) VALUES (' - 3'); -ERROR: invalid input syntax for type double precision: " - 3" -INSERT INTO FLOAT8_TBL(f1) VALUES ('123 5'); -ERROR: invalid input syntax for type double precision: "123 5" --- special inputs -SELECT 'NaN'::float8; - float8 --------- - NaN -(1 row) - -SELECT 'nan'::float8; - float8 --------- - NaN -(1 row) - -SELECT ' NAN '::float8; - float8 --------- - NaN -(1 row) - -SELECT 'infinity'::float8; - float8 ----------- - Infinity -(1 row) - -SELECT ' -INFINiTY '::float8; - float8 ------------ - -Infinity -(1 row) - --- bad special inputs -SELECT 'N A N'::float8; -ERROR: invalid input syntax for type double precision: "N A N" -LINE 1: SELECT 'N A N'::float8; - ^ -SELECT 'NaN x'::float8; -ERROR: invalid input syntax for type double precision: "NaN x" -LINE 1: SELECT 'NaN x'::float8; - ^ -SELECT ' INFINITY x'::float8; -ERROR: invalid input syntax for type double precision: " INFINITY x" -LINE 1: SELECT ' INFINITY x'::float8; - ^ -SELECT 'Infinity'::float8 + 100.0; - ?column? ----------- - Infinity -(1 row) - -SELECT 'Infinity'::float8 / 'Infinity'::float8; - ?column? ----------- - NaN -(1 row) - -SELECT 'nan'::float8 / 'nan'::float8; - ?column? ----------- - NaN -(1 row) - -SELECT '' AS five, f1 FROM FLOAT8_TBL ORDER BY 2; - five | f1 -------+---------------------- - | -34.84 - | 0 - | 1.2345678901234e-200 - | 1004.3 - | 1.2345678901234e+200 -(5 rows) - -SELECT '' AS four, f.f1 FROM FLOAT8_TBL f WHERE f.f1 <> '1004.3' ORDER BY 2; - four | f1 -------+---------------------- - | -34.84 - | 0 - | 1.2345678901234e-200 - | 1.2345678901234e+200 -(4 rows) - -SELECT '' AS one, f.f1 FROM FLOAT8_TBL f WHERE f.f1 = '1004.3' ORDER BY 2; - one | f1 ------+-------- - | 1004.3 -(1 row) - -SELECT '' AS three, f.f1 FROM FLOAT8_TBL f WHERE '1004.3' > f.f1 ORDER BY 2; - three | f1 --------+---------------------- - | -34.84 - | 0 - | 1.2345678901234e-200 -(3 rows) - -SELECT '' AS three, f.f1 FROM FLOAT8_TBL f WHERE f.f1 < '1004.3' ORDER BY 2; - three | f1 --------+---------------------- - | -34.84 - | 0 - | 1.2345678901234e-200 -(3 rows) - -SELECT '' AS four, f.f1 FROM FLOAT8_TBL f WHERE '1004.3' >= f.f1 ORDER BY 2; - four | f1 -------+---------------------- - | -34.84 - | 0 - | 1.2345678901234e-200 - | 1004.3 -(4 rows) - -SELECT '' AS four, f.f1 FROM FLOAT8_TBL f WHERE f.f1 <= '1004.3' ORDER BY 2; - four | f1 -------+---------------------- - | -34.84 - | 0 - | 1.2345678901234e-200 - | 1004.3 -(4 rows) - -SELECT '' AS three, f.f1, f.f1 * '-10' AS x - FROM FLOAT8_TBL f - WHERE f.f1 > '0.0' ORDER BY 2; - three | f1 | x --------+----------------------+----------------------- - | 1.2345678901234e-200 | -1.2345678901234e-199 - | 1004.3 | -10043 - | 1.2345678901234e+200 | -1.2345678901234e+201 -(3 rows) - -SELECT '' AS three, f.f1, f.f1 + '-10' AS x - FROM FLOAT8_TBL f - WHERE f.f1 > '0.0' ORDER BY 2; - three | f1 | x --------+----------------------+---------------------- - | 1.2345678901234e-200 | -10 - | 1004.3 | 994.3 - | 1.2345678901234e+200 | 1.2345678901234e+200 -(3 rows) - -SELECT '' AS three, f.f1, f.f1 / '-10' AS x - FROM FLOAT8_TBL f - WHERE f.f1 > '0.0' ORDER BY 2; - three | f1 | x --------+----------------------+----------------------- - | 1.2345678901234e-200 | -1.2345678901234e-201 - | 1004.3 | -100.43 - | 1.2345678901234e+200 | -1.2345678901234e+199 -(3 rows) - -SELECT '' AS three, f.f1, f.f1 - '-10' AS x - FROM FLOAT8_TBL f - WHERE f.f1 > '0.0' ORDER BY 2; - three | f1 | x --------+----------------------+---------------------- - | 1.2345678901234e-200 | 10 - | 1004.3 | 1014.3 - | 1.2345678901234e+200 | 1.2345678901234e+200 -(3 rows) - -SELECT '' AS one, f.f1 ^ '2.0' AS square_f1 - FROM FLOAT8_TBL f where f.f1 = '1004.3'; - one | square_f1 ------+------------ - | 1008618.49 -(1 row) - --- absolute value -SELECT '' AS five, f.f1, @f.f1 AS abs_f1 - FROM FLOAT8_TBL f ORDER BY 2; - five | f1 | abs_f1 -------+----------------------+---------------------- - | -34.84 | 34.84 - | 0 | 0 - | 1.2345678901234e-200 | 1.2345678901234e-200 - | 1004.3 | 1004.3 - | 1.2345678901234e+200 | 1.2345678901234e+200 -(5 rows) - --- truncate -SELECT '' AS five, f.f1, trunc(f.f1) AS trunc_f1 - FROM FLOAT8_TBL f ORDER BY 2; - five | f1 | trunc_f1 -------+----------------------+---------------------- - | -34.84 | -34 - | 0 | 0 - | 1.2345678901234e-200 | 0 - | 1004.3 | 1004 - | 1.2345678901234e+200 | 1.2345678901234e+200 -(5 rows) - --- round -SELECT '' AS five, f.f1, round(f.f1) AS round_f1 - FROM FLOAT8_TBL f ORDER BY 2; - five | f1 | round_f1 -------+----------------------+---------------------- - | -34.84 | -35 - | 0 | 0 - | 1.2345678901234e-200 | 0 - | 1004.3 | 1004 - | 1.2345678901234e+200 | 1.2345678901234e+200 -(5 rows) - --- ceil / ceiling -select ceil(f1) as ceil_f1 from float8_tbl f ORDER BY 1; - ceil_f1 ----------------------- - -34 - 0 - 1 - 1005 - 1.2345678901234e+200 -(5 rows) - -select ceiling(f1) as ceiling_f1 from float8_tbl f ORDER BY 1; - ceiling_f1 ----------------------- - -34 - 0 - 1 - 1005 - 1.2345678901234e+200 -(5 rows) - --- floor -select floor(f1) as floor_f1 from float8_tbl f ORDER BY 1; - floor_f1 ----------------------- - -35 - 0 - 0 - 1004 - 1.2345678901234e+200 -(5 rows) - --- sign -select sign(f1) as sign_f1 from float8_tbl f ORDER BY 1; - sign_f1 ---------- - -1 - 0 - 1 - 1 - 1 -(5 rows) - --- square root -SELECT sqrt(float8 '64') AS eight; - eight -------- - 8 -(1 row) - -SELECT |/ float8 '64' AS eight; - eight -------- - 8 -(1 row) - -SELECT '' AS three, f.f1, |/f.f1 AS sqrt_f1 - FROM FLOAT8_TBL f - WHERE f.f1 > '0.0' ORDER BY 2; - three | f1 | sqrt_f1 --------+----------------------+----------------------- - | 1.2345678901234e-200 | 1.11111110611109e-100 - | 1004.3 | 31.6906926399535 - | 1.2345678901234e+200 | 1.11111110611109e+100 -(3 rows) - --- power -SELECT power(float8 '144', float8 '0.5'); - power -------- - 12 -(1 row) - --- take exp of ln(f.f1) -SELECT '' AS three, f.f1, exp(ln(f.f1)) AS exp_ln_f1 - FROM FLOAT8_TBL f - WHERE f.f1 > '0.0' ORDER BY 2; - three | f1 | exp_ln_f1 --------+----------------------+----------------------- - | 1.2345678901234e-200 | 1.23456789012339e-200 - | 1004.3 | 1004.3 - | 1.2345678901234e+200 | 1.23456789012338e+200 -(3 rows) - --- cube root -SELECT ||/ float8 '27' AS three; - three -------- - 3 -(1 row) - -SELECT '' AS five, f.f1, ||/f.f1 AS cbrt_f1 FROM FLOAT8_TBL f ORDER BY 2; - five | f1 | cbrt_f1 -------+----------------------+---------------------- - | -34.84 | -3.26607421344208 - | 0 | 0 - | 1.2345678901234e-200 | 2.3112042409018e-67 - | 1004.3 | 10.014312837827 - | 1.2345678901234e+200 | 4.97933859234765e+66 -(5 rows) - -SELECT '' AS five, f1 FROM FLOAT8_TBL ORDER BY 2; - five | f1 -------+---------------------- - | -34.84 - | 0 - | 1.2345678901234e-200 - | 1004.3 - | 1.2345678901234e+200 -(5 rows) - -UPDATE FLOAT8_TBL - SET f1 = FLOAT8_TBL.f1 * '-1' - WHERE FLOAT8_TBL.f1 > '0.0'; -SELECT '' AS bad, f.f1 * '1e200' from FLOAT8_TBL f; -ERROR: value out of range: overflow (seg0 slice1 localhost:50001 pid=1946) -SELECT '' AS bad, f.f1 ^ '1e200' from FLOAT8_TBL f; -ERROR: value out of range: overflow (seg0 slice1 localhost:50001 pid=1946) -SELECT '' AS bad, ln(f.f1) from FLOAT8_TBL f where f.f1 = '0.0' ; -ERROR: cannot take logarithm of zero (seg0 slice1 localhost:50001 pid=1946) -SELECT '' AS bad, ln(f.f1) from FLOAT8_TBL f where f.f1 < '0.0' ; -ERROR: cannot take logarithm of a negative number (seg0 slice1 localhost:50001 pid=1946) -SELECT '' AS bad, exp(f.f1) from FLOAT8_TBL f; -ERROR: value out of range: underflow (seg0 slice1 localhost:50001 pid=1946) -SELECT '' AS bad, f.f1 / '0.0' from FLOAT8_TBL f; -ERROR: division by zero (seg0 slice1 localhost:50001 pid=1946) -SELECT '' AS five, f1 FROM FLOAT8_TBL ORDER BY 2; - five | f1 -------+----------------------- - | -1.2345678901234e+200 - | -1004.3 - | -34.84 - | -1.2345678901234e-200 - | 0 -(5 rows) - --- test for over- and underflow -INSERT INTO FLOAT8_TBL(f1) VALUES ('10e400'); -ERROR: "10e400" is out of range for type double precision -INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e400'); -ERROR: "-10e400" is out of range for type double precision -INSERT INTO FLOAT8_TBL(f1) VALUES ('10e-400'); -ERROR: "10e-400" is out of range for type double precision -INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e-400'); -ERROR: "-10e-400" is out of range for type double precision --- maintain external table consistency across platforms --- delete all values and reinsert well-behaved ones -DELETE FROM FLOAT8_TBL; -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; - five | f1 -------+----------------------- - | -1.2345678901234e+200 - | -1004.3 - | -34.84 - | -1.2345678901234e-200 - | 0 -(5 rows) - http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/f05c19ed/src/test/regress/expected/int2.out ---------------------------------------------------------------------- diff --git a/src/test/regress/expected/int2.out b/src/test/regress/expected/int2.out deleted file mode 100755 index dc1f34c..0000000 --- a/src/test/regress/expected/int2.out +++ /dev/null @@ -1,230 +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'); -ERROR: invalid input syntax for integer: "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'); -ERROR: value "100000" is out of range for type smallint -INSERT INTO INT2_TBL(f1) VALUES ('asdf'); -ERROR: invalid input syntax for integer: "asdf" -INSERT INTO INT2_TBL(f1) VALUES (' '); -ERROR: invalid input syntax for integer: " " -INSERT INTO INT2_TBL(f1) VALUES ('- 1234'); -ERROR: invalid input syntax for integer: "- 1234" -INSERT INTO INT2_TBL(f1) VALUES ('4 444'); -ERROR: invalid input syntax for integer: "4 444" -INSERT INTO INT2_TBL(f1) VALUES ('123 dt'); -ERROR: invalid input syntax for integer: "123 dt" -INSERT INTO INT2_TBL(f1) VALUES (''); -ERROR: invalid input syntax for integer: "" -SELECT '' AS five, * FROM INT2_TBL order by f1; - five | f1 -------+-------- - | -32767 - | -1234 - | 0 - | 1234 - | 32767 -(5 rows) - -SELECT '' AS four, i.* FROM INT2_TBL i WHERE i.f1 <> int2 '0' order by f1; - four | f1 -------+-------- - | -32767 - | -1234 - | 1234 - | 32767 -(4 rows) - -SELECT '' AS four, i.* FROM INT2_TBL i WHERE i.f1 <> int4 '0' order by f1; - four | f1 -------+-------- - | -32767 - | -1234 - | 1234 - | 32767 -(4 rows) - -SELECT '' AS one, i.* FROM INT2_TBL i WHERE i.f1 = int2 '0' order by f1; - one | f1 ------+---- - | 0 -(1 row) - -SELECT '' AS one, i.* FROM INT2_TBL i WHERE i.f1 = int4 '0' order by f1; - one | f1 ------+---- - | 0 -(1 row) - -SELECT '' AS two, i.* FROM INT2_TBL i WHERE i.f1 < int2 '0' order by f1; - two | f1 ------+-------- - | -32767 - | -1234 -(2 rows) - -SELECT '' AS two, i.* FROM INT2_TBL i WHERE i.f1 < int4 '0' order by f1; - two | f1 ------+-------- - | -32767 - | -1234 -(2 rows) - -SELECT '' AS three, i.* FROM INT2_TBL i WHERE i.f1 <= int2 '0' order by f1; - three | f1 --------+-------- - | -32767 - | -1234 - | 0 -(3 rows) - -SELECT '' AS three, i.* FROM INT2_TBL i WHERE i.f1 <= int4 '0' order by f1; - three | f1 --------+-------- - | -32767 - | -1234 - | 0 -(3 rows) - -SELECT '' AS two, i.* FROM INT2_TBL i WHERE i.f1 > int2 '0' order by f1; - two | f1 ------+------- - | 1234 - | 32767 -(2 rows) - -SELECT '' AS two, i.* FROM INT2_TBL i WHERE i.f1 > int4 '0' order by f1; - two | f1 ------+------- - | 1234 - | 32767 -(2 rows) - -SELECT '' AS three, i.* FROM INT2_TBL i WHERE i.f1 >= int2 '0' order by f1; - three | f1 --------+------- - | 0 - | 1234 - | 32767 -(3 rows) - -SELECT '' AS three, i.* FROM INT2_TBL i WHERE i.f1 >= int4 '0' order by f1; - three | f1 --------+------- - | 0 - | 1234 - | 32767 -(3 rows) - --- positive odds -SELECT '' AS one, i.* FROM INT2_TBL i WHERE (i.f1 % int2 '2') = int2 '1' order by f1; - one | f1 ------+------- - | 32767 -(1 row) - --- any evens -SELECT '' AS three, i.* FROM INT2_TBL i WHERE (i.f1 % int4 '2') = int2 '0' order by f1; - three | f1 --------+------- - | -1234 - | 0 - | 1234 -(3 rows) - -SELECT '' AS five, i.f1, i.f1 * int2 '2' AS x FROM INT2_TBL i order by f1; -ERROR: smallint out of range -SELECT '' AS five, i.f1, i.f1 * int2 '2' AS x FROM INT2_TBL i -WHERE abs(f1) < 16384 order by f1; - five | f1 | x -------+-------+------- - | -1234 | -2468 - | 0 | 0 - | 1234 | 2468 -(3 rows) - -SELECT '' AS five, i.f1, i.f1 * int4 '2' AS x FROM INT2_TBL i order by f1; - five | f1 | x -------+--------+-------- - | -32767 | -65534 - | -1234 | -2468 - | 0 | 0 - | 1234 | 2468 - | 32767 | 65534 -(5 rows) - -SELECT '' AS five, i.f1, i.f1 + int2 '2' AS x FROM INT2_TBL i order by f1; -ERROR: smallint out of range -SELECT '' AS five, i.f1, i.f1 + int2 '2' AS x FROM INT2_TBL i -WHERE f1 < 32766 order by f1; - five | f1 | x -------+--------+-------- - | -32767 | -32765 - | -1234 | -1232 - | 0 | 2 - | 1234 | 1236 -(4 rows) - -SELECT '' AS five, i.f1, i.f1 + int4 '2' AS x FROM INT2_TBL i order by f1; - five | f1 | x -------+--------+-------- - | -32767 | -32765 - | -1234 | -1232 - | 0 | 2 - | 1234 | 1236 - | 32767 | 32769 -(5 rows) - -SELECT '' AS five, i.f1, i.f1 - int2 '2' AS x FROM INT2_TBL i order by f1; -ERROR: smallint out of range -SELECT '' AS five, i.f1, i.f1 - int2 '2' AS x FROM INT2_TBL i -WHERE f1 > -32767 order by f1; - five | f1 | x -------+-------+------- - | -1234 | -1236 - | 0 | -2 - | 1234 | 1232 - | 32767 | 32765 -(4 rows) - -SELECT '' AS five, i.f1, i.f1 - int4 '2' AS x FROM INT2_TBL i order by f1; - five | f1 | x -------+--------+-------- - | -32767 | -32769 - | -1234 | -1236 - | 0 | -2 - | 1234 | 1232 - | 32767 | 32765 -(5 rows) - -SELECT '' AS five, i.f1, i.f1 / int2 '2' AS x FROM INT2_TBL i order by f1; - five | f1 | x -------+--------+-------- - | -32767 | -16383 - | -1234 | -617 - | 0 | 0 - | 1234 | 617 - | 32767 | 16383 -(5 rows) - -SELECT '' AS five, i.f1, i.f1 / int4 '2' AS x FROM INT2_TBL i order by f1; - five | f1 | x -------+--------+-------- - | -32767 | -16383 - | -1234 | -617 - | 0 | 0 - | 1234 | 617 - | 32767 | 16383 -(5 rows) - http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/f05c19ed/src/test/regress/expected/int4.out ---------------------------------------------------------------------- diff --git a/src/test/regress/expected/int4.out b/src/test/regress/expected/int4.out deleted file mode 100755 index b8d018f..0000000 --- a/src/test/regress/expected/int4.out +++ /dev/null @@ -1,317 +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'); -ERROR: invalid input syntax for integer: "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'); -ERROR: value "1000000000000" is out of range for type integer -INSERT INTO INT4_TBL(f1) VALUES ('asdf'); -ERROR: invalid input syntax for integer: "asdf" -INSERT INTO INT4_TBL(f1) VALUES (' '); -ERROR: invalid input syntax for integer: " " -INSERT INTO INT4_TBL(f1) VALUES (' asdf '); -ERROR: invalid input syntax for integer: " asdf " -INSERT INTO INT4_TBL(f1) VALUES ('- 1234'); -ERROR: invalid input syntax for integer: "- 1234" -INSERT INTO INT4_TBL(f1) VALUES ('123 5'); -ERROR: invalid input syntax for integer: "123 5" -INSERT INTO INT4_TBL(f1) VALUES (''); -ERROR: invalid input syntax for integer: "" -SELECT '' AS five, * FROM INT4_TBL order by f1; - five | f1 -------+------------- - | -2147483647 - | -123456 - | 0 - | 123456 - | 2147483647 -(5 rows) - -SELECT '' AS four, i.* FROM INT4_TBL i WHERE i.f1 <> int2 '0' order by f1; - four | f1 -------+------------- - | -2147483647 - | -123456 - | 123456 - | 2147483647 -(4 rows) - -SELECT '' AS four, i.* FROM INT4_TBL i WHERE i.f1 <> int4 '0' order by f1; - four | f1 -------+------------- - | -2147483647 - | -123456 - | 123456 - | 2147483647 -(4 rows) - -SELECT '' AS one, i.* FROM INT4_TBL i WHERE i.f1 = int2 '0' order by f1; - one | f1 ------+---- - | 0 -(1 row) - -SELECT '' AS one, i.* FROM INT4_TBL i WHERE i.f1 = int4 '0' order by f1; - one | f1 ------+---- - | 0 -(1 row) - -SELECT '' AS two, i.* FROM INT4_TBL i WHERE i.f1 < int2 '0' order by f1; - two | f1 ------+------------- - | -2147483647 - | -123456 -(2 rows) - -SELECT '' AS two, i.* FROM INT4_TBL i WHERE i.f1 < int4 '0' order by f1; - two | f1 ------+------------- - | -2147483647 - | -123456 -(2 rows) - -SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 <= int2 '0' order by f1; - three | f1 --------+------------- - | -2147483647 - | -123456 - | 0 -(3 rows) - -SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 <= int4 '0' order by f1; - three | f1 --------+------------- - | -2147483647 - | -123456 - | 0 -(3 rows) - -SELECT '' AS two, i.* FROM INT4_TBL i WHERE i.f1 > int2 '0' order by f1; - two | f1 ------+------------ - | 123456 - | 2147483647 -(2 rows) - -SELECT '' AS two, i.* FROM INT4_TBL i WHERE i.f1 > int4 '0' order by f1; - two | f1 ------+------------ - | 123456 - | 2147483647 -(2 rows) - -SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 >= int2 '0' order by f1; - three | f1 --------+------------ - | 0 - | 123456 - | 2147483647 -(3 rows) - -SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 >= int4 '0' order by f1; - three | f1 --------+------------ - | 0 - | 123456 - | 2147483647 -(3 rows) - --- positive odds -SELECT '' AS one, i.* FROM INT4_TBL i WHERE (i.f1 % int2 '2') = int2 '1' order by f1; - one | f1 ------+------------ - | 2147483647 -(1 row) - --- any evens -SELECT '' AS three, i.* FROM INT4_TBL i WHERE (i.f1 % int4 '2') = int2 '0' order by f1; - three | f1 --------+--------- - | -123456 - | 0 - | 123456 -(3 rows) - -SELECT '' AS five, i.f1, i.f1 * int2 '2' AS x FROM INT4_TBL i order by f1; -ERROR: integer out of range -SELECT '' AS five, i.f1, i.f1 * int2 '2' AS x FROM INT4_TBL i -WHERE abs(f1) < 1073741824 order by f1; - five | f1 | x -------+---------+--------- - | -123456 | -246912 - | 0 | 0 - | 123456 | 246912 -(3 rows) - -SELECT '' AS five, i.f1, i.f1 * int4 '2' AS x FROM INT4_TBL i order by f1; -ERROR: integer out of range -SELECT '' AS five, i.f1, i.f1 * int4 '2' AS x FROM INT4_TBL i -WHERE abs(f1) < 1073741824 order by f1; - five | f1 | x -------+---------+--------- - | -123456 | -246912 - | 0 | 0 - | 123456 | 246912 -(3 rows) - -SELECT '' AS five, i.f1, i.f1 + int2 '2' AS x FROM INT4_TBL i order by f1; -ERROR: integer out of range -SELECT '' AS five, i.f1, i.f1 + int2 '2' AS x FROM INT4_TBL i -WHERE f1 < 2147483646 order by f1; - five | f1 | x -------+-------------+------------- - | -2147483647 | -2147483645 - | -123456 | -123454 - | 0 | 2 - | 123456 | 123458 -(4 rows) - -SELECT '' AS five, i.f1, i.f1 + int4 '2' AS x FROM INT4_TBL i order by f1; -ERROR: integer out of range -SELECT '' AS five, i.f1, i.f1 + int4 '2' AS x FROM INT4_TBL i -WHERE f1 < 2147483646 order by f1; - five | f1 | x -------+-------------+------------- - | -2147483647 | -2147483645 - | -123456 | -123454 - | 0 | 2 - | 123456 | 123458 -(4 rows) - -SELECT '' AS five, i.f1, i.f1 - int2 '2' AS x FROM INT4_TBL i order by f1; -ERROR: integer out of range -SELECT '' AS five, i.f1, i.f1 - int2 '2' AS x FROM INT4_TBL i -WHERE f1 > -2147483647 order by f1; - five | f1 | x -------+------------+------------ - | -123456 | -123458 - | 0 | -2 - | 123456 | 123454 - | 2147483647 | 2147483645 -(4 rows) - -SELECT '' AS five, i.f1, i.f1 - int4 '2' AS x FROM INT4_TBL i order by f1; -ERROR: integer out of range -SELECT '' AS five, i.f1, i.f1 - int4 '2' AS x FROM INT4_TBL i -WHERE f1 > -2147483647 order by f1; - five | f1 | x -------+------------+------------ - | -123456 | -123458 - | 0 | -2 - | 123456 | 123454 - | 2147483647 | 2147483645 -(4 rows) - -SELECT '' AS five, i.f1, i.f1 / int2 '2' AS x FROM INT4_TBL i order by f1; - five | f1 | x -------+-------------+------------- - | -2147483647 | -1073741823 - | -123456 | -61728 - | 0 | 0 - | 123456 | 61728 - | 2147483647 | 1073741823 -(5 rows) - -SELECT '' AS five, i.f1, i.f1 / int4 '2' AS x FROM INT4_TBL i order by f1; - five | f1 | x -------+-------------+------------- - | -2147483647 | -1073741823 - | -123456 | -61728 - | 0 | 0 - | 123456 | 61728 - | 2147483647 | 1073741823 -(5 rows) - --- --- more complex expressions --- --- variations on unary minus parsing -SELECT -2+3 AS one; - one ------ - 1 -(1 row) - -SELECT 4-2 AS two; - two ------ - 2 -(1 row) - -SELECT 2- -1 AS three; - three -------- - 3 -(1 row) - -SELECT 2 - -2 AS four; - four ------- - 4 -(1 row) - -SELECT int2 '2' * int2 '2' = int2 '16' / int2 '4' AS true; - true ------- - t -(1 row) - -SELECT int4 '2' * int2 '2' = int2 '16' / int4 '4' AS true; - true ------- - t -(1 row) - -SELECT int2 '2' * int4 '2' = int4 '16' / int2 '4' AS true; - true ------- - t -(1 row) - -SELECT int4 '1000' < int4 '999' AS false; - false -------- - f -(1 row) - -SELECT 4! AS twenty_four; - twenty_four -------------- - 24 -(1 row) - -SELECT !!3 AS six; - six ------ - 6 -(1 row) - -SELECT 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 AS ten; - ten ------ - 10 -(1 row) - -SELECT 2 + 2 / 2 AS three; - three -------- - 3 -(1 row) - -SELECT (2 + 2) / 2 AS two; - two ------ - 2 -(1 row) - http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/f05c19ed/src/test/regress/expected/int8.out ---------------------------------------------------------------------- diff --git a/src/test/regress/expected/int8.out b/src/test/regress/expected/int8.out deleted file mode 100755 index 91e1b36..0000000 --- a/src/test/regress/expected/int8.out +++ /dev/null @@ -1,325 +0,0 @@ --- --- INT8 --- Test int8 64-bit integers. --- -CREATE TABLE INT8_TBL(q1 int8, q2 int8); -NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'q1' as the Greenplum Database data distribution key for this table. -HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. -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 (' '); -ERROR: invalid input syntax for integer: " " -INSERT INTO INT8_TBL(q1) VALUES ('xxx'); -ERROR: invalid input syntax for integer: "xxx" -INSERT INTO INT8_TBL(q1) VALUES ('3908203590239580293850293850329485'); -ERROR: value "3908203590239580293850293850329485" is out of range for type bigint -INSERT INTO INT8_TBL(q1) VALUES ('-1204982019841029840928340329840934'); -ERROR: value "-1204982019841029840928340329840934" is out of range for type bigint -INSERT INTO INT8_TBL(q1) VALUES ('- 123'); -ERROR: invalid input syntax for integer: "- 123" -INSERT INTO INT8_TBL(q1) VALUES (' 345 5'); -ERROR: invalid input syntax for integer: " 345 5" -INSERT INTO INT8_TBL(q1) VALUES (''); -ERROR: invalid input syntax for integer: "" -SELECT * FROM INT8_TBL ; - q1 | q2 -------------------+------------------- - 123 | 456 - 123 | 4567890123456789 - 4567890123456789 | -4567890123456789 - 4567890123456789 | 123 - 4567890123456789 | 4567890123456789 -(5 rows) - -SELECT '' AS five, q1 AS plus, -q1 AS minus FROM INT8_TBL ; - five | plus | minus -------+------------------+------------------- - | 123 | -123 - | 123 | -123 - | 4567890123456789 | -4567890123456789 - | 4567890123456789 | -4567890123456789 - | 4567890123456789 | -4567890123456789 -(5 rows) - -SELECT '' AS five, q1, q2, q1 + q2 AS plus FROM INT8_TBL ; - five | q1 | q2 | plus -------+------------------+-------------------+------------------ - | 123 | 456 | 579 - | 123 | 4567890123456789 | 4567890123456912 - | 4567890123456789 | -4567890123456789 | 0 - | 4567890123456789 | 123 | 4567890123456912 - | 4567890123456789 | 4567890123456789 | 9135780246913578 -(5 rows) - -SELECT '' AS five, q1, q2, q1 - q2 AS minus FROM INT8_TBL ; - five | q1 | q2 | minus -------+------------------+-------------------+------------------- - | 123 | 456 | -333 - | 123 | 4567890123456789 | -4567890123456666 - | 4567890123456789 | -4567890123456789 | 9135780246913578 - | 4567890123456789 | 123 | 4567890123456666 - | 4567890123456789 | 4567890123456789 | 0 -(5 rows) - -SELECT '' AS three, q1, q2, q1 * q2 AS multiply FROM INT8_TBL ; -ERROR: bigint out of range -SELECT '' AS three, q1, q2, q1 * q2 AS multiply FROM INT8_TBL - WHERE q1 < 1000 or (q2 > 0 and q2 < 1000) ; - three | q1 | q2 | multiply --------+------------------+------------------+-------------------- - | 123 | 456 | 56088 - | 123 | 4567890123456789 | 561850485185185047 - | 4567890123456789 | 123 | 561850485185185047 -(3 rows) - -SELECT '' AS five, q1, q2, q1 / q2 AS divide FROM INT8_TBL ; - five | q1 | q2 | divide -------+------------------+-------------------+---------------- - | 123 | 456 | 0 - | 123 | 4567890123456789 | 0 - | 4567890123456789 | -4567890123456789 | -1 - | 4567890123456789 | 123 | 37137318076884 - | 4567890123456789 | 4567890123456789 | 1 -(5 rows) - -SELECT '' AS five, q1, float8(q1) FROM INT8_TBL ; - five | q1 | float8 -------+------------------+---------------------- - | 123 | 123 - | 123 | 123 - | 4567890123456789 | 4.56789012345679e+15 - | 4567890123456789 | 4.56789012345679e+15 - | 4567890123456789 | 4.56789012345679e+15 -(5 rows) - -SELECT '' AS five, q2, float8(q2) FROM INT8_TBL ; - five | q2 | float8 -------+-------------------+----------------------- - | -4567890123456789 | -4.56789012345679e+15 - | 123 | 123 - | 456 | 456 - | 4567890123456789 | 4.56789012345679e+15 - | 4567890123456789 | 4.56789012345679e+15 -(5 rows) - -SELECT '' AS five, 2 * q1 AS "twice int4" FROM INT8_TBL ; - five | twice int4 -------+------------------ - | 246 - | 246 - | 9135780246913578 - | 9135780246913578 - | 9135780246913578 -(5 rows) - -SELECT '' AS five, q1 * 2 AS "twice int4" FROM INT8_TBL ; - five | twice int4 -------+------------------ - | 246 - | 246 - | 9135780246913578 - | 9135780246913578 - | 9135780246913578 -(5 rows) - --- TO_CHAR() --- -SELECT '' AS to_char_1, to_char(q1, '9G999G999G999G999G999'), to_char(q2, '9,999,999,999,999,999') - FROM INT8_TBL ; - to_char_1 | to_char | to_char ------------+------------------------+------------------------ - | 123 | 456 - | 123 | 4,567,890,123,456,789 - | 4,567,890,123,456,789 | 123 - | 4,567,890,123,456,789 | 4,567,890,123,456,789 - | 4,567,890,123,456,789 | -4,567,890,123,456,789 -(5 rows) - -SELECT '' AS to_char_2, to_char(q1, '9G999G999G999G999G999D999G999'), to_char(q2, '9,999,999,999,999,999.999,999') - FROM INT8_TBL ; - to_char_2 | to_char | to_char ------------+--------------------------------+-------------------------------- - | 123.000,000 | 456.000,000 - | 123.000,000 | 4,567,890,123,456,789.000,000 - | 4,567,890,123,456,789.000,000 | 123.000,000 - | 4,567,890,123,456,789.000,000 | 4,567,890,123,456,789.000,000 - | 4,567,890,123,456,789.000,000 | -4,567,890,123,456,789.000,000 -(5 rows) - -SELECT '' AS to_char_3, to_char( (q1 * -1), '9999999999999999PR'), to_char( (q2 * -1), '9999999999999999.999PR') - FROM INT8_TBL ; - to_char_3 | to_char | to_char ------------+--------------------+------------------------ - | <123> | <456.000> - | <123> | <4567890123456789.000> - | <4567890123456789> | <123.000> - | <4567890123456789> | <4567890123456789.000> - | <4567890123456789> | 4567890123456789.000 -(5 rows) - -SELECT '' AS to_char_4, to_char( (q1 * -1), '9999999999999999S'), to_char( (q2 * -1), 'S9999999999999999') - FROM INT8_TBL ; - to_char_4 | to_char | to_char ------------+-------------------+------------------- - | 123- | -456 - | 123- | -4567890123456789 - | 4567890123456789- | -123 - | 4567890123456789- | -4567890123456789 - | 4567890123456789- | +4567890123456789 -(5 rows) - -SELECT '' AS to_char_5, to_char(q2, 'MI9999999999999999') FROM INT8_TBL ; - to_char_5 | to_char ------------+------------------- - | 123 - | 456 - | 4567890123456789 - | 4567890123456789 - | -4567890123456789 -(5 rows) - -SELECT '' AS to_char_6, to_char(q2, 'FMS9999999999999999') FROM INT8_TBL ; - to_char_6 | to_char ------------+------------------- - | +123 - | +456 - | -4567890123456789 - | +4567890123456789 - | +4567890123456789 -(5 rows) - -SELECT '' AS to_char_7, to_char(q2, 'FM9999999999999999THPR') FROM INT8_TBL ; - to_char_7 | to_char ------------+-------------------- - | 123RD - | <4567890123456789> - | 4567890123456789TH - | 4567890123456789TH - | 456TH -(5 rows) - -SELECT '' AS to_char_8, to_char(q2, 'SG9999999999999999th') FROM INT8_TBL ; - to_char_8 | to_char ------------+--------------------- - | + 123rd - | -4567890123456789 - | +4567890123456789th - | +4567890123456789th - | + 456th -(5 rows) - -SELECT '' AS to_char_9, to_char(q2, '0999999999999999') FROM INT8_TBL ; - to_char_9 | to_char ------------+------------------- - | 0000000000000123 - | 0000000000000456 - | 4567890123456789 - | 4567890123456789 - | -4567890123456789 -(5 rows) - -SELECT '' AS to_char_10, to_char(q2, 'S0999999999999999') FROM INT8_TBL ; - to_char_10 | to_char -------------+------------------- - | +0000000000000123 - | +0000000000000456 - | -4567890123456789 - | +4567890123456789 - | +4567890123456789 -(5 rows) - -SELECT '' AS to_char_11, to_char(q2, 'FM0999999999999999') FROM INT8_TBL ; - to_char_11 | to_char -------------+------------------- - | 0000000000000123 - | 0000000000000456 - | 4567890123456789 - | 4567890123456789 - | -4567890123456789 -(5 rows) - -SELECT '' AS to_char_12, to_char(q2, 'FM9999999999999999.000') FROM INT8_TBL ; - to_char_12 | to_char -------------+----------------------- - | 123.000 - | 456.000 - | -4567890123456789.000 - | 4567890123456789.000 - | 4567890123456789.000 -(5 rows) - -SELECT '' AS to_char_13, to_char(q2, 'L9999999999999999.000') FROM INT8_TBL ; - to_char_13 | to_char -------------+------------------------ - | 123.000 - | 456.000 - | 4567890123456789.000 - | 4567890123456789.000 - | -4567890123456789.000 -(5 rows) - -SELECT '' AS to_char_14, to_char(q2, 'FM9999999999999999.999') FROM INT8_TBL ; - to_char_14 | to_char -------------+-------------------- - | 123. - | 456. - | -4567890123456789. - | 4567890123456789. - | 4567890123456789. -(5 rows) - -SELECT '' AS to_char_15, to_char(q2, 'S 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 . 9 9 9') FROM INT8_TBL ; - to_char_15 | to_char -------------+------------------------------------------- - | +1 2 3 . 0 0 0 - | +4 5 6 . 0 0 0 - | -4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 . 0 0 0 - | +4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 . 0 0 0 - | +4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 . 0 0 0 -(5 rows) - -SELECT '' AS to_char_16, to_char(q2, E'99999 "text" 9999 "9999" 999 "\\"text between quote marks\\"" 9999') FROM INT8_TBL ; - to_char_16 | to_char -------------+----------------------------------------------------------- - | 45678 text 9012 9999 345 "text between quote marks" 6789 - | 45678 text 9012 9999 345 "text between quote marks" 6789 - | -45678 text 9012 9999 345 "text between quote marks" 6789 - | text 9999 "text between quote marks" 123 - | text 9999 "text between quote marks" 456 -(5 rows) - -SELECT '' AS to_char_17, to_char(q2, '999999SG9999999999') FROM INT8_TBL ; - to_char_17 | to_char -------------+------------------- - | + 123 - | + 456 - | 456789-0123456789 - | 456789+0123456789 - | 456789+0123456789 -(5 rows) - --- check min/max values -select '-9223372036854775808'::int8; - int8 ----------------------- - -9223372036854775808 -(1 row) - -select '-9223372036854775809'::int8; -ERROR: value "-9223372036854775809" is out of range for type bigint -LINE 1: select '-9223372036854775809'::int8; - ^ -select '9223372036854775807'::int8; - int8 ---------------------- - 9223372036854775807 -(1 row) - -select '9223372036854775808'::int8; -ERROR: value "9223372036854775808" is out of range for type bigint -LINE 1: select '9223372036854775808'::int8; - ^ http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/f05c19ed/src/test/regress/expected/money.out ---------------------------------------------------------------------- diff --git a/src/test/regress/expected/money.out b/src/test/regress/expected/money.out deleted file mode 100755 index a4028c4..0000000 --- a/src/test/regress/expected/money.out +++ /dev/null @@ -1,158 +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; - 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'); -ERROR: invalid input syntax for type money: "xyz" -INSERT INTO MONEY_TBL(f1) VALUES ('5.0.0'); -ERROR: invalid input syntax for type money: "5.0.0" -INSERT INTO MONEY_TBL(f1) VALUES ('5 . 0'); -ERROR: invalid input syntax for type money: "5 . 0" -INSERT INTO MONEY_TBL(f1) VALUES ('5. 0'); -ERROR: invalid input syntax for type money: "5. 0" -INSERT INTO MONEY_TBL(f1) VALUES ('123 5'); -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; -ERROR: division by zero (seg0 localhost:40000 pid=281608) -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); -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; - 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) -