Subject: SQL conformance related patch

From: Troels Arvin <[email-masked]>
Date: Thu, 25 Nov 2004 12:24:35 +0100

Hello,

Simon Riggs, Elein Mustain, and I have worked on adjustments to the
information schema and parts of the documentation to reflect upcoming
changes in PostgreSQL 8 and changes from SQL:1999 to SQL:2003.

Attached is the result of that: A "diff -c" patch for current CVS HEAD.
The patch is also available via HTTP[1].

The patch subsumes Simon's patch of November 21 with subject "SQL
Conformance introductory section"[2]. Peter's objection to Simon's
choice of words has been accommodated.

We've had some discussions regarding feature ID F411 (Time zone
specification). If we were to be strict, PostgreSQL's support for F411
should be changed to NO, due to a difference in TIMESTAMP literal
handling. As it is, the status remains YES, but a comment has been added
in the information schema, and the datetime documentation has been
expanded a bit.

Simon also has ideas for improvement of the conformance documentation
page (features.sgml), but that's probably a version 8.1 thing. I think
he'd better describe the ideas himself.

The list of probably-not-but-just-maybe-supported features in my message
as of October 20[3] remains unchanged, but as previously discussed on
this list, time is running out.

As previously mentioned, I've created a little list of "low hanging
fruits" for PostgreSQL's standards-conformance - i.e. features in
SQL:2003 which PostgreSQL currently doesn't support but which might
actually be relatively easy to implement:
http://troels.arvin.dk/db/pgsql/conformance/low-hanging-fruits.txt

References:

1:
http://troels.arvin.dk/db/pgsql/conformance/pgsql-sql-conformance.patch

2:
http://article.gmane.org/gmane.comp.db.postgresql.devel.patches/10318

3:
http://article.gmane.org/gmane.comp.db.postgresql.devel.documentation/1278

-- 
Greetings from Troels Arvin, Copenhagen, Denmark
Index: doc/src/sgml/datatype.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/datatype.sgml,v
retrieving revision 1.150
diff -c -r1.150 datatype.sgml
*** doc/src/sgml/datatype.sgml	20 Sep 2004 22:48:25 -0000	1.150
--- doc/src/sgml/datatype.sgml	25 Nov 2004 11:01:11 -0000
***************
*** 1744,1752 ****
       </para>
  
       <para>
!       For <type>timestamp [without time zone]</type>, any explicit time
!       zone specified in the input is silently ignored. That is, the
!       resulting date/time value is derived from the explicit date/time
        fields in the input value, and is not adjusted for time zone.
       </para>
  
--- 1744,1763 ----
       </para>
  
       <para>
!       The <acronym>SQL</acronym> standard differentiates <type>timestamp without time zone</type> 
!       and <type>timestamp with time zone</type> literals by the existence of a 
!       <quote>+</quote>; or <quote>-</quote>. Hence, according to the standard, 
!       <programlisting>TIMESTAMP '2004-10-19 10:23:54'</programlisting>
!       is a <type>timestamp without time zone</type>, while
!       <programlisting>TIMESTAMP '2004-10-19 10:23:54+02'</programlisting>
!       is a <type>timestamp with time zone</type>.
!       <productname>PostgreSQL</productname> 
!       differs from the standard by requiring that <type>timestamp with time zone</type> 
!       literals be explicitly typed:
!       <programlisting>TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'</programlisting>
!       If a literal is not explicitly indicated as being of <type>timestamp with time zone</type>,
!       PostgreSQL will silently ignore any time zone indication in the literal.
!       That is, the resulting date/time value is derived from the date/time
        fields in the input value, and is not adjusted for time zone.
       </para>
  
***************
*** 1778,1783 ****
--- 1789,1795 ----
        as <varname>timezone</> local time.  A different zone reference can
        be specified for the conversion using <literal>AT TIME ZONE</>.
       </para>
+ 
      </sect3>
  
      <sect3>
Index: doc/src/sgml/features.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/features.sgml,v
retrieving revision 2.22
diff -c -r2.22 features.sgml
*** doc/src/sgml/features.sgml	29 Nov 2003 19:51:37 -0000	2.22
--- doc/src/sgml/features.sgml	25 Nov 2004 11:01:11 -0000
***************
*** 7,35 ****
  
   <para>
    This section attempts to outline to what extent
!   <productname>PostgreSQL</productname> conforms to the SQL standard.
!   Full compliance to the standard or a complete statement about the
!   compliance to the standard is complicated and not particularly
!   useful, so this section can only give an overview.
!   </para>
  
   <para>
    The formal name of the SQL standard is ISO/IEC 9075 <quote>Database
!   Language SQL</quote>.  A revised version of the standard is released
!   from time to time; the most recent one appearing in 1999.  That
!   version is referred to as ISO/IEC 9075:1999, or informally as SQL99.
!   The version prior to that was SQL92.
!   <productname>PostgreSQL</productname> development tends to aim for
    conformance with the latest official version of the standard where
    such conformance does not contradict traditional features or common
!   sense.  At the time of this writing, balloting is under way for a
!   new revision of the standard, which, if approved, will eventually
!   become the conformance target for future
!   <productname>PostgreSQL</productname> development.
   </para>
  
   <para>
!   <acronym>SQL92</acronym> defined three feature sets for conformance:
    Entry, Intermediate, and Full. Most database management systems claiming
    <acronym>SQL</acronym> standard conformance were conforming at only
    the Entry level, since the entire set of features in the
--- 7,59 ----
  
   <para>
    This section attempts to outline to what extent
!   <productname>PostgreSQL</productname> conforms to the current international 
!   SQL standard.
!  </para>
! 
!  <para>
!   PostgreSQL supports all the major features of SQL:2003. Out of 164 mandatory features
!   required for full Core conformance, PostgreSQL conforms to 150. The remaining 14 features
!   are generally minor differences. In addition, PostgreSQL supports 96 optional features
!   providing enhanced standards compliance in the following areas: 
! 
!   <itemizedlist>
!    <listitem><para>Enhanced Datetime facilities</para></listitem>
!    <listitem><para>OLAP facilities</para></listitem>
!    <listitem><para>Enhanced integrity management</para></listitem>
!    <listitem><para>Active Database</para></listitem>
!    <listitem><para>Enhanced Object Support</para></listitem>
!   </itemizedlist>
!  </para>
! 
!  <para>
!   The following information is not a full 
!   statement of conformance, though presents the main topics in as much detail 
!   as is both reasonable and useful for developers.
!  </para>
  
   <para>
    The formal name of the SQL standard is ISO/IEC 9075 <quote>Database
