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
ID |
Package |
# |
Description |
Comments |
---|---|---|---|---|
E011 |
Numeric data types |
Only Java types are supported |
||
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 |
|
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 |
5 |
LIKE predicate ESCAPE clause |
|
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 |
|
E111 |
Single row SELECT 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 |
|
F311 |
Schema definition statement |
5 |
GRANT statement |
|
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 |
|||
T151 |
DISTINCT predicate |
|||
T152 |
DISTINCT predicate with negation |
|||
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 |
|
T331 |
Basic roles |
|||
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 |
|||
M004 |
Foreign data support |
|||
M005 |
Foreign schema support |
|||
M010 |
Foreign data wrapper support |