diff options
| author | Thomas G. Lockhart <lockhart@fourpalms.org> | 1997-05-11 15:42:09 +0000 |
|---|---|---|
| committer | Thomas G. Lockhart <lockhart@fourpalms.org> | 1997-05-11 15:42:09 +0000 |
| commit | 83b8cf5b6b91b7ecfda7579b4d8e9c830124f286 (patch) | |
| tree | adf2b262e5c917f03cfd016bc45d94ef1fb60d25 /src | |
| parent | 57f55032ff54c0a2f9f821b8df1b522bc41fd418 (diff) | |
| download | postgresql-83b8cf5b6b91b7ecfda7579b4d8e9c830124f286.tar.gz postgresql-83b8cf5b6b91b7ecfda7579b4d8e9c830124f286.zip | |
Add inter-type regression tests for geometry, date/time, and numbers.
Add regression tests for circles, line segments, and paths.
Modify regression tests to allow GEQ optimizer (order results).
Diffstat (limited to 'src')
34 files changed, 1626 insertions, 165 deletions
diff --git a/src/test/regress/expected/abstime.out b/src/test/regress/expected/abstime.out index b189423e62c..5278e652c78 100644 --- a/src/test/regress/expected/abstime.out +++ b/src/test/regress/expected/abstime.out @@ -139,20 +139,21 @@ three|f1 |Mon May 10 23:59:12 1943 PST (3 rows) -QUERY: SELECT '' AS ten, ABSTIME_TBL.*, RELTIME_TBL.* +QUERY: SELECT '' AS ten, ABSTIME_TBL.f1 AS abstime, RELTIME_TBL.f1 AS reltime WHERE (ABSTIME_TBL.f1 + RELTIME_TBL.f1) - < 'Jan 14 14:00:00 1971'::abstime; -ten|f1 |f1 + < 'Jan 14 14:00:00 1971'::abstime + ORDER BY abstime, reltime; +ten|abstime |reltime ---+----------------------------+---------------- - |epoch |@ 1 minute + |Mon May 10 23:59:12 1943 PST|@ 14 seconds ago |Mon May 10 23:59:12 1943 PST|@ 1 minute - |epoch |@ 5 hours |Mon May 10 23:59:12 1943 PST|@ 5 hours - |epoch |@ 10 days |Mon May 10 23:59:12 1943 PST|@ 10 days - |epoch |@ 3 months |Mon May 10 23:59:12 1943 PST|@ 3 months |epoch |@ 14 seconds ago - |Mon May 10 23:59:12 1943 PST|@ 14 seconds ago + |epoch |@ 1 minute + |epoch |@ 5 hours + |epoch |@ 10 days + |epoch |@ 3 months (10 rows) diff --git a/src/test/regress/expected/box.out b/src/test/regress/expected/box.out index a3d5329fc3b..e8e23c1587c 100644 --- a/src/test/regress/expected/box.out +++ b/src/test/regress/expected/box.out @@ -167,4 +167,12 @@ one|f1 |f1 |(3,3),(1,1)|(3,3),(3,3) (1 row) -QUERY: DROP TABLE BOX_TBL; +QUERY: SELECT '' AS four, height(f1), width(f1) FROM BOX_TBL; +four|height|width +----+------+----- + | 2| 2 + | 2| 2 + | 1| 0 + | 0| 0 +(4 rows) + diff --git a/src/test/regress/expected/circle.out b/src/test/regress/expected/circle.out new file mode 100644 index 00000000000..8c950d6c00c --- /dev/null +++ b/src/test/regress/expected/circle.out @@ -0,0 +1,89 @@ +QUERY: CREATE TABLE CIRCLE_TBL (f1 circle); +QUERY: INSERT INTO CIRCLE_TBL VALUES ('<(0,0),3>'); +QUERY: INSERT INTO CIRCLE_TBL VALUES ('<(1,2),100>'); +QUERY: INSERT INTO CIRCLE_TBL VALUES ('1,3,5'); +QUERY: INSERT INTO CIRCLE_TBL VALUES ('((1,2),3)'); +QUERY: INSERT INTO CIRCLE_TBL VALUES ('<(100,200),10>'); +QUERY: INSERT INTO CIRCLE_TBL VALUES ('<(100,0),100>'); +QUERY: INSERT INTO CIRCLE_TBL VALUES ('<(-100,0),-100>'); +WARN:Bad circle external representation '<(-100,0),-100>' +QUERY: INSERT INTO CIRCLE_TBL VALUES ('1abc,3,5'); +WARN:Bad circle external representation '1abc,3,5' +QUERY: INSERT INTO CIRCLE_TBL VALUES ('(3,(1,2),3)'); +WARN:Bad circle external representation '(3,(1,2),3)' +QUERY: SELECT * FROM CIRCLE_TBL; +f1 +-------------- +<(0,0),3> +<(1,2),100> +<(1,3),5> +<(1,2),3> +<(100,200),10> +<(100,0),100> +(6 rows) + +QUERY: SELECT '' AS six, center(f1) AS center + FROM CIRCLE_TBL; +six|center +---+--------- + |(0,0) + |(1,2) + |(1,3) + |(1,2) + |(100,200) + |(100,0) +(6 rows) + +QUERY: SELECT '' AS six, radius(f1) AS radius + FROM CIRCLE_TBL; +six|radius +---+------ + | 3 + | 100 + | 5 + | 3 + | 10 + | 100 +(6 rows) + +QUERY: SELECT '' AS six, diameter(f1) AS diameter + FROM CIRCLE_TBL; +six|diameter +---+-------- + | 6 + | 200 + | 10 + | 6 + | 20 + | 200 +(6 rows) + +QUERY: SELECT '' AS two, f1 FROM CIRCLE_TBL WHERE radius(f1) < 5; +two|f1 +---+--------- + |<(0,0),3> + |<(1,2),3> +(2 rows) + +QUERY: SELECT '' AS four, f1 FROM CIRCLE_TBL WHERE diameter(f1) >= 10; +four|f1 +----+-------------- + |<(1,2),100> + |<(1,3),5> + |<(100,200),10> + |<(100,0),100> +(4 rows) + +QUERY: SELECT '' as five, c1.f1 AS one, c2.f1 AS two, (c1.f1 <===> c2.f1) AS distance + FROM CIRCLE_TBL c1, CIRCLE_TBL c2 + WHERE (c1.f1 < c2.f1) AND ((c1.f1 <===> c2.f1) > 0) + ORDER BY distance, one, two; +five|one |two | distance +----+--------------+--------------+---------------- + |<(100,200),10>|<(100,0),100> | 90 + |<(100,200),10>|<(1,2),100> |111.370729772479 + |<(1,3),5> |<(100,200),10>|205.476756144497 + |<(1,2),3> |<(100,200),10>|208.370729772479 + |<(0,0),3> |<(100,200),10>|210.606797749979 +(5 rows) + diff --git a/src/test/regress/expected/datetime.out b/src/test/regress/expected/datetime.out index 6a0858f4e76..d8548f4ee8d 100644 --- a/src/test/regress/expected/datetime.out +++ b/src/test/regress/expected/datetime.out @@ -112,6 +112,7 @@ QUERY: INSERT INTO DATETIME_TBL VALUES ('Jan 01 17:32:01 2001'); QUERY: INSERT INTO DATETIME_TBL VALUES ('Feb 16 17:32:01 -0097'); WARN:Bad datetime external representation Feb 16 17:32:01 -0097 QUERY: INSERT INTO DATETIME_TBL VALUES ('Feb 16 17:32:01 5097 BC'); +WARN:Datetime out of range Feb 16 17:32:01 5097 BC QUERY: SELECT '' AS sixtythree, d1 FROM DATETIME_TBL; sixtythree|d1 ----------+------------------------------- @@ -177,8 +178,7 @@ sixtythree|d1 |Sat Jan 01 17:32:01.00 2000 PST |Sun Dec 31 17:32:01.00 2000 PST |Mon Jan 01 17:32:01.00 2001 PST - |invalid -(63 rows) +(62 rows) QUERY: SELECT '' AS fortythree, d1 FROM DATETIME_TBL WHERE d1 > '1997-01-02'::datetime and d1 != 'current'::datetime; @@ -458,8 +458,7 @@ sixtythree|one_year |Mon Jan 01 17:32:01.00 2001 PST |Mon Dec 31 17:32:01.00 2001 PST |Tue Jan 01 17:32:01.00 2002 PST - |invalid -(63 rows) +(62 rows) QUERY: SELECT '' AS sixtythree, d1 - '1 year'::timespan AS one_year FROM DATETIME_TBL; sixtythree|one_year @@ -526,8 +525,7 @@ sixtythree|one_year |Fri Jan 01 17:32:01.00 1999 PST |Fri Dec 31 17:32:01.00 1999 PST |Sat Jan 01 17:32:01.00 2000 PST - |invalid -(63 rows) +(62 rows) QUERY: SELECT '' AS fifty, d1 - '1997-01-02'::datetime AS diff FROM DATETIME_TBL WHERE d1 BETWEEN '1902-01-01' AND '2038-01-01'; diff --git a/src/test/regress/expected/errors.out b/src/test/regress/expected/errors.out index 637d3faaa02..f3ff2dd7b1e 100644 --- a/src/test/regress/expected/errors.out +++ b/src/test/regress/expected/errors.out @@ -1,7 +1,7 @@ QUERY: select 1 select select * from nonesuch; -WARN:parser: syntax error at or near "select" +WARN:parser: parse error at or near "select" QUERY: select nonesuch from pg_database; WARN:attribute "nonesuch" not found @@ -10,22 +10,22 @@ WARN:attribute "nonesuch" not found QUERY: select * from pg_database where pg_database.datname = nonesuch; WARN:attribute "nonesuch" not found QUERY: select distinct on foobar from pg_database; -WARN:parser: syntax error at or near "from" +WARN:parser: parse error at or near "from" QUERY: select distinct on foobar * from pg_database; WARN:The field specified in the UNIQUE ON clause is not in the targetlist QUERY: delete from; -WARN:parser: syntax error at or near ";" +WARN:parser: parse error at or near ";" QUERY: delete from nonesuch; WARN:nonesuch: Table does not exist. QUERY: drop table; -WARN:parser: syntax error at or near ";" +WARN:parser: parse error at or near ";" QUERY: drop table nonesuch; WARN:Relation nonesuch Does Not Exist! QUERY: alter table rename; -WARN:parser: syntax error at or near "rename" +WARN:parser: parse error at or near "rename" QUERY: alter table nonesuch rename to newnonesuch; WARN:renamerel: relation "nonesuch" does not exist @@ -84,54 +84,54 @@ QUERY: create aggregate newcnt1 (sfunc2 = int4inc, stype2 = int4); WARN:AggregateCreate: transition function 2 MUST have an initial value QUERY: drop index; -WARN:parser: syntax error at or near ";" +WARN:parser: parse error at or near ";" QUERY: drop index 314159; -WARN:parser: syntax error at or near "314159" +WARN:parser: parse error at or near "314159" QUERY: drop index nonesuch; WARN:index "nonesuch" nonexistent QUERY: drop aggregate; -WARN:parser: syntax error at or near ";" +WARN:parser: parse error at or near ";" QUERY: drop aggregate 314159; -WARN:parser: syntax error at or near "314159" +WARN:parser: parse error at or near "314159" QUERY: drop aggregate nonesuch; WARN:RemoveAggregate: aggregate 'nonesuch' does not exist QUERY: drop function (); -WARN:parser: syntax error at or near "(" +WARN:parser: parse error at or near "(" QUERY: drop function 314159(); -WARN:parser: syntax error at or near "314159" +WARN:parser: parse error at or near "314159" QUERY: drop function nonesuch(); WARN:RemoveFunction: function nonesuch() does not exist QUERY: drop type; -WARN:parser: syntax error at or near ";" +WARN:parser: parse error at or near ";" QUERY: drop type 314159; -WARN:parser: syntax error at or near "314159" +WARN:parser: parse error at or near "314159" QUERY: drop type nonesuch; WARN:RemoveType: type 'nonesuch' does not exist QUERY: drop operator; -WARN:parser: syntax error at or near ";" +WARN:parser: parse error at or near ";" QUERY: drop operator equals; -WARN:parser: syntax error at or near "equals" +WARN:parser: parse error at or near "equals" QUERY: drop operator ===; -WARN:parser: syntax error at or near ";" +WARN:parser: parse error at or near ";" QUERY: drop operator int4, int4; -WARN:parser: syntax error at or near "int4" +WARN:parser: parse error at or near "int4" QUERY: drop operator (int4, int4); -WARN:parser: syntax error at or near "(" +WARN:parser: parse error at or near "(" QUERY: drop operator === (); -WARN:parser: syntax error at or near ")" +WARN:parser: parse error at or near ")" QUERY: drop operator === (int4); WARN:parser: argument type missing (use NONE for unary operators) @@ -140,29 +140,29 @@ WARN:RemoveOperator: binary operator '===' taking 'int4' and 'int4' does not exi QUERY: drop operator = (nonesuch); WARN:parser: argument type missing (use NONE for unary operators) QUERY: drop operator = ( , int4); -WARN:parser: syntax error at or near "," +WARN:parser: parse error at or near "," QUERY: drop operator = (nonesuch, int4); WARN:RemoveOperator: type 'nonesuch' does not exist QUERY: drop operator = (int4, nonesuch); WARN:RemoveOperator: type 'nonesuch' does not exist QUERY: drop operator = (int4, ); -WARN:parser: syntax error at or near ")" +WARN:parser: parse error at or near ")" QUERY: drop rule; -WARN:parser: syntax error at or near ";" +WARN:parser: parse error at or near ";" QUERY: drop rule 314159; -WARN:parser: syntax error at or near "314159" +WARN:parser: parse error at or near "314159" QUERY: drop rule nonesuch; WARN:RewriteGetRuleEventRel: rule "nonesuch" not found QUERY: drop tuple rule nonesuch; -WARN:parser: syntax error at or near "tuple" +WARN:parser: parse error at or near "tuple" QUERY: drop instance rule nonesuch; -WARN:parser: syntax error at or near "instance" +WARN:parser: parse error at or near "instance" QUERY: drop rewrite rule nonesuch; -WARN:parser: syntax error at or near "rewrite" +WARN:parser: parse error at or near "rewrite" diff --git a/src/test/regress/expected/float4.out b/src/test/regress/expected/float4.out index 143f6d62fc2..92cfe073af7 100644 --- a/src/test/regress/expected/float4.out +++ b/src/test/regress/expected/float4.out @@ -146,4 +146,3 @@ five|f1 |-1.23457e-20 (5 rows) -QUERY: DROP TABLE FLOAT4_TBL; diff --git a/src/test/regress/expected/float8.out b/src/test/regress/expected/float8.out index 78e375725c5..45ae14a34c2 100644 --- a/src/test/regress/expected/float8.out +++ b/src/test/regress/expected/float8.out @@ -223,4 +223,3 @@ five|f1 |-1.2345678901234e-200 (5 rows) -QUERY: DROP TABLE FLOAT8_TBL; diff --git a/src/test/regress/expected/geometry.out b/src/test/regress/expected/geometry.out new file mode 100644 index 00000000000..a572587d2ff --- /dev/null +++ b/src/test/regress/expected/geometry.out @@ -0,0 +1,204 @@ +QUERY: SELECT point(f1) FROM CIRCLE_TBL; +point +--------- +(0,0) +(1,2) +(1,3) +(1,2) +(100,200) +(100,0) +(6 rows) + +QUERY: SELECT '' AS two, p1.f1 + FROM POINT_TBL p1 + WHERE ishorizontal(p1.f1, '(0,0)'::point); +two|f1 +---+------- + |(0,0) + |(-10,0) +(2 rows) + +QUERY: SELECT '' AS one, p1.f1 + FROM POINT_TBL p1 + WHERE isvertical(p1.f1, '(5.1,34.5)'::point); +one|f1 +---+---------- + |(5.1,34.5) +(1 row) + +QUERY: SELECT center(f1) FROM BOX_TBL; +center +------- +(1,1) +(2,2) +(2.5,3) +(3,3) +(4 rows) + +QUERY: SELECT box(f1) FROM CIRCLE_TBL; +WARN:func_get_detail: function box(circle) does not exist +QUERY: SELECT '' AS count, b.f1 + p.f1 + FROM BOX_TBL b, POINT_TBL p; +NOTICE:there is no operator + for types box and point +NOTICE:You will either have to retype this query using an +NOTICE:explicit cast, or you will have to define the operator +WARN:+ for box and point using CREATE OPERATOR +QUERY: SELECT '' AS count, b.f1 * p.f1 + FROM BOX_TBL b, POINT_TBL p; +NOTICE:there is no operator * for types box and point +NOTICE:You will either have to retype this query using an +NOTICE:explicit cast, or you will have to define the operator +WARN:* for box and point using CREATE OPERATOR +QUERY: SET geqo TO 'off'; +QUERY: SELECT points(f1) AS npoints, f1 AS path FROM PATH_TBL; +npoints|path +-------+------------------------- + 2|[(1,2),(3,4)] + 2|((1,2),(3,4)) + 4|[(0,0),(3,0),(4,5),(1,6)] + 2|((1,2),(3,4)) + 2|((1,2),(3,4)) + 2|[(1,2),(3,4)] + 2|[(11,12),(13,14)] + 2|((11,12),(13,14)) +(8 rows) + +QUERY: SELECT path(f1) FROM POLYGON_TBL; +path +------------------- +((2,0),(2,4),(0,0)) +((3,1),(3,3),(1,0)) +((0,0)) +((0,1),(0,1)) +(4 rows) + +QUERY: SELECT '' AS eight, p1.f1 + '(10,10)'::point AS dist_add + FROM PATH_TBL p1; +eight|dist_add +-----+--------------------------------- + |[(11,12),(13,14)] + |((11,12),(13,14)) + |[(10,10),(13,10),(14,15),(11,16)] + |((11,12),(13,14)) + |((11,12),(13,14)) + |[(11,12),(13,14)] + |[(21,22),(23,24)] + |((21,22),(23,24)) +(8 rows) + +QUERY: SELECT '' AS eight, p1.f1 * '(2,-1)'::point AS dist_mul + FROM PATH_TBL p1; +eight|dist_mul +-----+---------------------------- + |[(4,3),(10,5)] + |((4,3),(10,5)) + |[(0,0),(6,-3),(13,6),(8,11)] + |((4,3),(10,5)) + |((4,3),(10,5)) + |[(4,3),(10,5)] + |[(34,13),(40,15)] + |((34,13),(40,15)) +(8 rows) + +QUERY: RESET geqo; +QUERY: SELECT points(f1) AS npoints, f1 AS polygon FROM POLYGON_TBL; +npoints|polygon +-------+------------------- + 3|((2,0),(2,4),(0,0)) + 3|((3,1),(3,3),(1,0)) + 1|((0,0)) + 2|((0,1),(0,1)) +(4 rows) + +QUERY: SELECT polygon(f1) FROM BOX_TBL; +polygon +----------------------------------------- +((0,0),(0,2),(2,2),(2,0)) +((1,1),(1,3),(3,3),(3,1)) +((2.5,2.5),(2.5,3.5),(2.5,3.5),(2.5,2.5)) +((3,3),(3,3),(3,3),(3,3)) +(4 rows) + +QUERY: SELECT polygon(f1) FROM PATH_TBL WHERE isclosed(f1); +polygon +----------------- +((1,2),(3,4)) +((1,2),(3,4)) +((1,2),(3,4)) +((11,12),(13,14)) +(4 rows) + +QUERY: SELECT f1 AS open_path, polygon( pclose(f1)) AS polygon FROM PATH_TBL WHERE isopen(f1); +open_path |polygon +-------------------------+------------------------- +[(1,2),(3,4)] |((1,2),(3,4)) +[(0,0),(3,0),(4,5),(1,6)]|((0,0),(3,0),(4,5),(1,6)) +[(1,2),(3,4)] |((1,2),(3,4)) +[(11,12),(13,14)] |((11,12),(13,14)) +(4 rows) + +QUERY: SELECT polygon(f1) FROM CIRCLE_TBL; +polygon +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ +((-3,0),(-2.59807621135332,1.5),(-1.5,2.59807621135332),(-1.83690953073357e-16,3),(1.5,2.59807621135332),(2.59807621135332,1.5),(3,3.67381906146713e-16),(2.59807621135332,-1.5),(1.5,-2.59807621135332),(5.5107285922007e-16,-3),(-1.5,-2.59807621135332),(-2.59807621135332,-1.5)) +((-99,2),(-85.6025403784439,52),(-49,88.6025403784439),(0.999999999999994,102),(51,88.6025403784439),(87.6025403784439,52),(101,2.00000000000001),(87.6025403784439,-48),(51,-84.6025403784438),(1.00000000000002,-98),(-49,-84.6025403784439),(-85.6025403784438,-48)) +((-4,3),(-3.33012701892219,5.5),(-1.5,7.33012701892219),(1,8),(3.5,7.33012701892219),(5.33012701892219,5.5),(6,3),(5.33012701892219,0.500000000000001),(3.5,-1.33012701892219),(1,-2),(-1.5,-1.33012701892219),(-3.33012701892219,0.499999999999998)) +((-2,2),(-1.59807621135332,3.5),(-0.5,4.59807621135332),(1,5),(2.5,4.59807621135332),(3.59807621135332,3.5),(4,2),(3.59807621135332,0.500000000000001),(2.5,-0.598076211353315),(1,-1),(-0.5,-0.598076211353316),(-1.59807621135332,0.499999999999999)) +((90,200),(91.3397459621556,205),(95,208.660254037844),(100,210),(105,208.660254037844),(108.660254037844,205),(110,200),(108.660254037844,195),(105,191.339745962156),(100,190),(95,191.339745962156),(91.3397459621556,195)) +((0,0),(13.3974596215561,50),(50,86.6025403784439),(100,100),(150,86.6025403784439),(186.602540378444,50),(200,1.22460635382238e-14),(186.602540378444,-50),(150,-86.6025403784438),(100,-100),(50,-86.6025403784439),(13.3974596215562,-50)) +(6 rows) + +QUERY: SELECT polygon(8, f1) FROM CIRCLE_TBL; +polygon +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- +((-3,0),(-2.12132034355964,2.12132034355964),(-1.83690953073357e-16,3),(2.12132034355964,2.12132034355964),(3,3.67381906146713e-16),(2.12132034355964,-2.12132034355964),(5.5107285922007e-16,-3),(-2.12132034355964,-2.12132034355964)) +((-99,2),(-69.7106781186548,72.7106781186548),(0.999999999999994,102),(71.7106781186547,72.7106781186548),(101,2.00000000000001),(71.7106781186548,-68.7106781186547),(1.00000000000002,-98),(-69.7106781186547,-68.7106781186548)) +((-4,3),(-2.53553390593274,6.53553390593274),(1,8),(4.53553390593274,6.53553390593274),(6,3),(4.53553390593274,-0.535533905932737),(1,-2),(-2.53553390593274,-0.535533905932738)) +((-2,2),(-1.12132034355964,4.12132034355964),(1,5),(3.12132034355964,4.12132034355964),(4,2),(3.12132034355964,-0.121320343559642),(1,-1),(-1.12132034355964,-0.121320343559643)) +((90,200),(92.9289321881345,207.071067811865),(100,210),(107.071067811865,207.071067811865),(110,200),(107.071067811865,192.928932188135),(100,190),(92.9289321881345,192.928932188135)) +((0,0),(29.2893218813452,70.7106781186548),(100,100),(170.710678118655,70.7106781186548),(200,1.22460635382238e-14),(170.710678118655,-70.7106781186547),(100,-100),(29.2893218813453,-70.7106781186548)) +(6 rows) + +QUERY: SELECT circle( f1, 50.0) FROM POINT_TBL; +circle +--------------- +<(0,0),50> +<(-10,0),50> +<(-3,4),50> +<(5.1,34.5),50> +<(-5,-12),50> +<(10,10),50> +(6 rows) + +QUERY: SELECT '' AS twentyfour, c1.f1 AS circle, p1.f1 AS point, (p1.f1 <===> c1.f1) AS distance + from CIRCLE_TBL c1, POINT_TBL p1 + WHERE (p1.f1 <===> c1.f1) > 0 + ORDER BY distance, circle; +twentyfour|circle |point | distance +----------+--------------+----------+----------------- + |<(100,0),100> |(5.1,34.5)|0.976531926977964 + |<(1,2),3> |(-3,4) | 1.47213595499958 + |<(0,0),3> |(-3,4) | 2 + |<(100,0),100> |(-3,4) | 3.07764064044151 + |<(100,0),100> |(-5,-12) | 5.68348972285122 + |<(1,3),5> |(10,10) | 6.40175425099138 + |<(1,3),5> |(-10,0) | 6.40175425099138 + |<(0,0),3> |(-10,0) | 7 + |<(1,2),3> |(-10,0) | 8.18033988749895 + |<(1,2),3> |(10,10) | 9.0415945787923 + |<(0,0),3> |(-5,-12) | 10 + |<(100,0),100> |(-10,0) | 10 + |<(0,0),3> |(10,10) | 11.142135623731 + |<(1,3),5> |(-5,-12) | 11.1554944214035 + |<(1,2),3> |(-5,-12) | 12.2315462117278 + |<(1,3),5> |(5.1,34.5)| 26.7657047773224 + |<(1,2),3> |(5.1,34.5)| 29.757594539282 + |<(0,0),3> |(5.1,34.5)| 31.8749193547455 + |<(100,200),10>|(5.1,34.5)| 180.778038568384 + |<(100,200),10>|(10,10) | 200.237960416286 + |<(100,200),10>|(-3,4) | 211.415898254845 + |<(100,200),10>|(0,0) | 213.606797749979 + |<(100,200),10>|(-10,0) | 218.254244210267 + |<(100,200),10>|(-5,-12) | 226.577682802077 +(24 rows) + diff --git a/src/test/regress/expected/horology.out b/src/test/regress/expected/horology.out new file mode 100644 index 00000000000..7904e32eef8 --- /dev/null +++ b/src/test/regress/expected/horology.out @@ -0,0 +1,534 @@ +QUERY: CREATE TABLE TEMP_DATETIME (f1 datetime); +QUERY: INSERT INTO TEMP_DATETIME (f1) + SELECT d1 FROM DATETIME_TBL + WHERE d1 BETWEEN '13-jun-1957' AND '1-jan-1997' + OR d1 BETWEEN '1-jan-1999' AND '1-jan-2010'; +QUERY: SELECT '' AS ten, f1 AS datetime + FROM TEMP_DATETIME + ORDER BY datetime; +ten|datetime +---+------------------------------- + |epoch + |Wed Feb 28 17:32:01.00 1996 PST + |Thu Feb 29 17:32:01.00 1996 PST + |Fri Mar 01 17:32:01.00 1996 PST + |Mon Dec 30 17:32:01.00 1996 PST + |Tue Dec 31 17:32:01.00 1996 PST + |Fri Dec 31 17:32:01.00 1999 PST + |Sat Jan 01 17:32:01.00 2000 PST + |Sun Dec 31 17:32:01.00 2000 PST + |Mon Jan 01 17:32:01.00 2001 PST +(10 rows) + +QUERY: SELECT '' AS hundred, d.f1 AS datetime, t.f1 AS timespan, d.f1 + t.f1 AS plus + FROM TEMP_DATETIME d, TIMESPAN_TBL t + ORDER BY plus; +hundred|datetime |timespan |plus +-------+-------------------------------+-----------------------------+------------------------------- + |epoch |@ 14 secs ago |Wed Dec 31 15:59:46.00 1969 PST + |epoch |@ 1 min |Wed Dec 31 16:01:00.00 1969 PST + |epoch |@ 5 hours |Wed Dec 31 21:00:00.00 1969 PST + |epoch |@ 1 day 2 hours 3 mins 4 secs|Thu Jan 01 18:03:04.00 1970 PST + |epoch |@ 10 days |Sat Jan 10 16:00:00.00 1970 PST + |epoch |@ 3 mons |Tue Mar 31 16:00:00.00 1970 PST + |epoch |@ 5 mons |Sun May 31 17:00:00.00 1970 PDT + |epoch |@ 5 mons 12 hours |Mon Jun 01 05:00:00.00 1970 PDT + |epoch |@ 6 years |Wed Dec 31 16:00:00.00 1975 PST + |Wed Feb 28 17:32:01.00 1996 PST|@ 14 secs ago |Wed Feb 28 17:31:47.00 1996 PST + |Wed Feb 28 17:32:01.00 1996 PST|@ 1 min |Wed Feb 28 17:33:01.00 1996 PST + |Wed Feb 28 17:32:01.00 1996 PST|@ 5 hours |Wed Feb 28 22:32:01.00 1996 PST + |Thu Feb 29 17:32:01.00 1996 PST|@ 14 secs ago |Thu Feb 29 17:31:47.00 1996 PST + |Thu Feb 29 17:32:01.00 1996 PST|@ 1 min |Thu Feb 29 17:33:01.00 1996 PST + |Wed Feb 28 17:32:01.00 1996 PST|@ 1 day 2 hours 3 mins 4 secs|Thu Feb 29 19:35:05.00 1996 PST + |Thu Feb 29 17:32:01.00 1996 PST|@ 5 hours |Thu Feb 29 22:32:01.00 1996 PST + |Fri Mar 01 17:32:01.00 1996 PST|@ 14 secs ago |Fri Mar 01 17:31:47.00 1996 PST + |Fri Mar 01 17:32:01.00 1996 PST|@ 1 min |Fri Mar 01 17:33:01.00 1996 PST + |Thu Feb 29 17:32:01.00 1996 PST|@ 1 day 2 hours 3 mins 4 secs|Fri Mar 01 19:35:05.00 1996 PST + |Fri Mar 01 17:32:01.00 1996 PST|@ 5 hours |Fri Mar 01 22:32:01.00 1996 PST + |Fri Mar 01 17:32:01.00 1996 PST|@ 1 day 2 hours 3 mins 4 secs|Sat Mar 02 19:35:05.00 1996 PST + |Wed Feb 28 17:32:01.00 1996 PST|@ 10 days |Sat Mar 09 17:32:01.00 1996 PST + |Thu Feb 29 17:32:01.00 1996 PST|@ 10 days |Sun Mar 10 17:32:01.00 1996 PST + |Fri Mar 01 17:32:01.00 1996 PST|@ 10 days |Mon Mar 11 17:32:01.00 1996 PST + |Wed Feb 28 17:32:01.00 1996 PST|@ 3 mons |Tue May 28 18:32:01.00 1996 PDT + |Thu Feb 29 17:32:01.00 1996 PST|@ 3 mons |Fri May 31 18:32:01.00 1996 PDT + |Fri Mar 01 17:32:01.00 1996 PST|@ 3 mons |Sat Jun 01 18:32:01.00 1996 PDT + |Wed Feb 28 17:32:01.00 1996 PST|@ 5 mons |Sun Jul 28 18:32:01.00 1996 PDT + |Wed Feb 28 17:32:01.00 1996 PST|@ 5 mons 12 hours |Mon Jul 29 06:32:01.00 1996 PDT + |Thu Feb 29 17:32:01.00 1996 PST|@ 5 mons |Wed Jul 31 18:32:01.00 1996 PDT + |Thu Feb 29 17:32:01.00 1996 PST|@ 5 mons 12 hours |Thu Aug 01 06:32:01.00 1996 PDT + |Fri Mar 01 17:32:01.00 1996 PST|@ 5 mons |Thu Aug 01 18:32:01.00 1996 PDT + |Fri Mar 01 17:32:01.00 1996 PST|@ 5 mons 12 hours |Fri Aug 02 06:32:01.00 1996 PDT + |Mon Dec 30 17:32:01.00 1996 PST|@ 14 secs ago |Mon Dec 30 17:31:47.00 1996 PST + |Mon Dec 30 17:32:01.00 1996 PST|@ 1 min |Mon Dec 30 17:33:01.00 1996 PST + |Mon Dec 30 17:32:01.00 1996 PST|@ 5 hours |Mon Dec 30 22:32:01.00 1996 PST + |Tue Dec 31 17:32:01.00 1996 PST|@ 14 secs ago |Tue Dec 31 17:31:47.00 1996 PST + |Tue Dec 31 17:32:01.00 1996 PST|@ 1 min |Tue Dec 31 17:33:01.00 1996 PST + |Mon Dec 30 17:32:01.00 1996 PST|@ 1 day 2 hours 3 mins 4 secs|Tue Dec 31 19:35:05.00 1996 PST + |Tue Dec 31 17:32:01.00 1996 PST|@ 5 hours |Tue Dec 31 22:32:01.00 1996 PST + |Tue Dec 31 17:32:01.00 1996 PST|@ 1 day 2 hours 3 mins 4 secs|Wed Jan 01 19:35:05.00 1997 PST + |Mon Dec 30 17:32:01.00 1996 PST|@ 10 days |Thu Jan 09 17:32:01.00 1997 PST + |Tue Dec 31 17:32:01.00 1996 PST|@ 10 days |Fri Jan 10 17:32:01.00 1997 PST + |Mon Dec 30 17:32:01.00 1996 PST|@ 3 mons |Sun Mar 30 17:32:01.00 1997 PST + |Tue Dec 31 17:32:01.00 1996 PST|@ 3 mons |Mon Mar 31 17:32:01.00 1997 PST + |Mon Dec 30 17:32:01.00 1996 PST|@ 5 mons |Fri May 30 18:32:01.00 1997 PDT + |Mon Dec 30 17:32:01.00 1996 PST|@ 5 mons 12 hours |Sat May 31 06:32:01.00 1997 PDT + |Tue Dec 31 17:32:01.00 1996 PST|@ 5 mons |Sat May 31 18:32:01.00 1997 PDT + |Tue Dec 31 17:32:01.00 1996 PST|@ 5 mons 12 hours |Sun Jun 01 06:32:01.00 1997 PDT + |Fri Dec 31 17:32:01.00 1999 PST|@ 14 secs ago |Fri Dec 31 17:31:47.00 1999 PST + |Fri Dec 31 17:32:01.00 1999 PST|@ 1 min |Fri Dec 31 17:33:01.00 1999 PST + |Fri Dec 31 17:32:01.00 1999 PST|@ 5 hours |Fri Dec 31 22:32:01.00 1999 PST + |Sat Jan 01 17:32:01.00 2000 PST|@ 14 secs ago |Sat Jan 01 17:31:47.00 2000 PST + |Sat Jan 01 17:32:01.00 2000 PST|@ 1 min |Sat Jan 01 17:33:01.00 2000 PST + |Fri Dec 31 17:32:01.00 1999 PST|@ 1 day 2 hours 3 mins 4 secs|Sat Jan 01 19:35:05.00 2000 PST + |Sat Jan 01 17:32:01.00 2000 PST|@ 5 hours |Sat Jan 01 22:32:01.00 2000 PST + |Sat Jan 01 17:32:01.00 2000 PST|@ 1 day 2 hours 3 mins 4 secs|Sun Jan 02 19:35:05.00 2000 PST + |Fri Dec 31 17:32:01.00 1999 PST|@ 10 days |Mon Jan 10 17:32:01.00 2000 PST + |Sat Jan 01 17:32:01.00 2000 PST|@ 10 days |Tue Jan 11 17:32:01.00 2000 PST + |Fri Dec 31 17:32:01.00 1999 PST|@ 3 mons |Fri Mar 31 17:32:01.00 2000 PST + |Sat Jan 01 17:32:01.00 2000 PST|@ 3 mons |Sat Apr 01 17:32:01.00 2000 PST + |Fri Dec 31 17:32:01.00 1999 PST|@ 5 mons |Wed May 31 18:32:01.00 2000 PDT + |Fri Dec 31 17:32:01.00 1999 PST|@ 5 mons 12 hours |Thu Jun 01 06:32:01.00 2000 PDT + |Sat Jan 01 17:32:01.00 2000 PST|@ 5 mons |Thu Jun 01 18:32:01.00 2000 PDT + |Sat Jan 01 17:32:01.00 2000 PST|@ 5 mons 12 hours |Fri Jun 02 06:32:01.00 2000 PDT + |Sun Dec 31 17:32:01.00 2000 PST|@ 14 secs ago |Sun Dec 31 17:31:47.00 2000 PST + |Sun Dec 31 17:32:01.00 2000 PST|@ 1 min |Sun Dec 31 17:33:01.00 2000 PST + |Sun Dec 31 17:32:01.00 2000 PST|@ 5 hours |Sun Dec 31 22:32:01.00 2000 PST + |Mon Jan 01 17:32:01.00 2001 PST|@ 14 secs ago |Mon Jan 01 17:31:47.00 2001 PST + |Mon Jan 01 17:32:01.00 2001 PST|@ 1 min |Mon Jan 01 17:33:01.00 2001 PST + |Sun Dec 31 17:32:01.00 2000 PST|@ 1 day 2 hours 3 mins 4 secs|Mon Jan 01 19:35:05.00 2001 PST + |Mon Jan 01 17:32:01.00 2001 PST|@ 5 hours |Mon Jan 01 22:32:01.00 2001 PST + |Mon Jan 01 17:32:01.00 2001 PST|@ 1 day 2 hours 3 mins 4 secs|Tue Jan 02 19:35:05.00 2001 PST + |Sun Dec 31 17:32:01.00 2000 PST|@ 10 days |Wed Jan 10 17:32:01.00 2001 PST + |Mon Jan 01 17:32:01.00 2001 PST|@ 10 days |Thu Jan 11 17:32:01.00 2001 PST + |Sun Dec 31 17:32:01.00 2000 PST|@ 3 mons |Sat Mar 31 17:32:01.00 2001 PST + |Mon Jan 01 17:32:01.00 2001 PST|@ 3 mons |Sun Apr 01 18:32:01.00 2001 PDT + |Sun Dec 31 17:32:01.00 2000 PST|@ 5 mons |Thu May 31 18:32:01.00 2001 PDT + |Sun Dec 31 17:32:01.00 2000 PST|@ 5 mons 12 hours |Fri Jun 01 06:32:01.00 2001 PDT + |Mon Jan 01 17:32:01.00 2001 PST|@ 5 mons |Fri Jun 01 18:32:01.00 2001 PDT + |Mon Jan 01 17:32:01.00 2001 PST|@ 5 mons 12 hours |Sat Jun 02 06:32:01.00 2001 PDT + |Wed Feb 28 17:32:01.00 1996 PST|@ 6 years |Wed Feb 27 17:32:01.00 2002 PST + |Thu Feb 29 17:32:01.00 1996 PST|@ 6 years |Thu Feb 28 17:32:01.00 2002 PST + |Fri Mar 01 17:32:01.00 1996 PST|@ 6 years |Fri Mar 01 17:32:01.00 2002 PST + |Mon Dec 30 17:32:01.00 1996 PST|@ 6 years |Mon Dec 30 17:32:01.00 2002 PST + |Tue Dec 31 17:32:01.00 1996 PST|@ 6 years |Tue Dec 31 17:32:01.00 2002 PST + |epoch |@ 34 years |Wed Dec 31 16:00:00.00 2003 PST + |Fri Dec 31 17:32:01.00 1999 PST|@ 6 years |Sat Dec 31 17:32:01.00 2005 PST + |Sat Jan 01 17:32:01.00 2000 PST|@ 6 years |Sun Jan 01 17:32:01.00 2006 PST + |Sun Dec 31 17:32:01.00 2000 PST|@ 6 years |Sun Dec 31 17:32:01.00 2006 PST + |Mon Jan 01 17:32:01.00 2001 PST|@ 6 years |Mon Jan 01 17:32:01.00 2007 PST + |Wed Feb 28 17:32:01.00 1996 PST|@ 34 years |Wed Feb 27 17:32:01.00 2030 PST + |Thu Feb 29 17:32:01.00 1996 PST|@ 34 years |Thu Feb 28 17:32:01.00 2030 PST + |Fri Mar 01 17:32:01.00 1996 PST|@ 34 years |Fri Mar 01 17:32:01.00 2030 PST + |Mon Dec 30 17:32:01.00 1996 PST|@ 34 years |Mon Dec 30 17:32:01.00 2030 PST + |Tue Dec 31 17:32:01.00 1996 PST|@ 34 years |Tue Dec 31 17:32:01.00 2030 PST + |Fri Dec 31 17:32:01.00 1999 PST|@ 34 years |Sat Dec 31 17:32:01.00 2033 PST + |Sat Jan 01 17:32:01.00 2000 PST|@ 34 years |Sun Jan 01 17:32:01.00 2034 PST + |Sun Dec 31 17:32:01.00 2000 PST|@ 34 years |Sun Dec 31 17:32:01.00 2034 PST + |Mon Jan 01 17:32:01.00 2001 PST|@ 34 years |Mon Jan 01 17:32:01.00 2035 PST +(100 rows) + +QUERY: SELECT '' AS hundred, d.f1 AS datetime, t.f1 AS timespan, d.f1 - t.f1 AS minus + FROM TEMP_DATETIME d, TIMESPAN_TBL t + WHERE isfinite(d.f1) + ORDER BY minus; +hundred|datetime |timespan |minus +-------+-------------------------------+-----------------------------+------------------------------- + |epoch |@ 34 years |Tue Dec 31 16:00:00.00 1935 PST + |Wed Feb 28 17:32:01.00 1996 PST|@ 34 years |Tue Feb 27 17:32:01.00 1962 PST + |Thu Feb 29 17:32:01.00 1996 PST|@ 34 years |Wed Feb 28 17:32:01.00 1962 PST + |Fri Mar 01 17:32:01.00 1996 PST|@ 34 years |Thu Mar 01 17:32:01.00 1962 PST + |Mon Dec 30 17:32:01.00 1996 PST|@ 34 years |Sun Dec 30 17:32:01.00 1962 PST + |Tue Dec 31 17:32:01.00 1996 PST|@ 34 years |Mon Dec 31 17:32:01.00 1962 PST + |epoch |@ 6 years |Tue Dec 31 16:00:00.00 1963 PST + |Fri Dec 31 17:32:01.00 1999 PST|@ 34 years |Fri Dec 31 17:32:01.00 1965 PST + |Sat Jan 01 17:32:01.00 2000 PST|@ 34 years |Sat Jan 01 17:32:01.00 1966 PST + |Sun Dec 31 17:32:01.00 2000 PST|@ 34 years |Sat Dec 31 17:32:01.00 1966 PST + |Mon Jan 01 17:32:01.00 2001 PST|@ 34 years |Sun Jan 01 17:32:01.00 1967 PST + |epoch |@ 5 mons 12 hours |Thu Jul 31 04:00:00.00 1969 PST + |epoch |@ 5 mons |Thu Jul 31 16:00:00.00 1969 PST + |epoch |@ 3 mons |Tue Sep 30 16:00:00.00 1969 PST + |epoch |@ 10 days |Sun Dec 21 16:00:00.00 1969 PST + |epoch |@ 1 day 2 hours 3 mins 4 secs|Tue Dec 30 13:56:56.00 1969 PST + |epoch |@ 5 hours |Wed Dec 31 11:00:00.00 1969 PST + |epoch |@ 1 min |Wed Dec 31 15:59:00.00 1969 PST + |epoch |@ 14 secs ago |Wed Dec 31 16:00:14.00 1969 PST + |Wed Feb 28 17:32:01.00 1996 PST|@ 6 years |Tue Feb 27 17:32:01.00 1990 PST + |Thu Feb 29 17:32:01.00 1996 PST|@ 6 years |Wed Feb 28 17:32:01.00 1990 PST + |Fri Mar 01 17:32:01.00 1996 PST|@ 6 years |Thu Mar 01 17:32:01.00 1990 PST + |Mon Dec 30 17:32:01.00 1996 PST|@ 6 years |Sun Dec 30 17:32:01.00 1990 PST + |Tue Dec 31 17:32:01.00 1996 PST|@ 6 years |Mon Dec 31 17:32:01.00 1990 PST + |Fri Dec 31 17:32:01.00 1999 PST|@ 6 years |Fri Dec 31 17:32:01.00 1993 PST + |Sat Jan 01 17:32:01.00 2000 PST|@ 6 years |Sat Jan 01 17:32:01.00 1994 PST + |Sun Dec 31 17:32:01.00 2000 PST|@ 6 years |Sat Dec 31 17:32:01.00 1994 PST + |Mon Jan 01 17:32:01.00 2001 PST|@ 6 years |Sun Jan 01 17:32:01.00 1995 PST + |Wed Feb 28 17:32:01.00 1996 PST|@ 5 mons 12 hours |Thu Sep 28 06:32:01.00 1995 PDT + |Wed Feb 28 17:32:01.00 1996 PST|@ 5 mons |Thu Sep 28 18:32:01.00 1995 PDT + |Thu Feb 29 17:32:01.00 1996 PST|@ 5 mons 12 hours |Sat Sep 30 06:32:01.00 1995 PDT + |Thu Feb 29 17:32:01.00 1996 PST|@ 5 mons |Sat Sep 30 18:32:01.00 1995 PDT + |Fri Mar 01 17:32:01.00 1996 PST|@ 5 mons 12 hours |Sun Oct 01 06:32:01.00 1995 PDT + |Fri Mar 01 17:32:01.00 1996 PST|@ 5 mons |Sun Oct 01 18:32:01.00 1995 PDT + |Wed Feb 28 17:32:01.00 1996 PST|@ 3 mons |Tue Nov 28 17:32:01.00 1995 PST + |Thu Feb 29 17:32:01.00 1996 PST|@ 3 mons |Thu Nov 30 17:32:01.00 1995 PST + |Fri Mar 01 17:32:01.00 1996 PST|@ 3 mons |Fri Dec 01 17:32:01.00 1995 PST + |Wed Feb 28 17:32:01.00 1996 PST|@ 10 days |Sun Feb 18 17:32:01.00 1996 PST + |Thu Feb 29 17:32:01.00 1996 PST|@ 10 days |Mon Feb 19 17:32:01.00 1996 PST + |Fri Mar 01 17:32:01.00 1996 PST|@ 10 days |Tue Feb 20 17:32:01.00 1996 PST + |Wed Feb 28 17:32:01.00 1996 PST|@ 1 day 2 hours 3 mins 4 secs|Tue Feb 27 15:28:57.00 1996 PST + |Wed Feb 28 17:32:01.00 1996 PST|@ 5 hours |Wed Feb 28 12:32:01.00 1996 PST + |Thu Feb 29 17:32:01.00 1996 PST|@ 1 day 2 hours 3 mins 4 secs|Wed Feb 28 15:28:57.00 1996 PST + |Wed Feb 28 17:32:01.00 1996 PST|@ 1 min |Wed Feb 28 17:31:01.00 1996 PST + |Wed Feb 28 17:32:01.00 1996 PST|@ 14 secs ago |Wed Feb 28 17:32:15.00 1996 PST + |Thu Feb 29 17:32:01.00 1996 PST|@ 5 hours |Thu Feb 29 12:32:01.00 1996 PST + |Fri Mar 01 17:32:01.00 1996 PST|@ 1 day 2 hours 3 mins 4 secs|Thu Feb 29 15:28:57.00 1996 PST + |Thu Feb 29 17:32:01.00 1996 PST|@ 1 min |Thu Feb 29 17:31:01.00 1996 PST + |Thu Feb 29 17:32:01.00 1996 PST|@ 14 secs ago |Thu Feb 29 17:32:15.00 1996 PST + |Fri Mar 01 17:32:01.00 1996 PST|@ 5 hours |Fri Mar 01 12:32:01.00 1996 PST + |Fri Mar 01 17:32:01.00 1996 PST|@ 1 min |Fri Mar 01 17:31:01.00 1996 PST + |Fri Mar 01 17:32:01.00 1996 PST|@ 14 secs ago |Fri Mar 01 17:32:15.00 1996 PST + |Mon Dec 30 17:32:01.00 1996 PST|@ 5 mons 12 hours |Tue Jul 30 06:32:01.00 1996 PDT + |Mon Dec 30 17:32:01.00 1996 PST|@ 5 mons |Tue Jul 30 18:32:01.00 1996 PDT + |Tue Dec 31 17:32:01.00 1996 PST|@ 5 mons 12 hours |Wed Jul 31 06:32:01.00 1996 PDT + |Tue Dec 31 17:32:01.00 1996 PST|@ 5 mons |Wed Jul 31 18:32:01.00 1996 PDT + |Mon Dec 30 17:32:01.00 1996 PST|@ 3 mons |Sun Sep 29 18:32:01.00 1996 PDT + |Tue Dec 31 17:32:01.00 1996 PST|@ 3 mons |Mon Sep 30 18:32:01.00 1996 PDT + |Mon Dec 30 17:32:01.00 1996 PST|@ 10 days |Fri Dec 20 17:32:01.00 1996 PST + |Tue Dec 31 17:32:01.00 1996 PST|@ 10 days |Sat Dec 21 17:32:01.00 1996 PST + |Mon Dec 30 17:32:01.00 1996 PST|@ 1 day 2 hours 3 mins 4 secs|Sun Dec 29 15:28:57.00 1996 PST + |Mon Dec 30 17:32:01.00 1996 PST|@ 5 hours |Mon Dec 30 12:32:01.00 1996 PST + |Tue Dec 31 17:32:01.00 1996 PST|@ 1 day 2 hours 3 mins 4 secs|Mon Dec 30 15:28:57.00 1996 PST + |Mon Dec 30 17:32:01.00 1996 PST|@ 1 min |Mon Dec 30 17:31:01.00 1996 PST + |Mon Dec 30 17:32:01.00 1996 PST|@ 14 secs ago |Mon Dec 30 17:32:15.00 1996 PST + |Tue Dec 31 17:32:01.00 1996 PST|@ 5 hours |Tue Dec 31 12:32:01.00 1996 PST + |Tue Dec 31 17:32:01.00 1996 PST|@ 1 min |Tue Dec 31 17:31:01.00 1996 PST + |Tue Dec 31 17:32:01.00 1996 PST|@ 14 secs ago |Tue Dec 31 17:32:15.00 1996 PST + |Fri Dec 31 17:32:01.00 1999 PST|@ 5 mons 12 hours |Sat Jul 31 06:32:01.00 1999 PDT + |Fri Dec 31 17:32:01.00 1999 PST|@ 5 mons |Sat Jul 31 18:32:01.00 1999 PDT + |Sat Jan 01 17:32:01.00 2000 PST|@ 5 mons 12 hours |Sun Aug 01 06:32:01.00 1999 PDT + |Sat Jan 01 17:32:01.00 2000 PST|@ 5 mons |Sun Aug 01 18:32:01.00 1999 PDT + |Fri Dec 31 17:32:01.00 1999 PST|@ 3 mons |Thu Sep 30 18:32:01.00 1999 PDT + |Sat Jan 01 17:32:01.00 2000 PST|@ 3 mons |Fri Oct 01 18:32:01.00 1999 PDT + |Fri Dec 31 17:32:01.00 1999 PST|@ 10 days |Tue Dec 21 17:32:01.00 1999 PST + |Sat Jan 01 17:32:01.00 2000 PST|@ 10 days |Wed Dec 22 17:32:01.00 1999 PST + |Fri Dec 31 17:32:01.00 1999 PST|@ 1 day 2 hours 3 mins 4 secs|Thu Dec 30 15:28:57.00 1999 PST + |Fri Dec 31 17:32:01.00 1999 PST|@ 5 hours |Fri Dec 31 12:32:01.00 1999 PST + |Sat Jan 01 17:32:01.00 2000 PST|@ 1 day 2 hours 3 mins 4 secs|Fri Dec 31 15:28:57.00 1999 PST + |Fri Dec 31 17:32:01.00 1999 PST|@ 1 min |Fri Dec 31 17:31:01.00 1999 PST + |Fri Dec 31 17:32:01.00 1999 PST|@ 14 secs ago |Fri Dec 31 17:32:15.00 1999 PST + |Sat Jan 01 17:32:01.00 2000 PST|@ 5 hours |Sat Jan 01 12:32:01.00 2000 PST + |Sat Jan 01 17:32:01.00 2000 PST|@ 1 min |Sat Jan 01 17:31:01.00 2000 PST + |Sat Jan 01 17:32:01.00 2000 PST|@ 14 secs ago |Sat Jan 01 17:32:15.00 2000 PST + |Sun Dec 31 17:32:01.00 2000 PST|@ 5 mons 12 hours |Mon Jul 31 06:32:01.00 2000 PDT + |Sun Dec 31 17:32:01.00 2000 PST|@ 5 mons |Mon Jul 31 18:32:01.00 2000 PDT + |Mon Jan 01 17:32:01.00 2001 PST|@ 5 mons 12 hours |Tue Aug 01 06:32:01.00 2000 PDT + |Mon Jan 01 17:32:01.00 2001 PST|@ 5 mons |Tue Aug 01 18:32:01.00 2000 PDT + |Sun Dec 31 17:32:01.00 2000 PST|@ 3 mons |Sat Sep 30 18:32:01.00 2000 PDT + |Mon Jan 01 17:32:01.00 2001 PST|@ 3 mons |Sun Oct 01 18:32:01.00 2000 PDT + |Sun Dec 31 17:32:01.00 2000 PST|@ 10 days |Thu Dec 21 17:32:01.00 2000 PST + |Mon Jan 01 17:32:01.00 2001 PST|@ 10 days |Fri Dec 22 17:32:01.00 2000 PST + |Sun Dec 31 17:32:01.00 2000 PST|@ 1 day 2 hours 3 mins 4 secs|Sat Dec 30 15:28:57.00 2000 PST + |Sun Dec 31 17:32:01.00 2000 PST|@ 5 hours |Sun Dec 31 12:32:01.00 2000 PST + |Mon Jan 01 17:32:01.00 2001 PST|@ 1 day 2 hours 3 mins 4 secs|Sun Dec 31 15:28:57.00 2000 PST + |Sun Dec 31 17:32:01.00 2000 PST|@ 1 min |Sun Dec 31 17:31:01.00 2000 PST + |Sun Dec 31 17:32:01.00 2000 PST|@ 14 secs ago |Sun Dec 31 17:32:15.00 2000 PST + |Mon Jan 01 17:32:01.00 2001 PST|@ 5 hours |Mon Jan 01 12:32:01.00 2001 PST + |Mon Jan 01 17:32:01.00 2001 PST|@ 1 min |Mon Jan 01 17:31:01.00 2001 PST + |Mon Jan 01 17:32:01.00 2001 PST|@ 14 secs ago |Mon Jan 01 17:32:15.00 2001 PST +(100 rows) + +QUERY: SELECT '' AS ten, d.f1 AS datetime, '1980-01-06 00:00 GMT'::datetime AS gpstime_zero, + d.f1 - '1980-01-06 00:00 GMT'::datetime AS difference + FROM TEMP_DATETIME d + ORDER BY difference; +ten|datetime |gpstime_zero |difference +---+-------------------------------+-------------------------------+-------------------------------- + |epoch |Sat Jan 05 16:00:00.00 1980 PST|@ 3657 days ago + |Wed Feb 28 17:32:01.00 1996 PST|Sat Jan 05 16:00:00.00 1980 PST|@ 5898 days 1 hour 32 mins 1 sec + |Thu Feb 29 17:32:01.00 1996 PST|Sat Jan 05 16:00:00.00 1980 PST|@ 5899 days 1 hour 32 mins 1 sec + |Fri Mar 01 17:32:01.00 1996 PST|Sat Jan 05 16:00:00.00 1980 PST|@ 5900 days 1 hour 32 mins 1 sec + |Mon Dec 30 17:32:01.00 1996 PST|Sat Jan 05 16:00:00.00 1980 PST|@ 6204 days 1 hour 32 mins 1 sec + |Tue Dec 31 17:32:01.00 1996 PST|Sat Jan 05 16:00:00.00 1980 PST|@ 6205 days 1 hour 32 mins 1 sec + |Fri Dec 31 17:32:01.00 1999 PST|Sat Jan 05 16:00:00.00 1980 PST|@ 7300 days 1 hour 32 mins 1 sec + |Sat Jan 01 17:32:01.00 2000 PST|Sat Jan 05 16:00:00.00 1980 PST|@ 7301 days 1 hour 32 mins 1 sec + |Sun Dec 31 17:32:01.00 2000 PST|Sat Jan 05 16:00:00.00 1980 PST|@ 7666 days 1 hour 32 mins 1 sec + |Mon Jan 01 17:32:01.00 2001 PST|Sat Jan 05 16:00:00.00 1980 PST|@ 7667 days 1 hour 32 mins 1 sec +(10 rows) + +QUERY: SELECT '' AS hundred, d1.f1 AS datetime1, d2.f1 AS datetime2, d1.f1 - d2.f1 AS difference + FROM TEMP_DATETIME d1, TEMP_DATETIME d2 + ORDER BY datetime1, datetime2, difference; +hundred|datetime1 |datetime2 |difference +-------+-------------------------------+-------------------------------+------------------------------------- + |epoch |epoch |@ 0 + |epoch |Wed Feb 28 17:32:01.00 1996 PST|@ 9555 days 1 hour 32 mins 1 sec ago + |epoch |Thu Feb 29 17:32:01.00 1996 PST|@ 9556 days 1 hour 32 mins 1 sec ago + |epoch |Fri Mar 01 17:32:01.00 1996 PST|@ 9557 days 1 hour 32 mins 1 sec ago + |epoch |Mon Dec 30 17:32:01.00 1996 PST|@ 9861 days 1 hour 32 mins 1 sec ago + |epoch |Tue Dec 31 17:32:01.00 1996 PST|@ 9862 days 1 hour 32 mins 1 sec ago + |epoch |Fri Dec 31 17:32:01.00 1999 PST|@ 10957 days 1 hour 32 mins 1 sec ago + |epoch |Sat Jan 01 17:32:01.00 2000 PST|@ 10958 days 1 hour 32 mins 1 sec ago + |epoch |Sun Dec 31 17:32:01.00 2000 PST|@ 11323 days 1 hour 32 mins 1 sec ago + |epoch |Mon Jan 01 17:32:01.00 2001 PST|@ 11324 days 1 hour 32 mins 1 sec ago + |Wed Feb 28 17:32:01.00 1996 PST|epoch |@ 9555 days 1 hour 32 mins 1 sec + |Wed Feb 28 17:32:01.00 1996 PST|Wed Feb 28 17:32:01.00 1996 PST|@ 0 + |Wed Feb 28 17:32:01.00 1996 PST|Thu Feb 29 17:32:01.00 1996 PST|@ 1 day ago + |Wed Feb 28 17:32:01.00 1996 PST|Fri Mar 01 17:32:01.00 1996 PST|@ 2 days ago + |Wed Feb 28 17:32:01.00 1996 PST|Mon Dec 30 17:32:01.00 1996 PST|@ 306 days ago + |Wed Feb 28 17:32:01.00 1996 PST|Tue Dec 31 17:32:01.00 1996 PST|@ 307 days ago + |Wed Feb 28 17:32:01.00 1996 PST|Fri Dec 31 17:32:01.00 1999 PST|@ 1402 days ago + |Wed Feb 28 17:32:01.00 1996 PST|Sat Jan 01 17:32:01.00 2000 PST|@ 1403 days ago + |Wed Feb 28 17:32:01.00 1996 PST|Sun Dec 31 17:32:01.00 2000 PST|@ 1768 days ago + |Wed Feb 28 17:32:01.00 1996 PST|Mon Jan 01 17:32:01.00 2001 PST|@ 1769 days ago + |Thu Feb 29 17:32:01.00 1996 PST|epoch |@ 9556 days 1 hour 32 mins 1 sec + |Thu Feb 29 17:32:01.00 1996 PST|Wed Feb 28 17:32:01.00 1996 PST|@ 1 day + |Thu Feb 29 17:32:01.00 1996 PST|Thu Feb 29 17:32:01.00 1996 PST|@ 0 + |Thu Feb 29 17:32:01.00 1996 PST|Fri Mar 01 17:32:01.00 1996 PST|@ 1 day ago + |Thu Feb 29 17:32:01.00 1996 PST|Mon Dec 30 17:32:01.00 1996 PST|@ 305 days ago + |Thu Feb 29 17:32:01.00 1996 PST|Tue Dec 31 17:32:01.00 1996 PST|@ 306 days ago + |Thu Feb 29 17:32:01.00 1996 PST|Fri Dec 31 17:32:01.00 1999 PST|@ 1401 days ago + |Thu Feb 29 17:32:01.00 1996 PST|Sat Jan 01 17:32:01.00 2000 PST|@ 1402 days ago + |Thu Feb 29 17:32:01.00 1996 PST|Sun Dec 31 17:32:01.00 2000 PST|@ 1767 days ago + |Thu Feb 29 17:32:01.00 1996 PST|Mon Jan 01 17:32:01.00 2001 PST|@ 1768 days ago + |Fri Mar 01 17:32:01.00 1996 PST|epoch |@ 9557 days 1 hour 32 mins 1 sec + |Fri Mar 01 17:32:01.00 1996 PST|Wed Feb 28 17:32:01.00 1996 PST|@ 2 days + |Fri Mar 01 17:32:01.00 1996 PST|Thu Feb 29 17:32:01.00 1996 PST|@ 1 day + |Fri Mar 01 17:32:01.00 1996 PST|Fri Mar 01 17:32:01.00 1996 PST|@ 0 + |Fri Mar 01 17:32:01.00 1996 PST|Mon Dec 30 17:32:01.00 1996 PST|@ 304 days ago + |Fri Mar 01 17:32:01.00 1996 PST|Tue Dec 31 17:32:01.00 1996 PST|@ 305 days ago + |Fri Mar 01 17:32:01.00 1996 PST|Fri Dec 31 17:32:01.00 1999 PST|@ 1400 days ago + |Fri Mar 01 17:32:01.00 1996 PST|Sat Jan 01 17:32:01.00 2000 PST|@ 1401 days ago + |Fri Mar 01 17:32:01.00 1996 PST|Sun Dec 31 17:32:01.00 2000 PST|@ 1766 days ago + |Fri Mar 01 17:32:01.00 1996 PST|Mon Jan 01 17:32:01.00 2001 PST|@ 1767 days ago + |Mon Dec 30 17:32:01.00 1996 PST|epoch |@ 9861 days 1 hour 32 mins 1 sec + |Mon Dec 30 17:32:01.00 1996 PST|Wed Feb 28 17:32:01.00 1996 PST|@ 306 days + |Mon Dec 30 17:32:01.00 1996 PST|Thu Feb 29 17:32:01.00 1996 PST|@ 305 days + |Mon Dec 30 17:32:01.00 1996 PST|Fri Mar 01 17:32:01.00 1996 PST|@ 304 days + |Mon Dec 30 17:32:01.00 1996 PST|Mon Dec 30 17:32:01.00 1996 PST|@ 0 + |Mon Dec 30 17:32:01.00 1996 PST|Tue Dec 31 17:32:01.00 1996 PST|@ 1 day ago + |Mon Dec 30 17:32:01.00 1996 PST|Fri Dec 31 17:32:01.00 1999 PST|@ 1096 days ago + |Mon Dec 30 17:32:01.00 1996 PST|Sat Jan 01 17:32:01.00 2000 PST|@ 1097 days ago + |Mon Dec 30 17:32:01.00 1996 PST|Sun Dec 31 17:32:01.00 2000 PST|@ 1462 days ago + |Mon Dec 30 17:32:01.00 1996 PST|Mon Jan 01 17:32:01.00 2001 PST|@ 1463 days ago + |Tue Dec 31 17:32:01.00 1996 PST|epoch |@ 9862 days 1 hour 32 mins 1 sec + |Tue Dec 31 17:32:01.00 1996 PST|Wed Feb 28 17:32:01.00 1996 PST|@ 307 days + |Tue Dec 31 17:32:01.00 1996 PST|Thu Feb 29 17:32:01.00 1996 PST|@ 306 days + |Tue Dec 31 17:32:01.00 1996 PST|Fri Mar 01 17:32:01.00 1996 PST|@ 305 days + |Tue Dec 31 17:32:01.00 1996 PST|Mon Dec 30 17:32:01.00 1996 PST|@ 1 day + |Tue Dec 31 17:32:01.00 1996 PST|Tue Dec 31 17:32:01.00 1996 PST|@ 0 + |Tue Dec 31 17:32:01.00 1996 PST|Fri Dec 31 17:32:01.00 1999 PST|@ 1095 days ago + |Tue Dec 31 17:32:01.00 1996 PST|Sat Jan 01 17:32:01.00 2000 PST|@ 1096 days ago + |Tue Dec 31 17:32:01.00 1996 PST|Sun Dec 31 17:32:01.00 2000 PST|@ 1461 days ago + |Tue Dec 31 17:32:01.00 1996 PST|Mon Jan 01 17:32:01.00 2001 PST|@ 1462 days ago + |Fri Dec 31 17:32:01.00 1999 PST|epoch |@ 10957 days 1 hour 32 mins 1 sec + |Fri Dec 31 17:32:01.00 1999 PST|Wed Feb 28 17:32:01.00 1996 PST|@ 1402 days + |Fri Dec 31 17:32:01.00 1999 PST|Thu Feb 29 17:32:01.00 1996 PST|@ 1401 days + |Fri Dec 31 17:32:01.00 1999 PST|Fri Mar 01 17:32:01.00 1996 PST|@ 1400 days + |Fri Dec 31 17:32:01.00 1999 PST|Mon Dec 30 17:32:01.00 1996 PST|@ 1096 days + |Fri Dec 31 17:32:01.00 1999 PST|Tue Dec 31 17:32:01.00 1996 PST|@ 1095 days + |Fri Dec 31 17:32:01.00 1999 PST|Fri Dec 31 17:32:01.00 1999 PST|@ 0 + |Fri Dec 31 17:32:01.00 1999 PST|Sat Jan 01 17:32:01.00 2000 PST|@ 1 day ago + |Fri Dec 31 17:32:01.00 1999 PST|Sun Dec 31 17:32:01.00 2000 PST|@ 366 days ago + |Fri Dec 31 17:32:01.00 1999 PST|Mon Jan 01 17:32:01.00 2001 PST|@ 367 days ago + |Sat Jan 01 17:32:01.00 2000 PST|epoch |@ 10958 days 1 hour 32 mins 1 sec + |Sat Jan 01 17:32:01.00 2000 PST|Wed Feb 28 17:32:01.00 1996 PST|@ 1403 days + |Sat Jan 01 17:32:01.00 2000 PST|Thu Feb 29 17:32:01.00 1996 PST|@ 1402 days + |Sat Jan 01 17:32:01.00 2000 PST|Fri Mar 01 17:32:01.00 1996 PST|@ 1401 days + |Sat Jan 01 17:32:01.00 2000 PST|Mon Dec 30 17:32:01.00 1996 PST|@ 1097 days + |Sat Jan 01 17:32:01.00 2000 PST|Tue Dec 31 17:32:01.00 1996 PST|@ 1096 days + |Sat Jan 01 17:32:01.00 2000 PST|Fri Dec 31 17:32:01.00 1999 PST|@ 1 day + |Sat Jan 01 17:32:01.00 2000 PST|Sat Jan 01 17:32:01.00 2000 PST|@ 0 + |Sat Jan 01 17:32:01.00 2000 PST|Sun Dec 31 17:32:01.00 2000 PST|@ 365 days ago + |Sat Jan 01 17:32:01.00 2000 PST|Mon Jan 01 17:32:01.00 2001 PST|@ 366 days ago + |Sun Dec 31 17:32:01.00 2000 PST|epoch |@ 11323 days 1 hour 32 mins 1 sec + |Sun Dec 31 17:32:01.00 2000 PST|Wed Feb 28 17:32:01.00 1996 PST|@ 1768 days + |Sun Dec 31 17:32:01.00 2000 PST|Thu Feb 29 17:32:01.00 1996 PST|@ 1767 days + |Sun Dec 31 17:32:01.00 2000 PST|Fri Mar 01 17:32:01.00 1996 PST|@ 1766 days + |Sun Dec 31 17:32:01.00 2000 PST|Mon Dec 30 17:32:01.00 1996 PST|@ 1462 days + |Sun Dec 31 17:32:01.00 2000 PST|Tue Dec 31 17:32:01.00 1996 PST|@ 1461 days + |Sun Dec 31 17:32:01.00 2000 PST|Fri Dec 31 17:32:01.00 1999 PST|@ 366 days + |Sun Dec 31 17:32:01.00 2000 PST|Sat Jan 01 17:32:01.00 2000 PST|@ 365 days + |Sun Dec 31 17:32:01.00 2000 PST|Sun Dec 31 17:32:01.00 2000 PST|@ 0 + |Sun Dec 31 17:32:01.00 2000 PST|Mon Jan 01 17:32:01.00 2001 PST|@ 1 day ago + |Mon Jan 01 17:32:01.00 2001 PST|epoch |@ 11324 days 1 hour 32 mins 1 sec + |Mon Jan 01 17:32:01.00 2001 PST|Wed Feb 28 17:32:01.00 1996 PST|@ 1769 days + |Mon Jan 01 17:32:01.00 2001 PST|Thu Feb 29 17:32:01.00 1996 PST|@ 1768 days + |Mon Jan 01 17:32:01.00 2001 PST|Fri Mar 01 17:32:01.00 1996 PST|@ 1767 days + |Mon Jan 01 17:32:01.00 2001 PST|Mon Dec 30 17:32:01.00 1996 PST|@ 1463 days + |Mon Jan 01 17:32:01.00 2001 PST|Tue Dec 31 17:32:01.00 1996 PST|@ 1462 days + |Mon Jan 01 17:32:01.00 2001 PST|Fri Dec 31 17:32:01.00 1999 PST|@ 367 days + |Mon Jan 01 17:32:01.00 2001 PST|Sat Jan 01 17:32:01.00 2000 PST|@ 366 days + |Mon Jan 01 17:32:01.00 2001 PST|Sun Dec 31 17:32:01.00 2000 PST|@ 1 day + |Mon Jan 01 17:32:01.00 2001 PST|Mon Jan 01 17:32:01.00 2001 PST|@ 0 +(100 rows) + +QUERY: SELECT '' as fifty, d1 as datetime, + date_part('year', d1) AS year, date_part('month', d1) AS month, + date_part('day',d1) AS day, date_part('hour', d1) AS hour, + date_part('minute', d1) AS minute, date_part('second', d1) AS second + FROM DATETIME_TBL + WHERE isfinite(d1) and d1 >= '1-jan-1900 GMT' + ORDER BY datetime; +fifty|datetime |year|month|day|hour|minute| second +-----+-------------------------------+----+-----+---+----+------+-------- + |epoch |1969| 12| 31| 16| 0| 0 + |Wed Feb 28 17:32:01.00 1996 PST|1996| 2| 28| 17| 32| 1 + |Thu Feb 29 17:32:01.00 1996 PST|1996| 2| 29| 17| 32| 1 + |Fri Mar 01 17:32:01.00 1996 PST|1996| 3| 1| 17| 32| 1 + |Mon Dec 30 17:32:01.00 1996 PST|1996| 12| 30| 17| 32| 1 + |Tue Dec 31 17:32:01.00 1996 PST|1996| 12| 31| 17| 32| 1 + |Wed Jan 01 17:32:01.00 1997 PST|1997| 1| 1| 17| 32| 1 + |Thu Jan 02 00:00:00.00 1997 PST|1997| 1| 2| 0| 0| 0 + |Thu Jan 02 03:04:05.00 1997 PST|1997| 1| 2| 3| 4| 5 + |Mon Feb 10 09:32:01.00 1997 PST|1997| 2| 10| 9| 32| 1 + |Mon Feb 10 09:32:01.00 1997 PST|1997| 2| 10| 9| 32| 1 + |Mon Feb 10 09:32:01.00 1997 PST|1997| 2| 10| 9| 32| 1 + |Mon Feb 10 17:32:00.00 1997 PST|1997| 2| 10| 17| 32| 0 + |Mon Feb 10 17:32:01.00 1997 PST|1997| 2| 10| 17| 32| 1 + |Mon Feb 10 17:32:01.00 1997 PST|1997| 2| 10| 17| 32| 1 + |Mon Feb 10 17:32:01.00 1997 PST|1997| 2| 10| 17| 32| 1 + |Mon Feb 10 17:32:01.00 1997 PST|1997| 2| 10| 17| 32| 1 + |Mon Feb 10 17:32:01.00 1997 PST|1997| 2| 10| 17| 32| 1 + |Mon Feb 10 17:32:01.00 1997 PST|1997| 2| 10| 17| 32| 1 + |Mon Feb 10 17:32:01.00 1997 PST|1997| 2| 10| 17| 32| 1 + |Mon Feb 10 17:32:01.00 1997 PST|1997| 2| 10| 17| 32| 1 + |Mon Feb 10 17:32:01.00 1997 PST|1997| 2| 10| 17| 32| 1 + |Mon Feb 10 17:32:01.00 1997 PST|1997| 2| 10| 17| 32| 1 + |Mon Feb 10 17:32:01.00 1997 PST|1997| 2| 10| 17| 32| 1 + |Mon Feb 10 17:32:01.00 1997 PST|1997| 2| 10| 17| 32| 1 + |Mon Feb 10 17:32:01.00 1997 PST|1997| 2| 10| 17| 32| 1 + |Mon Feb 10 17:32:01.00 1997 PST|1997| 2| 10| 17| 32|1.000001 + |Mon Feb 10 17:32:01.40 1997 PST|1997| 2| 10| 17| 32| 1.4 + |Mon Feb 10 17:32:01.50 1997 PST|1997| 2| 10| 17| 32| 1.5 + |Mon Feb 10 17:32:01.60 1997 PST|1997| 2| 10| 17| 32| 1.6 + |Mon Feb 10 17:32:02.00 1997 PST|1997| 2| 10| 17| 32|1.999999 + |Tue Feb 11 17:32:01.00 1997 PST|1997| 2| 11| 17| 32| 1 + |Wed Feb 12 17:32:01.00 1997 PST|1997| 2| 12| 17| 32| 1 + |Thu Feb 13 17:32:01.00 1997 PST|1997| 2| 13| 17| 32| 1 + |Fri Feb 14 17:32:01.00 1997 PST|1997| 2| 14| 17| 32| 1 + |Sat Feb 15 17:32:01.00 1997 PST|1997| 2| 15| 17| 32| 1 + |Sun Feb 16 17:32:01.00 1997 PST|1997| 2| 16| 17| 32| 1 + |Sun Feb 16 17:32:01.00 1997 PST|1997| 2| 16| 17| 32| 1 + |Fri Feb 28 17:32:01.00 1997 PST|1997| 2| 28| 17| 32| 1 + |Sat Mar 01 17:32:01.00 1997 PST|1997| 3| 1| 17| 32| 1 + |Sat Mar 01 17:32:01.00 1997 PST|1997| 3| 1| 17| 32| 1 + |Tue Jun 10 17:32:01.00 1997 PDT|1997| 6| 10| 17| 32| 1 + |Tue Jun 10 18:32:01.00 1997 PDT|1997| 6| 10| 18| 32| 1 + |Tue Dec 30 17:32:01.00 1997 PST|1997| 12| 30| 17| 32| 1 + |Wed Dec 31 17:32:01.00 1997 PST|1997| 12| 31| 17| 32| 1 + |Fri Dec 31 17:32:01.00 1999 PST|1999| 12| 31| 17| 32| 1 + |Sat Jan 01 17:32:01.00 2000 PST|2000| 1| 1| 17| 32| 1 + |Sun Dec 31 17:32:01.00 2000 PST|2000| 12| 31| 17| 32| 1 + |Mon Jan 01 17:32:01.00 2001 PST|2001| 1| 1| 17| 32| 1 + |Sat Feb 16 17:32:01.00 2097 |2097| 2| 16| 17| 32| 1 +(50 rows) + +QUERY: SELECT '' AS four, f1 AS abstime, + date_part('year', f1) AS year, date_part('month', f1) AS month, + date_part('day',f1) AS day, date_part('hour', f1) AS hour, + date_part('minute', f1) AS minute, date_part('second', f1) AS second + FROM ABSTIME_TBL + WHERE isfinite(f1) and f1 <> 'current'::abstime + ORDER BY abstime; +four|abstime |year|month|day|hour|minute|second +----+----------------------------+----+-----+---+----+------+------ + |Mon May 10 23:59:12 1943 PST|1943| 5| 10| 23| 59| 12 + |epoch |1969| 12| 31| 16| 0| 0 + |Sun Jan 14 03:14:21 1973 PST|1973| 1| 14| 3| 14| 21 + |Mon May 01 00:30:30 1995 PDT|1995| 5| 1| 0| 30| 30 +(4 rows) + +QUERY: SELECT '' AS ten, f1 AS datetime, date( f1) AS date + FROM TEMP_DATETIME + WHERE f1 <> 'current'::datetime + ORDER BY date; +ten|datetime | date +---+-------------------------------+---------- + |Wed Feb 28 17:32:01.00 1996 PST|02-29-1996 + |Thu Feb 29 17:32:01.00 1996 PST|03-01-1996 + |Fri Mar 01 17:32:01.00 1996 PST|03-02-1996 + |Mon Dec 30 17:32:01.00 1996 PST|12-31-1996 + |Tue Dec 31 17:32:01.00 1996 PST|01-01-1997 + |Fri Dec 31 17:32:01.00 1999 PST|01-01-2000 + |epoch |01-01-2000 + |Sat Jan 01 17:32:01.00 2000 PST|01-02-2000 + |Sun Dec 31 17:32:01.00 2000 PST|01-01-2001 + |Mon Jan 01 17:32:01.00 2001 PST|01-02-2001 +(10 rows) + +QUERY: SELECT '' AS ten, f1 AS datetime, abstime( f1) AS abstime + FROM TEMP_DATETIME + ORDER BY abstime; +ten|datetime |abstime +---+-------------------------------+---------------------------- + |epoch |epoch + |Wed Feb 28 17:32:01.00 1996 PST|Wed Feb 28 17:32:01 1996 PST + |Thu Feb 29 17:32:01.00 1996 PST|Thu Feb 29 17:32:01 1996 PST + |Fri Mar 01 17:32:01.00 1996 PST|Fri Mar 01 17:32:01 1996 PST + |Mon Dec 30 17:32:01.00 1996 PST|Mon Dec 30 17:32:01 1996 PST + |Tue Dec 31 17:32:01.00 1996 PST|Tue Dec 31 17:32:01 1996 PST + |Fri Dec 31 17:32:01.00 1999 PST|Fri Dec 31 17:32:01 1999 PST + |Sat Jan 01 17:32:01.00 2000 PST|Sat Jan 01 17:32:01 2000 PST + |Sun Dec 31 17:32:01.00 2000 PST|Sun Dec 31 17:32:01 2000 PST + |Mon Jan 01 17:32:01.00 2001 PST|Mon Jan 01 17:32:01 2001 PST +(10 rows) + +QUERY: SELECT '' AS five, f1 AS abstime, date( f1) AS date + FROM ABSTIME_TBL + WHERE isfinite(f1) AND f1 <> 'current'::abstime + ORDER BY date; +five|abstime | date +----+----------------------------+---------- + |Mon May 10 23:59:12 1943 PST|05-10-1943 + |epoch |01-01-1970 + |Sun Jan 14 03:14:21 1973 PST|01-14-1973 + |Mon May 01 00:30:30 1995 PDT|05-01-1995 +(4 rows) + +QUERY: SELECT '' AS five, d1 AS datetime, abstime(d1) AS abstime + FROM DATETIME_TBL WHERE NOT isfinite(d1); +five|datetime |abstime +----+---------+--------- + |invalid |invalid + |-infinity|-infinity + |infinity |infinity + |invalid |invalid + |invalid |invalid +(5 rows) + +QUERY: SELECT '' AS three, f1 as abstime, datetime(f1) AS datetime + FROM ABSTIME_TBL WHERE NOT isfinite(f1); +three|abstime |datetime +-----+---------+--------- + |infinity |infinity + |-infinity|-infinity + |invalid |invalid +(3 rows) + +QUERY: SELECT '' AS ten, f1 AS timespan, reltime( f1) AS reltime + FROM TIMESPAN_TBL; +ten|timespan |reltime +---+-----------------------------+---------------- + |@ 1 min |@ 1 minute + |@ 5 hours |@ 5 hours + |@ 10 days |@ 10 days + |@ 34 years |@ 408 months + |@ 3 mons |@ 3 months + |@ 14 secs ago |@ 14 seconds ago + |@ 1 day 2 hours 3 mins 4 secs|@ 93784 seconds + |@ 6 years |@ 72 months + |@ 5 mons |@ 5 months + |@ 5 mons 12 hours |@ 3612 hours +(10 rows) + +QUERY: SELECT '' AS six, f1 as reltime, timespan( f1) AS timespan + FROM RELTIME_TBL; +six|reltime |timespan +---+----------------+------------- + |@ 1 minute |@ 1 min + |@ 5 hours |@ 5 hours + |@ 10 days |@ 10 days + |@ 34 years |@ 12410 days + |@ 3 months |@ 90 days + |@ 14 seconds ago|@ 14 secs ago +(6 rows) + +QUERY: DROP TABLE TEMP_DATETIME; diff --git a/src/test/regress/expected/int2.out b/src/test/regress/expected/int2.out index 2ee4c134c58..ef79f91fc91 100644 --- a/src/test/regress/expected/int2.out +++ b/src/test/regress/expected/int2.out @@ -204,4 +204,3 @@ five| f1| x |-32767|-16383 (5 rows) -QUERY: DROP TABLE INT2_TBL; diff --git a/src/test/regress/expected/int4.out b/src/test/regress/expected/int4.out index a43c3085298..ed3b9f2d096 100644 --- a/src/test/regress/expected/int4.out +++ b/src/test/regress/expected/int4.out @@ -276,4 +276,3 @@ three 3 (1 row) -QUERY: DROP TABLE INT4_TBL; diff --git a/src/test/regress/expected/lseg.out b/src/test/regress/expected/lseg.out new file mode 100644 index 00000000000..5d5e22bd40f --- /dev/null +++ b/src/test/regress/expected/lseg.out @@ -0,0 +1,37 @@ +QUERY: CREATE TABLE LSEG_TBL (s lseg); +QUERY: INSERT INTO LSEG_TBL VALUES ('[(1,2),(3,4)]'); +QUERY: INSERT INTO LSEG_TBL VALUES ('(0,0),(6,6)'); +QUERY: INSERT INTO LSEG_TBL VALUES ('10,-10 ,-3,-4'); +QUERY: INSERT INTO LSEG_TBL VALUES ('[-1e6,2e2,3e5, -4e1]'); +QUERY: INSERT INTO LSEG_TBL VALUES ('(11,22,33,44)'); +QUERY: INSERT INTO LSEG_TBL VALUES ('(3asdf,2 ,3,4r2)'); +WARN:Bad lseg external representation '(3asdf,2 ,3,4r2)' +QUERY: INSERT INTO LSEG_TBL VALUES ('[1,2,3, 4'); +WARN:Bad lseg external representation '[1,2,3, 4' +QUERY: INSERT INTO LSEG_TBL VALUES ('[(,2),(3,4)]'); +WARN:Bad lseg external representation '[(,2),(3,4)]' +QUERY: INSERT INTO LSEG_TBL VALUES ('[(1,2),(3,4)'); +WARN:Bad lseg external representation '[(1,2),(3,4)' +QUERY: select * from LSEG_TBL; +s +----------------------------- +[(1,2),(3,4)] +[(0,0),(6,6)] +[(10,-10),(-3,-4)] +[(-1000000,200),(300000,-40)] +[(11,22),(33,44)] +(5 rows) + +QUERY: SELECT * FROM LSEG_TBL WHERE s <= '[(1,2),(3,4)]'::lseg; +NOTICE:there is no operator <= for types lseg and lseg +NOTICE:You will either have to retype this query using an +NOTICE:explicit cast, or you will have to define the operator +WARN:<= for lseg and lseg using CREATE OPERATOR +QUERY: SELECT * FROM LSEG_TBL WHERE (s <===> '[(1,2),(3,4)]'::lseg) < 10; +s +------------------ +[(1,2),(3,4)] +[(0,0),(6,6)] +[(10,-10),(-3,-4)] +(3 rows) + diff --git a/src/test/regress/expected/numerology.out b/src/test/regress/expected/numerology.out new file mode 100644 index 00000000000..a2bdf3316aa --- /dev/null +++ b/src/test/regress/expected/numerology.out @@ -0,0 +1,118 @@ +QUERY: CREATE TABLE TEMP_FLOAT (f1 FLOAT8); +QUERY: INSERT INTO TEMP_FLOAT (f1) + SELECT float8(f1) FROM INT4_TBL; +QUERY: INSERT INTO TEMP_FLOAT (f1) + SELECT float8(f1) FROM INT2_TBL; +QUERY: SELECT '' AS ten, f1 FROM TEMP_FLOAT + ORDER BY f1; +ten| f1 +---+----------- + |-2147483647 + | -123456 + | -32767 + | -1234 + | 0 + | 0 + | 1234 + | 32767 + | 123456 + | 2147483647 +(10 rows) + +QUERY: CREATE TABLE TEMP_INT4 (f1 INT4); +QUERY: INSERT INTO TEMP_INT4 (f1) + SELECT int4(f1) FROM FLOAT8_TBL + WHERE (f1 > -2147483647) AND (f1 < 2147483647); +QUERY: INSERT INTO TEMP_INT4 (f1) + SELECT int4(f1) FROM INT2_TBL; +QUERY: SELECT '' AS nine, f1 FROM TEMP_INT4 + ORDER BY f1; +nine| f1 +----+------ + |-32767 + | -1234 + | -1004 + | -35 + | 0 + | 0 + | 0 + | 1234 + | 32767 +(9 rows) + +QUERY: CREATE TABLE TEMP_INT2 (f1 INT2); +QUERY: INSERT INTO TEMP_INT2 (f1) + SELECT int2(f1) FROM FLOAT8_TBL + WHERE (f1 >= -32767) AND (f1 <= 32767); +QUERY: INSERT INTO TEMP_INT2 (f1) + SELECT int2(f1) FROM INT4_TBL + WHERE (f1 >= -32767) AND (f1 <= 32767); +QUERY: SELECT '' AS five, f1 FROM TEMP_INT2 + ORDER BY f1; +five| f1 +----+----- + |-1004 + | -35 + | 0 + | 0 + | 0 +(5 rows) + +QUERY: CREATE TABLE TEMP_GROUP (f1 INT4, f2 INT4, f3 FLOAT8); +QUERY: INSERT INTO TEMP_GROUP + SELECT 1, (- i.f1), (- f.f1) + FROM INT4_TBL i, FLOAT8_TBL f; +QUERY: INSERT INTO TEMP_GROUP + SELECT 2, i.f1, f.f1 + FROM INT4_TBL i, FLOAT8_TBL f; +QUERY: SELECT DISTINCT f1 AS two FROM TEMP_GROUP; +two +--- + 1 + 2 +(2 rows) + +QUERY: SELECT f1 AS two, max(f3) AS max_float, min(f3) as min_float + FROM TEMP_GROUP + GROUP BY two + ORDER BY two, max_float, min_float; +two|max_float |min_float +---+--------------------+--------------------- + 1|1.2345678901234e+200|0 + 2|0 |-1.2345678901234e+200 +(2 rows) + +QUERY: SELECT f1 AS two, max(f3) AS max_float, min(f3) AS min_float + FROM TEMP_GROUP + GROUP BY two + ORDER BY two, max_float, min_float; +two|max_float |min_float +---+--------------------+--------------------- + 1|1.2345678901234e+200|0 + 2|0 |-1.2345678901234e+200 +(2 rows) + +QUERY: SELECT f1 AS two, (max(f3) + 1) AS max_plus_1, (min(f3) - 1) AS min_minus_1 + FROM TEMP_GROUP + GROUP BY two + ORDER BY two, min_minus_1; +two|max_plus_1 |min_minus_1 +---+--------------------+--------------------- + 1|1.2345678901234e+200|-1 + 2|1 |-1.2345678901234e+200 +(2 rows) + +QUERY: SELECT f1 AS two, (max(f3) + 1) AS max_plus_1, (min(f3) - 1) AS min_minus_1 + FROM TEMP_GROUP + GROUP BY two + ORDER BY two, min_minus_1; +two|max_plus_1 |min_minus_1 +---+--------------------+--------------------- + 1|1.2345678901234e+200|-1 + 2|1 |-1.2345678901234e+200 +(2 rows) + +QUERY: DROP TABLE TEMP_INT2; +QUERY: DROP TABLE TEMP_INT4; +QUERY: DROP TABLE TEMP_FLOAT; +QUERY: DROP TABLE TEMP_GROUP; diff --git a/src/test/regress/expected/path.out b/src/test/regress/expected/path.out new file mode 100644 index 00000000000..f159bee9362 --- /dev/null +++ b/src/test/regress/expected/path.out @@ -0,0 +1,70 @@ +QUERY: CREATE TABLE PATH_TBL (f1 path); +QUERY: INSERT INTO PATH_TBL VALUES ('[(1,2),(3,4)]'); +QUERY: INSERT INTO PATH_TBL VALUES ('((1,2),(3,4))'); +QUERY: INSERT INTO PATH_TBL VALUES ('[(0,0),(3,0),(4,5),(1,6)]'); +QUERY: INSERT INTO PATH_TBL VALUES ('((1,2),(3,4))'); +QUERY: INSERT INTO PATH_TBL VALUES ('1,2 ,3,4'); +QUERY: INSERT INTO PATH_TBL VALUES ('[1,2,3, 4]'); +QUERY: INSERT INTO PATH_TBL VALUES ('(0,2,11,12,13,14)'); +QUERY: INSERT INTO PATH_TBL VALUES ('(1,2,11,12,13,14)'); +QUERY: INSERT INTO PATH_TBL VALUES ('[(,2),(3,4)]'); +WARN:Bad path external representation '[(,2),(3,4)]' +QUERY: INSERT INTO PATH_TBL VALUES ('[(1,2),(3,4)'); +WARN:Bad path external representation '[(1,2),(3,4)' +QUERY: SELECT f1 FROM PATH_TBL; +f1 +------------------------- +[(1,2),(3,4)] +((1,2),(3,4)) +[(0,0),(3,0),(4,5),(1,6)] +((1,2),(3,4)) +((1,2),(3,4)) +[(1,2),(3,4)] +[(11,12),(13,14)] +((11,12),(13,14)) +(8 rows) + +QUERY: SELECT '' AS count, f1 AS open_path FROM PATH_TBL WHERE isopen(f1); +count|open_path +-----+------------------------- + |[(1,2),(3,4)] + |[(0,0),(3,0),(4,5),(1,6)] + |[(1,2),(3,4)] + |[(11,12),(13,14)] +(4 rows) + +QUERY: SELECT '' AS count, f1 AS closed_path FROM PATH_TBL WHERE isclosed(f1); +count|closed_path +-----+----------------- + |((1,2),(3,4)) + |((1,2),(3,4)) + |((1,2),(3,4)) + |((11,12),(13,14)) +(4 rows) + +QUERY: SELECT '' AS count, pclose(f1) AS closed_path FROM PATH_TBL; +count|closed_path +-----+------------------------- + |((1,2),(3,4)) + |((1,2),(3,4)) + |((0,0),(3,0),(4,5),(1,6)) + |((1,2),(3,4)) + |((1,2),(3,4)) + |((1,2),(3,4)) + |((11,12),(13,14)) + |((11,12),(13,14)) +(8 rows) + +QUERY: SELECT '' AS count, popen(f1) AS open_path FROM PATH_TBL; +count|open_path +-----+------------------------- + |[(1,2),(3,4)] + |[(1,2),(3,4)] + |[(0,0),(3,0),(4,5),(1,6)] + |[(1,2),(3,4)] + |[(1,2),(3,4)] + |[(1,2),(3,4)] + |[(11,12),(13,14)] + |[(11,12),(13,14)] +(8 rows) + diff --git a/src/test/regress/expected/point.out b/src/test/regress/expected/point.out index 547b2f521a5..4421f6bced5 100644 --- a/src/test/regress/expected/point.out +++ b/src/test/regress/expected/point.out @@ -82,63 +82,67 @@ two|f1 |(-10,0) (2 rows) -QUERY: SELECT '' AS six, p.f1, p.f1 <===> '(0,0)'::point AS dist FROM POINT_TBL p; +QUERY: SELECT '' AS six, p.f1, p.f1 <===> '(0,0)'::point AS dist + FROM POINT_TBL p + ORDER BY dist; six|f1 | dist ---+----------+---------------- |(0,0) | 0 - |(-10,0) | 10 |(-3,4) | 5 - |(5.1,34.5)|34.8749193547455 + |(-10,0) | 10 |(-5,-12) | 13 |(10,10) | 14.142135623731 + |(5.1,34.5)|34.8749193547455 (6 rows) -QUERY: SELECT '' AS thirtysix, p1.f1, p2.f1, p1.f1 <===> p2.f1 AS dist - FROM POINT_TBL p1, POINT_TBL p2; -thirtysix|f1 |f1 | dist +QUERY: SET geqo TO 'off'; +QUERY: SELECT '' AS thirtysix, p1.f1 AS point1, p2.f1 AS point2, p1.f1 <===> p2.f1 AS dist + FROM POINT_TBL p1, POINT_TBL p2 + ORDER BY dist; +thirtysix|point1 |point2 | dist ---------+----------+----------+---------------- + |(10,10) |(10,10) | 0 + |(-5,-12) |(-5,-12) | 0 + |(5.1,34.5)|(5.1,34.5)| 0 + |(-3,4) |(-3,4) | 0 + |(-10,0) |(-10,0) | 0 |(0,0) |(0,0) | 0 - |(-10,0) |(0,0) | 10 + |(0,0) |(-3,4) | 5 |(-3,4) |(0,0) | 5 - |(5.1,34.5)|(0,0) |34.8749193547455 - |(-5,-12) |(0,0) | 13 - |(10,10) |(0,0) | 14.142135623731 - |(0,0) |(-10,0) | 10 - |(-10,0) |(-10,0) | 0 + |(-10,0) |(-3,4) |8.06225774829855 |(-3,4) |(-10,0) |8.06225774829855 - |(5.1,34.5)|(-10,0) |37.6597928831267 + |(-10,0) |(0,0) | 10 + |(0,0) |(-10,0) | 10 + |(-5,-12) |(0,0) | 13 + |(-10,0) |(-5,-12) | 13 + |(0,0) |(-5,-12) | 13 |(-5,-12) |(-10,0) | 13 + |(10,10) |(0,0) | 14.142135623731 + |(0,0) |(10,10) | 14.142135623731 + |(10,10) |(-3,4) |14.3178210632764 + |(-3,4) |(10,10) |14.3178210632764 + |(-3,4) |(-5,-12) |16.1245154965971 + |(-5,-12) |(-3,4) |16.1245154965971 + |(-10,0) |(10,10) |22.3606797749979 |(10,10) |(-10,0) |22.3606797749979 - |(0,0) |(-3,4) | 5 - |(-10,0) |(-3,4) |8.06225774829855 - |(-3,4) |(-3,4) | 0 + |(5.1,34.5)|(10,10) |24.9851956166046 + |(10,10) |(5.1,34.5)|24.9851956166046 + |(-5,-12) |(10,10) |26.6270539113887 + |(10,10) |(-5,-12) |26.6270539113887 + |(-3,4) |(5.1,34.5)|31.5572495632937 |(5.1,34.5)|(-3,4) |31.5572495632937 - |(-5,-12) |(-3,4) |16.1245154965971 - |(10,10) |(-3,4) |14.3178210632764 + |(5.1,34.5)|(0,0) |34.8749193547455 |(0,0) |(5.1,34.5)|34.8749193547455 |(-10,0) |(5.1,34.5)|37.6597928831267 - |(-3,4) |(5.1,34.5)|31.5572495632937 - |(5.1,34.5)|(5.1,34.5)| 0 + |(5.1,34.5)|(-10,0) |37.6597928831267 |(-5,-12) |(5.1,34.5)|47.5842410888311 - |(10,10) |(5.1,34.5)|24.9851956166046 - |(0,0) |(-5,-12) | 13 - |(-10,0) |(-5,-12) | 13 - |(-3,4) |(-5,-12) |16.1245154965971 |(5.1,34.5)|(-5,-12) |47.5842410888311 - |(-5,-12) |(-5,-12) | 0 - |(10,10) |(-5,-12) |26.6270539113887 - |(0,0) |(10,10) | 14.142135623731 - |(-10,0) |(10,10) |22.3606797749979 - |(-3,4) |(10,10) |14.3178210632764 - |(5.1,34.5)|(10,10) |24.9851956166046 - |(-5,-12) |(10,10) |26.6270539113887 - |(10,10) |(10,10) | 0 (36 rows) -QUERY: SELECT '' AS thirty, p1.f1, p2.f1 +QUERY: SELECT '' AS thirty, p1.f1 AS point1, p2.f1 AS point2 FROM POINT_TBL p1, POINT_TBL p2 WHERE (p1.f1 <===> p2.f1) > 3; -thirty|f1 |f1 +thirty|point1 |point2 ------+----------+---------- |(-10,0) |(0,0) |(-3,4) |(0,0) @@ -172,39 +176,38 @@ thirty|f1 |f1 |(-5,-12) |(10,10) (30 rows) -QUERY: SELECT '' AS fifteen, p1.f1, p2.f1 +QUERY: SELECT '' AS fifteen, p1.f1 AS point1, p2.f1 AS point2, (p1.f1 <===> p2.f1) AS distance FROM POINT_TBL p1, POINT_TBL p2 - WHERE (p1.f1 <===> p2.f1) > 3 and - p1.f1 !< p2.f1; -fifteen|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) - |(0,0) |(10,10) - |(-10,0) |(10,10) - |(-3,4) |(10,10) - |(5.1,34.5)|(10,10) - |(-5,-12) |(10,10) + WHERE (p1.f1 <===> p2.f1) > 3 and p1.f1 !< p2.f1 + ORDER BY distance; +fifteen|point1 |point2 | distance +-------+----------+----------+---------------- + |(-3,4) |(0,0) | 5 + |(-10,0) |(-3,4) |8.06225774829855 + |(-10,0) |(0,0) | 10 + |(-5,-12) |(0,0) | 13 + |(-10,0) |(-5,-12) | 13 + |(0,0) |(10,10) | 14.142135623731 + |(-3,4) |(10,10) |14.3178210632764 + |(-5,-12) |(-3,4) |16.1245154965971 + |(-10,0) |(10,10) |22.3606797749979 + |(5.1,34.5)|(10,10) |24.9851956166046 + |(-5,-12) |(10,10) |26.6270539113887 + |(-3,4) |(5.1,34.5)|31.5572495632937 + |(0,0) |(5.1,34.5)|34.8749193547455 + |(-10,0) |(5.1,34.5)|37.6597928831267 + |(-5,-12) |(5.1,34.5)|47.5842410888311 (15 rows) -QUERY: SELECT '' AS three, p1.f1, p2.f1 +QUERY: SELECT '' AS three, p1.f1 AS point1, p2.f1 AS point2, (p1.f1 <===> p2.f1) AS distance FROM POINT_TBL p1, POINT_TBL p2 - WHERE (p1.f1 <===> p2.f1) > 3 and - p1.f1 !< p2.f1 and - p1.f1 !^ p2.f1; -three|f1 |f1 ------+----------+-------- - |(-3,4) |(0,0) - |(-10,0) |(-5,-12) - |(5.1,34.5)|(10,10) + WHERE (p1.f1 <===> p2.f1) > 3 and p1.f1 !< p2.f1 and p1.f1 !^ p2.f1 + ORDER BY distance; +three|point1 |point2 | distance +-----+----------+--------+---------------- + |(-3,4) |(0,0) | 5 + |(-10,0) |(-5,-12)| 13 + |(5.1,34.5)|(10,10) |24.9851956166046 (3 rows) -QUERY: DROP TABLE POINT_TBL; +QUERY: RESET geqo; diff --git a/src/test/regress/expected/polygon.out b/src/test/regress/expected/polygon.out index 884b57f2e14..826ec59cfe2 100644 --- a/src/test/regress/expected/polygon.out +++ b/src/test/regress/expected/polygon.out @@ -139,4 +139,3 @@ true t (1 row) -QUERY: DROP TABLE POLYGON_TBL; diff --git a/src/test/regress/expected/timespan.out b/src/test/regress/expected/timespan.out index 71ed584331c..ab21126f844 100644 --- a/src/test/regress/expected/timespan.out +++ b/src/test/regress/expected/timespan.out @@ -10,9 +10,9 @@ QUERY: INSERT INTO TIMESPAN_TBL (f1) VALUES ('6 years'); QUERY: INSERT INTO TIMESPAN_TBL (f1) VALUES ('5 months'); QUERY: INSERT INTO TIMESPAN_TBL (f1) VALUES ('5 months 12 hours'); QUERY: INSERT INTO TIMESPAN_TBL (f1) VALUES ('badly formatted timespan'); -WARN:Bad timespan external representation badly formatted timespan +WARN:Bad timespan external representation 'badly formatted timespan' QUERY: INSERT INTO TIMESPAN_TBL (f1) VALUES ('@ 30 eons ago'); -WARN:Bad timespan external representation @ 30 eons ago +WARN:Bad timespan external representation '@ 30 eons ago' QUERY: SELECT '' AS ten, TIMESPAN_TBL.*; ten|f1 ---+----------------------------- diff --git a/src/test/regress/expected/tinterval.out b/src/test/regress/expected/tinterval.out index ff6aef26e5d..cf0e02a6d78 100644 --- a/src/test/regress/expected/tinterval.out +++ b/src/test/regress/expected/tinterval.out @@ -89,61 +89,66 @@ three|f1 |["epoch" "Mon May 01 00:30:30 1995 PDT"] (3 rows) -QUERY: SELECT '' AS five, t1.*, t2.* +QUERY: SET geqo TO 'off'; +QUERY: SELECT '' AS five, t1.f1, t2.f1 FROM TINTERVAL_TBL t1, TINTERVAL_TBL t2 WHERE t1.f1 && t2.f1 and - t1.f1 = t2.f1; + t1.f1 = t2.f1 + ORDER BY t1.f1, t2.f1; five|f1 |f1 ----+---------------------------------------------------------------+--------------------------------------------------------------- |["-infinity" "infinity"] |["-infinity" "infinity"] + |["Thu Feb 15 12:15:03 1990 PST" "current"] |["Thu Feb 15 12:15:03 1990 PST" "current"] |["Mon May 10 23:59:12 1943 PST" "Sun Jan 14 03:14:21 1973 PST"]|["Mon May 10 23:59:12 1943 PST" "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"] + |["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"] (5 rows) -QUERY: SELECT '' AS fourteen, t1.*, t2.* +QUERY: SELECT '' AS fourteen, t1.f1 AS interval1, t2.f1 AS interval2 FROM TINTERVAL_TBL t1, TINTERVAL_TBL t2 - WHERE t1.f1 && t2.f1 and - not t1.f1 = t2.f1; -fourteen|f1 |f1 + WHERE t1.f1 && t2.f1 and not t1.f1 = t2.f1 + ORDER BY interval1, interval2; +fourteen|interval1 |interval2 --------+---------------------------------------------------------------+--------------------------------------------------------------- - |["Mon May 10 23:59:12 1943 PST" "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 PST" "Sun Jan 14 03:14:21 1973 PST"] - |["epoch" "Mon May 01 00:30:30 1995 PDT"] |["Mon May 10 23:59:12 1943 PST" "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"] + |["-infinity" "infinity"] |["Thu Feb 15 12:15:03 1990 PST" "current"] + |["Mon May 10 23:59:12 1943 PST" "Sun Jan 14 03:14:21 1973 PST"]|["-infinity" "infinity"] + |["epoch" "Mon May 01 00:30:30 1995 PDT"] |["Mon May 10 23:59:12 1943 PST" "Sun Jan 14 03:14:21 1973 PST"] + |["epoch" "Mon May 01 00:30:30 1995 PDT"] |["-infinity" "infinity"] + |["Thu Feb 15 12:15:03 1990 PST" "current"] |["-infinity" "infinity"] + |["epoch" "Mon May 01 00:30:30 1995 PDT"] |["Thu Feb 15 12:15:03 1990 PST" "current"] + |["Sun Sep 04 23:59:12 1983 PDT" "Tue Oct 04 23:59:12 1983 PDT"]|["-infinity" "infinity"] |["Mon May 10 23:59:12 1943 PST" "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"] + |["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"] (14 rows) -QUERY: SELECT '' AS five, t1.* +QUERY: SELECT '' AS five, t1.f1 FROM TINTERVAL_TBL t1 WHERE not t1.f1 << - '["Aug 15 14:23:19 1980" "Sep 16 14:23:19 1990"]'::tinterval; + '["Aug 15 14:23:19 1980" "Sep 16 14:23:19 1990"]'::tinterval + ORDER BY t1.f1; five|f1 ----+--------------------------------------------------------------- - |["Mon May 10 23:59:12 1943 PST" "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"] + |["Sun Sep 04 23:59:12 1983 PDT" "Tue Oct 04 23:59:12 1983 PDT"] + |["Mon May 10 23:59:12 1943 PST" "Sun Jan 14 03:14:21 1973 PST"] (3 rows) -QUERY: SELECT '' AS three, t1.* +QUERY: SELECT '' AS three, t1.f1 FROM TINTERVAL_TBL t1 WHERE t1.f1 && ('Aug 15 14:23:19 1983'::abstime <#> - 'Sep 16 14:23:19 1983'::abstime); + 'Sep 16 14:23:19 1983'::abstime) + ORDER BY t1.f1; 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"] + |["Sun Sep 04 23:59:12 1983 PDT" "Tue Oct 04 23:59:12 1983 PDT"] (3 rows) +QUERY: RESET geqo; diff --git a/src/test/regress/sql/abstime.sql b/src/test/regress/sql/abstime.sql index 581c62b696d..24e53704488 100644 --- a/src/test/regress/sql/abstime.sql +++ b/src/test/regress/sql/abstime.sql @@ -80,8 +80,8 @@ SELECT '' AS three, ABSTIME_TBL.* WHERE (ABSTIME_TBL.f1 - '@ 3 year ago'::reltime) -- -(-3) years < 'Jan 14 14:00:00 1977'::abstime; - -SELECT '' AS ten, ABSTIME_TBL.*, RELTIME_TBL.* +SELECT '' AS ten, ABSTIME_TBL.f1 AS abstime, RELTIME_TBL.f1 AS reltime WHERE (ABSTIME_TBL.f1 + RELTIME_TBL.f1) - < 'Jan 14 14:00:00 1971'::abstime; + < 'Jan 14 14:00:00 1971'::abstime + ORDER BY abstime, reltime; diff --git a/src/test/regress/sql/box.sql b/src/test/regress/sql/box.sql index b6197cc9ac8..12125959c87 100644 --- a/src/test/regress/sql/box.sql +++ b/src/test/regress/sql/box.sql @@ -114,4 +114,5 @@ SELECT '' AS one, b1.*, b2.* FROM BOX_TBL b1, BOX_TBL b2 WHERE b1.f1 ~ b2.f1 and not b1.f1 ~= b2.f1; -DROP TABLE BOX_TBL; +SELECT '' AS four, height(f1), width(f1) FROM BOX_TBL; + diff --git a/src/test/regress/sql/circle.sql b/src/test/regress/sql/circle.sql new file mode 100644 index 00000000000..bd6ad9c0916 --- /dev/null +++ b/src/test/regress/sql/circle.sql @@ -0,0 +1,46 @@ +-- +-- circles +-- + +CREATE TABLE CIRCLE_TBL (f1 circle); + +INSERT INTO CIRCLE_TBL VALUES ('<(0,0),3>'); + +INSERT INTO CIRCLE_TBL VALUES ('<(1,2),100>'); + +INSERT INTO CIRCLE_TBL VALUES ('1,3,5'); + +INSERT INTO CIRCLE_TBL VALUES ('((1,2),3)'); + +INSERT INTO CIRCLE_TBL VALUES ('<(100,200),10>'); + +INSERT INTO CIRCLE_TBL VALUES ('<(100,0),100>'); + +-- bad values + +INSERT INTO CIRCLE_TBL VALUES ('<(-100,0),-100>'); + +INSERT INTO CIRCLE_TBL VALUES ('1abc,3,5'); + +INSERT INTO CIRCLE_TBL VALUES ('(3,(1,2),3)'); + +SELECT * FROM CIRCLE_TBL; + +SELECT '' AS six, center(f1) AS center + FROM CIRCLE_TBL; + +SELECT '' AS six, radius(f1) AS radius + FROM CIRCLE_TBL; + +SELECT '' AS six, diameter(f1) AS diameter + FROM CIRCLE_TBL; + +SELECT '' AS two, f1 FROM CIRCLE_TBL WHERE radius(f1) < 5; + +SELECT '' AS four, f1 FROM CIRCLE_TBL WHERE diameter(f1) >= 10; + +SELECT '' as five, c1.f1 AS one, c2.f1 AS two, (c1.f1 <===> c2.f1) AS distance + FROM CIRCLE_TBL c1, CIRCLE_TBL c2 + WHERE (c1.f1 < c2.f1) AND ((c1.f1 <===> c2.f1) > 0) + ORDER BY distance, one, two; + diff --git a/src/test/regress/sql/float4.sql b/src/test/regress/sql/float4.sql index 518cc902cb0..1bf1308bd1a 100644 --- a/src/test/regress/sql/float4.sql +++ b/src/test/regress/sql/float4.sql @@ -62,4 +62,3 @@ UPDATE FLOAT4_TBL SELECT '' AS five, FLOAT4_TBL.*; -DROP TABLE FLOAT4_TBL; diff --git a/src/test/regress/sql/float8.sql b/src/test/regress/sql/float8.sql index e55f6c7f24e..820d36b08a1 100644 --- a/src/test/regress/sql/float8.sql +++ b/src/test/regress/sql/float8.sql @@ -101,4 +101,3 @@ SELECT '' AS bad, f.f1 / '0.0' from FLOAT8_TBL f; SELECT '' AS five, FLOAT8_TBL.*; -DROP TABLE FLOAT8_TBL; diff --git a/src/test/regress/sql/geometry.sql b/src/test/regress/sql/geometry.sql new file mode 100644 index 00000000000..bd2b88a1a7b --- /dev/null +++ b/src/test/regress/sql/geometry.sql @@ -0,0 +1,87 @@ +-- +-- Points +-- + +SELECT point(f1) FROM CIRCLE_TBL; + +SELECT '' AS two, p1.f1 + FROM POINT_TBL p1 + WHERE ishorizontal(p1.f1, '(0,0)'::point); + +SELECT '' AS one, p1.f1 + FROM POINT_TBL p1 + WHERE isvertical(p1.f1, '(5.1,34.5)'::point); + +-- +-- Line segments +-- + +-- +-- Lines +-- + +-- +-- Boxes +-- + +SELECT center(f1) FROM BOX_TBL; + +SELECT box(f1) FROM CIRCLE_TBL; + +-- translation +SELECT '' AS count, b.f1 + p.f1 + FROM BOX_TBL b, POINT_TBL p; + +-- scaling and rotation +SELECT '' AS count, b.f1 * p.f1 + FROM BOX_TBL b, POINT_TBL p; + +-- +-- Paths +-- + +SET geqo TO 'off'; + +SELECT points(f1) AS npoints, f1 AS path FROM PATH_TBL; + +SELECT path(f1) FROM POLYGON_TBL; + +-- translation +SELECT '' AS eight, p1.f1 + '(10,10)'::point AS dist_add + FROM PATH_TBL p1; + +-- scaling and rotation +SELECT '' AS eight, p1.f1 * '(2,-1)'::point AS dist_mul + FROM PATH_TBL p1; + +RESET geqo; + +-- +-- Polygons +-- + +SELECT points(f1) AS npoints, f1 AS polygon FROM POLYGON_TBL; + +SELECT polygon(f1) FROM BOX_TBL; + +SELECT polygon(f1) FROM PATH_TBL WHERE isclosed(f1); + +SELECT f1 AS open_path, polygon( pclose(f1)) AS polygon FROM PATH_TBL WHERE isopen(f1); + +-- convert circles to polygons using the default number of points +SELECT polygon(f1) FROM CIRCLE_TBL; + +-- convert the circle to an 8-point polygon +SELECT polygon(8, f1) FROM CIRCLE_TBL; + +-- +-- Circles +-- + +SELECT circle( f1, 50.0) FROM POINT_TBL; + +SELECT '' AS twentyfour, c1.f1 AS circle, p1.f1 AS point, (p1.f1 <===> c1.f1) AS distance + from CIRCLE_TBL c1, POINT_TBL p1 + WHERE (p1.f1 <===> c1.f1) > 0 + ORDER BY distance, circle; + diff --git a/src/test/regress/sql/horology.sql b/src/test/regress/sql/horology.sql new file mode 100644 index 00000000000..bf772e46249 --- /dev/null +++ b/src/test/regress/sql/horology.sql @@ -0,0 +1,91 @@ +-- +-- horology.sql +-- + +-- +-- datetime, timespan arithmetic +-- + +CREATE TABLE TEMP_DATETIME (f1 datetime); + +-- get some candidate input values + +INSERT INTO TEMP_DATETIME (f1) + SELECT d1 FROM DATETIME_TBL + WHERE d1 BETWEEN '13-jun-1957' AND '1-jan-1997' + OR d1 BETWEEN '1-jan-1999' AND '1-jan-2010'; + +SELECT '' AS ten, f1 AS datetime + FROM TEMP_DATETIME + ORDER BY datetime; + +SELECT '' AS hundred, d.f1 AS datetime, t.f1 AS timespan, d.f1 + t.f1 AS plus + FROM TEMP_DATETIME d, TIMESPAN_TBL t + ORDER BY plus; + +SELECT '' AS hundred, d.f1 AS datetime, t.f1 AS timespan, d.f1 - t.f1 AS minus + FROM TEMP_DATETIME d, TIMESPAN_TBL t + WHERE isfinite(d.f1) + ORDER BY minus; + +SELECT '' AS ten, d.f1 AS datetime, '1980-01-06 00:00 GMT'::datetime AS gpstime_zero, + d.f1 - '1980-01-06 00:00 GMT'::datetime AS difference + FROM TEMP_DATETIME d + ORDER BY difference; + +SELECT '' AS hundred, d1.f1 AS datetime1, d2.f1 AS datetime2, d1.f1 - d2.f1 AS difference + FROM TEMP_DATETIME d1, TEMP_DATETIME d2 + ORDER BY datetime1, datetime2, difference; + +SELECT '' as fifty, d1 as datetime, + date_part('year', d1) AS year, date_part('month', d1) AS month, + date_part('day',d1) AS day, date_part('hour', d1) AS hour, + date_part('minute', d1) AS minute, date_part('second', d1) AS second + FROM DATETIME_TBL + WHERE isfinite(d1) and d1 >= '1-jan-1900 GMT' + ORDER BY datetime; + +-- +-- abstime, reltime arithmetic +-- + +SELECT '' AS four, f1 AS abstime, + date_part('year', f1) AS year, date_part('month', f1) AS month, + date_part('day',f1) AS day, date_part('hour', f1) AS hour, + date_part('minute', f1) AS minute, date_part('second', f1) AS second + FROM ABSTIME_TBL + WHERE isfinite(f1) and f1 <> 'current'::abstime + ORDER BY abstime; + +-- +-- conversions +-- + +SELECT '' AS ten, f1 AS datetime, date( f1) AS date + FROM TEMP_DATETIME + WHERE f1 <> 'current'::datetime + ORDER BY date; + +SELECT '' AS ten, f1 AS datetime, abstime( f1) AS abstime + FROM TEMP_DATETIME + ORDER BY abstime; + +SELECT '' AS five, f1 AS abstime, date( f1) AS date + FROM ABSTIME_TBL + WHERE isfinite(f1) AND f1 <> 'current'::abstime + ORDER BY date; + +SELECT '' AS five, d1 AS datetime, abstime(d1) AS abstime + FROM DATETIME_TBL WHERE NOT isfinite(d1); + +SELECT '' AS three, f1 as abstime, datetime(f1) AS datetime + FROM ABSTIME_TBL WHERE NOT isfinite(f1); + +SELECT '' AS ten, f1 AS timespan, reltime( f1) AS reltime + FROM TIMESPAN_TBL; + +SELECT '' AS six, f1 as reltime, timespan( f1) AS timespan + FROM RELTIME_TBL; + +DROP TABLE TEMP_DATETIME; + diff --git a/src/test/regress/sql/int2.sql b/src/test/regress/sql/int2.sql index cc46babbab1..a6b71b3efe2 100644 --- a/src/test/regress/sql/int2.sql +++ b/src/test/regress/sql/int2.sql @@ -73,4 +73,3 @@ 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; -DROP TABLE INT2_TBL; diff --git a/src/test/regress/sql/int4.sql b/src/test/regress/sql/int4.sql index 4dd1352a0f8..884e1e46367 100644 --- a/src/test/regress/sql/int4.sql +++ b/src/test/regress/sql/int4.sql @@ -101,4 +101,3 @@ SELECT |/'64'::float8 AS eight; SELECT ||/'27'::float8 AS three; -DROP TABLE INT4_TBL; diff --git a/src/test/regress/sql/lseg.sql b/src/test/regress/sql/lseg.sql new file mode 100644 index 00000000000..a70b5c2adf7 --- /dev/null +++ b/src/test/regress/sql/lseg.sql @@ -0,0 +1,25 @@ +-- +-- Line segments +-- + +--DROP TABLE LSEG_TBL; +CREATE TABLE LSEG_TBL (s lseg); + +INSERT INTO LSEG_TBL VALUES ('[(1,2),(3,4)]'); +INSERT INTO LSEG_TBL VALUES ('(0,0),(6,6)'); +INSERT INTO LSEG_TBL VALUES ('10,-10 ,-3,-4'); +INSERT INTO LSEG_TBL VALUES ('[-1e6,2e2,3e5, -4e1]'); +INSERT INTO LSEG_TBL VALUES ('(11,22,33,44)'); + +-- bad values for parser testing +INSERT INTO LSEG_TBL VALUES ('(3asdf,2 ,3,4r2)'); +INSERT INTO LSEG_TBL VALUES ('[1,2,3, 4'); +INSERT INTO LSEG_TBL VALUES ('[(,2),(3,4)]'); +INSERT INTO LSEG_TBL VALUES ('[(1,2),(3,4)'); + +select * from LSEG_TBL; + +SELECT * FROM LSEG_TBL WHERE s <= '[(1,2),(3,4)]'::lseg; + +SELECT * FROM LSEG_TBL WHERE (s <===> '[(1,2),(3,4)]'::lseg) < 10; + diff --git a/src/test/regress/sql/numerology.sql b/src/test/regress/sql/numerology.sql new file mode 100644 index 00000000000..65791aa125d --- /dev/null +++ b/src/test/regress/sql/numerology.sql @@ -0,0 +1,94 @@ +-- +-- numerology.sql +-- + +-- +-- Test implicit type conversions +-- This fails for Postgres v6.1 (and earlier?) +-- so let's try explicit conversions for now - tgl 97/05/07 +-- + +CREATE TABLE TEMP_FLOAT (f1 FLOAT8); + +INSERT INTO TEMP_FLOAT (f1) + SELECT float8(f1) FROM INT4_TBL; + +INSERT INTO TEMP_FLOAT (f1) + SELECT float8(f1) FROM INT2_TBL; + +SELECT '' AS ten, f1 FROM TEMP_FLOAT + ORDER BY f1; + +-- int4 + +CREATE TABLE TEMP_INT4 (f1 INT4); + +INSERT INTO TEMP_INT4 (f1) + SELECT int4(f1) FROM FLOAT8_TBL + WHERE (f1 > -2147483647) AND (f1 < 2147483647); + +INSERT INTO TEMP_INT4 (f1) + SELECT int4(f1) FROM INT2_TBL; + +SELECT '' AS nine, f1 FROM TEMP_INT4 + ORDER BY f1; + +-- int2 + +CREATE TABLE TEMP_INT2 (f1 INT2); + +INSERT INTO TEMP_INT2 (f1) + SELECT int2(f1) FROM FLOAT8_TBL + WHERE (f1 >= -32767) AND (f1 <= 32767); + +INSERT INTO TEMP_INT2 (f1) + SELECT int2(f1) FROM INT4_TBL + WHERE (f1 >= -32767) AND (f1 <= 32767); + +SELECT '' AS five, f1 FROM TEMP_INT2 + ORDER BY f1; + +-- +-- Group-by combinations +-- + +CREATE TABLE TEMP_GROUP (f1 INT4, f2 INT4, f3 FLOAT8); + +INSERT INTO TEMP_GROUP + SELECT 1, (- i.f1), (- f.f1) + FROM INT4_TBL i, FLOAT8_TBL f; + +INSERT INTO TEMP_GROUP + SELECT 2, i.f1, f.f1 + FROM INT4_TBL i, FLOAT8_TBL f; + +SELECT DISTINCT f1 AS two FROM TEMP_GROUP; + +SELECT f1 AS two, max(f3) AS max_float, min(f3) as min_float + FROM TEMP_GROUP + GROUP BY two + ORDER BY two, max_float, min_float; + +SELECT f1 AS two, max(f3) AS max_float, min(f3) AS min_float + FROM TEMP_GROUP + GROUP BY two + ORDER BY two, max_float, min_float; + +SELECT f1 AS two, (max(f3) + 1) AS max_plus_1, (min(f3) - 1) AS min_minus_1 + FROM TEMP_GROUP + GROUP BY two + ORDER BY two, min_minus_1; + +SELECT f1 AS two, (max(f3) + 1) AS max_plus_1, (min(f3) - 1) AS min_minus_1 + FROM TEMP_GROUP + GROUP BY two + ORDER BY two, min_minus_1; + +DROP TABLE TEMP_INT2; + +DROP TABLE TEMP_INT4; + +DROP TABLE TEMP_FLOAT; + +DROP TABLE TEMP_GROUP; + diff --git a/src/test/regress/sql/path.sql b/src/test/regress/sql/path.sql new file mode 100644 index 00000000000..8b40b5738ee --- /dev/null +++ b/src/test/regress/sql/path.sql @@ -0,0 +1,40 @@ +-- +-- path.sql +-- + +--DROP TABLE PATH_TBL; + +CREATE TABLE PATH_TBL (f1 path); + +INSERT INTO PATH_TBL VALUES ('[(1,2),(3,4)]'); + +INSERT INTO PATH_TBL VALUES ('((1,2),(3,4))'); + +INSERT INTO PATH_TBL VALUES ('[(0,0),(3,0),(4,5),(1,6)]'); + +INSERT INTO PATH_TBL VALUES ('((1,2),(3,4))'); + +INSERT INTO PATH_TBL VALUES ('1,2 ,3,4'); + +INSERT INTO PATH_TBL VALUES ('[1,2,3, 4]'); + +-- obsolete v6.0 format (closed,count,x1,y1,...) +INSERT INTO PATH_TBL VALUES ('(0,2,11,12,13,14)'); + +INSERT INTO PATH_TBL VALUES ('(1,2,11,12,13,14)'); + +-- bad values for parser testing +INSERT INTO PATH_TBL VALUES ('[(,2),(3,4)]'); + +INSERT INTO PATH_TBL VALUES ('[(1,2),(3,4)'); + +SELECT f1 FROM PATH_TBL; + +SELECT '' AS count, f1 AS open_path FROM PATH_TBL WHERE isopen(f1); + +SELECT '' AS count, f1 AS closed_path FROM PATH_TBL WHERE isclosed(f1); + +SELECT '' AS count, pclose(f1) AS closed_path FROM PATH_TBL; + +SELECT '' AS count, popen(f1) AS open_path FROM PATH_TBL; + diff --git a/src/test/regress/sql/point.sql b/src/test/regress/sql/point.sql index a316cf14a82..745d8ce6342 100644 --- a/src/test/regress/sql/point.sql +++ b/src/test/regress/sql/point.sql @@ -49,24 +49,31 @@ SELECT '' AS three, p.* FROM POINT_TBL p SELECT '' AS two, p.* FROM POINT_TBL p WHERE on_ppath(p.f1,'(0,3,0,0,-10,0,-10,10)'::path); -SELECT '' AS six, p.f1, p.f1 <===> '(0,0)'::point AS dist FROM POINT_TBL p; +SELECT '' AS six, p.f1, p.f1 <===> '(0,0)'::point AS dist + FROM POINT_TBL p + ORDER BY dist; -SELECT '' AS thirtysix, p1.f1, p2.f1, p1.f1 <===> p2.f1 AS dist - FROM POINT_TBL p1, POINT_TBL p2; +SET geqo TO 'off'; -SELECT '' AS thirty, p1.f1, p2.f1 +SELECT '' AS thirtysix, p1.f1 AS point1, p2.f1 AS point2, p1.f1 <===> p2.f1 AS dist + FROM POINT_TBL p1, POINT_TBL p2 + ORDER BY dist; + +SELECT '' AS thirty, p1.f1 AS point1, p2.f1 AS point2 FROM POINT_TBL p1, POINT_TBL p2 WHERE (p1.f1 <===> p2.f1) > 3; -SELECT '' AS fifteen, p1.f1, p2.f1 +-- put distance result into output to allow sorting with GEQ optimizer - tgl 97/05/10 +SELECT '' AS fifteen, p1.f1 AS point1, p2.f1 AS point2, (p1.f1 <===> p2.f1) AS distance FROM POINT_TBL p1, POINT_TBL p2 - WHERE (p1.f1 <===> p2.f1) > 3 and - p1.f1 !< p2.f1; + WHERE (p1.f1 <===> p2.f1) > 3 and p1.f1 !< p2.f1 + ORDER BY distance; -SELECT '' AS three, p1.f1, p2.f1 +-- put distance result into output to allow sorting with GEQ optimizer - tgl 97/05/10 +SELECT '' AS three, p1.f1 AS point1, p2.f1 AS point2, (p1.f1 <===> p2.f1) AS distance FROM POINT_TBL p1, POINT_TBL p2 - WHERE (p1.f1 <===> p2.f1) > 3 and - p1.f1 !< p2.f1 and - p1.f1 !^ p2.f1; + WHERE (p1.f1 <===> p2.f1) > 3 and p1.f1 !< p2.f1 and p1.f1 !^ p2.f1 + ORDER BY distance; + +RESET geqo; -DROP TABLE POINT_TBL; diff --git a/src/test/regress/sql/polygon.sql b/src/test/regress/sql/polygon.sql index cb6c7b5b28c..f8928545a21 100644 --- a/src/test/regress/sql/polygon.sql +++ b/src/test/regress/sql/polygon.sql @@ -115,4 +115,3 @@ 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)'::poly -- 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; -DROP TABLE POLYGON_TBL; diff --git a/src/test/regress/sql/tests b/src/test/regress/sql/tests index 3d7aecbe34a..ee759e29fec 100644 --- a/src/test/regress/sql/tests +++ b/src/test/regress/sql/tests @@ -13,14 +13,20 @@ oidint4 oidname float4 float8 +numerology point +lseg box +path polygon +circle +geometry timespan datetime reltime abstime tinterval +horology create_function_1 create_type create_table diff --git a/src/test/regress/sql/tinterval.sql b/src/test/regress/sql/tinterval.sql index a5c1d65ea36..432e0bf4c03 100644 --- a/src/test/regress/sql/tinterval.sql +++ b/src/test/regress/sql/tinterval.sql @@ -63,26 +63,33 @@ SELECT '' AS three, t1.* WHERE t1.f1 && '["Aug 15 14:23:19 1983" "Sep 16 14:23:19 1983"]'::tinterval; -SELECT '' AS five, t1.*, t2.* +SET geqo TO 'off'; + +SELECT '' AS five, t1.f1, t2.f1 FROM TINTERVAL_TBL t1, TINTERVAL_TBL t2 WHERE t1.f1 && t2.f1 and - t1.f1 = t2.f1; + t1.f1 = t2.f1 + ORDER BY t1.f1, t2.f1; -SELECT '' AS fourteen, t1.*, t2.* +SELECT '' AS fourteen, t1.f1 AS interval1, t2.f1 AS interval2 FROM TINTERVAL_TBL t1, TINTERVAL_TBL t2 - WHERE t1.f1 && t2.f1 and - not t1.f1 = t2.f1; + WHERE t1.f1 && t2.f1 and not t1.f1 = t2.f1 + ORDER BY interval1, interval2; -- contains -SELECT '' AS five, t1.* +SELECT '' AS five, t1.f1 FROM TINTERVAL_TBL t1 WHERE not t1.f1 << - '["Aug 15 14:23:19 1980" "Sep 16 14:23:19 1990"]'::tinterval; + '["Aug 15 14:23:19 1980" "Sep 16 14:23:19 1990"]'::tinterval + ORDER BY t1.f1; -- make time interval -SELECT '' AS three, t1.* +SELECT '' AS three, t1.f1 FROM TINTERVAL_TBL t1 WHERE t1.f1 && ('Aug 15 14:23:19 1983'::abstime <#> - 'Sep 16 14:23:19 1983'::abstime); + 'Sep 16 14:23:19 1983'::abstime) + ORDER BY t1.f1; + +RESET geqo; |