!   Language SQL</quote>, jointly published by ISO and ANSI. A revised version of 
!   the standard is released from time to time; the most recent one appearing in 
!   late 2003. That version is referred to as ISO/IEC 9075:2003, or 
!   simply SQL:2003. It is also often popularly referred to as ANSI SQL.
!   The versions prior to that were SQL:1999, and before that SQL-92. Each version 
!   replaces the previous one, so claims of conformance to earlier versions have
!   no official merit. No current version of any database management system 
!   currently claims full conformance to Core SQL:2003, at time of writing.
!   <productname>PostgreSQL</productname> development aims for
    conformance with the latest official version of the standard where
    such conformance does not contradict traditional features or common
!   sense. <acronym>PGDG</acronym> were not represented on the ISO/IEC 9075
!   Working Group during the preparation of SQL:2003. Even so, many of the 
!   features required by SQL:2003 are already supported, though sometimes with
!   slightly differing syntax or function. Further moves towards conformance 
!   may be expected in later releases.
   </para>
  
   <para>
!   <acronym>SQL-92</acronym> defined three feature sets for conformance:
    Entry, Intermediate, and Full. Most database management systems claiming
    <acronym>SQL</acronym> standard conformance were conforming at only
    the Entry level, since the entire set of features in the
***************
*** 38,74 ****
   </para>
  
   <para>
!   <acronym>SQL99</acronym> defines a large set of individual features
!   rather than the ineffectively broad three levels found in
!   <acronym>SQL92</acronym>.  A large subset of these features
!   represents the <quote>core</quote> features, which every conforming
    SQL implementation must supply.  The rest of the features are purely
    optional.  Some optional features are grouped together to form
!   <quote>packages</quote>, which SQL implementations can claim
    conformance to, thus claiming conformance to particular groups of
    features.
   </para>
  
   <para>
!   The <acronym>SQL99</acronym> standard is also split into 5 parts:
!   Framework, Foundation, Call Level Interface, Persistent Stored
!   Modules, and Host Language Bindings.
!   <productname>PostgreSQL</productname> only covers parts 1, 2, and 5.
    Part 3 is similar to the ODBC interface, and part 4 is similar to
    the <application>PL/pgSQL</application> programming language, but
!   exact conformance is not specifically intended in either case.
   </para>
  
   <para>
    In the following two sections, we provide a list of those features
    that <productname>PostgreSQL</productname> supports, followed by a
!   list of the features defined in SQL99 which are not yet supported in
!   <productname>PostgreSQL</productname>.  Both of these lists are
    approximate: There may be minor details that are nonconforming for a
    feature that is listed as supported, and large parts of an
    unsupported feature may in fact be implemented.  The main body of
    the documentation always contains the most accurate information
!   about what does and does not work.
   </para>
  
   <note>
--- 62,132 ----
   </para>
  
   <para>
!   Starting with <acronym>SQL:1999</acronym>, the SQL standard now defines a large 
!   set of individual features rather than the ineffectively broad three levels 
!   found in <acronym>SQL-92</acronym>. A large subset of these features
!   represents the <quote>Core</quote> features, which every conforming
    SQL implementation must supply.  The rest of the features are purely
    optional.  Some optional features are grouped together to form
!   <quote>Packages</quote>, which SQL implementations can claim
    conformance to, thus claiming conformance to particular groups of
    features.
   </para>
  
   <para>
!   The <acronym>SQL:2003</acronym> standard is also split into a number of parts.
!   Each is known by a shorthand name, such as <acronym>SQL/CLI</acronym> or 
!   <acronym>SQL/XML</acronym>. Note that these parts are not consecutively 
!   numbered.
!   <itemizedlist>
!    <listitem><para>ISO/IEC 9075-1 Framework 
!     (<acronym>SQL/Framework</acronym>)
!    </para></listitem>
!    <listitem><para>ISO/IEC 9075-2 Foundation 
!     (<acronym>SQL/Foundation</acronym>)
!    </para></listitem>
!    <listitem><para>ISO/IEC 9075-3 Call Level Interface 
!     (<acronym>SQL/CLI</acronym>)
!    </para></listitem>
!    <listitem><para>ISO/IEC 9075-4 Persistent Stored Modules 
!     (<acronym>SQL/PSM</acronym>)
!    </para></listitem>
!    <listitem><para>ISO/IEC 9075-9 Management of External Data 
!     (<acronym>SQL/MED</acronym>)
!    </para></listitem>
!    <listitem><para>ISO/IEC 9075-10 Object Language Bindings 
!     (<acronym>SQL/OLB</acronym>)
!    </para></listitem>
!    <listitem><para>ISO/IEC 9075-11 Information and Definition Schemas 
!     (<acronym>SQL/Schemata</acronym>)
!    </para></listitem>
!    <listitem><para>ISO/IEC 9075-13 Routines and Types using the Java Language 
!     (<acronym>SQL/JRT</acronym>)
!    </para></listitem>
!    <listitem><para>ISO/IEC 9075-14 XML-related specifications 
!     (<acronym>SQL/XML</acronym>)
!    </para></listitem>
!   </itemizedlist>
!  </para>
! 
!  <para>
!   <productname>PostgreSQL</productname> covers parts 1, 2, and 11.
    Part 3 is similar to the ODBC interface, and part 4 is similar to
    the <application>PL/pgSQL</application> programming language, but
!   exact conformance is not specifically intended in either case. 
   </para>
  
   <para>
    In the following two sections, we provide a list of those features
    that <productname>PostgreSQL</productname> supports, followed by a
!   list of the features defined in <acronym>SQL:2003</acronym> which are not yet 
!   supported in <productname>PostgreSQL</productname>.  Both of these lists are
    approximate: There may be minor details that are nonconforming for a
    feature that is listed as supported, and large parts of an
    unsupported feature may in fact be implemented.  The main body of
    the documentation always contains the most accurate information
