array concat, et al patch (was: [GENERAL] join of array) - Mailing list pgsql-patches

Tom Lane wrote:
> Could you look at how big a change it'd be, anyway?  Offhand I think it
> may just mean that the subscript-checking done in parse_expr.c needs to
> be done at runtime instead.  Remember parse_expr should only be
> concerned about determining datatype, and for its purposes all arrays of
> a given element type are the same --- subscript checking should happen
> at runtime.  (It seems likely that having an ndims field in ArrayExpr
> is inappropriate.)

The attached patch fixes code and regression tests for the following
(docs to follow once applied):

========================================================================
1) Array concatenation of equidimensional arrays:
========================================================================
regression=# select ARRAY[1,2] || ARRAY[3,4];
  ?column?
-----------
  {1,2,3,4}
(1 row)

regression=# select ARRAY[[1],[2],[3]] || ARRAY[[4],[5]];
        ?column?
-----------------------
  {{1},{2},{3},{4},{5}}
(1 row)

regression=# select ARRAY[[1,2],[2,3],[3,4]] || ARRAY[[4,5],[5,6]];
             ?column?
---------------------------------
  {{1,2},{2,3},{3,4},{4,5},{5,6}}
(1 row)


========================================================================
2) Array literals or vars in ARRAY expressions:
========================================================================
regression=# create table arr(f1 int[], f2 int[]);
CREATE TABLE
regression=# insert into arr values (ARRAY[[1,2],[3,4]],ARRAY[[5,6],[7,8]]);
INSERT 2635544 1
regression=# select ARRAY[f1,f2] from arr;
              array
-------------------------------
  {{{1,2},{3,4}},{{5,6},{7,8}}}
(1 row)

regression=# select ARRAY['{{1,2},{3,4}}'::int[],'{{5,6},{7,8}}'::int[]];
              array
-------------------------------
  {{{1,2},{3,4}},{{5,6},{7,8}}}
(1 row)


========================================================================
3) Lower bound of outer array adjusted downward when an "element" (which
could itself be an array) is concatenated onto the front of an array:
========================================================================
regression=# create table arr(f1 int[]);
CREATE TABLE
regression=# insert into arr values ('{}');
INSERT 2635538 1
regression=# update arr set f1[-2] = 1;
UPDATE 1
regression=# select array_lower(f1,1) from arr;
  array_lower
-------------
           -2
(1 row)

regression=# select array_lower(f1 || 2, 1) from arr;
  array_lower
-------------
           -2
(1 row)

regression=# select array_lower(0 || f1, 1) from arr;
  array_lower
-------------
           -3
(1 row)

regression=# update arr set f1 = ARRAY[[1,2],[3,4]];
UPDATE 1
regression=# select array_lower(f1,1) from arr;
  array_lower
-------------
            1
(1 row)

regression=# select array_lower(f1 || ARRAY[5,6], 1) from arr;
  array_lower
-------------
            1
(1 row)

regression=# select array_lower(ARRAY[-1,0] || f1, 1) from arr;
  array_lower
-------------
            0
(1 row)


Compiles without warnings and passes all regression tests. If there are
no objections, please apply. As I mentioned above, docs to follow once
I'm sure what actually ends up being committed.

Joe
Index: src/backend/executor/execQual.c
===================================================================
RCS file: /opt/src/cvs/pgsql-server/src/backend/executor/execQual.c,v
retrieving revision 1.141
diff -c -r1.141 execQual.c
*** src/backend/executor/execQual.c    8 Aug 2003 21:41:39 -0000    1.141
--- src/backend/executor/execQual.c    15 Aug 2003 21:52:30 -0000
***************
*** 1620,1635 ****
      ArrayType  *result;
      List       *element;
      Oid            element_type = arrayExpr->element_typeid;
!     int            ndims = arrayExpr->ndims;
      int            dims[MAXDIM];
      int            lbs[MAXDIM];

