Subject: SQL 2003 conformance

From: Peter Eisentraut <[email-masked]>
Date: Tue, 12 Oct 2004 19:52:24 +0200

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/

---

From: elein <[email-masked]>
Date: Wed, 13 Oct 2004 17:29:02 -0700

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
[email-masked]

On Tue, Oct 12, 2004 at 07:52:24PM +0200, Peter Eisentraut wrote:
> ---------------------------(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

---

From: Peter Eisentraut <[email-masked]>
Date: Thu, 14 Oct 2004 09:14:13 +0200

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

---

From: Troels Arvin <[email-masked]>
Date: Thu, 14 Oct 2004 15:06:24 +0200

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

> conformance chapter in the documentation for SQL 2003?

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

> I know that 

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

---

From: Alvaro Herrera <[email-masked]>
Date: Thu, 14 Oct 2004 10:18:28 -0300

> 1999 would also help the group to analyse the individual feature groups 

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)

---

From: elein <[email-masked]>
Date: Thu, 14 Oct 2004 09:07:12 -0700

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

---

From: Neil Conway <[email-masked]>
Date: Mon, 18 Oct 2004 11:10:24 +1000

> 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

---

From: Tom Lane <[email-masked]>
Date: Sun, 17 Oct 2004 22:06:43 -0400

> SQL:2003 consistently. At present they refer to a mix of SQL-92,

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

---

From: Troels Arvin <[email-masked]>
Date: Mon, 18 Oct 2004 15:12:37 +0200

> 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

---

From: Peter Eisentraut <[email-masked]>
Date: Mon, 18 Oct 2004 16:12:36 +0200

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

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.

---

From: elein <[email-masked]>
Date: Tue, 19 Oct 2004 13:47:04 -0700

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:
> TIP 8: explain analyze is your friend

---

From: Troels Arvin <[email-masked]>
Date: Tue, 19 Oct 2004 22:58:20 +0200

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.

> 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

---

From: Peter Eisentraut <[email-masked]>
Date: Tue, 19 Oct 2004 23:06:07 +0200

Troels Arvin wrote:
> sql_features.txt isn't up-to-date any more, so I basically needed
> data to compare SQL:2003 with PostgreSQL's current
>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.

---

From: Troels Arvin <[email-masked]>
Date: Wed, 20 Oct 2004 22:07:30 +0200

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

>> I basically needed
> 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.

---

From: Peter Eisentraut <[email-masked]>
Date: Thu, 21 Oct 2004 14:20:49 +0200

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

---

From: Josh Berkus <[email-masked]>
Date: Thu, 21 Oct 2004 09:35:03 -0700

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.

> important for sequences, one might say that it's important for the syntax

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

-- 
Josh Berkus
Aglio Database Solutions
San Francisco