!   about what does and does not work. Please submit questions, clarifications
!   and documentation changes should you find additional information.
   </para>
  
   <note>
***************
*** 79,85 ****
    </para>
   </note>
  
!   <sect1 id="features-sql99">
     <title>Supported Features</title>
  
     <para>
--- 137,143 ----
    </para>
   </note>
  
!   <sect1 id="features-sql-standard">
     <title>Supported Features</title>
  
     <para>
***************
*** 101,111 ****
     </para>
    </sect1>
  
!   <sect1 id="unsupported-features-sql99">
     <title>Unsupported Features</title>
  
     <para>
!     The following features defined in <acronym>SQL99</acronym> are not
      implemented in this release of
      <productname>PostgreSQL</productname>. In a few cases, equivalent
      functionality is available.
--- 159,169 ----
     </para>
    </sect1>
  
!   <sect1 id="unsupported-features-sql-standard">
     <title>Unsupported Features</title>
  
     <para>
!     The following features defined in <acronym>SQL:2003</acronym> are not
      implemented in this release of
      <productname>PostgreSQL</productname>. In a few cases, equivalent
      functionality is available.
Index: doc/src/sgml/ref/alter_sequence.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/alter_sequence.sgml,v
retrieving revision 1.8
diff -c -r1.8 alter_sequence.sgml
*** doc/src/sgml/ref/alter_sequence.sgml	24 Aug 2004 00:06:51 -0000	1.8
--- doc/src/sgml/ref/alter_sequence.sgml	25 Nov 2004 11:01:11 -0000
***************
*** 200,209 ****
     </title>
  
     <para>
!     <command>ALTER SEQUENCE</command> is a <productname>PostgreSQL</productname>
!     language extension.
!     There is no <command>ALTER SEQUENCE</command> statement
!     in <acronym>SQL99</acronym>.
     </para>
    </refsect2>
   </refsect1>
--- 200,206 ----
     </title>
  
     <para>
!     <command>ALTER SEQUENCE</command> conforms with <acronym>SQL:2003</acronym>.
     </para>
    </refsect2>
   </refsect1>
Index: doc/src/sgml/ref/create_sequence.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/create_sequence.sgml,v
retrieving revision 1.41
diff -c -r1.41 create_sequence.sgml
*** doc/src/sgml/ref/create_sequence.sgml	12 Jul 2004 05:36:56 -0000	1.41
--- doc/src/sgml/ref/create_sequence.sgml	25 Nov 2004 11:01:12 -0000
***************
*** 297,306 ****
    <title>Compatibility</title>
  
    <para>
!    <command>CREATE SEQUENCE</command> is a
!    <productname>PostgreSQL</productname> language extension.  There is
!    no <command>CREATE SEQUENCE</command> statement in the SQL
!    standard.
    </para>
   </refsect1>
  </refentry>
--- 297,308 ----
    <title>Compatibility</title>
  
    <para>
!    <command>CREATE SEQUENCE</command> is is specified in <acronym>SQL:2003</acronym>.
!    <productname>PostgreSQL</productname> conforms with the standard, with the following exceptions:
!    <itemizedlist>
!     <listitem><para>The standard's <literal>AS &lt;data type&gt;</literal> expression is not supported.</para></listitem>
!     <listitem><para>Obtaining the next value is done using the <function>nextval()</> function instead of the standard's <command>NEXT VALUE FOR</command> expression.</para></listitem>
!    </itemizedlist>
    </para>
   </refsect1>
  </refentry>
Index: doc/src/sgml/ref/drop_sequence.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/drop_sequence.sgml,v
retrieving revision 1.20
diff -c -r1.20 drop_sequence.sgml
*** doc/src/sgml/ref/drop_sequence.sgml	29 Nov 2003 19:51:38 -0000	1.20
--- doc/src/sgml/ref/drop_sequence.sgml	25 Nov 2004 11:01:12 -0000
***************
*** 82,88 ****
    <title>Compatibility</title>
  
    <para>
!    There is no <command>DROP SEQUENCE</command> statement in the SQL standard.
    </para>
   </refsect1>
  
--- 82,88 ----
    <title>Compatibility</title>
  
    <para>
!     <command>DROP SEQUENCE</command> conforms with <acronym>SQL:2003</acronym>.
    </para>
   </refsect1>
  
Index: src/backend/catalog/information_schema.sql
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/catalog/information_schema.sql,v
retrieving revision 1.24
diff -c -r1.24 information_schema.sql
*** src/backend/catalog/information_schema.sql	22 Jun 2004 22:30:32 -0000	1.24
--- src/backend/catalog/information_schema.sql	25 Nov 2004 11:01:15 -0000
***************
*** 1181,1188 ****
      sql_language_programming_language character_data
  ) WITHOUT OIDS;
  
! INSERT INTO sql_languages VALUES ('ISO 9075', '1999', 'CORE', NULL, NULL, 'DIRECT', NULL);
! INSERT INTO sql_languages VALUES ('ISO 9075', '1999', 'CORE', NULL, NULL, 'EMBEDDED', 'C');
  
  GRANT SELECT ON sql_languages TO PUBLIC;
  
--- 1181,1188 ----
      sql_language_programming_language character_data
  ) WITHOUT OIDS;
  
! INSERT INTO sql_languages VALUES ('ISO 9075', '2003', 'CORE', NULL, NULL, 'DIRECT', NULL);
! INSERT INTO sql_languages VALUES ('ISO 9075', '2003', 'CORE', NULL, NULL, 'EMBEDDED', 'C');
  
  GRANT SELECT ON sql_languages TO PUBLIC;
  
***************
*** 1209,1215 ****
  INSERT INTO sql_packages VALUES ('PKG006', 'Basic object support', 'NO', NULL, '');
  INSERT INTO sql_packages VALUES ('PKG007', 'Enhanced object support', 'NO', NULL, '');
  INSERT INTO sql_packages VALUES ('PKG008', 'Active database', 'NO', NULL, '');
