List of Expressions for Pushdown

When TiDB reads data from TiKV, TiDB tries to push down some expressions (including calculations of functions or operators) to be processed to TiKV. This reduces the amount of transferred data and offloads processing from a single TiDB node. This document introduces the expressions that TiDB already supports pushing down and how to prohibit specific expressions from being pushed down using blocklist.

TiFlash also supports pushdown for the functions and operators listed on this page.

Supported expressions for pushdown to TiKV

Expression TypeOperations
Logical operatorsAND (&&)
OR (||)
NOT (!)
XOR
Bit operators&
~
|
^
<<
>>
Comparison functions and operators<
<=
=
!= (<>)
>
>=
<=>
BETWEEN ... AND ...
COALESCE()
IN()
INTERVAL()
IS NOT NULL
IS NOT
IS NULL
IS
ISNULL()
LIKE
NOT BETWEEN ... AND ...
NOT IN()
NOT LIKE
STRCMP()
Numeric functions and operators+
-
*
/
DIV
% (MOD)
-
ABS()
ACOS()
ASIN()
ATAN()
ATAN2()
ATAN()

CEIL()
CEILING()
CONV()
COS()
COT()
CRC32()
DEGREES()
EXP()
FLOOR()
LN()
LOG()
LOG10()
LOG2()
MOD()
PI()
POW()
POWER()
RADIANS()
RAND()
ROUND()
SIGN()
SIN()
SQRT()
Control flow functionsCASE
IF()
IFNULL()
JSON functionsJSON_ARRAY_APPEND()
JSON_ARRAY()
JSON_CONTAINS()
JSON_EXTRACT()
JSON_INSERT()
JSON_LENGTH()
JSON_MERGE_PATCH()
JSON_MERGE()
JSON_OBJECT()
JSON_REMOVE()
JSON_REPLACE()
JSON_SET()
JSON_TYPE()
JSON_UNQUOTE()
JSON_VALID()
MEMBER OF()
Date and time functionsDATE()
DATE_FORMAT()
DATEDIFF()
DAYOFMONTH()
DAYOFWEEK()
DAYOFYEAR()
FROM_DAYS()
HOUR()
MAKEDATE()
MAKETIME()
MICROSECOND()
MINUTE()
MONTH()
MONTHNAME()
PERIOD_ADD()
PERIOD_DIFF()
SEC_TO_TIME()
SECOND()
SYSDATE()
TIME_TO_SEC()
TIMEDIFF()
WEEK()
WEEKOFYEAR()
YEAR()
String functionsASCII()
BIT_LENGTH()
CHAR()
CHAR_LENGTH()
CONCAT()
CONCAT_WS()
ELT()
FIELD()
HEX()
LENGTH()
LIKE
LOWER()
LTRIM()
MID()
NOT LIKE
NOT REGEXP
REGEXP
REGEXP_LIKE()
REGEXP_REPLACE()
REGEXP_SUBSTR()
REPLACE()
REVERSE()
RIGHT(), RLIKE
RTRIM()
SPACE()
STRCMP()
SUBSTR()
SUBSTRING()
UPPER()
Aggregation functionsCOUNT()
COUNT(DISTINCT)
SUM()
AVG()
MAX()
MIN()
VARIANCE()
VAR_POP()
STD()
STDDEV()
STDDEV_POP
VAR_SAMP()
STDDEV_SAMP()
JSON_ARRAYAGG(key)
JSON_OBJECTAGG(key, value)
Encryption and compression functionsMD5()
SHA1(), SHA()
UNCOMPRESSED_LENGTH()
Cast functions and operatorsCAST()
CONVERT()
Miscellaneous functionsUUID()
Window FunctionsNone

Blocklist specific expressions

If unexpected behavior occurs in the calculation process when pushing down the supported expressions or specific data types (only the ENUM type and the BIT type), you can restore the application quickly by prohibiting the pushdown of the corresponding functions, operators, or data types. Specifically, you can prohibit the functions, operators, or data types from being pushed down by adding them to the blocklist mysql.expr_pushdown_blacklist. For details, refer to Add to the blocklist.

Was this page helpful?