You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by Apache Wiki <wi...@apache.org> on 2006/09/05 15:06:53 UTC
[Db-derby Wiki] Update of "SQLvsDerbyFeatures" by DagWanvik
Dear Wiki user,
You have subscribed to a wiki page or wiki category on "Db-derby Wiki" for change notification.
The following page has been changed by DagWanvik:
http://wiki.apache.org/db-derby/SQLvsDerbyFeatures
The comment on the change is:
Added SUR capability, changed wording of "sort of" to "Yes*"
------------------------------------------------------------------------------
Legend:
||<#00FF00>Yes ||The feature is supported. ||
+ ||<#FFFF00>Yes* ||The feature is supported (e.g. through JDBC) but not according to the SQL standard. ||
+ ||<#FFFF00>Partial ||The feature is partially supported. ||
||<#FF0000>No ||The feature is '''not''' supported.||
- ||<#FFFF00>Sort of ||The feature is supported (e.g. through JDBC) but not according to the SQL standard. ||
- ||<#FFFF00>Partial ||The feature is partially supported. ||
||N/A ||Does not apply. ||
|| ||Not yet documented on this page ('''You''' may contribute). ||
@@ -29, +29 @@
||E021-01||CHARACTER data type (including all its spellings)||<#00FF00>Yes||<#00FF00>Yes|| ||
||E021-02||CHARACTER VARYING data type (including all its spellings)||<#00FF00>Yes||<#00FF00>Yes|| ||
||E021-03||Character literals||<#00FF00>Yes||<#00FF00>Yes|| ||
- ||E021-04||CHARACTER_LENGTH function||<#FFFF00>Sort of||<#FFFF00>Sort of||Called LENGTH. {fn LENGTH(...)} is according to JDBC specification. ||
+ ||E021-04||CHARACTER_LENGTH function||<#FFFF00>Yes*||<#FFFF00>Yes*||Called LENGTH. {fn LENGTH(...)} is according to JDBC specification. ||
||E021-05||OCTET_LENGTH function||<#FF0000>No||<#FF0000>No|| ||
- ||E021-06||SUBSTRING function||<#FFFF00>Sort of||<#FFFF00>Sort of||Called SUBSTR. xNo FROM...FOR, just commas. {fn SUBSTRING(....)} is according to JDBC specification. ||
+ ||E021-06||SUBSTRING function||<#FFFF00>Yes*||<#FFFF00>Yes*||Called SUBSTR. xNo FROM...FOR, just commas. {fn SUBSTRING(....)} is according to JDBC specification. ||
||E021-07||Character concatenation||<#00FF00>Yes||<#00FF00>Yes|| ||
||E021-08||UPPER and LOWER functions||<#00FF00>Yes||<#00FF00>Yes|| ||
- ||E021-09||TRIM function||<#FFFF00>Sort of||<#FFFF00>Sort of||Simple LTRIM and RTRIM instead. {fn LTRIM(...)} and {fn RTRIM(...)} is according to JDBC specification. ||
+ ||E021-09||TRIM function||<#FFFF00>Yes*||<#FFFF00>Yes*||Simple LTRIM and RTRIM instead. {fn LTRIM(...)} and {fn RTRIM(...)} is according to JDBC specification. ||
||E021-10||Implicit casting among the character data types||<#00FF00>Yes||<#00FF00>Yes|| ||
- ||E021-11||POSITION function||<#FFFF00>Sort of||<#FFFF00>Sort of||Called LOCATE. {fn LOCATE(...)} is according to JDBC specification. ||
+ ||E021-11||POSITION function||<#FFFF00>Yes*||<#FFFF00>Yes*||Called LOCATE. {fn LOCATE(...)} is according to JDBC specification. ||
||E021-12||Character comparison||<#00FF00>Yes||<#00FF00>Yes|| ||
||'''E031'''||'''Identifiers'''||<#00FF00>Yes||<#00FF00>Yes|| ||
||E031-01||Delimited identifiers||<#00FF00>Yes||<#00FF00>Yes|| ||
@@ -95, +95 @@
||E101-03||Searched UPDATE statement||<#00FF00>Yes||<#FFFF00>Partial||''correlation name'' not supported||
||E101-04||Searched DELETE statement||<#00FF00>Yes||<#FFFF00>Partial||''correlation name'' not supported||
||'''E111'''||'''Single row select statement'''||<#00FF00>Yes||<#00FF00>Yes|| ||
- ||'''E121'''||'''Basic cursor support'''||<#FFFF00>Sort of||<#FFFF00>Sort of||Through JDBC ||
+ ||'''E121'''||'''Basic cursor support'''||<#FFFF00>Yes*||<#FFFF00>Yes*||Through JDBC ||
||E121-01||Declare cursor||<#FF0000>No||<#FF0000>No|| ||
||E121-02||ORDER BY columns need not be in select list||<#00FF00>Yes||<#00FF00>Yes|| ||
||E121-03||Value expressions in ORDER BY clause||<#00FF00>Yes||<#00FF00>Yes|| ||
@@ -116, +116 @@
||E141-08||NOT NULL inferred on PRIMARY KEY||<#00FF00>Yes (10.1)||<#00FF00>Yes (10.1)||Implemented from 10.1. [http://issues.apache.org/jira/browse/DERBY-158 Issue DERBY-158] ||
||E141-10||Names in a foreign key can be specified in any order||<#00FF00>Yes||<#00FF00>Yes|| ||
||'''E151'''||'''Transaction support'''||<#00FF00>Yes||<#00FF00>Yes|| ||
- ||E151-01||COMMIT statement||<#FFFF00>Sort of||<#FFFF00>Sort of||Through JDBC Connection.commit, ij supports COMMIT statement||
+ ||E151-01||COMMIT statement||<#FFFF00>Yes*||<#FFFF00>Yes*||Through JDBC Connection.commit, ij supports COMMIT statement||
- ||E151-02||ROLLBACK statement||<#FFFF00>Sort of||<#FFFF00>Sort of||Through JDBC Connection.rollback, ij supports ROLLBACK statement||
+ ||E151-02||ROLLBACK statement||<#FFFF00>Yes*||<#FFFF00>Yes*||Through JDBC Connection.rollback, ij supports ROLLBACK statement||
- ||'''E152'''||'''Basic SET TRANSACTION statement'''||<#FFFF00>Sort of.||<#FFFF00>Sort of.||Se below. ||
+ ||'''E152'''||'''Basic SET TRANSACTION statement'''||<#FFFF00>Yes*.||<#FFFF00>Yes*.||Se below. ||
- ||E152-01||SET TRANSACTION statement: ISOLATION LEVEL SERIALIZABLE clause||<#FFFF00>Sort of||<#FFFF00>Sort of||SET [CURRENT] ISOLATION SERIALIZABLE. Connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE) is according to JDBC specification. ||
+ ||E152-01||SET TRANSACTION statement: ISOLATION LEVEL SERIALIZABLE clause||<#FFFF00>Yes*||<#FFFF00>Yes*||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||<#FFFF00>Sort of||<#FFFF00>Sort of||No SQL syntax. Connection.setReadWrite() is according to JDBC specification. ||
+ ||E152-02||SET TRANSACTION statement: READ ONLY and READ WRITE clauses||<#FFFF00>Yes*||<#FFFF00>Yes*||No SQL syntax. Connection.setReadWrite() is according to JDBC specification. ||
||'''E153'''||'''Updatable queries with subqueries'''||<#FF0000>No||<#FF0000>No|| ||
||'''E161'''||'''SQL comments using leading double minus'''||<#00FF00>Yes||<#00FF00>Yes|| ||
||'''E171'''||SQLSTATE support||<#00FF00>Yes||<#00FF00>Yes||I think , have not checked all values) ||
@@ -148, +148 @@
||F041-05||Outer joins can be nested||<#00FF00>Yes||<#00FF00>Yes|| ||
||F041-07||The inner table in a left or right outer join can also be used in an inner join||<#FF0000>No||<#FF0000>No|| ||
||F041-08||All comparison operators are supported (rather than just =)||<#00FF00>Yes||<#00FF00>Yes|| ||
- ||'''F051'''||'''Basic date and time'''||<#FFFF00>Sort of||<#FFFF00>Sort of||See below. ||
+ ||'''F051'''||'''Basic date and time'''||<#FFFF00>Yes*||<#FFFF00>Yes*||See below. ||
- ||F051-01||DATE data type (including DATE literal)||<#FFFF00>Sort of||<#FFFF00>Sort of||DATE literal is implemented as built-in function. {d 'yyyy-mm-ff'} is according to JDBC specification. ||
+ ||F051-01||DATE data type (including DATE literal)||<#FFFF00>Yes*||<#FFFF00>Yes*||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||<#FFFF00>Sort of||<#FFFF00>Sort of||TIME literal is implemented as built-in function. No precision in datatype. {t' hh:mm:ss'} is according to JDBC specification. ||
+ ||F051-02||TIME data type (including TIME literal) with fractional seconds precision of 0||<#FFFF00>Yes*||<#FFFF00>Yes*||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||<#FFFF00>Sort of||<#FFFF00>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-03||TIMESTAMP data type (including TIMESTAMP literal) with fractional seconds precision of 0 and 6||<#FFFF00>Yes*||<#FFFF00>Yes*||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||<#00FF00>Yes||<#00FF00>Yes|| ||
||F051-05||Explicit CAST between datetime types and character types||<#00FF00>Yes||<#00FF00>Yes|| ||
- ||F051-06||CURRENT_DATE||<#FFFF00>Sort of||<#FFFF00>Sort of||No time zone in datetime value expression ||
+ ||F051-06||CURRENT_DATE||<#FFFF00>Yes*||<#FFFF00>Yes*||No time zone in datetime value expression ||
- ||F051-07||LOCALTIME||<#FFFF00>Sort of.||<#FFFF00>Sort of.||{fn CURTIME()} is according to JDBC specification. ||
+ ||F051-07||LOCALTIME||<#FFFF00>Yes*.||<#FFFF00>Yes*.||{fn CURTIME()} is according to JDBC specification. ||
||F051-08||LOCALTIMESTAMP||<#FF0000>No.||<#FF0000>No.|| ||
||'''F081'''||'''UNION and EXCEPT in views'''||<#FF0000>No||<#FF0000>No||UNION in 10.0. I suppose EXCEPT in 10.1, but does not work when this is written ||
||'''F131'''||'''Grouped operations'''||<#00FF00>Yes||<#00FF00>Yes|| ||
@@ -167, +167 @@
||'''F181'''||'''Multiple module support'''||<#FF0000>No||<#FF0000>No|| ||
||'''F201'''||'''CAST function'''||<#00FF00>Yes||<#00FF00>Yes|| ||
||'''F221'''||'''Explicit defaults'''||<#00FF00>Yes||<#00FF00>Yes|| ||
- ||'''F261'''||'''CASE expression'''||<#FFFF00>Sort of||<#FFFF00>Sort of|| ||
+ ||'''F261'''||'''CASE expression'''||<#FFFF00>Yes*||<#FFFF00>Yes*|| ||
||F261-01||Simple CASE||<#FF0000>No||<#FF0000>No|| ||
||F261-02||Searched CASE||<#00FF00>Yes||<#00FF00>Yes|| ||
||F261-03||NULLIF function||<#FFFF00>Partial||<#FFFF00>Partial||Works only for CHAR types. See also [http://issues.apache.org/jira/browse/DERBY-7 issue DERBY-7] ||
@@ -279, +279 @@
||'''F402'''||'''Named column joins for LOBs, arrays, and multisets'''||N/A||<#00FF00>Yes|| ||
||'''F411'''||'''Time zone specification'''||<#FF0000>No||<#FF0000>No|| ||
||'''F421'''||'''National character'''||<#FF0000>No||<#FF0000>No|| ||
- ||'''F431'''||'''Read-only scrollable cursors'''||<#FFFF00>Sort of||<#FFFF00>Sort of||Through JDBC. Only insensitive cursors.||
+ ||'''F431'''||'''Read-only scrollable cursors'''||<#FFFF00>Yes*||<#FFFF00>Yes*||Through JDBC. Only insensitive cursors.||
||F431-01||FETCH with explicit NEXT||<#FF0000>No||<#FF0000>No|| ||
||F431-02||FETCH FIRST||<#FF0000>No||<#FF0000>No|| ||
||F431-03||FETCH LAST||<#FF0000>No||<#FF0000>No|| ||
@@ -321, +321 @@
||'''F761'''||'''Session management'''||<#FF0000>No||<#FF0000>No|| ||
||'''F771'''||'''Connection management'''||<#FF0000>No||<#FF0000>No|| ||
||'''F781'''||'''Self-referencing operations'''||<#00FF00>Yes||<#00FF00>Yes|| ||
- ||'''F791'''||'''Insensitive cursors'''||<#FFFF00>Sort of||<#FFFF00>Sort of||Through JDBC||
+ ||'''F791'''||'''Insensitive cursors'''||<#FFFF00>Yes*||<#FFFF00>Yes*||Through JDBC||
||'''F801'''||'''Full set function '''||<#FFFF00>Partial||<#FFFF00>Partial||DISTINCT in more than one aggregate function will not work, but SELECT DISTINCT with DISTINCT in one aggregate function will work. ||
||'''F811'''||'''Extended flagging'''||<#FF0000>No||N/A|| ||
||'''F813'''||'''Extended flagging -- Part 1, Subclause 8.5, "SQL flagger": With "level of flagging" specified to be Core SQL Flagging and "extent of checking" specified to be Catalog Lookup'''||<#FF0000>No||<#FF0000>No|| ||
||'''F821'''||'''Local table references'''||<#FF0000>No||<#FF0000>No|| ||
- ||'''F831'''||'''Full cursor update'''||<#FFFF00>Partial||<#FFFF00>Partial||Update and delete of forward only cursors. See issues [http://issues.apache.org/jira/browse/DERBY-98 98] [http://issues.apache.org/jira/browse/DERBY-99 99] [http://issues.apache.org/jira/browse/DERBY-100 100] [http://issues.apache.org/jira/browse/DERBY-690 690]||
- ||F831-01||Updateable scrollable cursors||<#FF0000>No||<#FF0000>No||||
+ ||'''F831'''||'''Full cursor update'''||<#FFFF00>Partial||<#FFFF00>Partial||Forward-only and insensitive result set cursors only||
+ ||F831-01||Updatable scrollable cursors||<#FFFF00>Partial||<#FFFF00>Partial||insensitive result set cursors only||
- ||F831-02||Updateable ordered cursors||<#FF0000>No||<#FF0000>No|| ||
+ ||F831-02||Updatable ordered cursors||<#FF0000>No||<#FF0000>No|| ||
||'''S023'''||'''Basic structured types'''||<#FF0000>No||<#FF0000>No|| ||
||'''S024'''||'''Enhanced structured types'''||<#FF0000>No||<#FF0000>No|| ||
||'''S025'''||'''Final structured types'''||N/A||<#FF0000>No|| ||
@@ -376, +376 @@
||'''T041'''||'''Basic LOB data type support'''||<#00FF00>Yes||<#00FF00>Yes|| ||
||T041-01|| BLOB data type -- Subclause 5.2, "<token> and <separator>": The <reserved word>s BINARY, BLOB, LARGE, and OBJECT -- Subclause 5.3, "<literal>": <binary string literal> -- Subclause 6.1, "<data type>": The BINARY LARGE OBJECT data type -- Subclause 6.28, "<string value expression>": For values of type BINARY LARGE OBJECT -- Subclause 13.6, "Data type correspondences": Type correspondences for BINARY LARGE OBJECT for all supported languages||<#00FF00>Yes||<#00FF00>Yes|| ||
||T041-02|| CLOB data type -- Subclause 5.2, "<token> and <separator>": The <reserved word>s CHARACTER, CLOB, LARGE, and OBJECT -- Subclause 6.1, "<data type>": The CHARACTER LARGE OBJECT data type -- Subclause 6.28, "<string value expression>": For values of type CHARACTER LARGE OBJECT -- Subclause 13.6, "Data type correspondences": Type correspondences for CHARACTER LARGE OBJECT for all supported languages -- The implicit casting among the fixed-length and variable-length character string types supported by subfeature E021-10 is extended to support the character large object type||<#00FF00>Yes||<#00FF00>Yes|| ||
- ||T041-03|| POSITION, LENGTH, LOWER, TRIM, UPPER, and SUBSTRING functions for LOB data types -- Subclause 6.27, "<numeric value function>": The <position expression> for expressions of type BINARY LARGE OBJECT and CHARACTER LARGE OBJECT -- Subclause 6.27, "<numeric value function>": The <char length expression> for expressions of type CHARACTER LARGE OBJECT -- Subclause 6.27, "<numeric value function>": The <octet length expression> for expressions of type BINARY LARGE OBJECT and CHARACTER LARGE OBJECT -- Subclause 6.29, "<string value function>": The <fold> function for expressions of type CHARACTER LARGE OBJECT -- Subclause 6.29, "<string value function>": The <trim function> for expressions of type CHARACTER LARGE OBJECT -- Subclause 6.29, "<string value function>": The <blob trim function> -- Subclause 6.29, "<string value function>": The <character substring function> for expressions of type CHARACTER LARGE OBJECT -- Subclause 6.29, "<string value function>": The <blob
substring function> ||<#FFFF00>Sort of||<#FFFF00>Sort of||Not standard SQL syntax. See notes on features E021-04, E021-06, E021-09 and E021-11||
+ ||T041-03|| POSITION, LENGTH, LOWER, TRIM, UPPER, and SUBSTRING functions for LOB data types -- Subclause 6.27, "<numeric value function>": The <position expression> for expressions of type BINARY LARGE OBJECT and CHARACTER LARGE OBJECT -- Subclause 6.27, "<numeric value function>": The <char length expression> for expressions of type CHARACTER LARGE OBJECT -- Subclause 6.27, "<numeric value function>": The <octet length expression> for expressions of type BINARY LARGE OBJECT and CHARACTER LARGE OBJECT -- Subclause 6.29, "<string value function>": The <fold> function for expressions of type CHARACTER LARGE OBJECT -- Subclause 6.29, "<string value function>": The <trim function> for expressions of type CHARACTER LARGE OBJECT -- Subclause 6.29, "<string value function>": The <blob trim function> -- Subclause 6.29, "<string value function>": The <character substring function> for expressions of type CHARACTER LARGE OBJECT -- Subclause 6.29, "<string value function>": The <blob
substring function> ||<#FFFF00>Yes*||<#FFFF00>Yes*||Not standard SQL syntax. See notes on features E021-04, E021-06, E021-09 and E021-11||
||T041-04|| Concatenation of LOB data types -- Subclause 6.28, "<string value expression>": The <concatenation> expression for expressions of type CHARACTER LARGE OBJECT -- Subclause 6.28, "<string value expression>": The <blob concatenation> expression||<#00FF00>Yes||<#00FF00>Yes|| ||
||T041-05|| LOB locator: non-holdable -- Subclause 13.3, "<externally-invoked procedure>": <locator indication> -- Subclause 14.14, "<free locator statement>"||<#FF0000>No||<#FF0000>No|| ||
||'''T042'''||'''Extended LOB data type support'''|| || || ||
@@ -396, +396 @@
||'''T171'''||'''LIKE clause in table definition'''||<#FF0000>No||<#FF0000>No|| ||
||'''T172'''||'''AS subquery clause in table definition'''||N/A||<#FF0000>No|| ||
||'''T173'''||'''Extended LIKE clause in table definition'''||N/A||<#FF0000>No|| ||
- ||'''T174'''||'''Identity columns'''||N/A||<#FFFF00>Sort of || MAXVALUE and CYCLE not supported. Deviation from standard: A comma (",") is required before INCREMENT. ||
+ ||'''T174'''||'''Identity columns'''||N/A||<#FFFF00>Yes* || MAXVALUE and CYCLE not supported. Deviation from standard: A comma (",") is required before INCREMENT. ||
||'''T175'''||'''Generated columns'''||N/A||<#FF0000>No || ||
||'''T176'''||'''Sequence generator support'''||N/A||<#FF0000>No || ||
||'''T191'''||'''Referential action RESTRICT'''|| || || ||
||'''T201'''||'''Comparable data types for referential constraints'''|| || || ||
- ||'''T211'''||'''Basic trigger capability'''||<#FFFF00>Sort of||<#FFFF00>Sort of||Need to specify non standard MODE DB2SQL||
+ ||'''T211'''||'''Basic trigger capability'''||<#FFFF00>Yes*||<#FFFF00>Yes*||Need to specify non standard MODE DB2SQL||
||T211-01||Triggers activated on UPDATE, INSERT, or DELETE of one base table.||<#00FF00>Yes||<#00FF00>Yes|| ||
- ||T211-02||BEFORE triggers||<#FFFF00>Sort of||<#FFFF00>Sort of||Need to specify non-standard ON CASCADE BEFORE. Before triggers cannot have INSERT, UPDATE or DELETE statements as their action.||
+ ||T211-02||BEFORE triggers||<#FFFF00>Yes*||<#FFFF00>Yes*||Need to specify non-standard ON CASCADE BEFORE. Before triggers cannot have INSERT, UPDATE or DELETE statements as their action.||
||T211-03||AFTER triggers||<#00FF00>Yes||<#00FF00>Yes|| ||
||T211-04||FOR EACH ROW triggers||<#00FF00>Yes||<#00FF00>Yes|| ||
||T211-05||Ability to specify a search condition that shall be True before the trigger is invoked.||<#FF0000>No||<#FF0000>No|| ||