expressions-pushed-down.md

title	summary	aliases
List of Expressions for Pushdown	Learn a list of expressions that can be pushed down to TiKV and the related operations.	/docs/dev/functions-and-operators/expressions-pushed-down/ /docs/dev/reference/sql/functions-and-operators/expressions-pushed-down/

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.

Note:

The Aggregate functions do not support pushdown to TiKV when used as window functions.

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_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 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() DATE_ADD() DATE_SUB() ADDDATE() SUBDATE()
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_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()
Window Functions	None

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.