Skip to content

Latest commit

 

History

History
32 lines (25 loc) · 17.1 KB

expressions-pushed-down.md

File metadata and controls

32 lines (25 loc) · 17.1 KB
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.

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_REPLACE(json_doc, path, val[, path, val] ...),
JSON_SET(json_doc, path, val[, path, val] ...),
JSON_TYPE(json_val),
JSON_UNQUOTE(json_val),
JSON_VALID(val)
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, LTRIM(), MID(), NOT LIKE, NOT REGEXP, REGEXP, REPLACE(), REVERSE(), RIGHT(), RTRIM(), SPACE(), STRCMP(), SUBSTR(), SUBSTRING()
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.