summaryrefslogtreecommitdiff
path: root/src
diff options
context:
space:
mode:
authorMichael Paquier2012-04-21 01:54:43 +0000
committerMichael Paquier2012-04-21 01:54:43 +0000
commit26550e3ff3853b49a51c404fa532084db140acda (patch)
tree1bc84f987fbaade5bf13195d48a18b88909f2211 /src
parent6d2fe633ba6791b5b41000e70d6ddd1b1b42091f (diff)
Fix for regression test array and simplify remote planner
xc_node_id has been introduced previously to insure that tuples fetched from remote nodes are identified consistently in cluster. UPDATE and DELETE remote planning has been simplified to use only xc_node_id and ctid in WHERE clauses generated to be sure to interact with the good tuple. This had consequences on arrays test where a check on a NULL array value was done with = operator where it should have been an operator like IS NULL or IS NOT NULL. Such an addition to WHERE clauses is unnecessary since xc_node_id has been implemented.
Diffstat (limited to 'src')
-rw-r--r--src/backend/optimizer/plan/createplan.c100
-rw-r--r--src/backend/pgxc/plan/planner.c10
-rw-r--r--src/test/regress/expected/arrays_1.out1578
-rw-r--r--src/test/regress/expected/xc_FQS_join.out24
4 files changed, 1639 insertions, 73 deletions
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index 061169bb7e..72b5c638d7 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -5752,7 +5752,17 @@ create_remoteupdate_plan(PlannerInfo *root, Plan *topplan)
}
else
{
- /* Add target list element to WHERE clause */
+ /* Set parameter type */
+ param_types[natts + where_count - 1] = exprType((Node *) tle->expr);
+ where_count++;
+
+ /*
+ * ctid and xc_node_id are sufficient to identify
+ * remote tuple.
+ */
+ if (strcmp(tle->resname, "xc_node_id") != 0 &&
+ strcmp(tle->resname, "ctid") != 0)
+ continue;
/* Set the clause if necessary */
if (!is_where_printed)
@@ -5763,13 +5773,10 @@ create_remoteupdate_plan(PlannerInfo *root, Plan *topplan)
else
appendStringInfoString(buf2, "AND ");
+ /* Complete string */
appendStringInfo(buf2, "%s = $%d ",
tle->resname,
- natts + where_count);
- /* Set parameter type */
- param_types[natts + where_count - 1] = exprType((Node *) tle->expr);
-
- where_count++;
+ natts + where_count - 1);
}
}
@@ -5793,20 +5800,7 @@ create_remoteupdate_plan(PlannerInfo *root, Plan *topplan)
{
Form_pg_attribute att_saved = (Form_pg_attribute) GETSTRUCT(tp);
- /* Set the clause if necessary */
- if (!is_where_printed)
- {
- is_where_printed = true;
- appendStringInfoString(buf2, " WHERE ");
- }
- else
- appendStringInfoString(buf2, "AND ");
-
- /* Complete string */
- appendStringInfo(buf2, "%s = $%d ",
- NameStr(att_saved->attname),
- count);
-
+ /* Set parameter type of attribute */
param_types[count - 1] = att_saved->atttypid;
ReleaseSysCache(tp);
}
@@ -5834,20 +5828,6 @@ create_remoteupdate_plan(PlannerInfo *root, Plan *topplan)
count_prepparams++;
- /* Nullify non-parent entry here */
- if (!is_where_printed)
- {
- is_where_printed = true;
- appendStringInfoString(buf2, " WHERE ");
- }
- else
- appendStringInfoString(buf2, "AND ");
-
- /* Complete string */
- appendStringInfo(buf2, "$%d = $%d ",
- count_prepparams,
- count_prepparams);
-
/* Determine the correct parameter type */
switch (rc->markType)
{
@@ -5881,13 +5861,7 @@ create_remoteupdate_plan(PlannerInfo *root, Plan *topplan)
{
/* For a child table, tableoid is also necessary */
count_prepparams++;
- appendStringInfoString(buf2, "AND ");
-
- /* Complete string */
- appendStringInfo(buf2, "$%d = $%d ",
- count_prepparams,
- count_prepparams);
-
+ /* Set parameter type */
param_types[count_prepparams - 1] = OIDOID;
elt = lnext(elt);
}
@@ -6009,34 +5983,40 @@ create_remotedelete_plan(PlannerInfo *root, Plan *topplan)
{
TargetEntry *tle = lfirst(elt);
- /* Set the clause if necessary */
- if (!is_where_created)
- {
- is_where_created = true;
- appendStringInfoString(buf, "WHERE ");
- }
- else
- appendStringInfoString(buf, "AND ");
+ /* Set up the parameter type */
+ param_types[count - 1] = exprType((Node *) tle->expr);
+ count++;
+
+ /*
+ * In WHERE clause, ctid and xc_node_id are
+ * sufficient to fetch a tuple from remote node.
+ */
+ if (strcmp(tle->resname, "xc_node_id") != 0 &&
+ strcmp(tle->resname, "ctid") != 0)
+ continue;
Assert(IsA((Node *)tle->expr, Var));
if (IsA((Node *)tle->expr, Var))
{
Var *var = (Var *) tle->expr;
- /* Nullify TLEs that are not from this relation */
+ /* Target entries from other relations are not necessary */
if (var->varno != resultRelationIndex)
- appendStringInfo(buf, "$%d = $%d ",
- count,
- count);
+ continue;
+
+ /* Set the clause if necessary */
+ if (!is_where_created)
+ {
+ is_where_created = true;
+ appendStringInfoString(buf, "WHERE ");
+ }
else
- appendStringInfo(buf, "%s = $%d ",
- quote_identifier(tle->resname),
- count);
- }
+ appendStringInfoString(buf, "AND ");
- /* Associate type of parameter */
- param_types[count - 1] = exprType((Node *) tle->expr);
- count++;
+ appendStringInfo(buf, "%s = $%d ",
+ quote_identifier(tle->resname),
+ count - 1);
+ }
}
/* Finish by building the plan step */
diff --git a/src/backend/pgxc/plan/planner.c b/src/backend/pgxc/plan/planner.c
index e155fa9b8f..2da2f1ca26 100644
--- a/src/backend/pgxc/plan/planner.c
+++ b/src/backend/pgxc/plan/planner.c
@@ -1609,7 +1609,6 @@ pgxc_FQS_walker(Node *node, FQS_context *fqs_context)
* do we handle DDLs here?
*/
case T_FieldSelect:
- case T_ArrayRef:
case T_RangeTblRef:
case T_NamedArgExpr:
case T_BoolExpr:
@@ -1633,6 +1632,15 @@ pgxc_FQS_walker(Node *node, FQS_context *fqs_context)
case T_CoerceToDomain:
break;
+ case T_ArrayRef:
+ /*
+ * When multiple values of of an array are updated at once
+ * FQS planner cannot yet handle SQL representation correctly.
+ * So disable FQS in this case and let standard planner manage it.
+ */
+ pgxc_FQS_set_reason(fqs_context, FQS_UNSUPPORTED_EXPR);
+ break;
+
case T_FieldStore:
/*
* PostgreSQL deparsing logic does not handle the FieldStore
diff --git a/src/test/regress/expected/arrays_1.out b/src/test/regress/expected/arrays_1.out
new file mode 100644
index 0000000000..6d22f56f5d
--- /dev/null
+++ b/src/test/regress/expected/arrays_1.out
@@ -0,0 +1,1578 @@
+--
+-- ARRAYS
+--
+CREATE TABLE arrtest (
+ a int2[],
+ b int4[][][],
+ c name[],
+ d text[][],
+ e float8[],
+ f char(5)[],
+ g varchar(5)[]
+);
+--
+-- only the 'e' array is 0-based, the others are 1-based.
+--
+INSERT INTO arrtest (a[1:5], b[1:1][1:2][1:2], c, d, f, g)
+ VALUES ('{1,2,3,4,5}', '{{{0,0},{1,2}}}', '{}', '{}', '{}', '{}');
+UPDATE arrtest SET e[0] = '1.1';
+UPDATE arrtest SET e[1] = '2.2';
+INSERT INTO arrtest (f)
+ VALUES ('{"too long"}');
+ERROR: value too long for type character(5)
+INSERT INTO arrtest (a, b[1:2][1:2], c, d, e, f, g)
+ VALUES ('{11,12,23}', '{{3,4},{4,5}}', '{"foobar"}',
+ '{{"elt1", "elt2"}}', '{"3.4", "6.7"}',
+ '{"abc","abcde"}', '{"abc","abcde"}');
+INSERT INTO arrtest (a, b[1:2], c, d[1:2])
+ VALUES ('{}', '{3,4}', '{foo,bar}', '{bar,foo}');
+SELECT * FROM arrtest ORDER BY a, b, c;
+ a | b | c | d | e | f | g
+-------------+-----------------+-----------+---------------+-----------------+-----------------+-------------
+ {} | {3,4} | {foo,bar} | {bar,foo} | | |
+ {1,2,3,4,5} | {{{0,0},{1,2}}} | {} | {} | [0:1]={1.1,2.2} | {} | {}
+ {11,12,23} | {{3,4},{4,5}} | {foobar} | {{elt1,elt2}} | {3.4,6.7} | {"abc ",abcde} | {abc,abcde}
+(3 rows)
+
+SELECT arrtest.a[1],
+ arrtest.b[1][1][1],
+ arrtest.c[1],
+ arrtest.d[1][1],
+ arrtest.e[0]
+ FROM arrtest
+ ORDER BY a, b, c;
+ a | b | c | d | e
+----+---+--------+------+-----
+ 1 | 0 | | | 1.1
+ 11 | | foobar | elt1 |
+ | | foo | |
+(3 rows)
+
+SELECT a[1], b[1][1][1], c[1], d[1][1], e[0]
+ FROM arrtest
+ ORDER BY a, b, c;
+ a | b | c | d | e
+----+---+--------+------+-----
+ 1 | 0 | | | 1.1
+ 11 | | foobar | elt1 |
+ | | foo | |
+(3 rows)
+
+SELECT a[1:3],
+ b[1:1][1:2][1:2],
+ c[1:2],
+ d[1:1][1:2]
+ FROM arrtest
+ ORDER BY a, b, c;
+ a | b | c | d
+------------+-----------------+-----------+---------------
+ {} | {} | {foo,bar} | {}
+ {1,2,3} | {{{0,0},{1,2}}} | {} | {}
+ {11,12,23} | {} | {foobar} | {{elt1,elt2}}
+(3 rows)
+
+SELECT array_ndims(a) AS a,array_ndims(b) AS b,array_ndims(c) AS c
+ FROM arrtest
+ ORDER BY b;
+ a | b | c
+---+---+---
+ | 1 | 1
+ 1 | 2 | 1
+ 1 | 3 |
+(3 rows)
+
+SELECT array_dims(a) AS a,array_dims(b) AS b,array_dims(c) AS c
+ FROM arrtest
+ ORDER BY b;
+ a | b | c
+-------+-----------------+-------
+ [1:5] | [1:1][1:2][1:2] |
+ | [1:2] | [1:2]
+ [1:3] | [1:2][1:2] | [1:1]
+(3 rows)
+
+-- returns nothing
+SELECT *
+ FROM arrtest
+ WHERE a[1] < 5 and
+ c = '{"foobar"}'::_name;
+ a | b | c | d | e | f | g
+---+---+---+---+---+---+---
+(0 rows)
+
+UPDATE arrtest
+ SET a[1:2] = '{16,25}'
+ WHERE NOT a = '{}'::_int2;
+UPDATE arrtest
+ SET b[1:1][1:1][1:2] = '{113, 117}',
+ b[1:1][1:2][2:2] = '{142, 147}'
+ WHERE array_dims(b) = '[1:1][1:2][1:2]';
+UPDATE arrtest
+ SET c[2:2] = '{"new_word"}'
+ WHERE array_dims(c) is not null;
+SELECT a,b,c FROM arrtest ORDER BY a, b, c;
+ a | b | c
+---------------+-----------------------+-------------------
+ {} | {3,4} | {foo,new_word}
+ {16,25,3,4,5} | {{{113,142},{1,147}}} | {}
+ {16,25,23} | {{3,4},{4,5}} | {foobar,new_word}
+(3 rows)
+
+SELECT a[1:3],
+ b[1:1][1:2][1:2],
+ c[1:2],
+ d[1:1][2:2]
+ FROM arrtest
+ ORDER BY a, b, c;
+ a | b | c | d
+------------+-----------------------+-------------------+----------
+ {} | {} | {foo,new_word} | {}
+ {16,25,3} | {{{113,142},{1,147}}} | {} | {}
+ {16,25,23} | {} | {foobar,new_word} | {{elt2}}
+(3 rows)
+
+INSERT INTO arrtest(a) VALUES('{1,null,3}');
+SELECT a FROM arrtest ORDER BY 1;
+ a
+---------------
+ {}
+ {1,NULL,3}
+ {16,25,3,4,5}
+ {16,25,23}
+(4 rows)
+
+UPDATE arrtest SET a[4] = NULL WHERE a[2] IS NULL;
+SELECT a FROM arrtest WHERE a[2] IS NULL ORDER BY 1;
+ a
+-----------------
+ {1,NULL,3,NULL}
+ [4:4]={NULL}
+(2 rows)
+
+DELETE FROM arrtest WHERE a[2] IS NULL AND b IS NULL;
+SELECT a,b,c FROM arrtest ORDER BY a, b, c;
+ a | b | c
+---------------+-----------------------+-------------------
+ {16,25,3,4,5} | {{{113,142},{1,147}}} | {}
+ {16,25,23} | {{3,4},{4,5}} | {foobar,new_word}
+ [4:4]={NULL} | {3,4} | {foo,new_word}
+(3 rows)
+
+--
+-- test array extension
+--
+-- Enforce use of COMMIT instead of 2PC for temporary objects
+SET enforce_two_phase_commit TO off;
+CREATE TEMP TABLE arrtest1 (i int[], t text[]);
+insert into arrtest1 values(array[1,2,null,4], array['one','two',null,'four']);
+select * from arrtest1;
+ i | t
+--------------+---------------------
+ {1,2,NULL,4} | {one,two,NULL,four}
+(1 row)
+
+update arrtest1 set i[2] = 22, t[2] = 'twenty-two';
+select * from arrtest1;
+ i | t
+---------------+----------------------------
+ {1,22,NULL,4} | {one,twenty-two,NULL,four}
+(1 row)
+
+update arrtest1 set i[5] = 5, t[5] = 'five';
+select * from arrtest1;
+ i | t
+-----------------+---------------------------------
+ {1,22,NULL,4,5} | {one,twenty-two,NULL,four,five}
+(1 row)
+
+update arrtest1 set i[8] = 8, t[8] = 'eight';
+select * from arrtest1;
+ i | t
+-----------------------------+-------------------------------------------------
+ {1,22,NULL,4,5,NULL,NULL,8} | {one,twenty-two,NULL,four,five,NULL,NULL,eight}
+(1 row)
+
+update arrtest1 set i[0] = 0, t[0] = 'zero';
+select * from arrtest1;
+ i | t
+-------------------------------------+------------------------------------------------------------
+ [0:8]={0,1,22,NULL,4,5,NULL,NULL,8} | [0:8]={zero,one,twenty-two,NULL,four,five,NULL,NULL,eight}
+(1 row)
+
+update arrtest1 set i[-3] = -3, t[-3] = 'minus-three';
+select * from arrtest1;
+ i | t
+---------------------------------------------------+-----------------------------------------------------------------------------------
+ [-3:8]={-3,NULL,NULL,0,1,22,NULL,4,5,NULL,NULL,8} | [-3:8]={minus-three,NULL,NULL,zero,one,twenty-two,NULL,four,five,NULL,NULL,eight}
+(1 row)
+
+update arrtest1 set i[0:2] = array[10,11,12], t[0:2] = array['ten','eleven','twelve'];
+select * from arrtest1;
+ i | t
+-----------------------------------------------------+---------------------------------------------------------------------------------
+ [-3:8]={-3,NULL,NULL,10,11,12,NULL,4,5,NULL,NULL,8} | [-3:8]={minus-three,NULL,NULL,ten,eleven,twelve,NULL,four,five,NULL,NULL,eight}
+(1 row)
+
+update arrtest1 set i[8:10] = array[18,null,20], t[8:10] = array['p18',null,'p20'];
+select * from arrtest1;
+ i | t
+---------------------------------------------------------------+-----------------------------------------------------------------------------------------
+ [-3:10]={-3,NULL,NULL,10,11,12,NULL,4,5,NULL,NULL,18,NULL,20} | [-3:10]={minus-three,NULL,NULL,ten,eleven,twelve,NULL,four,five,NULL,NULL,p18,NULL,p20}
+(1 row)
+
+update arrtest1 set i[11:12] = array[null,22], t[11:12] = array[null,'p22'];
+select * from arrtest1;
+ i | t
+-----------------------------------------------------------------------+--------------------------------------------------------------------------------------------------
+ [-3:12]={-3,NULL,NULL,10,11,12,NULL,4,5,NULL,NULL,18,NULL,20,NULL,22} | [-3:12]={minus-three,NULL,NULL,ten,eleven,twelve,NULL,four,five,NULL,NULL,p18,NULL,p20,NULL,p22}
+(1 row)
+
+update arrtest1 set i[15:16] = array[null,26], t[15:16] = array[null,'p26'];
+select * from arrtest1;
+ i | t
+-----------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------
+ [-3:16]={-3,NULL,NULL,10,11,12,NULL,4,5,NULL,NULL,18,NULL,20,NULL,22,NULL,NULL,NULL,26} | [-3:16]={minus-three,NULL,NULL,ten,eleven,twelve,NULL,four,five,NULL,NULL,p18,NULL,p20,NULL,p22,NULL,NULL,NULL,p26}
+(1 row)
+
+update arrtest1 set i[-5:-3] = array[-15,-14,-13], t[-5:-3] = array['m15','m14','m13'];
+select * from arrtest1;
+ i | t
+--------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------
+ [-5:16]={-15,-14,-13,NULL,NULL,10,11,12,NULL,4,5,NULL,NULL,18,NULL,20,NULL,22,NULL,NULL,NULL,26} | [-5:16]={m15,m14,m13,NULL,NULL,ten,eleven,twelve,NULL,four,five,NULL,NULL,p18,NULL,p20,NULL,p22,NULL,NULL,NULL,p26}
+(1 row)
+
+update arrtest1 set i[-7:-6] = array[-17,null], t[-7:-6] = array['m17',null];
+select * from arrtest1;
+ i | t
+-----------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------
+ [-7:16]={-17,NULL,-15,-14,-13,NULL,NULL,10,11,12,NULL,4,5,NULL,NULL,18,NULL,20,NULL,22,NULL,NULL,NULL,26} | [-7:16]={m17,NULL,m15,m14,m13,NULL,NULL,ten,eleven,twelve,NULL,four,five,NULL,NULL,p18,NULL,p20,NULL,p22,NULL,NULL,NULL,p26}
+(1 row)
+
+update arrtest1 set i[-12:-10] = array[-22,null,-20], t[-12:-10] = array['m22',null,'m20'];
+select * from arrtest1;
+ i | t
+-----------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------
+ [-12:16]={-22,NULL,-20,NULL,NULL,-17,NULL,-15,-14,-13,NULL,NULL,10,11,12,NULL,4,5,NULL,NULL,18,NULL,20,NULL,22,NULL,NULL,NULL,26} | [-12:16]={m22,NULL,m20,NULL,NULL,m17,NULL,m15,m14,m13,NULL,NULL,ten,eleven,twelve,NULL,four,five,NULL,NULL,p18,NULL,p20,NULL,p22,NULL,NULL,NULL,p26}
+(1 row)
+
+delete from arrtest1;
+insert into arrtest1 values(array[1,2,null,4], array['one','two',null,'four']);
+select * from arrtest1;
+ i | t
+--------------+---------------------
+ {1,2,NULL,4} | {one,two,NULL,four}
+(1 row)
+
+update arrtest1 set i[0:5] = array[0,1,2,null,4,5], t[0:5] = array['z','p1','p2',null,'p4','p5'];
+select * from arrtest1;
+ i | t
+------------------------+----------------------------
+ [0:5]={0,1,2,NULL,4,5} | [0:5]={z,p1,p2,NULL,p4,p5}
+(1 row)
+
+--
+-- array expressions and operators
+--
+-- table creation and INSERTs
+CREATE TEMP TABLE arrtest2 (i integer ARRAY[4], f float8[], n numeric[], t text[], d timestamp[]);
+INSERT INTO arrtest2 VALUES(
+ ARRAY[[[113,142],[1,147]]],
+ ARRAY[1.1,1.2,1.3]::float8[],
+ ARRAY[1.1,1.2,1.3],
+ ARRAY[[['aaa','aab'],['aba','abb'],['aca','acb']],[['baa','bab'],['bba','bbb'],['bca','bcb']]],
+ ARRAY['19620326','19931223','19970117']::timestamp[]
+);
+-- some more test data
+CREATE TEMP TABLE arrtest_f (f0 int, f1 text, f2 float8);
+insert into arrtest_f values(1,'cat1',1.21);
+insert into arrtest_f values(2,'cat1',1.24);
+insert into arrtest_f values(3,'cat1',1.18);
+insert into arrtest_f values(4,'cat1',1.26);
+insert into arrtest_f values(5,'cat1',1.15);
+insert into arrtest_f values(6,'cat2',1.15);
+insert into arrtest_f values(7,'cat2',1.26);
+insert into arrtest_f values(8,'cat2',1.32);
+insert into arrtest_f values(9,'cat2',1.30);
+CREATE TEMP TABLE arrtest_i (f0 int, f1 text, f2 int);
+insert into arrtest_i values(1,'cat1',21);
+insert into arrtest_i values(2,'cat1',24);
+insert into arrtest_i values(3,'cat1',18);
+insert into arrtest_i values(4,'cat1',26);
+insert into arrtest_i values(5,'cat1',15);
+insert into arrtest_i values(6,'cat2',15);
+insert into arrtest_i values(7,'cat2',26);
+insert into arrtest_i values(8,'cat2',32);
+insert into arrtest_i values(9,'cat2',30);
+-- expressions
+SELECT t.f[1][3][1] AS "131", t.f[2][2][1] AS "221" FROM (
+ SELECT ARRAY[[[111,112],[121,122],[131,132]],[[211,212],[221,122],[231,232]]] AS f
+) AS t;
+ 131 | 221
+-----+-----
+ 131 | 221
+(1 row)
+
+SELECT ARRAY[[[[[['hello'],['world']]]]]];
+ array
+---------------------------
+ {{{{{{hello},{world}}}}}}
+(1 row)
+
+SELECT ARRAY[ARRAY['hello'],ARRAY['world']];
+ array
+-------------------
+ {{hello},{world}}
+(1 row)
+
+SELECT ARRAY(select f2 from arrtest_f order by f2) AS "ARRAY";
+ ARRAY
+-----------------------------------------------
+ {1.15,1.15,1.18,1.21,1.24,1.26,1.26,1.3,1.32}
+(1 row)
+
+-- with nulls
+SELECT '{1,null,3}'::int[];
+ int4
+------------
+ {1,NULL,3}
+(1 row)
+
+SELECT ARRAY[1,NULL,3];
+ array
+------------
+ {1,NULL,3}
+(1 row)
+
+-- functions
+SELECT array_append(array[42], 6) AS "{42,6}";
+ {42,6}
+--------
+ {42,6}
+(1 row)
+
+SELECT array_prepend(6, array[42]) AS "{6,42}";
+ {6,42}
+--------
+ {6,42}
+(1 row)
+
+SELECT array_cat(ARRAY[1,2], ARRAY[3,4]) AS "{1,2,3,4}";
+ {1,2,3,4}
+-----------
+ {1,2,3,4}
+(1 row)
+
+SELECT array_cat(ARRAY[1,2], ARRAY[[3,4],[5,6]]) AS "{{1,2},{3,4},{5,6}}";
+ {{1,2},{3,4},{5,6}}
+---------------------
+ {{1,2},{3,4},{5,6}}
+(1 row)
+
+SELECT array_cat(ARRAY[[3,4],[5,6]], ARRAY[1,2]) AS "{{3,4},{5,6},{1,2}}";
+ {{3,4},{5,6},{1,2}}
+---------------------
+ {{3,4},{5,6},{1,2}}
+(1 row)
+
+-- operators
+SELECT a FROM arrtest WHERE b = ARRAY[[[113,142],[1,147]]];
+ a
+---------------
+ {16,25,3,4,5}
+(1 row)
+
+SELECT NOT ARRAY[1.1,1.2,1.3] = ARRAY[1.1,1.2,1.3] AS "FALSE";
+ FALSE
+-------
+ f
+(1 row)
+
+SELECT ARRAY[1,2] || 3 AS "{1,2,3}";
+ {1,2,3}
+---------
+ {1,2,3}
+(1 row)
+
+SELECT 0 || ARRAY[1,2] AS "{0,1,2}";
+ {0,1,2}
+---------
+ {0,1,2}
+(1 row)
+
+SELECT ARRAY[1,2] || ARRAY[3,4] AS "{1,2,3,4}";
+ {1,2,3,4}
+-----------
+ {1,2,3,4}
+(1 row)
+
+SELECT ARRAY[[['hello','world']]] || ARRAY[[['happy','birthday']]] AS "ARRAY";
+ ARRAY
+--------------------------------------
+ {{{hello,world}},{{happy,birthday}}}
+(1 row)
+
+SELECT ARRAY[[1,2],[3,4]] || ARRAY[5,6] AS "{{1,2},{3,4},{5,6}}";
+ {{1,2},{3,4},{5,6}}
+---------------------
+ {{1,2},{3,4},{5,6}}
+(1 row)
+
+SELECT ARRAY[0,0] || ARRAY[1,1] || ARRAY[2,2] AS "{0,0,1,1,2,2}";
+ {0,0,1,1,2,2}
+---------------
+ {0,0,1,1,2,2}
+(1 row)
+
+SELECT 0 || ARRAY[1,2] || 3 AS "{0,1,2,3}";
+ {0,1,2,3}
+-----------
+ {0,1,2,3}
+(1 row)
+
+SELECT * FROM array_op_test WHERE i @> '{32}' ORDER BY seqno;
+ seqno | i | t
+-------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------
+ 6 | {39,35,5,94,17,92,60,32} | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657}
+ 74 | {32} | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956}
+ 77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066}
+ 89 | {40,32,17,6,30,88} | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673}
+ 98 | {38,34,32,89} | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845}
+ 100 | {85,32,57,39,49,84,32,3,30} | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523}
+(6 rows)
+
+SELECT * FROM array_op_test WHERE i && '{32}' ORDER BY seqno;
+ seqno | i | t
+-------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------
+ 6 | {39,35,5,94,17,92,60,32} | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657}
+ 74 | {32} | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956}
+ 77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066}
+ 89 | {40,32,17,6,30,88} | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673}
+ 98 | {38,34,32,89} | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845}
+ 100 | {85,32,57,39,49,84,32,3,30} | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523}
+(6 rows)
+
+SELECT * FROM array_op_test WHERE i @> '{17}' ORDER BY seqno;
+ seqno | i | t
+-------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------
+ 6 | {39,35,5,94,17,92,60,32} | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657}
+ 12 | {17,99,18,52,91,72,0,43,96,23} | {AAAAA33250,AAAAAAAAAAAAAAAAAAA85420,AAAAAAAAAAA33576}
+ 15 | {17,14,16,63,67} | {AA6416,AAAAAAAAAA646,AAAAA95309}
+ 19 | {52,82,17,74,23,46,69,51,75} | {AAAAAAAAAAAAA73084,AAAAA75968,AAAAAAAAAAAAAAAA14047,AAAAAAA80240,AAAAAAAAAAAAAAAAAAA1205,A68938}
+ 53 | {38,17} | {AAAAAAAAAAA21658}
+ 65 | {61,5,76,59,17} | {AAAAAA99807,AAAAA64741,AAAAAAAAAAA53908,AA21643,AAAAAAAAA10012}
+ 77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066}
+ 89 | {40,32,17,6,30,88} | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673}
+(8 rows)
+
+SELECT * FROM array_op_test WHERE i && '{17}' ORDER BY seqno;
+ seqno | i | t
+-------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------
+ 6 | {39,35,5,94,17,92,60,32} | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657}
+ 12 | {17,99,18,52,91,72,0,43,96,23} | {AAAAA33250,AAAAAAAAAAAAAAAAAAA85420,AAAAAAAAAAA33576}
+ 15 | {17,14,16,63,67} | {AA6416,AAAAAAAAAA646,AAAAA95309}
+ 19 | {52,82,17,74,23,46,69,51,75} | {AAAAAAAAAAAAA73084,AAAAA75968,AAAAAAAAAAAAAAAA14047,AAAAAAA80240,AAAAAAAAAAAAAAAAAAA1205,A68938}
+ 53 | {38,17} | {AAAAAAAAAAA21658}
+ 65 | {61,5,76,59,17} | {AAAAAA99807,AAAAA64741,AAAAAAAAAAA53908,AA21643,AAAAAAAAA10012}
+ 77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066}
+ 89 | {40,32,17,6,30,88} | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673}
+(8 rows)
+
+SELECT * FROM array_op_test WHERE i @> '{32,17}' ORDER BY seqno;
+ seqno | i | t
+-------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------
+ 6 | {39,35,5,94,17,92,60,32} | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657}
+ 77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066}
+ 89 | {40,32,17,6,30,88} | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673}
+(3 rows)
+
+SELECT * FROM array_op_test WHERE i && '{32,17}' ORDER BY seqno;
+ seqno | i | t
+-------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------
+ 6 | {39,35,5,94,17,92,60,32} | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657}
+ 12 | {17,99,18,52,91,72,0,43,96,23} | {AAAAA33250,AAAAAAAAAAAAAAAAAAA85420,AAAAAAAAAAA33576}
+ 15 | {17,14,16,63,67} | {AA6416,AAAAAAAAAA646,AAAAA95309}
+ 19 | {52,82,17,74,23,46,69,51,75} | {AAAAAAAAAAAAA73084,AAAAA75968,AAAAAAAAAAAAAAAA14047,AAAAAAA80240,AAAAAAAAAAAAAAAAAAA1205,A68938}
+ 53 | {38,17} | {AAAAAAAAAAA21658}
+ 65 | {61,5,76,59,17} | {AAAAAA99807,AAAAA64741,AAAAAAAAAAA53908,AA21643,AAAAAAAAA10012}
+ 74 | {32} | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956}
+ 77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066}
+ 89 | {40,32,17,6,30,88} | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673}
+ 98 | {38,34,32,89} | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845}
+ 100 | {85,32,57,39,49,84,32,3,30} | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523}
+(11 rows)
+
+SELECT * FROM array_op_test WHERE i <@ '{38,34,32,89}' ORDER BY seqno;
+ seqno | i | t
+-------+---------------+----------------------------------------------------------------------------------------------------------------------------
+ 40 | {34} | {AAAAAAAAAAAAAA10611,AAAAAAAAAAAAAAAAAAA1205,AAAAAAAAAAA50956,AAAAAAAAAAAAAAAA31334,AAAAA70466,AAAAAAAA81587,AAAAAAA74623}
+ 74 | {32} | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956}
+ 98 | {38,34,32,89} | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845}
+ 101 | {} | {}
+(4 rows)
+
+SELECT * FROM array_op_test WHERE i = '{}' ORDER BY seqno;
+ seqno | i | t
+-------+----+----
+ 101 | {} | {}
+(1 row)
+
+SELECT * FROM array_op_test WHERE i @> '{}' ORDER BY seqno;
+ seqno | i | t
+-------+---------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ 1 | {92,75,71,52,64,83} | {AAAAAAAA44066,AAAAAA1059,AAAAAAAAAAA176,AAAAAAA48038}
+ 2 | {3,6} | {AAAAAA98232,AAAAAAAA79710,AAAAAAAAAAAAAAAAA69675,AAAAAAAAAAAAAAAA55798,AAAAAAAAA12793}
+ 3 | {37,64,95,43,3,41,13,30,11,43} | {AAAAAAAAAA48845,AAAAA75968,AAAAA95309,AAA54451,AAAAAAAAAA22292,AAAAAAA99836,A96617,AA17009,AAAAAAAAAAAAAA95246}
+ 4 | {71,39,99,55,33,75,45} | {AAAAAAAAA53663,AAAAAAAAAAAAAAA67062,AAAAAAAAAA64777,AAA99043,AAAAAAAAAAAAAAAAAAA91804,39557}
+ 5 | {50,42,77,50,4} | {AAAAAAAAAAAAAAAAA26540,AAAAAAA79710,AAAAAAAAAAAAAAAAAAA1205,AAAAAAAAAAA176,AAAAA95309,AAAAAAAAAAA46154,AAAAAA66777,AAAAAAAAA27249,AAAAAAAAAA64777,AAAAAAAAAAAAAAAAAAA70104}
+ 6 | {39,35,5,94,17,92,60,32} | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657}
+ 7 | {12,51,88,64,8} | {AAAAAAAAAAAAAAAAAA12591,AAAAAAAAAAAAAAAAA50407,AAAAAAAAAAAA67946}
+ 8 | {60,84} | {AAAAAAA81898,AAAAAA1059,AAAAAAAAAAAA81511,AAAAA961,AAAAAAAAAAAAAAAA31334,AAAAA64741,AA6416,AAAAAAAAAAAAAAAAAA32918,AAAAAAAAAAAAAAAAA50407}
+ 9 | {56,52,35,27,80,44,81,22} | {AAAAAAAAAAAAAAA73034,AAAAAAAAAAAAA7929,AAAAAAA66161,AA88409,39557,A27153,AAAAAAAA9523,AAAAAAAAAAA99000}
+ 10 | {71,5,45} | {AAAAAAAAAAA21658,AAAAAAAAAAAA21089,AAA54451,AAAAAAAAAAAAAAAAAA54141,AAAAAAAAAAAAAA28620,AAAAAAAAAAA21658,AAAAAAAAAAA74076,AAAAAAAAA27249}
+ 11 | {41,86,74,48,22,74,47,50} | {AAAAAAAA9523,AAAAAAAAAAAA37562,AAAAAAAAAAAAAAAA14047,AAAAAAAAAAA46154,AAAA41702,AAAAAAAAAAAAAAAAA764,AAAAA62737,39557}
+ 12 | {17,99,18,52,91,72,0,43,96,23} | {AAAAA33250,AAAAAAAAAAAAAAAAAAA85420,AAAAAAAAAAA33576}
+ 13 | {3,52,34,23} | {AAAAAA98232,AAAA49534,AAAAAAAAAAA21658}
+ 14 | {78,57,19} | {AAAA8857,AAAAAAAAAAAAAAA73034,AAAAAAAA81587,AAAAAAAAAAAAAAA68526,AAAAA75968,AAAAAAAAAAAAAA65909,AAAAAAAAA10012,AAAAAAAAAAAAAA65909}
+ 15 | {17,14,16,63,67} | {AA6416,AAAAAAAAAA646,AAAAA95309}
+ 16 | {14,63,85,11} | {AAAAAA66777}
+ 17 | {7,10,81,85} | {AAAAAA43678,AAAAAAA12144,AAAAAAAAAAA50956,AAAAAAAAAAAAAAAAAAA15356}
+ 18 | {1} | {AAAAAAAAAAA33576,AAAAA95309,64261,AAA59323,AAAAAAAAAAAAAA95246,55847,AAAAAAAAAAAA67946,AAAAAAAAAAAAAAAAAA64374}
+ 19 | {52,82,17,74,23,46,69,51,75} | {AAAAAAAAAAAAA73084,AAAAA75968,AAAAAAAAAAAAAAAA14047,AAAAAAA80240,AAAAAAAAAAAAAAAAAAA1205,A68938}
+ 20 | {72,89,70,51,54,37,8,49,79} | {AAAAAA58494}
+ 21 | {2,8,65,10,5,79,43} | {AAAAAAAAAAAAAAAAA88852,AAAAAAAAAAAAAAAAAAA91804,AAAAA64669,AAAAAAAAAAAAAAAA1443,AAAAAAAAAAAAAAAA23657,AAAAA12179,AAAAAAAAAAAAAAAAA88852,AAAAAAAAAAAAAAAA31334,AAAAAAAAAAAAAAAA41303,AAAAAAAAAAAAAAAAAAA85420}
+ 22 | {11,6,56,62,53,30} | {AAAAAAAA72908}
+ 23 | {40,90,5,38,72,40,30,10,43,55} | {A6053,AAAAAAAAAAA6119,AA44673,AAAAAAAAAAAAAAAAA764,AA17009,AAAAA17383,AAAAA70514,AAAAA33250,AAAAA95309,AAAAAAAAAAAA37562}
+ 24 | {94,61,99,35,48} | {AAAAAAAAAAA50956,AAAAAAAAAAA15165,AAAA85070,AAAAAAAAAAAAAAA36627,AAAAA961,AAAAAAAAAA55219}
+ 25 | {31,1,10,11,27,79,38} | {AAAAAAAAAAAAAAAAAA59334,45449}
+ 26 | {71,10,9,69,75} | {47735,AAAAAAA21462,AAAAAAAAAAAAAAAAA6897,AAAAAAAAAAAAAAAAAAA91804,AAAAAAAAA72121,AAAAAAAAAAAAAAAAAAA1205,AAAAA41597,AAAA8857,AAAAAAAAAAAAAAAAAAA15356,AA17009}
+ 27 | {94} | {AA6416,A6053,AAAAAAA21462,AAAAAAA57334,AAAAAAAAAAAAAAAAAA12591,AA88409,AAAAAAAAAAAAA70254}
+ 28 | {14,33,6,34,14} | {AAAAAAAAAAAAAAA13198,AAAAAAAA69452,AAAAAAAAAAA82945,AAAAAAA12144,AAAAAAAAA72121,AAAAAAAAAA18601}
+ 29 | {39,21} | {AAAAAAAAAAAAAAAAA6897,AAAAAAAAAAAAAAAAAAA38885,AAAA85070,AAAAAAAAAAAAAAAAAAA70104,AAAAA66674,AAAAAAAAAAAAA62007,AAAAAAAA69452,AAAAAAA1242,AAAAAAAAAAAAAAAA1729,AAAA35194}
+ 30 | {26,81,47,91,34} | {AAAAAAAAAAAAAAAAAAA70104,AAAAAAA80240}
+ 31 | {80,24,18,21,54} | {AAAAAAAAAAAAAAA13198,AAAAAAAAAAAAAAAAAAA70415,A27153,AAAAAAAAA53663,AAAAAAAAAAAAAAAAA50407,A68938}
+ 32 | {58,79,82,80,67,75,98,10,41} | {AAAAAAAAAAAAAAAAAA61286,AAA54451,AAAAAAAAAAAAAAAAAAA87527,A96617,51533}
+ 33 | {74,73} | {A85417,AAAAAAA56483,AAAAA17383,AAAAAAAAAAAAA62159,AAAAAAAAAAAA52814,AAAAAAAAAAAAA85723,AAAAAAAAAAAAAAAAAA55796}
+ 34 | {70,45} | {AAAAAAAAAAAAAAAAAA71621,AAAAAAAAAAAAAA28620,AAAAAAAAAA55219,AAAAAAAA23648,AAAAAAAAAA22292,AAAAAAA1242}
+ 35 | {23,40} | {AAAAAAAAAAAA52814,AAAA48949,AAAAAAAAA34727,AAAA8857,AAAAAAAAAAAAAAAAAAA62179,AAAAAAAAAAAAAAA68526,AAAAAAA99836,AAAAAAAA50094,AAAA91194,AAAAAAAAAAAAA73084}
+ 36 | {79,82,14,52,30,5,79} | {AAAAAAAAA53663,AAAAAAAAAAAAAAAA55798,AAAAAAAAAAAAAAAAAAA89194,AA88409,AAAAAAAAAAAAAAA81326,AAAAAAAAAAAAAAAAA63050,AAAAAAAAAAAAAAAA33598}
+ 37 | {53,11,81,39,3,78,58,64,74} | {AAAAAAAAAAAAAAAAAAA17075,AAAAAAA66161,AAAAAAAA23648,AAAAAAAAAAAAAA10611}
+ 38 | {59,5,4,95,28} | {AAAAAAAAAAA82945,A96617,47735,AAAAA12179,AAAAA64669,AAAAAA99807,AA74433,AAAAAAAAAAAAAAAAA59387}
+ 39 | {82,43,99,16,74} | {AAAAAAAAAAAAAAA67062,AAAAAAA57334,AAAAAAAAAAAAAA65909,A27153,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAAAA43052,AAAAAAAAAA64777,AAAAAAAAAAAA81511,AAAAAAAAAAAAAA65909,AAAAAAAAAAAAAA28620}
+ 40 | {34} | {AAAAAAAAAAAAAA10611,AAAAAAAAAAAAAAAAAAA1205,AAAAAAAAAAA50956,AAAAAAAAAAAAAAAA31334,AAAAA70466,AAAAAAAA81587,AAAAAAA74623}
+ 41 | {19,26,63,12,93,73,27,94} | {AAAAAAA79710,AAAAAAAAAA55219,AAAA41702,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAAAAA71621,AAAAAAAAAAAAAAAAA63050,AAAAAAA99836,AAAAAAAAAAAAAA8666}
+ 42 | {15,76,82,75,8,91} | {AAAAAAAAAAA176,AAAAAA38063,45449,AAAAAA54032,AAAAAAA81898,AA6416,AAAAAAAAAAAAAAAAAAA62179,45449,AAAAA60038,AAAAAAAA81587}
+ 43 | {39,87,91,97,79,28} | {AAAAAAAAAAA74076,A96617,AAAAAAAAAAAAAAAAAAA89194,AAAAAAAAAAAAAAAAAA55796,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAA67946}
+ 44 | {40,58,68,29,54} | {AAAAAAA81898,AAAAAA66777,AAAAAA98232}
+ 45 | {99,45} | {AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611}
+ 46 | {53,24} | {AAAAAAAAAAA53908,AAAAAA54032,AAAAA17383,AAAA48949,AAAAAAAAAA18601,AAAAA64669,45449,AAAAAAAAAAA98051,AAAAAAAAAAAAAAAAAA71621}
+ 47 | {98,23,64,12,75,61} | {AAA59323,AAAAA95309,AAAAAAAAAAAAAAAA31334,AAAAAAAAA27249,AAAAA17383,AAAAAAAAAAAA37562,AAAAAA1059,A84822,55847,AAAAA70466}
+ 48 | {76,14} | {AAAAAAAAAAAAA59671,AAAAAAAAAAAAAAAAAAA91804,AAAAAA66777,AAAAAAAAAAAAAAAAAAA89194,AAAAAAAAAAAAAAA36627,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAA73084,AAAAAAA79710,AAAAAAAAAAAAAAA40402,AAAAAAAAAAAAAAAAAAA65037}
+ 49 | {56,5,54,37,49} | {AA21643,AAAAAAAAAAA92631,AAAAAAAA81587}
+ 50 | {20,12,37,64,93} | {AAAAAAAAAA5483,AAAAAAAAAAAAAAAAAAA1205,AA6416,AAAAAAAAAAAAAAAAA63050,AAAAAAAAAAAAAAAAAA47955}
+ 51 | {47} | {AAAAAAAAAAAAAA96505,AAAAAAAAAAAAAAAAAA36842,AAAAA95309,AAAAAAAA81587,AA6416,AAAA91194,AAAAAA58494,AAAAAA1059,AAAAAAAA69452}
+ 52 | {89,0} | {AAAAAAAAAAAAAAAAAA47955,AAAAAAA48038,AAAAAAAAAAAAAAAAA43052,AAAAAAAAAAAAA73084,AAAAA70466,AAAAAAAAAAAAAAAAA764,AAAAAAAAAAA46154,AA66862}
+ 53 | {38,17} | {AAAAAAAAAAA21658}
+ 54 | {70,47} | {AAAAAAAAAAAAAAAAAA54141,AAAAA40681,AAAAAAA48038,AAAAAAAAAAAAAAAA29150,AAAAA41597,AAAAAAAAAAAAAAAAAA59334,AA15322}
+ 55 | {47,79,47,64,72,25,71,24,93} | {AAAAAAAAAAAAAAAAAA55796,AAAAA62737}
+ 56 | {33,7,60,54,93,90,77,85,39} | {AAAAAAAAAAAAAAAAAA32918,AA42406}
+ 57 | {23,45,10,42,36,21,9,96} | {AAAAAAAAAAAAAAAAAAA70415}
+ 58 | {92} | {AAAAAAAAAAAAAAAA98414,AAAAAAAA23648,AAAAAAAAAAAAAAAAAA55796,AA25381,AAAAAAAAAAA6119}
+ 59 | {9,69,46,77} | {39557,AAAAAAA89932,AAAAAAAAAAAAAAAAA43052,AAAAAAAAAAAAAAAAA26540,AAA20874,AA6416,AAAAAAAAAAAAAAAAAA47955}
+ 60 | {62,2,59,38,89} | {AAAAAAA89932,AAAAAAAAAAAAAAAAAAA15356,AA99927,AA17009,AAAAAAAAAAAAAAA35875}
+ 61 | {72,2,44,95,54,54,13} | {AAAAAAAAAAAAAAAAAAA91804}
+ 62 | {83,72,29,73} | {AAAAAAAAAAAAA15097,AAAA8857,AAAAAAAAAAAA35809,AAAAAAAAAAAA52814,AAAAAAAAAAAAAAAAAAA38885,AAAAAAAAAAAAAAAAAA24183,AAAAAA43678,A96617}
+ 63 | {11,4,61,87} | {AAAAAAAAA27249,AAAAAAAAAAAAAAAAAA32918,AAAAAAAAAAAAAAA13198,AAA20874,39557,51533,AAAAAAAAAAA53908,AAAAAAAAAAAAAA96505,AAAAAAAA78938}
+ 64 | {26,19,34,24,81,78} | {A96617,AAAAAAAAAAAAAAAAAAA70104,A68938,AAAAAAAAAAA53908,AAAAAAAAAAAAAAA453,AA17009,AAAAAAA80240}
+ 65 | {61,5,76,59,17} | {AAAAAA99807,AAAAA64741,AAAAAAAAAAA53908,AA21643,AAAAAAAAA10012}
+ 66 | {31,23,70,52,4,33,48,25} | {AAAAAAAAAAAAAAAAA69675,AAAAAAAA50094,AAAAAAAAAAA92631,AAAA35194,39557,AAAAAAA99836}
+ 67 | {31,94,7,10} | {AAAAAA38063,A96617,AAAA35194,AAAAAAAAAAAA67946}
+ 68 | {90,43,38} | {AA75092,AAAAAAAAAAAAAAAAA69675,AAAAAAAAAAA92631,AAAAAAAAA10012,AAAAAAAAAAAAA7929,AA21643}
+ 69 | {67,35,99,85,72,86,44} | {AAAAAAAAAAAAAAAAAAA1205,AAAAAAAA50094,AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAAAAAAA47955}
+ 70 | {56,70,83} | {AAAA41702,AAAAAAAAAAA82945,AA21643,AAAAAAAAAAA99000,A27153,AA25381,AAAAAAAAAAAAAA96505,AAAAAAA1242}
+ 71 | {74,26} | {AAAAAAAAAAA50956,AA74433,AAAAAAA21462,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAA36627,AAAAAAAAAAAAA70254,AAAAAAAAAA43419,39557}
+ 72 | {22,1,16,78,20,91,83} | {47735,AAAAAAA56483,AAAAAAAAAAAAA93788,AA42406,AAAAAAAAAAAAA73084,AAAAAAAA72908,AAAAAAAAAAAAAAAAAA61286,AAAAA66674,AAAAAAAAAAAAAAAAA50407}
+ 73 | {88,25,96,78,65,15,29,19} | {AAA54451,AAAAAAAAA27249,AAAAAAA9228,AAAAAAAAAAAAAAA67062,AAAAAAAAAAAAAAAAAAA70415,AAAAA17383,AAAAAAAAAAAAAAAA33598}
+ 74 | {32} | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956}
+ 75 | {12,96,83,24,71,89,55} | {AAAA48949,AAAAAAAA29716,AAAAAAAAAAAAAAAAAAA1205,AAAAAAAAAAAA67946,AAAAAAAAAAAAAAAA29150,AAA28075,AAAAAAAAAAAAAAAAA43052}
+ 76 | {92,55,10,7} | {AAAAAAAAAAAAAAA67062}
+ 77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066}
+ 78 | {55,89,44,84,34} | {AAAAAAAAAAA6119,AAAAAAAAAAAAAA8666,AA99927,AA42406,AAAAAAA81898,AAAAAAA9228,AAAAAAAAAAA92631,AA21643,AAAAAAAAAAAAAA28620}
+ 79 | {45} | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908}
+ 80 | {74,89,44,80,0} | {AAAA35194,AAAAAAAA79710,AAA20874,AAAAAAAAAAAAAAAAAAA70104,AAAAAAAAAAAAA73084,AAAAAAA57334,AAAAAAA9228,AAAAAAAAAAAAA62007}
+ 81 | {63,77,54,48,61,53,97} | {AAAAAAAAAAAAAAA81326,AAAAAAAAAA22292,AA25381,AAAAAAAAAAA74076,AAAAAAA81898,AAAAAAAAA72121}
+ 82 | {34,60,4,79,78,16,86,89,42,50} | {AAAAA40681,AAAAAAAAAAAAAAAAAA12591,AAAAAAA80240,AAAAAAAAAAAAAAAA55798,AAAAAAAAAAAAAAAAAAA70104}
+ 83 | {14,10} | {AAAAAAAAAA22292,AAAAAAAAAAAAA70254,AAAAAAAAAAA6119}
+ 84 | {11,83,35,13,96,94} | {AAAAA95309,AAAAAAAAAAAAAAAAAA32918,AAAAAAAAAAAAAAAAAA24183}
+ 85 | {39,60} | {AAAAAAAAAAAAAAAA55798,AAAAAAAAAA22292,AAAAAAA66161,AAAAAAA21462,AAAAAAAAAAAAAAAAAA12591,55847,AAAAAA98232,AAAAAAAAAAA46154}
+ 86 | {33,81,72,74,45,36,82} | {AAAAAAAA81587,AAAAAAAAAAAAAA96505,45449,AAAA80176}
+ 87 | {57,27,50,12,97,68} | {AAAAAAAAAAAAAAAAA26540,AAAAAAAAA10012,AAAAAAAAAAAA35809,AAAAAAAAAAAAAAAA29150,AAAAAAAAAAA82945,AAAAAA66777,31228,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAA96505}
+ 88 | {41,90,77,24,6,24} | {AAAA35194,AAAA35194,AAAAAAA80240,AAAAAAAAAAA46154,AAAAAA58494,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAAAAA59334,AAAAAAAAAAAAAAAAAAA91804,AA74433}
+ 89 | {40,32,17,6,30,88} | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673}
+ 90 | {88,75} | {AAAAA60038,AAAAAAAA23648,AAAAAAAAAAA99000,AAAA41702,AAAAAAAAAAAAA22860,AAAAAAAAAAAAAAA68526}
+ 91 | {78} | {AAAAAAAAAAAAA62007,AAA99043}
+ 92 | {85,63,49,45} | {AAAAAAA89932,AAAAAAAAAAAAA22860,AAAAAAAAAAAAAAAAAAA1205,AAAAAAAAAAAA21089}
+ 93 | {11} | {AAAAAAAAAAA176,AAAAAAAAAAAAAA8666,AAAAAAAAAAAAAAA453,AAAAAAAAAAAAA85723,A68938,AAAAAAAAAAAAA9821,AAAAAAA48038,AAAAAAAAAAAAAAAAA59387,AA99927,AAAAA17383}
+ 94 | {98,9,85,62,88,91,60,61,38,86} | {AAAAAAAA81587,AAAAA17383,AAAAAAAA81587}
+ 95 | {47,77} | {AAAAAAAAAAAAAAAAA764,AAAAAAAAAAA74076,AAAAAAAAAA18107,AAAAA40681,AAAAAAAAAAAAAAA35875,AAAAA60038,AAAAAAA56483}
+ 96 | {23,97,43} | {AAAAAAAAAA646,A87088}
+ 97 | {54,2,86,65} | {47735,AAAAAAA99836,AAAAAAAAAAAAAAAAA6897,AAAAAAAAAAAAAAAA29150,AAAAAAA80240,AAAAAAAAAAAAAAAA98414,AAAAAAA56483,AAAAAAAAAAAAAAAA29150,AAAAAAA39692,AA21643}
+ 98 | {38,34,32,89} | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845}
+ 99 | {37,86} | {AAAAAAAAAAAAAAAAAA32918,AAAAA70514,AAAAAAAAA10012,AAAAAAAAAAAAAAAAA59387,AAAAAAAAAA64777,AAAAAAAAAAAAAAAAAAA15356}
+ 100 | {85,32,57,39,49,84,32,3,30} | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523}
+ 101 | {} | {}
+ 102 | {NULL} | {NULL}
+(102 rows)
+
+SELECT * FROM array_op_test WHERE i && '{}' ORDER BY seqno;
+ seqno | i | t
+-------+---+---
+(0 rows)
+
+SELECT * FROM array_op_test WHERE i <@ '{}' ORDER BY seqno;
+ seqno | i | t
+-------+----+----
+ 101 | {} | {}
+(1 row)
+
+SELECT * FROM array_op_test WHERE i = '{NULL}' ORDER BY seqno;
+ seqno | i | t
+-------+--------+--------
+ 102 | {NULL} | {NULL}
+(1 row)
+
+SELECT * FROM array_op_test WHERE i @> '{NULL}' ORDER BY seqno;
+ seqno | i | t
+-------+---+---
+(0 rows)
+
+SELECT * FROM array_op_test WHERE i && '{NULL}' ORDER BY seqno;
+ seqno | i | t
+-------+---+---
+(0 rows)
+
+SELECT * FROM array_op_test WHERE i <@ '{NULL}' ORDER BY seqno;
+ seqno | i | t
+-------+----+----
+ 101 | {} | {}
+(1 row)
+
+SELECT * FROM array_op_test WHERE t @> '{AAAAAAAA72908}' ORDER BY seqno;
+ seqno | i | t
+-------+-----------------------+--------------------------------------------------------------------------------------------------------------------------------------------
+ 22 | {11,6,56,62,53,30} | {AAAAAAAA72908}
+ 45 | {99,45} | {AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611}
+ 72 | {22,1,16,78,20,91,83} | {47735,AAAAAAA56483,AAAAAAAAAAAAA93788,AA42406,AAAAAAAAAAAAA73084,AAAAAAAA72908,AAAAAAAAAAAAAAAAAA61286,AAAAA66674,AAAAAAAAAAAAAAAAA50407}
+ 79 | {45} | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908}
+(4 rows)
+
+SELECT * FROM array_op_test WHERE t && '{AAAAAAAA72908}' ORDER BY seqno;
+ seqno | i | t
+-------+-----------------------+--------------------------------------------------------------------------------------------------------------------------------------------
+ 22 | {11,6,56,62,53,30} | {AAAAAAAA72908}
+ 45 | {99,45} | {AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611}
+ 72 | {22,1,16,78,20,91,83} | {47735,AAAAAAA56483,AAAAAAAAAAAAA93788,AA42406,AAAAAAAAAAAAA73084,AAAAAAAA72908,AAAAAAAAAAAAAAAAAA61286,AAAAA66674,AAAAAAAAAAAAAAAAA50407}
+ 79 | {45} | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908}
+(4 rows)
+
+SELECT * FROM array_op_test WHERE t @> '{AAAAAAAAAA646}' ORDER BY seqno;
+ seqno | i | t
+-------+------------------+--------------------------------------------------------------------
+ 15 | {17,14,16,63,67} | {AA6416,AAAAAAAAAA646,AAAAA95309}
+ 79 | {45} | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908}
+ 96 | {23,97,43} | {AAAAAAAAAA646,A87088}
+(3 rows)
+
+SELECT * FROM array_op_test WHERE t && '{AAAAAAAAAA646}' ORDER BY seqno;
+ seqno | i | t
+-------+------------------+--------------------------------------------------------------------
+ 15 | {17,14,16,63,67} | {AA6416,AAAAAAAAAA646,AAAAA95309}
+ 79 | {45} | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908}
+ 96 | {23,97,43} | {AAAAAAAAAA646,A87088}
+(3 rows)
+
+SELECT * FROM array_op_test WHERE t @> '{AAAAAAAA72908,AAAAAAAAAA646}' ORDER BY seqno;
+ seqno | i | t
+-------+------+--------------------------------------------------------------------
+ 79 | {45} | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908}
+(1 row)
+
+SELECT * FROM array_op_test WHERE t && '{AAAAAAAA72908,AAAAAAAAAA646}' ORDER BY seqno;
+ seqno | i | t
+-------+-----------------------+--------------------------------------------------------------------------------------------------------------------------------------------
+ 15 | {17,14,16,63,67} | {AA6416,AAAAAAAAAA646,AAAAA95309}
+ 22 | {11,6,56,62,53,30} | {AAAAAAAA72908}
+ 45 | {99,45} | {AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611}
+ 72 | {22,1,16,78,20,91,83} | {47735,AAAAAAA56483,AAAAAAAAAAAAA93788,AA42406,AAAAAAAAAAAAA73084,AAAAAAAA72908,AAAAAAAAAAAAAAAAAA61286,AAAAA66674,AAAAAAAAAAAAAAAAA50407}
+ 79 | {45} | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908}
+ 96 | {23,97,43} | {AAAAAAAAAA646,A87088}
+(6 rows)
+
+SELECT * FROM array_op_test WHERE t <@ '{AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611}' ORDER BY seqno;
+ seqno | i | t
+-------+--------------------+-----------------------------------------------------------------------------------------------------------
+ 22 | {11,6,56,62,53,30} | {AAAAAAAA72908}
+ 45 | {99,45} | {AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611}
+ 101 | {} | {}
+(3 rows)
+
+SELECT * FROM array_op_test WHERE t = '{}' ORDER BY seqno;
+ seqno | i | t
+-------+----+----
+ 101 | {} | {}
+(1 row)
+
+SELECT * FROM array_op_test WHERE t @> '{}' ORDER BY seqno;
+ seqno | i | t
+-------+---------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ 1 | {92,75,71,52,64,83} | {AAAAAAAA44066,AAAAAA1059,AAAAAAAAAAA176,AAAAAAA48038}
+ 2 | {3,6} | {AAAAAA98232,AAAAAAAA79710,AAAAAAAAAAAAAAAAA69675,AAAAAAAAAAAAAAAA55798,AAAAAAAAA12793}
+ 3 | {37,64,95,43,3,41,13,30,11,43} | {AAAAAAAAAA48845,AAAAA75968,AAAAA95309,AAA54451,AAAAAAAAAA22292,AAAAAAA99836,A96617,AA17009,AAAAAAAAAAAAAA95246}
+ 4 | {71,39,99,55,33,75,45} | {AAAAAAAAA53663,AAAAAAAAAAAAAAA67062,AAAAAAAAAA64777,AAA99043,AAAAAAAAAAAAAAAAAAA91804,39557}
+ 5 | {50,42,77,50,4} | {AAAAAAAAAAAAAAAAA26540,AAAAAAA79710,AAAAAAAAAAAAAAAAAAA1205,AAAAAAAAAAA176,AAAAA95309,AAAAAAAAAAA46154,AAAAAA66777,AAAAAAAAA27249,AAAAAAAAAA64777,AAAAAAAAAAAAAAAAAAA70104}
+ 6 | {39,35,5,94,17,92,60,32} | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657}
+ 7 | {12,51,88,64,8} | {AAAAAAAAAAAAAAAAAA12591,AAAAAAAAAAAAAAAAA50407,AAAAAAAAAAAA67946}
+ 8 | {60,84} | {AAAAAAA81898,AAAAAA1059,AAAAAAAAAAAA81511,AAAAA961,AAAAAAAAAAAAAAAA31334,AAAAA64741,AA6416,AAAAAAAAAAAAAAAAAA32918,AAAAAAAAAAAAAAAAA50407}
+ 9 | {56,52,35,27,80,44,81,22} | {AAAAAAAAAAAAAAA73034,AAAAAAAAAAAAA7929,AAAAAAA66161,AA88409,39557,A27153,AAAAAAAA9523,AAAAAAAAAAA99000}
+ 10 | {71,5,45} | {AAAAAAAAAAA21658,AAAAAAAAAAAA21089,AAA54451,AAAAAAAAAAAAAAAAAA54141,AAAAAAAAAAAAAA28620,AAAAAAAAAAA21658,AAAAAAAAAAA74076,AAAAAAAAA27249}
+ 11 | {41,86,74,48,22,74,47,50} | {AAAAAAAA9523,AAAAAAAAAAAA37562,AAAAAAAAAAAAAAAA14047,AAAAAAAAAAA46154,AAAA41702,AAAAAAAAAAAAAAAAA764,AAAAA62737,39557}
+ 12 | {17,99,18,52,91,72,0,43,96,23} | {AAAAA33250,AAAAAAAAAAAAAAAAAAA85420,AAAAAAAAAAA33576}
+ 13 | {3,52,34,23} | {AAAAAA98232,AAAA49534,AAAAAAAAAAA21658}
+ 14 | {78,57,19} | {AAAA8857,AAAAAAAAAAAAAAA73034,AAAAAAAA81587,AAAAAAAAAAAAAAA68526,AAAAA75968,AAAAAAAAAAAAAA65909,AAAAAAAAA10012,AAAAAAAAAAAAAA65909}
+ 15 | {17,14,16,63,67} | {AA6416,AAAAAAAAAA646,AAAAA95309}
+ 16 | {14,63,85,11} | {AAAAAA66777}
+ 17 | {7,10,81,85} | {AAAAAA43678,AAAAAAA12144,AAAAAAAAAAA50956,AAAAAAAAAAAAAAAAAAA15356}
+ 18 | {1} | {AAAAAAAAAAA33576,AAAAA95309,64261,AAA59323,AAAAAAAAAAAAAA95246,55847,AAAAAAAAAAAA67946,AAAAAAAAAAAAAAAAAA64374}
+ 19 | {52,82,17,74,23,46,69,51,75} | {AAAAAAAAAAAAA73084,AAAAA75968,AAAAAAAAAAAAAAAA14047,AAAAAAA80240,AAAAAAAAAAAAAAAAAAA1205,A68938}
+ 20 | {72,89,70,51,54,37,8,49,79} | {AAAAAA58494}
+ 21 | {2,8,65,10,5,79,43} | {AAAAAAAAAAAAAAAAA88852,AAAAAAAAAAAAAAAAAAA91804,AAAAA64669,AAAAAAAAAAAAAAAA1443,AAAAAAAAAAAAAAAA23657,AAAAA12179,AAAAAAAAAAAAAAAAA88852,AAAAAAAAAAAAAAAA31334,AAAAAAAAAAAAAAAA41303,AAAAAAAAAAAAAAAAAAA85420}
+ 22 | {11,6,56,62,53,30} | {AAAAAAAA72908}
+ 23 | {40,90,5,38,72,40,30,10,43,55} | {A6053,AAAAAAAAAAA6119,AA44673,AAAAAAAAAAAAAAAAA764,AA17009,AAAAA17383,AAAAA70514,AAAAA33250,AAAAA95309,AAAAAAAAAAAA37562}
+ 24 | {94,61,99,35,48} | {AAAAAAAAAAA50956,AAAAAAAAAAA15165,AAAA85070,AAAAAAAAAAAAAAA36627,AAAAA961,AAAAAAAAAA55219}
+ 25 | {31,1,10,11,27,79,38} | {AAAAAAAAAAAAAAAAAA59334,45449}
+ 26 | {71,10,9,69,75} | {47735,AAAAAAA21462,AAAAAAAAAAAAAAAAA6897,AAAAAAAAAAAAAAAAAAA91804,AAAAAAAAA72121,AAAAAAAAAAAAAAAAAAA1205,AAAAA41597,AAAA8857,AAAAAAAAAAAAAAAAAAA15356,AA17009}
+ 27 | {94} | {AA6416,A6053,AAAAAAA21462,AAAAAAA57334,AAAAAAAAAAAAAAAAAA12591,AA88409,AAAAAAAAAAAAA70254}
+ 28 | {14,33,6,34,14} | {AAAAAAAAAAAAAAA13198,AAAAAAAA69452,AAAAAAAAAAA82945,AAAAAAA12144,AAAAAAAAA72121,AAAAAAAAAA18601}
+ 29 | {39,21} | {AAAAAAAAAAAAAAAAA6897,AAAAAAAAAAAAAAAAAAA38885,AAAA85070,AAAAAAAAAAAAAAAAAAA70104,AAAAA66674,AAAAAAAAAAAAA62007,AAAAAAAA69452,AAAAAAA1242,AAAAAAAAAAAAAAAA1729,AAAA35194}
+ 30 | {26,81,47,91,34} | {AAAAAAAAAAAAAAAAAAA70104,AAAAAAA80240}
+ 31 | {80,24,18,21,54} | {AAAAAAAAAAAAAAA13198,AAAAAAAAAAAAAAAAAAA70415,A27153,AAAAAAAAA53663,AAAAAAAAAAAAAAAAA50407,A68938}
+ 32 | {58,79,82,80,67,75,98,10,41} | {AAAAAAAAAAAAAAAAAA61286,AAA54451,AAAAAAAAAAAAAAAAAAA87527,A96617,51533}
+ 33 | {74,73} | {A85417,AAAAAAA56483,AAAAA17383,AAAAAAAAAAAAA62159,AAAAAAAAAAAA52814,AAAAAAAAAAAAA85723,AAAAAAAAAAAAAAAAAA55796}
+ 34 | {70,45} | {AAAAAAAAAAAAAAAAAA71621,AAAAAAAAAAAAAA28620,AAAAAAAAAA55219,AAAAAAAA23648,AAAAAAAAAA22292,AAAAAAA1242}
+ 35 | {23,40} | {AAAAAAAAAAAA52814,AAAA48949,AAAAAAAAA34727,AAAA8857,AAAAAAAAAAAAAAAAAAA62179,AAAAAAAAAAAAAAA68526,AAAAAAA99836,AAAAAAAA50094,AAAA91194,AAAAAAAAAAAAA73084}
+ 36 | {79,82,14,52,30,5,79} | {AAAAAAAAA53663,AAAAAAAAAAAAAAAA55798,AAAAAAAAAAAAAAAAAAA89194,AA88409,AAAAAAAAAAAAAAA81326,AAAAAAAAAAAAAAAAA63050,AAAAAAAAAAAAAAAA33598}
+ 37 | {53,11,81,39,3,78,58,64,74} | {AAAAAAAAAAAAAAAAAAA17075,AAAAAAA66161,AAAAAAAA23648,AAAAAAAAAAAAAA10611}
+ 38 | {59,5,4,95,28} | {AAAAAAAAAAA82945,A96617,47735,AAAAA12179,AAAAA64669,AAAAAA99807,AA74433,AAAAAAAAAAAAAAAAA59387}
+ 39 | {82,43,99,16,74} | {AAAAAAAAAAAAAAA67062,AAAAAAA57334,AAAAAAAAAAAAAA65909,A27153,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAAAA43052,AAAAAAAAAA64777,AAAAAAAAAAAA81511,AAAAAAAAAAAAAA65909,AAAAAAAAAAAAAA28620}
+ 40 | {34} | {AAAAAAAAAAAAAA10611,AAAAAAAAAAAAAAAAAAA1205,AAAAAAAAAAA50956,AAAAAAAAAAAAAAAA31334,AAAAA70466,AAAAAAAA81587,AAAAAAA74623}
+ 41 | {19,26,63,12,93,73,27,94} | {AAAAAAA79710,AAAAAAAAAA55219,AAAA41702,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAAAAA71621,AAAAAAAAAAAAAAAAA63050,AAAAAAA99836,AAAAAAAAAAAAAA8666}
+ 42 | {15,76,82,75,8,91} | {AAAAAAAAAAA176,AAAAAA38063,45449,AAAAAA54032,AAAAAAA81898,AA6416,AAAAAAAAAAAAAAAAAAA62179,45449,AAAAA60038,AAAAAAAA81587}
+ 43 | {39,87,91,97,79,28} | {AAAAAAAAAAA74076,A96617,AAAAAAAAAAAAAAAAAAA89194,AAAAAAAAAAAAAAAAAA55796,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAA67946}
+ 44 | {40,58,68,29,54} | {AAAAAAA81898,AAAAAA66777,AAAAAA98232}
+ 45 | {99,45} | {AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611}
+ 46 | {53,24} | {AAAAAAAAAAA53908,AAAAAA54032,AAAAA17383,AAAA48949,AAAAAAAAAA18601,AAAAA64669,45449,AAAAAAAAAAA98051,AAAAAAAAAAAAAAAAAA71621}
+ 47 | {98,23,64,12,75,61} | {AAA59323,AAAAA95309,AAAAAAAAAAAAAAAA31334,AAAAAAAAA27249,AAAAA17383,AAAAAAAAAAAA37562,AAAAAA1059,A84822,55847,AAAAA70466}
+ 48 | {76,14} | {AAAAAAAAAAAAA59671,AAAAAAAAAAAAAAAAAAA91804,AAAAAA66777,AAAAAAAAAAAAAAAAAAA89194,AAAAAAAAAAAAAAA36627,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAA73084,AAAAAAA79710,AAAAAAAAAAAAAAA40402,AAAAAAAAAAAAAAAAAAA65037}
+ 49 | {56,5,54,37,49} | {AA21643,AAAAAAAAAAA92631,AAAAAAAA81587}
+ 50 | {20,12,37,64,93} | {AAAAAAAAAA5483,AAAAAAAAAAAAAAAAAAA1205,AA6416,AAAAAAAAAAAAAAAAA63050,AAAAAAAAAAAAAAAAAA47955}
+ 51 | {47} | {AAAAAAAAAAAAAA96505,AAAAAAAAAAAAAAAAAA36842,AAAAA95309,AAAAAAAA81587,AA6416,AAAA91194,AAAAAA58494,AAAAAA1059,AAAAAAAA69452}
+ 52 | {89,0} | {AAAAAAAAAAAAAAAAAA47955,AAAAAAA48038,AAAAAAAAAAAAAAAAA43052,AAAAAAAAAAAAA73084,AAAAA70466,AAAAAAAAAAAAAAAAA764,AAAAAAAAAAA46154,AA66862}
+ 53 | {38,17} | {AAAAAAAAAAA21658}
+ 54 | {70,47} | {AAAAAAAAAAAAAAAAAA54141,AAAAA40681,AAAAAAA48038,AAAAAAAAAAAAAAAA29150,AAAAA41597,AAAAAAAAAAAAAAAAAA59334,AA15322}
+ 55 | {47,79,47,64,72,25,71,24,93} | {AAAAAAAAAAAAAAAAAA55796,AAAAA62737}
+ 56 | {33,7,60,54,93,90,77,85,39} | {AAAAAAAAAAAAAAAAAA32918,AA42406}
+ 57 | {23,45,10,42,36,21,9,96} | {AAAAAAAAAAAAAAAAAAA70415}
+ 58 | {92} | {AAAAAAAAAAAAAAAA98414,AAAAAAAA23648,AAAAAAAAAAAAAAAAAA55796,AA25381,AAAAAAAAAAA6119}
+ 59 | {9,69,46,77} | {39557,AAAAAAA89932,AAAAAAAAAAAAAAAAA43052,AAAAAAAAAAAAAAAAA26540,AAA20874,AA6416,AAAAAAAAAAAAAAAAAA47955}
+ 60 | {62,2,59,38,89} | {AAAAAAA89932,AAAAAAAAAAAAAAAAAAA15356,AA99927,AA17009,AAAAAAAAAAAAAAA35875}
+ 61 | {72,2,44,95,54,54,13} | {AAAAAAAAAAAAAAAAAAA91804}
+ 62 | {83,72,29,73} | {AAAAAAAAAAAAA15097,AAAA8857,AAAAAAAAAAAA35809,AAAAAAAAAAAA52814,AAAAAAAAAAAAAAAAAAA38885,AAAAAAAAAAAAAAAAAA24183,AAAAAA43678,A96617}
+ 63 | {11,4,61,87} | {AAAAAAAAA27249,AAAAAAAAAAAAAAAAAA32918,AAAAAAAAAAAAAAA13198,AAA20874,39557,51533,AAAAAAAAAAA53908,AAAAAAAAAAAAAA96505,AAAAAAAA78938}
+ 64 | {26,19,34,24,81,78} | {A96617,AAAAAAAAAAAAAAAAAAA70104,A68938,AAAAAAAAAAA53908,AAAAAAAAAAAAAAA453,AA17009,AAAAAAA80240}
+ 65 | {61,5,76,59,17} | {AAAAAA99807,AAAAA64741,AAAAAAAAAAA53908,AA21643,AAAAAAAAA10012}
+ 66 | {31,23,70,52,4,33,48,25} | {AAAAAAAAAAAAAAAAA69675,AAAAAAAA50094,AAAAAAAAAAA92631,AAAA35194,39557,AAAAAAA99836}
+ 67 | {31,94,7,10} | {AAAAAA38063,A96617,AAAA35194,AAAAAAAAAAAA67946}
+ 68 | {90,43,38} | {AA75092,AAAAAAAAAAAAAAAAA69675,AAAAAAAAAAA92631,AAAAAAAAA10012,AAAAAAAAAAAAA7929,AA21643}
+ 69 | {67,35,99,85,72,86,44} | {AAAAAAAAAAAAAAAAAAA1205,AAAAAAAA50094,AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAAAAAAA47955}
+ 70 | {56,70,83} | {AAAA41702,AAAAAAAAAAA82945,AA21643,AAAAAAAAAAA99000,A27153,AA25381,AAAAAAAAAAAAAA96505,AAAAAAA1242}
+ 71 | {74,26} | {AAAAAAAAAAA50956,AA74433,AAAAAAA21462,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAA36627,AAAAAAAAAAAAA70254,AAAAAAAAAA43419,39557}
+ 72 | {22,1,16,78,20,91,83} | {47735,AAAAAAA56483,AAAAAAAAAAAAA93788,AA42406,AAAAAAAAAAAAA73084,AAAAAAAA72908,AAAAAAAAAAAAAAAAAA61286,AAAAA66674,AAAAAAAAAAAAAAAAA50407}
+ 73 | {88,25,96,78,65,15,29,19} | {AAA54451,AAAAAAAAA27249,AAAAAAA9228,AAAAAAAAAAAAAAA67062,AAAAAAAAAAAAAAAAAAA70415,AAAAA17383,AAAAAAAAAAAAAAAA33598}
+ 74 | {32} | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956}
+ 75 | {12,96,83,24,71,89,55} | {AAAA48949,AAAAAAAA29716,AAAAAAAAAAAAAAAAAAA1205,AAAAAAAAAAAA67946,AAAAAAAAAAAAAAAA29150,AAA28075,AAAAAAAAAAAAAAAAA43052}
+ 76 | {92,55,10,7} | {AAAAAAAAAAAAAAA67062}
+ 77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066}
+ 78 | {55,89,44,84,34} | {AAAAAAAAAAA6119,AAAAAAAAAAAAAA8666,AA99927,AA42406,AAAAAAA81898,AAAAAAA9228,AAAAAAAAAAA92631,AA21643,AAAAAAAAAAAAAA28620}
+ 79 | {45} | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908}
+ 80 | {74,89,44,80,0} | {AAAA35194,AAAAAAAA79710,AAA20874,AAAAAAAAAAAAAAAAAAA70104,AAAAAAAAAAAAA73084,AAAAAAA57334,AAAAAAA9228,AAAAAAAAAAAAA62007}
+ 81 | {63,77,54,48,61,53,97} | {AAAAAAAAAAAAAAA81326,AAAAAAAAAA22292,AA25381,AAAAAAAAAAA74076,AAAAAAA81898,AAAAAAAAA72121}
+ 82 | {34,60,4,79,78,16,86,89,42,50} | {AAAAA40681,AAAAAAAAAAAAAAAAAA12591,AAAAAAA80240,AAAAAAAAAAAAAAAA55798,AAAAAAAAAAAAAAAAAAA70104}
+ 83 | {14,10} | {AAAAAAAAAA22292,AAAAAAAAAAAAA70254,AAAAAAAAAAA6119}
+ 84 | {11,83,35,13,96,94} | {AAAAA95309,AAAAAAAAAAAAAAAAAA32918,AAAAAAAAAAAAAAAAAA24183}
+ 85 | {39,60} | {AAAAAAAAAAAAAAAA55798,AAAAAAAAAA22292,AAAAAAA66161,AAAAAAA21462,AAAAAAAAAAAAAAAAAA12591,55847,AAAAAA98232,AAAAAAAAAAA46154}
+ 86 | {33,81,72,74,45,36,82} | {AAAAAAAA81587,AAAAAAAAAAAAAA96505,45449,AAAA80176}
+ 87 | {57,27,50,12,97,68} | {AAAAAAAAAAAAAAAAA26540,AAAAAAAAA10012,AAAAAAAAAAAA35809,AAAAAAAAAAAAAAAA29150,AAAAAAAAAAA82945,AAAAAA66777,31228,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAA96505}
+ 88 | {41,90,77,24,6,24} | {AAAA35194,AAAA35194,AAAAAAA80240,AAAAAAAAAAA46154,AAAAAA58494,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAAAAA59334,AAAAAAAAAAAAAAAAAAA91804,AA74433}
+ 89 | {40,32,17,6,30,88} | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673}
+ 90 | {88,75} | {AAAAA60038,AAAAAAAA23648,AAAAAAAAAAA99000,AAAA41702,AAAAAAAAAAAAA22860,AAAAAAAAAAAAAAA68526}
+ 91 | {78} | {AAAAAAAAAAAAA62007,AAA99043}
+ 92 | {85,63,49,45} | {AAAAAAA89932,AAAAAAAAAAAAA22860,AAAAAAAAAAAAAAAAAAA1205,AAAAAAAAAAAA21089}
+ 93 | {11} | {AAAAAAAAAAA176,AAAAAAAAAAAAAA8666,AAAAAAAAAAAAAAA453,AAAAAAAAAAAAA85723,A68938,AAAAAAAAAAAAA9821,AAAAAAA48038,AAAAAAAAAAAAAAAAA59387,AA99927,AAAAA17383}
+ 94 | {98,9,85,62,88,91,60,61,38,86} | {AAAAAAAA81587,AAAAA17383,AAAAAAAA81587}
+ 95 | {47,77} | {AAAAAAAAAAAAAAAAA764,AAAAAAAAAAA74076,AAAAAAAAAA18107,AAAAA40681,AAAAAAAAAAAAAAA35875,AAAAA60038,AAAAAAA56483}
+ 96 | {23,97,43} | {AAAAAAAAAA646,A87088}
+ 97 | {54,2,86,65} | {47735,AAAAAAA99836,AAAAAAAAAAAAAAAAA6897,AAAAAAAAAAAAAAAA29150,AAAAAAA80240,AAAAAAAAAAAAAAAA98414,AAAAAAA56483,AAAAAAAAAAAAAAAA29150,AAAAAAA39692,AA21643}
+ 98 | {38,34,32,89} | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845}
+ 99 | {37,86} | {AAAAAAAAAAAAAAAAAA32918,AAAAA70514,AAAAAAAAA10012,AAAAAAAAAAAAAAAAA59387,AAAAAAAAAA64777,AAAAAAAAAAAAAAAAAAA15356}
+ 100 | {85,32,57,39,49,84,32,3,30} | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523}
+ 101 | {} | {}
+ 102 | {NULL} | {NULL}
+(102 rows)
+
+SELECT * FROM array_op_test WHERE t && '{}' ORDER BY seqno;
+ seqno | i | t
+-------+---+---
+(0 rows)
+
+SELECT * FROM array_op_test WHERE t <@ '{}' ORDER BY seqno;
+ seqno | i | t
+-------+----+----
+ 101 | {} | {}
+(1 row)
+
+-- array casts
+SELECT ARRAY[1,2,3]::text[]::int[]::float8[] AS "{1,2,3}";
+ {1,2,3}
+---------
+ {1,2,3}
+(1 row)
+
+SELECT ARRAY[1,2,3]::text[]::int[]::float8[] is of (float8[]) as "TRUE";
+ TRUE
+------
+ t
+(1 row)
+
+SELECT ARRAY[['a','bc'],['def','hijk']]::text[]::varchar[] AS "{{a,bc},{def,hijk}}";
+ {{a,bc},{def,hijk}}
+---------------------
+ {{a,bc},{def,hijk}}
+(1 row)
+
+SELECT ARRAY[['a','bc'],['def','hijk']]::text[]::varchar[] is of (varchar[]) as "TRUE";
+ TRUE
+------
+ t
+(1 row)
+
+SELECT CAST(ARRAY[[[[[['a','bb','ccc']]]]]] as text[]) as "{{{{{{a,bb,ccc}}}}}}";
+ {{{{{{a,bb,ccc}}}}}}
+----------------------
+ {{{{{{a,bb,ccc}}}}}}
+(1 row)
+
+-- scalar op any/all (array)
+select 33 = any ('{1,2,3}');
+ ?column?
+----------
+ f
+(1 row)
+
+select 33 = any ('{1,2,33}');
+ ?column?
+----------
+ t
+(1 row)
+
+select 33 = all ('{1,2,33}');
+ ?column?
+----------
+ f
+(1 row)
+
+select 33 >= all ('{1,2,33}');
+ ?column?
+----------
+ t
+(1 row)
+
+-- boundary cases
+select null::int >= all ('{1,2,33}');
+ ?column?
+----------
+
+(1 row)
+
+select null::int >= all ('{}');
+ ?column?
+----------
+ t
+(1 row)
+
+select null::int >= any ('{}');
+ ?column?
+----------
+ f
+(1 row)
+
+-- cross-datatype
+select 33.4 = any (array[1,2,3]);
+ ?column?
+----------
+ f
+(1 row)
+
+select 33.4 > all (array[1,2,3]);
+ ?column?
+----------
+ t
+(1 row)
+
+-- errors
+select 33 * any ('{1,2,3}');
+ERROR: op ANY/ALL (array) requires operator to yield boolean
+LINE 1: select 33 * any ('{1,2,3}');
+ ^
+select 33 * any (44);
+ERROR: op ANY/ALL (array) requires array on right side
+LINE 1: select 33 * any (44);
+ ^
+-- nulls
+select 33 = any (null::int[]);
+ ?column?
+----------
+
+(1 row)
+
+select null::int = any ('{1,2,3}');
+ ?column?
+----------
+
+(1 row)
+
+select 33 = any ('{1,null,3}');
+ ?column?
+----------
+
+(1 row)
+
+select 33 = any ('{1,null,33}');
+ ?column?
+----------
+ t
+(1 row)
+
+select 33 = all (null::int[]);
+ ?column?
+----------
+
+(1 row)
+
+select null::int = all ('{1,2,3}');
+ ?column?
+----------
+
+(1 row)
+
+select 33 = all ('{1,null,3}');
+ ?column?
+----------
+ f
+(1 row)
+
+select 33 = all ('{33,null,33}');
+ ?column?
+----------
+
+(1 row)
+
+-- test indexes on arrays
+create temp table arr_tbl (f1 int[] unique);
+ERROR: Column f1 is not a hash distributable data type
+insert into arr_tbl values ('{1,2,3}');
+ERROR: relation "arr_tbl" does not exist
+LINE 1: insert into arr_tbl values ('{1,2,3}');
+ ^
+insert into arr_tbl values ('{1,2}');
+ERROR: relation "arr_tbl" does not exist
+LINE 1: insert into arr_tbl values ('{1,2}');
+ ^
+-- failure expected:
+insert into arr_tbl values ('{1,2,3}');
+ERROR: relation "arr_tbl" does not exist
+LINE 1: insert into arr_tbl values ('{1,2,3}');
+ ^
+insert into arr_tbl values ('{2,3,4}');
+ERROR: relation "arr_tbl" does not exist
+LINE 1: insert into arr_tbl values ('{2,3,4}');
+ ^
+insert into arr_tbl values ('{1,5,3}');
+ERROR: relation "arr_tbl" does not exist
+LINE 1: insert into arr_tbl values ('{1,5,3}');
+ ^
+insert into arr_tbl values ('{1,2,10}');
+ERROR: relation "arr_tbl" does not exist
+LINE 1: insert into arr_tbl values ('{1,2,10}');
+ ^
+set enable_seqscan to off;
+set enable_bitmapscan to off;
+select * from arr_tbl where f1 > '{1,2,3}' and f1 <= '{1,5,3}' ORDER BY 1;
+ERROR: relation "arr_tbl" does not exist
+LINE 1: select * from arr_tbl where f1 > '{1,2,3}' and f1 <= '{1,5,3...
+ ^
+select * from arr_tbl where f1 >= '{1,2,3}' and f1 < '{1,5,3}' ORDER BY 1;
+ERROR: relation "arr_tbl" does not exist
+LINE 1: select * from arr_tbl where f1 >= '{1,2,3}' and f1 < '{1,5,3...
+ ^
+-- note: if above selects don't produce the expected tuple order,
+-- then you didn't get an indexscan plan, and something is busted.
+reset enable_seqscan;
+reset enable_bitmapscan;
+-- test [not] (like|ilike) (any|all) (...)
+select 'foo' like any (array['%a', '%o']); -- t
+ ?column?
+----------
+ t
+(1 row)
+
+select 'foo' like any (array['%a', '%b']); -- f
+ ?column?
+----------
+ f
+(1 row)
+
+select 'foo' like all (array['f%', '%o']); -- t
+ ?column?
+----------
+ t
+(1 row)
+
+select 'foo' like all (array['f%', '%b']); -- f
+ ?column?
+----------
+ f
+(1 row)
+
+select 'foo' not like any (array['%a', '%b']); -- t
+ ?column?
+----------
+ t
+(1 row)
+
+select 'foo' not like all (array['%a', '%o']); -- f
+ ?column?
+----------
+ f
+(1 row)
+
+select 'foo' ilike any (array['%A', '%O']); -- t
+ ?column?
+----------
+ t
+(1 row)
+
+select 'foo' ilike all (array['F%', '%O']); -- t
+ ?column?
+----------
+ t
+(1 row)
+
+--
+-- General array parser tests
+--
+-- none of the following should be accepted
+select '{{1,{2}},{2,3}}'::text[];
+ERROR: malformed array literal: "{{1,{2}},{2,3}}"
+LINE 1: select '{{1,{2}},{2,3}}'::text[];
+ ^
+select '{{},{}}'::text[];
+ERROR: malformed array literal: "{{},{}}"
+LINE 1: select '{{},{}}'::text[];
+ ^
+select E'{{1,2},\\{2,3}}'::text[];
+ERROR: malformed array literal: "{{1,2},\{2,3}}"
+LINE 1: select E'{{1,2},\\{2,3}}'::text[];
+ ^
+select '{{"1 2" x},{3}}'::text[];
+ERROR: malformed array literal: "{{"1 2" x},{3}}"
+LINE 1: select '{{"1 2" x},{3}}'::text[];
+ ^
+select '{}}'::text[];
+ERROR: malformed array literal: "{}}"
+LINE 1: select '{}}'::text[];
+ ^
+select '{ }}'::text[];
+ERROR: malformed array literal: "{ }}"
+LINE 1: select '{ }}'::text[];
+ ^
+select array[];
+ERROR: cannot determine type of empty array
+LINE 1: select array[];
+ ^
+HINT: Explicitly cast to the desired type, for example ARRAY[]::integer[].
+-- none of the above should be accepted
+-- all of the following should be accepted
+select '{}'::text[];
+ text
+------
+ {}
+(1 row)
+
+select '{{{1,2,3,4},{2,3,4,5}},{{3,4,5,6},{4,5,6,7}}}'::text[];
+ text
+-----------------------------------------------
+ {{{1,2,3,4},{2,3,4,5}},{{3,4,5,6},{4,5,6,7}}}
+(1 row)
+
+select '{0 second ,0 second}'::interval[];
+ interval
+---------------
+ {"@ 0","@ 0"}
+(1 row)
+
+select '{ { "," } , { 3 } }'::text[];
+ text
+-------------
+ {{","},{3}}
+(1 row)
+
+select ' { { " 0 second " , 0 second } }'::text[];
+ text
+-------------------------------
+ {{" 0 second ","0 second"}}
+(1 row)
+
+select '{
+ 0 second,
+ @ 1 hour @ 42 minutes @ 20 seconds
+ }'::interval[];
+ interval
+------------------------------------
+ {"@ 0","@ 1 hour 42 mins 20 secs"}
+(1 row)
+
+select array[]::text[];
+ array
+-------
+ {}
+(1 row)
+
+select '[0:1]={1.1,2.2}'::float8[];
+ float8
+-----------------
+ [0:1]={1.1,2.2}
+(1 row)
+
+-- all of the above should be accepted
+-- tests for array aggregates
+CREATE TEMP TABLE arraggtest ( f1 INT[], f2 TEXT[][], f3 FLOAT[]);
+INSERT INTO arraggtest (f1, f2, f3) VALUES
+('{1,2,3,4}','{{grey,red},{blue,blue}}','{1.6, 0.0}');
+INSERT INTO arraggtest (f1, f2, f3) VALUES
+('{1,2,3}','{{grey,red},{grey,blue}}','{1.6}');
+SELECT max(f1), min(f1), max(f2), min(f2), max(f3), min(f3) FROM arraggtest;
+ max | min | max | min | max | min
+-----------+---------+--------------------------+--------------------------+---------+-------
+ {1,2,3,4} | {1,2,3} | {{grey,red},{grey,blue}} | {{grey,red},{blue,blue}} | {1.6,0} | {1.6}
+(1 row)
+
+INSERT INTO arraggtest (f1, f2, f3) VALUES
+('{3,3,2,4,5,6}','{{white,yellow},{pink,orange}}','{2.1,3.3,1.8,1.7,1.6}');
+SELECT max(f1), min(f1), max(f2), min(f2), max(f3), min(f3) FROM arraggtest;
+ max | min | max | min | max | min
+---------------+---------+--------------------------------+--------------------------+-----------------------+-------
+ {3,3,2,4,5,6} | {1,2,3} | {{white,yellow},{pink,orange}} | {{grey,red},{blue,blue}} | {2.1,3.3,1.8,1.7,1.6} | {1.6}
+(1 row)
+
+INSERT INTO arraggtest (f1, f2, f3) VALUES
+('{2}','{{black,red},{green,orange}}','{1.6,2.2,2.6,0.4}');
+SELECT max(f1), min(f1), max(f2), min(f2), max(f3), min(f3) FROM arraggtest;
+ max | min | max | min | max | min
+---------------+---------+--------------------------------+------------------------------+-----------------------+-------
+ {3,3,2,4,5,6} | {1,2,3} | {{white,yellow},{pink,orange}} | {{black,red},{green,orange}} | {2.1,3.3,1.8,1.7,1.6} | {1.6}
+(1 row)
+
+INSERT INTO arraggtest (f1, f2, f3) VALUES
+('{4,2,6,7,8,1}','{{red},{black},{purple},{blue},{blue}}',NULL);
+SELECT max(f1), min(f1), max(f2), min(f2), max(f3), min(f3) FROM arraggtest;
+ max | min | max | min | max | min
+---------------+---------+--------------------------------+------------------------------+-----------------------+-------
+ {4,2,6,7,8,1} | {1,2,3} | {{white,yellow},{pink,orange}} | {{black,red},{green,orange}} | {2.1,3.3,1.8,1.7,1.6} | {1.6}
+(1 row)
+
+INSERT INTO arraggtest (f1, f2, f3) VALUES
+('{}','{{pink,white,blue,red,grey,orange}}','{2.1,1.87,1.4,2.2}');
+SELECT max(f1), min(f1), max(f2), min(f2), max(f3), min(f3) FROM arraggtest;
+ max | min | max | min | max | min
+---------------+-----+--------------------------------+------------------------------+-----------------------+-------
+ {4,2,6,7,8,1} | {} | {{white,yellow},{pink,orange}} | {{black,red},{green,orange}} | {2.1,3.3,1.8,1.7,1.6} | {1.6}
+(1 row)
+
+-- A few simple tests for arrays of composite types
+create type comptype as (f1 int, f2 text);
+create table comptable (c1 comptype, c2 comptype[]);
+-- XXX would like to not have to specify row() construct types here ...
+insert into comptable
+ values (row(1,'foo'), array[row(2,'bar')::comptype, row(3,'baz')::comptype]);
+-- check that implicitly named array type _comptype isn't a problem
+create type _comptype as enum('fooey');
+select * from comptable;
+ c1 | c2
+---------+-----------------------
+ (1,foo) | {"(2,bar)","(3,baz)"}
+(1 row)
+
+select c2[2].f2 from comptable;
+ f2
+-----
+ baz
+(1 row)
+
+drop type _comptype;
+drop table comptable;
+drop type comptype;
+create or replace function unnest1(anyarray)
+returns setof anyelement as $$
+select $1[s] from generate_subscripts($1,1) g(s);
+$$ language sql immutable;
+create or replace function unnest2(anyarray)
+returns setof anyelement as $$
+select $1[s1][s2] from generate_subscripts($1,1) g1(s1),
+ generate_subscripts($1,2) g2(s2);
+$$ language sql immutable;
+select * from unnest1(array[1,2,3]);
+ unnest1
+---------
+ 1
+ 2
+ 3
+(3 rows)
+
+select * from unnest2(array[[1,2,3],[4,5,6]]);
+ unnest2
+---------
+ 1
+ 2
+ 3
+ 4
+ 5
+ 6
+(6 rows)
+
+drop function unnest1(anyarray);
+drop function unnest2(anyarray);
+select array_fill(null::integer, array[3,3],array[2,2]);
+ array_fill
+-----------------------------------------------------------------
+ [2:4][2:4]={{NULL,NULL,NULL},{NULL,NULL,NULL},{NULL,NULL,NULL}}
+(1 row)
+
+select array_fill(null::integer, array[3,3]);
+ array_fill
+------------------------------------------------------
+ {{NULL,NULL,NULL},{NULL,NULL,NULL},{NULL,NULL,NULL}}
+(1 row)
+
+select array_fill(null::text, array[3,3],array[2,2]);
+ array_fill
+-----------------------------------------------------------------
+ [2:4][2:4]={{NULL,NULL,NULL},{NULL,NULL,NULL},{NULL,NULL,NULL}}
+(1 row)
+
+select array_fill(null::text, array[3,3]);
+ array_fill
+------------------------------------------------------
+ {{NULL,NULL,NULL},{NULL,NULL,NULL},{NULL,NULL,NULL}}
+(1 row)
+
+select array_fill(7, array[3,3],array[2,2]);
+ array_fill
+--------------------------------------
+ [2:4][2:4]={{7,7,7},{7,7,7},{7,7,7}}
+(1 row)
+
+select array_fill(7, array[3,3]);
+ array_fill
+---------------------------
+ {{7,7,7},{7,7,7},{7,7,7}}
+(1 row)
+
+select array_fill('juhu'::text, array[3,3],array[2,2]);
+ array_fill
+-----------------------------------------------------------------
+ [2:4][2:4]={{juhu,juhu,juhu},{juhu,juhu,juhu},{juhu,juhu,juhu}}
+(1 row)
+
+select array_fill('juhu'::text, array[3,3]);
+ array_fill
+------------------------------------------------------
+ {{juhu,juhu,juhu},{juhu,juhu,juhu},{juhu,juhu,juhu}}
+(1 row)
+
+-- raise exception
+select array_fill(1, null, array[2,2]);
+ERROR: dimension array or low bound array cannot be null
+select array_fill(1, array[2,2], null);
+ERROR: dimension array or low bound array cannot be null
+select array_fill(1, array[3,3], array[1,1,1]);
+ERROR: wrong number of array subscripts
+DETAIL: Low bound array has different size than dimensions array.
+select array_fill(1, array[1,2,null]);
+ERROR: dimension values cannot be null
+select string_to_array('1|2|3', '|');
+ string_to_array
+-----------------
+ {1,2,3}
+(1 row)
+
+select string_to_array('1|2|3|', '|');
+ string_to_array
+-----------------
+ {1,2,3,""}
+(1 row)
+
+select string_to_array('1||2|3||', '||');
+ string_to_array
+-----------------
+ {1,2|3,""}
+(1 row)
+
+select string_to_array('1|2|3', '');
+ string_to_array
+-----------------
+ {1|2|3}
+(1 row)
+
+select string_to_array('', '|');
+ string_to_array
+-----------------
+ {}
+(1 row)
+
+select string_to_array('1|2|3', NULL);
+ string_to_array
+-----------------
+ {1,|,2,|,3}
+(1 row)
+
+select string_to_array(NULL, '|') IS NULL;
+ ?column?
+----------
+ t
+(1 row)
+
+select string_to_array('abc', '');
+ string_to_array
+-----------------
+ {abc}
+(1 row)
+
+select string_to_array('abc', '', 'abc');
+ string_to_array
+-----------------
+ {NULL}
+(1 row)
+
+select string_to_array('abc', ',');
+ string_to_array
+-----------------
+ {abc}
+(1 row)
+
+select string_to_array('abc', ',', 'abc');
+ string_to_array
+-----------------
+ {NULL}
+(1 row)
+
+select string_to_array('1,2,3,4,,6', ',');
+ string_to_array
+-----------------
+ {1,2,3,4,"",6}
+(1 row)
+
+select string_to_array('1,2,3,4,,6', ',', '');
+ string_to_array
+------------------
+ {1,2,3,4,NULL,6}
+(1 row)
+
+select string_to_array('1,2,3,4,*,6', ',', '*');
+ string_to_array
+------------------
+ {1,2,3,4,NULL,6}
+(1 row)
+
+select array_to_string(NULL::int4[], ',') IS NULL;
+ ?column?
+----------
+ t
+(1 row)
+
+select array_to_string('{}'::int4[], ',');
+ array_to_string
+-----------------
+
+(1 row)
+
+select array_to_string(array[1,2,3,4,NULL,6], ',');
+ array_to_string
+-----------------
+ 1,2,3,4,6
+(1 row)
+
+select array_to_string(array[1,2,3,4,NULL,6], ',', '*');
+ array_to_string
+-----------------
+ 1,2,3,4,*,6
+(1 row)
+
+select array_to_string(array[1,2,3,4,NULL,6], NULL);
+ array_to_string
+-----------------
+
+(1 row)
+
+select array_to_string(array[1,2,3,4,NULL,6], ',', NULL);
+ array_to_string
+-----------------
+ 1,2,3,4,6
+(1 row)
+
+select array_to_string(string_to_array('1|2|3', '|'), '|');
+ array_to_string
+-----------------
+ 1|2|3
+(1 row)
+
+select array_length(array[1,2,3], 1);
+ array_length
+--------------
+ 3
+(1 row)
+
+select array_length(array[[1,2,3], [4,5,6]], 0);
+ array_length
+--------------
+
+(1 row)
+
+select array_length(array[[1,2,3], [4,5,6]], 1);
+ array_length
+--------------
+ 2
+(1 row)
+
+select array_length(array[[1,2,3], [4,5,6]], 2);
+ array_length
+--------------
+ 3
+(1 row)
+
+select array_length(array[[1,2,3], [4,5,6]], 3);
+ array_length
+--------------
+
+(1 row)
+
+select array_agg(unique1) from (select unique1 from tenk1 where unique1 < 15 order by unique1) ss;
+ array_agg
+--------------------------------------
+ {0,1,2,3,4,5,6,7,8,9,10,11,12,13,14}
+(1 row)
+
+select array_agg(ten) from (select ten from tenk1 where unique1 < 15 order by unique1) ss;
+ array_agg
+---------------------------------
+ {0,1,2,3,4,5,6,7,8,9,0,1,2,3,4}
+(1 row)
+
+select array_agg(nullif(ten, 4)) from (select ten from tenk1 where unique1 < 15 order by unique1) ss;
+ array_agg
+---------------------------------------
+ {0,1,2,3,NULL,5,6,7,8,9,0,1,2,3,NULL}
+(1 row)
+
+select array_agg(unique1) from tenk1 where unique1 < -15;
+ array_agg
+-----------
+
+(1 row)
+
+select unnest(array[1,2,3]);
+ unnest
+--------
+ 1
+ 2
+ 3
+(3 rows)
+
+select * from unnest(array[1,2,3]);
+ unnest
+--------
+ 1
+ 2
+ 3
+(3 rows)
+
+select unnest(array[1,2,3,4.5]::float8[]);
+ unnest
+--------
+ 1
+ 2
+ 3
+ 4.5
+(4 rows)
+
+select unnest(array[1,2,3,4.5]::numeric[]);
+ unnest
+--------
+ 1
+ 2
+ 3
+ 4.5
+(4 rows)
+
+select unnest(array[1,2,3,null,4,null,null,5,6]);
+ unnest
+--------
+ 1
+ 2
+ 3
+
+ 4
+
+
+ 5
+ 6
+(9 rows)
+
+select unnest(array[1,2,3,null,4,null,null,5,6]::text[]);
+ unnest
+--------
+ 1
+ 2
+ 3
+
+ 4
+
+
+ 5
+ 6
+(9 rows)
+
+-- Insert/update on a column that is array of composite
+create temp table t1 (f1 int8_tbl[]);
+insert into t1 (f1[5].q1) values(42);
+select * from t1;
+ f1
+-----------------
+ [5:5]={"(42,)"}
+(1 row)
+
+update t1 set f1[5].q2 = 43;
+select * from t1;
+ f1
+-------------------
+ [5:5]={"(42,43)"}
+(1 row)
+
diff --git a/src/test/regress/expected/xc_FQS_join.out b/src/test/regress/expected/xc_FQS_join.out
index 76348c82c0..aa496cc9a0 100644
--- a/src/test/regress/expected/xc_FQS_join.out
+++ b/src/test/regress/expected/xc_FQS_join.out
@@ -480,11 +480,11 @@ explain (costs off, verbose on, nodes off) select * from tab1_mod, tab3_mod
set enable_remotejoin to false;
explain (costs off, verbose on, nodes off) update tab1_mod set val2 = 1000 from tab2_mod
where tab1_mod.val = tab2_mod.val and tab1_mod. val2 = tab2_mod.val2;
- QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------------------
Update on public.tab1_mod
Node expr: tab1_mod.val
- Remote query: UPDATE public.tab1_mod SET val2 = $2 WHERE val = $3 AND val2 = $4 AND ctid = $5 AND xc_node_id = $6 AND val = $1 AND $7 = $7
+ Remote query: UPDATE public.tab1_mod SET val2 = $2 WHERE ctid = $5 AND xc_node_id = $6
-> Nested Loop
Output: tab1_mod.val, 1000, tab1_mod.val, tab1_mod.val2, tab1_mod.ctid, tab1_mod.xc_node_id, tab2_mod.ctid
Join Filter: ((tab1_mod.val = tab2_mod.val) AND (tab1_mod.val2 = tab2_mod.val2))
@@ -498,10 +498,10 @@ explain (costs off, verbose on, nodes off) update tab1_mod set val2 = 1000 from
explain (costs off, verbose on, nodes off) delete from tab1_mod using tab2_mod
where tab1_mod.val = tab2_mod.val and tab1_mod.val2 = tab2_mod.val2;
- QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------------------------
Delete on public.tab1_mod
- Remote query: DELETE FROM public.tab1_mod WHERE val = $1 AND val2 = $2 AND ctid = $3 AND xc_node_id = $4 AND $5 = $5
+ Remote query: DELETE FROM public.tab1_mod WHERE ctid = $3 AND xc_node_id = $4
-> Nested Loop
Output: tab1_mod.val, tab1_mod.val2, tab1_mod.ctid, tab1_mod.xc_node_id, tab2_mod.ctid
Join Filter: ((tab1_mod.val = tab2_mod.val) AND (tab1_mod.val2 = tab2_mod.val2))
@@ -515,10 +515,10 @@ explain (costs off, verbose on, nodes off) delete from tab1_mod using tab2_mod
explain (costs off, verbose on, nodes off) update tab1_rep set val2 = 1000 from tab2_rep
where tab1_rep.val = tab2_rep.val and tab1_rep.val2 = tab2_rep.val2;
- QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------
Update on public.tab1_rep
- Remote query: UPDATE public.tab1_rep SET val2 = $2 WHERE val = $3 AND val2 = $4 AND ctid = $5 AND val = $1 AND $6 = $6
+ Remote query: UPDATE public.tab1_rep SET val2 = $2 WHERE ctid = $5
-> Nested Loop
Output: tab1_rep.val, 1000, tab1_rep.val, tab1_rep.val2, tab1_rep.ctid, tab2_rep.ctid
Join Filter: ((tab1_rep.val = tab2_rep.val) AND (tab1_rep.val2 = tab2_rep.val2))
@@ -532,10 +532,10 @@ explain (costs off, verbose on, nodes off) update tab1_rep set val2 = 1000 from
explain (costs off, verbose on, nodes off) delete from tab1_rep using tab2_rep
where tab1_rep.val = tab2_rep.val and tab1_rep.val2 = tab2_rep.val2;
- QUERY PLAN
------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------------------
Delete on public.tab1_rep
- Remote query: DELETE FROM public.tab1_rep WHERE val = $1 AND val2 = $2 AND ctid = $3 AND $4 = $4
+ Remote query: DELETE FROM public.tab1_rep WHERE ctid = $3
-> Nested Loop
Output: tab1_rep.val, tab1_rep.val2, tab1_rep.ctid, tab2_rep.ctid
Join Filter: ((tab1_rep.val = tab2_rep.val) AND (tab1_rep.val2 = tab2_rep.val2))
generated by cgit v1.2.3 (git 2.39.1) at 2025-12-17 18:40:47 +0000