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.
417 lines
14 KiB
417 lines
14 KiB
// 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 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 cascades_test
|
|
|
|
import (
|
|
"fmt"
|
|
|
|
. "github.com/pingcap/check"
|
|
"github.com/pingcap/tidb/config"
|
|
"github.com/pingcap/tidb/kv"
|
|
"github.com/pingcap/tidb/session"
|
|
"github.com/pingcap/tidb/sessionctx/variable"
|
|
"github.com/pingcap/tidb/store/mockstore"
|
|
"github.com/pingcap/tidb/util/testkit"
|
|
"github.com/pingcap/tidb/util/testutil"
|
|
)
|
|
|
|
var _ = Suite(&testIntegrationSuite{})
|
|
|
|
type testIntegrationSuite struct {
|
|
store kv.Storage
|
|
testData testutil.TestData
|
|
}
|
|
|
|
func newStoreWithBootstrap() (kv.Storage, error) {
|
|
// TODO: remove this and find out the root cause of race.
|
|
config.GetGlobalConfig().EnableCollectExecutionInfo = false
|
|
store, err := mockstore.NewMockTikvStore()
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
_, err = session.BootstrapSession(store)
|
|
return store, err
|
|
}
|
|
|
|
func (s *testIntegrationSuite) SetUpSuite(c *C) {
|
|
var err error
|
|
s.store, err = newStoreWithBootstrap()
|
|
c.Assert(err, IsNil)
|
|
s.testData, err = testutil.LoadTestSuiteData("testdata", "integration_suite")
|
|
c.Assert(err, IsNil)
|
|
}
|
|
|
|
func (s *testIntegrationSuite) TearDownSuite(c *C) {
|
|
c.Assert(s.testData.GenerateOutputIfNeeded(), IsNil)
|
|
s.store.Close()
|
|
}
|
|
|
|
func (s *testIntegrationSuite) TestSimpleProjDual(c *C) {
|
|
tk := testkit.NewTestKitWithInit(c, s.store)
|
|
tk.MustExec("set session tidb_enable_cascades_planner = 1")
|
|
tk.MustQuery("explain select 1").Check(testkit.Rows(
|
|
"Projection_3 1.00 root 1->Column#1",
|
|
"└─TableDual_4 1.00 root rows:1",
|
|
))
|
|
tk.MustQuery("select 1").Check(testkit.Rows(
|
|
"1",
|
|
))
|
|
}
|
|
|
|
func (s *testIntegrationSuite) TestPKIsHandleRangeScan(c *C) {
|
|
tk := testkit.NewTestKitWithInit(c, s.store)
|
|
tk.MustExec("drop table if exists t")
|
|
tk.MustExec("create table t(a int primary key, b int)")
|
|
tk.MustExec("insert into t values(1,2),(3,4),(5,6)")
|
|
tk.MustExec("set session tidb_enable_cascades_planner = 1")
|
|
|
|
var input []string
|
|
var output []struct {
|
|
SQL string
|
|
Plan []string
|
|
Result []string
|
|
}
|
|
s.testData.GetTestCases(c, &input, &output)
|
|
for i, sql := range input {
|
|
s.testData.OnRecord(func() {
|
|
output[i].SQL = sql
|
|
output[i].Plan = s.testData.ConvertRowsToStrings(tk.MustQuery("explain " + sql).Rows())
|
|
output[i].Result = s.testData.ConvertRowsToStrings(tk.MustQuery(sql).Rows())
|
|
})
|
|
tk.MustQuery("explain " + sql).Check(testkit.Rows(output[i].Plan...))
|
|
tk.MustQuery(sql).Check(testkit.Rows(output[i].Result...))
|
|
}
|
|
}
|
|
|
|
func (s *testIntegrationSuite) TestIndexScan(c *C) {
|
|
tk := testkit.NewTestKitWithInit(c, s.store)
|
|
tk.MustExec("drop table if exists t")
|
|
tk.MustExec("create table t(a int primary key, b int, c int, d int, index idx_b(b), index idx_c_b(c, b))")
|
|
tk.MustExec("insert into t values(1,2,3,100),(4,5,6,200),(7,8,9,300)")
|
|
tk.MustExec("set session tidb_enable_cascades_planner = 1")
|
|
var input []string
|
|
var output []struct {
|
|
SQL string
|
|
Plan []string
|
|
Result []string
|
|
}
|
|
s.testData.GetTestCases(c, &input, &output)
|
|
for i, sql := range input {
|
|
s.testData.OnRecord(func() {
|
|
output[i].SQL = sql
|
|
output[i].Plan = s.testData.ConvertRowsToStrings(tk.MustQuery("explain " + sql).Rows())
|
|
output[i].Result = s.testData.ConvertRowsToStrings(tk.MustQuery(sql).Rows())
|
|
})
|
|
tk.MustQuery("explain " + sql).Check(testkit.Rows(output[i].Plan...))
|
|
tk.MustQuery(sql).Check(testkit.Rows(output[i].Result...))
|
|
}
|
|
}
|
|
|
|
func (s *testIntegrationSuite) TestBasicShow(c *C) {
|
|
tk := testkit.NewTestKitWithInit(c, s.store)
|
|
tk.MustExec("drop table if exists t")
|
|
tk.MustExec("create table t(a int primary key, b int)")
|
|
tk.MustExec("set session tidb_enable_cascades_planner = 1")
|
|
tk.MustQuery("desc t").Check(testkit.Rows(
|
|
"a int(11) NO PRI <nil> ",
|
|
"b int(11) YES <nil> ",
|
|
))
|
|
}
|
|
|
|
func (s *testIntegrationSuite) TestSort(c *C) {
|
|
tk := testkit.NewTestKitWithInit(c, s.store)
|
|
tk.MustExec("drop table if exists t")
|
|
tk.MustExec("create table t(a int primary key, b int)")
|
|
tk.MustExec("insert into t values (1, 11), (4, 44), (2, 22), (3, 33)")
|
|
tk.MustExec("set session tidb_enable_cascades_planner = 1")
|
|
var input []string
|
|
var output []struct {
|
|
SQL string
|
|
Plan []string
|
|
Result []string
|
|
}
|
|
s.testData.GetTestCases(c, &input, &output)
|
|
for i, sql := range input {
|
|
s.testData.OnRecord(func() {
|
|
output[i].SQL = sql
|
|
output[i].Plan = s.testData.ConvertRowsToStrings(tk.MustQuery("explain " + sql).Rows())
|
|
output[i].Result = s.testData.ConvertRowsToStrings(tk.MustQuery(sql).Rows())
|
|
})
|
|
tk.MustQuery("explain " + sql).Check(testkit.Rows(output[i].Plan...))
|
|
tk.MustQuery(sql).Check(testkit.Rows(output[i].Result...))
|
|
}
|
|
}
|
|
|
|
func (s *testIntegrationSuite) TestAggregation(c *C) {
|
|
tk := testkit.NewTestKitWithInit(c, s.store)
|
|
tk.MustExec("drop table if exists t")
|
|
tk.MustExec("create table t(a int primary key, b int)")
|
|
tk.MustExec("insert into t values (1, 11), (4, 44), (2, 22), (3, 33)")
|
|
tk.MustExec("set session tidb_enable_cascades_planner = 1")
|
|
var input []string
|
|
var output []struct {
|
|
SQL string
|
|
Plan []string
|
|
Result []string
|
|
}
|
|
s.testData.GetTestCases(c, &input, &output)
|
|
for i, sql := range input {
|
|
s.testData.OnRecord(func() {
|
|
output[i].SQL = sql
|
|
output[i].Plan = s.testData.ConvertRowsToStrings(tk.MustQuery("explain " + sql).Rows())
|
|
output[i].Result = s.testData.ConvertRowsToStrings(tk.MustQuery(sql).Rows())
|
|
})
|
|
tk.MustQuery("explain " + sql).Check(testkit.Rows(output[i].Plan...))
|
|
tk.MustQuery(sql).Check(testkit.Rows(output[i].Result...))
|
|
}
|
|
}
|
|
|
|
func (s *testIntegrationSuite) TestPushdownDistinctEnable(c *C) {
|
|
var (
|
|
input []string
|
|
output []struct {
|
|
SQL string
|
|
Plan []string
|
|
Result []string
|
|
}
|
|
)
|
|
s.testData.GetTestCases(c, &input, &output)
|
|
vars := []string{
|
|
fmt.Sprintf("set @@session.%s = 1", variable.TiDBOptDistinctAggPushDown),
|
|
}
|
|
s.doTestPushdownDistinct(c, vars, input, output)
|
|
}
|
|
|
|
func (s *testIntegrationSuite) TestPushdownDistinctDisable(c *C) {
|
|
var (
|
|
input []string
|
|
output []struct {
|
|
SQL string
|
|
Plan []string
|
|
Result []string
|
|
}
|
|
)
|
|
s.testData.GetTestCases(c, &input, &output)
|
|
vars := []string{
|
|
fmt.Sprintf("set @@session.%s = 0", variable.TiDBOptDistinctAggPushDown),
|
|
}
|
|
s.doTestPushdownDistinct(c, vars, input, output)
|
|
}
|
|
|
|
func (s *testIntegrationSuite) doTestPushdownDistinct(c *C, vars, input []string, output []struct {
|
|
SQL string
|
|
Plan []string
|
|
Result []string
|
|
}) {
|
|
tk := testkit.NewTestKitWithInit(c, s.store)
|
|
tk.MustExec("drop table if exists t")
|
|
tk.MustExec("create table t(a int, b int, c int, index(c))")
|
|
tk.MustExec("insert into t values (1, 1, 1), (1, 1, 3), (1, 2, 3), (2, 1, 3), (1, 2, NULL);")
|
|
tk.MustExec("set session sql_mode=''")
|
|
tk.MustExec(fmt.Sprintf("set session %s=1", variable.TiDBHashAggPartialConcurrency))
|
|
tk.MustExec(fmt.Sprintf("set session %s=1", variable.TiDBHashAggFinalConcurrency))
|
|
tk.MustExec("set session tidb_enable_cascades_planner = 1")
|
|
|
|
for _, v := range vars {
|
|
tk.MustExec(v)
|
|
}
|
|
|
|
for i, ts := range input {
|
|
s.testData.OnRecord(func() {
|
|
output[i].SQL = ts
|
|
output[i].Plan = s.testData.ConvertRowsToStrings(tk.MustQuery("explain " + ts).Rows())
|
|
output[i].Result = s.testData.ConvertRowsToStrings(tk.MustQuery(ts).Sort().Rows())
|
|
})
|
|
tk.MustQuery("explain " + ts).Check(testkit.Rows(output[i].Plan...))
|
|
tk.MustQuery(ts).Sort().Check(testkit.Rows(output[i].Result...))
|
|
}
|
|
}
|
|
|
|
func (s *testIntegrationSuite) TestSimplePlans(c *C) {
|
|
tk := testkit.NewTestKitWithInit(c, s.store)
|
|
tk.MustExec("drop table if exists t")
|
|
tk.MustExec("create table t(a int primary key, b int)")
|
|
tk.MustExec("insert into t values (1, 11), (4, 44), (2, 22), (3, 33)")
|
|
tk.MustExec("set session tidb_enable_cascades_planner = 1")
|
|
var input []string
|
|
var output []struct {
|
|
SQL string
|
|
Plan []string
|
|
Result []string
|
|
}
|
|
s.testData.GetTestCases(c, &input, &output)
|
|
for i, sql := range input {
|
|
s.testData.OnRecord(func() {
|
|
output[i].SQL = sql
|
|
output[i].Plan = s.testData.ConvertRowsToStrings(tk.MustQuery("explain " + sql).Rows())
|
|
output[i].Result = s.testData.ConvertRowsToStrings(tk.MustQuery(sql).Rows())
|
|
})
|
|
tk.MustQuery("explain " + sql).Check(testkit.Rows(output[i].Plan...))
|
|
tk.MustQuery(sql).Check(testkit.Rows(output[i].Result...))
|
|
}
|
|
}
|
|
|
|
func (s *testIntegrationSuite) TestJoin(c *C) {
|
|
tk := testkit.NewTestKitWithInit(c, s.store)
|
|
tk.MustExec("drop table if exists t1")
|
|
tk.MustExec("drop table if exists t2")
|
|
tk.MustExec("create table t1(a int primary key, b int)")
|
|
tk.MustExec("create table t2(a int primary key, b int)")
|
|
tk.MustExec("insert into t1 values (1, 11), (4, 44), (2, 22), (3, 33)")
|
|
tk.MustExec("insert into t2 values (1, 111), (2, 222), (3, 333), (5, 555)")
|
|
tk.MustExec("set session tidb_enable_cascades_planner = 1")
|
|
var input []string
|
|
var output []struct {
|
|
SQL string
|
|
Plan []string
|
|
Result []string
|
|
}
|
|
s.testData.GetTestCases(c, &input, &output)
|
|
for i, sql := range input {
|
|
s.testData.OnRecord(func() {
|
|
output[i].SQL = sql
|
|
output[i].Plan = s.testData.ConvertRowsToStrings(tk.MustQuery("explain " + sql).Rows())
|
|
output[i].Result = s.testData.ConvertRowsToStrings(tk.MustQuery(sql).Rows())
|
|
})
|
|
tk.MustQuery("explain " + sql).Check(testkit.Rows(output[i].Plan...))
|
|
tk.MustQuery(sql).Check(testkit.Rows(output[i].Result...))
|
|
}
|
|
}
|
|
|
|
func (s *testIntegrationSuite) TestApply(c *C) {
|
|
tk := testkit.NewTestKitWithInit(c, s.store)
|
|
tk.MustExec("drop table if exists t1, t2")
|
|
tk.MustExec("create table t1(a int primary key, b int)")
|
|
tk.MustExec("create table t2(a int primary key, b int)")
|
|
tk.MustExec("insert into t1 values (1, 11), (4, 44), (2, 22), (3, 33)")
|
|
tk.MustExec("insert into t2 values (1, 11), (2, 22), (3, 33)")
|
|
tk.MustExec("set session tidb_enable_cascades_planner = 1")
|
|
var input []string
|
|
var output []struct {
|
|
SQL string
|
|
Plan []string
|
|
Result []string
|
|
}
|
|
s.testData.GetTestCases(c, &input, &output)
|
|
for i, sql := range input {
|
|
s.testData.OnRecord(func() {
|
|
output[i].SQL = sql
|
|
output[i].Plan = s.testData.ConvertRowsToStrings(tk.MustQuery("explain " + sql).Rows())
|
|
output[i].Result = s.testData.ConvertRowsToStrings(tk.MustQuery(sql).Rows())
|
|
})
|
|
tk.MustQuery("explain " + sql).Check(testkit.Rows(output[i].Plan...))
|
|
tk.MustQuery(sql).Check(testkit.Rows(output[i].Result...))
|
|
}
|
|
}
|
|
|
|
func (s *testIntegrationSuite) TestMemTableScan(c *C) {
|
|
tk := testkit.NewTestKitWithInit(c, s.store)
|
|
tk.MustExec("set session tidb_enable_cascades_planner = 1")
|
|
var input []string
|
|
var output []struct {
|
|
SQL string
|
|
Plan []string
|
|
Result []string
|
|
}
|
|
s.testData.GetTestCases(c, &input, &output)
|
|
for i, sql := range input {
|
|
s.testData.OnRecord(func() {
|
|
output[i].SQL = sql
|
|
output[i].Plan = s.testData.ConvertRowsToStrings(tk.MustQuery("explain " + sql).Rows())
|
|
output[i].Result = s.testData.ConvertRowsToStrings(tk.MustQuery(sql).Rows())
|
|
})
|
|
tk.MustQuery("explain " + sql).Check(testkit.Rows(output[i].Plan...))
|
|
tk.MustQuery(sql).Check(testkit.Rows(output[i].Result...))
|
|
}
|
|
}
|
|
|
|
func (s *testIntegrationSuite) TestTopN(c *C) {
|
|
tk := testkit.NewTestKitWithInit(c, s.store)
|
|
tk.MustExec("drop table if exists t;")
|
|
tk.MustExec("create table t(a int primary key, b int);")
|
|
tk.MustExec("insert into t values (1, 11), (4, 44), (2, 22), (3, 33);")
|
|
tk.MustExec("set session tidb_enable_cascades_planner = 1;")
|
|
var input []string
|
|
var output []struct {
|
|
SQL string
|
|
Plan []string
|
|
Result []string
|
|
}
|
|
s.testData.GetTestCases(c, &input, &output)
|
|
for i, sql := range input {
|
|
s.testData.OnRecord(func() {
|
|
output[i].SQL = sql
|
|
output[i].Plan = s.testData.ConvertRowsToStrings(tk.MustQuery("explain " + sql).Rows())
|
|
output[i].Result = s.testData.ConvertRowsToStrings(tk.MustQuery(sql).Rows())
|
|
})
|
|
tk.MustQuery("explain " + sql).Check(testkit.Rows(output[i].Plan...))
|
|
tk.MustQuery(sql).Check(testkit.Rows(output[i].Result...))
|
|
}
|
|
}
|
|
|
|
func (s *testIntegrationSuite) TestCascadePlannerHashedPartTable(c *C) {
|
|
tk := testkit.NewTestKit(c, s.store)
|
|
tk.MustExec("use test")
|
|
tk.MustExec("drop table if exists pt1")
|
|
tk.MustExec("create table pt1(a bigint, b bigint) partition by hash(a) partitions 4")
|
|
tk.MustExec(`insert into pt1 values(1,10)`)
|
|
tk.MustExec(`insert into pt1 values(2,20)`)
|
|
tk.MustExec(`insert into pt1 values(3,30)`)
|
|
tk.MustExec(`insert into pt1 values(4,40)`)
|
|
tk.MustExec(`insert into pt1 values(5,50)`)
|
|
|
|
tk.MustExec("set @@tidb_enable_cascades_planner = 1")
|
|
var input []string
|
|
var output []struct {
|
|
SQL string
|
|
Plan []string
|
|
Result []string
|
|
}
|
|
s.testData.GetTestCases(c, &input, &output)
|
|
for i, sql := range input {
|
|
s.testData.OnRecord(func() {
|
|
output[i].SQL = sql
|
|
output[i].Plan = s.testData.ConvertRowsToStrings(tk.MustQuery("explain " + sql).Rows())
|
|
output[i].Result = s.testData.ConvertRowsToStrings(tk.MustQuery(sql).Rows())
|
|
})
|
|
tk.MustQuery("explain " + sql).Check(testkit.Rows(output[i].Plan...))
|
|
tk.MustQuery(sql).Check(testkit.Rows(output[i].Result...))
|
|
}
|
|
}
|
|
|
|
func (s *testIntegrationSuite) TestInlineProjection(c *C) {
|
|
tk := testkit.NewTestKitWithInit(c, s.store)
|
|
tk.MustExec("drop table if exists t1, t2;")
|
|
tk.MustExec("create table t1(a bigint, b bigint, index idx_a(a), index idx_b(b));")
|
|
tk.MustExec("create table t2(a bigint, b bigint, index idx_a(a), index idx_b(b));")
|
|
tk.MustExec("insert into t1 values (1, 1), (2, 2);")
|
|
tk.MustExec("insert into t2 values (1, 1), (3, 3);")
|
|
tk.MustExec("set session tidb_enable_cascades_planner = 1;")
|
|
var input []string
|
|
var output []struct {
|
|
SQL string
|
|
Plan []string
|
|
Result []string
|
|
}
|
|
s.testData.GetTestCases(c, &input, &output)
|
|
for i, sql := range input {
|
|
s.testData.OnRecord(func() {
|
|
output[i].SQL = sql
|
|
output[i].Plan = s.testData.ConvertRowsToStrings(tk.MustQuery("explain " + sql).Rows())
|
|
output[i].Result = s.testData.ConvertRowsToStrings(tk.MustQuery(sql).Rows())
|
|
})
|
|
tk.MustQuery("explain " + sql).Check(testkit.Rows(output[i].Plan...))
|
|
tk.MustQuery(sql).Check(testkit.Rows(output[i].Result...))
|
|
}
|
|
}
|
|
|