func builtinAvg(args []interface{}, ctx map[interface{}]interface{}) (v interface{}, err error) {
	// avg use decimal for integer and decimal type, use float for others
	// see https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html
	type avg struct {
		sum           interface{}
		n             uint64
		decimalResult bool
	}

	if _, ok := ctx[ExprEvalArgAggEmpty]; ok {
		return
	}

	fn := ctx[ExprEvalFn]
	distinct := getDistinct(ctx, fn)

	if _, ok := ctx[ExprAggDone]; ok {
		distinct.clear()

		data, ok := ctx[fn].(avg)
		if !ok {
			return
		}

		switch x := data.sum.(type) {
		case nil:
			return nil, nil
		case float64:
			return float64(x) / float64(data.n), nil
		case mysql.Decimal:
			return x.Div(mysql.NewDecimalFromUint(data.n, 0)), nil
		}
		panic("should not happend")
	}

	data, _ := ctx[fn].(avg)
	y := args[0]
	if types.IsNil(y) {
		return
	}

	ok, err := distinct.isDistinct(args...)
	if err != nil || !ok {
		// if err or not distinct, return
		return nil, err
	}

	if types.IsNil(data.sum) {
		data.n = 0
	}

	data.sum, err = calculateSum(data.sum, y)
	if err != nil {
		return nil, errors.Errorf("eval AVG aggregate err: %v", err)
	}

	data.n++
	ctx[fn] = data
	return
}

// operator: >=, >, <=, <, !=, <>, = <=>, etc.
// see https://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html
func (o *BinaryOperation) evalComparisonOp(ctx context.Context, args map[interface{}]interface{}) (interface{}, error) {
	a, b, err := o.get2(ctx, args)
	if err != nil {
		return nil, err
	}

	if types.IsNil(a) || types.IsNil(b) {
		// for <=>, if a and b are both nil, return true.
		// if a or b is nil, return false.
		if o.Op == opcode.NullEQ {
			return types.IsNil(a) && types.IsNil(b), nil
		}

		return nil, nil
	}

	n, err := types.Compare(a, b)
	if err != nil {
		return nil, o.traceErr(err)
	}

	r, err := getCompResult(o.Op, n)
	if err != nil {
		return nil, o.errorf(err.Error())
	}

	return r, nil
}

func (e *Evaluator) patternLike(p *ast.PatternLikeExpr) bool {
	expr := p.Expr.GetValue()
	if types.IsNil(expr) {
		p.SetValue(nil)
		return true
	}

	sexpr, err := types.ToString(expr)
	if err != nil {
		e.err = errors.Trace(err)
		return false
	}

	// We need to compile pattern if it has not been compiled or it is not static.
	var needCompile = len(p.PatChars) == 0 || !ast.IsConstant(p.Pattern)
	if needCompile {
		pattern := p.Pattern.GetValue()
		if types.IsNil(pattern) {
			p.SetValue(nil)
			return true
		}
		spattern, err := types.ToString(pattern)
		if err != nil {
			e.err = errors.Trace(err)
			return false
		}
		p.PatChars, p.PatTypes = compilePattern(spattern, p.Escape)
	}
	match := doMatch(sexpr, p.PatChars, p.PatTypes)
	if p.Not {
		match = !match
	}
	p.SetValue(boolToInt64(match))
	return true
}

func (e *Evaluator) handleOrOr(o *ast.BinaryOperationExpr) bool {
	leftVal := o.L.GetValue()
	righVal := o.R.GetValue()
	if !types.IsNil(leftVal) {
		x, err := types.ToBool(leftVal)
		if err != nil {
			e.err = errors.Trace(err)
			return false
		} else if x == 1 {
			// true || any other types is true.
			o.SetValue(x)
			return true
		}
	}
	if !types.IsNil(righVal) {
		y, err := types.ToBool(righVal)
		if err != nil {
			e.err = errors.Trace(err)
			return false
		} else if y == 1 {
			o.SetValue(y)
			return true
		}
	}
	if types.IsNil(leftVal) || types.IsNil(righVal) {
		o.SetValue(nil)
		return true
	}
	o.SetValue(int64(0))
	return true
}

func (e *Evaluator) patternIn(n *ast.PatternInExpr) bool {
	lhs := n.Expr.GetValue()
	if types.IsNil(lhs) {
		n.SetValue(nil)
		return true
	}
	hasNull := false
	for _, v := range n.List {
		if types.IsNil(v.GetValue()) {
			hasNull = true
			continue
		}
		r, err := types.Compare(n.Expr.GetValue(), v.GetValue())
		if err != nil {
			e.err = errors.Trace(err)
			return false
		}
		if r == 0 {
			n.SetValue(boolToInt64(!n.Not))
			return true
		}
	}
	if hasNull {
		// if no matched but we got null in In, return null
		// e.g 1 in (null, 2, 3) returns null
		n.SetValue(nil)
		return true
	}
	n.SetValue(boolToInt64(n.Not))
	return true
}

