// UpdateAggFields adds aggregate function resultfield to select result field list. func (s *SelectList) UpdateAggFields(expr expression.Expression, tableFields []*field.ResultField) (expression.Expression, error) { // For aggregate function, the name can be in table or select list. names := expression.MentionedColumns(expr) for _, name := range names { if field.ContainFieldName(name, tableFields, field.DefaultFieldFlag) { continue } if field.ContainFieldName(name, s.ResultFields, field.DefaultFieldFlag) { continue } return nil, errors.Errorf("Unknown column '%s'", name) } // We must add aggregate function to hidden select list // and use a position expression to fetch its value later. exprName := expr.String() idx := field.GetResultFieldIndex(exprName, s.ResultFields, field.CheckFieldFlag) if len(idx) == 0 { f := &field.Field{Expr: expr} resultField := &field.ResultField{Name: exprName} s.AddField(f, resultField) return &expression.Position{N: len(s.Fields), Name: exprName}, nil } // select list has this field, use it directly. return &expression.Position{N: idx[0] + 1, Name: exprName}, nil }
func (r *TableDefaultPlan) filter(ctx context.Context, expr expression.Expression, checkColumns bool) (plan.Plan, bool, error) { if checkColumns { colNames := expression.MentionedColumns(expr) // make sure all mentioned column names are in Fields // if not, e.g. the expr has two table like t1.c1 = t2.c2, we can't use filter if !field.ContainAllFieldNames(colNames, r.Fields) { return r, false, nil } } switch x := expr.(type) { case *expression.BinaryOperation: return r.filterBinOp(ctx, x) case *expression.Ident: return r.filterIdent(ctx, x, true) case *expression.IsNull: return r.filterIsNull(ctx, x) case *expression.UnaryOperation: if x.Op != '!' { break } if operand, ok := x.V.(*expression.Ident); ok { return r.filterIdent(ctx, operand, false) } } return r, false, nil }
// CheckAndUpdateSelectList checks order by fields validity and set hidden fields to selectList. func (r *OrderByRset) CheckAndUpdateSelectList(selectList *plans.SelectList, tableFields []*field.ResultField) error { for i, v := range r.By { if expression.ContainAggregateFunc(v.Expr) { expr, err := selectList.UpdateAggFields(v.Expr, tableFields) if err != nil { return errors.Errorf("%s in 'order clause'", err.Error()) } r.By[i].Expr = expr } else { names := expression.MentionedColumns(v.Expr) for _, name := range names { // try to find in select list // TODO: mysql has confused result for this, see #555. // now we use select list then order by, later we should make it easier. if field.ContainFieldName(name, selectList.ResultFields, field.CheckFieldFlag) { // check ambiguous fields, like `select c1 as c2, c2 from t order by c2`. if err := field.CheckAmbiguousField(name, selectList.ResultFields, field.DefaultFieldFlag); err != nil { return errors.Errorf("Column '%s' in order statement is ambiguous", name) } continue } if !selectList.CloneHiddenField(name, tableFields) { return errors.Errorf("Unknown column '%s' in 'order clause'", name) } } } } return nil }
func (r *TableDefaultPlan) filterIsNull(ctx context.Context, x *expression.IsNull) (plan.Plan, bool, error) { if _, ok := x.Expr.(*expression.Ident); !ok { // if expression is not Ident expression, we cannot use index // e.g, "(x > null) is not null", (x > null) is a binary expression, we must evaluate it first return r, false, nil } cns := expression.MentionedColumns(x.Expr) if len(cns) == 0 { return r, false, nil } cn := cns[0] t := r.T ix := t.FindIndexByColName(cn) if ix == nil { // Column cn has no index. return r, false, nil } var spans []*indexSpan if x.Not { spans = toSpans(opcode.GE, minNotNullVal) } else { spans = toSpans(opcode.EQ, nil) } return &indexPlan{ src: t, colName: cn, idxName: ix.Name.L, idx: ix.X, spans: spans, }, true, nil }
// Plan gets SelectExprPlan. func (r *SelectFromDualRset) Plan(ctx context.Context) (plan.Plan, error) { // field cannot contain identifier for _, f := range r.Fields { if cs := expression.MentionedColumns(f.Expr); len(cs) > 0 { return nil, errors.Errorf("Unknown column '%s' in 'field list'", cs[0]) } } return &plans.SelectFromDualPlan{Fields: r.Fields}, nil }
// Plan get SrcPlan/OrderByDefaultPlan. // If src is NullPlan or order by fields are empty, then gets SrcPlan. // Default gets OrderByDefaultPlan. func (r *OrderByRset) Plan(ctx context.Context) (plan.Plan, error) { if _, ok := r.Src.(*plans.NullPlan); ok { return r.Src, nil } var ( by []expression.Expression ascs []bool ) fields := r.Src.GetFields() for i := range r.By { e := r.By[i].Expr if v, ok := e.(expression.Value); ok { var ( position int isPosition = true ) switch u := v.Val.(type) { case int64: position = int(u) case uint64: position = int(u) default: isPosition = false // only const value } if isPosition { if position < 1 || position > len(fields) { return nil, errors.Errorf("Unknown column '%d' in 'order clause'", position) } // use Position expression for the associated field. r.By[i].Expr = &expression.Position{N: position} } } else { colNames := expression.MentionedColumns(e) if err := field.CheckAllFieldNames(colNames, fields, field.CheckFieldFlag); err != nil { return nil, errors.Trace(err) } } by = append(by, r.By[i].Expr) ascs = append(ascs, r.By[i].Asc) } // if no order by key, use src plan instead. if len(by) == 0 { return r.Src, nil } return &plans.OrderByDefaultPlan{By: by, Ascs: ascs, Src: r.Src, SelectList: r.SelectList}, nil }
// Plan gets SelectExprPlan. func (r *SelectFromDualRset) Plan(ctx context.Context) (plan.Plan, error) { // field cannot contain identifier for _, f := range r.Fields { if cs := expression.MentionedColumns(f.Expr); len(cs) > 0 { // TODO: check in outer query, like select * from t where t.c = (select c limit 1); return nil, errors.Errorf("Unknown column '%s' in 'field list'", cs[0]) } } return &plans.SelectFromDualPlan{Fields: r.Fields}, nil }
// CheckAndUpdateSelectList checks having fields validity and set hidden fields to selectList. func (r *HavingRset) CheckAndUpdateSelectList(selectList *plans.SelectList, groupBy []expression.Expression, tableFields []*field.ResultField) error { if expression.ContainAggregateFunc(r.Expr) { expr, err := selectList.UpdateAggFields(r.Expr, tableFields) if err != nil { return errors.Errorf("%s in 'having clause'", err.Error()) } r.Expr = expr } else { // having can only contain group by column and select list, e.g, // `select c1 from t group by c2 having c3 > 0` is invalid, // because c3 is not in group by and select list. names := expression.MentionedColumns(r.Expr) for _, name := range names { found := false // check name whether in select list. // notice that `select c1 as c2 from t group by c1, c2, c3 having c2 > c3`, // will use t.c2 not t.c1 here. if field.ContainFieldName(name, selectList.ResultFields, field.OrgFieldNameFlag) { continue } if field.ContainFieldName(name, selectList.ResultFields, field.FieldNameFlag) { if field.ContainFieldName(name, tableFields, field.OrgFieldNameFlag) { selectList.CloneHiddenField(name, tableFields) } continue } // check name whether in group by. // group by must only have column name, e.g, // `select c1 from t group by c2 having c2 > 0` is valid, // but `select c1 from t group by c2 + 1 having c2 > 0` is invalid. for _, by := range groupBy { if !field.CheckFieldsEqual(name, by.String()) { continue } // if name is not in table fields, it will get an unknown field error in GroupByRset, // so no need to check return value. selectList.CloneHiddenField(name, tableFields) found = true break } if !found { return errors.Errorf("Unknown column '%s' in 'having clause'", name) } } } return nil }
func (r *TableDefaultPlan) filterIsNull(ctx context.Context, x *expression.IsNull) (plan.Plan, bool, error) { if _, ok := x.Expr.(*expression.Ident); !ok { // if expression is not Ident expression, we cannot use index // e.g, "(x > null) is not null", (x > null) is a binary expression, we must evaluate it first return r, false, nil } cns := expression.MentionedColumns(x.Expr) if len(cns) == 0 { return r, false, nil } cn := cns[0] t := r.T col := column.FindCol(t.Cols(), cn) if col == nil { return r, false, nil } if col.IsPKHandleColumn(t.Meta()) { if x.Not { // PK handle column never be null. return r, false, nil } return &NullPlan{ Fields: r.Fields, }, true, nil } else if r.rangeScan { return r, false, nil } var spans []*indexSpan if x.Not { spans = toSpans(opcode.GE, minNotNullVal, nil) } else { spans = toSpans(opcode.EQ, nil, nil) } ix := t.FindIndexByColName(cn) if ix == nil { // Column cn has no index. return r, false, nil } return &indexPlan{ src: t, col: col, unique: ix.Unique, idxName: ix.Name.L, idx: ix.X, spans: spans, }, true, nil }
func (r *WhereRset) planIsNull(ctx context.Context, x *expression.IsNull) (plan.Plan, error) { p := r.Src cns := expression.MentionedColumns(x.Expr) if len(cns) == 0 { return &plans.FilterDefaultPlan{Plan: p, Expr: x}, nil } p2, filtered, err := p.Filter(ctx, x) if err != nil { return nil, err } if filtered { return p2, nil } return &plans.FilterDefaultPlan{Plan: p, Expr: x}, nil }
// CheckAndUpdateSelectList checks order by fields validity and set hidden fields to selectList. func (r *OrderByRset) CheckAndUpdateSelectList(selectList *plans.SelectList, tableFields []*field.ResultField) error { for i, v := range r.By { if expression.ContainAggregateFunc(v.Expr) { expr, err := selectList.UpdateAggFields(v.Expr, tableFields) if err != nil { return errors.Errorf("%s in 'order clause'", err.Error()) } r.By[i].Expr = expr } else { if _, err := selectList.CheckReferAmbiguous(v.Expr); err != nil { return errors.Errorf("Column '%s' in order statement is ambiguous", v.Expr) } // TODO: check more ambiguous case // Order by ambiguous rule: // select c1 as a, c2 as a from t order by a is ambiguous // select c1 as a, c2 as a from t order by a + 1 is ambiguous // select c1 as c2, c2 from t order by c2 is ambiguous // select c1 as c2, c2 from t order by c2 + 1 is ambiguous // TODO: use vistor to refactor all and combine following plan check. names := expression.MentionedColumns(v.Expr) for _, name := range names { // try to find in select list // TODO: mysql has confused result for this, see #555. // now we use select list then order by, later we should make it easier. if field.ContainFieldName(name, selectList.ResultFields, field.CheckFieldFlag) { continue } if !selectList.CloneHiddenField(name, tableFields) { return errors.Errorf("Unknown column '%s' in 'order clause'", name) } } } } return nil }
// ResolveSelectList gets fields and result fields from selectFields and srcFields, // including field validity check and wildcard field processing. func ResolveSelectList(selectFields []*field.Field, srcFields []*field.ResultField) (*SelectList, error) { selectList := &SelectList{ Fields: make([]*field.Field, 0, len(selectFields)), ResultFields: make([]*field.ResultField, 0, len(selectFields)), AggFields: make(map[int]struct{}), FromFields: srcFields, } wildcardNum := 0 for _, v := range selectFields { // Check metioned field. names := expression.MentionedColumns(v.Expr) if len(names) == 0 { selectList.AddField(v, nil) continue } // Check wildcard field. name := names[0] table, ok, err := field.CheckWildcardField(name) if err != nil { return nil, err } if ok { // Check unqualified wildcard field number, // like `select *, * from t`. if table == "" { wildcardNum++ if wildcardNum > 1 { return nil, errors.Errorf("wildcard field exist more than once") } } selectList.updateFields(table, srcFields) continue } var result *field.ResultField for _, name := range names { idx := field.GetResultFieldIndex(name, srcFields, field.DefaultFieldFlag) if len(idx) > 1 { return nil, errors.Errorf("ambiguous field %s", name) } // TODO: must check in outer query too. if len(idx) == 0 { return nil, errors.Errorf("unknown field %s", name) } } if _, ok := v.Expr.(*expression.Ident); ok { // Field is ident. if result, err = field.CloneFieldByName(name, srcFields, field.DefaultFieldFlag); err != nil { return nil, errors.Trace(err) } // Use alias name if len(v.AsName) > 0 { result.Name = v.AsName } else { // use field identifier name directly, but not contain qualified name. // e.g, select t.c will only return c as the column name. s := v.Expr.String() n := strings.LastIndexByte(s, '.') if n == -1 { result.Name = s } else { result.Name = s[n+1:] } } } selectList.AddField(v, result) } selectList.HiddenFieldOffset = len(selectList.Fields) selectList.resolveAggFields() if selectList.HiddenFieldOffset == 0 { return nil, errors.Errorf("invalid empty select fields") } return selectList, nil }
// Plan get SrcPlan/OrderByDefaultPlan. // If src is NullPlan or order by fields are empty, then gets SrcPlan. // Default gets OrderByDefaultPlan. func (r *OrderByRset) Plan(ctx context.Context) (plan.Plan, error) { if _, ok := r.Src.(*plans.NullPlan); ok { return r.Src, nil } var ( by []expression.Expression ascs []bool ) fields := r.Src.GetFields() for i := range r.By { e := r.By[i].Expr if v, ok := e.(expression.Value); ok { var ( position int isPosition = true ) switch u := v.Val.(type) { case int64: position = int(u) case uint64: position = int(u) default: isPosition = false // only const value } if isPosition { if position < 1 || position > len(fields) { return nil, errors.Errorf("Unknown column '%d' in 'order clause'", position) } // use Position expression for the associated field. r.By[i].Expr = &expression.Position{N: position} } } else { // Don't check ambiguous here, only check field exists or not. // TODO: use visitor to refactor. colNames := expression.MentionedColumns(e) for _, name := range colNames { if idx := field.GetResultFieldIndex(name, r.SelectList.ResultFields, field.DefaultFieldFlag); len(idx) == 0 { // find in from if idx = field.GetResultFieldIndex(name, r.SelectList.FromFields, field.DefaultFieldFlag); len(idx) == 0 { return nil, errors.Errorf("unknown field %s", name) } } } } by = append(by, r.By[i].Expr) ascs = append(ascs, r.By[i].Asc) } // if no order by key, use src plan instead. if len(by) == 0 { return r.Src, nil } return &plans.OrderByDefaultPlan{By: by, Ascs: ascs, Src: r.Src, SelectList: r.SelectList}, nil }
// ResolveSelectList gets fields and result fields from selectFields and srcFields, // including field validity check and wildcard field processing. func ResolveSelectList(selectFields []*field.Field, srcFields []*field.ResultField) (*SelectList, error) { selectList := &SelectList{ Fields: make([]*field.Field, 0, len(selectFields)), ResultFields: make([]*field.ResultField, 0, len(selectFields)), AggFields: make(map[int]struct{}), FromFields: srcFields, } wildcardNum := 0 for _, v := range selectFields { // Check metioned field. names := expression.MentionedColumns(v.Expr) if len(names) == 0 { selectList.AddField(v, nil) continue } // Check wildcard field. name := names[0] table, ok, err := field.CheckWildcardField(name) if err != nil { return nil, err } if ok { // Check unqualified wildcard field number, // like `select *, * from t`. if table == "" { wildcardNum++ if wildcardNum > 1 { return nil, errors.Errorf("wildcard field exist more than once") } } selectList.updateFields(table, srcFields) continue } var result *field.ResultField if err = field.CheckAllFieldNames(names, srcFields, field.DefaultFieldFlag); err != nil { return nil, errors.Trace(err) } if _, ok := v.Expr.(*expression.Ident); ok { // Field is ident. if result, err = field.CloneFieldByName(name, srcFields, field.DefaultFieldFlag); err != nil { return nil, errors.Trace(err) } // Maybe alias name or only column name. if !expression.IsQualified(v.Name) { result.Name = v.Name } } else { // The field is not an ident, maybe binary expression, // like `select c1 + c2`, or `select c1 + 10`, etc. result = &field.ResultField{Name: v.Name} } selectList.AddField(v, result) } selectList.HiddenFieldOffset = len(selectList.Fields) selectList.resolveAggFields() if selectList.HiddenFieldOffset == 0 { return nil, errors.Errorf("invalid empty select fields") } return selectList, nil }
// Plan gets GroupByDefaultPlan. func (r *GroupByRset) Plan(ctx context.Context) (plan.Plan, error) { fields := r.SelectList.Fields r.SelectList.AggFields = GetAggFields(fields) aggFields := r.SelectList.AggFields for i, e := range r.By { if v, ok := e.(expression.Value); ok { var position int switch u := v.Val.(type) { case int64: position = int(u) case uint64: position = int(u) default: continue } if position < 1 || position > len(fields) { return nil, errors.Errorf("Unknown column '%d' in 'group statement'", position) } index := position - 1 if _, ok := aggFields[index]; ok { return nil, errors.Errorf("Can't group on '%s'", fields[index]) } // use Position expression for the associated field. r.By[i] = &expression.Position{N: position} } else { index, err := r.SelectList.CheckReferAmbiguous(e) if err != nil { return nil, errors.Errorf("Column '%s' in group statement is ambiguous", e) } else if _, ok := aggFields[index]; ok { return nil, errors.Errorf("Can't group on '%s'", e) } // TODO: check more ambiguous case // Group by ambiguous rule: // select c1 as a, c2 as a from t group by a is ambiguous // select c1 as a, c2 as a from t group by a + 1 is ambiguous // select c1 as c2, c2 from t group by c2 is ambiguous // select c1 as c2, c2 from t group by c2 + 1 is ambiguous // TODO: use visitor to check aggregate function names := expression.MentionedColumns(e) for _, name := range names { indices := field.GetFieldIndex(name, fields[0:r.SelectList.HiddenFieldOffset], field.DefaultFieldFlag) if len(indices) == 1 { // check reference to aggregate function, like `select c1, count(c1) as b from t group by b + 1`. index := indices[0] if _, ok := aggFields[index]; ok { return nil, errors.Errorf("Reference '%s' not supported (reference to group function)", name) } } } // group by should be an expression, a qualified field name or a select field position, // but can not contain any aggregate function. if e := r.By[i]; expression.ContainAggregateFunc(e) { return nil, errors.Errorf("group by cannot contain aggregate function %s", e.String()) } } } return &plans.GroupByDefaultPlan{By: r.By, Src: r.Src, SelectList: r.SelectList}, nil }
// Plan gets GroupByDefaultPlan. func (r *GroupByRset) Plan(ctx context.Context) (plan.Plan, error) { fields := r.SelectList.Fields resultfields := r.SelectList.ResultFields srcFields := r.Src.GetFields() r.SelectList.AggFields = GetAggFields(fields) aggFields := r.SelectList.AggFields for i, e := range r.By { if v, ok := e.(expression.Value); ok { var position int switch u := v.Val.(type) { case int64: position = int(u) case uint64: position = int(u) default: continue } if position < 1 || position > len(fields) { return nil, errors.Errorf("Unknown column '%d' in 'group statement'", position) } index := position - 1 if _, ok := aggFields[index]; ok { return nil, errors.Errorf("Can't group on '%s'", fields[index].Name) } // use Position expression for the associated field. r.By[i] = &expression.Position{N: position} } else { names := expression.MentionedColumns(e) for _, name := range names { if field.ContainFieldName(name, srcFields, field.DefaultFieldFlag) { // check whether column is qualified, like `select t.c1 c1, t.c2 from t group by t.c1, t.c2` // no need to check ambiguous field. if expression.IsQualified(name) { continue } // check ambiguous fields, like `select c1 as c2, c2 from t group by c2`. if err := field.CheckAmbiguousField(name, resultfields, field.DefaultFieldFlag); err == nil { continue } } // check reference to group function name indices := field.GetFieldIndex(name, fields[0:r.SelectList.HiddenFieldOffset], field.CheckFieldFlag) if len(indices) > 1 { // check ambiguous fields, like `select c1 as a, c2 as a from t group by a`, // notice that `select c2 as c2, c2 as c2 from t group by c2;` is valid. if r.HasAmbiguousField(indices, fields[0:r.SelectList.HiddenFieldOffset]) { return nil, errors.Errorf("Column '%s' in group statement is ambiguous", name) } } else if len(indices) == 1 { // check reference to aggregate function, like `select c1, count(c1) as b from t group by b + 1`. index := indices[0] if _, ok := aggFields[index]; ok { return nil, errors.Errorf("Reference '%s' not supported (reference to group function)", name) } } } // group by should be an expression, a qualified field name or a select field position, // but can not contain any aggregate function. if e := r.By[i]; expression.ContainAggregateFunc(e) { return nil, errors.Errorf("group by cannot contain aggregate function %s", e.String()) } } } return &plans.GroupByDefaultPlan{By: r.By, Src: r.Src, SelectList: r.SelectList}, nil }