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