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.
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 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, 2024, Oracle Corporation and/or its affiliates. All rights reserved.