SQL Compliance

vsql aims to be SQL compliant by following the 2016 SQL Standard in both BNF syntax and mandatory features.

There are still many features to be implemented before it can be considered fully compliant - as shown by the Mandatory Features table below.

Mandatory Features

As of the latest version (or at least the version of this documentation) vsql supports 62 of the 164 mandatory features of the SQL:2016 Standard.

Table 43 — Feature taxonomy and definition for mandatory features

Feature ID

Feature Name

E011

Numeric data types

✅ E011-01

INTEGER and SMALLINT data types (including all spellings)

✅ E011-02

REAL, DOUBLE PRECISON, and FLOAT data types

✅ E011-03

DECIMAL and NUMERIC data types

✅ E011-04

Arithmetic operators

✅ E011-05

Numeric comparison

✅ E011-06

Implicit casting among the numeric data types

E021

Character string types

✅ E021-01

CHARACTER data type (including all its spellings)

✅ E021-02

CHARACTER VARYING data type (including all its spellings)

✅ E021-03

Character literals

✅ E021-04

CHARACTER_LENGTH function

✅ E021-05

OCTET_LENGTH function

✅ E021-06

SUBSTRING function

✅ E021-07

Character concatenation

✅ E021-08

UPPER and LOWER functions

✅ E021-09

TRIM function

❌ E021-10

Implicit casting among the fixed-length and variable-length character string types

✅ E021-11

POSITION function

⭕ E021-12

Character comparison

E031

Identifiers

✅ E031-01

Delimited identifiers

✅ E031-02

Lower case identifiers

✅ E031-03

Trailing underscore

E051

Basic query specification

❌ E051-01

SELECT DISTINCT

✅ E051-02

GROUP BY clause

✅ E051-04

GROUP BY can contain columns not in <select list>

✅ E051-05

Select list items can be renamed

❌ E051-06

HAVING clause

✅ E051-07

Qualified * in select list

❌ E051-08

Correlation names in the FROM clause

✅ E051-09

Rename columns in the FROM clause

E061

Basic predicates and search conditions

✅ E061-01

Comparison predicate

✅ E061-02

BETWEEN predicate

❌ E061-03

IN predicate with list of values

✅ E061-04

LIKE predicate

❌ E061-05

LIKE predicate: ESCAPE clause

✅ E061-06

NULL predicate

❌ E061-07

Quantified comparison predicate

❌ E061-08

EXISTS predicate

❌ E061-09

Subqueries in comparison predicate

❌ E061-11

Subqueries in IN predicate

❌ E061-12

Subqueries in quantified comparison predicate

❌ E061-13

Correlated subqueries

✅ E061-14

Search condition

E071

Basic query expressions

❌ E071-01

UNION DISTINCT table operator

❌ E071-02

UNION ALL table operator

❌ E071-03

EXCEPT DISTINCT table operator

❌ E071-05

Columns combined via table operators need not have exactly the same data type.

❌ E071-06

Table operators in subqueries

E081

Basic Privileges

❌ E081-01

SELECT privilege at the table level

❌ E081-02

DELETE privilege

❌ E081-03

INSERT privilege at the table level

❌ E081-04

UPDATE privilege at the table level

❌ E081-05

UPDATE privilege at the column level

❌ E081-06

REFERENCES privilege at the table level

❌ E081-07

REFERENCES privilege at the column level

❌ E081-08

WITH GRANT OPTION

❌ E081-09

USAGE privilege

❌ E081-10

EXECUTE privilege

E091

Set functions

✅ E091-01

AVG

✅ E091-02

COUNT

✅ E091-03

MAX

✅ E091-04

MIN

✅ E091-05

SUM

❌ E091-06

ALL quantifier

❌ E091-07

DISTINCT quantifier

E101

Basic data manipulation

✅ E101-01

INSERT statement

✅ E101-03

Searched UPDATE statement

✅ E101-04

Searched DELETE statement

E111

Single row SELECT statement

E121

Basic cursor support

❌ E121-01

DECLARE CURSOR

❌ E121-02

ORDER BY columns need not be in select list

❌ E121-03

Value expressions in ORDER BY clause

❌ E121-04