! INSERT INTO sql_packages VALUES ('PKG009', 'SQL/MM support', 'NO', NULL, '');
  
  GRANT SELECT ON sql_packages TO PUBLIC;
  
--- 1209,1215 ----
  INSERT INTO sql_packages VALUES ('PKG006', 'Basic object support', 'NO', NULL, '');
  INSERT INTO sql_packages VALUES ('PKG007', 'Enhanced object support', 'NO', NULL, '');
  INSERT INTO sql_packages VALUES ('PKG008', 'Active database', 'NO', NULL, '');
! INSERT INTO sql_packages VALUES ('PKG010', 'OLAP', 'NO', NULL, 'NO');
  
  GRANT SELECT ON sql_packages TO PUBLIC;
  
Index: src/backend/catalog/sql_feature_packages.txt
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/catalog/sql_feature_packages.txt,v
retrieving revision 1.1
diff -c -r1.1 sql_feature_packages.txt
*** src/backend/catalog/sql_feature_packages.txt	14 Jan 2003 23:19:34 -0000	1.1
--- src/backend/catalog/sql_feature_packages.txt	25 Nov 2004 11:01:15 -0000
***************
*** 53,73 ****
  F812	Core
  S011	Core
  S023	Basic object support
- S023	SQL/MM support
  S024	Enhanced object support
- S024	SQL/MM support
  S041	Basic object support
  S043	Enhanced object support
  S051	Basic object support
  S071	Enhanced object support
  S081	Enhanced object support
- S091	SQL/MM support
- S092	SQL/MM support
  S111	Enhanced object support
  S151	Basic object support
  S161	Enhanced object support
  S211	Enhanced object support
- S211	SQL/MM support
  S231	Enhanced object support
  S241	Enhanced object support
  T041	Basic object support
--- 53,68 ----
***************
*** 78,82 ****
  T212	Enhanced integrity management
  T321	Core
  T322	PSM
- T322	SQL/MM support
  T431	OLAP facilities
--- 73,76 ----
Index: src/backend/catalog/sql_features.txt
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/catalog/sql_features.txt,v
retrieving revision 1.10
diff -c -r1.10 sql_features.txt
*** src/backend/catalog/sql_features.txt	2 Aug 2004 12:46:49 -0000	1.10
--- src/backend/catalog/sql_features.txt	25 Nov 2004 11:01:16 -0000
***************
*** 8,16 ****
  B021	Direct SQL			YES	
  B031	Basic dynamic SQL			NO	
  B032	Extended dynamic SQL			NO	
! B032	Extended dynamic SQL	01	<describe input> statement	NO	
  B041	Extensions to embedded SQL exception declarations			NO	
  B051	Enhanced execution rights			NO	
  E011	Numeric data types			YES	
  E011	Numeric data types	01	INTEGER and SMALLINT data types	YES	
  E011	Numeric data types	02	REAL, DOUBLE PRECISION, and FLOAT data types	YES	
--- 8,33 ----
  B021	Direct SQL			YES	
  B031	Basic dynamic SQL			NO	
  B032	Extended dynamic SQL			NO	
! B032	Extended dynamic SQL	01	<describe input statement>	NO	
! B033	Untyped SQL-invoked function arguments			NO	
! B034	Dynamic specification of cursor attributes			NO	
  B041	Extensions to embedded SQL exception declarations			NO	
  B051	Enhanced execution rights			NO	
+ B111	Module language Ada			NO	
+ B112	Module language C			NO	
+ B113	Module language COBOL			NO	
+ B114	Module language Fortran			NO	
+ B115	Module language MUMPS			NO	
+ B116	Module language Pascal			NO	
+ B117	Module language PL/I			NO	
+ B121	Routine language Ada			NO	
+ B122	Routine language C			YES	
+ B123	Routine language COBOL			NO	
+ B124	Routine language Fortran			NO	
+ B125	Routine language MUMPS			NO	
+ B126	Routine language Pascal			NO	
+ B127	Routine language PL/I			NO	
+ B128	Routine language SQL			YES	
  E011	Numeric data types			YES	
  E011	Numeric data types	01	INTEGER and SMALLINT data types	YES	
  E011	Numeric data types	02	REAL, DOUBLE PRECISION, and FLOAT data types	YES	
***************
*** 19,36 ****
  E011	Numeric data types	05	Numeric comparison	YES	
  E011	Numeric data types	06	Implicit casting among the numeric data types	YES	
  E021	Character data types			YES	
! E021	Character data types	01	CHARACTER data type	YES	
! E021	Character data types	02	CHARACTER VARYING data type	YES	
! E021	Character data types	03	Character literals	YES	
! E021	Character data types	04	CHARACTER_LENGTH function	YES	
! E021	Character data types	05	OCTET_LENGTH function	YES	
! E021	Character data types	06	SUBSTRING function	YES	
! E021	Character data types	07	Character concatenation	YES	
! E021	Character data types	08	UPPER and LOWER functions	YES	
! E021	Character data types	09	TRIM function	YES	
! E021	Character data types	10	Implicit casting among the character data types	YES	
! E021	Character data types	11	POSITION function	YES	
! E021	Character data types	12	Character comparison	YES	
  E031	Identifiers			YES	
  E031	Identifiers	01	Delimited identifiers	YES	
  E031	Identifiers	02	Lower case identifiers	YES	
--- 36,53 ----
  E011	Numeric data types	05	Numeric comparison	YES	
  E011	Numeric data types	06	Implicit casting among the numeric data types	YES	
  E021	Character data types			YES	
! E021	Character string types	01	CHARACTER data type	YES	
! E021	Character string types	02	CHARACTER VARYING data type	YES	
! E021	Character string types	03	Character literals	YES	
! E021	Character string types	04	CHARACTER_LENGTH function	YES	trims trailing spaces from CHARACTER values before counting
! E021	Character string types	05	OCTET_LENGTH function	YES	
! E021	Character string types	06	SUBSTRING function	YES	
! E021	Character string types	07	Character concatenation	YES	
! E021	Character string types	08	UPPER and LOWER functions	YES	
! E021	Character string types	09	TRIM function	YES	
! E021	Character string types	10	Implicit casting among the character string types	YES	
! E021	Character string types	11	POSITION function	YES	
! E021	Character string types	12	Character comparison	YES	
  E031	Identifiers			YES	
  E031	Identifiers	01	Delimited identifiers	YES	
  E031	Identifiers	02	Lower case identifiers	YES	
