diff options
Diffstat (limited to 'contrib/intarray/expected/_int.out')
| -rw-r--r-- | contrib/intarray/expected/_int.out | 971 |
1 files changed, 971 insertions, 0 deletions
diff --git a/contrib/intarray/expected/_int.out b/contrib/intarray/expected/_int.out new file mode 100644 index 0000000..b39ab82 --- /dev/null +++ b/contrib/intarray/expected/_int.out @@ -0,0 +1,971 @@ +CREATE EXTENSION intarray; +-- 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) + +SELECT intset(1234); + intset +-------- + {1234} +(1 row) + +SELECT icount('{1234234,234234}'); + icount +-------- + 2 +(1 row) + +SELECT sort('{1234234,-30,234234}'); + sort +---------------------- + {-30,234234,1234234} +(1 row) + +SELECT sort('{1234234,-30,234234}','asc'); + sort +---------------------- + {-30,234234,1234234} +(1 row) + +SELECT sort('{1234234,-30,234234}','desc'); + sort +---------------------- + {1234234,234234,-30} +(1 row) + +SELECT sort_asc('{1234234,-30,234234}'); + sort_asc +---------------------- + {-30,234234,1234234} +(1 row) + +SELECT sort_desc('{1234234,-30,234234}'); + sort_desc +---------------------- + {1234234,234234,-30} +(1 row) + +SELECT uniq('{1234234,-30,-30,234234,-30}'); + uniq +-------------------------- + {1234234,-30,234234,-30} +(1 row) + +SELECT uniq(sort_asc('{1234234,-30,-30,234234,-30}')); + uniq +---------------------- + {-30,234234,1234234} +(1 row) + +SELECT idx('{1234234,-30,-30,234234,-30}',-30); + idx +----- + 2 +(1 row) + +SELECT subarray('{1234234,-30,-30,234234,-30}',2,3); + subarray +------------------ + {-30,-30,234234} +(1 row) + +SELECT subarray('{1234234,-30,-30,234234,-30}',-1,1); + subarray +---------- + {-30} +(1 row) + +SELECT subarray('{1234234,-30,-30,234234,-30}',0,-1); + subarray +-------------------------- + {1234234,-30,-30,234234} +(1 row) + +SELECT #'{1234234,234234}'::int[]; + ?column? +---------- + 2 +(1 row) + +SELECT '{123,623,445}'::int[] + 1245; + ?column? +-------------------- + {123,623,445,1245} +(1 row) + +SELECT '{123,623,445}'::int[] + 445; + ?column? +------------------- + {123,623,445,445} +(1 row) + +SELECT '{123,623,445}'::int[] + '{1245,87,445}'; + ?column? +--------------------------- + {123,623,445,1245,87,445} +(1 row) + +SELECT '{123,623,445}'::int[] - 623; + ?column? +----------- + {123,445} +(1 row) + +SELECT '{123,623,445}'::int[] - '{1623,623}'; + ?column? +----------- + {123,445} +(1 row) + +SELECT '{123,623,445}'::int[] | 623; + ?column? +--------------- + {123,445,623} +(1 row) + +SELECT '{123,623,445}'::int[] | 1623; + ?column? +-------------------- + {123,445,623,1623} +(1 row) + +SELECT '{123,623,445}'::int[] | '{1623,623}'; + ?column? +-------------------- + {123,445,623,1623} +(1 row) + +SELECT '{123,623,445}'::int[] & '{1623,623}'; + ?column? +---------- + {623} +(1 row) + +SELECT '{-1,3,1}'::int[] & '{1,2}'; + ?column? +---------- + {1} +(1 row) + +SELECT '{1}'::int[] & '{2}'::int[]; + ?column? +---------- + {} +(1 row) + +SELECT array_dims('{1}'::int[] & '{2}'::int[]); + array_dims +------------ + +(1 row) + +SELECT ('{1}'::int[] & '{2}'::int[]) = '{}'::int[]; + ?column? +---------- + t +(1 row) + +SELECT ('{}'::int[] & '{}'::int[]) = '{}'::int[]; + ?column? +---------- + t +(1 row) + +--test query_int +SELECT '1'::query_int; + query_int +----------- + 1 +(1 row) + +SELECT ' 1'::query_int; + query_int +----------- + 1 +(1 row) + +SELECT '1 '::query_int; + query_int +----------- + 1 +(1 row) + +SELECT ' 1 '::query_int; + query_int +----------- + 1 +(1 row) + +SELECT ' ! 1 '::query_int; + query_int +----------- + !1 +(1 row) + +SELECT '!1'::query_int; + query_int +----------- + !1 +(1 row) + +SELECT '1|2'::query_int; + query_int +----------- + 1 | 2 +(1 row) + +SELECT '1|!2'::query_int; + query_int +----------- + 1 | !2 +(1 row) + +SELECT '!1|2'::query_int; + query_int +----------- + !1 | 2 +(1 row) + +SELECT '!1|!2'::query_int; + query_int +----------- + !1 | !2 +(1 row) + +SELECT '!(!1|!2)'::query_int; + query_int +-------------- + !( !1 | !2 ) +(1 row) + +SELECT '!(!1|2)'::query_int; + query_int +------------- + !( !1 | 2 ) +(1 row) + +SELECT '!(1|!2)'::query_int; + query_int +------------- + !( 1 | !2 ) +(1 row) + +SELECT '!(1|2)'::query_int; + query_int +------------ + !( 1 | 2 ) +(1 row) + +SELECT '1&2'::query_int; + query_int +----------- + 1 & 2 +(1 row) + +SELECT '!1&2'::query_int; + query_int +----------- + !1 & 2 +(1 row) + +SELECT '1&!2'::query_int; + query_int +----------- + 1 & !2 +(1 row) + +SELECT '!1&!2'::query_int; + query_int +----------- + !1 & !2 +(1 row) + +SELECT '(1&2)'::query_int; + query_int +----------- + 1 & 2 +(1 row) + +SELECT '1&(2)'::query_int; + query_int +----------- + 1 & 2 +(1 row) + +SELECT '!(1)&2'::query_int; + query_int +----------- + !1 & 2 +(1 row) + +SELECT '!(1&2)'::query_int; + query_int +------------ + !( 1 & 2 ) +(1 row) + +SELECT '1|2&3'::query_int; + query_int +----------- + 1 | 2 & 3 +(1 row) + +SELECT '1|(2&3)'::query_int; + query_int +----------- + 1 | 2 & 3 +(1 row) + +SELECT '(1|2)&3'::query_int; + query_int +--------------- + ( 1 | 2 ) & 3 +(1 row) + +SELECT '1|2&!3'::query_int; + query_int +------------ + 1 | 2 & !3 +(1 row) + +SELECT '1|!2&3'::query_int; + query_int +------------ + 1 | !2 & 3 +(1 row) + +SELECT '!1|2&3'::query_int; + query_int +------------ + !1 | 2 & 3 +(1 row) + +SELECT '!1|(2&3)'::query_int; + query_int +------------ + !1 | 2 & 3 +(1 row) + +SELECT '!(1|2)&3'::query_int; + query_int +---------------- + !( 1 | 2 ) & 3 +(1 row) + +SELECT '(!1|2)&3'::query_int; + query_int +---------------- + ( !1 | 2 ) & 3 +(1 row) + +SELECT '1|(2|(4|(5|6)))'::query_int; + query_int +------------------------------- + 1 | ( 2 | ( 4 | ( 5 | 6 ) ) ) +(1 row) + +SELECT '1|2|4|5|6'::query_int; + query_int +------------------------------- + ( ( ( 1 | 2 ) | 4 ) | 5 ) | 6 +(1 row) + +SELECT '1&(2&(4&(5&6)))'::query_int; + query_int +------------------- + 1 & 2 & 4 & 5 & 6 +(1 row) + +SELECT '1&2&4&5&6'::query_int; + query_int +------------------- + 1 & 2 & 4 & 5 & 6 +(1 row) + +SELECT '1&(2&(4&(5|6)))'::query_int; + query_int +----------------------- + 1 & 2 & 4 & ( 5 | 6 ) +(1 row) + +SELECT '1&(2&(4&(5|!6)))'::query_int; + query_int +------------------------ + 1 & 2 & 4 & ( 5 | !6 ) +(1 row) + +-- test non-error-throwing input +SELECT str as "query_int", + pg_input_is_valid(str,'query_int') as ok, + errinfo.sql_error_code, + errinfo.message, + errinfo.detail, + errinfo.hint +FROM (VALUES ('1&(2&(4&(5|6)))'), + ('1#(2&(4&(5&6)))'), + ('foo')) + AS a(str), + LATERAL pg_input_error_info(a.str, 'query_int') as errinfo; + query_int | ok | sql_error_code | message | detail | hint +-----------------+----+----------------+--------------+--------+------ + 1&(2&(4&(5|6))) | t | | | | + 1#(2&(4&(5&6))) | f | 42601 | syntax error | | + foo | f | 42601 | syntax error | | +(3 rows) + +CREATE TABLE test__int( a int[] ); +\copy test__int from 'data/test__int.data' +ANALYZE test__int; +SELECT count(*) from test__int WHERE a && '{23,50}'; + count +------- + 403 +(1 row) + +SELECT count(*) from test__int WHERE a @@ '23|50'; + count +------- + 403 +(1 row) + +SELECT count(*) from test__int WHERE a @> '{23,50}'; + count +------- + 12 +(1 row) + +SELECT count(*) from test__int WHERE a @@ '23&50'; + count +------- + 12 +(1 row) + +SELECT count(*) from test__int WHERE a @> '{20,23}'; + count +------- + 12 +(1 row) + +SELECT count(*) from test__int WHERE a <@ '{73,23,20}'; + count +------- + 10 +(1 row) + +SELECT count(*) from test__int WHERE a = '{73,23,20}'; + count +------- + 1 +(1 row) + +SELECT count(*) from test__int WHERE a @@ '50&68'; + count +------- + 9 +(1 row) + +SELECT count(*) from test__int WHERE a @> '{20,23}' or a @> '{50,68}'; + count +------- + 21 +(1 row) + +SELECT count(*) from test__int WHERE a @@ '(20&23)|(50&68)'; + count +------- + 21 +(1 row) + +SELECT count(*) from test__int WHERE a @@ '20 | !21'; + count +------- + 6567 +(1 row) + +SELECT count(*) from test__int WHERE a @@ '!20 & !21'; + count +------- + 6344 +(1 row) + +SET enable_seqscan = off; -- not all of these would use index by default +CREATE INDEX text_idx on test__int using gist ( a gist__int_ops ); +SELECT count(*) from test__int WHERE a && '{23,50}'; + count +------- + 403 +(1 row) + +SELECT count(*) from test__int WHERE a @@ '23|50'; + count +------- + 403 +(1 row) + +SELECT count(*) from test__int WHERE a @> '{23,50}'; + count +------- + 12 +(1 row) + +SELECT count(*) from test__int WHERE a @@ '23&50'; + count +------- + 12 +(1 row) + +SELECT count(*) from test__int WHERE a @> '{20,23}'; + count +------- + 12 +(1 row) + +SELECT count(*) from test__int WHERE a <@ '{73,23,20}'; + count +------- + 10 +(1 row) + +SELECT count(*) from test__int WHERE a = '{73,23,20}'; + count +------- + 1 +(1 row) + +SELECT count(*) from test__int WHERE a @@ '50&68'; + count +------- + 9 +(1 row) + +SELECT count(*) from test__int WHERE a @> '{20,23}' or a @> '{50,68}'; + count +------- + 21 +(1 row) + +SELECT count(*) from test__int WHERE a @@ '(20&23)|(50&68)'; + count +------- + 21 +(1 row) + +SELECT count(*) from test__int WHERE a @@ '20 | !21'; + count +------- + 6567 +(1 row) + +SELECT count(*) from test__int WHERE a @@ '!20 & !21'; + count +------- + 6344 +(1 row) + +INSERT INTO test__int SELECT array(SELECT x FROM generate_series(1, 1001) x); -- should fail +ERROR: input array is too big (199 maximum allowed, 1001 current), use gist__intbig_ops opclass instead +DROP INDEX text_idx; +CREATE INDEX text_idx on test__int using gist (a gist__int_ops(numranges = 0)); +ERROR: value 0 out of bounds for option "numranges" +DETAIL: Valid values are between "1" and "252". +CREATE INDEX text_idx on test__int using gist (a gist__int_ops(numranges = 253)); +ERROR: value 253 out of bounds for option "numranges" +DETAIL: Valid values are between "1" and "252". +CREATE INDEX text_idx on test__int using gist (a gist__int_ops(numranges = 252)); +SELECT count(*) from test__int WHERE a && '{23,50}'; + count +------- + 403 +(1 row) + +SELECT count(*) from test__int WHERE a @@ '23|50'; + count +------- + 403 +(1 row) + +SELECT count(*) from test__int WHERE a @> '{23,50}'; + count +------- + 12 +(1 row) + +SELECT count(*) from test__int WHERE a @@ '23&50'; + count +------- + 12 +(1 row) + +SELECT count(*) from test__int WHERE a @> '{20,23}'; + count +------- + 12 +(1 row) + +SELECT count(*) from test__int WHERE a <@ '{73,23,20}'; + count +------- + 10 +(1 row) + +SELECT count(*) from test__int WHERE a = '{73,23,20}'; + count +------- + 1 +(1 row) + +SELECT count(*) from test__int WHERE a @@ '50&68'; + count +------- + 9 +(1 row) + +SELECT count(*) from test__int WHERE a @> '{20,23}' or a @> '{50,68}'; + count +------- + 21 +(1 row) + +SELECT count(*) from test__int WHERE a @@ '(20&23)|(50&68)'; + count +------- + 21 +(1 row) + +SELECT count(*) from test__int WHERE a @@ '20 | !21'; + count +------- + 6567 +(1 row) + +SELECT count(*) from test__int WHERE a @@ '!20 & !21'; + count +------- + 6344 +(1 row) + +DROP INDEX text_idx; +CREATE INDEX text_idx on test__int using gist (a gist__intbig_ops(siglen = 0)); +ERROR: value 0 out of bounds for option "siglen" +DETAIL: Valid values are between "1" and "2024". +CREATE INDEX text_idx on test__int using gist (a gist__intbig_ops(siglen = 2025)); +ERROR: value 2025 out of bounds for option "siglen" +DETAIL: Valid values are between "1" and "2024". +CREATE INDEX text_idx on test__int using gist (a gist__intbig_ops(siglen = 2024)); +SELECT count(*) from test__int WHERE a && '{23,50}'; + count +------- + 403 +(1 row) + +SELECT count(*) from test__int WHERE a @@ '23|50'; + count +------- + 403 +(1 row) + +SELECT count(*) from test__int WHERE a @> '{23,50}'; + count +------- + 12 +(1 row) + +SELECT count(*) from test__int WHERE a @@ '23&50'; + count +------- + 12 +(1 row) + +SELECT count(*) from test__int WHERE a @> '{20,23}'; + count +------- + 12 +(1 row) + +SELECT count(*) from test__int WHERE a <@ '{73,23,20}'; + count +------- + 10 +(1 row) + +SELECT count(*) from test__int WHERE a = '{73,23,20}'; + count +------- + 1 +(1 row) + +SELECT count(*) from test__int WHERE a @@ '50&68'; + count +------- + 9 +(1 row) + +SELECT count(*) from test__int WHERE a @> '{20,23}' or a @> '{50,68}'; + count +------- + 21 +(1 row) + +SELECT count(*) from test__int WHERE a @@ '(20&23)|(50&68)'; + count +------- + 21 +(1 row) + +SELECT count(*) from test__int WHERE a @@ '20 | !21'; + count +------- + 6567 +(1 row) + +SELECT count(*) from test__int WHERE a @@ '!20 & !21'; + count +------- + 6344 +(1 row) + +DROP INDEX text_idx; +CREATE INDEX text_idx on test__int using gist ( a gist__intbig_ops ); +SELECT count(*) from test__int WHERE a && '{23,50}'; + count +------- + 403 +(1 row) + +SELECT count(*) from test__int WHERE a @@ '23|50'; + count +------- + 403 +(1 row) + +SELECT count(*) from test__int WHERE a @> '{23,50}'; + count +------- + 12 +(1 row) + +SELECT count(*) from test__int WHERE a @@ '23&50'; + count +------- + 12 +(1 row) + +SELECT count(*) from test__int WHERE a @> '{20,23}'; + count +------- + 12 +(1 row) + +SELECT count(*) from test__int WHERE a <@ '{73,23,20}'; + count +------- + 10 +(1 row) + +SELECT count(*) from test__int WHERE a = '{73,23,20}'; + count +------- + 1 +(1 row) + +SELECT count(*) from test__int WHERE a @@ '50&68'; + count +------- + 9 +(1 row) + +SELECT count(*) from test__int WHERE a @> '{20,23}' or a @> '{50,68}'; + count +------- + 21 +(1 row) + +SELECT count(*) from test__int WHERE a @@ '(20&23)|(50&68)'; + count +------- + 21 +(1 row) + +SELECT count(*) from test__int WHERE a @@ '20 | !21'; + count +------- + 6567 +(1 row) + +SELECT count(*) from test__int WHERE a @@ '!20 & !21'; + count +------- + 6344 +(1 row) + +DROP INDEX text_idx; +CREATE INDEX text_idx on test__int using gin ( a gin__int_ops ); +SELECT count(*) from test__int WHERE a && '{23,50}'; + count +------- + 403 +(1 row) + +SELECT count(*) from test__int WHERE a @@ '23|50'; + count +------- + 403 +(1 row) + +SELECT count(*) from test__int WHERE a @> '{23,50}'; + count +------- + 12 +(1 row) + +SELECT count(*) from test__int WHERE a @@ '23&50'; + count +------- + 12 +(1 row) + +SELECT count(*) from test__int WHERE a @> '{20,23}'; + count +------- + 12 +(1 row) + +SELECT count(*) from test__int WHERE a <@ '{73,23,20}'; + count +------- + 10 +(1 row) + +SELECT count(*) from test__int WHERE a = '{73,23,20}'; + count +------- + 1 +(1 row) + +SELECT count(*) from test__int WHERE a @@ '50&68'; + count +------- + 9 +(1 row) + +SELECT count(*) from test__int WHERE a @> '{20,23}' or a @> '{50,68}'; + count +------- + 21 +(1 row) + +SELECT count(*) from test__int WHERE a @@ '(20&23)|(50&68)'; + count +------- + 21 +(1 row) + +SELECT count(*) from test__int WHERE a @@ '20 | !21'; + count +------- + 6567 +(1 row) + +SELECT count(*) from test__int WHERE a @@ '!20 & !21'; + count +------- + 6344 +(1 row) + +DROP INDEX text_idx; +-- Repeat the same queries with an extended data set. The data set is the +-- same that we used before, except that each element in the array is +-- repeated three times, offset by 1000 and 2000. For example, {1, 5} +-- becomes {1, 1001, 2001, 5, 1005, 2005}. +-- +-- That has proven to be unreasonably effective at exercising codepaths in +-- core GiST code related to splitting parent pages, which is not covered by +-- other tests. This is a bit out-of-place as the point is to test core GiST +-- code rather than this extension, but there is no suitable GiST opclass in +-- core that would reach the same codepaths. +CREATE TABLE more__int AS SELECT + -- Leave alone NULLs, empty arrays and the one row that we use to test + -- equality; also skip INT_MAX + CASE WHEN a IS NULL OR a = '{}' OR a = '{73,23,20}' THEN a ELSE + (select array_agg(u) || array_agg(u + 1000) || array_agg(u + 2000) + from unnest(a) u where u < 2000000000) + END AS a, a as b + FROM test__int; +CREATE INDEX ON more__int using gist (a gist__int_ops(numranges = 252)); +SELECT count(*) from more__int WHERE a && '{23,50}'; + count +------- + 403 +(1 row) + +SELECT count(*) from more__int WHERE a @@ '23|50'; + count +------- + 403 +(1 row) + +SELECT count(*) from more__int WHERE a @> '{23,50}'; + count +------- + 12 +(1 row) + +SELECT count(*) from more__int WHERE a @@ '23&50'; + count +------- + 12 +(1 row) + +SELECT count(*) from more__int WHERE a @> '{20,23}'; + count +------- + 12 +(1 row) + +SELECT count(*) from more__int WHERE a <@ '{73,23,20}'; + count +------- + 10 +(1 row) + +SELECT count(*) from more__int WHERE a = '{73,23,20}'; + count +------- + 1 +(1 row) + +SELECT count(*) from more__int WHERE a @@ '50&68'; + count +------- + 9 +(1 row) + +SELECT count(*) from more__int WHERE a @> '{20,23}' or a @> '{50,68}'; + count +------- + 21 +(1 row) + +SELECT count(*) from more__int WHERE a @@ '(20&23)|(50&68)'; + count +------- + 21 +(1 row) + +SELECT count(*) from more__int WHERE a @@ '20 | !21'; + count +------- + 6567 +(1 row) + +SELECT count(*) from more__int WHERE a @@ '!20 & !21'; + count +------- + 6344 +(1 row) + +RESET enable_seqscan; |