SQL-99 and SQL-2003 features mapped to Derby

Introduction

This document shows the features as defined in SQL-99 (ANSI/ISO/IEC 9075-2-1999: for Information Technology – Database Languages – SQL – Part 2: Foundation (SQL/Foundation)) and SQL-2003 (ISO/IEC 9075-2: Information technology — Database languages — SQL — Part 2: Foundation (SQL/Foundation)) and whether they are implemented in Derby.

Legend:

Yes

The feature is supported.

Yes*

The feature is supported (e.g. through JDBC) but not according to the SQL standard. See explanatory comment.

Partial

The feature is partially supported.

No

The feature is not supported.

N/A

Does not apply.

Not yet documented on this page (You may contribute).

Map

SQL-99 Core / SQL-2003 Mandatory

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

Yes*

Yes*

Called LENGTH. {fn LENGTH(...)} is according to JDBC specification.

E021-05

OCTET_LENGTH function

No

No

E021-06

SUBSTRING function

Yes*

Yes*

Called SUBSTR. xNo 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

Yes (10.3)

Yes (10.3)

Earlier versions (10.0 - 10.2): Simple LTRIM and RTRIM. {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

Yes*

Yes*

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 (10.3)

Yes (10.3)

In earlier versions (10-0 - 10.2): Direct column references in patterns was not supported. Issue DERBY-2147

E061-05

LIKE predicate: ESCAPE clause

Yes (10.3)

Yes (10.3)

In earlier versions (10-0 - 10.2): Direct column references in escape clause was not supported. Issue DERBY-2147

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

Yes (10.1)

Yes (10.1)

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

Partial (10.2)

Partial (10.2)

E081-01

SELECT privilege at the table level

Yes (10.2)

Yes (10.2)

E081-02

DELETE privilege

Yes (10.2)

Yes (10.2)

E081-03

INSERT privilege at the table level

Yes (10.2)

Yes (10.2)

E081-04

UPDATE privilege at the table level

Yes (10.2)

Yes (10.2)

E081-05

UPDATE privilege at the column level

Yes (10.2)

Yes (10.2)

E081-06

REFERENCES privilege at the table level

Yes (10.2)

Yes (10.2)

E081-07

REFERENCES privilege at the column level

Yes (10.2)

Yes (10.2)

E081-08

WITH GRANT OPTION

No

No

E081-09

USAGE privilege

No

No

E081-10

EXECUTE privilege

Yes (10.2)

Yes (10.2)

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

Partial

correlation name not supported

E101-04

Searched DELETE statement

Yes

Partial

correlation name not supported

E111

Single row select statement

Yes

Yes

E121

Basic cursor support

Yes*

Yes*

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

Partial

correlation name not supported

E121-07

Positioned DELETE statement

Yes

Partial

correlation name not supported

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

Yes (10.1)

Yes (10.1)

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*

Through JDBC Connection.commit, ij supports COMMIT statement

E151-02

ROLLBACK statement

Yes*

Yes*

Through JDBC Connection.rollback, ij supports ROLLBACK statement

E152

Basic SET TRANSACTION statement

Yes*.

Yes*.

Se below.

E152-01

SET TRANSACTION statement: ISOLATION LEVEL SERIALIZABLE clause

Yes*

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

Yes*

Yes*

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

Yes (10.2)

Yes (10.2)

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

Yes (10.2)

Yes (10.2)

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

Yes*

Yes*

See below.

F051-01

DATE data type (including DATE literal)

Yes*

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

Yes*

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

Yes*

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

Yes

Yes

F051-05

Explicit CAST between datetime types and character types

Yes

Yes

F051-06

CURRENT_DATE

Yes*

Yes*

No time zone in datetime value expression

F051-07

LOCALTIME

Yes*.

Yes*.

{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

Yes*

Yes*

F261-01

Simple CASE

No

No

F261-02

Searched CASE

Yes

Yes

F261-03

NULLIF function

Yes (10.1.3)

Yes (10.1.3)

Works only for CHAR types prior to 10.1.3.

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

Yes (10.2)

Yes (10.2)

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

SQL-99 Outside Core / SQL-2003 Optional

Feature ID

Feature name

SQL-99 Outside Core

SQL-2003 Optional

Note

B011

Embedded Ada

N/A

No

B012

Embedded C

N/A

No

B013

Embedded COBOL

N/A

No

B014

Embedded Fortran

N/A

No

B015

Embedded MUMPS

N/A

No

B016

Embedded Pascal

N/A

No

B017

Embedded PL/I

N/A

No

B021

Direct SQL

N/A

No

B031

Basic dynamic SQL

N/A

No

B032

Extended dynamic SQL

N/A

No

B032-01

<describe input statement>

N/A

No

B033

Untyped SQL-invoked function arguments

N/A

No

B034

Dynamic specification of cursor attributes

N/A

No

B041

Extensions to embedded SQL exception declarations

N/A

No

B051

Enhanced execution rights

N/A

No

B111

Module language Ada

N/A

No

B112

Module language C

N/A

No

B113

Module language COBOL

N/A

No

B114

Module language Fortran

N/A

No

B115

Module language MUMPS

N/A

No

B116

Module language Pascal

N/A

No

B117

Module language PL/I

N/A

No

B121

Routine language Ada

N/A

No

B122

Routine language C

N/A

No

B123

Routine language COBOL

N/A

No

B124

Routine language Fortran

N/A

No

B125

Routine language MUMPS

N/A

No

B126

Routine language Pascal

N/A

No

B127

Routine language PL/I

N/A

No

B128

Routine language SQL

N/A

No

F032

CASCADE drop behavior

No

No

F033

ALTER TABLE statement: DROP COLUMN clause

Yes

Yes

From 10.3

F034

Extended REVOKE statement

No

No

Issue DERBY-1631, Issue DERBY-1632 and Issue DERBY-1782

F034-01

REVOKE statement performed by other than the owner of a schema object

No

No

F034-02

REVOKE statement: GRANT OPTION FOR clause

No

No

F034-03

REVOKE statement to revoke a privilege that the grantee has WITH GRANT OPTION

No

No

F052

Intervals and datetime arithmetic

No

No

F053

OVERLAPS predicate

N/A

No

F111

Isolation levels other than SERIALIZABLE

Yes

Yes

F111-01

READ UNCOMMITTED isolation level

Yes

Yes

F111-02

READ COMMITTED isolation level

Yes

Yes

F111-03

REPEATABLE READ isolation level

Yes

Yes

F121

Basic diagnostics management

No

No

F121-01

GET DIAGNOSTICS statement

No

No

F121-02

SET TRANSACTION statement: DIAGNOSTICS SIZE clause

No

No

F171

Multiple schemas per user

No

No

Derby does not support SQL users

F191

Referential delete actions

No

No

F222

INSERT statement: DEFAULT VALUES clause

No

No

F231

Privilege tables

No

No

F231-01

TABLE_PRIVILEGES view

No

No

F231-02

COLUMN_PRIVILEGES view

No

No

F231-03

USAGE_PRIVILEGES view

No

No

F251

Domain support

No

No

F262

Extended CASE expression

N/A

F271

Compound character literals

No

No

F281

LIKE enhancements

Yes (10.3)

Yes (10.3)

In earlier versions (10-0 - 10.2): The <escape character> had to be a <value specification>

F291

UNIQUE predicate

No

No

F301

CORRESPONDING in query expressions

No

No

F302

INTERSECT table operator

Yes

Yes

F302-01

INTERSECT DISTINCT table operator

Yes

Yes

F302-02

INTERSECT ALL table operator

Yes

Yes

F304

EXCEPT ALL table operator

Yes

Yes

F312

MERGE statement

N/A

No

F321

User authorization

No

No

F341

Usage tables

No

No

F361

Subprogram support

No

No

F381

Extended schema manipulation

Partial

Partial

F381-01

ALTER TABLE statement: ALTER COLUMN clause

Partial

Partial

F381-02

ALTER TABLE statement: ADD CONSTRAINT clause

Partial

Partial

F381-03

ALTER TABLE statement: DROP CONSTRAINT clause

Yes

Yes

F391

Long identifiers

Yes

Yes

F392

Unicode escapes in identifiers

N/A

No

F393

Unicode escapes in literals

N/A

No

F401

Extended joined table

Partial

Partial

F401-01

NATURAL JOIN

Yes

Yes

Since 10.6

F401-02

FULL OUTER JOIN

No

No

F401-04

CROSS JOIN

Yes

Yes

Since 10.6

F402

Named column joins for LOBs, arrays, and multisets

N/A

Yes

F411

Time zone specification

No

No

F421

National character

No

No

F431

Read-only scrollable cursors

Yes*

Yes*

Through JDBC. (Only insensitive cursors. See T231)

F431-01

FETCH with explicit NEXT

No

No

F431-02

FETCH FIRST

No

No

F431-03

FETCH LAST

No

No

F431-04

FETCH PRIOR

No

No

F431-05

FETCH ABSOLUTE

No

No

F431-06

FETCH RELATIVE

No

No

F441

Extended set function support

No

No

F442

Mixed column references in set functions

N/A

No

F451

Character set definition

No

No

F461

Named character sets

No

No

F491

Constraint management

Yes

Yes

F502

Enhanced documentation tables

No

No

F502-01

SQL_SIZING_PROFILES view

No

No

F502-02

SQL_IMPLEMENTATION_INFO view

No

No

F502-03

SQL_PACKAGES view

No

No

F521

Assertions

No

No

F531

Temporary tables

Partial

Partial

Global tables

F555

Enhanced seconds precision

No

No

F561

Full value expressions

No

No

F571

Truth value tests

No

No

Due for 10.2? Issue DERBY-499

F591

Derived tables

Yes

Yes

F611

Indicator data types

No

No

F641

Row and table constructors

Yes

Yes

F651

Catalog name qualifiers

No

No

F661

Simple tables

No

No

F671

Subqueries in CHECK

F672

Retrospective check constraints

N/A

F691

Collation and translation

Partial

Partial

DERBY-1478 Starting Derby 10.3, user can create a database with territory based collation

F692

Enhanced collation support

N/A

No

F693

SQL-session and client module collations

N/A

No

F695

Translation support

N/A

No

F696

Additional translation documentation

N/A

No

F701

Referential update actions

Partial

Partial

F711

ALTER domain

No

No

F721

Deferrable constraints

No

No

F731

INSERT column privileges

No

No

F741

Referential MATCH types

No

No

F751

View CHECK enhancements

No

No

F761

Session management

No

No

F771

Connection management

No

No

F781

Self-referencing operations

Yes

Yes

F791

Insensitive cursors

Yes*

Yes*

Through JDBC

F801

Full set function

Partial

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

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

No

No

F821

Local table references

No

No

F831

Full cursor update

Partial

Partial

F831-01

Updatable scrollable cursors

Partial (10.2)

Partial (10.2)

Since 10.2: Insensitive result set cursors (Sensitive: See T231)

F831-02

Updatable ordered cursors

No

No

S023

Basic structured types

No

No

S024

Enhanced structured types

No

No

S025

Final structured types

N/A

No

S026

Self-referencing structured types

N/A

No

S027

Create method by specific method name

N/A

No

S028

Permutable UDT options list

N/A

No

S041

Basic reference types

No

No

S043

Enhanced reference types

No

No

S051

Create table of type

No

No

S071

SQL paths in function and type name resolution

No

No

S081

Subtables

No

No

S091

Basic array support

No

No

S091-01

Arrays of built-in data types

No

No

S091-02

Arrays of distinct types

No

No

S091-03

Array expressions

No

No

S092

Arrays of user-defined types

No

No

S094

Arrays of reference types

No

No

S095

Array constructors by query

N/A

No

S096

Optional array bounds

N/A

No

S097

Array element assignment

N/A

No

S111

ONLY in query expressions

No

No

S151

Type predicate

No

No

S161

Subtype treatment

No

No

S162

Subtype treatment for references

N/A

No

S201

SQL-invoked routines on arrays

No

No

S201-01

Array parameters

No

No

S201-02

Array as result type of functions

No

No

S202

SQL-invoked routines on multisets

N/A

No

S211

User-defined cast functions

No

No

S231

Structured type locators

No

No

S232

Array locators

No

No

S233

Multiset locators

N/A

No

S241

Transform functions

No

No

S242

Alter transform statement

N/A

No

S251

User-defined orderings

No

No

S261

Specific type method

No

No

S271

Basic multiset support

N/A

No

S272

Multisets of user-defined types

N/A

No

S274

Multisets of reference types

N/A

No

S275

Advanced multiset support

N/A

No

S281

Nested collection types

N/A

No

S291

Unique constraint on entire row

N/A

No

T011

Timestamp in Information Schema

No

No

T031

BOOLEAN data type

Yes

Yes

Since 10.7.

T041

Basic LOB data type support

Yes

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

Yes

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

Yes

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>

Yes*

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

Yes

Yes

T041-05

LOB locator: non-holdable -- Subclause 13.3, "<externally-invoked procedure>": <locator indication> -- Subclause 14.14, "<free locator statement>"

No

No

T042

Extended LOB data type support

Partial

Partial

CAST and string value functions implemented. No comparision or ordering.

T051

Row types

No

No

T052

MAX and MIN for row types

N/A

No

T053

Explicit aliases for all-fields reference

N/A

No

T061

UCS support

N/A

No

T071

BIGINT data type

N/A

Yes

T111

Updatable joins, unions, and columns

No

No

T121

WITH (excluding RECURSIVE) in query expression

No

No

T122

WITH (excluding RECURSIVE) in subquery

N/A

No

T131

Recursive query

No

No

T132

Recursive query in subquery

N/A

No

T141

SIMILAR predicate

No

No

T151

DISTINCT predicate

No

No

T152

DISTINCT predicate with negation

N/A

No

T171

LIKE clause in table definition

No

No

T172

AS subquery clause in table definition

N/A

No

T173

Extended LIKE clause in table definition

N/A

No

T174

Identity columns

N/A

Yes*

MAXVALUE and CYCLE not supported. Deviation from standard: A comma (",") is required before INCREMENT.

T175

Generated columns

N/A

Yes

Since 10.5

T176

Sequence generator support

N/A

Yes*

ALTER SEQUENCE not supported. Only one NEXT VALUE FOR clause per sequence in each statement (DERBY-4513).

T191

Referential action RESTRICT

T201

Comparable data types for referential constraints

T211

Basic trigger capability

Yes*

Yes*

10.2 and earlier: Need to specify non standard MODE DB2SQL (see DERBY-1770). Also, the REFERENCING part is not quite compliant. The optional ROW keyword is not allowed, and NEW TABLE/OLD TABLE must be written NEW_TABLE/OLD_TABLE

T211-01

Triggers activated on UPDATE, INSERT, or DELETE of one base table.

Yes

Yes

T211-02

BEFORE triggers

Yes*

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

Yes

Yes

T211-04

FOR EACH ROW triggers

Yes

Yes

T211-05

Ability to specify a search condition that shall be True before the trigger is invoked.

No

No

T211-06

Support for run-time rules for the interaction of triggers and constraints.

T211-07

TRIGGER privilege

Yes (10.2)

Yes (10.2)

T211-08

Multiple triggers for the same event are executed in the order in which they were created in the catalog.

Yes

Yes

I think....

T212

Enhanced trigger capability

T231

Sensitive cursors

No

No

T241

START TRANSACTION statement

No

No

T251

SET TRANSACTION statement: LOCAL option

No

No

T261

Chained transactions

No

No

T271

Savepoints

Yes

Yes

T272

Enhanced savepoint management

N/A

No

T281

SELECT privilege with column granularity

Yes (10.2)

Yes (10.2)

T301

Functional dependencies

No

No

T312

OVERLAY function

No

No

T322

Overloading of SQL-invoked functions and procedures

T323

Explicit security for external routines

T324

Explicit security for SQL routines

N/A

No

T325

Qualified SQL parameter references

N/A

No

T326

Table functions

N/A

Partial

T331

Basic roles

Partial

Partial

T332

Extended roles

Partial

Partial

Only CURRENT_ROLE

T351

Bracketed SQL comments (/*...*/ comments)

Yes

Yes

Ready for release 10.4

T431

Extended grouping capabilities

Partial

Partial support for GROUP BY ROLLUP since 10.6

T432

Nested and concatenated GROUPING SETS

N/A

T433

Multiargument GROUPING function

N/A

T434

GROUP BY DISINCT

N/A

T441

ABS and MOD functions

Yes

Yes

T461

Symmetric BETWEEN predicate

No

No

T471

Result sets return value

No

No

T491

LATERAL derived table

No

No

T501

Enhanced EXISTS predicate

No

No

T511

Transaction counts

No

No

T541

Updatable table references

No

N/A

T551

Optional key words for default syntax

No

No

T561

Holdable locators

No

No

T571

Array-returning external SQL-invoked functions

No

No

T572

Multiset-returning external SQL-invoked functions

N/A

No

T581

Regular expression substring function

No

No

T591

UNIQUE constraints of possibly null columns

Yes (10.4)

Yes (10.4)

T601

Local cursor references

No

No

T611

Elementary OLAP operations

N/A

No

T612

Advanced OLAP operations

N/A

No

T613

Sampling

N/A

No

T621

Enhanced numeric functions

N/A

No

T641

Multiple column assignment

N/A

No

T651

SQL-schema statements in SQL routines

No

No

T652

SQL-dynamic statements in SQL routines

No

No

T653

SQL-schema statements in external routines

No

No

T654

SQL-dynamic statements in external routines

No

No

T655

Cyclically dependent routines

No

No

SQLvsDerbyFeatures (last edited 2011-11-18 11:01:10 by KnutAndersHatlen)