Important

You are viewing the archived documentation of TiDB, which no longer receives updates. It is recommended that you use the latest LTS version of the TiDB database.

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 Type	Operations
Logical operators	AND (&&), 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 functions	CASE, IF(), IFNULL()
JSON functions	JSON_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 functions	DATE(), 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 functions	ASCII(), BIT_LENGTH(), CHAR(), CHAR_LENGTH(), CONCAT(), CONCAT_WS(), ELT(), FIELD(), HEX(), LENGTH(), LIKE, LOWER(), LTRIM(), MID(), NOT LIKE, NOT REGEXP, REGEXP, REGEXP_INSTR(), REGEXP_LIKE(), REGEXP_REPLACE(), REGEXP_SUBSTR(), REPLACE(), REVERSE(), RIGHT(), RLIKE, RTRIM(), SPACE(), STRCMP(), SUBSTR(), SUBSTRING(), UPPER()
Aggregation functions	COUNT(), 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 functions	MD5(), SHA1(), SHA(), UNCOMPRESSED_LENGTH()
Cast functions and operators	CAST(), CONVERT()
Miscellaneous functions	UUID()

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.

Download PDF