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
}
func (e *Evaluator) funcLocate(v *ast.FuncLocateExpr) 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
}
// eval substr
fs = v.SubStr.GetValue()
if types.IsNil(fs) {
v.SetValue(nil)
return true
}
substr, err := types.ToString(fs)
if err != nil {
e.err = errors.Trace(err)
return false
}
// eval pos
pos := 0
if v.Pos != nil {
t := v.Pos.GetValue()
p, err := types.ToInt64(t)
if err != nil {
e.err = errors.Trace(err)
return false
}
pos = int(p)
}
// eval locate
if pos < 0 || pos > len(str) {
e.err = ErrInvalidOperation.Gen("Locate invalid pos args: %d", pos)
return false
}
str = str[pos:]
i := strings.Index(str, substr)
v.SetValue(i + 1 + pos)
return true
}
// See http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_minute
func builtinMinute(args []interface{}, ctx map[interface{}]interface{}) (interface{}, error) {
v, err := convertToDuration(args[0], mysql.MaxFsp)
if err != nil || types.IsNil(v) {
return v, err
}
// No need to check type here.
d := v.(mysql.Duration)
return int64(d.Minute()), nil
}
func builtinGroupConcat(args []interface{}, ctx map[interface{}]interface{}) (v interface{}, err error) {
// TODO: the real group_concat is very complex, here we just support the simplest one.
if _, ok := ctx[ExprEvalArgAggEmpty]; ok {
return nil, nil
}
fn := ctx[ExprEvalFn]
distinct := getDistinct(ctx, fn)
if _, ok := ctx[ExprAggDone]; ok {
distinct.clear()
if v, _ := ctx[fn]; !types.IsNil(v) {
return v.(string), nil
}
return nil, nil
}
var buf bytes.Buffer
if v := ctx[fn]; !types.IsNil(v) {
s := v.(string)
// now use comma separator
buf.WriteString(s)
buf.WriteString(",")
}
ok, err := distinct.isDistinct(args...)
if err != nil || !ok {
// if err or not distinct, return
return nil, err
}
for i := 0; i < len(args); i++ {
if types.IsNil(args[i]) {
// if any is nil, we will not concat
return
}
buf.WriteString(fmt.Sprintf("%v", args[i]))
}
// TODO: if total length is greater than global var group_concat_max_len, truncate it.
ctx[fn] = buf.String()
return
}
func (o *BinaryOperation) evalOrOr(ctx context.Context, args map[interface{}]interface{}) (interface{}, error) {
a, err := o.L.Eval(ctx, args)
if err != nil {
return nil, o.traceErr(err)
}
var (
x int64
y int64
)
if !types.IsNil(a) {
x, err = types.ToBool(a)
if err != nil {
return nil, o.traceErr(err)
} else if x == 1 {
// true || any other types is true
return x, nil
}
}
b, err := o.R.Eval(ctx, args)
if err != nil {
return nil, o.traceErr(err)
}
if !types.IsNil(b) {
y, err = types.ToBool(b)
if err != nil {
return nil, o.traceErr(err)
} else if y == 1 {
return y, nil
}
}
// here x and y are all not true
// if a or b is nil
if types.IsNil(a) || types.IsNil(b) {
return nil, nil
}
return int64(0), nil
}
// Eval implements the Expression Eval interface.
func (p *PatternLike) Eval(ctx context.Context, args map[interface{}]interface{}) (v interface{}, err error) {
expr, err := p.Expr.Eval(ctx, args)
if err != nil {
return nil, errors.Trace(err)
}
if types.IsNil(expr) {
return nil, nil
}
sexpr, err := types.ToString(expr)
if err != nil {
return nil, errors.Trace(err)
}
// We need to compile pattern if it has not been compiled or it is not static.
var needCompile = len(p.patChars) == 0 || !p.Pattern.IsStatic()
if needCompile {
pattern, err := p.Pattern.Eval(ctx, args)
if err != nil {
return nil, errors.Trace(err)
}
if types.IsNil(pattern) {
return nil, nil
}
var spattern string
switch v := pattern.(type) {
case string:
spattern = v
case []byte:
spattern = string(v)
default:
return nil, errors.Errorf("Pattern should be string or []byte in LIKE: %v (Value of type %T)", pattern, pattern)
}
p.patChars, p.patTypes = compilePattern(spattern, p.Escape)
}
match := doMatch(sexpr, p.patChars, p.patTypes)
if p.Not {
return !match, nil
}
return match, nil
}
func (e *Evaluator) isNull(v *ast.IsNullExpr) bool {
var boolVal bool
if types.IsNil(v.Expr.GetValue()) {
boolVal = true
}
if v.Not {
boolVal = !boolVal
}
v.SetValue(boolToInt64(boolVal))
return true
}
// Eval implements the Expression Eval interface.
func (is *IsNull) 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)
}
return types.IsNil(val) != is.Not, nil
}
// See https://dev.mysql.com/doc/refman/5.7/en/control-flow-functions.html#function_ifnull
func builtinIfNull(args []interface{}, m map[interface{}]interface{}) (interface{}, error) {
// ifnull(expr1, expr2)
// if expr1 is not null, return expr1, otherwise, return expr2
v1 := args[0]
v2 := args[1]
if !types.IsNil(v1) {
return v1, nil
}
return v2, nil
}
func (o *BinaryOperation) evalAndAnd(ctx context.Context, args map[interface{}]interface{}) (interface{}, error) {
a, err := o.L.Eval(ctx, args)
if err != nil {
return nil, o.traceErr(err)
}
if !types.IsNil(a) {
var x int64
x, err = types.ToBool(a)
if err != nil {
return nil, o.traceErr(err)
} else if x == 0 {
// false && any other types is false
return x, nil
}
}
b, err := o.R.Eval(ctx, args)
if err != nil {
return nil, o.traceErr(err)
}
if !types.IsNil(b) {
var y int64
y, err = types.ToBool(b)
if err != nil {
return nil, o.traceErr(err)
} else if y == 0 {
return y, nil
}
}
// here x and y are all not false
// if a or b is nil
if types.IsNil(a) || types.IsNil(b) {
return nil, nil
}
return int64(1), nil
}
func (e *Evaluator) handleBitOp(o *ast.BinaryOperationExpr) bool {
a, b := types.Coerce(o.L.GetValue(), o.R.GetValue())
if types.IsNil(a) || types.IsNil(b) {
o.SetValue(nil)
return true
}
x, err := types.ToInt64(a)
if err != nil {
e.err = errors.Trace(err)
return false
}
y, err := types.ToInt64(b)
if err != nil {
e.err = errors.Trace(err)
return false
}
// use a int64 for bit operator, return uint64
switch o.Op {
case opcode.And:
o.SetValue(uint64(x & y))
case opcode.Or:
o.SetValue(uint64(x | y))
case opcode.Xor:
o.SetValue(uint64(x ^ y))
case opcode.RightShift:
o.SetValue(uint64(x) >> uint64(y))
case opcode.LeftShift:
o.SetValue(uint64(x) << uint64(y))
default:
e.err = ErrInvalidOperation.Gen("invalid op %v in bit operation", o.Op)
return false
}
return true
}