Re: SQL 2003 conformance

Is anyone feeling masochistic and wants to review the SQL standards
conformance chapter in the documentation for SQL 2003? I know that
several features were added or revised for SQL 2003 conformance, so it
would be nice to have that documented for the 8.0 release.

I think an overview of how the structure of SQL 2003 differs from SQL
1999 would also help the group to analyse the individual feature groups
more quickly.

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

I have a couple of the new features written up, but nothing
like an entire review. If someone can tackle this job
and wants help, I'll help (but I cannot sign up for the
whole thing). Or if someone wants the material
on the items I have done, just email me.

--elein
elein(at)varlena(dot)com

On Tue, Oct 12, 2004 at 07:52:24PM +0200, Peter Eisentraut wrote:
> Is anyone feeling masochistic and wants to review the SQL standards
> conformance chapter in the documentation for SQL 2003? I know that
> several features were added or revised for SQL 2003 conformance, so it
> would be nice to have that documented for the 8.0 release.
>
> I think an overview of how the structure of SQL 2003 differs from SQL
> 1999 would also help the group to analyse the individual feature groups
> more quickly.
>
> --
> Peter Eisentraut
> http://developer.postgresql.org/~petere/
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match

elein wrote:
> I have a couple of the new features written up, but nothing
> like an entire review. If someone can tackle this job
> and wants help, I'll help (but I cannot sign up for the
> whole thing). Or if someone wants the material
> on the items I have done, just email me.

I was not asking for a review of the new features, I was talking about
reviewing (revising, updating, ...) the existing SQL conformance
feature list in the documentation.

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

On Tue, 12 Oct 2004 19:52:24 +0200, Peter Eisentraut wrote:

> Is anyone feeling masochistic and wants to review the SQL standards
> conformance chapter in the documentation for SQL 2003?

That means a revision of src/backend/catalog/sql_features.txt ?

> I know that
> several features were added or revised for SQL 2003 conformance, so it
> would be nice to have that documented for the 8.0 release.

True. I'll try putting some time into it. Elein, we could possibly discus
some of the issues that might come up on the freenode channel?

--
Greetings from Troels Arvin, Copenhagen, Denmark

On Tue, Oct 12, 2004 at 07:52:24PM +0200, Peter Eisentraut wrote:

> I think an overview of how the structure of SQL 2003 differs from SQL
> 1999 would also help the group to analyse the individual feature groups
> more quickly.

Some guys from the SQL committee put up a paper on this:

http://www.acm.org/sigmod/record/issues/0403/E.JimAndrew-standard.pdf

It may be helpful.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"La virtud es el justo medio entre dos defectos" (Aristóteles)

What I've got is how the new SQL2003 features
work with Postgres (or not). This is based on
the paper that Alvaro suggested later in this thread.
The information is helpful in reviewing
postgres' conformance list.

Anyway, Troels, ping me on IRC.

--elein

On Thu, Oct 14, 2004 at 09:14:13AM +0200, Peter Eisentraut wrote:
> elein wrote:
> > I have a couple of the new features written up, but nothing
> > like an entire review. If someone can tackle this job
> > and wants help, I'll help (but I cannot sign up for the
> > whole thing). Or if someone wants the material
> > on the items I have done, just email me.
>
> I was not asking for a review of the new features, I was talking about
> reviewing (revising, updating, ...) the existing SQL conformance
> feature list in the documentation.
>
> --
> Peter Eisentraut
> http://developer.postgresql.org/~petere/

On Wed, 2004-10-13 at 03:52, Peter Eisentraut wrote:
> Is anyone feeling masochistic and wants to review the SQL standards
> conformance chapter in the documentation for SQL 2003?

Another useful improvement would be to update the "Standards
conformance" section of the SQL command reference pages to refer to
SQL:2003 consistently. At present they refer to a mix of SQL-92,
SQL:1999, and SQL:2003.

-Neil

Neil Conway <neilc(at)samurai(dot)com> writes:
> On Wed, 2004-10-13 at 03:52, Peter Eisentraut wrote:
>> Is anyone feeling masochistic and wants to review the SQL standards
>> conformance chapter in the documentation for SQL 2003?

> Another useful improvement would be to update the "Standards
> conformance" section of the SQL command reference pages to refer to
> SQL:2003 consistently. At present they refer to a mix of SQL-92,
> SQL:1999, and SQL:2003.

