WL#2081: Add ARRAY data type

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

MySQL will implement a data type, ARRAY, to store
variable-sized arrays, in compliance with Standard
SQL (SQL:2003) array functionality.

-- Needed functionality:
ARRAY functionality is required by standard SQL.
MySQL 4.1 currently has no ability to support arrays.

-- Compatibility:
Other DBMSs (e.g. Oracle10g) do provide array support.
Add a new column data type:
<data type> ARRAY [[<maximum_elements]]

-- The ARRAY data type specification may be used anywhere
in an SQL statement where a data type specification is
legal, e.g. to define a column in CREATE TABLE, to define
a variable, parameter, or function return in a stored
procedure, and so on.

-- The <data type> may be any data type supported (except
for ARRAY itself, and REF, which MySQL does not support).
It defines the type of data which the array will contain.

-- The [<maximum_elements>] must be an unsigned integer
greater than zero. It defines the maximum cardinality of
the array, rather than its exact size. Note that the inner
set of brackets are mandatory when defining an array with
a specific size, e.g. INT ARRAY is correct for defining an
array with the default size, INT ARRAY[5] is correct syntax
for defining an array that will contain 5 elements.
   -- As shown in the syntax diagram, [<maximum_elements>]
is optional. If omitted, the maximum cardinality of the
array defaults to an implementation-defined default value.
Oracle's VARRAY size is limited to the maximum number of
columns allowed in a table, so I suggest we make our default
match that maximum. Thus, if [<maximum_elements>] is omitted,
the maximum cardinality of the array defaults to 1000, which
should also be the absolute maximum cardinality. Thus:
    -- [<maximum_elements>] defaults to 1000.
    -- [<maximum_elements>] may range from 1 to 1000.

An array is an ordered collection of elements, possibly
containing data values. The ARRAY data type will be used
to store data arrays in database tables.

-- An array is an ordered set of elements.

-- The maximum number of elements in the array is known as
the array's maximum cardinality. The maximum cardinality is
defined at the time the array is defined, as is the element
data type.

-- The actual number of elements that contain data values is
known as the array's cardinality. The cardinality of an array
may vary and is not defined at the time the array is defined.
That is, an instance of an array may always contain fewer
elements than the maximum cardinality allows.

-- Each element may contain a data value that corresponds
to the array's defined data type.
   -- Each element has three states: blank (no value assigned
to the element), NULL (NULL assigned to the element), and
containing valid value (data value assigned to the element). 

-- Each element is associated with exactly one ordinal position
in the array. The first array element is found at position 1 (one),
the next at position 2 (two), and so on. Thus, assuming n is the
cardinality of an array, the ordinal position of any array element
is an integer in the range 1 (one) <= element <= n.

-- An array has a maximum cardinality and an actual cardinality.
   -- 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.
   -- It is not an error if one attempts to assign values to only
some of an array's elements.

-- Privileges:
   -- No special privileges are required to create a table
with the ARRAY data type, or to utilize ARRAY data.

-- Comparison:
   -- See WL#2084 Add ability to compare ARRAY data.

-- Assignment:
   -- See WL#2082 Add ARRAY element reference function and
WL#2083 Add ARRAY value constructor function.

Other statements
-- Two other syntax elements must be implemented in order for
the ARRAY data type to be useful. See WL#2082 for 
Array Element Reference syntax and WL#2083 for Array Constructor
-- Also related:
   -- CARDINALITY(<array_expression>). See WL#2085.
   -- Array concatenation. See WL#.

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]);

Retrieve data: 
SELECT array_column from ArrayTable 
   WHERE array_column <> ARRAY[];
-- Returns all cases where array_column is not an empty array

Reserved words