10 minutes to become TIDB Contributor series | Add a built-in function

This is a creation in Article, where the information may have evolved or changed.

10 minutes to become TIDB Contributor series | Add a built-in function

Recently we have made some improvements to the TIDB code, which greatly simplifies the process of adding built-in functions, a tutorial that describes how to add builtin functions to TIDB. First introduce some necessary background knowledge, then introduce the process of adding builtin function, and finally take a function as an example.

Background Knowledge

After the

SQL statement is sent to TIDB, it passes through parser, becomes the AST from the text parse (abstract syntax tree), generates an execution plan from the Query Optimizer, obtains a plan that can be executed, and the result is obtained by executing the plan, which involves How to get data from a table, how to filter, calculate, sort, aggregate, filter, and evaluate an expression. For a builtin function, it is important to parse the syntax and evaluate the value. The syntax parsing part needs to know how to write YACC and how to modify TIDB lexical parser, more cumbersome, we have done this part of the work ahead of time, most of the BUILTIN function syntax parsing work has been done. The evaluation of the BUILTIN function needs to be done under the TIDB expression evaluation framework, each BUILTIN function is considered an expression, denoted by a scalarfunction, each BUILTIN function through its function name and parameters, Gets the corresponding function type and function signature, which is then evaluated by the function signature. Overall, the above process is not familiar with Tidb's friend is more complex, we have done some work on this part, some of the process, more cumbersome work to do a unified processing, the majority of the Buitlin function is now the parsing of the syntax and the search for a function signature work is completed, However, the function implementation is partially left blank. In other words, if you find a function implementation that is empty, add it as a PR.

Add Builtin function Overall process

• Find the non-implemented function in the TIDB source in the expression directory search errFunctionNotExists , you can find all the non-implemented functions, from which to choose a function of interest, such as the SHA2 function:

func (b *builtinSHA2Sig) eval(row []types.Datum) (d types.Datum, err error) {    return d, errFunctionNotExists.GenByArgs("SHA2")}

• Implementing a function signature The next thing to do is to implement the Eval method, the function of the functions please refer to the MySQL document, the specific implementation method can refer to the current implementation of the function.

• Add type deduction information in Typeinferer Add the return result type of this function to the handlefunccallexpr () in Plan/typeinferer.go, and keep it consistent with MySQL results. See all type definitions in MySQL Const.

    * 注意大多数函数除了需要填写返回值类型之外，还需要获取返回值的长度。

• Write unit tests in the expression directory, add unit tests to the implementation of the function, and also adding Typeinferer unit tests to the Plan/typeinferer_test.go file

• Run make Dev to ensure that all test case runs past

Example

Here to add the SHA1 () function of the PR as an example, the detailed explanation first look expression/builtin_encryption.go : the SHA1 () of the evaluation method to complement the complete

func (b *builtinSHA1Sig) eval(row []types.Datum) (d types.Datum, err error) {    // 首先对参数进行求值，这块一般不用修改    args, err := b.evalArgs(row)    if err != nil {        return types.Datum{}, errors.Trace(err)    }    // 每个参数的意义请参考 MySQL 文档    // SHA/SHA1 function only accept 1 parameter    arg := args[0]    if arg.IsNull() {        return d, nil    }    // 这里对参数值做了一个类型转换，函数的实现请参考 util/types/datum.go    bin, err := arg.ToBytes()    if err != nil {        return d, errors.Trace(err)    }    hasher := sha1.New()    hasher.Write(bin)    data := fmt.Sprintf("%x", hasher.Sum(nil))    // 设置返回值    d.SetString(data)    return d, nil}

Next, add unit tests to the function implementation, see expression/builtin_encryption_test.go :

var shacases = []struct {Origin interface{} crypt string} {"Test", "A94a8fe5ccb19ba61c4c0873d391e987982fbbd3" }, {"c4pt0r", "034923dcabf099fc4c8917c0ab91ffcd4c2578a6"}, {"Pingcap", "73bf9ef43a44f42e2ea2894d62f0917af149a006"} {"Foobar", "8843d7f92416211de9ebb963ff4ce28125932878"}, {1024x768, "128351137a9c47206c4507dcf2e6fbeeca3a9079"}, {12 3.45, "22f8b438ad7e89300b51d88684f3f0b9fa1d7a32"},} func (S *testevaluatorsuite) Testshaencrypt (c *c) {defer Testlea K.aftertest (c) ()//Monitoring Goroutine leakage tool, can directly copy FC: = Funcs[ast. SHA] For _, Test: = Range Shacases {in: = types. Newdatum (Test.origin) F, _: = Fc.getfunction (Datumstoconstants ([]types. Datum{in}), S.ctx) crypt, err: = F.eval (nil) C.assert (err, Isnil) res, err: = Crypt. ToString () C.assert (err, Isnil) C.assert (res, Equals, Test.crypt)}//test NULL input for SHA var Argnull types. Datum F, _: = Fc.getfunction (Datumstoconstants ([]types. Datum{argnull}), S.ctx) crypt, err: = F.eval (nil) C.assert (err, Isnil) C.assert (crypt. IsNull (), IsTrue)}* Note that, in addition to the normal case, it is best to add some exception cases, such as nil input values or multiple types of parameters

Finally, you need to add the type deduction information as well as the test case, see plan/typeinferer.go plan/typeinferer_test.go :

case ast.SHA, ast.SHA1:        tp = types.NewFieldType(mysql.TypeVarString)        chs = v.defaultCharset        tp.Flen = 40

        {`sha1(123)`, mysql.TypeVarString, "utf8"},        {`sha(123)`, mysql.TypeVarString, "utf8"},

Edit by: Add Tidb Robot, join the TIDB contributor Club, no barriers to participate in open source projects, change the world start here (Meng Meng da).