Change array_offset to return subscripts, not offsets

... and rename it and its sibling array_offsets to array_position and
array_positions, to account for the changed behavior.

Having the functions return subscripts better matches existing practice,
and is better suited to using the result value as a subscript into the
array directly.  For one-based arrays, the new definition is identical
to what was originally committed.

(We use the term "subscript" in the documentation, which is what we use
whenever we talk about arrays; but the functions themselves are named
using the word "position" to match the standard-defined POSITION()
functions.)

Author: Pavel Stěhule
Behavioral problem noted by Dean Rasheed.

6 files changed, 117 insertions, 98 deletions

diff --git a/src/backend/utils/adt/array_userfuncs.c b/src/backend/utils/adt/array_userfuncs.c
index 57074e0f46e..c0bfd33174c 100644
--- a/src/backend/utils/adt/array_userfuncs.c
+++ b/src/backend/utils/adt/array_userfuncs.c
@@ -19,7 +19,7 @@
 #include "utils/typcache.h"
 
 
-static Datum array_offset_common(FunctionCallInfo fcinfo);
+static Datum array_position_common(FunctionCallInfo fcinfo);
 
 
 /*
@@ -659,7 +659,7 @@ array_agg_array_finalfn(PG_FUNCTION_ARGS)
 }
 
 /*-----------------------------------------------------------------------------
- * array_offset, array_offset_start :
+ * array_position, array_position_start :
  *			return the offset of a value in an array.
  *
  * IS NOT DISTINCT FROM semantics are used for comparisons.  Return NULL when
@@ -667,26 +667,26 @@ array_agg_array_finalfn(PG_FUNCTION_ARGS)
  *-----------------------------------------------------------------------------
  */
 Datum
-array_offset(PG_FUNCTION_ARGS)
+array_position(PG_FUNCTION_ARGS)
 {
-	return array_offset_common(fcinfo);
+	return array_position_common(fcinfo);
 }
 
 Datum
-array_offset_start(PG_FUNCTION_ARGS)
+array_position_start(PG_FUNCTION_ARGS)
 {
-	return array_offset_common(fcinfo);
+	return array_position_common(fcinfo);
 }
 
 /*
- * array_offset_common
- * 		Common code for array_offset and array_offset_start
+ * array_position_common
+ * 		Common code for array_position and array_position_start
  *
  * These are separate wrappers for the sake of opr_sanity regression test.
  * They are not strict so we have to test for null inputs explicitly.
  */
 static Datum
-array_offset_common(FunctionCallInfo fcinfo)
+array_position_common(FunctionCallInfo fcinfo)
 {
 	ArrayType  *array;
 	Oid			collation = PG_GET_COLLATION();
@@ -694,8 +694,8 @@ array_offset_common(FunctionCallInfo fcinfo)
 	Datum		searched_element,
 				value;
 	bool		isnull;
-	int			offset = 0,
-				offset_min;
+	int			position,
+				position_min;
 	bool		found = false;
 	TypeCacheEntry *typentry;
 	ArrayMetaState *my_extra;
@@ -731,18 +731,20 @@ array_offset_common(FunctionCallInfo fcinfo)
 		null_search = false;
 	}
 
+	position = (ARR_LBOUND(array))[0] - 1;
+
 	/* figure out where to start */
 	if (PG_NARGS() == 3)
 	{
 		if (PG_ARGISNULL(2))
 			ereport(ERROR,
 					(errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
-					 errmsg("initial offset should not be NULL")));
+					 errmsg("initial position should not be NULL")));
 
-		offset_min = PG_GETARG_INT32(2);
+		position_min = PG_GETARG_INT32(2);
 	}
 	else
-		offset_min = 1;
+		position_min = (ARR_LBOUND(array))[0];
 
 	/*
 	 * We arrange to look up type info for array_create_iterator only once per
@@ -780,10 +782,10 @@ array_offset_common(FunctionCallInfo fcinfo)
 	array_iterator = array_create_iterator(array, 0, my_extra);
 	while (array_iterate(array_iterator, &value, &isnull))
 	{
-		offset += 1;
+		position++;
 
 		/* skip initial elements if caller requested so */