***************
*** 73,78 ****
--- 90,97 ----
  E081	Basic Privileges	06	REFERENCES privilege at the table level	YES	
  E081	Basic Privileges	07	REFERENCES privilege at the column level	NO	
  E081	Basic Privileges	08	WITH GRANT OPTION	YES	
+ E081	Basic Privileges	09	USAGE privilege	NO	
+ E081	Basic Privileges	10	EXECUTE privilege	YES	
  E091	Set functions			YES	
  E091	Set functions	01	AVG	YES	
  E091	Set functions	02	COUNT	YES	
***************
*** 87,93 ****
  E101	Basic data manipulation	04	Searched DELETE statement	YES	
  E111	Single row SELECT statement			YES	
  E121	Basic cursor support			NO	
! E121	Basic cursor supoprt	01	DECLARE CURSOR	YES	
  E121	Basic cursor support	02	ORDER BY columns need not be in select list	YES	
  E121	Basic cursor support	03	Value expressions in ORDER BY clause	YES	
  E121	Basic cursor support	04	OPEN statement	YES	
--- 106,112 ----
  E101	Basic data manipulation	04	Searched DELETE statement	YES	
  E111	Single row SELECT statement			YES	
  E121	Basic cursor support			NO	
! E121	Basic cursor support	01	DECLARE CURSOR	YES	
  E121	Basic cursor support	02	ORDER BY columns need not be in select list	YES	
  E121	Basic cursor support	03	Value expressions in ORDER BY clause	YES	
  E121	Basic cursor support	04	OPEN statement	YES	
***************
*** 150,160 ****
  F051	Basic date and time	02	TIME data type (including support of TIME literal) with fractional seconds precision of at least 0	YES	
  F051	Basic date and time	03	TIMESTAMP data type (including support of TIMESTAMP literal) with fractional seconds precision of at least 0 and 6	YES	
  F051	Basic date and time	04	Comparison predicate on DATE, TIME, and TIMESTAMP data types	YES	
! F051	Basic date and time	05	Explicit CAST between datetime types and character types	YES	
  F051	Basic date and time	06	CURRENT_DATE	YES	
  F051	Basic date and time	07	LOCALTIME	YES	
  F051	Basic date and time	08	LOCALTIMESTAMP	YES	
  F052	Intervals and datetime arithmetic			YES	
  F081	UNION and EXCEPT in views			YES	
  F111	Isolation levels other than SERIALIZABLE			YES	
  F111	Isolation levels other than SERIALIZABLE	01	READ UNCOMMITTED isolation level	YES	behaves like READ COMMITTED
--- 169,180 ----
  F051	Basic date and time	02	TIME data type (including support of TIME literal) with fractional seconds precision of at least 0	YES	
  F051	Basic date and time	03	TIMESTAMP data type (including support of TIMESTAMP literal) with fractional seconds precision of at least 0 and 6	YES	
  F051	Basic date and time	04	Comparison predicate on DATE, TIME, and TIMESTAMP data types	YES	
! F051	Basic date and time	05	Explicit CAST between datetime types and character string types	YES	
  F051	Basic date and time	06	CURRENT_DATE	YES	
  F051	Basic date and time	07	LOCALTIME	YES	
  F051	Basic date and time	08	LOCALTIMESTAMP	YES	
  F052	Intervals and datetime arithmetic			YES	
+ F053	OVERLAPS predicate			YES	
  F081	UNION and EXCEPT in views			YES	
  F111	Isolation levels other than SERIALIZABLE			YES	
  F111	Isolation levels other than SERIALIZABLE	01	READ UNCOMMITTED isolation level	YES	behaves like READ COMMITTED
***************
*** 175,190 ****
  F201	CAST function			YES	
  F221	Explicit defaults			YES	
  F222	INSERT statement: DEFAULT VALUES clause			YES	
! F231	Privilege Tables			YES	
! F231	Privilege Tables	01	TABLE_PRIVILEGES view	YES	
! F231	Privilege Tables	02	COLUMN_PRIVILEGES view	YES	
! F231	Privilege Tables	03	USAGE_PRIVILEGES view	YES	
  F251	Domain support			YES	
  F261	CASE expression			YES	
  F261	CASE expression	01	Simple CASE	YES	
  F261	CASE expression	02	Searched CASE	YES	
  F261	CASE expression	03	NULLIF	YES	
  F261	CASE expression	04	COALESCE	YES	
  F271	Compound character literals			YES	
  F281	LIKE enhancements			YES	
  F291	UNIQUE predicate			NO	
--- 195,212 ----
  F201	CAST function			YES	
  F221	Explicit defaults			YES	
  F222	INSERT statement: DEFAULT VALUES clause			YES	
! F231	Privilege tables			YES	
! F231	Privilege tables	01	TABLE_PRIVILEGES view	YES	
! F231	Privilege tables	02	COLUMN_PRIVILEGES view	YES	
! F231	Privilege tables	03	USAGE_PRIVILEGES view	YES	
  F251	Domain support			YES	
  F261	CASE expression			YES	
  F261	CASE expression	01	Simple CASE	YES	
  F261	CASE expression	02	Searched CASE	YES	
  F261	CASE expression	03	NULLIF	YES	
  F261	CASE expression	04	COALESCE	YES	
+ F262	Extended CASE expression			NO	
+ F263	Comma-separated predicates in simple CASE expression			NO	
  F271	Compound character literals			YES	
  F281	LIKE enhancements			YES	
  F291	UNIQUE predicate			NO	
***************
*** 199,204 ****
--- 221,227 ----
  F311	Schema definition statement	03	CREATE VIEW	YES	
  F311	Schema definition statement	04	CREATE VIEW: WITH CHECK OPTION	NO	
  F311	Schema definition statement	05	GRANT statement	YES	
+ F312	MERGE statement			NO	
  F321	User authorization			YES	
  F341	Usage tables			NO	
  F361	Subprogram support			YES	
