You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
1667 lines
47 KiB
1667 lines
47 KiB
// Copyright 2015 PingCAP, Inc.
|
|
//
|
|
// Licensed under the Apache License, Version 2.0 (the "License");
|
|
// you may not use this file except in compliance with the License.
|
|
// You may obtain a copy of the License at
|
|
//
|
|
// http://www.apache.org/licenses/LICENSE-2.0
|
|
//
|
|
// Unless required by applicable law or agreed to in writing, software
|
|
// distributed under the License is distributed on an "AS IS" BASIS,
|
|
// See the License for the specific language governing permissions and
|
|
// limitations under the License.
|
|
|
|
package core
|
|
|
|
import (
|
|
"context"
|
|
"fmt"
|
|
"sort"
|
|
"strings"
|
|
"testing"
|
|
|
|
. "github.com/pingcap/check"
|
|
"github.com/pingcap/parser"
|
|
"github.com/pingcap/parser/ast"
|
|
"github.com/pingcap/parser/format"
|
|
"github.com/pingcap/parser/model"
|
|
"github.com/pingcap/parser/mysql"
|
|
"github.com/pingcap/parser/terror"
|
|
"github.com/pingcap/tidb/expression"
|
|
"github.com/pingcap/tidb/infoschema"
|
|
"github.com/pingcap/tidb/planner/property"
|
|
"github.com/pingcap/tidb/planner/util"
|
|
"github.com/pingcap/tidb/sessionctx"
|
|
"github.com/pingcap/tidb/sessionctx/variable"
|
|
"github.com/pingcap/tidb/util/hint"
|
|
"github.com/pingcap/tidb/util/testleak"
|
|
"github.com/pingcap/tidb/util/testutil"
|
|
)
|
|
|
|
var _ = Suite(&testPlanSuite{})
|
|
|
|
func TestT(t *testing.T) {
|
|
CustomVerboseFlag = true
|
|
TestingT(t)
|
|
}
|
|
|
|
type testPlanSuite struct {
|
|
*parser.Parser
|
|
|
|
is infoschema.InfoSchema
|
|
ctx sessionctx.Context
|
|
|
|
testData testutil.TestData
|
|
|
|
optimizeVars map[string]string
|
|
}
|
|
|
|
func (s *testPlanSuite) SetUpSuite(c *C) {
|
|
s.is = infoschema.MockInfoSchema([]*model.TableInfo{MockSignedTable(), MockUnsignedTable(), MockView()})
|
|
s.ctx = MockContext()
|
|
s.ctx.GetSessionVars().EnableWindowFunction = true
|
|
s.Parser = parser.New()
|
|
s.Parser.EnableWindowFunc(true)
|
|
|
|
var err error
|
|
s.testData, err = testutil.LoadTestSuiteData("testdata", "plan_suite_unexported")
|
|
c.Assert(err, IsNil)
|
|
}
|
|
|
|
func (s *testPlanSuite) TearDownSuite(c *C) {
|
|
c.Assert(s.testData.GenerateOutputIfNeeded(), IsNil)
|
|
}
|
|
|
|
func (s *testPlanSuite) TestPredicatePushDown(c *C) {
|
|
defer testleak.AfterTest(c)()
|
|
var input, output []string
|
|
s.testData.GetTestCases(c, &input, &output)
|
|
ctx := context.Background()
|
|
for ith, ca := range input {
|
|
comment := Commentf("for %s", ca)
|
|
stmt, err := s.ParseOneStmt(ca, "", "")
|
|
c.Assert(err, IsNil, comment)
|
|
p, _, err := BuildLogicalPlan(ctx, s.ctx, stmt, s.is)
|
|
c.Assert(err, IsNil)
|
|
p, err = logicalOptimize(context.TODO(), flagPredicatePushDown|flagDecorrelate|flagPrunColumns|flagPrunColumnsAgain, p.(LogicalPlan))
|
|
c.Assert(err, IsNil)
|
|
s.testData.OnRecord(func() {
|
|
output[ith] = ToString(p)
|
|
})
|
|
c.Assert(ToString(p), Equals, output[ith], Commentf("for %s %d", ca, ith))
|
|
}
|
|
}
|
|
|
|
func (s *testPlanSuite) TestJoinPredicatePushDown(c *C) {
|
|
defer testleak.AfterTest(c)()
|
|
var (
|
|
input []string
|
|
output []struct {
|
|
Left string
|
|
Right string
|
|
}
|
|
)
|
|
s.testData.GetTestCases(c, &input, &output)
|
|
|
|
ctx := context.Background()
|
|
for i, ca := range input {
|
|
comment := Commentf("for %s", ca)
|
|
stmt, err := s.ParseOneStmt(ca, "", "")
|
|
c.Assert(err, IsNil, comment)
|
|
p, _, err := BuildLogicalPlan(ctx, s.ctx, stmt, s.is)
|
|
c.Assert(err, IsNil, comment)
|
|
p, err = logicalOptimize(context.TODO(), flagPredicatePushDown|flagDecorrelate|flagPrunColumns|flagPrunColumnsAgain, p.(LogicalPlan))
|
|
c.Assert(err, IsNil, comment)
|
|
proj, ok := p.(*LogicalProjection)
|
|
c.Assert(ok, IsTrue, comment)
|
|
join, ok := proj.children[0].(*LogicalJoin)
|
|
c.Assert(ok, IsTrue, comment)
|
|
leftPlan, ok := join.children[0].(*DataSource)
|
|
c.Assert(ok, IsTrue, comment)
|
|
rightPlan, ok := join.children[1].(*DataSource)
|
|
c.Assert(ok, IsTrue, comment)
|
|
leftCond := fmt.Sprintf("%s", leftPlan.pushedDownConds)
|
|
rightCond := fmt.Sprintf("%s", rightPlan.pushedDownConds)
|
|
s.testData.OnRecord(func() {
|
|
output[i].Left, output[i].Right = leftCond, rightCond
|
|
})
|
|
c.Assert(leftCond, Equals, output[i].Left, comment)
|
|
c.Assert(rightCond, Equals, output[i].Right, comment)
|
|
}
|
|
}
|
|
|
|
func (s *testPlanSuite) TestOuterWherePredicatePushDown(c *C) {
|
|
defer testleak.AfterTest(c)()
|
|
var (
|
|
input []string
|
|
output []struct {
|
|
Sel string
|
|
Left string
|
|
Right string
|
|
}
|
|
)
|
|
s.testData.GetTestCases(c, &input, &output)
|
|
|
|
ctx := context.Background()
|
|
for i, ca := range input {
|
|
comment := Commentf("for %s", ca)
|
|
stmt, err := s.ParseOneStmt(ca, "", "")
|
|
c.Assert(err, IsNil, comment)
|
|
p, _, err := BuildLogicalPlan(ctx, s.ctx, stmt, s.is)
|
|
c.Assert(err, IsNil, comment)
|
|
p, err = logicalOptimize(context.TODO(), flagPredicatePushDown|flagDecorrelate|flagPrunColumns|flagPrunColumnsAgain, p.(LogicalPlan))
|
|
c.Assert(err, IsNil, comment)
|
|
proj, ok := p.(*LogicalProjection)
|
|
c.Assert(ok, IsTrue, comment)
|
|
selection, ok := proj.children[0].(*LogicalSelection)
|
|
c.Assert(ok, IsTrue, comment)
|
|
selCond := fmt.Sprintf("%s", selection.Conditions)
|
|
s.testData.OnRecord(func() {
|
|
output[i].Sel = selCond
|
|
})
|
|
c.Assert(selCond, Equals, output[i].Sel, comment)
|
|
join, ok := selection.children[0].(*LogicalJoin)
|
|
c.Assert(ok, IsTrue, comment)
|
|
leftPlan, ok := join.children[0].(*DataSource)
|
|
c.Assert(ok, IsTrue, comment)
|
|
rightPlan, ok := join.children[1].(*DataSource)
|
|
c.Assert(ok, IsTrue, comment)
|
|
leftCond := fmt.Sprintf("%s", leftPlan.pushedDownConds)
|
|
rightCond := fmt.Sprintf("%s", rightPlan.pushedDownConds)
|
|
s.testData.OnRecord(func() {
|
|
output[i].Left, output[i].Right = leftCond, rightCond
|
|
})
|
|
c.Assert(leftCond, Equals, output[i].Left, comment)
|
|
c.Assert(rightCond, Equals, output[i].Right, comment)
|
|
}
|
|
}
|
|
|
|
func (s *testPlanSuite) TestSimplifyOuterJoin(c *C) {
|
|
defer testleak.AfterTest(c)()
|
|
var (
|
|
input []string
|
|
output []struct {
|
|
Best string
|
|
JoinType string
|
|
}
|
|
)
|
|
s.testData.GetTestCases(c, &input, &output)
|
|
|
|
ctx := context.Background()
|
|
for i, ca := range input {
|
|
comment := Commentf("for %s", ca)
|
|
stmt, err := s.ParseOneStmt(ca, "", "")
|
|
c.Assert(err, IsNil, comment)
|
|
p, _, err := BuildLogicalPlan(ctx, s.ctx, stmt, s.is)
|
|
c.Assert(err, IsNil, comment)
|
|
p, err = logicalOptimize(context.TODO(), flagPredicatePushDown|flagPrunColumns|flagPrunColumnsAgain, p.(LogicalPlan))
|
|
c.Assert(err, IsNil, comment)
|
|
planString := ToString(p)
|
|
s.testData.OnRecord(func() {
|
|
output[i].Best = planString
|
|
})
|
|
c.Assert(planString, Equals, output[i].Best, comment)
|
|
join, ok := p.(LogicalPlan).Children()[0].(*LogicalJoin)
|
|
if !ok {
|
|
join, ok = p.(LogicalPlan).Children()[0].Children()[0].(*LogicalJoin)
|
|
c.Assert(ok, IsTrue, comment)
|
|
}
|
|
s.testData.OnRecord(func() {
|
|
output[i].JoinType = join.JoinType.String()
|
|
})
|
|
c.Assert(join.JoinType.String(), Equals, output[i].JoinType, comment)
|
|
}
|
|
}
|
|
|
|
func (s *testPlanSuite) TestAntiSemiJoinConstFalse(c *C) {
|
|
defer testleak.AfterTest(c)()
|
|
tests := []struct {
|
|
sql string
|
|
best string
|
|
joinType string
|
|
}{
|
|
{
|
|
sql: "select a from t t1 where not exists (select a from t t2 where t1.a = t2.a and t2.b = 1 and t2.b = 2)",
|
|
best: "Join{DataScan(t1)->DataScan(t2)}(test.t.a,test.t.a)->Projection",
|
|
joinType: "anti semi join",
|
|
},
|
|
}
|
|
|
|
ctx := context.Background()
|
|
for _, ca := range tests {
|
|
comment := Commentf("for %s", ca.sql)
|
|
stmt, err := s.ParseOneStmt(ca.sql, "", "")
|
|
c.Assert(err, IsNil, comment)
|
|
p, _, err := BuildLogicalPlan(ctx, s.ctx, stmt, s.is)
|
|
c.Assert(err, IsNil, comment)
|
|
p, err = logicalOptimize(context.TODO(), flagDecorrelate|flagPredicatePushDown|flagPrunColumns|flagPrunColumnsAgain, p.(LogicalPlan))
|
|
c.Assert(err, IsNil, comment)
|
|
c.Assert(ToString(p), Equals, ca.best, comment)
|
|
join, _ := p.(LogicalPlan).Children()[0].(*LogicalJoin)
|
|
c.Assert(join.JoinType.String(), Equals, ca.joinType, comment)
|
|
}
|
|
}
|
|
|
|
func (s *testPlanSuite) TestDeriveNotNullConds(c *C) {
|
|
defer testleak.AfterTest(c)()
|
|
var (
|
|
input []string
|
|
output []struct {
|
|
Plan string
|
|
Left string
|
|
Right string
|
|
}
|
|
)
|
|
s.testData.GetTestCases(c, &input, &output)
|
|
|
|
ctx := context.Background()
|
|
for i, ca := range input {
|
|
comment := Commentf("for %s", ca)
|
|
stmt, err := s.ParseOneStmt(ca, "", "")
|
|
c.Assert(err, IsNil, comment)
|
|
p, _, err := BuildLogicalPlan(ctx, s.ctx, stmt, s.is)
|
|
c.Assert(err, IsNil, comment)
|
|
p, err = logicalOptimize(context.TODO(), flagPredicatePushDown|flagPrunColumns|flagPrunColumnsAgain|flagDecorrelate, p.(LogicalPlan))
|
|
c.Assert(err, IsNil, comment)
|
|
s.testData.OnRecord(func() {
|
|
output[i].Plan = ToString(p)
|
|
})
|
|
c.Assert(ToString(p), Equals, output[i].Plan, comment)
|
|
join := p.(LogicalPlan).Children()[0].(*LogicalJoin)
|
|
left := join.Children()[0].(*DataSource)
|
|
right := join.Children()[1].(*DataSource)
|
|
leftConds := fmt.Sprintf("%s", left.pushedDownConds)
|
|
rightConds := fmt.Sprintf("%s", right.pushedDownConds)
|
|
s.testData.OnRecord(func() {
|
|
output[i].Left, output[i].Right = leftConds, rightConds
|
|
})
|
|
c.Assert(leftConds, Equals, output[i].Left, comment)
|
|
c.Assert(rightConds, Equals, output[i].Right, comment)
|
|
}
|
|
}
|
|
|
|
func buildLogicPlan4GroupBy(s *testPlanSuite, c *C, sql string) (Plan, error) {
|
|
sqlMode := s.ctx.GetSessionVars().SQLMode
|
|
mockedTableInfo := MockSignedTable()
|
|
// mock the table info here for later use
|
|
// enable only full group by
|
|
s.ctx.GetSessionVars().SQLMode = sqlMode | mysql.ModeOnlyFullGroupBy
|
|
defer func() { s.ctx.GetSessionVars().SQLMode = sqlMode }() // restore it
|
|
comment := Commentf("for %s", sql)
|
|
stmt, err := s.ParseOneStmt(sql, "", "")
|
|
c.Assert(err, IsNil, comment)
|
|
|
|
stmt.(*ast.SelectStmt).From.TableRefs.Left.(*ast.TableSource).Source.(*ast.TableName).TableInfo = mockedTableInfo
|
|
|
|
p, _, err := BuildLogicalPlan(context.Background(), s.ctx, stmt, s.is)
|
|
return p, err
|
|
}
|
|
|
|
func (s *testPlanSuite) TestGroupByWhenNotExistCols(c *C) {
|
|
sqlTests := []struct {
|
|
sql string
|
|
expectedErrMatch string
|
|
}{
|
|
{
|
|
sql: "select a from t group by b",
|
|
expectedErrMatch: ".*contains nonaggregated column 'test\\.t\\.a'.*",
|
|
},
|
|
{
|
|
// has an as column alias
|
|
sql: "select a as tempField from t group by b",
|
|
expectedErrMatch: ".*contains nonaggregated column 'test\\.t\\.a'.*",
|
|
},
|
|
{
|
|
// has as table alias
|
|
sql: "select tempTable.a from t as tempTable group by b",
|
|
expectedErrMatch: ".*contains nonaggregated column 'test\\.tempTable\\.a'.*",
|
|
},
|
|
{
|
|
// has a func call
|
|
sql: "select length(a) from t group by b",
|
|
expectedErrMatch: ".*contains nonaggregated column 'test\\.t\\.a'.*",
|
|
},
|
|
{
|
|
// has a func call with two cols
|
|
sql: "select length(b + a) from t group by b",
|
|
expectedErrMatch: ".*contains nonaggregated column 'test\\.t\\.a'.*",
|
|
},
|
|
{
|
|
// has a func call with two cols
|
|
sql: "select length(a + b) from t group by b",
|
|
expectedErrMatch: ".*contains nonaggregated column 'test\\.t\\.a'.*",
|
|
},
|
|
{
|
|
// has a func call with two cols
|
|
sql: "select length(a + b) as tempField from t group by b",
|
|
expectedErrMatch: ".*contains nonaggregated column 'test\\.t\\.a'.*",
|
|
},
|
|
}
|
|
for _, test := range sqlTests {
|
|
sql := test.sql
|
|
p, err := buildLogicPlan4GroupBy(s, c, sql)
|
|
c.Assert(err, NotNil)
|
|
c.Assert(p, IsNil)
|
|
c.Assert(err, ErrorMatches, test.expectedErrMatch)
|
|
}
|
|
}
|
|
|
|
func (s *testPlanSuite) TestDupRandJoinCondsPushDown(c *C) {
|
|
sql := "select * from t as t1 join t t2 on t1.a > rand() and t1.a > rand()"
|
|
comment := Commentf("for %s", sql)
|
|
stmt, err := s.ParseOneStmt(sql, "", "")
|
|
c.Assert(err, IsNil, comment)
|
|
p, _, err := BuildLogicalPlan(context.Background(), s.ctx, stmt, s.is)
|
|
c.Assert(err, IsNil, comment)
|
|
p, err = logicalOptimize(context.TODO(), flagPredicatePushDown, p.(LogicalPlan))
|
|
c.Assert(err, IsNil, comment)
|
|
proj, ok := p.(*LogicalProjection)
|
|
c.Assert(ok, IsTrue, comment)
|
|
join, ok := proj.children[0].(*LogicalJoin)
|
|
c.Assert(ok, IsTrue, comment)
|
|
leftPlan, ok := join.children[0].(*LogicalSelection)
|
|
c.Assert(ok, IsTrue, comment)
|
|
leftCond := fmt.Sprintf("%s", leftPlan.Conditions)
|
|
// Condition with mutable function cannot be de-duplicated when push down join conds.
|
|
c.Assert(leftCond, Equals, "[gt(cast(test.t.a, double BINARY), rand()) gt(cast(test.t.a, double BINARY), rand())]", comment)
|
|
}
|
|
|
|
func (s *testPlanSuite) TestTablePartition(c *C) {
|
|
defer testleak.AfterTest(c)()
|
|
definitions := []model.PartitionDefinition{
|
|
{
|
|
ID: 41,
|
|
Name: model.NewCIStr("p1"),
|
|
LessThan: []string{"16"},
|
|
},
|
|
{
|
|
ID: 42,
|
|
Name: model.NewCIStr("p2"),
|
|
LessThan: []string{"32"},
|
|
},
|
|
{
|
|
ID: 43,
|
|
Name: model.NewCIStr("p3"),
|
|
LessThan: []string{"64"},
|
|
},
|
|
{
|
|
ID: 44,
|
|
Name: model.NewCIStr("p4"),
|
|
LessThan: []string{"128"},
|
|
},
|
|
{
|
|
ID: 45,
|
|
Name: model.NewCIStr("p5"),
|
|
LessThan: []string{"maxvalue"},
|
|
},
|
|
}
|
|
is := MockPartitionInfoSchema(definitions)
|
|
// is1 equals to is without maxvalue partition.
|
|
definitions1 := make([]model.PartitionDefinition, len(definitions)-1)
|
|
copy(definitions1, definitions)
|
|
is1 := MockPartitionInfoSchema(definitions1)
|
|
isChoices := []infoschema.InfoSchema{is, is1}
|
|
|
|
var (
|
|
input []struct {
|
|
SQL string
|
|
IsIdx int
|
|
}
|
|
output []string
|
|
)
|
|
s.testData.GetTestCases(c, &input, &output)
|
|
|
|
ctx := context.Background()
|
|
for i, ca := range input {
|
|
comment := Commentf("for %s", ca.SQL)
|
|
stmt, err := s.ParseOneStmt(ca.SQL, "", "")
|
|
c.Assert(err, IsNil, comment)
|
|
s.testData.OnRecord(func() {
|
|
|
|
})
|
|
p, _, err := BuildLogicalPlan(ctx, s.ctx, stmt, isChoices[ca.IsIdx])
|
|
c.Assert(err, IsNil)
|
|
p, err = logicalOptimize(context.TODO(), flagDecorrelate|flagPrunColumns|flagPrunColumnsAgain|flagPredicatePushDown|flagPartitionProcessor, p.(LogicalPlan))
|
|
c.Assert(err, IsNil)
|
|
planString := ToString(p)
|
|
s.testData.OnRecord(func() {
|
|
output[i] = planString
|
|
})
|
|
c.Assert(ToString(p), Equals, output[i], Commentf("for %s", ca))
|
|
}
|
|
}
|
|
|
|
func (s *testPlanSuite) TestSubquery(c *C) {
|
|
defer testleak.AfterTest(c)()
|
|
var input, output []string
|
|
s.testData.GetTestCases(c, &input, &output)
|
|
|
|
ctx := context.Background()
|
|
for ith, ca := range input {
|
|
comment := Commentf("for %s", ca)
|
|
stmt, err := s.ParseOneStmt(ca, "", "")
|
|
c.Assert(err, IsNil, comment)
|
|
|
|
Preprocess(s.ctx, stmt, s.is)
|
|
p, _, err := BuildLogicalPlan(ctx, s.ctx, stmt, s.is)
|
|
c.Assert(err, IsNil)
|
|
if lp, ok := p.(LogicalPlan); ok {
|
|
p, err = logicalOptimize(context.TODO(), flagBuildKeyInfo|flagDecorrelate|flagPrunColumns|flagPrunColumnsAgain, lp)
|
|
c.Assert(err, IsNil)
|
|
}
|
|
s.testData.OnRecord(func() {
|
|
output[ith] = ToString(p)
|
|
})
|
|
c.Assert(ToString(p), Equals, output[ith], Commentf("for %s %d", ca, ith))
|
|
}
|
|
}
|
|
|
|
func (s *testPlanSuite) TestPlanBuilder(c *C) {
|
|
defer testleak.AfterTest(c)()
|
|
var input, output []string
|
|
s.testData.GetTestCases(c, &input, &output)
|
|
ctx := context.Background()
|
|
for i, ca := range input {
|
|
comment := Commentf("for %s", ca)
|
|
stmt, err := s.ParseOneStmt(ca, "", "")
|
|
c.Assert(err, IsNil, comment)
|
|
|
|
s.ctx.GetSessionVars().HashJoinConcurrency = 1
|
|
Preprocess(s.ctx, stmt, s.is)
|
|
p, _, err := BuildLogicalPlan(ctx, s.ctx, stmt, s.is)
|
|
c.Assert(err, IsNil)
|
|
if lp, ok := p.(LogicalPlan); ok {
|
|
p, err = logicalOptimize(context.TODO(), flagPrunColumns|flagPrunColumnsAgain, lp)
|
|
c.Assert(err, IsNil)
|
|
}
|
|
s.testData.OnRecord(func() {
|
|
output[i] = ToString(p)
|
|
})
|
|
c.Assert(ToString(p), Equals, output[i], Commentf("for %s", ca))
|
|
}
|
|
}
|
|
|
|
func (s *testPlanSuite) TestJoinReOrder(c *C) {
|
|
defer testleak.AfterTest(c)()
|
|
var input, output []string
|
|
s.testData.GetTestCases(c, &input, &output)
|
|
|
|
ctx := context.Background()
|
|
for i, tt := range input {
|
|
comment := Commentf("for %s", tt)
|
|
stmt, err := s.ParseOneStmt(tt, "", "")
|
|
c.Assert(err, IsNil, comment)
|
|
|
|
p, _, err := BuildLogicalPlan(ctx, s.ctx, stmt, s.is)
|
|
c.Assert(err, IsNil)
|
|
p, err = logicalOptimize(context.TODO(), flagPredicatePushDown|flagJoinReOrder, p.(LogicalPlan))
|
|
c.Assert(err, IsNil)
|
|
planString := ToString(p)
|
|
s.testData.OnRecord(func() {
|
|
output[i] = planString
|
|
})
|
|
c.Assert(planString, Equals, output[i], Commentf("for %s", tt))
|
|
}
|
|
}
|
|
|
|
func (s *testPlanSuite) TestEagerAggregation(c *C) {
|
|
defer testleak.AfterTest(c)()
|
|
var input []string
|
|
var output []string
|
|
s.testData.GetTestCases(c, &input, &output)
|
|
ctx := context.Background()
|
|
s.ctx.GetSessionVars().AllowAggPushDown = true
|
|
for ith, tt := range input {
|
|
comment := Commentf("for %s", tt)
|
|
stmt, err := s.ParseOneStmt(tt, "", "")
|
|
c.Assert(err, IsNil, comment)
|
|
|
|
p, _, err := BuildLogicalPlan(ctx, s.ctx, stmt, s.is)
|
|
c.Assert(err, IsNil)
|
|
p, err = logicalOptimize(context.TODO(), flagBuildKeyInfo|flagPredicatePushDown|flagPrunColumns|flagPrunColumnsAgain|flagPushDownAgg, p.(LogicalPlan))
|
|
c.Assert(err, IsNil)
|
|
s.testData.OnRecord(func() {
|
|
output[ith] = ToString(p)
|
|
})
|
|
c.Assert(ToString(p), Equals, output[ith], Commentf("for %s %d", tt, ith))
|
|
}
|
|
s.ctx.GetSessionVars().AllowAggPushDown = false
|
|
}
|
|
|
|
func (s *testPlanSuite) TestColumnPruning(c *C) {
|
|
defer testleak.AfterTest(c)()
|
|
var (
|
|
input []string
|
|
output []map[int][]string
|
|
)
|
|
s.testData.GetTestCases(c, &input, &output)
|
|
|
|
ctx := context.Background()
|
|
for i, tt := range input {
|
|
comment := Commentf("for %s", tt)
|
|
stmt, err := s.ParseOneStmt(tt, "", "")
|
|
c.Assert(err, IsNil, comment)
|
|
|
|
p, _, err := BuildLogicalPlan(ctx, s.ctx, stmt, s.is)
|
|
c.Assert(err, IsNil)
|
|
lp, err := logicalOptimize(ctx, flagPredicatePushDown|flagPrunColumns|flagPrunColumnsAgain, p.(LogicalPlan))
|
|
c.Assert(err, IsNil)
|
|
s.testData.OnRecord(func() {
|
|
output[i] = make(map[int][]string)
|
|
})
|
|
s.checkDataSourceCols(lp, c, output[i], comment)
|
|
}
|
|
}
|
|
|
|
func (s *testPlanSuite) TestSortByItemsPruning(c *C) {
|
|
defer testleak.AfterTest(c)()
|
|
var (
|
|
input []string
|
|
output [][]string
|
|
)
|
|
s.testData.GetTestCases(c, &input, &output)
|
|
s.testData.OnRecord(func() {
|
|
output = make([][]string, len(input))
|
|
})
|
|
|
|
ctx := context.Background()
|
|
for i, tt := range input {
|
|
comment := Commentf("for %s", tt)
|
|
stmt, err := s.ParseOneStmt(tt, "", "")
|
|
c.Assert(err, IsNil, comment)
|
|
|
|
p, _, err := BuildLogicalPlan(ctx, s.ctx, stmt, s.is)
|
|
c.Assert(err, IsNil)
|
|
lp, err := logicalOptimize(ctx, flagEliminateProjection|flagPredicatePushDown|flagPrunColumns|flagPrunColumnsAgain, p.(LogicalPlan))
|
|
c.Assert(err, IsNil)
|
|
s.checkOrderByItems(lp, c, &output[i], comment)
|
|
}
|
|
}
|
|
|
|
func (s *testPlanSuite) TestProjectionEliminator(c *C) {
|
|
defer testleak.AfterTest(c)()
|
|
tests := []struct {
|
|
sql string
|
|
best string
|
|
}{
|
|
{
|
|
sql: "select 1+num from (select 1+a as num from t) t1;",
|
|
best: "DataScan(t)->Projection",
|
|
},
|
|
}
|
|
|
|
ctx := context.Background()
|
|
for ith, tt := range tests {
|
|
comment := Commentf("for %s", tt.sql)
|
|
stmt, err := s.ParseOneStmt(tt.sql, "", "")
|
|
c.Assert(err, IsNil, comment)
|
|
|
|
p, _, err := BuildLogicalPlan(ctx, s.ctx, stmt, s.is)
|
|
c.Assert(err, IsNil)
|
|
p, err = logicalOptimize(context.TODO(), flagBuildKeyInfo|flagPrunColumns|flagPrunColumnsAgain|flagEliminateProjection, p.(LogicalPlan))
|
|
c.Assert(err, IsNil)
|
|
c.Assert(ToString(p), Equals, tt.best, Commentf("for %s %d", tt.sql, ith))
|
|
}
|
|
}
|
|
|
|
func (s *testPlanSuite) TestAllocID(c *C) {
|
|
ctx := MockContext()
|
|
pA := DataSource{}.Init(ctx, 0)
|
|
pB := DataSource{}.Init(ctx, 0)
|
|
c.Assert(pA.id+1, Equals, pB.id)
|
|
}
|
|
|
|
func (s *testPlanSuite) checkDataSourceCols(p LogicalPlan, c *C, ans map[int][]string, comment CommentInterface) {
|
|
switch p.(type) {
|
|
case *DataSource:
|
|
s.testData.OnRecord(func() {
|
|
ans[p.ID()] = make([]string, p.Schema().Len())
|
|
})
|
|
colList, ok := ans[p.ID()]
|
|
c.Assert(ok, IsTrue, Commentf("For %v DataSource ID %d Not found", comment, p.ID()))
|
|
c.Assert(len(p.Schema().Columns), Equals, len(colList), comment)
|
|
for i, col := range p.Schema().Columns {
|
|
s.testData.OnRecord(func() {
|
|
colList[i] = col.String()
|
|
})
|
|
c.Assert(col.String(), Equals, colList[i], comment)
|
|
}
|
|
case *LogicalUnionAll:
|
|
s.testData.OnRecord(func() {
|
|
ans[p.ID()] = make([]string, p.Schema().Len())
|
|
})
|
|
colList, ok := ans[p.ID()]
|
|
c.Assert(ok, IsTrue, Commentf("For %v UnionAll ID %d Not found", comment, p.ID()))
|
|
c.Assert(len(p.Schema().Columns), Equals, len(colList), comment)
|
|
for i, col := range p.Schema().Columns {
|
|
s.testData.OnRecord(func() {
|
|
colList[i] = col.String()
|
|
})
|
|
c.Assert(col.String(), Equals, colList[i], comment)
|
|
}
|
|
}
|
|
for _, child := range p.Children() {
|
|
s.checkDataSourceCols(child, c, ans, comment)
|
|
}
|
|
}
|
|
|
|
func (s *testPlanSuite) checkOrderByItems(p LogicalPlan, c *C, colList *[]string, comment CommentInterface) {
|
|
switch p := p.(type) {
|
|
case *LogicalSort:
|
|
s.testData.OnRecord(func() {
|
|
*colList = make([]string, len(p.ByItems))
|
|
})
|
|
for i, col := range p.ByItems {
|
|
s.testData.OnRecord(func() {
|
|
(*colList)[i] = col.String()
|
|
})
|
|
s := col.String()
|
|
c.Assert(s, Equals, (*colList)[i], comment)
|
|
}
|
|
}
|
|
children := p.Children()
|
|
c.Assert(len(children), LessEqual, 1, Commentf("For %v Expected <= 1 Child", comment))
|
|
for _, child := range children {
|
|
s.checkOrderByItems(child, c, colList, comment)
|
|
}
|
|
}
|
|
|
|
func (s *testPlanSuite) TestValidate(c *C) {
|
|
defer testleak.AfterTest(c)()
|
|
tests := []struct {
|
|
sql string
|
|
err *terror.Error
|
|
}{
|
|
{
|
|
sql: "select date_format((1,2), '%H');",
|
|
err: expression.ErrOperandColumns,
|
|
},
|
|
{
|
|
sql: "select cast((1,2) as date)",
|
|
err: expression.ErrOperandColumns,
|
|
},
|
|
{
|
|
sql: "select (1,2) between (3,4) and (5,6)",
|
|
err: expression.ErrOperandColumns,
|
|
},
|
|
{
|
|
sql: "select (1,2) rlike '1'",
|
|
err: expression.ErrOperandColumns,
|
|
},
|
|
{
|
|
sql: "select (1,2) like '1'",
|
|
err: expression.ErrOperandColumns,
|
|
},
|
|
{
|
|
sql: "select case(1,2) when(1,2) then true end",
|
|
err: expression.ErrOperandColumns,
|
|
},
|
|
{
|
|
sql: "select (1,2) in ((3,4),(5,6))",
|
|
err: nil,
|
|
},
|
|
{
|
|
sql: "select row(1,(2,3)) in (select a,b from t)",
|
|
err: expression.ErrOperandColumns,
|
|
},
|
|
{
|
|
sql: "select row(1,2) in (select a,b from t)",
|
|
err: nil,
|
|
},
|
|
{
|
|
sql: "select (1,2) in ((3,4),5)",
|
|
err: expression.ErrOperandColumns,
|
|
},
|
|
{
|
|
sql: "select (1,2) is true",
|
|
err: expression.ErrOperandColumns,
|
|
},
|
|
{
|
|
sql: "select (1,2) is null",
|
|
err: expression.ErrOperandColumns,
|
|
},
|
|
{
|
|
sql: "select (+(1,2))=(1,2)",
|
|
err: nil,
|
|
},
|
|
{
|
|
sql: "select (-(1,2))=(1,2)",
|
|
err: expression.ErrOperandColumns,
|
|
},
|
|
{
|
|
sql: "select (1,2)||(1,2)",
|
|
err: expression.ErrOperandColumns,
|
|
},
|
|
{
|
|
sql: "select (1,2) < (3,4)",
|
|
err: nil,
|
|
},
|
|
{
|
|
sql: "select (1,2) < 3",
|
|
err: expression.ErrOperandColumns,
|
|
},
|
|
{
|
|
sql: "select 1, * from t",
|
|
err: ErrInvalidWildCard,
|
|
},
|
|
{
|
|
sql: "select *, 1 from t",
|
|
err: nil,
|
|
},
|
|
{
|
|
sql: "select 1, t.* from t",
|
|
err: nil,
|
|
},
|
|
{
|
|
sql: "select 1 from t t1, t t2 where t1.a > all((select a) union (select a))",
|
|
err: ErrAmbiguous,
|
|
},
|
|
{
|
|
sql: "insert into t set a = 1, b = a + 1",
|
|
err: nil,
|
|
},
|
|
{
|
|
sql: "insert into t set a = 1, b = values(a) + 1",
|
|
err: nil,
|
|
},
|
|
{
|
|
sql: "select a, b, c from t order by 0",
|
|
err: ErrUnknownColumn,
|
|
},
|
|
{
|
|
sql: "select a, b, c from t order by 4",
|
|
err: ErrUnknownColumn,
|
|
},
|
|
{
|
|
sql: "select a as c1, b as c1 from t order by c1",
|
|
err: ErrAmbiguous,
|
|
},
|
|
{
|
|
sql: "(select a as b, b from t) union (select a, b from t) order by b",
|
|
err: ErrAmbiguous,
|
|
},
|
|
{
|
|
sql: "(select a as b, b from t) union (select a, b from t) order by a",
|
|
err: ErrUnknownColumn,
|
|
},
|
|
{
|
|
sql: "select * from t t1 use index(e)",
|
|
err: ErrKeyDoesNotExist,
|
|
},
|
|
{
|
|
sql: "select a from t having c2",
|
|
err: ErrUnknownColumn,
|
|
},
|
|
{
|
|
sql: "select a from t group by c2 + 1 having c2",
|
|
err: ErrUnknownColumn,
|
|
},
|
|
{
|
|
sql: "select a as b, b from t having b",
|
|
err: ErrAmbiguous,
|
|
},
|
|
{
|
|
sql: "select a + 1 from t having a",
|
|
err: ErrUnknownColumn,
|
|
},
|
|
{
|
|
sql: "select a from t having sum(avg(a))",
|
|
err: ErrInvalidGroupFuncUse,
|
|
},
|
|
{
|
|
sql: "select concat(c_str, d_str) from t group by `concat(c_str, d_str)`",
|
|
err: nil,
|
|
},
|
|
{
|
|
sql: "select concat(c_str, d_str) from t group by `concat(c_str,d_str)`",
|
|
err: ErrUnknownColumn,
|
|
},
|
|
{
|
|
sql: "select a from t b having b.a",
|
|
err: nil,
|
|
},
|
|
{
|
|
sql: "select b.a from t b having b.a",
|
|
err: nil,
|
|
},
|
|
{
|
|
sql: "select b.a from t b having a",
|
|
err: nil,
|
|
},
|
|
{
|
|
sql: "select a+1 from t having t.a",
|
|
err: ErrUnknownColumn,
|
|
},
|
|
}
|
|
|
|
ctx := context.Background()
|
|
for _, tt := range tests {
|
|
sql := tt.sql
|
|
comment := Commentf("for %s", sql)
|
|
stmt, err := s.ParseOneStmt(sql, "", "")
|
|
c.Assert(err, IsNil, comment)
|
|
Preprocess(s.ctx, stmt, s.is)
|
|
_, _, err = BuildLogicalPlan(ctx, s.ctx, stmt, s.is)
|
|
if tt.err == nil {
|
|
c.Assert(err, IsNil, comment)
|
|
} else {
|
|
c.Assert(tt.err.Equal(err), IsTrue, comment)
|
|
}
|
|
}
|
|
}
|
|
|
|
func (s *testPlanSuite) checkUniqueKeys(p LogicalPlan, c *C, ans map[int][][]string, sql string) {
|
|
s.testData.OnRecord(func() {
|
|
ans[p.ID()] = make([][]string, len(p.Schema().Keys))
|
|
})
|
|
keyList, ok := ans[p.ID()]
|
|
c.Assert(ok, IsTrue, Commentf("for %s, %v not found", sql, p.ID()))
|
|
c.Assert(len(p.Schema().Keys), Equals, len(keyList), Commentf("for %s, %v, the number of key doesn't match, the schema is %s", sql, p.ID(), p.Schema()))
|
|
for i := range keyList {
|
|
s.testData.OnRecord(func() {
|
|
keyList[i] = make([]string, len(p.Schema().Keys[i]))
|
|
})
|
|
c.Assert(len(p.Schema().Keys[i]), Equals, len(keyList[i]), Commentf("for %s, %v %v, the number of column doesn't match", sql, p.ID(), keyList[i]))
|
|
for j := range keyList[i] {
|
|
s.testData.OnRecord(func() {
|
|
keyList[i][j] = p.Schema().Keys[i][j].String()
|
|
})
|
|
c.Assert(p.Schema().Keys[i][j].String(), Equals, keyList[i][j], Commentf("for %s, %v %v, column dosen't match", sql, p.ID(), keyList[i]))
|
|
}
|
|
}
|
|
s.testData.OnRecord(func() {
|
|
ans[p.ID()] = keyList
|
|
})
|
|
for _, child := range p.Children() {
|
|
s.checkUniqueKeys(child, c, ans, sql)
|
|
}
|
|
}
|
|
|
|
func (s *testPlanSuite) TestUniqueKeyInfo(c *C) {
|
|
defer testleak.AfterTest(c)()
|
|
var input []string
|
|
var output []map[int][][]string
|
|
s.testData.GetTestCases(c, &input, &output)
|
|
s.testData.OnRecord(func() {
|
|
output = make([]map[int][][]string, len(input))
|
|
})
|
|
|
|
ctx := context.Background()
|
|
for ith, tt := range input {
|
|
comment := Commentf("for %s %d", tt, ith)
|
|
stmt, err := s.ParseOneStmt(tt, "", "")
|
|
c.Assert(err, IsNil, comment)
|
|
|
|
p, _, err := BuildLogicalPlan(ctx, s.ctx, stmt, s.is)
|
|
c.Assert(err, IsNil)
|
|
lp, err := logicalOptimize(context.TODO(), flagPredicatePushDown|flagPrunColumns|flagBuildKeyInfo, p.(LogicalPlan))
|
|
c.Assert(err, IsNil)
|
|
s.testData.OnRecord(func() {
|
|
output[ith] = make(map[int][][]string)
|
|
})
|
|
s.checkUniqueKeys(lp, c, output[ith], tt)
|
|
}
|
|
}
|
|
|
|
func (s *testPlanSuite) TestAggPrune(c *C) {
|
|
defer testleak.AfterTest(c)()
|
|
var input, output []string
|
|
s.testData.GetTestCases(c, &input, &output)
|
|
|
|
ctx := context.Background()
|
|
for i, tt := range input {
|
|
comment := Commentf("for %s", tt)
|
|
stmt, err := s.ParseOneStmt(tt, "", "")
|
|
c.Assert(err, IsNil, comment)
|
|
|
|
p, _, err := BuildLogicalPlan(ctx, s.ctx, stmt, s.is)
|
|
c.Assert(err, IsNil)
|
|
|
|
p, err = logicalOptimize(context.TODO(), flagPredicatePushDown|flagPrunColumns|flagPrunColumnsAgain|flagBuildKeyInfo|flagEliminateAgg|flagEliminateProjection, p.(LogicalPlan))
|
|
c.Assert(err, IsNil)
|
|
planString := ToString(p)
|
|
s.testData.OnRecord(func() {
|
|
output[i] = planString
|
|
})
|
|
c.Assert(planString, Equals, output[i], comment)
|
|
}
|
|
}
|
|
|
|
func (s *testPlanSuite) TestVisitInfo(c *C) {
|
|
defer testleak.AfterTest(c)()
|
|
tests := []struct {
|
|
sql string
|
|
ans []visitInfo
|
|
}{
|
|
{
|
|
sql: "insert into t (a) values (1)",
|
|
ans: []visitInfo{
|
|
{mysql.InsertPriv, "test", "t", "", nil},
|
|
},
|
|
},
|
|
{
|
|
sql: "delete from t where a = 1",
|
|
ans: []visitInfo{
|
|
{mysql.DeletePriv, "test", "t", "", nil},
|
|
{mysql.SelectPriv, "test", "t", "", nil},
|
|
},
|
|
},
|
|
{
|
|
sql: "delete from a1 using t as a1 inner join t as a2 where a1.a = a2.a",
|
|
ans: []visitInfo{
|
|
{mysql.DeletePriv, "test", "t", "", nil},
|
|
{mysql.SelectPriv, "test", "t", "", nil},
|
|
},
|
|
},
|
|
{
|
|
sql: "update t set a = 7 where a = 1",
|
|
ans: []visitInfo{
|
|
{mysql.UpdatePriv, "test", "t", "", nil},
|
|
{mysql.SelectPriv, "test", "t", "", nil},
|
|
},
|
|
},
|
|
{
|
|
sql: "update t, (select * from t) a1 set t.a = a1.a;",
|
|
ans: []visitInfo{
|
|
{mysql.UpdatePriv, "test", "t", "", nil},
|
|
{mysql.SelectPriv, "test", "t", "", nil},
|
|
},
|
|
},
|
|
{
|
|
sql: "update t a1 set a1.a = a1.a + 1",
|
|
ans: []visitInfo{
|
|
{mysql.UpdatePriv, "test", "t", "", nil},
|
|
{mysql.SelectPriv, "test", "t", "", nil},
|
|
},
|
|
},
|
|
{
|
|
sql: "select a, sum(e) from t group by a",
|
|
ans: []visitInfo{
|
|
{mysql.SelectPriv, "test", "t", "", nil},
|
|
},
|
|
},
|
|
{
|
|
sql: "truncate table t",
|
|
ans: []visitInfo{
|
|
{mysql.DropPriv, "test", "t", "", nil},
|
|
},
|
|
},
|
|
{
|
|
sql: "drop table t",
|
|
ans: []visitInfo{
|
|
{mysql.DropPriv, "test", "t", "", nil},
|
|
},
|
|
},
|
|
{
|
|
sql: "create table t (a int)",
|
|
ans: []visitInfo{
|
|
{mysql.CreatePriv, "test", "t", "", nil},
|
|
},
|
|
},
|
|
{
|
|
sql: "create table t1 like t",
|
|
ans: []visitInfo{
|
|
{mysql.CreatePriv, "test", "t1", "", nil},
|
|
{mysql.SelectPriv, "test", "t", "", nil},
|
|
},
|
|
},
|
|
{
|
|
sql: "create database test",
|
|
ans: []visitInfo{
|
|
{mysql.CreatePriv, "test", "", "", nil},
|
|
},
|
|
},
|
|
{
|
|
sql: "drop database test",
|
|
ans: []visitInfo{
|
|
{mysql.DropPriv, "test", "", "", nil},
|
|
},
|
|
},
|
|
{
|
|
sql: "create index t_1 on t (a)",
|
|
ans: []visitInfo{
|
|
{mysql.IndexPriv, "test", "t", "", nil},
|
|
},
|
|
},
|
|
{
|
|
sql: "drop index e on t",
|
|
ans: []visitInfo{
|
|
{mysql.IndexPriv, "test", "t", "", nil},
|
|
},
|
|
},
|
|
{
|
|
sql: `grant all privileges on test.* to 'test'@'%'`,
|
|
ans: []visitInfo{
|
|
{mysql.SelectPriv, "test", "", "", nil},
|
|
{mysql.InsertPriv, "test", "", "", nil},
|
|
{mysql.UpdatePriv, "test", "", "", nil},
|
|
{mysql.DeletePriv, "test", "", "", nil},
|
|
{mysql.CreatePriv, "test", "", "", nil},
|
|
{mysql.DropPriv, "test", "", "", nil},
|
|
{mysql.GrantPriv, "test", "", "", nil},
|
|
{mysql.AlterPriv, "test", "", "", nil},
|
|
{mysql.ExecutePriv, "test", "", "", nil},
|
|
{mysql.IndexPriv, "test", "", "", nil},
|
|
{mysql.CreateViewPriv, "test", "", "", nil},
|
|
{mysql.ShowViewPriv, "test", "", "", nil},
|
|
},
|
|
},
|
|
{
|
|
sql: `grant select on test.ttt to 'test'@'%'`,
|
|
ans: []visitInfo{
|
|
{mysql.SelectPriv, "test", "ttt", "", nil},
|
|
{mysql.GrantPriv, "test", "ttt", "", nil},
|
|
},
|
|
},
|
|
{
|
|
sql: `grant select on ttt to 'test'@'%'`,
|
|
ans: []visitInfo{
|
|
{mysql.SelectPriv, "test", "ttt", "", nil},
|
|
{mysql.GrantPriv, "test", "ttt", "", nil},
|
|
},
|
|
},
|
|
{
|
|
sql: `revoke all privileges on test.* from 'test'@'%'`,
|
|
ans: []visitInfo{
|
|
{mysql.SelectPriv, "test", "", "", nil},
|
|
{mysql.InsertPriv, "test", "", "", nil},
|
|
{mysql.UpdatePriv, "test", "", "", nil},
|
|
{mysql.DeletePriv, "test", "", "", nil},
|
|
{mysql.CreatePriv, "test", "", "", nil},
|
|
{mysql.DropPriv, "test", "", "", nil},
|
|
{mysql.GrantPriv, "test", "", "", nil},
|
|
{mysql.AlterPriv, "test", "", "", nil},
|
|
{mysql.ExecutePriv, "test", "", "", nil},
|
|
{mysql.IndexPriv, "test", "", "", nil},
|
|
{mysql.CreateViewPriv, "test", "", "", nil},
|
|
{mysql.ShowViewPriv, "test", "", "", nil},
|
|
},
|
|
},
|
|
{
|
|
sql: `set password for 'root'@'%' = 'xxxxx'`,
|
|
ans: []visitInfo{},
|
|
},
|
|
{
|
|
sql: `show create table test.ttt`,
|
|
ans: []visitInfo{
|
|
{mysql.AllPrivMask, "test", "ttt", "", nil},
|
|
},
|
|
},
|
|
{
|
|
sql: "alter table t add column a int(4)",
|
|
ans: []visitInfo{
|
|
{mysql.AlterPriv, "test", "t", "", nil},
|
|
},
|
|
},
|
|
{
|
|
sql: "rename table t_old to t_new",
|
|
ans: []visitInfo{
|
|
{mysql.AlterPriv, "test", "t_old", "", nil},
|
|
{mysql.DropPriv, "test", "t_old", "", nil},
|
|
{mysql.CreatePriv, "test", "t_new", "", nil},
|
|
{mysql.InsertPriv, "test", "t_new", "", nil},
|
|
},
|
|
},
|
|
{
|
|
sql: "alter table t_old rename to t_new",
|
|
ans: []visitInfo{
|
|
{mysql.AlterPriv, "test", "t_old", "", nil},
|
|
{mysql.DropPriv, "test", "t_old", "", nil},
|
|
{mysql.CreatePriv, "test", "t_new", "", nil},
|
|
{mysql.InsertPriv, "test", "t_new", "", nil},
|
|
},
|
|
},
|
|
{
|
|
sql: "alter table t drop partition p0;",
|
|
ans: []visitInfo{
|
|
{mysql.AlterPriv, "test", "t", "", nil},
|
|
{mysql.DropPriv, "test", "t", "", nil},
|
|
},
|
|
},
|
|
{
|
|
sql: "flush privileges",
|
|
ans: []visitInfo{
|
|
{mysql.ReloadPriv, "", "", "", ErrSpecificAccessDenied},
|
|
},
|
|
},
|
|
}
|
|
|
|
for _, tt := range tests {
|
|
comment := Commentf("for %s", tt.sql)
|
|
stmt, err := s.ParseOneStmt(tt.sql, "", "")
|
|
c.Assert(err, IsNil, comment)
|
|
Preprocess(s.ctx, stmt, s.is)
|
|
builder := NewPlanBuilder(MockContext(), s.is, &hint.BlockHintProcessor{})
|
|
builder.ctx.GetSessionVars().HashJoinConcurrency = 1
|
|
_, err = builder.Build(context.TODO(), stmt)
|
|
c.Assert(err, IsNil, comment)
|
|
|
|
checkVisitInfo(c, builder.visitInfo, tt.ans, comment)
|
|
}
|
|
}
|
|
|
|
type visitInfoArray []visitInfo
|
|
|
|
func (v visitInfoArray) Len() int {
|
|
return len(v)
|
|
}
|
|
|
|
func (v visitInfoArray) Less(i, j int) bool {
|
|
if v[i].privilege < v[j].privilege {
|
|
return true
|
|
}
|
|
if v[i].db < v[j].db {
|
|
return true
|
|
}
|
|
if v[i].table < v[j].table {
|
|
return true
|
|
}
|
|
if v[i].column < v[j].column {
|
|
return true
|
|
}
|
|
|
|
return false
|
|
}
|
|
|
|
func (v visitInfoArray) Swap(i, j int) {
|
|
v[i], v[j] = v[j], v[i]
|
|
}
|
|
|
|
func unique(v []visitInfo) []visitInfo {
|
|
repeat := 0
|
|
for i := 1; i < len(v); i++ {
|
|
if v[i] == v[i-1] {
|
|
repeat++
|
|
} else {
|
|
v[i-repeat] = v[i]
|
|
}
|
|
}
|
|
return v[:len(v)-repeat]
|
|
}
|
|
|
|
func checkVisitInfo(c *C, v1, v2 []visitInfo, comment CommentInterface) {
|
|
sort.Sort(visitInfoArray(v1))
|
|
sort.Sort(visitInfoArray(v2))
|
|
v1 = unique(v1)
|
|
v2 = unique(v2)
|
|
|
|
c.Assert(len(v1), Equals, len(v2), comment)
|
|
for i := 0; i < len(v1); i++ {
|
|
// loose compare errors for code match
|
|
c.Assert(terror.ErrorEqual(v1[i].err, v2[i].err), IsTrue, Commentf("err1 %v, err2 %v for %s", v1[i].err, v2[i].err, comment))
|
|
// compare remainder
|
|
v1[i].err = v2[i].err
|
|
c.Assert(v1[i], Equals, v2[i], comment)
|
|
}
|
|
}
|
|
|
|
func (s *testPlanSuite) TestUnion(c *C) {
|
|
defer func() {
|
|
testleak.AfterTest(c)()
|
|
}()
|
|
var input []string
|
|
var output []struct {
|
|
Best string
|
|
Err bool
|
|
}
|
|
s.testData.GetTestCases(c, &input, &output)
|
|
ctx := context.TODO()
|
|
for i, tt := range input {
|
|
comment := Commentf("case:%v sql:%s", i, tt)
|
|
stmt, err := s.ParseOneStmt(tt, "", "")
|
|
c.Assert(err, IsNil, comment)
|
|
Preprocess(s.ctx, stmt, s.is)
|
|
builder := NewPlanBuilder(MockContext(), s.is, &hint.BlockHintProcessor{})
|
|
plan, err := builder.Build(ctx, stmt)
|
|
s.testData.OnRecord(func() {
|
|
output[i].Err = err != nil
|
|
})
|
|
if output[i].Err {
|
|
c.Assert(err, NotNil)
|
|
continue
|
|
}
|
|
c.Assert(err, IsNil)
|
|
p := plan.(LogicalPlan)
|
|
p, err = logicalOptimize(ctx, builder.optFlag, p.(LogicalPlan))
|
|
s.testData.OnRecord(func() {
|
|
output[i].Best = ToString(p)
|
|
})
|
|
c.Assert(err, IsNil)
|
|
c.Assert(ToString(p), Equals, output[i].Best, comment)
|
|
}
|
|
}
|
|
|
|
func (s *testPlanSuite) TestTopNPushDown(c *C) {
|
|
defer func() {
|
|
testleak.AfterTest(c)()
|
|
}()
|
|
var input, output []string
|
|
s.testData.GetTestCases(c, &input, &output)
|
|
ctx := context.TODO()
|
|
for i, tt := range input {
|
|
comment := Commentf("case:%v sql:%s", i, tt)
|
|
stmt, err := s.ParseOneStmt(tt, "", "")
|
|
c.Assert(err, IsNil, comment)
|
|
Preprocess(s.ctx, stmt, s.is)
|
|
builder := NewPlanBuilder(MockContext(), s.is, &hint.BlockHintProcessor{})
|
|
p, err := builder.Build(ctx, stmt)
|
|
c.Assert(err, IsNil)
|
|
p, err = logicalOptimize(ctx, builder.optFlag, p.(LogicalPlan))
|
|
c.Assert(err, IsNil)
|
|
s.testData.OnRecord(func() {
|
|
output[i] = ToString(p)
|
|
})
|
|
c.Assert(ToString(p), Equals, output[i], comment)
|
|
}
|
|
}
|
|
|
|
func (s *testPlanSuite) TestNameResolver(c *C) {
|
|
defer testleak.AfterTest(c)()
|
|
tests := []struct {
|
|
sql string
|
|
err string
|
|
}{
|
|
{"select a from t", ""},
|
|
{"select c3 from t", "[planner:1054]Unknown column 'c3' in 'field list'"},
|
|
{"select c1 from t4", "[schema:1146]Table 'test.t4' doesn't exist"},
|
|
{"select * from t", ""},
|
|
{"select t.* from t", ""},
|
|
{"select t2.* from t", "[planner:1051]Unknown table 't2'"},
|
|
{"select b as a, c as a from t group by a", "[planner:1052]Column 'c' in field list is ambiguous"},
|
|
{"select 1 as a, b as a, c as a from t group by a", ""},
|
|
{"select a, b as a from t group by a+1", ""},
|
|
{"select c, a as c from t order by c+1", ""},
|
|
{"select * from t as t1, t as t2 join t as t3 on t2.a = t3.a", ""},
|
|
{"select * from t as t1, t as t2 join t as t3 on t1.c1 = t2.a", "[planner:1054]Unknown column 't1.c1' in 'on clause'"},
|
|
{"select a from t group by a having a = 3", ""},
|
|
{"select a from t group by a having c2 = 3", "[planner:1054]Unknown column 'c2' in 'having clause'"},
|
|
{"select a from t where exists (select b)", ""},
|
|
{"select cnt from (select count(a) as cnt from t group by b) as t2 group by cnt", ""},
|
|
{"select a from t where t11.a < t.a", "[planner:1054]Unknown column 't11.a' in 'where clause'"},
|
|
{"select a from t having t11.c1 < t.a", "[planner:1054]Unknown column 't11.c1' in 'having clause'"},
|
|
{"select a from t where t.a < t.a order by t11.c1", "[planner:1054]Unknown column 't11.c1' in 'order clause'"},
|
|
{"select a from t group by t11.c1", "[planner:1054]Unknown column 't11.c1' in 'group statement'"},
|
|
{"delete a from (select * from t ) as a, t", "[planner:1288]The target table a of the DELETE is not updatable"},
|
|
{"delete b from (select * from t ) as a, t", "[planner:1109]Unknown table 'b' in MULTI DELETE"},
|
|
{"select '' as fakeCol from t group by values(fakeCol)", "[planner:1054]Unknown column '' in 'VALUES() function'"},
|
|
{"update t, (select * from t) as b set b.a = t.a", "[planner:1288]The target table b of the UPDATE is not updatable"},
|
|
{"select row_number() over () from t group by 1", "[planner:1056]Can't group on 'row_number() over ()'"},
|
|
{"select row_number() over () as x from t group by 1", "[planner:1056]Can't group on 'x'"},
|
|
{"select sum(a) as x from t group by 1", "[planner:1056]Can't group on 'x'"},
|
|
}
|
|
|
|
ctx := context.Background()
|
|
for _, t := range tests {
|
|
comment := Commentf("for %s", t.sql)
|
|
stmt, err := s.ParseOneStmt(t.sql, "", "")
|
|
c.Assert(err, IsNil, comment)
|
|
s.ctx.GetSessionVars().HashJoinConcurrency = 1
|
|
|
|
_, _, err = BuildLogicalPlan(ctx, s.ctx, stmt, s.is)
|
|
if t.err == "" {
|
|
c.Check(err, IsNil)
|
|
} else {
|
|
c.Assert(err.Error(), Equals, t.err)
|
|
}
|
|
}
|
|
}
|
|
|
|
func (s *testPlanSuite) TestOuterJoinEliminator(c *C) {
|
|
defer testleak.AfterTest(c)()
|
|
var input, output []string
|
|
s.testData.GetTestCases(c, &input, &output)
|
|
|
|
ctx := context.TODO()
|
|
for i, tt := range input {
|
|
comment := Commentf("case:%v sql:%s", i, tt)
|
|
stmt, err := s.ParseOneStmt(tt, "", "")
|
|
c.Assert(err, IsNil, comment)
|
|
Preprocess(s.ctx, stmt, s.is)
|
|
builder := NewPlanBuilder(MockContext(), s.is, &hint.BlockHintProcessor{})
|
|
p, err := builder.Build(ctx, stmt)
|
|
c.Assert(err, IsNil)
|
|
p, err = logicalOptimize(ctx, builder.optFlag, p.(LogicalPlan))
|
|
c.Assert(err, IsNil)
|
|
planString := ToString(p)
|
|
s.testData.OnRecord(func() {
|
|
output[i] = planString
|
|
})
|
|
c.Assert(planString, Equals, output[i], comment)
|
|
}
|
|
}
|
|
|
|
func (s *testPlanSuite) TestSelectView(c *C) {
|
|
defer func() {
|
|
testleak.AfterTest(c)()
|
|
}()
|
|
tests := []struct {
|
|
sql string
|
|
best string
|
|
}{
|
|
{
|
|
sql: "select * from v",
|
|
best: "DataScan(t)->Projection",
|
|
},
|
|
{
|
|
sql: "select v.b, v.c, v.d from v",
|
|
best: "DataScan(t)->Projection",
|
|
},
|
|
}
|
|
ctx := context.TODO()
|
|
for i, tt := range tests {
|
|
comment := Commentf("case:%v sql:%s", i, tt.sql)
|
|
stmt, err := s.ParseOneStmt(tt.sql, "", "")
|
|
c.Assert(err, IsNil, comment)
|
|
Preprocess(s.ctx, stmt, s.is)
|
|
builder := NewPlanBuilder(MockContext(), s.is, &hint.BlockHintProcessor{})
|
|
p, err := builder.Build(ctx, stmt)
|
|
c.Assert(err, IsNil)
|
|
p, err = logicalOptimize(ctx, builder.optFlag, p.(LogicalPlan))
|
|
c.Assert(err, IsNil)
|
|
c.Assert(ToString(p), Equals, tt.best, comment)
|
|
}
|
|
}
|
|
|
|
func (s *testPlanSuite) TestWindowFunction(c *C) {
|
|
s.optimizeVars = map[string]string{
|
|
variable.TiDBWindowConcurrency: "1",
|
|
}
|
|
defer func() {
|
|
s.optimizeVars = nil
|
|
testleak.AfterTest(c)()
|
|
}()
|
|
var input, output []string
|
|
s.testData.GetTestCases(c, &input, &output)
|
|
s.doTestWindowFunction(c, input, output)
|
|
}
|
|
|
|
func (s *testPlanSuite) TestWindowParallelFunction(c *C) {
|
|
s.optimizeVars = map[string]string{
|
|
variable.TiDBWindowConcurrency: "4",
|
|
}
|
|
defer func() {
|
|
s.optimizeVars = nil
|
|
testleak.AfterTest(c)()
|
|
}()
|
|
var input, output []string
|
|
s.testData.GetTestCases(c, &input, &output)
|
|
s.doTestWindowFunction(c, input, output)
|
|
}
|
|
|
|
func (s *testPlanSuite) doTestWindowFunction(c *C, input, output []string) {
|
|
ctx := context.TODO()
|
|
for i, tt := range input {
|
|
comment := Commentf("case:%v sql:%s", i, tt)
|
|
p, stmt, err := s.optimize(ctx, tt)
|
|
if err != nil {
|
|
s.testData.OnRecord(func() {
|
|
output[i] = err.Error()
|
|
})
|
|
c.Assert(err.Error(), Equals, output[i], comment)
|
|
continue
|
|
}
|
|
s.testData.OnRecord(func() {
|
|
output[i] = ToString(p)
|
|
})
|
|
c.Assert(ToString(p), Equals, output[i], comment)
|
|
|
|
var sb strings.Builder
|
|
// After restore, the result should be the same.
|
|
err = stmt.Restore(format.NewRestoreCtx(format.DefaultRestoreFlags, &sb))
|
|
c.Assert(err, IsNil)
|
|
p, _, err = s.optimize(ctx, sb.String())
|
|
if err != nil {
|
|
c.Assert(err.Error(), Equals, output[i], comment)
|
|
continue
|
|
}
|
|
c.Assert(ToString(p), Equals, output[i], comment)
|
|
}
|
|
}
|
|
|
|
func (s *testPlanSuite) optimize(ctx context.Context, sql string) (PhysicalPlan, ast.Node, error) {
|
|
stmt, err := s.ParseOneStmt(sql, "", "")
|
|
if err != nil {
|
|
return nil, nil, err
|
|
}
|
|
err = Preprocess(s.ctx, stmt, s.is)
|
|
if err != nil {
|
|
return nil, nil, err
|
|
}
|
|
|
|
sctx := MockContext()
|
|
for k, v := range s.optimizeVars {
|
|
if err = sctx.GetSessionVars().SetSystemVar(k, v); err != nil {
|
|
return nil, nil, err
|
|
}
|
|
}
|
|
builder := NewPlanBuilder(sctx, s.is, &hint.BlockHintProcessor{})
|
|
p, err := builder.Build(ctx, stmt)
|
|
if err != nil {
|
|
return nil, nil, err
|
|
}
|
|
p, err = logicalOptimize(ctx, builder.optFlag, p.(LogicalPlan))
|
|
if err != nil {
|
|
return nil, nil, err
|
|
}
|
|
p, _, err = physicalOptimize(p.(LogicalPlan))
|
|
return p.(PhysicalPlan), stmt, err
|
|
}
|
|
|
|
func byItemsToProperty(byItems []*util.ByItems) *property.PhysicalProperty {
|
|
pp := &property.PhysicalProperty{}
|
|
for _, item := range byItems {
|
|
pp.Items = append(pp.Items, property.Item{Col: item.Expr.(*expression.Column), Desc: item.Desc})
|
|
}
|
|
return pp
|
|
}
|
|
|
|
func pathsName(paths []*candidatePath) string {
|
|
var names []string
|
|
for _, path := range paths {
|
|
if path.path.IsTablePath {
|
|
names = append(names, "PRIMARY_KEY")
|
|
} else {
|
|
names = append(names, path.path.Index.Name.O)
|
|
}
|
|
}
|
|
return strings.Join(names, ",")
|
|
}
|
|
|
|
func (s *testPlanSuite) TestSkylinePruning(c *C) {
|
|
defer testleak.AfterTest(c)()
|
|
tests := []struct {
|
|
sql string
|
|
result string
|
|
}{
|
|
{
|
|
sql: "select * from t",
|
|
result: "PRIMARY_KEY",
|
|
},
|
|
{
|
|
sql: "select * from t order by f",
|
|
result: "PRIMARY_KEY,f,f_g",
|
|
},
|
|
{
|
|
sql: "select * from t where a > 1",
|
|
result: "PRIMARY_KEY",
|
|
},
|
|
{
|
|
sql: "select * from t where a > 1 order by f",
|
|
result: "PRIMARY_KEY,f,f_g",
|
|
},
|
|
{
|
|
sql: "select * from t where f > 1",
|
|
result: "PRIMARY_KEY,f,f_g",
|
|
},
|
|
{
|
|
sql: "select f from t where f > 1",
|
|
result: "f,f_g",
|
|
},
|
|
{
|
|
sql: "select f from t where f > 1 order by a",
|
|
result: "PRIMARY_KEY,f,f_g",
|
|
},
|
|
{
|
|
sql: "select * from t where f > 1 and g > 1",
|
|
result: "PRIMARY_KEY,f,g,f_g",
|
|
},
|
|
{
|
|
sql: "select count(1) from t",
|
|
result: "PRIMARY_KEY,c_d_e,f,g,f_g,c_d_e_str,e_d_c_str_prefix",
|
|
},
|
|
}
|
|
ctx := context.TODO()
|
|
for i, tt := range tests {
|
|
comment := Commentf("case:%v sql:%s", i, tt.sql)
|
|
stmt, err := s.ParseOneStmt(tt.sql, "", "")
|
|
c.Assert(err, IsNil, comment)
|
|
Preprocess(s.ctx, stmt, s.is)
|
|
builder := NewPlanBuilder(MockContext(), s.is, &hint.BlockHintProcessor{})
|
|
p, err := builder.Build(ctx, stmt)
|
|
if err != nil {
|
|
c.Assert(err.Error(), Equals, tt.result, comment)
|
|
continue
|
|
}
|
|
c.Assert(err, IsNil, comment)
|
|
p, err = logicalOptimize(ctx, builder.optFlag, p.(LogicalPlan))
|
|
c.Assert(err, IsNil, comment)
|
|
lp := p.(LogicalPlan)
|
|
_, err = lp.recursiveDeriveStats()
|
|
c.Assert(err, IsNil, comment)
|
|
var ds *DataSource
|
|
var byItems []*util.ByItems
|
|
for ds == nil {
|
|
switch v := lp.(type) {
|
|
case *DataSource:
|
|
ds = v
|
|
case *LogicalSort:
|
|
byItems = v.ByItems
|
|
lp = lp.Children()[0]
|
|
case *LogicalProjection:
|
|
newItems := make([]*util.ByItems, 0, len(byItems))
|
|
for _, col := range byItems {
|
|
idx := v.schema.ColumnIndex(col.Expr.(*expression.Column))
|
|
switch expr := v.Exprs[idx].(type) {
|
|
case *expression.Column:
|
|
newItems = append(newItems, &util.ByItems{Expr: expr, Desc: col.Desc})
|
|
}
|
|
}
|
|
byItems = newItems
|
|
lp = lp.Children()[0]
|
|
default:
|
|
lp = lp.Children()[0]
|
|
}
|
|
}
|
|
paths := ds.skylinePruning(byItemsToProperty(byItems))
|
|
c.Assert(pathsName(paths), Equals, tt.result, comment)
|
|
}
|
|
}
|
|
|
|
func (s *testPlanSuite) TestFastPlanContextTables(c *C) {
|
|
defer testleak.AfterTest(c)()
|
|
tests := []struct {
|
|
sql string
|
|
fastPlan bool
|
|
}{
|
|
{
|
|
"select * from t where a=1",
|
|
true,
|
|
},
|
|
{
|
|
|
|
"update t set f=0 where a=43215",
|
|
true,
|
|
},
|
|
{
|
|
"delete from t where a =43215",
|
|
true,
|
|
},
|
|
{
|
|
"select * from t where a>1",
|
|
false,
|
|
},
|
|
}
|
|
s.ctx.GetSessionVars().SnapshotInfoschema = s.is
|
|
for _, tt := range tests {
|
|
stmt, err := s.ParseOneStmt(tt.sql, "", "")
|
|
c.Assert(err, IsNil)
|
|
Preprocess(s.ctx, stmt, s.is)
|
|
s.ctx.GetSessionVars().StmtCtx.Tables = nil
|
|
p := TryFastPlan(s.ctx, stmt)
|
|
if tt.fastPlan {
|
|
c.Assert(p, NotNil)
|
|
c.Assert(len(s.ctx.GetSessionVars().StmtCtx.Tables), Equals, 1)
|
|
c.Assert(s.ctx.GetSessionVars().StmtCtx.Tables[0].Table, Equals, "t")
|
|
c.Assert(s.ctx.GetSessionVars().StmtCtx.Tables[0].DB, Equals, "test")
|
|
} else {
|
|
c.Assert(p, IsNil)
|
|
c.Assert(len(s.ctx.GetSessionVars().StmtCtx.Tables), Equals, 0)
|
|
}
|
|
}
|
|
}
|
|
|
|
func (s *testPlanSuite) TestUpdateEQCond(c *C) {
|
|
defer testleak.AfterTest(c)()
|
|
tests := []struct {
|
|
sql string
|
|
best string
|
|
}{
|
|
{
|
|
sql: "select t1.a from t t1, t t2 where t1.a = t2.a+1",
|
|
best: "Join{DataScan(t1)->DataScan(t2)->Projection}(test.t.a,Column#25)->Projection",
|
|
},
|
|
}
|
|
ctx := context.TODO()
|
|
for i, tt := range tests {
|
|
comment := Commentf("case:%v sql:%s", i, tt.sql)
|
|
stmt, err := s.ParseOneStmt(tt.sql, "", "")
|
|
c.Assert(err, IsNil, comment)
|
|
Preprocess(s.ctx, stmt, s.is)
|
|
builder := NewPlanBuilder(MockContext(), s.is, &hint.BlockHintProcessor{})
|
|
p, err := builder.Build(ctx, stmt)
|
|
c.Assert(err, IsNil)
|
|
p, err = logicalOptimize(ctx, builder.optFlag, p.(LogicalPlan))
|
|
c.Assert(err, IsNil)
|
|
c.Assert(ToString(p), Equals, tt.best, comment)
|
|
}
|
|
}
|
|
|
|
func (s *testPlanSuite) TestConflictedJoinTypeHints(c *C) {
|
|
defer testleak.AfterTest(c)()
|
|
sql := "select /*+ INL_JOIN(t1) HASH_JOIN(t1) */ * from t t1, t t2 where t1.e = t2.e"
|
|
ctx := context.TODO()
|
|
stmt, err := s.ParseOneStmt(sql, "", "")
|
|
c.Assert(err, IsNil)
|
|
Preprocess(s.ctx, stmt, s.is)
|
|
builder := NewPlanBuilder(MockContext(), s.is, &hint.BlockHintProcessor{})
|
|
p, err := builder.Build(ctx, stmt)
|
|
c.Assert(err, IsNil)
|
|
p, err = logicalOptimize(ctx, builder.optFlag, p.(LogicalPlan))
|
|
c.Assert(err, IsNil)
|
|
proj, ok := p.(*LogicalProjection)
|
|
c.Assert(ok, IsTrue)
|
|
join, ok := proj.Children()[0].(*LogicalJoin)
|
|
c.Assert(ok, IsTrue)
|
|
c.Assert(join.hintInfo, IsNil)
|
|
c.Assert(join.preferJoinType, Equals, uint(0))
|
|
}
|
|
|
|
func (s *testPlanSuite) TestSimplyOuterJoinWithOnlyOuterExpr(c *C) {
|
|
defer testleak.AfterTest(c)()
|
|
sql := "select * from t t1 right join t t0 ON TRUE where CONCAT_WS(t0.e=t0.e, 0, NULL) IS NULL"
|
|
ctx := context.TODO()
|
|
stmt, err := s.ParseOneStmt(sql, "", "")
|
|
c.Assert(err, IsNil)
|
|
Preprocess(s.ctx, stmt, s.is)
|
|
builder := NewPlanBuilder(MockContext(), s.is, &hint.BlockHintProcessor{})
|
|
p, err := builder.Build(ctx, stmt)
|
|
c.Assert(err, IsNil)
|
|
p, err = logicalOptimize(ctx, builder.optFlag, p.(LogicalPlan))
|
|
c.Assert(err, IsNil)
|
|
proj, ok := p.(*LogicalProjection)
|
|
c.Assert(ok, IsTrue)
|
|
join, ok := proj.Children()[0].(*LogicalJoin)
|
|
c.Assert(ok, IsTrue)
|
|
// previous wrong JoinType is InnerJoin
|
|
c.Assert(join.JoinType, Equals, RightOuterJoin)
|
|
}
|
|
|