!     if (ndims == 1)
      {
          int            nelems;
          Datum       *dvalues;
          int            i = 0;

          nelems = length(astate->elements);

          /* Shouldn't happen here, but if length is 0, return NULL */
--- 1620,1637 ----
      ArrayType  *result;
      List       *element;
      Oid            element_type = arrayExpr->element_typeid;
!     int            ndims = 0;
      int            dims[MAXDIM];
      int            lbs[MAXDIM];

!     if (!arrayExpr->multidims)
      {
+         /* Elements are presumably of scalar type */
          int            nelems;
          Datum       *dvalues;
          int            i = 0;

+         ndims = 1;
          nelems = length(astate->elements);

          /* Shouldn't happen here, but if length is 0, return NULL */
***************
*** 1667,1672 ****
--- 1669,1675 ----
      }
      else
      {
+         /* Must be nested array expressions */
          char       *dat = NULL;
          Size        ndatabytes = 0;
          int            nbytes;
***************
*** 1677,1688 ****
          bool        firstone = true;
          int            i;

-         if (ndims <= 0 || ndims > MAXDIM)
-             ereport(ERROR,
-                     (errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
-                      errmsg("number of array dimensions exceeds the maximum allowed, %d",
-                             MAXDIM)));
-
          /* loop through and get data area from each element */
          foreach(element, astate->elements)
          {
--- 1680,1685 ----
***************
*** 1705,1714 ****
--- 1702,1719 ----
              {
                  /* Get sub-array details from first member */
                  elem_ndims = ARR_NDIM(array);
+                 ndims = elem_ndims + 1;
+                 if (ndims <= 0 || ndims > MAXDIM)
+                     ereport(ERROR,
+                             (errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
+                              errmsg("number of array dimensions exceeds " \
+                                     "the maximum allowed, %d", MAXDIM)));
+
                  elem_dims = (int *) palloc(elem_ndims * sizeof(int));
                  memcpy(elem_dims, ARR_DIMS(array), elem_ndims * sizeof(int));
                  elem_lbs = (int *) palloc(elem_ndims * sizeof(int));
                  memcpy(elem_lbs, ARR_LBOUND(array), elem_ndims * sizeof(int));
+
                  firstone = false;
              }
              else
Index: src/backend/nodes/copyfuncs.c
===================================================================
RCS file: /opt/src/cvs/pgsql-server/src/backend/nodes/copyfuncs.c,v
retrieving revision 1.263
diff -c -r1.263 copyfuncs.c
*** src/backend/nodes/copyfuncs.c    8 Aug 2003 21:41:43 -0000    1.263
--- src/backend/nodes/copyfuncs.c    15 Aug 2003 21:44:30 -0000
***************
*** 947,953 ****
      COPY_SCALAR_FIELD(array_typeid);
      COPY_SCALAR_FIELD(element_typeid);
      COPY_NODE_FIELD(elements);
!     COPY_SCALAR_FIELD(ndims);

      return newnode;
  }
--- 947,953 ----
      COPY_SCALAR_FIELD(array_typeid);
      COPY_SCALAR_FIELD(element_typeid);
      COPY_NODE_FIELD(elements);
!     COPY_SCALAR_FIELD(multidims);

      return newnode;
  }
Index: src/backend/nodes/equalfuncs.c
===================================================================
RCS file: /opt/src/cvs/pgsql-server/src/backend/nodes/equalfuncs.c,v
retrieving revision 1.207
diff -c -r1.207 equalfuncs.c
*** src/backend/nodes/equalfuncs.c    8 Aug 2003 21:41:43 -0000    1.207
--- src/backend/nodes/equalfuncs.c    15 Aug 2003 21:44:44 -0000
***************
*** 409,415 ****
      COMPARE_SCALAR_FIELD(array_typeid);
      COMPARE_SCALAR_FIELD(element_typeid);
      COMPARE_NODE_FIELD(elements);
!     COMPARE_SCALAR_FIELD(ndims);

      return true;
  }