***************
*** 207,218 ****
  F381	Extended schema manipulation	02	ALTER TABLE statement: ADD CONSTRAINT clause	YES	
  F381	Extended schema manipulation	03	ALTER TABLE statement: DROP CONSTRAINT clause	YES	
  F391	Long identifiers			YES	
  F401	Extended joined table			YES	
  F401	Extended joined table	01	NATURAL JOIN	YES	
  F401	Extended joined table	02	FULL OUTER JOIN	YES	
- F401	Extended joined table	03	UNION JOIN	YES	
  F401	Extended joined table	04	CROSS JOIN	YES	
! F411	Time zone specification			YES	
  F421	National character			YES	
  F431	Read-only scrollable cursors			YES	
  F431	Read-only scrollable cursors	01	FETCH with explicit NEXT	YES	
--- 230,243 ----
  F381	Extended schema manipulation	02	ALTER TABLE statement: ADD CONSTRAINT clause	YES	
  F381	Extended schema manipulation	03	ALTER TABLE statement: DROP CONSTRAINT clause	YES	
  F391	Long identifiers			YES	
+ F392	Unicode escapes in identifiers			NO	
+ F393	Unicode escapes in literals			NO	
  F401	Extended joined table			YES	
  F401	Extended joined table	01	NATURAL JOIN	YES	
  F401	Extended joined table	02	FULL OUTER JOIN	YES	
  F401	Extended joined table	04	CROSS JOIN	YES	
! F402	Named column joins for LOBs, arrays, and multisets			NO	
! F411	Time zone specification			YES	differences regarding literal interpretation
  F421	National character			YES	
  F431	Read-only scrollable cursors			YES	
  F431	Read-only scrollable cursors	01	FETCH with explicit NEXT	YES	
***************
*** 222,227 ****
--- 247,253 ----
  F431	Read-only scrollable cursors	05	FETCH ABSOLUTE	YES	
  F431	Read-only scrollable cursors	06	FETCH RELATIVE	YES	
  F441	Extended set function support			YES	
+ F442	Mixed column references in set functions			NO	
  F451	Character set definition			NO	
  F461	Named character sets			NO	
  F471	Scalar subquery values			YES	
***************
*** 235,241 ****
  F502	Enhanced documentation tables	01	SQL_SIZING_PROFILES view	YES	
  F502	Enhanced documentation tables	02	SQL_IMPLEMENTATION_INFO view	YES	
  F502	Enhanced documentation tables	03	SQL_PACKAGES view	YES	
! F511	BIT data type			YES	
  F521	Assertions			NO	
  F531	Temporary tables			YES	
  F555	Enhanced seconds precision			YES	
--- 261,267 ----
  F502	Enhanced documentation tables	01	SQL_SIZING_PROFILES view	YES	
  F502	Enhanced documentation tables	02	SQL_IMPLEMENTATION_INFO view	YES	
  F502	Enhanced documentation tables	03	SQL_PACKAGES view	YES	
! F511	BIT data type			YES	defined in SQL:1999, though now removed from SQL:2003
  F521	Assertions			NO	
  F531	Temporary tables			YES	
  F555	Enhanced seconds precision			YES	
***************
*** 247,253 ****
--- 273,284 ----
  F651	Catalog name qualifiers			YES	
  F661	Simple tables			NO	
  F671	Subqueries in CHECK			NO	intentionally omitted
+ F672	Retrospective check constraints			YES	
  F691	Collation and translation			NO	
+ F692	Enhanced collation support			NO	
+ F693	SQL-session and client module collations			NO	
+ F695	Translation support			NO	
+ F696	Additional translation documentation			NO	
  F701	Referential update actions			YES	
  F711	ALTER domain			YES	
  F721	Deferrable constraints			NO	foreign keys only
***************
*** 261,267 ****
  F801	Full set function			YES	
  F811	Extended flagging			NO	
  F812	Basic flagging			NO	
! F813	Extended flagging for "Core SQL Flagging" and "Catalog Lookup" only			NO	
  F821	Local table references			NO	
  F831	Full cursor update			NO	
  F831	Full cursor update	01	Updatable scrollable cursors	NO	
--- 292,298 ----
  F801	Full set function			YES	
  F811	Extended flagging			NO	
  F812	Basic flagging			NO	
! F813	Extended flagging			NO	
  F821	Local table references			NO	
  F831	Full cursor update			NO	
  F831	Full cursor update	01	Updatable scrollable cursors	NO	
***************
*** 270,275 ****
--- 301,310 ----
  S011	Distinct data types	01	USER_DEFINED_TYPES view	NO	
  S023	Basic structured types			NO	
  S024	Enhanced structured types			NO	
+ S025	Final structured types			NO	
+ S026	Self-referencing structured types			NO	
+ S027	Create method by specific method name			NO	
+ S028	Permutable UDT options list			NO	
  S041	Basic reference types			NO	
  S043	Enhanced reference types			NO	
  S051	Create table of type			NO	
***************
*** 281,298 ****
  S091	Basic array support	03	Array expressions	NO	
  S092	Arrays of user-defined types			NO	
  S094	Arrays of reference types			NO	
  S111	ONLY in query expressions			YES	
  S151	Type predicate			NO	
  S161	Subtype treatment			NO	
! S201	SQL routines on arrays			NO	
! S201	SQL routines on arrays	01	Array parameters	NO	
! S201	SQL routines on arrays	02	Array as result type of functions	NO	
  S211	User-defined cast functions			YES	
  S231	Structured type locators			NO	
  S232	Array locators			NO	
  S241	Transform functions			NO	
  S251	User-defined orderings			NO	
  S261	Specific type method			NO	
  T011	Timestamp in Information Schema			NO	
  T031	BOOLEAN data type			YES	
  T041	Basic LOB data type support			NO	
--- 316,346 ----
  S091	Basic array support	03	Array expressions	NO	
  S092	Arrays of user-defined types			NO	
  S094	Arrays of reference types			NO	
+ S095	Array constructors by query			NO	
+ S096	Optional array bounds			NO	
+ S097	Array element assignment			NO	
  S111	ONLY in query expressions			YES	
  S151	Type predicate			NO	
  S161	Subtype treatment			NO	
