(This is stale and you may wish to find a more up-to-date copy, but it is preserved here for posterity. Enjoy -- Daria 24 Nov 2017) Information Technology - Database Language SQL (Proposed revised text of DIS 9075) July 1992 (Second Informal Review Draft) ISO/IEC 9075:1992, Database Language SQL- July 30, 1992 Digital Equipment Corporation Maynard, Massachusetts Contents Page Foreword.........................................................xi Introduction.....................................................xiii 1 Scope ........................................................ 1 2 Normative references ......................................... 3 3 Definitions, notations, and conventions ...................... 5 3.1 Definitions ................................................ 5 3.1.1Definitions taken from ISO/IEC 10646 ....................... 5 3.1.2Definitions taken from ISO 8601 ............................ 5 3.1.3Definitions provided in this International Standard ........ 5 3.2 Notation ................................................... 7 3.3 Conventions ................................................ 9 3.3.1Informative elements ....................................... 9 3.3.2Specification of syntactic elements ........................ 9 3.3.3Specification of the Information Schema ....................10 3.3.4Use of terms ...............................................10 3.3.4Exceptions .................................................10 3.3.4Syntactic containment ......................................11 3.3.4Terms denoting rule requirements ...........................12 3.3.4Rule evaluation order ......................................12 3.3.4Conditional rules ..........................................13 3.3.4Syntactic substitution .....................................13 3.3.4Other terms ................................................14 3.3.5Descriptors ................................................14 3.3.6Index typography ...........................................15 3.4 Object identifier for Database Language SQL ................16 4 Concepts .....................................................19 4.1 Data types .................................................19 4.2 Character strings ..........................................20 4.2.1Character strings and collating sequences ..................20 4.2.2Operations involving character strings .....................22 4.2.2Operators that operate on character strings and return char- acter strings...............................................22 4.2.2Other operators involving character strings ................23 4.2.3Rules determining collating sequence usage .................23 4.3 Bit strings ................................................26 4.3.1Bit string comparison and assignment .......................27 4.3.2Operations involving bit strings ...........................27 4.3.2Operators that operate on bit strings and return bit strings ............................................................27 4.3.2Other operators involving bit strings ......................27 ii Database Language SQL 4.4 Numbers ....................................................27 4.4.1Characteristics of numbers .................................28 4.4.2Operations involving numbers ...............................29 4.5 Datetimes and intervals ....................................29 4.5.1Datetimes ..................................................30 4.5.2Intervals ..................................................32 4.5.3Operations involving datetimes and intervals ...............34 4.6 Type conversions and mixing of data types ..................34 4.7 Domains ....................................................35 4.8 Columns ....................................................36 4.9 Tables .....................................................37 4.10 Integrity constraints ......................................40 4.10.Checking of constraints ....................................41 4.10.Table constraints ..........................................41 4.10.Domain constraints .........................................43 4.10.Assertions .................................................43 4.11 SQL-schemas ................................................44 4.12 Catalogs ...................................................45 4.13 Clusters of catalogs .......................................45 4.14 SQL-data ...................................................45 4.15 SQL-environment ............................................46 4.16 Modules ....................................................46 4.17 Procedures .................................................47 4.18 Parameters .................................................47 4.18.Status parameters ..........................................47 4.18.Data parameters ............................................48 4.18.Indicator parameters .......................................48 4.19 Diagnostics area ...........................................48 4.20 Standard programming languages .............................49 4.21 Cursors ....................................................49 4.22 SQL-statements .............................................51 4.22.Classes of SQL-statements ..................................51 4.22.SQL-statements classified by function ......................52 4.22.Embeddable SQL-statements ..................................55 4.22.Preparable and immediately executable SQL-statements .......56 4.22.Directly executable SQL-statements .........................58 4.22.SQL-statements and transaction states ......................59 4.23 Embedded syntax ............................................61 4.24 SQL dynamic statements .....................................61 4.25 Direct invocation of SQL ...................................64 4.26 Privileges .................................................64 4.27 SQL-agents .................................................66 4.28 SQL-transactions ...........................................67 4.29 SQL-connections ............................................70 4.30 SQL-sessions ...............................................72 Table of Contents iii 4.31 Client-server operation ....................................74 4.32 Information Schema .........................................75 4.33 Leveling ...................................................75 4.34 SQL Flagger ................................................76 5 Lexical elements .............................................79 5.1 ...................................79 5.2 and ....................................82 5.3 ..................................................89 5.4 Names and identifiers ......................................98 6 Scalar expressions ...........................................107 6.1 ................................................107 6.2 and ...........114 6.3 ..........................................118 6.4 .........................................121 6.5 ...............................124 6.6 ...................................128 6.7 ....................................132 6.8 ..................................139 6.9 ..........................................141 6.10 .......................................144 6.11 .........................................155 6.12 .................................157 6.13 ..................................160 6.14 ................................165 6.15 ................................168 7 Query expressions ............................................173 7.1 ....................................173 7.2 ..................................176 7.3 .........................................177 7.4 ..............................................178 7.5 .............................................180 7.6 .............................................185 7.7 ..........................................187 7.8 ............................................189 7.9 ......................................191 7.10 .........................................196 7.11 , , and ....203 8 Predicates ...................................................205 8.1 ................................................205 8.2 .....................................207 8.3 ........................................211 8.4 .............................................212 iv Database Language SQL 8.5 ...........................................214 8.6 ...........................................218 8.7 ..........................220 8.8 .........................................222 8.9 .........................................223 8.10 ..........................................224 8.11 .......................................227 8.12 .........................................229 9 Data assignment rules ........................................231 9.1 Retrieval assignment .......................................231 9.2 Store assignment ...........................................234 9.3 Set operation result data types ............................237 10 Additional common elements ...................................239 10.1 .......................................239 10.2 ..........................................243 10.3 ...............................................245 10.4 ..............................248 10.5 ...........................................251 10.6 and ...252 11 Schema definition and manipulation ...........................255 11.1 ........................................255 11.2 ....................................258 11.3 .........................................260 11.4 ........................................262 11.5 ...........................................266 11.6 ..............................270 11.7 .............................272 11.8 ........................274 11.9 ..............................281 11.10 ....................................283 11.11 ....................................284 11.12 ..................................286 11.13 ................................287 11.14 ...............................288 11.15 ...................................289 11.16 ..........................291 11.17 .........................292 11.18 .....................................294 11.19 ..........................................296 11.20 ......................................300 11.21 ........................................301 11.22 ...................................304 11.23 ................................305 Table of Contents v 11.24 ...............................306 11.25 .........................307 11.26 ........................308 11.27 ....................................309 11.28 .................................311 11.29 .............................313 11.30 .....................................314 11.31 .................................318 11.32 ...................................320 11.33 ...............................323 11.34 .....................................325 11.35 .................................328 11.36 ..........................................329 11.37 .........................................333 12 Module .......................................................341 12.1 ...................................................341 12.2 .......................................344 12.3 ................................................346 12.4 Calls to a .....................................352 12.5 ..................................368 13 Data manipulation ............................................371 13.1 ...........................................371 13.2 ...........................................375 13.3 ..........................................377 13.4 ..........................................381 13.5 .............................382 13.6 .............................384 13.7 ...............................386 13.8 .........................................388 13.9 .............................391 13.10 ...............................394 13.11 ..............................397 14 Transaction management .......................................399 14.1 ................................399 14.2 ...........................401 14.3 .........................................403 14.4 .......................................405 15 Connection management ........................................407 15.1 ........................................407 15.2 .................................410 15.3 .....................................412 vi Database Language SQL 16 Session management ...........................................415 16.1 ....................................415 16.2 .....................................417 16.3 ......................................419 16.4 ...........420 16.5 ............................422 17 Dynamic SQL ..................................................425 17.1 Description of SQL item descriptor areas ...................425 17.2 ............................431 17.3 ..........................433 17.4 .................................434 17.5 .................................438 17.6 ........................................442 17.7 ............................449 17.8 .......................................450 17.9 .............................................451 17.10 ........................................459 17.11 ..............................462 17.12 ...................................464 17.13 ................................465 17.14 ...................................467 17.15 ..................................469 17.16 ..................................471 17.17 .....................472 17.18 .....................474 17.19 ..........476 17.20 ..........477 18 Diagnostics management .......................................479 18.1 ................................479 19 Embedded SQL .................................................489 19.1 ................................489 19.2 ...........................497 19.3 .................................500 19.4 ...................................504 19.5 ...............................508 19.6 .............................512 19.7 ...............................515 19.8 ..............................517 19.9 ................................520 20 Direct invocation of SQL .....................................525 20.1 .....................................525 20.2 ...................530 Table of Contents vii 21 Information Schema and Definition Schema .....................533 21.1 Introduction ...............................................533 21.2 Information Schema .........................................535 21.2.INFORMATION_SCHEMA Schema ..................................535 21.2.INFORMATION_SCHEMA_CATALOG_NAME base table .................536 21.2.INFORMATION_SCHEMA_CATALOG_NAME_CARDINALITY assertion ......537 21.2.SCHEMATA view ..............................................538 21.2.DOMAINS view ...............................................539 21.2.DOMAIN_CONSTRAINTS view ....................................541 21.2.TABLES view ................................................542 21.2.VIEWS view .................................................543 21.2.COLUMNS view ...............................................544 21.2.TABLE_PRIVILEGES view ......................................546 21.2.COLUMN_PRIVILEGES view .....................................547 21.2.USAGE_PRIVILEGES view ......................................548 21.2.TABLE_CONSTRAINTS view .....................................549 21.2.REFERENTIAL_CONSTRAINTS view ...............................550 21.2.CHECK_CONSTRAINTS view .....................................551 21.2.KEY_COLUMN_USAGE view ......................................552 21.2.ASSERTIONS view ............................................553 21.2.CHARACTER_SETS view ........................................554 21.2.COLLATIONS view ............................................555 21.2.TRANSLATIONS view ..........................................556 21.2.VIEW_TABLE_USAGE view ......................................557 21.2.VIEW_COLUMN_USAGE view .....................................558 21.2.CONSTRAINT_TABLE_USAGE view ................................559 21.2.CONSTRAINT_COLUMN_USAGE view ...............................561 21.2.COLUMN_DOMAIN_USAGE view ...................................562 21.2.SQL_LANGUAGES view .........................................563 21.2.SQL_IDENTIFIER domain ......................................564 21.2.CHARACTER_DATA domain ......................................564 21.2.CARDINAL_NUMBER domain .....................................565 21.3 Definition Schema ..........................................566 21.3.Introduction ...............................................566 21.3.DEFINITION_SCHEMA Schema ...................................567 21.3.USERS base table ...........................................568 21.3.SCHEMATA base table ........................................569 21.3.DATA_TYPE_DESCRIPTOR base table ............................570 21.3.DOMAINS base table .........................................573 21.3.DOMAIN_CONSTRAINTS base table ..............................574 21.3.TABLES base table ..........................................576 21.3.VIEWS base table ...........................................578 21.3.COLUMNS base table .........................................580 21.3.VIEW_TABLE_USAGE base table ................................583 21.3.VIEW_COLUMN_USAGE base table ...............................584 viii Database Language SQL 21.3.TABLE_CONSTRAINTS base table ...............................585 21.3.KEY_COLUMN_USAGE base table ................................588 21.3.REFERENTIAL_CONSTRAINTS base table .........................590 21.3.CHECK_CONSTRAINTS base table ...............................593 21.3.CHECK_TABLE_USAGE base table ...............................595 21.3.CHECK_COLUMN_USAGE base table ..............................596 21.3.ASSERTIONS base table ......................................598 21.3.TABLE_PRIVILEGES base table ................................600 21.3.COLUMN_PRIVILEGES base table ...............................602 21.3.USAGE_PRIVILEGES base table ................................604 21.3.CHARACTER_SETS base table ..................................606 21.3.COLLATIONS base table ......................................608 21.3.TRANSLATIONS base table ....................................610 21.3.SQL_LANGUAGES base table ...................................612 21.4 Assertions on the base tables ..............................616 21.4.UNIQUE_CONSTRAINT_NAME assertion ...........................616 21.4.EQUAL_KEY_DEGREES assertion ................................617 21.4.KEY_DEGREE_GREATER_THAN_OR_EQUAL_TO_1 assertion ............618 22 Status codes .................................................619 22.1 SQLSTATE ...................................................619 22.2 SQLCODE ....................................................624 23 Conformance ..................................................625 23.1 Introduction ...............................................625 23.2 Claims of conformance ......................................625 23.3 Extensions and options .....................................626 23.4 Flagger requirements .......................................626 23.5 Processing methods .........................................627 Annex A Leveling the SQL Language..............................629 A.1 Intermediate SQL Specifications ............................629 A.2 Entry SQL Specifications ...................................640 Annex B Implementation-defined elements........................653 Annex C Implementation-dependent elements......................667 Annex D Deprecated features....................................675 Annex E Incompatibilities with ISO/IEC 9075:1989...............677 Annex F Maintenance and interpretation of SQL..................685 Index Table of Contents ix TABLES Table Page 1 Collating coercibility rules for monadic operators .........24 2 Collating coercibility rules for dyadic operators ..........24 3 Collating sequence usage for comparisons ...................25 4 Fields in datetime items ...................................30 5 Fields in year-month INTERVAL items ........................32 6 Fields in day-time INTERVAL items ..........................32 7 Valid values for fields in INTERVAL items ..................33 8 Valid operators involving datetimes and intervals ..........34 9 SQL-transaction isolation levels and the three phenomena ...69 10 Valid values for fields in datetime items ..................112 11 Valid values for fields in INTERVAL items ..................113 12 semantics .................................218 13 Truth table for the AND boolean ............................230 14 Truth table for the OR boolean .............................230 15 Truth table for the IS boolean .............................230 16 Standard programming languages .............................243 17 Data types of s used in SQL item descriptor areas ............................................................427 18 Codes used for SQL data types in Dynamic SQL ...............429 19 Codes associated with datetime data types in Dynamic SQL ...429 20 Codes used for s in Dynamic SQL ........430 21 s for use with .....481 22 SQL-statement character codes for use in the diagnostics area........................................................482 23 SQLSTATE class and subclass values .........................619 24 SQLCODE values .............................................624 x Database Language SQL X3H2-92-154/DBL CBR-002 Foreword ISO (the International Organization for Standardization) is a worldwide federation of national standards bodies (ISO member bodies). The work of preparing International Standards is nor- mally carried out through ISO technical committees. Each member body interested in a subject for which a technical committee has been established has the right to be represented on that committee. International organizations, governmental and non-governmental, in liaison with ISO, also take part in the work. ISO collaborates closely with the International Electrotechnical Commission (IEC) on all matters of electrotechnical standardization. Draft International Standards adopted by the technical committees are circulated to the member bodies for approval before their ac- ceptance as International Standards by the ISO Council. They are approved in accordance with ISO procedures requiring at least 75% approval by the member bodies voting. International Standard ISO/IEC 9075:1992 was prepared by Joint Technical Committee ISO/IEC JTC1, Information Processing Systems. It cancels and replaces International Standard ISO/IEC 9075:1989, Database Language-SQL, of which it constitutes a technical revi- sion. This International Standard contains seven informative annexes: - Annex A (informative): Leveling the SQL Language; - Annex B (informative): Implementation-defined elements; - Annex C (informative): Implementation-dependent elements; - Annex D (informative): Deprecated features; - Annex E (informative): Incompatibilities with ISO/IEC 9075:1989; and - Annex F (informative): Maintenance and interpretation of SQL. Foreword xi X3H2-92-154/DBL CBR-002 Introduction This International Standard was approved in 1992. This International Standard was developed from ISO/IEC 9075:1989, Information Systems, Database Language SQL with Integrity Enhancements, and replaces that International Standard. It adds significant new features and capabilities to the specifications. It is generally compatible with ISO/IEC 9075:1989, in the sense that, with very few exceptions, SQL language that conforms to ISO/IEC 9075:1989 also conforms to this International Standard, and will be treated in the same way by an implementation of this International Standard as it would by an implementation of ISO/IEC 9075:1989. The known incompatibilities between ISO/IEC 9075:1989 and this International Standard are stated in informative Annex E, "Incompatibilities with ISO/IEC 9075:1989". Technical changes between ISO/IEC 9075:1989 and this International Standard include both improvements or enhancements to existing fea- tures and the definition of new features. Significant improvements in existing features include: - A better definition of direct invocation of SQL language; - Improved diagnostic capabilities, especially a new status param- eter (SQLSTATE), a diagnostics area, and supporting statements. Significant new features are: 1) Support for additional data types (DATE, TIME, TIMESTAMP, INTERVAL, BIT string, variable-length character and bit strings, and NATIONAL CHARACTER strings), 2) Support for character sets beyond that required to express SQL language itself and support for additional collations, 3) Support for additional scalar operations, such as string opera- tions for concatenate and substring, date and time operations, and a form for conditional expressions, 4) Increased generality and orthogonality in the use of scalar- valued and table-valued query expressions, 5) Additional set operators (for example, union join, natural join, set difference, and set intersection), 6) Capability for domain definitions in the schema, 7) Support for Schema Manipulation capabilities (especially DROP and ALTER statements), Introduction xiii X3H2-92-154/DBL CBR-002 8) Support for bindings (modules and embedded syntax) in the Ada, C, and MUMPS languages, 9) Additional privilege capabilities, 10)Additional referential integrity facilities, including ref- erential actions, subqueries in CHECK constraints, separate assertions, and user-controlled deferral of constraints, 11)Definition of an Information Schema, 12)Support for dynamic execution of SQL language, 13)Support for certain facilities required for Remote Database Access (especially connection management statements and quali- fied schema names), 14)Support for temporary tables, 15)Support for transaction consistency levels, 16)Support for data type conversions (CAST expressions among data types), 17)Support for scrolled cursors, and 18)A requirement for a flagging capability to aid in portability of application programs. The organization of this International Standard is as follows: 1) Clause 1, "Scope", specifies the scope of this International Standard. 2) Clause 2, "Normative references", identifies additional stan- dards that, through reference in this International Standard, constitute provisions of this International Standard. 3) Clause 3, "Definitions, notations, and conventions", defines the notations and conventions used in this International Standard. 4) Clause 4, "Concepts", presents concepts used in the definition of SQL. 5) Clause 5, "Lexical elements", defines the lexical elements of the language. 6) Clause 6, "Scalar expressions", defines the elements of the language that produce scalar values. 7) Clause 7, "Query expressions", defines the elements of the lan- guage that produce rows and tables of data. 8) Clause 8, "Predicates", defines the predicates of the language. xiv Database Language SQL X3H2-92-154/DBL CBR-002 9) Clause 9, "Data assignment rules", specifies the rules for assignments that retrieve data from or store data into the database, and formation rules for set operations. 10)Clause 10, "Additional common elements", defines additional lan- guage elements that are used in various parts of the language. 11)Clause 11, "Schema definition and manipulation", defines facili- ties for creating and managing a schema. 12)Clause 12, "Module", defines modules and procedures. 13)Clause 13, "Data manipulation", defines the data manipulation statements. 14)Clause 14, "Transaction management", defines the SQL-transaction management statements. 15)Clause 15, "Connection management" defines the SQL-connection management statements. 16)Clause 16, "Session management", defines the SQL-session manage- ment statements. 17)Clause 17, "Dynamic SQL", defines the facilities for executing SQL-statements dynamically. 18)Clause 18, "Diagnostics management", defines the diagnostics management facilities. 19)Clause 19, "Embedded SQL", defines syntax for embedding SQL in certain standard programming languages. 20)Clause 20, "Direct invocation of SQL", defines the direct invo- cation of SQL language. 21)Clause 21, "Information Schema and Definition Schema", defines viewed tables that contain schema information. 22)Clause 22, "Status codes", defines values that identify the status of the execution of SQL-statements and the mechanisms by which those values are returned. 23)Clause 23, "Conformance", defines the criteria for conformance to this International standard. 24)Annex A, "Leveling the SQL Language", is an informative Annex. It lists the leveling rules defining the Entry SQL and Intermediate SQL subset levels of the SQL language. 25)Annex B, "Implementation-defined elements", is an informa- tive Annex. It lists those features for which the body of the International Standard states that the syntax or meaning or ef- fect on the database is partly or wholly implementation-defined, and describes the defining information that an implementor shall provide in each case. Introduction xv X3H2-92-154/DBL CBR-002 26)Annex C, "Implementation-dependent elements", is an informa- tive Annex. It lists those features for which the body of the International Standard states explicitly that the meaning or effect on the database is implementation-dependent. 27)Annex D, "Deprecated features", is an informative Annex. It lists features that the responsible Technical Committee in- tends will not appear in a future revised version of this International Standard. 28)Annex E, "Incompatibilities with ISO/IEC 9075:1989", is an in- formative Annex. It lists the incompatibilities between this version of this International Standard and ISO/IEC 9075:1989. 29)Annex F, "Maintenance and interpretation of SQL", is an infor- mative Annex. It identifies SQL interpretations and corrections that have been processed by ISO/IEC JTC1/SC21 since adoption of ISO/IEC 9075:1989. In the text of this International Standard, Clauses begin a new odd-numbered page, and in Clause 5, "Lexical elements", through Clause 22, "Status codes", Subclauses begin a new page. Any result- ing blank space is not significant. xvi Database Language SQL X3H2-92-154/DBL CBR-002 Information Technology - Database Language SQL 1 Scope This International Standard defines the data structures and basic operations on SQL-data. It provides functional capabilities for creating, accessing, maintaining, controlling, and protecting SQL- data. Note: The framework for this International Standard is described by the Reference Model of Data Management (ISO/IEC DIS 10032:1991). This International Standard specifies the syntax and semantics of a database language - for specifying and modifying the structure and the integrity constraints of SQL-data, - for declaring and invoking operations on SQL-data and cursors, and - for declaring database language procedures and embedding them into a standard programming language. It also specifies an Information Schema that describes the struc- ture and the integrity constraints of SQL-data. This International Standard - provides a vehicle for portability of data definitions and com- pilation units between SQL-implementations, - provides a vehicle for interconnection of SQL-implementations, - specifies syntax for embedding SQL-statements in a compilation unit that otherwise conforms to the standard for a particular programming language. It defines how an equivalent compilation unit may be derived that conforms to the particular programming language standard. In that equivalent compilation unit, each embedded SQL-statement has been replaced by statements that invoke a database language procedure that contains the SQL- statement, and - specifies syntax for direct invocation of SQL-statements. Scope 1 X3H2-92-154/DBL CBR-002 This International Standard does not define the method or time of binding between any of: - database management system components, - SQL data definition declarations, - SQL procedures, or - compilation units, including those containing embedded SQL. Implementations of this International Standard may exist in en- vironments that also support application programming languages, end-user query languages, report generator systems, data dictionary systems, program library systems, and distributed communication systems, as well as various tools for database design, data admin- istration, and performance optimization. 2 Database Language SQL X3H2-92-154/DBL CBR-002 2 Normative references The following standards contain provisions that, through reference in this text, constitute provisions of this International Standard. At the time of publication, the editions indicated were valid. All standards are subject to revision, and parties to agreements based on this International Standard are encouraged to investigate the possibility of applying the most recent editions of the stan- dards listed below. Members of IEC and ISO maintain registers of currently valid International Standards. - ISO/IEC 646:1991, Information technology-ISO 7-bit coded charac- ter set for information interchange. - ISO/IEC 1539:1991, Information technology-Programming languages- Fortran. - ISO 1989:1985, Programming languages-COBOL. (Endorsement of ANSI X3.23-1985). - ISO 2022:1986, Information technology-ISO 7-bit and 8-bit coded character sets-code extension techniques. - ISO 6160:1979, Programming languages-PL/I (Endorsement of ANSI X3.53-1976). - ISO 7185:1990, Information technology-Programming languages- Pascal. - ISO 8601:1988, Data elements and interchange formats - Information interchange-Representation of dates and times. - ISO 8652:1987, Programming languages-Ada. (Endorsement of ANSI/MIL-STD-1815A-1983). - ISO/IEC 8824:1990, Information technology-Open Systems Interconnection- Specification of Abstract Syntax Notation One (ASN.1). - ISO/IEC 9579-2:[1], Information technology - Open Systems Interconnection - Remote Database Access, Part 2: SQL special- ization. - ISO/IEC 9899:1990, Programming languages - C. - ISO/IEC 10206:1991, Information technology-Programming languages- Extended Pascal. - ISO/IEC 10646:[1], Information technology-Multiple-octet coded character set. ____________________ [1] To be published Normative references 3 X3H2-92-154/DBL CBR-002 - ISO/IEC 11756:[1], Information technology-Programming languages- MUMPS. 4 Database Language SQL X3H2-92-154/DBL CBR-002 3 Definitions, notations, and conventions 3.1 Definitions For the purposes of this International Standard, the following definitions apply. 3.1.1 Definitions taken from ISO/IEC 10646 This International Standard makes use of the following terms de- fined in ISO/IEC 10646: a) character b) octet c) variable-length coding d) fixed-length coding 3.1.2 Definitions taken from ISO 8601 This International Standard makes use of the following terms de- fined in ISO 8601: a) Coordinated Universal Time (UTC) b) date ("date, calendar" in ISO 8601) 3.1.3 Definitions provided in this International Standard This International Standard defines the following terms: a) assignable: The characteristic of a value or of a data type that permits that value or the values of that data type to be assigned to data instances of a specified data type. b) cardinality (of a collection): The number of objects in that collection. Those objects need not necessarily have distinct values. c) character repertoire; repertoire: A set of characters used for a specific purpose or application. Each character repertoire has an implied default collating sequence. d) coercibility: An attribute of character string data items that governs how a collating sequence for the items is determined. Definitions, notations, and conventions 5 X3H2-92-154/DBL CBR-002 3.1 Definitions e) collation; collating sequence: A method of ordering two com- parable character strings. Every character set has a default collation. f) comparable: The characteristic of two data objects that per- mits the value of one object to be compared with the value of the other object. Also said of data types: Two data types are comparable if objects of those data types are comparable. g) descriptor: A coded description of an SQL object. It includes all of the information about the object that a conforming SQL- implementation requires. h) distinct: Two values are said to be not distinct if either: both are the null value, or they compare equal according to Subclause 8.2, "". Otherwise they are distinct. Two rows (or partial rows) are distinct if at least one of their pairs of respective values is distinct. Otherwise they are not distinct. The result of evaluating whether or not two values or two rows are distinct is never unknown. i) duplicate: Two or more values or rows are said to be duplicates (of each other) if and only if they are not distinct. j) dyadic operator: An operator having two operands: a left operand and a right operand. An example of a dyadic arithmetic operator in this International Standard is "-", specifying the subtrac- tion of the right operand from the left operand. k) form-of-use: A convention (or encoding) for representing charac- ters (in character strings). Some forms-of-use are fixed-length codings and others are variable-length codings. l) form-of-use conversion: A method of converting character strings from one form-of-use to another form-of-use. m) implementation-defined: Possibly differing between SQL- implementations, but specified by the implementor for each particular SQL-implementation. n) implementation-dependent: Possibly differing between SQL- implementations, but not specified by this International Standard and not required to be specified by the implementor for any particular SQL-implementations. o) monadic operator: An operator having one operand. An example of a monadic arithmetic operator in this International Standard is "-", specifying the negation of the operand. p) multiset: An unordered collection of objects that are not neces- sarily distinct. The collection may be empty. q) n-adic operator: An operator having a variable number of operands (informally: n operands). An example of an n-adic operator in this International Standard is COALESCE. 6 Database Language SQL X3H2-92-154/DBL CBR-002 3.1 Definitions r) null value (null): A special value, or mark, that is used to indicate the absence of any data value. s) persistent: Continuing to exist indefinitely, until destroyed deliberately. Referential and cascaded actions are regarded as deliberate. Actions incidental to the ending of an SQL- transaction (see Subclause 4.28, "SQL-transactions") or an SQL- session (see Subclause 4.30, "SQL-sessions") are not regarded as deliberate. t) redundant duplicates: All except one of any multiset of dupli- cate values or rows. u) repertoire: See character repertoire. v) sequence: An ordered collection of objects that are not neces- sarily distinct. w) set: An unordered collection of distinct objects. The collection may be empty. x) SQL-implementation: A database management system that conforms to this International Standard. y) translation: A method of translating characters in one character repertoire into characters of the same or a different character repertoire. 3.2 Notation The syntactic notation used in this International Standard is an extended version of BNF ("Backus Naur Form" or "Backus Normal Form"). In BNF, each syntactic element of the language is defined by means of a production rule. This defines the element in terms of a for- mula consisting of the characters, character strings, and syntactic elements that can be used to form an instance of it. The version of BNF used in this International Standard makes use of the following symbols: SymbolMeaning Angle brackets delimit character strings that are the names of syntactic elements, the non-terminal symbols of the SQL language. ::= The definition operator. This is used in a production rule to separate the element defined by the rule from its definition. The element being defined appears to the left of the opera- tor and the formula that defines the element appears to the right. Definitions, notations, and conventions 7 X3H2-92-154/DBL CBR-002 3.2 Notation [ ] Square brackets indicate optional elements in a formula. The portion of the formula within the brackets may be explicitly specified or may be omitted. { } Braces group elements in a formula. The portion of the for- mula within the braces shall be explicitly specified. | The alternative operator. The vertical bar indicates that the portion of the formula following the bar is an alterna- tive to the portion preceding the bar. If the vertical bar appears at a position where it is not enclosed in braces or square brackets, it specifies a complete alternative for the element defined by the production rule. If the vertical bar appears in a portion of a formula enclosed in braces or square brackets, it specifies alternatives for the contents of the innermost pair of such braces or brackets. . . . The ellipsis indicates that the element to which it applies in a formula may be repeated any number of times. If the el- lipsis appears immediately after a closing brace "}", then it applies to the portion of the formula enclosed between that closing brace and the corresponding opening brace "{". If an ellipsis appears after any other element, then it applies only to that element. !! Introduces ordinary English text. This is used when the defi- nition of a syntactic element is not expressed in BNF. Spaces are used to separate syntactic elements. Multiple spaces and line breaks are treated as a single space. Apart from those symbols to which special functions were given above, other characters and character strings in a formula stand for themselves. In addition, if the symbols to the right of the definition operator in a produc- tion consist entirely of BNF symbols, then those symbols stand for themselves and do not take on their special meaning. Pairs of braces and square brackets may be nested to any depth, and the alternative operator may appear at any depth within such a nest. A character string that forms an instance of any syntactic element may be generated from the BNF definition of that syntactic element by application of the following steps: 1) Select any one option from those defined in the right hand side of a production rule for the element, and replace the element with this option. 2) Replace each ellipsis and the object to which it applies with one or more instances of that object. 3) For every portion of the string enclosed in square brackets, either delete the brackets and their contents or change the brackets to braces. 8 Database Language SQL X3H2-92-154/DBL CBR-002 3.2 Notation 4) For every portion of the string enclosed in braces, apply steps 1 through 5 to the substring between the braces, then remove the braces. 5) Apply steps 1 through 5 to any non-terminal syntactic element (i.e., name enclosed in angle brackets) that remains in the string. The expansion or production is complete when no further non- terminal symbols remain in the character string. 3.3 Conventions 3.3.1 Informative elements In several places in the body of this International Standard, in- formative notes appear. For example: Note: This is an example of a note. Those notes do not belong to the normative part of this International Standard and conformance to material specified in those notes shall not be claimed. 3.3.2 Specification of syntactic elements Syntactic elements are specified in terms of: - Function: A short statement of the purpose of the element. - Format: A BNF definition of the syntax of the element. - Syntax Rules: A specification of the syntactic properties of the element, or of additional syntactic constraints, not expressed in BNF, that the element shall satisfy, or both. - Access Rules: A specification of rules governing the accessibil- ity of schema objects that shall hold before the General Rules may be successfully applied. - General Rules: A specification of the run-time effect of the element. Where more than one General Rule is used to specify the effect of an element, the required effect is that which would be obtained by beginning with the first General Rule and applying the Rules in numerical sequence unless a Rule is applied that specifies or implies a change in sequence or termination of the application of the Rules. Unless otherwise specified or implied by a specific Rule that is applied, application of General Rules terminates when the last in the sequence has been applied. - Leveling Rules: A specification of how the element shall be supported for each of the levels of SQL. Definitions, notations, and conventions 9 X3H2-92-154/DBL CBR-002 3.3 Conventions The scope of notational symbols is the Subclause in which those symbols are defined. Within a Subclause, the symbols defined in Syntax Rules, Access Rules, or General Rules can be referenced in other rules provided that they are defined before being referenced. 3.3.3 Specification of the Information Schema The objects of the Information Schema in this International Standard are specified in terms of: - Function: A short statement of the purpose of the definition. - Definition: A definition, in SQL, of the object being defined. - Description: A specification of the run-time value of the ob- ject, to the extent that this is not clear from the definition. The definitions used to define the views in the Information Schema are used only to specify clearly the contents of those viewed tables. The actual objects on which these views are based are implementation-dependent. 3.3.4 Use of terms 3.3.4.1 Exceptions The phrase "an exception condition is raised:", followed by the name of a condition, is used in General Rules and elsewhere to indicate that the execution of a statement is unsuccessful, ap- plication of General Rules, other than those of Subclause 12.3, "", and Subclause 20.1, "", may be terminated, diagnostic information is to be made available, and execution of the statement is to have no effect on SQL-data or schemas. The effect on s and SQL descriptor areas of an SQL-statement that terminates with an exception condi- tion, unless explicitly defined by this International Standard, is implementation-dependent. The phrase "a completion condition is raised:", followed by the name of a condition, is used in General Rules and elsewhere to indicate that application of General Rules is not terminated and diagnostic information is to be made available; unless an excep- tion condition is also raised, the execution of the statement is successful. If more than one condition could have occurred as a result of a statement, it is implementation-dependent whether diagnostic infor- mation pertaining to more than one condition is made available. 10 Database Language SQL X3H2-92-154/DBL CBR-002 3.3 Conventions 3.3.4.2 Syntactic containment In a Format, a syntactic element is said to immediately contain a syntactic element if appears on the right-hand side of the BNF production rule for . A syntactic element is said to contain or specify a syntactic element if immediately contains or if immediately contains a syntactic element that contains . In SQL language, an instance A1 of is said to immediately con- tain an instance B1 of if immediately contains and the text of B1 is part of the text of A1. An instance A1 of is said to contain or specify an instance C1 of if A1 immediately con- tains C1 or if A1 immediately contains an instance B1 of that contains C1. An instance A1 of is said to contain an instance B1 of with an intervening if A1 contains B1 and A1 contains an instance C1 of that contains B1. An instance A1 of is said to contain an instance B1 of without an intervening if A1 contains B1 and A1 does not contain an instance C1 of that contains B1. An instance A1 of simply contains an instance B1 of if A1 contains B1 without an intervening instance A2 of or an intervening instance B2 of . If contains , then is said to be contained in and is said to be a containing production symbol for . If simply contains , then is said to be simply contained in and is said to be a simply containing production symbol for . Let A1 be an instance of and let B1 be an instance of . If contains , then A1 is said to contain B1 and B1 is said to be contained in A1. If simply contains , then A1 is said to simply contain B1 and B1 is said to be simply contained in A1. An instance A1 of is the innermost satisfying a condition C if A1 satisfies C and A1 does not contain an instance A2 of that satisfies C. An instance A1 of is the outermost satis- fying a condition C if A1 satisfies C and A1 is not contained in an instance A2 of that satisfies C. If contains a that identifies a view that is defined by a V, then is said to generally contain the contained in V. If contains , then generally contains . If generally contains and generally contains , then generally contains . An instance A1 of directly contains an instance B1 of if A1 contains B1 without an intervening or . Definitions, notations, and conventions 11 X3H2-92-154/DBL CBR-002 3.3 Conventions 3.3.4.3 Terms denoting rule requirements In the Syntax Rules, the term shall defines conditions that are required to be true of syntactically conforming SQL language. When such conditions depend on the contents of the schema, then they are required to be true just before the actions specified by the General Rules are performed. The treatment of language that does not conform to the SQL Formats and Syntax Rules is implementation- dependent. If any condition required by Syntax Rules is not sat- isfied when the evaluation of Access or General Rules is attempted and the implementation is neither processing non-conforming SQL language nor processing conforming SQL language in a non-conforming manner, then an exception condition is raised: syntax error or access rule violation (if this situation occurs during dynamic ex- ecution of an SQL-statement, then the exception that is raised is syntax error or access rule violation in dynamic SQL statement; if the situation occurs during direct invocation of an SQL-statement, then the exception that is raised is syntax error or access rule violation in direct SQL statement). In the Access Rules, the term shall defines conditions that are required to be satisfied for the successful application of the General Rules. If any such condition is not satisfied when the General Rules are applied, then an exception condition is raised: syntax error or access rule violation (if this situation occurs during dynamic execution of an SQL-statement, then the exception that is raised is syntax error or access rule violation in dynamic SQL statement; if the situation occurs during direct invocation of an SQL-statement, then the exception that is raised is syntax error or access rule violation in direct SQL statement). In the Leveling Rules, the term shall defines conditions that are required to be true of SQL language for it to syntactically conform to the specified level of conformance. 3.3.4.4 Rule evaluation order A conforming implementation is not required to perform the exact sequence of actions defined in the General Rules, but shall achieve the same effect on SQL-data and schemas as that sequence. The term effectively is used to emphasize actions whose effect might be achieved in other ways by an implementation. The Syntax Rules and Access Rules for contained syntactic elements are effectively applied at the same time as the Syntax Rules and Access Rules for the containing syntactic elements. The General Rules for contained syntactic elements are effectively applied be- fore the General Rules for the containing syntactic elements. Where the precedence of operators is determined by the Formats of this International Standard or by parentheses, those operators are ef- fectively applied in the order specified by that precedence. Where the precedence is not determined by the Formats or by parentheses, effective evaluation of expressions is generally performed from 12 Database Language SQL X3H2-92-154/DBL CBR-002 3.3 Conventions left to right. However, it is implementation-dependent whether ex- pressions are actually evaluated left to right, particularly when operands or operators might cause conditions to be raised or if the results of the expressions can be determined without completely evaluating all parts of the expression. In general, if some syn- tactic element contains more than one other syntactic element, then the General Rules for contained elements that appear earlier in the production for the containing syntactic element are applied before the General Rules for contained elements that appear later. For example, in the production: ::= the Syntax Rules and Access Rules for , , and are ef- fectively applied simultaneously. The General Rules for are applied before the General Rules for , and the General Rules for are applied after the General Rules for both and . If the result of an expression or search condition can be deter- mined without completely evaluating all parts of the expression or search condition, then the parts of the expression or search condi- tion whose evaluation is not necessary are called the inessential parts. If the Access Rules pertaining to inessential parts are not satisfied, then the syntax error or access rule violation exception condition is raised regardless of whether or not the inessential parts are actually evaluated. If evaluation of the inessential parts would cause an exception condition to be raised, then it is implementation-dependent whether or not that exception condition is raised. 3.3.4.5 Conditional rules Conditional rules are specified with "If" or "Case" conventions. Rules specified with "Case" conventions include a list of con- ditional sub-rules using "If" conventions. The first such "If" sub-rule whose condition is true is the effective sub-rule of the "Case" rule. The last sub-rule of a "Case" rule may specify "Otherwise". Such a sub-rule is the effective sub-rule of the "Case" rule if no preceding "If" sub-rule in the "Case" rule has a true condition. 3.3.4.6 Syntactic substitution In the Syntax and General Rules, the phrase "X is implicit" indi- cates that the Syntax and General Rules are to be interpreted as if the element X had actually been specified. In the Syntax and General Rules, the phrase "the following is implicit: Y" indicates that the Syntax and General Rules are to be interpreted as if a syntactic element containing Y had actually been specified. Definitions, notations, and conventions 13 X3H2-92-154/DBL CBR-002 3.3 Conventions In the Syntax Rules and General Rules, the phrase "former is equiv- alent to latter" indicates that the Syntax Rules and General Rules are to be interpreted as if all instances of former in the element had been instances of latter. If a BNF nonterminal is referenced in a Subclause without speci- fying how it is contained in a BNF production that the Subclause defines, then Case: - If the BNF nonterminal is itself defined in the Subclause, then the reference shall be assumed to be the occurrence of that BNF nonterminal on the left side of the defining production. - Otherwise, the reference shall be assumed to be to a BNF pro- duction in which the particular BNF nonterminal is immediately contained. 3.3.4.7 Other terms Some Syntax Rules define terms, such as T1, to denote named or unnamed tables. Such terms are used as table names or correlation names. Where such a term is used as a correlation name, it does not imply that any new correlation name is actually defined for the denoted table, nor does it affect the scopes of any actual correlation names. An SQL-statement S1 is said to be executed as a direct result of executing an SQL-statement if S1 is the SQL-statement contained in a that has been executed, or if S1 is the value of an referenced by an contained in a that has been executed, or if S1 was the value of the that was associ- ated with an by a and that same is referenced by an contained in a that has been executed. 3.3.5 Descriptors A descriptor is a conceptual structured collection of data that defines the attributes of an instance of an object of a specified type. The concept of descriptor is used in specifying the seman- tics of SQL. It is not necessary that any descriptor exist in any particular form in any database or environment. Some SQL objects cannot exist except in the context of other SQL objects. For example, columns cannot exist except in tables. Those objects are independently described by descriptors, and the de- scriptors of enabling objects (e.g., tables) are said to include the descriptors of enabled objects (e.g., columns or table con- straints). Conversely, the descriptor of an enabled object is said to be included in the descriptor of an enabling object. 14 Database Language SQL X3H2-92-154/DBL CBR-002 3.3 Conventions In other cases, certain SQL objects cannot exist unless some other SQL object exists, even though there is not an inclusion relation- ship. For example, SQL does not permit an assertion to exist if the tables referenced by the assertion do not exist. Therefore, an as- sertion descriptor is dependent on or depends on zero or more table descriptors (equivalently, an assertion is dependent on or depends on zero or more tables). In general, a descriptor D1 can be said to depend on, or be dependent on, some descriptor D2. There are two ways of indicating dependency of one construct on another. In many cases, the descriptor of the dependent construct is said to "include the name of" the construct on which it is de- pendent. In this case "the name of" is to be understood as meaning "sufficient information to identify the descriptor of"; thus an implementor might choose to use a pointer or a concatenation of , , etc. Alternatively, the descrip- tor may be said to include text (e.g., , ). In such cases, whether the implementation includes ac- tual text (with defaults and implications made explicit) or its own style of parse tree is irrelevant; the validity of the descriptor is clearly "dependent on" the existence of descriptors for objects that are referred to in it. The statement that a column "is based on" a domain, is equivalent to a statement that a column "is dependent on" that domain. An attempt to destroy a descriptor may fail if other descriptors are dependent on it, depending on how the destruction is specified. Such an attempt may also fail if the descriptor to be destroyed is included in some other descriptor. Destruction of a descriptor results in the destruction of all descriptors included in it, but has no effect on descriptors on which it is dependent. 3.3.6 Index typography In the Index to this International Standard, the following conven- tions are used: - Index entries appearing in boldface indicate the page where the word, phrase, or BNF nonterminal was defined; - Index entries appearing in italics indicate a page where the BNF nonterminal was used in a Format; and - Index entries appearing in roman type indicate a page where the word, phrase, or BNF nonterminal was used in a heading, Function, Syntax Rule, Access Rule, General Rule, Leveling Rule, Table, or other descriptive text. Definitions, notations, and conventions 15 X3H2-92-154/DBL CBR-002 3.4 Object identifier for Database Language SQL 3.4 Object identifier for Database Language SQL Function The object identifier for Database Language SQL identifies the characteristics of an SQL-implementation to other entities in an open systems environment. Format ::= ::=