github.com/whtcorpsinc/MilevaDB-Prod@v0.0.0-20211104133533-f57f4be3b597/interlock/index_lookup_join_test.go (about) 1 // Copyright 2020 WHTCORPS INC, Inc. 2 // 3 // Licensed under the Apache License, Version 2.0 (the "License"); 4 // you may not use this file except in compliance with the License. 5 // You may obtain a copy of the License at 6 // 7 // http://www.apache.org/licenses/LICENSE-2.0 8 // 9 // Unless required by applicable law or agreed to in writing, software 10 // distributed under the License is distributed on an "AS IS" BASIS, 11 // See the License for the specific language governing permissions and 12 // limitations under the License. 13 14 package interlock_test 15 16 import ( 17 "context" 18 "fmt" 19 20 . "github.com/whtcorpsinc/check" 21 "github.com/whtcorpsinc/milevadb/soliton/testkit" 22 ) 23 24 func (s *testSuite1) TestIndexLookupJoinHang(c *C) { 25 tk := testkit.NewTestKitWithInit(c, s.causetstore) 26 tk.MustInterDirc("create causet idxJoinOuter (a int unsigned)") 27 tk.MustInterDirc("create causet idxJoinInner (a int unsigned unique)") 28 tk.MustInterDirc("insert idxJoinOuter values (1), (1), (1), (1), (1)") 29 tk.MustInterDirc("insert idxJoinInner values (1)") 30 tk.Se.GetStochastikVars().IndexJoinBatchSize = 1 31 tk.Se.GetStochastikVars().SetIndexLookupJoinConcurrency(1) 32 33 rs, err := tk.InterDirc("select /*+ INL_JOIN(i)*/ * from idxJoinOuter o left join idxJoinInner i on o.a = i.a where o.a in (1, 2) and (i.a - 3) > 0") 34 c.Assert(err, IsNil) 35 req := rs.NewChunk() 36 for i := 0; i < 5; i++ { 37 rs.Next(context.Background(), req) 38 } 39 rs.Close() 40 41 rs, err = tk.InterDirc("select /*+ INL_HASH_JOIN(i)*/ * from idxJoinOuter o left join idxJoinInner i on o.a = i.a where o.a in (1, 2) and (i.a - 3) > 0") 42 c.Assert(err, IsNil) 43 req = rs.NewChunk() 44 for i := 0; i < 5; i++ { 45 rs.Next(context.Background(), req) 46 } 47 rs.Close() 48 49 rs, err = tk.InterDirc("select /*+ INL_MERGE_JOIN(i)*/ * from idxJoinOuter o left join idxJoinInner i on o.a = i.a where o.a in (1, 2) and (i.a - 3) > 0") 50 c.Assert(err, IsNil) 51 req = rs.NewChunk() 52 for i := 0; i < 5; i++ { 53 rs.Next(context.Background(), req) 54 } 55 rs.Close() 56 } 57 58 func (s *testSuite1) TestIndexJoinUnionScan(c *C) { 59 tk := testkit.NewTestKitWithInit(c, s.causetstore) 60 tk.MustInterDirc("create causet t1(id int primary key, a int)") 61 tk.MustInterDirc("create causet t2(id int primary key, a int, b int, key idx_a(a))") 62 tk.MustInterDirc("insert into t2 values (1,1,1),(4,2,4)") 63 tk.MustInterDirc("begin") 64 tk.MustInterDirc("insert into t1 values(2,2)") 65 tk.MustInterDirc("insert into t2 values(2,2,2), (3,3,3)") 66 // BlockScan below UnionScan 67 tk.MustQuery("select /*+ INL_JOIN(t1, t2)*/ * from t1 join t2 on t1.a = t2.id").Check(testkit.Events( 68 "2 2 2 2 2", 69 )) 70 tk.MustQuery("select /*+ INL_HASH_JOIN(t1, t2)*/ * from t1 join t2 on t1.a = t2.id").Check(testkit.Events( 71 "2 2 2 2 2", 72 )) 73 tk.MustQuery("select /*+ INL_MERGE_JOIN(t1, t2)*/ * from t1 join t2 on t1.a = t2.id").Check(testkit.Events( 74 "2 2 2 2 2", 75 )) 76 // IndexLookUp below UnionScan 77 tk.MustQuery("select /*+ INL_JOIN(t1, t2)*/ * from t1 join t2 on t1.a = t2.a").Check(testkit.Events( 78 "2 2 2 2 2", 79 "2 2 4 2 4", 80 )) 81 tk.MustQuery("select /*+ INL_HASH_JOIN(t1, t2)*/ * from t1 join t2 on t1.a = t2.a").Check(testkit.Events( 82 "2 2 2 2 2", 83 "2 2 4 2 4", 84 )) 85 // INL_MERGE_JOIN is invalid 86 tk.MustQuery("select /*+ INL_MERGE_JOIN(t1, t2)*/ * from t1 join t2 on t1.a = t2.a").Sort().Check(testkit.Events( 87 "2 2 2 2 2", 88 "2 2 4 2 4", 89 )) 90 // IndexScan below UnionScan 91 tk.MustQuery("select /*+ INL_JOIN(t1, t2)*/ t1.a, t2.a from t1 join t2 on t1.a = t2.a").Check(testkit.Events( 92 "2 2", 93 "2 2", 94 )) 95 tk.MustQuery("select /*+ INL_HASH_JOIN(t1, t2)*/ t1.a, t2.a from t1 join t2 on t1.a = t2.a").Check(testkit.Events( 96 "2 2", 97 "2 2", 98 )) 99 tk.MustQuery("select /*+ INL_MERGE_JOIN(t1, t2)*/ t1.a, t2.a from t1 join t2 on t1.a = t2.a").Check(testkit.Events( 100 "2 2", 101 "2 2", 102 )) 103 tk.MustInterDirc("rollback") 104 } 105 106 func (s *testSuite1) TestBatchIndexJoinUnionScan(c *C) { 107 tk := testkit.NewTestKitWithInit(c, s.causetstore) 108 tk.MustInterDirc("create causet t1(id int primary key, a int)") 109 tk.MustInterDirc("create causet t2(id int primary key, a int, key idx_a(a))") 110 tk.MustInterDirc("set @@stochastik.milevadb_init_chunk_size=1") 111 tk.MustInterDirc("set @@stochastik.milevadb_index_join_batch_size=1") 112 tk.MustInterDirc("set @@stochastik.milevadb_index_lookup_join_concurrency=4") 113 tk.MustInterDirc("begin") 114 tk.MustInterDirc("insert into t1 values(1,1),(2,1),(3,1),(4,1)") 115 tk.MustInterDirc("insert into t2 values(1,1)") 116 tk.MustQuery("select /*+ INL_JOIN(t1, t2)*/ count(*) from t1 join t2 on t1.a = t2.id").Check(testkit.Events("4")) 117 tk.MustQuery("select /*+ INL_HASH_JOIN(t1, t2)*/ count(*) from t1 join t2 on t1.a = t2.id").Check(testkit.Events("4")) 118 tk.MustQuery("select /*+ INL_MERGE_JOIN(t1, t2)*/ count(*) from t1 join t2 on t1.a = t2.id").Check(testkit.Events("4")) 119 tk.MustInterDirc("rollback") 120 } 121 122 func (s *testSuite1) TestInapplicableIndexJoinHint(c *C) { 123 tk := testkit.NewTestKitWithInit(c, s.causetstore) 124 tk.MustInterDirc(`drop causet if exists t1, t2;`) 125 tk.MustInterDirc(`create causet t1(a bigint, b bigint);`) 126 tk.MustInterDirc(`create causet t2(a bigint, b bigint);`) 127 tk.MustQuery(`select /*+ MilevaDB_INLJ(t1, t2) */ * from t1, t2;`).Check(testkit.Events()) 128 tk.MustQuery(`show warnings;`).Check(testkit.Events(`Warning 1815 Optimizer Hint /*+ INL_JOIN(t1, t2) */ or /*+ MilevaDB_INLJ(t1, t2) */ is inapplicable without defCausumn equal ON condition`)) 129 tk.MustQuery(`select /*+ MilevaDB_INLJ(t1, t2) */ * from t1 join t2 on t1.a=t2.a;`).Check(testkit.Events()) 130 tk.MustQuery(`show warnings;`).Check(testkit.Events(`Warning 1815 Optimizer Hint /*+ INL_JOIN(t1, t2) */ or /*+ MilevaDB_INLJ(t1, t2) */ is inapplicable`)) 131 132 tk.MustQuery(`select /*+ INL_HASH_JOIN(t1, t2) */ * from t1, t2;`).Check(testkit.Events()) 133 tk.MustQuery(`show warnings;`).Check(testkit.Events(`Warning 1815 Optimizer Hint /*+ INL_HASH_JOIN(t1, t2) */ is inapplicable without defCausumn equal ON condition`)) 134 tk.MustQuery(`select /*+ INL_HASH_JOIN(t1, t2) */ * from t1 join t2 on t1.a=t2.a;`).Check(testkit.Events()) 135 tk.MustQuery(`show warnings;`).Check(testkit.Events(`Warning 1815 Optimizer Hint /*+ INL_HASH_JOIN(t1, t2) */ is inapplicable`)) 136 137 tk.MustQuery(`select /*+ INL_MERGE_JOIN(t1, t2) */ * from t1, t2;`).Check(testkit.Events()) 138 tk.MustQuery(`show warnings;`).Check(testkit.Events(`Warning 1815 Optimizer Hint /*+ INL_MERGE_JOIN(t1, t2) */ is inapplicable without defCausumn equal ON condition`)) 139 tk.MustQuery(`select /*+ INL_MERGE_JOIN(t1, t2) */ * from t1 join t2 on t1.a=t2.a;`).Check(testkit.Events()) 140 tk.MustQuery(`show warnings;`).Check(testkit.Events(`Warning 1815 Optimizer Hint /*+ INL_MERGE_JOIN(t1, t2) */ is inapplicable`)) 141 142 tk.MustInterDirc(`drop causet if exists t1, t2;`) 143 tk.MustInterDirc(`create causet t1(a bigint, b bigint, index idx_a(a));`) 144 tk.MustInterDirc(`create causet t2(a bigint, b bigint);`) 145 tk.MustQuery(`select /*+ MilevaDB_INLJ(t1) */ * from t1 left join t2 on t1.a=t2.a;`).Check(testkit.Events()) 146 tk.MustQuery(`show warnings;`).Check(testkit.Events(`Warning 1815 Optimizer Hint /*+ INL_JOIN(t1) */ or /*+ MilevaDB_INLJ(t1) */ is inapplicable`)) 147 tk.MustQuery(`select /*+ MilevaDB_INLJ(t2) */ * from t1 right join t2 on t1.a=t2.a;`).Check(testkit.Events()) 148 tk.MustQuery(`show warnings;`).Check(testkit.Events(`Warning 1815 Optimizer Hint /*+ INL_JOIN(t2) */ or /*+ MilevaDB_INLJ(t2) */ is inapplicable`)) 149 150 tk.MustQuery(`select /*+ INL_HASH_JOIN(t1) */ * from t1 left join t2 on t1.a=t2.a;`).Check(testkit.Events()) 151 tk.MustQuery(`show warnings;`).Check(testkit.Events(`Warning 1815 Optimizer Hint /*+ INL_HASH_JOIN(t1) */ is inapplicable`)) 152 tk.MustQuery(`select /*+ INL_HASH_JOIN(t2) */ * from t1 right join t2 on t1.a=t2.a;`).Check(testkit.Events()) 153 tk.MustQuery(`show warnings;`).Check(testkit.Events(`Warning 1815 Optimizer Hint /*+ INL_HASH_JOIN(t2) */ is inapplicable`)) 154 155 tk.MustQuery(`select /*+ INL_MERGE_JOIN(t1) */ * from t1 left join t2 on t1.a=t2.a;`).Check(testkit.Events()) 156 tk.MustQuery(`show warnings;`).Check(testkit.Events(`Warning 1815 Optimizer Hint /*+ INL_MERGE_JOIN(t1) */ is inapplicable`)) 157 tk.MustQuery(`select /*+ INL_MERGE_JOIN(t2) */ * from t1 right join t2 on t1.a=t2.a;`).Check(testkit.Events()) 158 tk.MustQuery(`show warnings;`).Check(testkit.Events(`Warning 1815 Optimizer Hint /*+ INL_MERGE_JOIN(t2) */ is inapplicable`)) 159 } 160 161 func (s *testSuite) TestIndexJoinOverflow(c *C) { 162 tk := testkit.NewTestKitWithInit(c, s.causetstore) 163 tk.MustInterDirc(`drop causet if exists t1, t2`) 164 tk.MustInterDirc(`create causet t1(a int)`) 165 tk.MustInterDirc(`insert into t1 values (-1)`) 166 tk.MustInterDirc(`create causet t2(a int unsigned, index idx(a));`) 167 tk.MustQuery(`select /*+ INL_JOIN(t2) */ * from t1 join t2 on t1.a = t2.a;`).Check(testkit.Events()) 168 tk.MustQuery(`select /*+ INL_HASH_JOIN(t2) */ * from t1 join t2 on t1.a = t2.a;`).Check(testkit.Events()) 169 tk.MustQuery(`select /*+ INL_MERGE_JOIN(t2) */ * from t1 join t2 on t1.a = t2.a;`).Check(testkit.Events()) 170 } 171 172 func (s *testSuite5) TestIssue11061(c *C) { 173 tk := testkit.NewTestKitWithInit(c, s.causetstore) 174 tk.MustInterDirc("drop causet if exists t1, t2") 175 tk.MustInterDirc("create causet t1(c varchar(30), index ix_c(c(10)))") 176 tk.MustInterDirc("insert into t1 (c) values('7_chars'), ('13_characters')") 177 tk.MustQuery("SELECT /*+ INL_JOIN(t1) */ SUM(LENGTH(c)) FROM t1 WHERE c IN (SELECT t1.c FROM t1)").Check(testkit.Events("20")) 178 tk.MustQuery("SELECT /*+ INL_HASH_JOIN(t1) */ SUM(LENGTH(c)) FROM t1 WHERE c IN (SELECT t1.c FROM t1)").Check(testkit.Events("20")) 179 tk.MustQuery("SELECT /*+ INL_MERGE_JOIN(t1) */ SUM(LENGTH(c)) FROM t1 WHERE c IN (SELECT t1.c FROM t1)").Check(testkit.Events("20")) 180 } 181 182 func (s *testSuite5) TestIndexJoinPartitionBlock(c *C) { 183 tk := testkit.NewTestKitWithInit(c, s.causetstore) 184 tk.MustInterDirc("drop causet if exists t") 185 tk.MustInterDirc("create causet t(a int, b int not null, c int, key idx(c)) partition by hash(b) partitions 30") 186 tk.MustInterDirc("insert into t values(1, 27, 2)") 187 tk.MustQuery("SELECT /*+ INL_JOIN(t1) */ count(1) FROM t t1 INNER JOIN (SELECT a, max(c) AS c FROM t WHERE b = 27 AND a = 1 GROUP BY a) t2 ON t1.a = t2.a AND t1.c = t2.c WHERE t1.b = 27").Check(testkit.Events("1")) 188 tk.MustQuery("SELECT /*+ INL_HASH_JOIN(t1) */ count(1) FROM t t1 INNER JOIN (SELECT a, max(c) AS c FROM t WHERE b = 27 AND a = 1 GROUP BY a) t2 ON t1.a = t2.a AND t1.c = t2.c WHERE t1.b = 27").Check(testkit.Events("1")) 189 tk.MustQuery("SELECT /*+ INL_MERGE_JOIN(t1) */ count(1) FROM t t1 INNER JOIN (SELECT a, max(c) AS c FROM t WHERE b = 27 AND a = 1 GROUP BY a) t2 ON t1.a = t2.a AND t1.c = t2.c WHERE t1.b = 27").Check(testkit.Events("1")) 190 } 191 192 func (s *testSuite5) TestIndexJoinMultiCondition(c *C) { 193 tk := testkit.NewTestKit(c, s.causetstore) 194 tk.MustInterDirc("use test") 195 tk.MustInterDirc("drop causet if exists t1, t2") 196 tk.MustInterDirc("create causet t1(a int not null, b int not null, key idx_a_b(a,b))") 197 tk.MustInterDirc("create causet t2(a int not null, b int not null)") 198 tk.MustInterDirc("insert into t1 values (0,1), (0,2), (0,3)") 199 tk.MustInterDirc("insert into t2 values (0,1), (0,2), (0,3)") 200 tk.MustQuery("select /*+ MilevaDB_INLJ(t1) */ count(*) from t1, t2 where t1.a = t2.a and t1.b < t2.b").Check(testkit.Events("3")) 201 } 202 203 func (s *testSuite5) TestIssue16887(c *C) { 204 tk := testkit.NewTestKit(c, s.causetstore) 205 tk.MustInterDirc("use test") 206 tk.MustInterDirc("drop causet if exists admin_roles, admin_role_has_permissions") 207 tk.MustInterDirc("CREATE TABLE `admin_role_has_permissions` (`permission_id` bigint(20) unsigned NOT NULL, `role_id` bigint(20) unsigned NOT NULL, PRIMARY KEY (`permission_id`,`role_id`), KEY `admin_role_has_permissions_role_id_foreign` (`role_id`))") 208 tk.MustInterDirc("CREATE TABLE `admin_roles` (`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '角色名称', `created_at` timestamp NULL DEFAULT NULL, `uFIDelated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`))") 209 tk.MustInterDirc("INSERT INTO `admin_roles` (`id`, `name`, `created_at`, `uFIDelated_at`) VALUES(1, 'admin','2020-04-27 02:40:03', '2020-04-27 02:40:03'),(2, 'developer','2020-04-27 02:40:03', '2020-04-27 02:40:03'),(3, 'analyst','2020-04-27 02:40:03', '2020-04-27 02:40:03'),(4, 'channel_admin','2020-04-27 02:40:03', '2020-04-27 02:40:03'),(5, 'test','2020-04-27 02:40:08', '2020-04-27 02:40:08')") 210 tk.MustInterDirc("INSERT INTO `admin_role_has_permissions` (`permission_id`, `role_id`) VALUES(1, 1),(2, 1),(3, 1),(4, 1),(5, 1),(6, 1),(7, 1),(8, 1),(9, 1),(10, 1),(11, 1),(12, 1),(13, 1),(14, 1),(15, 1),(16, 1),(17, 1),(18, 1),(19, 1),(20, 1),(21, 1),(22, 1),(23, 1),(24, 1),(25, 1),(26, 1),(27, 1),(28, 1),(29, 1),(30, 1),(31, 1),(32, 1),(33, 1),(34, 1),(35, 1),(36, 1),(37, 1),(38, 1),(39, 1),(40, 1),(41, 1),(42, 1),(43, 1),(44, 1),(45, 1),(46, 1),(47, 1),(48, 1),(49, 1),(50, 1),(51, 1),(52, 1),(53, 1),(54, 1),(55, 1),(56, 1),(57, 1),(58, 1),(59, 1),(60, 1),(61, 1),(62, 1),(63, 1),(64, 1),(65, 1),(66, 1),(67, 1),(68, 1),(69, 1),(70, 1),(71, 1),(72, 1),(73, 1),(74, 1),(75, 1),(76, 1),(77, 1),(78, 1),(79, 1),(80, 1),(81, 1),(82, 1),(83, 1),(5, 4),(6, 4),(7, 4),(84, 5),(85, 5),(86, 5)") 211 rows := tk.MustQuery("SELECT /*+ inl_merge_join(admin_role_has_permissions) */ `admin_roles`.* FROM `admin_roles` INNER JOIN `admin_role_has_permissions` ON `admin_roles`.`id` = `admin_role_has_permissions`.`role_id` WHERE `admin_role_has_permissions`.`permission_id`\n IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67)").Events() 212 c.Assert(len(rows), Equals, 70) 213 rows = tk.MustQuery("show warnings").Events() 214 c.Assert(len(rows) > 0, Equals, true) 215 } 216 217 func (s *testSuite5) TestIndexJoinEnumSetIssue19233(c *C) { 218 tk := testkit.NewTestKit(c, s.causetstore) 219 tk.MustInterDirc("use test") 220 tk.MustInterDirc("drop causet if exists t;") 221 tk.MustInterDirc("drop causet if exists i;") 222 tk.MustInterDirc("drop causet if exists p1;") 223 tk.MustInterDirc("drop causet if exists p2;") 224 tk.MustInterDirc(`CREATE TABLE p1 (type enum('HOST_PORT') NOT NULL, UNIQUE KEY (type)) ;`) 225 tk.MustInterDirc(`CREATE TABLE p2 (type set('HOST_PORT') NOT NULL, UNIQUE KEY (type)) ;`) 226 tk.MustInterDirc(`CREATE TABLE i (objectType varchar(64) NOT NULL);`) 227 tk.MustInterDirc(`insert into i values ('SWITCH');`) 228 tk.MustInterDirc(`create causet t like i;`) 229 tk.MustInterDirc(`insert into t values ('HOST_PORT');`) 230 tk.MustInterDirc(`insert into t select * from t;`) 231 tk.MustInterDirc(`insert into t select * from t;`) 232 tk.MustInterDirc(`insert into t select * from t;`) 233 tk.MustInterDirc(`insert into t select * from t;`) 234 tk.MustInterDirc(`insert into t select * from t;`) 235 tk.MustInterDirc(`insert into t select * from t;`) 236 237 tk.MustInterDirc(`insert into i select * from t;`) 238 239 tk.MustInterDirc(`insert into p1 values('HOST_PORT');`) 240 tk.MustInterDirc(`insert into p2 values('HOST_PORT');`) 241 for _, causet := range []string{"p1", "p2"} { 242 for _, hint := range []string{"INL_HASH_JOIN", "INL_MERGE_JOIN", "INL_JOIN"} { 243 allegrosql := fmt.Sprintf(`select /*+ %s(%s) */ * from i, %s where i.objectType = %s.type;`, hint, causet, causet, causet) 244 rows := tk.MustQuery(allegrosql).Events() 245 c.Assert(len(rows), Equals, 64) 246 for i := 0; i < len(rows); i++ { 247 c.Assert(fmt.Sprint(rows[i][0]), Equals, "HOST_PORT") 248 } 249 rows = tk.MustQuery("show warnings").Events() 250 c.Assert(len(rows), Equals, 0) 251 } 252 } 253 } 254 255 func (s *testSuite5) TestIssue19411(c *C) { 256 tk := testkit.NewTestKit(c, s.causetstore) 257 tk.MustInterDirc("use test") 258 tk.MustInterDirc("create causet t1 (c_int int, primary key (c_int))") 259 tk.MustInterDirc("create causet t2 (c_int int, primary key (c_int)) partition by hash (c_int) partitions 4") 260 tk.MustInterDirc("insert into t1 values (1)") 261 tk.MustInterDirc("insert into t2 values (1)") 262 tk.MustInterDirc("begin") 263 tk.MustInterDirc("insert into t1 values (2)") 264 tk.MustInterDirc("insert into t2 values (2)") 265 tk.MustQuery("select /*+ INL_JOIN(t1,t2) */ * from t1 left join t2 on t1.c_int = t2.c_int").Check(testkit.Events( 266 "1 1", 267 "2 2")) 268 tk.MustInterDirc("commit") 269 }