The ANSI/ISO SQL-2016 standard is divided into a number of named features. In this appendix it is shown which of these features that are supported in Mimer SQL. The information can also be retrieved from the data dictionary by selecting from the view INFORMATION_SCHEMA.SQL_FEATURES.
Feature ID |
Feature name |
Supported |
B012 |
Embedded C |
Yes |
B013 |
Embedded COBOL |
Yes |
B014 |
Embedded Fortran |
Yes |
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 string 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 |
Yes |
E021-05 |
OCTET_LENGTH function |
Yes |
E021-06 |
SUBSTRING function |
Yes |
E021-07 |
Character concatenation |
Yes |
E021-08 |
UPPER and LOWER functions |
Yes |
E021-09 |
TRIM function |
Yes |
E021-10 |
Implicit casting among the fixed-length and variable-length character string types |
Yes |
E021-11 |
POSITION function |
Yes |
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 |
Yes |
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 |
Yes |
E081-01 |
SELECT privilege at the table level |
Yes |
E081-02 |
DELETE privilege |
Yes |
E081-03 |
INSERT privilege at the table level |
Yes |
E081-04 |
UPDATE privilege at the table level |
Yes |
E081-05 |
UPDATE privilege at the column level |
Yes |
E081-06 |
REFERENCES privilege at the table level |
Yes |
E081-07 |
REFERENCES privilege at the column level |
Yes |
E081-08 |
WITH GRANT OPTION |
Yes |
E081-09 |
USAGE privilege |
Yes |
E081-10 |
EXECUTE privilege |
Yes |
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 |
Yes |
E121-01 |
DECLARE CURSOR |
Yes |
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 |
Yes |
E121-06 |
Positioned UPDATE statement |
Yes |
E121-07 |
Positioned DELETE statement |
Yes |
E121-08 |
CLOSE statement |
Yes |
E121-10 |
FETCH statement: implicit NEXT |
Yes |
E121-17 |
WITH HOLD cursors |
Yes |
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 for both referential delete action and referential update action |
Yes |
E141-06 |
CHECK constraints |
Yes |
E141-07 |
Column defaults |
Yes |
E141-08 |
NOT NULL inferred on PRIMARY KEY |
Yes |
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 |
Yes |
E152-01 |
SET TRANSACTION statement: ISOLATION LEVEL SERIALIZABLE clause |
Yes |
E152-02 |
SET TRANSACTION statement: READ ONLY and READ WRITE clauses |
Yes |
E153 |
Updatable queries with subqueries |
Yes |
E161 |
SQL comments using leading double minus |
Yes |
E171 |
SQLSTATE support |
Yes |
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 |
Yes |
F031-04 |
ALTER TABLE statement: ADD COLUMN clause |
Yes |
F031-13 |
DROP TABLE statement: RESTRICT clause |
Yes |
F031-16 |
DROP VIEW statement: RESTRICT clause |
Yes |
F031-19 |
REVOKE statement: RESTRICT clause |
Yes |
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-04 |
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 |
Yes |
F041-08 |
All comparison operators are supported (rather than just =) |
Yes |
F051 |
Basic date and time |
Yes |
F051-01 |
DATE data type (including DATE literal) |
Yes |
F051-02 |
TIME data type (including TIME literal) with fractional seconds precision of 0 |
Yes |
F051-03 |
TIMESTAMP data type (including TIMESTAMP literal) with fractional seconds precision of 0 and 6 |
Yes |
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 |
Yes |
F051-07 |
LOCALTIME |
Yes |
F051-08 |
LOCALTIMESTAMP |
Yes |
F081 |
UNION and EXCEPT in views |
No |
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 |
Yes |
F201 |
CAST function |
Yes |
F221 |
Explicit defaults |
Yes |
F261 |
CASE expression |
Yes |
F261-01 |
Simple CASE |
Yes |
F261-02 |
Searched CASE |
Yes |
F261-03 |
NULLIF |
Yes |
F261-04 |
COALESCE |
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-04 |
CREATE VIEW: WITH CHECK OPTION |
Yes |
F311-05 |
GRANT statement |
Yes |
F471 |
Scalar subquery values |
Yes |
F481 |
Expanded NULL predicate |
Yes |
F812 |
Basic flagging |
Yes |
S011 |
Distinct data types |
Yes |
T321 |
Basic SQL invoked routines |
Yes |
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 |
Yes |
Features Outside Core Supported by Mimer SQL
Feature ID |
Feature Name |
B021 |
Direct SQL |
B031 |
Basic dynamic SQL |
B032 |
Extended dynamic SQL |
B032-01 |
<describe input> statement |
B033 |
Untyped SQL invoked function argument |
B112 |
Module language C |
B113 |
Module language COBOL |
B114 |
Module language Fortran |
B128 |
Routine language SQL |
F032 |
CASCADE drop behavior |
F033 |
ALTER TABLE statement: DROP COLUMN clause |
F034 |
Extended REVOKE statement |
F052 |
Intervals and datetime arithmetic |
F053 |
OVERLAPS predicate |
F054 |
Timestamp in DATE type precedence list |
F111 |
Isolation levels other than SERIALIZABLE |
F111-01 |
READ UNCOMMITTED isolation level |
F111-02 |
READ COMMITTED isolation level |
F111-03 |
REPEATABLE READ isolation level |
F121 |
Basic diagnostics management |
F121-01 |
GET DIAGNOSTICS statement |
F121-02 |
SET TRANSACTION statement: DIAGNOSTICS SIZE clause |
F122 |
Enhanced diagnostics management |
F171 |
Multiple schemas per user |
F191 |
Referential delete actions |
F222 |
INSERT statement: DEFAULT VALUES clause |
F231 |
Privilege tables |
F251 |
Domain support |
F271 |
Compound character literals |
F281 |
LIKE enhancements |
F291 |
UNIQUE predicate |
F302 |
INTERSECT table operator |
F302-01 |
INTERSECT DISTINCT table operator |
F302-02 |
INTERSECT ALL table operator |
F304 |
EXCEPT ALL table operator |
F341 |
Usage tables |
F361 |
Subprogram support |
F381 |
Extended schema manipulation |
F381-01 |
ALTER TABLE statement: ALTER COLUMN clause |
F381-02 |
ALTER TABLE statement: ADD CONSTRAINT clause |
F381-03 |
ALTER TABLE statement: DROP CONSTRAINT clause |
F382 |
Alter column data type |
F391 |
Long identifiers |
F392 |
Unicode escapes in identifiers |
F393 |
Unicode escapes in literals |
F401-01 |
NATURAL JOIN |
F401-04 |
CROSS JOIN |
F421 |
National character |
F431 |
Read-only scrollable cursors |
F441 |
Extended set function support |
F442 |
Mixed column references in set functions |
F491 |
Constraint management |
F555 |
Enhanced seconds precision |
F561 |
Full value expressions |
F571 |
Truth value tests |
F591 |
Derived tables |
F661 |
Simple tables |
F672 |
Retrospective check constraints |
F673 |
Reads SQL-data routine invocations in CHECK constraints |
F690 |
Collation support |
F692 |
Enhanced collation support |
F701 |
Referential update actions |
F721 |
Deferrable constraints |
F731 |
INSERT column privileges |
F771 |
Connection management |
F781 |
Self-referencing operations |
F850 |
Top-level <order by clause> in <query expression> |
F851 |
<order by clause> in subqueries |
F852 |
Top-level <order by clause> in views |
F855 |
Nested <order by clause> in <query expression> |
F856 |
Nested <fetch first clause> in <query expression> |
F857 |
Top-level <fetch first clause> in <query expression> |
F858 |
<fetch first clause> in subqueries |
F859 |
Top-level <fetch first clause> in views |
F860 |
dynamic <fetch first row count> in <fetch first clause> |
F861 |
Top-level <result offset clause> in <query expression> |
F862 |
<result offset clause> in subqueries |
F863 |
Nested <result offset clause> in <query expression> |
F864 |
Top-level <result offset clause> in views |
F865 |
dynamic <offset row count> in <result offset clause> |
P001 |
Stored Modules |
P002 |
Computational completeness |
P003 |
Information Schema views |
P004 |
Extended CASE statement |
P005 |
Qualified SQL variable references |
P006 |
Multiple assignment |
S028 |
Permutable UDT options list |
T011 |
Timestamp in Information Schema |
T021 |
BINARY and VARBINARY data types |
T022 |
Advanced support for BINARY and VARBINARY data types |
T023 |
Compound binary literals |
T024 |
Spaces in binary literals |
T031 |
BOOLEAN data type |
T041 |
Basic LOB data type support. Mimer SQL does not support the following sub-features: |
T071 |
BIGINT data type |
T101 |
Enhanced nullability determination |
T121 |
WITH (excluding RECURSIVE) in query expression |
T122 |
WITH (excluding RECURSIVE) in subquery |
T132 |
Recursive query in subquery |
T151 |
DISTINCT predicate |
T152 |
DISTINCT predicate with negation |
T176 |
Sequence generator support |
T177 |
Sequence generator support: simple restart option |
T191 |
Referential action RESTRICT |
T211 |
Basic trigger capability |
T212 |
Enhanced trigger capability |
T213 |
INSTEAD OF triggers |
T241 |
START TRANSACTION statement |
T285 |
Enhanced derived column names |
T312 |
OVERLAY function |
T321 |
Basic SQL-invoked routines |
T341 |
Overloading of SQL-invoked functions and SQL-invoked procedures |
T441 |
ABS and MOD functions |
T461 |
Symmetric BETWEEN predicate |
T501 |
Enhanced EXISTS predicate |
T551 |
Optional key words for default syntax |
T591 |
UNIQUE constraints of possibly null columns |
T622 |
Trigonometric functions |
T624 |
Common logarithm functions |
T631 |
IN predicate with one list element |
T641 |
Multiple column assignment |
T655 |
Cyclically dependent routines |
P001 |
Stored modules |
P002 |
Computational completeness |
P003 |
Information Schema views |