ISO/IEC 9075:1992/Cor 1:1996

Information technology — Database languages — SQL — Technical Corrigendum 1

Technologies de l'information — Langages de base de données — SQL — Rectificatif technique 1

General Information

• Status: Withdrawn
• Publication Date: 14-Aug-1996
• Withdrawal Date: 14-Aug-1996
• ICS: 35.060 - Languages used in information technology
• Technical Committee: ISO/IEC JTC 1/SC 32 - Data management and interchange
• Drafting Committee: ISO/IEC JTC 1/SC 32 - Data management and interchange
• Current Stage: 9599 - Withdrawal of International Standard
• Completion Date: 16-Dec-1999

Relations

• Corrects: ISO/IEC 9075:1992 - Information technology — Database languages — SQL
• Effective Date: 06-Jun-2022
• Child: ISO/IEC 9075:1992 - Information technology — Database languages — SQL
• Effective Date: 15-Apr-2008

Standards Content (Sample)

INTERNATIONAL STANDARD ISOJIEC 9075:1992
TECHNICAL CORRIGENDUM 1
Published 1996-08-l 5
INTERNATIONAL ORGANIZATION FOR STANDARDlZATlON*ME~~YHAPO,flHAFI OPTAHM3AUMFl l-l0 CTAHflAPTM3AlJWl* ORGANISATION INTERNATIONALE DE NORMALISATION
INTERNATIONAL ELECTROTECHNICAL COMMISSION*ME)) Information technology - Database languages - SQL
TECHNICAL CORRIGENDUM 1
Technologies de I ’informa tion - Langages de bases de don&es - SQL
RECTIFICATIF TECHNIQUE I
Technical Corrigendum 1 to International Standard lSO/IEC 9075:1992 was prepared by Joint Technical Committee
lSO/IEC JTC 1, Information technology, Subcommittee SC 21, Open systems interconnection, data management
and open distributed processing.
Relation to previous technical corrigenda:
This Corrigendum contains the cumulative set of corrections to ISO/IEC 9075: 1992.
It cancels and replaces Technical Corrigendum 1: 1994.
Statement of purpose for rationale:
A statement indicating the rationale for each change to ISO/IEC 9075: 1992 is included. This is to inform the users
of that standard as to the reason why it was judged necessary to change the original wording. In many cases the
reason is editorial or to clarify the wording; in some cases it is to correct an error or an omission in the original
wording.
Notes on rule numbering:
This Corrigendum introduces some new Syntax, Access, General and Leveling Rules. The new Rules in this
Corrigendum have been numbered as follows:
Rules inserted between, for example, Rules 7) and 8) (in ISO/IEC 9075:1992) are numbered 7.1), 7.2), etc. [or
7) a. l), 7) a.2), etc.]. Those inserted before Rule 1) are numbered O.l), 0.2). etc.
Ref. No. ISO/IEC 9075:1992/Cor.l:1996(E)
KS 35.060
information interchange, documentation, documents,
Descriptors: data processing, text processing, factual data bases,
logical structk-e, artificial languages, database languages, SQL.
0 ISO/IEC 1996
Printed in Switzerland

