diff options
Diffstat (limited to 'contrib/cube/expected/cube.out')
| -rw-r--r-- | contrib/cube/expected/cube.out | 1950 |
1 files changed, 1950 insertions, 0 deletions
diff --git a/contrib/cube/expected/cube.out b/contrib/cube/expected/cube.out new file mode 100644 index 0000000..5b89cb1 --- /dev/null +++ b/contrib/cube/expected/cube.out @@ -0,0 +1,1950 @@ +-- +-- Test cube datatype +-- +CREATE EXTENSION cube; +-- Check whether any of our opclasses fail amvalidate +SELECT amname, opcname +FROM pg_opclass opc LEFT JOIN pg_am am ON am.oid = opcmethod +WHERE opc.oid >= 16384 AND NOT amvalidate(opc.oid); + amname | opcname +--------+--------- +(0 rows) + +-- +-- testing the input and output functions +-- +-- Any number (a one-dimensional point) +SELECT '1'::cube AS cube; + cube +------ + (1) +(1 row) + +SELECT '-1'::cube AS cube; + cube +------ + (-1) +(1 row) + +SELECT '1.'::cube AS cube; + cube +------ + (1) +(1 row) + +SELECT '-1.'::cube AS cube; + cube +------ + (-1) +(1 row) + +SELECT '.1'::cube AS cube; + cube +------- + (0.1) +(1 row) + +SELECT '-.1'::cube AS cube; + cube +-------- + (-0.1) +(1 row) + +SELECT '1.0'::cube AS cube; + cube +------ + (1) +(1 row) + +SELECT '-1.0'::cube AS cube; + cube +------ + (-1) +(1 row) + +SELECT 'infinity'::cube AS cube; + cube +------------ + (Infinity) +(1 row) + +SELECT '-infinity'::cube AS cube; + cube +------------- + (-Infinity) +(1 row) + +SELECT 'NaN'::cube AS cube; + cube +------- + (NaN) +(1 row) + +SELECT '.1234567890123456'::cube AS cube; + cube +---------------------- + (0.1234567890123456) +(1 row) + +SELECT '+.1234567890123456'::cube AS cube; + cube +---------------------- + (0.1234567890123456) +(1 row) + +SELECT '-.1234567890123456'::cube AS cube; + cube +----------------------- + (-0.1234567890123456) +(1 row) + +-- simple lists (points) +SELECT '()'::cube AS cube; + cube +------ + () +(1 row) + +SELECT '1,2'::cube AS cube; + cube +-------- + (1, 2) +(1 row) + +SELECT '(1,2)'::cube AS cube; + cube +-------- + (1, 2) +(1 row) + +SELECT '1,2,3,4,5'::cube AS cube; + cube +----------------- + (1, 2, 3, 4, 5) +(1 row) + +SELECT '(1,2,3,4,5)'::cube AS cube; + cube +----------------- + (1, 2, 3, 4, 5) +(1 row) + +-- double lists (cubes) +SELECT '(),()'::cube AS cube; + cube +------ + () +(1 row) + +SELECT '(0),(0)'::cube AS cube; + cube +------ + (0) +(1 row) + +SELECT '(0),(1)'::cube AS cube; + cube +--------- + (0),(1) +(1 row) + +SELECT '[(0),(0)]'::cube AS cube; + cube +------ + (0) +(1 row) + +SELECT '[(0),(1)]'::cube AS cube; + cube +--------- + (0),(1) +(1 row) + +SELECT '(0,0,0,0),(0,0,0,0)'::cube AS cube; + cube +-------------- + (0, 0, 0, 0) +(1 row) + +SELECT '(0,0,0,0),(1,0,0,0)'::cube AS cube; + cube +--------------------------- + (0, 0, 0, 0),(1, 0, 0, 0) +(1 row) + +SELECT '[(0,0,0,0),(0,0,0,0)]'::cube AS cube; + cube +-------------- + (0, 0, 0, 0) +(1 row) + +SELECT '[(0,0,0,0),(1,0,0,0)]'::cube AS cube; + cube +--------------------------- + (0, 0, 0, 0),(1, 0, 0, 0) +(1 row) + +-- invalid input: parse errors +SELECT ''::cube AS cube; +ERROR: invalid input syntax for cube +LINE 1: SELECT ''::cube AS cube; + ^ +DETAIL: syntax error at end of input +SELECT 'ABC'::cube AS cube; +ERROR: invalid input syntax for cube +LINE 1: SELECT 'ABC'::cube AS cube; + ^ +DETAIL: syntax error at or near "A" +SELECT '[]'::cube AS cube; +ERROR: invalid input syntax for cube +LINE 1: SELECT '[]'::cube AS cube; + ^ +DETAIL: syntax error at or near "]" +SELECT '[()]'::cube AS cube; +ERROR: invalid input syntax for cube +LINE 1: SELECT '[()]'::cube AS cube; + ^ +DETAIL: syntax error at or near "]" +SELECT '[(1)]'::cube AS cube; +ERROR: invalid input syntax for cube +LINE 1: SELECT '[(1)]'::cube AS cube; + ^ +DETAIL: syntax error at or near "]" +SELECT '[(1),]'::cube AS cube; +ERROR: invalid input syntax for cube +LINE 1: SELECT '[(1),]'::cube AS cube; + ^ +DETAIL: syntax error at or near "]" +SELECT '[(1),2]'::cube AS cube; +ERROR: invalid input syntax for cube +LINE 1: SELECT '[(1),2]'::cube AS cube; + ^ +DETAIL: syntax error at or near "2" +SELECT '[(1),(2),(3)]'::cube AS cube; +ERROR: invalid input syntax for cube +LINE 1: SELECT '[(1),(2),(3)]'::cube AS cube; + ^ +DETAIL: syntax error at or near "," +SELECT '1,'::cube AS cube; +ERROR: invalid input syntax for cube +LINE 1: SELECT '1,'::cube AS cube; + ^ +DETAIL: syntax error at end of input +SELECT '1,2,'::cube AS cube; +ERROR: invalid input syntax for cube +LINE 1: SELECT '1,2,'::cube AS cube; + ^ +DETAIL: syntax error at end of input +SELECT '1,,2'::cube AS cube; +ERROR: invalid input syntax for cube +LINE 1: SELECT '1,,2'::cube AS cube; + ^ +DETAIL: syntax error at or near "," +SELECT '(1,)'::cube AS cube; +ERROR: invalid input syntax for cube +LINE 1: SELECT '(1,)'::cube AS cube; + ^ +DETAIL: syntax error at or near ")" +SELECT '(1,2,)'::cube AS cube; +ERROR: invalid input syntax for cube +LINE 1: SELECT '(1,2,)'::cube AS cube; + ^ +DETAIL: syntax error at or near ")" +SELECT '(1,,2)'::cube AS cube; +ERROR: invalid input syntax for cube +LINE 1: SELECT '(1,,2)'::cube AS cube; + ^ +DETAIL: syntax error at or near "," +-- invalid input: semantic errors and trailing garbage +SELECT '[(1),(2)],'::cube AS cube; -- 0 +ERROR: invalid input syntax for cube +LINE 1: SELECT '[(1),(2)],'::cube AS cube; + ^ +DETAIL: syntax error at or near "," +SELECT '[(1,2,3),(2,3)]'::cube AS cube; -- 1 +ERROR: invalid input syntax for cube +LINE 1: SELECT '[(1,2,3),(2,3)]'::cube AS cube; + ^ +DETAIL: Different point dimensions in (1,2,3) and (2,3). +SELECT '[(1,2),(1,2,3)]'::cube AS cube; -- 1 +ERROR: invalid input syntax for cube +LINE 1: SELECT '[(1,2),(1,2,3)]'::cube AS cube; + ^ +DETAIL: Different point dimensions in (1,2) and (1,2,3). +SELECT '(1),(2),'::cube AS cube; -- 2 +ERROR: invalid input syntax for cube +LINE 1: SELECT '(1),(2),'::cube AS cube; + ^ +DETAIL: syntax error at or near "," +SELECT '(1,2,3),(2,3)'::cube AS cube; -- 3 +ERROR: invalid input syntax for cube +LINE 1: SELECT '(1,2,3),(2,3)'::cube AS cube; + ^ +DETAIL: Different point dimensions in (1,2,3) and (2,3). +SELECT '(1,2),(1,2,3)'::cube AS cube; -- 3 +ERROR: invalid input syntax for cube +LINE 1: SELECT '(1,2),(1,2,3)'::cube AS cube; + ^ +DETAIL: Different point dimensions in (1,2) and (1,2,3). +SELECT '(1,2,3)ab'::cube AS cube; -- 4 +ERROR: invalid input syntax for cube +LINE 1: SELECT '(1,2,3)ab'::cube AS cube; + ^ +DETAIL: syntax error at or near "a" +SELECT '(1,2,3)a'::cube AS cube; -- 5 +ERROR: invalid input syntax for cube +LINE 1: SELECT '(1,2,3)a'::cube AS cube; + ^ +DETAIL: syntax error at or near "a" +SELECT '(1,2)('::cube AS cube; -- 5 +ERROR: invalid input syntax for cube +LINE 1: SELECT '(1,2)('::cube AS cube; + ^ +DETAIL: syntax error at or near "(" +SELECT '1,2ab'::cube AS cube; -- 6 +ERROR: invalid input syntax for cube +LINE 1: SELECT '1,2ab'::cube AS cube; + ^ +DETAIL: syntax error at or near "a" +SELECT '1 e7'::cube AS cube; -- 6 +ERROR: invalid input syntax for cube +LINE 1: SELECT '1 e7'::cube AS cube; + ^ +DETAIL: syntax error at or near "e" +SELECT '1,2a'::cube AS cube; -- 7 +ERROR: invalid input syntax for cube +LINE 1: SELECT '1,2a'::cube AS cube; + ^ +DETAIL: syntax error at or near "a" +SELECT '1..2'::cube AS cube; -- 7 +ERROR: invalid input syntax for cube +LINE 1: SELECT '1..2'::cube AS cube; + ^ +DETAIL: syntax error at or near ".2" +SELECT '-1e-700'::cube AS cube; -- out of range +ERROR: "-1e-700" is out of range for type double precision +LINE 1: SELECT '-1e-700'::cube AS cube; + ^ +-- +-- Testing building cubes from float8 values +-- +SELECT cube(0::float8); + cube +------ + (0) +(1 row) + +SELECT cube(1::float8); + cube +------ + (1) +(1 row) + +SELECT cube(1,2); + cube +--------- + (1),(2) +(1 row) + +SELECT cube(cube(1,2),3); + cube +--------------- + (1, 3),(2, 3) +(1 row) + +SELECT cube(cube(1,2),3,4); + cube +--------------- + (1, 3),(2, 4) +(1 row) + +SELECT cube(cube(cube(1,2),3,4),5); + cube +--------------------- + (1, 3, 5),(2, 4, 5) +(1 row) + +SELECT cube(cube(cube(1,2),3,4),5,6); + cube +--------------------- + (1, 3, 5),(2, 4, 6) +(1 row) + +-- +-- Test that the text -> cube cast was installed. +-- +SELECT '(0)'::text::cube; + cube +------ + (0) +(1 row) + +-- +-- Test the float[] -> cube cast +-- +SELECT cube('{0,1,2}'::float[], '{3,4,5}'::float[]); + cube +--------------------- + (0, 1, 2),(3, 4, 5) +(1 row) + +SELECT cube('{0,1,2}'::float[], '{3}'::float[]); +ERROR: UR and LL arrays must be of same length +SELECT cube(NULL::float[], '{3}'::float[]); + cube +------ + +(1 row) + +SELECT cube('{0,1,2}'::float[]); + cube +----------- + (0, 1, 2) +(1 row) + +SELECT cube_subset(cube('(1,3,5),(6,7,8)'), ARRAY[3,2,1,1]); + cube_subset +--------------------------- + (5, 3, 1, 1),(8, 7, 6, 6) +(1 row) + +SELECT cube_subset(cube('(1,3,5),(1,3,5)'), ARRAY[3,2,1,1]); + cube_subset +-------------- + (5, 3, 1, 1) +(1 row) + +SELECT cube_subset(cube('(1,3,5),(6,7,8)'), ARRAY[4,0]); +ERROR: Index out of bounds +SELECT cube_subset(cube('(6,7,8),(6,7,8)'), ARRAY[4,0]); +ERROR: Index out of bounds +-- test for limits: this should pass +SELECT cube_subset(cube('(6,7,8),(6,7,8)'), array(SELECT 1 as a FROM generate_series(1,100))); + cube_subset +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + (6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6) +(1 row) + +-- and this should fail +SELECT cube_subset(cube('(6,7,8),(6,7,8)'), array(SELECT 1 as a FROM generate_series(1,101))); +ERROR: array is too long +DETAIL: A cube cannot have more than 100 dimensions. +-- +-- Test point processing +-- +SELECT cube('(1,2),(1,2)'); -- cube_in + cube +-------- + (1, 2) +(1 row) + +SELECT cube('{0,1,2}'::float[], '{0,1,2}'::float[]); -- cube_a_f8_f8 + cube +----------- + (0, 1, 2) +(1 row) + +SELECT cube('{5,6,7,8}'::float[]); -- cube_a_f8 + cube +-------------- + (5, 6, 7, 8) +(1 row) + +SELECT cube(1.37); -- cube_f8 + cube +-------- + (1.37) +(1 row) + +SELECT cube(1.37, 1.37); -- cube_f8_f8 + cube +-------- + (1.37) +(1 row) + +SELECT cube(cube(1,1), 42); -- cube_c_f8 + cube +--------- + (1, 42) +(1 row) + +SELECT cube(cube(1,2), 42); -- cube_c_f8 + cube +----------------- + (1, 42),(2, 42) +(1 row) + +SELECT cube(cube(1,1), 42, 42); -- cube_c_f8_f8 + cube +--------- + (1, 42) +(1 row) + +SELECT cube(cube(1,1), 42, 24); -- cube_c_f8_f8 + cube +----------------- + (1, 42),(1, 24) +(1 row) + +SELECT cube(cube(1,2), 42, 42); -- cube_c_f8_f8 + cube +----------------- + (1, 42),(2, 42) +(1 row) + +SELECT cube(cube(1,2), 42, 24); -- cube_c_f8_f8 + cube +----------------- + (1, 42),(2, 24) +(1 row) + +-- +-- Testing limit of CUBE_MAX_DIM dimensions check in cube_in. +-- +-- create too big cube from literal +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; +ERROR: invalid input syntax for cube +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... + ^ +DETAIL: A cube cannot have more than 100 dimensions. +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; +ERROR: invalid input syntax for cube +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... + ^ +DETAIL: A cube cannot have more than 100 dimensions. +-- from an array +select cube(array(SELECT 0 as a FROM generate_series(1,101))); +ERROR: array is too long +DETAIL: A cube cannot have more than 100 dimensions. +select cube(array(SELECT 0 as a FROM generate_series(1,101)),array(SELECT 0 as a FROM generate_series(1,101))); +ERROR: can't extend cube +DETAIL: A cube cannot have more than 100 dimensions. +-- extend cube beyond limit +-- this should work +select cube(array(SELECT 0 as a FROM generate_series(1,100))); + cube +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + (0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0) +(1 row) + +select cube(array(SELECT 0 as a FROM generate_series(1,100)),array(SELECT 0 as a FROM generate_series(1,100))); + cube +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + (0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0) +(1 row) + +-- this should fail +select cube(cube(array(SELECT 0 as a FROM generate_series(1,100))), 0); +ERROR: can't extend cube +DETAIL: A cube cannot have more than 100 dimensions. +select cube(cube(array(SELECT 0 as a FROM generate_series(1,100)),array(SELECT 0 as a FROM generate_series(1,100))), 0, 0); +ERROR: can't extend cube +DETAIL: A cube cannot have more than 100 dimensions. +-- +-- testing the operators +-- +-- equality/inequality: +-- +SELECT '24, 33.20'::cube = '24, 33.20'::cube AS bool; + bool +------ + t +(1 row) + +SELECT '24, 33.20'::cube != '24, 33.20'::cube AS bool; + bool +------ + f +(1 row) + +SELECT '24, 33.20'::cube = '24, 33.21'::cube AS bool; + bool +------ + f +(1 row) + +SELECT '24, 33.20'::cube != '24, 33.21'::cube AS bool; + bool +------ + t +(1 row) + +SELECT '(2,0),(3,1)'::cube = '(2,0,0,0,0),(3,1,0,0,0)'::cube AS bool; + bool +------ + f +(1 row) + +SELECT '(2,0),(3,1)'::cube = '(2,0,0,0,0),(3,1,0,0,1)'::cube AS bool; + bool +------ + f +(1 row) + +-- "lower than" / "greater than" +-- (these operators are not useful for anything but ordering) +-- +SELECT '1'::cube > '2'::cube AS bool; + bool +------ + f +(1 row) + +SELECT '1'::cube < '2'::cube AS bool; + bool +------ + t +(1 row) + +SELECT '1,1'::cube > '1,2'::cube AS bool; + bool +------ + f +(1 row) + +SELECT '1,1'::cube < '1,2'::cube AS bool; + bool +------ + t +(1 row) + +SELECT '(2,0),(3,1)'::cube > '(2,0,0,0,0),(3,1,0,0,1)'::cube AS bool; + bool +------ + f +(1 row) + +SELECT '(2,0),(3,1)'::cube < '(2,0,0,0,0),(3,1,0,0,1)'::cube AS bool; + bool +------ + t +(1 row) + +SELECT '(2,0),(3,1)'::cube > '(2,0,0,0,1),(3,1,0,0,0)'::cube AS bool; + bool +------ + f +(1 row) + +SELECT '(2,0),(3,1)'::cube < '(2,0,0,0,1),(3,1,0,0,0)'::cube AS bool; + bool +------ + t +(1 row) + +SELECT '(2,0),(3,1)'::cube > '(2,0,0,0,0),(3,1,0,0,0)'::cube AS bool; + bool +------ + f +(1 row) + +SELECT '(2,0),(3,1)'::cube < '(2,0,0,0,0),(3,1,0,0,0)'::cube AS bool; + bool +------ + t +(1 row) + +SELECT '(2,0,0,0,0),(3,1,0,0,1)'::cube > '(2,0),(3,1)'::cube AS bool; + bool +------ + t +(1 row) + +SELECT '(2,0,0,0,0),(3,1,0,0,1)'::cube < '(2,0),(3,1)'::cube AS bool; + bool +------ + f +(1 row) + +SELECT '(2,0,0,0,1),(3,1,0,0,0)'::cube > '(2,0),(3,1)'::cube AS bool; + bool +------ + t +(1 row) + +SELECT '(2,0,0,0,1),(3,1,0,0,0)'::cube < '(2,0),(3,1)'::cube AS bool; + bool +------ + f +(1 row) + +SELECT '(2,0,0,0,0),(3,1,0,0,0)'::cube > '(2,0),(3,1)'::cube AS bool; + bool +------ + t +(1 row) + +SELECT '(2,0,0,0,0),(3,1,0,0,0)'::cube < '(2,0),(3,1)'::cube AS bool; + bool +------ + f +(1 row) + +-- "overlap" +-- +SELECT '1'::cube && '1'::cube AS bool; + bool +------ + t +(1 row) + +SELECT '1'::cube && '2'::cube AS bool; + bool +------ + f +(1 row) + +SELECT '[(-1,-1,-1),(1,1,1)]'::cube && '0'::cube AS bool; + bool +------ + t +(1 row) + +SELECT '[(-1,-1,-1),(1,1,1)]'::cube && '1'::cube AS bool; + bool +------ + t +(1 row) + +SELECT '[(-1,-1,-1),(1,1,1)]'::cube && '1,1,1'::cube AS bool; + bool +------ + t +(1 row) + +SELECT '[(-1,-1,-1),(1,1,1)]'::cube && '[(1,1,1),(2,2,2)]'::cube AS bool; + bool +------ + t +(1 row) + +SELECT '[(-1,-1,-1),(1,1,1)]'::cube && '[(1,1),(2,2)]'::cube AS bool; + bool +------ + t +(1 row) + +SELECT '[(-1,-1,-1),(1,1,1)]'::cube && '[(2,1,1),(2,2,2)]'::cube AS bool; + bool +------ + f +(1 row) + +-- "contained in" (the left operand is the cube entirely enclosed by +-- the right operand): +-- +SELECT '0'::cube <@ '0'::cube AS bool; + bool +------ + t +(1 row) + +SELECT '0,0,0'::cube <@ '0,0,0'::cube AS bool; + bool +------ + t +(1 row) + +SELECT '0,0'::cube <@ '0,0,1'::cube AS bool; + bool +------ + t +(1 row) + +SELECT '0,0,0'::cube <@ '0,0,1'::cube AS bool; + bool +------ + f +(1 row) + +SELECT '1,0,0'::cube <@ '0,0,1'::cube AS bool; + bool +------ + f +(1 row) + +SELECT '(1,0,0),(0,0,1)'::cube <@ '(1,0,0),(0,0,1)'::cube AS bool; + bool +------ + t +(1 row) + +SELECT '(1,0,0),(0,0,1)'::cube <@ '(-1,-1,-1),(1,1,1)'::cube AS bool; + bool +------ + t +(1 row) + +SELECT '(1,0,0),(0,0,1)'::cube <@ '(-1,-1,-1,-1),(1,1,1,1)'::cube AS bool; + bool +------ + t +(1 row) + +SELECT '0'::cube <@ '(-1),(1)'::cube AS bool; + bool +------ + t +(1 row) + +SELECT '1'::cube <@ '(-1),(1)'::cube AS bool; + bool +------ + t +(1 row) + +SELECT '-1'::cube <@ '(-1),(1)'::cube AS bool; + bool +------ + t +(1 row) + +SELECT '(-1),(1)'::cube <@ '(-1),(1)'::cube AS bool; + bool +------ + t +(1 row) + +SELECT '(-1),(1)'::cube <@ '(-1,-1),(1,1)'::cube AS bool; + bool +------ + t +(1 row) + +SELECT '(-2),(1)'::cube <@ '(-1),(1)'::cube AS bool; + bool +------ + f +(1 row) + +SELECT '(-2),(1)'::cube <@ '(-1,-1),(1,1)'::cube AS bool; + bool +------ + f +(1 row) + +-- "contains" (the left operand is the cube that entirely encloses the +-- right operand) +-- +SELECT '0'::cube @> '0'::cube AS bool; + bool +------ + t +(1 row) + +SELECT '0,0,0'::cube @> '0,0,0'::cube AS bool; + bool +------ + t +(1 row) + +SELECT '0,0,1'::cube @> '0,0'::cube AS bool; + bool +------ + t +(1 row) + +SELECT '0,0,1'::cube @> '0,0,0'::cube AS bool; + bool +------ + f +(1 row) + +SELECT '0,0,1'::cube @> '1,0,0'::cube AS bool; + bool +------ + f +(1 row) + +SELECT '(1,0,0),(0,0,1)'::cube @> '(1,0,0),(0,0,1)'::cube AS bool; + bool +------ + t +(1 row) + +SELECT '(-1,-1,-1),(1,1,1)'::cube @> '(1,0,0),(0,0,1)'::cube AS bool; + bool +------ + t +(1 row) + +SELECT '(-1,-1,-1,-1),(1,1,1,1)'::cube @> '(1,0,0),(0,0,1)'::cube AS bool; + bool +------ + t +(1 row) + +SELECT '(-1),(1)'::cube @> '0'::cube AS bool; + bool +------ + t +(1 row) + +SELECT '(-1),(1)'::cube @> '1'::cube AS bool; + bool +------ + t +(1 row) + +SELECT '(-1),(1)'::cube @> '-1'::cube AS bool; + bool +------ + t +(1 row) + +SELECT '(-1),(1)'::cube @> '(-1),(1)'::cube AS bool; + bool +------ + t +(1 row) + +SELECT '(-1,-1),(1,1)'::cube @> '(-1),(1)'::cube AS bool; + bool +------ + t +(1 row) + +SELECT '(-1),(1)'::cube @> '(-2),(1)'::cube AS bool; + bool +------ + f +(1 row) + +SELECT '(-1,-1),(1,1)'::cube @> '(-2),(1)'::cube AS bool; + bool +------ + f +(1 row) + +-- Test of distance function +-- +SELECT cube_distance('(0)'::cube,'(2,2,2,2)'::cube); + cube_distance +--------------- + 4 +(1 row) + +SELECT cube_distance('(0)'::cube,'(.3,.4)'::cube); + cube_distance +--------------- + 0.5 +(1 row) + +SELECT cube_distance('(2,3,4)'::cube,'(2,3,4)'::cube); + cube_distance +--------------- + 0 +(1 row) + +SELECT cube_distance('(42,42,42,42)'::cube,'(137,137,137,137)'::cube); + cube_distance +--------------- + 190 +(1 row) + +SELECT cube_distance('(42,42,42)'::cube,'(137,137)'::cube); + cube_distance +-------------------- + 140.76221083799445 +(1 row) + +-- Test of cube function (text to cube) +-- +SELECT cube('(1,1.2)'::text); + cube +---------- + (1, 1.2) +(1 row) + +SELECT cube(NULL); + cube +------ + +(1 row) + +-- Test of cube_dim function (dimensions stored in cube) +-- +SELECT cube_dim('(0)'::cube); + cube_dim +---------- + 1 +(1 row) + +SELECT cube_dim('(0,0)'::cube); + cube_dim +---------- + 2 +(1 row) + +SELECT cube_dim('(0,0,0)'::cube); + cube_dim +---------- + 3 +(1 row) + +SELECT cube_dim('(42,42,42),(42,42,42)'::cube); + cube_dim +---------- + 3 +(1 row) + +SELECT cube_dim('(4,8,15,16,23),(4,8,15,16,23)'::cube); + cube_dim +---------- + 5 +(1 row) + +-- Test of cube_ll_coord function (retrieves LL coordinate values) +-- +SELECT cube_ll_coord('(-1,1),(2,-2)'::cube, 1); + cube_ll_coord +--------------- + -1 +(1 row) + +SELECT cube_ll_coord('(-1,1),(2,-2)'::cube, 2); + cube_ll_coord +--------------- + -2 +(1 row) + +SELECT cube_ll_coord('(-1,1),(2,-2)'::cube, 3); + cube_ll_coord +--------------- + 0 +(1 row) + +SELECT cube_ll_coord('(1,2),(1,2)'::cube, 1); + cube_ll_coord +--------------- + 1 +(1 row) + +SELECT cube_ll_coord('(1,2),(1,2)'::cube, 2); + cube_ll_coord +--------------- + 2 +(1 row) + +SELECT cube_ll_coord('(1,2),(1,2)'::cube, 3); + cube_ll_coord +--------------- + 0 +(1 row) + +SELECT cube_ll_coord('(42,137)'::cube, 1); + cube_ll_coord +--------------- + 42 +(1 row) + +SELECT cube_ll_coord('(42,137)'::cube, 2); + cube_ll_coord +--------------- + 137 +(1 row) + +SELECT cube_ll_coord('(42,137)'::cube, 3); + cube_ll_coord +--------------- + 0 +(1 row) + +-- Test of cube_ur_coord function (retrieves UR coordinate values) +-- +SELECT cube_ur_coord('(-1,1),(2,-2)'::cube, 1); + cube_ur_coord +--------------- + 2 +(1 row) + +SELECT cube_ur_coord('(-1,1),(2,-2)'::cube, 2); + cube_ur_coord +--------------- + 1 +(1 row) + +SELECT cube_ur_coord('(-1,1),(2,-2)'::cube, 3); + cube_ur_coord +--------------- + 0 +(1 row) + +SELECT cube_ur_coord('(1,2),(1,2)'::cube, 1); + cube_ur_coord +--------------- + 1 +(1 row) + +SELECT cube_ur_coord('(1,2),(1,2)'::cube, 2); + cube_ur_coord +--------------- + 2 +(1 row) + +SELECT cube_ur_coord('(1,2),(1,2)'::cube, 3); + cube_ur_coord +--------------- + 0 +(1 row) + +SELECT cube_ur_coord('(42,137)'::cube, 1); + cube_ur_coord +--------------- + 42 +(1 row) + +SELECT cube_ur_coord('(42,137)'::cube, 2); + cube_ur_coord +--------------- + 137 +(1 row) + +SELECT cube_ur_coord('(42,137)'::cube, 3); + cube_ur_coord +--------------- + 0 +(1 row) + +-- Test of cube_is_point +-- +SELECT cube_is_point('(0)'::cube); + cube_is_point +--------------- + t +(1 row) + +SELECT cube_is_point('(0,1,2)'::cube); + cube_is_point +--------------- + t +(1 row) + +SELECT cube_is_point('(0,1,2),(0,1,2)'::cube); + cube_is_point +--------------- + t +(1 row) + +SELECT cube_is_point('(0,1,2),(-1,1,2)'::cube); + cube_is_point +--------------- + f +(1 row) + +SELECT cube_is_point('(0,1,2),(0,-1,2)'::cube); + cube_is_point +--------------- + f +(1 row) + +SELECT cube_is_point('(0,1,2),(0,1,-2)'::cube); + cube_is_point +--------------- + f +(1 row) + +-- Test of cube_enlarge (enlarging and shrinking cubes) +-- +SELECT cube_enlarge('(0)'::cube, 0, 0); + cube_enlarge +-------------- + (0) +(1 row) + +SELECT cube_enlarge('(0)'::cube, 0, 1); + cube_enlarge +-------------- + (0) +(1 row) + +SELECT cube_enlarge('(0)'::cube, 0, 2); + cube_enlarge +-------------- + (0) +(1 row) + +SELECT cube_enlarge('(2),(-2)'::cube, 0, 4); + cube_enlarge +-------------- + (-2),(2) +(1 row) + +SELECT cube_enlarge('(0)'::cube, 1, 0); + cube_enlarge +-------------- + (-1),(1) +(1 row) + +SELECT cube_enlarge('(0)'::cube, 1, 1); + cube_enlarge +-------------- + (-1),(1) +(1 row) + +SELECT cube_enlarge('(0)'::cube, 1, 2); + cube_enlarge +----------------- + (-1, -1),(1, 1) +(1 row) + +SELECT cube_enlarge('(2),(-2)'::cube, 1, 4); + cube_enlarge +------------------------------- + (-3, -1, -1, -1),(3, 1, 1, 1) +(1 row) + +SELECT cube_enlarge('(0)'::cube, -1, 0); + cube_enlarge +-------------- + (0) +(1 row) + +SELECT cube_enlarge('(0)'::cube, -1, 1); + cube_enlarge +-------------- + (0) +(1 row) + +SELECT cube_enlarge('(0)'::cube, -1, 2); + cube_enlarge +-------------- + (0) +(1 row) + +SELECT cube_enlarge('(2),(-2)'::cube, -1, 4); + cube_enlarge +-------------- + (-1),(1) +(1 row) + +SELECT cube_enlarge('(0,0,0)'::cube, 1, 0); + cube_enlarge +------------------------ + (-1, -1, -1),(1, 1, 1) +(1 row) + +SELECT cube_enlarge('(0,0,0)'::cube, 1, 2); + cube_enlarge +------------------------ + (-1, -1, -1),(1, 1, 1) +(1 row) + +SELECT cube_enlarge('(2,-2),(-3,7)'::cube, 1, 2); + cube_enlarge +----------------- + (-4, -3),(3, 8) +(1 row) + +SELECT cube_enlarge('(2,-2),(-3,7)'::cube, 3, 2); + cube_enlarge +------------------ + (-6, -5),(5, 10) +(1 row) + +SELECT cube_enlarge('(2,-2),(-3,7)'::cube, -1, 2); + cube_enlarge +----------------- + (-2, -1),(1, 6) +(1 row) + +SELECT cube_enlarge('(2,-2),(-3,7)'::cube, -3, 2); + cube_enlarge +--------------------- + (-0.5, 1),(-0.5, 4) +(1 row) + +SELECT cube_enlarge('(42,-23,-23),(42,23,23)'::cube, -23, 5); + cube_enlarge +-------------- + (42, 0, 0) +(1 row) + +SELECT cube_enlarge('(42,-23,-23),(42,23,23)'::cube, -24, 5); + cube_enlarge +-------------- + (42, 0, 0) +(1 row) + +-- Test of cube_union (MBR for two cubes) +-- +SELECT cube_union('(1,2),(3,4)'::cube, '(5,6,7),(8,9,10)'::cube); + cube_union +---------------------- + (1, 2, 0),(8, 9, 10) +(1 row) + +SELECT cube_union('(1,2)'::cube, '(4,2,0,0)'::cube); + cube_union +--------------------------- + (1, 2, 0, 0),(4, 2, 0, 0) +(1 row) + +SELECT cube_union('(1,2),(1,2)'::cube, '(4,2),(4,2)'::cube); + cube_union +--------------- + (1, 2),(4, 2) +(1 row) + +SELECT cube_union('(1,2),(1,2)'::cube, '(1,2),(1,2)'::cube); + cube_union +------------ + (1, 2) +(1 row) + +SELECT cube_union('(1,2),(1,2)'::cube, '(1,2,0),(1,2,0)'::cube); + cube_union +------------ + (1, 2, 0) +(1 row) + +-- Test of cube_inter +-- +SELECT cube_inter('(1,2),(10,11)'::cube, '(3,4), (16,15)'::cube); -- intersects + cube_inter +----------------- + (3, 4),(10, 11) +(1 row) + +SELECT cube_inter('(1,2),(10,11)'::cube, '(3,4), (6,5)'::cube); -- includes + cube_inter +--------------- + (3, 4),(6, 5) +(1 row) + +SELECT cube_inter('(1,2),(10,11)'::cube, '(13,14), (16,15)'::cube); -- no intersection + cube_inter +------------------- + (13, 14),(10, 11) +(1 row) + +SELECT cube_inter('(1,2),(10,11)'::cube, '(3,14), (16,15)'::cube); -- no intersection, but one dimension intersects + cube_inter +------------------ + (3, 14),(10, 11) +(1 row) + +SELECT cube_inter('(1,2),(10,11)'::cube, '(10,11), (16,15)'::cube); -- point intersection + cube_inter +------------ + (10, 11) +(1 row) + +SELECT cube_inter('(1,2,3)'::cube, '(1,2,3)'::cube); -- point args + cube_inter +------------ + (1, 2, 3) +(1 row) + +SELECT cube_inter('(1,2,3)'::cube, '(5,6,3)'::cube); -- point args + cube_inter +--------------------- + (5, 6, 3),(1, 2, 3) +(1 row) + +-- Test of cube_size +-- +SELECT cube_size('(4,8),(15,16)'::cube); + cube_size +----------- + 88 +(1 row) + +SELECT cube_size('(42,137)'::cube); + cube_size +----------- + 0 +(1 row) + +-- Test of distances (euclidean distance may not be bit-exact) +-- +SET extra_float_digits = 0; +SELECT cube_distance('(1,1)'::cube, '(4,5)'::cube); + cube_distance +--------------- + 5 +(1 row) + +SELECT '(1,1)'::cube <-> '(4,5)'::cube as d_e; + d_e +----- + 5 +(1 row) + +RESET extra_float_digits; +SELECT distance_chebyshev('(1,1)'::cube, '(4,5)'::cube); + distance_chebyshev +-------------------- + 4 +(1 row) + +SELECT '(1,1)'::cube <=> '(4,5)'::cube as d_c; + d_c +----- + 4 +(1 row) + +SELECT distance_taxicab('(1,1)'::cube, '(4,5)'::cube); + distance_taxicab +------------------ + 7 +(1 row) + +SELECT '(1,1)'::cube <#> '(4,5)'::cube as d_t; + d_t +----- + 7 +(1 row) + +-- zero for overlapping +SELECT cube_distance('(2,2),(10,10)'::cube, '(0,0),(5,5)'::cube); + cube_distance +--------------- + 0 +(1 row) + +SELECT distance_chebyshev('(2,2),(10,10)'::cube, '(0,0),(5,5)'::cube); + distance_chebyshev +-------------------- + 0 +(1 row) + +SELECT distance_taxicab('(2,2),(10,10)'::cube, '(0,0),(5,5)'::cube); + distance_taxicab +------------------ + 0 +(1 row) + +-- coordinate access +SELECT cube(array[10,20,30], array[40,50,60])->1; + ?column? +---------- + 10 +(1 row) + +SELECT cube(array[40,50,60], array[10,20,30])->1; + ?column? +---------- + 40 +(1 row) + +SELECT cube(array[10,20,30], array[40,50,60])->6; + ?column? +---------- + 60 +(1 row) + +SELECT cube(array[10,20,30], array[40,50,60])->0; +ERROR: cube index 0 is out of bounds +SELECT cube(array[10,20,30], array[40,50,60])->7; +ERROR: cube index 7 is out of bounds +SELECT cube(array[10,20,30], array[40,50,60])->-1; +ERROR: cube index -1 is out of bounds +SELECT cube(array[10,20,30], array[40,50,60])->-6; +ERROR: cube index -6 is out of bounds +SELECT cube(array[10,20,30])->3; + ?column? +---------- + 30 +(1 row) + +SELECT cube(array[10,20,30])->6; + ?column? +---------- + 30 +(1 row) + +SELECT cube(array[10,20,30])->-6; +ERROR: cube index -6 is out of bounds +-- "normalized" coordinate access +SELECT cube(array[10,20,30], array[40,50,60])~>1; + ?column? +---------- + 10 +(1 row) + +SELECT cube(array[40,50,60], array[10,20,30])~>1; + ?column? +---------- + 10 +(1 row) + +SELECT cube(array[10,20,30], array[40,50,60])~>2; + ?column? +---------- + 40 +(1 row) + +SELECT cube(array[40,50,60], array[10,20,30])~>2; + ?column? +---------- + 40 +(1 row) + +SELECT cube(array[10,20,30], array[40,50,60])~>3; + ?column? +---------- + 20 +(1 row) + +SELECT cube(array[40,50,60], array[10,20,30])~>3; + ?column? +---------- + 20 +(1 row) + +SELECT cube(array[40,50,60], array[10,20,30])~>0; +ERROR: zero cube index is not defined +SELECT cube(array[40,50,60], array[10,20,30])~>4; + ?column? +---------- + 50 +(1 row) + +SELECT cube(array[40,50,60], array[10,20,30])~>(-1); + ?column? +---------- + -10 +(1 row) + +-- Load some example data and build the index +-- +CREATE TABLE test_cube (c cube); +\copy test_cube from 'data/test_cube.data' +CREATE INDEX test_cube_ix ON test_cube USING gist (c); +SELECT * FROM test_cube WHERE c && '(3000,1000),(0,0)' ORDER BY c; + c +-------------------------- + (337, 455),(240, 359) + (759, 187),(662, 163) + (1444, 403),(1346, 344) + (1594, 1043),(1517, 971) + (2424, 160),(2424, 81) +(5 rows) + +-- Test sorting +SELECT * FROM test_cube WHERE c && '(3000,1000),(0,0)' GROUP BY c ORDER BY c; + c +-------------------------- + (337, 455),(240, 359) + (759, 187),(662, 163) + (1444, 403),(1346, 344) + (1594, 1043),(1517, 971) + (2424, 160),(2424, 81) +(5 rows) + +-- Test index-only scans +SET enable_bitmapscan = false; +EXPLAIN (COSTS OFF) +SELECT c FROM test_cube WHERE c <@ '(3000,1000),(0,0)' ORDER BY c; + QUERY PLAN +-------------------------------------------------------- + Sort + Sort Key: c + -> Index Only Scan using test_cube_ix on test_cube + Index Cond: (c <@ '(3000, 1000),(0, 0)'::cube) +(4 rows) + +SELECT c FROM test_cube WHERE c <@ '(3000,1000),(0,0)' ORDER BY c; + c +------------------------- + (337, 455),(240, 359) + (759, 187),(662, 163) + (1444, 403),(1346, 344) + (2424, 160),(2424, 81) +(4 rows) + +RESET enable_bitmapscan; +-- Test kNN +INSERT INTO test_cube VALUES ('(1,1)'), ('(100000)'), ('(0, 100000)'); -- Some corner cases +SET enable_seqscan = false; +-- Test different metrics +SET extra_float_digits = 0; +SELECT *, c <-> '(100, 100),(500, 500)'::cube as dist FROM test_cube ORDER BY c <-> '(100, 100),(500, 500)'::cube LIMIT 5; + c | dist +-------------------------+------------------ + (337, 455),(240, 359) | 0 + (1, 1) | 140.007142674936 + (759, 187),(662, 163) | 162 + (948, 1201),(907, 1156) | 772.000647668122 + (1444, 403),(1346, 344) | 846 +(5 rows) + +RESET extra_float_digits; +SELECT *, c <=> '(100, 100),(500, 500)'::cube as dist FROM test_cube ORDER BY c <=> '(100, 100),(500, 500)'::cube LIMIT 5; + c | dist +-------------------------+------ + (337, 455),(240, 359) | 0 + (1, 1) | 99 + (759, 187),(662, 163) | 162 + (948, 1201),(907, 1156) | 656 + (1444, 403),(1346, 344) | 846 +(5 rows) + +SELECT *, c <#> '(100, 100),(500, 500)'::cube as dist FROM test_cube ORDER BY c <#> '(100, 100),(500, 500)'::cube LIMIT 5; + c | dist +-------------------------+------ + (337, 455),(240, 359) | 0 + (759, 187),(662, 163) | 162 + (1, 1) | 198 + (1444, 403),(1346, 344) | 846 + (369, 1457),(278, 1409) | 909 +(5 rows) + +-- Test sorting by coordinates +SELECT c~>1, c FROM test_cube ORDER BY c~>1 LIMIT 15; -- ascending by left bound + ?column? | c +----------+--------------------------- + 0 | (0, 100000) + 1 | (1, 1) + 3 | (54, 38679),(3, 38602) + 15 | (83, 10271),(15, 10265) + 64 | (122, 46832),(64, 46762) + 92 | (167, 17214),(92, 17184) + 107 | (161, 24465),(107, 24374) + 120 | (162, 26040),(120, 25963) + 138 | (154, 4019),(138, 3990) + 175 | (259, 1850),(175, 1820) + 179 | (207, 40886),(179, 40879) + 204 | (288, 49588),(204, 49571) + 226 | (270, 32616),(226, 32607) + 235 | (318, 31489),(235, 31404) + 240 | (337, 455),(240, 359) +(15 rows) + +SELECT c~>2, c FROM test_cube ORDER BY c~>2 LIMIT 15; -- ascending by right bound + ?column? | c +----------+--------------------------- + 0 | (0, 100000) + 1 | (1, 1) + 54 | (54, 38679),(3, 38602) + 83 | (83, 10271),(15, 10265) + 122 | (122, 46832),(64, 46762) + 154 | (154, 4019),(138, 3990) + 161 | (161, 24465),(107, 24374) + 162 | (162, 26040),(120, 25963) + 167 | (167, 17214),(92, 17184) + 207 | (207, 40886),(179, 40879) + 259 | (259, 1850),(175, 1820) + 270 | (270, 29508),(264, 29440) + 270 | (270, 32616),(226, 32607) + 288 | (288, 49588),(204, 49571) + 318 | (318, 31489),(235, 31404) +(15 rows) + +SELECT c~>3, c FROM test_cube ORDER BY c~>3 LIMIT 15; -- ascending by lower bound + ?column? | c +----------+--------------------------- + 0 | (100000) + 1 | (1, 1) + 6 | (30333, 50),(30273, 6) + 43 | (43301, 75),(43227, 43) + 51 | (19650, 142),(19630, 51) + 81 | (2424, 160),(2424, 81) + 108 | (3449, 171),(3354, 108) + 109 | (18037, 155),(17941, 109) + 114 | (28511, 208),(28479, 114) + 118 | (19946, 217),(19941, 118) + 139 | (16906, 191),(16816, 139) + 163 | (759, 187),(662, 163) + 181 | (22684, 266),(22656, 181) + 213 | (24423, 255),(24360, 213) + 222 | (45989, 249),(45910, 222) +(15 rows) + +SELECT c~>4, c FROM test_cube ORDER BY c~>4 LIMIT 15; -- ascending by upper bound + ?column? | c +----------+--------------------------- + 0 | (100000) + 1 | (1, 1) + 50 | (30333, 50),(30273, 6) + 75 | (43301, 75),(43227, 43) + 142 | (19650, 142),(19630, 51) + 155 | (18037, 155),(17941, 109) + 160 | (2424, 160),(2424, 81) + 171 | (3449, 171),(3354, 108) + 187 | (759, 187),(662, 163) + 191 | (16906, 191),(16816, 139) + 208 | (28511, 208),(28479, 114) + 217 | (19946, 217),(19941, 118) + 249 | (45989, 249),(45910, 222) + 255 | (24423, 255),(24360, 213) + 266 | (22684, 266),(22656, 181) +(15 rows) + +SELECT c~>(-1), c FROM test_cube ORDER BY c~>(-1) LIMIT 15; -- descending by left bound + ?column? | c +----------+------------------------------- + -100000 | (100000) + -49951 | (50027, 49230),(49951, 49214) + -49937 | (49980, 35004),(49937, 34963) + -49927 | (49985, 6436),(49927, 6338) + -49908 | (49999, 27218),(49908, 27176) + -49905 | (49954, 1340),(49905, 1294) + -49902 | (49944, 25163),(49902, 25153) + -49898 | (49981, 34876),(49898, 34786) + -49897 | (49957, 43390),(49897, 43384) + -49848 | (49853, 18504),(49848, 18503) + -49818 | (49902, 41752),(49818, 41746) + -49810 | (49907, 30225),(49810, 30158) + -49808 | (49843, 5175),(49808, 5145) + -49805 | (49887, 24274),(49805, 24184) + -49798 | (49847, 7128),(49798, 7067) +(15 rows) + +SELECT c~>(-2), c FROM test_cube ORDER BY c~>(-2) LIMIT 15; -- descending by right bound + ?column? | c +----------+------------------------------- + -100000 | (100000) + -50027 | (50027, 49230),(49951, 49214) + -49999 | (49999, 27218),(49908, 27176) + -49985 | (49985, 6436),(49927, 6338) + -49981 | (49981, 34876),(49898, 34786) + -49980 | (49980, 35004),(49937, 34963) + -49957 | (49957, 43390),(49897, 43384) + -49954 | (49954, 1340),(49905, 1294) + -49944 | (49944, 25163),(49902, 25153) + -49907 | (49907, 30225),(49810, 30158) + -49902 | (49902, 41752),(49818, 41746) + -49887 | (49887, 24274),(49805, 24184) + -49853 | (49853, 18504),(49848, 18503) + -49847 | (49847, 7128),(49798, 7067) + -49843 | (49843, 5175),(49808, 5145) +(15 rows) + +SELECT c~>(-3), c FROM test_cube ORDER BY c~>(-3) LIMIT 15; -- descending by lower bound + ?column? | c +----------+------------------------------- + -100000 | (0, 100000) + -49992 | (30746, 50040),(30727, 49992) + -49987 | (36311, 50073),(36258, 49987) + -49934 | (3531, 49962),(3463, 49934) + -49915 | (17954, 49975),(17865, 49915) + -49914 | (2168, 50012),(2108, 49914) + -49913 | (31287, 49923),(31236, 49913) + -49885 | (21551, 49983),(21492, 49885) + -49878 | (43925, 49912),(43888, 49878) + -49849 | (19128, 49932),(19112, 49849) + -49844 | (38266, 49852),(38233, 49844) + -49836 | (14913, 49873),(14849, 49836) + -49834 | (37595, 49849),(37581, 49834) + -49830 | (46151, 49848),(46058, 49830) + -49818 | (29261, 49910),(29247, 49818) +(15 rows) + +SELECT c~>(-4), c FROM test_cube ORDER BY c~>(-4) LIMIT 15; -- descending by upper bound + ?column? | c +----------+------------------------------- + -100000 | (0, 100000) + -50073 | (36311, 50073),(36258, 49987) + -50040 | (30746, 50040),(30727, 49992) + -50012 | (2168, 50012),(2108, 49914) + -49983 | (21551, 49983),(21492, 49885) + -49975 | (17954, 49975),(17865, 49915) + -49962 | (3531, 49962),(3463, 49934) + -49932 | (19128, 49932),(19112, 49849) + -49923 | (31287, 49923),(31236, 49913) + -49912 | (43925, 49912),(43888, 49878) + -49910 | (29261, 49910),(29247, 49818) + -49873 | (14913, 49873),(14849, 49836) + -49858 | (20007, 49858),(19921, 49778) + -49852 | (38266, 49852),(38233, 49844) + -49849 | (37595, 49849),(37581, 49834) +(15 rows) + +-- Same queries with sequential scan (should give the same results as above) +RESET enable_seqscan; +SET enable_indexscan = OFF; +SET extra_float_digits = 0; +SELECT *, c <-> '(100, 100),(500, 500)'::cube as dist FROM test_cube ORDER BY c <-> '(100, 100),(500, 500)'::cube LIMIT 5; + c | dist +-------------------------+------------------ + (337, 455),(240, 359) | 0 + (1, 1) | 140.007142674936 + (759, 187),(662, 163) | 162 + (948, 1201),(907, 1156) | 772.000647668122 + (1444, 403),(1346, 344) | 846 +(5 rows) + +RESET extra_float_digits; +SELECT *, c <=> '(100, 100),(500, 500)'::cube as dist FROM test_cube ORDER BY c <=> '(100, 100),(500, 500)'::cube LIMIT 5; + c | dist +-------------------------+------ + (337, 455),(240, 359) | 0 + (1, 1) | 99 + (759, 187),(662, 163) | 162 + (948, 1201),(907, 1156) | 656 + (1444, 403),(1346, 344) | 846 +(5 rows) + +SELECT *, c <#> '(100, 100),(500, 500)'::cube as dist FROM test_cube ORDER BY c <#> '(100, 100),(500, 500)'::cube LIMIT 5; + c | dist +-------------------------+------ + (337, 455),(240, 359) | 0 + (759, 187),(662, 163) | 162 + (1, 1) | 198 + (1444, 403),(1346, 344) | 846 + (369, 1457),(278, 1409) | 909 +(5 rows) + +SELECT c~>1, c FROM test_cube ORDER BY c~>1 LIMIT 15; -- ascending by left bound + ?column? | c +----------+--------------------------- + 0 | (0, 100000) + 1 | (1, 1) + 3 | (54, 38679),(3, 38602) + 15 | (83, 10271),(15, 10265) + 64 | (122, 46832),(64, 46762) + 92 | (167, 17214),(92, 17184) + 107 | (161, 24465),(107, 24374) + 120 | (162, 26040),(120, 25963) + 138 | (154, 4019),(138, 3990) + 175 | (259, 1850),(175, 1820) + 179 | (207, 40886),(179, 40879) + 204 | (288, 49588),(204, 49571) + 226 | (270, 32616),(226, 32607) + 235 | (318, 31489),(235, 31404) + 240 | (337, 455),(240, 359) +(15 rows) + +SELECT c~>2, c FROM test_cube ORDER BY c~>2 LIMIT 15; -- ascending by right bound + ?column? | c +----------+--------------------------- + 0 | (0, 100000) + 1 | (1, 1) + 54 | (54, 38679),(3, 38602) + 83 | (83, 10271),(15, 10265) + 122 | (122, 46832),(64, 46762) + 154 | (154, 4019),(138, 3990) + 161 | (161, 24465),(107, 24374) + 162 | (162, 26040),(120, 25963) + 167 | (167, 17214),(92, 17184) + 207 | (207, 40886),(179, 40879) + 259 | (259, 1850),(175, 1820) + 270 | (270, 29508),(264, 29440) + 270 | (270, 32616),(226, 32607) + 288 | (288, 49588),(204, 49571) + 318 | (318, 31489),(235, 31404) +(15 rows) + +SELECT c~>3, c FROM test_cube ORDER BY c~>3 LIMIT 15; -- ascending by lower bound + ?column? | c +----------+--------------------------- + 0 | (100000) + 1 | (1, 1) + 6 | (30333, 50),(30273, 6) + 43 | (43301, 75),(43227, 43) + 51 | (19650, 142),(19630, 51) + 81 | (2424, 160),(2424, 81) + 108 | (3449, 171),(3354, 108) + 109 | (18037, 155),(17941, 109) + 114 | (28511, 208),(28479, 114) + 118 | (19946, 217),(19941, 118) + 139 | (16906, 191),(16816, 139) + 163 | (759, 187),(662, 163) + 181 | (22684, 266),(22656, 181) + 213 | (24423, 255),(24360, 213) + 222 | (45989, 249),(45910, 222) +(15 rows) + +SELECT c~>4, c FROM test_cube ORDER BY c~>4 LIMIT 15; -- ascending by upper bound + ?column? | c +----------+--------------------------- + 0 | (100000) + 1 | (1, 1) + 50 | (30333, 50),(30273, 6) + 75 | (43301, 75),(43227, 43) + 142 | (19650, 142),(19630, 51) + 155 | (18037, 155),(17941, 109) + 160 | (2424, 160),(2424, 81) + 171 | (3449, 171),(3354, 108) + 187 | (759, 187),(662, 163) + 191 | (16906, 191),(16816, 139) + 208 | (28511, 208),(28479, 114) + 217 | (19946, 217),(19941, 118) + 249 | (45989, 249),(45910, 222) + 255 | (24423, 255),(24360, 213) + 266 | (22684, 266),(22656, 181) +(15 rows) + +SELECT c~>(-1), c FROM test_cube ORDER BY c~>(-1) LIMIT 15; -- descending by left bound + ?column? | c +----------+------------------------------- + -100000 | (100000) + -49951 | (50027, 49230),(49951, 49214) + -49937 | (49980, 35004),(49937, 34963) + -49927 | (49985, 6436),(49927, 6338) + -49908 | (49999, 27218),(49908, 27176) + -49905 | (49954, 1340),(49905, 1294) + -49902 | (49944, 25163),(49902, 25153) + -49898 | (49981, 34876),(49898, 34786) + -49897 | (49957, 43390),(49897, 43384) + -49848 | (49853, 18504),(49848, 18503) + -49818 | (49902, 41752),(49818, 41746) + -49810 | (49907, 30225),(49810, 30158) + -49808 | (49843, 5175),(49808, 5145) + -49805 | (49887, 24274),(49805, 24184) + -49798 | (49847, 7128),(49798, 7067) +(15 rows) + +SELECT c~>(-2), c FROM test_cube ORDER BY c~>(-2) LIMIT 15; -- descending by right bound + ?column? | c +----------+------------------------------- + -100000 | (100000) + -50027 | (50027, 49230),(49951, 49214) + -49999 | (49999, 27218),(49908, 27176) + -49985 | (49985, 6436),(49927, 6338) + -49981 | (49981, 34876),(49898, 34786) + -49980 | (49980, 35004),(49937, 34963) + -49957 | (49957, 43390),(49897, 43384) + -49954 | (49954, 1340),(49905, 1294) + -49944 | (49944, 25163),(49902, 25153) + -49907 | (49907, 30225),(49810, 30158) + -49902 | (49902, 41752),(49818, 41746) + -49887 | (49887, 24274),(49805, 24184) + -49853 | (49853, 18504),(49848, 18503) + -49847 | (49847, 7128),(49798, 7067) + -49843 | (49843, 5175),(49808, 5145) +(15 rows) + +SELECT c~>(-3), c FROM test_cube ORDER BY c~>(-3) LIMIT 15; -- descending by lower bound + ?column? | c +----------+------------------------------- + -100000 | (0, 100000) + -49992 | (30746, 50040),(30727, 49992) + -49987 | (36311, 50073),(36258, 49987) + -49934 | (3531, 49962),(3463, 49934) + -49915 | (17954, 49975),(17865, 49915) + -49914 | (2168, 50012),(2108, 49914) + -49913 | (31287, 49923),(31236, 49913) + -49885 | (21551, 49983),(21492, 49885) + -49878 | (43925, 49912),(43888, 49878) + -49849 | (19128, 49932),(19112, 49849) + -49844 | (38266, 49852),(38233, 49844) + -49836 | (14913, 49873),(14849, 49836) + -49834 | (37595, 49849),(37581, 49834) + -49830 | (46151, 49848),(46058, 49830) + -49818 | (29261, 49910),(29247, 49818) +(15 rows) + +SELECT c~>(-4), c FROM test_cube ORDER BY c~>(-4) LIMIT 15; -- descending by upper bound + ?column? | c +----------+------------------------------- + -100000 | (0, 100000) + -50073 | (36311, 50073),(36258, 49987) + -50040 | (30746, 50040),(30727, 49992) + -50012 | (2168, 50012),(2108, 49914) + -49983 | (21551, 49983),(21492, 49885) + -49975 | (17954, 49975),(17865, 49915) + -49962 | (3531, 49962),(3463, 49934) + -49932 | (19128, 49932),(19112, 49849) + -49923 | (31287, 49923),(31236, 49913) + -49912 | (43925, 49912),(43888, 49878) + -49910 | (29261, 49910),(29247, 49818) + -49873 | (14913, 49873),(14849, 49836) + -49858 | (20007, 49858),(19921, 49778) + -49852 | (38266, 49852),(38233, 49844) + -49849 | (37595, 49849),(37581, 49834) +(15 rows) + +RESET enable_indexscan; |