1-- 2-- Test cube datatype 3-- 4 5CREATE EXTENSION cube; 6 7-- Check whether any of our opclasses fail amvalidate 8SELECT amname, opcname 9FROM pg_opclass opc LEFT JOIN pg_am am ON am.oid = opcmethod 10WHERE opc.oid >= 16384 AND NOT amvalidate(opc.oid); 11 12-- 13-- testing the input and output functions 14-- 15 16-- Any number (a one-dimensional point) 17SELECT '1'::cube AS cube; 18SELECT '-1'::cube AS cube; 19SELECT '1.'::cube AS cube; 20SELECT '-1.'::cube AS cube; 21SELECT '.1'::cube AS cube; 22SELECT '-.1'::cube AS cube; 23SELECT '1.0'::cube AS cube; 24SELECT '-1.0'::cube AS cube; 25SELECT 'infinity'::cube AS cube; 26SELECT '-infinity'::cube AS cube; 27SELECT 'NaN'::cube AS cube; 28SELECT '.1234567890123456'::cube AS cube; 29SELECT '+.1234567890123456'::cube AS cube; 30SELECT '-.1234567890123456'::cube AS cube; 31 32-- simple lists (points) 33SELECT '()'::cube AS cube; 34SELECT '1,2'::cube AS cube; 35SELECT '(1,2)'::cube AS cube; 36SELECT '1,2,3,4,5'::cube AS cube; 37SELECT '(1,2,3,4,5)'::cube AS cube; 38 39-- double lists (cubes) 40SELECT '(),()'::cube AS cube; 41SELECT '(0),(0)'::cube AS cube; 42SELECT '(0),(1)'::cube AS cube; 43SELECT '[(0),(0)]'::cube AS cube; 44SELECT '[(0),(1)]'::cube AS cube; 45SELECT '(0,0,0,0),(0,0,0,0)'::cube AS cube; 46SELECT '(0,0,0,0),(1,0,0,0)'::cube AS cube; 47SELECT '[(0,0,0,0),(0,0,0,0)]'::cube AS cube; 48SELECT '[(0,0,0,0),(1,0,0,0)]'::cube AS cube; 49 50-- invalid input: parse errors 51SELECT ''::cube AS cube; 52SELECT 'ABC'::cube AS cube; 53SELECT '[]'::cube AS cube; 54SELECT '[()]'::cube AS cube; 55SELECT '[(1)]'::cube AS cube; 56SELECT '[(1),]'::cube AS cube; 57SELECT '[(1),2]'::cube AS cube; 58SELECT '[(1),(2),(3)]'::cube AS cube; 59SELECT '1,'::cube AS cube; 60SELECT '1,2,'::cube AS cube; 61SELECT '1,,2'::cube AS cube; 62SELECT '(1,)'::cube AS cube; 63SELECT '(1,2,)'::cube AS cube; 64SELECT '(1,,2)'::cube AS cube; 65 66-- invalid input: semantic errors and trailing garbage 67SELECT '[(1),(2)],'::cube AS cube; -- 0 68SELECT '[(1,2,3),(2,3)]'::cube AS cube; -- 1 69SELECT '[(1,2),(1,2,3)]'::cube AS cube; -- 1 70SELECT '(1),(2),'::cube AS cube; -- 2 71SELECT '(1,2,3),(2,3)'::cube AS cube; -- 3 72SELECT '(1,2),(1,2,3)'::cube AS cube; -- 3 73SELECT '(1,2,3)ab'::cube AS cube; -- 4 74SELECT '(1,2,3)a'::cube AS cube; -- 5 75SELECT '(1,2)('::cube AS cube; -- 5 76SELECT '1,2ab'::cube AS cube; -- 6 77SELECT '1 e7'::cube AS cube; -- 6 78SELECT '1,2a'::cube AS cube; -- 7 79SELECT '1..2'::cube AS cube; -- 7 80SELECT '-1e-700'::cube AS cube; -- out of range 81 82-- 83-- Testing building cubes from float8 values 84-- 85 86SELECT cube(0::float8); 87SELECT cube(1::float8); 88SELECT cube(1,2); 89SELECT cube(cube(1,2),3); 90SELECT cube(cube(1,2),3,4); 91SELECT cube(cube(cube(1,2),3,4),5); 92SELECT cube(cube(cube(1,2),3,4),5,6); 93 94-- 95-- Test that the text -> cube cast was installed. 96-- 97 98SELECT '(0)'::text::cube; 99 100-- 101-- Test the float[] -> cube cast 102-- 103SELECT cube('{0,1,2}'::float[], '{3,4,5}'::float[]); 104SELECT cube('{0,1,2}'::float[], '{3}'::float[]); 105SELECT cube(NULL::float[], '{3}'::float[]); 106SELECT cube('{0,1,2}'::float[]); 107SELECT cube_subset(cube('(1,3,5),(6,7,8)'), ARRAY[3,2,1,1]); 108SELECT cube_subset(cube('(1,3,5),(1,3,5)'), ARRAY[3,2,1,1]); 109SELECT cube_subset(cube('(1,3,5),(6,7,8)'), ARRAY[4,0]); 110SELECT cube_subset(cube('(6,7,8),(6,7,8)'), ARRAY[4,0]); 111-- test for limits: this should pass 112SELECT cube_subset(cube('(6,7,8),(6,7,8)'), array(SELECT 1 as a FROM generate_series(1,100))); 113-- and this should fail 114SELECT cube_subset(cube('(6,7,8),(6,7,8)'), array(SELECT 1 as a FROM generate_series(1,101))); 115 116 117 118-- 119-- Test point processing 120-- 121SELECT cube('(1,2),(1,2)'); -- cube_in 122SELECT cube('{0,1,2}'::float[], '{0,1,2}'::float[]); -- cube_a_f8_f8 123SELECT cube('{5,6,7,8}'::float[]); -- cube_a_f8 124SELECT cube(1.37); -- cube_f8 125SELECT cube(1.37, 1.37); -- cube_f8_f8 126SELECT cube(cube(1,1), 42); -- cube_c_f8 127SELECT cube(cube(1,2), 42); -- cube_c_f8 128SELECT cube(cube(1,1), 42, 42); -- cube_c_f8_f8 129SELECT cube(cube(1,1), 42, 24); -- cube_c_f8_f8 130SELECT cube(cube(1,2), 42, 42); -- cube_c_f8_f8 131SELECT cube(cube(1,2), 42, 24); -- cube_c_f8_f8 132 133-- 134-- Testing limit of CUBE_MAX_DIM dimensions check in cube_in. 135-- 136-- create too big cube from literal 137select '(0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,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; 138select '(0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0),(0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,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; 139-- from an array 140select cube(array(SELECT 0 as a FROM generate_series(1,101))); 141select cube(array(SELECT 0 as a FROM generate_series(1,101)),array(SELECT 0 as a FROM generate_series(1,101))); 142 143-- extend cube beyond limit 144-- this should work 145select cube(array(SELECT 0 as a FROM generate_series(1,100))); 146select cube(array(SELECT 0 as a FROM generate_series(1,100)),array(SELECT 0 as a FROM generate_series(1,100))); 147-- this should fail 148select cube(cube(array(SELECT 0 as a FROM generate_series(1,100))), 0); 149select cube(cube(array(SELECT 0 as a FROM generate_series(1,100)),array(SELECT 0 as a FROM generate_series(1,100))), 0, 0); 150 151 152-- 153-- testing the operators 154-- 155 156-- equality/inequality: 157-- 158SELECT '24, 33.20'::cube = '24, 33.20'::cube AS bool; 159SELECT '24, 33.20'::cube != '24, 33.20'::cube AS bool; 160SELECT '24, 33.20'::cube = '24, 33.21'::cube AS bool; 161SELECT '24, 33.20'::cube != '24, 33.21'::cube AS bool; 162SELECT '(2,0),(3,1)'::cube = '(2,0,0,0,0),(3,1,0,0,0)'::cube AS bool; 163SELECT '(2,0),(3,1)'::cube = '(2,0,0,0,0),(3,1,0,0,1)'::cube AS bool; 164 165-- "lower than" / "greater than" 166-- (these operators are not useful for anything but ordering) 167-- 168SELECT '1'::cube > '2'::cube AS bool; 169SELECT '1'::cube < '2'::cube AS bool; 170SELECT '1,1'::cube > '1,2'::cube AS bool; 171SELECT '1,1'::cube < '1,2'::cube AS bool; 172 173SELECT '(2,0),(3,1)'::cube > '(2,0,0,0,0),(3,1,0,0,1)'::cube AS bool; 174SELECT '(2,0),(3,1)'::cube < '(2,0,0,0,0),(3,1,0,0,1)'::cube AS bool; 175SELECT '(2,0),(3,1)'::cube > '(2,0,0,0,1),(3,1,0,0,0)'::cube AS bool; 176SELECT '(2,0),(3,1)'::cube < '(2,0,0,0,1),(3,1,0,0,0)'::cube AS bool; 177SELECT '(2,0),(3,1)'::cube > '(2,0,0,0,0),(3,1,0,0,0)'::cube AS bool; 178SELECT '(2,0),(3,1)'::cube < '(2,0,0,0,0),(3,1,0,0,0)'::cube AS bool; 179SELECT '(2,0,0,0,0),(3,1,0,0,1)'::cube > '(2,0),(3,1)'::cube AS bool; 180SELECT '(2,0,0,0,0),(3,1,0,0,1)'::cube < '(2,0),(3,1)'::cube AS bool; 181SELECT '(2,0,0,0,1),(3,1,0,0,0)'::cube > '(2,0),(3,1)'::cube AS bool; 182SELECT '(2,0,0,0,1),(3,1,0,0,0)'::cube < '(2,0),(3,1)'::cube AS bool; 183SELECT '(2,0,0,0,0),(3,1,0,0,0)'::cube > '(2,0),(3,1)'::cube AS bool; 184SELECT '(2,0,0,0,0),(3,1,0,0,0)'::cube < '(2,0),(3,1)'::cube AS bool; 185 186 187-- "overlap" 188-- 189SELECT '1'::cube && '1'::cube AS bool; 190SELECT '1'::cube && '2'::cube AS bool; 191 192SELECT '[(-1,-1,-1),(1,1,1)]'::cube && '0'::cube AS bool; 193SELECT '[(-1,-1,-1),(1,1,1)]'::cube && '1'::cube AS bool; 194SELECT '[(-1,-1,-1),(1,1,1)]'::cube && '1,1,1'::cube AS bool; 195SELECT '[(-1,-1,-1),(1,1,1)]'::cube && '[(1,1,1),(2,2,2)]'::cube AS bool; 196SELECT '[(-1,-1,-1),(1,1,1)]'::cube && '[(1,1),(2,2)]'::cube AS bool; 197SELECT '[(-1,-1,-1),(1,1,1)]'::cube && '[(2,1,1),(2,2,2)]'::cube AS bool; 198 199 200-- "contained in" (the left operand is the cube entirely enclosed by 201-- the right operand): 202-- 203SELECT '0'::cube <@ '0'::cube AS bool; 204SELECT '0,0,0'::cube <@ '0,0,0'::cube AS bool; 205SELECT '0,0'::cube <@ '0,0,1'::cube AS bool; 206SELECT '0,0,0'::cube <@ '0,0,1'::cube AS bool; 207SELECT '1,0,0'::cube <@ '0,0,1'::cube AS bool; 208SELECT '(1,0,0),(0,0,1)'::cube <@ '(1,0,0),(0,0,1)'::cube AS bool; 209SELECT '(1,0,0),(0,0,1)'::cube <@ '(-1,-1,-1),(1,1,1)'::cube AS bool; 210SELECT '(1,0,0),(0,0,1)'::cube <@ '(-1,-1,-1,-1),(1,1,1,1)'::cube AS bool; 211SELECT '0'::cube <@ '(-1),(1)'::cube AS bool; 212SELECT '1'::cube <@ '(-1),(1)'::cube AS bool; 213SELECT '-1'::cube <@ '(-1),(1)'::cube AS bool; 214SELECT '(-1),(1)'::cube <@ '(-1),(1)'::cube AS bool; 215SELECT '(-1),(1)'::cube <@ '(-1,-1),(1,1)'::cube AS bool; 216SELECT '(-2),(1)'::cube <@ '(-1),(1)'::cube AS bool; 217SELECT '(-2),(1)'::cube <@ '(-1,-1),(1,1)'::cube AS bool; 218 219 220-- "contains" (the left operand is the cube that entirely encloses the 221-- right operand) 222-- 223SELECT '0'::cube @> '0'::cube AS bool; 224SELECT '0,0,0'::cube @> '0,0,0'::cube AS bool; 225SELECT '0,0,1'::cube @> '0,0'::cube AS bool; 226SELECT '0,0,1'::cube @> '0,0,0'::cube AS bool; 227SELECT '0,0,1'::cube @> '1,0,0'::cube AS bool; 228SELECT '(1,0,0),(0,0,1)'::cube @> '(1,0,0),(0,0,1)'::cube AS bool; 229SELECT '(-1,-1,-1),(1,1,1)'::cube @> '(1,0,0),(0,0,1)'::cube AS bool; 230SELECT '(-1,-1,-1,-1),(1,1,1,1)'::cube @> '(1,0,0),(0,0,1)'::cube AS bool; 231SELECT '(-1),(1)'::cube @> '0'::cube AS bool; 232SELECT '(-1),(1)'::cube @> '1'::cube AS bool; 233SELECT '(-1),(1)'::cube @> '-1'::cube AS bool; 234SELECT '(-1),(1)'::cube @> '(-1),(1)'::cube AS bool; 235SELECT '(-1,-1),(1,1)'::cube @> '(-1),(1)'::cube AS bool; 236SELECT '(-1),(1)'::cube @> '(-2),(1)'::cube AS bool; 237SELECT '(-1,-1),(1,1)'::cube @> '(-2),(1)'::cube AS bool; 238 239-- Test of distance function 240-- 241SELECT cube_distance('(0)'::cube,'(2,2,2,2)'::cube); 242SELECT cube_distance('(0)'::cube,'(.3,.4)'::cube); 243SELECT cube_distance('(2,3,4)'::cube,'(2,3,4)'::cube); 244SELECT cube_distance('(42,42,42,42)'::cube,'(137,137,137,137)'::cube); 245SELECT cube_distance('(42,42,42)'::cube,'(137,137)'::cube); 246 247-- Test of cube function (text to cube) 248-- 249SELECT cube('(1,1.2)'::text); 250SELECT cube(NULL); 251 252-- Test of cube_dim function (dimensions stored in cube) 253-- 254SELECT cube_dim('(0)'::cube); 255SELECT cube_dim('(0,0)'::cube); 256SELECT cube_dim('(0,0,0)'::cube); 257SELECT cube_dim('(42,42,42),(42,42,42)'::cube); 258SELECT cube_dim('(4,8,15,16,23),(4,8,15,16,23)'::cube); 259 260-- Test of cube_ll_coord function (retrieves LL coordinate values) 261-- 262SELECT cube_ll_coord('(-1,1),(2,-2)'::cube, 1); 263SELECT cube_ll_coord('(-1,1),(2,-2)'::cube, 2); 264SELECT cube_ll_coord('(-1,1),(2,-2)'::cube, 3); 265SELECT cube_ll_coord('(1,2),(1,2)'::cube, 1); 266SELECT cube_ll_coord('(1,2),(1,2)'::cube, 2); 267SELECT cube_ll_coord('(1,2),(1,2)'::cube, 3); 268SELECT cube_ll_coord('(42,137)'::cube, 1); 269SELECT cube_ll_coord('(42,137)'::cube, 2); 270SELECT cube_ll_coord('(42,137)'::cube, 3); 271 272-- Test of cube_ur_coord function (retrieves UR coordinate values) 273-- 274SELECT cube_ur_coord('(-1,1),(2,-2)'::cube, 1); 275SELECT cube_ur_coord('(-1,1),(2,-2)'::cube, 2); 276SELECT cube_ur_coord('(-1,1),(2,-2)'::cube, 3); 277SELECT cube_ur_coord('(1,2),(1,2)'::cube, 1); 278SELECT cube_ur_coord('(1,2),(1,2)'::cube, 2); 279SELECT cube_ur_coord('(1,2),(1,2)'::cube, 3); 280SELECT cube_ur_coord('(42,137)'::cube, 1); 281SELECT cube_ur_coord('(42,137)'::cube, 2); 282SELECT cube_ur_coord('(42,137)'::cube, 3); 283 284-- Test of cube_is_point 285-- 286SELECT cube_is_point('(0)'::cube); 287SELECT cube_is_point('(0,1,2)'::cube); 288SELECT cube_is_point('(0,1,2),(0,1,2)'::cube); 289SELECT cube_is_point('(0,1,2),(-1,1,2)'::cube); 290SELECT cube_is_point('(0,1,2),(0,-1,2)'::cube); 291SELECT cube_is_point('(0,1,2),(0,1,-2)'::cube); 292 293-- Test of cube_enlarge (enlarging and shrinking cubes) 294-- 295SELECT cube_enlarge('(0)'::cube, 0, 0); 296SELECT cube_enlarge('(0)'::cube, 0, 1); 297SELECT cube_enlarge('(0)'::cube, 0, 2); 298SELECT cube_enlarge('(2),(-2)'::cube, 0, 4); 299SELECT cube_enlarge('(0)'::cube, 1, 0); 300SELECT cube_enlarge('(0)'::cube, 1, 1); 301SELECT cube_enlarge('(0)'::cube, 1, 2); 302SELECT cube_enlarge('(2),(-2)'::cube, 1, 4); 303SELECT cube_enlarge('(0)'::cube, -1, 0); 304SELECT cube_enlarge('(0)'::cube, -1, 1); 305SELECT cube_enlarge('(0)'::cube, -1, 2); 306SELECT cube_enlarge('(2),(-2)'::cube, -1, 4); 307SELECT cube_enlarge('(0,0,0)'::cube, 1, 0); 308SELECT cube_enlarge('(0,0,0)'::cube, 1, 2); 309SELECT cube_enlarge('(2,-2),(-3,7)'::cube, 1, 2); 310SELECT cube_enlarge('(2,-2),(-3,7)'::cube, 3, 2); 311SELECT cube_enlarge('(2,-2),(-3,7)'::cube, -1, 2); 312SELECT cube_enlarge('(2,-2),(-3,7)'::cube, -3, 2); 313SELECT cube_enlarge('(42,-23,-23),(42,23,23)'::cube, -23, 5); 314SELECT cube_enlarge('(42,-23,-23),(42,23,23)'::cube, -24, 5); 315 316-- Test of cube_union (MBR for two cubes) 317-- 318SELECT cube_union('(1,2),(3,4)'::cube, '(5,6,7),(8,9,10)'::cube); 319SELECT cube_union('(1,2)'::cube, '(4,2,0,0)'::cube); 320SELECT cube_union('(1,2),(1,2)'::cube, '(4,2),(4,2)'::cube); 321SELECT cube_union('(1,2),(1,2)'::cube, '(1,2),(1,2)'::cube); 322SELECT cube_union('(1,2),(1,2)'::cube, '(1,2,0),(1,2,0)'::cube); 323 324-- Test of cube_inter 325-- 326SELECT cube_inter('(1,2),(10,11)'::cube, '(3,4), (16,15)'::cube); -- intersects 327SELECT cube_inter('(1,2),(10,11)'::cube, '(3,4), (6,5)'::cube); -- includes 328SELECT cube_inter('(1,2),(10,11)'::cube, '(13,14), (16,15)'::cube); -- no intersection 329SELECT cube_inter('(1,2),(10,11)'::cube, '(3,14), (16,15)'::cube); -- no intersection, but one dimension intersects 330SELECT cube_inter('(1,2),(10,11)'::cube, '(10,11), (16,15)'::cube); -- point intersection 331SELECT cube_inter('(1,2,3)'::cube, '(1,2,3)'::cube); -- point args 332SELECT cube_inter('(1,2,3)'::cube, '(5,6,3)'::cube); -- point args 333 334-- Test of cube_size 335-- 336SELECT cube_size('(4,8),(15,16)'::cube); 337SELECT cube_size('(42,137)'::cube); 338 339-- Test of distances (euclidean distance may not be bit-exact) 340-- 341SET extra_float_digits = 0; 342SELECT cube_distance('(1,1)'::cube, '(4,5)'::cube); 343SELECT '(1,1)'::cube <-> '(4,5)'::cube as d_e; 344RESET extra_float_digits; 345SELECT distance_chebyshev('(1,1)'::cube, '(4,5)'::cube); 346SELECT '(1,1)'::cube <=> '(4,5)'::cube as d_c; 347SELECT distance_taxicab('(1,1)'::cube, '(4,5)'::cube); 348SELECT '(1,1)'::cube <#> '(4,5)'::cube as d_t; 349-- zero for overlapping 350SELECT cube_distance('(2,2),(10,10)'::cube, '(0,0),(5,5)'::cube); 351SELECT distance_chebyshev('(2,2),(10,10)'::cube, '(0,0),(5,5)'::cube); 352SELECT distance_taxicab('(2,2),(10,10)'::cube, '(0,0),(5,5)'::cube); 353-- coordinate access 354SELECT cube(array[10,20,30], array[40,50,60])->1; 355SELECT cube(array[40,50,60], array[10,20,30])->1; 356SELECT cube(array[10,20,30], array[40,50,60])->6; 357SELECT cube(array[10,20,30], array[40,50,60])->0; 358SELECT cube(array[10,20,30], array[40,50,60])->7; 359SELECT cube(array[10,20,30], array[40,50,60])->-1; 360SELECT cube(array[10,20,30], array[40,50,60])->-6; 361SELECT cube(array[10,20,30])->3; 362SELECT cube(array[10,20,30])->6; 363SELECT cube(array[10,20,30])->-6; 364-- "normalized" coordinate access 365SELECT cube(array[10,20,30], array[40,50,60])~>1; 366SELECT cube(array[40,50,60], array[10,20,30])~>1; 367SELECT cube(array[10,20,30], array[40,50,60])~>2; 368SELECT cube(array[40,50,60], array[10,20,30])~>2; 369SELECT cube(array[10,20,30], array[40,50,60])~>3; 370SELECT cube(array[40,50,60], array[10,20,30])~>3; 371 372SELECT cube(array[40,50,60], array[10,20,30])~>0; 373SELECT cube(array[40,50,60], array[10,20,30])~>4; 374SELECT cube(array[40,50,60], array[10,20,30])~>(-1); 375 376-- Load some example data and build the index 377-- 378CREATE TABLE test_cube (c cube); 379 380\copy test_cube from 'data/test_cube.data' 381 382CREATE INDEX test_cube_ix ON test_cube USING gist (c); 383SELECT * FROM test_cube WHERE c && '(3000,1000),(0,0)' ORDER BY c; 384 385-- Test sorting 386SELECT * FROM test_cube WHERE c && '(3000,1000),(0,0)' GROUP BY c ORDER BY c; 387 388-- Test index-only scans 389SET enable_bitmapscan = false; 390EXPLAIN (COSTS OFF) 391SELECT c FROM test_cube WHERE c <@ '(3000,1000),(0,0)' ORDER BY c; 392SELECT c FROM test_cube WHERE c <@ '(3000,1000),(0,0)' ORDER BY c; 393RESET enable_bitmapscan; 394 395-- Test kNN 396INSERT INTO test_cube VALUES ('(1,1)'), ('(100000)'), ('(0, 100000)'); -- Some corner cases 397SET enable_seqscan = false; 398 399-- Test different metrics 400SET extra_float_digits = 0; 401SELECT *, c <-> '(100, 100),(500, 500)'::cube as dist FROM test_cube ORDER BY c <-> '(100, 100),(500, 500)'::cube LIMIT 5; 402RESET extra_float_digits; 403SELECT *, c <=> '(100, 100),(500, 500)'::cube as dist FROM test_cube ORDER BY c <=> '(100, 100),(500, 500)'::cube LIMIT 5; 404SELECT *, c <#> '(100, 100),(500, 500)'::cube as dist FROM test_cube ORDER BY c <#> '(100, 100),(500, 500)'::cube LIMIT 5; 405 406-- Test sorting by coordinates 407SELECT c~>1, c FROM test_cube ORDER BY c~>1 LIMIT 15; -- ascending by left bound 408SELECT c~>2, c FROM test_cube ORDER BY c~>2 LIMIT 15; -- ascending by right bound 409SELECT c~>3, c FROM test_cube ORDER BY c~>3 LIMIT 15; -- ascending by lower bound 410SELECT c~>4, c FROM test_cube ORDER BY c~>4 LIMIT 15; -- ascending by upper bound 411SELECT c~>(-1), c FROM test_cube ORDER BY c~>(-1) LIMIT 15; -- descending by left bound 412SELECT c~>(-2), c FROM test_cube ORDER BY c~>(-2) LIMIT 15; -- descending by right bound 413SELECT c~>(-3), c FROM test_cube ORDER BY c~>(-3) LIMIT 15; -- descending by lower bound 414SELECT c~>(-4), c FROM test_cube ORDER BY c~>(-4) LIMIT 15; -- descending by upper bound 415 416-- Same queries with sequential scan (should give the same results as above) 417RESET enable_seqscan; 418SET enable_indexscan = OFF; 419SET extra_float_digits = 0; 420SELECT *, c <-> '(100, 100),(500, 500)'::cube as dist FROM test_cube ORDER BY c <-> '(100, 100),(500, 500)'::cube LIMIT 5; 421RESET extra_float_digits; 422SELECT *, c <=> '(100, 100),(500, 500)'::cube as dist FROM test_cube ORDER BY c <=> '(100, 100),(500, 500)'::cube LIMIT 5; 423SELECT *, c <#> '(100, 100),(500, 500)'::cube as dist FROM test_cube ORDER BY c <#> '(100, 100),(500, 500)'::cube LIMIT 5; 424SELECT c~>1, c FROM test_cube ORDER BY c~>1 LIMIT 15; -- ascending by left bound 425SELECT c~>2, c FROM test_cube ORDER BY c~>2 LIMIT 15; -- ascending by right bound 426SELECT c~>3, c FROM test_cube ORDER BY c~>3 LIMIT 15; -- ascending by lower bound 427SELECT c~>4, c FROM test_cube ORDER BY c~>4 LIMIT 15; -- ascending by upper bound 428SELECT c~>(-1), c FROM test_cube ORDER BY c~>(-1) LIMIT 15; -- descending by left bound 429SELECT c~>(-2), c FROM test_cube ORDER BY c~>(-2) LIMIT 15; -- descending by right bound 430SELECT c~>(-3), c FROM test_cube ORDER BY c~>(-3) LIMIT 15; -- descending by lower bound 431SELECT c~>(-4), c FROM test_cube ORDER BY c~>(-4) LIMIT 15; -- descending by upper bound 432RESET enable_indexscan; 433