ISO/IEC 9075-15:2019

INTERNATIONAL ISO/IEC
STANDARD 9075-15
First edition
2019-06
Information technology database
languages — SQL —
Part 15:
Multi-dimensional arrays (SQL/MDA)
Langages de base de données IT — SQL —
Partie 15: Tableaux multi-dimensionnels (SQL/MDA)
Reference number
©
ISO/IEC 2019
© ISO/IEC 2019
All rights reserved. Unless otherwise specified, or required in the context of its implementation, no part of this publication may
be reproduced or utilized otherwise in any form or by any means, electronic or mechanical, including photocopying, or posting
on the internet or an intranet, without prior written permission. Permission can be requested from either ISO at the address
below or ISO’s member body in the country of the requester.
ISO copyright office
CP 401 • Ch. de Blandonnet 8
CH-1214 Vernier, Geneva
Phone: +41 22 749 01 11
Fax: +41 22 749 09 47
Email: copyright@iso.org
Website: www.iso.org
Published in Switzerland
ii © ISO/IEC 2019 – All rights reserved

Contents Page
Foreword. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ix
Introduction. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . x
1 Scope. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
2 Normative references. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3
3 Terms and definitions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
3.1 Definitions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
3.1.1 Definitions provided in this document. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
4 Concepts. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
4.1 SQL data types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
4.1.1 SQL data types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
4.1.1.1 Collection types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .7
4.2 SQL-schema objects. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
4.2.1 User-defined types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .7
4.2.1.1 Distinct types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .7
5 The parts of ISO/IEC 9075. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
5.1 Overview. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
5.2 ISO/IEC 9075-15: MultiDimensional Arrays (SQL/MDA). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
6 Concepts. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
6.1 Data types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
6.1.1 General introduction to data types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
6.1.2 Data type terminology. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
6.2 Numbers. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
6.2.1 Operations involving numbers. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
6.3 User-defined types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .12
6.3.1 Distinct types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .12
6.4 Collection types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .12
6.4.1 Introduction to collection types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
6.4.2 MD-arrays. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
6.4.3 Collection comparison and assignment. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
6.4.4 Operations involving MD-arrays. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
6.4.4.1 Operators that operate on MD-array values and return MD-array values. . . . . . . . . . . . . . . . . . . . . . . . . . . 14
6.4.4.2 Operators that operate on MD-array values and return tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
6.4.4.3 Operators that operate on MD-array values and return numbers. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
6.4.4.4 Operators that operate on MD-array values and return character strings. . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
6.4.4.5 Operators that operate on MD-array values and return numbers or Boolean values. . . . . . . . . . . . . . . . . . . 17
6.4.4.6 Operators that operate on MD-array values and return character or binary strings. . . . . . . . . . . . . . . . . . . . 17
6.4.4.7 Operators that construct new MD-array values. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
6.4.4.8 Operators that operate on MD-array values and return MD-array elements. . . . . . . . . . . . . . . . . . . . . . . . . 18
©ISO/IEC 2019 – All rights reserved Contents iii

6.4.5 MD-axis variables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
7 Lexical elements. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .19
7.1 and . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
7.2 Names and identifiers. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
8 Scalar expressions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
8.1 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
8.2 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
8.3 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .26
8.4 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .29
8.5 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
8.6 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
8.7 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .35
8.8 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
8.9 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
8.10 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43
8.11 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
8.12 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46
8.13 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52
8.14 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60
8.15 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66
9 Query expressions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69
9.1 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .69
9.2 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73
10 Predicates. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75
10.1 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75
11 Additional common rules. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77
11.1 Retrieval assignment. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77
11.2 Store assignment. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79
11.3 Passing a value from a host language to the SQL-server. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80
11.4 Passing a value from the SQL-server to a host language. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81
11.5 Result of data type combinations. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82
11.6 Type precedence list determination. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83
11.7 Type name determination. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84
11.8 Determination of identical values. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85
11.9 Equality operations. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86
11.10 Grouping operations. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87
11.11 Multiset element grouping operations. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88
11.12 Ordering operations. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .89
11.13 Data type identity. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90
11.14 Indexed name. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .91
11.15 MD-array subset. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92
11.16 Canonicalize MD-array element reference. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96
11.17 Execution of MD-array-returning functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 98
12 Additional common elements. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .101
12.1 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101
iv  Multidimensional Arrays (SQL/MDA) ©ISO/IEC 2019 – All rights reserved

