Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Development" <derby-dev@db.apache.org>
Content-Type: text/plain; charset="us-ascii"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
From: Apache Wiki <wikidiffs@apache.org>
To: derby-commits@db.apache.org
Date: Thu, 13 Oct 2005 12:24:48 -0000
Message-ID: <20051013122448.1913.37436@ajax.apache.org>
Subject: [Db-derby Wiki] Update of "SQLvsDerbyFeatures" by BerntJohnsen

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 BerntJohnsen:
http://wiki.apache.org/db-derby/SQLvsDerbyFeatures

New page:
||'''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. [http://issues.apache.org/jira/browse/DERBY-158 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 [http://issues.apache.org/jira/browse/DERBY-7 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|| ||