! S162	Subtype treatment for references			NO	
! S201	SQL-invoked routines on arrays			NO	
! S201	SQL-invoked routines on arrays	01	Array parameters	NO	
! S201	SQL-invoked routines on arrays	02	Array as result type of functions	NO	
! S202	SQL-invoked routines on multisets			NO	
  S211	User-defined cast functions			YES	
  S231	Structured type locators			NO	
  S232	Array locators			NO	
+ S233	Multiset locators			NO	
  S241	Transform functions			NO	
+ S242	Alter transform statement			NO	
  S251	User-defined orderings			NO	
  S261	Specific type method			NO	
+ S271	Basic multiset support			NO	
+ S272	Multisets of user-defined types			NO	
+ S274	Multisets of reference types			NO	
+ S275	Advanced multiset support			NO	
+ S281	Nested collection types			NO	
+ S291	Unique constraint on entire row			NO	
  T011	Timestamp in Information Schema			NO	
  T031	BOOLEAN data type			YES	
  T041	Basic LOB data type support			NO	
***************
*** 303,314 ****
--- 351,374 ----
  T041	Basic LOB data type support	05	LOB locator: non-holdable	NO	
  T042	Extended LOB data type support			NO	
  T051	Row types			NO	
+ T052	MAX and MIN for row types			NO	
+ T053	Explicit aliases for all-fields reference			NO	
+ T061	UCS support			NO	
+ T071	BIGINT data type			YES	
  T111	Updatable joins, unions, and columns			NO	
  T121	WITH (excluding RECURSIVE) in query expression			NO	
+ T122	WITH (excluding RECURSIVE) in subquery			NO	
  T131	Recursive query			NO	
+ T132	Recursive query in subquery			NO	
  T141	SIMILAR predicate			YES	
  T151	DISTINCT predicate			YES	
+ T152	DISTINCT predicate with negation			NO	
  T171	LIKE clause in table definition			YES	
+ T172	AS subquery clause in table definition			NO	
+ T173	Extended LIKE clause in table definition			NO	
+ T174	Identity columns			NO	
+ T175	Generated columns			NO	
+ T176	Sequence generator support			NO	
  T191	Referential action RESTRICT			YES	
  T201	Comparable data types for referential constraints			YES	
  T211	Basic trigger capability			NO	
***************
*** 319,333 ****
  T211	Basic trigger capability	05	Ability to specify a search condition that must be true before the trigger is invoked	NO	
  T211	Basic trigger capability	06	Support for run-time rules for the interaction of triggers and constraints	NO	
  T211	Basic trigger capability	07	TRIGGER privilege	YES	
! T211	Basic trigger capability	08	Multiple triggers for the same event are executed in the order in which they were created	NO	intentionally omitted
  T212	Enhanced trigger capability			YES	
! T231	SENSITIVE cursors			YES	
  T241	START TRANSACTION statement			YES	
  T251	SET TRANSACTION statement: LOCAL option			NO	
  T261	Chained transactions			NO	
  T271	Savepoints			YES	
  T281	SELECT privilege with column granularity			NO	
! T301	Functional Dependencies			NO	
  T312	OVERLAY function			YES	
  T321	Basic SQL-invoked routines			NO	
  T321	Basic SQL-invoked routines	01	User-defined functions with no overloading	YES	
--- 379,394 ----
  T211	Basic trigger capability	05	Ability to specify a search condition that must be true before the trigger is invoked	NO	
  T211	Basic trigger capability	06	Support for run-time rules for the interaction of triggers and constraints	NO	
  T211	Basic trigger capability	07	TRIGGER privilege	YES	
! T211	Basic trigger capability	08	Multiple triggers for the same event are executed in the order in which they were created in the catalog	NO	intentionally omitted
  T212	Enhanced trigger capability			YES	
! T231	Sensitive cursors			YES	
  T241	START TRANSACTION statement			YES	
  T251	SET TRANSACTION statement: LOCAL option			NO	
  T261	Chained transactions			NO	
  T271	Savepoints			YES	
+ T272	Enhanced savepoint management			NO	
  T281	SELECT privilege with column granularity			NO	
! T301	Functional dependencies			NO	
  T312	OVERLAY function			YES	
  T321	Basic SQL-invoked routines			NO	
  T321	Basic SQL-invoked routines	01	User-defined functions with no overloading	YES	
***************
*** 339,350 ****
  T321	Basic SQL-invoked routines	07	PARAMETERS view	YES	
  T322	Overloading of SQL-invoked functions and procedures			YES	
  T323	Explicit security for external routines			YES	
  T331	Basic roles			NO	
  T332	Extended roles			NO	
  T351	Bracketed SQL comments (/*...*/ comments)			YES	
  T401	INSERT into a cursor			NO	
  T411	UPDATE statement: SET ROW option			NO	
! T431	CUBE and ROLLUP operations			NO	
  T441	ABS and MOD functions			YES	
  T461	Symmetric BETWEEN predicate			NO	
  T471	Result sets return value			NO	
--- 400,417 ----
  T321	Basic SQL-invoked routines	07	PARAMETERS view	YES	
  T322	Overloading of SQL-invoked functions and procedures			YES	
  T323	Explicit security for external routines			YES	
+ T324	Explicit security for SQL routines			NO	
+ T325	Qualified SQL parameter references			NO	
+ T326	Table functions			NO	
  T331	Basic roles			NO	
  T332	Extended roles			NO	
  T351	Bracketed SQL comments (/*...*/ comments)			YES	
  T401	INSERT into a cursor			NO	
  T411	UPDATE statement: SET ROW option			NO	
! T431	Extended grouping capabilities			NO	
! T432	Nested and concatenated GROUPING SETS			NO	
! T433	Multiargument GROUPING function			NO	
! T434	GROUP BY DISINCT			NO	
  T441	ABS and MOD functions			YES	
  T461	Symmetric BETWEEN predicate			NO	
  T471	Result sets return value			NO	
***************
*** 355,360 ****
--- 422,439 ----
  T551	Optional key words for default syntax			YES	
  T561	Holdable locators			NO	
  T571	Array-returning external SQL-invoked functions			NO	