12.2 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .103
12.3 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105
13 Schema definition and manipulation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107
13.1 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107
13.2 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .108
13.3 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109
13.4 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .110
14 SQL-client modules. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .111
14.1 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111
14.2 Data type correspondences. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113
15 Data manipulation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .115
15.1 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115
16 Dynamic SQL. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .119
16.1 Description of SQL descriptor areas. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119
16.2 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 121
16.3 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .122
17 Embedded SQL. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 123
17.1 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 123
17.2 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 125
17.3 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 126
17.4 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 127
17.5 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 128
17.6 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129
18 Call-Level Interface specifications. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131
18.1 SQL/CLI data type correspondences. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131
19 Information Schema. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .133
19.1 ELEMENT_TYPES view. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 133
19.2 MD_EXTENTS view. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .134
20 Definition Schema. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .135
20.1 DATA_TYPE_DESCRIPTOR base table. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 135
20.2 ELEMENT_TYPES base table. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 136
20.3 MD_EXTENTS base table. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 137
20.4 SQL_CONFORMANCE base table. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139
21 Status codes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .141
21.1 SQLSTATE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 141
22 Conformance. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 143
22.1 Claims of conformance to SQL/MDA. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 143
22.2 Implied feature relationships of SQL/MDA. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 143
Annex A (informative) SQL Conformance Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 145
Annex B (informative) Implementation-defined elements. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .151
Annex C (informative) Implementation-dependent elements. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .153
Annex D (informative) Incompatibilities with ISO/IEC 9075:2011. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 155
Annex E (informative) SQL feature taxonomy. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 157
©ISO/IEC 2019 – All rights reserved Contents v

Index. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 159
vi  Multidimensional Arrays (SQL/MDA) ©ISO/IEC 2019 – All rights reserved

Tables
Table Page
1 Table aggregation operators. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
2 Data type correspondences for Ada. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113
3 Data type correspondences for C. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113
4 Data type correspondences for COBOL. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113
5 Data type correspondences for Fortran. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113
6 Data type correspondences for M. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 114
7 Data type correspondences for Pascal. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 114
8 Data type correspondences for PL/I. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 114
9 Data types of s used in SQL item descriptor areas. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119
10 Codes used for SQL data types in Dynamic SQL. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 120
11 SQL/CLI data type correspondences for Ada. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131
12 SQL/CLI data type correspondences for C. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131
13 SQL/CLI data type correspondences for COBOL. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 132
14 SQL/CLI data type correspondences for Fortran. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 132
15 SQL/CLI data type correspondences for M. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 132
16 SQL/CLI data type correspondences for Pascal. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 132
17 SQL/CLI data type correspondences for PL/I. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 132
18 SQLSTATE class and subclass values. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 141
19 Implied feature relationships of SQL/MDA. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 143
20 Feature taxonomy for optional features. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 157
©ISO/IEC 2019 – All rights reserved Contents vii

(Blank page)
viii  Multidimensional Arrays (SQL/MDA) ©ISO/IEC 2019 – All rights reserved

