Thread: 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@varlena.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@samurai.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