--- 409,415 ----
      COMPARE_SCALAR_FIELD(array_typeid);
      COMPARE_SCALAR_FIELD(element_typeid);
      COMPARE_NODE_FIELD(elements);
!     COMPARE_SCALAR_FIELD(multidims);

      return true;
  }
Index: src/backend/nodes/outfuncs.c
===================================================================
RCS file: /opt/src/cvs/pgsql-server/src/backend/nodes/outfuncs.c,v
retrieving revision 1.217
diff -c -r1.217 outfuncs.c
*** src/backend/nodes/outfuncs.c    8 Aug 2003 21:41:44 -0000    1.217
--- src/backend/nodes/outfuncs.c    15 Aug 2003 21:45:34 -0000
***************
*** 785,791 ****
      WRITE_OID_FIELD(array_typeid);
      WRITE_OID_FIELD(element_typeid);
      WRITE_NODE_FIELD(elements);
!     WRITE_INT_FIELD(ndims);
  }

  static void
--- 785,791 ----
      WRITE_OID_FIELD(array_typeid);
      WRITE_OID_FIELD(element_typeid);
      WRITE_NODE_FIELD(elements);
!     WRITE_BOOL_FIELD(multidims);
  }

  static void
Index: src/backend/nodes/readfuncs.c
===================================================================
RCS file: /opt/src/cvs/pgsql-server/src/backend/nodes/readfuncs.c,v
retrieving revision 1.161
diff -c -r1.161 readfuncs.c
*** src/backend/nodes/readfuncs.c    4 Aug 2003 02:39:59 -0000    1.161
--- src/backend/nodes/readfuncs.c    15 Aug 2003 21:46:05 -0000
***************
*** 659,665 ****
      READ_OID_FIELD(array_typeid);
      READ_OID_FIELD(element_typeid);
      READ_NODE_FIELD(elements);
!     READ_INT_FIELD(ndims);

      READ_DONE();
  }
--- 659,665 ----
      READ_OID_FIELD(array_typeid);
      READ_OID_FIELD(element_typeid);
      READ_NODE_FIELD(elements);
!     READ_BOOL_FIELD(multidims);

      READ_DONE();
  }
Index: src/backend/optimizer/util/clauses.c
===================================================================
RCS file: /opt/src/cvs/pgsql-server/src/backend/optimizer/util/clauses.c,v
retrieving revision 1.152
diff -c -r1.152 clauses.c
*** src/backend/optimizer/util/clauses.c    8 Aug 2003 21:41:55 -0000    1.152
--- src/backend/optimizer/util/clauses.c    15 Aug 2003 21:48:03 -0000
***************
*** 1515,1521 ****
          newarray->array_typeid = arrayexpr->array_typeid;
          newarray->element_typeid = arrayexpr->element_typeid;
          newarray->elements = FastListValue(&newelems);