OPEN statement

❌ E121-06

Positioned UPDATE statement

❌ E121-07

Positioned DELETE statement

❌ E121-08

CLOSE statement

❌ E121-10

FETCH statement: implicit NEXT

❌ E121-17

WITH HOLD cursors

E131

Null value support (nulls in lieu of values)

E141

Basic integrity constraints

❌ E141-01

NOT NULL constraints

❌ E141-02

UNIQUE constraints of NOT NULL columns

✅ E141-03

PRIMARY KEY constraints

❌ E141-04

Basic FOREIGN KEY constraint with the NO ACTION default for both referential delete action and referential update action

❌ E141-06

CHECK constraints

❌ E141-07

Column defaults

❌ E141-08

NOT NULL inferred on PRIMARY KEY

❌ E141-10

Names in a foreign key can be specified in any order

E151

Transaction support

✅ E151-01

COMMIT statement

✅ E151-02

ROLLBACK statement

E152

Basic SET TRANSACTION statement

❌ E152-01

SET TRANSACTION statement: ISOLATION LEVEL SERIALIZABLE clause

❌ E152-02

SET TRANSACTION statement: READ ONLY and READ WRITE clauses

E153

Updatable queries with subqueries

E161

SQL comments using leading double minus

E171

SQLSTATE support

E182

Host language binding

F031

Basic schema manipulation

✅ F031-01

CREATE TABLE statement to create persistent base tables

❌ F031-02

CREATE VIEW statement

❌ F031-03

GRANT statement

❌ F031-04

ALTER TABLE statement: ADD COLUMN clause

❌ F031-13

DROP TABLE statement: RESTRICT clause

❌ F031-16

DROP VIEW statement: RESTRICT clause

❌ F031-19

REVOKE statement: RESTRICT clause

F041

Basic joined table

✅ F041-01

Inner join (but not necessarily the INNER keyword)

✅ F041-02

INNER keyword

✅ F041-03

LEFT OUTER JOIN

✅ F041-04

RIGHT OUTER JOIN

❌ F041-05

Outer joins can be nested

❌ F041-07

The inner table in a left or right outer join can also be used in an inner join

✅ F041-08

All comparison operators are supported (rather than just =)

F051

Basic date and time

✅ F051-01

DATE data type (including support of DATE literal)

✅ F051-02

TIME data type (including support of TIME literal) with fractional seconds precision of at least 0.

✅ F051-03

TIMESTAMP data type (including support of TIMESTAMP literal) with fractional seconds precision of at least 0 and 6.

❌ F051-04

Comparison predicate on DATE, TIME, and TIMESTAMP data types

❌ F051-05

Explicit CAST between date-time types and character string types

✅ F051-06

CURRENT_DATE

✅ F051-07

LOCALTIME

✅ F051-08

LOCALTIMESTAMP

F081

UNION and EXCEPT in views

F131

Grouped operations

❌ F131-01

WHERE, GROUP BY, and HAVING clauses supported in queries with grouped views

❌ F131-02

Multiple tables supported in queries with grouped views

❌ F131-03

Set functions supported in queries with grouped views

❌ F131-04

Subqueries with GROUP BY and HAVING clauses and grouped views

❌ F131-05

Single row SELECT with GROUP BY and HAVING clauses and grouped views

F181

Multiple module support

F201

CAST function

F221

Explicit defaults

F261

CASE expression

❌ F261-01

Simple CASE

❌ F261-02

Searched CASE

✅ F261-03

NULLIF

✅ F261-04

COALESCE

F311

Schema definition statement

✅ F311-01

CREATE SCHEMA

✅ F311-02

CREATE TABLE for persistent base tables

❌ F311-03

CREATE VIEW

❌ F311-04

CREATE VIEW: WITH CHECK OPTION

❌ F311-05

GRANT statement

F471

Scalar subquery values

F481

Expanded NULL predicate

F812

Basic flagging

S011

Distinct data types

T321

Basic SQL-invoked routines

❌ T321-01

User-defined functions with no overloading

❌ T321-02

User-defined stored procedures with no overloading

❌ T321-03

Function invocation

❌ T321-04

CALL statement

