Supported Features: Apache Hive 3.1

This table covers all mandatory features from SQL:2016 as well as optional features that Hive implements.

Feature ID	Feature Name	Implemented	Mandatory	Comments
E011	Numeric data types	Yes	Mandatory
E011-01	INTEGER and SMALLINT data types (including all spellings)	Yes	Mandatory
E011-02	REAL, DOUBLE PRECISON, and FLOAT data types	Yes	Mandatory
E011-03	DECIMAL and NUMERIC data types	Yes	Mandatory
E011-04	Arithmetic operators	Yes	Mandatory
E011-05	Numeric comparison	Yes	Mandatory
E011-06	Implicit casting among the numeric data types	Yes	Mandatory
E021	Character string types	Yes	Mandatory
E021-01	CHARACTER data type (including all its spellings)	Partial	Mandatory	Only support CHAR, not CHARACTER
E021-02	CHARACTER VARYING data type (including all its spellings)	Partial	Mandatory	Only support VARCHAR, not CHARACTER VARYING or CHAR VARYING
E021-03	Character literals	Yes	Mandatory
E021-04	CHARACTER_LENGTH function	Yes	Mandatory
E021-05	OCTET_LENGTH function	Yes	Mandatory
E021-06	SUBSTRING function	Partial	Mandatory	Standard: SUBSTRING(val FROM startpos [FOR len]). Hive: SUBSTRING(val, startpos [, len])
E021-07	Character concatenation	Yes	Mandatory
E021-08	UPPER and LOWER functions	Yes	Mandatory
E021-09	TRIM function	Yes	Mandatory
E021-10	Implicit casting among the fixed-length and variable-length character string types	Yes	Mandatory
E021-11	POSITION function	No	Mandatory
E021-12	Character comparison	Yes	Mandatory
E031	Identifiers	Partial	Mandatory	Unquoted identifiers use C syntax ([A-Za-z][A-Za-z0-9_]*). Quoted identifiers can have any character.
E031-01	Delimited identifiers	Partial	Mandatory	Quoting done with ` rather than ", only supported for columns, not tables, views, etc.
E031-02	Lower case identifiers	Yes	Mandatory
E031-03	Trailing underscore	Yes	Mandatory
E051	Basic query specification	Yes	Mandatory
E051-01	SELECT DISTINCT	Yes	Mandatory
E051-02	GROUP BY clause	Yes	Mandatory
E051-04	GROUP BY can contain columns not in <select list>	Yes	Mandatory
E051-05	Select list items can be renamed	Yes	Mandatory
E051-06	HAVING clause	Yes	Mandatory
E051-07	Qualified * in select list	Yes	Mandatory
E051-08	Correlation names in the FROM clause	Yes	Mandatory
E051-09	Rename columns in the FROM clause	Yes	Mandatory
E061	Basic predicates and search conditions	Yes	Mandatory
E061-01	Comparison predicate	Yes	Mandatory
E061-02	BETWEEN predicate	Yes	Mandatory
E061-03	IN predicate with list of values	Yes	Mandatory
E061-04	LIKE predicate	Yes	Mandatory
E061-05	LIKE predicate: ESCAPE clause	Yes	Mandatory
E061-06	NULL predicate	Yes	Mandatory
E061-07	Quantified comparison predicate	No	Mandatory
E061-08	EXISTS predicate	Yes	Mandatory
E061-09	Subqueries in comparison predicate	No	Mandatory
E061-11	Subqueries in IN predicate	Yes	Mandatory
E061-12	Subqueries in quantified comparison predicate	No	Mandatory
E061-13	Correlated subqueries	Yes	Mandatory
E061-14	Search condition	Yes	Mandatory
E071	Basic query expressions	Yes	Mandatory
E071-01	UNION DISTINCT table operator	Yes	Mandatory
E071-02	UNION ALL table operator	Yes	Mandatory
E071-03	EXCEPT DISTINCT table operator	Yes	Mandatory
E071-05	Columns combined via table operators need not have exactly the same data type.	Yes	Mandatory
E071-06	Table operators in subqueries	Yes	Mandatory
E081	Basic Privileges	Yes	Mandatory
E081-01	SELECT privilege at the table level	Yes	Mandatory
E081-02	DELETE privilege	Yes	Mandatory
E081-03	INSERT privilege at the table level	Yes	Mandatory
E081-04	UPDATE privilege at the table level	Yes	Mandatory
E081-05	UPDATE privilege at the column level	Yes	Mandatory
E081-06	REFERENCES privilege at the table level	No	Mandatory
E081-07	REFERENCES privilege at the column level	No	Mandatory
E081-08	WITH GRANT OPTION	Yes	Mandatory
E081-09	USAGE privilege	No	Mandatory
E081-10	EXECUTE privilege	No	Mandatory
E091	Set functions	Yes	Mandatory
E091-01	AVG	Yes	Mandatory
E091-02	COUNT	Yes	Mandatory
E091-03	MAX	Yes	Mandatory
E091-04	MIN	Yes	Mandatory
E091-05	SUM	Yes	Mandatory
E091-06	ALL quantifier	Yes	Mandatory
E091-07	DISTINCT quantifier	Yes	Mandatory
E101	Basic data manipulation	Yes	Mandatory
E101-01	INSERT statement	Yes	Mandatory
E101-03	Searched UPDATE statement	Yes	Mandatory
E101-04	Searched DELETE statement	Yes	Mandatory
E111	Single row SELECT statement	No	Mandatory
E121	Basic cursor support	No	Mandatory
E121-01	DECLARE CURSOR	No	Mandatory
E121-02	ORDER BY columns need not be in select list	No	Mandatory
E121-03	Value expressions in ORDER BY clause	No	Mandatory
E121-04	OPEN statement	No	Mandatory
E121-06	Positioned UPDATE statement	No	Mandatory
E121-07	Positioned DELETE statement	No	Mandatory
E121-08	CLOSE statement	No	Mandatory
E121-10	FETCH statement: implicit NEXT	No	Mandatory
E121-17	WITH HOLD cursors	No	Mandatory
E131	Null value support (nulls in lieu of values)	Yes	Mandatory
E141	Basic integrity constraints	Partial	Mandatory	Don't support UNIQUE (VALUE) constraints, don't support UNIQUE over a list of columns. Unique constraints not enforced. Don't support referencing periods, MATCH, or triggered actions in foreign key. Don't support CHECK constraints.
E141-01	NOT NULL constraints	Yes	Mandatory
E141-02	UNIQUE constraints of NOT NULL columns	Partial	Mandatory	UNIQUE constraints not enforced
E141-03	PRIMARY KEY constraints	Partial	Mandatory	Primary keys not enforced
E141-04	Basic FOREIGN KEY constraint with the NO ACTION default for both referential delete action and referential update action.	Partial	Mandatory	Don't support referencing periods, MATCH, or triggered actions. Foreign keys not enforced.
E141-06	CHECK constraints	Yes	Mandatory
E141-07	Column defaults	Yes	Mandatory
E141-08	NOT NULL inferred on PRIMARY KEY	No	Mandatory	No need to declare NOT NULL with PRIMARY KEY or UNIQUE, but non-nullness not enforced.
E141-10	Names in a foreign key can be specified in any order	No	Mandatory
E151	Transaction support	No	Mandatory
E151-01	COMMIT statement	No	Mandatory
E151-02	ROLLBACK statement	No	Mandatory
E152	Basic SET TRANSACTION statement	No	Mandatory
E152-01	SET TRANSACTION state- ment: ISOLATION LEVEL SERIALIZABLE clause	No	Mandatory
E152-02	SET TRANSACTION state- ment: READ ONLY and READ WRITE clauses	No	Mandatory
E153	Updatable queries with subqueries	No	Mandatory
E161	SQL comments using leading double minus	Yes	Mandatory
E171	SQLSTATE support	No	Mandatory
F031	Basic schema manipulation	Yes	Mandatory
F031-01	CREATE TABLE statement to create persistent base tables	Yes	Mandatory
F031-02	CREATE VIEW statement	Yes	Mandatory
F031-03	GRANT statement	Yes	Mandatory
F031-04	ALTER TABLE statement: ADD COLUMN clause	Yes	Mandatory
F031-13	DROP TABLE statement: RESTRICT clause	Yes	Mandatory
F031-16	DROP VIEW statement: RESTRICT clause	Yes	Mandatory
F031-19	REVOKE statement: RESTRICT clause	No	Mandatory
F032	CASCADE drop behavior	Yes	Optional
F034	Extended REVOKE statement	Yes	Optional
F034-01	REVOKE statement performed by other than the owner of a schema object	Yes	Optional
F034-02	REVOKE statement: GRANT OPTION FOR clause	Yes	Optional
F034-03	REVOKE statement to revoke a privilege that the grantee has WITH GRANT OPTION	Yes	Optional
F041	Basic joined table	Yes	Mandatory
F041-01	Inner join (but not necessarily the INNER keyword)	Yes	Mandatory
F041-02	INNER keyword	Yes	Mandatory
F041-03	LEFT OUTER JOIN	Yes	Mandatory
F041-04	RIGHT OUTER JOIN	Yes	Mandatory
F041-05	Outer joins can be nested	Yes	Mandatory
F041-07	The inner table in a left or right outer join can also be used in an inner join	Yes	Mandatory
F041-08	All comparison operators are supported (rather than just =)	Yes	Mandatory
F051	Basic date and time	Partial	Mandatory	No support for WITH/OUT TIMEZONE. No support for precision in TIMESTAMP. No support for TIME type.
F051-01	DATE data type (including support of DATE literal)	Partial	Mandatory	Intervals don't match spec syntax
F051-02	TIME data type (including support of TIME literal) with fractional seconds precision of at least 0.	No	Mandatory
F051-03	TIMESTAMP data type (including support of TIMESTAMP literal) with fractional seconds precision of at least 0 and 6.	Partial	Mandatory	No support for WITH/OUT TIMEZONE. No support for precision. Intervals don't match spec syntax.
F051-04	Comparison predicate on DATE, TIME, and TIMESTAMP data types	Partial	Mandatory	No support for TIME
F051-05	Explicit CAST between date-time types and character string types	Partial	Mandatory	No support for TIME
F051-06	CURRENT_DATE	Yes	Mandatory
F051-07	LOCALTIME	No	Mandatory
F051-08	LOCALTIMESTAMP	Partial	Mandatory	CURRENT_TIMESTAMP supported, doesn't take precision argument LOCALTIMESTAMP not supported
F052	Intervals and datetime arithmetic	Partial	Optional	Interval not supported as column type, only as expression type in queries. Interval syntax differs from standard.
F054	TIMESTAMP in DATE type precedence list	Yes	Optional
F081	UNION and EXCEPT in views	Yes	Mandatory
F131	Grouped operations	Yes	Mandatory
F131-01	WHERE, GROUP BY, and HAVING clauses supported in queries with grouped views	Yes	Mandatory
F131-02	Multiple tables supported in queries with grouped views	Yes	Mandatory
F131-03	Set functions supported in queries with grouped views	Yes	Mandatory
F131-04	Subqueries with GROUP BY and HAVING clauses and grouped views	Yes	Mandatory
F131-05	Single row SELECT with GROUP BY and HAVING clauses and grouped views	Yes	Mandatory
F171	Multiple schemas per user	Yes	Optional
F200	TRUNCATE TABLE statement	Yes	Optional
F181	Multiple module support	No	Mandatory
F201	CAST function	Yes	Mandatory
F221	Explicit defaults	Yes	Mandatory
F261	CASE expression	Yes	Mandatory
F261-01	Simple CASE	Yes	Mandatory
F261-02	Searched CASE	Yes	Mandatory
F261-03	NULLIF	Yes	Mandatory
F261-04	COALESCE	Yes	Mandatory
F302	INTERSECT table operator	Yes	Optional
F302-01	INTERSECT DISTINCT table operator	Yes	Optional
F302-02	INTERSECT ALL table operator	Yes	Optional
F304	EXCEPT ALL table operator	Yes	Optional
F311	Schema definition statement	Yes	Mandatory
F311-01	CREATE SCHEMA	Yes	Mandatory
F311-02	CREATE TABLE for persistent base tables	Partial	Mandatory	Does not create schema element creation as part of schema creation, must be done in separate statement
F311-03	CREATE VIEW	Partial	Mandatory	Does not create schema element creation as part of schema creation, must be done in separate statement
F311-04	CREATE VIEW: WITH CHECK OPTION	No	Mandatory
F311-05	GRANT statement	Partial	Mandatory	Does not create schema element creation as part of schema creation, must be done in separate statement
F312	MERGE statement	Yes	Optional
F313	Enhanced MERGE statement	Yes	Optional
F314	MERGE statement with DELETE branch	Yes	Optional
F321	User authorization	Partial	Optional	Support for CURRENT_USER function, none of the rest
F381	Extended schema manipulation	Partial	Optional	No support for scope. No support for ALTER routine.
F381-01	ALTER TABLE statement: ALTER COLUMN clause	Partial	Optional	Syntax non-standard. No support for scope. No support for identities. No support for column generation.
F381-02	ALTER TABLE statement: ADD CONSTRAINT clause	Partial	Optional	Same limitations as creating constraints above
F381-03	ALTER TABLE statement: DROP CONSTRAINT clause	Partial	Optional	Same limitations as creating constraints above
F382	Alter column data type	Partial	Optional	Syntax non-standard
F383	Set column not null clause	Partial	Optional	Syntax non-standard
F391	Long identifiers	Yes	Optional
F401	Extended joined table	Partial	Optional	NATURAL joins not supported
F401-02	FULL OUTER JOIN	Yes	Optional
F401-04	CROSS JOIN	Yes	Optional
F471	Scalar subquery values	Yes	Mandatory
F481	Expanded NULL predicate	Yes	Mandatory
F531	Temporary tabels	Partial	Optional	GLOBAL/LOCAL scope not supported. DECLARE TEMPORARY TABLE not supported.
F555	Enhanced seconds precision	Yes	Optional
F763	CURRENT_SCHEMA	Partial	Optional	CURRENT_DATABASE, which is equivalent
F812	Basic flagging	No	Mandatory
F841	LIKE_REGEX predicate	Partial	Optional	use RLIKE instead
F847	Nonconstant regular expressions	Yes	Optional
F850	Top level <order by clause> in <query expression>	Yes	Optional
F851	<order by clause> in subqueries	Yes	Optional
F852	Top-level <order by clause> in views	Yes	Optional
F855	Nested <order by clause> in <query expression>	Yes	Optional
F856	Nested <fetch first clause> in <query expression>	Yes	Optional
F857	Top-level <fetch first clause> in <query expression>	Yes	Optional
F858	<fetch first clause> in subqueries	Yes	Optional
F859	Top-level <fetch first clause> in views	Yes	Optional
S011	Distinct data types	No	Mandatory
S091	Basic array support	Partial	Optional	Syntax non-standard. No option to declare max cardinality. SIZE instead of CARDINALITY.
S091-01	Arrays of built-in data types	Partial	Optional	Syntax non-standard
S091-03	Array expressions	Partial	Optional	Support array element reference and cardinality (though syntax non-standard) No support for array concatenation, trimming, or max-cardinality
T021	BINARY and VARBINARY types	Partial	Optional	BINARY only, though it acts like VARBINARY, no length parameter accepted. No support for overlay, trim, position, or LIKE.
T031	BOOLEAN data type	Yes	Optional
T041	Basic LOB data type support	Partial	Optional	BINARY acts as BLOB (no size restrictions) STRING acts as CLOB Non-standard syntax
T041-01	BLOB data type	Partial	Optional	BINARY acts as BLOB, non-standard syntax
T041-02	CLOB data type	Partial	Optional	STRING acts as CLOB, non-standard syntax
T041-03	POSITION, LENGTH, LOWER, TRIM, UPPER, SUBSTRING for LOB data types	Partial	Optional	No POSITION LOWER, UPPER only applicable to STRING
T041-04	Concatenation of LOB types	Yes	Optional
T042	Extended LOB data type support	Partial	Optional	Cast for BINARY and STRING supported. LIKE for STRING supported. All other advanced options not supported.
T051	Row types	Partial	Optional	Called STRUCT rather than ROW
T071	BIGINT data type	Yes	Optional
T121	WITH (excluding RECURSIVE) in query expression	Yes	Optional
T321	Basic SQL-invoked routines	No	Mandatory
T321-01	User-defined functions with no overloading	No	Mandatory
T321-02	User-defined stored procedures with no overloading	No	Mandatory
T321-03	Function invocation	No	Mandatory
T321-04	CALL statement	No	Mandatory
T321-05	RETURN statement	No	Mandatory
T331	Basic roles	Yes	Optional
T351	Bracketed comments	Yes	Optional
T431	Extended grouping capabilities	Yes	Optional
T433	Multiargument GROUPING function	Yes	Optional
T441	ABS and MOD functions	Yes	Optional
T501	Enhanced EXISTS predicate	Yes	Optional
T581	Regular expression substring function	Yes	Optional
T591	UNIQUE constraints of possibly null columns	Yes	Optional
T611	Elementary OLAP operations	Yes	Optional
T612	Advanced OLAP operations	Partial	Optional
T613	Sampling	Yes	Optional
T614	NTILE function	Yes	Optional
T615	LEAD and LAG functions	Yes	Optional
T617	FIRST_VALUE and LAST_VALUE functions	Yes	Optional
T621	Enhanced numeric functions	Yes	Optional
T622	Trigonometric functions	Partial	Optional	No sinh, cosh, tanh
T623	General logarithm functions	Yes	Optional
T624	Common logarithm functions	Yes	Optional
T631	IN predicate with one list element	Yes	Mandatory

Space shortcuts

Child pages

5 Comments

Vineet Garg

Vineet Garg

Alan Gates

Vineet Garg

Alan Gates