Foreword
ISO (the International Organization for Standardization) and IEC (the International Electrotechnical Commission)
form the specialized system for worldwide standardization. National bodies that are members of ISO or IEC
participate in the development of International Standards through technical committees established by the
respective organization to deal with particular fields of technical activity. ISO and IEC technical committees
collaborate in fields of mutual interest. Other international organizations, governmental and non-governmental,
in liaison with ISO and IEC, also take part in the work.
The procedures used to develop this document and those intended for its further maintenance are described in
the ISO/IEC Directives, Part 1. In particular, the different approval criteria needed for the different types of
document should be noted. This document was drafted in accordance with the editorial rules of the ISO/IEC
Directives, Part 2 (see www.iso.org/directives).
Attention is drawn to the possibility that some of the elements of this document may be the subject of patent
rights. ISO and IEC shall not be held responsible for identifying any or all such patent rights. Details of any
patent rights identified during the development of the document will be in the Introduction and/or on the ISO
list of patent declarations received (see www.iso.org/patents), or the IEC list of patent declarations
received (see http://patents.iec.ch).
Any trade name used in this document is information given for the convenience of users and does not constitute
an endorsement.
For an explanation of the voluntary nature of standards, the meaning of ISO specific terms and expressions
related to conformity assessment, as well as information about ISO's adherence to the World Trade Organization
(WTO) principles in the Technical Barriers to Trade (TBT) see www.iso.org/iso/foreword.html.
This document was prepared by Technical Committee ISO/IEC JTC 1, Information technology, Subcommittee
SC 32, Data management and interchange.
This is the first edition of this document.
A list of all parts in the ISO/IEC 9075 series can be found on the ISO website.
Any feedback or questions on this document should be directed to the user's national standards body. A complete
listing of these bodies can be found at www.iso.org/members.html.
NOTE 1 — The individual parts of multi-part standards are not necessarily published together. New editions of one or more parts
may be published without publication of new editions of other parts.
©ISO/IEC 2019 – All rights reserved Foreword ix

Introduction
This document was developed in response to industry demand for the ability to store and manipulate data in
the form of multidimensional arrays within databases managed using database language SQL.
The organization of this document is as follows:
1) Clause 1, “Scope”, specifies the scope of this document.
2) Clause 2, “Normative references”, identifies additional standards that, through reference in this document,
constitute provisions of this document.
3) Clause 3, “Terms and definitions”, defines the notations and conventions used in this document.
4) Clause 4, “Concepts”, describes the concepts used in ISO/IEC 9075.
5) Clause 5, “The parts of ISO/IEC 9075”, augments Clause 5, “The parts of ISO/IEC 9075”, of ISO9075-1,
by summarizing the content of this document, in terms of the concepts described in Clause 4, “Concepts”,
of ISO9075-1.
6) Clause 6, “Concepts”, presents concepts used in the definition of multidimensional arrays.
7) Clause 7, “Lexical elements”, defines a number of lexical elements used in the definition of multidimensional
arrays.
8) Clause 8, “Scalar expressions”, defines a number of scalar expressions used in the definition of multidi-
mensional arrays.
9) Clause 9, “Query expressions”, defines the elements of the language that produce rows and tables of data
as used in multidimensional arrays.
10) Clause 10, “Predicates”, defines the predicates used in the definition of multidimensional arrays.
11) Clause 11, “Additional common rules”, specifies the rules for assignments that retrieve multidimensional
array data from or store multidimensional array data into SQL-data, and formation rules for set operations.
12) Clause 12, “Additional common elements”, defines additional common elements used in the definition of
multidimensional arrays.
13) Clause 13, “Schema definition and manipulation”, defines facilities for creating and managing a schema.
14) Clause 14, “SQL-client modules”, defines SQL-client modules and externally-invoked procedures in the
context of multidimensional arrays.
15) Clause 15, “Data manipulation”, defines the data manipulation statements.
16) Clause 16, “Dynamic SQL”, defines the facilities for executing SQL-statements dynamically in the context
of multidimensional arrays.
17) Clause 17, “Embedded SQL”, defines the host language embeddings in the context of multidimensional
arrays.
18) Clause 18, “Call-Level Interface specifications”, defines facilities for using SQL through a Call-Level
Interface.
19) Clause 19, “Information Schema”, defines the Information and Definition Schema objects associated with
multidimensional arrays.
20) Clause 20, “Definition Schema”, defines base tables on which the viewed tables containing schema infor-
mation depend.
x  Multidimensional Arrays (SQL/MDA) ©ISO/IEC 2019 – All rights reserved

