{"id":119,"date":"2016-12-19T00:00:00","date_gmt":"2016-12-19T00:00:00","guid":{"rendered":"https:\/\/en.pingcap.com\/blog\/adding-built-in-function\/"},"modified":"2024-05-23T19:51:15","modified_gmt":"2024-05-24T02:51:15","slug":"adding-built-in-function","status":"publish","type":"post","link":"https:\/\/www.pingcap.com\/ko\/blog\/adding-built-in-function\/","title":{"rendered":"Adding Built-in Functions"},"content":{"rendered":"

This document describes how to add built-in functions to TiDB.<\/p>\n

Background<\/h3>\n

How is the SQL statement executed in TiDB?<\/p>\n

The SQL statement is parsed to an abstract syntax tree (AST) by the parser first and then uses Query Optimizer to generate an execution plan. The plan can then be executed to get the result. This process involves how to access the data in the table, and how to filter, calculate, sort, aggregate, and distinct the data, etc. For a built-in function, the most important part is to parse and to evaluate.<\/p>\n

For parsing, it is redundant work because you should know how to write YACC commands and how to modify TiDB syntax parser. But we have finished this work for you and syntax parsing of most built-in functions is done.<\/p>\n

As for evaluation, it should be finished in the TiDB expression evaluation framework. Each built-in function is considered as an expression indicated by ScalarFunction<\/code> and obtains the corresponding function type and function signature through the function name and parameters to evaluate.<\/p>\n

The procedure discussed above is complicated for users who are not familiar with TiDB. We have finished syntax parsing and function signature confirmation of most unimplemented functions. But implementation is left empty. In other words, locating and completing the empty implementation makes a Pull Request (PR).<\/p>\n

The procedure to add a built-in function<\/h3>\n

The following procedure describes how to add a built-in function.<\/p>\n

    \n
  1. Locate the unimplemented function.\n
      \n
    1. Search for errFunctionNotExists<\/code> in the expression<\/code> directory of TiDB source code. You can find all the unimplemented functions.<\/li>\n
    2. Choose a function you are interested in. Take the SHA2 function as an example:\n
      func (b *builtinSHA2Sig) eval(row []types.Datum) (d types.Datum, err error) {\nreturn d, errFunctionNotExists.GenByArgs(\"SHA2\")\n}\n<\/code><\/pre>\n<\/li>\n<\/ol>\n<\/li>\n
    3. Implement the function signature.This step is to implement eval<\/code>. For the function features, see MySQL documentation. For the specific implementation method, see the method of implemented functions.<\/li>\n
    4. Add the type inference information to the typeinferer<\/code> file.Add the type of the returned result of the function to handleFuncCallExpr()<\/code> in the the plan\/typeinferer.go<\/code> file and make sure the result is consistent with the result in MySQL. See MySQL Const<\/a> for the complete list of the type definition.\n
      Note:<\/strong> For most functions, you need to input the type of the returned result and obtain the length of the returned result.<\/li>\n
    5. Add a unit test case.Add a unit test case for the function to the expression<\/code> directory. Add a unit test case of typeinferer<\/code> to the plan\/typeinferer_test.go<\/code> file.<\/li>\n
    6. Run the make dev<\/code> command and make sure all the test cases can pass.<\/li>\n<\/ol>\n