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".
Copyright (c) 2000, 2025, Oracle Corporation and/or its affiliates. All rights reserved.