ISO-ANSI Working Draft Database Language SQL (SQL3) February 1993 ISO and ANSI SQL3 Working Draft-February 5, 1993 Digital Equipment Corporation Maynard, Massachusetts Contents Page Foreword.........................................................xiii Introduction.....................................................xvii 1 Scope ........................................................ 1 2 Normative references ......................................... 3 3 Definitions, notations, and conventions ...................... 7 3.1 Definitions ................................................ 7 3.1.1Definitions taken from ISO/IEC DIS 10646 ................... 7 3.1.2Definitions taken from ISO 8601 ............................ 7 3.1.3Definitions provided in this Standard ...................... 7 3.2 Notation ...................................................12 3.3 Conventions ................................................13 3.3.1Informative elements .......................................14 3.3.2Specification of syntactic elements ........................14 3.3.3Specification of the Information Schema ....................15 3.3.4Use of terms ...............................................15 3.3.4Exceptions .................................................15 3.3.4Syntactic containment ......................................16 3.3.4Terms denoting rule requirements ...........................17 3.3.4Rule evaluation order ......................................17 3.3.4Conditional rules ..........................................18 3.3.4Syntactic substitution .....................................18 3.3.4Other terms ................................................19 3.3.5Descriptors ................................................19 3.3.6Index typography ...........................................20 3.4 Object identifier for Database Language SQL ................21 4 Concepts .....................................................23 4.1 Data types .................................................23 4.2 Character strings ..........................................24 4.2.1Character strings and collating sequences ..................25 4.2.2Operations involving character strings .....................26 4.2.2Operators that operate on character strings and return char- acter strings...............................................27 4.2.2Other operators involving character strings ................28 4.2.3Rules determining collating sequence usage .................28 4.3 Bit strings ................................................31 4.3.1Bit string comparison and assignment .......................32 4.3.2Operations involving bit strings ...........................32 4.3.2Operators that operate on bit strings and return bit strings ............................................................32 4.3.2Other operators involving bit strings ......................32 ii (ISO-ANSI working draft) Database Language SQL (SQL3) X3H2-93-091 and YOK-003 4.4 Numbers ....................................................32 4.4.1Characteristics of numbers .................................33 4.4.2Operations involving numbers ...............................34 4.5 Enumerated types ...........................................34 4.6 Boolean types ..............................................35 4.7 Datetimes and intervals ....................................35 4.7.1Datetimes ..................................................36 4.7.2Intervals ..................................................37 4.7.3Operations involving datetimes and intervals ...............39 4.8 Object identifier type .....................................40 4.9 Abstract data types ........................................41 4.9.1Encapsulation ..............................................42 4.9.2Subtypes and supertypes ....................................42 4.9.3Type templates .............................................43 4.10 Collection types ...........................................44 4.10.Set type ...................................................45 4.10.Multiset type ..............................................45 4.10.List type ..................................................46 4.11 Type conversions and mixing of data types ..................46 4.12 Domains ....................................................48 4.13 Nulls ......................................................49 4.14 Columns and attributes .....................................50 4.15 Tables, sets, and lists ....................................51 4.16 Tables .....................................................52 4.17 Integrity constraints ......................................58 4.17.Checking of constraints ....................................58 4.17.Table constraints ..........................................59 4.17.Domain constraints .........................................61 4.17.Assertions .................................................61 4.18 Functional dependencies and candidate keys .................61 4.18.General rules and definitions ..............................62 4.18.Functional dependencies in a base table ....................63 4.18.Functional dependencies in .......64 4.18.Functional dependencies in a ................64 4.18.Functional dependencies in the result of a ...65 4.18.Functional dependencies in the result of a ..65 4.18.Functional dependencies in the result of a ............................................................66 4.18.Functional dependencies in the result of a ............................................................66 4.18.Functional dependencies in a .........67 4.18.Functional dependencies in a ............67 4.18.Functional dependencies in a .............68 4.18.Selection of primary key or preferred candidate key ........68 4.19 Triggers ...................................................69 Table of Contents iii X3H2-93-091 and YOK-003 4.19.Triggered actions ..........................................70 4.19.Execution of triggered actions .............................70 4.20 SQL-schemas ................................................70 4.21 Catalogs ...................................................71 4.22 Clusters of catalogs .......................................72 4.23 SQL-data ...................................................72 4.24 SQL-environment ............................................72 4.25 Modules ....................................................73 4.26 Routines ...................................................74 4.27 Parameters .................................................76 4.27.Status parameters ..........................................77 4.27.Data parameters ............................................78 4.27.Indicator parameters .......................................78 4.28 Diagnostics area ...........................................78 4.29 Standard programming languages .............................79 4.30 Cursors ....................................................80 4.31 SQL-statements .............................................82 4.31.Classes of SQL-statements ..................................82 4.31.SQL-statements classified by function ......................83 4.31.Embeddable SQL-statements ..................................87 4.31.Preparable and immediately executable SQL-statements .......89 4.31.Directly executable SQL-statements .........................90 4.31.SQL-statements and transaction states ......................91 4.31.Asynchronous execution .....................................93 4.32 Embedded syntax ............................................94 4.33 SQL dynamic statements .....................................94 4.34 Direct invocation of SQL ...................................97 4.35 Privileges and roles .......................................98 4.36 SQL-agents .................................................101 4.37 SQL-transactions ...........................................101 4.38 SQL-connections ............................................106 4.39 SQL-sessions ...............................................107 4.40 Client-server operation ....................................109 4.41 Information Schema .........................................110 4.42 Leveling ...................................................110 4.43 SQL Flagger ................................................112 5 Lexical elements .............................................115 5.1 ...................................115 5.2 and ....................................118 5.3 ..................................................128 5.4 Names and identifiers ......................................139 iv (ISO-ANSI working draft) Database Language SQL (SQL3) X3H2-93-091 and YOK-003 6 Scalar expressions ...........................................151 6.1 ................................................151 6.2 and ...........161 6.3 ...........................................168 6.4 ......................................170 6.5
..........................................172 6.6 and ..................175 6.7 ...............................179 6.8 ...................................183 6.9 ....................................187 6.10 ..................................195 6.11 ............................197 6.12 ..........................................199 6.13 .......................................202 6.14 .........................................221 6.15 .................................225 6.16 ..................................228 6.17 ..............................233 6.18 ................................235 6.19 ................................239 6.20
...................................243 6.21 .................................245 7 Query expressions ............................................249 7.1 ....................................249 7.2
..................................253 7.3 ....................................255 7.4 ...............................257 7.5 ...................................259 7.6
.........................................261 7.7 ..............................................263 7.8 .............................................265 7.9 .............................................277 7.10 ..........................................279 7.11 ............................................281 7.12 ......................................283 7.13 .........................................295 7.14 ..........................................310 7.15 , , and
....319 8 Predicates ...................................................321 8.1 ................................................321 8.2 .....................................323 8.3 ........................................329 8.4 .............................................331 8.5 ...........................................333 Table of Contents v X3H2-93-091 and YOK-003 8.6 ........................................337 8.7 ...........................................349 8.8 ..........................352 8.9 .........................................354 8.10 .........................................355 8.11 ..........................................356 8.12 .......................................359 8.13 .....................................361 8.14 .......................................364 8.15 .......................................366 8.16 ........................................369 8.17 .........................................371 9 Data assignment rules and function determination .............373 9.1 Retrieval assignment .......................................373 9.2 Store assignment ...........................................377 9.3 Set operation result data types and nullabilities ..........381 9.4 Subject routine determination ..............................384 10 Additional common elements ...................................385 10.1 .......................................385 10.2 ..........................................390 10.3 .......................................393 10.4 .................................403 10.5 ...............................................406 10.6 ..............................412 10.7 ...........................................415 10.8 and ...416 11 Schema definition and manipulation ...........................419 11.1 ........................................419 11.2 ....................................423 11.3
.........................................426 11.4 ........................................439 11.5 ...........................................447 11.6
..............................453 11.7 .............................455 11.8 ........................458 11.9 ..............................469 11.10 ....................................472 11.11 ....................................474 11.12 ..................................476 11.13 ................................478 11.14 ...............................479 11.15 ................................480 11.16 ...................................483 vi (ISO-ANSI working draft) Database Language SQL (SQL3) X3H2-93-091 and YOK-003 11.17 ....................................486 11.18 ...................................488 11.19 ..........................489 11.20 .........................490 11.21 .....................................492 11.22 ..........................................494 11.23 ......................................500 11.24 ........................................502 11.25 ...................................505 11.26 ................................506 11.27 ...............................507 11.28 .........................508 11.29 ........................509 11.30 ....................................511 11.31 ....................................516 11.32 ................................518 11.33 .................................520 11.34 .............................524 11.35 .....................................526 11.36 .................................533 11.37 ...................................535 11.38 ...............................539 11.39 .....................................541 11.40 .................................545 11.41 .......................................546 11.42 ...................................563 11.43 ...................................565 11.44 ............................568 11.45 ..................................570 11.46 .................................580 11.47 .................................582 11.48 .............................584 11.49 .................................586 11.50 ........................................588 11.51 ....................................590 11.52 ..........................................591 11.53 ..........................................596 11.54 .....................................597 11.55 ....................................599 11.56 ......................................600 11.57 .........................................601 Table of Contents vii X3H2-93-091 and YOK-003 12 Standard type templates and types ............................613 12.1 Introduction ...............................................613 12.2 SQL type templates and types ...............................613 12.2.Definitions used in this Subclause .........................614 12.2.Concepts used in this Subclause ............................614 12.2.SQL_Table type template ....................................615 12.2.SQL_Set type template ......................................620 12.2.SQL_List type template .....................................622 12.2.SQL_Empty_Table type .......................................625 12.2.SQL_Empty_Set type .........................................627 12.2.SQL_Empty_List type ........................................629 13 Module .......................................................631 13.1 ...................................................631 13.2 ..................................................636 13.3 ..................................654 13.4 Data type correspondences ..................................659 13.5 Rules for externally-invoked s ....................675 14 Control statements ...........................................687 14.1 ...........................................687 14.2 .........................................688 14.3 .......................................690 14.4 ........................................694 14.5 ....................................696 14.6 .................................697 14.7 .....................................699 14.8 ............................................701 14.9 ........................................703 14.10 ...........................................705 14.11 .............................................707 14.12 ..........................................709 14.13 ...........................................710 14.14 .........................................714 14.16 .......................................715 15 Data manipulation ............................................717 15.1 ...........................................717 15.2 ...........................................723 15.3 ..........................................726 15.4 ..........................................730 15.5
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 . 16 (ISO-ANSI working draft) Database Language SQL (SQL3) X3H2-93-091 and YOK-003 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 American International Standard or by parentheses, those operators are effectively applied in the order specified by that precedence. Where the precedence is Definitions, notations, and conventions 17 X3H2-93-091 and YOK-003 3.3 Conventions not determined by the Formats or by parentheses, effective eval- uation of expressions is generally performed from left to right. However, it is implementation-dependent whether expressions 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 syntactic 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. 18 (ISO-ANSI working draft) Database Language SQL (SQL3) X3H2-93-091 and YOK-003 3.3 Conventions 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. 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 associated with an by a and that same is referenced by an contained in a that has been executed. An S1 is said to be executed as an indirect result of executing an if S1 is a that is contained in some and a triggering is 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. Definitions, notations, and conventions 19 X3H2-93-091 and YOK-003 3.3 Conventions 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. 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 , , et cetera. Alternatively, the de- scriptor may be said to include text (e.g., , ). In such cases, whether the implementation in- cludes actual 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 American International Standard, the following conventions 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 20 (ISO-ANSI working draft) Database Language SQL (SQL3) X3H2-93-091 and YOK-003 3.3 Conventions - 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. 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 ::= ::= ::= iso | 1 | iso 1 ::= standard | 0 | standard 0 ::= 9075 ::= ::= <1987> | <1989> | <1992> <1987> ::= 0 | edition1987 0 <1989> ::= <1989 base> <1989 package> <1989 base> ::= 1 | edition1989 1 <1989 package> ::= | ::= 0 | IntegrityNo 0 ::= 1 | IntegrityYes 1 <1992> ::= 2 | edition1992 2 ::= | | ::= 0 | Low 0 ::= 1 | Intermediate 1 ::= 2 | High 2 Definitions, notations, and conventions 21 X3H2-93-091 and YOK-003 3.4 Object identifier for Database Language SQL Syntax Rules 1) An of shall not be specified unless the is specified as <1992>. 2) The value of identifies the level at which conformance is claimed as follows: a) If specifies <1992>, then Case: i) , then Entry SQL level. ii) , then Intermediate SQL level. iii) , then Full SQL level. b) Otherwise: i) , then level 1. ii) , then level 2. 3) A specification of <1989 package> as implies that the integrity enhancement feature is not implemented. A specification of <1989 package> as implies that the integrity enhancement feature is implemented. 22 (ISO-ANSI working draft) Database Language SQL (SQL3) X3H2-93-091 and YOK-003 4 Concepts 4.1 Data types A data type is a set of representable values. The logical represen- tation of a value is a . The physical representation of a value is implementation-dependent. | | SQL supports two sorts of data types: primitive data types and | abstract data types. Primitive data types are sometimes called | built-in data types or predefined types. Abstract data types can | be defined by a standard, by an implementation, or by an applica- | tion. Abtract data types have no corresponding s and thus | no logical representation. Abstract data types are summarized in | Subclause 11.44, "". | ___________________________________________________________________ | ANSI Only-SQL3 | ___________________________________________________________________ | | SQL also supports the collection data types of set, multiset and | list. A collection is a multiset of values or objects called ele- | ments. The elements may be primitive data type elements, abstract | data type elements or collections. A set is an unordered collection | of distinct elements, with no duplicates. A multiset is similar to | a set except that duplicates are permitted. A list is similar to a | multiset except that the elements are ordered. | ___________________________________________________________________ | | | Values corresponding to primitive data types are also primitive in | that they have no logical subdivision within this | American | International | standard. | | Values corresponding to abstract data types may be primitive or | complex, depending only on the definition of the abstract data | type. | Values are either a null value or a non-null value. A null value is an implementation-dependent special value that is distinct from all non-null values of the associated data type. There is effectively only one null value and that value is a member of every SQL data type. There is no for a null value although the keyword NULL is used in some places to indicate that a null value is desired. Concepts 23 X3H2-93-091 and YOK-003 4.1 Data types SQL defines distinct data types named by the following s: CHARACTER, CHARACTER VARYING, BIT, BIT VARYING, NUMERIC, DECIMAL, INTEGER, SMALLINT, ENUMERATED, FLOAT, REAL, DOUBLE PRECISION, | BOOLEAN, DATE, TIME, TIMESTAMP, INTERVAL, and REF.  1 paragraph deleted. For reference purposes, the data types CHARACTER and CHARACTER VARYING, are collectively referred to as character string types. The data types BIT and BIT VARYING are collectively referred to as bit string types. Character string types and bit string types are collectively referred to as string types and values of string types are referred to as strings. The data types NUMERIC, DECIMAL, INTEGER and SMALLINT are collectively referred to as exact numeric types. The data types FLOAT, REAL, and DOUBLE PRECISION are col- lectively referred to as approximate numeric types. Exact numeric types and approximate numeric types are collectively referred to as numeric types. Values of numeric type are referred to as numbers. The data types DATE, TIME, and TIMESTAMP are collectively referred to as datetime types. Values of datetime types are referred to as datetimes. The data type INTERVAL is referred to as an interval type. Values of interval types are called intervals. Each data type has an associated data type descriptor; the contents of a data type descriptor are determined by the specific data type that it describes. A data type descriptor includes an identifica- tion of the data type and all information needed to characterize an instance of that data type. Subclause 6.1, "", describes the semantic properties of each data type. Each host language has its own data types, which are separate and distinct from SQL data types, even though similar names may be used to describe the data types. Mappings of SQL data types to data | types in host languages are described in Subclause 13.2, "", and Subclause 21.1, "". Not every SQL data type has a corresponding data type in every host language. 4.2 Character strings A character string data type is described by a character string data type descriptor. A character string data type descriptor con- tains: - the name of the specific character string data type (CHARACTER or CHARACTER VARYING; NATIONAL CHARACTER and NATIONAL CHARACTER VARYING are represented as CHARACTER and CHARACTER VARYING, respectively); - the length or maximum length in characters of the character string data type; 24 (ISO-ANSI working draft) Database Language SQL (SQL3) X3H2-93-091 and YOK-003 4.2 Character strings - the catalog name, schema name, and character set name of the character set of the character string data type; and - the catalog name, schema name, and collation name of the colla- tion of the character string data type. Character sets fall into three categories: those defined by na- tional or international standards, those provided by implemen- tations, and those defined by applications. All character sets, however defined, always contain the character. Character sets defined by applications can be defined to "reside" in any schema chosen by the application. Character sets defined by stan- dards or by implementations reside in the Information Schema (named INFORMATION_SCHEMA) in each catalog, as do collations defined by standards and collations and form-of-use conversions defined by implementations. The SQL_TEXT specifies the name of a character repertoire and implied form-of- use that can represent every character that is in and all other characters that are in character sets supported by the implementation. 4.2.1 Character strings and collating sequences A character string is a sequence of characters chosen from the same character repertoire. The character repertoire from which the characters of a particular string are chosen may be specified explicitly or implicitly. A character string has a length, which is the number of characters in the sequence. The length is 0 or a positive integer. All character strings of a given character repertoire are compara- ble. A collating sequence, also known as a collation, is a set of rules determining comparison of character strings in a particular char- acter repertoire. There is a default collating sequence for each character repertoire, but additional collating sequences can be defined for any character repertoire. Note: A column may be defined as having a default collating se- quence. This default collating sequence for the column may be different from the default collating sequence for its character repertoire, e.g., if the is specified in the . It will be clear from context when the term "default collating sequence" is used whether it is meant for a column or for a character repertoire. Given a collating sequence, two character strings are identical if and only if they are equal in accordance with the comparison rules specified in Subclause 8.2, "". The collat- ing sequence used for a particular comparison is determined as in Subclause 4.2.3, "Rules determining collating sequence usage". Concepts 25 X3H2-93-091 and YOK-003 4.2 Character strings The s NATIONAL CHARACTER are used to specify a character string data type with a particular implementation-defined character repertoire. Special syntax (N"string") is provided for representing literals in that character repertoire. A character set is described by a character set descriptor. A char- acter set descriptor includes: - the name of the character set or character repertoire, - if the character set is a character repertoire, then the name of the form-of-use, | - an indication of what characters are in the character set, and | | - whether or not the character set uses the DEFAULT collation for | its character repertoire, and | | - if the character set does not utilize the DEFAULT collation for | its character repertoire, then the conained | in the character set's , if any, the | contained in the character set's or , if any, and whether | or not DESC was specified in the reference to the collation. For every character set, there is at least one collation. A colla- tion is described by a collation descriptor. A collation descriptor includes: - the name of the collation, | - the name of the character repertoire on which the collation operates, - whether the collation has the NO PAD or the PAD SPACE attribute, | and | | - whether or not this collation utilizes the DEFAULT collation for | its character repertoire, | | - if the collation does not utilize the DEFAULT collation for its | character repertoire, then the contained in | the collation's , if any, the contained in the collation's , if any, | and whether or not DESC was specified in the definition of the | collation. 4.2.2 Operations involving character strings 26 (ISO-ANSI working draft) Database Language SQL (SQL3) X3H2-93-091 and YOK-003 4.2 Character strings 4.2.2.1 Operators that operate on character strings and return character strings is an operator, |, that returns the char- acter string made by joining its character string operands in the order given. is a triadic function, SUBSTRING, that returns a string extracted from a given string according to a given numeric starting position and a given numeric length. Truncation occurs when the implied starting and ending positions are not both within the given string. is a pair of functions for converting all the lower case characters in a given string to upper case (UPPER) or all the upper case ones to lower case (LOWER), useful only in connection with strings that may contain s. is a function that invokes an installation- supplied form-of-use conversion to return a character string S2 derived from a given character string S1. It is intended, though not enforced by this American International Standard, that S2 be exactly the same sequence of characters as S1, but encoded according some different form-of-use. A typical use might be to convert a character string from two-octet UCS to one-octet Latin1 or vice versa. is a function that returns its first string ar- gument with leading and/or trailing pad characters removed. The second argument indicates whether leading, or trailing, or both leading and trailing pad characters should be removed. The third argument specifies the pad character that is to be removed. is a function for changing each charac- ter of a given string according to some many-to-one or one-to-one mapping between two not necessarily distinct character sets. The mapping, rather than being specified as part of the function, is some external function identified by a . For any pair of character sets, there are zero or more translations that may be invoked by a . A translation is described by a translation descriptor. A translation descriptor includes: - the name of the translation, - the name of the character set from which it translates, - the name of the character set to which it translates, and - an indication of how the translation is performed. Concepts 27 X3H2-93-091 and YOK-003 4.2 Character strings 4.2.2.2 Other operators involving character strings returns the length of a given character string, as an integer, in characters, octets, or bits according to the choice of function. determines the first position, if any, at which one string, S1, occurs within another, S2. If S1 is of length zero, then it occurs at position 1 for any value of S2. If S1 does not occur in S2, then zero is returned. uses the triadic operator LIKE (or the inverse, NOT LIKE), operating on three character strings and returning a Boolean. LIKE determines whether or not a character string "matches" a given "pattern" (also a character string). The char- acters "%" (percent) and "_" (underscore) have special meaning when they occur in the pattern. The optional third argument is a charac- ter string containing exactly one character, known as the "escape character", for use when a percent or underscore is required in the pattern without its special meaning. 4.2.3 Rules determining collating sequence usage The rules determining collating sequence usage for character strings are based on the following: - Expressions where no columns are involved (e.g., literals, host variables) are by default compared using the default collating sequence for their character repertoire. Note: The default collating sequence for a character repertoire is defined in Subclause 10.6, "", and Subclause 11.33, "". - When columns are involved (e.g., comparing two columns, or com- paring a column to a literal), by default the default collating sequence of the columns involved is used so long as the columns have the same default collating sequence. - When columns are involved having different default collating sequences, explicit specification of the collating sequence in the expression is required via the when the expression participates in a comparison. - Any explicit specification of collating sequence in an expres- sion overrides any default collating sequence. To formalize this, s effectively have a coercibility attribute. This attribute has the values Coercible, Implicit, No collating sequence, and Explicit. s with the Coercible, Implicit, or Explicit attributes have a collating sequence. 28 (ISO-ANSI working draft) Database Language SQL (SQL3) X3H2-93-091 and YOK-003 4.2 Character strings A consisting of a column reference has the Implicit attribute, with collating sequence as defined when the column was created. A consisting of a value other than a column (e.g., a host variable or a literal) has the Coercible attribute, with the default collation for its char- acter repertoire. A simply containing a has the Explicit attribute, with the collating sequence specified in the . Note: When the coercibility attribute is Coercible, the collating sequence is uniquely determined as specified in Subclause 8.2, "". The tables below define how the collating sequence and the co- ercibility attribute is determined for the result of any monadic or dyadic operation. Table 1, "Collating coercibility rules for monadic operators", shows the collating sequence and coercibility rules for monadic operators, and Table 2, "Collating coercibil- ity rules for dyadic operators", shows the collating sequence and coercibility rules for dyadic operators. Table 3, "Collating se- quence usage for comparisons", shows how the collating sequence is determined for a particular comparison. _____Table_1-Collating_coercibility_rules_for_monadic_operators____ Operand Coercibility Result Coercibility _____and_Collating_Sequence_____ _____and_Collating_Sequence___ | Collating | Collating | |_Coercibility______Sequence______|_Coercibility______Sequence_____| | | | | Coercible | default | Coercible | default | | | | | | | Implicit | X | Implicit | X | | | | | | | Explicit | X | Explicit | X | | | | | | |_______No_collati|g_sequence_____|______No_collatin|_sequence_____| | | | | | _____Table_2-Collating_coercibility_rules_for_dyadic_operators_____ Result Coercibility Operand 1 Coercibility Operand 2 Coercibility and Collating _and_Collating_Sequence _and_Collating_Sequence ___Sequence___ | Collating | Collating | Col|ating |_Coercibility_Sequence__|_Coercibility_Sequence__|__CoercibilitySe|uence | | | | | Coercible | default | Coercible | default | Coercible| def|ult | | | | | | | | Coercible | default | Implicit | Y | Implicit | Y | | | | | | | | | Coercible | default | No collati|g sequence | No colla|ing | sequence Concepts 29 X3H2-93-091 and YOK-003 4.2 Character strings _Table_2-Collating_coercibility_rules_for_dyadic_operators_(Cont.)_ Result Coercibility Operand 1 Coercibility Operand 2 Coercibility and Collating _and_Collating_Sequence _and_Collating_Sequence ___Sequence___ | Collating | Collating | Col|ating |_Coercibility_Sequence__|_Coercibility_Sequence__|__CoercibilitySe|uence | | | | | Coercible | default | Explicit | Y | Explicit | Y | | | | | | | | | Implicit | X | Coercible | default | Implicit | X | | | | | | | | | Implicit | X | Implicit | X | Implicit | X | | | | | | | | | Implicit | X | Implicit | Y /= X | No colla|ing | sequence | Implicit | X | No collati|g sequence | No collating | | | | | | sequence | | | | | | | | Implicit | X | Explicit Y | Explicit Y | | | | | | | No collati|g sequence | Any, | Any | No colla|ing | except sequence Explicit | No collating sequence | Explicit | X | Explicit X | | | | | | | Explicit X | Coercible | default | Explicit | X | | | | | | | | Explicit | X | Implicit | Y | Explicit | X | | | | | | | | | Explicit | X | No collati|g sequence | Explicit | X | | | | | | | | | Explicit | X | Explicit X | Explicit | X | | | | | | | | Explicit | X | Explicit | Y /= X | Not permi|ted:| ____________________________________________________invalid_syntax_ |__________Ta|le_3-Collat|ng_sequence_|sage_for_co|parisons________| Comparand 1 Comparand 2 Coercibility and Coercibility and _Collating_Sequence _Collating_Sequence | | | Collating Sequence | | Collatin| Collatin| Used For The | |_CoercibilitSequence|_CoercibilitSequence|__Comparison____________| | | | | | Coercible| default | Coercible| default | default | | | | | | | | Coercible| default | Implicit | Y | Y | | | | | | | | Coercible| default | No co|lating | Not permitted: invalid| sequence syntax | Coercible| default | Explicit Y | Y | | | | | | 30 (ISO-ANSI working draft) Database Language SQL (SQL3) X3H2-93-091 and YOK-003 4.2 Character strings ______Table_3-Collating_sequence_usage_for_comparisons_(Cont.)_____ Comparand 1 Comparand 2 Coercibility and Coercibility and _Collating_Sequence _Collating_Sequence | | | Collating Sequence | | Collatin| Collatin| Used For The | |_CoercibilitSequence|_CoercibilitSequence|__Comparison____________| | | | | | Implicit | X | Coercible| default | X | | | | | | | | Implicit | X | Implicit | X | X | | | | | | | | Implicit | X | Implicit | Y /= X | Not permitted: invalid| syntax | Implicit | X | No co|lating | Not permitted: invalid| | | | seq|ence | syntax | | | | | | | | Implicit | X | Explicit Y | Y | | | | | | | No co|lating | Any | Any | Not permitted: invalid| sequence except syntax Explicit | No collating | Explicit | X | X | | sequence | | | | | | | | | | Explicit X | Coercible| default | X | | | | | | | Explicit | X | Implicit | Y | X | | | | | | | | Explicit | X | No co|lating | X | sequence | Explicit | X | Explicit X | X | | | | | | | Explicit | X | Explicit | Y /= X | Not permitted: invalid| ____________________________________________syntax_________________ |For n-adic|operation| (e.g., ) with operands X1, | X2, . . . , n , the collating sequence is effectively determined by considering X1 and X2, then combining this result with X3, and so on. 4.3 Bit strings A bit string is a sequence of bits, each having the value of 0 or 1. A bit string has a length, which is the number of bits in the string. The length is 0 or a positive integer. A bit string data type is described by a bit string data type de- scriptor. A bit string data type descriptor contains: - the name of the specific bit string data type (BIT or BIT VARYING); and Concepts 31 X3H2-93-091 and YOK-003 4.3 Bit strings - the length of the bit string data type (in bits). 4.3.1 Bit string comparison and assignment All bit strings are mutually comparable. A bit string is identical to another bit string if and only if it is equal to that bit string in accordance with the comparison rules specified in Subclause 8.2, "". Assignment of a bit string to a bit string variable is performed from the most significant bit to the least significant bit in the source string to the most significant bit in the target string, one bit at a time. 4.3.2 Operations involving bit strings 4.3.2.1 Operators that operate on bit strings and return bit strings is an operator, |, that returns the bit string made by concatenating the two bit string operands in the order given. is a triadic function identical in syntax and semantics to except that the first argument and the returned value are both bit strings. 4.3.2.2 Other operators involving bit strings returns the length (as an integer number of octets or bits according to the choice of function) of a given bit string. determines the first position, if any, at which one string, S1, occurs within another, S2. If S1 is of length zero, then it occurs at position 1 for any value of S2. If S1 does not occur in S2, then zero is returned. 4.4 Numbers A number is either an exact numeric value or an approximate numeric value. Any two numbers are mutually comparable to each other. A numeric data type is described by a numeric data type descriptor. A numeric data type descriptor contains: - the name of the specific numeric data type (NUMERIC, DECIMAL, INTEGER, SMALLINT, FLOAT, REAL, or DOUBLE PRECISION); - the precision of the numeric data type; 32 (ISO-ANSI working draft) Database Language SQL (SQL3) X3H2-93-091 and YOK-003 4.4 Numbers - the scale of the numeric data type, if it is an exact numeric data type; and - an indication of whether the precision (and scale) are expressed in decimal or binary terms. 4.4.1 Characteristics of numbers An exact numeric value has a precision and a scale. The precision is a positive integer that determines the number of significant digits in a particular radix (binary or decimal). The scale is a non-negative integer. A scale of 0 indicates that the number is an integer. For a scale of S, the exact numeric value is the integer value of the significant digits multiplied by 10-S. An approximate numeric value consists of a mantissa and an expo- nent. The mantissa is a signed numeric value, and the exponent is a signed integer that specifies the magnitude of the mantissa. An approximate numeric value has a precision. The precision is a posi- tive integer that specifies the number of significant binary digits in the mantissa. The value of an approximate numeric value is the mantissa multiplied by 10x, where x is the exponent. Whenever an exact or approximate numeric value is assigned to a data item or parameter representing an exact numeric value, an approximation of its value that preserves leading significant dig- its after rounding or truncating is represented in the data type of the target. The value is converted to have the precision and scale of the target. The choice of whether to truncate or round is implementation-defined. An approximation obtained by truncation of a numerical value N for an T is a value V representable in T such that N is not closer to zero than the numerical value of V and such that the absolute value of the difference between N and the numer- ical value of V is less than the absolute value of the difference between two successive numerical values representable in T. An approximation obtained by rounding of a numerical value N for an T is a value V representable in T such that the absolute value of the difference between N and the nu- merical value of V is not greater than half the absolute value of the difference between two successive numerical values repre- sentable in T. If there are more than one such values V, then it is implementation-defined which one is taken. All numerical values between the smallest and the largest value, inclusive, representable in a given exact numeric type have an approximation obtained by rounding or truncation for that type; it is implementation-defined which other numerical values have such approximations. Concepts 33 X3H2-93-091 and YOK-003 4.4 Numbers An approximation obtained by truncation or rounding of a numerical value N for an T is a value V repre- sentable in T such that there is no numerical value representable in T and distinct from that of V that lies between the numerical value of V and N, inclusive. If there are more than one such values V then it is implementation- defined which one is taken. It is implementation-defined which numerical values have approximations obtained by rounding or trun- cation for a given approximate numeric type. Whenever an exact or approximate numeric value is assigned to a data item or parameter representing an approximate numeric value, an approximation of its value is represented in the data type of the target. The value is converted to have the precision of the target. Operations on numbers are performed according to the normal rules of arithmetic, within implementation-defined limits, except as provided for in Subclause 6.15, "". 4.4.2 Operations involving numbers As well as the usual arithmetic operators, plus, minus, times, divide, unary plus, and unary minus, there are the following func- tions that return numbers: - (see Subclause 4.2.2, "Operations involv- ing character strings", and Subclause 4.3.2, "Operations involv- ing bit strings") takes two strings as arguments and returns an integer; - (see Subclause 4.2.2, "Operations involving character strings", and Subclause 4.3.2, "Operations involv- ing bit strings") operates on a string argument and returns an integer; - (see Subclause 4.7.3, "Operations involving datetimes and intervals") operates on a datetime or interval argument and returns an integer. 4.5 Enumerated types An enumerated type is a list of distinct identifiers that repre- sents an ordered set of values. All values for a given enumerated type are comparable. Values of two different enumerated types are not comparable. An enumerated data type is described by an enumerated data type descriptor. An enumerated data type descriptor contains: - the name of the enumerated data type (ENUMERATED); 34 (ISO-ANSI working draft) Database Language SQL (SQL3) X3H2-93-091 and YOK-003 4.5 Enumerated types - the number of enumeration names that participate in the enumer- ated type; and - a list of the enumeration names that participate in the enumer- ated type. 4.6 Boolean types A boolean is a data type that may take on the values true and false. A boolean data type is described by a boolean data type descriptor. A boolean data type descriptor contains: - the name of the boolean data type (BOOLEAN). 4.7 Datetimes and intervals A datetime data type is described by a datetime data type descrip- tor. An interval data type is described by an interval data type descriptor. A datetime data type descriptor contains: - the name of the specific datetime data type (DATE, TIME, TIMESTAMP, TIME WITH TIME ZONE, or TIMESTAMP WITH TIME ZONE); and - the value of the