From aa51d0d1853310af888ce6fc6316a256fffcdb7b Mon Sep 17 00:00:00 2001 From: "Marc G. Fournier" Date: Sat, 5 Apr 1997 11:58:40 +0000 Subject: [PATCH] Purge out tests/expected that are now in sql/expected subdirectories from 'master' file Commit mods to regress.sh so that split out tests are run...look forward to finding out how to do a proper redirect to continue visual cleanup :) --- src/test/regress/expected.input | 2730 ------------------------------- src/test/regress/queries.source | 1392 +--------------- src/test/regress/regress.sh | 24 +- src/test/regress/sql/tests | 1 - 4 files changed, 19 insertions(+), 4128 deletions(-) diff --git a/src/test/regress/expected.input b/src/test/regress/expected.input index 50e34c9288..2c4ed9a02e 100644 --- a/src/test/regress/expected.input +++ b/src/test/regress/expected.input @@ -482,2736 +482,6 @@ tenk2 |t (19 rows) =============== running regression queries ... ================= -QUERY: SELECT 1 AS one; -one ---- - 1 -(1 row) - -QUERY: SELECT 't'::bool AS true; -true ----- -t -(1 row) - -QUERY: SELECT 'f'::bool AS false; -false ------ -f -(1 row) - -QUERY: SELECT 't'::bool or 'f'::bool AS true; -true ----- -t -(1 row) - -QUERY: SELECT 't'::bool and 'f'::bool AS false; -false ------ -f -(1 row) - -QUERY: SELECT not 'f'::bool AS true; -true ----- -t -(1 row) - -QUERY: SELECT 't'::bool = 'f'::bool AS false; -false ------ -f -(1 row) - -QUERY: SELECT 't'::bool <> 'f'::bool AS true; -true ----- -t -(1 row) - -QUERY: CREATE TABLE BOOLTBL1 (f1 bool); -QUERY: INSERT INTO BOOLTBL1 (f1) VALUES ('t'::bool); -QUERY: INSERT INTO BOOLTBL1 (f1) VALUES ('True'::bool); -QUERY: INSERT INTO BOOLTBL1 (f1) VALUES ('true'::bool); -QUERY: SELECT '' AS t_3, BOOLTBL1.*; -t_3|f1 ----+-- - |t - |t - |t -(3 rows) - -QUERY: SELECT '' AS t_3, BOOLTBL1.* - FROM BOOLTBL1 - WHERE f1 = 'true'::bool; -t_3|f1 ----+-- - |t - |t - |t -(3 rows) - -QUERY: SELECT '' AS t_3, BOOLTBL1.* - FROM BOOLTBL1 - WHERE f1 <> 'false'::bool; -t_3|f1 ----+-- - |t - |t - |t -(3 rows) - -QUERY: SELECT '' AS zero, BOOLTBL1.* - FROM BOOLTBL1 - WHERE booleq('false'::bool, f1); -zero|f1 -----+-- -(0 rows) - -QUERY: INSERT INTO BOOLTBL1 (f1) VALUES ('f'::bool); -QUERY: SELECT '' AS f_1, BOOLTBL1.* - FROM BOOLTBL1 - WHERE f1 = 'false'::bool; -f_1|f1 ----+-- - |f -(1 row) - -QUERY: CREATE TABLE BOOLTBL2 (f1 bool); -QUERY: INSERT INTO BOOLTBL2 (f1) VALUES ('f'::bool); -QUERY: INSERT INTO BOOLTBL2 (f1) VALUES ('false'::bool); -QUERY: INSERT INTO BOOLTBL2 (f1) VALUES ('False'::bool); -QUERY: INSERT INTO BOOLTBL2 (f1) - VALUES ('XXX'::bool); -QUERY: SELECT '' AS f_4, BOOLTBL2.*; -f_4|f1 ----+-- - |f - |f - |f - |f -(4 rows) - -QUERY: SELECT '' AS tf_12, 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) - -QUERY: SELECT '' AS tf_12, 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) - -QUERY: SELECT '' AS ff_4, BOOLTBL1.*, BOOLTBL2.* - WHERE BOOLTBL2.f1 = BOOLTBL1.f1 and BOOLTBL1.f1 = 'false'::bool; -ff_4|f1|f1 -----+--+-- - |f |f - |f |f - |f |f - |f |f -(4 rows) - -QUERY: SELECT '' AS tf_12_ff_4, BOOLTBL1.*, BOOLTBL2.* - WHERE BOOLTBL2.f1 = BOOLTBL1.f1 or BOOLTBL1.f1 = 'true'::bool; -tf_12_ff_4|f1|f1 -----------+--+-- - |t |f - |t |f - |t |f - |f |f - |t |f - |t |f - |t |f - |f |f - |t |f - |t |f - |t |f - |f |f - |t |f - |t |f - |t |f - |f |f -(16 rows) - -QUERY: CREATE TABLE ABSTIME_TBL (f1 abstime); -QUERY: INSERT INTO ABSTIME_TBL (f1) VALUES ('Jan 14, 1973 03:14:21'); -QUERY: INSERT INTO ABSTIME_TBL (f1) VALUES ('Mon May 1 00:30:30 1995'::abstime); -QUERY: INSERT INTO ABSTIME_TBL (f1) VALUES ('epoch'::abstime); -QUERY: INSERT INTO ABSTIME_TBL (f1) VALUES ('current'::abstime); -QUERY: INSERT INTO ABSTIME_TBL (f1) VALUES ('infinity'::abstime); -QUERY: INSERT INTO ABSTIME_TBL (f1) VALUES ('-infinity'::abstime); -QUERY: INSERT INTO ABSTIME_TBL (f1) VALUES ('May 10, 1943 23:59:12'); -QUERY: INSERT INTO ABSTIME_TBL (f1) VALUES ('Feb 35, 1946 10:00:00'); -QUERY: INSERT INTO ABSTIME_TBL (f1) VALUES ('Feb 28, 1984 25:08:10'); -QUERY: INSERT INTO ABSTIME_TBL (f1) VALUES ('bad date format'); -QUERY: INSERT INTO ABSTIME_TBL (f1) VALUES ('Jun 10, 1843'); -QUERY: CREATE TABLE RELTIME_TBL (f1 reltime); -QUERY: INSERT INTO RELTIME_TBL (f1) VALUES ('@ 1 minute'); -QUERY: INSERT INTO RELTIME_TBL (f1) VALUES ('@ 5 hour'); -QUERY: INSERT INTO RELTIME_TBL (f1) VALUES ('@ 10 day'); -QUERY: INSERT INTO RELTIME_TBL (f1) VALUES ('@ 34 year'); -QUERY: INSERT INTO RELTIME_TBL (f1) VALUES ('@ 3 months'); -QUERY: INSERT INTO RELTIME_TBL (f1) VALUES ('@ 14 seconds ago'); -QUERY: INSERT INTO RELTIME_TBL (f1) VALUES ('badly formatted reltime'); -QUERY: INSERT INTO RELTIME_TBL (f1) VALUES ('@ 30 eons ago'); -QUERY: CREATE TABLE TINTERVAL_TBL (f1 tinterval); -QUERY: INSERT INTO TINTERVAL_TBL (f1) - VALUES ('["-infinity" "infinity"]'); -QUERY: INSERT INTO TINTERVAL_TBL (f1) - VALUES ('["May 10, 1943 23:59:12" "Jan 14, 1973 03:14:21"]'); -QUERY: INSERT INTO TINTERVAL_TBL (f1) - VALUES ('["Sep 4, 1983 23:59:12" "Oct 4, 1983 23:59:12"]'); -QUERY: INSERT INTO TINTERVAL_TBL (f1) - VALUES ('["epoch" "Mon May 1 00:30:30 1995"]'); -QUERY: INSERT INTO TINTERVAL_TBL (f1) - VALUES ('["Feb 15 1990 12:15:03" "current"]'); -QUERY: INSERT INTO TINTERVAL_TBL (f1) - VALUES ('["bad time specifications" ""]'); -QUERY: INSERT INTO TINTERVAL_TBL (f1) - VALUES ('["" "infinity"]'); -QUERY: SELECT '' AS eleven, ABSTIME_TBL.*; -eleven|f1 -------+---------------------------- - |Sun Jan 14 03:14:21 1973 PST - |Mon May 01 00:30:30 1995 PDT - |epoch - |current - |infinity - |-infinity - |Mon May 10 23:59:12 1943 PWT - |Thu Mar 07 10:00:00 1946 PST - |Wed Dec 31 15:59:59 1969 PST - |Invalid Abstime - |Invalid Abstime -(11 rows) - -QUERY: SELECT '' AS eight, ABSTIME_TBL.* - WHERE ABSTIME_TBL.f1 < 'Jun 30, 2001'::abstime; -eight|f1 ------+---------------------------- - |Sun Jan 14 03:14:21 1973 PST - |Mon May 01 00:30:30 1995 PDT - |epoch - |current - |-infinity - |Mon May 10 23:59:12 1943 PWT - |Thu Mar 07 10:00:00 1946 PST - |Wed Dec 31 15:59:59 1969 PST -(8 rows) - -QUERY: SELECT '' AS eight, ABSTIME_TBL.* - WHERE ABSTIME_TBL.f1 > '-infinity'::abstime; -eight|f1 ------+---------------------------- - |Sun Jan 14 03:14:21 1973 PST - |Mon May 01 00:30:30 1995 PDT - |epoch - |current - |infinity - |Mon May 10 23:59:12 1943 PWT - |Thu Mar 07 10:00:00 1946 PST - |Wed Dec 31 15:59:59 1969 PST -(8 rows) - -QUERY: SELECT '' AS eight, ABSTIME_TBL.* - WHERE 'May 10, 1943 23:59:12'::abstime <> ABSTIME_TBL.f1; -eight|f1 ------+---------------------------- - |Sun Jan 14 03:14:21 1973 PST - |Mon May 01 00:30:30 1995 PDT - |epoch - |current - |infinity - |-infinity - |Thu Mar 07 10:00:00 1946 PST - |Wed Dec 31 15:59:59 1969 PST -(8 rows) - -QUERY: SELECT '' AS one, ABSTIME_TBL.* - WHERE 'current'::abstime = ABSTIME_TBL.f1; -one|f1 ----+------- - |current -(1 row) - -QUERY: SELECT '' AS five, ABSTIME_TBL.* - WHERE 'epoch'::abstime >= ABSTIME_TBL.f1; -five|f1 -----+---------------------------- - |epoch - |-infinity - |Mon May 10 23:59:12 1943 PWT - |Thu Mar 07 10:00:00 1946 PST - |Wed Dec 31 15:59:59 1969 PST -(5 rows) - -QUERY: SELECT '' AS six, ABSTIME_TBL.* - WHERE ABSTIME_TBL.f1 <= 'Jan 14, 1973 03:14:21'::abstime; -six|f1 ----+---------------------------- - |Sun Jan 14 03:14:21 1973 PST - |epoch - |-infinity - |Mon May 10 23:59:12 1943 PWT - |Thu Mar 07 10:00:00 1946 PST - |Wed Dec 31 15:59:59 1969 PST -(6 rows) - -QUERY: SELECT '' AS six, ABSTIME_TBL.* - WHERE ABSTIME_TBL.f1 - '["Apr 1 1945 00:00:00" "Dec 30 1999 23:00:00"]'::tinterval; -six|f1 ----+---------------------------- - |Sun Jan 14 03:14:21 1973 PST - |Mon May 01 00:30:30 1995 PDT - |epoch - |current - |Thu Mar 07 10:00:00 1946 PST - |Wed Dec 31 15:59:59 1969 PST -(6 rows) - -QUERY: SELECT '' AS five, ABSTIME_TBL.* - WHERE (ABSTIME_TBL.f1 + '@ 3 year'::reltime) - < 'Jan 14 14:00:00 1977'::abstime; -five|f1 -----+---------------------------- - |Sun Jan 14 03:14:21 1973 PST - |epoch - |Mon May 10 23:59:12 1943 PWT - |Thu Mar 07 10:00:00 1946 PST - |Wed Dec 31 15:59:59 1969 PST -(5 rows) - -QUERY: SELECT '' AS five, ABSTIME_TBL.* - WHERE (ABSTIME_TBL.f1 + '@ 3 year ago'::reltime) - < 'Jan 14 14:00:00 1971'::abstime; -five|f1 -----+---------------------------- - |Sun Jan 14 03:14:21 1973 PST - |epoch - |Mon May 10 23:59:12 1943 PWT - |Thu Mar 07 10:00:00 1946 PST - |Wed Dec 31 15:59:59 1969 PST -(5 rows) - -QUERY: SELECT '' AS five, ABSTIME_TBL.* - WHERE (ABSTIME_TBL.f1 - '@ 3 year'::reltime) - < 'Jan 14 14:00:00 1971'::abstime; -five|f1 -----+---------------------------- - |Sun Jan 14 03:14:21 1973 PST - |epoch - |Mon May 10 23:59:12 1943 PWT - |Thu Mar 07 10:00:00 1946 PST - |Wed Dec 31 15:59:59 1969 PST -(5 rows) - -QUERY: SELECT '' AS five, ABSTIME_TBL.* - WHERE (ABSTIME_TBL.f1 - '@ 3 year ago'::reltime) - < 'Jan 14 14:00:00 1977'::abstime; -five|f1 -----+---------------------------- - |Sun Jan 14 03:14:21 1973 PST - |epoch - |Mon May 10 23:59:12 1943 PWT - |Thu Mar 07 10:00:00 1946 PST - |Wed Dec 31 15:59:59 1969 PST -(5 rows) - -QUERY: SELECT '' AS twenty, ABSTIME_TBL.*, RELTIME_TBL.* - WHERE (ABSTIME_TBL.f1 + RELTIME_TBL.f1) - < 'Jan 14 14:00:00 1971'::abstime; -twenty|f1 |f1 -------+----------------------------+---------------- - |epoch |@ 1 minute - |Mon May 10 23:59:12 1943 PWT|@ 1 minute - |Thu Mar 07 10:00:00 1946 PST|@ 1 minute - |Wed Dec 31 15:59:59 1969 PST|@ 1 minute - |epoch |@ 5 hours - |Mon May 10 23:59:12 1943 PWT|@ 5 hours - |Thu Mar 07 10:00:00 1946 PST|@ 5 hours - |Wed Dec 31 15:59:59 1969 PST|@ 5 hours - |epoch |@ 10 days - |Mon May 10 23:59:12 1943 PWT|@ 10 days - |Thu Mar 07 10:00:00 1946 PST|@ 10 days - |Wed Dec 31 15:59:59 1969 PST|@ 10 days - |epoch |@ 3 months - |Mon May 10 23:59:12 1943 PWT|@ 3 months - |Thu Mar 07 10:00:00 1946 PST|@ 3 months - |Wed Dec 31 15:59:59 1969 PST|@ 3 months - |epoch |@ 14 seconds ago - |Mon May 10 23:59:12 1943 PWT|@ 14 seconds ago - |Thu Mar 07 10:00:00 1946 PST|@ 14 seconds ago - |Wed Dec 31 15:59:59 1969 PST|@ 14 seconds ago -(20 rows) - -QUERY: SELECT '' AS eight, RELTIME_TBL.*; -eight|f1 ------+----------------- - |@ 1 minute - |@ 5 hours - |@ 10 days - |@ 34 years - |@ 3 months - |@ 14 seconds ago - |Undefined RelTime - |Undefined RelTime -(8 rows) - -QUERY: SELECT '' AS five, RELTIME_TBL.* - WHERE RELTIME_TBL.f1 <> '@ 10 days'::reltime; -five|f1 -----+---------------- - |@ 1 minute - |@ 5 hours - |@ 34 years - |@ 3 months - |@ 14 seconds ago -(5 rows) - -QUERY: SELECT '' AS three, RELTIME_TBL.* - WHERE RELTIME_TBL.f1 <= '@ 5 hours'::reltime; -three|f1 ------+---------------- - |@ 1 minute - |@ 5 hours - |@ 14 seconds ago -(3 rows) - -QUERY: SELECT '' AS three, RELTIME_TBL.* - WHERE RELTIME_TBL.f1 < '@ 1 day'::reltime; -three|f1 ------+---------------- - |@ 1 minute - |@ 5 hours - |@ 14 seconds ago -(3 rows) - -QUERY: SELECT '' AS one, RELTIME_TBL.* - WHERE RELTIME_TBL.f1 = '@ 34 years'::reltime; -one|f1 ----+---------- - |@ 34 years -(1 row) - -QUERY: SELECT '' AS two, RELTIME_TBL.* - WHERE RELTIME_TBL.f1 >= '@ 1 month'::reltime; -two|f1 ----+---------- - |@ 34 years - |@ 3 months -(2 rows) - -QUERY: SELECT '' AS five, RELTIME_TBL.* - WHERE RELTIME_TBL.f1 > '@ 3 seconds ago'::reltime; -five|f1 -----+---------- - |@ 1 minute - |@ 5 hours - |@ 10 days - |@ 34 years - |@ 3 months -(5 rows) - -QUERY: SELECT '' AS fifteen, r1.*, r2.* - FROM RELTIME_TBL r1, RELTIME_TBL r2 - WHERE r1.f1 > r2.f1; -fifteen|f1 |f1 --------+----------+---------------- - |@ 5 hours |@ 1 minute - |@ 10 days |@ 1 minute - |@ 34 years|@ 1 minute - |@ 3 months|@ 1 minute - |@ 10 days |@ 5 hours - |@ 34 years|@ 5 hours - |@ 3 months|@ 5 hours - |@ 34 years|@ 10 days - |@ 3 months|@ 10 days - |@ 34 years|@ 3 months - |@ 1 minute|@ 14 seconds ago - |@ 5 hours |@ 14 seconds ago - |@ 10 days |@ 14 seconds ago - |@ 34 years|@ 14 seconds ago - |@ 3 months|@ 14 seconds ago -(15 rows) - -QUERY: SELECT '' AS seven, TINTERVAL_TBL.*; -seven|f1 ------+--------------------------------------------------------------- - |['-infinity' 'infinity'] - |['Mon May 10 23:59:12 1943 PWT' 'Sun Jan 14 03:14:21 1973 PST'] - |['Sun Sep 04 23:59:12 1983 PDT' 'Tue Oct 04 23:59:12 1983 PDT'] - |['epoch' 'Mon May 01 00:30:30 1995 PDT'] - |['Thu Feb 15 12:15:03 1990 PST' 'current'] - |['Undefined Range'] - |['Undefined Range'] -(7 rows) - -QUERY: SELECT '' AS one, t.* - FROM TINTERVAL_TBL t - WHERE t.f1 #= '@ 1 months'; -one|f1 ----+--------------------------------------------------------------- - |['Sun Sep 04 23:59:12 1983 PDT' 'Tue Oct 04 23:59:12 1983 PDT'] -(1 row) - -QUERY: SELECT '' AS three, t.* - FROM TINTERVAL_TBL t - WHERE t.f1 #<> '@ 1 months'; -three|f1 ------+--------------------------------------------------------------- - |['Mon May 10 23:59:12 1943 PWT' 'Sun Jan 14 03:14:21 1973 PST'] - |['epoch' 'Mon May 01 00:30:30 1995 PDT'] - |['Thu Feb 15 12:15:03 1990 PST' 'current'] -(3 rows) - -QUERY: SELECT '' AS zero, t.* - FROM TINTERVAL_TBL t - WHERE t.f1 #< '@ 1 month'; -zero|f1 -----+-- -(0 rows) - -QUERY: SELECT '' AS one, t.* - FROM TINTERVAL_TBL t - WHERE t.f1 #<= '@ 1 month'; -one|f1 ----+--------------------------------------------------------------- - |['Sun Sep 04 23:59:12 1983 PDT' 'Tue Oct 04 23:59:12 1983 PDT'] -(1 row) - -QUERY: SELECT '' AS three, t.* - FROM TINTERVAL_TBL t - WHERE t.f1 #> '@ 1 year'; -three|f1 ------+--------------------------------------------------------------- - |['Mon May 10 23:59:12 1943 PWT' 'Sun Jan 14 03:14:21 1973 PST'] - |['epoch' 'Mon May 01 00:30:30 1995 PDT'] - |['Thu Feb 15 12:15:03 1990 PST' 'current'] -(3 rows) - -QUERY: SELECT '' AS three, t.* - FROM TINTERVAL_TBL t - WHERE t.f1 #>= '@ 3 years'; -three|f1 ------+--------------------------------------------------------------- - |['Mon May 10 23:59:12 1943 PWT' 'Sun Jan 14 03:14:21 1973 PST'] - |['epoch' 'Mon May 01 00:30:30 1995 PDT'] - |['Thu Feb 15 12:15:03 1990 PST' 'current'] -(3 rows) - -QUERY: SELECT '' AS three, t1.* - FROM TINTERVAL_TBL t1 - WHERE t1.f1 && - '["Aug 15 14:23:19 1983" "Sep 16 14:23:19 1983"]'::tinterval; -three|f1 ------+--------------------------------------------------------------- - |['-infinity' 'infinity'] - |['Sun Sep 04 23:59:12 1983 PDT' 'Tue Oct 04 23:59:12 1983 PDT'] - |['epoch' 'Mon May 01 00:30:30 1995 PDT'] -(3 rows) - -QUERY: SELECT '' AS five, t1.*, t2.* - FROM TINTERVAL_TBL t1, TINTERVAL_TBL t2 - WHERE t1.f1 && t2.f1 and - t1.f1 = t2.f1; -five|f1 |f1 -----+---------------------------------------------------------------+--------------------------------------------------------------- - |['-infinity' 'infinity'] |['-infinity' 'infinity'] - |['Mon May 10 23:59:12 1943 PWT' 'Sun Jan 14 03:14:21 1973 PST']|['Mon May 10 23:59:12 1943 PWT' 'Sun Jan 14 03:14:21 1973 PST'] - |['Sun Sep 04 23:59:12 1983 PDT' 'Tue Oct 04 23:59:12 1983 PDT']|['Sun Sep 04 23:59:12 1983 PDT' 'Tue Oct 04 23:59:12 1983 PDT'] - |['epoch' 'Mon May 01 00:30:30 1995 PDT'] |['epoch' 'Mon May 01 00:30:30 1995 PDT'] - |['Thu Feb 15 12:15:03 1990 PST' 'current'] |['Thu Feb 15 12:15:03 1990 PST' 'current'] -(5 rows) - -QUERY: SELECT '' AS fourteen, t1.*, t2.* - FROM TINTERVAL_TBL t1, TINTERVAL_TBL t2 - WHERE t1.f1 && t2.f1 and - not t1.f1 = t2.f1; -fourteen|f1 |f1 ---------+---------------------------------------------------------------+--------------------------------------------------------------- - |['Mon May 10 23:59:12 1943 PWT' 'Sun Jan 14 03:14:21 1973 PST']|['-infinity' 'infinity'] - |['Sun Sep 04 23:59:12 1983 PDT' 'Tue Oct 04 23:59:12 1983 PDT']|['-infinity' 'infinity'] - |['epoch' 'Mon May 01 00:30:30 1995 PDT'] |['-infinity' 'infinity'] - |['Thu Feb 15 12:15:03 1990 PST' 'current'] |['-infinity' 'infinity'] - |['-infinity' 'infinity'] |['Mon May 10 23:59:12 1943 PWT' 'Sun Jan 14 03:14:21 1973 PST'] - |['epoch' 'Mon May 01 00:30:30 1995 PDT'] |['Mon May 10 23:59:12 1943 PWT' 'Sun Jan 14 03:14:21 1973 PST'] - |['-infinity' 'infinity'] |['Sun Sep 04 23:59:12 1983 PDT' 'Tue Oct 04 23:59:12 1983 PDT'] - |['epoch' 'Mon May 01 00:30:30 1995 PDT'] |['Sun Sep 04 23:59:12 1983 PDT' 'Tue Oct 04 23:59:12 1983 PDT'] - |['-infinity' 'infinity'] |['epoch' 'Mon May 01 00:30:30 1995 PDT'] - |['Mon May 10 23:59:12 1943 PWT' 'Sun Jan 14 03:14:21 1973 PST']|['epoch' 'Mon May 01 00:30:30 1995 PDT'] - |['Sun Sep 04 23:59:12 1983 PDT' 'Tue Oct 04 23:59:12 1983 PDT']|['epoch' 'Mon May 01 00:30:30 1995 PDT'] - |['Thu Feb 15 12:15:03 1990 PST' 'current'] |['epoch' 'Mon May 01 00:30:30 1995 PDT'] - |['-infinity' 'infinity'] |['Thu Feb 15 12:15:03 1990 PST' 'current'] - |['epoch' 'Mon May 01 00:30:30 1995 PDT'] |['Thu Feb 15 12:15:03 1990 PST' 'current'] -(14 rows) - -QUERY: SELECT '' AS five, t1.* - FROM TINTERVAL_TBL t1 - WHERE not t1.f1 << - '["Aug 15 14:23:19 1980" "Sep 16 14:23:19 1990"]'::tinterval; -five|f1 -----+--------------------------------------------------------------- - |['Mon May 10 23:59:12 1943 PWT' 'Sun Jan 14 03:14:21 1973 PST'] - |['Sun Sep 04 23:59:12 1983 PDT' 'Tue Oct 04 23:59:12 1983 PDT'] - |['Thu Feb 15 12:15:03 1990 PST' 'current'] - |['Undefined Range'] - |['Undefined Range'] -(5 rows) - -QUERY: SELECT '' AS three, t1.* - FROM TINTERVAL_TBL t1 - WHERE t1.f1 && - ('Aug 15 14:23:19 1983'::abstime <#> - 'Sep 16 14:23:19 1983'::abstime); -three|f1 ------+--------------------------------------------------------------- - |['-infinity' 'infinity'] - |['Sun Sep 04 23:59:12 1983 PDT' 'Tue Oct 04 23:59:12 1983 PDT'] - |['epoch' 'Mon May 01 00:30:30 1995 PDT'] -(3 rows) - -QUERY: CREATE TABLE BOX_TBL (f1 box); -QUERY: INSERT INTO BOX_TBL (f1) VALUES ('(2.0,2.0,0.0,0.0)'); -QUERY: INSERT INTO BOX_TBL (f1) VALUES ('(1.0,1.0,3.0,3.0)'); -QUERY: INSERT INTO BOX_TBL (f1) VALUES ('(2.5, 2.5, 2.5,3.5)'); -QUERY: INSERT INTO BOX_TBL (f1) VALUES ('(3.0, 3.0,3.0,3.0)'); -QUERY: INSERT INTO BOX_TBL (f1) VALUES ('(2.3, 4.5)'); -WARN:Bad box external representation '(2.3, 4.5)' -QUERY: INSERT INTO BOX_TBL (f1) VALUES ('asdfasdf(ad'); -WARN:Bad box external representation 'asdfasdf(ad' -QUERY: SELECT '' AS four, BOX_TBL.*; -four|f1 -----+----------------- - |(2,2,0,0) - |(3,3,1,1) - |(2.5,3.5,2.5,2.5) - |(3,3,3,3) -(4 rows) - -QUERY: SELECT '' AS four, b.*, box_area(b.f1) as barea - FROM BOX_TBL b; -four|f1 |barea -----+-----------------+----- - |(2,2,0,0) | 4 - |(3,3,1,1) | 4 - |(2.5,3.5,2.5,2.5)| 0 - |(3,3,3,3) | 0 -(4 rows) - -QUERY: SELECT '' AS three, b.f1 - FROM BOX_TBL b - WHERE b.f1 && '(2.5,2.5,1.0,1.0)'::box; -three|f1 ------+----------------- - |(2,2,0,0) - |(3,3,1,1) - |(2.5,3.5,2.5,2.5) -(3 rows) - -QUERY: SELECT '' AS two, b1.* - FROM BOX_TBL b1 - WHERE b1.f1 &< '(2.0,2.0,2.5,2.5)'::box; -two|f1 ----+----------------- - |(2,2,0,0) - |(2.5,3.5,2.5,2.5) -(2 rows) - -QUERY: SELECT '' AS two, b1.* - FROM BOX_TBL b1 - WHERE b1.f1 &> '(2.0,2.0,2.5,2.5)'::box; -two|f1 ----+----------------- - |(2.5,3.5,2.5,2.5) - |(3,3,3,3) -(2 rows) - -QUERY: SELECT '' AS two, b.f1 - FROM BOX_TBL b - WHERE b.f1 << '(3.0,3.0,5.0,5.0)'::box; -two|f1 ----+----------------- - |(2,2,0,0) - |(2.5,3.5,2.5,2.5) -(2 rows) - -QUERY: SELECT '' AS four, b.f1 - FROM BOX_TBL b - WHERE b.f1 <= '(3.0,3.0,5.0,5.0)'::box; -four|f1 -----+----------------- - |(2,2,0,0) - |(3,3,1,1) - |(2.5,3.5,2.5,2.5) - |(3,3,3,3) -(4 rows) - -QUERY: SELECT '' AS two, b.f1 - FROM BOX_TBL b - WHERE b.f1 < '(3.0,3.0,5.0,5.0)'::box; -two|f1 ----+----------------- - |(2.5,3.5,2.5,2.5) - |(3,3,3,3) -(2 rows) - -QUERY: SELECT '' AS two, b.f1 - FROM BOX_TBL b - WHERE b.f1 = '(3.0,3.0,5.0,5.0)'::box; -two|f1 ----+--------- - |(2,2,0,0) - |(3,3,1,1) -(2 rows) - -QUERY: SELECT '' AS two, b.f1 - FROM BOX_TBL b - WHERE b.f1 > '(3.5,3.0,4.5,3.0)'::box; -two|f1 ----+--------- - |(2,2,0,0) - |(3,3,1,1) -(2 rows) - -QUERY: SELECT '' AS four, b.f1 - FROM BOX_TBL b - WHERE b.f1 >= '(3.5,3.0,4.5,3.0)'::box; -four|f1 -----+----------------- - |(2,2,0,0) - |(3,3,1,1) - |(2.5,3.5,2.5,2.5) - |(3,3,3,3) -(4 rows) - -QUERY: SELECT '' AS two, b.f1 - FROM BOX_TBL b - WHERE '(3.0,3.0,5.0,5.0)'::box >> b.f1; -two|f1 ----+----------------- - |(2,2,0,0) - |(2.5,3.5,2.5,2.5) -(2 rows) - -QUERY: SELECT '' AS three, b.f1 - FROM BOX_TBL b - WHERE b.f1 @ '(0,0,3,3)'::box; -three|f1 ------+--------- - |(2,2,0,0) - |(3,3,1,1) - |(3,3,3,3) -(3 rows) - -QUERY: SELECT '' AS three, b.f1 - FROM BOX_TBL b - WHERE '(0,0,3,3)'::box ~ b.f1; -three|f1 ------+--------- - |(2,2,0,0) - |(3,3,1,1) - |(3,3,3,3) -(3 rows) - -QUERY: SELECT '' AS one, b.f1 - FROM BOX_TBL b - WHERE '(1,1,3,3)'::box ~= b.f1; -one|f1 ----+--------- - |(3,3,1,1) -(1 row) - -QUERY: SELECT '' AS four, @@(b1.f1) AS p - FROM BOX_TBL b1; -four|p -----+------- - |(1,1) - |(2,2) - |(2.5,3) - |(3,3) -(4 rows) - -QUERY: SELECT '' AS one, b1.*, b2.* - FROM BOX_TBL b1, BOX_TBL b2 - WHERE b1.f1 ~ b2.f1 and not b1.f1 ~= b2.f1; -one|f1 |f1 ----+---------+--------- - |(3,3,1,1)|(3,3,3,3) -(1 row) - -QUERY: CREATE TABLE CHAR_TBL(f1 char); -QUERY: INSERT INTO CHAR_TBL (f1) VALUES ('a'); -QUERY: INSERT INTO CHAR_TBL (f1) VALUES ('A'); -QUERY: INSERT INTO CHAR_TBL (f1) VALUES ('1'); -QUERY: INSERT INTO CHAR_TBL (f1) VALUES (2); -QUERY: INSERT INTO CHAR_TBL (f1) VALUES ('3'); -QUERY: INSERT INTO CHAR_TBL (f1) VALUES (''); -QUERY: INSERT INTO CHAR_TBL (f1) VALUES ('cd'); -QUERY: SELECT '' AS seven, CHAR_TBL.*; -seven|f1 ------+-- - |a - |A - |1 - |2 - |3 - | - |c -(7 rows) - -QUERY: SELECT '' AS six, c.* - FROM CHAR_TBL c - WHERE c.f1 <> 'a'; -six|f1 ----+-- - |A - |1 - |2 - |3 - | - |c -(6 rows) - -QUERY: SELECT '' AS one, c.* - FROM CHAR_TBL c - WHERE c.f1 = 'a'; -one|f1 ----+-- - |a -(1 row) - -QUERY: SELECT '' AS five, c.* - FROM CHAR_TBL c - WHERE c.f1 < 'a'; -five|f1 -----+-- - |A - |1 - |2 - |3 - | -(5 rows) - -QUERY: SELECT '' AS six, c.* - FROM CHAR_TBL c - WHERE c.f1 <= 'a'; -six|f1 ----+-- - |a - |A - |1 - |2 - |3 - | -(6 rows) - -QUERY: SELECT '' AS one, c.* - FROM CHAR_TBL c - WHERE c.f1 > 'a'; -one|f1 ----+-- - |c -(1 row) - -QUERY: SELECT '' AS two, c.* - FROM CHAR_TBL c - WHERE c.f1 >= 'a'; -two|f1 ----+-- - |a - |c -(2 rows) - -QUERY: CREATE TABLE CHAR2_TBL(f1 char2); -QUERY: INSERT INTO CHAR2_TBL (f1) VALUES ('AB'); -QUERY: INSERT INTO CHAR2_TBL (f1) VALUES ('ab'); -QUERY: INSERT INTO CHAR2_TBL (f1) VALUES ('ZY'); -QUERY: INSERT INTO CHAR2_TBL (f1) VALUES ('34'); -QUERY: INSERT INTO CHAR2_TBL (f1) VALUES ('d'); -QUERY: INSERT INTO CHAR2_TBL (f1) VALUES (''); -QUERY: INSERT INTO CHAR2_TBL (f1) VALUES ('12345'); -QUERY: SELECT '' AS seven, CHAR2_TBL.*; -seven|f1 ------+-- - |AB - |ab - |ZY - |34 - |d - | - |12 -(7 rows) - -QUERY: SELECT '' AS six, c.f1 FROM CHAR2_TBL c WHERE c.f1 <> 'AB'; -six|f1 ----+-- - |ab - |ZY - |34 - |d - | - |12 -(6 rows) - -QUERY: SELECT '' AS one, c.f1 FROM CHAR2_TBL c WHERE c.f1 = 'AB'; -one|f1 ----+-- - |AB -(1 row) - -QUERY: SELECT '' AS three, c.f1 FROM CHAR2_TBL c WHERE c.f1 < 'AB'; -three|f1 ------+-- - |34 - | - |12 -(3 rows) - -QUERY: SELECT '' AS four, c.f1 FROM CHAR2_TBL c WHERE c.f1 <= 'AB'; -four|f1 -----+-- - |AB - |34 - | - |12 -(4 rows) - -QUERY: SELECT '' AS three, c.f1 FROM CHAR2_TBL c WHERE c.f1 > 'AB'; -three|f1 ------+-- - |ab - |ZY - |d -(3 rows) - -QUERY: SELECT '' AS four, c.f1 FROM CHAR2_TBL c WHERE c.f1 >= 'AB'; -four|f1 -----+-- - |AB - |ab - |ZY - |d -(4 rows) - -QUERY: SELECT '' AS seven, c.f1 FROM CHAR2_TBL c WHERE c.f1 ~ '.*'; -seven|f1 ------+-- - |AB - |ab - |ZY - |34 - |d - | - |12 -(7 rows) - -QUERY: SELECT '' AS zero, c.f1 FROM CHAR2_TBL c WHERE c.f1 !~ '.*'; -zero|f1 -----+-- -(0 rows) - -QUERY: SELECT '' AS one, c.f1 FROM CHAR2_TBL c WHERE c.f1 ~ '34'; -one|f1 ----+-- - |34 -(1 row) - -QUERY: SELECT '' AS one, c.f1 FROM CHAR2_TBL c WHERE c.f1 ~ '3.*'; -one|f1 ----+-- - |34 -(1 row) - -QUERY: CREATE TABLE CHAR4_TBL (f1 char4); -QUERY: INSERT INTO CHAR4_TBL(f1) VALUES ('ABCD'); -QUERY: INSERT INTO CHAR4_TBL(f1) VALUES ('abcd'); -QUERY: INSERT INTO CHAR4_TBL(f1) VALUES ('ZYWZ'); -QUERY: INSERT INTO CHAR4_TBL(f1) VALUES ('343f'); -QUERY: INSERT INTO CHAR4_TBL(f1) VALUES ('d34a'); -QUERY: INSERT INTO CHAR4_TBL(f1) VALUES (''); -QUERY: INSERT INTO CHAR4_TBL(f1) VALUES ('12345678'); -QUERY: SELECT '' AS seven, CHAR4_TBL.*; -seven|f1 ------+---- - |ABCD - |abcd - |ZYWZ - |343f - |d34a - | - |1234 -(7 rows) - -QUERY: SELECT '' AS six, c.f1 FROM CHAR4_TBL c WHERE c.f1 <> 'ABCD'; -six|f1 ----+---- - |abcd - |ZYWZ - |343f - |d34a - | - |1234 -(6 rows) - -QUERY: SELECT '' AS one, c.f1 FROM CHAR4_TBL c WHERE c.f1 = 'ABCD'; -one|f1 ----+---- - |ABCD -(1 row) - -QUERY: SELECT '' AS three, c.f1 FROM CHAR4_TBL c WHERE c.f1 < 'ABCD'; -three|f1 ------+---- - |343f - | - |1234 -(3 rows) - -QUERY: SELECT '' AS four, c.f1 FROM CHAR4_TBL c WHERE c.f1 <= 'ABCD'; -four|f1 -----+---- - |ABCD - |343f - | - |1234 -(4 rows) - -QUERY: SELECT '' AS three, c.f1 FROM CHAR4_TBL c WHERE c.f1 > 'ABCD'; -three|f1 ------+---- - |abcd - |ZYWZ - |d34a -(3 rows) - -QUERY: SELECT '' AS four, c.f1 FROM CHAR4_TBL c WHERE c.f1 >= 'ABCD'; -four|f1 -----+---- - |ABCD - |abcd - |ZYWZ - |d34a -(4 rows) - -QUERY: SELECT '' AS seven, c.f1 FROM CHAR4_TBL c WHERE c.f1 ~ '.*'; -seven|f1 ------+---- - |ABCD - |abcd - |ZYWZ - |343f - |d34a - | - |1234 -(7 rows) - -QUERY: SELECT '' AS zero, c.f1 FROM CHAR4_TBL c WHERE c.f1 !~ '.*'; -zero|f1 -----+-- -(0 rows) - -QUERY: SELECT '' AS three, c.f1 FROM CHAR4_TBL c WHERE c.f1 ~ '.*34.*'; -three|f1 ------+---- - |343f - |d34a - |1234 -(3 rows) - -QUERY: CREATE TABLE CHAR8_TBL(f1 char8); -QUERY: INSERT INTO CHAR8_TBL(f1) VALUES ('ABCDEFGH'); -QUERY: INSERT INTO CHAR8_TBL(f1) VALUES ('abcdefgh'); -QUERY: INSERT INTO CHAR8_TBL(f1) VALUES ('ZYWZ410-'); -QUERY: INSERT INTO CHAR8_TBL(f1) VALUES ('343f%2a'); -QUERY: INSERT INTO CHAR8_TBL(f1) VALUES ('d34aas'); -QUERY: INSERT INTO CHAR8_TBL(f1) VALUES (''); -QUERY: INSERT INTO CHAR8_TBL(f1) VALUES ('1234567890'); -QUERY: SELECT '' AS seven, CHAR8_TBL.*; -seven|f1 ------+-------- - |ABCDEFGH - |abcdefgh - |ZYWZ410- - |343f%2a - |d34aas - | - |12345678 -(7 rows) - -QUERY: SELECT '' AS six, c.f1 FROM CHAR8_TBL c WHERE c.f1 <> 'ABCDEFGH'; -six|f1 ----+-------- - |abcdefgh - |ZYWZ410- - |343f%2a - |d34aas - | - |12345678 -(6 rows) - -QUERY: SELECT '' AS one, c.f1 FROM CHAR8_TBL c WHERE c.f1 = 'ABCDEFGH'; -one|f1 ----+-------- - |ABCDEFGH -(1 row) - -QUERY: SELECT '' AS three, c.f1 FROM CHAR8_TBL c WHERE c.f1 < 'ABCDEFGH'; -three|f1 ------+-------- - |343f%2a - | - |12345678 -(3 rows) - -QUERY: SELECT '' AS four, c.f1 FROM CHAR8_TBL c WHERE c.f1 <= 'ABCDEFGH'; -four|f1 -----+-------- - |ABCDEFGH - |343f%2a - | - |12345678 -(4 rows) - -QUERY: SELECT '' AS three, c.f1 FROM CHAR8_TBL c WHERE c.f1 > 'ABCDEFGH'; -three|f1 ------+-------- - |abcdefgh - |ZYWZ410- - |d34aas -(3 rows) - -QUERY: SELECT '' AS four, c.f1 FROM CHAR8_TBL c WHERE c.f1 >= 'ABCDEFGH'; -four|f1 -----+-------- - |ABCDEFGH - |abcdefgh - |ZYWZ410- - |d34aas -(4 rows) - -QUERY: SELECT '' AS seven, c.f1 FROM CHAR8_TBL c WHERE c.f1 ~ '.*'; -seven|f1 ------+-------- - |ABCDEFGH - |abcdefgh - |ZYWZ410- - |343f%2a - |d34aas - | - |12345678 -(7 rows) - -QUERY: SELECT '' AS zero, c.f1 FROM CHAR8_TBL c WHERE c.f1 !~ '.*'; -zero|f1 -----+-- -(0 rows) - -QUERY: SELECT '' AS four, c.f1 FROM CHAR8_TBL c WHERE c.f1 ~ '[0-9]'; -four|f1 -----+-------- - |ZYWZ410- - |343f%2a - |d34aas - |12345678 -(4 rows) - -QUERY: SELECT '' AS three, c.f1 FROM CHAR8_TBL c WHERE c.f1 ~ '.*34.*'; -three|f1 ------+-------- - |343f%2a - |d34aas - |12345678 -(3 rows) - -QUERY: CREATE TABLE CHAR16_TBL(f1 char16); -QUERY: INSERT INTO CHAR16_TBL(f1) VALUES ('ABCDEFGHIJKLMNOP'); -QUERY: INSERT INTO CHAR16_TBL(f1) VALUES ('abcdefghijklmnop'); -QUERY: INSERT INTO CHAR16_TBL(f1) VALUES ('asdfghjkl;'); -QUERY: INSERT INTO CHAR16_TBL(f1) VALUES ('343f%2a'); -QUERY: INSERT INTO CHAR16_TBL(f1) VALUES ('d34aaasdf'); -QUERY: INSERT INTO CHAR16_TBL(f1) VALUES (''); -QUERY: INSERT INTO CHAR16_TBL(f1) VALUES ('1234567890ABCDEFGHIJKLMNOPQRSTUV'); -QUERY: SELECT '' AS seven, CHAR16_TBL.*; -seven|f1 ------+---------------- - |ABCDEFGHIJKLMNOP - |abcdefghijklmnop - |asdfghjkl; - |343f%2a - |d34aaasdf - | - |1234567890ABCDEF -(7 rows) - -QUERY: SELECT '' AS six, c.f1 FROM CHAR16_TBL c WHERE c.f1 <> 'ABCDEFGHIJKLMNOP'; -six|f1 ----+---------------- - |abcdefghijklmnop - |asdfghjkl; - |343f%2a - |d34aaasdf - | - |1234567890ABCDEF -(6 rows) - -QUERY: SELECT '' AS one, c.f1 FROM CHAR16_TBL c WHERE c.f1 = 'ABCDEFGHIJKLMNOP'; -one|f1 ----+---------------- - |ABCDEFGHIJKLMNOP -(1 row) - -QUERY: SELECT '' AS three, c.f1 FROM CHAR16_TBL c WHERE c.f1 < 'ABCDEFGHIJKLMNOP'; -three|f1 ------+---------------- - |343f%2a - | - |1234567890ABCDEF -(3 rows) - -QUERY: SELECT '' AS four, c.f1 FROM CHAR16_TBL c WHERE c.f1 <= 'ABCDEFGHIJKLMNOP'; -four|f1 -----+---------------- - |ABCDEFGHIJKLMNOP - |343f%2a - | - |1234567890ABCDEF -(4 rows) - -QUERY: SELECT '' AS three, c.f1 FROM CHAR16_TBL c WHERE c.f1 > 'ABCDEFGHIJKLMNOP'; -three|f1 ------+---------------- - |abcdefghijklmnop - |asdfghjkl; - |d34aaasdf -(3 rows) - -QUERY: SELECT '' AS four, c.f1 FROM CHAR16_TBL c WHERE c.f1 >= 'ABCDEFGHIJKLMNOP'; -four|f1 -----+---------------- - |ABCDEFGHIJKLMNOP - |abcdefghijklmnop - |asdfghjkl; - |d34aaasdf -(4 rows) - -QUERY: SELECT '' AS seven, c.f1 FROM CHAR16_TBL c WHERE c.f1 ~ '.*'; -seven|f1 ------+---------------- - |ABCDEFGHIJKLMNOP - |abcdefghijklmnop - |asdfghjkl; - |343f%2a - |d34aaasdf - | - |1234567890ABCDEF -(7 rows) - -QUERY: SELECT '' AS zero, c.f1 FROM CHAR16_TBL c WHERE c.f1 !~ '.*'; -zero|f1 -----+-- -(0 rows) - -QUERY: SELECT '' AS three, c.f1 FROM CHAR16_TBL c WHERE c.f1 ~ '[0-9]'; -three|f1 ------+---------------- - |343f%2a - |d34aaasdf - |1234567890ABCDEF -(3 rows) - -QUERY: SELECT '' AS two, c.f1 FROM CHAR16_TBL c WHERE c.f1 ~ '.*asdf.*'; -two|f1 ----+---------- - |asdfghjkl; - |d34aaasdf -(2 rows) - -QUERY: CREATE TABLE FLOAT4_TBL (f1 float4); -QUERY: INSERT INTO FLOAT4_TBL(f1) VALUES ('0.0'); -QUERY: INSERT INTO FLOAT4_TBL(f1) VALUES ('1004.30'); -QUERY: INSERT INTO FLOAT4_TBL(f1) VALUES ('-34.84'); -QUERY: INSERT INTO FLOAT4_TBL(f1) VALUES ('1.2345678901234e+20'); -QUERY: INSERT INTO FLOAT4_TBL(f1) VALUES ('1.2345678901234e-20'); -QUERY: INSERT INTO FLOAT4_TBL(f1) VALUES ('10e40'); -WARN: Bad float4 input format -- overflow - -QUERY: INSERT INTO FLOAT4_TBL(f1) VALUES ('-10e40'); -WARN: Bad float4 input format -- overflow - -QUERY: INSERT INTO FLOAT4_TBL(f1) VALUES ('10e-40'); -WARN: Bad float4 input format -- underflow - -QUERY: INSERT INTO FLOAT4_TBL(f1) VALUES ('-10e-40'); -WARN: Bad float4 input format -- underflow - -QUERY: SELECT '' AS five, FLOAT4_TBL.*; -five|f1 -----+----------- - |0 - |1004.3 - |-34.84 - |1.23457e+20 - |1.23457e-20 -(5 rows) - -QUERY: SELECT '' AS four, f.* FROM FLOAT4_TBL f WHERE f.f1 <> '1004.3'; -four|f1 -----+----------- - |0 - |-34.84 - |1.23457e+20 - |1.23457e-20 -(4 rows) - -QUERY: SELECT '' AS one, f.* FROM FLOAT4_TBL f WHERE f.f1 = '1004.3'; -one| f1 ----+------ - |1004.3 -(1 row) - -QUERY: SELECT '' AS three, f.* FROM FLOAT4_TBL f WHERE '1004.3' > f.f1; -three| f1 ------+----------- - | 0 - | -34.84 - |1.23457e-20 -(3 rows) - -QUERY: SELECT '' AS three, f.* FROM FLOAT4_TBL f WHERE f.f1 < '1004.3'; -three| f1 ------+----------- - | 0 - | -34.84 - |1.23457e-20 -(3 rows) - -QUERY: SELECT '' AS four, f.* FROM FLOAT4_TBL f WHERE '1004.3' >= f.f1; -four| f1 -----+----------- - | 0 - | 1004.3 - | -34.84 - |1.23457e-20 -(4 rows) - -QUERY: SELECT '' AS four, f.* FROM FLOAT4_TBL f WHERE f.f1 <= '1004.3'; -four| f1 -----+----------- - | 0 - | 1004.3 - | -34.84 - |1.23457e-20 -(4 rows) - -QUERY: SELECT '' AS three, f.f1, f.f1 * '-10' AS x FROM FLOAT4_TBL f - WHERE f.f1 > '0.0'; -three|f1 |x ------+-----------+------------ - |1004.3 |-10043 - |1.23457e+20|-1.23457e+21 - |1.23457e-20|-1.23457e-19 -(3 rows) - -QUERY: SELECT '' AS three, f.f1, f.f1 + '-10' AS x FROM FLOAT4_TBL f - WHERE f.f1 > '0.0'; -three|f1 |x ------+-----------+----------- - |1004.3 |994.3 - |1.23457e+20|1.23457e+20 - |1.23457e-20|-10 -(3 rows) - -QUERY: SELECT '' AS three, f.f1, f.f1 / '-10' AS x FROM FLOAT4_TBL f - WHERE f.f1 > '0.0'; -three|f1 |x ------+-----------+------------ - |1004.3 |-100.43 - |1.23457e+20|-1.23457e+19 - |1.23457e-20|-1.23457e-21 -(3 rows) - -QUERY: SELECT '' AS three, f.f1, f.f1 - '-10' AS x FROM FLOAT4_TBL f - WHERE f.f1 > '0.0'; -three|f1 |x ------+-----------+----------- - |1004.3 |1014.3 - |1.23457e+20|1.23457e+20 - |1.23457e-20|10 -(3 rows) - -QUERY: SELECT '' AS bad, f.f1 / '0.0' from FLOAT4_TBL f; -WARN:float4div: divide by 0.0 error -QUERY: SELECT '' AS five, FLOAT4_TBL.*; -five|f1 -----+----------- - |0 - |1004.3 - |-34.84 - |1.23457e+20 - |1.23457e-20 -(5 rows) - -QUERY: SELECT '' AS five, f.f1, @f.f1 AS abs_f1 FROM FLOAT4_TBL f; -five|f1 |abs_f1 -----+-----------+----------- - |0 |0 - |1004.3 |1004.3 - |-34.84 |34.84 - |1.23457e+20|1.23457e+20 - |1.23457e-20|1.23457e-20 -(5 rows) - -QUERY: UPDATE FLOAT4_TBL - SET f1 = FLOAT4_TBL.f1 * '-1' - WHERE FLOAT4_TBL.f1 > '0.0'; -QUERY: SELECT '' AS five, FLOAT4_TBL.*; -five|f1 -----+------------ - |0 - |-34.84 - |-1004.3 - |-1.23457e+20 - |-1.23457e-20 -(5 rows) - -QUERY: CREATE TABLE FLOAT8_TBL(f1 float8); -QUERY: INSERT INTO FLOAT8_TBL(f1) VALUES ('0.0'); -QUERY: INSERT INTO FLOAT8_TBL(f1) VALUES ('1004.30'); -QUERY: INSERT INTO FLOAT8_TBL(f1) VALUES ('-34.84'); -QUERY: INSERT INTO FLOAT8_TBL(f1) VALUES ('1.2345678901234e+200'); -QUERY: INSERT INTO FLOAT8_TBL(f1) VALUES ('1.2345678901234e-200'); -QUERY: INSERT INTO FLOAT8_TBL(f1) VALUES ('10e400'); -WARN: Bad float8 input format - -QUERY: INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e400'); -WARN: Bad float8 input format - -QUERY: INSERT INTO FLOAT8_TBL(f1) VALUES ('10e-400'); -WARN: Bad float8 input format - -QUERY: INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e-400'); -WARN: Bad float8 input format - -QUERY: SELECT '' AS five, FLOAT8_TBL.*; -five|f1 -----+-------------------- - |0 - |1004.3 - |-34.84 - |1.2345678901234e+200 - |1.2345678901234e-200 -(5 rows) - -QUERY: SELECT '' AS four, f.* FROM FLOAT8_TBL f WHERE f.f1 <> '1004.3'; -four|f1 -----+-------------------- - |0 - |-34.84 - |1.2345678901234e+200 - |1.2345678901234e-200 -(4 rows) - -QUERY: SELECT '' AS one, f.* FROM FLOAT8_TBL f WHERE f.f1 = '1004.3'; -one| f1 ----+------ - |1004.3 -(1 row) - -QUERY: SELECT '' AS three, f.* FROM FLOAT8_TBL f WHERE '1004.3' > f.f1; -three| f1 ------+-------------------- - | 0 - | -34.84 - |1.2345678901234e-200 -(3 rows) - -QUERY: SELECT '' AS three, f.* FROM FLOAT8_TBL f WHERE f.f1 < '1004.3'; -three| f1 ------+-------------------- - | 0 - | -34.84 - |1.2345678901234e-200 -(3 rows) - -QUERY: SELECT '' AS four, f.* FROM FLOAT8_TBL f WHERE '1004.3' >= f.f1; -four| f1 -----+-------------------- - | 0 - | 1004.3 - | -34.84 - |1.2345678901234e-200 -(4 rows) - -QUERY: SELECT '' AS four, f.* FROM FLOAT8_TBL f WHERE f.f1 <= '1004.3'; -four| f1 -----+-------------------- - | 0 - | 1004.3 - | -34.84 - |1.2345678901234e-200 -(4 rows) - -QUERY: SELECT '' AS three, f.f1, f.f1 * '-10' AS x - FROM FLOAT8_TBL f - WHERE f.f1 > '0.0'; -three|f1 |x ------+--------------------+--------------------- - |1004.3 |-10043 - |1.2345678901234e+200|-1.2345678901234e+201 - |1.2345678901234e-200|-1.2345678901234e-199 -(3 rows) - -QUERY: SELECT '' AS three, f.f1, f.f1 + '-10' AS x - FROM FLOAT8_TBL f - WHERE f.f1 > '0.0'; -three|f1 |x ------+--------------------+-------------------- - |1004.3 |994.3 - |1.2345678901234e+200|1.2345678901234e+200 - |1.2345678901234e-200|-10 -(3 rows) - -QUERY: SELECT '' AS three, f.f1, f.f1 / '-10' AS x - FROM FLOAT8_TBL f - WHERE f.f1 > '0.0'; -three|f1 |x ------+--------------------+--------------------- - |1004.3 |-100.43 - |1.2345678901234e+200|-1.2345678901234e+199 - |1.2345678901234e-200|-1.2345678901234e-201 -(3 rows) - -QUERY: SELECT '' AS three, f.f1, f.f1 - '-10' AS x - FROM FLOAT8_TBL f - WHERE f.f1 > '0.0'; -three|f1 |x ------+--------------------+-------------------- - |1004.3 |1014.3 - |1.2345678901234e+200|1.2345678901234e+200 - |1.2345678901234e-200|10 -(3 rows) - -QUERY: 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) - -QUERY: SELECT '' AS five, f.f1, @f.f1 AS abs_f1 - FROM FLOAT8_TBL f; -five|f1 |abs_f1 -----+--------------------+-------------------- - |0 |0 - |1004.3 |1004.3 - |-34.84 |34.84 - |1.2345678901234e+200|1.2345678901234e+200 - |1.2345678901234e-200|1.2345678901234e-200 -(5 rows) - -QUERY: SELECT '' AS five, f.f1, %f.f1 AS trunc_f1 - FROM FLOAT8_TBL f; -five|f1 |trunc_f1 -----+--------------------+-------------------- - |0 |0 - |1004.3 |1004 - |-34.84 |-34 - |1.2345678901234e+200|1.2345678901234e+200 - |1.2345678901234e-200|0 -(5 rows) - -QUERY: SELECT '' AS five, f.f1, f.f1 % AS round_f1 - FROM FLOAT8_TBL f; -five|f1 |round_f1 -----+--------------------+-------------------- - |0 |0 - |1004.3 |1004 - |-34.84 |-35 - |1.2345678901234e+200|1.2345678901234e+200 - |1.2345678901234e-200|0 -(5 rows) - -QUERY: SELECT '' AS three, f.f1, |/f.f1 AS sqrt_f1 - FROM FLOAT8_TBL f - WHERE f.f1 > '0.0'; -three|f1 |sqrt_f1 ------+--------------------+--------------------- - |1004.3 |31.6906926399535 - |1.2345678901234e+200|1.11111110611109e+100 - |1.2345678901234e-200|1.11111110611109e-100 -(3 rows) - -QUERY: SELECT '' AS three, f.f1, : ( ; f.f1) AS exp_ln_f1 - FROM FLOAT8_TBL f - WHERE f.f1 > '0.0'; -three|f1 |exp_ln_f1 ------+--------------------+--------------------- - |1004.3 |1004.3 - |1.2345678901234e+200|1.23456789012338e+200 - |1.2345678901234e-200|1.23456789012339e-200 -(3 rows) - -QUERY: SELECT '' AS five, f.f1, ||/f.f1 AS cbrt_f1 FROM FLOAT8_TBL f; -five|f1 |cbrt_f1 -----+--------------------+-------------------- - |0 |0 - |1004.3 |10.014312837827 - |-34.84 |-3.26607421344208 - |1.2345678901234e+200|4.97933859234765e+66 - |1.2345678901234e-200|2.3112042409018e-67 -(5 rows) - -QUERY: SELECT '' AS five, FLOAT8_TBL.*; -five|f1 -----+-------------------- - |0 - |1004.3 - |-34.84 - |1.2345678901234e+200 - |1.2345678901234e-200 -(5 rows) - -QUERY: UPDATE FLOAT8_TBL - SET f1 = FLOAT8_TBL.f1 * '-1' - WHERE FLOAT8_TBL.f1 > '0.0'; -QUERY: SELECT '' AS bad, f.f1 * '1e200' from FLOAT8_TBL f; -WARN: Bad float8 input format -- overflow - -QUERY: SELECT '' AS bad, f.f1 ^ '1e200' from FLOAT8_TBL f; -WARN: Bad float8 input format -- overflow - -QUERY: SELECT '' AS bad, (; (f.f1)) from FLOAT8_TBL f where f.f1 = '0.0' ; -WARN:can't take log of 0! -QUERY: SELECT '' AS bad, (; (f.f1)) from FLOAT8_TBL f where f.f1 < '0.0' ; -WARN:can't take log of a negative number -QUERY: SELECT '' AS bad, : (f.f1) from FLOAT8_TBL f; -WARN:exp() returned a floating point out of range - -QUERY: SELECT '' AS bad, f.f1 / '0.0' from FLOAT8_TBL f; -WARN:float8div: divide by 0.0 error -QUERY: SELECT '' AS five, FLOAT8_TBL.*; -five|f1 -----+--------------------- - |0 - |-34.84 - |-1004.3 - |-1.2345678901234e+200 - |-1.2345678901234e-200 -(5 rows) - -QUERY: CREATE TABLE INT2_TBL(f1 int2); -QUERY: INSERT INTO INT2_TBL(f1) VALUES ('0'); -QUERY: INSERT INTO INT2_TBL(f1) VALUES ('1234'); -QUERY: INSERT INTO INT2_TBL(f1) VALUES ('-1234'); -QUERY: INSERT INTO INT2_TBL(f1) VALUES ('34.5'); -WARN:pg_atoi: error in "34.5": can't parse ".5" -QUERY: INSERT INTO INT2_TBL(f1) VALUES ('32767'); -QUERY: INSERT INTO INT2_TBL(f1) VALUES ('-32767'); -QUERY: INSERT INTO INT2_TBL(f1) VALUES ('100000'); -WARN:pg_atoi: error reading "100000": Result too large -QUERY: INSERT INTO INT2_TBL(f1) VALUES ('asdf'); -WARN:pg_atoi: error in "asdf": can't parse "asdf" -QUERY: SELECT '' AS five, INT2_TBL.*; -five| f1 -----+------ - | 0 - | 1234 - | -1234 - | 32767 - |-32767 -(5 rows) - -QUERY: SELECT '' AS four, i.* FROM INT2_TBL i WHERE i.f1 <> '0'::int2; -four| f1 -----+------ - | 1234 - | -1234 - | 32767 - |-32767 -(4 rows) - -QUERY: SELECT '' AS four, i.* FROM INT2_TBL i WHERE i.f1 <> '0'::int4; -four| f1 -----+------ - | 1234 - | -1234 - | 32767 - |-32767 -(4 rows) - -QUERY: SELECT '' AS one, i.* FROM INT2_TBL i WHERE i.f1 = '0'::int2; -one|f1 ----+-- - | 0 -(1 row) - -QUERY: SELECT '' AS one, i.* FROM INT2_TBL i WHERE i.f1 = '0'::int4; -one|f1 ----+-- - | 0 -(1 row) - -QUERY: SELECT '' AS two, i.* FROM INT2_TBL i WHERE i.f1 < '0'::int2; -two| f1 ----+------ - | -1234 - |-32767 -(2 rows) - -QUERY: SELECT '' AS two, i.* FROM INT2_TBL i WHERE i.f1 < '0'::int4; -two| f1 ----+------ - | -1234 - |-32767 -(2 rows) - -QUERY: SELECT '' AS three, i.* FROM INT2_TBL i WHERE i.f1 <= '0'::int2; -three| f1 ------+------ - | 0 - | -1234 - |-32767 -(3 rows) - -QUERY: SELECT '' AS three, i.* FROM INT2_TBL i WHERE i.f1 <= '0'::int4; -three| f1 ------+------ - | 0 - | -1234 - |-32767 -(3 rows) - -QUERY: SELECT '' AS two, i.* FROM INT2_TBL i WHERE i.f1 > '0'::int2; -two| f1 ----+----- - | 1234 - |32767 -(2 rows) - -QUERY: SELECT '' AS two, i.* FROM INT2_TBL i WHERE i.f1 > '0'::int4; -two| f1 ----+----- - | 1234 - |32767 -(2 rows) - -QUERY: SELECT '' AS three, i.* FROM INT2_TBL i WHERE i.f1 >= '0'::int2; -three| f1 ------+----- - | 0 - | 1234 - |32767 -(3 rows) - -QUERY: SELECT '' AS three, i.* FROM INT2_TBL i WHERE i.f1 >= '0'::int4; -three| f1 ------+----- - | 0 - | 1234 - |32767 -(3 rows) - -QUERY: SELECT '' AS one, i.* FROM INT2_TBL i WHERE (i.f1 % '2'::int2) = '1'::int2; -one| f1 ----+----- - |32767 -(1 row) - -QUERY: SELECT '' AS three, i.* FROM INT2_TBL i WHERE (i.f1 % '2'::int4) = '0'::int2; -three| f1 ------+----- - | 0 - | 1234 - |-1234 -(3 rows) - -QUERY: SELECT '' AS five, i.f1, i.f1 * '2'::int2 AS x FROM INT2_TBL i; -five| f1| x -----+------+----- - | 0| 0 - | 1234| 2468 - | -1234|-2468 - | 32767| -2 - |-32767| 2 -(5 rows) - -QUERY: SELECT '' AS five, i.f1, i.f1 * '2'::int4 AS x FROM INT2_TBL i; -five| f1| x -----+------+------ - | 0| 0 - | 1234| 2468 - | -1234| -2468 - | 32767| 65534 - |-32767|-65534 -(5 rows) - -QUERY: SELECT '' AS five, i.f1, i.f1 + '2'::int2 AS x FROM INT2_TBL i; -five| f1| x -----+------+------ - | 0| 2 - | 1234| 1236 - | -1234| -1232 - | 32767|-32767 - |-32767|-32765 -(5 rows) - -QUERY: SELECT '' AS five, i.f1, i.f1 + '2'::int4 AS x FROM INT2_TBL i; -five| f1| x -----+------+------ - | 0| 2 - | 1234| 1236 - | -1234| -1232 - | 32767| 32769 - |-32767|-32765 -(5 rows) - -QUERY: SELECT '' AS five, i.f1, i.f1 - '2'::int2 AS x FROM INT2_TBL i; -five| f1| x -----+------+----- - | 0| -2 - | 1234| 1232 - | -1234|-1236 - | 32767|32765 - |-32767|32767 -(5 rows) - -QUERY: SELECT '' AS five, i.f1, i.f1 - '2'::int4 AS x FROM INT2_TBL i; -five| f1| x -----+------+------ - | 0| -2 - | 1234| 1232 - | -1234| -1236 - | 32767| 32765 - |-32767|-32769 -(5 rows) - -QUERY: SELECT '' AS five, i.f1, i.f1 / '2'::int2 AS x FROM INT2_TBL i; -five| f1| x -----+------+------ - | 0| 0 - | 1234| 617 - | -1234| -617 - | 32767| 16383 - |-32767|-16383 -(5 rows) - -QUERY: SELECT '' AS five, i.f1, i.f1 / '2'::int4 AS x FROM INT2_TBL i; -five| f1| x -----+------+------ - | 0| 0 - | 1234| 617 - | -1234| -617 - | 32767| 16383 - |-32767|-16383 -(5 rows) - -QUERY: CREATE TABLE INT4_TBL(f1 int4); -QUERY: INSERT INTO INT4_TBL(f1) VALUES ('0'); -QUERY: INSERT INTO INT4_TBL(f1) VALUES ('123456'); -QUERY: INSERT INTO INT4_TBL(f1) VALUES ('-123456'); -QUERY: INSERT INTO INT4_TBL(f1) VALUES ('34.5'); -WARN:pg_atoi: error in "34.5": can't parse ".5" -QUERY: INSERT INTO INT4_TBL(f1) VALUES ('2147483647'); -QUERY: INSERT INTO INT4_TBL(f1) VALUES ('-2147483647'); -QUERY: INSERT INTO INT4_TBL(f1) VALUES ('1000000000000'); -WARN:pg_atoi: error reading "1000000000000": Result too large -QUERY: INSERT INTO INT4_TBL(f1) VALUES ('asdf'); -WARN:pg_atoi: error in "asdf": can't parse "asdf" -QUERY: SELECT '' AS five, INT4_TBL.*; -five| f1 -----+----------- - | 0 - | 123456 - | -123456 - | 2147483647 - |-2147483647 -(5 rows) - -QUERY: SELECT '' AS four, i.* FROM INT4_TBL i WHERE i.f1 <> '0'::int2; -four| f1 -----+----------- - | 123456 - | -123456 - | 2147483647 - |-2147483647 -(4 rows) - -QUERY: SELECT '' AS four, i.* FROM INT4_TBL i WHERE i.f1 <> '0'::int4; -four| f1 -----+----------- - | 123456 - | -123456 - | 2147483647 - |-2147483647 -(4 rows) - -QUERY: SELECT '' AS one, i.* FROM INT4_TBL i WHERE i.f1 = '0'::int2; -one|f1 ----+-- - | 0 -(1 row) - -QUERY: SELECT '' AS one, i.* FROM INT4_TBL i WHERE i.f1 = '0'::int4; -one|f1 ----+-- - | 0 -(1 row) - -QUERY: SELECT '' AS two, i.* FROM INT4_TBL i WHERE i.f1 < '0'::int2; -two| f1 ----+----------- - | -123456 - |-2147483647 -(2 rows) - -QUERY: SELECT '' AS two, i.* FROM INT4_TBL i WHERE i.f1 < '0'::int4; -two| f1 ----+----------- - | -123456 - |-2147483647 -(2 rows) - -QUERY: SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 <= '0'::int2; -three| f1 ------+----------- - | 0 - | -123456 - |-2147483647 -(3 rows) - -QUERY: SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 <= '0'::int4; -three| f1 ------+----------- - | 0 - | -123456 - |-2147483647 -(3 rows) - -QUERY: SELECT '' AS two, i.* FROM INT4_TBL i WHERE i.f1 > '0'::int2; -two| f1 ----+---------- - | 123456 - |2147483647 -(2 rows) - -QUERY: SELECT '' AS two, i.* FROM INT4_TBL i WHERE i.f1 > '0'::int4; -two| f1 ----+---------- - | 123456 - |2147483647 -(2 rows) - -QUERY: SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 >= '0'::int2; -three| f1 ------+---------- - | 0 - | 123456 - |2147483647 -(3 rows) - -QUERY: SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 >= '0'::int4; -three| f1 ------+---------- - | 0 - | 123456 - |2147483647 -(3 rows) - -QUERY: SELECT '' AS one, i.* FROM INT4_TBL i WHERE (i.f1 % '2'::int2) = '1'::int2; -one| f1 ----+---------- - |2147483647 -(1 row) - -QUERY: SELECT '' AS three, i.* FROM INT4_TBL i WHERE (i.f1 % '2'::int4) = '0'::int2; -three| f1 ------+------- - | 0 - | 123456 - |-123456 -(3 rows) - -QUERY: SELECT '' AS five, i.f1, i.f1 * '2'::int2 AS x FROM INT4_TBL i; -five| f1| x -----+-----------+------- - | 0| 0 - | 123456| 246912 - | -123456|-246912 - | 2147483647| -2 - |-2147483647| 2 -(5 rows) - -QUERY: SELECT '' AS five, i.f1, i.f1 * '2'::int4 AS x FROM INT4_TBL i; -five| f1| x -----+-----------+------- - | 0| 0 - | 123456| 246912 - | -123456|-246912 - | 2147483647| -2 - |-2147483647| 2 -(5 rows) - -QUERY: SELECT '' AS five, i.f1, i.f1 + '2'::int2 AS x FROM INT4_TBL i; -five| f1| x -----+-----------+----------- - | 0| 2 - | 123456| 123458 - | -123456| -123454 - | 2147483647|-2147483647 - |-2147483647|-2147483645 -(5 rows) - -QUERY: SELECT '' AS five, i.f1, i.f1 + '2'::int4 AS x FROM INT4_TBL i; -five| f1| x -----+-----------+----------- - | 0| 2 - | 123456| 123458 - | -123456| -123454 - | 2147483647|-2147483647 - |-2147483647|-2147483645 -(5 rows) - -QUERY: SELECT '' AS five, i.f1, i.f1 - '2'::int2 AS x FROM INT4_TBL i; -five| f1| x -----+-----------+---------- - | 0| -2 - | 123456| 123454 - | -123456| -123458 - | 2147483647|2147483645 - |-2147483647|2147483647 -(5 rows) - -QUERY: SELECT '' AS five, i.f1, i.f1 - '2'::int4 AS x FROM INT4_TBL i; -five| f1| x -----+-----------+---------- - | 0| -2 - | 123456| 123454 - | -123456| -123458 - | 2147483647|2147483645 - |-2147483647|2147483647 -(5 rows) - -QUERY: SELECT '' AS five, i.f1, i.f1 / '2'::int2 AS x FROM INT4_TBL i; -five| f1| x -----+-----------+----------- - | 0| 0 - | 123456| 61728 - | -123456| -61728 - | 2147483647| 1073741823 - |-2147483647|-1073741823 -(5 rows) - -QUERY: SELECT '' AS five, i.f1, i.f1 / '2'::int4 AS x FROM INT4_TBL i; -five| f1| x -----+-----------+----------- - | 0| 0 - | 123456| 61728 - | -123456| -61728 - | 2147483647| 1073741823 - |-2147483647|-1073741823 -(5 rows) - -QUERY: SELECT '2'::int2 * '2'::int2 = '16'::int2 / '4'::int2 AS true; -true ----- -t -(1 row) - -QUERY: SELECT '2'::int4 * '2'::int2 = '16'::int2 / '4'::int4 AS true; -true ----- -t -(1 row) - -QUERY: SELECT '2'::int2 * '2'::int4 = '16'::int4 / '4'::int2 AS true; -true ----- -t -(1 row) - -QUERY: SELECT '1000'::int4 < '999'::int4 AS false; -false ------ -f -(1 row) - -QUERY: SELECT 4! AS twenty_four; -twenty_four ------------ - 24 -(1 row) - -QUERY: SELECT !!3 AS six; -six ---- - 6 -(1 row) - -QUERY: SELECT 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 AS ten; -ten ---- - 10 -(1 row) - -QUERY: SELECT 2 + 2 / 2 AS three; -three ------ - 3 -(1 row) - -QUERY: SELECT (2 + 2) / 2 AS two; -two ---- - 2 -(1 row) - -QUERY: SELECT dsqrt('64'::float8) AS eight; -eight ------ - 8 -(1 row) - -QUERY: SELECT |/'64'::float8 AS eight; -eight ------ - 8 -(1 row) - -QUERY: SELECT ||/'27'::float8 AS three; -three ------ - 3 -(1 row) - -QUERY: CREATE TABLE OID_TBL(f1 oid); -QUERY: INSERT INTO OID_TBL(f1) VALUES ('1234'); -QUERY: INSERT INTO OID_TBL(f1) VALUES ('1235'); -QUERY: INSERT INTO OID_TBL(f1) VALUES ('987'); -QUERY: INSERT INTO OID_TBL(f1) VALUES ('-1040'); -QUERY: INSERT INTO OID_TBL(f1) VALUES (''); -QUERY: INSERT INTO OID_TBL(f1) VALUES ('asdfasd'); -WARN:pg_atoi: error in "asdfasd": can't parse "asdfasd" -QUERY: SELECT '' AS five, OID_TBL.*; -five| f1 -----+----- - | 1234 - | 1235 - | 987 - |-1040 - | 0 -(5 rows) - -QUERY: SELECT '' AS one, o.* FROM OID_TBL o WHERE o.f1 = '1234'::oid; -one| f1 ----+---- - |1234 -(1 row) - -QUERY: SELECT '' AS four, o.* FROM OID_TBL o WHERE o.f1 <> '1234'; -four| f1 -----+----- - | 1235 - | 987 - |-1040 - | 0 -(4 rows) - -QUERY: SELECT '' AS four, o.* FROM OID_TBL o WHERE o.f1 <= '1234'; -four| f1 -----+----- - | 1234 - | 987 - |-1040 - | 0 -(4 rows) - -QUERY: SELECT '' AS three, o.* FROM OID_TBL o WHERE o.f1 < '1234'; -three| f1 ------+----- - | 987 - |-1040 - | 0 -(3 rows) - -QUERY: SELECT '' AS two, o.* FROM OID_TBL o WHERE o.f1 >= '1234'; -two| f1 ----+---- - |1234 - |1235 -(2 rows) - -QUERY: SELECT '' AS one, o.* FROM OID_TBL o WHERE o.f1 > '1234'; -one| f1 ----+---- - |1235 -(1 row) - -QUERY: CREATE TABLE OIDNAME_TBL(f1 oidname); -QUERY: INSERT INTO OIDNAME_TBL(f1) VALUES ('1234,abcd'); -QUERY: INSERT INTO OIDNAME_TBL(f1) VALUES ('1235,efgh'); -QUERY: INSERT INTO OIDNAME_TBL(f1) VALUES ('987,XXXX'); -QUERY: INSERT INTO OIDNAME_TBL(f1) VALUES ('123456'); -WARN:Bad input data for type oidname -QUERY: INSERT INTO OIDNAME_TBL(f1) VALUES ('123456,abcdefghijklmnopqrsutvwyz'); -QUERY: INSERT INTO OIDNAME_TBL(f1) VALUES (''); -WARN:Bad input data for type oidname -QUERY: INSERT INTO OIDNAME_TBL(f1) VALUES ('asdfasd'); -WARN:Bad input data for type oidname -QUERY: SELECT '' AS four, OIDNAME_TBL.*; -four|f1 -----+-------------------------------- - |1234,abcd - |1235,efgh - |987,XXXX - |123456,abcdefghijklmnopqrsutvwyz -(4 rows) - -QUERY: SELECT '' AS one, o.* FROM OIDNAME_TBL o WHERE o.f1 = '1234,abcd'; -one|f1 ----+--------- - |1234,abcd -(1 row) - -QUERY: SELECT '' AS three, o.* FROM OIDNAME_TBL o WHERE o.f1 <> '1234,abcd'; -three|f1 ------+-------------------------------- - |1235,efgh - |987,XXXX - |123456,abcdefghijklmnopqrsutvwyz -(3 rows) - -QUERY: SELECT '' AS two, o.* FROM OIDNAME_TBL o WHERE o.f1 <= '1234,abcd'; -two|f1 ----+--------- - |1234,abcd - |987,XXXX -(2 rows) - -QUERY: SELECT '' AS one, o.* FROM OIDNAME_TBL o WHERE o.f1 < '1234,abcd'; -one|f1 ----+-------- - |987,XXXX -(1 row) - -QUERY: SELECT '' AS three, o.* FROM OIDNAME_TBL o WHERE o.f1 >= '1234,abcd'; -three|f1 ------+-------------------------------- - |1234,abcd - |1235,efgh - |123456,abcdefghijklmnopqrsutvwyz -(3 rows) - -QUERY: SELECT '' AS two, o.* FROM OIDNAME_TBL o WHERE o.f1 > '1234,abcd'; -two|f1 ----+-------------------------------- - |1235,efgh - |123456,abcdefghijklmnopqrsutvwyz -(2 rows) - -QUERY: CREATE TABLE OIDINT2_TBL(f1 oidint2); -QUERY: INSERT INTO OIDINT2_TBL(f1) VALUES ('1234/9873'); -QUERY: INSERT INTO OIDINT2_TBL(f1) VALUES ('1235/9873'); -QUERY: INSERT INTO OIDINT2_TBL(f1) VALUES ('987/-1234'); -QUERY: INSERT INTO OIDINT2_TBL(f1) VALUES ('123456'); -QUERY: INSERT INTO OIDINT2_TBL(f1) VALUES ('123456/123456'); -WARN:pg_atoi: error reading "123456": Result too large -QUERY: INSERT INTO OIDINT2_TBL(f1) VALUES (''); -QUERY: INSERT INTO OIDINT2_TBL(f1) VALUES ('asdfasd'); -WARN:pg_atoi: error in "asdfasd": can't parse "asdfasd" -QUERY: SELECT '' AS five, OIDINT2_TBL.*; -five|f1 -----+--------- - |1234/9873 - |1235/9873 - |987/-1234 - |123456/0 - |0/0 -(5 rows) - -QUERY: SELECT '' AS one, o.* FROM OIDINT2_TBL o WHERE o.f1 = '1235/9873'; -one|f1 ----+--------- - |1235/9873 -(1 row) - -QUERY: SELECT '' AS four, o.* FROM OIDINT2_TBL o WHERE o.f1 <> '1235/9873'; -four|f1 -----+--------- - |1234/9873 - |987/-1234 - |123456/0 - |0/0 -(4 rows) - -QUERY: SELECT '' AS four, o.* FROM OIDINT2_TBL o WHERE o.f1 <= '1235/9873'; -four|f1 -----+--------- - |1234/9873 - |1235/9873 - |987/-1234 - |0/0 -(4 rows) - -QUERY: SELECT '' AS three, o.* FROM OIDINT2_TBL o WHERE o.f1 < '1235/9873'; -three|f1 ------+--------- - |1234/9873 - |987/-1234 - |0/0 -(3 rows) - -QUERY: SELECT '' AS two, o.* FROM OIDINT2_TBL o WHERE o.f1 >= '1235/9873'; -two|f1 ----+--------- - |1235/9873 - |123456/0 -(2 rows) - -QUERY: SELECT '' AS one, o.* FROM OIDINT2_TBL o WHERE o.f1 > '1235/9873'; -one|f1 ----+-------- - |123456/0 -(1 row) - -QUERY: CREATE TABLE OIDINT4_TBL(f1 oidint4); -QUERY: INSERT INTO OIDINT4_TBL(f1) VALUES ('1234/9873'); -QUERY: INSERT INTO OIDINT4_TBL(f1) VALUES ('1235/9873'); -QUERY: INSERT INTO OIDINT4_TBL(f1) VALUES ('987/-1234'); -QUERY: INSERT INTO OIDINT4_TBL(f1) VALUES ('123456'); -QUERY: INSERT INTO OIDINT4_TBL(f1) VALUES ('123456/1234568901234567890'); -WARN:pg_atoi: error reading "1234568901234567890": Result too large -QUERY: INSERT INTO OIDINT4_TBL(f1) VALUES (''); -QUERY: INSERT INTO OIDINT4_TBL(f1) VALUES ('asdfasd'); -WARN:pg_atoi: error in "asdfasd": can't parse "asdfasd" -QUERY: SELECT '' AS five, OIDINT4_TBL.*; -five|f1 -----+--------- - |1234/9873 - |1235/9873 - |987/-1234 - |123456/0 - |0/0 -(5 rows) - -QUERY: SELECT '' AS one, o.* FROM OIDINT4_TBL o WHERE o.f1 = '1235/9873'; -one|f1 ----+--------- - |1235/9873 -(1 row) - -QUERY: SELECT '' AS four, o.* FROM OIDINT4_TBL o WHERE o.f1 <> '1235/9873'; -four|f1 -----+--------- - |1234/9873 - |987/-1234 - |123456/0 - |0/0 -(4 rows) - -QUERY: SELECT '' AS four, o.* FROM OIDINT4_TBL o WHERE o.f1 <= '1235/9873'; -four|f1 -----+--------- - |1234/9873 - |1235/9873 - |987/-1234 - |0/0 -(4 rows) - -QUERY: SELECT '' AS three, o.* FROM OIDINT4_TBL o WHERE o.f1 < '1235/9873'; -three|f1 ------+--------- - |1234/9873 - |987/-1234 - |0/0 -(3 rows) - -QUERY: SELECT '' AS two, o.* FROM OIDINT4_TBL o WHERE o.f1 >= '1235/9873'; -two|f1 ----+--------- - |1235/9873 - |123456/0 -(2 rows) - -QUERY: SELECT '' AS one, o.* FROM OIDINT4_TBL o WHERE o.f1 > '1235/9873'; -one|f1 ----+-------- - |123456/0 -(1 row) - -QUERY: CREATE TABLE POINT_TBL(f1 point); -QUERY: INSERT INTO POINT_TBL(f1) VALUES ('(0.0,0.0)'); -QUERY: INSERT INTO POINT_TBL(f1) VALUES ('(-10.0,0.0)'); -QUERY: INSERT INTO POINT_TBL(f1) VALUES ('(-3.0,4.0)'); -QUERY: INSERT INTO POINT_TBL(f1) VALUES ('(5.1, 34.5)'); -QUERY: INSERT INTO POINT_TBL(f1) VALUES ('(-5.0,-12.0)'); -QUERY: INSERT INTO POINT_TBL(f1) VALUES ('asdfasdf'); -WARN:Bad point external representation 'asdfasdf' -QUERY: INSERT INTO POINT_TBL(f1) VALUES ('10.0,10.0'); -WARN:Bad point external representation '10.0,10.0' -QUERY: INSERT INTO POINT_TBL(f1) VALUES ('(10.0 10.0)'); -WARN:Bad point external representation '(10.0 10.0)' -QUERY: INSERT INTO POINT_TBL(f1) VALUES ('(10.0,10.0'); -WARN:Bad point external representation '(10.0,10.0' -QUERY: SELECT '' AS five, POINT_TBL.*; -five|f1 -----+---------- - |(0,0) - |(-10,0) - |(-3,4) - |(5.1,34.5) - |(-5,-12) -(5 rows) - -QUERY: SELECT '' AS three, p.* FROM POINT_TBL p WHERE p.f1 !< '(0.0, 0.0)'; -three|f1 ------+-------- - |(-10,0) - |(-3,4) - |(-5,-12) -(3 rows) - -QUERY: SELECT '' AS three, p.* FROM POINT_TBL p WHERE '(0.0,0.0)' !> p.f1; -three|f1 ------+-------- - |(-10,0) - |(-3,4) - |(-5,-12) -(3 rows) - -QUERY: SELECT '' AS one, p.* FROM POINT_TBL p WHERE '(0.0,0.0)' !^ p.f1; -one|f1 ----+-------- - |(-5,-12) -(1 row) - -QUERY: SELECT '' AS one, p.* FROM POINT_TBL p WHERE p.f1 !| '(0.0, 0.0)'; -one|f1 ----+-------- - |(-5,-12) -(1 row) - -QUERY: SELECT '' AS one, p.* FROM POINT_TBL p WHERE p.f1 =|= '(5.1, 34.5)'; -one|f1 ----+---------- - |(5.1,34.5) -(1 row) - -QUERY: SELECT '' AS two, p.* FROM POINT_TBL p - WHERE p.f1 ===> '(0,0,100,100)'; -two|f1 ----+---------- - |(0,0) - |(5.1,34.5) -(2 rows) - -QUERY: SELECT '' AS three, p.* FROM POINT_TBL p - WHERE not on_pb(p.f1,'(0,0,100,100)'::box); -three|f1 ------+-------- - |(-10,0) - |(-3,4) - |(-5,-12) -(3 rows) - -QUERY: SELECT '' AS two, p.* FROM POINT_TBL p - WHERE on_ppath(p.f1,'(0,3,0,0,-10,0,-10,10)'::path); -two|f1 ----+------- - |(0,0) - |(-10,0) -(2 rows) - -QUERY: SELECT '' AS five, p.f1, p.f1 <===> '(0,0)' AS dist FROM POINT_TBL p; -five|f1 |dist -----+----------+---- - |(0,0) | 0 - |(-10,0) | 10 - |(-3,4) | 5 - |(5.1,34.5)| 34 - |(-5,-12) | 13 -(5 rows) - -QUERY: SELECT '' AS twentyfive, p1.f1, p2.f1, p1.f1 <===> p2.f1 AS dist - FROM POINT_TBL p1, POINT_TBL p2; -twentyfive|f1 |f1 |dist -----------+----------+----------+---- - |(0,0) |(0,0) | 0 - |(-10,0) |(0,0) | 10 - |(-3,4) |(0,0) | 5 - |(5.1,34.5)|(0,0) | 34 - |(-5,-12) |(0,0) | 13 - |(0,0) |(-10,0) | 10 - |(-10,0) |(-10,0) | 0 - |(-3,4) |(-10,0) | 8 - |(5.1,34.5)|(-10,0) | 37 - |(-5,-12) |(-10,0) | 13 - |(0,0) |(-3,4) | 5 - |(-10,0) |(-3,4) | 8 - |(-3,4) |(-3,4) | 0 - |(5.1,34.5)|(-3,4) | 31 - |(-5,-12) |(-3,4) | 16 - |(0,0) |(5.1,34.5)| 34 - |(-10,0) |(5.1,34.5)| 37 - |(-3,4) |(5.1,34.5)| 31 - |(5.1,34.5)|(5.1,34.5)| 0 - |(-5,-12) |(5.1,34.5)| 47 - |(0,0) |(-5,-12) | 13 - |(-10,0) |(-5,-12) | 13 - |(-3,4) |(-5,-12) | 16 - |(5.1,34.5)|(-5,-12) | 47 - |(-5,-12) |(-5,-12) | 0 -(25 rows) - -QUERY: SELECT '' AS twenty, p1.f1, p2.f1 - FROM POINT_TBL p1, POINT_TBL p2 - WHERE (p1.f1 <===> p2.f1) > 3; -twenty|f1 |f1 -------+----------+---------- - |(-10,0) |(0,0) - |(-3,4) |(0,0) - |(5.1,34.5)|(0,0) - |(-5,-12) |(0,0) - |(0,0) |(-10,0) - |(-3,4) |(-10,0) - |(5.1,34.5)|(-10,0) - |(-5,-12) |(-10,0) - |(0,0) |(-3,4) - |(-10,0) |(-3,4) - |(5.1,34.5)|(-3,4) - |(-5,-12) |(-3,4) - |(0,0) |(5.1,34.5) - |(-10,0) |(5.1,34.5) - |(-3,4) |(5.1,34.5) - |(-5,-12) |(5.1,34.5) - |(0,0) |(-5,-12) - |(-10,0) |(-5,-12) - |(-3,4) |(-5,-12) - |(5.1,34.5)|(-5,-12) -(20 rows) - -QUERY: SELECT '' AS ten, p1.f1, p2.f1 - FROM POINT_TBL p1, POINT_TBL p2 - WHERE (p1.f1 <===> p2.f1) > 3 and - p1.f1 !< p2.f1; -ten|f1 |f1 ----+--------+---------- - |(-10,0) |(0,0) - |(-3,4) |(0,0) - |(-5,-12)|(0,0) - |(-10,0) |(-3,4) - |(-5,-12)|(-3,4) - |(0,0) |(5.1,34.5) - |(-10,0) |(5.1,34.5) - |(-3,4) |(5.1,34.5) - |(-5,-12)|(5.1,34.5) - |(-10,0) |(-5,-12) -(10 rows) - -QUERY: SELECT '' AS two, p1.f1, p2.f1 - FROM POINT_TBL p1, POINT_TBL p2 - WHERE (p1.f1 <===> p2.f1) > 3 and - p1.f1 !< p2.f1 and - p1.f1 !^ p2.f1; -two|f1 |f1 ----+-------+-------- - |(-3,4) |(0,0) - |(-10,0)|(-5,-12) -(2 rows) - -QUERY: CREATE TABLE POLYGON_TBL(f1 polygon); -QUERY: INSERT INTO POLYGON_TBL(f1) VALUES ('(2.0,2.0,0.0,0.0,4.0,0.0)'); -QUERY: INSERT INTO POLYGON_TBL(f1) VALUES ('(3.0,3.0,1.0,1.0,3.0,0.0)'); -QUERY: INSERT INTO POLYGON_TBL(f1) VALUES ('(0.0,0.0)'); -QUERY: INSERT INTO POLYGON_TBL(f1) VALUES ('(0.0,0.0,1.0,1.0)'); -QUERY: INSERT INTO POLYGON_TBL(f1) VALUES ('0.0'); -WARN:Bad polygon external representation '0.0' -QUERY: INSERT INTO POLYGON_TBL(f1) VALUES ('(0.0 0.0'); -WARN:Bad polygon external representation '(0.0 0.0' -QUERY: INSERT INTO POLYGON_TBL(f1) VALUES ('(0,1,2)'); -WARN:Bad polygon external representation '(0,1,2)' -QUERY: INSERT INTO POLYGON_TBL(f1) VALUES ('(0,1,2,3'); -WARN:Bad polygon external representation '(0,1,2,3' -QUERY: INSERT INTO POLYGON_TBL(f1) VALUES ('asdf'); -WARN:Bad polygon external representation 'asdf' -QUERY: SELECT '' AS four, POLYGON_TBL.*; -four|f1 -----+------------------------------------------------------------------------------- - |( 2, 2, 0, 0, 4, 0) - |( 3, 3, 1, 1, 3, 0) - |( 0, 0) - |( 0, 0, 1, 1) -(4 rows) - -QUERY: SELECT '' AS three, p.* - FROM POLYGON_TBL p - WHERE p.f1 && '(3.0,3.0,1.0,1.0,3.0,0.0)'; -three|f1 ------+------------------------------------------------------------------------------- - |( 2, 2, 0, 0, 4, 0) - |( 3, 3, 1, 1, 3, 0) - |( 0, 0, 1, 1) -(3 rows) - -QUERY: SELECT '' AS four, p.* - FROM POLYGON_TBL p - WHERE p.f1 &< '(3.0,3.0,1.0,1.0,3.0,0.0)'; -four|f1 -----+------------------------------------------------------------------------------- - |( 2, 2, 0, 0, 4, 0) - |( 3, 3, 1, 1, 3, 0) - |( 0, 0) - |( 0, 0, 1, 1) -(4 rows) - -QUERY: SELECT '' AS two, p.* - FROM POLYGON_TBL p - WHERE p.f1 &> '(3.0,3.0,1.0,1.0,3.0,0.0)'; -two|f1 ----+------------------------------------------------------------------------------- - |( 2, 2, 0, 0, 4, 0) - |( 3, 3, 1, 1, 3, 0) -(2 rows) - -QUERY: SELECT '' AS one, p.* - FROM POLYGON_TBL p - WHERE p.f1 << '(3.0,3.0,1.0,1.0,3.0,0.0)'; -one|f1 ----+--------------------------- - |( 0, 0) -(1 row) - -QUERY: SELECT '' AS zero, p.* - FROM POLYGON_TBL p - WHERE p.f1 >> '(3.0,3.0,1.0,1.0,3.0,0.0)'; -zero|f1 -----+-- -(0 rows) - -QUERY: SELECT '' AS one, p.* - FROM POLYGON_TBL p - WHERE p.f1 @ '(3.0,3.0,1.0,1.0,3.0,0.0)'; -one|f1 ----+------------------------------------------------------------------------------- - |( 3, 3, 1, 1, 3, 0) -(1 row) - -QUERY: SELECT '' AS one, p.* - FROM POLYGON_TBL p - WHERE p.f1 ~= '(3.0,3.0,1.0,1.0,3.0,0.0)'; -one|f1 ----+------------------------------------------------------------------------------- - |( 3, 3, 1, 1, 3, 0) -(1 row) - -QUERY: SELECT '' AS one, p.* - FROM POLYGON_TBL p - WHERE p.f1 ~ '(3.0,3.0,1.0,1.0,3.0,0.0)'; -one|f1 ----+------------------------------------------------------------------------------- - |( 3, 3, 1, 1, 3, 0) -(1 row) - -QUERY: SELECT 'char 16 string'::char16 = 'char 16 string '::char16 AS false; -false ------ -f -(1 row) - -QUERY: SELECT 'c'::char = 'c'::char AS true; -true ----- -t -(1 row) - -QUERY: SELECT 'this is a text string'::text = 'this is a text string'::text AS true; -true ----- -t -(1 row) - -QUERY: SELECT 'this is a text string'::text = 'this is a text strin'::text AS false; -false ------ -f -(1 row) - -QUERY: SELECT '(2.0,2.0,0.0,0.0,4.0,0.0)'::polygon << '(3.0,3.0,1.0,1.0,3.0,0.0)'::polygon AS false; -false ------ -f -(1 row) - -QUERY: SELECT '(2.0,2.0,0.0,0.0,4.0,0.0)'::polygon &< '(3.0,3.0,1.0,1.0,3.0,0.0)'::polygon AS true; -true ----- -t -(1 row) - -QUERY: SELECT '(2.0,2.0,0.0,0.0,4.0,0.0)'::polygon &> '(3.0,3.0,1.0,1.0,3.0,0.0)'::polygon AS true; -true ----- -t -(1 row) - -QUERY: SELECT '(2.0,2.0,0.0,0.0,4.0,0.0)'::polygon >> '(3.0,3.0,1.0,1.0,3.0,0.0)'::polygon AS false; -false ------ -f -(1 row) - -QUERY: SELECT '(2.0,2.0,0.0,0.0,4.0,0.0)'::polygon @ '(3.0,3.0,1.0,1.0,3.0,0.0)'::polygon AS false; -false ------ -f -(1 row) - -QUERY: SELECT '(2.0,2.0,0.0,0.0,4.0,0.0)'::polygon ~ '(3.0,3.0,1.0,1.0,3.0,0.0)'::polygon AS false; -false ------ -f -(1 row) - -QUERY: SELECT '(2.0,2.0,0.0,0.0,4.0,0.0)'::polygon ~= '(3.0,3.0,1.0,1.0,3.0,0.0)'::polygon AS false; -false ------ -f -(1 row) - -QUERY: SELECT '(2.0,2.0,0.0,0.0,4.0,0.0)'::polygon && '(3.0,3.0,1.0,1.0,3.0,0.0)'::polygon AS true; -true ----- -t -(1 row) - QUERY: SELECT onek.* WHERE onek.unique1 < 10; unique1|unique2|two|four|ten|twenty|hundred|thousand|twothousand|fivethous|tenthous|odd|even|stringu1|stringu2|string4 -------+-------+---+----+---+------+-------+--------+-----------+---------+--------+---+----+--------+--------+------- diff --git a/src/test/regress/queries.source b/src/test/regress/queries.source index 49e5bdc78a..4bf5a9ff5b 100644 --- a/src/test/regress/queries.source +++ b/src/test/regress/queries.source @@ -1,7 +1,7 @@ -- -- queries.source -- --- $Header: /cvsroot/pgsql/src/test/regress/Attic/queries.source,v 1.7 1997/01/14 01:42:38 momjian Exp $ +-- $Header: /cvsroot/pgsql/src/test/regress/Attic/queries.source,v 1.8 1997/04/05 11:58:31 scrappy Exp $ -- -- The comments that contain sequences of UNIX commands generate the -- desired output for the POSTQUEL statement(s). @@ -11,1396 +11,6 @@ -- --- operators and target lists --- -- --- --- 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 't'::bool AS true; - -SELECT 'f'::bool AS false; - -SELECT 't'::bool or 'f'::bool AS true; - -SELECT 't'::bool and 'f'::bool AS false; - -SELECT not 'f'::bool AS true; - -SELECT 't'::bool = 'f'::bool AS false; - -SELECT 't'::bool <> 'f'::bool AS true; - - -CREATE TABLE BOOLTBL1 (f1 bool); - -INSERT INTO BOOLTBL1 (f1) VALUES ('t'::bool); - -INSERT INTO BOOLTBL1 (f1) VALUES ('True'::bool); - -INSERT INTO BOOLTBL1 (f1) VALUES ('true'::bool); - - --- BOOLTBL1 should be full of true's at this point -SELECT '' AS t_3, BOOLTBL1.*; - - -SELECT '' AS t_3, BOOLTBL1.* - FROM BOOLTBL1 - WHERE f1 = 'true'::bool; - - -SELECT '' AS t_3, BOOLTBL1.* - FROM BOOLTBL1 - WHERE f1 <> 'false'::bool; - -SELECT '' AS zero, BOOLTBL1.* - FROM BOOLTBL1 - WHERE booleq('false'::bool, f1); - -INSERT INTO BOOLTBL1 (f1) VALUES ('f'::bool); - -SELECT '' AS f_1, BOOLTBL1.* - FROM BOOLTBL1 - WHERE f1 = 'false'::bool; - - -CREATE TABLE BOOLTBL2 (f1 bool); - -INSERT INTO BOOLTBL2 (f1) VALUES ('f'::bool); - -INSERT INTO BOOLTBL2 (f1) VALUES ('false'::bool); - -INSERT INTO BOOLTBL2 (f1) VALUES ('False'::bool); - --- this evaluates to a false value -INSERT INTO BOOLTBL2 (f1) - VALUES ('XXX'::bool); - - --- BOOLTBL2 should be full of false's at this point -SELECT '' AS f_4, BOOLTBL2.*; - - -SELECT '' AS tf_12, BOOLTBL1.*, BOOLTBL2.* - WHERE BOOLTBL2.f1 <> BOOLTBL1.f1; - - -SELECT '' AS tf_12, BOOLTBL1.*, BOOLTBL2.* - WHERE boolne(BOOLTBL2.f1,BOOLTBL1.f1); - - -SELECT '' AS ff_4, BOOLTBL1.*, BOOLTBL2.* - WHERE BOOLTBL2.f1 = BOOLTBL1.f1 and BOOLTBL1.f1 = 'false'::bool; - - -SELECT '' AS tf_12_ff_4, BOOLTBL1.*, BOOLTBL2.* - WHERE BOOLTBL2.f1 = BOOLTBL1.f1 or BOOLTBL1.f1 = 'true'::bool; - - --- **** testing built-in time types: abstime, reltime, and tinterval **** - --- --- timezones may vary based not only on location but the operating --- system. the main correctness issue is that the OS may not get --- DST right for times prior to unix epoch (jan 1 1970). --- - -CREATE TABLE ABSTIME_TBL (f1 abstime); - -INSERT INTO ABSTIME_TBL (f1) VALUES ('Jan 14, 1973 03:14:21'); - --- was INSERT INTO ABSTIME_TBL (f1) VALUES ('now'::abstime): -INSERT INTO ABSTIME_TBL (f1) VALUES ('Mon May 1 00:30:30 1995'::abstime); - -INSERT INTO ABSTIME_TBL (f1) VALUES ('epoch'::abstime); - -INSERT INTO ABSTIME_TBL (f1) VALUES ('current'::abstime); - -INSERT INTO ABSTIME_TBL (f1) VALUES ('infinity'::abstime); - -INSERT INTO ABSTIME_TBL (f1) VALUES ('-infinity'::abstime); - -INSERT INTO ABSTIME_TBL (f1) VALUES ('May 10, 1943 23:59:12'); - - --- what happens if we specify slightly misformatted abstime? -INSERT INTO ABSTIME_TBL (f1) VALUES ('Feb 35, 1946 10:00:00'); - -INSERT INTO ABSTIME_TBL (f1) VALUES ('Feb 28, 1984 25:08:10'); - - --- badly formatted abstimes: these should result in invalid abstimes -INSERT INTO ABSTIME_TBL (f1) VALUES ('bad date format'); - -INSERT INTO ABSTIME_TBL (f1) VALUES ('Jun 10, 1843'); - - -CREATE TABLE RELTIME_TBL (f1 reltime); - -INSERT INTO RELTIME_TBL (f1) VALUES ('@ 1 minute'); - -INSERT INTO RELTIME_TBL (f1) VALUES ('@ 5 hour'); - -INSERT INTO RELTIME_TBL (f1) VALUES ('@ 10 day'); - -INSERT INTO RELTIME_TBL (f1) VALUES ('@ 34 year'); - -INSERT INTO RELTIME_TBL (f1) VALUES ('@ 3 months'); - -INSERT INTO RELTIME_TBL (f1) VALUES ('@ 14 seconds ago'); - - --- badly formatted reltimes: -INSERT INTO RELTIME_TBL (f1) VALUES ('badly formatted reltime'); - -INSERT INTO RELTIME_TBL (f1) VALUES ('@ 30 eons ago'); - - -CREATE TABLE TINTERVAL_TBL (f1 tinterval); - -INSERT INTO TINTERVAL_TBL (f1) - VALUES ('["-infinity" "infinity"]'); - -INSERT INTO TINTERVAL_TBL (f1) - VALUES ('["May 10, 1943 23:59:12" "Jan 14, 1973 03:14:21"]'); - -INSERT INTO TINTERVAL_TBL (f1) - VALUES ('["Sep 4, 1983 23:59:12" "Oct 4, 1983 23:59:12"]'); - -INSERT INTO TINTERVAL_TBL (f1) - VALUES ('["epoch" "Mon May 1 00:30:30 1995"]'); - -INSERT INTO TINTERVAL_TBL (f1) - VALUES ('["Feb 15 1990 12:15:03" "current"]'); - - --- badly formatted tintervals -INSERT INTO TINTERVAL_TBL (f1) - VALUES ('["bad time specifications" ""]'); - -INSERT INTO TINTERVAL_TBL (f1) - VALUES ('["" "infinity"]'); - - --- test abstime operators - -SELECT '' AS eleven, ABSTIME_TBL.*; - -SELECT '' AS eight, ABSTIME_TBL.* - WHERE ABSTIME_TBL.f1 < 'Jun 30, 2001'::abstime; - -SELECT '' AS eight, ABSTIME_TBL.* - WHERE ABSTIME_TBL.f1 > '-infinity'::abstime; - -SELECT '' AS eight, ABSTIME_TBL.* - WHERE 'May 10, 1943 23:59:12'::abstime <> ABSTIME_TBL.f1; - -SELECT '' AS one, ABSTIME_TBL.* - WHERE 'current'::abstime = ABSTIME_TBL.f1; - -SELECT '' AS five, ABSTIME_TBL.* - WHERE 'epoch'::abstime >= ABSTIME_TBL.f1; - -SELECT '' AS six, ABSTIME_TBL.* - WHERE ABSTIME_TBL.f1 <= 'Jan 14, 1973 03:14:21'::abstime; - -SELECT '' AS six, ABSTIME_TBL.* - WHERE ABSTIME_TBL.f1 - '["Apr 1 1945 00:00:00" "Dec 30 1999 23:00:00"]'::tinterval; - - --- these four queries should return the same answer --- the "infinity" and "-infinity" tuples in ABSTIME_TBL cannot be added and --- therefore, should not show up in the results. -SELECT '' AS five, ABSTIME_TBL.* - WHERE (ABSTIME_TBL.f1 + '@ 3 year'::reltime) -- +3 years - < 'Jan 14 14:00:00 1977'::abstime; - -SELECT '' AS five, ABSTIME_TBL.* - WHERE (ABSTIME_TBL.f1 + '@ 3 year ago'::reltime) -- -3 years - < 'Jan 14 14:00:00 1971'::abstime; - -SELECT '' AS five, ABSTIME_TBL.* - WHERE (ABSTIME_TBL.f1 - '@ 3 year'::reltime) -- -(+3) years - < 'Jan 14 14:00:00 1971'::abstime; - -SELECT '' AS five, ABSTIME_TBL.* - WHERE (ABSTIME_TBL.f1 - '@ 3 year ago'::reltime) -- -(-3) years - < 'Jan 14 14:00:00 1977'::abstime; - - -SELECT '' AS twenty, ABSTIME_TBL.*, RELTIME_TBL.* - WHERE (ABSTIME_TBL.f1 + RELTIME_TBL.f1) - < 'Jan 14 14:00:00 1971'::abstime; - - --- test reltime operators - -SELECT '' AS eight, RELTIME_TBL.*; - -SELECT '' AS five, RELTIME_TBL.* - WHERE RELTIME_TBL.f1 <> '@ 10 days'::reltime; - -SELECT '' AS three, RELTIME_TBL.* - WHERE RELTIME_TBL.f1 <= '@ 5 hours'::reltime; - -SELECT '' AS three, RELTIME_TBL.* - WHERE RELTIME_TBL.f1 < '@ 1 day'::reltime; - -SELECT '' AS one, RELTIME_TBL.* - WHERE RELTIME_TBL.f1 = '@ 34 years'::reltime; - -SELECT '' AS two, RELTIME_TBL.* - WHERE RELTIME_TBL.f1 >= '@ 1 month'::reltime; - -SELECT '' AS five, RELTIME_TBL.* - WHERE RELTIME_TBL.f1 > '@ 3 seconds ago'::reltime; - -SELECT '' AS fifteen, r1.*, r2.* - FROM RELTIME_TBL r1, RELTIME_TBL r2 - WHERE r1.f1 > r2.f1; - - --- test tinterval operators - -SELECT '' AS seven, TINTERVAL_TBL.*; - --- length == -SELECT '' AS one, t.* - FROM TINTERVAL_TBL t - WHERE t.f1 #= '@ 1 months'; - --- length <> -SELECT '' AS three, t.* - FROM TINTERVAL_TBL t - WHERE t.f1 #<> '@ 1 months'; - --- length < -SELECT '' AS zero, t.* - FROM TINTERVAL_TBL t - WHERE t.f1 #< '@ 1 month'; - --- length <= -SELECT '' AS one, t.* - FROM TINTERVAL_TBL t - WHERE t.f1 #<= '@ 1 month'; - --- length > -SELECT '' AS three, t.* - FROM TINTERVAL_TBL t - WHERE t.f1 #> '@ 1 year'; - --- length >= -SELECT '' AS three, t.* - FROM TINTERVAL_TBL t - WHERE t.f1 #>= '@ 3 years'; - --- overlaps -SELECT '' AS three, t1.* - FROM TINTERVAL_TBL t1 - WHERE t1.f1 && - '["Aug 15 14:23:19 1983" "Sep 16 14:23:19 1983"]'::tinterval; - -SELECT '' AS five, t1.*, t2.* - FROM TINTERVAL_TBL t1, TINTERVAL_TBL t2 - WHERE t1.f1 && t2.f1 and - t1.f1 = t2.f1; - -SELECT '' AS fourteen, t1.*, t2.* - FROM TINTERVAL_TBL t1, TINTERVAL_TBL t2 - WHERE t1.f1 && t2.f1 and - not t1.f1 = t2.f1; - --- contains -SELECT '' AS five, t1.* - FROM TINTERVAL_TBL t1 - WHERE not t1.f1 << - '["Aug 15 14:23:19 1980" "Sep 16 14:23:19 1990"]'::tinterval; - --- make time interval -SELECT '' AS three, t1.* - FROM TINTERVAL_TBL t1 - WHERE t1.f1 && - ('Aug 15 14:23:19 1983'::abstime <#> - 'Sep 16 14:23:19 1983'::abstime); - - --- ****************** test built-in type box ******************** - --- --- box logic --- o --- 3 o--|X --- | o| --- 2 +-+-+ | --- | | | | --- 1 | o-+-o --- | | --- 0 +---+ --- --- 0 1 2 3 --- - --- boxes are specified by two points, given by four floats x1,y1,x2,y2 - - -CREATE TABLE BOX_TBL (f1 box); - -INSERT INTO BOX_TBL (f1) VALUES ('(2.0,2.0,0.0,0.0)'); - -INSERT INTO BOX_TBL (f1) VALUES ('(1.0,1.0,3.0,3.0)'); - --- degenerate cases where the box is a line or a point --- note that lines and points boxes all have zero area -INSERT INTO BOX_TBL (f1) VALUES ('(2.5, 2.5, 2.5,3.5)'); - -INSERT INTO BOX_TBL (f1) VALUES ('(3.0, 3.0,3.0,3.0)'); - --- badly formatted box inputs -INSERT INTO BOX_TBL (f1) VALUES ('(2.3, 4.5)'); - -INSERT INTO BOX_TBL (f1) VALUES ('asdfasdf(ad'); - - -SELECT '' AS four, BOX_TBL.*; - -SELECT '' AS four, b.*, box_area(b.f1) as barea - FROM BOX_TBL b; - --- overlap -SELECT '' AS three, b.f1 - FROM BOX_TBL b - WHERE b.f1 && '(2.5,2.5,1.0,1.0)'::box; - --- left-or-overlap (x only) -SELECT '' AS two, b1.* - FROM BOX_TBL b1 - WHERE b1.f1 &< '(2.0,2.0,2.5,2.5)'::box; - --- right-or-overlap (x only) -SELECT '' AS two, b1.* - FROM BOX_TBL b1 - WHERE b1.f1 &> '(2.0,2.0,2.5,2.5)'::box; - --- left of -SELECT '' AS two, b.f1 - FROM BOX_TBL b - WHERE b.f1 << '(3.0,3.0,5.0,5.0)'::box; - --- area <= -SELECT '' AS four, b.f1 - FROM BOX_TBL b - WHERE b.f1 <= '(3.0,3.0,5.0,5.0)'::box; - --- area < -SELECT '' AS two, b.f1 - FROM BOX_TBL b - WHERE b.f1 < '(3.0,3.0,5.0,5.0)'::box; - --- area = -SELECT '' AS two, b.f1 - FROM BOX_TBL b - WHERE b.f1 = '(3.0,3.0,5.0,5.0)'::box; - --- area > -SELECT '' AS two, b.f1 - FROM BOX_TBL b -- zero area - WHERE b.f1 > '(3.5,3.0,4.5,3.0)'::box; - --- area >= -SELECT '' AS four, b.f1 - FROM BOX_TBL b -- zero area - WHERE b.f1 >= '(3.5,3.0,4.5,3.0)'::box; - --- right of -SELECT '' AS two, b.f1 - FROM BOX_TBL b - WHERE '(3.0,3.0,5.0,5.0)'::box >> b.f1; - --- contained in -SELECT '' AS three, b.f1 - FROM BOX_TBL b - WHERE b.f1 @ '(0,0,3,3)'::box; - --- contains -SELECT '' AS three, b.f1 - FROM BOX_TBL b - WHERE '(0,0,3,3)'::box ~ b.f1; - --- box equality -SELECT '' AS one, b.f1 - FROM BOX_TBL b - WHERE '(1,1,3,3)'::box ~= b.f1; - --- center of box, left unary operator -SELECT '' AS four, @@(b1.f1) AS p - FROM BOX_TBL b1; - --- wholly-contained -SELECT '' AS one, b1.*, b2.* - FROM BOX_TBL b1, BOX_TBL b2 - WHERE b1.f1 ~ b2.f1 and not b1.f1 ~= b2.f1; - - --- ****************** test built-in type char ************** --- --- all inputs are SILENTLY truncated at 1 character --- - -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'); - - -SELECT '' AS seven, 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'; - - --- **************** testing built-in type char2 ************** --- --- all inputs are silently truncated at 2 characters --- - -CREATE TABLE CHAR2_TBL(f1 char2); - -INSERT INTO CHAR2_TBL (f1) VALUES ('AB'); - -INSERT INTO CHAR2_TBL (f1) VALUES ('ab'); - -INSERT INTO CHAR2_TBL (f1) VALUES ('ZY'); - -INSERT INTO CHAR2_TBL (f1) VALUES ('34'); - -INSERT INTO CHAR2_TBL (f1) VALUES ('d'); - -INSERT INTO CHAR2_TBL (f1) VALUES (''); - -INSERT INTO CHAR2_TBL (f1) VALUES ('12345'); - - -SELECT '' AS seven, CHAR2_TBL.*; - -SELECT '' AS six, c.f1 FROM CHAR2_TBL c WHERE c.f1 <> 'AB'; - -SELECT '' AS one, c.f1 FROM CHAR2_TBL c WHERE c.f1 = 'AB'; - -SELECT '' AS three, c.f1 FROM CHAR2_TBL c WHERE c.f1 < 'AB'; - -SELECT '' AS four, c.f1 FROM CHAR2_TBL c WHERE c.f1 <= 'AB'; - -SELECT '' AS three, c.f1 FROM CHAR2_TBL c WHERE c.f1 > 'AB'; - -SELECT '' AS four, c.f1 FROM CHAR2_TBL c WHERE c.f1 >= 'AB'; - -SELECT '' AS seven, c.f1 FROM CHAR2_TBL c WHERE c.f1 ~ '.*'; - -SELECT '' AS zero, c.f1 FROM CHAR2_TBL c WHERE c.f1 !~ '.*'; - -SELECT '' AS one, c.f1 FROM CHAR2_TBL c WHERE c.f1 ~ '34'; - -SELECT '' AS one, c.f1 FROM CHAR2_TBL c WHERE c.f1 ~ '3.*'; - - - ---**************** testing built-in type char4 ************** --- --- all inputs are silently truncated at 4 characters --- - -CREATE TABLE CHAR4_TBL (f1 char4); - -INSERT INTO CHAR4_TBL(f1) VALUES ('ABCD'); - -INSERT INTO CHAR4_TBL(f1) VALUES ('abcd'); - -INSERT INTO CHAR4_TBL(f1) VALUES ('ZYWZ'); - -INSERT INTO CHAR4_TBL(f1) VALUES ('343f'); - -INSERT INTO CHAR4_TBL(f1) VALUES ('d34a'); - -INSERT INTO CHAR4_TBL(f1) VALUES (''); - -INSERT INTO CHAR4_TBL(f1) VALUES ('12345678'); - - -SELECT '' AS seven, CHAR4_TBL.*; - -SELECT '' AS six, c.f1 FROM CHAR4_TBL c WHERE c.f1 <> 'ABCD'; - -SELECT '' AS one, c.f1 FROM CHAR4_TBL c WHERE c.f1 = 'ABCD'; - -SELECT '' AS three, c.f1 FROM CHAR4_TBL c WHERE c.f1 < 'ABCD'; - -SELECT '' AS four, c.f1 FROM CHAR4_TBL c WHERE c.f1 <= 'ABCD'; - -SELECT '' AS three, c.f1 FROM CHAR4_TBL c WHERE c.f1 > 'ABCD'; - -SELECT '' AS four, c.f1 FROM CHAR4_TBL c WHERE c.f1 >= 'ABCD'; - -SELECT '' AS seven, c.f1 FROM CHAR4_TBL c WHERE c.f1 ~ '.*'; - -SELECT '' AS zero, c.f1 FROM CHAR4_TBL c WHERE c.f1 !~ '.*'; - -SELECT '' AS three, c.f1 FROM CHAR4_TBL c WHERE c.f1 ~ '.*34.*'; - - --- **************** testing built-in type char8 ************** --- --- all inputs are silently truncated at 8 characters --- - -CREATE TABLE CHAR8_TBL(f1 char8); - -INSERT INTO CHAR8_TBL(f1) VALUES ('ABCDEFGH'); - -INSERT INTO CHAR8_TBL(f1) VALUES ('abcdefgh'); - -INSERT INTO CHAR8_TBL(f1) VALUES ('ZYWZ410-'); - -INSERT INTO CHAR8_TBL(f1) VALUES ('343f%2a'); - -INSERT INTO CHAR8_TBL(f1) VALUES ('d34aas'); - -INSERT INTO CHAR8_TBL(f1) VALUES (''); - -INSERT INTO CHAR8_TBL(f1) VALUES ('1234567890'); - - -SELECT '' AS seven, CHAR8_TBL.*; - -SELECT '' AS six, c.f1 FROM CHAR8_TBL c WHERE c.f1 <> 'ABCDEFGH'; - -SELECT '' AS one, c.f1 FROM CHAR8_TBL c WHERE c.f1 = 'ABCDEFGH'; - -SELECT '' AS three, c.f1 FROM CHAR8_TBL c WHERE c.f1 < 'ABCDEFGH'; - -SELECT '' AS four, c.f1 FROM CHAR8_TBL c WHERE c.f1 <= 'ABCDEFGH'; - -SELECT '' AS three, c.f1 FROM CHAR8_TBL c WHERE c.f1 > 'ABCDEFGH'; - -SELECT '' AS four, c.f1 FROM CHAR8_TBL c WHERE c.f1 >= 'ABCDEFGH'; - -SELECT '' AS seven, c.f1 FROM CHAR8_TBL c WHERE c.f1 ~ '.*'; - -SELECT '' AS zero, c.f1 FROM CHAR8_TBL c WHERE c.f1 !~ '.*'; - -SELECT '' AS four, c.f1 FROM CHAR8_TBL c WHERE c.f1 ~ '[0-9]'; - -SELECT '' AS three, c.f1 FROM CHAR8_TBL c WHERE c.f1 ~ '.*34.*'; - - - ---**************** testing built-in type char16 ************** --- --- all inputs are silently truncated at 16 characters --- - -CREATE TABLE CHAR16_TBL(f1 char16); - -INSERT INTO CHAR16_TBL(f1) VALUES ('ABCDEFGHIJKLMNOP'); - -INSERT INTO CHAR16_TBL(f1) VALUES ('abcdefghijklmnop'); - -INSERT INTO CHAR16_TBL(f1) VALUES ('asdfghjkl;'); - -INSERT INTO CHAR16_TBL(f1) VALUES ('343f%2a'); - -INSERT INTO CHAR16_TBL(f1) VALUES ('d34aaasdf'); - -INSERT INTO CHAR16_TBL(f1) VALUES (''); - -INSERT INTO CHAR16_TBL(f1) VALUES ('1234567890ABCDEFGHIJKLMNOPQRSTUV'); - - -SELECT '' AS seven, CHAR16_TBL.*; - -SELECT '' AS six, c.f1 FROM CHAR16_TBL c WHERE c.f1 <> 'ABCDEFGHIJKLMNOP'; - -SELECT '' AS one, c.f1 FROM CHAR16_TBL c WHERE c.f1 = 'ABCDEFGHIJKLMNOP'; - -SELECT '' AS three, c.f1 FROM CHAR16_TBL c WHERE c.f1 < 'ABCDEFGHIJKLMNOP'; - -SELECT '' AS four, c.f1 FROM CHAR16_TBL c WHERE c.f1 <= 'ABCDEFGHIJKLMNOP'; - -SELECT '' AS three, c.f1 FROM CHAR16_TBL c WHERE c.f1 > 'ABCDEFGHIJKLMNOP'; - -SELECT '' AS four, c.f1 FROM CHAR16_TBL c WHERE c.f1 >= 'ABCDEFGHIJKLMNOP'; - -SELECT '' AS seven, c.f1 FROM CHAR16_TBL c WHERE c.f1 ~ '.*'; - -SELECT '' AS zero, c.f1 FROM CHAR16_TBL c WHERE c.f1 !~ '.*'; - -SELECT '' AS three, c.f1 FROM CHAR16_TBL c WHERE c.f1 ~ '[0-9]'; - -SELECT '' AS two, c.f1 FROM CHAR16_TBL c WHERE c.f1 ~ '.*asdf.*'; - - --- *************testing built-in type 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'); - - -SELECT '' AS five, FLOAT4_TBL.*; - -SELECT '' AS four, f.* FROM FLOAT4_TBL f WHERE f.f1 <> '1004.3'; - -SELECT '' AS one, f.* FROM FLOAT4_TBL f WHERE f.f1 = '1004.3'; - -SELECT '' AS three, f.* FROM FLOAT4_TBL f WHERE '1004.3' > f.f1; - -SELECT '' AS three, f.* FROM FLOAT4_TBL f WHERE f.f1 < '1004.3'; - -SELECT '' AS four, f.* FROM FLOAT4_TBL f WHERE '1004.3' >= f.f1; - -SELECT '' AS four, f.* FROM FLOAT4_TBL f WHERE f.f1 <= '1004.3'; - -SELECT '' AS three, f.f1, f.f1 * '-10' AS x FROM FLOAT4_TBL f - WHERE f.f1 > '0.0'; - -SELECT '' AS three, f.f1, f.f1 + '-10' AS x FROM FLOAT4_TBL f - WHERE f.f1 > '0.0'; - -SELECT '' AS three, f.f1, f.f1 / '-10' AS x FROM FLOAT4_TBL f - WHERE f.f1 > '0.0'; - -SELECT '' AS three, f.f1, f.f1 - '-10' AS x FROM FLOAT4_TBL f - WHERE f.f1 > '0.0'; - --- test divide by zero -SELECT '' AS bad, f.f1 / '0.0' from FLOAT4_TBL f; - -SELECT '' AS five, FLOAT4_TBL.*; - --- test the unary float4abs operator -SELECT '' AS five, f.f1, @f.f1 AS abs_f1 FROM FLOAT4_TBL f; - -UPDATE FLOAT4_TBL - SET f1 = FLOAT4_TBL.f1 * '-1' - WHERE FLOAT4_TBL.f1 > '0.0'; - -SELECT '' AS five, FLOAT4_TBL.*; - - --- *************testing built-in type float8 **************** - -CREATE TABLE FLOAT8_TBL(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 over and under flow -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'); - - -SELECT '' AS five, FLOAT8_TBL.*; - -SELECT '' AS four, f.* FROM FLOAT8_TBL f WHERE f.f1 <> '1004.3'; - -SELECT '' AS one, f.* FROM FLOAT8_TBL f WHERE f.f1 = '1004.3'; - -SELECT '' AS three, f.* FROM FLOAT8_TBL f WHERE '1004.3' > f.f1; - -SELECT '' AS three, f.* FROM FLOAT8_TBL f WHERE f.f1 < '1004.3'; - -SELECT '' AS four, f.* FROM FLOAT8_TBL f WHERE '1004.3' >= f.f1; - -SELECT '' AS four, f.* FROM FLOAT8_TBL f WHERE f.f1 <= '1004.3'; - -SELECT '' AS three, f.f1, f.f1 * '-10' AS x - FROM FLOAT8_TBL f - WHERE f.f1 > '0.0'; - -SELECT '' AS three, f.f1, f.f1 + '-10' AS x - FROM FLOAT8_TBL f - WHERE f.f1 > '0.0'; - -SELECT '' AS three, f.f1, f.f1 / '-10' AS x - FROM FLOAT8_TBL f - WHERE f.f1 > '0.0'; - -SELECT '' AS three, f.f1, f.f1 - '-10' AS x - FROM FLOAT8_TBL f - WHERE f.f1 > '0.0'; - -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; - --- truncate -SELECT '' AS five, f.f1, %f.f1 AS trunc_f1 - FROM FLOAT8_TBL f; - --- round -SELECT '' AS five, f.f1, f.f1 % AS round_f1 - FROM FLOAT8_TBL f; - --- square root -SELECT '' AS three, f.f1, |/f.f1 AS sqrt_f1 - FROM FLOAT8_TBL f - WHERE f.f1 > '0.0'; - --- take exp of ln(f.f1) -SELECT '' AS three, f.f1, : ( ; f.f1) AS exp_ln_f1 - FROM FLOAT8_TBL f - WHERE f.f1 > '0.0'; - --- cube root -SELECT '' AS five, f.f1, ||/f.f1 AS cbrt_f1 FROM FLOAT8_TBL f; - - -SELECT '' AS five, FLOAT8_TBL.*; - -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, (; (f.f1)) from FLOAT8_TBL f where f.f1 = '0.0' ; - -SELECT '' AS bad, (; (f.f1)) from FLOAT8_TBL f where f.f1 < '0.0' ; - -SELECT '' AS bad, : (f.f1) from FLOAT8_TBL f; - -SELECT '' AS bad, f.f1 / '0.0' from FLOAT8_TBL f; - -SELECT '' AS five, FLOAT8_TBL.*; - - --- *************testing built-in type 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 warnings -INSERT INTO INT2_TBL(f1) VALUES ('100000'); - -INSERT INTO INT2_TBL(f1) VALUES ('asdf'); - - -SELECT '' AS five, INT2_TBL.*; - -SELECT '' AS four, i.* FROM INT2_TBL i WHERE i.f1 <> '0'::int2; - -SELECT '' AS four, i.* FROM INT2_TBL i WHERE i.f1 <> '0'::int4; - -SELECT '' AS one, i.* FROM INT2_TBL i WHERE i.f1 = '0'::int2; - -SELECT '' AS one, i.* FROM INT2_TBL i WHERE i.f1 = '0'::int4; - -SELECT '' AS two, i.* FROM INT2_TBL i WHERE i.f1 < '0'::int2; - -SELECT '' AS two, i.* FROM INT2_TBL i WHERE i.f1 < '0'::int4; - -SELECT '' AS three, i.* FROM INT2_TBL i WHERE i.f1 <= '0'::int2; - -SELECT '' AS three, i.* FROM INT2_TBL i WHERE i.f1 <= '0'::int4; - -SELECT '' AS two, i.* FROM INT2_TBL i WHERE i.f1 > '0'::int2; - -SELECT '' AS two, i.* FROM INT2_TBL i WHERE i.f1 > '0'::int4; - -SELECT '' AS three, i.* FROM INT2_TBL i WHERE i.f1 >= '0'::int2; - -SELECT '' AS three, i.* FROM INT2_TBL i WHERE i.f1 >= '0'::int4; - --- positive odds -SELECT '' AS one, i.* FROM INT2_TBL i WHERE (i.f1 % '2'::int2) = '1'::int2; - --- any evens -SELECT '' AS three, i.* FROM INT2_TBL i WHERE (i.f1 % '2'::int4) = '0'::int2; - -SELECT '' AS five, i.f1, i.f1 * '2'::int2 AS x FROM INT2_TBL i; - -SELECT '' AS five, i.f1, i.f1 * '2'::int4 AS x FROM INT2_TBL i; - -SELECT '' AS five, i.f1, i.f1 + '2'::int2 AS x FROM INT2_TBL i; - -SELECT '' AS five, i.f1, i.f1 + '2'::int4 AS x FROM INT2_TBL i; - -SELECT '' AS five, i.f1, i.f1 - '2'::int2 AS x FROM INT2_TBL i; - -SELECT '' AS five, i.f1, i.f1 - '2'::int4 AS x FROM INT2_TBL i; - -SELECT '' AS five, i.f1, i.f1 / '2'::int2 AS x FROM INT2_TBL i; - -SELECT '' AS five, i.f1, i.f1 / '2'::int4 AS x FROM INT2_TBL i; - - - --- *************testing built-in type 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 warnings -INSERT INTO INT4_TBL(f1) VALUES ('1000000000000'); - -INSERT INTO INT4_TBL(f1) VALUES ('asdf'); - - -SELECT '' AS five, INT4_TBL.*; - -SELECT '' AS four, i.* FROM INT4_TBL i WHERE i.f1 <> '0'::int2; - -SELECT '' AS four, i.* FROM INT4_TBL i WHERE i.f1 <> '0'::int4; - -SELECT '' AS one, i.* FROM INT4_TBL i WHERE i.f1 = '0'::int2; - -SELECT '' AS one, i.* FROM INT4_TBL i WHERE i.f1 = '0'::int4; - -SELECT '' AS two, i.* FROM INT4_TBL i WHERE i.f1 < '0'::int2; - -SELECT '' AS two, i.* FROM INT4_TBL i WHERE i.f1 < '0'::int4; - -SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 <= '0'::int2; - -SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 <= '0'::int4; - -SELECT '' AS two, i.* FROM INT4_TBL i WHERE i.f1 > '0'::int2; - -SELECT '' AS two, i.* FROM INT4_TBL i WHERE i.f1 > '0'::int4; - -SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 >= '0'::int2; - -SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 >= '0'::int4; - --- positive odds -SELECT '' AS one, i.* FROM INT4_TBL i WHERE (i.f1 % '2'::int2) = '1'::int2; - --- any evens -SELECT '' AS three, i.* FROM INT4_TBL i WHERE (i.f1 % '2'::int4) = '0'::int2; - -SELECT '' AS five, i.f1, i.f1 * '2'::int2 AS x FROM INT4_TBL i; - -SELECT '' AS five, i.f1, i.f1 * '2'::int4 AS x FROM INT4_TBL i; - -SELECT '' AS five, i.f1, i.f1 + '2'::int2 AS x FROM INT4_TBL i; - -SELECT '' AS five, i.f1, i.f1 + '2'::int4 AS x FROM INT4_TBL i; - -SELECT '' AS five, i.f1, i.f1 - '2'::int2 AS x FROM INT4_TBL i; - -SELECT '' AS five, i.f1, i.f1 - '2'::int4 AS x FROM INT4_TBL i; - -SELECT '' AS five, i.f1, i.f1 / '2'::int2 AS x FROM INT4_TBL i; - -SELECT '' AS five, i.f1, i.f1 / '2'::int4 AS x FROM INT4_TBL i; - - --- --- more complex expressions --- -SELECT '2'::int2 * '2'::int2 = '16'::int2 / '4'::int2 AS true; - -SELECT '2'::int4 * '2'::int2 = '16'::int2 / '4'::int4 AS true; - -SELECT '2'::int2 * '2'::int4 = '16'::int4 / '4'::int2 AS true; - -SELECT '1000'::int4 < '999'::int4 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; - -SELECT dsqrt('64'::float8) AS eight; - -SELECT |/'64'::float8 AS eight; - -SELECT ||/'27'::float8 AS three; - - - --- *************testing built-in type 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 (''); - --- bad inputs -INSERT INTO OID_TBL(f1) VALUES ('asdfasd'); - -SELECT '' AS five, OID_TBL.*; - - -SELECT '' AS one, o.* FROM OID_TBL o WHERE o.f1 = '1234'::oid; - -SELECT '' AS four, o.* FROM OID_TBL o WHERE o.f1 <> '1234'; - -SELECT '' AS four, o.* FROM OID_TBL o WHERE o.f1 <= '1234'; - -SELECT '' AS three, o.* FROM OID_TBL o WHERE o.f1 < '1234'; - -SELECT '' AS two, o.* FROM OID_TBL o WHERE o.f1 >= '1234'; - -SELECT '' AS one, o.* FROM OID_TBL o WHERE o.f1 > '1234'; - - --- *************testing built-in type oidname **************** --- oidname is a an adt for multiple key indices involving oid and name --- probably will not be used directly by most users - -CREATE TABLE OIDNAME_TBL(f1 oidname); - -INSERT INTO OIDNAME_TBL(f1) VALUES ('1234,abcd'); - -INSERT INTO OIDNAME_TBL(f1) VALUES ('1235,efgh'); - -INSERT INTO OIDNAME_TBL(f1) VALUES ('987,XXXX'); - --- no char16 component -INSERT INTO OIDNAME_TBL(f1) VALUES ('123456'); - --- char16 component too long -INSERT INTO OIDNAME_TBL(f1) VALUES ('123456,abcdefghijklmnopqrsutvwyz'); - --- bad inputs -INSERT INTO OIDNAME_TBL(f1) VALUES (''); - -INSERT INTO OIDNAME_TBL(f1) VALUES ('asdfasd'); - - -SELECT '' AS four, OIDNAME_TBL.*; - -SELECT '' AS one, o.* FROM OIDNAME_TBL o WHERE o.f1 = '1234,abcd'; - -SELECT '' AS three, o.* FROM OIDNAME_TBL o WHERE o.f1 <> '1234,abcd'; - -SELECT '' AS two, o.* FROM OIDNAME_TBL o WHERE o.f1 <= '1234,abcd'; - -SELECT '' AS one, o.* FROM OIDNAME_TBL o WHERE o.f1 < '1234,abcd'; - -SELECT '' AS three, o.* FROM OIDNAME_TBL o WHERE o.f1 >= '1234,abcd'; - -SELECT '' AS two, o.* FROM OIDNAME_TBL o WHERE o.f1 > '1234,abcd'; - - --- *************testing built-in type oidint2 **************** --- oidint2 is a an adt for multiple key indices involving oid and int2 --- probably will not be used directly by most users - -CREATE TABLE OIDINT2_TBL(f1 oidint2); - -INSERT INTO OIDINT2_TBL(f1) VALUES ('1234/9873'); - -INSERT INTO OIDINT2_TBL(f1) VALUES ('1235/9873'); - -INSERT INTO OIDINT2_TBL(f1) VALUES ('987/-1234'); - --- no int2 component --- --- this is defined as good in the code -- I don't know what will break --- if we disallow it. --- -INSERT INTO OIDINT2_TBL(f1) VALUES ('123456'); - --- int2 component too large -INSERT INTO OIDINT2_TBL(f1) VALUES ('123456/123456'); - --- --- this is defined as good in the code -- I don't know what will break --- if we disallow it. --- -INSERT INTO OIDINT2_TBL(f1) VALUES (''); - --- bad inputs -INSERT INTO OIDINT2_TBL(f1) VALUES ('asdfasd'); - - -SELECT '' AS five, OIDINT2_TBL.*; - -SELECT '' AS one, o.* FROM OIDINT2_TBL o WHERE o.f1 = '1235/9873'; - -SELECT '' AS four, o.* FROM OIDINT2_TBL o WHERE o.f1 <> '1235/9873'; - -SELECT '' AS four, o.* FROM OIDINT2_TBL o WHERE o.f1 <= '1235/9873'; - -SELECT '' AS three, o.* FROM OIDINT2_TBL o WHERE o.f1 < '1235/9873'; - -SELECT '' AS two, o.* FROM OIDINT2_TBL o WHERE o.f1 >= '1235/9873'; - -SELECT '' AS one, o.* FROM OIDINT2_TBL o WHERE o.f1 > '1235/9873'; - - ---*************testing built-in type oidint4 **************** --- oidint4 is a an adt for multiple key indices involving oid and int4 --- probably will not be used directly by most users - -CREATE TABLE OIDINT4_TBL(f1 oidint4); - -INSERT INTO OIDINT4_TBL(f1) VALUES ('1234/9873'); - -INSERT INTO OIDINT4_TBL(f1) VALUES ('1235/9873'); - -INSERT INTO OIDINT4_TBL(f1) VALUES ('987/-1234'); - --- no int4 component --- --- this is defined as good in the code -- I don't know what will break --- if we disallow it. --- -INSERT INTO OIDINT4_TBL(f1) VALUES ('123456'); - --- int4 component too large -INSERT INTO OIDINT4_TBL(f1) VALUES ('123456/1234568901234567890'); - --- --- this is defined as good in the code -- I don't know what will break --- if we disallow it. --- -INSERT INTO OIDINT4_TBL(f1) VALUES (''); - --- bad inputs -INSERT INTO OIDINT4_TBL(f1) VALUES ('asdfasd'); - -SELECT '' AS five, OIDINT4_TBL.*; - -SELECT '' AS one, o.* FROM OIDINT4_TBL o WHERE o.f1 = '1235/9873'; - -SELECT '' AS four, o.* FROM OIDINT4_TBL o WHERE o.f1 <> '1235/9873'; - -SELECT '' AS four, o.* FROM OIDINT4_TBL o WHERE o.f1 <= '1235/9873'; - -SELECT '' AS three, o.* FROM OIDINT4_TBL o WHERE o.f1 < '1235/9873'; - -SELECT '' AS two, o.* FROM OIDINT4_TBL o WHERE o.f1 >= '1235/9873'; - -SELECT '' AS one, o.* FROM OIDINT4_TBL o WHERE o.f1 > '1235/9873'; - - --- ************testing built-in type point **************** - -CREATE TABLE POINT_TBL(f1 point); - -INSERT INTO POINT_TBL(f1) VALUES ('(0.0,0.0)'); - -INSERT INTO POINT_TBL(f1) VALUES ('(-10.0,0.0)'); - -INSERT INTO POINT_TBL(f1) VALUES ('(-3.0,4.0)'); - -INSERT INTO POINT_TBL(f1) VALUES ('(5.1, 34.5)'); - -INSERT INTO POINT_TBL(f1) VALUES ('(-5.0,-12.0)'); - --- bad format points -INSERT INTO POINT_TBL(f1) VALUES ('asdfasdf'); - -INSERT INTO POINT_TBL(f1) VALUES ('10.0,10.0'); - -INSERT INTO POINT_TBL(f1) VALUES ('(10.0 10.0)'); - -INSERT INTO POINT_TBL(f1) VALUES ('(10.0,10.0'); - - -SELECT '' AS five, POINT_TBL.*; - --- left of -SELECT '' AS three, p.* FROM POINT_TBL p WHERE p.f1 !< '(0.0, 0.0)'; - --- right of -SELECT '' AS three, p.* FROM POINT_TBL p WHERE '(0.0,0.0)' !> p.f1; - --- above -SELECT '' AS one, p.* FROM POINT_TBL p WHERE '(0.0,0.0)' !^ p.f1; - --- below -SELECT '' AS one, p.* FROM POINT_TBL p WHERE p.f1 !| '(0.0, 0.0)'; - --- equal -SELECT '' AS one, p.* FROM POINT_TBL p WHERE p.f1 =|= '(5.1, 34.5)'; - --- point in box -SELECT '' AS two, p.* FROM POINT_TBL p - WHERE p.f1 ===> '(0,0,100,100)'; - -SELECT '' AS three, p.* FROM POINT_TBL p - WHERE not on_pb(p.f1,'(0,0,100,100)'::box); - -SELECT '' AS two, p.* FROM POINT_TBL p - WHERE on_ppath(p.f1,'(0,3,0,0,-10,0,-10,10)'::path); - -SELECT '' AS five, p.f1, p.f1 <===> '(0,0)' AS dist FROM POINT_TBL p; - -SELECT '' AS twentyfive, p1.f1, p2.f1, p1.f1 <===> p2.f1 AS dist - FROM POINT_TBL p1, POINT_TBL p2; - -SELECT '' AS twenty, p1.f1, p2.f1 - FROM POINT_TBL p1, POINT_TBL p2 - WHERE (p1.f1 <===> p2.f1) > 3; - -SELECT '' AS ten, p1.f1, p2.f1 - FROM POINT_TBL p1, POINT_TBL p2 - WHERE (p1.f1 <===> p2.f1) > 3 and - p1.f1 !< p2.f1; - -SELECT '' AS two, p1.f1, p2.f1 - FROM POINT_TBL p1, POINT_TBL p2 - WHERE (p1.f1 <===> p2.f1) > 3 and - p1.f1 !< p2.f1 and - p1.f1 !^ p2.f1; - - --- *************testing built-in type polygon **************** --- --- polygon logic --- --- 3 o --- | --- 2 + | --- / | --- 1 # o + --- / | --- 0 #-----o-+ --- --- 0 1 2 3 4 --- - -CREATE TABLE POLYGON_TBL(f1 polygon); - - -INSERT INTO POLYGON_TBL(f1) VALUES ('(2.0,2.0,0.0,0.0,4.0,0.0)'); - -INSERT INTO POLYGON_TBL(f1) VALUES ('(3.0,3.0,1.0,1.0,3.0,0.0)'); - --- degenerate polygons -INSERT INTO POLYGON_TBL(f1) VALUES ('(0.0,0.0)'); - -INSERT INTO POLYGON_TBL(f1) VALUES ('(0.0,0.0,1.0,1.0)'); --- bad polygon input strings -INSERT INTO POLYGON_TBL(f1) VALUES ('0.0'); - -INSERT INTO POLYGON_TBL(f1) VALUES ('(0.0 0.0'); - -INSERT INTO POLYGON_TBL(f1) VALUES ('(0,1,2)'); - -INSERT INTO POLYGON_TBL(f1) VALUES ('(0,1,2,3'); - -INSERT INTO POLYGON_TBL(f1) VALUES ('asdf'); - - -SELECT '' AS four, POLYGON_TBL.*; - --- overlap -SELECT '' AS three, p.* - FROM POLYGON_TBL p - WHERE p.f1 && '(3.0,3.0,1.0,1.0,3.0,0.0)'; - --- left overlap -SELECT '' AS four, p.* - FROM POLYGON_TBL p - WHERE p.f1 &< '(3.0,3.0,1.0,1.0,3.0,0.0)'; - --- right overlap -SELECT '' AS two, p.* - FROM POLYGON_TBL p - WHERE p.f1 &> '(3.0,3.0,1.0,1.0,3.0,0.0)'; - --- left of -SELECT '' AS one, p.* - FROM POLYGON_TBL p - WHERE p.f1 << '(3.0,3.0,1.0,1.0,3.0,0.0)'; - --- right of -SELECT '' AS zero, p.* - FROM POLYGON_TBL p - WHERE p.f1 >> '(3.0,3.0,1.0,1.0,3.0,0.0)'; - --- contained -SELECT '' AS one, p.* - FROM POLYGON_TBL p - WHERE p.f1 @ '(3.0,3.0,1.0,1.0,3.0,0.0)'; - --- same -SELECT '' AS one, p.* - FROM POLYGON_TBL p - WHERE p.f1 ~= '(3.0,3.0,1.0,1.0,3.0,0.0)'; - --- contains -SELECT '' AS one, p.* - FROM POLYGON_TBL p - WHERE p.f1 ~ '(3.0,3.0,1.0,1.0,3.0,0.0)'; - - --- *************testing built-in type text **************** - --- --- adt operators in the target list --- --- fixed-length by reference -SELECT 'char 16 string'::char16 = 'char 16 string '::char16 AS false; - --- fixed-length by value -SELECT 'c'::char = 'c'::char AS true; - --- variable-length -SELECT 'this is a text string'::text = 'this is a text string'::text AS true; - -SELECT 'this is a text string'::text = 'this is a text strin'::text AS false; - --- --- polygon logic --- --- 3 o --- | --- 2 + | --- / | --- 1 / o + --- / | --- 0 +-----o-+ --- --- 0 1 2 3 4 --- --- left of -SELECT '(2.0,2.0,0.0,0.0,4.0,0.0)'::polygon << '(3.0,3.0,1.0,1.0,3.0,0.0)'::polygon AS false; - --- left overlap -SELECT '(2.0,2.0,0.0,0.0,4.0,0.0)'::polygon &< '(3.0,3.0,1.0,1.0,3.0,0.0)'::polygon AS true; - --- right overlap -SELECT '(2.0,2.0,0.0,0.0,4.0,0.0)'::polygon &> '(3.0,3.0,1.0,1.0,3.0,0.0)'::polygon AS true; - --- right of -SELECT '(2.0,2.0,0.0,0.0,4.0,0.0)'::polygon >> '(3.0,3.0,1.0,1.0,3.0,0.0)'::polygon AS false; - --- contained in -SELECT '(2.0,2.0,0.0,0.0,4.0,0.0)'::polygon @ '(3.0,3.0,1.0,1.0,3.0,0.0)'::polygon AS false; - --- contains -SELECT '(2.0,2.0,0.0,0.0,4.0,0.0)'::polygon ~ '(3.0,3.0,1.0,1.0,3.0,0.0)'::polygon AS false; - --- same -SELECT '(2.0,2.0,0.0,0.0,4.0,0.0)'::polygon ~= '(3.0,3.0,1.0,1.0,3.0,0.0)'::polygon AS false; - --- overlap -SELECT '(2.0,2.0,0.0,0.0,4.0,0.0)'::polygon && '(3.0,3.0,1.0,1.0,3.0,0.0)'::polygon AS true; - - -- -- qualifications diff --git a/src/test/regress/regress.sh b/src/test/regress/regress.sh index cdb154b4a3..dd6467a5a5 100755 --- a/src/test/regress/regress.sh +++ b/src/test/regress/regress.sh @@ -1,5 +1,5 @@ #!/bin/sh -# $Header: /cvsroot/pgsql/src/test/regress/Attic/regress.sh,v 1.3 1996/11/14 16:09:28 momjian Exp $ +# $Header: /cvsroot/pgsql/src/test/regress/Attic/regress.sh,v 1.4 1997/04/05 11:58:35 scrappy Exp $ # if [ -d ./obj ]; then cd ./obj @@ -20,13 +20,25 @@ if [ $? -ne 0 ]; then exit 1 fi -$FRONTEND regression < create.sql -if [ $? -ne 0 ]; then - echo the creation script has an error - exit 1 -fi +#$FRONTEND regression < create.sql +#if [ $? -ne 0 ]; then +# echo the creation script has an error +# exit 1 +#fi echo =============== running regression queries ... ================= +for i in `cat sql/tests` +do + echo -n ${i} .. + $FRONTEND regression < sql/${i}.sql 2>&1 | tee output/${i}.out + if [ `diff expected/${i}.out output/${i}.out | wc -l` -ne 0 ] + then + echo failed + else + echo ok + fi +done +exit $FRONTEND regression < queries.sql # this will generate error result code diff --git a/src/test/regress/sql/tests b/src/test/regress/sql/tests index a9aec283e7..3661f3686d 100644 --- a/src/test/regress/sql/tests +++ b/src/test/regress/sql/tests @@ -11,7 +11,6 @@ float4 float8 int2 int4 -misc oid oidint2 oidint4 -- 2.40.0