func (s *testEvaluatorSuite) TestEvaluatedFlag(c *C) { l := ast.NewValueExpr(int64(1)) r := ast.NewValueExpr(int64(2)) b := &ast.BinaryOperationExpr{L: l, R: r, Op: opcode.Plus} ast.SetFlag(b) c.Assert(ast.IsPreEvaluable(b), Equals, true) d, err := Eval(s.ctx, b) c.Assert(ast.IsEvaluated(b), Equals, true) c.Assert(err, IsNil) c.Assert(d, testutil.DatumEquals, types.NewIntDatum(3)) funcCall := &ast.FuncCallExpr{ FnName: model.NewCIStr("abs"), Args: []ast.ExprNode{ast.NewValueExpr(int(-1))}, } b = &ast.BinaryOperationExpr{L: funcCall, R: r, Op: opcode.Plus} ast.ResetEvaluatedFlag(b) ast.SetFlag(b) c.Assert(ast.IsPreEvaluable(b), Equals, true) d, err = Eval(s.ctx, b) c.Assert(ast.IsEvaluated(b), Equals, false) c.Assert(err, IsNil) c.Assert(d, testutil.DatumEquals, types.NewIntDatum(3)) rf := &ast.ResultField{Expr: ast.NewValueExpr(int64(1))} colExpr := &ast.ColumnNameExpr{Refer: rf} b = &ast.BinaryOperationExpr{L: colExpr, R: r, Op: opcode.Plus} ast.ResetEvaluatedFlag(b) ast.SetFlag(b) c.Assert(ast.IsPreEvaluable(b), Equals, false) d, err = Eval(s.ctx, b) c.Assert(ast.IsEvaluated(b), Equals, false) c.Assert(err, IsNil) c.Assert(d, testutil.DatumEquals, types.NewIntDatum(3)) }
func (s *testPlanSuite) TestMultiColumnIndex(c *C) { defer testleak.AfterTest(c)() cases := []struct { sql string accessEqualCount int usedColumnCount int }{ {"select * from t where c = 0 and d = 0 and e = 0", 3, 3}, {"select * from t where c = 0 and d = 0 and e > 0", 2, 3}, {"select * from t where d > 0 and e = 0 and c = 0", 1, 2}, } for _, ca := range cases { comment := Commentf("for %s", ca.sql) s, err := s.ParseOneStmt(ca.sql, "", "") c.Assert(err, IsNil, comment) stmt := s.(*ast.SelectStmt) ast.SetFlag(stmt) mockResolve(stmt) b := &planBuilder{} p := b.buildFrom(stmt) err = Refine(p) c.Assert(err, IsNil) idxScan, ok := p.(*IndexScan) c.Assert(ok, IsTrue) c.Assert(idxScan.AccessEqualCount, Equals, ca.accessEqualCount) c.Assert(idxScan.Ranges[0].LowVal, HasLen, ca.usedColumnCount) } }
func (s *testEvaluatorSuite) TestCast(c *C) { defer testleak.AfterTest(c)() f := types.NewFieldType(mysql.TypeLonglong) expr := &ast.FuncCastExpr{ Expr: ast.NewValueExpr(1), Tp: f, } ast.SetFlag(expr) v, err := Eval(s.ctx, expr) c.Assert(err, IsNil) c.Assert(v, testutil.DatumEquals, types.NewDatum(int64(1))) f.Flag |= mysql.UnsignedFlag v, err = Eval(s.ctx, expr) c.Assert(err, IsNil) c.Assert(v, testutil.DatumEquals, types.NewDatum(uint64(1))) f.Tp = mysql.TypeString f.Charset = charset.CharsetBin v, err = Eval(s.ctx, expr) c.Assert(err, IsNil) c.Assert(v, testutil.DatumEquals, types.NewDatum([]byte("1"))) f.Tp = mysql.TypeString f.Charset = "utf8" v, err = Eval(s.ctx, expr) c.Assert(err, IsNil) c.Assert(v, testutil.DatumEquals, types.NewDatum("1")) expr.Expr = ast.NewValueExpr(nil) v, err = Eval(s.ctx, expr) c.Assert(err, IsNil) c.Assert(v.Kind(), Equals, types.KindNull) }
// Parse parses a query string to raw ast.StmtNode. // If charset or collation is "", default charset and collation will be used. func (parser *Parser) Parse(sql, charset, collation string) ([]ast.StmtNode, error) { if charset == "" { charset = mysql.DefaultCharset } if collation == "" { collation = mysql.DefaultCollationName } parser.charset = charset parser.collation = collation parser.src = sql parser.result = parser.result[:0] sql = handleMySQLSpecificCode(sql) var l yyLexer parser.lexer.reset(sql) l = &parser.lexer yyParse(l, parser) if len(l.Errors()) != 0 { return nil, errors.Trace(l.Errors()[0]) } for _, stmt := range parser.result { ast.SetFlag(stmt) } return parser.result, nil }
// Compile compiles an ast.StmtNode to a stmt.Statement. // If it is supported to use new plan and executer, it optimizes the node to // a plan, and we wrap the plan in an adapter as stmt.Statement. // If it is not supported, the node will be converted to old statement. func (c *Compiler) Compile(ctx context.Context, node ast.StmtNode) (stmt.Statement, error) { if optimizer.IsSupported(node) { ast.SetFlag(node) is := sessionctx.GetDomain(ctx).InfoSchema() if err := optimizer.Preprocess(node, is, ctx); err != nil { return nil, errors.Trace(err) } // Validate should be after NameResolve. if err := optimizer.Validate(node, false); err != nil { return nil, errors.Trace(err) } sb := NewSubQueryBuilder(is) p, err := optimizer.Optimize(ctx, node, sb) if err != nil { return nil, errors.Trace(err) } sa := &statementAdapter{ is: is, plan: p, } return sa, nil } c.converter = &converter.Converter{} s, err := c.converter.Convert(node) if err != nil { return nil, errors.Trace(err) } return s, nil }
func (b *planBuilder) buildTablePlanFromJoinPath(path *joinPath) Plan { for _, equiv := range path.eqConds { columnNameExpr := &ast.ColumnNameExpr{} columnNameExpr.Name = &ast.ColumnName{} columnNameExpr.Name.Name = equiv.left.Column.Name columnNameExpr.Name.Table = equiv.left.Table.Name columnNameExpr.Refer = equiv.left condition := &ast.BinaryOperationExpr{L: columnNameExpr, R: equiv.right.Expr, Op: opcode.EQ} ast.SetFlag(condition) path.conditions = append(path.conditions, condition) } candidates := b.buildAllAccessMethodsPlan(path.table, path.conditions) var p Plan var lowestCost float64 for _, can := range candidates { cost := EstimateCost(can) if p == nil { p = can lowestCost = cost } if cost < lowestCost { p = can lowestCost = cost } } return p }
func (s *testPlanSuite) TestTableScanWithOrder(c *C) { defer testleak.AfterTest(c)() // Sort result by scanning PKHandle column. sql := "select * from t order by a limit 1;" stmt, err := s.ParseOneStmt(sql, "", "") c.Assert(err, IsNil) ast.SetFlag(stmt) err = newMockResolve(stmt) c.Assert(err, IsNil) builder := &planBuilder{ allocator: new(idAllocator), ctx: mock.NewContext(), colMapper: make(map[*ast.ColumnNameExpr]int), } p := builder.build(stmt) c.Assert(builder.err, IsNil) logic, ok := p.(LogicalPlan) c.Assert(ok, IsTrue) // Get physical plan. _, pp, _, err := logic.convert2PhysicalPlan(nil) c.Assert(err, IsNil) // Limit->Projection->PhysicalTableScan // Get PhysicalTableScan plan. cpp, ok := pp.p.GetChildByIndex(0).GetChildByIndex(0).(*PhysicalTableScan) c.Assert(cpp, NotNil) c.Assert(ok, IsTrue) // Make sure KeepOrder is true. c.Assert(cpp.KeepOrder, IsTrue) }
// Compile compiles an ast.StmtNode to a stmt.Statement. // If it is supported to use new plan and executer, it optimizes the node to // a plan, and we wrap the plan in an adapter as stmt.Statement. // If it is not supported, the node will be converted to old statement. func (c *Compiler) Compile(ctx context.Context, node ast.StmtNode) (stmt.Statement, error) { if optimizer.IsSupported(node) { ast.SetFlag(node) if err := optimizer.Validate(node, false); err != nil { return nil, errors.Trace(err) } is := sessionctx.GetDomain(ctx).InfoSchema() if err := optimizer.ResolveName(node, is, ctx); err != nil { return nil, errors.Trace(err) } p, err := optimizer.Optimize(ctx, node) if err != nil { return nil, errors.Trace(err) } sa := &statementAdapter{ is: is, plan: p, } return sa, nil } c.converter = &converter.Converter{} s, err := c.converter.Convert(node) if err != nil { return nil, errors.Trace(err) } return s, nil }
// Compile compiles an ast.StmtNode to a stmt.Statement. // If it is supported to use new plan and executer, it optimizes the node to // a plan, and we wrap the plan in an adapter as stmt.Statement. // If it is not supported, the node will be converted to old statement. func (c *Compiler) Compile(ctx context.Context, node ast.StmtNode) (ast.Statement, error) { ast.SetFlag(node) if _, ok := node.(*ast.UpdateStmt); ok { sVars := variable.GetSessionVars(ctx) sVars.InUpdateStmt = true defer func() { sVars.InUpdateStmt = false }() } is := sessionctx.GetDomain(ctx).InfoSchema() if err := plan.Preprocess(node, is, ctx); err != nil { return nil, errors.Trace(err) } // Validate should be after NameResolve. if err := plan.Validate(node, false); err != nil { return nil, errors.Trace(err) } sb := NewSubQueryBuilder(is) p, err := plan.Optimize(ctx, node, sb, is) if err != nil { return nil, errors.Trace(err) } _, isDDL := node.(ast.DDLNode) sa := &statement{ is: is, plan: p, text: node.Text(), isDDL: isDDL, } return sa, nil }
func (s *testPlanSuite) TestVisitCount(c *C) { sqls := []string{ "select t1.c1, t2.c2 from t1, t2", "select * from t1 left join t2 on t1.c1 = t2.c1", "select * from t1 group by t1.c1 having sum(t1.c2) = 1", "select * from t1 where t1.c1 > 2 order by t1.c2 limit 100", "insert t1 values (1), (2)", "delete from t1 where false", "truncate table t1", "do 1", "show databases", } for _, sql := range sqls { stmt, err := parser.ParseOneStmt(sql, "", "") c.Assert(err, IsNil, Commentf(sql)) ast.SetFlag(stmt) mockJoinResolve(c, stmt) b := &planBuilder{} p := b.build(stmt) c.Assert(b.err, IsNil) visitor := &countVisitor{} for i := 0; i < 5; i++ { visitor.skipAt = i visitor.enterCount = 0 visitor.leaveCount = 0 p.Accept(visitor) c.Assert(visitor.enterCount, Equals, visitor.leaveCount, Commentf(sql)) } } }
func (s *testPlanSuite) TestJoinReOrder(c *C) { defer testleak.AfterTest(c)() cases := []struct { sql string best string }{ { sql: "select * from t t1, t t2, t t3, t t4, t t5, t t6 where t1.a = t2.b and t2.a = t3.b and t3.c = t4.a and t4.d = t2.c and t5.d = t6.d", best: "LeftHashJoin{LeftHashJoin{LeftHashJoin{LeftHashJoin{Table(t)->Table(t)}(t1.a,t2.b)->Table(t)}(t2.a,t3.b)->Table(t)}(t3.c,t4.a)(t2.c,t4.d)->LeftHashJoin{Table(t)->Table(t)}(t5.d,t6.d)}->Projection", }, { sql: "select * from t t1, t t2, t t3, t t4, t t5, t t6, t t7, t t8 where t1.a = t8.a", best: "LeftHashJoin{LeftHashJoin{LeftHashJoin{LeftHashJoin{Table(t)->Table(t)}(t1.a,t8.a)->Table(t)}->LeftHashJoin{Table(t)->Table(t)}}->LeftHashJoin{LeftHashJoin{Table(t)->Table(t)}->Table(t)}}->Projection", }, { sql: "select * from t t1, t t2, t t3, t t4, t t5 where t1.a = t5.a and t5.a = t4.a and t4.a = t3.a and t3.a = t2.a and t2.a = t1.a and t1.a = t3.a and t2.a = t4.a and t5.b < 8", best: "LeftHashJoin{LeftHashJoin{LeftHashJoin{RightHashJoin{Table(t)->Selection->Table(t)}(t5.a,t1.a)->Table(t)}(t1.a,t2.a)->Table(t)}(t2.a,t3.a)(t1.a,t3.a)->Table(t)}(t5.a,t4.a)(t3.a,t4.a)(t2.a,t4.a)->Projection", }, { sql: "select * from t t1, t t2, t t3, t t4, t t5 where t1.a = t5.a and t5.a = t4.a and t4.a = t3.a and t3.a = t2.a and t2.a = t1.a and t1.a = t3.a and t2.a = t4.a and t3.b = 1 and t4.a = 1", best: "LeftHashJoin{LeftHashJoin{LeftHashJoin{Table(t)->Selection->Table(t)}->LeftHashJoin{Table(t)->Table(t)}}->Table(t)}->Projection", }, { sql: "select * from t o where o.b in (select t3.c from t t1, t t2, t t3 where t1.a = t3.a and t2.a = t3.a and t2.a = o.a)", best: "Table(t)->Apply(LeftHashJoin{RightHashJoin{Table(t)->Selection->Table(t)}(t2.a,t3.a)->Table(t)}(t3.a,t1.a)->Projection)->Selection->Projection", }, { sql: "select * from t o where o.b in (select t3.c from t t1, t t2, t t3 where t1.a = t3.a and t2.a = t3.a and t2.a = o.a and t1.a = 1)", best: "Table(t)->Apply(LeftHashJoin{LeftHashJoin{Table(t)->Table(t)}->Table(t)->Selection}->Projection)->Selection->Projection", }, } for _, ca := range cases { comment := Commentf("for %s", ca.sql) stmt, err := s.ParseOneStmt(ca.sql, "", "") c.Assert(err, IsNil, comment) ast.SetFlag(stmt) err = mockResolve(stmt) c.Assert(err, IsNil) builder := &planBuilder{ allocator: new(idAllocator), ctx: mock.NewContext(), colMapper: make(map[*ast.ColumnNameExpr]int), } p := builder.build(stmt) c.Assert(builder.err, IsNil) lp := p.(LogicalPlan) _, lp, err = lp.PredicatePushDown(nil) c.Assert(err, IsNil) _, err = lp.PruneColumnsAndResolveIndices(lp.GetSchema()) c.Assert(err, IsNil) _, res, _, err := lp.convert2PhysicalPlan(nil) c.Assert(err, IsNil) p = res.p.PushLimit(nil) c.Assert(ToString(p), Equals, ca.best, Commentf("for %s", ca.sql)) } }
func (s *testPlanSuite) TestRefine(c *C) { UseNewPlanner = true defer testleak.AfterTest(c)() cases := []struct { sql string best string }{ { sql: "select a from t where c = 4 and d = 5 and e = 6", best: "Index(t.c_d_e)[[4 5 6,4 5 6]]->Selection->Projection", }, { sql: "select a from t where d = 4 and c = 5", best: "Index(t.c_d_e)[[5 4,5 4]]->Selection->Projection", }, { sql: "select a from t where c = 4 and e < 5", best: "Index(t.c_d_e)[[4,4]]->Selection->Projection", }, { sql: "select a from t where c = 4 and d <= 5 and d > 3", best: "Index(t.c_d_e)[[4 3,4 5]]->Selection->Projection", }, { sql: "select a from t where d <= 5 and d > 3", best: "Index(t.c_d_e)[[<nil>,<nil>]]->Selection->Projection", }, { sql: "select a from t where c <= 5 and c >= 3 and d = 1", best: "Index(t.c_d_e)[[3,5]]->Selection->Projection", }, } for _, ca := range cases { comment := Commentf("for %s", ca.sql) stmt, err := s.ParseOneStmt(ca.sql, "", "") c.Assert(err, IsNil, comment) ast.SetFlag(stmt) err = newMockResolve(stmt) c.Assert(err, IsNil) builder := &planBuilder{ allocator: new(idAllocator), } p := builder.build(stmt).(LogicalPlan) c.Assert(builder.err, IsNil) _, p, err = p.PredicatePushDown(nil) c.Assert(err, IsNil) _, err = p.PruneColumnsAndResolveIndices(p.GetSchema()) c.Assert(err, IsNil) np := p.Convert2PhysicalPlan() err = refine(np) c.Assert(err, IsNil) c.Assert(ToString(np), Equals, ca.best, Commentf("for %s", ca.sql)) } UseNewPlanner = false }
// Prepare prepares a raw statement parsed from parser. // The statement must be prepared before it can be passed to optimize function. // We pass InfoSchema instead of getting from Context in case it is changed after resolving name. func Prepare(is infoschema.InfoSchema, ctx context.Context, node ast.Node) error { ast.SetFlag(node) if err := Preprocess(node, is, ctx); err != nil { return errors.Trace(err) } if err := Validate(node, true); err != nil { return errors.Trace(err) } return nil }
// ParseOneStmt parses a query and returns an ast.StmtNode. // The query must have one statement, otherwise ErrSyntax is returned. func (parser *Parser) ParseOneStmt(sql, charset, collation string) (ast.StmtNode, error) { stmts, err := parser.Parse(sql, charset, collation) if err != nil { return nil, errors.Trace(err) } if len(stmts) != 1 { return nil, ErrSyntax } ast.SetFlag(stmts[0]) return stmts[0], nil }
// Prepare prepares a raw statement parsed from parser. // The statement must be prepared before it can be passed to optimize function. // We pass InfoSchema instead of getting from Context in case it is changed after resolving name. func Prepare(is infoschema.InfoSchema, ctx context.Context, node ast.Node) error { if err := Validate(node, true); err != nil { return errors.Trace(err) } ast.SetFlag(node) if err := ResolveName(node, is, ctx); err != nil { return errors.Trace(err) } if err := InferType(node); err != nil { return errors.Trace(err) } return nil }
func (s *testPlanSuite) TestIndexHint(c *C) { defer testleak.AfterTest(c)() cases := []struct { sql string explain string }{ { "select * from t1 force index (i1) where t1.i1 > 0 and t1.i2 = 0", "Index(t1.i1)->Fields", }, { "select * from t1 use index (i1) where t1.i1 > 0 and t1.i2 = 0", "Index(t1.i1)->Fields", }, { "select * from t1 ignore index (i2) where t1.i1 > 0 and t1.i2 = 0", "Index(t1.i1)->Fields", }, { "select * from t1 use index (i1, i2) where t1.i1 > 0 and t1.i2 between 0 and 2 and t1.i3 = 0", "Index(t1.i2)->Fields", }, { "select * from t1 ignore index (i1, i2, i3) where t1.i1 = 0 and t1.i2 = 0 and t1.i3 = 0", "Table(t1)->Fields", }, { "select * from t1 use index () where t1.i1 = 0 and t1.i2 = 0 and t1.i3 = 0", "Table(t1)->Fields", }, { "select * from t1 use index (i1) ignore index (i1) where t1.i1 = 0", "Table(t1)->Fields", }, } for _, ca := range cases { comment := Commentf("for %s", ca.sql) s, err := s.ParseOneStmt(ca.sql, "", "") c.Assert(err, IsNil, comment) stmt := s.(*ast.SelectStmt) mockJoinResolve(c, stmt) ast.SetFlag(stmt) p, err := BuildPlan(stmt, nil) c.Assert(err, IsNil) c.Assert(ToString(p), Equals, ca.explain, comment) } }
func (s *testEvaluatorSuite) TestColumnNameExpr(c *C) { defer testleak.AfterTest(c)() value1 := ast.NewValueExpr(1) rf := &ast.ResultField{Expr: value1} expr := &ast.ColumnNameExpr{Refer: rf} ast.SetFlag(expr) result, err := Eval(s.ctx, expr) c.Assert(err, IsNil) c.Assert(result, testutil.DatumEquals, types.NewDatum(int64(1))) value2 := ast.NewValueExpr(2) rf.Expr = value2 result, err = Eval(s.ctx, expr) c.Assert(err, IsNil) c.Assert(result, testutil.DatumEquals, types.NewDatum(int64(2))) }
func (b *planBuilder) buildPlanFromJoinPath(path *joinPath) Plan { if path.table != nil { return b.buildTablePlanFromJoinPath(path) } if path.subquery != nil { return b.buildSubqueryJoinPath(path) } if path.outer != nil { join := &JoinOuter{ Outer: b.buildPlanFromJoinPath(path.outer), Inner: b.buildPlanFromJoinPath(path.inner), } addChild(join, join.Outer) addChild(join, join.Inner) if path.rightJoin { join.SetFields(append(join.Inner.Fields(), join.Outer.Fields()...)) } else { join.SetFields(append(join.Outer.Fields(), join.Inner.Fields()...)) } return join } join := &JoinInner{} for _, in := range path.inners { inPlan := b.buildPlanFromJoinPath(in) join.Inners = append(join.Inners, inPlan) join.fields = append(join.fields, in.resultFields()...) addChild(join, inPlan) } join.Conditions = path.conditions for _, equiv := range path.eqConds { columnNameExpr := &ast.ColumnNameExpr{} columnNameExpr.Name = &ast.ColumnName{} columnNameExpr.Name.Name = equiv.left.Column.Name columnNameExpr.Name.Table = equiv.left.Table.Name columnNameExpr.Refer = equiv.left ast.SetFlag(columnNameExpr) cond := &ast.BinaryOperationExpr{L: columnNameExpr, R: equiv.right.Expr, Op: opcode.EQ} ast.MergeChildrenFlags(cond, columnNameExpr, equiv.right.Expr) join.Conditions = append(join.Conditions, cond) } return join }
// Build subquery join path plan func (b *planBuilder) buildSubqueryJoinPath(path *joinPath) Plan { for _, equiv := range path.eqConds { columnNameExpr := &ast.ColumnNameExpr{} columnNameExpr.Name = &ast.ColumnName{} columnNameExpr.Name.Name = equiv.left.Column.Name columnNameExpr.Name.Table = equiv.left.Table.Name columnNameExpr.Refer = equiv.left condition := &ast.BinaryOperationExpr{L: columnNameExpr, R: equiv.right.Expr, Op: opcode.EQ} ast.SetFlag(condition) path.conditions = append(path.conditions, condition) } p := b.build(path.subquery) if len(path.conditions) == 0 { return p } filterPlan := &Filter{Conditions: path.conditions} filterPlan.SetSrc(p) filterPlan.SetFields(p.Fields()) return filterPlan }
func (s *testEvaluatorSuite) TestAggFuncAvg(c *C) { defer testleak.AfterTest(c)() ctx := mock.NewContext() avg := &ast.AggregateFuncExpr{ F: ast.AggFuncAvg, } avg.CurrentGroup = []byte("emptyGroup") ast.SetFlag(avg) result, err := Eval(ctx, avg) c.Assert(err, IsNil) // Empty group should return nil. c.Assert(result.Kind(), Equals, types.KindNull) avg.Args = []ast.ExprNode{ast.NewValueExpr(2)} avg.Update() avg.Args = []ast.ExprNode{ast.NewValueExpr(4)} avg.Update() result, err = Eval(ctx, avg) c.Assert(err, IsNil) expect := mysql.NewDecFromInt(3) c.Assert(result.Kind(), Equals, types.KindMysqlDecimal) c.Assert(result.GetMysqlDecimal().Compare(expect), Equals, 0) }
func (s *testEvaluatorSuite) TestDateArith(c *C) { defer testleak.AfterTest(c)() ctx := mock.NewContext() // list all test cases tests := []struct { Date interface{} Interval interface{} Unit string AddResult interface{} SubResult interface{} error bool }{ // basic test {"2011-11-11", 1, "DAY", "2011-11-12", "2011-11-10", false}, // nil test {nil, 1, "DAY", nil, nil, false}, {"2011-11-11", nil, "DAY", nil, nil, false}, // tests for different units {"2011-11-11 10:10:10", 1000, "MICROSECOND", "2011-11-11 10:10:10.001000", "2011-11-11 10:10:09.999000", false}, {"2011-11-11 10:10:10", "10", "SECOND", "2011-11-11 10:10:20", "2011-11-11 10:10:00", false}, {"2011-11-11 10:10:10", "10", "MINUTE", "2011-11-11 10:20:10", "2011-11-11 10:00:10", false}, {"2011-11-11 10:10:10", "10", "HOUR", "2011-11-11 20:10:10", "2011-11-11 00:10:10", false}, {"2011-11-11 10:10:10", "11", "DAY", "2011-11-22 10:10:10", "2011-10-31 10:10:10", false}, {"2011-11-11 10:10:10", "2", "WEEK", "2011-11-25 10:10:10", "2011-10-28 10:10:10", false}, {"2011-11-11 10:10:10", "2", "MONTH", "2012-01-11 10:10:10", "2011-09-11 10:10:10", false}, {"2011-11-11 10:10:10", "4", "QUARTER", "2012-11-11 10:10:10", "2010-11-11 10:10:10", false}, {"2011-11-11 10:10:10", "2", "YEAR", "2013-11-11 10:10:10", "2009-11-11 10:10:10", false}, {"2011-11-11 10:10:10", "10.00100000", "SECOND_MICROSECOND", "2011-11-11 10:10:20.100000", "2011-11-11 10:09:59.900000", false}, {"2011-11-11 10:10:10", "10.0010000000", "SECOND_MICROSECOND", "2011-11-11 10:10:30", "2011-11-11 10:09:50", false}, {"2011-11-11 10:10:10", "10.0010000010", "SECOND_MICROSECOND", "2011-11-11 10:10:30.000010", "2011-11-11 10:09:49.999990", false}, {"2011-11-11 10:10:10", "10:10.100", "MINUTE_MICROSECOND", "2011-11-11 10:20:20.100000", "2011-11-11 09:59:59.900000", false}, {"2011-11-11 10:10:10", "10:10", "MINUTE_SECOND", "2011-11-11 10:20:20", "2011-11-11 10:00:00", false}, {"2011-11-11 10:10:10", "10:10:10.100", "HOUR_MICROSECOND", "2011-11-11 20:20:20.100000", "2011-11-10 23:59:59.900000", false}, {"2011-11-11 10:10:10", "10:10:10", "HOUR_SECOND", "2011-11-11 20:20:20", "2011-11-11 00:00:00", false}, {"2011-11-11 10:10:10", "10:10", "HOUR_MINUTE", "2011-11-11 20:20:10", "2011-11-11 00:00:10", false}, {"2011-11-11 10:10:10", "11 10:10:10.100", "DAY_MICROSECOND", "2011-11-22 20:20:20.100000", "2011-10-30 23:59:59.900000", false}, {"2011-11-11 10:10:10", "11 10:10:10", "DAY_SECOND", "2011-11-22 20:20:20", "2011-10-31 00:00:00", false}, {"2011-11-11 10:10:10", "11 10:10", "DAY_MINUTE", "2011-11-22 20:20:10", "2011-10-31 00:00:10", false}, {"2011-11-11 10:10:10", "11 10", "DAY_HOUR", "2011-11-22 20:10:10", "2011-10-31 00:10:10", false}, {"2011-11-11 10:10:10", "11-1", "YEAR_MONTH", "2022-12-11 10:10:10", "2000-10-11 10:10:10", false}, {"2011-11-11 10:10:10", "11-11", "YEAR_MONTH", "2023-10-11 10:10:10", "1999-12-11 10:10:10", false}, // tests for interval in day forms {"2011-11-11 10:10:10", "20", "DAY", "2011-12-01 10:10:10", "2011-10-22 10:10:10", false}, {"2011-11-11 10:10:10", 19.88, "DAY", "2011-12-01 10:10:10", "2011-10-22 10:10:10", false}, {"2011-11-11 10:10:10", "19.88", "DAY", "2011-11-30 10:10:10", "2011-10-23 10:10:10", false}, {"2011-11-11 10:10:10", "prefix19suffix", "DAY", "2011-11-30 10:10:10", "2011-10-23 10:10:10", false}, {"2011-11-11 10:10:10", "20-11", "DAY", "2011-12-01 10:10:10", "2011-10-22 10:10:10", false}, {"2011-11-11 10:10:10", "20,11", "daY", "2011-12-01 10:10:10", "2011-10-22 10:10:10", false}, {"2011-11-11 10:10:10", "1000", "dAy", "2014-08-07 10:10:10", "2009-02-14 10:10:10", false}, {"2011-11-11 10:10:10", "true", "Day", "2011-11-12 10:10:10", "2011-11-10 10:10:10", false}, {"2011-11-11 10:10:10", true, "Day", "2011-11-12 10:10:10", "2011-11-10 10:10:10", false}, // test for different return data types {"2011-11-11", 1, "DAY", "2011-11-12", "2011-11-10", false}, {"2011-11-11", 10, "HOUR", "2011-11-11 10:00:00", "2011-11-10 14:00:00", false}, {"2011-11-11", 10, "MINUTE", "2011-11-11 00:10:00", "2011-11-10 23:50:00", false}, {"2011-11-11", 10, "SECOND", "2011-11-11 00:00:10", "2011-11-10 23:59:50", false}, {"2011-11-11", "10:10", "HOUR_MINUTE", "2011-11-11 10:10:00", "2011-11-10 13:50:00", false}, {"2011-11-11", "10:10:10", "HOUR_SECOND", "2011-11-11 10:10:10", "2011-11-10 13:49:50", false}, {"2011-11-11", "10:10:10.101010", "HOUR_MICROSECOND", "2011-11-11 10:10:10.101010", "2011-11-10 13:49:49.898990", false}, {"2011-11-11", "10:10", "MINUTE_SECOND", "2011-11-11 00:10:10", "2011-11-10 23:49:50", false}, {"2011-11-11", "10:10.101010", "MINUTE_MICROSECOND", "2011-11-11 00:10:10.101010", "2011-11-10 23:49:49.898990", false}, {"2011-11-11", "10.101010", "SECOND_MICROSECOND", "2011-11-11 00:00:10.101010", "2011-11-10 23:59:49.898990", false}, {"2011-11-11 00:00:00", 1, "DAY", "2011-11-12 00:00:00", "2011-11-10 00:00:00", false}, {"2011-11-11 00:00:00", 10, "HOUR", "2011-11-11 10:00:00", "2011-11-10 14:00:00", false}, {"2011-11-11 00:00:00", 10, "MINUTE", "2011-11-11 00:10:00", "2011-11-10 23:50:00", false}, {"2011-11-11 00:00:00", 10, "SECOND", "2011-11-11 00:00:10", "2011-11-10 23:59:50", false}, // tests for invalid input {"2011-11-11", "abc1000", "MICROSECOND", nil, nil, true}, {"20111111 10:10:10", "1", "DAY", nil, nil, true}, {"2011-11-11", "10", "SECOND_MICROSECOND", nil, nil, true}, {"2011-11-11", "10.0000", "MINUTE_MICROSECOND", nil, nil, true}, {"2011-11-11", "10:10:10", "MINUTE_MICROSECOND", nil, nil, true}, } // run the test cases for _, t := range tests { op := ast.NewValueExpr(ast.DateAdd) dateArithInterval := ast.NewValueExpr( ast.DateArithInterval{ Unit: t.Unit, Interval: ast.NewValueExpr(t.Interval), }, ) date := ast.NewValueExpr(t.Date) expr := &ast.FuncCallExpr{ FnName: model.NewCIStr("DATE_ARITH"), Args: []ast.ExprNode{ op, date, dateArithInterval, }, } ast.SetFlag(expr) v, err := Eval(ctx, expr) if t.error == true { c.Assert(err, NotNil) } else { c.Assert(err, IsNil) if v.IsNull() { c.Assert(nil, Equals, t.AddResult) } else { c.Assert(v.Kind(), Equals, types.KindMysqlTime) value := v.GetMysqlTime() c.Assert(value.String(), Equals, t.AddResult) } } op = ast.NewValueExpr(ast.DateSub) expr.Args[0] = op v, err = Eval(ctx, expr) if t.error == true { c.Assert(err, NotNil) } else { c.Assert(err, IsNil) if v.IsNull() { c.Assert(nil, Equals, t.AddResult) } else { c.Assert(v.Kind(), Equals, types.KindMysqlTime) value := v.GetMysqlTime() c.Assert(value.String(), Equals, t.SubResult) } } } }
func (s *testPlanSuite) TestJoinPath(c *C) { defer testleak.AfterTest(c)() cases := []struct { sql string explain string }{ { "select * from t1, t2 where t1.c1 > 0", "InnerJoin{Table(t1)->Table(t2)}->Fields", }, { "select * from t1 left join t2 on 1 where t2.c1 != 0", "InnerJoin{Table(t2)->Table(t1)}->Fields", }, { "select * from t1 left join t2 on 1 where t2.c1 != 0 or t1.c1 != 0", "OuterJoin{Table(t1)->Table(t2)}->Filter->Fields", }, { "select * from t1 left join t2 on t1.i1 = t2.i1 where t1.i1 = 1", "OuterJoin{Index(t1.i1)->Index(t2.i1)}->Fields", }, { "select * from t1 join t2 on t2.c1 = t1.i1", "InnerJoin{Table(t2)->Index(t1.i1)}->Fields", }, { "select * from t1, t2 where t2.c1 = t1.i1", "InnerJoin{Table(t2)->Index(t1.i1)}->Fields", }, { `select * from t1 left join (t2 join t3 on t2.i1 = t3.c1) on t1.c1 = t2.i2`, "OuterJoin{Table(t1)->InnerJoin{Index(t2.i2)->Table(t3)}}->Fields", }, { `select * from (t1, t2) left join t3 on t2.c1 = t3.i1 where t2.i2 between 1 and 4 and t1.i1 = 3`, "OuterJoin{InnerJoin{Index(t1.i1)->Index(t2.i2)}->Index(t3.i1)}->Fields", }, { `select * from t1 join ( (t2 join t3 on t2.i3 = t3.i3 and t2.c2 > t3.c3 ) left join t4 on t3.c3 = t4.i4 ) on t1.i1 = 1 and t1.c1 = t2.i2`, "InnerJoin{Index(t1.i1)->OuterJoin{InnerJoin{Index(t2.i2)->Index(t3.i3)}->Index(t4.i4)}}->Fields", }, { `select * from t1 join ( t2 left join ( t3 join t4 on t3.i3 = t4.c4 ) on t2.i2 = t3.c3 ) on t1.i1 = t2.c2`, "InnerJoin{OuterJoin{Table(t2)->InnerJoin{Table(t4)->Index(t3.i3)}}->Index(t1.i1)}->Fields", }, { `select * from t1 join ( (t2 join t3 on t2.i2 = t3.i3 ) ) on t1.i1 = t2.i2 where t1.i1 = 1`, "InnerJoin{Index(t1.i1)->Index(t2.i2)->Index(t3.i3)}->Fields", }, } for _, ca := range cases { comment := Commentf("for %s", ca.sql) s, err := s.ParseOneStmt(ca.sql, "", "") c.Assert(err, IsNil, comment) stmt := s.(*ast.SelectStmt) mockJoinResolve(c, stmt) ast.SetFlag(stmt) p, err := BuildPlan(stmt, nil) c.Assert(err, IsNil) c.Assert(ToString(p), Equals, ca.explain, comment) } }
func (s *testPlanSuite) TestBestPlan(c *C) { defer testleak.AfterTest(c)() cases := []struct { sql string best string }{ { sql: "select * from t", best: "Table(t)->Fields", }, { sql: "select * from t order by a", best: "Table(t)->Fields", }, { sql: "select * from t where b = 1 order by a", best: "Index(t.b)->Fields->Sort", }, { sql: "select * from t where (a between 1 and 2) and (b = 3)", best: "Index(t.b)->Fields", }, { sql: "select * from t where a > 0 order by b limit 100", best: "Index(t.b) + Limit(100)->Fields->Limit", }, { sql: "select * from t where a > 0 order by b DESC limit 100", best: "Index(t.b) + Limit(100)->Fields->Limit", }, { sql: "select * from t where a > 0 order by b + a limit 100", best: "Range(t)->Fields->Sort + Limit(100) + Offset(0)", }, { sql: "select count(*) from t where a > 0 order by b limit 100", best: "Range(t)->Aggregate->Fields->Sort + Limit(100) + Offset(0)", }, { sql: "select count(*) from t where a > 0 limit 100", best: "Range(t)->Aggregate->Fields->Limit", }, { sql: "select distinct a from t where a > 0 limit 100", best: "Range(t)->Fields->Distinct->Limit", }, { sql: "select * from t where a > 0 order by a limit 100", best: "Range(t) + Limit(100)->Fields->Limit", }, { sql: "select * from t where d = 0", best: "Table(t)->Fields", }, { sql: "select * from t where c = 0 and d = 0", best: "Index(t.c_d_e)->Fields", }, { sql: "select * from t where c = 0 and d = 0 and e = 0", best: "Index(t.c_d_e)->Fields", }, { sql: "select * from t where (d = 0 and e = 0) and c = 0", best: "Index(t.c_d_e)->Fields", }, { sql: "select * from t where e = 0 and (d = 0 and c = 0)", best: "Index(t.c_d_e)->Fields", }, { sql: "select * from t where b like 'abc%'", best: "Index(t.b)->Fields", }, { sql: "select * from t where b like ''", best: "Index(t.b)->Fields", }, { sql: "select * from t where d", best: "Table(t)->Fields", }, { sql: "select * from t where a is null", best: "Range(t)->Fields", }, { sql: "select a from t where a = 1 limit 1 for update", best: "Range(t) + Limit(1)->Lock->Fields->Limit", }, { sql: "admin show ddl", best: "ShowDDL", }, { sql: "admin check table t", best: "CheckTable", }, } for _, ca := range cases { comment := Commentf("for %s", ca.sql) stmt, err := s.ParseOneStmt(ca.sql, "", "") c.Assert(err, IsNil, comment) ast.SetFlag(stmt) mockResolve(stmt) p, err := BuildPlan(stmt, nil) c.Assert(err, IsNil) err = Refine(p) c.Assert(err, IsNil) c.Assert(ToString(p), Equals, ca.best, Commentf("for %s cost %v", ca.sql, EstimateCost(p))) } }
func (s *testPlanSuite) TestCBO(c *C) { defer testleak.AfterTest(c)() cases := []struct { sql string best string }{ { sql: "select * from t t1 where 1 = 0", best: "Dummy->Projection", }, { sql: "select count(*) from t t1 having 1 = 0", best: "Dummy->Aggr->Selection->Projection", }, { sql: "select * from t a where a.c = 1 order by a.d limit 2", best: "Index(t.c_d_e)[[1,1]]->Projection", }, { sql: "select * from t a where 1 = a.c and a.d > 1 order by a.d desc limit 2", best: "Index(t.c_d_e)[(1 1,1 +inf]]->Projection", }, { sql: "select * from t a where a.c < 10000 order by a.a limit 2", best: "Table(t)->Selection->Limit->Projection", }, { sql: "select * from (select * from t) a left outer join (select * from t) b on 1 order by a.c", best: "LeftHashJoin{Index(t.c_d_e)[[<nil>,+inf]]->Projection->Table(t)->Projection}->Projection", }, { sql: "select * from (select * from t) a left outer join (select * from t) b on 1 order by b.c", best: "LeftHashJoin{Table(t)->Projection->Table(t)->Projection}->Projection->Sort", }, { sql: "select * from (select * from t) a right outer join (select * from t) b on 1 order by a.c", best: "RightHashJoin{Table(t)->Projection->Table(t)->Projection}->Projection->Sort", }, { sql: "select * from (select * from t) a right outer join (select * from t) b on 1 order by b.c", best: "RightHashJoin{Table(t)->Projection->Index(t.c_d_e)[[<nil>,+inf]]->Projection}->Projection", }, { sql: "select * from t a where exists(select * from t b where a.a = b.a) and a.c = 1 order by a.d limit 3", best: "SemiJoin{Index(t.c_d_e)[[1,1]]->Table(t)}->Limit->Projection", }, { sql: "select exists(select * from t b where a.a = b.a and b.c = 1) from t a order by a.c limit 3", best: "SemiJoinWithAux{Index(t.c_d_e)[[<nil>,+inf]]->Index(t.c_d_e)[[1,1]]}->Projection->Trim", }, { sql: "select * from (select t.a from t union select t.d from t where t.c = 1 union select t.c from t) k order by a limit 1", best: "UnionAll{Table(t)->Projection->Index(t.c_d_e)[[1,1]]->Projection->Index(t.c_d_e)[[<nil>,+inf]]->Projection}->Distinct->Limit->Projection", }, { sql: "select * from (select t.a from t union select t.d from t union select t.c from t) k order by a limit 1", best: "UnionAll{Table(t)->Projection->Table(t)->Projection->Table(t)->Projection}->Distinct->Projection->Sort + Limit(1) + Offset(0)", }, } for _, ca := range cases { comment := Commentf("for %s", ca.sql) stmt, err := s.ParseOneStmt(ca.sql, "", "") c.Assert(err, IsNil, comment) ast.SetFlag(stmt) err = newMockResolve(stmt) c.Assert(err, IsNil) builder := &planBuilder{ allocator: new(idAllocator), ctx: mock.NewContext(), colMapper: make(map[*ast.ColumnNameExpr]int), } p := builder.build(stmt) c.Assert(builder.err, IsNil) lp := p.(LogicalPlan) _, lp, err = lp.PredicatePushDown(nil) c.Assert(err, IsNil) _, err = lp.PruneColumnsAndResolveIndices(lp.GetSchema()) c.Assert(err, IsNil) _, res, _, err := lp.convert2PhysicalPlan(nil) c.Assert(err, IsNil) p = res.p.PushLimit(nil) c.Assert(ToString(p), Equals, ca.best, Commentf("for %s", ca.sql)) } }
func (s *testPlanSuite) TestRefine(c *C) { defer testleak.AfterTest(c)() cases := []struct { sql string best string }{ { sql: "select a from t where c is not null", best: "Index(t.c_d_e)[[-inf,+inf]]->Projection", }, { sql: "select a from t where c >= 4", best: "Index(t.c_d_e)[[4,+inf]]->Projection", }, { sql: "select a from t where c <= 4", best: "Index(t.c_d_e)[[-inf,4]]->Projection", }, { sql: "select a from t where c = 4 and d = 5 and e = 6", best: "Index(t.c_d_e)[[4 5 6,4 5 6]]->Projection", }, { sql: "select a from t where d = 4 and c = 5", best: "Index(t.c_d_e)[[5 4,5 4]]->Projection", }, { sql: "select a from t where c = 4 and e < 5", best: "Index(t.c_d_e)[[4,4]]->Selection->Projection", }, { sql: "select a from t where c = 4 and d <= 5 and d > 3", best: "Index(t.c_d_e)[(4 3,4 5]]->Projection", }, { sql: "select a from t where d <= 5 and d > 3", best: "Table(t)->Selection->Projection", }, { sql: "select a from t where c <= 5 and c >= 3 and d = 1", best: "Index(t.c_d_e)[[3,5]]->Selection->Projection", }, { sql: "select a from t where c = 1 or c = 2 or c = 3", best: "Index(t.c_d_e)[[1,1] [2,2] [3,3]]->Projection", }, { sql: "select b from t where c = 1 or c = 2 or c = 3 or c = 4 or c = 5", best: "Table(t)->Selection->Projection", }, { sql: "select a from t where c = 5", best: "Index(t.c_d_e)[[5,5]]->Projection", }, { sql: "select a from t where c = 5 and b = 1", best: "Index(t.c_d_e)[[5,5]]->Selection->Projection", }, { sql: "select a from t where c in (1)", best: "Index(t.c_d_e)[[1,1]]->Projection", }, { sql: "select a from t where c in (1) and d > 3", best: "Index(t.c_d_e)[[1,1]]->Selection->Projection", }, { sql: "select a from t where c in (1, 2, 3)", best: "Index(t.c_d_e)[[1,1] [2,2] [3,3]]->Projection", }, { sql: "select a from t where d in (1, 2, 3)", best: "Table(t)->Selection->Projection", }, { sql: "select a from t where c not in (1)", best: "Table(t)->Selection->Projection", }, { sql: "select a from t where c like ''", best: "Index(t.c_d_e)[[,]]->Projection", }, { sql: "select a from t where c like 'abc'", best: "Index(t.c_d_e)[[abc,abc]]->Projection", }, { sql: "select a from t where c not like 'abc'", best: "Table(t)->Selection->Projection", }, { sql: "select a from t where not (c like 'abc' or c like 'abd')", best: "Table(t)->Selection->Projection", }, { sql: "select a from t where c like '_abc'", best: "Table(t)->Selection->Projection", }, { sql: "select a from t where c like 'abc%'", best: "Index(t.c_d_e)[[abc,abd)]->Projection", }, { sql: "select a from t where c like 'abc_'", best: "Index(t.c_d_e)[(abc,abd)]->Selection->Projection", }, { sql: "select a from t where c like 'abc%af'", best: "Index(t.c_d_e)[[abc,abd)]->Selection->Projection", }, { sql: `select a from t where c like 'abc\\_' escape ''`, best: "Index(t.c_d_e)[[abc_,abc_]]->Projection", }, { sql: `select a from t where c like 'abc\\_'`, best: "Index(t.c_d_e)[[abc_,abc_]]->Projection", }, { sql: `select a from t where c like 'abc\\\\_'`, best: "Index(t.c_d_e)[(abc\\,abc])]->Selection->Projection", }, { sql: `select a from t where c like 'abc\\_%'`, best: "Index(t.c_d_e)[[abc_,abc`)]->Projection", }, { sql: `select a from t where c like 'abc=_%' escape '='`, best: "Index(t.c_d_e)[[abc_,abc`)]->Projection", }, { sql: `select a from t where c like 'abc\\__'`, best: "Index(t.c_d_e)[(abc_,abc`)]->Selection->Projection", }, } for _, ca := range cases { comment := Commentf("for %s", ca.sql) stmt, err := s.ParseOneStmt(ca.sql, "", "") c.Assert(err, IsNil, comment) ast.SetFlag(stmt) err = newMockResolve(stmt) c.Assert(err, IsNil) builder := &planBuilder{ allocator: new(idAllocator), ctx: mock.NewContext(), } p := builder.build(stmt).(LogicalPlan) c.Assert(builder.err, IsNil) _, p, err = p.PredicatePushDown(nil) c.Assert(err, IsNil) _, err = p.PruneColumnsAndResolveIndices(p.GetSchema()) c.Assert(err, IsNil) _, res, _, err := p.convert2PhysicalPlan(nil) c.Assert(err, IsNil) np := res.p.PushLimit(nil) c.Assert(ToString(np), Equals, ca.best, Commentf("for %s", ca.sql)) } }
// createResultFields creates result field list for a single select field. func (nr *nameResolver) createResultFields(field *ast.SelectField) (rfs []*ast.ResultField) { ctx := nr.currentContext() if field.WildCard != nil { if len(ctx.tables) == 0 { nr.Err = errors.New("No table used.") return } tableRfs := []*ast.ResultField{} if field.WildCard.Table.L == "" { for _, v := range ctx.tables { tableRfs = append(tableRfs, v.GetResultFields()...) } } else { name := nr.tableUniqueName(field.WildCard.Schema, field.WildCard.Table) tableIdx, ok1 := ctx.tableMap[name] derivedTableIdx, ok2 := ctx.derivedTableMap[name] if !ok1 && !ok2 { nr.Err = errors.Errorf("unknown table %s.", field.WildCard.Table.O) } if ok1 { tableRfs = ctx.tables[tableIdx].GetResultFields() } if ok2 { tableRfs = append(tableRfs, ctx.tables[derivedTableIdx].GetResultFields()...) } } for _, trf := range tableRfs { // Convert it to ColumnNameExpr cn := &ast.ColumnName{ Schema: trf.DBName, Table: trf.Table.Name, Name: trf.ColumnAsName, } cnExpr := &ast.ColumnNameExpr{ Name: cn, Refer: trf, } ast.SetFlag(cnExpr) cnExpr.SetType(trf.Expr.GetType()) rf := *trf rf.Expr = cnExpr rfs = append(rfs, &rf) } return } // The column is visited before so it must has been resolved already. rf := &ast.ResultField{ColumnAsName: field.AsName} innerExpr := getInnerFromParentheses(field.Expr) switch v := innerExpr.(type) { case *ast.ColumnNameExpr: rf.Column = v.Refer.Column rf.Table = v.Refer.Table rf.DBName = v.Refer.DBName rf.TableName = v.Refer.TableName rf.Expr = v default: rf.Column = &model.ColumnInfo{} // Empty column info. rf.Table = &model.TableInfo{} // Empty table info. rf.Expr = v } if field.AsName.L == "" { switch x := innerExpr.(type) { case *ast.ColumnNameExpr: rf.ColumnAsName = model.NewCIStr(x.Name.Name.O) case *ast.ValueExpr: if innerExpr.Text() != "" { rf.ColumnAsName = model.NewCIStr(innerExpr.Text()) } else { rf.ColumnAsName = model.NewCIStr(field.Text()) } default: rf.ColumnAsName = model.NewCIStr(field.Text()) } } rfs = append(rfs, rf) return }
func (s *testPlanSuite) TestPredicatePushDown(c *C) { UseNewPlanner = true defer testleak.AfterTest(c)() cases := []struct { sql string first string best string }{ { sql: "select a from (select a from t where d = 0) k where k.a = 5", first: "DataScan(t)->Selection->Projection->Selection->Projection", best: "DataScan(t)->Selection->Projection->Projection", }, { sql: "select a from (select 1+2 as a from t where d = 0) k where k.a = 5", first: "DataScan(t)->Selection->Projection->Selection->Projection", best: "DataScan(t)->Selection->Projection->Selection->Projection", }, { sql: "select a from (select d as a from t where d = 0) k where k.a = 5", first: "DataScan(t)->Selection->Projection->Selection->Projection", best: "DataScan(t)->Selection->Projection->Projection", }, { sql: "select * from t ta join t tb on ta.d = tb.d and ta.d > 1 where tb.a = 0", first: "Join{DataScan(t)->DataScan(t)}->Selection->Projection", best: "Join{DataScan(t)->Selection->DataScan(t)->Selection}->Projection", }, { sql: "select * from t ta join t tb on ta.d = tb.d where ta.d > 1 and tb.a = 0", first: "Join{DataScan(t)->DataScan(t)}->Selection->Projection", best: "Join{DataScan(t)->Selection->DataScan(t)->Selection}->Projection", }, { sql: "select * from t ta left outer join t tb on ta.d = tb.d and ta.d > 1 where tb.a = 0", first: "Join{DataScan(t)->DataScan(t)}->Selection->Projection", best: "Join{DataScan(t)->DataScan(t)}->Selection->Projection", }, { sql: "select * from t ta right outer join t tb on ta.d = tb.d and ta.a > 1 where tb.a = 0", first: "Join{DataScan(t)->DataScan(t)}->Selection->Projection", best: "Join{DataScan(t)->Selection->DataScan(t)->Selection}->Projection", }, { sql: "select a, d from (select * from t union all select * from t union all select * from t) z where a < 10", first: "UnionAll{DataScan(t)->Projection->DataScan(t)->Projection->DataScan(t)->Projection}->Selection->Projection", best: "UnionAll{DataScan(t)->Selection->Projection->DataScan(t)->Selection->Projection->DataScan(t)->Selection->Projection}->Projection", }, { sql: "select (select count(*) from t where t.a = k.a) from t k", first: "DataScan(t)->Apply(DataScan(t)->Selection->Aggr->Projection->MaxOneRow)->Projection", best: "DataScan(t)->Apply(DataScan(t)->Selection->Aggr->Projection->MaxOneRow)->Projection", }, { sql: "select a from t where exists(select 1 from t as x where x.a < t.a)", first: "DataScan(t)->Apply(DataScan(t)->Selection->Projection->Exists)->Selection->Projection", best: "DataScan(t)->Apply(DataScan(t)->Selection->Projection->Exists)->Selection->Projection", }, } for _, ca := range cases { comment := Commentf("for %s", ca.sql) stmt, err := s.ParseOneStmt(ca.sql, "", "") c.Assert(err, IsNil, comment) ast.SetFlag(stmt) err = newMockResolve(stmt) c.Assert(err, IsNil) builder := &planBuilder{ allocator: new(idAllocator), } p := builder.build(stmt).(LogicalPlan) c.Assert(builder.err, IsNil) c.Assert(ToString(p), Equals, ca.first, Commentf("for %s", ca.sql)) _, p, err = p.PredicatePushDown(nil) c.Assert(err, IsNil) _, err = p.PruneColumnsAndResolveIndices(p.GetSchema()) c.Assert(err, IsNil) c.Assert(ToString(p), Equals, ca.best, Commentf("for %s", ca.sql)) } UseNewPlanner = false }
func (s *testPlanSuite) TestColumnPruning(c *C) { UseNewPlanner = true defer testleak.AfterTest(c)() cases := []struct { sql string ans map[string][]string }{ { sql: "select count(*) from t group by a", ans: map[string][]string{ "TableScan_1": {"a"}, }, }, { sql: "select count(*) from t", ans: map[string][]string{ "TableScan_1": {}, }, }, { sql: "select count(*) from t a join t b where a.a < 1", ans: map[string][]string{ "TableScan_1": {"a"}, "TableScan_2": {}, }, }, { sql: "select count(*) from t a join t b on a.a = b.d", ans: map[string][]string{ "TableScan_1": {"a"}, "TableScan_2": {"d"}, }, }, { sql: "select count(*) from t a join t b on a.a = b.d order by sum(a.d)", ans: map[string][]string{ "TableScan_1": {"a", "d"}, "TableScan_2": {"d"}, }, }, { sql: "select count(b.a) from t a join t b on a.a = b.d group by b.b order by sum(a.d)", ans: map[string][]string{ "TableScan_1": {"a", "d"}, "TableScan_2": {"a", "b", "d"}, }, }, { sql: "select * from (select count(b.a) from t a join t b on a.a = b.d group by b.b having sum(a.d) < 0) tt", ans: map[string][]string{ "TableScan_1": {"a", "d"}, "TableScan_2": {"a", "b", "d"}, }, }, { sql: "select (select count(a) from t where b = k.a) from t k", ans: map[string][]string{ "TableScan_1": {"a"}, "TableScan_2": {"a", "b"}, }, }, { sql: "select exists (select count(*) from t where b = k.a) from t k", ans: map[string][]string{ "TableScan_1": {"a"}, "TableScan_2": {"b"}, }, }, { sql: "select b = (select count(*) from t where b = k.a) from t k", ans: map[string][]string{ "TableScan_1": {"a", "b"}, "TableScan_2": {"b"}, }, }, { sql: "select exists (select count(a) from t where b = k.a) from t k", ans: map[string][]string{ "TableScan_1": {"a"}, "TableScan_2": {"b"}, }, }, { sql: "select a as c1, b as c2 from t order by 1, c1 + c2 + c", ans: map[string][]string{ "TableScan_1": {"a", "b", "c"}, }, }, { sql: "select a from t where b < any (select c from t)", ans: map[string][]string{ "TableScan_1": {"a", "b"}, "TableScan_2": {"c"}, }, }, { sql: "select a from t where (b,a) != all (select c,d from t)", ans: map[string][]string{ "TableScan_1": {"a", "b"}, "TableScan_2": {"c", "d"}, }, }, { sql: "select a from t where (b,a) in (select c,d from t)", ans: map[string][]string{ "TableScan_1": {"a", "b"}, "TableScan_2": {"c", "d"}, }, }, } for _, ca := range cases { comment := Commentf("for %s", ca.sql) stmt, err := s.ParseOneStmt(ca.sql, "", "") c.Assert(err, IsNil, comment) ast.SetFlag(stmt) err = newMockResolve(stmt) c.Assert(err, IsNil) builder := &planBuilder{ colMapper: make(map[*ast.ColumnNameExpr]int), allocator: new(idAllocator)} p := builder.build(stmt).(LogicalPlan) c.Assert(builder.err, IsNil, comment) _, p, err = p.PredicatePushDown(nil) c.Assert(err, IsNil) _, err = p.PruneColumnsAndResolveIndices(p.GetSchema()) c.Assert(err, IsNil) check(p, c, ca.ans, comment) } UseNewPlanner = false }
func (s *testPlanSuite) TestBestPlan(c *C) { cases := []struct { sql string best string }{ { sql: "select * from t", best: "Table(t)->Fields", }, { sql: "select * from t order by a", best: "Table(t)->Fields", }, { sql: "select * from t where b = 1 order by a", best: "Index(t.b)->Filter->Fields->Sort", }, { sql: "select * from t where (a between 1 and 2) and (b = 3)", best: "Index(t.b)->Filter->Fields", }, { sql: "select * from t where a > 0 order by b limit 100", best: "Index(t.b)->Filter->Fields->Limit", }, { sql: "select * from t where d = 0", best: "Table(t)->Filter->Fields", }, { sql: "select * from t where c = 0 and d = 0", best: "Index(t.c_d)->Filter->Fields", }, { sql: "select * from t where b like 'abc%'", best: "Index(t.b)->Filter->Fields", }, { sql: "select * from t where d", best: "Table(t)->Filter->Fields", }, { sql: "select * from t where a is null", best: "Range(t)->Filter->Fields", }, } for _, ca := range cases { comment := Commentf("for %s", ca.sql) s, err := parser.ParseOneStmt(ca.sql, "", "") c.Assert(err, IsNil, comment) stmt := s.(*ast.SelectStmt) ast.SetFlag(stmt) mockResolve(stmt) p, err := BuildPlan(stmt) c.Assert(err, IsNil) alts, err := Alternatives(p) c.Assert(err, IsNil) err = Refine(p) c.Assert(err, IsNil) bestCost := EstimateCost(p) bestPlan := p for _, alt := range alts { c.Assert(Refine(alt), IsNil) cost := EstimateCost(alt) if cost < bestCost { bestCost = cost bestPlan = alt } } explainStr, err := Explain(bestPlan) c.Assert(err, IsNil) c.Assert(explainStr, Equals, ca.best, Commentf("for %s cost %v", ca.sql, bestCost)) } }
func (s *testPlanSuite) TestColumnPruning(c *C) { UseNewPlanner = true defer testleak.AfterTest(c)() cases := []struct { sql string ans map[string][]string }{ { sql: "select count(*) from t group by a", ans: map[string][]string{ "*plan.NewTableScan_1": {"a"}, }, }, { sql: "select count(*) from t", ans: map[string][]string{ "*plan.NewTableScan_1": {}, }, }, { sql: "select count(*) from t a join t b where a.a < 1", ans: map[string][]string{ "*plan.NewTableScan_1": {"a"}, "*plan.NewTableScan_2": {}, }, }, { sql: "select count(*) from t a join t b on a.a = b.d", ans: map[string][]string{ "*plan.NewTableScan_1": {"a"}, "*plan.NewTableScan_2": {"d"}, }, }, { sql: "select count(*) from t a join t b on a.a = b.d order by sum(a.d)", ans: map[string][]string{ "*plan.NewTableScan_1": {"a", "d"}, "*plan.NewTableScan_2": {"d"}, }, }, { sql: "select count(b.a) from t a join t b on a.a = b.d group by b.b order by sum(a.d)", ans: map[string][]string{ "*plan.NewTableScan_1": {"a", "d"}, "*plan.NewTableScan_2": {"a", "b", "d"}, }, }, { sql: "select * from (select count(b.a) from t a join t b on a.a = b.d group by b.b having sum(a.d) < 0) tt", ans: map[string][]string{ "*plan.NewTableScan_1": {"a", "d"}, "*plan.NewTableScan_2": {"a", "b", "d"}, }, }, { sql: "select (select count(a) from t where b = k.a) from t k", ans: map[string][]string{ "*plan.NewTableScan_1": {"a"}, "*plan.NewTableScan_2": {"a", "b"}, }, }, { sql: "select exists (select count(*) from t where b = k.a) from t k", ans: map[string][]string{ "*plan.NewTableScan_1": {"a"}, "*plan.NewTableScan_2": {"b"}, }, }, { sql: "select b = (select count(*) from t where b = k.a) from t k", ans: map[string][]string{ "*plan.NewTableScan_1": {"a", "b"}, "*plan.NewTableScan_2": {"b"}, }, }, { sql: "select exists (select count(a) from t where b = k.a) from t k", ans: map[string][]string{ "*plan.NewTableScan_1": {"a"}, "*plan.NewTableScan_2": {"b"}, }, }, } for _, ca := range cases { comment := Commentf("for %s", ca.sql) stmt, err := parser.ParseOneStmt(ca.sql, "", "") c.Assert(err, IsNil, comment) ast.SetFlag(stmt) err = newMockResolve(stmt) c.Assert(err, IsNil) builder := &planBuilder{} p := builder.build(stmt) c.Assert(builder.err, IsNil) _, err = builder.predicatePushDown(p, []expression.Expression{}) c.Assert(err, IsNil) _, _, err = pruneColumnsAndResolveIndices(p, p.GetSchema()) c.Assert(err, IsNil) check(p, c, ca.ans, comment) } UseNewPlanner = false }