-		if (offset < offset_min)
+		if (position < position_min)
 			continue;
 
 		/*
@@ -818,12 +820,12 @@ array_offset_common(FunctionCallInfo fcinfo)
 	if (!found)
 		PG_RETURN_NULL();
 
-	PG_RETURN_INT32(offset);
+	PG_RETURN_INT32(position);
 }
 
 /*-----------------------------------------------------------------------------
- * array_offsets :
- *			return an array of offsets of a value in an array.
+ * array_positions :
+ *			return an array of positions of a value in an array.
  *
  * IS NOT DISTINCT FROM semantics are used for comparisons.  Returns NULL when
  * the input array is NULL.  When the value is not found in the array, returns
@@ -833,7 +835,7 @@ array_offset_common(FunctionCallInfo fcinfo)
  *-----------------------------------------------------------------------------
  */
 Datum
-array_offsets(PG_FUNCTION_ARGS)
+array_positions(PG_FUNCTION_ARGS)
 {
 	ArrayType  *array;
 	Oid			collation = PG_GET_COLLATION();
@@ -841,7 +843,7 @@ array_offsets(PG_FUNCTION_ARGS)
 	Datum		searched_element,
 				value;
 	bool		isnull;
-	int			offset = 0;
+	int			position;
 	TypeCacheEntry *typentry;
 	ArrayMetaState *my_extra;
 	bool		null_search;
@@ -854,6 +856,8 @@ array_offsets(PG_FUNCTION_ARGS)
 	array = PG_GETARG_ARRAYTYPE_P(0);
 	element_type = ARR_ELEMTYPE(array);
 
+	position = (ARR_LBOUND(array))[0] - 1;
+
 	/*
 	 * We refuse to search for elements in multi-dimensional arrays, since we
 	 * have no good way to report the element's location in the array.
@@ -912,12 +916,12 @@ array_offsets(PG_FUNCTION_ARGS)
 	}
 
 	/*
-	 * Accumulate each array offset iff the element matches the given element.
+	 * Accumulate each array position iff the element matches the given element.
 	 */
 	array_iterator = array_create_iterator(array, 0, my_extra);
 	while (array_iterate(array_iterator, &value, &isnull))
 	{
-		offset += 1;
+		position += 1;
 
 		/*
 		 * Can't look at the array element's value if it's null; but if we
@@ -927,7 +931,7 @@ array_offsets(PG_FUNCTION_ARGS)
 		{
 			if (isnull && null_search)
 				astate =
-					accumArrayResult(astate, Int32GetDatum(offset), false,
+					accumArrayResult(astate, Int32GetDatum(position), false,
 									 INT4OID, CurrentMemoryContext);
 
 			continue;
@@ -937,7 +941,7 @@ array_offsets(PG_FUNCTION_ARGS)
 		if (DatumGetBool(FunctionCall2Coll(&my_extra->proc, collation,
 										   searched_element, value)))
 			astate =
-				accumArrayResult(astate, Int32GetDatum(offset), false,
+				accumArrayResult(astate, Int32GetDatum(position), false,
 								 INT4OID, CurrentMemoryContext);
 	}
 
diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index 0fde1d6ec6d..b87d080c692 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -53,6 +53,6 @@
  */
 
 /*							yyyymmddN */
-#define CATALOG_VERSION_NO	201503301
+#define CATALOG_VERSION_NO	201503302
 
 #endif
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 3cd7851c4ac..8890ade7edf 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -895,11 +895,11 @@ DATA(insert OID = 515 (  array_larger	   PGNSP PGUID 12 1 0 0 0 f f f f t f i 2
 DESCR("larger of two");
 DATA(insert OID = 516 (  array_smaller	   PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 2277 "2277 2277" _null_ _null_ _null_ _null_ array_smaller _null_ _null_ _null_ ));
 DESCR("smaller of two");
-DATA(insert OID = 3277 (  array_offset		   PGNSP PGUID 12 1 0 0 0 f f f f f f i 2 0 23 "2277 2283" _null_ _null_ _null_ _null_ array_offset _null_ _null_ _null_ ));
+DATA(insert OID = 3277 (  array_position		   PGNSP PGUID 12 1 0 0 0 f f f f f f i 2 0 23 "2277 2283" _null_ _null_ _null_ _null_ array_position _null_ _null_ _null_ ));
 DESCR("returns a offset of value in array");
-DATA(insert OID = 3278 (  array_offset		   PGNSP PGUID 12 1 0 0 0 f f f f f f i 3 0 23 "2277 2283 23" _null_ _null_ _null_ _null_ array_offset_start _null_ _null_ _null_ ));
+DATA(insert OID = 3278 (  array_position		   PGNSP PGUID 12 1 0 0 0 f f f f f f i 3 0 23 "2277 2283 23" _null_ _null_ _null_ _null_ array_position_start _null_ _null_ _null_ ));
 DESCR("returns a offset of value in array with start index");
-DATA(insert OID = 3279 (  array_offsets		   PGNSP PGUID 12 1 0 0 0 f f f f f f i 2 0 1007 "2277 2283" _null_ _null_ _null_ _null_ array_offsets _null_ _null_ _null_ ));
+DATA(insert OID = 3279 (  array_positions		   PGNSP PGUID 12 1 0 0 0 f f f f f f i 2 0 1007 "2277 2283" _null_ _null_ _null_ _null_ array_positions _null_ _null_ _null_ ));
 DESCR("returns a array of offsets of some value in array");
 DATA(insert OID = 1191 (  generate_subscripts PGNSP PGUID 12 1 1000 0 0 f f f f t t i 3 0 23 "2277 23 16" _null_ _null_ _null_ _null_ generate_subscripts _null_ _null_ _null_ ));
 DESCR("array subscripts generator");
diff --git a/src/include/utils/array.h b/src/include/utils/array.h
index b78b42abddd..0a488e7b0c1 100644
--- a/src/include/utils/array.h
+++ b/src/include/utils/array.h
@@ -358,9 +358,9 @@ extern Datum array_agg_finalfn(PG_FUNCTION_ARGS);
 extern Datum array_agg_array_transfn(PG_FUNCTION_ARGS);
 extern Datum array_agg_array_finalfn(PG_FUNCTION_ARGS);
 
-extern Datum array_offset(PG_FUNCTION_ARGS);
-extern Datum array_offset_start(PG_FUNCTION_ARGS);
-extern Datum array_offsets(PG_FUNCTION_ARGS);
+extern Datum array_position(PG_FUNCTION_ARGS);
+extern Datum array_position_start(PG_FUNCTION_ARGS);
+extern Datum array_positions(PG_FUNCTION_ARGS);
 
 /*
  * prototypes for functions defined in array_typanalyze.c
diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out
index 14d6d329906..5f1532f2371 100644
--- a/src/test/regress/expected/arrays.out
+++ b/src/test/regress/expected/arrays.out
@@ -366,83 +366,83 @@ SELECT array_cat(ARRAY[[3,4],[5,6]], ARRAY[1,2]) AS "{{3,4},{5,6},{1,2}}";
  {{3,4},{5,6},{1,2}}
 (1 row)
 
-SELECT array_offset(ARRAY[1,2,3,4,5], 4);
- array_offset 
---------------
-            4
+SELECT array_position(ARRAY[1,2,3,4,5], 4);
+ array_position 
+----------------
+              4
 (1 row)
 
-SELECT array_offset(ARRAY[5,3,4,2,1], 4);
- array_offset 
---------------
-            3
+SELECT array_position(ARRAY[5,3,4,2,1], 4);
+ array_position 
+----------------
+              3
 (1 row)
 
-SELECT array_offset(ARRAY[[1,2],[3,4]], 3);
+SELECT array_position(ARRAY[[1,2],[3,4]], 3);
 ERROR:  searching for elements in multidimensional arrays is not supported
-SELECT array_offset(ARRAY['sun','mon','tue','wed','thu','fri','sat'], 'mon');
- array_offset 
---------------
-            2
+SELECT array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat'], 'mon');
+ array_position 
+----------------
+              2
 (1 row)
 
-SELECT array_offset(ARRAY['sun','mon','tue','wed','thu','fri','sat'], 'sat');
- array_offset 
---------------
-            7
+SELECT array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat'], 'sat');
+ array_position 
+----------------
+              7
 (1 row)
 
-SELECT array_offset(ARRAY['sun','mon','tue','wed','thu','fri','sat'], NULL);
- array_offset 
---------------
-             
+SELECT array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat'], NULL);
+ array_position 
+----------------
+               
 (1 row)
 
-SELECT array_offset(ARRAY['sun','mon','tue','wed','thu',NULL,'fri','sat'], NULL);
- array_offset 
---------------
-            6
+SELECT array_position(ARRAY['sun','mon','tue','wed','thu',NULL,'fri','sat'], NULL);
+ array_position 
+----------------
+              6
 (1 row)
 
-SELECT array_offset(ARRAY['sun','mon','tue','wed','thu',NULL,'fri','sat'], 'sat');
- array_offset 
---------------
-            8
+SELECT array_position(ARRAY['sun','mon','tue','wed','thu',NULL,'fri','sat'], 'sat');
+ array_position 
+----------------
+              8
 (1 row)
 
-SELECT array_offsets(NULL, 10);
- array_offsets 
----------------
+SELECT array_positions(NULL, 10);
+ array_positions 
+-----------------
  
 (1 row)
 
-SELECT array_offsets(NULL, NULL::int);
- array_offsets 
----------------
+SELECT array_positions(NULL, NULL::int);
+ array_positions 
+-----------------
  
 (1 row)
 
-SELECT array_offsets(ARRAY[1,2,3,4,5,6,1,2,3,4,5,6], 4);
- array_offsets 
----------------
+SELECT array_positions(ARRAY[1,2,3,4,5,6,1,2,3,4,5,6], 4);
+ array_positions 
+-----------------
  {4,10}
 (1 row)
 
-SELECT array_offsets(ARRAY[[1,2],[3,4]], 4);
+SELECT array_positions(ARRAY[[1,2],[3,4]], 4);
 ERROR:  searching for elements in multidimensional arrays is not supported
-SELECT array_offsets(ARRAY[1,2,3,4,5,6,1,2,3,4,5,6], NULL);
- array_offsets 
----------------
+SELECT array_positions(ARRAY[1,2,3,4,5,6,1,2,3,4,5,6], NULL);
+ array_positions 
+-----------------
  {}
 (1 row)
 
-SELECT array_offsets(ARRAY[1,2,3,NULL,5,6,1,2,3,NULL,5,6], NULL);
- array_offsets 
----------------
+SELECT array_positions(ARRAY[1,2,3,NULL,5,6,1,2,3,NULL,5,6], NULL);
+ array_positions 
+-----------------
  {4,10}
 (1 row)
 
-SELECT array_length(array_offsets(ARRAY(SELECT 'AAAAAAAAAAAAAAAAAAAAAAAAA'::text || i % 10
+SELECT array_length(array_positions(ARRAY(SELECT 'AAAAAAAAAAAAAAAAAAAAAAAAA'::text || i % 10
                                           FROM generate_series(1,100) g(i)),
                                   'AAAAAAAAAAAAAAAAAAAAAAAAA5'), 1);
  array_length 
@@ -455,17 +455,29 @@ DECLARE
   o int;
   a int[] := ARRAY[1,2,3,2,3,1,2];
 BEGIN
-  o := array_offset(a, 2);
+  o := array_position(a, 2);
   WHILE o IS NOT NULL
   LOOP
     RAISE NOTICE '%', o;
-    o := array_offset(a, 2, o + 1);
+    o := array_position(a, 2, o + 1);
   END LOOP;
 END
 $$ LANGUAGE plpgsql;
 NOTICE:  2
 NOTICE:  4
 NOTICE:  7
+SELECT array_position('[2:4]={1,2,3}'::int[], 1);
+ array_position 
+----------------
+              2
+(1 row)
+
+SELECT array_positions('[2:4]={1,2,3}'::int[], 1);
+ array_positions 
+-----------------
+ {2}
+(1 row)
+
 -- operators
 SELECT a FROM arrtest WHERE b = ARRAY[[[113,142],[1,147]]];
        a       
diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql
index 40950a2c20b..562134b2863 100644
--- a/src/test/regress/sql/arrays.sql
+++ b/src/test/regress/sql/arrays.sql
@@ -185,22 +185,22 @@ 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}}";
 
-SELECT array_offset(ARRAY[1,2,3,4,5], 4);
-SELECT array_offset(ARRAY[5,3,4,2,1], 4);
-SELECT array_offset(ARRAY[[1,2],[3,4]], 3);
-SELECT array_offset(ARRAY['sun','mon','tue','wed','thu','fri','sat'], 'mon');
-SELECT array_offset(ARRAY['sun','mon','tue','wed','thu','fri','sat'], 'sat');
-SELECT array_offset(ARRAY['sun','mon','tue','wed','thu','fri','sat'], NULL);
-SELECT array_offset(ARRAY['sun','mon','tue','wed','thu',NULL,'fri','sat'], NULL);
-SELECT array_offset(ARRAY['sun','mon','tue','wed','thu',NULL,'fri','sat'], 'sat');
-
-SELECT array_offsets(NULL, 10);
-SELECT array_offsets(NULL, NULL::int);
-SELECT array_offsets(ARRAY[1,2,3,4,5,6,1,2,3,4,5,6], 4);
-SELECT array_offsets(ARRAY[[1,2],[3,4]], 4);
-SELECT array_offsets(ARRAY[1,2,3,4,5,6,1,2,3,4,5,6], NULL);
-SELECT array_offsets(ARRAY[1,2,3,NULL,5,6,1,2,3,NULL,5,6], NULL);
-SELECT array_length(array_offsets(ARRAY(SELECT 'AAAAAAAAAAAAAAAAAAAAAAAAA'::text || i % 10
+SELECT array_position(ARRAY[1,2,3,4,5], 4);
+SELECT array_position(ARRAY[5,3,4,2,1], 4);
+SELECT array_position(ARRAY[[1,2],[3,4]], 3);
+SELECT array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat'], 'mon');
+SELECT array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat'], 'sat');
+SELECT array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat'], NULL);
+SELECT array_position(ARRAY['sun','mon','tue','wed','thu',NULL,'fri','sat'], NULL);
+SELECT array_position(ARRAY['sun','mon','tue','wed','thu',NULL,'fri','sat'], 'sat');
+
+SELECT array_positions(NULL, 10);
+SELECT array_positions(NULL, NULL::int);
+SELECT array_positions(ARRAY[1,2,3,4,5,6,1,2,3,4,5,6], 4);
+SELECT array_positions(ARRAY[[1,2],[3,4]], 4);
+SELECT array_positions(ARRAY[1,2,3,4,5,6,1,2,3,4,5,6], NULL);
+SELECT array_positions(ARRAY[1,2,3,NULL,5,6,1,2,3,NULL,5,6], NULL);
+SELECT array_length(array_positions(ARRAY(SELECT 'AAAAAAAAAAAAAAAAAAAAAAAAA'::text || i % 10
                                           FROM generate_series(1,100) g(i)),
                                   'AAAAAAAAAAAAAAAAAAAAAAAAA5'), 1);
 
@@ -209,15 +209,18 @@ DECLARE
   o int;
   a int[] := ARRAY[1,2,3,2,3,1,2];
 BEGIN
-  o := array_offset(a, 2);
+  o := array_position(a, 2);
   WHILE o IS NOT NULL
   LOOP
     RAISE NOTICE '%', o;
-    o := array_offset(a, 2, o + 1);
+    o := array_position(a, 2, o + 1);
   END LOOP;
 END
 $$ LANGUAGE plpgsql;
 
+SELECT array_position('[2:4]={1,2,3}'::int[], 1);
+SELECT array_positions('[2:4]={1,2,3}'::int[], 1);
+
 -- operators
 SELECT a FROM arrtest WHERE b = ARRAY[[[113,142],[1,147]]];
 SELECT NOT ARRAY[1.1,1.2,1.3] = ARRAY[1.1,1.2,1.3] AS "FALSE";