// Copyright 2019 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 aprettyPrintlicable 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_test import ( "bytes" "fmt" "strings" "time" . "github.com/pingcap/check" "github.com/pingcap/parser/model" "github.com/pingcap/tidb/domain" "github.com/pingcap/tidb/kv" "github.com/pingcap/tidb/planner/core" "github.com/pingcap/tidb/util/israce" "github.com/pingcap/tidb/util/plancodec" "github.com/pingcap/tidb/util/testkit" "github.com/pingcap/tidb/util/testleak" "github.com/pingcap/tidb/util/testutil" ) var _ = Suite(&testPlanNormalize{}) type testPlanNormalize struct { store kv.Storage dom *domain.Domain testData testutil.TestData } func (s *testPlanNormalize) SetUpSuite(c *C) { testleak.BeforeTest() store, dom, err := newStoreWithBootstrap() c.Assert(err, IsNil) s.store = store s.dom = dom s.testData, err = testutil.LoadTestSuiteData("testdata", "plan_normalized_suite") c.Assert(err, IsNil) } func (s *testPlanNormalize) TearDownSuite(c *C) { c.Assert(s.testData.GenerateOutputIfNeeded(), IsNil) s.dom.Close() s.store.Close() testleak.AfterTest(c)() } func (s *testPlanNormalize) TestNormalizedPlan(c *C) { tk := testkit.NewTestKit(c, s.store) tk.MustExec("use test") tk.MustExec("drop table if exists t1,t2,t3,t4") tk.MustExec("create table t1 (a int key,b int,c int, index (b));") tk.MustExec("create table t2 (a int key,b int,c int, index (b));") tk.MustExec("create table t3 (a int key,b int) partition by hash(a) partitions 2;") tk.MustExec("create table t4 (a int, b int, index(a)) partition by range(a) (partition p0 values less than (10),partition p1 values less than MAXVALUE);") var input []string var output []struct { SQL string Plan []string } s.testData.GetTestCases(c, &input, &output) for i, tt := range input { tk.Se.GetSessionVars().PlanID = 0 tk.MustExec(tt) info := tk.Se.ShowProcess() c.Assert(info, NotNil) p, ok := info.Plan.(core.Plan) c.Assert(ok, IsTrue) normalized, _ := core.NormalizePlan(p) normalizedPlan, err := plancodec.DecodeNormalizedPlan(normalized) normalizedPlanRows := getPlanRows(normalizedPlan) c.Assert(err, IsNil) s.testData.OnRecord(func() { output[i].SQL = tt output[i].Plan = normalizedPlanRows }) compareStringSlice(c, normalizedPlanRows, output[i].Plan) } } func (s *testPlanNormalize) TestNormalizedPlanForDiffStore(c *C) { tk := testkit.NewTestKit(c, s.store) tk.MustExec("use test") tk.MustExec("drop table if exists t1") tk.MustExec("create table t1 (a int, b int, c int, primary key(a))") tk.MustExec("insert into t1 values(1,1,1), (2,2,2), (3,3,3)") tbl, err := s.dom.InfoSchema().TableByName(model.CIStr{O: "test", L: "test"}, model.CIStr{O: "t1", L: "t1"}) c.Assert(err, IsNil) // Set the hacked TiFlash replica for explain tests. tbl.Meta().TiFlashReplica = &model.TiFlashReplicaInfo{Count: 1, Available: true} var input []string var output []struct { Digest string Plan []string } s.testData.GetTestCases(c, &input, &output) lastDigest := "" for i, tt := range input { tk.Se.GetSessionVars().PlanID = 0 tk.MustExec(tt) info := tk.Se.ShowProcess() c.Assert(info, NotNil) ep, ok := info.Plan.(*core.Explain) c.Assert(ok, IsTrue) normalized, digest := core.NormalizePlan(ep.TargetPlan) normalizedPlan, err := plancodec.DecodeNormalizedPlan(normalized) normalizedPlanRows := getPlanRows(normalizedPlan) c.Assert(err, IsNil) s.testData.OnRecord(func() { output[i].Digest = digest output[i].Plan = normalizedPlanRows }) compareStringSlice(c, normalizedPlanRows, output[i].Plan) c.Assert(digest != lastDigest, IsTrue) lastDigest = digest } } func (s *testPlanNormalize) TestEncodeDecodePlan(c *C) { if israce.RaceEnabled { c.Skip("skip race test") } tk := testkit.NewTestKit(c, s.store) tk.MustExec("use test") tk.MustExec("drop table if exists t1,t2") tk.MustExec("create table t1 (a int key,b int,c int, index (b));") tk.MustExec("set tidb_enable_collect_execution_info=1;") tk.Se.GetSessionVars().PlanID = 0 getPlanTree := func() string { info := tk.Se.ShowProcess() c.Assert(info, NotNil) p, ok := info.Plan.(core.Plan) c.Assert(ok, IsTrue) encodeStr := core.EncodePlan(p) planTree, err := plancodec.DecodePlan(encodeStr) c.Assert(err, IsNil) return planTree } tk.MustExec("select max(a) from t1 where a>0;") planTree := getPlanTree() c.Assert(strings.Contains(planTree, "time"), IsTrue) c.Assert(strings.Contains(planTree, "loops"), IsTrue) tk.MustExec("insert into t1 values (1,1,1);") planTree = getPlanTree() c.Assert(strings.Contains(planTree, "Insert"), IsTrue) c.Assert(strings.Contains(planTree, "time"), IsTrue) c.Assert(strings.Contains(planTree, "loops"), IsTrue) } func (s *testPlanNormalize) TestNormalizedDigest(c *C) { tk := testkit.NewTestKit(c, s.store) tk.MustExec("use test") tk.MustExec("drop table if exists t1,t2,t3,t4, bmsql_order_line, bmsql_district,bmsql_stock") tk.MustExec("create table t1 (a int key,b int,c int, index (b));") tk.MustExec("create table t2 (a int key,b int,c int, index (b));") tk.MustExec("create table t3 (a int, b int, index(a)) partition by range(a) (partition p0 values less than (10),partition p1 values less than MAXVALUE);") tk.MustExec("create table t4 (a int key,b int) partition by hash(a) partitions 2;") tk.MustExec(`CREATE TABLE bmsql_order_line ( ol_w_id int(11) NOT NULL, ol_d_id int(11) NOT NULL, ol_o_id int(11) NOT NULL, ol_number int(11) NOT NULL, ol_i_id int(11) NOT NULL, ol_delivery_d timestamp NULL DEFAULT NULL, ol_amount decimal(6,2) DEFAULT NULL, ol_supply_w_id int(11) DEFAULT NULL, ol_quantity int(11) DEFAULT NULL, ol_dist_info char(24) DEFAULT NULL, PRIMARY KEY ( ol_w_id , ol_d_id , ol_o_id , ol_number ) );`) tk.MustExec(`CREATE TABLE bmsql_district ( d_w_id int(11) NOT NULL, d_id int(11) NOT NULL, d_ytd decimal(12,2) DEFAULT NULL, d_tax decimal(4,4) DEFAULT NULL, d_next_o_id int(11) DEFAULT NULL, d_name varchar(10) DEFAULT NULL, d_street_1 varchar(20) DEFAULT NULL, d_street_2 varchar(20) DEFAULT NULL, d_city varchar(20) DEFAULT NULL, d_state char(2) DEFAULT NULL, d_zip char(9) DEFAULT NULL, PRIMARY KEY ( d_w_id , d_id ) );`) tk.MustExec(`CREATE TABLE bmsql_stock ( s_w_id int(11) NOT NULL, s_i_id int(11) NOT NULL, s_quantity int(11) DEFAULT NULL, s_ytd int(11) DEFAULT NULL, s_order_cnt int(11) DEFAULT NULL, s_remote_cnt int(11) DEFAULT NULL, s_data varchar(50) DEFAULT NULL, s_dist_01 char(24) DEFAULT NULL, s_dist_02 char(24) DEFAULT NULL, s_dist_03 char(24) DEFAULT NULL, s_dist_04 char(24) DEFAULT NULL, s_dist_05 char(24) DEFAULT NULL, s_dist_06 char(24) DEFAULT NULL, s_dist_07 char(24) DEFAULT NULL, s_dist_08 char(24) DEFAULT NULL, s_dist_09 char(24) DEFAULT NULL, s_dist_10 char(24) DEFAULT NULL, PRIMARY KEY ( s_w_id , s_i_id ) );`) normalizedDigestCases := []struct { sql1 string sql2 string isSame bool }{ { sql1: "select * from t1;", sql2: "select * from t2;", isSame: false, }, { // test for tableReader and tableScan. sql1: "select * from t1 where a<1", sql2: "select * from t1 where a<2", isSame: true, }, { sql1: "select * from t1 where a<1", sql2: "select * from t1 where a=2", isSame: false, }, { // test for point get. sql1: "select * from t1 where a=3", sql2: "select * from t1 where a=2", isSame: true, }, { // test for indexLookUp. sql1: "select * from t1 use index(b) where b=3", sql2: "select * from t1 use index(b) where b=1", isSame: true, }, { // test for indexReader. sql1: "select a+1,b+2 from t1 use index(b) where b=3", sql2: "select a+2,b+3 from t1 use index(b) where b=2", isSame: true, }, { // test for merge join. sql1: "SELECT /*+ TIDB_SMJ(t1, t2) */ * from t1, t2 where t1.a = t2.a and t1.c>1;", sql2: "SELECT /*+ TIDB_SMJ(t1, t2) */ * from t1, t2 where t1.a = t2.a and t1.c>2;", isSame: true, }, { // test for indexLookUpJoin. sql1: "SELECT /*+ TIDB_INLJ(t1, t2) */ * from t1, t2 where t1.a = t2.a and t1.c>1;", sql2: "SELECT /*+ TIDB_INLJ(t1, t2) */ * from t1, t2 where t1.a = t2.a and t1.c>3;", isSame: true, }, { // test for hashJoin. sql1: "SELECT /*+ TIDB_HJ(t1, t2) */ * from t1, t2 where t1.a = t2.a and t1.c>1;", sql2: "SELECT /*+ TIDB_HJ(t1, t2) */ * from t1, t2 where t1.a = t2.a and t1.c>3;", isSame: true, }, { // test for diff join. sql1: "SELECT /*+ TIDB_HJ(t1, t2) */ * from t1, t2 where t1.a = t2.a and t1.c>1;", sql2: "SELECT /*+ TIDB_INLJ(t1, t2) */ * from t1, t2 where t1.a = t2.a and t1.c>3;", isSame: false, }, { // test for diff join. sql1: "SELECT /*+ TIDB_INLJ(t1, t2) */ * from t1, t2 where t1.a = t2.a and t1.c>1;", sql2: "SELECT /*+ TIDB_SMJ(t1, t2) */ * from t1, t2 where t1.a = t2.a and t1.c>3;", isSame: false, }, { // test for apply. sql1: "select * from t1 where t1.b > 0 and t1.a in (select sum(t2.b) from t2 where t2.a=t1.a and t2.b is not null and t2.c >1)", sql2: "select * from t1 where t1.b > 1 and t1.a in (select sum(t2.b) from t2 where t2.a=t1.a and t2.b is not null and t2.c >0)", isSame: true, }, { // test for apply. sql1: "select * from t1 where t1.b > 0 and t1.a in (select sum(t2.b) from t2 where t2.a=t1.a and t2.b is not null and t2.c >1)", sql2: "select * from t1 where t1.b > 1 and t1.a in (select sum(t2.b) from t2 where t2.a=t1.a and t2.b is not null)", isSame: false, }, { // test for topN. sql1: "SELECT * from t1 where a!=1 order by c limit 1", sql2: "SELECT * from t1 where a!=2 order by c limit 2", isSame: true, }, { // test for union sql1: "select count(1) as num,a from t1 where a=1 group by a union select count(1) as num,a from t1 where a=3 group by a;", sql2: "select count(1) as num,a from t1 where a=2 group by a union select count(1) as num,a from t1 where a=4 group by a;", isSame: true, }, { // test for tablescan partition sql1: "select * from t3 where a=5", sql2: "select * from t3 where a=15", isSame: true, }, { // test for point get partition sql1: "select * from t4 where a=4", sql2: "select * from t4 where a=30", isSame: true, }, { sql1: `SELECT COUNT(*) AS low_stock FROM ( SELECT * FROM bmsql_stock WHERE s_w_id = 1 AND s_quantity < 2 AND s_i_id IN ( SELECT /*+ TIDB_INLJ(bmsql_order_line) */ ol_i_id FROM bmsql_district JOIN bmsql_order_line ON ol_w_id = d_w_id AND ol_d_id = d_id AND ol_o_id >= d_next_o_id - 20 AND ol_o_id < d_next_o_id WHERE d_w_id = 1 AND d_id = 2 ) ) AS L;`, sql2: `SELECT COUNT(*) AS low_stock FROM ( SELECT * FROM bmsql_stock WHERE s_w_id = 5 AND s_quantity < 6 AND s_i_id IN ( SELECT /*+ TIDB_INLJ(bmsql_order_line) */ ol_i_id FROM bmsql_district JOIN bmsql_order_line ON ol_w_id = d_w_id AND ol_d_id = d_id AND ol_o_id >= d_next_o_id - 70 AND ol_o_id < d_next_o_id WHERE d_w_id = 5 AND d_id = 6 ) ) AS L;`, isSame: true, }, } for _, testCase := range normalizedDigestCases { testNormalizeDigest(tk, c, testCase.sql1, testCase.sql2, testCase.isSame) } } func testNormalizeDigest(tk *testkit.TestKit, c *C, sql1, sql2 string, isSame bool) { tk.Se.GetSessionVars().PlanID = 0 tk.MustQuery(sql1) info := tk.Se.ShowProcess() c.Assert(info, NotNil) physicalPlan, ok := info.Plan.(core.PhysicalPlan) c.Assert(ok, IsTrue) normalized1, digest1 := core.NormalizePlan(physicalPlan) tk.Se.GetSessionVars().PlanID = 0 tk.MustQuery(sql2) info = tk.Se.ShowProcess() c.Assert(info, NotNil) physicalPlan, ok = info.Plan.(core.PhysicalPlan) c.Assert(ok, IsTrue) normalized2, digest2 := core.NormalizePlan(physicalPlan) comment := Commentf("sql1: %v, sql2: %v\n%v !=\n%v\n", sql1, sql2, normalized1, normalized2) if isSame { c.Assert(normalized1, Equals, normalized2, comment) c.Assert(digest1, Equals, digest2, comment) } else { c.Assert(normalized1 != normalized2, IsTrue, comment) c.Assert(digest1 != digest2, IsTrue, comment) } } func getPlanRows(planStr string) []string { planStr = strings.Replace(planStr, "\t", " ", -1) return strings.Split(planStr, "\n") } func compareStringSlice(c *C, ss1, ss2 []string) { c.Assert(len(ss1), Equals, len(ss2)) for i, s := range ss1 { c.Assert(s, Equals, ss2[i]) } } func (s *testPlanNormalize) TestDecodePlanPerformance(c *C) { tk := testkit.NewTestKit(c, s.store) tk.MustExec("use test") tk.MustExec("drop table if exists t") tk.MustExec("create table t (a varchar(10) key,b int);") tk.MustExec("set @@tidb_slow_log_threshold=200000") // generate SQL buf := bytes.NewBuffer(make([]byte, 0, 1024*1024*4)) for i := 0; i < 50000; i++ { if i > 0 { buf.WriteString(" union ") } buf.WriteString(fmt.Sprintf("select count(1) as num,a from t where a='%v' group by a", i)) } query := buf.String() tk.Se.GetSessionVars().PlanID = 0 tk.MustExec(query) info := tk.Se.ShowProcess() c.Assert(info, NotNil) p, ok := info.Plan.(core.PhysicalPlan) c.Assert(ok, IsTrue) // TODO: optimize the encode plan performance when encode plan with runtimeStats tk.Se.GetSessionVars().StmtCtx.RuntimeStatsColl = nil encodedPlanStr := core.EncodePlan(p) start := time.Now() _, err := plancodec.DecodePlan(encodedPlanStr) c.Assert(err, IsNil) c.Assert(time.Since(start).Seconds(), Less, 3.0) } func (s *testPlanNormalize) TestEncodePlanPerformance(c *C) { tk := testkit.NewTestKit(c, s.store) tk.MustExec("use test") tk.MustExec("drop table if exists th") tk.MustExec("set @@session.tidb_enable_table_partition = 1") tk.MustExec("create table th (i int, a int,b int, c int, index (a)) partition by hash (a) partitions 1024;") tk.MustExec("set @@tidb_slow_log_threshold=200000") query := "select count(*) from th t1 join th t2 join th t3 join th t4 join th t5 join th t6 where t1.i=t2.a and t1.i=t3.i and t3.i=t4.i and t4.i=t5.i and t5.i=t6.i" tk.Se.GetSessionVars().PlanID = 0 tk.MustExec(query) info := tk.Se.ShowProcess() c.Assert(info, NotNil) p, ok := info.Plan.(core.PhysicalPlan) c.Assert(ok, IsTrue) tk.Se.GetSessionVars().StmtCtx.RuntimeStatsColl = nil start := time.Now() encodedPlanStr := core.EncodePlan(p) c.Assert(time.Since(start).Seconds(), Less, 10.0) _, err := plancodec.DecodePlan(encodedPlanStr) c.Assert(err, IsNil) }