SQL Dialects & Conformance

This page provides details on the SQL dialects supported by Databend, along with its conformity to the SQL standard, particularly focusing on SQL:2011 features and their support status within Databend.

Supported SQL Dialects

A SQL dialect refers to a particular variation or flavor of the Structured Query Language. Databend supports the PostgreSQL dialect by default and offers the flexibility to switch to other supported dialects. Please refer to the table below for details on the supported dialects and their respective brief descriptions:

Dialect	Introduction	Learn More
PostgreSQL	Default supported dialect commonly used in enterprises	https://www.postgresql.org/
MySQL	Open-source database management system	https://www.mysql.com/
Hive	Data warehouse for big data processing	https://hive.apache.org/
Prql	PRQL is a modern language for transforming data — a simple, powerful, pipelined SQL replacement	https://github.com/PRQL/prql
Experimental	Experimental dialect for testing and research	N/A

To switch between the supported SQL dialects or display the current one, use the sql_dialect setting:

Examples:

-- Set SQL dialect to PRQL
SET sql_dialect = 'Prql';

-- Display current dialect
SHOW SETTINGS LIKE 'sql_dialect';

SQL Conformance Summary

Databend aims to conform to the SQL standard, with particular support for ISO/IEC 9075:2011, also known as SQL:2011. While not an exhaustive statement of conformance, Databend incorporates many features required by the SQL standard, often with slight differences in syntax or function. This page outlines the level of conformity of Databend to the SQL:2011 standard.

