Supported Operators and Expressions
This section describes the operators and expressions supported when Spark uses OmniOperator, and the data types of the supported operators and expressions.
- 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.
The lists of operators and expressions use different symbols to indicate support states. See Table 1.
Status |
Description |
|---|---|
S |
Fully supported by OmniOperator. |
PS |
Supported by OmniOperator when certain conditions are met. |
NS |
Not supported by OmniOperator. |
NA |
Not applicable. No such input scenarios exist even on the open source Spark engine. |
NA-2 |
Spark context function implemented inside Spark. OmniOperator is not used. |
[Blank Cell] |
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 |