WL#2083: Add ARRAY value constructor function

Affects: Server-7.1   —   Status: Un-Assigned   —   Priority: Medium

MySQL will implement the ability to assign values to
array elements, in compliance with Standard SQL 
(SQL:2003) array functionality. This is known as the 
array value constructor function.

Rationale
---------
-- Needed functionality:
The ability to assign data values to an array is
needed when doing any of the following on an array:
INSERT, UPDATE, DELETE, SELECT.

-- Compatibility:
ARRAY functionality is required by standard SQL.
MySQL 4.1 currently has no ability to support array
value constructors.
Syntax
------
Add the ability to assign values to the elements of
an array:
ARRAY[<element_value> [{,<element_value>} ...]]

-- <element_value> may be any expression that resolves
to a scalar value with a data type that can be assigned 
to the array's defined data type.

-- The number of element values may range from 0 (zero)
up to the array's maximum cardinality.
   -- ARRAY[] specifies an array with zero elements.
   -- Note that the square brackets are a mandatory part
of the syntax. 

Function
-------- 
The array value constructor constructs an array, thus the
returned value is an array with a cardinality that matches 
the number of elements in the element list. For arrays, an
array value constructor is the equivalent of a literal for
the other built-in data types.

Rules
-----
-- An array value constructor is a function that operates
on a set of values and returns an array with the same
cardinality as the number of values in the set. For example,
ARRAY[10,20,30,40,50] constructs an array with five elements.
The array would be valid for this column definition:
array_column SMALLINT ARRAY[5]

-- The function accepts one argument: a comma-delimited
list of values, all of which must be of a compatible data 
type.
   -- The list of values must be enclosed in square
brackets. 
   -- Each value in the list must resolve to a scalar
value.
   -- The brackets may be empty, to indicate an empty array.

-- The function assigns data values to the elements of an 
array. Thus, the result of the function is an array whose 
nth element contains the value of the nth value in the 
<element_value> list.

-- An array has a maximum cardinality and an actual cardinality
(see WL#2081).
   -- It is an error if one attempts to assign a value to an 
array element whose position is greater than the maximum 
cardinality of the array. Thus, for a column defined as 
INT ARRAY[3], it is an error to construct this array:
ARRAY[10,20,30,40]
   -- It is not an error if one attempts to assign values to 
only some of an array's elements. Thus, for a column defined 
as INT ARRAY[5], it is not an error to construct this array:
ARRAY[10,20,30]

-- The data type of the source and target array elements must
be compatible. That is, it is an error if one attempts to 
assign a numeric value to an array defined with a string or 
temporal data type.

-- If the maximum cardinality of the target array is equal 
to the cardinality of the source array, then the target's 
elements are set to the values of the corresponding elements 
from the source array using a one-to-one correspondence.

-- If the maximum cardinality of the target array is smaller 
than the cardinality of the source array, then (assume the 
target has a maximum cardinality of three and that the source
has a cardinality of four):
   -- (a) If the rightmost additional source elements (one,
in this example) are NULL, the target's elements are set to 
the values of the common corresponding elements from the 
source array (three, in this example). For example, assume a 
column is defined as INT ARRAY[3] and you wish to assign this 
array:
ARRAY[10,20,30,NULL]
The result is an array with a cardinality of three; the first
element of the array has a value of 10, the second element has
a value of 20, and the final element has a value of 30.
   -- (b) If one or more of the rightmost additional source
elements are not NULL, the result is an error: SQLSTATE 2202F
"data exception-array data, right truncation". For example, 
assume a column is defined as INT ARRAY[3] and you wish to 
assign this array:
ARRAY[10,20,30,NULL,50]
The result is an error.

-- If the maximum cardinality of the target array is greater 
than the cardinality of the source array, then the target's
elements are set to the values of the common corresponding 
elements from the source, and the cardinality of the target 
is set to that number. For example, assume the target has a 
maximum cardinality of five, and that the source has a 
cardinality of three. In this case, the first three elements 
of the target are set to the values of the source's three 
elements and the target's cardinality is set to three. The 
target's maximum cardinality is not affected by this.
   -- That is, assume a column is defined as INT ARRAY[5] 
and you wish to assign this array:
ARRAY[10,20,30]
The result is an array with a cardinality of three; the first
element of the array has a value of 10, the second element 
has a value of 20, and the final element has a value of 30.

Other statements 
---------------- 
-- The ARRAY data type must first be implemented. See WL#2081.

-- Note: In standard SQL, it is also possible to assign values
to an array with:
ARRAY(<query expression> [ORDER BY])
This syntax is new with SQL:2003 and is not supported in any
form by other DBMSs at this time. I think it best that we first 
implement the array value constructor in its simpler form, then 
create a new worklog entry for additional functionality
when we deem it necessary.

An example
----------
Create a table with the new data type: 
CREATE TABLE ArrayTable (array_column INT ARRAY[3]); 

Insert data:
INSERT INTO ArrayTable (array_column) 
   VALUES (ARRAY[10,20,30]);
-- Inserts one row into the table, with a valid value for 
each possible array element. The cardinality of the row is 
the same as the array's maximum cardinality (that is, three).

INSERT INTO ArrayTable (array_column) 
   VALUES (ARRAY[40,50]);
-- Inserts one row into the table, with a valid value for 
only the first two array elements. The cardinality of the 
row is two, but the array's maximum cardinality is still 
three.

Change data:
UPDATE ArrayTable SET array_column = ARRAY[40,50,60]
   WHERE array_column[1] = 40;
-- Changes the second row inserted, increasing the cardinality
of the array to three.

UPDATE ArrayTable SET array_column = ARRAY[30,40,50];
-- Changes both rows of the table, setting all three array 
elements to the stated values in each row.

UPDATE ArrayTable SET array_column = ARRAY[50,60];
-- Changes both rows of the table, setting the first two
array elements to the stated values (and changing the
actual cardinality to two) in each row. The third element
is no longer valid.

Reserved words 
-------------- 
ARRAY, eventually CARDINALITY 

Errors 
------ 

At INSERT, UPDATE, DELETE, and SELECT time, the server 
should reject invalid ARRAY values with SQLSTATE 22000 
"data exception-no subclass". 

If the maximum cardinality of a target array is less
than the cardinality of a source array and at least one
of the extra source elements is not NULL, the server
should reject the statement with SQLSTATE 2202F
"data exception-array data, right truncation".