Return-Path: Delivered-To: apmail-db-derby-dev-archive@www.apache.org Received: (qmail 68259 invoked from network); 11 Apr 2005 12:19:38 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur.apache.org with SMTP; 11 Apr 2005 12:19:37 -0000 Received: (qmail 34354 invoked by uid 500); 11 Apr 2005 12:19:35 -0000 Delivered-To: apmail-db-derby-dev-archive@db.apache.org Received: (qmail 34285 invoked by uid 500); 11 Apr 2005 12:19:33 -0000 Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: List-Id: Reply-To: "Derby Development" Delivered-To: mailing list derby-dev@db.apache.org Received: (qmail 34269 invoked by uid 99); 11 Apr 2005 12:19:33 -0000 X-ASF-Spam-Status: No, hits=0.5 required=10.0 tests=HTML_MESSAGE,HTML_TAG_EXIST_TBODY,HTML_TITLE_EMPTY,UPPERCASE_25_50 X-Spam-Check-By: apache.org Received-SPF: pass (hermes.apache.org: local policy) Received: from brmea-mail-3.Sun.COM (HELO brmea-mail-3.sun.com) (192.18.98.34) by apache.org (qpsmtpd/0.28) with ESMTP; Mon, 11 Apr 2005 05:19:32 -0700 Received: from phys-bur-2 ([129.148.9.73]) by brmea-mail-3.sun.com (8.12.10/8.12.9) with ESMTP id j3BCJTXi005499 for ; Mon, 11 Apr 2005 06:19:30 -0600 (MDT) Received: from conversion-daemon.bur-mail1.east.sun.com by bur-mail1.east.sun.com (iPlanet Messaging Server 5.2 HotFix 1.24 (built Dec 19 2003)) id <0IES00B017DZX9@bur-mail1.east.sun.com> (original mail from Lance.Andersen@Sun.COM) for derby-dev@db.apache.org; Mon, 11 Apr 2005 08:19:29 -0400 (EDT) Received: from [127.0.0.1] (vpn-129-150-65-208.East.Sun.COM [129.150.65.208]) by bur-mail1.east.sun.com (iPlanet Messaging Server 5.2 HotFix 1.24 (built Dec 19 2003)) with ESMTPA id <0IES00FFB7KE8D@bur-mail1.east.sun.com> for derby-dev@db.apache.org; Mon, 11 Apr 2005 08:19:29 -0400 (EDT) Date: Mon, 11 Apr 2005 08:19:28 -0400 From: "Lance J. Andersen" Subject: Re: SQL-99 Core vs. Derby features In-reply-to: <20050411092317.GB24242@atum01.norway.sun.com> To: Derby Development Message-id: <425A6B50.9030006@sun.com> MIME-version: 1.0 Content-type: multipart/alternative; boundary="Boundary_(ID_1uX4tE5XE/beYPOYLGmLIQ)" X-Accept-Language: en-us, en User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.7.2) Gecko/20040804 Netscape/7.2 (ax) References: <20050411092317.GB24242@atum01.norway.sun.com> X-Virus-Checked: Checked X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N This is a multi-part message in MIME format. --Boundary_(ID_1uX4tE5XE/beYPOYLGmLIQ) Content-type: text/plain; charset=us-ascii; format=flowed Content-transfer-encoding: 7BIT Hi Bernt, Could we also do the same for SQL2003? We are moving JDBC 4 to SQL2003 from SQL99, so this would be helpful to have. Regards Lance Bernt M. Johnsen wrote: >Hi all, > >I have missed a detailed map of the Derby features against the SQL >standard, so I made one myself against SQL-99 Core (Appendix F in >ISO/IEC 9075-2:1999). I think it would be useful for the community >e.g. in the docs as soon as it is bug free, so please comment on >it. Anyway, a first version is attached. > >Note that by "JDBC" in the map, I refer to the embedded driver. > >Best regards >Bernt > > > > ------------------------------------------------------------------------ > > > SQL-99 Core vs Derby Features > > > Feature ID Feature name Supported Note > E011 Numeric data types Yes > E011-01 INTEGER and SMALLINT data types (including all spellings) > Yes > E011-02 REAL, DOUBLE PRECISON, and FLOAT data types Yes > E011-03 DECIMAL and NUMERIC data types Yes > E011-04 Arithmetic operators Yes > E011-05 Numeric comparison Yes > E011-06 Implicit casting among the numeric data types Yes > E021 Character data types Yes > E021-01 CHARACTER data type (including all its spellings) Yes > E021-02 CHARACTER VARYING data type (including all its spellings) > Yes > E021-03 Character literals Yes > E021-04 CHARACTER_LENGTH function Sort of Called LENGTH. {fn > LENGTH(...)} is according to JDBC standard. > E021-05 OCTET_LENGTH function No > E021-06 SUBSTRING function Sort of Called SUBSTR. No FROM...FOR, > just commas. {fn SUBSTRING(....)} is according to JDBC standard. > E021-07 Character concatenation Yes > E021-08 UPPER and LOWER functions Yes > E021-09 TRIM function Sort of Simple LTRIM and RTRIM instead. {fn > LTRIM(...)} and {fn RTRIM(...)} is according to JDBC standard. > E021-10 Implicit casting among the character data types Yes > E021-11 POSITION function Sort of Called LOCATE. {fn LOCATE(...)} > is according to JDBC standard. > E021-12 Character comparison Yes > E031 Identifiers Yes > E031-01 Delimited identifiers Yes > E031-02 Lower case identifiers Yes > E031-03 Trailing underscore Yes > E051 Basic query specification Yes > E051-01 SELECT DISTINCT Yes > E051-02 GROUP BY clause Yes > E051-04 GROUP BY can contain columns not in select-list Yes > E051-05 Select list items can be renamed Yes > E051-06 HAVING clause Yes > E051-07 Qualified * in select list Yes > E051-08 Correlation names in the FROM clause Yes > E051-09 Rename columns in the FROM clause Yes > E061 Basic predicates and search conditions Yes > E061-01 Comparison predicate Yes > E061-02 BETWEEN predicate Yes > E061-03 IN predicate with list of values Yes > E061-04 LIKE predicate Yes > E061-05 LIKE predicate: ESCAPE clause Yes > E061-06 NULL predicate Yes > E061-07 Quantified comparison predicate Yes > E061-08 EXISTS predicate Yes > E061-09 Subqueries in comparison predicate Yes > E061-11 Subqueries in IN predicate Yes > E061-12 Subqueries in quantified comparison predicate Yes > E061-13 Correlated subqueries Yes > E061-14 Search condition Yes > E071 Basic query expressions Yes > E071-01 UNION DISTINCT table operator Yes > E071-02 UNION ALL table operator Yes > E071-03 EXCEPT DISTINCT table operator No From 10.1 > E071-05 Columns combined via table operators need not have exactly > the same data type Yes > E071-06 Table operators in subqueries Yes > E081 Basic Privileges No > E081-01 SELECT privilege at the table level No > E081-02 DELETE privilege No > E081-03 INSERT privilege at the table level No > E081-04 UPDATE privilege at the table level No > E081-05 UPDATE privilege at the column level No > E081-06 REFERENCES privilege at the table level No > E081-07 REFERENCES privilege at the column level No > E081-08 WITH GRANT OPTION No > E081-09 USAGE privilege No > E081-10 EXECUTE privilege No > E091 Set functions Yes > E091-01 AVG Yes > E091-02 COUNT Yes > E091-03 MAX Yes > E091-04 MIN Yes > E091-05 SUM Yes > E091-06 ALL quantifier Yes > E091-07 Distinct qualifier Yes > E101 Basic data manipulation Yes > E101-01 INSERT statement Yes > E101-03 Searched UPDATE statement Yes > E101-04 Searched DELETE statement Yes > E111 Single row select statement Yes > E121 Basic cursor support Sort of Through JDBC > E121-01 Declare cursor No > E121-02 ORDER BY columns need not be in select list Yes > E121-03 Value expressions in ORDER BY clause Yes > E121-04 Open statement No > E121-06 Positioned UPDATE statement Yes > E121-07 Positioned DELETE statement Yes > E121-08 CLOSE statement No > E121-10 FETCH statement No > E121-17 WITH HOLD cursors No > E131 Null value support (nulls in lieu of values) Yes > E141 Basic integrity constraints Yes > E141-01 NOT NULL constraints Yes > E141-02 UNIQUE constraints of NOT NULL columns Yes > E141-03 PRIMARY KEY constraints Yes > E141-04 Basic FOREIGN KEY constraint with the NO ACTION default Yes > E141-06 CHECK constraints Yes > E141-07 Column defaults Yes > E141-08 NOT NULL inferred on PRIMARY KEY No Implemented from 10.1. > Issue DERBY-158 > E141-10 Names in a foreign key can be specified in any order Yes > E151 Transaction support Yes > E151-01 COMMIT statement Yes > E151-02 ROLLBACK statement Yes > E152 Basic SET TRANSACTION statement Sort of. Se below. > E152-01 SET TRANSACTION statement: ISOLATION LEVEL SERIALIZABLE > clause Sort of SET [CURRENT] ISOLATION SERIALIZABLE. > Connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE) > is according to JDBC standard. > E152-02 SET TRANSACTION statement: READ ONLY and READ WRITE clauses > Sort of No SQL syntax. Connection.setReadWrite() is according to JDBC > standard. > E153 Updatable queries with subqueries No > E161 SQL comments using leading double minus Yes > E171 SQLSTATE support Yes I think , have not checked all values) > F021 Basic information schema No Note: JDBC DatabaseMetadata is ok. > F021-01 COLUMNS view No > F021-02 TABLES view No > F021-03 VIEWS view No > F021-04 TABLE_CONSTRAINTS view No > F021_05 REFERENTIAL_CONSTRAINTS No > F021-06 CHECK_CONSTRAINTS No > F031 Basic schema manipulation Yes > F031-01 CREATE TABLE statement to create persistent base tables Yes > F031-02 CREATE VIEW statement Yes > F031-03 GRANT statement No > F031-04 ALTER TABLE statement: ADD COLUMN clause Yes > F031-13 DROP TABLE statement: RESTRICT clause Yes (Implicit) > F031-16 DROP VIEW statement: RESTRICT clause Yes (Implicit) > F031-19 REVOKE statement: RESTRICT clause No > F041 Basic joined tables Yes > F041-01 Inner join (but not necessarily the INNER keyword) Yes > F041-02 INNER keyword Yes > F041-03 LEFT OUTER JOIN Yes > F041-03 RIGHT OUTER JOIN Yes > F041-05 Outer joins can be nested Yes > F041-07 The inner table in a left or right outer join can also be > used in an inner join No > F041-08 All comparison operators are supported (rather than just > =) Yes > F051 Basic date and time Sort of See below. > F051-01 DATE data type (including DATE literal) Sort of DATE > literal is implemented as built-in function. {d 'yyyy-mm-ff'} is > according to JDBC standard. > F051-02 TIME data type (including TIME literal) with fractional > seconds precision of 0 Sort of TIME literal is implemented as > built-in function. No precision in datatype. {t' hh:mm:ss'} is > according to JDBC standard. > F051-03 TIMESTAMP data type (including TIMESTAMP literal) with > fractional seconds precision of 0 and 6 Sort of TIMESTAMP literal is > implemented as built-in function. No precision spec in datatype. No > timezone spec in datatype. {ts 'yyyy-mm-dd hh:mm:ss.f...'} is > according to JDBC standard. > F051-04 Comparison predicate on DATE, TIME, and TIMESTAMP data > types Yes > F051-05 Explicit CAST between datetime types and character types Yes > F051-06 CURRENT_DATE Sort of No time zone in datetime value expression > F051-07 LOCALTIME Sort of. {fn CURTIME()} is according to JDBC > standard. > F051-08 LOCALTIMESTAMP No. > F081 UNION and EXCEPT in views No UNION in 10.0. I suppose EXCEPT > in 10.1, but does not work when this is written > F131 Grouped operations Yes > F131-01 WHERE, GROUP BY, and HAVING clauses supported in queries with > grouped views Yes > F131-02 Multiple tables supported in queries with grouped views Yes > F131-03 Set functions supported in queries with grouped views Yes > F131-04 Subqueries with GROUP BY and HAVING clauses and grouped > views Yes > F131-05 Single row SELECT with GROUP BY and HAVING clauses and > grouped views Yes > F181 Multiple module support No > F201 CAST function Yes > F221 Explicit defaults Yes > F261 CASE expression Sort of > F261-01 Simple CASE No > F261-02 Searched CASE Yes > F261-03 NULLIF function Sort of Works only for CHAR types. See also > issue DERBY-7 > F261-04 COALESCE function Yes > F311 Schema definition statement Yes > F311-01 Create schema Yes > F311-02 CREATE TABLE for persistent base tables Yes > F311-03 CREATE VIEW Yes > F311-04 CREATE VIEW: WITH CHECK OPTION No > F311-05 GRANT statement No > F471 Scalar subquery values Yes > F481 Expanded NULL predicate Yes > F501 Features and conformance views No > F501-01 SQL_FEATURES view No > F501-02 SQL_SIZING view No > F501-03 SQL_LANGUAGES view No > F812 Basic flagging No > S011 Distinct data types No > S011-01 USER_DEFINED_TYPES view No > T321 Basic SQL invoked routines Yes Language Java. Have not checked > all the details > T321-01 User-defined functions with no overloading Yes > T321-02 User-defined stored procedures with no overloading Yes > T321-03 Function invocation Yes > T321-04 CALL statement Yes > T321-05 RETURN statement No > T321-06 ROUTINES view No > T321-07 PARAMETERS view No > --Boundary_(ID_1uX4tE5XE/beYPOYLGmLIQ) Content-type: text/html; charset=us-ascii Content-transfer-encoding: 7BIT Hi Bernt,

