TiDB Cloud
TiDB Self-Managed
Learning Center
Contact Us
​
Sign InStart for Free

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([val[, val] ...]),
JSON_CONTAINS(target, candidate[, path]),
JSON_EXTRACT(json_doc, path[, path] ...),
JSON_INSERT(json_doc, path, val[, path, val] ...),
JSON_LENGTH(json_doc[, path]),
JSON_MERGE(json_doc, json_doc[, json_doc] ...),
JSON_OBJECT([key, val[, key, val] ...]),
JSON_REMOVE(json_doc, path[, path] ...),
JSON_SET(json_doc, path, val[, path, val] ...),
JSON_TYPE(json_val),
JSON_UNQUOTE(json_val),
JSON_VALID(val),
value MEMBER OF(json_array)
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, LTRIM(), MID(), NOT LIKE, NOT REGEXP, REGEXP, REGEXP_INSTR(), REGEXP_LIKE(), REGEXP_REPLACE(), REGEXP_SUBSTR(), REPLACE(), REVERSE(), RIGHT(), RLIKE, RTRIM(), SPACE(), STRCMP(), SUBSTR(), SUBSTRING()
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()

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?

Download PDFRequest docs changesAsk questions on Discord