---------------------- Page: 1 ----------------------
OISO/IEC
ISO/IEC 9075:1992/Cor.l:1996(E)
Contents Page
5
Introduction .
5
..................................................................
2 Normative references
................................................... 5
3.3.4.3 Terms denoting rule requirements
6
....................................................................
4.2 Character strings
............................................... 6
4.2.1 Character strings and collating sequences
7
4.4Numbers .
............................................................... 7
4.5 Datetimes and intervals
7
4.5.1Datetimes .
7
4.5.2Intervals .
7
4.8Columns .
7
..........................................................................
4.9Tables.
8
.................................................................
4.10.2 Table constraints
................................................................. 8
4.18.1 Status parameters
................................................. 8
4.22.6 SQL-statements and transaction states
............................................................ 8
4.24 SQL dynamic statements
8
........................................................................
4.26Privileges
.................................................................. 9
4.28 SQL-transactions
9
4.29 SQL-connections .
.............................................................. 9
4.3 1 Client-server operation
............................................................. 9
5.2 and
10
5.3 .
............................................................... 11
5.4 Names and identifiers
11
6.1 .
..........................................
11
6.2 and
..................................................................
12
6.3
............................................................
12
6.6
........................................................... 13
6.8
13
6.10 .
.........................................................
15
6.12
........................................................
15
6.14 cdatetime value expression>
......................................................... 16
6.15
............................................................. 16
7.1
16
7.4 .
17
7,5 .
18
7,6 .
............................................................... 18
7.9
19
7.10.~ .
................................ 19
7.11 , , and

20
................................................................
8.3
....................................................
20
8.7
................................................................ 20
9.1 Retrieval assignment
21
9.2Storeassignment .
................................................................ 21
10.1
........................................................
22
10.4
.................................................................. 22
10.5
................................... 22
10.6 and
22
...............................................................
ll.l
........................................................... 22
11.2
23
11.4. .
.................................................................... 23
11.5
......................................................... 25
11.6
....................................................
25
11.8
........................................................
25
11.9
25
ll.ll .
2

