ANSI SQL Compatibility of ClickHouse SQL Dialect

Note

This article relies on Table 38, “Feature taxonomy and definition for mandatory features”, Annex F of ISO/IEC CD 9075-2:2011.

Differences in Behaviour

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

Feature IDFeature NameDifference
E011Numeric data typesNumeric literal with period is interpreted as approximate (Float64) instead of exact (Decimal)
E051-05Select items can be renamedItem renames have a wider visibility scope than just the SELECT result
E141-01NOT NULL constraintsNOT NULL is implied for table columns by default
E011-04Arithmetic operatorsClickHouse overflows instead of checked arithmetic and changes the result data type based on custom rules

Feature Status

Feature IDFeature NameStatusComment
E011Numeric data typesPartial
E011-01INTEGER and SMALLINT data typesYes{.text-success}
E011-02REAL, DOUBLE PRECISION and FLOAT data types data typesPartial{.text-warning}FLOAT(<binary_precision>), REAL and DOUBLE PRECISION are not supported
E011-03DECIMAL and NUMERIC data typesPartial{.text-warning}Only DECIMAL(p,s) is supported, not NUMERIC
E011-04Arithmetic operatorsYes{.text-success}
E011-05Numeric comparisonYes{.text-success}
E011-06Implicit casting among the numeric data typesNo{.text-danger}ANSI SQL allows arbitrary implicit cast between numeric types, while ClickHouse relies on functions having multiple overloads instead of implicit cast
E021Character string typesPartial
E021-01CHARACTER data typeNo{.text-danger}
E021-02CHARACTER VARYING data typeNo{.text-danger}String behaves similarly, but without length limit in parentheses
E021-03Character literalsPartial{.text-warning}No automatic concatenation of consecutive literals and character set support
E021-04CHARACTER_LENGTH functionPartial{.text-warning}No USING clause
E021-05OCTET_LENGTH functionNo{.text-danger}LENGTH behaves similarly
E021-06SUBSTRINGPartial{.text-warning}No support for SIMILAR and ESCAPE clauses, no SUBSTRING_REGEX variant
E021-07Character concatenationPartial{.text-warning}No COLLATE clause
E021-08UPPER and LOWER functionsYes{.text-success}
E021-09TRIM functionYes{.text-success}
E021-10Implicit casting among the fixed-length and variable-length character string typesNo{.text-danger}ANSI SQL allows arbitrary implicit cast between string types, while ClickHouse relies on functions having multiple overloads instead of implicit cast
E021-11POSITION functionPartial{.text-warning}No support for IN and USING clauses, no POSITION_REGEX variant
E021-12Character comparisonYes{.text-success}
E031IdentifiersPartial
E031-01Delimited identifiersPartial{.text-warning}Unicode literal support is limited
E031-02Lower case identifiersYes{.text-success}
E031-03Trailing underscoreYes{.text-success}
E051Basic query specificationPartial
E051-01SELECT DISTINCTYes{.text-success}
E051-02GROUP BY clauseYes{.text-success}
E051-04GROUP BY can contain columns not in <select list>Yes{.text-success}
E051-05Select items can be renamedYes{.text-success}
E051-06HAVING clauseYes{.text-success}
E051-07Qualified * in select listYes{.text-success}
E051-08Correlation name in the FROM clauseYes{.text-success}
E051-09Rename columns in the FROM clauseNo{.text-danger}
E061Basic predicates and search conditionsPartial
E061-01Comparison predicateYes{.text-success}
E061-02BETWEEN predicatePartial{.text-warning}No SYMMETRIC and ASYMMETRIC clause
E061-03IN predicate with list of valuesYes{.text-success}
E061-04LIKE predicateYes{.text-success}
E061-05LIKE predicate: ESCAPE clauseNo{.text-danger}
E061-06NULL predicateYes{.text-success}
E061-07Quantified comparison predicateNo{.text-danger}
E061-08EXISTS predicateNo{.text-danger}
E061-09Subqueries in comparison predicateYes{.text-success}
E061-11Subqueries in IN predicateYes{.text-success}
E061-12Subqueries in quantified comparison predicateNo{.text-danger}
E061-13Correlated subqueriesNo{.text-danger}
E061-14Search conditionYes{.text-success}
E071Basic query expressionsPartial
E071-01UNION DISTINCT table operatorNo{.text-danger}
E071-02UNION ALL table operatorYes{.text-success}
E071-03EXCEPT DISTINCT table operatorNo{.text-danger}
E071-05Columns combined via table operators need not have exactly the same data typeYes{.text-success}
E071-06Table operators in subqueriesYes{.text-success}
E081Basic privilegesPartialWork in progress
E081-01SELECT privilege at the table level
E081-02DELETE privilege
E081-03INSERT privilege at the table level
E081-04UPDATE privilege at the table level
E081-05UPDATE privilege at the column level
E081-06REFERENCES privilege at the table level
E081-07REFERENCES privilege at the column level
E081-08WITH GRANT OPTION
E081-09USAGE privilege
E081-10EXECUTE privilege
E091Set functionsYes
E091-01AVGYes{.text-success}
E091-02COUNTYes{.text-success}
E091-03MAXYes{.text-success}
E091-04MINYes{.text-success}
E091-05SUMYes{.text-success}
E091-06ALL quantifierNo{.text-danger}
E091-07DISTINCT quantifierPartial{.text-warning}Not all aggregate functions supported
E101Basic data manipulationPartial
E101-01INSERT statementYes{.text-success}Note: primary key in ClickHouse does not imply the UNIQUE constraint
E101-03Searched UPDATE statementNo{.text-danger}There’s an ALTER UPDATE statement for batch data modification
E101-04Searched DELETE statementNo{.text-danger}There’s an ALTER DELETE statement for batch data removal
E111Single row SELECT statementNo
E121Basic cursor supportNo
E121-01DECLARE CURSORNo{.text-danger}
E121-02ORDER BY columns need not be in select listNo{.text-danger}
E121-03Value expressions in ORDER BY clauseNo{.text-danger}
E121-04OPEN statementNo{.text-danger}
E121-06Positioned UPDATE statementNo{.text-danger}
E121-07Positioned DELETE statementNo{.text-danger}
E121-08CLOSE statementNo{.text-danger}
E121-10FETCH statement: implicit NEXTNo{.text-danger}
E121-17WITH HOLD cursorsNo{.text-danger}
E131Null value support (nulls in lieu of values)PartialSome restrictions apply
E141Basic integrity constraintsPartial
E141-01NOT NULL constraintsYes{.text-success}Note: NOT NULL is implied for table columns by default
E141-02UNIQUE constraint of NOT NULL columnsNo{.text-danger}
E141-03PRIMARY KEY constraintsNo{.text-danger}
E141-04Basic FOREIGN KEY constraint with the NO ACTION default for both referential delete action and referential update actionNo{.text-danger}
E141-06CHECK constraintYes{.text-success}
E141-07Column defaultsYes{.text-success}
E141-08NOT NULL inferred on PRIMARY KEYYes{.text-success}
E141-10Names in a foreign key can be specified in any orderNo{.text-danger}
E151Transaction supportNo
E151-01COMMIT statementNo{.text-danger}
E151-02ROLLBACK statementNo{.text-danger}
E152Basic SET TRANSACTION statementNo
E152-01SET TRANSACTION statement: ISOLATION LEVEL SERIALIZABLE clauseNo{.text-danger}
E152-02SET TRANSACTION statement: READ ONLY and READ WRITE clausesNo{.text-danger}
E153Updatable queries with subqueriesNo
E161SQL comments using leading double minusYes
E171SQLSTATE supportNo
E182Host language bindingNo
F031Basic schema manipulationPartial
F031-01CREATE TABLE statement to create persistent base tablesPartial{.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-02CREATE VIEW statementPartial{.text-warning}No RECURSIVE, CHECK, UNDER, WITH OPTIONS clauses and no support for user resolved data types
F031-03GRANT statementYes{.text-success}
F031-04ALTER TABLE statement: ADD COLUMN clausePartial{.text-warning}No support for GENERATED clause and system time period
F031-13DROP TABLE statement: RESTRICT clauseNo{.text-danger}
F031-16DROP VIEW statement: RESTRICT clauseNo{.text-danger}
F031-19REVOKE statement: RESTRICT clauseNo{.text-danger}
F041Basic joined tablePartial
F041-01Inner join (but not necessarily the INNER keyword)Yes{.text-success}
F041-02INNER keywordYes{.text-success}
F041-03LEFT OUTER JOINYes{.text-success}
F041-04RIGHT OUTER JOINYes{.text-success}
F041-05Outer joins can be nestedYes{.text-success}
F041-07The inner table in a left or right outer join can also be used in an inner joinYes{.text-success}
F041-08All comparison operators are supported (rather than just =)No{.text-danger}
F051Basic date and timePartial
F051-01DATE data type (including support of DATE literal)Partial{.text-warning}No literal
F051-02TIME data type (including support of TIME literal) with fractional seconds precision of at least 0No{.text-danger}
F051-03TIMESTAMP data type (including support of TIMESTAMP literal) with fractional seconds precision of at least 0 and 6No{.text-danger}DateTime64 time provides similar functionality
F051-04Comparison predicate on DATE, TIME, and TIMESTAMP data typesPartial{.text-warning}Only one data type available
F051-05Explicit CAST between datetime types and character string typesYes{.text-success}
F051-06CURRENT_DATENo{.text-danger}today() is similar
F051-07LOCALTIMENo{.text-danger}now() is similar
F051-08LOCALTIMESTAMPNo{.text-danger}
F081UNION and EXCEPT in viewsPartial
F131Grouped operationsPartial
F131-01WHERE, GROUP BY, and HAVING clauses supported in queries with grouped viewsYes{.text-success}
F131-02Multiple tables supported in queries with grouped viewsYes{.text-success}
F131-03Set functions supported in queries with grouped viewsYes{.text-success}
F131-04Subqueries with GROUP BY and HAVING clauses and grouped viewsYes{.text-success}
F131-05Single row SELECT with GROUP BY and HAVING clauses and grouped viewsNo{.text-danger}
F181Multiple module supportNo
F201CAST functionYes
F221Explicit defaultsNo
F261CASE expressionYes
F261-01Simple CASEYes{.text-success}
F261-02Searched CASEYes{.text-success}
F261-03NULLIFYes{.text-success}
F261-04COALESCEYes{.text-success}
F311Schema definition statementPartial
F311-01CREATE SCHEMANo{.text-danger}
F311-02CREATE TABLE for persistent base tablesYes{.text-success}
F311-03CREATE VIEWYes{.text-success}
F311-04CREATE VIEW: WITH CHECK OPTIONNo{.text-danger}
F311-05GRANT statementYes{.text-success}
F471Scalar subquery valuesYes
F481Expanded NULL predicateYes
F812Basic flaggingNo
S011Distinct data types
T321Basic SQL-invoked routinesNo
T321-01User-defined functions with no overloadingNo{.text-danger}
T321-02User-defined stored procedures with no overloadingNo{.text-danger}
T321-03Function invocationNo{.text-danger}
T321-04CALL statementNo{.text-danger}
T321-05RETURN statementNo{.text-danger}
T631IN predicate with one list elementYes