This Confluence has been LDAP enabled, if you are an ASF Committer, please use your LDAP Credentials to login. Any problems file an INFRA jira ticket please.

Child pages
  • Supported Features: Apache Hive 3.1
Skip to end of metadata
Go to start of metadata

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

Feature IDFeature NameImplementedMandatoryComments
E011Numeric data typesYesMandatory
E011-01INTEGER and SMALLINT data types (including all spellings)YesMandatory
E011-02REAL, DOUBLE PRECISON, and FLOAT data typesYesMandatory
E011-03DECIMAL and NUMERIC data typesYesMandatory
E011-04Arithmetic operatorsYesMandatory
E011-05Numeric comparisonYesMandatory
E011-06Implicit casting among the numeric data typesYesMandatory
E021Character string typesYesMandatory
E021-01CHARACTER data type (including all its spellings)PartialMandatoryOnly support CHAR, not CHARACTER
E021-02CHARACTER VARYING data type (including all its spellings)PartialMandatoryOnly support VARCHAR, not CHARACTER VARYING or CHAR VARYING
E021-03Character literalsYesMandatory
E021-04CHARACTER_LENGTH functionYesMandatory
E021-05OCTET_LENGTH functionYesMandatory
E021-06SUBSTRING functionPartialMandatoryStandard: SUBSTRING(val FROM startpos [FOR len]). Hive: SUBSTRING(val, startpos [, len])
E021-07Character concatenationYesMandatory
E021-08UPPER and LOWER functionsYesMandatory
E021-09TRIM functionYesMandatory
E021-10Implicit casting among the fixed-length and variable-length character string typesYesMandatory
E021-11POSITION functionNoMandatory
E021-12Character comparisonYesMandatory
E031IdentifiersPartialMandatoryUnquoted identifiers use C syntax ([A-Za-z][A-Za-z0-9_]*). Quoted identifiers can have any character.
E031-01Delimited identifiersPartialMandatoryQuoting done with ` rather than ", only supported for columns, not tables, views, etc.
E031-02Lower case identifiersYesMandatory
E031-03Trailing underscoreYesMandatory
E051Basic query specificationYesMandatory
E051-01SELECT DISTINCTYesMandatory
E051-02GROUP BY clauseYesMandatory
E051-04GROUP BY can contain columns not in <select list>YesMandatory
E051-05Select list items can be renamedYesMandatory
E051-06HAVING clauseYesMandatory
E051-07Qualified * in select listYesMandatory
E051-08Correlation names in the FROM clauseYesMandatory
E051-09Rename columns in the FROM clauseYesMandatory
E061Basic predicates and search conditionsYesMandatory
E061-01Comparison predicateYesMandatory
E061-02BETWEEN predicateYesMandatory
E061-03IN predicate with list of valuesYesMandatory
E061-04LIKE predicateYesMandatory
E061-05LIKE predicate: ESCAPE clauseYesMandatory
E061-06NULL predicateYesMandatory
E061-07Quantified comparison predicateNoMandatory
E061-08EXISTS predicateYesMandatory
E061-09Subqueries in comparison predicateNoMandatory
E061-11Subqueries in IN predicateYesMandatory
E061-12Subqueries in quantified comparison predicateNoMandatory
E061-13Correlated subqueriesYesMandatory
E061-14Search conditionYesMandatory
E071Basic query expressionsYesMandatory
E071-01UNION DISTINCT table operatorYesMandatory
E071-02UNION ALL table operatorYesMandatory
E071-03EXCEPT DISTINCT table operatorYesMandatory
E071-05Columns combined via table operators need not have exactly the same data type.YesMandatory
E071-06Table operators in subqueriesYesMandatory
E081Basic PrivilegesYesMandatory
E081-01SELECT privilege at the table levelYesMandatory
E081-02DELETE privilegeYesMandatory
E081-03INSERT privilege at the table levelYesMandatory
E081-04UPDATE privilege at the table levelYesMandatory
E081-05UPDATE privilege at the column levelYesMandatory
E081-06REFERENCES privilege at the table levelNoMandatory
E081-07REFERENCES privilege at the column levelNoMandatory
E081-08WITH GRANT OPTIONYesMandatory
E081-09USAGE privilegeNoMandatory
E081-10EXECUTE privilegeNoMandatory
E091Set functionsYesMandatory
E091-01AVGYesMandatory
E091-02COUNTYesMandatory
E091-03MAXYesMandatory
E091-04MINYesMandatory
E091-05SUMYesMandatory
E091-06ALL quantifierYesMandatory
E091-07DISTINCT quantifierYesMandatory
E101Basic data manipulationYesMandatory
E101-01INSERT statementYesMandatory
E101-03Searched UPDATE statementYesMandatory
E101-04Searched DELETE statementYesMandatory
E111Single row SELECT statementNoMandatory
E121Basic cursor supportNoMandatory
E121-01DECLARE CURSORNoMandatory
E121-02ORDER BY columns need not be in select listNoMandatory
E121-03Value expressions in ORDER BY clauseNoMandatory
E121-04OPEN statementNoMandatory
E121-06Positioned UPDATE statementNoMandatory
E121-07Positioned DELETE statementNoMandatory
E121-08CLOSE statementNoMandatory
E121-10FETCH statement: implicit NEXTNoMandatory
E121-17WITH HOLD cursorsNoMandatory
E131Null value support (nulls in lieu of values)YesMandatory
E141Basic integrity constraintsPartialMandatoryDon'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-01NOT NULL constraintsYesMandatory
E141-02UNIQUE constraints of NOT NULL columnsPartialMandatoryUNIQUE constraints not enforced
E141-03PRIMARY KEY constraintsPartialMandatoryPrimary keys not enforced
E141-04Basic FOREIGN KEY constraint with the NO ACTION default for both referential delete action and referential update action.PartialMandatory

Don't support referencing periods, MATCH, or triggered actions.
Foreign keys not enforced.

E141-06CHECK constraintsYesMandatory
E141-07Column defaultsYesMandatory
E141-08NOT NULL inferred on PRIMARY KEYNoMandatoryNo need to declare NOT NULL with PRIMARY KEY or UNIQUE, but non-nullness not enforced.
E141-10Names in a foreign key can be specified in any orderNoMandatory
E151Transaction supportNoMandatory
E151-01COMMIT statementNoMandatory
E151-02ROLLBACK statementNoMandatory
E152Basic SET TRANSACTION statementNoMandatory
E152-01SET TRANSACTION state- ment: ISOLATION LEVEL SERIALIZABLE clauseNoMandatory
E152-02SET TRANSACTION state- ment: READ ONLY and READ WRITE clausesNoMandatory
E153Updatable queries with subqueriesNoMandatory
E161SQL comments using leading double minusYesMandatory
E171SQLSTATE supportNoMandatory
F031Basic schema manipulationYesMandatory
F031-01CREATE TABLE statement to create persistent base tablesYesMandatory
F031-02CREATE VIEW statementYesMandatory
F031-03GRANT statementYesMandatory
F031-04ALTER TABLE statement: ADD COLUMN clauseYesMandatory
F031-13DROP TABLE statement: RESTRICT clauseYesMandatory
F031-16DROP VIEW statement: RESTRICT clauseYesMandatory
F031-19REVOKE statement: RESTRICT clauseNoMandatory
F032CASCADE drop behaviorYesOptional
F034Extended REVOKE statementYesOptional
F034-01REVOKE statement performed by other than the owner of a schema objectYesOptional
F034-02REVOKE statement: GRANT OPTION FOR clauseYesOptional
F034-03REVOKE statement to revoke a privilege that the grantee has WITH GRANT OPTIONYesOptional
F041Basic joined tableYesMandatory
F041-01Inner join (but not necessarily the INNER keyword)YesMandatory
F041-02INNER keywordYesMandatory
F041-03LEFT OUTER JOINYesMandatory
F041-04RIGHT OUTER JOINYesMandatory
F041-05Outer joins can be nestedYesMandatory
F041-07The inner table in a left or right outer join can also be used in an inner joinYesMandatory
F041-08All comparison operators are supported (rather than just =)YesMandatory
F051Basic date and timePartialMandatoryNo support for WITH/OUT TIMEZONE.
No support for precision in TIMESTAMP.
No support for TIME type.
F051-01DATE data type (including support of DATE literal)PartialMandatoryIntervals don't match spec syntax
F051-02TIME data type (including support of TIME literal) with fractional seconds precision of at least 0.NoMandatory
F051-03TIMESTAMP data type (including support of TIMESTAMP literal) with fractional seconds precision of at least 0 and 6.PartialMandatoryNo support for WITH/OUT TIMEZONE.
No support for precision.
Intervals don't match spec syntax.
F051-04Comparison predicate on DATE, TIME, and TIMESTAMP data typesPartialMandatoryNo support for TIME
F051-05Explicit CAST between date-time types and character string typesPartialMandatoryNo support for TIME
F051-06CURRENT_DATEYesMandatory
F051-07LOCALTIMENoMandatory
F051-08LOCALTIMESTAMPPartialMandatoryCURRENT_TIMESTAMP supported, doesn't take precision argument
LOCALTIMESTAMP not supported 
F052Intervals and datetime arithmeticPartialOptionalInterval not supported as column type, only as expression type in queries.
Interval syntax differs from standard.
F054TIMESTAMP in DATE type precedence listYesOptional
F081UNION and EXCEPT in viewsYesMandatory
F131Grouped operationsYesMandatory
F131-01WHERE, GROUP BY, and HAVING clauses supported in queries with grouped viewsYesMandatory
F131-02Multiple tables supported in queries with grouped viewsYesMandatory
F131-03Set functions supported in queries with grouped viewsYesMandatory
F131-04Subqueries with GROUP BY and HAVING clauses and grouped viewsYesMandatory
F131-05Single row SELECT with GROUP BY and HAVING clauses and grouped viewsYesMandatory
F171Multiple schemas per userYesOptional
F200TRUNCATE TABLE statementYesOptional
F181Multiple module supportNoMandatory
F201CAST functionYesMandatory
F221Explicit defaultsYesMandatory
F261CASE expressionYesMandatory
F261-01Simple CASEYesMandatory
F261-02Searched CASEYesMandatory
F261-03NULLIFYesMandatory
F261-04COALESCEYesMandatory
F302INTERSECT table operatorYesOptional
F302-01INTERSECT DISTINCT table operatorYesOptional
F302-02INTERSECT ALL table operatorYesOptional
F304EXCEPT ALL table operatorYesOptional
F311Schema definition statementYesMandatory
F311-01CREATE SCHEMAYesMandatory
F311-02CREATE TABLE for persistent base tablesPartialMandatoryDoes not create schema element creation as part of schema creation, must be done in separate statement
F311-03CREATE VIEWPartialMandatoryDoes not create schema element creation as part of schema creation, must be done in separate statement
F311-04CREATE VIEW: WITH CHECK OPTIONNoMandatory
F311-05GRANT statementPartialMandatoryDoes not create schema element creation as part of schema creation, must be done in separate statement
F312MERGE statementYesOptional
F313Enhanced MERGE statementYesOptional
F314MERGE statement with DELETE branchYesOptional
F321User authorizationPartialOptionalSupport for CURRENT_USER function, none of the rest
F381Extended schema manipulationPartialOptionalNo support for scope.
No support for ALTER routine.
F381-01ALTER TABLE statement: ALTER COLUMN clausePartialOptionalSyntax non-standard.
No support for scope.
No support for identities.
No support for column generation.
F381-02ALTER TABLE statement: ADD CONSTRAINT clausePartialOptionalSame limitations as creating constraints above
F381-03ALTER TABLE statement: DROP CONSTRAINT clausePartialOptionalSame limitations as creating constraints above
F382Alter column data typePartialOptionalSyntax non-standard
F383Set column not null clausePartialOptionalSyntax non-standard
F391Long identifiersYesOptional
F401Extended joined tablePartialOptionalNATURAL joins not supported
F401-02FULL OUTER JOINYesOptional
F401-04CROSS JOINYesOptional
F471Scalar subquery values

Yes

Mandatory
F481Expanded NULL predicate

Yes

Mandatory
F531Temporary tabelsPartialOptionalGLOBAL/LOCAL scope not supported.
DECLARE TEMPORARY TABLE not supported.
F555Enhanced seconds precisionYesOptional
F763CURRENT_SCHEMAPartialOptionalCURRENT_DATABASE, which is equivalent
F812Basic flaggingNoMandatory
F841LIKE_REGEX predicatePartialOptionaluse RLIKE instead
F847Nonconstant regular expressionsYesOptional
F850Top level <order by clause> in <query expression>YesOptional
F851<order by clause> in subqueriesYesOptional
F852Top-level <order by clause> in viewsYesOptional
F855Nested <order by clause> in <query expression>YesOptional
F856Nested <fetch first clause> in <query expression>YesOptional
F857Top-level <fetch first clause> in <query expression>YesOptional
F858<fetch first clause> in subqueriesYesOptional
F859Top-level <fetch first clause> in viewsYesOptional
S011Distinct data typesNoMandatory
S091Basic array supportPartialOptionalSyntax non-standard.
No option to declare max cardinality.
SIZE instead of CARDINALITY.
S091-01Arrays of built-in data typesPartialOptionalSyntax non-standard
S091-03Array expressionsPartialOptionalSupport array element reference and cardinality (though syntax non-standard)
No support for array concatenation, trimming, or max-cardinality
T021BINARY and VARBINARY typesPartialOptionalBINARY only, though it acts like VARBINARY, no length parameter accepted.
No support for overlay, trim, position, or LIKE.
T031BOOLEAN data typeYesOptional
T041Basic LOB data type supportPartialOptionalBINARY acts as BLOB (no size restrictions)
STRING acts as CLOB
Non-standard syntax
T041-01BLOB data typePartialOptionalBINARY acts as BLOB, non-standard syntax
T041-02CLOB data typePartialOptionalSTRING acts as CLOB, non-standard syntax
T041-03POSITION, LENGTH, LOWER, TRIM, UPPER, SUBSTRING for LOB data typesPartialOptionalNo POSITION
LOWER, UPPER only applicable to STRING
T041-04Concatenation of LOB typesYesOptional
T042Extended LOB data type supportPartialOptionalCast for BINARY and STRING supported.
LIKE for STRING supported.
All other advanced options not supported.
T051Row typesPartialOptionalCalled STRUCT rather than ROW
T071BIGINT data typeYesOptional
T121WITH (excluding RECURSIVE) in query expressionYesOptional
T321Basic SQL-invoked routinesNoMandatory
T321-01User-defined functions with no overloadingNoMandatory
T321-02User-defined stored procedures with no overloadingNoMandatory
T321-03Function invocationNoMandatory
T321-04CALL statementNoMandatory
T321-05RETURN statementNoMandatory
T331Basic rolesYesOptional
T351Bracketed commentsYesOptional
T431Extended grouping capabilitiesYesOptional
T433Multiargument GROUPING functionYesOptional
T441ABS and MOD functionsYesOptional
T501Enhanced EXISTS predicateYesOptional
T581Regular expression substring functionYesOptional
T591UNIQUE constraints of possibly null columnsYesOptional
T611Elementary OLAP operationsYesOptional
T612Advanced OLAP operationsPartialOptional
T613SamplingYesOptional
T614NTILE functionYesOptional
T615LEAD and LAG functionsYesOptional
T617FIRST_VALUE and LAST_VALUE functionsYesOptional
T621Enhanced numeric functionsYesOptional
T622Trigonometric functionsPartialOptionalNo sinh, cosh, tanh
T623General logarithm functionsYesOptional
T624Common logarithm functionsYesOptional
T631IN predicate with one list elementYesMandatory
  • No labels

5 Comments

  1. Alan Gates Following features are supported in 3.1:

    E061-09Subqueries in comparison predicate
    E141-06CHECK constraintsNoMandatory
  2. No need to declare NOT NULL with PRIMARY KEY or UNIQUE - I think this is not true. NOT NULL is not inferred on UNIQUE and needs to be explicitly declared.

    1. IIUC the requirement isn't that you don't need to declare not null and it is inferred, but rather that it can support unique/pk indices with nulls in them.  

  3. E121-02ORDER BY columns need not be in select listNoMandatory

     Looks like this feature is partially supported. Hive allows this if there is not aggregate.

    1. Agreed, I missed this one.  Feel free to edit it.  I'll be circling back on this and a few others shortly to fix it.