Google Git
Sign in
apache / cloudberry / refs/heads/cbdb-postgres-merge / . / src / test / regress / expected / point.out
blob: 5275416ee501cb49b7e824a1650347808097cb44 [file] [log] [blame]
--
-- POINT
--
-- avoid bit-exact output here because operations may not be bit-exact.
SET extra_float_digits = 0;
INSERT INTO POINT_TBL(f1) VALUES ('asdfasdf');
ERROR: invalid input syntax for type point: "asdfasdf"
LINE 1: INSERT INTO POINT_TBL(f1) VALUES ('asdfasdf');
^
INSERT INTO POINT_TBL(f1) VALUES ('(10.0 10.0)');
ERROR: invalid input syntax for type point: "(10.0 10.0)"
LINE 1: INSERT INTO POINT_TBL(f1) VALUES ('(10.0 10.0)');
^
INSERT INTO POINT_TBL(f1) VALUES ('(10.0, 10.0) x');
ERROR: invalid input syntax for type point: "(10.0, 10.0) x"
LINE 1: INSERT INTO POINT_TBL(f1) VALUES ('(10.0, 10.0) x');
^
INSERT INTO POINT_TBL(f1) VALUES ('(10.0,10.0');
ERROR: invalid input syntax for type point: "(10.0,10.0"
LINE 1: INSERT INTO POINT_TBL(f1) VALUES ('(10.0,10.0');
^
INSERT INTO POINT_TBL(f1) VALUES ('(10.0, 1e+500)'); -- Out of range
ERROR: "1e+500" is out of range for type double precision
LINE 1: INSERT INTO POINT_TBL(f1) VALUES ('(10.0, 1e+500)');
^
SELECT * FROM POINT_TBL;
f1
-------------------
(0,0)
(-10,0)
(-3,4)
(5.1,34.5)
(-5,-12)
(1e-300,-1e-300)
(1e+300,Infinity)
(Infinity,1e+300)
(NaN,NaN)
(10,10)
(10 rows)
-- left of
SELECT p.* FROM POINT_TBL p WHERE p.f1 << '(0.0, 0.0)';
f1
----------
(-10,0)
(-3,4)
(-5,-12)
(3 rows)
-- right of
SELECT p.* FROM POINT_TBL p WHERE '(0.0,0.0)' >> p.f1;
f1
----------
(-10,0)
(-3,4)
(-5,-12)
(3 rows)
-- above
SELECT p.* FROM POINT_TBL p WHERE '(0.0,0.0)' |>> p.f1;
f1
----------
(-5,-12)
(1 row)
-- below
SELECT p.* FROM POINT_TBL p WHERE p.f1 <<| '(0.0, 0.0)';
f1
----------
(-5,-12)
(1 row)
-- equal
SELECT p.* FROM POINT_TBL p WHERE p.f1 ~= '(5.1, 34.5)';
f1
------------
(5.1,34.5)
(1 row)
-- point in box
SELECT p.* FROM POINT_TBL p
WHERE p.f1 <@ box '(0,0,100,100)';
f1
------------
(0,0)
(5.1,34.5)
(10,10)
(3 rows)
SELECT p.* FROM POINT_TBL p
WHERE box '(0,0,100,100)' @> p.f1;
f1
------------
(0,0)
(5.1,34.5)
(10,10)
(3 rows)
SELECT p.* FROM POINT_TBL p
WHERE not p.f1 <@ box '(0,0,100,100)';
f1
-------------------
(-10,0)
(-3,4)
(-5,-12)
(1e-300,-1e-300)
(1e+300,Infinity)
(Infinity,1e+300)
(NaN,NaN)
(7 rows)
SELECT p.* FROM POINT_TBL p
WHERE p.f1 <@ path '[(0,0),(-10,0),(-10,10)]';
f1
------------------
(0,0)
(-10,0)
(1e-300,-1e-300)
(3 rows)
SELECT p.* FROM POINT_TBL p
WHERE not box '(0,0,100,100)' @> p.f1;
f1
-------------------
(-10,0)
(-3,4)
(-5,-12)
(1e-300,-1e-300)
(1e+300,Infinity)
(Infinity,1e+300)
(NaN,NaN)
(7 rows)
SELECT p.f1, p.f1 <-> point '(0,0)' AS dist
FROM POINT_TBL p
ORDER BY dist;
f1 | dist
-------------------+----------------------
(0,0) | 0
(1e-300,-1e-300) | 1.4142135623731e-300
(-3,4) | 5
(-10,0) | 10
(-5,-12) | 13
(10,10) | 14.142135623731
(5.1,34.5) | 34.8749193547455
(1e+300,Infinity) | Infinity
(Infinity,1e+300) | Infinity
(NaN,NaN) | NaN
(10 rows)
SELECT p1.f1 AS point1, p2.f1 AS point2, p1.f1 <-> p2.f1 AS dist
FROM POINT_TBL p1, POINT_TBL p2
ORDER BY dist, p1.f1[0], p2.f1[0];
point1 | point2 | dist
-------------------+-------------------+----------------------
(-10,0) | (-10,0) | 0
(-5,-12) | (-5,-12) | 0
(-3,4) | (-3,4) | 0
(0,0) | (0,0) | 0
(1e-300,-1e-300) | (1e-300,-1e-300) | 0
(5.1,34.5) | (5.1,34.5) | 0
(10,10) | (10,10) | 0
(0,0) | (1e-300,-1e-300) | 1.4142135623731e-300
(1e-300,-1e-300) | (0,0) | 1.4142135623731e-300
(-3,4) | (0,0) | 5
(-3,4) | (1e-300,-1e-300) | 5
(0,0) | (-3,4) | 5
(1e-300,-1e-300) | (-3,4) | 5
(-10,0) | (-3,4) | 8.06225774829855
(-3,4) | (-10,0) | 8.06225774829855
(-10,0) | (0,0) | 10
(-10,0) | (1e-300,-1e-300) | 10
(0,0) | (-10,0) | 10
(1e-300,-1e-300) | (-10,0) | 10
(-10,0) | (-5,-12) | 13
(-5,-12) | (-10,0) | 13
(-5,-12) | (0,0) | 13
(-5,-12) | (1e-300,-1e-300) | 13
(0,0) | (-5,-12) | 13
(1e-300,-1e-300) | (-5,-12) | 13
(0,0) | (10,10) | 14.142135623731
(1e-300,-1e-300) | (10,10) | 14.142135623731
(10,10) | (0,0) | 14.142135623731
(10,10) | (1e-300,-1e-300) | 14.142135623731
(-3,4) | (10,10) | 14.3178210632764
(10,10) | (-3,4) | 14.3178210632764
(-5,-12) | (-3,4) | 16.1245154965971
(-3,4) | (-5,-12) | 16.1245154965971
(-10,0) | (10,10) | 22.3606797749979
(10,10) | (-10,0) | 22.3606797749979
(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
(0,0) | (5.1,34.5) | 34.8749193547455
(1e-300,-1e-300) | (5.1,34.5) | 34.8749193547455
(5.1,34.5) | (0,0) | 34.8749193547455
(5.1,34.5) | (1e-300,-1e-300) | 34.8749193547455
(-10,0) | (5.1,34.5) | 37.6597928831267
(5.1,34.5) | (-10,0) | 37.6597928831267
(-5,-12) | (5.1,34.5) | 47.5842410888311
(5.1,34.5) | (-5,-12) | 47.5842410888311
(-10,0) | (1e+300,Infinity) | Infinity
(-10,0) | (Infinity,1e+300) | Infinity
(-5,-12) | (1e+300,Infinity) | Infinity
(-5,-12) | (Infinity,1e+300) | Infinity
(-3,4) | (1e+300,Infinity) | Infinity
(-3,4) | (Infinity,1e+300) | Infinity
(0,0) | (1e+300,Infinity) | Infinity
(0,0) | (Infinity,1e+300) | Infinity
(1e-300,-1e-300) | (1e+300,Infinity) | Infinity
(1e-300,-1e-300) | (Infinity,1e+300) | Infinity
(5.1,34.5) | (1e+300,Infinity) | Infinity
(5.1,34.5) | (Infinity,1e+300) | Infinity
(10,10) | (1e+300,Infinity) | Infinity
(10,10) | (Infinity,1e+300) | Infinity
(1e+300,Infinity) | (-10,0) | Infinity
(1e+300,Infinity) | (-5,-12) | Infinity
(1e+300,Infinity) | (-3,4) | Infinity
(1e+300,Infinity) | (0,0) | Infinity
(1e+300,Infinity) | (1e-300,-1e-300) | Infinity
(1e+300,Infinity) | (5.1,34.5) | Infinity
(1e+300,Infinity) | (10,10) | Infinity
(1e+300,Infinity) | (Infinity,1e+300) | Infinity
(Infinity,1e+300) | (-10,0) | Infinity
(Infinity,1e+300) | (-5,-12) | Infinity
(Infinity,1e+300) | (-3,4) | Infinity
(Infinity,1e+300) | (0,0) | Infinity
(Infinity,1e+300) | (1e-300,-1e-300) | Infinity
(Infinity,1e+300) | (5.1,34.5) | Infinity
(Infinity,1e+300) | (10,10) | Infinity
(Infinity,1e+300) | (1e+300,Infinity) | Infinity
(-10,0) | (NaN,NaN) | NaN
(-5,-12) | (NaN,NaN) | NaN
(-3,4) | (NaN,NaN) | NaN
(0,0) | (NaN,NaN) | NaN
(1e-300,-1e-300) | (NaN,NaN) | NaN
(5.1,34.5) | (NaN,NaN) | NaN
(10,10) | (NaN,NaN) | NaN
(1e+300,Infinity) | (1e+300,Infinity) | NaN
(1e+300,Infinity) | (NaN,NaN) | NaN
(Infinity,1e+300) | (Infinity,1e+300) | NaN
(Infinity,1e+300) | (NaN,NaN) | NaN
(NaN,NaN) | (-10,0) | NaN
(NaN,NaN) | (-5,-12) | NaN
(NaN,NaN) | (-3,4) | NaN
(NaN,NaN) | (0,0) | NaN
(NaN,NaN) | (1e-300,-1e-300) | NaN
(NaN,NaN) | (5.1,34.5) | NaN
(NaN,NaN) | (10,10) | NaN
(NaN,NaN) | (1e+300,Infinity) | NaN
(NaN,NaN) | (Infinity,1e+300) | NaN
(NaN,NaN) | (NaN,NaN) | NaN
(100 rows)
SELECT p1.f1 AS point1, p2.f1 AS point2
FROM POINT_TBL p1, POINT_TBL p2
WHERE (p1.f1 <-> p2.f1) > 3;
point1 | point2
-------------------+-------------------
(0,0) | (-10,0)
(0,0) | (-3,4)
(0,0) | (5.1,34.5)
(0,0) | (-5,-12)
(0,0) | (1e+300,Infinity)
(0,0) | (Infinity,1e+300)
(0,0) | (NaN,NaN)
(0,0) | (10,10)
(-10,0) | (0,0)
(-10,0) | (-3,4)
(-10,0) | (5.1,34.5)
(-10,0) | (-5,-12)
(-10,0) | (1e-300,-1e-300)
(-10,0) | (1e+300,Infinity)
(-10,0) | (Infinity,1e+300)
(-10,0) | (NaN,NaN)
(-10,0) | (10,10)
(-3,4) | (0,0)
(-3,4) | (-10,0)
(-3,4) | (5.1,34.5)
(-3,4) | (-5,-12)
(-3,4) | (1e-300,-1e-300)
(-3,4) | (1e+300,Infinity)
(-3,4) | (Infinity,1e+300)
(-3,4) | (NaN,NaN)
(-3,4) | (10,10)
(5.1,34.5) | (0,0)
(5.1,34.5) | (-10,0)
(5.1,34.5) | (-3,4)
(5.1,34.5) | (-5,-12)
(5.1,34.5) | (1e-300,-1e-300)
(5.1,34.5) | (1e+300,Infinity)
(5.1,34.5) | (Infinity,1e+300)
(5.1,34.5) | (NaN,NaN)
(5.1,34.5) | (10,10)
(-5,-12) | (0,0)
(-5,-12) | (-10,0)
(-5,-12) | (-3,4)
(-5,-12) | (5.1,34.5)
(-5,-12) | (1e-300,-1e-300)
(-5,-12) | (1e+300,Infinity)
(-5,-12) | (Infinity,1e+300)
(-5,-12) | (NaN,NaN)
(-5,-12) | (10,10)
(1e-300,-1e-300) | (-10,0)
(1e-300,-1e-300) | (-3,4)
(1e-300,-1e-300) | (5.1,34.5)
(1e-300,-1e-300) | (-5,-12)
(1e-300,-1e-300) | (1e+300,Infinity)
(1e-300,-1e-300) | (Infinity,1e+300)
(1e-300,-1e-300) | (NaN,NaN)
(1e-300,-1e-300) | (10,10)
(1e+300,Infinity) | (0,0)
(1e+300,Infinity) | (-10,0)
(1e+300,Infinity) | (-3,4)
(1e+300,Infinity) | (5.1,34.5)
(1e+300,Infinity) | (-5,-12)
(1e+300,Infinity) | (1e-300,-1e-300)
(1e+300,Infinity) | (1e+300,Infinity)
(1e+300,Infinity) | (Infinity,1e+300)
(1e+300,Infinity) | (NaN,NaN)
(1e+300,Infinity) | (10,10)
(Infinity,1e+300) | (0,0)
(Infinity,1e+300) | (-10,0)
(Infinity,1e+300) | (-3,4)
(Infinity,1e+300) | (5.1,34.5)
(Infinity,1e+300) | (-5,-12)
(Infinity,1e+300) | (1e-300,-1e-300)
(Infinity,1e+300) | (1e+300,Infinity)
(Infinity,1e+300) | (Infinity,1e+300)
(Infinity,1e+300) | (NaN,NaN)
(Infinity,1e+300) | (10,10)
(NaN,NaN) | (0,0)
(NaN,NaN) | (-10,0)
(NaN,NaN) | (-3,4)
(NaN,NaN) | (5.1,34.5)
(NaN,NaN) | (-5,-12)
(NaN,NaN) | (1e-300,-1e-300)
(NaN,NaN) | (1e+300,Infinity)
(NaN,NaN) | (Infinity,1e+300)
(NaN,NaN) | (NaN,NaN)
(NaN,NaN) | (10,10)
(10,10) | (0,0)
(10,10) | (-10,0)
(10,10) | (-3,4)
(10,10) | (5.1,34.5)
(10,10) | (-5,-12)
(10,10) | (1e-300,-1e-300)
(10,10) | (1e+300,Infinity)
(10,10) | (Infinity,1e+300)
(10,10) | (NaN,NaN)
(91 rows)
-- put distance result into output to allow sorting with GEQ optimizer - tgl 97/05/10
SELECT 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
ORDER BY distance, p1.f1[0], p2.f1[0];
point1 | point2 | distance
-------------------+-------------------+------------------
(-3,4) | (0,0) | 5
(-3,4) | (1e-300,-1e-300) | 5
(-10,0) | (-3,4) | 8.06225774829855
(-10,0) | (0,0) | 10
(-10,0) | (1e-300,-1e-300) | 10
(-10,0) | (-5,-12) | 13
(-5,-12) | (0,0) | 13
(-5,-12) | (1e-300,-1e-300) | 13
(0,0) | (10,10) | 14.142135623731
(1e-300,-1e-300) | (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
(1e-300,-1e-300) | (5.1,34.5) | 34.8749193547455
(-10,0) | (5.1,34.5) | 37.6597928831267
(-5,-12) | (5.1,34.5) | 47.5842410888311
(-10,0) | (1e+300,Infinity) | Infinity
(-10,0) | (Infinity,1e+300) | Infinity
(-5,-12) | (1e+300,Infinity) | Infinity
(-5,-12) | (Infinity,1e+300) | Infinity
(-3,4) | (1e+300,Infinity) | Infinity
(-3,4) | (Infinity,1e+300) | Infinity
(0,0) | (1e+300,Infinity) | Infinity
(0,0) | (Infinity,1e+300) | Infinity
(1e-300,-1e-300) | (1e+300,Infinity) | Infinity
(1e-300,-1e-300) | (Infinity,1e+300) | Infinity
(5.1,34.5) | (1e+300,Infinity) | Infinity
(5.1,34.5) | (Infinity,1e+300) | Infinity
(10,10) | (1e+300,Infinity) | Infinity
(10,10) | (Infinity,1e+300) | Infinity
(1e+300,Infinity) | (Infinity,1e+300) | Infinity
(35 rows)
-- put distance result into output to allow sorting with GEQ optimizer - tgl 97/05/10
SELECT 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
ORDER BY distance;
point1 | point2 | distance
-------------------+-------------------+------------------
(-3,4) | (0,0) | 5
(-3,4) | (1e-300,-1e-300) | 5
(-10,0) | (-5,-12) | 13
(5.1,34.5) | (10,10) | 24.9851956166046
(1e+300,Infinity) | (Infinity,1e+300) | Infinity
(5 rows)
-- Test that GiST indexes provide same behavior as sequential scan
CREATE TEMP TABLE point_gist_tbl(f1 point);
INSERT INTO point_gist_tbl SELECT '(0,0)' FROM generate_series(0,1000);
CREATE INDEX point_gist_tbl_index ON point_gist_tbl USING gist (f1);
INSERT INTO point_gist_tbl VALUES ('(0.0000009,0.0000009)');
SET enable_seqscan TO true;
SET enable_indexscan TO false;
SET enable_bitmapscan TO false;
SELECT COUNT(*) FROM point_gist_tbl WHERE f1 ~= '(0.0000009,0.0000009)'::point;
count
-------
1002
(1 row)
SELECT COUNT(*) FROM point_gist_tbl WHERE f1 <@ '(0.0000009,0.0000009),(0.0000009,0.0000009)'::box;
count
-------
1
(1 row)
SELECT COUNT(*) FROM point_gist_tbl WHERE f1 ~= '(0.0000018,0.0000018)'::point;
count
-------
1
(1 row)
SET enable_seqscan TO false;
SET enable_indexscan TO true;
SET enable_bitmapscan TO true;
SELECT COUNT(*) FROM point_gist_tbl WHERE f1 ~= '(0.0000009,0.0000009)'::point;
count
-------
1002
(1 row)
SELECT COUNT(*) FROM point_gist_tbl WHERE f1 <@ '(0.0000009,0.0000009),(0.0000009,0.0000009)'::box;
count
-------
1
(1 row)
SELECT COUNT(*) FROM point_gist_tbl WHERE f1 ~= '(0.0000018,0.0000018)'::point;
count
-------
1
(1 row)
RESET enable_seqscan;
RESET enable_indexscan;
RESET enable_bitmapscan;
-- test non-error-throwing API for some core types
SELECT pg_input_is_valid('1,y', 'point');
pg_input_is_valid
-------------------
f
(1 row)
SELECT * FROM pg_input_error_info('1,y', 'point');
message | detail | hint | sql_error_code
--------------------------------------------+--------+------+----------------
invalid input syntax for type point: "1,y" | | | 22P02
(1 row)