func (e *Evaluator) handleComparisonOp(o *ast.BinaryOperationExpr) bool {
	a, b := types.Coerce(o.L.GetValue(), o.R.GetValue())
	if types.IsNil(a) || types.IsNil(b) {
		// for <=>, if a and b are both nil, return true.
		// if a or b is nil, return false.
		if o.Op == opcode.NullEQ {
			if types.IsNil(a) || types.IsNil(b) {
				o.SetValue(oneI64)
			} else {
				o.SetValue(zeroI64)
			}
		} else {
			o.SetValue(nil)
		}
		return true
	}

	n, err := types.Compare(a, b)
	if err != nil {
		e.err = errors.Trace(err)
		return false
	}

	r, err := getCompResult(o.Op, n)
	if err != nil {
		e.err = errors.Trace(err)
		return false
	}
	if r {
		o.SetValue(oneI64)
	} else {
		o.SetValue(zeroI64)
	}
	return true
}

func (e *Evaluator) handleXor(o *ast.BinaryOperationExpr) bool {
	leftVal := o.L.GetValue()
	righVal := o.R.GetValue()
	if types.IsNil(leftVal) || types.IsNil(righVal) {
		o.SetValue(nil)
		return true
	}
	x, err := types.ToBool(leftVal)
	if err != nil {
		e.err = errors.Trace(err)
		return false
	}

	y, err := types.ToBool(righVal)
	if err != nil {
		e.err = errors.Trace(err)
		return false
	}
	if x == y {
		o.SetValue(int64(0))
	} else {
		o.SetValue(int64(1))
	}
	return true
}

func (o *BinaryOperation) evalLogicXor(ctx context.Context, args map[interface{}]interface{}) (interface{}, error) {
	a, err := o.L.Eval(ctx, args)
	if err != nil || types.IsNil(a) {
		return nil, o.traceErr(err)
	}

	x, err := types.ToBool(a)
	if err != nil {
		return nil, o.traceErr(err)
	}

	b, err := o.R.Eval(ctx, args)
	if err != nil || types.IsNil(b) {
		return nil, o.traceErr(err)
	}

	y, err := types.ToBool(b)
	if err != nil {
		return nil, o.traceErr(err)
	}

	if x == y {
		return int64(0), nil
	}

	return int64(1), nil
}

// Eval implements the Expression Eval interface.
func (p *PatternRegexp) Eval(ctx context.Context, args map[interface{}]interface{}) (v interface{}, err error) {
	var sexpr string
	var ok bool
	switch {
	case p.Sexpr != nil:
		sexpr = *p.Sexpr
	default:
		expr, err := p.Expr.Eval(ctx, args)
		if err != nil {
			return nil, err
		}

		if types.IsNil(expr) {
			return nil, nil
		}

		sexpr, ok = expr.(string)
		if !ok {
			return nil, errors.Errorf("non-string Expression in LIKE: %v (Value of type %T)", expr, expr)
		}

		if p.Expr.IsStatic() {
			p.Sexpr = new(string)
			*p.Sexpr = sexpr
		}
	}

	re := p.Re
	if re == nil {
		pattern, err := p.Pattern.Eval(ctx, args)
		if err != nil {
			return nil, err
		}

		if types.IsNil(pattern) {
			return nil, nil
		}

		spattern, ok := pattern.(string)
		if !ok {
			return nil, errors.Errorf("non-string pattern in LIKE: %v (Value of type %T)", pattern, pattern)
		}

		if re, err = regexp.Compile(spattern); err != nil {
			return nil, err
		}

		if p.Pattern.IsStatic() {
			p.Re = re
		}
	}
	match := re.MatchString(sexpr)
	if p.Not {
		return !match, nil
	}
	return match, nil
}

