Supported Operators and Expressions
This section describes the support scope, restrictions, and usage rules for SQL operators and expressions (including data types) when the OmniOperator feature is used with SparkExtension.
Table 2, Table 3, and Table 4 describe the operators, expressions, and functions supported by OmniOperator on SparkExtension. Symbols are used to indicate whether the operators and expressions are supported. For details about the meanings of the symbols, see Table 1.
- This section uses Spark 3.3.1 as an example to describe the operators and expressions supported by OmniOperator.
- Operator List and Expression List list only the data types supported by OmniOperator. Other data types (BYTE/FLOAT/BINARY/ARRAY/MAP/STRUCT/CALENDAR/UDT) are not supported.
- If you use operators and expressions that are not supported by OmniOperator, the execution plan will be rolled back to open source execution, which deteriorates the performance.
Status |
Description |
|---|---|
S |
Indicates that the operator or expression is supported. |
PS |
Indicates that the operator or expression is partially supported, with some restrictions. For details about the restrictions, see Constraints. |
NS |
Indicates that the operator or expression is not supported. |
NA |
Indicates that the operator or expression is not involved. This scenario does not exist in open source Spark. |
NA-2 |
Indicates a context function implemented based on open source Spark, which does not involve using OmniOperator. |
[Blank Cell] |
Indicates a scenario that is irrelevant or needs to be confirmed. |
Operator List
Table 2 lists the operators supported by OmniOperator for the Spark engine.
Open Source Operator |
OmniOperator Operator |
BOOLEAN |
INT |
LONG |
DOUBLE |
STRING |
CHAR |
VARCHAR |
DATE |
DECIMAL |
SHORT |
TIMESTAMP |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
FileSourceScanExec |
ColumnarFileSourceScanExec |
S |
S |
S |
S |
S |
S |
S |
S |
S |
S |
S |
ProjectExec |
ColumnarProjectExec |
S |
S |
S |
S |
S |
S |
S |
S |
S |
NS |
S |
FilterExec |
ColumnarFilterExec |
S |
S |
S |
S |
S |
S |
S |
S |
S |
NS |
S |
ProjectExec+FilterExec |
ColumnarConditionProjectExec |
S |
S |
S |
S |
S |
S |
S |
S |
S |
NS |
S |
ExpandExec |
ColumnarExpandExec |
S |
S |
S |
S |
S |
S |
S |
S |
S |
NS |
S |
HashAggregateExec |
ColumnarHashAggregateExec |
S |
S |
S |
S |
S |
S |
S |
S |
S |
S |
S |
TopNSortExec |
ColumnarTopNSortExec |
S |
S |
S |
S |
S |
S |
S |
S |
S |
NS |
S |
SortExec |
ColumnarSortExec |
S |
S |
S |
S |
S |
S |
S |
S |
S |
S |
S |
BroadcastExchangeExec |
ColumnarBroadcastExchangeExec |
S |
S |
S |
S |
S |
S |
S |
S |
S |
S |
S |
TakeOrderedAndProjectExec |
ColumnarTakeOrderedAndProjectExec |
S |
S |
S |
S |
S |
S |
S |
S |
S |
S |
S |
UnionExec |
ColumnarUnionExec |
S |
S |
S |
S |
S |
S |
S |
S |
S |
S |
S |
ShuffleExchangeExec |
ColumnarShuffleExchangeExec |
S |
S |
S |
S |
S |
S |
S |
S |
S |
S |
S |
BroadcastHashJoinExec |
ColumnarBroadcastHashJoinExec |
S |
S |
S |
S |
S |
S |
S |
S |
S |
S |
S |
SortMergeJoinExec |
ColumnarSortMergeJoinExec |
S |
S |
S |
S |
S |
S |
S |
S |
S |
S |
S |
WindowExec |
ColumnarWindowExec |
S |
S |
S |
S |
S |
S |
S |
S |
S |
S |
S |
ShuffledHashJoinExec |
ColumnarShuffledHashJoinExec |
S |
S |
S |
S |
S |
S |
S |
S |
S |
S |
S |
LocalLimitExec |
ColumnarLocalLimitExec |
S |
S |
S |
S |
S |
S |
S |
S |
S |
S |
S |
GlobalLimitExec |
ColumnarGlobalLimitExec |
S |
S |
S |
S |
S |
S |
S |
S |
S |
S |
S |
CoalesceExec |
ColumnarCoalesceExec |
S |
S |
S |
S |
S |
S |
S |
S |
S |
S |
S |
SubqueryBroadcastExec |
OmniColumnarSubqueryBroadcastExec |
S |
S |
S |
S |
S |
S |
S |
S |
S |
S |
S |
AQEShuffleReadExec |
OmniAQEShuffleReadExec |
S |
S |
S |
S |
S |
S |
S |
S |
S |
S |
S |
WindowGroupLimitExec |
ColumnarWindowGroupLimitExec |
S |
S |
S |
S |
S |
S |
S |
S |
S |
NS |
S |
Expression List
Table 3 lists the expressions and functions supported by OmniOperator for the Spark engine.
Expression |
Supported by OmniOperator |
Function Type |
Restriction |
BOOLEAN |
INT |
LONG |
DOUBLE |
STRING |
CHAR |
VARCHAR |
DATE |
DECIMAL |
NULL |
SHORT |
TIMESTAMP |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
! |
S |
Scalar Functions |
- |
S |
NA |
NA |
NA |
NA |
NA |
NA |
NA |
NA |
S |
NA |
NA |
% |
S |
Scalar Functions |
- |
NA |
S |
S |
S |
S |
S |
S |
NA |
S |
S |
NS |
NA |
* |
S |
Scalar Functions |
- |
NA |
S |
S |
S |
S |
S |
S |
NA |
S |
S |
NS |
NA |
+ |
S |
Scalar Functions |
- |
NA |
S |
S |
S |
S |
S |
S |
NA |
S |
S |
NS |
NA |
- |
S |
Scalar Functions |
- |
NA |
S |
S |
S |
S |
S |
S |
NA |
S |
S |
NS |
NA |
/ |
S |
Scalar Functions |
- |
NA |
S |
S |
S |
S |
S |
S |
NA |
S |
S |
NS |
NA |
< |
S |
Scalar Functions |
- |
NS |
S |
S |
S |
S |
S |
S |
S |
S |
S |
NS |
S |
<= |
S |
Scalar Functions |
- |
NS |
S |
S |
S |
S |
S |
S |
S |
S |
S |
NS |
S |
> |
S |
Scalar Functions |
- |
NS |
S |
S |
S |
S |
S |
S |
S |
S |
S |
NS |
S |
>= |
S |
Scalar Functions |
- |
NS |
S |
S |
S |
S |
S |
S |
S |
S |
S |
NS |
S |
and |
S |
Scalar Functions |
- |
S |
NA |
NA |
NA |
NA |
NA |
NA |
NA |
NA |
S |
NA |
NA |
any |
S |
Aggregate Functions |
- |
S |
NA |
NA |
NA |
NA |
NA |
NA |
NA |
NA |
S |
NA |
NA |
avg |
S |
Aggregate Functions |
- |
NA |
S |
S |
S |
S |
S |
S |
NA |
S |
S |
S |
NA |
between |
S |
Scalar Functions |
- |
NS |
S |
S |
S |
S |
S |
S |
S |
S |
S |
NS |
S |
bool_and |
S |
Aggregate Functions |
- |
S |
NA |
NA |
NA |
NA |
NA |
NA |
NA |
NA |
S |
NA |
NA |
bool_or |
S |
Aggregate Functions |
- |
S |
NA |
NA |
NA |
NA |
NA |
NA |
NA |
NA |
S |
NA |
NA |
case |
S |
Scalar Functions |
- |
NS |
S |
S |
S |
S |
S |
S |
S |
S |
S |
NS |
S |
cast |
S |
Scalar Functions |
See Table 4. |
[Blank Cell] |
[Blank Cell] |
[Blank Cell] |
[Blank Cell] |
[Blank Cell] |
[Blank Cell] |
[Blank Cell] |
[Blank Cell] |
[Blank Cell] |
[Blank Cell] |
[Blank Cell] |
[Blank Cell] |
char_length |
S |
Scalar Functions |
- |
NS |
S |
S |
S |
S |
S |
S |
S |
S |
S |
NS |
NS |
character_length |
S |
Scalar Functions |
- |
NS |
S |
S |
S |
S |
S |
S |
S |
S |
S |
NS |
NS |
count |
PS |
Aggregate Functions |
Only one input parameter is allowed. |
S |
S |
S |
S |
S |
S |
S |
S |
S |
S |
S |
S |
count_if |
S |
Aggregate Functions |
- |
S |
NA |
NA |
NA |
NA |
NA |
NA |
NA |
NA |
S |
NA |
NA |
current_catalog |
NA-2 |
Scalar Functions |
- |
[Blank Cell] |
[Blank Cell] |
[Blank Cell] |
[Blank Cell] |
[Blank Cell] |
[Blank Cell] |
[Blank Cell] |
[Blank Cell] |
[Blank Cell] |
[Blank Cell] |
[Blank Cell] |
[Blank Cell] |
current_database |
NA-2 |
Scalar Functions |
- |
[Blank Cell] |
[Blank Cell] |
[Blank Cell] |
[Blank Cell] |
[Blank Cell] |
[Blank Cell] |
[Blank Cell] |
[Blank Cell] |
[Blank Cell] |
[Blank Cell] |
[Blank Cell] |
[Blank Cell] |
current_date |
NA-2 |
Scalar Functions |
- |
[Blank Cell] |
[Blank Cell] |
[Blank Cell] |
[Blank Cell] |
[Blank Cell] |
[Blank Cell] |
[Blank Cell] |
[Blank Cell] |
[Blank Cell] |
[Blank Cell] |
[Blank Cell] |
[Blank Cell] |
current_timezone |
NA-2 |
Scalar Functions |
- |
[Blank Cell] |
[Blank Cell] |
[Blank Cell] |
[Blank Cell] |
[Blank Cell] |
[Blank Cell] |
[Blank Cell] |
[Blank Cell] |
[Blank Cell] |
[Blank Cell] |
[Blank Cell] |
[Blank Cell] |
current_user |
NA-2 |
Scalar Functions |
- |
[Blank Cell] |
[Blank Cell] |
[Blank Cell] |
[Blank Cell] |
[Blank Cell] |
[Blank Cell] |
[Blank Cell] |
[Blank Cell] |
[Blank Cell] |
[Blank Cell] |
[Blank Cell] |
[Blank Cell] |
every |
S |
Aggregate Functions |
- |
S |
NA |
NA |
NA |
NA |
NA |
NA |
NA |
NA |
S |
NA |
NA |
first |
S |
Aggregate Functions |
- |
S |
S |
S |
S |
NS |
NS |
NS |
S |
S |
NS |
S |
S |
first_value |
S |
Aggregate Functions |
- |
S |
S |
S |
S |
NS |
NS |
NS |
S |
S |
NS |
S |
S |
grouping_id |
S |
Aggregate Functions |
- |
NA |
NA |
NA |
NA |
NA |
NA |
NA |
NA |
NA |
NA |
NA |
NA |
if |
S |
Scalar Functions |
- |
S |
S |
S |
S |
S |
S |
S |
S |
S |
S |
NS |
S |
instr |
S |
Scalar Functions |
- |
NS |
S |
S |
S |
S |
S |
S |
S |
S |
S |
NS |
NS |
isnotnull |
S |
Scalar Functions |
- |
S |
S |
S |
S |
S |
S |
S |
S |
S |
S |
NS |
S |
isnull |
S |
Scalar Functions |
- |
S |
S |
S |
S |
S |
S |
S |
S |
S |
S |
NS |
S |
lcase |
S |
Scalar Functions |
- |
NS |
S |
S |
S |
S |
S |
S |
S |
S |
S |
NS |
NS |
left |
S |
Scalar Functions |
- |
NS |
S |
S |
S |
S |
S |
S |
S |
S |
S |
NS |
NS |
length |
S |
Scalar Functions |
- |
NS |
S |
S |
S |
S |
S |
S |
S |
S |
S |
NS |
NS |
lower |
S |
Scalar Functions |
- |
NS |
S |
S |
S |
S |
S |
S |
S |
S |
S |
NS |
NS |
max |
S |
Aggregate Functions |
- |
S |
S |
S |
S |
NS |
NS |
NS |
S |
S |
NS |
S |
S |
md5 |
PS |
Scalar Functions |
The input parameter must be a variable of the String type. |
NA |
NA |
NA |
NA |
S |
S |
S |
NA |
NA |
S |
NA |
NA |
mean |
S |
Aggregate Functions |
- |
NA |
S |
S |
S |
S |
S |
S |
NA |
S |
S |
S |
NA |
min |
S |
Aggregate Functions |
- |
S |
S |
S |
S |
NS |
NS |
NS |
S |
S |
NS |
S |
S |
mod |
S |
Scalar Functions |
- |
NA |
S |
S |
S |
S |
S |
S |
NA |
S |
S |
NS |
NA |
not |
S |
Scalar Functions |
- |
S |
NA |
NA |
NA |
NA |
NA |
NA |
NA |
NA |
S |
NA |
NA |
nullif |
S |
Scalar Functions |
- |
NS |
S |
S |
S |
S |
S |
S |
S |
S |
S |
NS |
S |
nvl2 |
S |
Scalar Functions |
- |
S |
S |
S |
S |
S |
S |
S |
S |
S |
S |
NS |
S |
or |
S |
Scalar Functions |
- |
S |
NA |
NA |
NA |
NA |
NA |
NA |
NA |
NA |
S |
NA |
NA |
positive |
S |
Scalar Functions |
- |
NA |
S |
S |
S |
S |
S |
S |
NA |
S |
S |
NS |
NA |
rank |
S |
Window Functions |
No input parameter is involved. |
NA |
NA |
NA |
NA |
NA |
NA |
NA |
NA |
NA |
NA |
NA |
NA |
replace |
S |
Scalar Functions |
- |
NS |
S |
S |
S |
S |
S |
S |
S |
S |
S |
NS |
NS |
round |
S |
Scalar Functions |
- |
NA |
S |
S |
S |
S |
S |
S |
NA |
S |
S |
NS |
NA |
row_number |
S |
Window Functions |
No input parameter is involved. |
NA |
NA |
NA |
NA |
NA |
NA |
NA |
NA |
NA |
NA |
NA |
NA |
some |
S |
Aggregate Functions |
- |
S |
NA |
NA |
NA |
NA |
NA |
NA |
NA |
NA |
S |
NA |
NA |
substr |
S |
Scalar Functions |
- |
NA |
S |
S |
S |
S |
S |
S |
S |
S |
S |
NS |
NS |
substring |
S |
Scalar Functions |
- |
NA |
S |
S |
S |
S |
S |
S |
S |
S |
S |
NS |
NS |
trunc |
S |
Scalar Functions |
- |
NA |
NA |
NA |
NA |
S |
S |
S |
S |
NA |
S |
NA |
NS |
ucase |
S |
Scalar Functions |
- |
NS |
S |
S |
S |
S |
S |
S |
S |
S |
S |
NS |
NS |
upper |
S |
Scalar Functions |
- |
NS |
S |
S |
S |
S |
S |
S |
S |
S |
S |
NS |
NS |
when |
S |
Scalar Functions |
- |
NS |
S |
S |
S |
S |
S |
S |
S |
S |
S |
NS |
NS |
!= |
S |
Scalar Functions |
- |
NS |
S |
S |
S |
S |
S |
S |
S |
S |
S |
NS |
S |
<> |
S |
Scalar Functions |
- |
NS |
S |
S |
S |
S |
S |
S |
S |
S |
S |
NS |
S |
= |
S |
Scalar Functions |
- |
NS |
S |
S |
S |
S |
S |
S |
S |
S |
S |
NS |
S |
== |
S |
Scalar Functions |
- |
NS |
S |
S |
S |
S |
S |
S |
S |
S |
S |
NS |
S |
abs |
S |
Scalar Functions |
- |
NA |
S |
S |
S |
S |
S |
S |
NA |
S |
S |
NS |
NA |
concat |
S |
Scalar Functions |
- |
NS |
S |
S |
S |
S |
S |
S |
S |
S |
S |
NS |
NS |
contains |
S |
Scalar Functions |
- |
NS |
S |
S |
S |
S |
S |
S |
S |
S |
S |
NS |
NS |
decode |
PS |
Scalar Functions |
The number of input parameters must be greater than 2. |
NS |
S |
S |
S |
S |
S |
S |
S |
S |
S |
NS |
NS |
endswith |
PS |
Scalar Functions |
The second input parameter must be a constant of the String type. |
NS |
S |
S |
S |
S |
S |
S |
S |
S |
S |
NS |
NS |
hash |
S |
Scalar Functions |
- |
S |
S |
S |
S |
S |
S |
S |
S |
S |
S |
NS |
S |
ifnull |
S |
Scalar Functions |
- |
S |
S |
S |
S |
S |
S |
S |
S |
S |
S |
NS |
S |
in |
S |
Scalar Functions |
- |
NS |
S |
S |
S |
S |
S |
S |
S |
S |
S |
NS |
S |
like |
PS |
Scalar Functions |
The second input parameter must be a constant of the String type, and the constant cannot contain underscores (_) or more than one percent sign (%). |
NS |
S |
S |
S |
S |
S |
S |
S |
S |
S |
NS |
NS |
nvl |
S |
Scalar Functions |
- |
S |
S |
S |
S |
S |
S |
S |
S |
S |
S |
NS |
S |
regexp |
PS |
Scalar Functions |
The second input parameter must be a constant of the String type, that is, '^\\d+$' |
NS |
S |
S |
S |
S |
S |
S |
S |
S |
S |
NS |
NS |
regexp_like |
PS |
Scalar Functions |
The second input parameter must be a constant of the String type, that is, '^\\d+$' |
NS |
S |
S |
S |
S |
S |
S |
S |
S |
S |
NS |
NS |
regr_avgx |
S |
Aggregate Functions |
- |
NA |
S |
S |
S |
S |
S |
S |
NA |
S |
S |
NS |
NA |
regr_avgy |
S |
Aggregate Functions |
- |
NA |
S |
S |
S |
S |
S |
S |
NA |
S |
S |
NS |
NA |
right |
S |
Scalar Functions |
- |
NS |
S |
S |
S |
S |
S |
S |
S |
S |
S |
NS |
NS |
rlike |
PS |
Scalar Functions |
The second input parameter must be a constant of the String type, that is, '^\\d+$' |
NS |
S |
S |
S |
S |
S |
S |
S |
S |
S |
NS |
NS |
startswith |
PS |
Scalar Functions |
The second input parameter must be a constant of the String type. |
NS |
S |
S |
S |
S |
S |
S |
S |
S |
S |
NS |
NS |
sum |
S |
Aggregate Functions |
- |
NA |
S |
S |
S |
S |
S |
S |
NA |
S |
S |
S |
NA |
to_date |
PS |
Scalar Functions |
Only one input parameter is allowed. |
NS |
S |
S |
S |
S |
S |
S |
S |
S |
S |
NS |
NS |
xxhash64 |
S |
Scalar Functions |
- |
S |
S |
S |
S |
S |
S |
S |
S |
S |
S |
NS |
S |
|| |
S |
Scalar Functions |
- |
NS |
S |
S |
S |
S |
S |
S |
S |
S |
S |
NS |
NS |
bigint |
S |
Scalar Functions |
- |
NS |
S |
S |
S |
S |
S |
S |
NS |
S |
S |
NS |
NS |
boolean |
S |
Scalar Functions |
- |
S |
NS |
NS |
NS |
NS |
NS |
NS |
NS |
NS |
S |
NS |
NS |
date |
S |
Scalar Functions |
- |
NA |
NA |
NA |
NA |
S |
S |
S |
S |
NA |
S |
NA |
NS |
decimal |
S |
Scalar Functions |
- |
NS |
S |
S |
S |
S |
S |
S |
NS |
S |
S |
NS |
NS |
double |
S |
Scalar Functions |
- |
NS |
S |
S |
S |
S |
S |
S |
NS |
S |
S |
NS |
NS |
int |
S |
Scalar Functions |
- |
NS |
S |
S |
S |
S |
S |
S |
NS |
S |
S |
NS |
NS |
string |
S |
Scalar Functions |
- |
NS |
S |
S |
S |
S |
S |
S |
S |
S |
S |
NS |
NS |
coalesce |
PS |
Scalar Functions |
Two input parameters are used. |
S |
S |
S |
S |
S |
S |
S |
S |
S |
S |
NS |
S |
from_unixtime |
PS |
Scalar Functions |
Only yyyy-MM-dd and yyyy-MM-dd HH:mm:ss are supported, and the time zone must be GMT+08:00, Asia/Beijing, or Asia/Shanghai. |
NA |
S |
S |
S |
S |
S |
S |
NA |
S |
S |
NS |
NA |
greatest |
PS |
Scalar Functions |
Two input parameters are used. |
S |
S |
S |
S |
S |
S |
S |
NS |
S |
S |
NS |
NS |
unix_timestamp |
PS |
Scalar Functions |
timeExp must be String/Date. Only yyyy-MM-dd and yyyy-MM-dd HH:mm:ss are supported, and the time zone must be GMT+08:00, Asia/Beijing, or Asia/Shanghai. |
NA |
NA |
NA |
NA |
S |
S |
S |
S |
NA |
S |
NA |
NS |
try_add |
S |
Scalar Functions |
- |
NA |
S |
S |
S |
S |
S |
S |
NA |
S |
S |
NS |
NA |
try_divide |
S |
Scalar Functions |
- |
NA |
S |
S |
S |
S |
S |
S |
NA |
S |
S |
NS |
NA |
try_multiply |
S |
Scalar Functions |
- |
NA |
S |
S |
S |
S |
S |
S |
NA |
S |
S |
NS |
NA |
try_subtract |
S |
Scalar Functions |
- |
NA |
S |
S |
S |
S |
S |
S |
NA |
S |
S |
NS |
NA |
try_avg |
S |
Aggregate Functions |
- |
NA |
S |
S |
S |
S |
S |
S |
NA |
S |
S |
S |
NA |
try_sum |
S |
Aggregate Functions |
- |
NA |
S |
S |
S |
S |
S |
S |
NA |
S |
S |
S |
NA |
Source/Target Type |
BOOLEAN |
INT |
LONG |
DOUBLE |
STRING |
CHAR |
VARCHAR |
DATE |
DECIMAL |
SHORT |
TIMESTAMP |
|---|---|---|---|---|---|---|---|---|---|---|---|
BOOLEAN |
S |
NS |
NS |
NS |
NS |
NS |
NS |
NA |
NS |
NS |
NS |
INT |
NS |
S |
S |
S |
S |
S |
S |
NA |
S |
NS |
NS |
LONG |
NS |
S |
S |
S |
S |
S |
S |
NA |
S |
NS |
NS |
DOUBLE |
NS |
S |
S |
S |
S |
S |
S |
NA |
S |
NS |
NS |
STRING |
NS |
S |
S |
S |
S |
S |
S |
S |
S |
NS |
NS |
CHAR |
NS |
S |
S |
S |
S |
S |
S |
S |
S |
NS |
NS |
VARCHAR |
NS |
S |
S |
S |
S |
S |
S |
S |
S |
NS |
NS |
DATE |
NS |
NS |
NS |
NS |
S |
S |
S |
S |
NS |
NS |
NS |
DECIMAL |
NS |
S |
S |
S |
S |
S |
S |
NA |
S |
NS |
NS |
NULL |
S |
S |
S |
S |
S |
S |
S |
S |
S |
NS |
NS |
SHORT |
NS |
NS |
NS |
NS |
NS |
NS |
NS |
NS |
NS |
NS |
NS |
TIMESTAMP |
NS |
NS |
NS |
NS |
NS |
NS |
NS |
NS |
NS |
NS |
NS |