21) Clause 21, “Status codes”, defines SQLSTATE values related to multidimensional arrays.
22) Clause 22, “Conformance”, defines the criteria for conformance to this document.
23) Annex A, “SQL Conformance Summary”, is an informative Annex. It summarizes the conformance
requirements of the SQL language.
24) Annex B, “Implementation-defined elements”, is an informative Annex. It lists those features for which
the body of this document states that the syntax, the meaning, the returned results, the effect on SQL-data
and/or schemas, or any other behavior is partly or wholly implementation-defined.
25) Annex C, “Implementation-dependent elements”, is an informative Annex. It lists those features for which
the body of this document states that the syntax, the meaning, the returned results, the effect on SQL-data
and/or schemas, or any other behavior is partly or wholly implementation-dependent.
26) Annex D, “Incompatibilities with ISO/IEC 9075:2011”, is an informative Annex. It lists incompatibilities
with the previous version of this document.
27) Annex E, “SQL feature taxonomy”, is an informative Annex. It identifies features of the SQL language
specified in this document by an identifier and a short descriptive name. This taxonomy is used to specify
conformance.
In the text of this document, Clauses and Annexes begin new odd-numbered pages, and in Clause 7, “Lexical
elements”, through Clause 22, “Conformance”, Subclauses begin new pages. Any resulting blank space is not
significant.
©ISO/IEC 2019 – All rights reserved Introduction xi

(Blank page)
xii  Multidimensional Arrays (SQL/MDA) ©ISO/IEC 2019 – All rights reserved

INTERNATIONAL STANDARD ISO/IEC 9075-15:2019(E)
Information technology database languages — SQL —
Part 15:
Multi-dimensional arrays (SQL/MDA)
1 Scope
This document defines ways in which Database Language SQL can be used in conjunction with multidimensional
arrays.
©ISO/IEC 2019 – All rights reserved Scope 1

(Blank page)
2  Multidimensional Arrays (SQL/MDA) ©ISO/IEC 2019 – All rights reserved

2 Normative references
The following documents are referred to in the text in such a way that some or all of their content constitutes
requirements of this document. For dated references, only the edition cited applies. For undated references, the
latest edition of the referenced document (including any amendments) applies.
ISO/IEC 9075-1:2016, Information technology — Database languages — SQL — Part 1: Framework
(SQL/Framework).
ISO/IEC 9075-2:2016, Information technology — Database languages — SQL — Part 2: Foundation
(SQL/Foundation).
ISO/IEC 9075-3:2016, Information technology — Database languages — SQL — Part 3: Call-Level Interface
(SQL/CLI).
ISO/IEC 9075-11:2016, Information technology — Database languages — SQL — Part 11: Information
and Definition Schemas (SQL/Schemata).
Multipurpose Internet Mail Extensions (MIME), Part Two: Media Types
http://tools.ietf.org/html/rfc2046
The JavaScript Object Notation (JSON) Data Interchange Format
http://tools.ietf.org/html/rfc7159
©ISO/IEC 2019 – All rights reserved Normative references 3

(Blank page)
4  Multidimensional Arrays (SQL/MDA) ©ISO/IEC 2019 – All rights reserved

3.1 Definitions
3 Terms and definitions
This Clause modifies Clause 3, “Definitions, notations, and conventions”, in [ISO9075-2].
3.1 Definitions
This Subclause modifies Subclause 3.1, “Definitions”, in [ISO9075-2].
3.1.1 Definitions provided in this document
For the purposes of this document, the terms and definitions given in ISO9075-1, ISO9075-2, and the following
apply.
ISO and IEC maintain terminological databases for use in standardization at the following addresses:
• IEC Electropedia: available at http://www.electropedia.org/
• ISO Online browsing platform: available at http://www.iso.org/obp
3.1.1.1 coordinate
non-empty ordered list of integers
3.1.1.2 maximum MD-extent (of an MD-array type or a site of MD-array type)
MD-extent of an MD-array type
NOTE 2 — The term “maximum MD-extent” is used for the MD-extent of an MD-array type because it defines the maximum
MD-extent that a value of that MD-array type can have. The MD-extent of an MD-array value must be within the maximum MD-
extent of the value's type.
3.1.1.3 MD-array
ordered collection of elements of the same type associated with an MD-extent where each element
is 1:1 associated with some coordinate within its MD-extent
NOTE 3 — A coordinate is within an MD-extent if every coordinate value from the integer list is greater than or equal to the lower
limit, and less than or equal to the upper limit of the MD-interval of the MD-axis at the position in the MD-extent as the coordinate
value has within the coordinate.
3.1.1.4 MD-axis
named MD-interval
3.1.1.5 MD-dimension
number of MD-axes in the MD-extent of an MD-array
3.1.1.6 MD-extent
non-empty ordered collection of MD-axes with no duplicate names
3.1.1.7 MD-interval
integer interval given by a pair of lower and upper integer limits such that the lower limit is less than
or equal to the upper limit; the interval is closed, i.e., both limits are contained in it
©ISO/IEC 2019 – All rights reserved Terms and definitions 5