!         newarray->ndims = arrayexpr->ndims;

          if (all_const)
              return (Node *) evaluate_expr((Expr *) newarray,
--- 1515,1521 ----
          newarray->array_typeid = arrayexpr->array_typeid;
          newarray->element_typeid = arrayexpr->element_typeid;
          newarray->elements = FastListValue(&newelems);
!         newarray->multidims = arrayexpr->multidims;

          if (all_const)
              return (Node *) evaluate_expr((Expr *) newarray,
Index: src/backend/parser/parse_expr.c
===================================================================
RCS file: /opt/src/cvs/pgsql-server/src/backend/parser/parse_expr.c,v
retrieving revision 1.160
diff -c -r1.160 parse_expr.c
*** src/backend/parser/parse_expr.c    4 Aug 2003 02:40:01 -0000    1.160
--- src/backend/parser/parse_expr.c    15 Aug 2003 21:38:10 -0000
***************
*** 748,754 ****
                  List       *element;
                  Oid            array_type;
                  Oid            element_type;
-                 int            ndims;

                  /* Transform the element expressions */
                  foreach(element, a->elements)
--- 748,753 ----
***************
*** 781,791 ****
                  if (array_type != InvalidOid)
                  {
                      /* Elements are presumably of scalar type */
!                     ndims = 1;
                  }
                  else
                  {
                      /* Must be nested array expressions */
                      array_type = element_type;
                      element_type = get_element_type(array_type);
                      if (!OidIsValid(element_type))
--- 780,792 ----
                  if (array_type != InvalidOid)
                  {
                      /* Elements are presumably of scalar type */
!                     newa->multidims = false;
                  }
                  else
                  {
                      /* Must be nested array expressions */
+                     newa->multidims = true;
+
                      array_type = element_type;
                      element_type = get_element_type(array_type);
                      if (!OidIsValid(element_type))
***************
*** 793,839 ****
                                  (errcode(ERRCODE_UNDEFINED_OBJECT),
                                   errmsg("could not find array type for datatype %s",
                                          format_type_be(array_type))));
-
-                     /*
-                      * make sure the element expressions all have the same
-                      * number of dimensions
-                      */
-                     ndims = 0;
-                     foreach(element, newcoercedelems)
-                     {
-                         ArrayExpr  *e = (ArrayExpr *) lfirst(element);
-
-                         if (!IsA(e, ArrayExpr))
-                             ereport(ERROR,
-                                     (errcode(ERRCODE_SYNTAX_ERROR),
-                                      errmsg("multidimensional ARRAY[] must be built from nested array
expressions")));
-                         if (ndims == 0)
-                             ndims = e->ndims;
-                         else if (e->ndims != ndims)
-                             ereport(ERROR,
-                                     (errcode(ERRCODE_SYNTAX_ERROR),
-                                      errmsg("nested array expressions must have common number of dimensions")));
-                         if (e->element_typeid != element_type)
-                             ereport(ERROR,
-                                     (errcode(ERRCODE_SYNTAX_ERROR),
-                                      errmsg("nested array expressions must have common element type")));
-
-                     }
-                     /* increment the number of dimensions */
-                     ndims++;
-
-                     /* make sure we don't have too many dimensions now */
-                     if (ndims > MAXDIM)
-                         ereport(ERROR,
-                                 (errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
-                                  errmsg("number of array dimensions exceeds the maximum allowed, %d",
-                                         MAXDIM)));
                  }

                  newa->array_typeid = array_type;
                  newa->element_typeid = element_type;
                  newa->elements = newcoercedelems;
-                 newa->ndims = ndims;

                  result = (Node *) newa;
                  break;
--- 794,804 ----
Index: src/backend/utils/adt/array_userfuncs.c
===================================================================
RCS file: /opt/src/cvs/pgsql-server/src/backend/utils/adt/array_userfuncs.c,v
retrieving revision 1.7
diff -c -r1.7 array_userfuncs.c
*** src/backend/utils/adt/array_userfuncs.c    4 Aug 2003 00:43:25 -0000    1.7
--- src/backend/utils/adt/array_userfuncs.c    15 Aug 2003 22:41:12 -0000
***************
*** 132,138 ****

  /*-----------------------------------------------------------------------------
   * array_cat :
!  *        concatenate two nD arrays to form an (n+1)D array, or
   *        push an (n-1)D array onto the end of an nD array
   *----------------------------------------------------------------------------
   */
--- 132,138 ----

  /*-----------------------------------------------------------------------------
   * array_cat :
!  *        concatenate two nD arrays to form an nD array, or
   *        push an (n-1)D array onto the end of an nD array
   *----------------------------------------------------------------------------
   */
***************
*** 223,251 ****
      if (ndims1 == ndims2)
      {
          /*
!          * resulting array has two element outer array made up of input
!          * argument arrays
           */
          int            i;

!         ndims = ndims1 + 1;
          dims = (int *) palloc(ndims * sizeof(int));
          lbs = (int *) palloc(ndims * sizeof(int));

!         dims[0] = 2;            /* outer array made up of two input arrays */
!         lbs[0] = 1;                /* start lower bound at 1 */

!         for (i = 0; i < ndims1; i++)
          {
              if (dims1[i] != dims2[i] || lbs1[i] != lbs2[i])
                  ereport(ERROR,
                          (errcode(ERRCODE_ARRAY_SUBSCRIPT_ERROR),
                           errmsg("cannot concatenate incompatible arrays"),
!                     errdetail("Arrays with differing dimensions are not "
!                               "compatible for concatenation.")));

!             dims[i + 1] = dims1[i];
!             lbs[i + 1] = lbs1[i];
          }
      }
      else if (ndims1 == ndims2 - 1)
--- 223,251 ----
      if (ndims1 == ndims2)
      {
          /*
!          * resulting array is made up of the elements (possibly arrays themselves)
!          * of the input argument arrays
           */
          int            i;

!         ndims = ndims1;
          dims = (int *) palloc(ndims * sizeof(int));
          lbs = (int *) palloc(ndims * sizeof(int));

!         dims[0] = dims1[0] + dims2[0];
!         lbs[0] = lbs1[0];

!         for (i = 1; i < ndims; i++)
          {
              if (dims1[i] != dims2[i] || lbs1[i] != lbs2[i])
                  ereport(ERROR,
                          (errcode(ERRCODE_ARRAY_SUBSCRIPT_ERROR),
                           errmsg("cannot concatenate incompatible arrays"),
!                     errdetail("Arrays with differing element dimensions are "
!                               "not compatible for concatenation.")));

!             dims[i] = dims1[i];
!             lbs[i] = lbs1[i];
          }
      }
      else if (ndims1 == ndims2 - 1)
***************
*** 264,269 ****
--- 264,272 ----
          /* increment number of elements in outer array */
          dims[0] += 1;

+         /* decrement outer array lower bound */
+         lbs[0] -= 1;
+
          /* make sure the added element matches our existing elements */
          for (i = 0; i < ndims1; i++)
          {
***************
*** 276,284 ****
          }
      }
      else
- /* (ndims1 == ndims2 + 1) */
      {
!         /*
           * resulting array has the first argument as the outer array, with
           * the second argument appended to the end of the outer dimension
           */
--- 279,287 ----
          }
      }
      else
      {
!         /* (ndims1 == ndims2 + 1)
!          *
           * resulting array has the first argument as the outer array, with
           * the second argument appended to the end of the outer dimension
           */
Index: src/include/nodes/primnodes.h
===================================================================
RCS file: /opt/src/cvs/pgsql-server/src/include/nodes/primnodes.h,v
retrieving revision 1.91
diff -c -r1.91 primnodes.h
*** src/include/nodes/primnodes.h    11 Aug 2003 23:04:50 -0000    1.91
--- src/include/nodes/primnodes.h    15 Aug 2003 21:37:35 -0000
***************
*** 596,602 ****
      Oid            array_typeid;    /* type of expression result */
      Oid            element_typeid; /* common type of expression elements */
      List       *elements;        /* the array elements */
!     int            ndims;            /* number of array dimensions */
  } ArrayExpr;

  /*
--- 596,602 ----
      Oid            array_typeid;    /* type of expression result */
      Oid            element_typeid; /* common type of expression elements */
      List       *elements;        /* the array elements */
!     bool        multidims;        /* true if elements are also ArrayExprs */
  } ArrayExpr;

  /*
Index: src/test/regress/expected/arrays.out
===================================================================
RCS file: /opt/src/cvs/pgsql-server/src/test/regress/expected/arrays.out,v
retrieving revision 1.17
diff -c -r1.17 arrays.out
*** src/test/regress/expected/arrays.out    21 Jul 2003 20:29:40 -0000    1.17
--- src/test/regress/expected/arrays.out    15 Aug 2003 23:09:19 -0000
***************
*** 190,199 ****
   {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}}";
--- 190,199 ----
   {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}}";
***************
*** 233,248 ****
   {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}}";
--- 233,248 ----
   {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}}";
***************
*** 251,260 ****
   {{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}";
--- 251,260 ----
   {{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}";
Index: src/test/regress/sql/arrays.sql
===================================================================
RCS file: /opt/src/cvs/pgsql-server/src/test/regress/sql/arrays.sql,v
retrieving revision 1.14
diff -c -r1.14 arrays.sql
*** src/test/regress/sql/arrays.sql    29 Jun 2003 00:33:44 -0000    1.14
--- src/test/regress/sql/arrays.sql    15 Aug 2003 23:02:06 -0000
***************
*** 132,138 ****
  -- functions
  SELECT array_append(array[42], 6) AS "{42,6}";
  SELECT array_prepend(6, array[42]) AS "{6,42}";
! SELECT array_cat(ARRAY[1,2], ARRAY[3,4]) AS "{{1,2},{3,4}}";
  SELECT array_cat(ARRAY[1,2], ARRAY[[3,4],[5,6]]) AS "{{1,2},{3,4},{5,6}}";
  SELECT array_cat(ARRAY[[3,4],[5,6]], ARRAY[1,2]) AS "{{3,4},{5,6},{1,2}}";

--- 132,138 ----
  -- functions
  SELECT array_append(array[42], 6) AS "{42,6}";
  SELECT array_prepend(6, array[42]) AS "{6,42}";
! SELECT array_cat(ARRAY[1,2], ARRAY[3,4]) AS "{1,2,3,4}";
  SELECT array_cat(ARRAY[1,2], ARRAY[[3,4],[5,6]]) AS "{{1,2},{3,4},{5,6}}";
  SELECT array_cat(ARRAY[[3,4],[5,6]], ARRAY[1,2]) AS "{{3,4},{5,6},{1,2}}";

***************
*** 141,150 ****
  SELECT NOT ARRAY[1.1,1.2,1.3] = ARRAY[1.1,1.2,1.3] AS "FALSE";
  SELECT ARRAY[1,2] || 3 AS "{1,2,3}";
  SELECT 0 || ARRAY[1,2] AS "{0,1,2}";
! SELECT ARRAY[1,2] || ARRAY[3,4] AS "{{1,2},{3,4}}";
  SELECT ARRAY[[['hello','world']]] || ARRAY[[['happy','birthday']]] AS "ARRAY";
  SELECT ARRAY[[1,2],[3,4]] || ARRAY[5,6] AS "{{1,2},{3,4},{5,6}}";
! SELECT ARRAY[0,0] || ARRAY[1,1] || ARRAY[2,2] AS "{{0,0},{1,1},{2,2}}";
  SELECT 0 || ARRAY[1,2] || 3 AS "{0,1,2,3}";

  -- array casts
--- 141,150 ----
  SELECT NOT ARRAY[1.1,1.2,1.3] = ARRAY[1.1,1.2,1.3] AS "FALSE";
  SELECT ARRAY[1,2] || 3 AS "{1,2,3}";
  SELECT 0 || ARRAY[1,2] AS "{0,1,2}";
! SELECT ARRAY[1,2] || ARRAY[3,4] AS "{1,2,3,4}";
  SELECT ARRAY[[['hello','world']]] || ARRAY[[['happy','birthday']]] AS "ARRAY";
  SELECT ARRAY[[1,2],[3,4]] || ARRAY[5,6] AS "{{1,2},{3,4},{5,6}}";
! SELECT ARRAY[0,0] || ARRAY[1,1] || ARRAY[2,2] AS "{0,0,1,1,2,2}";
  SELECT 0 || ARRAY[1,2] || 3 AS "{0,1,2,3}";

  -- array casts