Skip to main content

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