Thread: SQL conformance related patch

SQL conformance related patch

From
Troels Arvin
Date:
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

Attachment

Re: SQL conformance related patch

From
Peter Eisentraut
Date:
Troels Arvin wrote:
> 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.

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/

Re: SQL conformance related patch

From
Troels Arvin
Date:
On Thu, 25 Nov 2004 19:16:47 +0100, Peter Eisentraut wrote:

> 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.

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.

--
Greetings from Troels Arvin, Copenhagen, Denmark


Re: SQL conformance related patch

From
Peter Eisentraut
Date:
Troels Arvin wrote:
> We have tried to use the official[1] short names:
> SQL-92
> SQL:1999
> SQL:2003

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.

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

Re: SQL conformance related patch

From
Troels Arvin
Date:
On Thu, 25 Nov 2004 22:13:01 +0100, Peter Eisentraut wrote:

> 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.

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.

>> 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.

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.

--
Greetings from Troels Arvin, Copenhagen, Denmark


Re: SQL conformance related patch

From
Simon Riggs
Date:
On Thu, 2004-11-25 at 11:24, Troels Arvin wrote:
> 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.

...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


Re: [DOCS] SQL conformance related patch

From
Peter Eisentraut
Date:
Simon Riggs wrote:
> 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]

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.

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

Re: SQL conformance related patch

From
Peter Eisentraut
Date:
Troels Arvin wrote:
> 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.

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

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

Re: [DOCS] SQL conformance related patch

From
Simon Riggs
Date:
On Fri, 2004-11-26 at 22:34, Peter Eisentraut wrote:
> Simon Riggs wrote:
> > 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.
> >

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.

> > 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]
>
> 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.

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.

--
Best Regards, Simon Riggs


Re: SQL conformance related patch

From
Troels Arvin
Date:
On Sat, 27 Nov 2004 22:27:13 +0100, Peter Eisentraut wrote:

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

Great.

A follow-up patch for current CVS HEAD is attached, and available at
http://troels.arvin.dk/db/pgsql/conformance/pgsql-sql-conformance-
followup.patch

The patch
 - includes a core feature ID that had been left
   out by mistake (C011)
 - updates the sql_feature_packages.txt table to
   reflect changes in SQL:2003 which were not
   covered properly in my last patch

--
Greetings from Troels Arvin, Copenhagen, Denmark

Attachment

Re: SQL conformance related patch

From
Bruce Momjian
Date:
Patch applied.  Thanks.

---------------------------------------------------------------------------


Troels Arvin wrote:
> On Sat, 27 Nov 2004 22:27:13 +0100, Peter Eisentraut wrote:
>
> > I have installed your patch and adjusted the names of the standards
> > throughout to the spellings suggested by your book.
>
> Great.
>
> A follow-up patch for current CVS HEAD is attached, and available at
> http://troels.arvin.dk/db/pgsql/conformance/pgsql-sql-conformance-
> followup.patch
>
> The patch
>  - includes a core feature ID that had been left
>    out by mistake (C011)
>  - updates the sql_feature_packages.txt table to
>    reflect changes in SQL:2003 which were not
>    covered properly in my last patch
>
> --
> Greetings from Troels Arvin, Copenhagen, Denmark

[ Attachment, skipping... ]

>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073