+ T572	Multiset-returning external SQL-invoked functions			NO	
  T581	Regular expression substring function			YES	
  T591	UNIQUE constraints of possibly null columns			YES	
  T601	Local cursor references			NO	
+ T611	Elementary OLAP operations			NO	
+ T612	Advanced OLAP operations			NO	
+ T613	Sampling			NO	
+ T621	Enhanced numeric functions			NO	
+ T631	IN predicate with one list element			NO	
+ T641	Multiple column assignment			NO	
+ T651	SQL-schema statements in SQL routines			NO	
+ T652	SQL-dynamic statements in SQL routines			NO	
+ T653	SQL-schema statements in external routines			NO	
+ T654	SQL-dynamic statements in external routines			NO	
+ T655	Cyclically dependent routines			NO

---

From: Peter Eisentraut <[email-masked]>
Date: Thu, 25 Nov 2004 19:16:47 +0100

> changes in PostgreSQL 8 and changes from SQL:1999 to SQL:2003.

Thanks.  I will evaluate this patch.

Btw., does anyone mind if I change the names of the standards to

SQL 1992
SQL 1999
SQL 2003

?  The other styles seem to be rather contrived and are not applied 
consistently.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

---

From: Troels Arvin <[email-masked]>
Date: Thu, 25 Nov 2004 20:57:39 +0100

> ?  The other styles seem to be rather contrived and are not applied

We have tried to use the official[1] short names:
SQL-92
SQL:1999
SQL:2003

- But it's true that those short names are not used consistently
throughout the documentation.

I see no reason not to use the offical short names; to me, they look OK
text-wise. But it boils down to a matter of taste.

Note 1:
http://books.elsevier.com/mk/default.asp?isbn=1558604561 has a section on
this subject.

---

From: Peter Eisentraut <[email-masked]>
Date: Thu, 25 Nov 2004 22:13:01 +0100

> We have tried to use the official[1] short names:

I could buy "SQL:1999", because then "SQL" would be a macro expanding to 
"ISO/IEC 9075".  But I don't see how SQL-92 fits in there.

> Note 1:
> http://books.elsevier.com/mk/default.asp?isbn=1558604561 has a
> section on this subject.

Note that this book is titled "SQL 1999 - Understanding Relational 
Language Components".  Apparently, they are not sure either.

---

From: Troels Arvin <[email-masked]>
Date: Thu, 25 Nov 2004 22:28:56 +0100

> "ISO/IEC 9075".  But I don't see how SQL-92 fits in there.

There was a naming change between SQL-92 and SQL:1999, according to the
mentioned book.
  The ":" is because the standard's name has been brought in line with
ISO's naming standards. The expansion to four digits was because of the
general year-2000 trend.

> Language Components".  Apparently, they are not sure either.

Yes, that's ironic. But I have the book right here. There is clearly a ":"
in the name on the front page, as well as in the colophon.

---

From: Simon Riggs <[email-masked]>
Date: Fri, 26 Nov 2004 19:52:28 +0000

> he'd better describe the ideas himself.

...since you mention it, I should explain:

The sections Supported Features and Unsupported Features cover both
Mandatory (Core) and Optional features in the same section. It would be
better to separate these, just as the SQL standard itself does in Annex
F - SQL Feature Taxonomy.

This seems especially important for the Unsupported Features section,
since the length of the list makes it look like 100% support is a long
way off, whereas it is only 14 features away, and many of them minor
[see Troels' low hanging fruit list on this thread]

Many of the optional features are definitely desirable and it is useful
information to retain the Unsupported optional features, but would be
best as a separate sub-section.

Troels tells me the sections are autogenerated, which makes the
re-editing slightly more difficult, so worth consultation first. 

Not sure who knows how to do that...or what techniques are considered
portable...XSLT?? other than maintaining two separate lists.

-- 
Best Regards, Simon Riggs

---

From: Peter Eisentraut <[email-masked]>
Date: Fri, 26 Nov 2004 23:34:16 +0100

Simon Riggs wrote:
> Mandatory (Core) and Optional features in the same section. It would
> be better to separate these, just as the SQL standard itself does in
> Annex F - SQL Feature Taxonomy.
>
> since the length of the list makes it look like 100% support is a
> long way off, whereas it is only 14 features away, and many of them
> minor [see Troels' low hanging fruit list on this thread]

If the "core" set of features were at all useful in practice then I 
would think about this, but it is not, so we'd just end up arranging 
the tables for marketing purposes instead of information purposes.  Ten 
years ago this would have been equivalent to making a separate section 
for SQL 92 Entry level and rejoicing upon completion, while realizing 
that a real-life DBMS needs at least Intermediate level.

---

From: Peter Eisentraut <[email-masked]>
Date: Sat, 27 Nov 2004 22:27:13 +0100

> changes in PostgreSQL 8 and changes from SQL:1999 to SQL:2003.

I have installed your patch and adjusted the names of the standards 
throughout to the spellings suggested by your book.

---

From: Simon Riggs <[email-masked]>
Date: Sun, 28 Nov 2004 21:35:25 +0000

> > Annex F - SQL Feature Taxonomy.

Please note that all that has been suggested is splitting a table into
two pieces, so that it matches the SQL:2003 standard's way of presenting
this information, as laid out in Annex F - SQL Feature Taxonomy. 

I found that arrangement useful in understanding the standard and wished
to recommend it to the project.

> that a real-life DBMS needs at least Intermediate level.

I agree completely with your assessment of SQL-92 Entry and Intermediate
level. Having recently spent an hour or two looking at the SQL:2003
standard, I don't think the analogy that SQL:2003 Core is similar to
SQL-92 Entry level is a useful one. I understand why people would think
this, because I would definitely have thought exactly the same, before I
looked.

For example, Microsoft SQL Server claims SQL-92 Entry level. If SQL:2003
were similar then they would simply switch the claim to SQL:2003 without
problem. They do not, because they cannot.

Please review what the list of SQL:2003 Core features contains:
SAVEPOINTS, outer joins, triggers, derived tables, quantified
sub-selects, constraints etc.. but not object-relational features, which
are only Optional. IMHO these features are useful in practice.

Yes, there are also many Optional features that are also desirable.