Feature ID	Feature Name	Supported?	Note
E011	Numeric data types	Yes
E011-01	INTEGER and SMALLINT data types	Yes
E011-02	REAL, DOUBLE PRECISION and FLOAT data types	Yes
E011-03	DECIMAL and NUMERIC data types	Yes
E011-04	Arithmetic operators	Yes
E011-05	Numeric comparison	Yes
E011-06	Implicit casting among the numeric data types	Yes
E021	Character string types	Partial
E021-01	CHARACTER data type	No	Fixed-length string type not supported
E021-02	CHARACTER VARYING data type	Yes
E021-03	Character literals	Yes
E021-04	CHARACTER_LENGTH function	Yes
E021-05	OCTET_LENGTH function	Yes
E021-06	SUBSTRING	Yes
E021-07	Character concatenation	Yes
E021-08	UPPER and LOWER functions	Yes
E021-09	TRIM function	Yes
E021-10	Implicit casting among the fixed-length and variable-length character string types	No	Fixed-length string type not supported
E021-11	POSITION function	Yes
E021-12	Character comparison	Yes
E031	Identifiers	Yes
E031-01	Delimited identifiers	Yes
E031-02	Lower case identifiers	Yes
E031-03	Trailing underscore	Yes
E051	Basic query specification	Partial
E051-01	SELECT DISTINCT	Yes
E051-02	GROUP BY clause	Yes
E051-04	GROUP BY can contain columns not in SELECT list	Yes
E051-05	Select items can be renamed	Yes
E051-06	HAVING clause	Yes
E051-07	Qualified * in select list	No
E051-08	Correlation name in the FROM clause	Yes
E051-09	Rename columns in the FROM clause	No
E061	Basic predicates and search conditions	Partial
E061-01	Comparison predicate	Yes
E061-02	BETWEEN predicate	Yes
E061-03	IN predicate with list of values	Yes
E061-04	LIKE predicate	Yes
E061-05	LIKE predicate: ESCAPE clause	No
E061-06	NULL predicate	Yes
E061-07	Quantified comparison predicate	Yes
E061-08	EXISTS predicate	Yes
E061-09	Subqueries in comparison predicate	Yes
E061-11	Subqueries in IN predicate	Yes
E061-12	Subqueries in quantified comparison predicate	Yes
E061-13	Correlated subqueries	Yes
E061-14	Search condition	Yes
E071	Basic query expressions	Partial
E071-01	UNION DISTINCT table operator	Yes
E071-02	UNION ALL table operator	Yes
E071-03	EXCEPT DISTINCT table operator	Yes
E071-05	Columns combined via table operators need not have exactly the same data type	Partial	Only columns with data types that can be implicitly coerced are allowed to be combined with table operators.
E071-06	Table operators in subqueries	Yes
E081	Basic privileges	Partial
E081-01	SELECT privilege at the table level	Yes
E081-02	DELETE privilege	Yes
E081-03	INSERT privilege at the table level	Yes
E081-04	UPDATE privilege at the table level	Yes
E081-05	UPDATE privilege at the column level	No
E081-06	REFERENCES privilege at the table level	No
E081-07	REFERENCES privilege at the column level	No
E081-08	WITH GRANT OPTION	No
E081-09	USAGE privilege	No
E081-10	EXECUTE privilege	No
E091	Set functions	Yes
E091-01	AVG	Yes
E091-02	COUNT	Yes
E091-03	MAX	Yes
E091-04	MIN	Yes
E091-05	SUM	Yes
E091-06	ALL quantifier	Yes
E091-07	DISTINCT quantifier	Partial	Currently, Databend supports COUNT(DISTINCT ...) and SELECT DISTINCT ... queries.
E101	Basic data manipulation	Partial
E101-01	INSERT statement	Yes
E101-03	Searched UPDATE statement	Yes
E101-04	Searched DELETE statement	Yes
E111	Single row SELECT statement	Yes
E121	Basic cursor support	Partial
E121-01	DECLARE CURSOR	No
E121-02	ORDER BY columns need not be in select list	Yes
E121-03	Value expressions in ORDER BY clause	Yes
E121-04	OPEN statement	No
E121-06	Positioned UPDATE statement	No
E121-07	Positioned DELETE statement	No
E121-08	CLOSE statement	No
E121-10	FETCH statement: implicit NEXT	No
E121-17	WITH HOLD cursors	No
E131	Null value support (nulls in lieu of values)	Yes
E141	Basic integrity constraints	No
E141-01	NOT NULL constraints	Yes	Default in Databend: All columns are nullable.
E141-02	UNIQUE constraint of NOT NULL columns	No
E141-03	PRIMARY KEY constraints	No
E141-04	Basic FOREIGN KEY constraint with the NO ACTION default for both referential delete action and referential update action	No
E141-06	CHECK constraint	No
E141-07	Column defaults	Yes
E141-08	NOT NULL inferred on PRIMARY KEY	No
E141-10	Names in a foreign key can be specified in any order	No
E151	Transaction support	Partial
E151-01	COMMIT statement	Partial	Databend only supports implicit transactions for every individual DML statement.
E151-02	ROLLBACK statement	No
E152	Basic SET TRANSACTION statement	No
E152-01	SET TRANSACTION statement: ISOLATION LEVEL SERIALIZABLE clause	No
E152-02	SET TRANSACTION statement: READ ONLY and READ WRITE clauses	No
E153	Updatable queries with subqueries	Yes
E161	SQL comments using leading double minus	Yes
E171	SQLSTATE support	No
E182	Host language binding	No
F031	Basic schema manipulation	Yes
F031-01	CREATE TABLE statement to create persistent base tables	Yes
F031-02	CREATE VIEW statement	Yes
F031-03	GRANT statement	Partial
F031-04	ALTER TABLE statement: ADD COLUMN clause	Yes
F031-13	DROP TABLE statement: RESTRICT clause	Partial
F031-16	DROP VIEW statement: RESTRICT clause	Partial
F031-19	REVOKE statement: RESTRICT clause	Partial
F041	Basic joined table	Yes
F041-01	Inner join (but not necessarily the INNER keyword)	Yes
F041-02	INNER keyword	Yes
F041-03	LEFT OUTER JOIN	Yes
F041-04	RIGHT OUTER JOIN	Yes
F041-05	Outer joins can be nested	Yes
F041-07	The inner table in a left or right outer join can also be used in an inner join	Yes
F041-08	All comparison operators are supported (rather than just =)	Yes
F051	Basic date and time	Partial
F051-01	DATE data type (including support of DATE literal)	Yes
F051-02	TIME data type (including support of TIME literal) with fractional seconds precision of at least 0	No
F051-03	TIMESTAMP data type (including support of TIMESTAMP literal) with fractional seconds precision of at least 0 and 6	Yes
F051-04	Comparison predicate on DATE, TIME, and TIMESTAMP data types	Yes
F051-05	Explicit CAST between datetime types and character string types	Yes
F051-06	CURRENT_DATE	Yes
F051-07	LOCALTIME	Yes
F051-08	LOCALTIMESTAMP	Yes
F081	UNION and EXCEPT in views	Yes
F131	Grouped operations	Yes
F131-01	WHERE, GROUP BY, and HAVING clauses supported in queries with grouped views	Yes
F131-02	Multiple tables supported in queries with grouped views	Yes
F131-03	Set functions supported in queries with grouped views	Yes
F131-04	Subqueries with GROUP BY and HAVING clauses and grouped views	Yes
F131-05	Single row SELECT with GROUP BY and HAVING clauses and grouped views	Yes
F181	Multiple module support	No
F201	CAST function	Yes
F221	Explicit defaults	No
F261	CASE expression	Yes
F261-01	Simple CASE	Yes
F261-02	Searched CASE	Yes
F261-03	NULLIF	Yes
F261-04	COALESCE	Yes
F311	Schema definition statement	Partial
F311-01	CREATE SCHEMA	Yes
F311-02	CREATE TABLE for persistent base tables	Yes
F311-03	CREATE VIEW	Yes
F311-04	CREATE VIEW: WITH CHECK OPTION	No
F311-05	GRANT statement	Partial
F471	Scalar subquery values	Yes
F481	Expanded NULL predicate	Yes
F812	Basic flagging	No
S011	Distinct data types	No
T321	Basic SQL-invoked routines	No
T321-01	User-defined functions with no overloading	Yes
T321-02	User-defined stored procedures with no overloading	No
T321-03	Function invocation	Yes
T321-04	CALL statement	No
T321-05	RETURN statement	No
T631	IN predicate with one list element	Yes