WL#2085: Add a CARDINALITY(ARRAY) function

Affects: Server-7.1   —   Status: Un-Assigned

MySQL will implement a CARDINALITY function to operate
on ARRAY data values, in compliance with Standard SQL
(SQL:2003) array functionality.

Rationale 
--------- 
-- Needed functionality: 
The ability to determine the cardinality of an array
may be 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 determine the 
cardinality of an array.
Syntax
------
Add a new function:
CARDINALITY()

--  can resolve to any one of: 
   -- the name of a column defined with the ARRAY data type; 
   -- the keyword ARRAY, followed by square brackets enclosing
an optional list of array elements (see WL#2083). 

Function
--------
The CARDINALITY function returns the number of elements in an
array, as an integer.

Rules 
----- 
-- CARDINALITY is a function that operates on an ARRAY value 
and returns the number of elements in the array, as an INTEGER
value.

-- The function accepts one argument: an array expression.
   -- The array expression is either the keyword ARRAY (followed 
by square brackets enclosing an optional list of array elements, 
e.g. ARRAY[10,20,30]) or the name of a column defined with the 
ARRAY data type.
   -- If the argument is NULL, the function returns NULL.

Other statements 
---------------- 
-- The ARRAY data type must first be implemented. See WL#2081. 
-- The ARRAY element reference function must first be implemented. 
See WL#2082. 
-- The ARRAY value constructor function must first be implemented. 
See WL#2083.

An example
----------
CARDINALITY(ARRAY[10,20,30,40]) returns 4

Create a table with the new data type: 
CREATE TABLE ArrayTable (
   num_column INT,
   array_column INT ARRAY[5]); 

Insert data: 
INSERT INTO ArrayTable VALUES (
   1, ARRAY[10,20,30]);

INSERT INTO ArrayTable VALUES (
   2, ARRAY[40,50,60,70,80]);

Retrieve data: 
SELECT CARDINALITY(array_column) 
   FROM ArrayTable
   WHERE num_column = 1;
-- Returns 3

SELECT CARDINALITY(array_column) 
   FROM ArrayTable
   WHERE num_column = 2;
-- Returns 5

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

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