ANSI SQL Compatibility of ClickHouse SQL Dialect 

Differences in Behaviour 

The following table lists cases when query feature works in ClickHouse, but behaves not as specified in ANSI SQL.

Feature ID Feature Name Difference
E011 Numeric data types Numeric literal with period is interpreted as approximate (Float64) instead of exact (Decimal)
E051-05 Select items can be renamed Item renames have a wider visibility scope than just the SELECT result
E141-01 NOT NULL constraints NOT NULL is implied for table columns by default
E011-04 Arithmetic operators ClickHouse overflows instead of checked arithmetic and changes the result data type based on custom rules

Feature Status 

Feature ID Feature Name Status Comment
E011 Numeric data types Partial
E011-01 INTEGER and SMALLINT data types Yes{.text-success}
E011-02 REAL, DOUBLE PRECISION and FLOAT data types data types Partial{.text-warning} FLOAT(<binary_precision>), REAL and DOUBLE PRECISION are not supported
E011-03 DECIMAL and NUMERIC data types Partial{.text-warning} Only DECIMAL(p,s) is supported, not NUMERIC
E011-04 Arithmetic operators Yes{.text-success}
E011-05 Numeric comparison Yes{.text-success}
E011-06 Implicit casting among the numeric data types No{.text-danger} ANSI SQL allows arbitrary implicit cast between numeric types, while ClickHouse relies on functions having multiple overloads instead of implicit cast
E021 Character string types Partial
E021-01 CHARACTER data type No{.text-danger}
E021-02 CHARACTER VARYING data type No{.text-danger} String behaves similarly, but without length limit in parentheses
E021-03 Character literals Partial{.text-warning} No automatic concatenation of consecutive literals and character set support
E021-04 CHARACTER_LENGTH function Partial{.text-warning} No USING clause
E021-05 OCTET_LENGTH function No{.text-danger} LENGTH behaves similarly
E021-06 SUBSTRING Partial{.text-warning} No support for SIMILAR and ESCAPE clauses, no SUBSTRING_REGEX variant
E021-07 Character concatenation Partial{.text-warning} No COLLATE clause
E021-08 UPPER and LOWER functions Yes{.text-success}
E021-09 TRIM function Yes{.text-success}
E021-10 Implicit casting among the fixed-length and variable-length character string types No{.text-danger} ANSI SQL allows arbitrary implicit cast between string types, while ClickHouse relies on functions having multiple overloads instead of implicit cast
E021-11 POSITION function Partial{.text-warning} No support for IN and USING clauses, no POSITION_REGEX variant
E021-12 Character comparison Yes{.text-success}
E031 Identifiers Partial
E031-01 Delimited identifiers Partial{.text-warning} Unicode literal support is limited
E031-02 Lower case identifiers Yes{.text-success}
E031-03 Trailing underscore Yes{.text-success}
E051 Basic query specification Partial
E051-01 SELECT DISTINCT Yes{.text-success}
E051-02 GROUP BY clause Yes{.text-success}
E051-04 GROUP BY can contain columns not in <select list> Yes{.text-success}
E051-05 Select items can be renamed Yes{.text-success}
E051-06 HAVING clause Yes{.text-success}
E051-07 Qualified * in select list Yes{.text-success}
E051-08 Correlation name in the FROM clause Yes{.text-success}
E051-09 Rename columns in the FROM clause No{.text-danger}
E061 Basic predicates and search conditions Partial
E061-01 Comparison predicate Yes{.text-success}
E061-02 BETWEEN predicate Partial{.text-warning} No SYMMETRIC and ASYMMETRIC clause
E061-03 IN predicate with list of values Yes{.text-success}
E061-04 LIKE predicate Yes{.text-success}
E061-05 LIKE predicate: ESCAPE clause No{.text-danger}
E061-06 NULL predicate Yes{.text-success}
E061-07 Quantified comparison predicate No{.text-danger}
E061-08 EXISTS predicate No{.text-danger}
E061-09 Subqueries in comparison predicate Yes{.text-success}
E061-11 Subqueries in IN predicate Yes{.text-success}
E061-12 Subqueries in quantified comparison predicate No{.text-danger}
E061-13 Correlated subqueries No{.text-danger}
E061-14 Search condition Yes{.text-success}
E071 Basic query expressions Partial
E071-01 UNION DISTINCT table operator No{.text-danger}
E071-02 UNION ALL table operator Yes{.text-success}
E071-03 EXCEPT DISTINCT table operator No{.text-danger}
E071-05 Columns combined via table operators need not have exactly the same data type Yes{.text-success}
E071-06 Table operators in subqueries Yes{.text-success}
E081 Basic privileges Partial Work in progress
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-09 USAGE privilege
E081-10 EXECUTE privilege
E091 Set functions Yes
E091-01 AVG Yes{.text-success}
E091-02 COUNT Yes{.text-success}
E091-03 MAX Yes{.text-success}
E091-04 MIN Yes{.text-success}
E091-05 SUM Yes{.text-success}
E091-06 ALL quantifier No{.text-danger}
E091-07 DISTINCT quantifier Partial{.text-warning} Not all aggregate functions supported
E101 Basic data manipulation Partial
E101-01 INSERT statement Yes{.text-success} Note: primary key in ClickHouse does not imply the UNIQUE constraint
E101-03 Searched UPDATE statement No{.text-danger} There’s an ALTER UPDATE statement for batch data modification
E101-04 Searched DELETE statement No{.text-danger} There’s an ALTER DELETE statement for batch data removal
E111 Single row SELECT statement No
E121 Basic cursor support No
E121-01 DECLARE CURSOR No{.text-danger}
E121-02 ORDER BY columns need not be in select list No{.text-danger}
E121-03 Value expressions in ORDER BY clause No{.text-danger}
E121-04 OPEN statement No{.text-danger}
E121-06 Positioned UPDATE statement No{.text-danger}
E121-07 Positioned DELETE statement No{.text-danger}
E121-08 CLOSE statement No{.text-danger}
E121-10 FETCH statement: implicit NEXT No{.text-danger}
E121-17 WITH HOLD cursors No{.text-danger}
E131 Null value support (nulls in lieu of values) Partial Some restrictions apply
E141 Basic integrity constraints Partial
E141-01 NOT NULL constraints Yes{.text-success} Note: NOT NULL is implied for table columns by default
E141-02 UNIQUE constraint of NOT NULL columns No{.text-danger}
E141-03 PRIMARY KEY constraints No{.text-danger}
E141-04 Basic FOREIGN KEY constraint with the NO ACTION default for both referential delete action and referential update action No{.text-danger}
E141-06 CHECK constraint Yes{.text-success}
E141-07 Column defaults Yes{.text-success}
E141-08 NOT NULL inferred on PRIMARY KEY Yes{.text-success}
E141-10 Names in a foreign key can be specified in any order No{.text-danger}
E151 Transaction support No
E151-01 COMMIT statement No{.text-danger}
E151-02 ROLLBACK statement No{.text-danger}
E152 Basic SET TRANSACTION statement No
E152-01 SET TRANSACTION statement: ISOLATION LEVEL SERIALIZABLE clause No{.text-danger}
E152-02 SET TRANSACTION statement: READ ONLY and READ WRITE clauses No{.text-danger}
E153 Updatable queries with subqueries No
E161 SQL comments using leading double minus Yes
E171 SQLSTATE support No
E182 Host language binding No
F031 Basic schema manipulation Partial
F031-01 CREATE TABLE statement to create persistent base tables Partial{.text-warning} No SYSTEM VERSIONING, ON COMMIT, GLOBAL, LOCAL, PRESERVE, DELETE, REF IS, WITH OPTIONS, UNDER, LIKE, PERIOD FOR clauses and no support for user resolved data types
F031-02 CREATE VIEW statement Partial{.text-warning} No RECURSIVE, CHECK, UNDER, WITH OPTIONS clauses and no support for user resolved data types
F031-03 GRANT statement Yes{.text-success}
F031-04 ALTER TABLE statement: ADD COLUMN clause Partial{.text-warning} No support for GENERATED clause and system time period
F031-13 DROP TABLE statement: RESTRICT clause No{.text-danger}
F031-16 DROP VIEW statement: RESTRICT clause No{.text-danger}
F031-19 REVOKE statement: RESTRICT clause No{.text-danger}
F041 Basic joined table Partial
F041-01 Inner join (but not necessarily the INNER keyword) Yes{.text-success}
F041-02 INNER keyword Yes{.text-success}
F041-03 LEFT OUTER JOIN Yes{.text-success}
F041-04 RIGHT OUTER JOIN Yes{.text-success}
F041-05 Outer joins can be nested Yes{.text-success}
F041-07 The inner table in a left or right outer join can also be used in an inner join Yes{.text-success}
F041-08 All comparison operators are supported (rather than just =) No{.text-danger}
F051 Basic date and time Partial
F051-01 DATE data type (including support of DATE literal) Partial{.text-warning} No literal
F051-02 TIME data type (including support of TIME literal) with fractional seconds precision of at least 0 No{.text-danger}
F051-03 TIMESTAMP data type (including support of TIMESTAMP literal) with fractional seconds precision of at least 0 and 6 No{.text-danger} DateTime64 time provides similar functionality
F051-04 Comparison predicate on DATE, TIME, and TIMESTAMP data types Partial{.text-warning} Only one data type available
F051-05 Explicit CAST between datetime types and character string types Yes{.text-success}
F051-06 CURRENT_DATE No{.text-danger} today() is similar
F051-07 LOCALTIME No{.text-danger} now() is similar
F051-08 LOCALTIMESTAMP No{.text-danger}
F081 UNION and EXCEPT in views Partial
F131 Grouped operations Partial
F131-01 WHERE, GROUP BY, and HAVING clauses supported in queries with grouped views Yes{.text-success}
F131-02 Multiple tables supported in queries with grouped views Yes{.text-success}
F131-03 Set functions supported in queries with grouped views Yes{.text-success}
F131-04 Subqueries with GROUP BY and HAVING clauses and grouped views Yes{.text-success}
F131-05 Single row SELECT with GROUP BY and HAVING clauses and grouped views No{.text-danger}
F181 Multiple module support No
F201 CAST function Yes
F221 Explicit defaults No
F261 CASE expression Yes
F261-01 Simple CASE Yes{.text-success}
F261-02 Searched CASE Yes{.text-success}
F261-03 NULLIF Yes{.text-success}
F261-04 COALESCE Yes{.text-success}
F311 Schema definition statement Partial
F311-01 CREATE SCHEMA No{.text-danger}
F311-02 CREATE TABLE for persistent base tables Yes{.text-success}
F311-03 CREATE VIEW Yes{.text-success}
F311-04 CREATE VIEW: WITH CHECK OPTION No{.text-danger}
F311-05 GRANT statement Yes{.text-success}
F471 Scalar subquery values Yes
F481 Expanded NULL predicate Yes
F812 Basic flagging No
S011 Distinct data types
T321 Basic SQL-invoked routines No
T321-01 User-defined functions with no overloading No{.text-danger}
T321-02 User-defined stored procedures with no overloading No{.text-danger}
T321-03 Function invocation No{.text-danger}
T321-04 CALL statement No{.text-danger}
T321-05 RETURN statement No{.text-danger}
T631 IN predicate with one list element Yes

Rating: 4.9 - 7 votes

Was this content helpful?