It is often said that reading source code is a must for every good developer, but in the face of complex systems such as TiDB, reading source code is a very large project. For some TiDB users, starting from their daily problems, reading source code in turn is a good entry point, so we planned the “Reading source code with problems” series of articles.

This article is the second part of this series, from a Power BI Desktop in TiDB performance abnormal problem as an example, introduced from the problem discovery, positioning, through the open source community to raise issues, write PR to solve the problem process, from the perspective of code implementation to do trouble shooting, Hope to help you better understand TiDB source code.

First let’s recreate the failed scenario (TiDB 5.1.1 on MacOS) and create a simple table with only one field:

CREATE TABLE test(name VARCHAR(1) PRIMARY KEY);
Copy the code

MySQL > MySQL > TiDB > MySQL > TiDB > TiDB

DataSource.Error: An error happened while reading data from the provider: ‘Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.’

Details: DataSourceKind=MySql DataSourcePath=localhost:4000; test

The last SQL to run on general log TiDB is:

select COLUMN_NAME, ORDINAL_POSITION, IS_NULLABLE, DATA_TYPE, case when NUMERIC_PRECISION is null then null when DATA_TYPE in ('FLOAT', 'DOUBLE') then 2 else 10 end AS NUMERIC_PRECISION_RADIX, NUMERIC_PRECISION, NUMERIC_SCALE,            CHARACTER_MAXIMUM_LENGTH, COLUMN_DEFAULT, COLUMN_COMMENT AS DESCRIPTION, COLUMN_TYPE  from INFORMATION_SCHEMA.COLUMNS  where table_schema = 'test' and table_name = 'test';
Copy the code

If tiUP is used to start a TiDB cluster, the tiup client will also report an error:

error: mysql: sql: Scan error on column index 4, name “NUMERIC_PRECISION_RADIX”: converting NULL to int64 is unsupported

Let’s focus on fixing this statement. Let’s look at what this error means for tiup Client. Tiup client uses golang XO/USQL library, but we cannot find the corresponding error information in xo/ USQL library, and the grep converting keyword returns very limited and irrelevant contents. The code of go-SQL-driver /mysql can be downloaded and grep converting can only return one piece of information in Changelog. The most likely error is not in this library. Take a look at the code in Go-SQL-driver /mysql and see that it relies on database/ SQL. Database/SQL is golang’s standard library, so we need to download golang’s source code. Grep converting in golang’s Database directory, and you can quickly find what matches the error message:

go/src/database/sql/convert.go

case reflect.Int, reflect.Int8, reflect.Int16, reflect.Int32, reflect.Int64: if src == nil { return fmt.Errorf("converting NULL to %s is unsupported", dv.Kind()) } s := asString(src) i64, err := strconv.ParseInt(s, 10, dv.Type().Bits()) if err ! = nil { err = strconvErr(err) return fmt.Errorf("converting driver.Value type %T (%q) to a %s: %v", src, s, dv.Kind(), err) } dv.SetInt(i64) return nilCopy the code

Let’s trace the fragment again to see how the type comes from here, and eventually we’ll come back to go-SQL-driver /mysql:

mysql/fields.go

case fieldTypeLongLong: if mf.flags&flagNotNULL ! = 0 { if mf.flags&flagUnsigned ! = 0 { return scanTypeUint64 } return scanTypeInt64 } return scanTypeNullIntCopy the code

This part of the code converts the column definition in the parse statement return body to the type in Golang. Mysql –host 127.0.0.1 –port 4000 -u root –column-type-info

MySQL

Field 5: `NUMERIC_PRECISION_RADIX`
Catalog: `def`
Database: `` 
Table: ``
Org_table: ``
Type: LONGLONG
Collation: binary (63)
Length: 3
Max_length: 0
Decimals: 0
Flags: BINARY NUM
Copy the code

TiDB

Field 5: `NUMERIC_PRECISION_RADIX`
Catalog: `def`
Database: ``
Table: ``
Org_table: ``
Type: LONGLONG
Collation: binary (63)
Length: 2
Max_length: 0
Decimals: 0
Flags: NOT_NULL BINARY NUM
Copy the code

It can be clearly seen that the column definition of NUMERIC_PRECISION_RADIX field in tiUP Client error message has obvious problems on TiDB. This field is marked as NOT_NULL in TiDB return body. Obviously this is not reasonable, because the field can obviously be NULL, and MySQL’s return value reflects this. Xo/USQL failed to handle the returned body. Now that we’ve figured out why the client is reporting an error, we need to find out why TiDB is returning an error column definition.