❌ T321-05

RETURN statement

T631

IN predicate with one list element

Optional Features

Table 44 — Feature taxonomy for optional features

Feature ID

Feature Name

B011 1

Embedded Ada

B012 1

Embedded C

B013 1

Embedded COBOL

B014 1

Embedded Fortran

B015 1

Embedded MUMPS

B016 1

Embedded Pascal

B017 1

Embedded PL/I

B021

Direct SQL

B031

Basic dynamic SQL

B032

Extended dynamic SQL

❌ B032-01

<describe input statement>

B033

Untyped SQL-invoked function arguments

B034

Dynamic specification of cursor attributes

B035

Non-extended descriptor names

B041

Extensions to embedded SQL exception declarations

B051

Enhanced execution rights

B111 1

Module language Ada

B112 1

Module language C

B113 1

Module language COBOL

B114 1

Module language Fortran

B115 1

Module language MUMPS

B116 1

Module language Pascal

B117 1

Module language PL/I

B121

Routine language Ada

B122

Routine language C

B123

Routine language COBOL

B124

Routine language Fortran

B125

Routine language MUMPS

B126

Routine language Pascal

B127

Routine language PL/I

B128

Routine language SQL

B200

Polymorphic table functions

B201

More than one PTF generic table parameter

B202

PTF Copartitioning

B203

More than one copartition specification

B204

PRUNE WHEN EMPTY

B205

Pass-through columns

B206

PTF descriptor parameters

B207

Cross products of partitionings

B208

PTF component procedure interface

B209

PTF extended names

B211

Module language Ada: VARCHAR and NUMERIC support

B221

Routine language Ada: VARCHAR and NUMERIC support

F032

CASCADE drop behavior

F033

ALTER TABLE statement: DROP COLUMN clause

F034

Extended REVOKE statement

❌ F034-01

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

❌ F034-02

REVOKE statement: GRANT OPTION FOR clause

❌ F034-03

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

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

F123

All diagnostics

F171

Multiple schemas per user

F191

Referential delete actions

F200

TRUNCATE TABLE statement

F202

TRUNCATE TABLE: identity column restart option

F222

INSERT statement: DEFAULT VALUES clause

F251

Domain support

F262

Extended CASE expression

F263

Comma-separated predicates in simple CASE expression

F271

Compound character literals

F281

LIKE enhancements

F291

UNIQUE predicate

F301

CORRESPONDING in query expressions

F302

INTERSECT table operator

❌ F302-01

INTERSECT DISTINCT table operator

❌ F302-02

INTERSECT ALL table operator

F304

EXCEPT ALL table operator

F312

MERGE statement

F313

Enhanced MERGE statement

F314

MERGE statement with DELETE branch

F321

User authorization

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

F383

Set column not null clause

F384

Drop identity property clause

F385

Drop column generation expression clause

F386

Set identity column generation clause

F391

Long identifiers

F392

Unicode escapes in identifiers

F393

Unicode escapes in literals

F394

Optional normal form specification

F401

Extended joined table

❌ F401-01

NATURAL JOIN

❌ F401-02

FULL OUTER JOIN

❌ F401-04

CROSS JOIN

F402

Named column joins for LOBs, arrays, and multisets

F403

Partitioned join tables

F404

Range variable for common column names

F411

Time zone specification

F421

National character

F431

Read-only scrollable cursors

❌ F431-01

FETCH with explicit NEXT

❌ F431-02

FETCH FIRST

❌ F431-03

FETCH LAST

❌ F431-04

FETCH PRIOR

❌ F431-05

FETCH ABSOLUTE

❌ F431-06

FETCH RELATIVE

F441

Extended set function support

F442

Mixed column references in set functions

F451

Character set definition

F461

Named character sets

F491

Constraint management

F492

Optional table constraint enforcement

F521

Assertions

F531

Temporary tables

F555

Enhanced seconds precision

F561

Full value expressions

F571

Truth value tests

F591

Derived tables

F611

Indicator data types

F641

Row and table constructors

F651

Catalog name qualifiers

F661

Simple tables

F671

Subqueries in CHECK constraints

F672

Retrospective check constraints

F673

