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 ofCalled LENGTH. {fn LENGTH(...)} is according to JDBC standard.
E021-05 OCTET_LENGTH function No 
E021-06 SUBSTRING function Sort ofCalled 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 ofSimple 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 ofCalled 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-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 NoFrom 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-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 ofThrough 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 NoImplemented 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 ofSET [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 ofNo 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 YesI think , have not checked all values)
F021 Basic information schema NoNote: JDBC DatabaseMetadata is ok.
F021-01 COLUMNS view No 
F021-02 TABLES view No 
F021-03 VIEWS view 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-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 ofSee below.
F051-01 DATE data type (including DATE literal) Sort ofDATE 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 ofTIME 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 ofTIMESTAMP 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 ofNo time zone in datetime value expression
F051-07 LOCALTIME Sort of.{fn CURTIME()} is according to JDBC standard.
F081 UNION and EXCEPT in views NoUNION 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 ofWorks 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-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 
T321 Basic SQL invoked routines YesLanguage 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