The TiDB Dev Guide provides an overview of the execution process of a DQL statement in TiDB. Look at the server/conn.go# clientconn. Run entry. All the way through Go# clientconn. dispatch, server/conn.go# clientconn. handleQuery, server/conn.go# clientconn. handleStmt, server/dri ExecuteStmt, session/session.go#session.ExecuteStmt, executor/compiler.go# compiler.compile, planner / optimize go# optimize, planner/optimize go# optimize, planner/core/planbuilder go# planbuilder. Build, planner/core/logical_pl An_builder.go # planBuilder.buildselect, in buildSelect we can see a series of processing done by TiDB Planner to query statements, Then we can go to the planner/core/expression_rewriter go# PlanBuilder. RewriteWithPreprocess and Planner/core/expression_rewriter go# PlanBuilder. RewriteExprNode, in rewriteExprNode, The problematic field NUMERIC_PRECISION_RADIX will be analyzed, Finally in this CASE expression parsing expression/builtin_control go# caseWhenFunctionClass. GetFunction, We finally get to the point where we calculate the column definition returned by the CASE expression (which depends on iterating the AST parsed by the Compiler) :

for i := 1; i < l; i += 2 { fieldTps = append(fieldTps, args[i].GetType()) decimal = mathutil.Max(decimal, args[i].GetType().Decimal) if args[i].GetType().Flen == -1 { flen = -1 } else if flen ! = -1 { flen = mathutil.Max(flen, args[i].GetType().Flen) } isBinaryStr = isBinaryStr || types.IsBinaryStr(args[i].GetType()) isBinaryFlag = isBinaryFlag | |! types.IsNonBinaryStr(args[i].GetType()) } if l%2 == 1 { fieldTps = append(fieldTps, args[l-1].GetType()) decimal = mathutil.Max(decimal, args[l-1].GetType().Decimal) if args[l-1].GetType().Flen == -1 { flen = -1 } else if flen ! = -1 { flen = mathutil.Max(flen, args[l-1].GetType().Flen) } isBinaryStr = isBinaryStr || types.IsBinaryStr(args[l-1].GetType()) isBinaryFlag = isBinaryFlag || ! types.IsNonBinaryStr(args[l-1].GetType()) } fieldTp := types.AggFieldType(fieldTps) // Here we turn off NotNullFlag. Because if all when-clauses are false, // the result of case-when expr is NULL. types.SetTypeFlag(&fieldTp.Flag, mysql.NotNullFlag, false) tp := fieldTp.EvalType() if tp == types.ETInt { decimal = 0 } fieldTp.Decimal, fieldTp.Flen = decimal, flen if fieldTp.EvalType().IsStringKind() && ! isBinaryStr { fieldTp.Charset, fieldTp.Collate = DeriveCollationFromExprs(ctx, args...) if fieldTp.Charset == charset.CharsetBin && fieldTp.Collate == charset.CollationBin { // When args are Json and Numerical type(eg. Int), the fieldTp is String. // Both their charset/collation is binary, but the String need a default charset/collation. fieldTp.Charset, fieldTp.Collate = charset.GetDefaultCharsetAndCollate() } } else { fieldTp.Charset, fieldTp.Collate = charset.CharsetBin, charset.CollationBin } if isBinaryFlag { fieldTp.Flag |= mysql.BinaryFlag } // Set retType to BINARY(0) if all arguments  are of type NULL. if fieldTp.Tp == mysql.TypeNull { fieldTp.Flen, fieldTp.Decimal = 0, types.UnspecifiedLength types.SetBinChsClnFlag(fieldTp) }Copy the code

If you look at the code above to evaluate the Column Definition flag, you can see that regardless of the CASE expression, the NOT_NULL flag bit must be set to false, so there is no problem! At this point we can only follow the code path above to see if the column Definition generated above has been modified later. Finally in the server/conn. Go# clientConn. HandleStmt, found that it calls the server/conn. Go# clientConn. WriteResultSet, Then gradually invoke server/conn. Go# clientConn. WriteChunks, server/conn. Go# clientConn. WriteColumnInfo, server/column. Go# ColumnInfo. Dump And server/column.go#dumpFlag, where the previously generated column definition flag is modified:

func dumpFlag(tp byte, flag uint16) uint16 {
    switch tp {
    case mysql.TypeSet:
        return flag | uint16(mysql.SetFlag)
    case mysql.TypeEnum:
        return flag | uint16(mysql.EnumFlag)
    default:
        if mysql.HasBinaryFlag(uint(flag)) {
            return flag | uint16(mysql.NotNullFlag)
        }
        return flag
    }
}
Copy the code

Finally, we found the reason why TiDB returned the wrong column definition! This bug has been fixed in TiDB 5.2.0: Fix some problems related to notNullFlag by wjhuang2016 · Pull Request #27697 · pingcap/tidb

Finally, while reading the code above, it would be nice to see what the AST looks like after being parsed by TiDB so that we don’t get blinded during the final walk through the AST. Parser/Quickstart. md at master · pingcap/ Parser has an example output of an AST, but simply exporting it is almost useless. We can use Davecgh/Go-spew to output a parser node directly, and then we get an understandable tree:

