SQL standard compliance

This page documents the standard SQL (ISO/IEC 9075) features that CrateDB supports, along with implementation notes and any associated caveats.

Caution

This list is approximate and features that are listed as supported might be nonconforming in their implementation. However, the main reference documentation always contains the most accurate information about the features CrateDB supports and how to use them.

See also

SQL compatibility

ID

Package

#

Description

Comments

E011

Numeric data types

1

INTEGER and SMALLINT data types

E011

Numeric data types

2

REAL, DOUBLE PRECISION, and FLOAT data types

E011

Numeric data types

3

DECIMAL and NUMERIC data types

Not supported in DDL

E011

Numeric data types

4

Arithmetic operators

E011

Numeric data types

5

Numeric comparison

E011

Numeric data types

6

Implicit casting among the numeric data types

E021

Character string types

1

CHARACTER data type

E021

Character string types

2

CHARACTER VARYING data type

E021

Character string types

3

Character literals

Only simple ‘ quoting

E021

Character string types

4

CHARACTER_LENGTH function

char_length only

E021

Character string types

5

OCTET_LENGTH function

E021

Character string types

6

SUBSTRING function

substr scalar

E021

Character string types

7

Character concatenation

E021

Character string types

8

UPPER and LOWER functions

E021

Character string types

9

TRIM function

E021

Character string types

10

Implicit casting among the character string types

just one type

E021

Character string types

12

Character comparison

E031

Identifiers

E031

Identifiers

1

Delimited identifiers

E031

Identifiers

2

Lower case identifiers

E031

Identifiers

3

Trailing underscore

E051

Basic query specification

E051

Basic query specification

1

SELECT DISTINCT

E051

Basic query specification

2

GROUP BY clause

E051

Basic query specification

4

GROUP BY can contain columns not in <select list>

E051

Basic query specification

5

Select list items can be renamed

E051

Basic query specification

6

HAVING clause

E051

Basic query specification

7

Qualified * in select list

E051

Basic query specification

8

Correlation names in the FROM clause

E051

Basic query specification

9

Rename columns in the FROM clause

E061

Basic predicates and search conditions

1

Comparison predicate

E061

Basic predicates and search conditions

2

BETWEEN predicate

E061

Basic predicates and search conditions

3

IN predicate with list of values

E061

Basic predicates and search conditions

4

LIKE predicate

E061

Basic predicates and search conditions

6

NULL predicate

E061

Basic predicates and search conditions

8

EXISTS predicate

E061

Basic predicates and search conditions

9

Subqueries in comparison predicate

E061

Basic predicates and search conditions

11

Subqueries in IN predicate

E061

Basic predicates and search conditions

12

Subqueries in quantified comparison predicate

E061

Basic predicates and search conditions

13

Correlated subqueries

E061

Basic predicates and search conditions

14

Search condition

E071

Basic query expressions

1

UNION DISTINCT table operator

E071

Basic query expressions

2

UNION ALL table operator

E081

Basic Privileges

E081

Basic Privileges

1

SELECT privilege

E081

Basic Privileges

2

DELETE privilege

E091

Set functions

E091

Set functions

1

AVG

E091

Set functions

2

COUNT

E091

Set functions

3

MAX

E091

Set functions

4

MIN

E091

Set functions

5

SUM

E091

Set functions

6

ALL quantifier

E091

Set functions

7

DISTINCT quantifier

E101

Basic data manipulation

E101

Basic data manipulation

1

INSERT statement

E101

Basic data manipulation

3

Searched UPDATE statement

E101

Basic data manipulation

4

Searched DELETE statement

E121

Basic cursor support

1

DECLARE CURSOR

E121

Basic cursor support

8

CLOSE statement

E121

Basic cursor support

10

FETCH statement implicit NEXT

E121

Basic cursor support

17

WITH HOLD cursors

E131

Null value support (nulls in lieu of values)

E141

Basic integrity constraints

1

NOT NULL constraints

E141

Basic integrity constraints

3

PRIMARY KEY constraints

E141

Basic integrity constraints

6

CHECK constraints

E141

Basic integrity constraints

7

Column defaults

E141

Basic integrity constraints

8

NOT NULL inferred on PRIMARY KEY

E151

Transaction support

1

COMMIT statement

E152

Basic SET TRANSACTION statement

E152

Basic SET TRANSACTION statement

1

SET TRANSACTION statement: ISOLATION LEVEL SERIALIZABLE clause

Is ignored

E152

Basic SET TRANSACTION statement

2

SET TRANSACTION statement: READ ONLY and READ WRITE clauses

Is ignored

E161

SQL comments using leading double minus

F021

Basic information schema

1

COLUMNS view

F021

Basic information schema

2

TABLES view

F021

Basic information schema

3

VIEWS view

F021

Basic information schema

4

TABLE_CONSTRAINTS view

F021