Could we also do the same for SQL2003?

We are moving JDBC 4 to SQL2003 from SQL99, so this would be helpful to have.

Regards
Lance

Bernt M. Johnsen wrote:
Hi all,

I have missed a detailed map of the Derby features against the SQL
standard, so I made one myself against SQL-99 Core (Appendix F in
ISO/IEC 9075-2:1999). I think it would be useful for the community
e.g. in the docs as soon as it is bug free, so please comment on
it. Anyway, a first version is attached.

Note that by "JDBC" in the map, I refer to the embedded driver. 

Best regards
Bernt


SQL-99 Core vs Derby Features

SQL-99 Core vs Derby Features
Feature ID Feature name Supported Note
E011 Numeric data types Yes  
E011-01 INTEGER and SMALLINT data types (including all spellings) Yes  
E011-02 REAL, DOUBLE PRECISON, and FLOAT data types Yes  
E011-03 DECIMAL and NUMERIC data types Yes  
E011-04 Arithmetic operators Yes  
E011-05 Numeric comparison Yes  
E011-06 Implicit casting among the numeric data types Yes  
E021 Character data types Yes  
E021-01 CHARACTER data type (including all its spellings) Yes  
E021-02 CHARACTER VARYING data type (including all its spellings) Yes  
E021-03 Character literals Yes  
E021-04 CHARACTER_LENGTH function Sort of Called LENGTH. {fn LENGTH(...)} is according to JDBC standard.
E021-05 OCTET_LENGTH function No  
E021-06 SUBSTRING function Sort of Called SUBSTR. No FROM...FOR, just commas. {fn SUBSTRING(....)} is according to JDBC standard.
E021-07 Character concatenation Yes  
E021-08 UPPER and LOWER functions Yes  
E021-09 TRIM function Sort of Simple LTRIM and RTRIM instead. {fn LTRIM(...)} and {fn RTRIM(...)} is according to JDBC standard.
E021-10 Implicit casting among the character data types Yes  
E021-11 POSITION function Sort of Called LOCATE. {fn LOCATE(...)} is according to JDBC standard.
E021-12 Character comparison Yes  
E031 Identifiers Yes  
E031-01 Delimited identifiers Yes  
E031-02 Lower case identifiers Yes  
E031-03 Trailing underscore Yes  
E051 Basic query specification Yes  
E051-01 SELECT DISTINCT Yes  
E051-02 GROUP BY clause Yes  
E051-04 GROUP BY can contain columns not in select-list Yes  
E051-05 Select list items can be renamed Yes  
E051-06 HAVING clause Yes  
E051-07 Qualified * in select list Yes  
E051-08 Correlation names in the FROM clause Yes  
E051-09 Rename columns in the FROM clause Yes  
E061 Basic predicates and search conditions Yes  
E061-01 Comparison predicate Yes  
E061-02 BETWEEN predicate Yes  
E061-03 IN predicate with list of values Yes  
E061-04 LIKE predicate Yes  
E061-05 LIKE predicate: ESCAPE clause Yes  
E061-06 NULL predicate Yes  
E061-07 Quantified comparison predicate Yes  
E061-08 EXISTS predicate Yes  
E061-09 Subqueries in comparison predicate Yes  
E061-11 Subqueries in IN predicate Yes  
E061-12 Subqueries in quantified comparison predicate Yes  
E061-13 Correlated subqueries Yes  
E061-14 Search condition Yes  
E071 Basic query expressions Yes  
E071-01 UNION DISTINCT table operator Yes  
E071-02 UNION ALL table operator Yes  
E071-03 EXCEPT DISTINCT table operator No From 10.1
E071-05 Columns combined via table operators need not have exactly the same data type Yes  
E071-06 Table operators in subqueries Yes  
E081 Basic Privileges No  
E081-01 SELECT privilege at the table level No  
E081-02 DELETE privilege No  
E081-03 INSERT privilege at the table level No  
E081-04 UPDATE privilege at the table level No  
E081-05 UPDATE privilege at the column level No  
E081-06 REFERENCES privilege at the table level No  
E081-07 REFERENCES privilege at the column level No  
E081-08 WITH GRANT OPTION No  
E081-09 USAGE privilege No  
E081-10 EXECUTE privilege No  
E091 Set functions Yes  
E091-01 AVG Yes  
E091-02 COUNT Yes  
E091-03 MAX Yes  
E091-04 MIN Yes  
E091-05 SUM Yes  
E091-06 ALL quantifier Yes  
E091-07 Distinct qualifier Yes  
E101 Basic data manipulation Yes  
E101-01 INSERT statement Yes  
E101-03 Searched UPDATE statement Yes  
E101-04 Searched DELETE statement Yes  
E111 Single row select statement Yes  
E121 Basic cursor support Sort of Through JDBC
E121-01 Declare cursor No  
E121-02 ORDER BY columns need not be in select list Yes  
E121-03 Value expressions in ORDER BY clause Yes  
E121-04 Open statement No  
E121-06 Positioned UPDATE statement Yes  
E121-07 Positioned DELETE statement Yes  
E121-08 CLOSE statement No  
E121-10 FETCH statement No  
E121-17 WITH HOLD cursors No  
E131 Null value support (nulls in lieu of values) Yes  
E141 Basic integrity constraints Yes  
E141-01 NOT NULL constraints Yes  
E141-02 UNIQUE constraints of NOT NULL columns Yes  
E141-03 PRIMARY KEY constraints Yes  
E141-04 Basic FOREIGN KEY constraint with the NO ACTION default Yes  
E141-06 CHECK constraints Yes  
E141-07 Column defaults Yes  
E141-08 NOT NULL inferred on PRIMARY KEY No Implemented from 10.1. Issue DERBY-158
E141-10 Names in a foreign key can be specified in any order Yes  
E151 Transaction support Yes  
E151-01 COMMIT statement Yes  
E151-02 ROLLBACK statement Yes  
E152 Basic SET TRANSACTION statement Sort of. Se below.
E152-01 SET TRANSACTION statement: ISOLATION LEVEL SERIALIZABLE clause Sort of SET [CURRENT] ISOLATION SERIALIZABLE. Connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE) is according to JDBC standard.
E152-02 SET TRANSACTION statement: READ ONLY and READ WRITE clauses Sort of No SQL syntax. Connection.setReadWrite() is according to JDBC standard.
E153 Updatable queries with subqueries No  
E161 SQL comments using leading double minus Yes  
E171 SQLSTATE support Yes I think , have not checked all values)
F021 Basic information schema No Note: JDBC DatabaseMetadata is ok.
F021-01 COLUMNS view No  
F021-02 TABLES view No  
F021-03 VIEWS view No  
F021-04 TABLE_CONSTRAINTS view No  
F021_05 REFERENTIAL_CONSTRAINTS No  
F021-06 CHECK_CONSTRAINTS No  
F031 Basic schema manipulation Yes  
F031-01 CREATE TABLE statement to create persistent base tables Yes  
F031-02 CREATE VIEW statement Yes  
F031-03 GRANT statement No  
F031-04 ALTER TABLE statement: ADD COLUMN clause Yes  
F031-13 DROP TABLE statement: RESTRICT clause Yes (Implicit)  
F031-16 DROP VIEW statement: RESTRICT clause Yes (Implicit)  
F031-19 REVOKE statement: RESTRICT clause No  
F041 Basic joined tables Yes  
F041-01 Inner join (but not necessarily the INNER keyword) Yes  
F041-02 INNER keyword Yes  
F041-03 LEFT OUTER JOIN Yes  
F041-03 RIGHT OUTER JOIN Yes  
F041-05 Outer joins can be nested Yes  
F041-07 The inner table in a left or right outer join can also be used in an inner join No  
F041-08 All comparison operators are supported (rather than just =) Yes  
F051 Basic date and time Sort of See below.
F051-01 DATE data type (including DATE literal) Sort of DATE literal is implemented as built-in function. {d 'yyyy-mm-ff'} is according to JDBC standard.
F051-02 TIME data type (including TIME literal) with fractional seconds precision of 0 Sort of TIME literal is implemented as built-in function. No precision in datatype. {t' hh:mm:ss'} is according to JDBC standard.
F051-03 TIMESTAMP data type (including TIMESTAMP literal) with fractional seconds precision of 0 and 6 Sort of TIMESTAMP literal is implemented as built-in function. No precision spec in datatype. No timezone spec in datatype. {ts 'yyyy-mm-dd hh:mm:ss.f...'} is according to JDBC standard.
F051-04 Comparison predicate on DATE, TIME, and TIMESTAMP data types Yes  
F051-05 Explicit CAST between datetime types and character types Yes  
F051-06 CURRENT_DATE Sort of No time zone in datetime value expression
F051-07 LOCALTIME Sort of. {fn CURTIME()} is according to JDBC standard.
F051-08 LOCALTIMESTAMP No.  
F081 UNION and EXCEPT in views No UNION in 10.0. I suppose EXCEPT in 10.1, but does not work when this is written
F131 Grouped operations Yes  
F131-01 WHERE, GROUP BY, and HAVING clauses supported in queries with grouped views Yes  
F131-02 Multiple tables supported in queries with grouped views Yes  
F131-03 Set functions supported in queries with grouped views Yes  
F131-04 Subqueries with GROUP BY and HAVING clauses and grouped views Yes  
F131-05 Single row SELECT with GROUP BY and HAVING clauses and grouped views Yes  
F181 Multiple module support No  
F201 CAST function Yes  
F221 Explicit defaults Yes  
F261 CASE expression Sort of  
F261-01 Simple CASE No  
F261-02 Searched CASE Yes  
F261-03 NULLIF function Sort of Works only for CHAR types. See also issue DERBY-7
F261-04 COALESCE function Yes  
F311 Schema definition statement Yes  
F311-01 Create schema Yes  
F311-02 CREATE TABLE for persistent base tables Yes  
F311-03 CREATE VIEW Yes  
F311-04 CREATE VIEW: WITH CHECK OPTION No  
F311-05 GRANT statement No  
F471 Scalar subquery values Yes  
F481 Expanded NULL predicate Yes  
F501 Features and conformance views No  
F501-01 SQL_FEATURES view No  
F501-02 SQL_SIZING view No  
F501-03 SQL_LANGUAGES view No  
F812 Basic flagging No  
S011 Distinct data types No  
S011-01 USER_DEFINED_TYPES view No  
T321 Basic SQL invoked routines Yes Language Java. Have not checked all the details
T321-01 User-defined functions with no overloading Yes  
T321-02 User-defined stored procedures with no overloading Yes  
T321-03 Function invocation Yes  
T321-04 CALL statement Yes  
T321-05 RETURN statement No  
T321-06 ROUTINES view No  
T321-07 PARAMETERS view No  

--Boundary_(ID_1uX4tE5XE/beYPOYLGmLIQ)--