WL#2084: Add ability to compare ARRAY data

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

MySQL will implement the ability to compare arrays, 
in compliance with Standard SQL (SQL:2003) array 
functionality.

Rationale
---------
-- Needed functionality:
The ability to compare arrays is needed when doing 
any of the following on an array:
INSERT, UPDATE, DELETE, SELECT (see also Related 
Operations, below).

-- Compatibility:
ARRAY functionality is required by standard SQL.
MySQL 4.1 currently has no ability to support array
comparison.
Syntax
------
Add the ability to compare arrays with the equal to (=)
and not equal to (<>) operators:
<array_expression> = <array_expression>
or
<array_expression> <> <array_expression>

-- <array_expression> 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
--------
One can check for array equality with the equal to (=)
operator. One can check for arrays that are not equal
with the not equal to (<>) operator. No other array
comparisons are needed.

Rules
-----
-- Only two comparisons are supported: = and <>.

-- The data type of each comparand must be comparable.
Thus ARRAY[10,20,30] = ARRAY[20,30,40] is a valid comparison,
but ARRAY[10,20,30] = ARRAY['this','string'] is an invalid
comparison.

-- Array elements are compared pairwise in element order, 
that is, the first element of the first comparand is
compared to the first element of the second comparand,
then the second elements are compared, and so on.

-- Two arrays are not equal if they do not have the same
cardinality. Thus ARRAY[10,20,30] = ARRAY[10,20] is FALSE.

-- Two arrays are not equal if they have the same cardinality
and at least one array element in the first array is distinct
from the same element in the second array. Thus 
ARRAY[10,20,30] = ARRAY[10,30,20] is FALSE.

-- Two arrays are equal if they have the same cardinality and 
every element in the first array is equal to its corresponding
element in the second array. Thus 
ARRAY[10,20,30] = ARRAY[10,20,30] is TRUE.

-- Two arrays are equal if both have a cardinality of 0 (zero).
Thus ARRAY[] = ARRRAY[] is TRUE.

-- If any array element is NULL, the result of the comparison
is UNKNOWN. Thus ARRAY[10,20,30] = ARRAY[10,NULL] is UNKNOWN.

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.

Related operations
------------------
-- Set Functions
COUNT and GROUPING can be used to operate on arrays.

-- Predicates
IS [NOT] NULL, [NOT] EXISTS (optionally with ALL/SOME/ANY), 
and IS DISTINCT FROM (not supported by MySQL) can be used to 
operate on arrays.

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".