---------------------- Page: 2 ----------------------
OISOIIEC
ISO/IEC 9075:1992/Cor.l:1996(E)
11.16
26
....................................................
11.17
26
...................................................
11.18
26
............................................................
ll.l9
................................................................
26
11.21
..............................................................
27
11.25
28
..................................................
11.26
................................................. 28
11.27
.......................................................... 28
11.28
.......................................................... 28
11.29
28
......................................................
ccollationdefinition>
11.30 .
29
11.31
29
.........................................................
11.32
........................................................... 30
11.34 cassertiondefinition> .
30
11.36 .
30
11.37 crevokestatement> .
32
12.3cprocedure>
.....................................................................
37
12.4 Calls to a
38
......................................................... ....
13.1cdeclarecursor> .
47
13.2copenstatement> .
48
13.3 48
.................................................................
13.4
48
.................................................................
13.5 ....................................................... 49 13.6 ....................................................... 49 13.7 ........................................................ 49 13.8 ................................................................. 49 13.9 cupdate statement: positioned> ...................................................... 50 13.10 cupdate statement: searched> ....................................................... 50 14.1 50 .......................................................... 14.2 50 ..................................................... 14.3ccommitstatement> . 51 15.1cconnectstatement>. . 51 15.2 .......................................................... 5 1 15.3 ............................................................ 5 1 16.5 51 ...................................................... 17.1 Description of SQL item descriptor areas 51 ............................................... 17.2 callocate descriptor statement> ...................................................... 52 17.3 .................................................... 52 17.4 52 .......................................................... 17.5 52 .......................................................... 17.6 53 ............................................................... 17.9cusingclause> . 56 17.10cexecutestatement>. . 56 17.11 ..................................................... 56 17.15 ......................................................... 56 17.18 .............................................. 56 18.1 56 ......................................................... 19.1 ...................................................... 58 19.5 58 .................................................. 20.1 59 ............................................................ 21.1Introduction . 59 21.2.2 INFORMATION SCHI?MA CATALOG NAME base table - . 59 - - 21.2.3 INFORMATION SCHEMA CATALOG NAME CARDINALITY assertion - - . 60 21.2.5DOMAINSview -. . ........ . 60 21.2.6DOMAIN CONSTRAINTSview . - 60 3 ---------------------- Page: 3 ---------------------- ISO/IEC 9075:1992/Cor.l:1996(E) OISOIIEC 60 ................................................................ 21.2.9 COLUMNS view 61 21.2.17 ASSERTIONS view . ....................................................... 61 21.2.27 SQL IDENTIFIER domain 21.3.5 DATA TYPE DESCRIPTOR base table . . . 61 64 21.3.6DOMkNSbaietable . ::::. . ::::. . ::::::::. . .............................................................. 64 21.3.8 TABLES base table 64 ........................................................... 21.3.10 COLUMNS base table 21.3.11 VIEW TABLE USAGE base table . 65 .............................................. 65 21.3.12 VIEW-COLUMN USAGE base table ....................................... 65 21.3.13 TABLE CONSTRAINTS base table . : : . . . 66 21.3.15 REFERENTIAL CONSTRAINTS base’ table ... 66 21.3.17 CHECK TABLE USAGE base table . : : . : 1. : : : : : : : : : : : : : : : : : : : : : : : 1. ............................................. 21.3.18 CHECK-COLUMN USAGE base table 66 ............................................... 21.3.21 COLUMk PRIVILEGES base table 66 ................................................. 66 21.3.22 USAGE PRIVILEGES base table ................................................... 67 21.3.23 CHARACTER SETS base table ........................................................ 67 2 1.3.24 COLLATIONSbase table 21.3.26 SQL LANGUAGES base table . 67 22.1SQLSTATE . 67 69 22.2SQLCODE . .................................. 69 22.3 Remote Database Access SQLSTATE Subclasses ............................................................ 72 23.2 Claims of Conformance 23.3 Extensions and options . 72 ................................................ 72 Annex A.1 Intermediate SQL Specifications ............................................................ 74 A.2 Entry SQL Specifications ................................................. 77 Annex B: Implementation-defined elements ............................................... Annex C Implementation-dependent elements 78 .......................................... Annex E Incompatibilities with ISO/IEC 9075: 1989 79 ............................................. 80 Annex F Maintenance and interpretation of SQL 4 ---------------------- Page: 4 ---------------------- OISO/IEC ISO/IEC 9075:1992/Cor.l:1996(E) Introduction Rationale: In the list of significant new features, the wording incorrectly implies 1. that all the listed examples in item 10) are referential integrity facilities. On page xiv, in Significant new feature lo), replace “referential integrity” with “integrity ”. 2 Normative references I. Rationale: Editorial. Add the following reference after the reference to “IS0 8601: 1988 ”: IS0 8649: 1988, Information processing systems -Open Systems Interconnection-Service definition for the Association Control Service Element. language standard (ISO/IEC 8652: 1995, Information 2. Rationale: The newly revised Ada technology-Programming languages-Ada) contains support for decimal-encoded numeric data and variable length character strings. The revised inter$ace allows newly written applications in the revised Ada language access to these features of SQL; previously written Ada applications, conformant with the earlier Ada inter$ace, are conformant with the revised inter$ace. Replace the reference to ISOIIEC 8652: 1987) with the following: ISO/IEC 8652: 1995, Information technology-Programming languages-Ada. 3. Rationale: Editorial. Add the following reference after the reference to ISO/IEC 8824: 1990: ISOIIEC 9579- 1: 1993, Information technology-Open Systems Interconnection-Remote Database Access-Part I: Generic Model, Service and Protocol. Add the following reference after the reference to ISO/IEC 9899: Interconnection-Distributed ISO/IEC 10026-2: 1996, Information technology-Open Systems Transaction Processing-Part 2: OSI TP Service. 3.3.4.3 Terms denoting rule requirements Rationale: The following unifies the SQLSTATE returnedfor the different ways of invoking an SQL statement. 1. In the first and second paragraphs, replace “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 or access rule violation in direct SQL statement)” with “syntax error or access rule violation ”. 5 ---------------------- Page: 5 ---------------------- ISO/IEC 9075:1992/Cor.l: 1996(E) OISOPIEC 4.2 Character strings 1. Rationale: Editorial. In the second paragraph, replace the last sentence with the following: Character sets defined by standards or by implementations reside in the Information Schema (named INFORMATION SCHEMA) in each catalog, as do collations and translations defined by standards and - collations, translations, and form-of-use conversions defined by implementations. 4.2.1 Character strings and collating sequences Rationale: The following changes make the de@nitions of character set and collation descriptors more precise. 1. Replace the text on page 17 that occurs after the first paragraph with the following: A character set is described by a character set descriptor. A character 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 contained 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 collation 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 name> 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. ---------------------- Page: 6 ---------------------- OISOIIEC ISO/IEC 9075:1992/Cor.l:1996(E) 4.4 Numbers 1. Rationale: Clarification. Add the following sentence immediately before the heading of Subclause 4.4.1 Characteristics of Numbers: A value described by a numeric data type descriptor is always signed. 4.5 Datetimes and intervals 1. Rationale: Clarification. Add the following sentence before the paragraph starting “Every datetime . . ‘I. A value described by an interval data type descriptor is always signed. 4.51 Datetimes 1. Rationale: Editorial. In the penultimate paragraph on page 24, replace “Universal Coordinated Time” with “Coordinated Universal Time ”. 4.5.2 Intervals 1. Rationale: Editorial. In Table 7, replace “” (two occurrences). 4.8 Columns 1. Rationale: Editorial. In the penultimate paragraph on page 28, replace “crow value constructor expression>” with “vow value constructor element> “. 4.9 Tables 1. Rationale: There is no named derived table other than a viewed table. After the paragraph that begins with “A derived table descriptor describes a derived table. “, delete the first item ( ‘I- if the table is named, then the name of the table; “). 2. Rationale: There is no named derived table other than a viewed table. After the paragraph that begins with “A view descriptor describes a view. “, insert “- the name of the view, and” before the existing item. ---------------------- Page: 7 ---------------------- ISO/IEC 9075:1992/Cor.l:1996(E) OISOIIEC 4.10.2 Table constraints 1. Rationale: Editorial. In the Note, replace “” with “ “. 2. Rationale: Editorial. In the paragraph that begins with “A referential constraint is satisfied ”, replace “” with “ type> “. 4.18.1 Status parameters 1. Rationale: To insure that the value returned to the user in SQLSTATE is representative of the actual state of the transaction or SQL-statement. Add the following as the last paragraph: For the purpose of choosing status parameter values to be returned, exceptions for transaction rollback have precedence over exceptions for statement failure. Similarly, completion condition no data has precedence over warning, which has precedence over successful completion. All exceptions have precedence over all completion conditions. The values assigned to SQLSTATE shall obey these precedence rules. 4.22.6 SQL-statements and transaction states 1. Rationale: No statement can be both transaction-initiating and not transaction-initiating. In the first dashed list (of transaction-initiating SQL-statements), in the bulleted sublist of SQL-data statements, delete the entry for . 4.24 SQL dynamic statements 1. Rationale: Editorial. In the fourth paragraph, replace “s” with “s” . 2. Rationale: Editorial. In the eighth paragraph, replace the first occurrence of “ and replace the second occurrence of “ 4.26 Privileges Rationale: Editorial. 1. In the fourth paragraph on page 52, replace “ is” with “ identifier> is ”. ---------------------- Page: 8 ---------------------- OISO/IEC ISO/IEC 9075: 1992Kor.l: 1996(E) 2. Rationale: Provide missing rules that cover the acquisition of the necessary privileges to acquire the WITH GRANT OPTION on views through a grant to PUBLIC. Add the following before the antepenultimate paragraph of this Subclause: The phrase user privileges refers to the set of privileges defined by the privilege descriptors whose grantee is either the identified or PUBLIC. 4.28 SQL-transactions 1. Rationale: Clarification. In the paragraph that begins “In some environments (e.g., remote database access) “, replace all occurrences of “SQL-environment” with “SQL-implementation ”. 4.29 SQL-connections 1. Rationale: Editorial. Replace the second paragraph with the following: An SQL-connection is an active SQL-connection if any SQL-statement that initiates or requires an SQL-transaction has been executed at its SQL-server via that SQL-connection during the current SQL-transaction. 2. Rationale: Clarification. In the last sentence of the penultimate paragraph, replace “SQL-environment” with “SQL-implementation ”. 4.31 Client-server operation 1. Rationale: Clarifzcation. Replace the first sentence with the following: As perceived by an SQL-agent, an SQL-implementation consists of one or more SQL-servers and one SQL-client through which SQL-connections can be made to them. 5.2 and 1. Rationale: The maximum length of an is intended to be 128 characters. Replace Syntax Rule 8) with the following: In a , the number of s shall be less f-9 than 128. 2. Rationale: A shall not contain any or . Thus, a identijier> with a containing a or is not equivalent to any . In Syntax Rule 13), delete the expression “(with all occurrences of replaced by and all occurrences of replaced by ) “. 9 ---------------------- Page: 9 ---------------------- ISO/IEC 9075: 1992Kor.l: 1996(E) OISO/IEC ” and 3. Rationale: Correct the incorrect references to I’ ‘2 quote symbol> ” and delete the redundant to “ “s and “ "S in Syntax Rule references 14. In Syntax Rule 14), delete “( with all occurrences of replaced and all of occurrences bY replaced by ) “. 4. Rationale: A does not appear in a or in a identifier body>. Replace Leveling Rule 2) a) with the following: The number of s plus the number of s contained in a a> shall be less than 18. Insert the following Leveling Rule 2) a. 1): a.1) The of a shall not comprise more than 18 identifier part>s. 5.3 1. Rationale: Support changes to Subclause 6.10. In Format, replace the BNF for , , ctimestamp string>, with the following: ::= ::= ::= ::= Add the following to Format: ::= ::= [ ] ::= [ ] ( I ) ::= Add the following sentence at the end of General Rule 5): , the sign of the literal is determined by If a is specified in both possible locations in an operators, i.e., double negation results in a positive literal. the normal mathematical interpretation of multiple sign 10 ---------------------- Page: 10 ---------------------- OISO/IEC ISO/IEC 9075: 1992/Cor.l:1996(E) 2. Rationale: Ellipses were placed both in the definition of and in the usage of in several types of string literals. This leads to a small ambiguity when two separator characters follow one another. The following changes remove the ellipses in the latter of these two places: In Format, in each of the productions for , , literal>, , replace “ . . ‘I with “ “. In Syntax Rules 1) - 3) replace “ . .I’ with “ “. 5.4 Names and identifiers 1. Rationale: Editorial. In Syntax Rule l), replace “identified” with “indicated ”. 2. Rationale: Add the rule for the comparison S missing since was of decoupled from . Add the following Syntax Rule: 10.5) Two are equal if they have the same and the same , regardless of whether the 6.1 Rationale: Clarify the error condition that should be returned if a datetime overflow occurs. 1. Delete General Rule 6). 2. Rationale: Clarification. Add the following Note after Syntax Rule 28): Note: The length of interval data types is specified in the General Rules of 10.1, “ “. 6.2 and 1. Rationale: Identify possible upward incompatibility. Add the following at the end of Syntax Rule 3): Note: In an environment where the SQL-implementation conforms to Entry SQL, conforming SQL language that contains either: a specified or implied that compares the USER with a a> other than USER, or a specified or implied assignment in which the “value” (as defined in Subclause 9.2 Store assignment) b) contains the USER will become non-conforming in an environment where the SQL-implementation conforms to Intermediate SQL or Full SQL, unless the character repertoire of the implementation-defined character set in that environment is TEXT. identical to the character repertoire of SQL - 11 ---------------------- Page: 11 ---------------------- ISO/IEC 9075: 199WCor.l: 1996(E) OISOIIEC 6.3
1. Rationale: Editorial.
In Syntax Rule 2) a), replace “with no intervening” with “without an intervening ”.
2.
...