SQL-2016 Compliance

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.

SQL-2016 Core 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:
T041-03 “POSITION, LENGTH, LOWER, TRIM, UPPER, and SUBSTRING functions for LOB data types” (except SUBSTRING that is supported)
T041-04 “Concatenation of LOB data types”
T041-05 “LOB locator: non-holdable”.

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