SQLvsDerbyFeatures - DERBY - Apache Software Foundation

SQLvsDerbyFeatures - DERBY - Apache Software Foundation
DUE TO SPAM, SIGN-UP IS DISABLED. Goto
Selfserve wiki signup
and request an account.
DERBY
Pages
Blog
Page tree
Browse pages
tachments (0)
Page History
Resolved comments
Page Information
View in Hierarchy
View Source
Export to PDF
Export to Word
Copy Page Tree
Jira links
SQLvsDerbyFeatures
Created by
ASF Infrabot
on
Apr 13, 2019
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-04
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
T631
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
Expected "=" to follow "describe"
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
Yes
From 10.11
F271
Compound character literals
No
No
F281
LIKE enhancements
Yes (10.3)
Yes (10.3)
In earlier versions (10-0 - 10.2): The had to be a
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
No
No
F672
Retrospective check constraints
N/A
No
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
Partial
Partial
From 10.11. Deferrable NOT NULL constraints are not supported.
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, " and ": The s BINARY, BLOB, LARGE, and OBJECT – Subclause 5.3, "": – Subclause 6.1, "": The BINARY LARGE OBJECT data type – Subclause 6.28, "": 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, " and ": The s CHARACTER, CLOB, LARGE, and OBJECT – Subclause 6.1, "": The CHARACTER LARGE OBJECT data type – Subclause 6.28, "": 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, "": The for expressions of type BINARY LARGE OBJECT and CHARACTER LARGE OBJECT – Subclause 6.27, "": The for expressions of type CHARACTER LARGE OBJECT – Subclause 6.27, "": The for expressions of type BINARY LARGE OBJECT and CHARACTER LARGE OBJECT – Subclause 6.29, "": The function for expressions of type CHARACTER LARGE OBJECT – Subclause 6.29, "": The for expressions of type CHARACTER LARGE OBJECT – Subclause 6.29, "": The – Subclause 6.29, "": The for expressions of type CHARACTER LARGE OBJECT – Subclause 6.29, "": The
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, "": The expression for expressions of type CHARACTER LARGE OBJECT – Subclause 6.28, "": The expression
Yes
Yes
T041-05
LOB locator: non-holdable – Subclause 13.3, "": – Subclause 14.14, ""
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
Yes
Yes
T201
Comparable data types for referential constraints
No
No
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.
No
No
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
Yes
Yes
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
Yes
Yes
From 10.7
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
No
T433
Multiargument GROUPING function
N/A
No
T434
GROUP BY DISTINCT
N/A
No
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
Yes
Yes
Since 10.9
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
Partial
Partial support for ROW_NUMBER since 10.4
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
No labels
Overview
Content Tools
Atlassian Confluence Open Source Project License
granted to Apache Software Foundation.
Evaluate Confluence today
Atlassian Confluence
8.5.31
Printed by Atlassian Confluence 8.5.31
Report a bug
Atlassian News
Atlassian
{"serverDuration": 147, "requestCorrelationId": "af750486512ee02e"}