X3H2-94-329 DBL:RIO-004 August, 1994 ISO International Organization for Standardization ANSI American National Standards Institute ANSI TC X3H2 ISO/IEC JTC 1/SC 21/WG 3 Database Title: (ISO-ANSI Working Draft) Database Language SQL (SQL3) Author: Jim Melton (Editor) References: 1) ANSI X3H2-94-079/SOU-003, (ISO Working Draft) Framework for SQL (SQL/Framework), March, 1994 2) ANSI X3H2-94-080/SOU-004, (ISO-ANSI Working Draft) Database Language SQL, March, 1994 3) ANSI X3H2-94-081/SOU-005, (ISO Working Draft) SQL Call-Level Interface (SQL/CLI), March, 1994 4) ANSI X3H2-94-082/SOU-006, (ISO Working Draft) SQL Persistent Stored Modules (SQL/PSM), March, 1994 5) ANSI X3H2-94-083/SOU-007, (ISO Working Draft) SQL Host Language Bindings (SQL /Bindings), March, 1994 6) X3H2-94-077, Minutes from ANSI X3H2 meeting, February, 1994 7) X3H2-94-185, Minutes from ANSI X3H2 meeting, April, 1994 8) X3H2-94-306, Minutes from ANSI X3H2 meeting, June, 1994 9) RIO-001, Minutes from ISO DBL RG meeting, July, 1994 1 Editorial Notes The attached SQL3 base document incorporates the changes arising from the ISO DBL RG meeting held January, 1994, in Munich, Germany, and the ANSI X3H2 meeting in February, 1994, in Tiburon, CA. The following conventions are used in the base document: 1) Words or short phrases that have been approved by ANSI but not by ISO are indicated by: ANSI sample phrase while words or short phrases approved by ISO and not by ANSI are indicated by: ISO example. Short differences between ANSI and ISO are indicated by: ANSI ANSI version. ISO ISO version. 2) Longer sequences of text that have been approved by ANSI, later changed by ISO, and not yet reconsidered by ANSI are distinguished by: ANSI Only—caused by ISO changes not yet considered by ANSI separating the ANSI-specific text from the preceding text by the convention you see here, and from the following text by the single heavy rule below. This separator is used to identify infor- mation that may change when ANSI has the opportunity to consider changes accepted by ISO that affect the area indicated. Similarly, longer sequences approved by ISO, changed by ANSI, and not yet reconsidered by ISO are distinguished by: ISO Only—caused by ANSI changes not yet considered by ISO separating the ISO-specific text from the preceding text by the convention you see here, and from the following text by the single heavy rule below as before. This separator is used to iden- tify information that may change when ISO has the opportunity to consider changes accepted by ANSI that affect the area indicated. It is the intent of these conventions to clearly highlight those portions of the SQL3 base docu- ment that are not yet approved by both bodies. One hopes that this highlighting will make it easier for the two bodies to move closer, so that a single standard can result from their efforts. 3) I have created a number of additional separators. While recognizing that the proliferation of these separators makes the document somewhat complex to examine, I believe it to be necessary in order that we may know the precise state of each part of the document. In particular, you may find in this document separators as follows: ISO Only–SQL3 This separator indicates that ISO has approved the text following the separator for SQL3. ANSI has not approved the text at all. Editorial Notes Notes–1 ANSI Only–SQL3 This separator indicates that ANSI has approved the text following the separator for SQL3. ISO has not approved the text at all. 4) I am usually able to produce a document that has changebars to indicate changes between the present document and the preceding version. New (inserted) and changed material is marked with a thin vertical bar like that next to this paragraph. • Example of deletion bullet Additionally, places in the current document where material from the preceding version has been deleted are marked with a bullet like the one preceding this paragraph. The note beside the bullet, if any, indicates the number of "entities" that have been deleted. Those entities are typically sentences, although they are sometimes text processor commands. In Clause 2, "Changes and Notes", I have made a number of notes related to the changes that I have made to Reference 1 to derive this document. In Clause 3, "Possible problems with the SQL3 language", I have listed a number of possible problems in the SQL3 language. This list should be viewed as a sort of "work item list", helping identify areas that should (must?) be resolved before the document is ready for public review. This material is divided into genuine problems versus those problems that simply should be addressed or for which "wordsmithing" is the likely answer; the second category is not viewed as sufficiently urgent to cause the document to fail a ballot for public review or advancement. Finally, there is a new section for "Language Opportunities" that lists those items in which some interest has been expressed, but that are not felt to be sufficiently important to justify delaying the standard in their absence. In Clause 4, "SQL3 Language Features Not Approved by Both ANSI and ISO", I have listed what I believe to be the differences between ANSI-approved SQL3 features and ISO-approved features at the time of publication of these Editor’s Notes. In Clause 5, "Guidelines for writing "user-friendly" change proposals", I have outlined some sug- gested guidelines that I encourage you to use when writing change proposals. Following those guidelines will markedly help improve the quality of the document. 2 2 Changes and Notes ISO DBL RG changes In its July, 1994, meeting in Southampton, United Kingdom, the ISO/IEC JTC1/SC21/WG3 Database Languages Rapporteur Group (DBL RG) Meeting considered most of the papers that had been put before it. All those papers that were accepted have been incorporated into this document. The results of those papers that were approved are reflected in this document; this portion of the Editor’s Notes indicates the papers whose changes are reflected. 1) SOU-020/X3H2-93-481, Privileges: correct definition of "abandoned" 2) SOU-021/X3H2-93-433, Large Object Strings 3) SOU-027/X3H2-93-531R1, Correction to VIEW_TABLE_USAGE base table 4) SOU-028/X3H2-93-523R, Further clarification to COLUMN_DEFAULT 5) SOU-029/X3H2-93-556, Null-elimination warning for cursors 6) SOU-030/X3H2-94-019, Correction to definition of outer reference 7) SOU-031/X3H2-94-015, Casting between datetimes and character strings 8) SOU-034/X3H2-93-493, Make column-level SELECT privileges work 9) SOU-035/X3H2-93-500, Different types of external routines 10) SOU-036/X3H2-93-503, Consistency of descriptor area and diagnostics area 11) SOU-037/X3H2-93-534, Fix on Leveling Rule of <referential constraint definition> 12) SOU-038/X3H2-93-560, Consistent SQLSTATEs for Referential Integrity constraint violation 13) SOU-040/X3H2-94-026, Triggers on views 14) SOU-041/X3H2-94-027, Schema paths for data types 15) SOU-042/X3H2-94-028, Improving encapsulation I 16) SOU-045/X3H2-94-031R, Interval qualifier 17) SOU-046/X3H2-94-060, Null class specification 18) SOU-051/X3H2-94-239, Common subexpression in queries 19) SOU-054/X3H2-94-242, MIscellaneous editorial corrections 20) SOU-055/X3H2-94-243, Erratum in Subclause 5.2 21) SOU-059/X3H2-94-111, Remove <SQL statement> 22) SOU-060/X3H2-94-126, Clean up <query specification> Changes and Notes Notes–3 23) SOU-062/X3H2-94-158, PL/I specification of FLOAT and DECIMAL 24) SOU-063/X3H2-94-102, Adding functions, routines, overloading to SQL/PSM 25) SOU-065/X3H2-94-106, Miscellaneous PSM changes 26) SOU-069/X3H2-94-148, Clarify <leave statement> Syntax Rules 27) SOU-070/X3H2-94-159, Exception handling in <for statement> 28) SOU-073/X3H2-94-096, Changing the type of an <abstract data type value expression> 29) SOU-074/X3H2-94-098, Remove semicolon in CREATE TRIGGER 30) SOU-075/X3H2-94-099, Fixes to Large Object Strings 31) SOU-076/X3H2-94-103, ROW types—base proposal 32) SOU-077/X3H2-94-150, Using Row Types in <for statement> 33) SOU-080/X3H2-94-109, Restrict use of ’empty’ ADT constructor 34) SOU-081/X3H2-94-178, Miscellaneous cleanup 35) SOU-082/X3H2-94-124, Replace "<column reference>" with "column reference" 36) SOU-083/X3H2-94-125, Alignment of ADT descriptor 37) SOU-084/X3H2-94-130, Destructors for object ADTs 38) SOU-085/X3H2-94-131, Subject routine determination in views/constraints 39) SOU-086/X3H2-94-143, SQL-session attributes 40) SOU-087/X3H2-94-149, Miscellaneous routine cleanup 41) SOU-088/X3H2-94-151, Inner-most atomic execution context 42) SOU-090/X3H2-94-169R, Bit strings and <position expression> 43) SOU-097/X3H2-94-244, Miscellaneous Possible Problems 44) SOU-104/X3H2-94-252, A proposal to fix Possible Problems 261, 298, and 300 45) SOU-105R1/X3H2-94-253, Removal of redundant <there is predicate> 46) SOU-106/X3H2-94-254, A proposal to fix Possible Problem 262 47) SOU-107/X3H2-94-255, A proposal to fix Possible Problem 277 and Language Opportunity 276 (but not Possible Problem 153) 48) SOU-108/X3H2-94-256, Fix to Possible Problem 299 49) SOU-109/X3H2-94-257, A proposal to fix Possible Problem 313 50) SOU-112/X3H2-94-260, Fix to Possible Problem 344 51) SOU-118/X3H2-94-264, A proposal to standardize Session Management statement syntax 4 52) SOU-130/X3H2-94-275, Cursor correction 53) SOU-131/X3H2-94-282, Ada OUT parameters 54) SOU-138/X3H2-94-216, CASE 55) SOU-139/X3H2-94-289, Schema information tables for SQL/PSM 56) SOU-141/X3H2-94-217, Single-root supertype rule 57) SOU-142/X3H2-94-231, Removal of virtual attributes 58) SOU-145/X3H2-94-233R, Change "handle" to "locator" 59) SOU-146/X3H2-94-293/X3H2-94-204, Improving encapsulation: changes and fixes 60) SOU-150/X3H2-94-268, OID Association 61) SOU-151/X3H2-94-269, Nullability with INTERSECT and EXCEPT 62) SOU-154/X3H2-94-284, Need for <specific type template designator 63) SOU-184/X3H2-94-201, Flow of control, exception handling, and transactions 64) SOU-186/X3H2-94-387, Response to SOU-071, "Specialize Generalize rows in subtable" 65) SOU-188/X3H2-94-387, Comments on Final Text of Corrigendum 1 66) SOU-???/X3H2-94-116, ELSEIF usage in the <if statement> ANSI X3H2 changes In its April, 1994, meeting in Pittsburgh, PA, and its June, 1994, meeting in Fargo, ND, the ANSI X3H2 body considered most of the papers that had been put before it in a timely manner. The results of those papers that were approved are reflected in this document; this portion of the Editor’s Notes indicates the papers whose changes are reflected. 1) SOU-020/X3H2-93-481, Privileges: correct definition of "abandoned" 2) SOU-021/X3H2-93-433, Large Object Strings 3) SOU-027/X3H2-93-531R1, Correction to VIEW_TABLE_USAGE base table 4) SOU-028/X3H2-93-523R, Further clarification to COLUMN_DEFAULT 5) SOU-029/X3H2-93-556, Null-elimination warning for cursors 6) SOU-030/X3H2-94-019, Correction to definition of outer reference 7) SOU-031/X3H2-94-015, Casting between datetimes and character strings 8) SOU-033/X3H2-93-465, A Backward-Compatible extension to the FROM clause 9) SOU-034/X3H2-93-493, Make column-level SELECT privileges work 10) SOU-035/X3H2-93-500, Different types of external routines 11) SOU-036/X3H2-93-503, Consistency of descriptor area and diagnostics area Changes and Notes Notes–5 12) SOU-037/X3H2-93-534, Fix on Leveling Rule of <referential constraint definition> 13) SOU-038/X3H2-93-560, Consistent SQLSTATEs for Referential Integrity constraint violation 14) SOU-039/X3H2-94-004, Row identifiers simplify applications 15) SOU-040/X3H2-94-026, Triggers on views 16) SOU-041/X3H2-94-027, Schema paths for data types 17) SOU-042/X3H2-94-028, Improving encapsulation I 18) SOU-044/X3H2-94-030R1, Roles module 19) SOU-045/X3H2-94-031R, Interval qualifier 20) SOU-046/X3H2-94-060, Null class specification 21) SOU-059/X3H2-94-111, Remove <SQL statement> 22) SOU-060/X3H2-94-126, Clean up <query specification> 23) SOU-062/X3H2-94-158, PL/I specification of FLOAT and DECIMAL 24) SOU-063/X3H2-94-102, Adding functions, routines, overloading to SQL/PSM 25) SOU-065/X3H2-94-106, Miscellaneous PSM changes 26) SOU-069/X3H2-94-148, Clarify <leave statement> Syntax Rules 27) SOU-070/X3H2-94-159, Exception handling in <for statement> 28) SOU-072/X3H2-94-092, Specifying triggers 29) SOU-073/X3H2-94-096, Changing the type of an <abstract data type value expression> 30) SOU-074/X3H2-94-098, Remove semicolon in CREATE TRIGGER 31) SOU-075/X3H2-94-099, Fixes to Large Object Strings 32) SOU-076/X3H2-94-103, ROW types—base proposal 33) SOU-077/X3H2-94-150, Using Row Types in <for statement> 34) SOU-080/X3H2-94-109, Restrict use of ’empty’ ADT constructor 35) SOU-081/X3H2-94-178, Miscellaneous cleanup 36) SOU-082/X3H2-94-124, Replace "<column reference>" with "column reference" 37) SOU-083/X3H2-94-125, Alignment of ADT descriptor 38) SOU-084/X3H2-94-130, Destructors for object ADTs 39) SOU-085/X3H2-94-131, Subject routine determination in views/constraints 40) SOU-086/X3H2-94-143, SQL-session attributes 41) SOU-087/X3H2-94-149, Miscellaneous routine cleanup 6 42) SOU-088/X3H2-94-151, Inner-most atomic execution context 43) SOU-089/X3H2-94-162, Default Roles 44) SOU-090/X3H2-94-169R, Bit strings and <position expression> 45) SOU-130/X3H2-94-275, Cursor correction 46) SOU-131/X3H2-94-282, Ada OUT parameters 47) SOU-138/X3H2-94-216, CASE 48) SOU-139/X3H2-94-289, Schema information tables for SQL/PSM 49) SOU-141/X3H2-94-217, Single-root supertype rule 50) SOU-142/X3H2-94-231, Removal of virtual attributes 51) SOU-145/X3H2-94-233R, Change "handle" to "locator" 52) SOU-146/X3H2-94-293/X3H2-94-204, Improving encapsulation: changes and fixes 53) SOU-150/X3H2-94-268, OID Association 54) SOU-151/X3H2-94-269, Nullability with INTERSECT and EXCEPT 55) SOU-154/X3H2-94-284, Need for <specific type template designator 56) SOU-???/X3H2-94-116, ELSEIF usage in the <if statement> Other changes In addition, the following changes appear in this document. 1) A number of minor editorial changes intended to clarify vague wording, incorrect grammar, inconsistent phraseology, and so forth. Changes and Notes Notes–7 8