(Blank page)
6  Multidimensional Arrays (SQL/MDA) ©ISO/IEC 2019 – All rights reserved

4.1 SQL data types
4 Concepts
This Clause modifies Clause 4, “Concepts”, in [ISO9075-1].
4.1 SQL data types
This Subclause modifies Subclause 4.4, “SQL data types”, in [ISO9075-1].
4.1.1 SQL data types
This Subclause modifies Subclause 4.4.5, “Constructed composite types”, in [ISO9075-1].
4.1.1.1 Collection types
This Subclause modifies Subclause 4.4.5.1, “Collection types”, in [ISO9075-1].
Append this paragraph An MD-array is an ordered collection of not necessarily distinct values, whose elements
are referenced by their coordinate in the MD-array.
Append this paragraph An MD-array type is specified by an MD-array type constructor.
4.2 SQL-schema objects
This Subclause modifies Subclause 4.6, “SQL-schema objects”, in [ISO9075-1].
4.2.1 User-defined types
This Subclause modifies Subclause 4.6.4, “User-defined types”, in [ISO9075-1].
4.2.1.1 Distinct types
This Subclause modifies Subclause 4.6.4.2, “Distinct types”, in [ISO9075-1].
Replace 1st paragraph A distinct type is a user-defined data type that is based on some predefined type, array
type, or multiset type. The values of a distinct type are represented by the values of the type on which it is
based.
©ISO/IEC 2019 – All rights reserved Concepts 7

(Blank page)
8  Multidimensional Arrays (SQL/MDA) ©ISO/IEC 2019 – All rights reserved

5.1 Overview
5 The parts of ISO/IEC 9075
This Clause modifies Clause 5, “The parts of ISO/IEC 9075”, in [ISO9075-1].
5.1 Overview
This Subclause modifies Subclause 5.1, “Overview”, in [ISO9075-1].
Insert before the last paragraph ISO/IEC 9075-15 defines ways in which Database Language SQL can be used
with multidimensional array data.
5.2 ISO/IEC 9075-15: MultiDimensional Arrays (SQL/MDA)
ISO/IEC 9075-15 defines facilities that allow Database Language SQL to support creation and manipulation
of multidimensional arrays.
These include:
1) A new collection type, MD-array.
2) New built-in operators to create and manipulate values of the MD-array type.
©ISO/IEC 2019 – All rights reserved The parts of ISO/IEC 9075 9

(Blank page)
10  Multidimensional Arrays (SQL/MDA) ©ISO/IEC 2019 – All rights reserved

6.1 Data types
6 Concepts
This Clause modifies Clause 4, “Concepts”, in [ISO9075-2].
6.1 Data types
This Subclause modifies Subclause 4.1, “Data types”, in [ISO9075-2].
6.1.1 General introduction to data types
This Subclause modifies Subclause 4.1.4, “Data type terminology”, in [ISO9075-2].
Replace 4th paragraph .
A constructed type is specified using one of SQL's data type constructors, ARRAY, MDARRAY, MULTISET,
REF, and ROW. A constructed type is either an array type, an MD-array type, a multiset type, a reference type,
or a row type, according to whether it is specified with ARRAY, MDARRAY, MULTISET, REF, or ROW,
respectively. Array types, MD-array types, and multiset types are known generically as collection types.
6.1.2 Data type terminology
This Subclause modifies Subclause 4.1.4, “Data type terminology”, in [ISO9075-2].
Append this paragraph A data type TY is a collection-containing type if exactly one of the following conditions
is true:
— TY is a collection type.
— TY is a row type, and the declared type of some field of TY is a collection-containing type.
— TY is distinct type, and the source type of TY is a collection-containing type.
— TY is a structured type and the declared type of some attribute of TY is a collection-containing type.
Augment the list in 11th paragraph
— A type T is MD-array-ordered if T is S-ordered, where S is the set of MD-array types.
6.2 Numbers
This Subclause modifies Subclause 4.4, “Numbers”, in [ISO9075-2].
©ISO/IEC 2019 – All rights reserved Concepts 11

