6
-- testing the input and output functions
8
-- Any number (a one-dimensional point)
9
SELECT '1'::cube AS cube;
15
SELECT '-1'::cube AS cube;
21
SELECT '1.'::cube AS cube;
27
SELECT '-1.'::cube AS cube;
33
SELECT '.1'::cube AS cube;
39
SELECT '-.1'::cube AS cube;
45
SELECT '1.0'::cube AS cube;
51
SELECT '-1.0'::cube AS cube;
57
SELECT '1e27'::cube AS cube;
63
SELECT '-1e27'::cube AS cube;
69
SELECT '1.0e27'::cube AS cube;
75
SELECT '-1.0e27'::cube AS cube;
81
SELECT '1e+27'::cube AS cube;
87
SELECT '-1e+27'::cube AS cube;
93
SELECT '1.0e+27'::cube AS cube;
99
SELECT '-1.0e+27'::cube AS cube;
105
SELECT '1e-7'::cube AS cube;
111
SELECT '-1e-7'::cube AS cube;
117
SELECT '1.0e-7'::cube AS cube;
123
SELECT '-1.0e-7'::cube AS cube;
129
SELECT '1e-700'::cube AS cube;
135
SELECT '-1e-700'::cube AS cube;
141
SELECT '1234567890123456'::cube AS cube;
143
------------------------
144
(1.23456789012346e+15)
147
SELECT '+1234567890123456'::cube AS cube;
149
------------------------
150
(1.23456789012346e+15)
153
SELECT '-1234567890123456'::cube AS cube;
155
-------------------------
156
(-1.23456789012346e+15)
159
SELECT '.1234567890123456'::cube AS cube;
161
---------------------
165
SELECT '+.1234567890123456'::cube AS cube;
167
---------------------
171
SELECT '-.1234567890123456'::cube AS cube;
173
----------------------
177
-- simple lists (points)
178
SELECT '1,2'::cube AS cube;
184
SELECT '(1,2)'::cube AS cube;
190
SELECT '1,2,3,4,5'::cube AS cube;
196
SELECT '(1,2,3,4,5)'::cube AS cube;
202
-- double lists (cubes)
203
SELECT '(0),(0)'::cube AS cube;
209
SELECT '(0),(1)'::cube AS cube;
215
SELECT '[(0),(0)]'::cube AS cube;
221
SELECT '[(0),(1)]'::cube AS cube;
227
SELECT '(0,0,0,0),(0,0,0,0)'::cube AS cube;
233
SELECT '(0,0,0,0),(1,0,0,0)'::cube AS cube;
235
---------------------------
236
(0, 0, 0, 0),(1, 0, 0, 0)
239
SELECT '[(0,0,0,0),(0,0,0,0)]'::cube AS cube;
245
SELECT '[(0,0,0,0),(1,0,0,0)]'::cube AS cube;
247
---------------------------
248
(0, 0, 0, 0),(1, 0, 0, 0)
251
-- invalid input: parse errors
252
SELECT ''::cube AS cube;
253
ERROR: bad cube representation
254
LINE 1: SELECT ''::cube AS cube;
256
DETAIL: syntax error at end of input
257
SELECT 'ABC'::cube AS cube;
258
ERROR: bad cube representation
259
LINE 1: SELECT 'ABC'::cube AS cube;
261
DETAIL: syntax error at or near "A"
262
SELECT '()'::cube AS cube;
263
ERROR: bad cube representation
264
LINE 1: SELECT '()'::cube AS cube;
266
DETAIL: syntax error at or near ")"
267
SELECT '[]'::cube AS cube;
268
ERROR: bad cube representation
269
LINE 1: SELECT '[]'::cube AS cube;
271
DETAIL: syntax error at or near "]"
272
SELECT '[()]'::cube AS cube;
273
ERROR: bad cube representation
274
LINE 1: SELECT '[()]'::cube AS cube;
276
DETAIL: syntax error at or near ")"
277
SELECT '[(1)]'::cube AS cube;
278
ERROR: bad cube representation
279
LINE 1: SELECT '[(1)]'::cube AS cube;
281
DETAIL: syntax error at or near "]"
282
SELECT '[(1),]'::cube AS cube;
283
ERROR: bad cube representation
284
LINE 1: SELECT '[(1),]'::cube AS cube;
286
DETAIL: syntax error at or near "]"
287
SELECT '[(1),2]'::cube AS cube;
288
ERROR: bad cube representation
289
LINE 1: SELECT '[(1),2]'::cube AS cube;
291
DETAIL: syntax error at or near "2"
292
SELECT '[(1),(2),(3)]'::cube AS cube;
293
ERROR: bad cube representation
294
LINE 1: SELECT '[(1),(2),(3)]'::cube AS cube;
296
DETAIL: syntax error at or near ","
297
SELECT '1,'::cube AS cube;
298
ERROR: bad cube representation
299
LINE 1: SELECT '1,'::cube AS cube;
301
DETAIL: syntax error at end of input
302
SELECT '1,2,'::cube AS cube;
303
ERROR: bad cube representation
304
LINE 1: SELECT '1,2,'::cube AS cube;
306
DETAIL: syntax error at end of input
307
SELECT '1,,2'::cube AS cube;
308
ERROR: bad cube representation
309
LINE 1: SELECT '1,,2'::cube AS cube;
311
DETAIL: syntax error at or near ","
312
SELECT '(1,)'::cube AS cube;
313
ERROR: bad cube representation
314
LINE 1: SELECT '(1,)'::cube AS cube;
316
DETAIL: syntax error at or near ")"
317
SELECT '(1,2,)'::cube AS cube;
318
ERROR: bad cube representation
319
LINE 1: SELECT '(1,2,)'::cube AS cube;
321
DETAIL: syntax error at or near ")"
322
SELECT '(1,,2)'::cube AS cube;
323
ERROR: bad cube representation
324
LINE 1: SELECT '(1,,2)'::cube AS cube;
326
DETAIL: syntax error at or near ","
327
-- invalid input: semantic errors and trailing garbage
328
SELECT '[(1),(2)],'::cube AS cube; -- 0
329
ERROR: bad cube representation
330
LINE 1: SELECT '[(1),(2)],'::cube AS cube;
332
DETAIL: syntax error at or near ","
333
SELECT '[(1,2,3),(2,3)]'::cube AS cube; -- 1
334
ERROR: bad cube representation
335
LINE 1: SELECT '[(1,2,3),(2,3)]'::cube AS cube;
337
DETAIL: Different point dimensions in (1,2,3) and (2,3).
338
SELECT '[(1,2),(1,2,3)]'::cube AS cube; -- 1
339
ERROR: bad cube representation
340
LINE 1: SELECT '[(1,2),(1,2,3)]'::cube AS cube;
342
DETAIL: Different point dimensions in (1,2) and (1,2,3).
343
SELECT '(1),(2),'::cube AS cube; -- 2
344
ERROR: bad cube representation
345
LINE 1: SELECT '(1),(2),'::cube AS cube;
347
DETAIL: syntax error at or near ","
348
SELECT '(1,2,3),(2,3)'::cube AS cube; -- 3
349
ERROR: bad cube representation
350
LINE 1: SELECT '(1,2,3),(2,3)'::cube AS cube;
352
DETAIL: Different point dimensions in (1,2,3) and (2,3).
353
SELECT '(1,2),(1,2,3)'::cube AS cube; -- 3
354
ERROR: bad cube representation
355
LINE 1: SELECT '(1,2),(1,2,3)'::cube AS cube;
357
DETAIL: Different point dimensions in (1,2) and (1,2,3).
358
SELECT '(1,2,3)ab'::cube AS cube; -- 4
359
ERROR: bad cube representation
360
LINE 1: SELECT '(1,2,3)ab'::cube AS cube;
362
DETAIL: syntax error at or near "a"
363
SELECT '(1,2,3)a'::cube AS cube; -- 5
364
ERROR: bad cube representation
365
LINE 1: SELECT '(1,2,3)a'::cube AS cube;
367
DETAIL: syntax error at or near "a"
368
SELECT '(1,2)('::cube AS cube; -- 5
369
ERROR: bad cube representation
370
LINE 1: SELECT '(1,2)('::cube AS cube;
372
DETAIL: syntax error at or near "("
373
SELECT '1,2ab'::cube AS cube; -- 6
374
ERROR: bad cube representation
375
LINE 1: SELECT '1,2ab'::cube AS cube;
377
DETAIL: syntax error at or near "a"
378
SELECT '1 e7'::cube AS cube; -- 6
379
ERROR: bad cube representation
380
LINE 1: SELECT '1 e7'::cube AS cube;
382
DETAIL: syntax error at or near "e"
383
SELECT '1,2a'::cube AS cube; -- 7
384
ERROR: bad cube representation
385
LINE 1: SELECT '1,2a'::cube AS cube;
387
DETAIL: syntax error at or near "a"
388
SELECT '1..2'::cube AS cube; -- 7
389
ERROR: bad cube representation
390
LINE 1: SELECT '1..2'::cube AS cube;
392
DETAIL: syntax error at or near ".2"
394
-- Testing building cubes from float8 values
396
SELECT cube(0::float8);
402
SELECT cube(1::float8);
414
SELECT cube(cube(1,2),3);
420
SELECT cube(cube(1,2),3,4);
426
SELECT cube(cube(cube(1,2),3,4),5);
428
---------------------
432
SELECT cube(cube(cube(1,2),3,4),5,6);
434
---------------------
439
-- Test that the text -> cube cast was installed.
441
SELECT '(0)'::text::cube;
448
-- Test the float[] -> cube cast
450
SELECT cube('{0,1,2}'::float[], '{3,4,5}'::float[]);
452
---------------------
456
SELECT cube('{0,1,2}'::float[], '{3}'::float[]);
457
ERROR: UR and LL arrays must be of same length
458
SELECT cube(NULL::float[], '{3}'::float[]);
464
SELECT cube('{0,1,2}'::float[]);
470
SELECT cube_subset(cube('(1,3,5),(6,7,8)'), ARRAY[3,2,1,1]);
472
---------------------------
473
(5, 3, 1, 1),(8, 7, 6, 6)
476
SELECT cube_subset(cube('(1,3,5),(6,7,8)'), ARRAY[4,0]);
477
ERROR: Index out of bounds
479
-- Testing limit of CUBE_MAX_DIM dimensions check in cube_in.
481
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;
482
ERROR: bad cube representation
483
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...
485
DETAIL: A cube cannot have more than 100 dimensions.
486
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;
487
ERROR: bad cube representation
488
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...
490
DETAIL: A cube cannot have more than 100 dimensions.
492
-- testing the operators
494
-- equality/inequality:
496
SELECT '24, 33.20'::cube = '24, 33.20'::cube AS bool;
502
SELECT '24, 33.20'::cube != '24, 33.20'::cube AS bool;
508
SELECT '24, 33.20'::cube = '24, 33.21'::cube AS bool;
514
SELECT '24, 33.20'::cube != '24, 33.21'::cube AS bool;
520
SELECT '(2,0),(3,1)'::cube = '(2,0,0,0,0),(3,1,0,0,0)'::cube AS bool;
526
SELECT '(2,0),(3,1)'::cube = '(2,0,0,0,0),(3,1,0,0,1)'::cube AS bool;
532
-- "lower than" / "greater than"
533
-- (these operators are not useful for anything but ordering)
535
SELECT '1'::cube > '2'::cube AS bool;
541
SELECT '1'::cube < '2'::cube AS bool;
547
SELECT '1,1'::cube > '1,2'::cube AS bool;
553
SELECT '1,1'::cube < '1,2'::cube AS bool;
559
SELECT '(2,0),(3,1)'::cube > '(2,0,0,0,0),(3,1,0,0,1)'::cube AS bool;
565
SELECT '(2,0),(3,1)'::cube < '(2,0,0,0,0),(3,1,0,0,1)'::cube AS bool;
571
SELECT '(2,0),(3,1)'::cube > '(2,0,0,0,1),(3,1,0,0,0)'::cube AS bool;
577
SELECT '(2,0),(3,1)'::cube < '(2,0,0,0,1),(3,1,0,0,0)'::cube AS bool;
583
SELECT '(2,0),(3,1)'::cube > '(2,0,0,0,0),(3,1,0,0,0)'::cube AS bool;
589
SELECT '(2,0),(3,1)'::cube < '(2,0,0,0,0),(3,1,0,0,0)'::cube AS bool;
595
SELECT '(2,0,0,0,0),(3,1,0,0,1)'::cube > '(2,0),(3,1)'::cube AS bool;
601
SELECT '(2,0,0,0,0),(3,1,0,0,1)'::cube < '(2,0),(3,1)'::cube AS bool;
607
SELECT '(2,0,0,0,1),(3,1,0,0,0)'::cube > '(2,0),(3,1)'::cube AS bool;
613
SELECT '(2,0,0,0,1),(3,1,0,0,0)'::cube < '(2,0),(3,1)'::cube AS bool;
619
SELECT '(2,0,0,0,0),(3,1,0,0,0)'::cube > '(2,0),(3,1)'::cube AS bool;
625
SELECT '(2,0,0,0,0),(3,1,0,0,0)'::cube < '(2,0),(3,1)'::cube AS bool;
633
SELECT '1'::cube && '1'::cube AS bool;
639
SELECT '1'::cube && '2'::cube AS bool;
645
SELECT '[(-1,-1,-1),(1,1,1)]'::cube && '0'::cube AS bool;
651
SELECT '[(-1,-1,-1),(1,1,1)]'::cube && '1'::cube AS bool;
657
SELECT '[(-1,-1,-1),(1,1,1)]'::cube && '1,1,1'::cube AS bool;
663
SELECT '[(-1,-1,-1),(1,1,1)]'::cube && '[(1,1,1),(2,2,2)]'::cube AS bool;
669
SELECT '[(-1,-1,-1),(1,1,1)]'::cube && '[(1,1),(2,2)]'::cube AS bool;
675
SELECT '[(-1,-1,-1),(1,1,1)]'::cube && '[(2,1,1),(2,2,2)]'::cube AS bool;
681
-- "contained in" (the left operand is the cube entirely enclosed by
682
-- the right operand):
684
SELECT '0'::cube <@ '0'::cube AS bool;
690
SELECT '0,0,0'::cube <@ '0,0,0'::cube AS bool;
696
SELECT '0,0'::cube <@ '0,0,1'::cube AS bool;
702
SELECT '0,0,0'::cube <@ '0,0,1'::cube AS bool;
708
SELECT '1,0,0'::cube <@ '0,0,1'::cube AS bool;
714
SELECT '(1,0,0),(0,0,1)'::cube <@ '(1,0,0),(0,0,1)'::cube AS bool;
720
SELECT '(1,0,0),(0,0,1)'::cube <@ '(-1,-1,-1),(1,1,1)'::cube AS bool;
726
SELECT '(1,0,0),(0,0,1)'::cube <@ '(-1,-1,-1,-1),(1,1,1,1)'::cube AS bool;
732
SELECT '0'::cube <@ '(-1),(1)'::cube AS bool;
738
SELECT '1'::cube <@ '(-1),(1)'::cube AS bool;
744
SELECT '-1'::cube <@ '(-1),(1)'::cube AS bool;
750
SELECT '(-1),(1)'::cube <@ '(-1),(1)'::cube AS bool;
756
SELECT '(-1),(1)'::cube <@ '(-1,-1),(1,1)'::cube AS bool;
762
SELECT '(-2),(1)'::cube <@ '(-1),(1)'::cube AS bool;
768
SELECT '(-2),(1)'::cube <@ '(-1,-1),(1,1)'::cube AS bool;
774
-- "contains" (the left operand is the cube that entirely encloses the
777
SELECT '0'::cube @> '0'::cube AS bool;
783
SELECT '0,0,0'::cube @> '0,0,0'::cube AS bool;
789
SELECT '0,0,1'::cube @> '0,0'::cube AS bool;
795
SELECT '0,0,1'::cube @> '0,0,0'::cube AS bool;
801
SELECT '0,0,1'::cube @> '1,0,0'::cube AS bool;
807
SELECT '(1,0,0),(0,0,1)'::cube @> '(1,0,0),(0,0,1)'::cube AS bool;
813
SELECT '(-1,-1,-1),(1,1,1)'::cube @> '(1,0,0),(0,0,1)'::cube AS bool;
819
SELECT '(-1,-1,-1,-1),(1,1,1,1)'::cube @> '(1,0,0),(0,0,1)'::cube AS bool;
825
SELECT '(-1),(1)'::cube @> '0'::cube AS bool;
831
SELECT '(-1),(1)'::cube @> '1'::cube AS bool;
837
SELECT '(-1),(1)'::cube @> '-1'::cube AS bool;
843
SELECT '(-1),(1)'::cube @> '(-1),(1)'::cube AS bool;
849
SELECT '(-1,-1),(1,1)'::cube @> '(-1),(1)'::cube AS bool;
855
SELECT '(-1),(1)'::cube @> '(-2),(1)'::cube AS bool;
861
SELECT '(-1,-1),(1,1)'::cube @> '(-2),(1)'::cube AS bool;
867
-- Test of distance function
869
SELECT cube_distance('(0)'::cube,'(2,2,2,2)'::cube);
875
SELECT cube_distance('(0)'::cube,'(.3,.4)'::cube);
881
-- Test of cube function (text to cube)
883
SELECT cube('(1,1.2)'::text);
895
-- Test of cube_dim function (dimensions stored in cube)
897
SELECT cube_dim('(0)'::cube);
903
SELECT cube_dim('(0,0)'::cube);
909
SELECT cube_dim('(0,0,0)'::cube);
915
-- Test of cube_ll_coord function (retrieves LL coodinate values)
917
SELECT cube_ll_coord('(-1,1),(2,-2)'::cube, 1);
923
SELECT cube_ll_coord('(-1,1),(2,-2)'::cube, 2);
929
SELECT cube_ll_coord('(-1,1),(2,-2)'::cube, 3);
935
-- Test of cube_ur_coord function (retrieves UR coodinate values)
937
SELECT cube_ur_coord('(-1,1),(2,-2)'::cube, 1);
943
SELECT cube_ur_coord('(-1,1),(2,-2)'::cube, 2);
949
SELECT cube_ur_coord('(-1,1),(2,-2)'::cube, 3);
955
-- Test of cube_is_point
957
SELECT cube_is_point('(0)'::cube);
963
SELECT cube_is_point('(0,1,2)'::cube);
969
SELECT cube_is_point('(0,1,2),(0,1,2)'::cube);
975
SELECT cube_is_point('(0,1,2),(-1,1,2)'::cube);
981
SELECT cube_is_point('(0,1,2),(0,-1,2)'::cube);
987
SELECT cube_is_point('(0,1,2),(0,1,-2)'::cube);
993
-- Test of cube_enlarge (enlarging and shrinking cubes)
995
SELECT cube_enlarge('(0)'::cube, 0, 0);
1001
SELECT cube_enlarge('(0)'::cube, 0, 1);
1007
SELECT cube_enlarge('(0)'::cube, 0, 2);
1013
SELECT cube_enlarge('(2),(-2)'::cube, 0, 4);
1019
SELECT cube_enlarge('(0)'::cube, 1, 0);
1025
SELECT cube_enlarge('(0)'::cube, 1, 1);
1031
SELECT cube_enlarge('(0)'::cube, 1, 2);
1037
SELECT cube_enlarge('(2),(-2)'::cube, 1, 4);
1039
-------------------------------
1040
(-3, -1, -1, -1),(3, 1, 1, 1)
1043
SELECT cube_enlarge('(0)'::cube, -1, 0);
1049
SELECT cube_enlarge('(0)'::cube, -1, 1);
1055
SELECT cube_enlarge('(0)'::cube, -1, 2);
1061
SELECT cube_enlarge('(2),(-2)'::cube, -1, 4);
1067
SELECT cube_enlarge('(0,0,0)'::cube, 1, 0);
1069
------------------------
1070
(-1, -1, -1),(1, 1, 1)
1073
SELECT cube_enlarge('(0,0,0)'::cube, 1, 2);
1075
------------------------
1076
(-1, -1, -1),(1, 1, 1)
1079
SELECT cube_enlarge('(2,-2),(-3,7)'::cube, 1, 2);
1085
SELECT cube_enlarge('(2,-2),(-3,7)'::cube, 3, 2);
1091
SELECT cube_enlarge('(2,-2),(-3,7)'::cube, -1, 2);
1097
SELECT cube_enlarge('(2,-2),(-3,7)'::cube, -3, 2);
1099
---------------------
1103
-- Load some example data and build the index
1105
CREATE TABLE test_cube (c cube);
1106
\copy test_cube from 'data/test_cube.data'
1107
CREATE INDEX test_cube_ix ON test_cube USING gist (c);
1108
SELECT * FROM test_cube WHERE c && '(3000,1000),(0,0)' ORDER BY c;
1110
--------------------------
1111
(337, 455),(240, 359)
1112
(759, 187),(662, 163)
1113
(1444, 403),(1346, 344)
1114
(1594, 1043),(1517, 971)
1115
(2424, 160),(2424, 81)
1119
SELECT * FROM test_cube WHERE c && '(3000,1000),(0,0)' GROUP BY c ORDER BY c;
1121
--------------------------
1122
(337, 455),(240, 359)
1123
(759, 187),(662, 163)
1124
(1444, 403),(1346, 344)
1125
(1594, 1043),(1517, 971)
1126
(2424, 160),(2424, 81)