Basic information schema

5

REFERENTIAL_CONSTRAINTS view

F021

Basic information schema

6

CHECK_CONSTRAINTS view

F031

Basic schema manipulation

1

CREATE TABLE statement to create persistent base tables

F031

Basic schema manipulation

2

CREATE VIEW statement

F031

Basic schema manipulation

3

GRANT statement

F031

Basic schema manipulation

4

ALTER TABLE statement: ADD COLUMN clause

F033

ALTER TABLE statement: DROP COLUMN clause

F034

Extended REVOKE statement

F034

Extended REVOKE statement

1

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

F041

Basic joined table

F041

Basic joined table

1

Inner join (but not necessarily the INNER keyword)

F041

Basic joined table

2

INNER keyword

F041

Basic joined table

3

LEFT OUTER JOIN

F041

Basic joined table

4

RIGHT OUTER JOIN

F041

Basic joined table

5

Outer joins can be nested

F041

Basic joined table

7

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

F041

Basic joined table

8

All comparison operators are supported (rather than just =)

F051

Basic date and time

1

DATE data type (including support of DATE literal)

F051

Basic date and time

3

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

F051

Basic date and time

4

Comparison predicate on DATE, TIME, and TIMESTAMP data types

F051

Basic date and time

5

Explicit CAST between datetime types and character string types

F051

Basic date and time

6

CURRENT_DATE

F052

Intervals and datetime arithmetic

F111

Isolation levels other than SERIALIZABLE

F111

Isolation levels other than SERIALIZABLE

1

READ UNCOMMITTED isolation level

Is ignored

F111

Isolation levels other than SERIALIZABLE

2

READ COMMITTED isolation level

Is ignored

F111

Isolation levels other than SERIALIZABLE

3

REPEATABLE READ isolation level

Is ignored

F131

Grouped operations

1

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

F131

Grouped operations

3

Set functions supported in queries with grouped views

F171

Multiple schemas per user

F201

CAST function

F221

Explicit defaults

F222

INSERT statement: DEFAULT VALUES clause

F261

CASE expression

F261

CASE expression

1

Simple CASE

F261

CASE expression

2

Searched CASE

F261

CASE expression

3

NULLIF

F261

CASE expression

4

COALESCE

F262

Extended CASE expression

F311

Schema definition statement

2

CREATE TABLE for persistent base tables

F311

Schema definition statement

3

CREATE VIEW

F381

Extended schema manipulation

F381

Extended schema manipulation

1

ALTER TABLE statement: ALTER COLUMN clause

F381

Extended schema manipulation

2

ALTER TABLE statement: ADD CONSTRAINT clause

F381

Extended schema manipulation

3

ALTER TABLE statement: DROP CONSTRAINT clause

F391

Long identifiers

F392

Unicode escapes in identifiers

F401

Extended joined table

2

FULL OUTER JOIN

F401

Extended joined table

4

CROSS JOIN

F431

Read-only scrollable cursors

F431

Read-only scrollable cursors

1

FETCH with explicit NEXT

F431

Read-only scrollable cursors

2

FETCH FIRST

F431

Read-only scrollable cursors

3

FETCH LAST

F431

Read-only scrollable cursors

4

FETCH PRIOR

F431

Read-only scrollable cursors

5

FETCH ABSOLUTE

F431

Read-only scrollable cursors

6

FETCH RELATIVE

F471

Scalar subquery values

F481

Expanded NULL predicate

F501

Features and conformance views

1

SQL_FEATURES view

F571

Truth value tests

F651

Catalog name qualifiers

F763

CURRENT_SCHEMA

F791

Insensitive cursors

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

<fetch first row count> in <fetch first clause>

S091

Basic array support

special syntax

S091

Basic array support

1

Arrays of built-in data types

special syntax

S098

ARRAY_AGG

T031

BOOLEAN data type

T051

Row types

Limited to built-in table functions

T054

GREATEST and LEAST

T055

String padding functions

T056

Multi-character trim functions

T071

BIGINT data type

T081

Optional string types maximum length

T121

WITH (excluding RECURSIVE) in query expression

T122

WITH (excluding RECURSIVE) in subquery

T175

Generated columns

T241

START TRANSACTION statement

Is ignored

T321

Basic SQL-invoked routines

1

User-defined functions with no overloading

T321

Basic SQL-invoked routines

3

Function invocation

T321

Basic SQL-invoked routines

6

ROUTINES view

T351

Bracketed SQL comments (/…/ comments)

T441

ABS and MOD functions

T461

Symmetric BETWEEN predicate

T471

Result sets return value

T615

LEAD and LAG functions

T617

FIRST_VALUE and LAST_VALUE function

T618

NTH_VALUE function

T621

Enhanced numeric functions

T626

ANY_VALUE aggregation

T631

IN predicate with one list element

T662

Underscores in numeric literals