I think it's a good idea to refer to the first version of the spec in
which the feature appeared. A global search-and-replace would amount
to removing information. (This is of course not meant to imply that
we've gotten it right everywhere, but that's what I'd like to think is
meant by referring to particular versions.)

If your intent is not to refer to any particular version then you should
just say "SQL", anyhow.

regards, tom lane

On Tue, 12 Oct 2004 19:52:24 +0200, Peter Eisentraut wrote:

> Is anyone feeling masochistic and wants to review the SQL standards
> conformance chapter in the documentation for SQL 2003?

Elein and I am working on it.

Part of the work was to store the SQL standard's feature IDs, etc, in a
usable manner. A result of that is available at
http://troels.arvin.dk/db/rdbms/sql-standard/2003/sql:2003-features.sql
The SQL creates some tables and views that may be used to explore
differences in SQL:2003 with views in the INFORMATION_SCHEMA.

My current - work-in-progress - patch aginst pgsql-HEAD is here:
http://troels.arvin.dk/db/pgsql/conformance/pgsql-sql-conformance.patch
The patch _will_ change, as some work is still pending, primarily:
Checking if any of SQL:2003's new features are conformed to, and -
conversely: checking of any of PostgreSQL 8's new features are coverede by
the standard somehow.

Some of the changes are rather boring, consisting mainly of feature name
changes, to bring the naming more in line with SQL:2003. Other changes
aren't only related to naming, see for example the changes for
E021-04
E081-10
F053
F672
T071
T176

Some (of the) stuff that I'm unsure of:
http://troels.arvin.dk/db/pgsql/conformance/in-doubt-features.txt

I have also started a little list of SQL:2003 features that I think might
be relatively easy to implement in PostgreSQL:
http://troels.arvin.dk/db/pgsql/conformance/low-hanging-fruits.txt

--
Greetings from Troels Arvin, Copenhagen, Denmark

Troels Arvin wrote:
> Part of the work was to store the SQL standard's feature IDs, etc, in
> a usable manner. A result of that is available at
> http://troels.arvin.dk/db/rdbms/sql-standard/2003/sql:2003-features.s
>ql The SQL creates some tables and views that may be used to explore
> differences in SQL:2003 with views in the INFORMATION_SCHEMA.

We already have all that set up. All you need to do is edit the files
src/backend/catalog/sql_features.txt and sql_feature_packages.txt.

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

Are these files loaded into the catalog somewhere?
Perhaps for the info_schema?

--elein

On Mon, Oct 18, 2004 at 04:12:36PM +0200, Peter Eisentraut wrote:
> Troels Arvin wrote:
> > Part of the work was to store the SQL standard's feature IDs, etc, in
> > a usable manner. A result of that is available at
> > http://troels.arvin.dk/db/rdbms/sql-standard/2003/sql:2003-features.s
> >ql The SQL creates some tables and views that may be used to explore
> > differences in SQL:2003 with views in the INFORMATION_SCHEMA.
>
> We already have all that set up. All you need to do is edit the files
> src/backend/catalog/sql_features.txt and sql_feature_packages.txt.
>
> --
> Peter Eisentraut
> http://developer.postgresql.org/~petere/
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend

On Tue, 2004-10-19 at 13:47 -0700, elein wrote:

> > We already have all that set up. All you need to do is edit the
> > files src/backend/catalog/sql_features.txt and
> > sql_feature_packages.txt.

> Are these files loaded into the catalog somewhere?
> Perhaps for the info_schema?

Yes, see INFORMATION_SCHEMA.SQL_FEATURES. However, with SQL:2003,
sql_features.txt isn't up-to-date any more, so I basically needed data
to compare SQL:2003 with PostgreSQL's current
INFORMATION_SCHEMA.SQL_FEATURES view; hence, I created
http://troels.arvin.dk/db/rdbms/sql-standard/2003/sql:2003-features.sql

--
Greetings from Troels Arvin, Copenhagen, Denmark

Troels Arvin wrote:
> Yes, see INFORMATION_SCHEMA.SQL_FEATURES. However, with SQL:2003,
> sql_features.txt isn't up-to-date any more, so I basically needed
> data to compare SQL:2003 with PostgreSQL's current
> INFORMATION_SCHEMA.SQL_FEATURES view; hence, I created
> http://troels.arvin.dk/db/rdbms/sql-standard/2003/sql:2003-features.s
>ql

