5 -- first, define the datatype. Turn off echoing so that expected file
6 -- does not depend on contents of cube.sql.
10 -- testing the input and output functions
12 -- Any number (a one-dimensional point)
13 SELECT '1'::cube AS cube;
19 SELECT '-1'::cube AS cube;
25 SELECT '1.'::cube AS cube;
31 SELECT '-1.'::cube AS cube;
37 SELECT '.1'::cube AS cube;
43 SELECT '-.1'::cube AS cube;
44 ERROR: parse error, expecting `FLOAT' or `O_PAREN' or `O_BRACKET' at or before position 2, character ('.', \056), input: '-.1'
46 SELECT '1.0'::cube AS cube;
52 SELECT '-1.0'::cube AS cube;
58 SELECT '1e7'::cube AS cube;
64 SELECT '-1e7'::cube AS cube;
70 SELECT '1.0e7'::cube AS cube;
76 SELECT '-1.0e7'::cube AS cube;
82 SELECT '1e+7'::cube AS cube;
88 SELECT '-1e+7'::cube AS cube;
94 SELECT '1.0e+7'::cube AS cube;
100 SELECT '-1.0e+7'::cube AS cube;
106 SELECT '1e-7'::cube AS cube;
112 SELECT '-1e-7'::cube AS cube;
118 SELECT '1.0e-7'::cube AS cube;
124 SELECT '-1.0e-7'::cube AS cube;
130 SELECT '1e-700'::cube AS cube;
136 SELECT '-1e-700'::cube AS cube;
142 -- simple lists (points)
143 SELECT '1,2'::cube AS cube;
149 SELECT '(1,2)'::cube AS cube;
155 SELECT '1,2,3,4,5'::cube AS cube;
161 SELECT '(1,2,3,4,5)'::cube AS cube;
167 -- double lists (cubes)
168 SELECT '(0),(0)'::cube AS cube;
174 SELECT '(0),(1)'::cube AS cube;
180 SELECT '[(0),(0)]'::cube AS cube;
186 SELECT '[(0),(1)]'::cube AS cube;
192 SELECT '(0,0,0,0),(0,0,0,0)'::cube AS cube;
198 SELECT '(0,0,0,0),(1,0,0,0)'::cube AS cube;
200 ---------------------------
201 (0, 0, 0, 0),(1, 0, 0, 0)
204 SELECT '[(0,0,0,0),(0,0,0,0)]'::cube AS cube;
210 SELECT '[(0,0,0,0),(1,0,0,0)]'::cube AS cube;
212 ---------------------------
213 (0, 0, 0, 0),(1, 0, 0, 0)
216 -- invalid input: parse errors
217 SELECT ''::cube AS cube;
218 ERROR: cube_in: can't parse an empty string
219 SELECT 'ABC'::cube AS cube;
220 ERROR: parse error, expecting `FLOAT' or `O_PAREN' or `O_BRACKET' at or before position 1, character ('A', \101), input: 'ABC'
222 SELECT '()'::cube AS cube;
223 ERROR: parse error, expecting `FLOAT' at or before position 2, character (')', \051), input: '()'
225 SELECT '[]'::cube AS cube;
226 ERROR: parse error, expecting `O_PAREN' at or before position 2, character (']', \135), input: '[]'
228 SELECT '[()]'::cube AS cube;
229 ERROR: parse error, expecting `FLOAT' at or before position 3, character (')', \051), input: '[()]'
231 SELECT '[(1)]'::cube AS cube;
232 ERROR: parse error, expecting `COMMA' at or before position 5, character (']', \135), input: '[(1)]'
234 SELECT '[(1),]'::cube AS cube;
235 ERROR: parse error, expecting `O_PAREN' at or before position 6, character (']', \135), input: '[(1),]'
237 SELECT '[(1),2]'::cube AS cube;
238 ERROR: parse error, expecting `O_PAREN' at or before position 7, character (']', \135), input: '[(1),2]'
240 SELECT '[(1),(2),(3)]'::cube AS cube;
241 ERROR: parse error, expecting `C_BRACKET' at or before position 9, character (',', \054), input: '[(1),(2),(3)]'
243 SELECT '1,'::cube AS cube;
244 ERROR: parse error, expecting `FLOAT' at or before position 2, character (',', \054), input: '1,'
246 SELECT '1,2,'::cube AS cube;
247 ERROR: parse error, expecting `FLOAT' at or before position 4, character (',', \054), input: '1,2,'
249 SELECT '1,,2'::cube AS cube;
250 ERROR: parse error, expecting `FLOAT' at or before position 3, character (',', \054), input: '1,,2'
252 SELECT '(1,)'::cube AS cube;
253 ERROR: parse error, expecting `FLOAT' at or before position 4, character (')', \051), input: '(1,)'
255 SELECT '(1,2,)'::cube AS cube;
256 ERROR: parse error, expecting `FLOAT' at or before position 6, character (')', \051), input: '(1,2,)'
258 SELECT '(1,,2)'::cube AS cube;
259 ERROR: parse error, expecting `FLOAT' at or before position 4, character (',', \054), input: '(1,,2)'
261 -- invalid input: semantic errors and trailing garbage
262 SELECT '[(1),(2)],'::cube AS cube; -- 0
263 ERROR: (0) bad cube representation; garbage at or before char 9, (',', \054)
265 SELECT '[(1,2,3),(2,3)]'::cube AS cube; -- 1
266 ERROR: (1) bad cube representation; different point dimensions in (1,2,3) and (2,3)
268 SELECT '[(1,2),(1,2,3)]'::cube AS cube; -- 1
269 ERROR: (1) bad cube representation; different point dimensions in (1,2) and (1,2,3)
271 SELECT '(1),(2),'::cube AS cube; -- 2
272 ERROR: (2) bad cube representation; garbage at or before char 7, (',', \054)
274 SELECT '(1,2,3),(2,3)'::cube AS cube; -- 3
275 ERROR: (3) bad cube representation; different point dimensions in (1,2,3) and (2,3)
277 SELECT '(1,2),(1,2,3)'::cube AS cube; -- 3
278 ERROR: (3) bad cube representation; different point dimensions in (1,2) and (1,2,3)
280 SELECT '(1,2,3)ab'::cube AS cube; -- 4
281 ERROR: (4) bad cube representation; garbage at or before char 8, ('b', \142)
283 SELECT '(1,2,3)a'::cube AS cube; -- 5
284 ERROR: (5) bad cube representation; garbage at or before char 8, ('end of input', \000)
286 SELECT '(1,2)('::cube AS cube; -- 5
287 ERROR: (5) bad cube representation; garbage at or before char 6, ('end of input', \000)
289 SELECT '1,2ab'::cube AS cube; -- 6
290 ERROR: (6) bad cube representation; garbage at or before char 4, ('b', \142)
292 SELECT '1 e7'::cube AS cube; -- 6
293 ERROR: (6) bad cube representation; garbage at or before char 3, ('7', \067)
295 SELECT '1,2a'::cube AS cube; -- 7
296 ERROR: (7) bad cube representation; garbage at or before char 4, ('end of input', \000)
298 SELECT '1..2'::cube AS cube; -- 7
299 ERROR: (7) bad cube representation; garbage at or before char 4, ('end of input', \000)
302 -- testing the operators
304 -- equality/inequality:
306 SELECT '24, 33.20'::cube = '24, 33.20'::cube AS bool;
312 SELECT '24, 33.20'::cube != '24, 33.20'::cube AS bool;
318 SELECT '24, 33.20'::cube = '24, 33.21'::cube AS bool;
324 SELECT '24, 33.20'::cube != '24, 33.21'::cube AS bool;
330 SELECT '(2,0),(3,1)'::cube = '(2,0,0,0,0),(3,1,0,0,0)'::cube AS bool;
336 SELECT '(2,0),(3,1)'::cube = '(2,0,0,0,0),(3,1,0,0,1)'::cube AS bool;
342 -- "lower than" / "greater than"
343 -- (these operators are not useful for anything but ordering)
345 SELECT '1'::cube > '2'::cube AS bool;
351 SELECT '1'::cube < '2'::cube AS bool;
357 SELECT '1,1'::cube > '1,2'::cube AS bool;
363 SELECT '1,1'::cube < '1,2'::cube AS bool;
369 SELECT '(2,0),(3,1)'::cube > '(2,0,0,0,0),(3,1,0,0,1)'::cube AS bool;
375 SELECT '(2,0),(3,1)'::cube < '(2,0,0,0,0),(3,1,0,0,1)'::cube AS bool;
381 SELECT '(2,0),(3,1)'::cube > '(2,0,0,0,1),(3,1,0,0,0)'::cube AS bool;
387 SELECT '(2,0),(3,1)'::cube < '(2,0,0,0,1),(3,1,0,0,0)'::cube AS bool;
393 SELECT '(2,0),(3,1)'::cube > '(2,0,0,0,0),(3,1,0,0,0)'::cube AS bool;
399 SELECT '(2,0),(3,1)'::cube < '(2,0,0,0,0),(3,1,0,0,0)'::cube AS bool;
405 SELECT '(2,0,0,0,0),(3,1,0,0,1)'::cube > '(2,0),(3,1)'::cube AS bool;
411 SELECT '(2,0,0,0,0),(3,1,0,0,1)'::cube < '(2,0),(3,1)'::cube AS bool;
417 SELECT '(2,0,0,0,1),(3,1,0,0,0)'::cube > '(2,0),(3,1)'::cube AS bool;
423 SELECT '(2,0,0,0,1),(3,1,0,0,0)'::cube < '(2,0),(3,1)'::cube AS bool;
429 SELECT '(2,0,0,0,0),(3,1,0,0,0)'::cube > '(2,0),(3,1)'::cube AS bool;
435 SELECT '(2,0,0,0,0),(3,1,0,0,0)'::cube < '(2,0),(3,1)'::cube AS bool;
443 SELECT '1'::cube && '1'::cube AS bool;
449 SELECT '1'::cube && '2'::cube AS bool;
455 SELECT '[(-1,-1,-1),(1,1,1)]'::cube && '0'::cube AS bool;
461 SELECT '[(-1,-1,-1),(1,1,1)]'::cube && '1'::cube AS bool;
467 SELECT '[(-1,-1,-1),(1,1,1)]'::cube && '1,1,1'::cube AS bool;
473 SELECT '[(-1,-1,-1),(1,1,1)]'::cube && '[(1,1,1),(2,2,2)]'::cube AS bool;
479 SELECT '[(-1,-1,-1),(1,1,1)]'::cube && '[(1,1),(2,2)]'::cube AS bool;
485 SELECT '[(-1,-1,-1),(1,1,1)]'::cube && '[(2,1,1),(2,2,2)]'::cube AS bool;
491 -- "overlap on the left" / "overlap on the right"
492 -- (these operators are not useful at all but R-tree seems to be
493 -- sensitive to their presence)
495 SELECT '1'::cube &< '0'::cube AS bool;
501 SELECT '1'::cube &< '1'::cube AS bool;
507 SELECT '1'::cube &< '2'::cube AS bool;
513 SELECT '(0),(1)'::cube &< '0'::cube AS bool;
519 SELECT '(0),(1)'::cube &< '1'::cube AS bool;
525 SELECT '(0),(1)'::cube &< '(0),(0.5)'::cube AS bool;
531 SELECT '(0),(1)'::cube &< '(0),(1)'::cube AS bool;
537 SELECT '(0),(1)'::cube &< '(0),(2)'::cube AS bool;
543 SELECT '(0),(1)'::cube &< '(1),(2)'::cube AS bool;
549 SELECT '(0),(1)'::cube &< '(2),(3)'::cube AS bool;
555 SELECT '0'::cube &> '1'::cube AS bool;
561 SELECT '1'::cube &> '1'::cube AS bool;
567 SELECT '2'::cube &> '1'::cube AS bool;
573 SELECT '0'::cube &> '(0),(1)'::cube AS bool;
579 SELECT '1'::cube &> '(0),(1)'::cube AS bool;
585 SELECT '(0),(0.5)' &> '(0),(1)'::cube AS bool;
591 SELECT '(0),(1)'::cube &> '(0),(1)'::cube AS bool;
597 SELECT '(0),(2)'::cube &> '(0),(1)'::cube AS bool;
603 SELECT '(1),(2)'::cube &> '(0),(1)'::cube AS bool;
609 SELECT '(2),(3)'::cube &> '(0),(1)'::cube AS bool;
616 -- (these operators are not useful but for 1-D or 2-D cubes, but R-tree
617 -- seems to want them defined)
619 SELECT '1'::cube << '0'::cube AS bool;
625 SELECT '1'::cube << '1'::cube AS bool;
631 SELECT '1'::cube << '2'::cube AS bool;
637 SELECT '(0),(1)'::cube << '0'::cube AS bool;
643 SELECT '(0),(1)'::cube << '1'::cube AS bool;
649 SELECT '(0),(1)'::cube << '(0),(0.5)'::cube AS bool;
655 SELECT '(0),(1)'::cube << '(0),(1)'::cube AS bool;
661 SELECT '(0),(1)'::cube << '(0),(2)'::cube AS bool;
667 SELECT '(0),(1)'::cube << '(1),(2)'::cube AS bool;
673 SELECT '(0),(1)'::cube << '(2),(3)'::cube AS bool;
679 SELECT '0'::cube >> '1'::cube AS bool;
685 SELECT '1'::cube >> '1'::cube AS bool;
691 SELECT '2'::cube >> '1'::cube AS bool;
697 SELECT '0'::cube >> '(0),(1)'::cube AS bool;
703 SELECT '1'::cube >> '(0),(1)'::cube AS bool;
709 SELECT '(0),(0.5)' >> '(0),(1)'::cube AS bool;
715 SELECT '(0),(1)'::cube >> '(0),(1)'::cube AS bool;
721 SELECT '(0),(2)'::cube >> '(0),(1)'::cube AS bool;
727 SELECT '(1),(2)'::cube >> '(0),(1)'::cube AS bool;
733 SELECT '(2),(3)'::cube >> '(0),(1)'::cube AS bool;
739 -- "contained in" (the left operand is the cube entirely enclosed by
740 -- the right operand):
742 SELECT '0'::cube ~ '0'::cube AS bool;
748 SELECT '0,0,0'::cube ~ '0,0,0'::cube AS bool;
754 SELECT '0,0'::cube ~ '0,0,1'::cube AS bool;
760 SELECT '0,0,0'::cube ~ '0,0,1'::cube AS bool;
766 SELECT '1,0,0'::cube ~ '0,0,1'::cube AS bool;
772 SELECT '(1,0,0),(0,0,1)'::cube ~ '(1,0,0),(0,0,1)'::cube AS bool;
778 SELECT '(1,0,0),(0,0,1)'::cube ~ '(-1,-1,-1),(1,1,1)'::cube AS bool;
784 SELECT '(1,0,0),(0,0,1)'::cube ~ '(-1,-1,-1,-1),(1,1,1,1)'::cube AS bool;
790 SELECT '0'::cube ~ '(-1),(1)'::cube AS bool;
796 SELECT '1'::cube ~ '(-1),(1)'::cube AS bool;
802 SELECT '-1'::cube ~ '(-1),(1)'::cube AS bool;
808 SELECT '(-1),(1)'::cube ~ '(-1),(1)'::cube AS bool;
814 SELECT '(-1),(1)'::cube ~ '(-1,-1),(1,1)'::cube AS bool;
820 SELECT '(-2),(1)'::cube ~ '(-1),(1)'::cube AS bool;
826 SELECT '(-2),(1)'::cube ~ '(-1,-1),(1,1)'::cube AS bool;
832 -- "contains" (the left operand is the cube that entirely encloses the
835 SELECT '0'::cube @ '0'::cube AS bool;
841 SELECT '0,0,0'::cube @ '0,0,0'::cube AS bool;
847 SELECT '0,0,1'::cube @ '0,0'::cube AS bool;
853 SELECT '0,0,1'::cube @ '0,0,0'::cube AS bool;
859 SELECT '0,0,1'::cube @ '1,0,0'::cube AS bool;
865 SELECT '(1,0,0),(0,0,1)'::cube @ '(1,0,0),(0,0,1)'::cube AS bool;
871 SELECT '(-1,-1,-1),(1,1,1)'::cube @ '(1,0,0),(0,0,1)'::cube AS bool;
877 SELECT '(-1,-1,-1,-1),(1,1,1,1)'::cube @ '(1,0,0),(0,0,1)'::cube AS bool;
883 SELECT '(-1),(1)'::cube @ '0'::cube AS bool;
889 SELECT '(-1),(1)'::cube @ '1'::cube AS bool;
895 SELECT '(-1),(1)'::cube @ '-1'::cube AS bool;
901 SELECT '(-1),(1)'::cube @ '(-1),(1)'::cube AS bool;
907 SELECT '(-1,-1),(1,1)'::cube @ '(-1),(1)'::cube AS bool;
913 SELECT '(-1),(1)'::cube @ '(-2),(1)'::cube AS bool;
919 SELECT '(-1,-1),(1,1)'::cube @ '(-2),(1)'::cube AS bool;
925 -- Load some example data and build the index
927 CREATE TABLE test_cube (c cube);
928 \copy test_cube from 'data/test_cube.data'
929 CREATE INDEX test_cube_ix ON test_cube USING gist (c);
930 SELECT * FROM test_cube WHERE c && '(3000,1000),(0,0)';
932 --------------------------
933 (2424, 160),(2424, 81)
934 (759, 187),(662, 163)
935 (1444, 403),(1346, 344)
936 (337, 455),(240, 359)
937 (1594, 1043),(1517, 971)
941 SELECT * FROM test_cube WHERE c && '(3000,1000),(0,0)' GROUP BY c;
943 --------------------------
944 (337, 455),(240, 359)
945 (759, 187),(662, 163)
946 (1444, 403),(1346, 344)
947 (1594, 1043),(1517, 971)
948 (2424, 160),(2424, 81)