6.2 Numbers
6.2.1 Operations involving numbers
This Subclause modifies Subclause 4.4.3, “Operations involving numbers”, in [ISO9075-2].
Augment the list following the 1st paragraph
— (see Subclause 6.4.4.3, “Operators that operate on MD-array values and
return numbers”) operates on an MD-array argument and an MD-axis name, and returns an integer denoting
its index position in the MD-extent of the MD-array.
— (see Subclause 6.4.4.3, “Operators that operate on MD-array values and return
numbers”) operates on an MD-array argument and returns an integer denoting its MD-dimension.
— (see Subclause 6.4.4.3, “Operators that operate on MD-array values and return
numbers”) operates on an MD-array argument and an MD-axis name or index, and returns the lower limit
of an MD-axis.
— (see Subclause 6.4.4.3, “Operators that operate on MD-array values and return
numbers”) operates on an MD-array argument and an MD-axis name or index, and returns the upper limit
of an MD-axis.
6.3 User-defined types
This Subclause modifies Subclause 4.7, “User-defined types”, in [ISO9075-2].
6.3.1 Distinct types
This Subclause modifies Subclause 4.7.2, “Distinct types”, in [ISO9075-2].
Replace 4th paragraph
A distinct type DT whose source type is a collection type CT is said to have an element type, which is the element
type of CT. CT shall not be an MD-array type. A value of DT has a cardinality, which is the number of elements
in that value. A distinct type whose source type is an array type AT is said to have a maximum cardinality,
which is the maximum cardinality of AT.
6.4 Collection types
This Subclause modifies Subclause 4.10, “Collection types”, in [ISO9075-2].
6.4.1 Introduction to collection types
This Subclause modifies Subclause 4.10.1, “Introduction to collection types”, in [ISO9075-2].
Replace last sentence of the 1st paragraph .
This document supports three kinds of collection types: arrays, MD-arrays, and multisets.
Replace 2nd paragraph .
12  Multidimensional Arrays (SQL/MDA) ©ISO/IEC 2019 – All rights reserved

6.4 Collection types
A specific CT is a specified by pairing a keyword KC (one of ARRAY,
MDARRAY, or MULTISET) with a specific data type EDT. In addition, a maximum cardinality may optionally
be specified for arrays, while a maximum MD-extent is mandatory for MD-arrays. Every element of every
possible value of CT is a value of EDT and is permitted to be, more specifically, of some subtype of EDT. EDT
is termed the element type of CT. KC specifies the kind of collection, such as ARRAY, MDARRAY, or
MULTISET, that every value of CT is, and thus determines the operators that are available for operating on or
returning values of CT.
Replace the list following the 4th paragraph :
— The type designator of CT.
— The descriptor of the element type of CT.
— An indication of the kind of the collection of CT: ARRAY (array type), MDARRAY (MD-array type), or
MULTISET (multiset type).
— If CT is an array type, the maximum number of elements of CT.
— If CT is an MD-array type, the MD-axis descriptors of each dimension of the MD-array.
An MD-axis descriptor describes a dimension of an MD-array type. The MD-axis descriptor includes:
— The name of the MD-axis.
— The ordinal position of the MD-axis in the MD-extent of the MD-array type.
— The lower limit of the MD-axis.
— The upper limit of the MD-axis.
6.4.2 MD-arrays
An MD-array A is a collection where each element is uniquely identified by a d-dimensional coordinate that is
an element of the Cartesian product of the d MD-intervals of A's MD-extent. d is the MD-dimension of A.
The MD-extent of A is a non-empty array of MD-axis elements. The MD-axis at position i, with 1 (one) ≤ i≤
d, consists of a name N unique within this MD-extent and a closed MD-interval given by a lower limit LO
i i
and an upper limit HI . A LO or HI that is the null value in a maximum MD-extent indicates t
...