Reads SQL-data routine invocations in CHECK constraints

F690

Collation support

F692

Enhanced collation support

F693

SQL-session and client module collations

F695

Translation support

F701

Referential update actions

F711

ALTER domain

F721

Deferrable constraints

F731

INSERT column privileges

F741

Referential MATCH types

F751

View CHECK enhancements

F761

Session management

F762

CURRENT_CATALOG

F763

CURRENT_SCHEMA

F771

Connection management

F781

Self-referencing operations

F791

Insensitive cursors

F801

Full set function

F813

Extended flagging

F821

Local table references

F831

Full cursor update

❌ F831-01

Updateable scrollable cursors

❌ F831-02

Updateable ordered cursors

F841

LIKE_REGEX predicate

F842

OCCURRENCES_REGEX function

F843

POSITION_REGEX function

F844

SUBSTRING_REGEX

F845

TRANSLATE_REGEX

F846

Octet support in regular expression operators

F847

Nonconstant regular expressions

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>

F866

FETCH FIRST clause: PERCENT option

F867

FETCH FIRST clause: WITH TIES option

R010

Row pattern recognition: FROM clause

R020

Row pattern recognition: WINDOW clause

R030

Row pattern recognition: full aggregate support

S023

Basic structured types

S024

Enhanced structured types

S025

Final structured types

S026

Self-referencing structured types

S027

Create method by specific method name

S028

Permutable UDT options list

S041

Basic reference types

S043

Enhanced reference types

S051

Create table of type

S071

SQL paths in function and type name resolution

S081

Subtables

S091

Basic array support

❌ S091-01

Arrays of built-in data types

❌ S091-02

Arrays of distinct types

❌ S091-03

Array expressions

S092

Arrays of user-defined types

S094

Arrays of reference types

S095

Array constructors by query

S096

Optional array bounds

S097

Array element assignment

S098

ARRAY_AGG

S111

ONLY in query expressions

S151

Type predicate

S161

Subtype treatment

S162

Subtype treatment for references

S201

SQL-invoked routines on arrays

❌ S201-01

Array parameters

❌ S201-02

Array as result type of functions

S202

SQL-invoked routines on multisets

S211

User-defined cast functions

S231

Structured type locators

S232

Array locators

S233

Multiset locators

S241

Transform functions

S242

Alter transform statement

S251

User-defined orderings

S261

Specific type method

S271

Basic multiset support

S272

Multisets of user-defined types

S274

Multisets of reference types

S275

Advanced multiset support

S281

Nested collection types

S291

Unique constraint on entire row

S301

Enhanced UNNEST

S401

Distinct types based on array types

S402

Distinct types based on multiset types

S403

ARRAY_MAX_CARDINALITY

S404

TRIM_ARRAY

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

❌ T041-01

BLOB data type

❌ T041-02

CLOB data type

❌ T041-03

POSITION, LENGTH, LOWER, TRIM, UPPER, and SUBSTRING functions for LOB data types

❌ T041-04

Concatenation of LOB data types

❌ T041-05

LOB locator: non-holdable

T042

Extended LOB data type support

T043

Multiplier T

T044

Multiplier P

T051

Row types

T053

Explicit aliases for all-fields reference

T061

UCS support

T071

BIGINT data type

T076

DECFLOAT data type

T101

Enhanced nullability determination

T111

Updatable joins, unions, and columns

T121

WITH (excluding RECURSIVE) in query expression

T122

WITH (excluding RECURSIVE) in subquery

T131

Recursive query

T132

Recursive query in subquery

T141

SIMILAR predicate

T151

DISTINCT predicate

T152

DISTINCT predicate with negation

T171

LIKE clause in table definition

T172

AS subquery clause in table definition

T173

Extended LIKE clause in table definition

T174

Identity columns

T175

Generated columns

T176

Sequence generator support

T177

Sequence generator support: simple restart option

T178

Identity columns: simple restart option

T180

System-versioned tables

T181

Application-time period tables

T191

Referential action RESTRICT

T201

Comparable data types for referential constraints

T211

Basic trigger capability

❌ T211-01

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

❌ T211-02

BEFORE triggers

❌ T211-03

