5 -- Check whether any of our opclasses fail amvalidate
7 FROM pg_opclass opc LEFT JOIN pg_am am ON am.oid = opcmethod
8 WHERE opc.oid >= 16384 AND NOT amvalidate(opc.oid);
14 -- testing the input and output functions
16 -- Any number (a one-dimensional point)
17 SELECT '1'::cube AS cube;
23 SELECT '-1'::cube AS cube;
29 SELECT '1.'::cube AS cube;
35 SELECT '-1.'::cube AS cube;
41 SELECT '.1'::cube AS cube;
47 SELECT '-.1'::cube AS cube;
53 SELECT '1.0'::cube AS cube;
59 SELECT '-1.0'::cube AS cube;
65 SELECT '1e27'::cube AS cube;
71 SELECT '-1e27'::cube AS cube;
77 SELECT '1.0e27'::cube AS cube;
83 SELECT '-1.0e27'::cube AS cube;
89 SELECT '1e+27'::cube AS cube;
95 SELECT '-1e+27'::cube AS cube;
101 SELECT '1.0e+27'::cube AS cube;
107 SELECT '-1.0e+27'::cube AS cube;
113 SELECT '1e-7'::cube AS cube;
119 SELECT '-1e-7'::cube AS cube;
125 SELECT '1.0e-7'::cube AS cube;
131 SELECT '-1.0e-7'::cube AS cube;
137 SELECT '1e-300'::cube AS cube;
143 SELECT '-1e-300'::cube AS cube;
149 SELECT 'infinity'::cube AS cube;
155 SELECT '-infinity'::cube AS cube;
161 SELECT 'NaN'::cube AS cube;
167 SELECT '1234567890123456'::cube AS cube;
169 ------------------------
170 (1.23456789012346e+15)
173 SELECT '+1234567890123456'::cube AS cube;
175 ------------------------
176 (1.23456789012346e+15)
179 SELECT '-1234567890123456'::cube AS cube;
181 -------------------------
182 (-1.23456789012346e+15)
185 SELECT '.1234567890123456'::cube AS cube;
187 ---------------------
191 SELECT '+.1234567890123456'::cube AS cube;
193 ---------------------
197 SELECT '-.1234567890123456'::cube AS cube;
199 ----------------------
203 -- simple lists (points)
204 SELECT '()'::cube AS cube;
210 SELECT '1,2'::cube AS cube;
216 SELECT '(1,2)'::cube AS cube;
222 SELECT '1,2,3,4,5'::cube AS cube;
228 SELECT '(1,2,3,4,5)'::cube AS cube;
234 -- double lists (cubes)
235 SELECT '(),()'::cube AS cube;
241 SELECT '(0),(0)'::cube AS cube;
247 SELECT '(0),(1)'::cube AS cube;
253 SELECT '[(0),(0)]'::cube AS cube;
259 SELECT '[(0),(1)]'::cube AS cube;
265 SELECT '(0,0,0,0),(0,0,0,0)'::cube AS cube;
271 SELECT '(0,0,0,0),(1,0,0,0)'::cube AS cube;
273 ---------------------------
274 (0, 0, 0, 0),(1, 0, 0, 0)
277 SELECT '[(0,0,0,0),(0,0,0,0)]'::cube AS cube;
283 SELECT '[(0,0,0,0),(1,0,0,0)]'::cube AS cube;
285 ---------------------------
286 (0, 0, 0, 0),(1, 0, 0, 0)
289 -- invalid input: parse errors
290 SELECT ''::cube AS cube;
291 ERROR: invalid input syntax for cube
292 LINE 1: SELECT ''::cube AS cube;
294 DETAIL: syntax error at end of input
295 SELECT 'ABC'::cube AS cube;
296 ERROR: invalid input syntax for cube
297 LINE 1: SELECT 'ABC'::cube AS cube;
299 DETAIL: syntax error at or near "A"
300 SELECT '[]'::cube AS cube;
301 ERROR: invalid input syntax for cube
302 LINE 1: SELECT '[]'::cube AS cube;
304 DETAIL: syntax error at or near "]"
305 SELECT '[()]'::cube AS cube;
306 ERROR: invalid input syntax for cube
307 LINE 1: SELECT '[()]'::cube AS cube;
309 DETAIL: syntax error at or near "]"
310 SELECT '[(1)]'::cube AS cube;
311 ERROR: invalid input syntax for cube
312 LINE 1: SELECT '[(1)]'::cube AS cube;
314 DETAIL: syntax error at or near "]"
315 SELECT '[(1),]'::cube AS cube;
316 ERROR: invalid input syntax for cube
317 LINE 1: SELECT '[(1),]'::cube AS cube;
319 DETAIL: syntax error at or near "]"
320 SELECT '[(1),2]'::cube AS cube;
321 ERROR: invalid input syntax for cube
322 LINE 1: SELECT '[(1),2]'::cube AS cube;
324 DETAIL: syntax error at or near "2"
325 SELECT '[(1),(2),(3)]'::cube AS cube;
326 ERROR: invalid input syntax for cube
327 LINE 1: SELECT '[(1),(2),(3)]'::cube AS cube;
329 DETAIL: syntax error at or near ","
330 SELECT '1,'::cube AS cube;
331 ERROR: invalid input syntax for cube
332 LINE 1: SELECT '1,'::cube AS cube;
334 DETAIL: syntax error at end of input
335 SELECT '1,2,'::cube AS cube;
336 ERROR: invalid input syntax for cube
337 LINE 1: SELECT '1,2,'::cube AS cube;
339 DETAIL: syntax error at end of input
340 SELECT '1,,2'::cube AS cube;
341 ERROR: invalid input syntax for cube
342 LINE 1: SELECT '1,,2'::cube AS cube;
344 DETAIL: syntax error at or near ","
345 SELECT '(1,)'::cube AS cube;
346 ERROR: invalid input syntax for cube
347 LINE 1: SELECT '(1,)'::cube AS cube;
349 DETAIL: syntax error at or near ")"
350 SELECT '(1,2,)'::cube AS cube;
351 ERROR: invalid input syntax for cube
352 LINE 1: SELECT '(1,2,)'::cube AS cube;
354 DETAIL: syntax error at or near ")"
355 SELECT '(1,,2)'::cube AS cube;
356 ERROR: invalid input syntax for cube
357 LINE 1: SELECT '(1,,2)'::cube AS cube;
359 DETAIL: syntax error at or near ","
360 -- invalid input: semantic errors and trailing garbage
361 SELECT '[(1),(2)],'::cube AS cube; -- 0
362 ERROR: invalid input syntax for cube
363 LINE 1: SELECT '[(1),(2)],'::cube AS cube;
365 DETAIL: syntax error at or near ","
366 SELECT '[(1,2,3),(2,3)]'::cube AS cube; -- 1
367 ERROR: invalid input syntax for cube
368 LINE 1: SELECT '[(1,2,3),(2,3)]'::cube AS cube;
370 DETAIL: Different point dimensions in (1,2,3) and (2,3).
371 SELECT '[(1,2),(1,2,3)]'::cube AS cube; -- 1
372 ERROR: invalid input syntax for cube
373 LINE 1: SELECT '[(1,2),(1,2,3)]'::cube AS cube;
375 DETAIL: Different point dimensions in (1,2) and (1,2,3).
376 SELECT '(1),(2),'::cube AS cube; -- 2
377 ERROR: invalid input syntax for cube
378 LINE 1: SELECT '(1),(2),'::cube AS cube;
380 DETAIL: syntax error at or near ","
381 SELECT '(1,2,3),(2,3)'::cube AS cube; -- 3
382 ERROR: invalid input syntax for cube
383 LINE 1: SELECT '(1,2,3),(2,3)'::cube AS cube;
385 DETAIL: Different point dimensions in (1,2,3) and (2,3).
386 SELECT '(1,2),(1,2,3)'::cube AS cube; -- 3
387 ERROR: invalid input syntax for cube
388 LINE 1: SELECT '(1,2),(1,2,3)'::cube AS cube;
390 DETAIL: Different point dimensions in (1,2) and (1,2,3).
391 SELECT '(1,2,3)ab'::cube AS cube; -- 4
392 ERROR: invalid input syntax for cube
393 LINE 1: SELECT '(1,2,3)ab'::cube AS cube;
395 DETAIL: syntax error at or near "a"
396 SELECT '(1,2,3)a'::cube AS cube; -- 5
397 ERROR: invalid input syntax for cube
398 LINE 1: SELECT '(1,2,3)a'::cube AS cube;
400 DETAIL: syntax error at or near "a"
401 SELECT '(1,2)('::cube AS cube; -- 5
402 ERROR: invalid input syntax for cube
403 LINE 1: SELECT '(1,2)('::cube AS cube;
405 DETAIL: syntax error at or near "("
406 SELECT '1,2ab'::cube AS cube; -- 6
407 ERROR: invalid input syntax for cube
408 LINE 1: SELECT '1,2ab'::cube AS cube;
410 DETAIL: syntax error at or near "a"
411 SELECT '1 e7'::cube AS cube; -- 6
412 ERROR: invalid input syntax for cube
413 LINE 1: SELECT '1 e7'::cube AS cube;
415 DETAIL: syntax error at or near "e"
416 SELECT '1,2a'::cube AS cube; -- 7
417 ERROR: invalid input syntax for cube
418 LINE 1: SELECT '1,2a'::cube AS cube;
420 DETAIL: syntax error at or near "a"
421 SELECT '1..2'::cube AS cube; -- 7
422 ERROR: invalid input syntax for cube
423 LINE 1: SELECT '1..2'::cube AS cube;
425 DETAIL: syntax error at or near ".2"
426 SELECT '-1e-700'::cube AS cube; -- out of range
427 ERROR: "-1e-700" is out of range for type double precision
428 LINE 1: SELECT '-1e-700'::cube AS cube;
431 -- Testing building cubes from float8 values
433 SELECT cube(0::float8);
439 SELECT cube(1::float8);
451 SELECT cube(cube(1,2),3);
457 SELECT cube(cube(1,2),3,4);
463 SELECT cube(cube(cube(1,2),3,4),5);
465 ---------------------
469 SELECT cube(cube(cube(1,2),3,4),5,6);
471 ---------------------
476 -- Test that the text -> cube cast was installed.
478 SELECT '(0)'::text::cube;
485 -- Test the float[] -> cube cast
487 SELECT cube('{0,1,2}'::float[], '{3,4,5}'::float[]);
489 ---------------------
493 SELECT cube('{0,1,2}'::float[], '{3}'::float[]);
494 ERROR: UR and LL arrays must be of same length
495 SELECT cube(NULL::float[], '{3}'::float[]);
501 SELECT cube('{0,1,2}'::float[]);
507 SELECT cube_subset(cube('(1,3,5),(6,7,8)'), ARRAY[3,2,1,1]);
509 ---------------------------
510 (5, 3, 1, 1),(8, 7, 6, 6)
513 SELECT cube_subset(cube('(1,3,5),(1,3,5)'), ARRAY[3,2,1,1]);
519 SELECT cube_subset(cube('(1,3,5),(6,7,8)'), ARRAY[4,0]);
520 ERROR: Index out of bounds
521 SELECT cube_subset(cube('(6,7,8),(6,7,8)'), ARRAY[4,0]);
522 ERROR: Index out of bounds
524 -- Test point processing
526 SELECT cube('(1,2),(1,2)'); -- cube_in
532 SELECT cube('{0,1,2}'::float[], '{0,1,2}'::float[]); -- cube_a_f8_f8
538 SELECT cube('{5,6,7,8}'::float[]); -- cube_a_f8
544 SELECT cube(1.37); -- cube_f8
550 SELECT cube(1.37, 1.37); -- cube_f8_f8
556 SELECT cube(cube(1,1), 42); -- cube_c_f8
562 SELECT cube(cube(1,2), 42); -- cube_c_f8
568 SELECT cube(cube(1,1), 42, 42); -- cube_c_f8_f8
574 SELECT cube(cube(1,1), 42, 24); -- cube_c_f8_f8
580 SELECT cube(cube(1,2), 42, 42); -- cube_c_f8_f8
586 SELECT cube(cube(1,2), 42, 24); -- cube_c_f8_f8
593 -- Testing limit of CUBE_MAX_DIM dimensions check in cube_in.
595 select '(0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0)'::cube;
596 ERROR: invalid input syntax for cube
597 LINE 1: select '(0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0...
599 DETAIL: A cube cannot have more than 100 dimensions.
600 select '(0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0),(0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0)'::cube;
601 ERROR: invalid input syntax for cube
602 LINE 1: select '(0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0...
604 DETAIL: A cube cannot have more than 100 dimensions.
606 -- testing the operators
608 -- equality/inequality:
610 SELECT '24, 33.20'::cube = '24, 33.20'::cube AS bool;
616 SELECT '24, 33.20'::cube != '24, 33.20'::cube AS bool;
622 SELECT '24, 33.20'::cube = '24, 33.21'::cube AS bool;
628 SELECT '24, 33.20'::cube != '24, 33.21'::cube AS bool;
634 SELECT '(2,0),(3,1)'::cube = '(2,0,0,0,0),(3,1,0,0,0)'::cube AS bool;
640 SELECT '(2,0),(3,1)'::cube = '(2,0,0,0,0),(3,1,0,0,1)'::cube AS bool;
646 -- "lower than" / "greater than"
647 -- (these operators are not useful for anything but ordering)
649 SELECT '1'::cube > '2'::cube AS bool;
655 SELECT '1'::cube < '2'::cube AS bool;
661 SELECT '1,1'::cube > '1,2'::cube AS bool;
667 SELECT '1,1'::cube < '1,2'::cube AS bool;
673 SELECT '(2,0),(3,1)'::cube > '(2,0,0,0,0),(3,1,0,0,1)'::cube AS bool;
679 SELECT '(2,0),(3,1)'::cube < '(2,0,0,0,0),(3,1,0,0,1)'::cube AS bool;
685 SELECT '(2,0),(3,1)'::cube > '(2,0,0,0,1),(3,1,0,0,0)'::cube AS bool;
691 SELECT '(2,0),(3,1)'::cube < '(2,0,0,0,1),(3,1,0,0,0)'::cube AS bool;
697 SELECT '(2,0),(3,1)'::cube > '(2,0,0,0,0),(3,1,0,0,0)'::cube AS bool;
703 SELECT '(2,0),(3,1)'::cube < '(2,0,0,0,0),(3,1,0,0,0)'::cube AS bool;
709 SELECT '(2,0,0,0,0),(3,1,0,0,1)'::cube > '(2,0),(3,1)'::cube AS bool;
715 SELECT '(2,0,0,0,0),(3,1,0,0,1)'::cube < '(2,0),(3,1)'::cube AS bool;
721 SELECT '(2,0,0,0,1),(3,1,0,0,0)'::cube > '(2,0),(3,1)'::cube AS bool;
727 SELECT '(2,0,0,0,1),(3,1,0,0,0)'::cube < '(2,0),(3,1)'::cube AS bool;
733 SELECT '(2,0,0,0,0),(3,1,0,0,0)'::cube > '(2,0),(3,1)'::cube AS bool;
739 SELECT '(2,0,0,0,0),(3,1,0,0,0)'::cube < '(2,0),(3,1)'::cube AS bool;
747 SELECT '1'::cube && '1'::cube AS bool;
753 SELECT '1'::cube && '2'::cube AS bool;
759 SELECT '[(-1,-1,-1),(1,1,1)]'::cube && '0'::cube AS bool;
765 SELECT '[(-1,-1,-1),(1,1,1)]'::cube && '1'::cube AS bool;
771 SELECT '[(-1,-1,-1),(1,1,1)]'::cube && '1,1,1'::cube AS bool;
777 SELECT '[(-1,-1,-1),(1,1,1)]'::cube && '[(1,1,1),(2,2,2)]'::cube AS bool;
783 SELECT '[(-1,-1,-1),(1,1,1)]'::cube && '[(1,1),(2,2)]'::cube AS bool;
789 SELECT '[(-1,-1,-1),(1,1,1)]'::cube && '[(2,1,1),(2,2,2)]'::cube AS bool;
795 -- "contained in" (the left operand is the cube entirely enclosed by
796 -- the right operand):
798 SELECT '0'::cube <@ '0'::cube AS bool;
804 SELECT '0,0,0'::cube <@ '0,0,0'::cube AS bool;
810 SELECT '0,0'::cube <@ '0,0,1'::cube AS bool;
816 SELECT '0,0,0'::cube <@ '0,0,1'::cube AS bool;
822 SELECT '1,0,0'::cube <@ '0,0,1'::cube AS bool;
828 SELECT '(1,0,0),(0,0,1)'::cube <@ '(1,0,0),(0,0,1)'::cube AS bool;
834 SELECT '(1,0,0),(0,0,1)'::cube <@ '(-1,-1,-1),(1,1,1)'::cube AS bool;
840 SELECT '(1,0,0),(0,0,1)'::cube <@ '(-1,-1,-1,-1),(1,1,1,1)'::cube AS bool;
846 SELECT '0'::cube <@ '(-1),(1)'::cube AS bool;
852 SELECT '1'::cube <@ '(-1),(1)'::cube AS bool;
858 SELECT '-1'::cube <@ '(-1),(1)'::cube AS bool;
864 SELECT '(-1),(1)'::cube <@ '(-1),(1)'::cube AS bool;
870 SELECT '(-1),(1)'::cube <@ '(-1,-1),(1,1)'::cube AS bool;
876 SELECT '(-2),(1)'::cube <@ '(-1),(1)'::cube AS bool;
882 SELECT '(-2),(1)'::cube <@ '(-1,-1),(1,1)'::cube AS bool;
888 -- "contains" (the left operand is the cube that entirely encloses the
891 SELECT '0'::cube @> '0'::cube AS bool;
897 SELECT '0,0,0'::cube @> '0,0,0'::cube AS bool;
903 SELECT '0,0,1'::cube @> '0,0'::cube AS bool;
909 SELECT '0,0,1'::cube @> '0,0,0'::cube AS bool;
915 SELECT '0,0,1'::cube @> '1,0,0'::cube AS bool;
921 SELECT '(1,0,0),(0,0,1)'::cube @> '(1,0,0),(0,0,1)'::cube AS bool;
927 SELECT '(-1,-1,-1),(1,1,1)'::cube @> '(1,0,0),(0,0,1)'::cube AS bool;
933 SELECT '(-1,-1,-1,-1),(1,1,1,1)'::cube @> '(1,0,0),(0,0,1)'::cube AS bool;
939 SELECT '(-1),(1)'::cube @> '0'::cube AS bool;
945 SELECT '(-1),(1)'::cube @> '1'::cube AS bool;
951 SELECT '(-1),(1)'::cube @> '-1'::cube AS bool;
957 SELECT '(-1),(1)'::cube @> '(-1),(1)'::cube AS bool;
963 SELECT '(-1,-1),(1,1)'::cube @> '(-1),(1)'::cube AS bool;
969 SELECT '(-1),(1)'::cube @> '(-2),(1)'::cube AS bool;
975 SELECT '(-1,-1),(1,1)'::cube @> '(-2),(1)'::cube AS bool;
981 -- Test of distance function
983 SELECT cube_distance('(0)'::cube,'(2,2,2,2)'::cube);
989 SELECT cube_distance('(0)'::cube,'(.3,.4)'::cube);
995 SELECT cube_distance('(2,3,4)'::cube,'(2,3,4)'::cube);
1001 SELECT cube_distance('(42,42,42,42)'::cube,'(137,137,137,137)'::cube);
1007 SELECT cube_distance('(42,42,42)'::cube,'(137,137)'::cube);
1013 -- Test of cube function (text to cube)
1015 SELECT cube('(1,1.2)'::text);
1027 -- Test of cube_dim function (dimensions stored in cube)
1029 SELECT cube_dim('(0)'::cube);
1035 SELECT cube_dim('(0,0)'::cube);
1041 SELECT cube_dim('(0,0,0)'::cube);
1047 SELECT cube_dim('(42,42,42),(42,42,42)'::cube);
1053 SELECT cube_dim('(4,8,15,16,23),(4,8,15,16,23)'::cube);
1059 -- Test of cube_ll_coord function (retrieves LL coordinate values)
1061 SELECT cube_ll_coord('(-1,1),(2,-2)'::cube, 1);
1067 SELECT cube_ll_coord('(-1,1),(2,-2)'::cube, 2);
1073 SELECT cube_ll_coord('(-1,1),(2,-2)'::cube, 3);
1079 SELECT cube_ll_coord('(1,2),(1,2)'::cube, 1);
1085 SELECT cube_ll_coord('(1,2),(1,2)'::cube, 2);
1091 SELECT cube_ll_coord('(1,2),(1,2)'::cube, 3);
1097 SELECT cube_ll_coord('(42,137)'::cube, 1);
1103 SELECT cube_ll_coord('(42,137)'::cube, 2);
1109 SELECT cube_ll_coord('(42,137)'::cube, 3);
1115 -- Test of cube_ur_coord function (retrieves UR coordinate values)
1117 SELECT cube_ur_coord('(-1,1),(2,-2)'::cube, 1);
1123 SELECT cube_ur_coord('(-1,1),(2,-2)'::cube, 2);
1129 SELECT cube_ur_coord('(-1,1),(2,-2)'::cube, 3);
1135 SELECT cube_ur_coord('(1,2),(1,2)'::cube, 1);
1141 SELECT cube_ur_coord('(1,2),(1,2)'::cube, 2);
1147 SELECT cube_ur_coord('(1,2),(1,2)'::cube, 3);
1153 SELECT cube_ur_coord('(42,137)'::cube, 1);
1159 SELECT cube_ur_coord('(42,137)'::cube, 2);
1165 SELECT cube_ur_coord('(42,137)'::cube, 3);
1171 -- Test of cube_is_point
1173 SELECT cube_is_point('(0)'::cube);
1179 SELECT cube_is_point('(0,1,2)'::cube);
1185 SELECT cube_is_point('(0,1,2),(0,1,2)'::cube);
1191 SELECT cube_is_point('(0,1,2),(-1,1,2)'::cube);
1197 SELECT cube_is_point('(0,1,2),(0,-1,2)'::cube);
1203 SELECT cube_is_point('(0,1,2),(0,1,-2)'::cube);
1209 -- Test of cube_enlarge (enlarging and shrinking cubes)
1211 SELECT cube_enlarge('(0)'::cube, 0, 0);
1217 SELECT cube_enlarge('(0)'::cube, 0, 1);
1223 SELECT cube_enlarge('(0)'::cube, 0, 2);
1229 SELECT cube_enlarge('(2),(-2)'::cube, 0, 4);
1235 SELECT cube_enlarge('(0)'::cube, 1, 0);
1241 SELECT cube_enlarge('(0)'::cube, 1, 1);
1247 SELECT cube_enlarge('(0)'::cube, 1, 2);
1253 SELECT cube_enlarge('(2),(-2)'::cube, 1, 4);
1255 -------------------------------
1256 (-3, -1, -1, -1),(3, 1, 1, 1)
1259 SELECT cube_enlarge('(0)'::cube, -1, 0);
1265 SELECT cube_enlarge('(0)'::cube, -1, 1);
1271 SELECT cube_enlarge('(0)'::cube, -1, 2);
1277 SELECT cube_enlarge('(2),(-2)'::cube, -1, 4);
1283 SELECT cube_enlarge('(0,0,0)'::cube, 1, 0);
1285 ------------------------
1286 (-1, -1, -1),(1, 1, 1)
1289 SELECT cube_enlarge('(0,0,0)'::cube, 1, 2);
1291 ------------------------
1292 (-1, -1, -1),(1, 1, 1)
1295 SELECT cube_enlarge('(2,-2),(-3,7)'::cube, 1, 2);
1301 SELECT cube_enlarge('(2,-2),(-3,7)'::cube, 3, 2);
1307 SELECT cube_enlarge('(2,-2),(-3,7)'::cube, -1, 2);
1313 SELECT cube_enlarge('(2,-2),(-3,7)'::cube, -3, 2);
1315 ---------------------
1319 SELECT cube_enlarge('(42,-23,-23),(42,23,23)'::cube, -23, 5);
1325 SELECT cube_enlarge('(42,-23,-23),(42,23,23)'::cube, -24, 5);
1331 -- Test of cube_union (MBR for two cubes)
1333 SELECT cube_union('(1,2),(3,4)'::cube, '(5,6,7),(8,9,10)'::cube);
1335 ----------------------
1336 (1, 2, 0),(8, 9, 10)
1339 SELECT cube_union('(1,2)'::cube, '(4,2,0,0)'::cube);
1341 ---------------------------
1342 (1, 2, 0, 0),(4, 2, 0, 0)
1345 SELECT cube_union('(1,2),(1,2)'::cube, '(4,2),(4,2)'::cube);
1351 SELECT cube_union('(1,2),(1,2)'::cube, '(1,2),(1,2)'::cube);
1357 SELECT cube_union('(1,2),(1,2)'::cube, '(1,2,0),(1,2,0)'::cube);
1363 -- Test of cube_inter
1365 SELECT cube_inter('(1,2),(10,11)'::cube, '(3,4), (16,15)'::cube); -- intersects
1371 SELECT cube_inter('(1,2),(10,11)'::cube, '(3,4), (6,5)'::cube); -- includes
1377 SELECT cube_inter('(1,2),(10,11)'::cube, '(13,14), (16,15)'::cube); -- no intersection
1383 SELECT cube_inter('(1,2),(10,11)'::cube, '(3,14), (16,15)'::cube); -- no intersection, but one dimension intersects
1389 SELECT cube_inter('(1,2),(10,11)'::cube, '(10,11), (16,15)'::cube); -- point intersection
1395 SELECT cube_inter('(1,2,3)'::cube, '(1,2,3)'::cube); -- point args
1401 SELECT cube_inter('(1,2,3)'::cube, '(5,6,3)'::cube); -- point args
1403 ---------------------
1407 -- Test of cube_size
1409 SELECT cube_size('(4,8),(15,16)'::cube);
1415 SELECT cube_size('(42,137)'::cube);
1421 -- Test of distances
1423 SELECT cube_distance('(1,1)'::cube, '(4,5)'::cube);
1429 SELECT '(1,1)'::cube <-> '(4,5)'::cube as d_e;
1435 SELECT distance_chebyshev('(1,1)'::cube, '(4,5)'::cube);
1437 --------------------
1441 SELECT '(1,1)'::cube <=> '(4,5)'::cube as d_c;
1447 SELECT distance_taxicab('(1,1)'::cube, '(4,5)'::cube);
1453 SELECT '(1,1)'::cube <#> '(4,5)'::cube as d_t;
1459 -- zero for overlapping
1460 SELECT cube_distance('(2,2),(10,10)'::cube, '(0,0),(5,5)'::cube);
1466 SELECT distance_chebyshev('(2,2),(10,10)'::cube, '(0,0),(5,5)'::cube);
1468 --------------------
1472 SELECT distance_taxicab('(2,2),(10,10)'::cube, '(0,0),(5,5)'::cube);
1478 -- coordinate access
1479 SELECT cube(array[10,20,30], array[40,50,60])->1;
1485 SELECT cube(array[40,50,60], array[10,20,30])->1;
1491 SELECT cube(array[10,20,30], array[40,50,60])->6;
1497 SELECT cube(array[10,20,30], array[40,50,60])->0;
1498 ERROR: cube index 0 is out of bounds
1499 SELECT cube(array[10,20,30], array[40,50,60])->7;
1500 ERROR: cube index 7 is out of bounds
1501 SELECT cube(array[10,20,30], array[40,50,60])->-1;
1502 ERROR: cube index -1 is out of bounds
1503 SELECT cube(array[10,20,30], array[40,50,60])->-6;
1504 ERROR: cube index -6 is out of bounds
1505 SELECT cube(array[10,20,30])->3;
1511 SELECT cube(array[10,20,30])->6;
1517 SELECT cube(array[10,20,30])->-6;
1518 ERROR: cube index -6 is out of bounds
1519 -- "normalized" coordinate access
1520 SELECT cube(array[10,20,30], array[40,50,60])~>1;
1526 SELECT cube(array[40,50,60], array[10,20,30])~>1;
1532 SELECT cube(array[10,20,30], array[40,50,60])~>2;
1538 SELECT cube(array[40,50,60], array[10,20,30])~>2;
1544 SELECT cube(array[10,20,30], array[40,50,60])~>3;
1550 SELECT cube(array[40,50,60], array[10,20,30])~>3;
1556 SELECT cube(array[40,50,60], array[10,20,30])~>0;
1557 ERROR: cube index 0 is out of bounds
1558 SELECT cube(array[40,50,60], array[10,20,30])~>4;
1564 SELECT cube(array[40,50,60], array[10,20,30])~>(-1);
1565 ERROR: cube index -1 is out of bounds
1566 -- Load some example data and build the index
1568 CREATE TABLE test_cube (c cube);
1569 \copy test_cube from 'data/test_cube.data'
1570 CREATE INDEX test_cube_ix ON test_cube USING gist (c);
1571 SELECT * FROM test_cube WHERE c && '(3000,1000),(0,0)' ORDER BY c;
1573 --------------------------
1574 (337, 455),(240, 359)
1575 (759, 187),(662, 163)
1576 (1444, 403),(1346, 344)
1577 (1594, 1043),(1517, 971)
1578 (2424, 160),(2424, 81)
1582 SELECT * FROM test_cube WHERE c && '(3000,1000),(0,0)' GROUP BY c ORDER BY c;
1584 --------------------------
1585 (337, 455),(240, 359)
1586 (759, 187),(662, 163)
1587 (1444, 403),(1346, 344)
1588 (1594, 1043),(1517, 971)
1589 (2424, 160),(2424, 81)
1593 SELECT *, c <-> '(100, 100),(500, 500)'::cube as dist FROM test_cube ORDER BY c <-> '(100, 100),(500, 500)'::cube LIMIT 5;
1595 -------------------------+------------------
1596 (337, 455),(240, 359) | 0
1597 (759, 187),(662, 163) | 162
1598 (948, 1201),(907, 1156) | 772.000647668122
1599 (1444, 403),(1346, 344) | 846
1600 (369, 1457),(278, 1409) | 909
1603 SELECT *, c <=> '(100, 100),(500, 500)'::cube as dist FROM test_cube ORDER BY c <=> '(100, 100),(500, 500)'::cube LIMIT 5;
1605 -------------------------+------
1606 (337, 455),(240, 359) | 0
1607 (759, 187),(662, 163) | 162
1608 (948, 1201),(907, 1156) | 656
1609 (1444, 403),(1346, 344) | 846
1610 (369, 1457),(278, 1409) | 909
1613 SELECT *, c <#> '(100, 100),(500, 500)'::cube as dist FROM test_cube ORDER BY c <#> '(100, 100),(500, 500)'::cube LIMIT 5;
1615 -------------------------+------
1616 (337, 455),(240, 359) | 0
1617 (759, 187),(662, 163) | 162
1618 (1444, 403),(1346, 344) | 846
1619 (369, 1457),(278, 1409) | 909
1620 (948, 1201),(907, 1156) | 1063
1623 -- kNN-based sorting
1624 SELECT * FROM test_cube ORDER BY c~>1 LIMIT 15; -- ascending by 1st coordinate of lower left corner
1626 ---------------------------
1627 (54, 38679),(3, 38602)
1628 (83, 10271),(15, 10265)
1629 (122, 46832),(64, 46762)
1630 (167, 17214),(92, 17184)
1631 (161, 24465),(107, 24374)
1632 (162, 26040),(120, 25963)
1633 (154, 4019),(138, 3990)
1634 (259, 1850),(175, 1820)
1635 (207, 40886),(179, 40879)
1636 (288, 49588),(204, 49571)
1637 (270, 32616),(226, 32607)
1638 (318, 31489),(235, 31404)
1639 (337, 455),(240, 359)
1640 (270, 29508),(264, 29440)
1641 (369, 1457),(278, 1409)
1644 SELECT * FROM test_cube ORDER BY c~>4 LIMIT 15; -- ascending by 2nd coordinate or upper right corner
1646 ---------------------------
1647 (30333, 50),(30273, 6)
1648 (43301, 75),(43227, 43)
1649 (19650, 142),(19630, 51)
1650 (2424, 160),(2424, 81)
1651 (3449, 171),(3354, 108)
1652 (18037, 155),(17941, 109)
1653 (28511, 208),(28479, 114)
1654 (19946, 217),(19941, 118)
1655 (16906, 191),(16816, 139)
1656 (759, 187),(662, 163)
1657 (22684, 266),(22656, 181)
1658 (24423, 255),(24360, 213)
1659 (45989, 249),(45910, 222)
1660 (11399, 377),(11360, 294)
1661 (12162, 389),(12103, 309)
1664 SELECT * FROM test_cube ORDER BY c~>1 DESC LIMIT 15; -- descending by 1st coordinate of lower left corner
1666 -------------------------------
1667 (50027, 49230),(49951, 49214)
1668 (49980, 35004),(49937, 34963)
1669 (49985, 6436),(49927, 6338)
1670 (49999, 27218),(49908, 27176)
1671 (49954, 1340),(49905, 1294)
1672 (49944, 25163),(49902, 25153)
1673 (49981, 34876),(49898, 34786)
1674 (49957, 43390),(49897, 43384)
1675 (49853, 18504),(49848, 18503)
1676 (49902, 41752),(49818, 41746)
1677 (49907, 30225),(49810, 30158)
1678 (49843, 5175),(49808, 5145)
1679 (49887, 24274),(49805, 24184)
1680 (49847, 7128),(49798, 7067)
1681 (49820, 7990),(49771, 7967)
1684 SELECT * FROM test_cube ORDER BY c~>4 DESC LIMIT 15; -- descending by 2nd coordinate or upper right corner
1686 -------------------------------
1687 (36311, 50073),(36258, 49987)
1688 (30746, 50040),(30727, 49992)
1689 (2168, 50012),(2108, 49914)
1690 (21551, 49983),(21492, 49885)
1691 (17954, 49975),(17865, 49915)
1692 (3531, 49962),(3463, 49934)
1693 (19128, 49932),(19112, 49849)
1694 (31287, 49923),(31236, 49913)
1695 (43925, 49912),(43888, 49878)
1696 (29261, 49910),(29247, 49818)
1697 (14913, 49873),(14849, 49836)
1698 (20007, 49858),(19921, 49778)
1699 (38266, 49852),(38233, 49844)
1700 (37595, 49849),(37581, 49834)
1701 (46151, 49848),(46058, 49830)
1704 -- same thing for index with points
1705 CREATE TABLE test_point(c cube);
1706 INSERT INTO test_point(SELECT cube(array[c->1,c->2,c->3,c->4]) FROM test_cube);
1707 CREATE INDEX ON test_point USING gist(c);
1708 SELECT * FROM test_point ORDER BY c~>1, c~>2 LIMIT 15; -- ascending by 1st then by 2nd coordinate
1710 --------------------------
1711 (54, 38679, 3, 38602)
1712 (83, 10271, 15, 10265)
1713 (122, 46832, 64, 46762)
1714 (154, 4019, 138, 3990)
1715 (161, 24465, 107, 24374)
1716 (162, 26040, 120, 25963)
1717 (167, 17214, 92, 17184)
1718 (207, 40886, 179, 40879)
1719 (259, 1850, 175, 1820)
1720 (270, 29508, 264, 29440)
1721 (270, 32616, 226, 32607)
1722 (288, 49588, 204, 49571)
1723 (318, 31489, 235, 31404)
1724 (326, 18837, 285, 18817)
1725 (337, 455, 240, 359)
1728 SELECT * FROM test_point ORDER BY c~>4 DESC LIMIT 15; -- descending by 1st coordinate
1730 ------------------------------
1731 (30746, 50040, 30727, 49992)
1732 (36311, 50073, 36258, 49987)
1733 (3531, 49962, 3463, 49934)
1734 (17954, 49975, 17865, 49915)
1735 (2168, 50012, 2108, 49914)
1736 (31287, 49923, 31236, 49913)
1737 (21551, 49983, 21492, 49885)
1738 (43925, 49912, 43888, 49878)
1739 (19128, 49932, 19112, 49849)
1740 (38266, 49852, 38233, 49844)
1741 (14913, 49873, 14849, 49836)
1742 (37595, 49849, 37581, 49834)
1743 (46151, 49848, 46058, 49830)
1744 (29261, 49910, 29247, 49818)
1745 (19233, 49824, 19185, 49794)