func (e *Evaluator) patternRegexp(p *ast.PatternRegexpExpr) bool {
	var sexpr string
	if p.Sexpr != nil {
		sexpr = *p.Sexpr
	} else {
		expr := p.Expr.GetValue()
		if types.IsNil(expr) {
			p.SetValue(nil)
			return true
		}
		var err error
		sexpr, err = types.ToString(expr)
		if err != nil {
			e.err = errors.Errorf("non-string Expression in LIKE: %v (Value of type %T)", expr, expr)
			return false
		}

		if ast.IsConstant(p.Expr) {
			p.Sexpr = new(string)
			*p.Sexpr = sexpr
		}
	}

	re := p.Re
	if re == nil {
		pattern := p.Pattern.GetValue()
		if types.IsNil(pattern) {
			p.SetValue(nil)
			return true
		}
		spattern, err := types.ToString(pattern)
		if err != nil {
			e.err = errors.Errorf("non-string pattern in LIKE: %v (Value of type %T)", pattern, pattern)
			return false
		}

		if re, err = regexp.Compile(spattern); err != nil {
			e.err = errors.Trace(err)
			return false
		}

		if ast.IsConstant(p.Pattern) {
			p.Re = re
		}
	}
	match := re.MatchString(sexpr)
	if p.Not {
		match = !match
	}
	p.SetValue(boolToInt64(match))
	return true
}

func (e *Evaluator) funcTrim(v *ast.FuncTrimExpr) bool {
	// eval str
	fs := v.Str.GetValue()
	if types.IsNil(fs) {
		v.SetValue(nil)
		return true
	}
	str, err := types.ToString(fs)
	if err != nil {
		e.err = errors.Trace(err)
		return false
	}
	remstr := ""
	// eval remstr
	if v.RemStr != nil {
		fs = v.RemStr.GetValue()
		if types.IsNil(fs) {
			v.SetValue(nil)
			return true
		}
		remstr, err = types.ToString(fs)
		if err != nil {
			e.err = errors.Trace(err)
			return false
		}
	}
	// Do trim
	var result string
	if v.Direction == ast.TrimLeading {
		if len(remstr) > 0 {
			result = trimLeft(str, remstr)
		} else {
			result = strings.TrimLeft(str, spaceChars)
		}
	} else if v.Direction == ast.TrimTrailing {
		if len(remstr) > 0 {
			result = trimRight(str, remstr)
		} else {
			result = strings.TrimRight(str, spaceChars)
		}
	} else if len(remstr) > 0 {
		x := trimLeft(str, remstr)
		result = trimRight(x, remstr)
	} else {
		result = strings.Trim(str, spaceChars)
	}
	v.SetValue(result)
	return true
}

// Eval implements the Expression Eval interface.
func (is *IsTruth) Eval(ctx context.Context, args map[interface{}]interface{}) (interface{}, error) {
	if err := CheckOneColumn(ctx, is.Expr); err != nil {
		return nil, errors.Trace(err)
	}

	val, err := is.Expr.Eval(ctx, args)
	if err != nil {
		return nil, errors.Trace(err)
	}

	if types.IsNil(val) {
		// null is true/false -> false
		// null is not true/false -> true
		return is.Not, nil
	}

	b, err := types.ToBool(val)
	if err != nil {
		return nil, errors.Trace(err)
	}

	if !is.Not {
		// true/false is true/false
		return b == is.True, nil
	}

	// true/false is not true/false
	return b != is.True, nil
}

func builtinCount(args []interface{}, ctx map[interface{}]interface{}) (v interface{}, err error) {
	if _, ok := ctx[ExprEvalArgAggEmpty]; ok {
		return int64(0), nil
	}

	fn := ctx[ExprEvalFn]
	distinct := getDistinct(ctx, fn)

	if _, ok := ctx[ExprAggDone]; ok {
		distinct.clear()
		return ctx[fn].(int64), nil
	}

	n, _ := ctx[fn].(int64)

	if !types.IsNil(args[0]) {
		ok, err := distinct.isDistinct(args...)
		if err != nil || !ok {
			// if err or not distinct, return
			return nil, err
		}
		n++
	}

	ctx[fn] = n
	return
}

func (e *Evaluator) checkInList(not bool, in interface{}, list []interface{}) (interface{}, error) {
	hasNull := false
	for _, v := range list {
		if types.IsNil(v) {
			hasNull = true
			continue
		}

		r, err := types.Compare(in, v)
		if err != nil {
			return nil, errors.Trace(err)
		}

		if r == 0 {
			return !not, nil
		}
	}

	if hasNull {
		// if no matched but we got null in In, return null
		// e.g 1 in (null, 2, 3) returns null
		return nil, nil
	}

	return not, nil
}

