layout: global title: ANSI Compliance displayTitle: ANSI Compliance license: | Licensed to the Apache Software Foundation (ASF) under one or more contributor license agreements. See the NOTICE file distributed with this work for additional information regarding copyright ownership. The ASF licenses this file to You under the Apache License, Version 2.0 (the “License”); you may not use this file except in compliance with the License. You may obtain a copy of the License at

 http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an “AS IS” BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.

In Spark SQL, there are two options to comply with the SQL standard: spark.sql.ansi.enabled and spark.sql.storeAssignmentPolicy (See a table below for details).

By default, spark.sql.ansi.enabled is true and Spark SQL uses an ANSI compliant dialect instead of being Hive compliant. For example, Spark will throw an exception at runtime instead of returning null results if the inputs to a SQL operator/function are invalid. Some ANSI dialect features may be not from the ANSI SQL standard directly, but their behaviors align with ANSI SQL's style.

Moreover, Spark SQL has an independent option to control implicit casting behaviours when inserting rows in a table. The casting behaviours are defined as store assignment rules in the standard.

By default, spark.sql.storeAssignmentPolicy is ANSI and Spark SQL complies with the ANSI store assignment rules.

The following subsections present behaviour changes in arithmetic operations, type conversions, and SQL parsing when the ANSI mode enabled. For type conversions in Spark SQL, there are three kinds of them and this article will introduce them one by one: cast, store assignment and type coercion.

Arithmetic Operations

In Spark SQL, by default, Spark throws an arithmetic exception at runtime for both interval and numeric type overflows. If spark.sql.ansi.enabled is false, then the decimal type will produce null values and other numeric types will behave in the same way as the corresponding operation in a Java/Scala program (e.g., if the sum of 2 integers is higher than the maximum value representable, the result is a negative number) which is the behavior of Spark 3 or older.

-- `spark.sql.ansi.enabled=true`
SELECT 2147483647 + 1;
org.apache.spark.SparkArithmeticException: [ARITHMETIC_OVERFLOW] integer overflow. Use 'try_add' to tolerate overflow and return NULL instead. If necessary set spark.sql.ansi.enabled to "false" to bypass this error.
== SQL(line 1, position 8) ==
SELECT 2147483647 + 1
       ^^^^^^^^^^^^^^

SELECT abs(-2147483648);
org.apache.spark.SparkArithmeticException: [ARITHMETIC_OVERFLOW] integer overflow. If necessary set spark.sql.ansi.enabled to "false" to bypass this error.

-- `spark.sql.ansi.enabled=false`
SELECT 2147483647 + 1;
+----------------+
|(2147483647 + 1)|
+----------------+
|     -2147483648|
+----------------+

SELECT abs(-2147483648);
+----------------+
|abs(-2147483648)|
+----------------+
|     -2147483648|
+----------------+

Cast

When spark.sql.ansi.enabled is set to true, explicit casting by CAST syntax throws a runtime exception for illegal cast patterns defined in the standard, e.g. casts from a string to an integer.

Besides, the ANSI SQL mode disallows the following type conversions which are allowed when ANSI mode is off:

  • Numeric <=> Binary
  • Date <=> Boolean
  • Timestamp <=> Boolean
  • Date => Numeric

The valid combinations of source and target data type in a CAST expression are given by the following table. “Y” indicates that the combination is syntactically valid without restriction and “N” indicates that the combination is not valid.

Source\TargetNumericStringDateTimestampTimestamp_NTZIntervalBooleanBinaryArrayMapStruct
NumericYYNYNYYNNNN
StringYYYYYYYYNNN
DateNYYYYNNNNNN
TimestampYYYYYNNNNNN
Timestamp_NTZNYYYYNNNNNN
IntervalYYNNNYNNNNN
BooleanYYNNNNYNNNN
BinaryNYNNNNNYNNN
ArrayNYNNNNNNYNN
MapNYNNNNNNNYN
StructNYNNNNNNNNY

