Re: SQL-99 Core vs. Derby features

11 Apr 2005 13:25:17 -0000 

>>>>>>>>>>>> Lance J. Andersen wrote (2005-04-11 08:19:28):
> 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.

There were minor differences, so I have added SQL2003 to the table.

-- 
Bernt Marius Johnsen, Database Technology Group, 
Sun Microsystems, Trondheim, Norway
Title: SQL-99 Core vs Derby Features
SQL-99 Core vs Derby Features

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

Reply via email to