AFTER triggers

❌ T211-04

FOR EACH ROW triggers

❌ T211-05

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

❌ T211-06

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

❌ T211-07

TRIGGER privilege

❌ T211-08

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

T212

Enhanced trigger capability

T213

INSTEAD OF triggers

T231

Sensitive cursors

T241

START TRANSACTION statement

T251

SET TRANSACTION statement: LOCAL option

T261

Chained transactions

T271

Savepoints

T272

Enhanced savepoint management

T281

SELECT privilege with column granularity

T285

Enhanced derived column names

T301

Functional dependencies

T312

OVERLAY function

T323

Explicit security for external routines

T324

Explicit security for SQL routines

T325

Qualified SQL parameter references

T326

Table functions

T331

Basic roles

T332

Extended roles

T341

Overloading of SQL-invoked functions and SQL-invoked procedures

T351

Bracketed comments

T431

Extended grouping capabilities

T432

Nested and concatenated GROUPING SETS

T433

Multiargument GROUPING function

T434

GROUP BY DISINCT

T441

ABS and MOD functions

T461

Symmetric BETWEEN predicate

T471

Result sets return value

T472

DESCRIBE CURSOR

T491

LATERAL derived table

T495

Combined data change and retrieval

T501

Enhanced EXISTS predicate

T502

Period predicates

T511

Transaction counts

T521

Named arguments in CALL statement

T522

Default values for IN parameters of SQL-invoked procedures

T523

Default values for INOUT parameters of SQL-invoked procedures

T524

Named arguments in routine invocations other than a CALL statement

T525

Default values for parameters of SQL-invoked functions

T551

Optional key words for default syntax

T561

Holdable locators

T571

Array-returning external SQL-invoked functions

T572

Multiset-returning external SQL-invoked functions

T581

Regular expression substring function

T591

UNIQUE constraints of possibly null columns

T601

Local cursor references

T611

Elementary OLAP operations

T612

Advanced OLAP operations

T613

Sampling

T614

NTILE function

T615

LEAD and LAG functions

T616

Null treatment option for LEAD and LAG functions

T617

FIRST_VALUE and LAST_VALUE functions

T618

NTH_VALUE function

T619

Nested window functions

T620

WINDOW clause: GROUPS option

T621

Enhanced numeric functions

T622

Trigonometric functions

T623

General logarithm functions

T624

Common logarithm functions

T625

LISTAGG

T641

Multiple column assignment

T651

SQL-schema statements in SQL routines

T652

SQL-dynamic statements in SQL routines

T653

SQL-schema statements in external routines

T654

SQL-dynamic statements in external routines

T655

Cyclically dependent routines

T811

Basic SQL/JSON constructor functions

T812

SQL/JSON: JSON_OBJECTAGG

T813

SQL/JSON: JSON_ARRAYAGG with ORDER BY

T814

Colon in JSON_OBJECT or JSON_OBJECTAGG

T821

Basic SQL/JSON query operators

T822

SQL/JSON: IS JSON WITH UNIQUE KEYS predicate

T823

SQL/JSON: PASSING clause

T824

JSON_TABLE: specific PLAN clause

T825

SQL/JSON: ON EMPTY and ON ERROR clauses

T826

General value expression in ON ERROR or ON EMPTY clauses

T827

JSON_TABLE: sibling NESTED COLUMNS clauses

T828

JSON_QUERY

T829

JSON_QUERY: array wrapper options

T830

Enforcing unique keys in SQL/JSON constructor functions

T831

SQL/JSON path language: strict mode

T832

SQL/JSON path language: item method

T833

SQL/JSON path language: multiple subscripts

T834

SQL/JSON path language: wildcard member accessor

T835

SQL/JSON path language: filter expressions

T836

SQL/JSON path language: starts with predicate

T837

SQL/JSON path language: regex_like predicate

T838

JSON_TABLE: PLAN DEFAULT clause

T839

Formatted cast of datetimes to/from character strings

1(1,2,3,4,5,6,7,8,9,10,11,12,13,14)

A conforming SQL-implementation is required to support at least one embedded language or to support the SQL-client module binding for at least one host language.

See Also