Beispiel #1
0
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))
}
Beispiel #2
0
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)
	}
}
Beispiel #3
0
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)
}
Beispiel #4
0
// 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
}
Beispiel #5
0
// 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
}
Beispiel #6
0
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
}
Beispiel #7
0
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)
}
Beispiel #8
0
// 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
}
Beispiel #9
0
// 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
}
Beispiel #10
0
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))
		}
	}
}
Beispiel #11
0
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))
	}
}
Beispiel #12
0
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
}
Beispiel #13
0
// 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
}
Beispiel #14
0
// 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
}
Beispiel #15
0
// 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
}
Beispiel #16
0
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)
	}
}
Beispiel #17
0
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)))
}
Beispiel #18
0
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
}
Beispiel #19
0
// 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
}
Beispiel #20
0
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)
}
Beispiel #21
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)
			}
		}
	}
}
Beispiel #22
0
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)
	}
}
Beispiel #23
0
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)))
	}
}
Beispiel #24
0
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))
	}
}
Beispiel #25
0
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))
	}
}
Beispiel #26
0
// 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
}
Beispiel #27
0
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
}
Beispiel #28
0
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
}
Beispiel #29
0
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))
	}
}
Beispiel #30
0
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
}