package main import ( "fmt" "github.com/pingcap/parser" "github.com/pingcap/parser/ast" _ "github.com/pingcap/parser/test_driver" "github.com/davecgh/go-spew/spew" ) func parse(sql string) (*ast.StmtNode, error) { p := parser.New() stmtNodes, _, err := p.Parse(sql, "", "") if err ! = nil { return nil, err } return &stmtNodes[0], nil } func main() { spew.Config.Indent = " " astNode, err := parse("SELECT a, b FROM t") if err ! = nil { fmt.Printf("parse error: %v\n", err.Error()) return } fmt.Printf("%s\n", spew.Sdump(*astNode)) }Copy the code
(*ast.SelectStmt)(0x140001dac30)({
    dmlNode: (ast.dmlNode) {
        stmtNode: (ast.stmtNode) {
            node: (ast.node) {
                text: (string) (len=18) "SELECT a, b FROM t"
            }
        }
    },
    resultSetNode: (ast.resultSetNode) {
        resultFields: ([]*ast.ResultField) <nil>
    },
    SelectStmtOpts: (*ast.SelectStmtOpts)(0x14000115bc0)({
        Distinct: (bool) false,
        SQLBigResult: (bool) false,
        SQLBufferResult: (bool) false,
        SQLCache: (bool) true,
        SQLSmallResult: (bool) false,
        CalcFoundRows: (bool) false,
        StraightJoin: (bool) false,
        Priority: (mysql.PriorityEnum) 0,
        TableHints: ([]*ast.TableOptimizerHint) <nil>
    }),
    Distinct: (bool) false,
    From: (*ast.TableRefsClause)(0x140001223c0)({
        node: (ast.node) {
            text: (string) ""
        },
        TableRefs: (*ast.Join)(0x14000254100)({
            node: (ast.node) {
                text: (string) ""
            },
            resultSetNode: (ast.resultSetNode) {
                resultFields: ([]*ast.ResultField) <nil>
            },
            Left: (*ast.TableSource)(0x14000156480)({
                node: (ast.node) {
                    text: (string) ""
                },
                Source: (*ast.TableName)(0x1400013a370)({
                    node: (ast.node) {
                        text: (string) ""
                    },
                    resultSetNode: (ast.resultSetNode) {
                        resultFields: ([]*ast.ResultField) <nil>
                    },
                    Schema: (model.CIStr) ,
                    Name: (model.CIStr) t,
                    DBInfo: (*model.DBInfo)(<nil>),
                    TableInfo: (*model.TableInfo)(<nil>),
                    IndexHints: ([]*ast.IndexHint) <nil>,
                    PartitionNames: ([]model.CIStr) {
                    }
                }),
                AsName: (model.CIStr)
            }),
            Right: (ast.ResultSetNode) <nil>,
            Tp: (ast.JoinType) 0,
            On: (*ast.OnCondition)(<nil>),
            Using: ([]*ast.ColumnName) <nil>,
            NaturalJoin: (bool) false,
            StraightJoin: (bool) false
        })
    }),
    Where: (ast.ExprNode) <nil>,
    Fields: (*ast.FieldList)(0x14000115bf0)({
        node: (ast.node) {
            text: (string) ""
        },
        Fields: ([]*ast.SelectField) (len=2 cap=2) {
            (*ast.SelectField)(0x140001367e0)({
                node: (ast.node) {
                    text: (string) (len=1) "a"
                },
                Offset: (int) 7,
                WildCard: (*ast.WildCardField)(<nil>),
                Expr: (*ast.ColumnNameExpr)(0x14000254000)({
                    exprNode: (ast.exprNode) {
                        node: (ast.node) {
                            text: (string) ""
                        },
                        Type: (types.FieldType) unspecified,
                        flag: (uint64) 8
                    },
                    Name: (*ast.ColumnName)(0x1400017dc70)(a),
                    Refer: (*ast.ResultField)(<nil>)
                }),
                AsName: (model.CIStr) ,
                Auxiliary: (bool) false
            }),
            (*ast.SelectField)(0x14000136840)({
                node: (ast.node) {
                    text: (string) (len=1) "b"
                },
                Offset: (int) 10,
                WildCard: (*ast.WildCardField)(<nil>),
                Expr: (*ast.ColumnNameExpr)(0x14000254080)({
                    exprNode: (ast.exprNode) {
                        node: (ast.node) {
                            text: (string) ""
                        },
                        Type: (types.FieldType) unspecified,
                        flag: (uint64) 8
                    },
                    Name: (*ast.ColumnName)(0x1400017dce0)(b),
                    Refer: (*ast.ResultField)(<nil>)
                }),
                AsName: (model.CIStr) ,
                Auxiliary: (bool) false
            })
        }
    }),
    GroupBy: (*ast.GroupByClause)(<nil>),
    Having: (*ast.HavingClause)(<nil>),
    WindowSpecs: ([]ast.WindowSpec) <nil>,
    OrderBy: (*ast.OrderByClause)(<nil>),
    Limit: (*ast.Limit)(<nil>),
    LockTp: (ast.SelectLockType) none,
    TableHints: ([]*ast.TableOptimizerHint) <nil>,
    IsAfterUnionDistinct: (bool) false,
    IsInBraces: (bool) false,
    QueryBlockOffset: (int) 0,
    SelectIntoOpt: (*ast.SelectIntoOption)(<nil>)
})
Copy the code