Yes, about 45% of the job that I proposed would be updating
sql_features.txt with the new feature set (the other 55% being toggling
the yes/no flags appropriately). Please extract the required format
from the data you're working with.

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

On Tue, 19 Oct 2004 23:06:07 +0200, Peter Eisentraut wrote:

>> I basically needed
>> data to compare SQL:2003 with PostgreSQL's current
>> INFORMATION_SCHEMA.SQL_FEATURES view; hence, I created
>> http://troels.arvin.dk/db/rdbms/sql-standard/2003/sql:2003-features.sql
>
> Yes, about 45% of the job that I proposed would be updating
> sql_features.txt with the new feature set

That has been done, see latest part of
http://troels.arvin.dk/db/pgsql/conformance/pgsql-sql-conformance.patch

What I've done is add the new SQL:2003-stuff and initially set all the new
stuff to 'NO' in conformance status. Elein and I are now selectively
changing NO to YES where appropriate. Some NOs have been changed to YES
already (B122, E081, F053, F672, T071); on the TODO-list is to look closer
at

B034 | Dynamic specification of cursor attributes
S095 | Array constructors by query
S096 | Optional array bounds
S097 | Array element assignment
S201-01 | SQL-invoked routines on arrays - Array parameters
S201-02 | SQL-invoked routines on arrays - Array as result type of functions
S291 | Unique constraint on entire row
T053 | Explicit aliases for all-fields reference
T061 | UCS support
T152 | DISTINCT predicate with negation
T172 | AS subquery clause in table definition
T272 | Enhanced savepoint management
T326 | Table functions
T631 | IN predicate with one list element
T641 | Multiple column assignment
T651 | SQL-schema statements in SQL routines
T652 | SQL-dynamic statements in SQL routines
T653 | SQL-schema statements in external routines
T654 | SQL-dynamic statements in external routines
T655 | Cyclically dependent routines

I didn't include the feature IDs from SQL/CLI, SQL/JRT, SQL/MED, SQL/OLB,
SQL/PSM and SQL/XML in the patch; do you think that those feature IDs
should be included? - I doubt that we will have time to look at all that
stuff, but if someone thinks that PostgreSQL 8 might actually implement
features from some of those feature sets, please tell us which of them to
look closer at. If someone _knows_ that the mentioned feature sets are
_not_ implemented, then I'll simply include them in the patch and set
conformance to NO.

An obvious question is how strict to be:

I believe that PostgreSQL 8 doesn't implement SQL:2003's IDENTITY columns,
although a primary key column of type SERIAL (and a proper trigger in case
of the ALLWAYS identity variant) can be used for the same thing.

Sequences are allmost implemented, except PostgreSQL doesn't have the NEXT
VALUE FOR (uses nextval()). As NEXT VALUE FOR functionality is rather
important for sequences, one might say that it's important for the syntax
to be equal here.

--
Greetings from Troels Arvin, Copenhagen, Denmark

Troels Arvin wrote:
> An obvious question is how strict to be:

Very strict. SQL is a standard, not a guideline. If you can't type in
what it says, then it's not supported.

We make occasional exceptions in extreme cases. For example, we claim
to support aliases in the select list (E051-05), but we require the AS.
It would be unhelpful if we instead wrote that we don't support select
list aliases. But a comment should be added in these cases.

The other exception is that because of additional features that
PostgreSQL provides, some standard features may be restricted.
Additional reserved key words are an obvious example. But that is
allowed by the SQL standard.

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

Troels,

> I believe that PostgreSQL 8 doesn't implement SQL:2003's IDENTITY columns,

You're correct. Mind you, IDENTITY was a mistake, and I'm happy we don't
implement it.

> Sequences are allmost implemented, except PostgreSQL doesn't have the NEXT
> VALUE FOR (uses nextval()). As NEXT VALUE FOR functionality is rather
> important for sequences, one might say that it's important for the syntax
> to be equal here.

Hmmm ... good point. Would be worth bringing up on Hackers as a TODO
suggestion.

--
Josh Berkus
Aglio Database Solutions
San Francisco