In the table above, all the CASTs with new syntax are marked as red Y:

  • CAST(Numeric AS Numeric): raise an overflow exception if the value is out of the target data type's range.
  • CAST(String AS (Numeric/Date/Timestamp/Timestamp_NTZ/Interval/Boolean)): raise a runtime exception if the value can't be parsed as the target data type.
  • CAST(Timestamp AS Numeric): raise an overflow exception if the number of seconds since epoch is out of the target data type's range.
  • CAST(Numeric AS Timestamp): raise an overflow exception if numeric value times 1000000(microseconds per second) is out of the range of Long type.
  • CAST(Array AS Array): raise an exception if there is any on the conversion of the elements.
  • CAST(Map AS Map): raise an exception if there is any on the conversion of the keys and the values.
  • CAST(Struct AS Struct): raise an exception if there is any on the conversion of the struct fields.
  • CAST(Numeric AS String): Always use plain string representation on casting decimal values to strings, instead of using scientific notation if an exponent is needed
  • CAST(Interval AS Numeric): raise an overflow exception if the number of microseconds of the day-time interval or months of year-month interval is out of the target data type's range.
  • CAST(Numeric AS Interval): raise an overflow exception if numeric value times by the target interval's end-unit is out of the range of the Int type for year-month intervals or the Long type for day-time intervals.
-- Examples of explicit casting

-- `spark.sql.ansi.enabled=true`
SELECT CAST('a' AS INT);
org.apache.spark.SparkNumberFormatException: [CAST_INVALID_INPUT] The value 'a' of the type "STRING" cannot be cast to "INT" because it is malformed. Correct the value as per the syntax, or change its target type. Use `try_cast` to tolerate malformed input and return NULL instead. If necessary set "spark.sql.ansi.enabled" to "false" to bypass this error.
== SQL(line 1, position 8) ==
SELECT CAST('a' AS INT)
       ^^^^^^^^^^^^^^^^

SELECT CAST(2147483648L AS INT);
org.apache.spark.SparkArithmeticException: [CAST_OVERFLOW] The value 2147483648L of the type "BIGINT" cannot be cast to "INT" due to an overflow. Use `try_cast` to tolerate overflow and return NULL instead. If necessary set "spark.sql.ansi.enabled" to "false" to bypass this error.

SELECT CAST(DATE'2020-01-01' AS INT);
org.apache.spark.sql.AnalysisException: cannot resolve 'CAST(DATE '2020-01-01' AS INT)' due to data type mismatch: cannot cast date to int.
To convert values from date to int, you can use function UNIX_DATE instead.

-- `spark.sql.ansi.enabled=false` (This is a default behaviour)
SELECT CAST('a' AS INT);
+--------------+
|CAST(a AS INT)|
+--------------+
|          null|
+--------------+

SELECT CAST(2147483648L AS INT);
+-----------------------+
|CAST(2147483648 AS INT)|
+-----------------------+
|            -2147483648|
+-----------------------+

SELECT CAST(DATE'2020-01-01' AS INT)
+------------------------------+
|CAST(DATE '2020-01-01' AS INT)|
+------------------------------+
|                          null|
+------------------------------+

-- Examples of store assignment rules
CREATE TABLE t (v INT);

-- `spark.sql.storeAssignmentPolicy=ANSI`
INSERT INTO t VALUES ('1');
org.apache.spark.sql.AnalysisException: [INCOMPATIBLE_DATA_FOR_TABLE.CANNOT_SAFELY_CAST] Cannot write incompatible data for table `spark_catalog`.`default`.`t`: Cannot safely cast `v`: "STRING" to "INT".

-- `spark.sql.storeAssignmentPolicy=LEGACY` (This is a legacy behaviour until Spark 2.x)
INSERT INTO t VALUES ('1');
SELECT * FROM t;
+---+
|  v|
+---+
|  1|
+---+

Rounding in cast

While casting of a decimal with a fraction to an interval type with SECOND as the end-unit like INTERVAL HOUR TO SECOND, Spark rounds the fractional part towards “nearest neighbor” unless both neighbors are equidistant, in which case round up.

Store assignment

As mentioned at the beginning, when spark.sql.storeAssignmentPolicy is set to ANSI(which is the default value), Spark SQL complies with the ANSI store assignment rules on table insertions. The valid combinations of source and target data type in table insertions are given by the following table.

Source\TargetNumericStringDateTimestampTimestamp_NTZIntervalBooleanBinaryArrayMapStruct
NumericYYNNNNNNNNN
StringNYNNNNNNNNN
DateNYYYYNNNNNN
TimestampNYYYYNNNNNN
Timestamp_NTZNYYYYNNNNNN
IntervalNYNNNN*NNNNN
BooleanNYNNNNYNNNN
BinaryNYNNNNNYNNN
ArrayNNNNNNNNY**NN
MapNNNNNNNNNY**N
StructNNNNNNNNNNY**

* Spark doesn't support interval type table column.

** For Array/Map/Struct types, the data type check rule applies recursively to its component elements.

During table insertion, Spark will throw exception on numeric value overflow.

CREATE TABLE test(i INT);

