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
}