func (e *Evaluator) patternIn(n *ast.PatternInExpr) bool {
	lhs := n.Expr.GetValue()
	if types.IsNil(lhs) {
		n.SetValue(nil)
		return true
	}
	if n.Sel == nil {
		values := make([]interface{}, 0, len(n.List))
		for _, ei := range n.List {
			values = append(values, ei.GetValue())
		}
		x := e.checkInList(n.Not, lhs, values)
		if e.err != nil {
			return false
		}
		n.SetValue(x)
		return true
	}
	se := n.Sel.(*ast.SubqueryExpr)
	sel := se.SubqueryExec

	res := sel.GetValue().([]interface{})
	x := e.checkInList(n.Not, lhs, res)
	if e.err != nil {
		return false
	}
	n.SetValue(x)
	return true
}

// See https://dev.mysql.com/doc/refman/5.7/en/control-flow-functions.html#function_nullif
func builtinNullIf(args []interface{}, m map[interface{}]interface{}) (interface{}, error) {
	// nullif(expr1, expr2)
	// returns null if expr1 = expr2 is true, otherwise returns expr1
	v1 := args[0]
	v2 := args[1]

	if types.IsNil(v1) || types.IsNil(v2) {
		return v1, nil
	}

	if n, err := types.Compare(v1, v2); err != nil || n == 0 {
		return nil, err
	}

	return v1, nil
}

func (n *PatternIn) checkInList(in interface{}, list []interface{}) (interface{}, error) {
	hasNull := false
	for _, v := range list {
		if types.IsNil(v) {
			hasNull = true
			continue
		}

		r, err := types.Compare(in, v)
		if err != nil {
			return nil, err
		}

		if r == 0 {
			return !n.Not, nil
		}
	}

	if hasNull {
		// if no matched but we got null in In, return null
		// e.g 1 in (null, 2, 3) returns null
		return nil, nil
	}

	return n.Not, nil
}

// Eval implements the Expression Eval interface.
func (f *FunctionTrim) Eval(ctx context.Context, args map[interface{}]interface{}) (interface{}, error) {
	// eval str
	fs, err := f.Str.Eval(ctx, args)
	if err != nil {
		return nil, errors.Trace(err)
	}
	if types.IsNil(fs) {
		return nil, nil
	}
	str, err := types.ToString(fs)
	if err != nil {
		return nil, errors.Trace(err)
	}
	remstr := ""
	// eval remstr
	if f.RemStr != nil {
		fs, err = f.RemStr.Eval(ctx, args)
		if err != nil {
			return nil, errors.Trace(err)
		}
		if types.IsNil(fs) {
			return nil, nil
		}
		remstr, err = types.ToString(fs)
		if err != nil {
			return nil, errors.Trace(err)
		}
	}
	// Do trim
	if f.Direction == TrimLeading {
		if len(remstr) > 0 {
			return trimLeft(str, remstr), nil
		}
		return strings.TrimLeft(str, spaceChars), nil
	} else if f.Direction == TrimTrailing {
		if len(remstr) > 0 {
			return trimRight(str, remstr), nil
		}
		return strings.TrimRight(str, spaceChars), nil
	}
	if len(remstr) > 0 {
		x := trimLeft(str, remstr)
		x = trimRight(x, remstr)
		return x, nil
	}
	return strings.Trim(str, spaceChars), nil
}

// Eval implements the Expression Eval interface.
func (f *FunctionLocate) Eval(ctx context.Context, args map[interface{}]interface{}) (interface{}, error) {
	// eval str
	fs, err := f.Str.Eval(ctx, args)
	if err != nil {
		return nil, errors.Trace(err)
	}
	if types.IsNil(fs) {
		return nil, nil
	}
	str, err := types.ToString(fs)
	if err != nil {
		return nil, errors.Trace(err)
	}
	// eval substr
	fs, err = f.SubStr.Eval(ctx, args)
	if err != nil {
		return nil, errors.Trace(err)
	}
	if types.IsNil(fs) {
		return nil, nil
	}
	substr, err := types.ToString(fs)
	if err != nil {
		return nil, errors.Trace(err)
	}
	// eval pos
	pos := 0
	if f.Pos != nil {
		t, err := f.Pos.Eval(ctx, args)
		if err != nil {
			return nil, errors.Trace(err)
		}
		p, err := types.ToInt64(t)
		if err != nil {
			return nil, errors.Trace(err)
		}
		pos = int(p)
	}
	// eval locate
	if pos < 0 || pos > len(str) {
		return 0, errors.Errorf("Locate invalid pos args: %d", pos)
	}
	str = str[pos:]
	i := strings.Index(str, substr)
	return i + 1 + pos, nil
}

// See: http://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html#function_coalesce
func builtinCoalesce(args []interface{}, ctx map[interface{}]interface{}) (v interface{}, err error) {
	for _, v := range args {
		if !types.IsNil(v) {
			return v, nil
		}
	}
	return nil, nil
}