INSERT INTO test VALUES (2147483648L);
org.apache.spark.SparkArithmeticException: [CAST_OVERFLOW_IN_TABLE_INSERT] Fail to insert a value of "BIGINT" type into the "INT" type column `i` due to an overflow. Use `try_cast` on the input value to tolerate overflow and return NULL instead.

Type coercion

Type Promotion and Precedence

When spark.sql.ansi.enabled is set to true, Spark SQL uses several rules that govern how conflicts between data types are resolved. At the heart of this conflict resolution is the Type Precedence List which defines whether values of a given data type can be promoted to another data type implicitly.

Data typeprecedence list(from narrowest to widest)
ByteByte -> Short -> Int -> Long -> Decimal -> Float* -> Double
ShortShort -> Int -> Long -> Decimal-> Float* -> Double
IntInt -> Long -> Decimal -> Float* -> Double
LongLong -> Decimal -> Float* -> Double
DecimalDecimal -> Float* -> Double
FloatFloat -> Double
DoubleDouble
DateDate -> Timestamp_NTZ -> Timestamp
TimestampTimestamp
StringString, Long -> Double, Date -> Timestamp_NTZ -> Timestamp , Boolean, Binary **
BinaryBinary
BooleanBoolean
IntervalInterval
MapMap***
ArrayArray***
StructStruct***

* For least common type resolution float is skipped to avoid loss of precision.

** String can be promoted to multiple kinds of data types. Note that Byte/Short/Int/Decimal/Float is not on this precedent list. The least common type between Byte/Short/Int and String is Long, while the least common type between Decimal/Float is Double.

*** For a complex type, the precedence rule applies recursively to its component elements.

Special rules apply for untyped NULL. A NULL can be promoted to any other type.

This is a graphical depiction of the precedence list as a directed tree:

Least Common Type Resolution

The least common type from a set of types is the narrowest type reachable from the precedence list by all elements of the set of types.

The least common type resolution is used to:

  • Derive the argument type for functions which expect a shared argument type for multiple parameters, such as coalesce, least, or greatest.
  • Derive the operand types for operators such as arithmetic operations or comparisons.
  • Derive the result type for expressions such as the case expression.
  • Derive the element, key, or value types for array and map constructors. Special rules are applied if the least common type resolves to FLOAT. With float type values, if any of the types is INT, BIGINT, or DECIMAL the least common type is pushed to DOUBLE to avoid potential loss of digits.

Decimal type is a bit more complicated here, as it's not a simple type but has parameters: precision and scale. A decimal(precision, scale) means the value can have at most precision - scale digits in the integral part and scale digits in the fractional part. A least common type between decimal types should have enough digits in both integral and fractional parts to represent all values. More precisely, a least common type between decimal(p1, s1) and decimal(p2, s2) has the scale of max(s1, s2) and precision of max(s1, s2) + max(p1 - s1, p2 - s2). However, decimal types in Spark have a maximum precision: 38. If the final decimal type need more precision, we must do truncation. Since the digits in the integral part are more significant, Spark truncates the digits in the fractional part first. For example, decimal(48, 20) will be reduced to decimal(38, 10).

Note, arithmetic operations have special rules to calculate the least common type for decimal inputs:

OperationResult precisionResult scale
e1 + e2max(s1, s2) + max(p1 - s1, p2 - s2) + 1max(s1, s2)
e1 - e2max(s1, s2) + max(p1 - s1, p2 - s2) + 1max(s1, s2)
e1 * e2p1 + p2 + 1s1 + s2
e1 / e2p1 - s1 + s2 + max(6, s1 + p2 + 1)max(6, s1 + p2 + 1)
e1 % e2min(p1 - s1, p2 - s2) + max(s1, s2)max(s1, s2)

The truncation rule is also different for arithmetic operations: they retain at least 6 digits in the fractional part, which means we can only reduce scale to 6. Overflow may happen in this case.

-- The coalesce function accepts any set of argument types as long as they share a least common type. 
-- The result type is the least common type of the arguments.
> SET spark.sql.ansi.enabled=true;
> SELECT typeof(coalesce(1Y, 1L, NULL));
BIGINT
> SELECT typeof(coalesce(1, DATE'2020-01-01'));
Error: Incompatible types [INT, DATE]

> SELECT typeof(coalesce(ARRAY(1Y), ARRAY(1L)));
ARRAY<BIGINT>
> SELECT typeof(coalesce(1, 1F));
DOUBLE
> SELECT typeof(coalesce(1L, 1F));
DOUBLE
> SELECT (typeof(coalesce(1BD, 1F)));
DOUBLE

