db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Lance J. Andersen" <Lance.Ander...@Sun.COM>
Subject Re: SQL-99 Core vs. Derby features
Date Mon, 11 Apr 2005 12:19:28 GMT
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 


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
> ------------------------------------------------------------------------
>   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 <http://issues.apache.org/jira/browse/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.
> 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 	 
> 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 <http://issues.apache.org/jira/browse/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 	 
> 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 	 

View raw message