WL#2082: Add ARRAY element reference function

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

MySQL will implement the ability to access array 
elements, in compliance with Standard SQL (SQL:2003)
array functionality. This is known as the array
element reference function.

Rationale
---------
-- Needed functionality:
The ability to refer to a specific array element is
needed when doing any of the following on an array:
UPDATE, DELETE, SELECT.

-- Compatibility:
ARRAY functionality is required by standard SQL.
MySQL 4.1 currently has no ability to support array
element references.
Syntax
------
Add the ability to access a specific element of an array:
<array_expression>[<integer_expression>]

-- <array_expression> can resolve to any one of:
   -- the name of a column defined with the ARRAY data type;
   -- the keyword ARRAY.

-- [<integer_expression>] must resolve to an integer.
   -- Note that the square brackets are a mandatory part of
the syntax.
   -- The specified value refers to the ordinal position of
an element in the array. For example, array_column[5] refers
to the fifth element in the array.
    -- It is an error if [<integer_expression>] resolves to a
negative number, or if [<integer_expression>] resolves to a
number greater than the cardinality of the array (see WL#2081). 
In each case, the DBMS should return SQLSTATE 2202E
"data exception-array element error".

-- If either argument is NULL, then the result is NULL.

Function
--------
The array element reference function returns an element
of an array. The data type of the returned value is the
same as the defined data type for the array.

Rules
-----
-- An array element reference is a function that operates
on an ARRAY value and returns the value of a specific
element of that array. 
   -- The data type of the returned value is the same as
the defined data type of the array, thus it varies.

-- The function accepts two arguments: an array expression
and an integer expression.
   -- The array expression is either the keyword ARRAY or
the name of a column defined with the ARRAY data type.
   -- The integer expression must resolve to an integer
value. It must be enclosed in square brackets and refers to
the ordinal position of the array element which is the target
of the reference (that is, [<integer_expression>] tells the
DBMS which array element value should be returned). For 
example, array_column[5] refers to the fifth element in the
array.

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

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

Update data:
UPDATE ArrayTable SET array_column = ARRAY[30,40,50];
-- Changes the entire row inserted.

UPDATE ArrayTable SET array_column[2] = 50;
-- Changes the second element of the array from 40 to 50.

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

SELECT array_column from ArrayTable 
   WHERE array_column[2] <> 40;
-- Returns the entire row where the array's second element
is not equal to 40.

SELECT array_column[2] from ArrayTable; 
-- Returns the value of the second element of the array,
for all rows of the table.

Delete data:
DELETE FROM ArrayTable WHERE array_column[2] = 50;
-- Removes all rows where the second element of the array
is equal to 50.

DELETE FROM ArrayTable WHERE array_column <> ARRAY[10,20,30];
-- Removes all rows where the entire array does not equal
the array specified.

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

Errors
------

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

If [<integer_expression>] resolves to a negative number,
the server should reject the statement with SQLSTATE
2202E "data exception-array element error".

If [<integer_expression>] resolves to a number greater 
than the cardinality of the array, the server should reject
the statement with SQLSTATE 2202E "data exception-array 
element error".