> SELECT typeof(coalesce(1, '2147483648'))
BIGINT
> SELECT typeof(coalesce(1.0, '2147483648'))
DOUBLE
> SELECT typeof(coalesce(DATE'2021-01-01', '2022-01-01'))
DATE

SQL Functions

Function invocation

Under ANSI mode(spark.sql.ansi.enabled=true), the function invocation of Spark SQL:

  • In general, it follows the Store assignment rules as storing the input values as the declared parameter type of the SQL functions
  • Special rules apply for untyped NULL. A NULL can be promoted to any other type.
> SET spark.sql.ansi.enabled=true;
-- implicitly cast Int to String type
> SELECT concat('total number: ', 1);
total number: 1
-- implicitly cast Timestamp to Date type
> select datediff(now(), current_date);
0

-- implicitly cast String to Double type
> SELECT ceil('0.1');
1
-- special rule: implicitly cast NULL to Date type
> SELECT year(null);
NULL

> CREATE TABLE t(s string);
-- Can't store String column as Numeric types.
> SELECT ceil(s) from t;
Error in query: cannot resolve 'CEIL(spark_catalog.default.t.s)' due to data type mismatch
-- Can't store String column as Date type.
> select year(s) from t;
Error in query: cannot resolve 'year(spark_catalog.default.t.s)' due to data type mismatch

Functions with different behaviors

The behavior of some SQL functions can be different under ANSI mode (spark.sql.ansi.enabled=true).

  • size: This function returns null for null input.
  • element_at:
    • This function throws ArrayIndexOutOfBoundsException if using invalid indices.
  • elt: This function throws ArrayIndexOutOfBoundsException if using invalid indices.
  • parse_url: This function throws IllegalArgumentException if an input string is not a valid url.
  • to_date: This function should fail with an exception if the input string can't be parsed, or the pattern string is invalid.
  • to_timestamp: This function should fail with an exception if the input string can't be parsed, or the pattern string is invalid.
  • unix_timestamp: This function should fail with an exception if the input string can't be parsed, or the pattern string is invalid.
  • to_unix_timestamp: This function should fail with an exception if the input string can't be parsed, or the pattern string is invalid.
  • make_date: This function should fail with an exception if the result date is invalid.
  • make_timestamp: This function should fail with an exception if the result timestamp is invalid.
  • make_interval: This function should fail with an exception if the result interval is invalid.
  • next_day: This function throws IllegalArgumentException if input is not a valid day of week.

SQL Operators

The behavior of some SQL operators can be different under ANSI mode (spark.sql.ansi.enabled=true).

  • array_col[index]: This operator throws ArrayIndexOutOfBoundsException if using invalid indices.

Useful Functions for ANSI Mode

When ANSI mode is on, it throws exceptions for invalid operations. You can use the following SQL functions to suppress such exceptions.

  • try_cast: identical to CAST, except that it returns NULL result instead of throwing an exception on runtime error.
  • try_add: identical to the add operator +, except that it returns NULL result instead of throwing an exception on integral value overflow.
  • try_subtract: identical to the add operator -, except that it returns NULL result instead of throwing an exception on integral value overflow.
  • try_multiply: identical to the add operator *, except that it returns NULL result instead of throwing an exception on integral value overflow.
  • try_divide: identical to the division operator /, except that it returns NULL result instead of throwing an exception on dividing 0.
  • try_sum: identical to the function sum, except that it returns NULL result instead of throwing an exception on integral/decimal/interval value overflow.
  • try_avg: identical to the function avg, except that it returns NULL result instead of throwing an exception on decimal/interval value overflow.
  • try_element_at: identical to the function element_at, except that it returns NULL result instead of throwing an exception on array's index out of bound.
  • try_to_timestamp: identical to the function to_timestamp, except that it returns NULL result instead of throwing an exception on string parsing error.

SQL Keywords (optional, disabled by default)

When both spark.sql.ansi.enabled and spark.sql.ansi.enforceReservedKeywords are true, Spark SQL will use the ANSI mode parser.

With the ANSI mode parser, Spark SQL has two kinds of keywords:

  • Non-reserved keywords: Keywords that have a special meaning only in particular contexts and can be used as identifiers in other contexts. For example, EXPLAIN SELECT ... is a command, but EXPLAIN can be used as identifiers in other places.
  • Reserved keywords: Keywords that are reserved and can't be used as identifiers for table, view, column, function, alias, etc.

With the default parser, Spark SQL has two kinds of keywords:

  • Non-reserved keywords: Same definition as the one when the ANSI mode enabled.
  • Strict-non-reserved keywords: A strict version of non-reserved keywords, which can not be used as table alias.

