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

WL#2081: Add ARRAY data type

WL#2082: Add ARRAY element reference function

WL#2083: Add ARRAY value constructor function

WL#2082: Add ARRAY element reference function

WL#2083: Add ARRAY value constructor function

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

Copyright (c) 2000, 2016, Oracle Corporation and/or its affiliates. All rights reserved.