By default, spark.sql.ansi.enforceReservedKeywords is false.

Below is a list of all the keywords in Spark SQL.

KeywordSpark SQL
ANSI Mode		Spark SQL
Default Mode		SQL-2016
ADDnon-reservednon-reservednon-reserved
AFTERnon-reservednon-reservednon-reserved
ALLreservednon-reservedreserved
ALTERnon-reservednon-reservedreserved
ALWAYSnon-reservednon-reservednon-reserved
ANALYZEnon-reservednon-reservednon-reserved
ANDreservednon-reservedreserved
ANTInon-reservedstrict-non-reservednon-reserved
ANYreservednon-reservedreserved
ANY_VALUEnon-reservednon-reservednon-reserved
ARCHIVEnon-reservednon-reservednon-reserved
ARRAYnon-reservednon-reservedreserved
ASreservednon-reservedreserved
ASCnon-reservednon-reservednon-reserved
ATnon-reservednon-reservedreserved
AUTHORIZATIONreservednon-reservedreserved
BETWEENnon-reservednon-reservedreserved
BIGINTnon-reservednon-reservedreserved
BINARYnon-reservednon-reservedreserved
BOOLEANnon-reservednon-reservedreserved
BOTHreservednon-reservedreserved
BUCKETnon-reservednon-reservednon-reserved
BUCKETSnon-reservednon-reservednon-reserved
BYnon-reservednon-reservedreserved
BYTEnon-reservednon-reservednon-reserved
CACHEnon-reservednon-reservednon-reserved
CASCADEnon-reservednon-reservednon-reserved
CASEreservednon-reservedreserved
CASTreservednon-reservedreserved
CATALOGnon-reservednon-reservednon-reserved
CATALOGSnon-reservednon-reservednon-reserved
CHANGEnon-reservednon-reservednon-reserved
CHARnon-reservednon-reservedreserved
CHARACTERnon-reservednon-reservedreserved
CHECKreservednon-reservedreserved
CLEARnon-reservednon-reservednon-reserved
CLUSTERnon-reservednon-reservednon-reserved
CLUSTEREDnon-reservednon-reservednon-reserved
CODEGENnon-reservednon-reservednon-reserved
COLLATEreservednon-reservedreserved
COLLATIONreservednon-reservedreserved
COLLECTIONnon-reservednon-reservednon-reserved
COLUMNreservednon-reservedreserved
COLUMNSnon-reservednon-reservednon-reserved
COMMENTnon-reservednon-reservednon-reserved
COMMITnon-reservednon-reservedreserved
COMPACTnon-reservednon-reservednon-reserved
COMPACTIONSnon-reservednon-reservednon-reserved
COMPUTEnon-reservednon-reservednon-reserved
CONCATENATEnon-reservednon-reservednon-reserved
CONSTRAINTreservednon-reservedreserved
COSTnon-reservednon-reservednon-reserved
CREATEreservednon-reservedreserved
CROSSreservedstrict-non-reservedreserved
CUBEnon-reservednon-reservedreserved
CURRENTnon-reservednon-reservedreserved
CURRENT_DATEreservednon-reservedreserved
CURRENT_TIMEreservednon-reservedreserved
CURRENT_TIMESTAMPreservednon-reservedreserved
CURRENT_USERreservednon-reservedreserved
DATAnon-reservednon-reservednon-reserved
DATEnon-reservednon-reservedreserved
DATABASEnon-reservednon-reservednon-reserved
DATABASESnon-reservednon-reservednon-reserved
DATEADDnon-reservednon-reservednon-reserved
DATE_ADDnon-reservednon-reservednon-reserved
DATEDIFFnon-reservednon-reservednon-reserved
DATE_DIFFnon-reservednon-reservednon-reserved
DAYnon-reservednon-reservednon-reserved
DAYSnon-reservednon-reservednon-reserved
DAYOFYEARnon-reservednon-reservednon-reserved
DBPROPERTIESnon-reservednon-reservednon-reserved
DECnon-reservednon-reservedreserved
DECIMALnon-reservednon-reservedreserved
DECLAREnon-reservednon-reservednon-reserved
DEFAULTnon-reservednon-reservednon-reserved
DEFINEDnon-reservednon-reservednon-reserved
DELETEnon-reservednon-reservedreserved
DELIMITEDnon-reservednon-reservednon-reserved
DESCnon-reservednon-reservednon-reserved
DESCRIBEnon-reservednon-reservedreserved
DFSnon-reservednon-reservednon-reserved
DIRECTORIESnon-reservednon-reservednon-reserved
DIRECTORYnon-reservednon-reservednon-reserved
DISTINCTreservednon-reservedreserved
DISTRIBUTEnon-reservednon-reservednon-reserved
DIVnon-reservednon-reservednot a keyword
DOUBLEnon-reservednon-reservedreserved
DROPnon-reservednon-reservedreserved
ELSEreservednon-reservedreserved
ENDreservednon-reservedreserved
ESCAPEreservednon-reservedreserved
ESCAPEDnon-reservednon-reservednon-reserved
EVOLUTIONnon-reservednon-reservednon-reserved
EXCEPTreservedstrict-non-reservedreserved
EXCHANGEnon-reservednon-reservednon-reserved
EXCLUDEnon-reservednon-reservednon-reserved
EXECUTEreservednon-reservedreserved
EXISTSnon-reservednon-reservedreserved
EXPLAINnon-reservednon-reservednon-reserved
EXPORTnon-reservednon-reservednon-reserved
EXTENDEDnon-reservednon-reservednon-reserved
EXTERNALnon-reservednon-reservedreserved
EXTRACTnon-reservednon-reservedreserved
FALSEreservednon-reservedreserved
FETCHreservednon-reservedreserved
FIELDSnon-reservednon-reservednon-reserved
FILTERreservednon-reservedreserved
FILEFORMATnon-reservednon-reservednon-reserved
FIRSTnon-reservednon-reservednon-reserved
FLOATnon-reservednon-reservedreserved
FOLLOWINGnon-reservednon-reservednon-reserved
FORreservednon-reservedreserved
FOREIGNreservednon-reservedreserved
FORMATnon-reservednon-reservednon-reserved
FORMATTEDnon-reservednon-reservednon-reserved
FROMreservednon-reservedreserved
FULLreservedstrict-non-reservedreserved
FUNCTIONnon-reservednon-reservedreserved
FUNCTIONSnon-reservednon-reservednon-reserved
GENERATEDnon-reservednon-reservednon-reserved
GLOBALnon-reservednon-reservedreserved
GRANTreservednon-reservedreserved
GROUPreservednon-reservedreserved
GROUPINGnon-reservednon-reservedreserved
HAVINGreservednon-reservedreserved
HOURnon-reservednon-reservednon-reserved
HOURSnon-reservednon-reservednon-reserved
IDENTIFIERnon-reservednon-reservednon-reserved
IFnon-reservednon-reservednot a keyword
IGNOREnon-reservednon-reservednon-reserved
IMMEDIATEnon-reservednon-reservednon-reserved
IMPORTnon-reservednon-reservednon-reserved
INreservednon-reservedreserved
INCLUDEnon-reservednon-reservednon-reserved
INDEXnon-reservednon-reservednon-reserved
INDEXESnon-reservednon-reservednon-reserved
INNERreservedstrict-non-reservedreserved
INPATHnon-reservednon-reservednon-reserved
INPUTFORMATnon-reservednon-reservednon-reserved
INSERTnon-reservednon-reservedreserved
INTnon-reservednon-reservedreserved
INTEGERnon-reservednon-reservedreserved
INTERSECTreservedstrict-non-reservedreserved
INTERVALnon-reservednon-reservedreserved
INTOreservednon-reservedreserved
ISreservednon-reservedreserved
ITEMSnon-reservednon-reservednon-reserved
JOINreservedstrict-non-reservedreserved
KEYSnon-reservednon-reservednon-reserved
LASTnon-reservednon-reservednon-reserved
LATERALreservedstrict-non-reservedreserved
LAZYnon-reservednon-reservednon-reserved
LEADINGreservednon-reservedreserved
LEFTreservedstrict-non-reservedreserved
LIKEnon-reservednon-reservedreserved
ILIKEnon-reservednon-reservednon-reserved
LIMITnon-reservednon-reservednon-reserved
LINESnon-reservednon-reservednon-reserved
LISTnon-reservednon-reservednon-reserved
LOADnon-reservednon-reservednon-reserved
LOCALnon-reservednon-reservedreserved
LOCATIONnon-reservednon-reservednon-reserved
LOCKnon-reservednon-reservednon-reserved
LOCKSnon-reservednon-reservednon-reserved
LOGICALnon-reservednon-reservednon-reserved
LONGnon-reservednon-reservednon-reserved
MACROnon-reservednon-reservednon-reserved
MAPnon-reservednon-reservednon-reserved
MATCHEDnon-reservednon-reservednon-reserved
MERGEnon-reservednon-reservednon-reserved
MICROSECONDnon-reservednon-reservednon-reserved
MICROSECONDSnon-reservednon-reservednon-reserved
MILLISECONDnon-reservednon-reservednon-reserved
MILLISECONDSnon-reservednon-reservednon-reserved
MINUTEnon-reservednon-reservednon-reserved
MINUTESnon-reservednon-reservednon-reserved
MINUSnon-reservedstrict-non-reservednon-reserved
MONTHnon-reservednon-reservednon-reserved
MONTHSnon-reservednon-reservednon-reserved
MSCKnon-reservednon-reservednon-reserved
NAMEnon-reservednon-reservednon-reserved
NAMESPACEnon-reservednon-reservednon-reserved
NAMESPACESnon-reservednon-reservednon-reserved
NANOSECONDnon-reservednon-reservednon-reserved
NANOSECONDSnon-reservednon-reservednon-reserved
NATURALreservedstrict-non-reservedreserved
NOnon-reservednon-reservedreserved
NOTreservednon-reservedreserved
NULLreservednon-reservedreserved
NULLSnon-reservednon-reservednon-reserved
NUMERICnon-reservednon-reservednon-reserved
OFnon-reservednon-reservedreserved
OFFSETreservednon-reservedreserved
ONreservedstrict-non-reservedreserved
ONLYreservednon-reservedreserved
OPTIONnon-reservednon-reservednon-reserved
OPTIONSnon-reservednon-reservednon-reserved
ORreservednon-reservedreserved
ORDERreservednon-reservedreserved
OUTnon-reservednon-reservedreserved
OUTERreservednon-reservedreserved
OUTPUTFORMATnon-reservednon-reservednon-reserved
OVERnon-reservednon-reservednon-reserved
OVERLAPSreservednon-reservedreserved
OVERLAYnon-reservednon-reservednon-reserved
OVERWRITEnon-reservednon-reservednon-reserved
PARTITIONnon-reservednon-reservedreserved
PARTITIONEDnon-reservednon-reservednon-reserved
PARTITIONSnon-reservednon-reservednon-reserved
PERCENTnon-reservednon-reservednon-reserved
PIVOTnon-reservednon-reservednon-reserved
PLACINGnon-reservednon-reservednon-reserved
POSITIONnon-reservednon-reservedreserved
PRECEDINGnon-reservednon-reservednon-reserved
PRIMARYreservednon-reservedreserved
PRINCIPALSnon-reservednon-reservednon-reserved
PROPERTIESnon-reservednon-reservednon-reserved
PURGEnon-reservednon-reservednon-reserved
QUARTERnon-reservednon-reservednon-reserved
QUERYnon-reservednon-reservednon-reserved
RANGEnon-reservednon-reservedreserved
REALnon-reservednon-reservedreserved
RECORDREADERnon-reservednon-reservednon-reserved
RECORDWRITERnon-reservednon-reservednon-reserved
RECOVERnon-reservednon-reservednon-reserved
REDUCEnon-reservednon-reservednon-reserved
REFERENCESreservednon-reservedreserved
REFRESHnon-reservednon-reservednon-reserved
REGEXPnon-reservednon-reservednot a keyword
RENAMEnon-reservednon-reservednon-reserved
REPAIRnon-reservednon-reservednon-reserved
REPEATABLEnon-reservednon-reservednon-reserved
REPLACEnon-reservednon-reservednon-reserved
RESETnon-reservednon-reservednon-reserved
RESPECTnon-reservednon-reservednon-reserved
RESTRICTnon-reservednon-reservednon-reserved
REVOKEnon-reservednon-reservedreserved
RIGHTreservedstrict-non-reservedreserved
RLIKEnon-reservednon-reservednon-reserved
ROLEnon-reservednon-reservednon-reserved
ROLESnon-reservednon-reservednon-reserved
ROLLBACKnon-reservednon-reservedreserved
ROLLUPnon-reservednon-reservedreserved
ROWnon-reservednon-reservedreserved
ROWSnon-reservednon-reservedreserved
SCHEMAnon-reservednon-reservednon-reserved
SCHEMASnon-reservednon-reservednon-reserved
SECONDnon-reservednon-reservednon-reserved
SECONDSnon-reservednon-reservednon-reserved
SELECTreservednon-reservedreserved
SEMInon-reservedstrict-non-reservednon-reserved
SEPARATEDnon-reservednon-reservednon-reserved
SERDEnon-reservednon-reservednon-reserved
SERDEPROPERTIESnon-reservednon-reservednon-reserved
SESSION_USERreservednon-reservedreserved
SETnon-reservednon-reservedreserved
SETSnon-reservednon-reservednon-reserved
SHORTnon-reservednon-reservednon-reserved
SHOWnon-reservednon-reservednon-reserved
SINGLEnon-reservednon-reservednon-reserved
SKEWEDnon-reservednon-reservednon-reserved
SMALLINTnon-reservednon-reservedreserved
SOMEreservednon-reservedreserved
SORTnon-reservednon-reservednon-reserved
SORTEDnon-reservednon-reservednon-reserved
SOURCEnon-reservednon-reservednon-reserved
STARTnon-reservednon-reservedreserved
STATISTICSnon-reservednon-reservednon-reserved
STOREDnon-reservednon-reservednon-reserved
STRATIFYnon-reservednon-reservednon-reserved
STRINGnon-reservednon-reservednon-reserved
STRUCTnon-reservednon-reservednon-reserved
SUBSTRnon-reservednon-reservednon-reserved
SUBSTRINGnon-reservednon-reservednon-reserved
SYNCnon-reservednon-reservednon-reserved
SYSTEM_TIMEnon-reservednon-reservednon-reserved
SYSTEM_VERSIONnon-reservednon-reservednon-reserved
TABLEreservednon-reservedreserved
TABLESnon-reservednon-reservednon-reserved
TABLESAMPLEnon-reservednon-reservedreserved
TARGETnon-reservednon-reservednon-reserved
TBLPROPERTIESnon-reservednon-reservednon-reserved
TEMPnon-reservednon-reservednot a keyword
TEMPORARYnon-reservednon-reservednon-reserved
TERMINATEDnon-reservednon-reservednon-reserved
THENreservednon-reservedreserved
TIMEreservednon-reservedreserved
TIMEDIFFnon-reservednon-reservednon-reserved
TIMESTAMPnon-reservednon-reservednon-reserved
TIMESTAMP_LTZnon-reservednon-reservednon-reserved
TIMESTAMP_NTZnon-reservednon-reservednon-reserved
TIMESTAMPADDnon-reservednon-reservednon-reserved
TIMESTAMPDIFFnon-reservednon-reservednon-reserved
TINYINTnon-reservednon-reservednon-reserved
TOreservednon-reservedreserved
TOUCHnon-reservednon-reservednon-reserved
TRAILINGreservednon-reservedreserved
TRANSACTIONnon-reservednon-reservednon-reserved
TRANSACTIONSnon-reservednon-reservednon-reserved
TRANSFORMnon-reservednon-reservednon-reserved
TRIMnon-reservednon-reservednon-reserved
TRUEnon-reservednon-reservedreserved
TRUNCATEnon-reservednon-reservedreserved
TRY_CASTnon-reservednon-reservednon-reserved
TYPEnon-reservednon-reservednon-reserved
UNARCHIVEnon-reservednon-reservednon-reserved
UNBOUNDEDnon-reservednon-reservednon-reserved
UNCACHEnon-reservednon-reservednon-reserved
UNIONreservedstrict-non-reservedreserved
UNIQUEreservednon-reservedreserved
UNKNOWNreservednon-reservedreserved
UNLOCKnon-reservednon-reservednon-reserved
UNPIVOTnon-reservednon-reservednon-reserved
UNSETnon-reservednon-reservednon-reserved
UPDATEnon-reservednon-reservedreserved
USEnon-reservednon-reservednon-reserved
USERreservednon-reservedreserved
USINGreservedstrict-non-reservedreserved
VALUESnon-reservednon-reservedreserved
VARCHARnon-reservednon-reservedreserved
VARnon-reservednon-reservednon-reserved
VARIABLEnon-reservednon-reservednon-reserved
VARIANTnon-reservednon-reservedreserved
VERSIONnon-reservednon-reservednon-reserved
VIEWnon-reservednon-reservednon-reserved
VIEWSnon-reservednon-reservednon-reserved
VOIDnon-reservednon-reservednon-reserved
WEEKnon-reservednon-reservednon-reserved
WEEKSnon-reservednon-reservednon-reserved
WHENreservednon-reservedreserved
WHEREreservednon-reservedreserved
WINDOWnon-reservednon-reservedreserved
WITHreservednon-reservedreserved
WITHINreservednon-reservedreserved
Xnon-reservednon-reservednon-reserved
YEARnon-reservednon-reservednon-reserved
YEARSnon-reservednon-reservednon-reserved
ZONEnon-reservednon-reservednon-reserved