github.com/XiaoMi/Gaea@v1.2.5/proxy/plan/plan_select_test.go (about) 1 // Copyright 2019 The Gaea Authors. All Rights Reserved. 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 // WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 12 // See the License for the specific language governing permissions and 13 // limitations under the License. 14 15 package plan 16 17 import ( 18 "testing" 19 20 "github.com/XiaoMi/Gaea/proxy/router" 21 ) 22 23 func TestSimpleSelectShardMycatMod(t *testing.T) { 24 ns, err := preparePlanInfo() 25 if err != nil { 26 t.Fatalf("prepare namespace error: %v", err) 27 } 28 29 tests := []SQLTestcase{ 30 { 31 db: "db_mycat", 32 sql: "select * from tbl_mycat where id = 0", 33 sqls: map[string]map[string][]string{ 34 "slice-0": { 35 "db_mycat_0": {"SELECT * FROM `tbl_mycat` WHERE `id`=0"}, 36 }, 37 }, 38 }, 39 { 40 db: "db_mycat", 41 sql: "select id from tbl_mycat where id = 0", 42 sqls: map[string]map[string][]string{ 43 "slice-0": { 44 "db_mycat_0": {"SELECT `id` FROM `tbl_mycat` WHERE `id`=0"}, 45 }, 46 }, 47 }, 48 { 49 db: "db_mycat", 50 sql: "select tbl_mycat.id from tbl_mycat where id = 0", 51 sqls: map[string]map[string][]string{ 52 "slice-0": { 53 "db_mycat_0": {"SELECT `tbl_mycat`.`id` FROM `tbl_mycat` WHERE `id`=0"}, 54 }, 55 }, 56 }, 57 { 58 db: "db_mycat", 59 sql: "select * from tbl_mycat where tbl_mycat.id = 0", 60 sqls: map[string]map[string][]string{ 61 "slice-0": { 62 "db_mycat_0": {"SELECT * FROM `tbl_mycat` WHERE `tbl_mycat`.`id`=0"}, 63 }, 64 }, 65 }, 66 { 67 db: "db_mycat", 68 sql: "select * from db_mycat.tbl_mycat where db_mycat.tbl_mycat.id = 0", 69 sqls: map[string]map[string][]string{ 70 "slice-0": { 71 "db_mycat_0": {"SELECT * FROM `db_mycat_0`.`tbl_mycat` WHERE `db_mycat_0`.`tbl_mycat`.`id`=0"}, 72 }, 73 }, 74 }, 75 { 76 db: "db_mycat", 77 sql: "select * from tbl_mycat where id = 1", 78 sqls: map[string]map[string][]string{ 79 "slice-0": { 80 "db_mycat_1": {"SELECT * FROM `tbl_mycat` WHERE `id`=1"}, 81 }, 82 }, 83 }, 84 { 85 db: "db_mycat", 86 sql: "select * from tbl_mycat where id = 2", 87 sqls: map[string]map[string][]string{ 88 "slice-1": { 89 "db_mycat_2": {"SELECT * FROM `tbl_mycat` WHERE `id`=2"}, 90 }, 91 }, 92 }, 93 { 94 db: "db_mycat", 95 sql: "select * from tbl_mycat where id = 3", 96 sqls: map[string]map[string][]string{ 97 "slice-1": { 98 "db_mycat_3": {"SELECT * FROM `tbl_mycat` WHERE `id`=3"}, 99 }, 100 }, 101 }, 102 { 103 db: "db_mycat", 104 sql: "select * from tbl_mycat where id = 4", 105 sqls: map[string]map[string][]string{ 106 "slice-0": { 107 "db_mycat_0": {"SELECT * FROM `tbl_mycat` WHERE `id`=4"}, 108 }, 109 }, 110 }, 111 { 112 db: "db_mycat", 113 sql: "select * from tbl_mycat where id in (0,1,2,3,4,6)", 114 sqls: map[string]map[string][]string{ 115 "slice-0": { 116 "db_mycat_0": {"SELECT * FROM `tbl_mycat` WHERE `id` IN (0,4)"}, 117 "db_mycat_1": {"SELECT * FROM `tbl_mycat` WHERE `id` IN (1)"}, 118 }, 119 "slice-1": { 120 "db_mycat_2": {"SELECT * FROM `tbl_mycat` WHERE `id` IN (2,6)"}, 121 "db_mycat_3": {"SELECT * FROM `tbl_mycat` WHERE `id` IN (3)"}, 122 }, 123 }, 124 }, 125 { 126 db: "db_mycat", 127 sql: "select * from tbl_mycat where k = 0", 128 sqls: map[string]map[string][]string{ 129 "slice-0": { 130 "db_mycat_0": {"SELECT * FROM `tbl_mycat` WHERE `k`=0"}, 131 "db_mycat_1": {"SELECT * FROM `tbl_mycat` WHERE `k`=0"}, 132 }, 133 "slice-1": { 134 "db_mycat_2": {"SELECT * FROM `tbl_mycat` WHERE `k`=0"}, 135 "db_mycat_3": {"SELECT * FROM `tbl_mycat` WHERE `k`=0"}, 136 }, 137 }, 138 }, 139 } 140 for _, test := range tests { 141 t.Run(test.sql, getTestFunc(ns, test)) 142 } 143 } 144 145 func TestSimpleSelectShardMycatMurmur(t *testing.T) { 146 ns, err := preparePlanInfo() 147 if err != nil { 148 t.Fatalf("prepare namespace error: %v", err) 149 } 150 151 tests := []SQLTestcase{ 152 { 153 db: "db_mycat", 154 sql: "select * from tbl_mycat_murmur where id = 0", 155 sqls: map[string]map[string][]string{ 156 "slice-1": { 157 "db_mycat_2": {"SELECT * FROM `tbl_mycat_murmur` WHERE `id`=0"}, 158 }, 159 }, 160 }, 161 { 162 db: "db_mycat", 163 sql: "select * from tbl_mycat_murmur where id = 1", 164 sqls: map[string]map[string][]string{ 165 "slice-0": { 166 "db_mycat_1": {"SELECT * FROM `tbl_mycat_murmur` WHERE `id`=1"}, 167 }, 168 }, 169 }, 170 { 171 db: "db_mycat", 172 sql: "select * from tbl_mycat_murmur where id = 2", 173 sqls: map[string]map[string][]string{ 174 "slice-0": { 175 "db_mycat_1": {"SELECT * FROM `tbl_mycat_murmur` WHERE `id`=2"}, 176 }, 177 }, 178 }, 179 { 180 db: "db_mycat", 181 sql: "select * from tbl_mycat_murmur where id = 3", 182 sqls: map[string]map[string][]string{ 183 "slice-0": { 184 "db_mycat_1": {"SELECT * FROM `tbl_mycat_murmur` WHERE `id`=3"}, 185 }, 186 }, 187 }, 188 { 189 db: "db_mycat", 190 sql: "select * from tbl_mycat_murmur where id = 4", 191 sqls: map[string]map[string][]string{ 192 "slice-1": { 193 "db_mycat_2": {"SELECT * FROM `tbl_mycat_murmur` WHERE `id`=4"}, 194 }, 195 }, 196 }, 197 { 198 db: "db_mycat", 199 sql: "select * from tbl_mycat_murmur where id in (0,1,2,3,4,6)", 200 sqls: map[string]map[string][]string{ 201 "slice-0": { 202 "db_mycat_0": {"SELECT * FROM `tbl_mycat_murmur` WHERE `id` IN (6)"}, 203 "db_mycat_1": {"SELECT * FROM `tbl_mycat_murmur` WHERE `id` IN (1,2,3)"}, 204 }, 205 "slice-1": { 206 "db_mycat_2": {"SELECT * FROM `tbl_mycat_murmur` WHERE `id` IN (0,4)"}, 207 }, 208 }, 209 }, 210 { 211 db: "db_mycat", 212 sql: "select * from tbl_mycat_murmur where k = 0", 213 sqls: map[string]map[string][]string{ 214 "slice-0": { 215 "db_mycat_0": {"SELECT * FROM `tbl_mycat_murmur` WHERE `k`=0"}, 216 "db_mycat_1": {"SELECT * FROM `tbl_mycat_murmur` WHERE `k`=0"}, 217 }, 218 "slice-1": { 219 "db_mycat_2": {"SELECT * FROM `tbl_mycat_murmur` WHERE `k`=0"}, 220 "db_mycat_3": {"SELECT * FROM `tbl_mycat_murmur` WHERE `k`=0"}, 221 }, 222 }, 223 }, 224 } 225 for _, test := range tests { 226 t.Run(test.sql, getTestFunc(ns, test)) 227 } 228 } 229 230 func TestSimpleSelectShardMycatMurmur_ShardKeyTypeString(t *testing.T) { 231 ns, err := preparePlanInfo() 232 if err != nil { 233 t.Fatalf("prepare namespace error: %v", err) 234 } 235 236 tests := []SQLTestcase{ 237 { 238 db: "db_mycat", 239 sql: "select * from tbl_mycat_murmur where id in ('0')", 240 sqls: map[string]map[string][]string{ 241 "slice-1": { 242 "db_mycat_2": {"SELECT * FROM `tbl_mycat_murmur` WHERE `id` IN ('0')"}, 243 }, 244 }, 245 }, 246 { 247 db: "db_mycat", 248 sql: "select * from tbl_mycat_murmur where id = '0'", 249 sqls: map[string]map[string][]string{ 250 "slice-1": { 251 "db_mycat_2": {"SELECT * FROM `tbl_mycat_murmur` WHERE `id`='0'"}, 252 }, 253 }, 254 }, 255 } 256 for _, test := range tests { 257 t.Run(test.sql, getTestFunc(ns, test)) 258 } 259 } 260 261 func TestSimpleSelectShardMycatString(t *testing.T) { 262 ns, err := preparePlanInfo() 263 if err != nil { 264 t.Fatalf("prepare namespace error: %v", err) 265 } 266 267 tests := []SQLTestcase{ 268 { 269 db: "db_mycat", 270 sql: "select * from tbl_mycat_string where id = 0", 271 sqls: map[string]map[string][]string{ 272 "slice-0": { 273 "db_mycat_0": {"SELECT * FROM `tbl_mycat_string` WHERE `id`=0"}, 274 }, 275 }, 276 }, 277 { 278 db: "db_mycat", 279 sql: "select * from tbl_mycat_string where k = 0", 280 sqls: map[string]map[string][]string{ 281 "slice-0": { 282 "db_mycat_0": {"SELECT * FROM `tbl_mycat_string` WHERE `k`=0"}, 283 "db_mycat_1": {"SELECT * FROM `tbl_mycat_string` WHERE `k`=0"}, 284 }, 285 "slice-1": { 286 "db_mycat_2": {"SELECT * FROM `tbl_mycat_string` WHERE `k`=0"}, 287 "db_mycat_3": {"SELECT * FROM `tbl_mycat_string` WHERE `k`=0"}, 288 }, 289 }, 290 }, 291 } 292 for _, test := range tests { 293 t.Run(test.sql, getTestFunc(ns, test)) 294 } 295 } 296 297 func TestSimpleSelectShardMycatLong(t *testing.T) { 298 ns, err := preparePlanInfo() 299 if err != nil { 300 t.Fatalf("prepare namespace error: %v", err) 301 } 302 303 tests := []SQLTestcase{ 304 { 305 db: "db_mycat", 306 sql: "select * from tbl_mycat_long where id = 0", 307 sqls: map[string]map[string][]string{ 308 "slice-0": { 309 "db_mycat_0": {"SELECT * FROM `tbl_mycat_long` WHERE `id`=0"}, 310 }, 311 }, 312 }, 313 { 314 db: "db_mycat", 315 sql: "select * from tbl_mycat_long where id = 1", 316 sqls: map[string]map[string][]string{ 317 "slice-0": { 318 "db_mycat_0": {"SELECT * FROM `tbl_mycat_long` WHERE `id`=1"}, 319 }, 320 }, 321 }, 322 { 323 db: "db_mycat", 324 sql: "select * from tbl_mycat_long where id = 2", 325 sqls: map[string]map[string][]string{ 326 "slice-0": { 327 "db_mycat_0": {"SELECT * FROM `tbl_mycat_long` WHERE `id`=2"}, 328 }, 329 }, 330 }, 331 { 332 db: "db_mycat", 333 sql: "select * from tbl_mycat_long where id = 3", 334 sqls: map[string]map[string][]string{ 335 "slice-0": { 336 "db_mycat_0": {"SELECT * FROM `tbl_mycat_long` WHERE `id`=3"}, 337 }, 338 }, 339 }, 340 { 341 db: "db_mycat", 342 sql: "select * from tbl_mycat_long where id = 256", 343 sqls: map[string]map[string][]string{ 344 "slice-0": { 345 "db_mycat_1": {"SELECT * FROM `tbl_mycat_long` WHERE `id`=256"}, 346 }, 347 }, 348 }, 349 { 350 db: "db_mycat", 351 sql: "select * from tbl_mycat_long where id = 512", 352 sqls: map[string]map[string][]string{ 353 "slice-1": { 354 "db_mycat_2": {"SELECT * FROM `tbl_mycat_long` WHERE `id`=512"}, 355 }, 356 }, 357 }, 358 { 359 db: "db_mycat", 360 sql: "select * from tbl_mycat_long where id = 768", 361 sqls: map[string]map[string][]string{ 362 "slice-1": { 363 "db_mycat_3": {"SELECT * FROM `tbl_mycat_long` WHERE `id`=768"}, 364 }, 365 }, 366 }, 367 { 368 db: "db_mycat", 369 sql: "select * from tbl_mycat_long where id = 769", 370 sqls: map[string]map[string][]string{ 371 "slice-1": { 372 "db_mycat_3": {"SELECT * FROM `tbl_mycat_long` WHERE `id`=769"}, 373 }, 374 }, 375 }, 376 { 377 db: "db_mycat", 378 sql: "select * from tbl_mycat_long where id = 1024", 379 sqls: map[string]map[string][]string{ 380 "slice-0": { 381 "db_mycat_0": {"SELECT * FROM `tbl_mycat_long` WHERE `id`=1024"}, 382 }, 383 }, 384 }, 385 { 386 db: "db_mycat", 387 sql: "select * from tbl_mycat_long where id = 1025", 388 sqls: map[string]map[string][]string{ 389 "slice-0": { 390 "db_mycat_0": {"SELECT * FROM `tbl_mycat_long` WHERE `id`=1025"}, 391 }, 392 }, 393 }, 394 { 395 db: "db_mycat", 396 sql: "select * from tbl_mycat_long where id in (0,1,256,257,512,513,768,769,1024,1025)", 397 sqls: map[string]map[string][]string{ 398 "slice-0": { 399 "db_mycat_0": {"SELECT * FROM `tbl_mycat_long` WHERE `id` IN (0,1,1024,1025)"}, 400 "db_mycat_1": {"SELECT * FROM `tbl_mycat_long` WHERE `id` IN (256,257)"}, 401 }, 402 "slice-1": { 403 "db_mycat_2": {"SELECT * FROM `tbl_mycat_long` WHERE `id` IN (512,513)"}, 404 "db_mycat_3": {"SELECT * FROM `tbl_mycat_long` WHERE `id` IN (768,769)"}, 405 }, 406 }, 407 }, 408 { 409 db: "db_mycat", 410 sql: "select * from tbl_mycat_long where k = 0", 411 sqls: map[string]map[string][]string{ 412 "slice-0": { 413 "db_mycat_0": {"SELECT * FROM `tbl_mycat_long` WHERE `k`=0"}, 414 "db_mycat_1": {"SELECT * FROM `tbl_mycat_long` WHERE `k`=0"}, 415 }, 416 "slice-1": { 417 "db_mycat_2": {"SELECT * FROM `tbl_mycat_long` WHERE `k`=0"}, 418 "db_mycat_3": {"SELECT * FROM `tbl_mycat_long` WHERE `k`=0"}, 419 }, 420 }, 421 }, 422 } 423 for _, test := range tests { 424 t.Run(test.sql, getTestFunc(ns, test)) 425 } 426 } 427 428 func TestMycatSelectMultiTablesEQ(t *testing.T) { 429 ns, err := preparePlanInfo() 430 if err != nil { 431 t.Fatalf("prepare namespace error: %v", err) 432 } 433 434 tests := []SQLTestcase{ 435 { 436 db: "db_mycat", 437 sql: "select * from tbl_mycat, tbl_mycat_child", 438 sqls: map[string]map[string][]string{ 439 "slice-0": { 440 "db_mycat_0": {"SELECT * FROM (`tbl_mycat`) JOIN `tbl_mycat_child`"}, 441 "db_mycat_1": {"SELECT * FROM (`tbl_mycat`) JOIN `tbl_mycat_child`"}, 442 }, 443 "slice-1": { 444 "db_mycat_2": {"SELECT * FROM (`tbl_mycat`) JOIN `tbl_mycat_child`"}, 445 "db_mycat_3": {"SELECT * FROM (`tbl_mycat`) JOIN `tbl_mycat_child`"}, 446 }, 447 }, 448 }, 449 450 { 451 db: "db_mycat", 452 sql: "select * from tbl_mycat, tbl_mycat_child where tbl_mycat.id = COUNT(1)", // 一边是列名, 另一边不支持, 则只替换列名 453 sqls: map[string]map[string][]string{ 454 "slice-0": { 455 "db_mycat_0": {"SELECT * FROM (`tbl_mycat`) JOIN `tbl_mycat_child` WHERE `tbl_mycat`.`id`=COUNT(1)"}, 456 "db_mycat_1": {"SELECT * FROM (`tbl_mycat`) JOIN `tbl_mycat_child` WHERE `tbl_mycat`.`id`=COUNT(1)"}, 457 }, 458 "slice-1": { 459 "db_mycat_2": {"SELECT * FROM (`tbl_mycat`) JOIN `tbl_mycat_child` WHERE `tbl_mycat`.`id`=COUNT(1)"}, 460 "db_mycat_3": {"SELECT * FROM (`tbl_mycat`) JOIN `tbl_mycat_child` WHERE `tbl_mycat`.`id`=COUNT(1)"}, 461 }, 462 }, 463 }, 464 { 465 db: "db_mycat", 466 sql: "select * from tbl_mycat, tbl_mycat_child where tbl_mycat.id = 1", 467 sqls: map[string]map[string][]string{ 468 "slice-0": { 469 "db_mycat_1": {"SELECT * FROM (`tbl_mycat`) JOIN `tbl_mycat_child` WHERE `tbl_mycat`.`id`=1"}, 470 }, 471 }, 472 }, 473 { 474 db: "db_mycat", 475 sql: "select * from tbl_mycat, tbl_mycat_child where tbl_mycat.id is null", 476 sqls: map[string]map[string][]string{ 477 "slice-0": { 478 "db_mycat_0": {"SELECT * FROM (`tbl_mycat`) JOIN `tbl_mycat_child` WHERE `tbl_mycat`.`id` IS NULL"}, 479 "db_mycat_1": {"SELECT * FROM (`tbl_mycat`) JOIN `tbl_mycat_child` WHERE `tbl_mycat`.`id` IS NULL"}, 480 }, 481 "slice-1": { 482 "db_mycat_2": {"SELECT * FROM (`tbl_mycat`) JOIN `tbl_mycat_child` WHERE `tbl_mycat`.`id` IS NULL"}, 483 "db_mycat_3": {"SELECT * FROM (`tbl_mycat`) JOIN `tbl_mycat_child` WHERE `tbl_mycat`.`id` IS NULL"}, 484 }, 485 }, 486 }, 487 // TODO: 分表列是否需要支持等值比较NULL 488 //{ 489 // db: "db_mycat", 490 // sql: "select * from tbl_mycat, tbl_mycat_child where tbl_mycat.id = null", 491 // hasErr: true, 492 //}, 493 { 494 db: "db_mycat", 495 sql: "select * from tbl_mycat, tbl_mycat_child where 1 = 1 and tbl_mycat.id = 1", 496 sqls: map[string]map[string][]string{ 497 "slice-0": { 498 "db_mycat_1": {"SELECT * FROM (`tbl_mycat`) JOIN `tbl_mycat_child` WHERE 1=1 AND `tbl_mycat`.`id`=1"}, 499 }, 500 }, 501 }, 502 { 503 db: "db_mycat", 504 sql: "select * from tbl_mycat join tbl_mycat_child on tbl_mycat.id = 1", 505 sqls: map[string]map[string][]string{ 506 "slice-0": { 507 "db_mycat_1": {"SELECT * FROM `tbl_mycat` JOIN `tbl_mycat_child` ON `tbl_mycat`.`id`=1"}, 508 }, 509 }, 510 }, 511 { 512 db: "db_mycat", 513 sql: "select * from tbl_mycat, tbl_mycat_child where tbl_mycat_child.id = 1", 514 sqls: map[string]map[string][]string{ 515 "slice-0": { 516 "db_mycat_1": {"SELECT * FROM (`tbl_mycat`) JOIN `tbl_mycat_child` WHERE `tbl_mycat_child`.`id`=1"}, 517 }, 518 }, 519 }, 520 { 521 db: "db_mycat", 522 sql: "select * from tbl_mycat join tbl_mycat_child on tbl_mycat_child.id = 1", 523 sqls: map[string]map[string][]string{ 524 "slice-0": { 525 "db_mycat_1": {"SELECT * FROM `tbl_mycat` JOIN `tbl_mycat_child` ON `tbl_mycat_child`.`id`=1"}, 526 }, 527 }, 528 }, 529 { 530 db: "db_mycat", 531 sql: "select * from tbl_mycat, tbl_mycat_child where id = 1", // id is ambiguous that both sharding key in tbl_mycat and tbl_mycat_child 532 hasErr: true, 533 }, 534 { 535 db: "db_mycat", 536 sql: "select * from tbl_mycat join tbl_mycat_child on id = 1", // id is ambiguous that both sharding key in tbl_mycat and tbl_mycat_child 537 hasErr: true, 538 }, 539 { 540 db: "db_mycat", 541 sql: "select * from tbl_mycat, tbl_mycat_user_child where id = 1", // id is not ambiguous 542 sqls: map[string]map[string][]string{ 543 "slice-0": { 544 "db_mycat_1": {"SELECT * FROM (`tbl_mycat`) JOIN `tbl_mycat_user_child` WHERE `id`=1"}, 545 }, 546 }, 547 }, 548 { 549 db: "db_mycat", 550 sql: "select * from tbl_mycat join tbl_mycat_user_child on id = 1", // id is not ambiguous 551 sqls: map[string]map[string][]string{ 552 "slice-0": { 553 "db_mycat_1": {"SELECT * FROM `tbl_mycat` JOIN `tbl_mycat_user_child` ON `id`=1"}, 554 }, 555 }, 556 }, 557 { 558 db: "db_mycat", 559 sql: "select * from tbl_mycat join tbl_mycat_child on tbl_mycat_child.id = 1 where tbl_mycat.id = 1", 560 sqls: map[string]map[string][]string{ 561 "slice-0": { 562 "db_mycat_1": {"SELECT * FROM `tbl_mycat` JOIN `tbl_mycat_child` ON `tbl_mycat_child`.`id`=1 WHERE `tbl_mycat`.`id`=1"}, 563 }, 564 }, 565 }, 566 { 567 db: "db_mycat", 568 sql: "select * from tbl_mycat join tbl_mycat_child on tbl_mycat_child.id = 1 where tbl_mycat.id = 0", 569 sqls: map[string]map[string][]string{}, 570 }, 571 { 572 db: "db_mycat", 573 sql: "select * from tbl_mycat join tbl_mycat_child on tbl_mycat_child.id = 1 or tbl_mycat.id = 2", 574 sqls: map[string]map[string][]string{ 575 "slice-0": { 576 "db_mycat_1": {"SELECT * FROM `tbl_mycat` JOIN `tbl_mycat_child` ON `tbl_mycat_child`.`id`=1 OR `tbl_mycat`.`id`=2"}, 577 }, 578 "slice-1": { 579 "db_mycat_2": {"SELECT * FROM `tbl_mycat` JOIN `tbl_mycat_child` ON `tbl_mycat_child`.`id`=1 OR `tbl_mycat`.`id`=2"}, 580 }, 581 }, 582 }, 583 { 584 db: "db_mycat", 585 sql: "select * from tbl_mycat, tbl_mycat_child where tbl_mycat_child.id = 1 or tbl_mycat.id = 2", 586 sqls: map[string]map[string][]string{ 587 "slice-0": { 588 "db_mycat_1": {"SELECT * FROM (`tbl_mycat`) JOIN `tbl_mycat_child` WHERE `tbl_mycat_child`.`id`=1 OR `tbl_mycat`.`id`=2"}, 589 }, 590 "slice-1": { 591 "db_mycat_2": {"SELECT * FROM (`tbl_mycat`) JOIN `tbl_mycat_child` WHERE `tbl_mycat_child`.`id`=1 OR `tbl_mycat`.`id`=2"}, 592 }, 593 }, 594 }, 595 { 596 db: "db_mycat", 597 sql: "select * from tbl_mycat join tbl_mycat_murmur on tbl_mycat.id = 1", // tables have different route 598 hasErr: true, 599 }, 600 601 // expr.R 602 { 603 db: "db_mycat", 604 sql: "select * from tbl_mycat, tbl_mycat_child where COUNT(1) = tbl_mycat.id", // 一边是列名, 另一边不支持, 则只替换列名 605 sqls: map[string]map[string][]string{ 606 "slice-0": { 607 "db_mycat_0": {"SELECT * FROM (`tbl_mycat`) JOIN `tbl_mycat_child` WHERE COUNT(1)=`tbl_mycat`.`id`"}, 608 "db_mycat_1": {"SELECT * FROM (`tbl_mycat`) JOIN `tbl_mycat_child` WHERE COUNT(1)=`tbl_mycat`.`id`"}, 609 }, 610 "slice-1": { 611 "db_mycat_2": {"SELECT * FROM (`tbl_mycat`) JOIN `tbl_mycat_child` WHERE COUNT(1)=`tbl_mycat`.`id`"}, 612 "db_mycat_3": {"SELECT * FROM (`tbl_mycat`) JOIN `tbl_mycat_child` WHERE COUNT(1)=`tbl_mycat`.`id`"}, 613 }, 614 }, 615 }, 616 { 617 db: "db_mycat", 618 sql: "select * from tbl_mycat, tbl_mycat_child where 1 = tbl_mycat.id", 619 sqls: map[string]map[string][]string{ 620 "slice-0": { 621 "db_mycat_1": {"SELECT * FROM (`tbl_mycat`) JOIN `tbl_mycat_child` WHERE 1=`tbl_mycat`.`id`"}, 622 }, 623 }, 624 }, 625 { 626 db: "db_mycat", 627 sql: "select * from tbl_mycat, tbl_mycat_child where 1 = a.id", 628 hasErr: true, 629 }, 630 } 631 632 for _, test := range tests { 633 t.Run(test.sql, getTestFunc(ns, test)) 634 } 635 } 636 637 func TestMycatSelectJoinUsing(t *testing.T) { 638 ns, err := preparePlanInfo() 639 if err != nil { 640 t.Fatalf("prepare namespace error: %v", err) 641 } 642 643 tests := []SQLTestcase{ 644 { 645 db: "db_mycat", 646 sql: "select * from tbl_mycat join tbl_mycat_child using(id)", 647 sqls: map[string]map[string][]string{ 648 "slice-0": { 649 "db_mycat_0": {"SELECT * FROM `tbl_mycat` JOIN `tbl_mycat_child` USING (`id`)"}, 650 "db_mycat_1": {"SELECT * FROM `tbl_mycat` JOIN `tbl_mycat_child` USING (`id`)"}, 651 }, 652 "slice-1": { 653 "db_mycat_2": {"SELECT * FROM `tbl_mycat` JOIN `tbl_mycat_child` USING (`id`)"}, 654 "db_mycat_3": {"SELECT * FROM `tbl_mycat` JOIN `tbl_mycat_child` USING (`id`)"}, 655 }, 656 }, 657 }, 658 { 659 db: "db_mycat", 660 sql: "select * from tbl_mycat join tbl_mycat_child using(tbl_mycat.id)", 661 hasErr: true, 662 }, 663 { 664 db: "db_mycat", 665 sql: "select * from tbl_mycat join tbl_mycat_child using(db_mycat.tbl_mycat.id)", 666 hasErr: true, 667 }, 668 } 669 670 for _, test := range tests { 671 t.Run(test.sql, getTestFunc(ns, test)) 672 } 673 } 674 675 func TestMycatSelectMultiTablesAlias(t *testing.T) { 676 ns, err := preparePlanInfo() 677 if err != nil { 678 t.Fatalf("prepare namespace error: %v", err) 679 } 680 681 tests := []SQLTestcase{ 682 { 683 db: "db_mycat", 684 sql: "select * from tbl_mycat as a, tbl_mycat_child where a.id = 1", 685 sqls: map[string]map[string][]string{ 686 "slice-0": { 687 "db_mycat_1": {"SELECT * FROM (`tbl_mycat` AS `a`) JOIN `tbl_mycat_child` WHERE `a`.`id`=1"}, 688 }, 689 }, 690 }, 691 { 692 db: "db_mycat", 693 sql: "select * from tbl_mycat join tbl_mycat_child as b on b.id = 1", 694 sqls: map[string]map[string][]string{ 695 "slice-0": { 696 "db_mycat_1": {"SELECT * FROM `tbl_mycat` JOIN `tbl_mycat_child` AS `b` ON `b`.`id`=1"}, 697 }, 698 }, 699 }, 700 { 701 db: "db_mycat", 702 sql: "select * from tbl_mycat as a join tbl_mycat_child as a on a.id = 1", 703 hasErr: true, 704 }, 705 { 706 db: "db_mycat", 707 sql: "select * from tbl_mycat as a join tbl_mycat_child on b.id = 1", 708 hasErr: true, 709 }, 710 } 711 712 for _, test := range tests { 713 t.Run(test.sql, getTestFunc(ns, test)) 714 } 715 } 716 717 func TestKingshardSelectAlias(t *testing.T) { 718 ns, err := preparePlanInfo() 719 if err != nil { 720 t.Fatalf("prepare namespace error: %v", err) 721 } 722 723 tests := []SQLTestcase{ 724 { 725 db: "db_ks", 726 sql: "select a.ss, a from tbl_ks as a where a.id = 1", 727 sqls: map[string]map[string][]string{ 728 "slice-0": { 729 "db_ks": { 730 "SELECT `a`.`ss`,`a` FROM `tbl_ks_0001` AS `a` WHERE `a`.`id`=1", 731 }, 732 }, 733 }, 734 }, 735 } 736 737 for _, test := range tests { 738 t.Run(test.sql, getTestFunc(ns, test)) 739 } 740 } 741 742 func TestKingshardSelectBetweenAlias(t *testing.T) { 743 ns, err := preparePlanInfo() 744 if err != nil { 745 t.Fatalf("prepare namespace error: %v", err) 746 } 747 748 tests := []SQLTestcase{ 749 { 750 db: "db_ks", 751 sql: "select name from tbl_ks as a where a.id between 10 and 100", 752 sqls: map[string]map[string][]string{ 753 "slice-0": { 754 "db_ks": { 755 "SELECT `name` FROM `tbl_ks_0000` AS `a` WHERE `a`.`id` BETWEEN 10 AND 100", 756 "SELECT `name` FROM `tbl_ks_0001` AS `a` WHERE `a`.`id` BETWEEN 10 AND 100", 757 }, 758 }, 759 "slice-1": { 760 "db_ks": { 761 "SELECT `name` FROM `tbl_ks_0002` AS `a` WHERE `a`.`id` BETWEEN 10 AND 100", 762 "SELECT `name` FROM `tbl_ks_0003` AS `a` WHERE `a`.`id` BETWEEN 10 AND 100", 763 }, 764 }, 765 }, 766 }, 767 } 768 769 for _, test := range tests { 770 t.Run(test.sql, getTestFunc(ns, test)) 771 } 772 } 773 774 func TestSelectColumnCaseInsensitive(t *testing.T) { 775 ns, err := preparePlanInfo() 776 if err != nil { 777 t.Fatalf("prepare namespace error: %v", err) 778 } 779 780 tests := []SQLTestcase{ 781 { 782 db: "db_ks", 783 sql: "select a.ss, a from tbl_ks as a where a.ID = 1", 784 sqls: map[string]map[string][]string{ 785 "slice-0": { 786 "db_ks": { 787 "SELECT `a`.`ss`,`a` FROM `tbl_ks_0001` AS `a` WHERE `a`.`ID`=1", 788 }, 789 }, 790 }, 791 }, 792 { 793 db: "db_ks", 794 sql: "select a.ss, a from tbl_ks as a where 1 = a.ID", 795 sqls: map[string]map[string][]string{ 796 "slice-0": { 797 "db_ks": { 798 "SELECT `a`.`ss`,`a` FROM `tbl_ks_0001` AS `a` WHERE 1=`a`.`ID`", 799 }, 800 }, 801 }, 802 }, 803 { 804 db: "db_ks", 805 sql: "select * from tbl_ks_day where CREATE_TIME between '2014-09-05 00:00:00' and '2014-09-07 00:00:00'", // 2014-09-01 00:00:00 806 sqls: map[string]map[string][]string{ 807 "slice-0": { 808 "db_ks": { 809 "SELECT * FROM `tbl_ks_day_20140905` WHERE `CREATE_TIME` BETWEEN '2014-09-05 00:00:00' AND '2014-09-07 00:00:00'", 810 }, 811 }, 812 "slice-1": { 813 "db_ks": { 814 "SELECT * FROM `tbl_ks_day_20140907` WHERE `CREATE_TIME` BETWEEN '2014-09-05 00:00:00' AND '2014-09-07 00:00:00'", 815 }, 816 }, 817 }, 818 }, 819 { 820 db: "db_mycat", 821 sql: "select * from tbl_mycat where ID in (0,2)", 822 sqls: map[string]map[string][]string{ 823 "slice-0": { 824 "db_mycat_0": {"SELECT * FROM `tbl_mycat` WHERE `ID` IN (0)"}, 825 }, 826 "slice-1": { 827 "db_mycat_2": {"SELECT * FROM `tbl_mycat` WHERE `ID` IN (2)"}, 828 }, 829 }, 830 }, 831 } 832 833 for _, test := range tests { 834 t.Run(test.sql, getTestFunc(ns, test)) 835 } 836 } 837 838 func TestSelectTableNameCaseInsensitive(t *testing.T) { 839 ns, err := preparePlanInfo() 840 if err != nil { 841 t.Fatalf("prepare namespace error: %v", err) 842 } 843 844 tests := []SQLTestcase{ 845 { 846 db: "db_ks", 847 sql: "select a.ss, a from TBL_KS_UPPERCASE as a where a.id = 1", 848 sqls: map[string]map[string][]string{ 849 "slice-0": { 850 "db_ks": { 851 "SELECT `a`.`ss`,`a` FROM `TBL_KS_UPPERCASE_0001` AS `a` WHERE `a`.`id`=1", 852 }, 853 }, 854 }, 855 }, 856 { 857 db: "db_ks", 858 sql: "select ss, a from TBL_KS_UPPERCASE where id = 1", 859 sqls: map[string]map[string][]string{ 860 "slice-0": { 861 "db_ks": { 862 "SELECT `ss`,`a` FROM `TBL_KS_UPPERCASE_0001` WHERE `id`=1", 863 }, 864 }, 865 }, 866 }, 867 { 868 db: "db_ks", 869 sql: "select a.ss, a from tbl_ks_uppercase as a where a.id = 1", 870 sqls: map[string]map[string][]string{ 871 "slice-0": { 872 "db_ks": { 873 "SELECT `a`.`ss`,`a` FROM `tbl_ks_uppercase_0001` AS `a` WHERE `a`.`id`=1", 874 }, 875 }, 876 }, 877 }, 878 { 879 db: "db_ks", 880 sql: "select a.ss, a from tbl_ks_uppercase_child as a where a.id = 1", 881 sqls: map[string]map[string][]string{ 882 "slice-0": { 883 "db_ks": { 884 "SELECT `a`.`ss`,`a` FROM `tbl_ks_uppercase_child_0001` AS `a` WHERE `a`.`id`=1", 885 }, 886 }, 887 }, 888 }, 889 { 890 db: "db_mycat", 891 sql: "select * from TBL_MYCAT where TBL_MYCAT.ID in (0,2)", 892 sqls: map[string]map[string][]string{ 893 "slice-0": { 894 "db_mycat_0": {"SELECT * FROM `TBL_MYCAT` WHERE `TBL_MYCAT`.`ID` IN (0)"}, 895 }, 896 "slice-1": { 897 "db_mycat_2": {"SELECT * FROM `TBL_MYCAT` WHERE `TBL_MYCAT`.`ID` IN (2)"}, 898 }, 899 }, 900 }, 901 { 902 db: "db_mycat", 903 sql: "select * from tbl_mycat as A where A.ID in (0,2)", 904 sqls: map[string]map[string][]string{ 905 "slice-0": { 906 "db_mycat_0": {"SELECT * FROM `tbl_mycat` AS `A` WHERE `A`.`ID` IN (0)"}, 907 }, 908 "slice-1": { 909 "db_mycat_2": {"SELECT * FROM `tbl_mycat` AS `A` WHERE `A`.`ID` IN (2)"}, 910 }, 911 }, 912 }, 913 { 914 db: "db_mycat", 915 sql: "select * from (select id, ss from tbl_mycat) A where A.ID in (0,2)", 916 sqls: map[string]map[string][]string{ 917 "slice-0": { 918 "db_mycat_0": {"SELECT * FROM (SELECT `id`,`ss` FROM (`tbl_mycat`)) AS `A` WHERE `A`.`ID` IN (0)"}, 919 }, 920 "slice-1": { 921 "db_mycat_2": {"SELECT * FROM (SELECT `id`,`ss` FROM (`tbl_mycat`)) AS `A` WHERE `A`.`ID` IN (2)"}, 922 }, 923 }, 924 }, 925 } 926 927 for _, test := range tests { 928 t.Run(test.sql, getTestFunc(ns, test)) 929 } 930 } 931 932 // TODO: range shard 933 func TestMycatSelectBinaryOperatorComparison(t *testing.T) { 934 ns, err := preparePlanInfo() 935 if err != nil { 936 t.Fatalf("prepare namespace error: %v", err) 937 } 938 939 tests := []SQLTestcase{ 940 { 941 db: "db_mycat", 942 sql: "select * from tbl_mycat where id = 1", 943 sqls: map[string]map[string][]string{ 944 "slice-0": { 945 "db_mycat_1": {"SELECT * FROM `tbl_mycat` WHERE `id`=1"}, 946 }, 947 }, 948 }, 949 { 950 db: "db_mycat", 951 sql: "select * from tbl_mycat where 1 = id", 952 sqls: map[string]map[string][]string{ 953 "slice-0": { 954 "db_mycat_1": {"SELECT * FROM `tbl_mycat` WHERE 1=`id`"}, 955 }, 956 }, 957 }, 958 { 959 db: "db_mycat", 960 sql: "select * from tbl_mycat where id > 1", 961 sqls: map[string]map[string][]string{ 962 "slice-0": { 963 "db_mycat_0": {"SELECT * FROM `tbl_mycat` WHERE `id`>1"}, 964 "db_mycat_1": {"SELECT * FROM `tbl_mycat` WHERE `id`>1"}, 965 }, 966 "slice-1": { 967 "db_mycat_2": {"SELECT * FROM `tbl_mycat` WHERE `id`>1"}, 968 "db_mycat_3": {"SELECT * FROM `tbl_mycat` WHERE `id`>1"}, 969 }, 970 }, 971 }, 972 { 973 db: "db_mycat", 974 sql: "select * from tbl_mycat where 1 < id", 975 sqls: map[string]map[string][]string{ 976 "slice-0": { 977 "db_mycat_0": {"SELECT * FROM `tbl_mycat` WHERE 1<`id`"}, 978 "db_mycat_1": {"SELECT * FROM `tbl_mycat` WHERE 1<`id`"}, 979 }, 980 "slice-1": { 981 "db_mycat_2": {"SELECT * FROM `tbl_mycat` WHERE 1<`id`"}, 982 "db_mycat_3": {"SELECT * FROM `tbl_mycat` WHERE 1<`id`"}, 983 }, 984 }, 985 }, 986 { 987 db: "db_mycat", 988 sql: "select * from tbl_mycat where id >= 1", 989 sqls: map[string]map[string][]string{ 990 "slice-0": { 991 "db_mycat_0": {"SELECT * FROM `tbl_mycat` WHERE `id`>=1"}, 992 "db_mycat_1": {"SELECT * FROM `tbl_mycat` WHERE `id`>=1"}, 993 }, 994 "slice-1": { 995 "db_mycat_2": {"SELECT * FROM `tbl_mycat` WHERE `id`>=1"}, 996 "db_mycat_3": {"SELECT * FROM `tbl_mycat` WHERE `id`>=1"}, 997 }, 998 }, 999 }, 1000 { 1001 db: "db_mycat", 1002 sql: "select * from tbl_mycat where 1 <= id", 1003 sqls: map[string]map[string][]string{ 1004 "slice-0": { 1005 "db_mycat_0": {"SELECT * FROM `tbl_mycat` WHERE 1<=`id`"}, 1006 "db_mycat_1": {"SELECT * FROM `tbl_mycat` WHERE 1<=`id`"}, 1007 }, 1008 "slice-1": { 1009 "db_mycat_2": {"SELECT * FROM `tbl_mycat` WHERE 1<=`id`"}, 1010 "db_mycat_3": {"SELECT * FROM `tbl_mycat` WHERE 1<=`id`"}, 1011 }, 1012 }, 1013 }, 1014 { 1015 db: "db_mycat", 1016 sql: "select * from tbl_mycat where id < 1", 1017 sqls: map[string]map[string][]string{ 1018 "slice-0": { 1019 "db_mycat_0": {"SELECT * FROM `tbl_mycat` WHERE `id`<1"}, 1020 "db_mycat_1": {"SELECT * FROM `tbl_mycat` WHERE `id`<1"}, 1021 }, 1022 "slice-1": { 1023 "db_mycat_2": {"SELECT * FROM `tbl_mycat` WHERE `id`<1"}, 1024 "db_mycat_3": {"SELECT * FROM `tbl_mycat` WHERE `id`<1"}, 1025 }, 1026 }, 1027 }, 1028 { 1029 db: "db_mycat", 1030 sql: "select * from tbl_mycat where 1 > id", 1031 sqls: map[string]map[string][]string{ 1032 "slice-0": { 1033 "db_mycat_0": {"SELECT * FROM `tbl_mycat` WHERE 1>`id`"}, 1034 "db_mycat_1": {"SELECT * FROM `tbl_mycat` WHERE 1>`id`"}, 1035 }, 1036 "slice-1": { 1037 "db_mycat_2": {"SELECT * FROM `tbl_mycat` WHERE 1>`id`"}, 1038 "db_mycat_3": {"SELECT * FROM `tbl_mycat` WHERE 1>`id`"}, 1039 }, 1040 }, 1041 }, 1042 { 1043 db: "db_mycat", 1044 sql: "select * from tbl_mycat where id <= 1", 1045 sqls: map[string]map[string][]string{ 1046 "slice-0": { 1047 "db_mycat_0": {"SELECT * FROM `tbl_mycat` WHERE `id`<=1"}, 1048 "db_mycat_1": {"SELECT * FROM `tbl_mycat` WHERE `id`<=1"}, 1049 }, 1050 "slice-1": { 1051 "db_mycat_2": {"SELECT * FROM `tbl_mycat` WHERE `id`<=1"}, 1052 "db_mycat_3": {"SELECT * FROM `tbl_mycat` WHERE `id`<=1"}, 1053 }, 1054 }, 1055 }, 1056 { 1057 db: "db_mycat", 1058 sql: "select * from tbl_mycat where 1 >= id", 1059 sqls: map[string]map[string][]string{ 1060 "slice-0": { 1061 "db_mycat_0": {"SELECT * FROM `tbl_mycat` WHERE 1>=`id`"}, 1062 "db_mycat_1": {"SELECT * FROM `tbl_mycat` WHERE 1>=`id`"}, 1063 }, 1064 "slice-1": { 1065 "db_mycat_2": {"SELECT * FROM `tbl_mycat` WHERE 1>=`id`"}, 1066 "db_mycat_3": {"SELECT * FROM `tbl_mycat` WHERE 1>=`id`"}, 1067 }, 1068 }, 1069 }, 1070 { 1071 db: "db_mycat", 1072 sql: "select * from tbl_mycat where id <> 1", 1073 sqls: map[string]map[string][]string{ 1074 "slice-0": { 1075 "db_mycat_0": {"SELECT * FROM `tbl_mycat` WHERE `id`!=1"}, 1076 "db_mycat_1": {"SELECT * FROM `tbl_mycat` WHERE `id`!=1"}, 1077 }, 1078 "slice-1": { 1079 "db_mycat_2": {"SELECT * FROM `tbl_mycat` WHERE `id`!=1"}, 1080 "db_mycat_3": {"SELECT * FROM `tbl_mycat` WHERE `id`!=1"}, 1081 }, 1082 }, 1083 }, 1084 { 1085 db: "db_mycat", 1086 sql: "select * from tbl_mycat where id != 1", 1087 sqls: map[string]map[string][]string{ 1088 "slice-0": { 1089 "db_mycat_0": {"SELECT * FROM `tbl_mycat` WHERE `id`!=1"}, 1090 "db_mycat_1": {"SELECT * FROM `tbl_mycat` WHERE `id`!=1"}, 1091 }, 1092 "slice-1": { 1093 "db_mycat_2": {"SELECT * FROM `tbl_mycat` WHERE `id`!=1"}, 1094 "db_mycat_3": {"SELECT * FROM `tbl_mycat` WHERE `id`!=1"}, 1095 }, 1096 }, 1097 }, 1098 1099 { 1100 db: "db_mycat", 1101 sql: "select * from tbl_mycat, tbl_mycat_child where tbl_mycat.id = tbl_mycat_child.id", 1102 sqls: map[string]map[string][]string{ 1103 "slice-0": { 1104 "db_mycat_0": {"SELECT * FROM (`tbl_mycat`) JOIN `tbl_mycat_child` WHERE `tbl_mycat`.`id`=`tbl_mycat_child`.`id`"}, 1105 "db_mycat_1": {"SELECT * FROM (`tbl_mycat`) JOIN `tbl_mycat_child` WHERE `tbl_mycat`.`id`=`tbl_mycat_child`.`id`"}, 1106 }, 1107 "slice-1": { 1108 "db_mycat_2": {"SELECT * FROM (`tbl_mycat`) JOIN `tbl_mycat_child` WHERE `tbl_mycat`.`id`=`tbl_mycat_child`.`id`"}, 1109 "db_mycat_3": {"SELECT * FROM (`tbl_mycat`) JOIN `tbl_mycat_child` WHERE `tbl_mycat`.`id`=`tbl_mycat_child`.`id`"}, 1110 }, 1111 }, 1112 }, 1113 } 1114 1115 for _, test := range tests { 1116 t.Run(test.sql, getTestFunc(ns, test)) 1117 } 1118 } 1119 1120 func TestMycatSelectPatternIn(t *testing.T) { 1121 ns, err := preparePlanInfo() 1122 if err != nil { 1123 t.Fatalf("prepare namespace error: %v", err) 1124 } 1125 1126 tests := []SQLTestcase{ 1127 { 1128 db: "db_mycat", 1129 sql: "select * from tbl_mycat where id in (0,1,2,3,4,5,6,7)", 1130 sqls: map[string]map[string][]string{ 1131 "slice-0": { 1132 "db_mycat_0": {"SELECT * FROM `tbl_mycat` WHERE `id` IN (0,4)"}, 1133 "db_mycat_1": {"SELECT * FROM `tbl_mycat` WHERE `id` IN (1,5)"}, 1134 }, 1135 "slice-1": { 1136 "db_mycat_2": {"SELECT * FROM `tbl_mycat` WHERE `id` IN (2,6)"}, 1137 "db_mycat_3": {"SELECT * FROM `tbl_mycat` WHERE `id` IN (3,7)"}, 1138 }, 1139 }, 1140 }, 1141 { 1142 db: "db_mycat", 1143 sql: "select * from tbl_mycat where id in (0,0,0,1)", 1144 sqls: map[string]map[string][]string{ 1145 "slice-0": { 1146 "db_mycat_0": {"SELECT * FROM `tbl_mycat` WHERE `id` IN (0,0,0)"}, 1147 "db_mycat_1": {"SELECT * FROM `tbl_mycat` WHERE `id` IN (1)"}, 1148 }, 1149 }, 1150 }, 1151 { 1152 db: "db_mycat", 1153 sql: "select * from tbl_mycat where id not in (1)", 1154 sqls: map[string]map[string][]string{ 1155 "slice-0": { 1156 "db_mycat_0": {"SELECT * FROM `tbl_mycat` WHERE `id` NOT IN (1)"}, 1157 "db_mycat_1": {"SELECT * FROM `tbl_mycat` WHERE `id` NOT IN (1)"}, 1158 }, 1159 "slice-1": { 1160 "db_mycat_2": {"SELECT * FROM `tbl_mycat` WHERE `id` NOT IN (1)"}, 1161 "db_mycat_3": {"SELECT * FROM `tbl_mycat` WHERE `id` NOT IN (1)"}, 1162 }, 1163 }, 1164 }, 1165 { 1166 db: "db_mycat", 1167 sql: "select * from tbl_mycat where 1 in (1,2,3) and id=1", 1168 sqls: map[string]map[string][]string{ 1169 "slice-0": { 1170 "db_mycat_1": {"SELECT * FROM `tbl_mycat` WHERE 1 IN (1,2,3) AND `id`=1"}, 1171 }, 1172 }, 1173 }, 1174 { 1175 db: "db_mycat", 1176 sql: "select * from tbl_mycat where 1 in (1,2,3) or id=1", 1177 sqls: map[string]map[string][]string{ 1178 "slice-0": { 1179 "db_mycat_0": {"SELECT * FROM `tbl_mycat` WHERE 1 IN (1,2,3) OR `id`=1"}, 1180 "db_mycat_1": {"SELECT * FROM `tbl_mycat` WHERE 1 IN (1,2,3) OR `id`=1"}, 1181 }, 1182 "slice-1": { 1183 "db_mycat_2": {"SELECT * FROM `tbl_mycat` WHERE 1 IN (1,2,3) OR `id`=1"}, 1184 "db_mycat_3": {"SELECT * FROM `tbl_mycat` WHERE 1 IN (1,2,3) OR `id`=1"}, 1185 }, 1186 }, 1187 }, 1188 { 1189 db: "db_mycat", 1190 sql: "select * from tbl_mycat where id in (1 = 1)", 1191 hasErr: true, 1192 }, 1193 } 1194 1195 for _, test := range tests { 1196 t.Run(test.sql, getTestFunc(ns, test)) 1197 } 1198 } 1199 1200 func TestMycatSelectPatternInWithFuncDatabase(t *testing.T) { 1201 ns, err := preparePlanInfo() 1202 if err != nil { 1203 t.Fatalf("prepare namespace error: %v", err) 1204 } 1205 1206 tests := []SQLTestcase{ 1207 { 1208 db: "db_mycat", 1209 sql: "select * from tbl_mycat where database() in ('db_mycat_0', 'db_mycat_1') and id = 1", 1210 sqls: map[string]map[string][]string{ 1211 "slice-0": { 1212 "db_mycat_1": {"SELECT * FROM `tbl_mycat` WHERE DATABASE() IN ('db_mycat_0','db_mycat_1') AND `id`=1"}, 1213 }, 1214 }, 1215 }, 1216 { 1217 db: "db_mycat", 1218 sql: "select * from tbl_mycat where database() not in ('db_mycat_0', 'db_mycat_1') and id = 1", 1219 sqls: map[string]map[string][]string{ 1220 "slice-0": { 1221 "db_mycat_1": {"SELECT * FROM `tbl_mycat` WHERE DATABASE() NOT IN ('db_mycat_0','db_mycat_1') AND `id`=1"}, 1222 }, 1223 }, 1224 }, 1225 { 1226 db: "db_mycat", 1227 sql: "select * from tbl_mycat where database() not in ('db_mycat_0', 'db_mycat_1') or id = 1", 1228 sqls: map[string]map[string][]string{ 1229 "slice-0": { 1230 "db_mycat_0": {"SELECT * FROM `tbl_mycat` WHERE DATABASE() NOT IN ('db_mycat_0','db_mycat_1') OR `id`=1"}, 1231 "db_mycat_1": {"SELECT * FROM `tbl_mycat` WHERE DATABASE() NOT IN ('db_mycat_0','db_mycat_1') OR `id`=1"}, 1232 }, 1233 "slice-1": { 1234 "db_mycat_2": {"SELECT * FROM `tbl_mycat` WHERE DATABASE() NOT IN ('db_mycat_0','db_mycat_1') OR `id`=1"}, 1235 "db_mycat_3": {"SELECT * FROM `tbl_mycat` WHERE DATABASE() NOT IN ('db_mycat_0','db_mycat_1') OR `id`=1"}, 1236 }, 1237 }, 1238 }, 1239 { 1240 db: "db_mycat", 1241 sql: "select * from tbl_mycat where database() in ('db_mycat_0', 'db_mycat_1')", 1242 sqls: map[string]map[string][]string{ 1243 "slice-0": { 1244 "db_mycat_0": {"SELECT * FROM `tbl_mycat` WHERE DATABASE() IN ('db_mycat_0','db_mycat_1')"}, 1245 "db_mycat_1": {"SELECT * FROM `tbl_mycat` WHERE DATABASE() IN ('db_mycat_0','db_mycat_1')"}, 1246 }, 1247 "slice-1": { 1248 "db_mycat_2": {"SELECT * FROM `tbl_mycat` WHERE DATABASE() IN ('db_mycat_0','db_mycat_1')"}, 1249 "db_mycat_3": {"SELECT * FROM `tbl_mycat` WHERE DATABASE() IN ('db_mycat_0','db_mycat_1')"}, 1250 }, 1251 }, 1252 }, 1253 { 1254 db: "db_mycat", 1255 sql: "select * from tbl_mycat where database() not in ('db_mycat_0', 'db_mycat_1')", 1256 sqls: map[string]map[string][]string{ 1257 "slice-0": { 1258 "db_mycat_0": {"SELECT * FROM `tbl_mycat` WHERE DATABASE() NOT IN ('db_mycat_0','db_mycat_1')"}, 1259 "db_mycat_1": {"SELECT * FROM `tbl_mycat` WHERE DATABASE() NOT IN ('db_mycat_0','db_mycat_1')"}, 1260 }, 1261 "slice-1": { 1262 "db_mycat_2": {"SELECT * FROM `tbl_mycat` WHERE DATABASE() NOT IN ('db_mycat_0','db_mycat_1')"}, 1263 "db_mycat_3": {"SELECT * FROM `tbl_mycat` WHERE DATABASE() NOT IN ('db_mycat_0','db_mycat_1')"}, 1264 }, 1265 }, 1266 }, 1267 { 1268 db: "db_mycat", 1269 sql: "select * from tbl_mycat where database() in ('db_mycat_0', 'db_mycat_1') and id in (0,1,2,3,4,5,6,7)", 1270 sqls: map[string]map[string][]string{ 1271 "slice-0": { 1272 "db_mycat_0": {"SELECT * FROM `tbl_mycat` WHERE DATABASE() IN ('db_mycat_0','db_mycat_1') AND `id` IN (0,4)"}, 1273 "db_mycat_1": {"SELECT * FROM `tbl_mycat` WHERE DATABASE() IN ('db_mycat_0','db_mycat_1') AND `id` IN (1,5)"}, 1274 }, 1275 "slice-1": { 1276 "db_mycat_2": {"SELECT * FROM `tbl_mycat` WHERE DATABASE() IN ('db_mycat_0','db_mycat_1') AND `id` IN (2,6)"}, 1277 "db_mycat_3": {"SELECT * FROM `tbl_mycat` WHERE DATABASE() IN ('db_mycat_0','db_mycat_1') AND `id` IN (3,7)"}, 1278 }, 1279 }, 1280 }, 1281 } 1282 1283 for _, test := range tests { 1284 t.Run(test.sql, getTestFunc(ns, test)) 1285 } 1286 } 1287 1288 // TODO: range shard 1289 func TestMycatSelectPatternBetween(t *testing.T) { 1290 ns, err := preparePlanInfo() 1291 if err != nil { 1292 t.Fatalf("prepare namespace error: %v", err) 1293 } 1294 1295 tests := []SQLTestcase{ 1296 { 1297 db: "db_mycat", 1298 sql: "select * from tbl_mycat where id between 1 and 5", 1299 sqls: map[string]map[string][]string{ 1300 "slice-0": { 1301 "db_mycat_0": {"SELECT * FROM `tbl_mycat` WHERE `id` BETWEEN 1 AND 5"}, 1302 "db_mycat_1": {"SELECT * FROM `tbl_mycat` WHERE `id` BETWEEN 1 AND 5"}, 1303 }, 1304 "slice-1": { 1305 "db_mycat_2": {"SELECT * FROM `tbl_mycat` WHERE `id` BETWEEN 1 AND 5"}, 1306 "db_mycat_3": {"SELECT * FROM `tbl_mycat` WHERE `id` BETWEEN 1 AND 5"}, 1307 }, 1308 }, 1309 }, 1310 { 1311 db: "db_mycat", 1312 sql: "select * from tbl_mycat where id between 5 and 1", 1313 sqls: map[string]map[string][]string{ 1314 "slice-0": { 1315 "db_mycat_0": {"SELECT * FROM `tbl_mycat` WHERE `id` BETWEEN 5 AND 1"}, 1316 "db_mycat_1": {"SELECT * FROM `tbl_mycat` WHERE `id` BETWEEN 5 AND 1"}, 1317 }, 1318 "slice-1": { 1319 "db_mycat_2": {"SELECT * FROM `tbl_mycat` WHERE `id` BETWEEN 5 AND 1"}, 1320 "db_mycat_3": {"SELECT * FROM `tbl_mycat` WHERE `id` BETWEEN 5 AND 1"}, 1321 }, 1322 }, 1323 }, 1324 { 1325 db: "db_mycat", 1326 sql: "select * from tbl_mycat where user between 'curry' and 'durant'", 1327 sqls: map[string]map[string][]string{ 1328 "slice-0": { 1329 "db_mycat_0": {"SELECT * FROM `tbl_mycat` WHERE `user` BETWEEN 'curry' AND 'durant'"}, 1330 "db_mycat_1": {"SELECT * FROM `tbl_mycat` WHERE `user` BETWEEN 'curry' AND 'durant'"}, 1331 }, 1332 "slice-1": { 1333 "db_mycat_2": {"SELECT * FROM `tbl_mycat` WHERE `user` BETWEEN 'curry' AND 'durant'"}, 1334 "db_mycat_3": {"SELECT * FROM `tbl_mycat` WHERE `user` BETWEEN 'curry' AND 'durant'"}, 1335 }, 1336 }, 1337 }, 1338 { 1339 db: "db_mycat", 1340 sql: "select * from tbl_mycat where 1 between 2 and 3", 1341 sqls: map[string]map[string][]string{ 1342 "slice-0": { 1343 "db_mycat_0": {"SELECT * FROM `tbl_mycat` WHERE 1 BETWEEN 2 AND 3"}, 1344 "db_mycat_1": {"SELECT * FROM `tbl_mycat` WHERE 1 BETWEEN 2 AND 3"}, 1345 }, 1346 "slice-1": { 1347 "db_mycat_2": {"SELECT * FROM `tbl_mycat` WHERE 1 BETWEEN 2 AND 3"}, 1348 "db_mycat_3": {"SELECT * FROM `tbl_mycat` WHERE 1 BETWEEN 2 AND 3"}, 1349 }, 1350 }, 1351 }, 1352 { 1353 db: "db_mycat", 1354 sql: "select * from tbl_mycat where 1 not between 2 and 3", 1355 sqls: map[string]map[string][]string{ 1356 "slice-0": { 1357 "db_mycat_0": {"SELECT * FROM `tbl_mycat` WHERE 1 NOT BETWEEN 2 AND 3"}, 1358 "db_mycat_1": {"SELECT * FROM `tbl_mycat` WHERE 1 NOT BETWEEN 2 AND 3"}, 1359 }, 1360 "slice-1": { 1361 "db_mycat_2": {"SELECT * FROM `tbl_mycat` WHERE 1 NOT BETWEEN 2 AND 3"}, 1362 "db_mycat_3": {"SELECT * FROM `tbl_mycat` WHERE 1 NOT BETWEEN 2 AND 3"}, 1363 }, 1364 }, 1365 }, 1366 } 1367 1368 for _, test := range tests { 1369 t.Run(test.sql, getTestFunc(ns, test)) 1370 } 1371 } 1372 1373 func TestMycatSelectPatternBetweenWithFuncDatabase(t *testing.T) { 1374 ns, err := preparePlanInfo() 1375 if err != nil { 1376 t.Fatalf("prepare namespace error: %v", err) 1377 } 1378 1379 tests := []SQLTestcase{ 1380 { 1381 db: "db_mycat", 1382 sql: "select * from tbl_mycat where database() between 'db_mycat_0' and 'db_mycat_2'", 1383 sqls: map[string]map[string][]string{ 1384 "slice-0": { 1385 "db_mycat_0": {"SELECT * FROM `tbl_mycat` WHERE DATABASE() BETWEEN 'db_mycat_0' AND 'db_mycat_2'"}, 1386 "db_mycat_1": {"SELECT * FROM `tbl_mycat` WHERE DATABASE() BETWEEN 'db_mycat_0' AND 'db_mycat_2'"}, 1387 }, 1388 "slice-1": { 1389 "db_mycat_2": {"SELECT * FROM `tbl_mycat` WHERE DATABASE() BETWEEN 'db_mycat_0' AND 'db_mycat_2'"}, 1390 "db_mycat_3": {"SELECT * FROM `tbl_mycat` WHERE DATABASE() BETWEEN 'db_mycat_0' AND 'db_mycat_2'"}, 1391 }, 1392 }, 1393 }, 1394 } 1395 1396 for _, test := range tests { 1397 t.Run(test.sql, getTestFunc(ns, test)) 1398 } 1399 } 1400 1401 func TestMycatSelectPatternLike(t *testing.T) { 1402 ns, err := preparePlanInfo() 1403 if err != nil { 1404 t.Fatalf("prepare namespace error: %v", err) 1405 } 1406 1407 tests := []SQLTestcase{ 1408 { 1409 db: "db_mycat", 1410 sql: "select * from tbl_mycat where id like '1%'", 1411 sqls: map[string]map[string][]string{ 1412 "slice-0": { 1413 "db_mycat_0": {"SELECT * FROM `tbl_mycat` WHERE `id` LIKE '1%'"}, 1414 "db_mycat_1": {"SELECT * FROM `tbl_mycat` WHERE `id` LIKE '1%'"}, 1415 }, 1416 "slice-1": { 1417 "db_mycat_2": {"SELECT * FROM `tbl_mycat` WHERE `id` LIKE '1%'"}, 1418 "db_mycat_3": {"SELECT * FROM `tbl_mycat` WHERE `id` LIKE '1%'"}, 1419 }, 1420 }, 1421 }, 1422 } 1423 1424 for _, test := range tests { 1425 t.Run(test.sql, getTestFunc(ns, test)) 1426 } 1427 } 1428 1429 func TestMycatSelectPatternLogicOperator(t *testing.T) { 1430 ns, err := preparePlanInfo() 1431 if err != nil { 1432 t.Fatalf("prepare namespace error: %v", err) 1433 } 1434 1435 tests := []SQLTestcase{ 1436 { 1437 db: "db_mycat", 1438 sql: "select * from tbl_mycat where id = 0 or id in (1,2)", 1439 sqls: map[string]map[string][]string{ 1440 "slice-0": { 1441 "db_mycat_0": {"SELECT * FROM `tbl_mycat` WHERE `id`=0 OR `id` IN ()"}, 1442 "db_mycat_1": {"SELECT * FROM `tbl_mycat` WHERE `id`=0 OR `id` IN (1)"}, 1443 }, 1444 "slice-1": { 1445 "db_mycat_2": {"SELECT * FROM `tbl_mycat` WHERE `id`=0 OR `id` IN (2)"}, 1446 }, 1447 }, 1448 }, 1449 { 1450 db: "db_mycat", 1451 sql: "select * from tbl_mycat where id = 0 and user = 'curry'", 1452 sqls: map[string]map[string][]string{ 1453 "slice-0": { 1454 "db_mycat_0": {"SELECT * FROM `tbl_mycat` WHERE `id`=0 AND `user`='curry'"}, 1455 }, 1456 }, 1457 }, 1458 { 1459 db: "db_mycat", 1460 sql: "select * from tbl_mycat where id = 0 or user = 'curry'", 1461 sqls: map[string]map[string][]string{ 1462 "slice-0": { 1463 "db_mycat_0": {"SELECT * FROM `tbl_mycat` WHERE `id`=0 OR `user`='curry'"}, 1464 "db_mycat_1": {"SELECT * FROM `tbl_mycat` WHERE `id`=0 OR `user`='curry'"}, 1465 }, 1466 "slice-1": { 1467 "db_mycat_2": {"SELECT * FROM `tbl_mycat` WHERE `id`=0 OR `user`='curry'"}, 1468 "db_mycat_3": {"SELECT * FROM `tbl_mycat` WHERE `id`=0 OR `user`='curry'"}, 1469 }, 1470 }, 1471 }, 1472 } 1473 1474 for _, test := range tests { 1475 t.Run(test.sql, getTestFunc(ns, test)) 1476 } 1477 } 1478 1479 // TODO: need more testcases 1480 func TestMycatSelectSubqueryInTableRefs(t *testing.T) { 1481 ns, err := preparePlanInfo() 1482 if err != nil { 1483 t.Fatalf("prepare namespace error: %v", err) 1484 } 1485 1486 tests := []SQLTestcase{ 1487 { 1488 db: "db_mycat", 1489 sql: "select id from (select user from tbl_mycat) as a", // table in subquery must be a sharding table 1490 sqls: map[string]map[string][]string{ 1491 "slice-0": { 1492 "db_mycat_0": {"SELECT `id` FROM (SELECT `user` FROM (`tbl_mycat`)) AS `a`"}, 1493 "db_mycat_1": {"SELECT `id` FROM (SELECT `user` FROM (`tbl_mycat`)) AS `a`"}, 1494 }, 1495 "slice-1": { 1496 "db_mycat_2": {"SELECT `id` FROM (SELECT `user` FROM (`tbl_mycat`)) AS `a`"}, 1497 "db_mycat_3": {"SELECT `id` FROM (SELECT `user` FROM (`tbl_mycat`)) AS `a`"}, 1498 }, 1499 }, 1500 }, 1501 { 1502 db: "db_mycat", 1503 sql: "select id from (select user from tbl_mycat_unknown) as a", //unshard plan 1504 sqls: map[string]map[string][]string{ 1505 "slice-0": { 1506 "db_mycat_0": {"SELECT `id` FROM (SELECT `user` FROM (`tbl_mycat_unknown`)) AS `a`"}, 1507 }, 1508 }, 1509 }, 1510 { 1511 db: "db_mycat", 1512 sql: "select id from tbl_mycat as a, (select user from tbl_mycat) as a", // table alias is ambiguous 1513 hasErr: true, 1514 }, 1515 } 1516 1517 for _, test := range tests { 1518 t.Run(test.sql, getTestFunc(ns, test)) 1519 } 1520 } 1521 1522 func TestMycatSelectAggregationFunctionCount(t *testing.T) { 1523 ns, err := preparePlanInfo() 1524 if err != nil { 1525 t.Fatalf("prepare namespace error: %v", err) 1526 } 1527 1528 tests := []SQLTestcase{ 1529 { 1530 db: "db_mycat", 1531 sql: "select count(*) from tbl_mycat", 1532 sqls: map[string]map[string][]string{ 1533 "slice-0": { 1534 "db_mycat_0": {"SELECT COUNT(1) FROM `tbl_mycat`"}, 1535 "db_mycat_1": {"SELECT COUNT(1) FROM `tbl_mycat`"}, 1536 }, 1537 "slice-1": { 1538 "db_mycat_2": {"SELECT COUNT(1) FROM `tbl_mycat`"}, 1539 "db_mycat_3": {"SELECT COUNT(1) FROM `tbl_mycat`"}, 1540 }, 1541 }, 1542 }, 1543 { 1544 db: "db_mycat", 1545 sql: "select count(id) from tbl_mycat", 1546 sqls: map[string]map[string][]string{ 1547 "slice-0": { 1548 "db_mycat_0": {"SELECT COUNT(`id`) FROM `tbl_mycat`"}, 1549 "db_mycat_1": {"SELECT COUNT(`id`) FROM `tbl_mycat`"}, 1550 }, 1551 "slice-1": { 1552 "db_mycat_2": {"SELECT COUNT(`id`) FROM `tbl_mycat`"}, 1553 "db_mycat_3": {"SELECT COUNT(`id`) FROM `tbl_mycat`"}, 1554 }, 1555 }, 1556 }, 1557 { 1558 db: "db_mycat", 1559 sql: "select count(user) from tbl_mycat where id = 1", 1560 sqls: map[string]map[string][]string{ 1561 "slice-0": { 1562 "db_mycat_1": {"SELECT COUNT(`user`) FROM `tbl_mycat` WHERE `id`=1"}, 1563 }, 1564 }, 1565 }, 1566 { 1567 db: "db_mycat", 1568 sql: "select count(user) from tbl_mycat where user = 'curry'", 1569 sqls: map[string]map[string][]string{ 1570 "slice-0": { 1571 "db_mycat_0": {"SELECT COUNT(`user`) FROM `tbl_mycat` WHERE `user`='curry'"}, 1572 "db_mycat_1": {"SELECT COUNT(`user`) FROM `tbl_mycat` WHERE `user`='curry'"}, 1573 }, 1574 "slice-1": { 1575 "db_mycat_2": {"SELECT COUNT(`user`) FROM `tbl_mycat` WHERE `user`='curry'"}, 1576 "db_mycat_3": {"SELECT COUNT(`user`) FROM `tbl_mycat` WHERE `user`='curry'"}, 1577 }, 1578 }, 1579 }, 1580 } 1581 1582 for _, test := range tests { 1583 t.Run(test.sql, getTestFunc(ns, test)) 1584 } 1585 } 1586 1587 func TestMycatSelectAggregationFunctionMax(t *testing.T) { 1588 ns, err := preparePlanInfo() 1589 if err != nil { 1590 t.Fatalf("prepare namespace error: %v", err) 1591 } 1592 1593 tests := []SQLTestcase{ 1594 { 1595 db: "db_mycat", 1596 sql: "select max(*) from tbl_mycat", 1597 hasErr: true, // max(*) is invalid syntax 1598 }, 1599 { 1600 db: "db_mycat", 1601 sql: "select max(id) from tbl_mycat", 1602 sqls: map[string]map[string][]string{ 1603 "slice-0": { 1604 "db_mycat_0": {"SELECT MAX(`id`) FROM `tbl_mycat`"}, 1605 "db_mycat_1": {"SELECT MAX(`id`) FROM `tbl_mycat`"}, 1606 }, 1607 "slice-1": { 1608 "db_mycat_2": {"SELECT MAX(`id`) FROM `tbl_mycat`"}, 1609 "db_mycat_3": {"SELECT MAX(`id`) FROM `tbl_mycat`"}, 1610 }, 1611 }, 1612 }, 1613 { 1614 db: "db_mycat", 1615 sql: "select max(user) from tbl_mycat where id = 1", 1616 sqls: map[string]map[string][]string{ 1617 "slice-0": { 1618 "db_mycat_1": {"SELECT MAX(`user`) FROM `tbl_mycat` WHERE `id`=1"}, 1619 }, 1620 }, 1621 }, 1622 { 1623 db: "db_mycat", 1624 sql: "select max(user) from tbl_mycat where user = 'curry'", 1625 sqls: map[string]map[string][]string{ 1626 "slice-0": { 1627 "db_mycat_0": {"SELECT MAX(`user`) FROM `tbl_mycat` WHERE `user`='curry'"}, 1628 "db_mycat_1": {"SELECT MAX(`user`) FROM `tbl_mycat` WHERE `user`='curry'"}, 1629 }, 1630 "slice-1": { 1631 "db_mycat_2": {"SELECT MAX(`user`) FROM `tbl_mycat` WHERE `user`='curry'"}, 1632 "db_mycat_3": {"SELECT MAX(`user`) FROM `tbl_mycat` WHERE `user`='curry'"}, 1633 }, 1634 }, 1635 }, 1636 } 1637 1638 for _, test := range tests { 1639 t.Run(test.sql, getTestFunc(ns, test)) 1640 } 1641 } 1642 1643 func TestMycatSelectAggregationFunctionMin(t *testing.T) { 1644 ns, err := preparePlanInfo() 1645 if err != nil { 1646 t.Fatalf("prepare namespace error: %v", err) 1647 } 1648 1649 tests := []SQLTestcase{ 1650 { 1651 db: "db_mycat", 1652 sql: "select min(*) from tbl_mycat", 1653 hasErr: true, // min(*) is invalid syntax 1654 }, 1655 { 1656 db: "db_mycat", 1657 sql: "select min(id) from tbl_mycat", 1658 sqls: map[string]map[string][]string{ 1659 "slice-0": { 1660 "db_mycat_0": {"SELECT MIN(`id`) FROM `tbl_mycat`"}, 1661 "db_mycat_1": {"SELECT MIN(`id`) FROM `tbl_mycat`"}, 1662 }, 1663 "slice-1": { 1664 "db_mycat_2": {"SELECT MIN(`id`) FROM `tbl_mycat`"}, 1665 "db_mycat_3": {"SELECT MIN(`id`) FROM `tbl_mycat`"}, 1666 }, 1667 }, 1668 }, 1669 { 1670 db: "db_mycat", 1671 sql: "select min(user) from tbl_mycat where id = 1", 1672 sqls: map[string]map[string][]string{ 1673 "slice-0": { 1674 "db_mycat_1": {"SELECT MIN(`user`) FROM `tbl_mycat` WHERE `id`=1"}, 1675 }, 1676 }, 1677 }, 1678 { 1679 db: "db_mycat", 1680 sql: "select min(user) from tbl_mycat where user = 'curry'", 1681 sqls: map[string]map[string][]string{ 1682 "slice-0": { 1683 "db_mycat_0": {"SELECT MIN(`user`) FROM `tbl_mycat` WHERE `user`='curry'"}, 1684 "db_mycat_1": {"SELECT MIN(`user`) FROM `tbl_mycat` WHERE `user`='curry'"}, 1685 }, 1686 "slice-1": { 1687 "db_mycat_2": {"SELECT MIN(`user`) FROM `tbl_mycat` WHERE `user`='curry'"}, 1688 "db_mycat_3": {"SELECT MIN(`user`) FROM `tbl_mycat` WHERE `user`='curry'"}, 1689 }, 1690 }, 1691 }, 1692 } 1693 1694 for _, test := range tests { 1695 t.Run(test.sql, getTestFunc(ns, test)) 1696 } 1697 } 1698 1699 func TestMycatSelectAggregationFunctionSum(t *testing.T) { 1700 ns, err := preparePlanInfo() 1701 if err != nil { 1702 t.Fatalf("prepare namespace error: %v", err) 1703 } 1704 1705 tests := []SQLTestcase{ 1706 { 1707 db: "db_mycat", 1708 sql: "select sum(*) from tbl_mycat", 1709 hasErr: true, // sum(*) is invalid syntax 1710 }, 1711 { 1712 db: "db_mycat", 1713 sql: "select sum(id) from tbl_mycat", 1714 sqls: map[string]map[string][]string{ 1715 "slice-0": { 1716 "db_mycat_0": {"SELECT SUM(`id`) FROM `tbl_mycat`"}, 1717 "db_mycat_1": {"SELECT SUM(`id`) FROM `tbl_mycat`"}, 1718 }, 1719 "slice-1": { 1720 "db_mycat_2": {"SELECT SUM(`id`) FROM `tbl_mycat`"}, 1721 "db_mycat_3": {"SELECT SUM(`id`) FROM `tbl_mycat`"}, 1722 }, 1723 }, 1724 }, 1725 { 1726 db: "db_mycat", 1727 sql: "select sum(user) from tbl_mycat where id = 1", 1728 sqls: map[string]map[string][]string{ 1729 "slice-0": { 1730 "db_mycat_1": {"SELECT SUM(`user`) FROM `tbl_mycat` WHERE `id`=1"}, 1731 }, 1732 }, 1733 }, 1734 { 1735 db: "db_mycat", 1736 sql: "select sum(user) from tbl_mycat where user = 'curry'", 1737 sqls: map[string]map[string][]string{ 1738 "slice-0": { 1739 "db_mycat_0": {"SELECT SUM(`user`) FROM `tbl_mycat` WHERE `user`='curry'"}, 1740 "db_mycat_1": {"SELECT SUM(`user`) FROM `tbl_mycat` WHERE `user`='curry'"}, 1741 }, 1742 "slice-1": { 1743 "db_mycat_2": {"SELECT SUM(`user`) FROM `tbl_mycat` WHERE `user`='curry'"}, 1744 "db_mycat_3": {"SELECT SUM(`user`) FROM `tbl_mycat` WHERE `user`='curry'"}, 1745 }, 1746 }, 1747 }, 1748 } 1749 1750 for _, test := range tests { 1751 t.Run(test.sql, getTestFunc(ns, test)) 1752 } 1753 } 1754 1755 func TestMycatSelectGroupBy(t *testing.T) { 1756 ns, err := preparePlanInfo() 1757 if err != nil { 1758 t.Fatalf("prepare namespace error: %v", err) 1759 } 1760 1761 tests := []SQLTestcase{ 1762 { 1763 db: "db_mycat", 1764 sql: "select id, user from tbl_mycat group by id", 1765 sqls: map[string]map[string][]string{ 1766 "slice-0": { 1767 "db_mycat_0": {"SELECT `id`,`user` FROM `tbl_mycat` GROUP BY `id`"}, 1768 "db_mycat_1": {"SELECT `id`,`user` FROM `tbl_mycat` GROUP BY `id`"}, 1769 }, 1770 "slice-1": { 1771 "db_mycat_2": {"SELECT `id`,`user` FROM `tbl_mycat` GROUP BY `id`"}, 1772 "db_mycat_3": {"SELECT `id`,`user` FROM `tbl_mycat` GROUP BY `id`"}, 1773 }, 1774 }, 1775 }, 1776 { 1777 db: "db_mycat", 1778 sql: "select id, count(user) from tbl_mycat group by id", 1779 sqls: map[string]map[string][]string{ 1780 "slice-0": { 1781 "db_mycat_0": {"SELECT `id`,COUNT(`user`) FROM `tbl_mycat` GROUP BY `id`"}, 1782 "db_mycat_1": {"SELECT `id`,COUNT(`user`) FROM `tbl_mycat` GROUP BY `id`"}, 1783 }, 1784 "slice-1": { 1785 "db_mycat_2": {"SELECT `id`,COUNT(`user`) FROM `tbl_mycat` GROUP BY `id`"}, 1786 "db_mycat_3": {"SELECT `id`,COUNT(`user`) FROM `tbl_mycat` GROUP BY `id`"}, 1787 }, 1788 }, 1789 }, 1790 { 1791 db: "db_mycat", 1792 sql: "select id, count(user) from tbl_mycat where id = 1 group by id", 1793 sqls: map[string]map[string][]string{ 1794 "slice-0": { 1795 "db_mycat_1": {"SELECT `id`,COUNT(`user`) FROM `tbl_mycat` WHERE `id`=1 GROUP BY `id`"}, 1796 }, 1797 }, 1798 }, 1799 { 1800 db: "db_mycat", 1801 sql: "select user, count(id) from tbl_mycat where user = 'curry' group by user", 1802 sqls: map[string]map[string][]string{ 1803 "slice-0": { 1804 "db_mycat_0": {"SELECT `user`,COUNT(`id`) FROM `tbl_mycat` WHERE `user`='curry' GROUP BY `user`"}, 1805 "db_mycat_1": {"SELECT `user`,COUNT(`id`) FROM `tbl_mycat` WHERE `user`='curry' GROUP BY `user`"}, 1806 }, 1807 "slice-1": { 1808 "db_mycat_2": {"SELECT `user`,COUNT(`id`) FROM `tbl_mycat` WHERE `user`='curry' GROUP BY `user`"}, 1809 "db_mycat_3": {"SELECT `user`,COUNT(`id`) FROM `tbl_mycat` WHERE `user`='curry' GROUP BY `user`"}, 1810 }, 1811 }, 1812 }, 1813 } 1814 1815 for _, test := range tests { 1816 t.Run(test.sql, getTestFunc(ns, test)) 1817 } 1818 } 1819 1820 func TestMycatSelectHaving(t *testing.T) { 1821 ns, err := preparePlanInfo() 1822 if err != nil { 1823 t.Fatalf("prepare namespace error: %v", err) 1824 } 1825 1826 tests := []SQLTestcase{ 1827 { 1828 db: "db_mycat", 1829 sql: "select id, user from tbl_mycat having id = 1", // note: does not calculate route in having clause 1830 sqls: map[string]map[string][]string{ 1831 "slice-0": { 1832 "db_mycat_0": {"SELECT `id`,`user` FROM `tbl_mycat` HAVING `id`=1"}, 1833 "db_mycat_1": {"SELECT `id`,`user` FROM `tbl_mycat` HAVING `id`=1"}, 1834 }, 1835 "slice-1": { 1836 "db_mycat_2": {"SELECT `id`,`user` FROM `tbl_mycat` HAVING `id`=1"}, 1837 "db_mycat_3": {"SELECT `id`,`user` FROM `tbl_mycat` HAVING `id`=1"}, 1838 }, 1839 }, 1840 }, 1841 { 1842 db: "db_mycat", 1843 sql: "select id, count(user) from tbl_mycat where id=1 group by id having count(user) > 5", 1844 sqls: map[string]map[string][]string{ 1845 "slice-0": { 1846 "db_mycat_1": {"SELECT `id`,COUNT(`user`) FROM `tbl_mycat` WHERE `id`=1 GROUP BY `id` HAVING COUNT(`user`)>5"}, 1847 }, 1848 }, 1849 }, 1850 } 1851 1852 for _, test := range tests { 1853 t.Run(test.sql, getTestFunc(ns, test)) 1854 } 1855 } 1856 1857 func TestMycatSelectOrderBy(t *testing.T) { 1858 ns, err := preparePlanInfo() 1859 if err != nil { 1860 t.Fatalf("prepare namespace error: %v", err) 1861 } 1862 1863 tests := []SQLTestcase{ 1864 { 1865 db: "db_mycat", 1866 sql: "select id, user from tbl_mycat order by id", 1867 sqls: map[string]map[string][]string{ 1868 "slice-0": { 1869 "db_mycat_0": {"SELECT `id`,`user` FROM `tbl_mycat` ORDER BY `id`"}, 1870 "db_mycat_1": {"SELECT `id`,`user` FROM `tbl_mycat` ORDER BY `id`"}, 1871 }, 1872 "slice-1": { 1873 "db_mycat_2": {"SELECT `id`,`user` FROM `tbl_mycat` ORDER BY `id`"}, 1874 "db_mycat_3": {"SELECT `id`,`user` FROM `tbl_mycat` ORDER BY `id`"}, 1875 }, 1876 }, 1877 }, 1878 { 1879 db: "db_mycat", 1880 sql: "select id, user from tbl_mycat where id = 1 order by id", 1881 sqls: map[string]map[string][]string{ 1882 "slice-0": { 1883 "db_mycat_1": {"SELECT `id`,`user` FROM `tbl_mycat` WHERE `id`=1 ORDER BY `id`"}, 1884 }, 1885 }, 1886 }, 1887 { 1888 db: "db_mycat", 1889 sql: "select id, user from tbl_mycat where id in (0,1,2,3,4,5,6,7) order by id", 1890 sqls: map[string]map[string][]string{ 1891 "slice-0": { 1892 "db_mycat_0": {"SELECT `id`,`user` FROM `tbl_mycat` WHERE `id` IN (0,4) ORDER BY `id`"}, 1893 "db_mycat_1": {"SELECT `id`,`user` FROM `tbl_mycat` WHERE `id` IN (1,5) ORDER BY `id`"}, 1894 }, 1895 "slice-1": { 1896 "db_mycat_2": {"SELECT `id`,`user` FROM `tbl_mycat` WHERE `id` IN (2,6) ORDER BY `id`"}, 1897 "db_mycat_3": {"SELECT `id`,`user` FROM `tbl_mycat` WHERE `id` IN (3,7) ORDER BY `id`"}, 1898 }, 1899 }, 1900 }, 1901 { 1902 db: "db_mycat", 1903 sql: "select id, user from tbl_mycat order by id desc", 1904 sqls: map[string]map[string][]string{ 1905 "slice-0": { 1906 "db_mycat_0": {"SELECT `id`,`user` FROM `tbl_mycat` ORDER BY `id` DESC"}, 1907 "db_mycat_1": {"SELECT `id`,`user` FROM `tbl_mycat` ORDER BY `id` DESC"}, 1908 }, 1909 "slice-1": { 1910 "db_mycat_2": {"SELECT `id`,`user` FROM `tbl_mycat` ORDER BY `id` DESC"}, 1911 "db_mycat_3": {"SELECT `id`,`user` FROM `tbl_mycat` ORDER BY `id` DESC"}, 1912 }, 1913 }, 1914 }, 1915 } 1916 1917 for _, test := range tests { 1918 t.Run(test.sql, getTestFunc(ns, test)) 1919 } 1920 } 1921 1922 func TestMycatSelectLimit(t *testing.T) { 1923 ns, err := preparePlanInfo() 1924 if err != nil { 1925 t.Fatalf("prepare namespace error: %v", err) 1926 } 1927 1928 tests := []SQLTestcase{ 1929 { 1930 db: "db_mycat", 1931 sql: "select id, user from tbl_mycat limit 10", 1932 sqls: map[string]map[string][]string{ 1933 "slice-0": { 1934 "db_mycat_0": {"SELECT `id`,`user` FROM `tbl_mycat` LIMIT 10"}, 1935 "db_mycat_1": {"SELECT `id`,`user` FROM `tbl_mycat` LIMIT 10"}, 1936 }, 1937 "slice-1": { 1938 "db_mycat_2": {"SELECT `id`,`user` FROM `tbl_mycat` LIMIT 10"}, 1939 "db_mycat_3": {"SELECT `id`,`user` FROM `tbl_mycat` LIMIT 10"}, 1940 }, 1941 }, 1942 }, 1943 { 1944 db: "db_mycat", 1945 sql: "select id, user from tbl_mycat limit 0, 10", 1946 sqls: map[string]map[string][]string{ 1947 "slice-0": { 1948 "db_mycat_0": {"SELECT `id`,`user` FROM `tbl_mycat` LIMIT 0,10"}, 1949 "db_mycat_1": {"SELECT `id`,`user` FROM `tbl_mycat` LIMIT 0,10"}, 1950 }, 1951 "slice-1": { 1952 "db_mycat_2": {"SELECT `id`,`user` FROM `tbl_mycat` LIMIT 0,10"}, 1953 "db_mycat_3": {"SELECT `id`,`user` FROM `tbl_mycat` LIMIT 0,10"}, 1954 }, 1955 }, 1956 }, 1957 { 1958 db: "db_mycat", 1959 sql: "select id, user from tbl_mycat limit 10, 10", 1960 sqls: map[string]map[string][]string{ 1961 "slice-0": { 1962 "db_mycat_0": {"SELECT `id`,`user` FROM `tbl_mycat` LIMIT 20"}, 1963 "db_mycat_1": {"SELECT `id`,`user` FROM `tbl_mycat` LIMIT 20"}, 1964 }, 1965 "slice-1": { 1966 "db_mycat_2": {"SELECT `id`,`user` FROM `tbl_mycat` LIMIT 20"}, 1967 "db_mycat_3": {"SELECT `id`,`user` FROM `tbl_mycat` LIMIT 20"}, 1968 }, 1969 }, 1970 }, 1971 { 1972 db: "db_mycat", 1973 sql: "select id, user from tbl_mycat where id= 1 group by age having age > 10 order by age desc limit 10, 10", 1974 sqls: map[string]map[string][]string{ 1975 "slice-0": { 1976 "db_mycat_1": {"SELECT `id`,`user` FROM `tbl_mycat` WHERE `id`=1 GROUP BY `age` HAVING `age`>10 ORDER BY `age` DESC LIMIT 10,10"}, 1977 }, 1978 }, 1979 }, 1980 { 1981 db: "db_mycat", 1982 sql: "select id, user from tbl_mycat where id in(0,1) group by age having age > 20 order by age desc limit 10, 10", 1983 sqls: map[string]map[string][]string{ 1984 "slice-0": { 1985 "db_mycat_0": {"SELECT `id`,`user`,`age`,`age` FROM `tbl_mycat` WHERE `id` IN (0) GROUP BY `age` HAVING `age`>20 ORDER BY `age` DESC LIMIT 20"}, 1986 "db_mycat_1": {"SELECT `id`,`user`,`age`,`age` FROM `tbl_mycat` WHERE `id` IN (1) GROUP BY `age` HAVING `age`>20 ORDER BY `age` DESC LIMIT 20"}, 1987 }, 1988 }, 1989 }, 1990 } 1991 1992 for _, test := range tests { 1993 t.Run(test.sql, getTestFunc(ns, test)) 1994 } 1995 } 1996 1997 func TestSelectMycatMultiTablesDatabaseHint(t *testing.T) { 1998 ns, err := preparePlanInfo() 1999 if err != nil { 2000 t.Fatalf("prepare namespace error: %v", err) 2001 } 2002 2003 tests := []SQLTestcase{ 2004 // database function is left 2005 { 2006 db: "db_mycat", 2007 sql: "select * from tbl_mycat, tbl_mycat_child where DATABASE() = `db_mycat_0` and tbl_mycat.id = 1", // hint is column name 2008 sqls: map[string]map[string][]string{ 2009 "slice-0": { 2010 "db_mycat_0": {"SELECT * FROM (`tbl_mycat`) JOIN `tbl_mycat_child` WHERE DATABASE()=`db_mycat_0` AND `tbl_mycat`.`id`=1"}, 2011 }, 2012 }, 2013 }, 2014 { 2015 db: "db_mycat", 2016 sql: "select * from tbl_mycat, tbl_mycat_child where DATABASE() = 'db_mycat_0' and tbl_mycat.id = 1", // hint is value 2017 sqls: map[string]map[string][]string{ 2018 "slice-0": { 2019 "db_mycat_0": {"SELECT * FROM (`tbl_mycat`) JOIN `tbl_mycat_child` WHERE DATABASE()='db_mycat_0' AND `tbl_mycat`.`id`=1"}, 2020 }, 2021 }, 2022 }, 2023 { 2024 db: "db_mycat", 2025 sql: "select * from tbl_mycat, tbl_mycat_child where DB() = `db_mycat_0` and tbl_mycat.id = 1", // not DATABASE hint, use origin route 2026 sqls: map[string]map[string][]string{ 2027 "slice-0": { 2028 "db_mycat_1": {"SELECT * FROM (`tbl_mycat`) JOIN `tbl_mycat_child` WHERE DB()=`db_mycat_0` AND `tbl_mycat`.`id`=1"}, 2029 }, 2030 }, 2031 }, 2032 2033 { 2034 db: "db_mycat", 2035 sql: "select * from tbl_mycat, tbl_mycat_child where DATABASE() = count(1) and tbl_mycat.id = 1", // hint must be a value or column name 2036 hasErr: true, 2037 }, 2038 { 2039 db: "db_mycat", 2040 sql: "select * from tbl_mycat, tbl_mycat_child where DATABASE() = 'db_mycat_10000' and tbl_mycat.id = 1", // phy db not found 2041 hasErr: true, 2042 }, 2043 { 2044 db: "db_ks", 2045 sql: "select * from tbl_ks where DATABASE() = 'db_ks' and id = 1", // only mycat route support database hint 2046 hasErr: true, 2047 }, 2048 // database function is right 2049 { 2050 db: "db_mycat", 2051 sql: "select * from tbl_mycat, tbl_mycat_child where `db_mycat_0` = DATABASE() and tbl_mycat.id = 1", 2052 sqls: map[string]map[string][]string{ 2053 "slice-0": { 2054 "db_mycat_0": {"SELECT * FROM (`tbl_mycat`) JOIN `tbl_mycat_child` WHERE `db_mycat_0`=DATABASE() AND `tbl_mycat`.`id`=1"}, 2055 }, 2056 }, 2057 }, 2058 { 2059 db: "db_mycat", 2060 sql: "select * from tbl_mycat, tbl_mycat_child where `db_mycat_0` = DB() and tbl_mycat.id = 1", // not DATABASE hint, use origin route 2061 sqls: map[string]map[string][]string{ 2062 "slice-0": { 2063 "db_mycat_1": {"SELECT * FROM (`tbl_mycat`) JOIN `tbl_mycat_child` WHERE `db_mycat_0`=DB() AND `tbl_mycat`.`id`=1"}, 2064 }, 2065 }, 2066 }, 2067 { 2068 db: "db_mycat", 2069 sql: "select * from tbl_mycat, tbl_mycat_child where DATABASE() = 'db_mycat_0' and tbl_mycat.id = 1", 2070 sqls: map[string]map[string][]string{ 2071 "slice-0": { 2072 "db_mycat_0": {"SELECT * FROM (`tbl_mycat`) JOIN `tbl_mycat_child` WHERE DATABASE()='db_mycat_0' AND `tbl_mycat`.`id`=1"}, 2073 }, 2074 }, 2075 }, 2076 { 2077 db: "db_mycat", 2078 sql: "select * from tbl_mycat, tbl_mycat_child where count(1) = DATABASE() and tbl_mycat.id = 1", // hint must be a value or column name 2079 hasErr: true, 2080 }, 2081 { 2082 db: "db_mycat", 2083 sql: "select * from tbl_mycat, tbl_mycat_child where 'db_mycat_10000' = DATABASE() and tbl_mycat.id = 1", // phy db not found 2084 hasErr: true, 2085 }, 2086 { 2087 db: "db_ks", 2088 sql: "select * from tbl_ks where 'db_ks' = DATABASE() and id = 1", // only mycat route support database hint 2089 hasErr: true, 2090 }, 2091 } 2092 2093 for _, test := range tests { 2094 t.Run(test.sql, getTestFunc(ns, test)) 2095 } 2096 } 2097 2098 func TestSelectMultiTablesKingshard(t *testing.T) { 2099 ns, err := preparePlanInfo() 2100 if err != nil { 2101 t.Fatalf("prepare namespace error: %v", err) 2102 } 2103 2104 tests := []SQLTestcase{ 2105 { 2106 db: "db_ks", 2107 sql: "select * from tbl_ks, tbl_ks_child", 2108 sqls: map[string]map[string][]string{ 2109 "slice-0": { 2110 "db_ks": { 2111 "SELECT * FROM (`tbl_ks_0000`) JOIN `tbl_ks_child_0000`", 2112 "SELECT * FROM (`tbl_ks_0001`) JOIN `tbl_ks_child_0001`", 2113 }, 2114 }, 2115 "slice-1": { 2116 "db_ks": { 2117 "SELECT * FROM (`tbl_ks_0002`) JOIN `tbl_ks_child_0002`", 2118 "SELECT * FROM (`tbl_ks_0003`) JOIN `tbl_ks_child_0003`", 2119 }, 2120 }, 2121 }, 2122 }, 2123 { 2124 db: "db_ks", 2125 sql: "select * from tbl_ks join tbl_ks_child", 2126 sqls: map[string]map[string][]string{ 2127 "slice-0": { 2128 "db_ks": { 2129 "SELECT * FROM `tbl_ks_0000` JOIN `tbl_ks_child_0000`", 2130 "SELECT * FROM `tbl_ks_0001` JOIN `tbl_ks_child_0001`", 2131 }, 2132 }, 2133 "slice-1": { 2134 "db_ks": { 2135 "SELECT * FROM `tbl_ks_0002` JOIN `tbl_ks_child_0002`", 2136 "SELECT * FROM `tbl_ks_0003` JOIN `tbl_ks_child_0003`", 2137 }, 2138 }, 2139 }, 2140 }, 2141 } 2142 2143 for _, test := range tests { 2144 t.Run(test.sql, getTestFunc(ns, test)) 2145 } 2146 } 2147 2148 func TestSelectKingshardNumRange(t *testing.T) { 2149 ns, err := preparePlanInfo() 2150 if err != nil { 2151 t.Fatalf("prepare namespace error: %v", err) 2152 } 2153 2154 tests := []SQLTestcase{ 2155 { 2156 db: "db_ks", 2157 sql: "select * from tbl_ks_range where id < 0", 2158 sqls: map[string]map[string][]string{}, 2159 }, 2160 { 2161 db: "db_ks", 2162 sql: "select * from tbl_ks_range where id <= 0", 2163 sqls: map[string]map[string][]string{ 2164 "slice-0": { 2165 "db_ks": { 2166 "SELECT * FROM `tbl_ks_range_0000` WHERE `id`<=0", 2167 }, 2168 }, 2169 }, 2170 }, 2171 { 2172 db: "db_ks", 2173 sql: "select * from tbl_ks_range where id < 50", 2174 sqls: map[string]map[string][]string{ 2175 "slice-0": { 2176 "db_ks": { 2177 "SELECT * FROM `tbl_ks_range_0000` WHERE `id`<50", 2178 }, 2179 }, 2180 }, 2181 }, 2182 { 2183 db: "db_ks", 2184 sql: "select * from tbl_ks_range where id < 100", 2185 sqls: map[string]map[string][]string{ 2186 "slice-0": { 2187 "db_ks": { 2188 "SELECT * FROM `tbl_ks_range_0000` WHERE `id`<100", 2189 }, 2190 }, 2191 }, 2192 }, 2193 { 2194 db: "db_ks", 2195 sql: "select * from tbl_ks_range where id <= 100", 2196 sqls: map[string]map[string][]string{ 2197 "slice-0": { 2198 "db_ks": { 2199 "SELECT * FROM `tbl_ks_range_0000` WHERE `id`<=100", 2200 "SELECT * FROM `tbl_ks_range_0001` WHERE `id`<=100", 2201 }, 2202 }, 2203 }, 2204 }, 2205 { 2206 db: "db_ks", 2207 sql: "select * from tbl_ks_range where id < 300", 2208 sqls: map[string]map[string][]string{ 2209 "slice-0": { 2210 "db_ks": { 2211 "SELECT * FROM `tbl_ks_range_0000` WHERE `id`<300", 2212 "SELECT * FROM `tbl_ks_range_0001` WHERE `id`<300", 2213 }, 2214 }, 2215 "slice-1": { 2216 "db_ks": { 2217 "SELECT * FROM `tbl_ks_range_0002` WHERE `id`<300", 2218 }, 2219 }, 2220 }, 2221 }, 2222 { 2223 db: "db_ks", 2224 sql: "select * from tbl_ks_range where id <= 300", 2225 sqls: map[string]map[string][]string{ 2226 "slice-0": { 2227 "db_ks": { 2228 "SELECT * FROM `tbl_ks_range_0000` WHERE `id`<=300", 2229 "SELECT * FROM `tbl_ks_range_0001` WHERE `id`<=300", 2230 }, 2231 }, 2232 "slice-1": { 2233 "db_ks": { 2234 "SELECT * FROM `tbl_ks_range_0002` WHERE `id`<=300", 2235 "SELECT * FROM `tbl_ks_range_0003` WHERE `id`<=300", 2236 }, 2237 }, 2238 }, 2239 }, 2240 { 2241 db: "db_ks", 2242 sql: "select * from tbl_ks_range where id < 400", 2243 hasErr: true, // shard key not in key range 2244 }, 2245 { 2246 db: "db_ks", 2247 sql: "select * from tbl_ks_range where id <= 400", 2248 hasErr: true, // shard key not in key range 2249 }, 2250 2251 { 2252 db: "db_ks", 2253 sql: "select * from tbl_ks_range where id > 0", 2254 sqls: map[string]map[string][]string{ 2255 "slice-0": { 2256 "db_ks": { 2257 "SELECT * FROM `tbl_ks_range_0000` WHERE `id`>0", 2258 "SELECT * FROM `tbl_ks_range_0001` WHERE `id`>0", 2259 }, 2260 }, 2261 "slice-1": { 2262 "db_ks": { 2263 "SELECT * FROM `tbl_ks_range_0002` WHERE `id`>0", 2264 "SELECT * FROM `tbl_ks_range_0003` WHERE `id`>0", 2265 }, 2266 }, 2267 }, 2268 }, 2269 { 2270 db: "db_ks", 2271 sql: "select * from tbl_ks_range where id >= 0", 2272 sqls: map[string]map[string][]string{ 2273 "slice-0": { 2274 "db_ks": { 2275 "SELECT * FROM `tbl_ks_range_0000` WHERE `id`>=0", 2276 "SELECT * FROM `tbl_ks_range_0001` WHERE `id`>=0", 2277 }, 2278 }, 2279 "slice-1": { 2280 "db_ks": { 2281 "SELECT * FROM `tbl_ks_range_0002` WHERE `id`>=0", 2282 "SELECT * FROM `tbl_ks_range_0003` WHERE `id`>=0", 2283 }, 2284 }, 2285 }, 2286 }, 2287 { 2288 db: "db_ks", 2289 sql: "select * from tbl_ks_range where id > 50", 2290 sqls: map[string]map[string][]string{ 2291 "slice-0": { 2292 "db_ks": { 2293 "SELECT * FROM `tbl_ks_range_0000` WHERE `id`>50", 2294 "SELECT * FROM `tbl_ks_range_0001` WHERE `id`>50", 2295 }, 2296 }, 2297 "slice-1": { 2298 "db_ks": { 2299 "SELECT * FROM `tbl_ks_range_0002` WHERE `id`>50", 2300 "SELECT * FROM `tbl_ks_range_0003` WHERE `id`>50", 2301 }, 2302 }, 2303 }, 2304 }, 2305 { 2306 db: "db_ks", 2307 sql: "select * from tbl_ks_range where id > 100", 2308 sqls: map[string]map[string][]string{ 2309 "slice-0": { 2310 "db_ks": { 2311 "SELECT * FROM `tbl_ks_range_0001` WHERE `id`>100", 2312 }, 2313 }, 2314 "slice-1": { 2315 "db_ks": { 2316 "SELECT * FROM `tbl_ks_range_0002` WHERE `id`>100", 2317 "SELECT * FROM `tbl_ks_range_0003` WHERE `id`>100", 2318 }, 2319 }, 2320 }, 2321 }, 2322 { 2323 db: "db_ks", 2324 sql: "select * from tbl_ks_range where id >= 100", 2325 sqls: map[string]map[string][]string{ 2326 "slice-0": { 2327 "db_ks": { 2328 "SELECT * FROM `tbl_ks_range_0001` WHERE `id`>=100", 2329 }, 2330 }, 2331 "slice-1": { 2332 "db_ks": { 2333 "SELECT * FROM `tbl_ks_range_0002` WHERE `id`>=100", 2334 "SELECT * FROM `tbl_ks_range_0003` WHERE `id`>=100", 2335 }, 2336 }, 2337 }, 2338 }, 2339 { 2340 db: "db_ks", 2341 sql: "select * from tbl_ks_range where id > 300", 2342 sqls: map[string]map[string][]string{ 2343 "slice-1": { 2344 "db_ks": { 2345 "SELECT * FROM `tbl_ks_range_0003` WHERE `id`>300", 2346 }, 2347 }, 2348 }, 2349 }, 2350 { 2351 db: "db_ks", 2352 sql: "select * from tbl_ks_range where id >= 300", 2353 sqls: map[string]map[string][]string{ 2354 "slice-1": { 2355 "db_ks": { 2356 "SELECT * FROM `tbl_ks_range_0003` WHERE `id`>=300", 2357 }, 2358 }, 2359 }, 2360 }, 2361 { 2362 db: "db_ks", 2363 sql: "select * from tbl_ks_range where id > 400", 2364 hasErr: true, // shard key not in key range 2365 }, 2366 { 2367 db: "db_ks", 2368 sql: "select * from tbl_ks_range where id >= 400", 2369 hasErr: true, // shard key not in key range 2370 }, 2371 { 2372 db: "db_ks", 2373 sql: "select * from tbl_ks_range where id <= 300 AND id > 300", 2374 sqls: map[string]map[string][]string{ 2375 "slice-1": { 2376 "db_ks": { 2377 "SELECT * FROM `tbl_ks_range_0003` WHERE `id`<=300 AND `id`>300", // we can't handle this testcase 2378 }, 2379 }, 2380 }, 2381 }, 2382 { 2383 db: "db_ks", 2384 sql: "select * from tbl_ks_range where id < 300 AND id >= 300", 2385 sqls: map[string]map[string][]string{}, 2386 }, 2387 { 2388 db: "db_ks", 2389 sql: "select * from tbl_ks_range where id > 100 AND id < 300", 2390 sqls: map[string]map[string][]string{ 2391 "slice-0": { 2392 "db_ks": { 2393 "SELECT * FROM `tbl_ks_range_0001` WHERE `id`>100 AND `id`<300", 2394 }, 2395 }, 2396 "slice-1": { 2397 "db_ks": { 2398 "SELECT * FROM `tbl_ks_range_0002` WHERE `id`>100 AND `id`<300", 2399 }, 2400 }, 2401 }, 2402 }, 2403 } 2404 2405 for _, test := range tests { 2406 t.Run(test.sql, getTestFunc(ns, test)) 2407 } 2408 } 2409 2410 func TestSelectKingshardNumRangeReverse(t *testing.T) { 2411 ns, err := preparePlanInfo() 2412 if err != nil { 2413 t.Fatalf("prepare namespace error: %v", err) 2414 } 2415 2416 tests := []SQLTestcase{ 2417 { 2418 db: "db_ks", 2419 sql: "select * from tbl_ks_range where 0>id", 2420 sqls: map[string]map[string][]string{}, 2421 }, 2422 { 2423 db: "db_ks", 2424 sql: "select * from tbl_ks_range where 0>=id", 2425 sqls: map[string]map[string][]string{ 2426 "slice-0": { 2427 "db_ks": { 2428 "SELECT * FROM `tbl_ks_range_0000` WHERE 0>=`id`", 2429 }, 2430 }, 2431 }, 2432 }, 2433 { 2434 db: "db_ks", 2435 sql: "select * from tbl_ks_range where 50>id", 2436 sqls: map[string]map[string][]string{ 2437 "slice-0": { 2438 "db_ks": { 2439 "SELECT * FROM `tbl_ks_range_0000` WHERE 50>`id`", 2440 }, 2441 }, 2442 }, 2443 }, 2444 { 2445 db: "db_ks", 2446 sql: "select * from tbl_ks_range where 100 > id", 2447 sqls: map[string]map[string][]string{ 2448 "slice-0": { 2449 "db_ks": { 2450 "SELECT * FROM `tbl_ks_range_0000` WHERE 100>`id`", 2451 }, 2452 }, 2453 }, 2454 }, 2455 { 2456 db: "db_ks", 2457 sql: "select * from tbl_ks_range where 100 >= id", 2458 sqls: map[string]map[string][]string{ 2459 "slice-0": { 2460 "db_ks": { 2461 "SELECT * FROM `tbl_ks_range_0000` WHERE 100>=`id`", 2462 "SELECT * FROM `tbl_ks_range_0001` WHERE 100>=`id`", 2463 }, 2464 }, 2465 }, 2466 }, 2467 { 2468 db: "db_ks", 2469 sql: "select * from tbl_ks_range where 300>id", 2470 sqls: map[string]map[string][]string{ 2471 "slice-0": { 2472 "db_ks": { 2473 "SELECT * FROM `tbl_ks_range_0000` WHERE 300>`id`", 2474 "SELECT * FROM `tbl_ks_range_0001` WHERE 300>`id`", 2475 }, 2476 }, 2477 "slice-1": { 2478 "db_ks": { 2479 "SELECT * FROM `tbl_ks_range_0002` WHERE 300>`id`", 2480 }, 2481 }, 2482 }, 2483 }, 2484 { 2485 db: "db_ks", 2486 sql: "select * from tbl_ks_range where 300 >= id", 2487 sqls: map[string]map[string][]string{ 2488 "slice-0": { 2489 "db_ks": { 2490 "SELECT * FROM `tbl_ks_range_0000` WHERE 300>=`id`", 2491 "SELECT * FROM `tbl_ks_range_0001` WHERE 300>=`id`", 2492 }, 2493 }, 2494 "slice-1": { 2495 "db_ks": { 2496 "SELECT * FROM `tbl_ks_range_0002` WHERE 300>=`id`", 2497 "SELECT * FROM `tbl_ks_range_0003` WHERE 300>=`id`", 2498 }, 2499 }, 2500 }, 2501 }, 2502 { 2503 db: "db_ks", 2504 sql: "select * from tbl_ks_range where 400 > id", 2505 hasErr: true, // shard key not in key range 2506 }, 2507 { 2508 db: "db_ks", 2509 sql: "select * from tbl_ks_range where 400 >= id", 2510 hasErr: true, // shard key not in key range 2511 }, 2512 2513 { 2514 db: "db_ks", 2515 sql: "select * from tbl_ks_range where 0 < id", 2516 sqls: map[string]map[string][]string{ 2517 "slice-0": { 2518 "db_ks": { 2519 "SELECT * FROM `tbl_ks_range_0000` WHERE 0<`id`", 2520 "SELECT * FROM `tbl_ks_range_0001` WHERE 0<`id`", 2521 }, 2522 }, 2523 "slice-1": { 2524 "db_ks": { 2525 "SELECT * FROM `tbl_ks_range_0002` WHERE 0<`id`", 2526 "SELECT * FROM `tbl_ks_range_0003` WHERE 0<`id`", 2527 }, 2528 }, 2529 }, 2530 }, 2531 { 2532 db: "db_ks", 2533 sql: "select * from tbl_ks_range where 0 <= id", 2534 sqls: map[string]map[string][]string{ 2535 "slice-0": { 2536 "db_ks": { 2537 "SELECT * FROM `tbl_ks_range_0000` WHERE 0<=`id`", 2538 "SELECT * FROM `tbl_ks_range_0001` WHERE 0<=`id`", 2539 }, 2540 }, 2541 "slice-1": { 2542 "db_ks": { 2543 "SELECT * FROM `tbl_ks_range_0002` WHERE 0<=`id`", 2544 "SELECT * FROM `tbl_ks_range_0003` WHERE 0<=`id`", 2545 }, 2546 }, 2547 }, 2548 }, 2549 { 2550 db: "db_ks", 2551 sql: "select * from tbl_ks_range where 50 < id", 2552 sqls: map[string]map[string][]string{ 2553 "slice-0": { 2554 "db_ks": { 2555 "SELECT * FROM `tbl_ks_range_0000` WHERE 50<`id`", 2556 "SELECT * FROM `tbl_ks_range_0001` WHERE 50<`id`", 2557 }, 2558 }, 2559 "slice-1": { 2560 "db_ks": { 2561 "SELECT * FROM `tbl_ks_range_0002` WHERE 50<`id`", 2562 "SELECT * FROM `tbl_ks_range_0003` WHERE 50<`id`", 2563 }, 2564 }, 2565 }, 2566 }, 2567 { 2568 db: "db_ks", 2569 sql: "select * from tbl_ks_range where 100 < id", 2570 sqls: map[string]map[string][]string{ 2571 "slice-0": { 2572 "db_ks": { 2573 "SELECT * FROM `tbl_ks_range_0001` WHERE 100<`id`", 2574 }, 2575 }, 2576 "slice-1": { 2577 "db_ks": { 2578 "SELECT * FROM `tbl_ks_range_0002` WHERE 100<`id`", 2579 "SELECT * FROM `tbl_ks_range_0003` WHERE 100<`id`", 2580 }, 2581 }, 2582 }, 2583 }, 2584 { 2585 db: "db_ks", 2586 sql: "select * from tbl_ks_range where 100<=id", 2587 sqls: map[string]map[string][]string{ 2588 "slice-0": { 2589 "db_ks": { 2590 "SELECT * FROM `tbl_ks_range_0001` WHERE 100<=`id`", 2591 }, 2592 }, 2593 "slice-1": { 2594 "db_ks": { 2595 "SELECT * FROM `tbl_ks_range_0002` WHERE 100<=`id`", 2596 "SELECT * FROM `tbl_ks_range_0003` WHERE 100<=`id`", 2597 }, 2598 }, 2599 }, 2600 }, 2601 { 2602 db: "db_ks", 2603 sql: "select * from tbl_ks_range where 300 < id", 2604 sqls: map[string]map[string][]string{ 2605 "slice-1": { 2606 "db_ks": { 2607 "SELECT * FROM `tbl_ks_range_0003` WHERE 300<`id`", 2608 }, 2609 }, 2610 }, 2611 }, 2612 { 2613 db: "db_ks", 2614 sql: "select * from tbl_ks_range where 300 <= id", 2615 sqls: map[string]map[string][]string{ 2616 "slice-1": { 2617 "db_ks": { 2618 "SELECT * FROM `tbl_ks_range_0003` WHERE 300<=`id`", 2619 }, 2620 }, 2621 }, 2622 }, 2623 { 2624 db: "db_ks", 2625 sql: "select * from tbl_ks_range where 400 < id", 2626 hasErr: true, // shard key not in key range 2627 }, 2628 { 2629 db: "db_ks", 2630 sql: "select * from tbl_ks_range where 400 <= id", 2631 hasErr: true, // shard key not in key range 2632 }, 2633 { 2634 db: "db_ks", 2635 sql: "select * from tbl_ks_range where 300 >= id AND 300 < id", 2636 sqls: map[string]map[string][]string{ 2637 "slice-1": { 2638 "db_ks": { 2639 "SELECT * FROM `tbl_ks_range_0003` WHERE 300>=`id` AND 300<`id`", // we can't handle this testcase 2640 }, 2641 }, 2642 }, 2643 }, 2644 { 2645 db: "db_ks", 2646 sql: "select * from tbl_ks_range where 300 > id AND 300 <= id", 2647 sqls: map[string]map[string][]string{}, 2648 }, 2649 { 2650 db: "db_ks", 2651 sql: "select * from tbl_ks_range where 100 < id AND 300 > id", 2652 sqls: map[string]map[string][]string{ 2653 "slice-0": { 2654 "db_ks": { 2655 "SELECT * FROM `tbl_ks_range_0001` WHERE 100<`id` AND 300>`id`", 2656 }, 2657 }, 2658 "slice-1": { 2659 "db_ks": { 2660 "SELECT * FROM `tbl_ks_range_0002` WHERE 100<`id` AND 300>`id`", 2661 }, 2662 }, 2663 }, 2664 }, 2665 } 2666 2667 for _, test := range tests { 2668 t.Run(test.sql, getTestFunc(ns, test)) 2669 } 2670 } 2671 2672 func TestSelectKingshardDateYear(t *testing.T) { 2673 ns, err := preparePlanInfo() 2674 if err != nil { 2675 t.Fatalf("prepare namespace error: %v", err) 2676 } 2677 2678 tests := []SQLTestcase{ 2679 { 2680 db: "db_ks", 2681 sql: "select * from tbl_ks_year where create_time > 1564070400", // 2019/07/26 00:00:00 2682 sqls: map[string]map[string][]string{ 2683 "slice-1": { 2684 "db_ks": { 2685 "SELECT * FROM `tbl_ks_year_2019` WHERE `create_time`>1564070400", 2686 }, 2687 }, 2688 }, 2689 }, 2690 { 2691 db: "db_ks", 2692 sql: "select * from tbl_ks_year where create_time > 1406304000", // 2014/07/26 00:00:00 2693 sqls: map[string]map[string][]string{ 2694 "slice-0": { 2695 "db_ks": { 2696 "SELECT * FROM `tbl_ks_year_2014` WHERE `create_time`>1406304000", 2697 "SELECT * FROM `tbl_ks_year_2015` WHERE `create_time`>1406304000", 2698 "SELECT * FROM `tbl_ks_year_2016` WHERE `create_time`>1406304000", 2699 "SELECT * FROM `tbl_ks_year_2017` WHERE `create_time`>1406304000", 2700 }, 2701 }, 2702 "slice-1": { 2703 "db_ks": { 2704 "SELECT * FROM `tbl_ks_year_2018` WHERE `create_time`>1406304000", 2705 "SELECT * FROM `tbl_ks_year_2019` WHERE `create_time`>1406304000", 2706 }, 2707 }, 2708 }, 2709 }, 2710 { 2711 db: "db_ks", 2712 sql: "select * from tbl_ks_year where create_time < 1388505600", // 2014/01/01 00:00:00 2713 sqls: map[string]map[string][]string{}, 2714 }, 2715 { 2716 db: "db_ks", 2717 sql: "select * from tbl_ks_year where create_time < 1385827200", // 2013/12/01 00:00:00 2718 sqls: map[string]map[string][]string{}, 2719 }, 2720 { 2721 db: "db_ks", 2722 sql: "select * from tbl_ks_year where create_time <= 1388505600", // 2014/01/01 00:00:00 2723 sqls: map[string]map[string][]string{ 2724 "slice-0": { 2725 "db_ks": { 2726 "SELECT * FROM `tbl_ks_year_2014` WHERE `create_time`<=1388505600", 2727 }, 2728 }, 2729 }, 2730 }, 2731 { 2732 db: "db_ks", 2733 sql: "select * from tbl_ks_year where create_time <= 1388505600", // 2014/01/01 00:00:00 2734 sqls: map[string]map[string][]string{ 2735 "slice-0": { 2736 "db_ks": { 2737 "SELECT * FROM `tbl_ks_year_2014` WHERE `create_time`<=1388505600", 2738 }, 2739 }, 2740 }, 2741 }, 2742 { 2743 db: "db_ks", 2744 sql: "select * from tbl_ks_year where create_time > 1514735999", // 2017/12/31 23:59:59 2745 sqls: map[string]map[string][]string{ 2746 "slice-0": { 2747 "db_ks": { 2748 "SELECT * FROM `tbl_ks_year_2017` WHERE `create_time`>1514735999", 2749 }, 2750 }, 2751 "slice-1": { 2752 "db_ks": { 2753 "SELECT * FROM `tbl_ks_year_2018` WHERE `create_time`>1514735999", 2754 "SELECT * FROM `tbl_ks_year_2019` WHERE `create_time`>1514735999", 2755 }, 2756 }, 2757 }, 2758 }, 2759 { 2760 db: "db_ks", 2761 sql: "select * from tbl_ks_year where create_time >= 1514736000", // 2018/01/01 00:00:00 2762 sqls: map[string]map[string][]string{ 2763 "slice-1": { 2764 "db_ks": { 2765 "SELECT * FROM `tbl_ks_year_2018` WHERE `create_time`>=1514736000", 2766 "SELECT * FROM `tbl_ks_year_2019` WHERE `create_time`>=1514736000", 2767 }, 2768 }, 2769 }, 2770 }, 2771 { 2772 db: "db_ks", 2773 sql: "select * from tbl_ks_year where create_time >= 1577808000", // 2020/01/01 00:00:00 2774 sqls: map[string]map[string][]string{}, 2775 }, 2776 } 2777 2778 for _, test := range tests { 2779 t.Run(test.sql, getTestFunc(ns, test)) 2780 } 2781 } 2782 2783 func TestSelectKingshardDateMonth(t *testing.T) { 2784 ns, err := preparePlanInfo() 2785 if err != nil { 2786 t.Fatalf("prepare namespace error: %v", err) 2787 } 2788 2789 tests := []SQLTestcase{ 2790 { 2791 db: "db_ks", 2792 sql: "select * from tbl_ks_month where create_time > 1398873600", // 2014/05/01 00:00:00 2793 sqls: map[string]map[string][]string{ 2794 "slice-0": { 2795 "db_ks": { 2796 "SELECT * FROM `tbl_ks_month_201405` WHERE `create_time`>1398873600", 2797 "SELECT * FROM `tbl_ks_month_201406` WHERE `create_time`>1398873600", 2798 }, 2799 }, 2800 "slice-1": { 2801 "db_ks": { 2802 "SELECT * FROM `tbl_ks_month_201408` WHERE `create_time`>1398873600", 2803 "SELECT * FROM `tbl_ks_month_201409` WHERE `create_time`>1398873600", 2804 }, 2805 }, 2806 }, 2807 }, 2808 { 2809 db: "db_ks", 2810 sql: "select * from tbl_ks_month where create_time > 1398614400", // 2014/04/28 00:00:00 2811 sqls: map[string]map[string][]string{ 2812 "slice-0": { 2813 "db_ks": { 2814 "SELECT * FROM `tbl_ks_month_201405` WHERE `create_time`>1398614400", 2815 "SELECT * FROM `tbl_ks_month_201406` WHERE `create_time`>1398614400", 2816 }, 2817 }, 2818 "slice-1": { 2819 "db_ks": { 2820 "SELECT * FROM `tbl_ks_month_201408` WHERE `create_time`>1398614400", 2821 "SELECT * FROM `tbl_ks_month_201409` WHERE `create_time`>1398614400", 2822 }, 2823 }, 2824 }, 2825 }, 2826 { 2827 db: "db_ks", 2828 sql: "select * from tbl_ks_month where create_time < 1398873600", // 2014/05/01 00:00:00 2829 sqls: map[string]map[string][]string{}, 2830 }, 2831 { 2832 db: "db_ks", 2833 sql: "select * from tbl_ks_month where create_time < 1398614400", // 2014/04/28 00:00:00 2834 sqls: map[string]map[string][]string{}, 2835 }, 2836 { 2837 db: "db_ks", 2838 sql: "select * from tbl_ks_month where create_time < 1404144000", // 2014/07/01 00:00:00 2839 sqls: map[string]map[string][]string{ 2840 "slice-0": { 2841 "db_ks": { 2842 "SELECT * FROM `tbl_ks_month_201405` WHERE `create_time`<1404144000", 2843 "SELECT * FROM `tbl_ks_month_201406` WHERE `create_time`<1404144000", 2844 }, 2845 }, 2846 }, 2847 }, 2848 { 2849 db: "db_ks", 2850 sql: "select * from tbl_ks_month where create_time >= 1404144000", // 2014/07/01 00:00:00 2851 sqls: map[string]map[string][]string{ 2852 "slice-1": { 2853 "db_ks": { 2854 "SELECT * FROM `tbl_ks_month_201408` WHERE `create_time`>=1404144000", 2855 "SELECT * FROM `tbl_ks_month_201409` WHERE `create_time`>=1404144000", 2856 }, 2857 }, 2858 }, 2859 }, 2860 { 2861 db: "db_ks", 2862 sql: "select * from tbl_ks_month where create_time > 1412092799", // 2014/09/30 23:59:59 2863 sqls: map[string]map[string][]string{ 2864 "slice-1": { 2865 "db_ks": { 2866 "SELECT * FROM `tbl_ks_month_201409` WHERE `create_time`>1412092799", 2867 }, 2868 }, 2869 }, 2870 }, 2871 { 2872 db: "db_ks", 2873 sql: "select * from tbl_ks_month where create_time >= 1412092800", // 2014/10/01 00:00:00 2874 sqls: map[string]map[string][]string{}, 2875 }, 2876 { 2877 db: "db_ks", 2878 sql: "select * from tbl_ks_month where create_time >= 1412438400", // 2014/10/05 00:00:00 2879 sqls: map[string]map[string][]string{}, 2880 }, 2881 } 2882 2883 for _, test := range tests { 2884 t.Run(test.sql, getTestFunc(ns, test)) 2885 } 2886 } 2887 2888 func TestSelectKingshardDateDay(t *testing.T) { 2889 ns, err := preparePlanInfo() 2890 if err != nil { 2891 t.Fatalf("prepare namespace error: %v", err) 2892 } 2893 2894 tests := []SQLTestcase{ 2895 { 2896 db: "db_ks", 2897 sql: "select * from tbl_ks_day where create_time < 1409500800", // 2014/09/01 00:00:00 2898 sqls: map[string]map[string][]string{}, 2899 }, 2900 { 2901 db: "db_ks", 2902 sql: "select * from tbl_ks_day where create_time <= 1409500800", // 2014/09/01 00:00:00 2903 sqls: map[string]map[string][]string{ 2904 "slice-0": { 2905 "db_ks": { 2906 "SELECT * FROM `tbl_ks_day_20140901` WHERE `create_time`<=1409500800", 2907 }, 2908 }, 2909 }, 2910 }, 2911 { 2912 db: "db_ks", 2913 sql: "select * from tbl_ks_day where create_time >= 1410278400", // 2014/09/10 00:00:00 2914 sqls: map[string]map[string][]string{}, 2915 }, 2916 { 2917 db: "db_ks", 2918 sql: "select * from tbl_ks_day where create_time = 1409846400", // 2014/09/05 00:00:00 2919 sqls: map[string]map[string][]string{ 2920 "slice-0": { 2921 "db_ks": { 2922 "SELECT * FROM `tbl_ks_day_20140905` WHERE `create_time`=1409846400", 2923 }, 2924 }, 2925 }, 2926 }, 2927 { 2928 db: "db_ks", 2929 sql: "select * from tbl_ks_day where create_time = 1409932800", // 2014/09/06 00:00:00 2930 }, 2931 { 2932 db: "db_ks", 2933 sql: "select * from tbl_ks_day where create_time = 1410019200", // 2014/09/07 00:00:00 2934 sqls: map[string]map[string][]string{ 2935 "slice-1": { 2936 "db_ks": { 2937 "SELECT * FROM `tbl_ks_day_20140907` WHERE `create_time`=1410019200", 2938 }, 2939 }, 2940 }, 2941 }, 2942 { 2943 db: "db_ks", 2944 sql: "select * from tbl_ks_day where create_time > 1410019200", // 2014/09/07 00:00:00 2945 sqls: map[string]map[string][]string{ 2946 "slice-1": { 2947 "db_ks": { 2948 "SELECT * FROM `tbl_ks_day_20140907` WHERE `create_time`>1410019200", 2949 "SELECT * FROM `tbl_ks_day_20140908` WHERE `create_time`>1410019200", 2950 }, 2951 }, 2952 }, 2953 }, 2954 { 2955 db: "db_ks", 2956 sql: "select * from tbl_ks_day where create_time > 1410019200", // 2014/09/07 00:00:00 2957 sqls: map[string]map[string][]string{ 2958 "slice-1": { 2959 "db_ks": { 2960 "SELECT * FROM `tbl_ks_day_20140907` WHERE `create_time`>1410019200", 2961 "SELECT * FROM `tbl_ks_day_20140908` WHERE `create_time`>1410019200", 2962 }, 2963 }, 2964 }, 2965 }, 2966 { 2967 db: "db_ks", 2968 sql: "select * from tbl_ks_day where create_time < 1410278400", // 2014/09/10 00:00:00 2969 sqls: map[string]map[string][]string{ 2970 "slice-0": { 2971 "db_ks": { 2972 "SELECT * FROM `tbl_ks_day_20140901` WHERE `create_time`<1410278400", 2973 "SELECT * FROM `tbl_ks_day_20140902` WHERE `create_time`<1410278400", 2974 "SELECT * FROM `tbl_ks_day_20140903` WHERE `create_time`<1410278400", 2975 "SELECT * FROM `tbl_ks_day_20140904` WHERE `create_time`<1410278400", 2976 "SELECT * FROM `tbl_ks_day_20140905` WHERE `create_time`<1410278400", 2977 }, 2978 }, 2979 "slice-1": { 2980 "db_ks": { 2981 "SELECT * FROM `tbl_ks_day_20140907` WHERE `create_time`<1410278400", 2982 "SELECT * FROM `tbl_ks_day_20140908` WHERE `create_time`<1410278400", 2983 }, 2984 }, 2985 }, 2986 }, 2987 { 2988 db: "db_ks", 2989 sql: "select * from tbl_ks_day where create_time >= 1408464000", // 2014/08/20 00:00:00 2990 sqls: map[string]map[string][]string{ 2991 "slice-0": { 2992 "db_ks": { 2993 "SELECT * FROM `tbl_ks_day_20140901` WHERE `create_time`>=1408464000", 2994 "SELECT * FROM `tbl_ks_day_20140902` WHERE `create_time`>=1408464000", 2995 "SELECT * FROM `tbl_ks_day_20140903` WHERE `create_time`>=1408464000", 2996 "SELECT * FROM `tbl_ks_day_20140904` WHERE `create_time`>=1408464000", 2997 "SELECT * FROM `tbl_ks_day_20140905` WHERE `create_time`>=1408464000", 2998 }, 2999 }, 3000 "slice-1": { 3001 "db_ks": { 3002 "SELECT * FROM `tbl_ks_day_20140907` WHERE `create_time`>=1408464000", 3003 "SELECT * FROM `tbl_ks_day_20140908` WHERE `create_time`>=1408464000", 3004 }, 3005 }, 3006 }, 3007 }, 3008 } 3009 3010 for _, test := range tests { 3011 t.Run(test.sql, getTestFunc(ns, test)) 3012 } 3013 } 3014 3015 func TestSelectMultiTablesOnConditionKingshard(t *testing.T) { 3016 ns, err := preparePlanInfo() 3017 if err != nil { 3018 t.Fatalf("prepare namespace error: %v", err) 3019 } 3020 3021 tests := []SQLTestcase{ 3022 { 3023 db: "db_ks", 3024 sql: "select * from tbl_ks join tbl_ks_child on tbl_ks.id in (1,2,3) AND tbl_ks.id = tbl_ks_child.id", 3025 sqls: map[string]map[string][]string{ 3026 "slice-0": { 3027 "db_ks": { 3028 "SELECT * FROM `tbl_ks_0001` JOIN `tbl_ks_child_0001` ON `tbl_ks_0001`.`id` IN (1) AND `tbl_ks_0001`.`id`=`tbl_ks_child_0001`.`id`", 3029 }, 3030 }, 3031 "slice-1": { 3032 "db_ks": { 3033 "SELECT * FROM `tbl_ks_0002` JOIN `tbl_ks_child_0002` ON `tbl_ks_0002`.`id` IN (2) AND `tbl_ks_0002`.`id`=`tbl_ks_child_0002`.`id`", 3034 "SELECT * FROM `tbl_ks_0003` JOIN `tbl_ks_child_0003` ON `tbl_ks_0003`.`id` IN (3) AND `tbl_ks_0003`.`id`=`tbl_ks_child_0003`.`id`", 3035 }, 3036 }, 3037 }, 3038 }, 3039 } 3040 3041 for _, test := range tests { 3042 t.Run(test.sql, getTestFunc(ns, test)) 3043 } 3044 } 3045 3046 func TestSelectMultiTablesComparisonKingshard(t *testing.T) { 3047 ns, err := preparePlanInfo() 3048 if err != nil { 3049 t.Fatalf("prepare namespace error: %v", err) 3050 } 3051 3052 tests := []SQLTestcase{ 3053 { 3054 db: "db_ks", 3055 sql: "select * from tbl_ks where (tbl_ks.id = 3 OR 1 = 1) AND 1 = 0", 3056 sqls: map[string]map[string][]string{ 3057 "slice-0": { 3058 "db_ks": { 3059 "SELECT * FROM `tbl_ks_0000` WHERE (`tbl_ks_0000`.`id`=3 OR 1=1) AND 1=0", 3060 "SELECT * FROM `tbl_ks_0001` WHERE (`tbl_ks_0001`.`id`=3 OR 1=1) AND 1=0", 3061 }, 3062 }, 3063 "slice-1": { 3064 "db_ks": { 3065 "SELECT * FROM `tbl_ks_0002` WHERE (`tbl_ks_0002`.`id`=3 OR 1=1) AND 1=0", 3066 "SELECT * FROM `tbl_ks_0003` WHERE (`tbl_ks_0003`.`id`=3 OR 1=1) AND 1=0", 3067 }, 3068 }, 3069 }, 3070 }, 3071 } 3072 3073 for _, test := range tests { 3074 t.Run(test.sql, getTestFunc(ns, test)) 3075 } 3076 } 3077 3078 func TestSelectShardTableWithGlobalTableKingshard(t *testing.T) { 3079 ns, err := preparePlanInfo() 3080 if err != nil { 3081 t.Fatalf("prepare namespace error: %v", err) 3082 } 3083 3084 tests := []SQLTestcase{ 3085 { 3086 db: "db_ks", 3087 sql: "select * from tbl_ks, tbl_ks_global_one where tbl_ks.id = 3 and tbl_ks_global_one.name='haha'", 3088 sqls: map[string]map[string][]string{ 3089 "slice-1": { 3090 "db_ks": { 3091 "SELECT * FROM (`tbl_ks_0003`) JOIN `tbl_ks_global_one` WHERE `tbl_ks_0003`.`id`=3 AND `tbl_ks_global_one`.`name`='haha'", 3092 }, 3093 }, 3094 }, 3095 }, 3096 { 3097 db: "db_ks", 3098 sql: "select * from tbl_ks, tbl_ks_global_one where tbl_ks.unshard_col = 3 and tbl_ks_global_one.name='haha'", 3099 sqls: map[string]map[string][]string{ 3100 "slice-0": { 3101 "db_ks": { 3102 "SELECT * FROM (`tbl_ks_0000`) JOIN `tbl_ks_global_one` WHERE `tbl_ks_0000`.`unshard_col`=3 AND `tbl_ks_global_one`.`name`='haha'", 3103 "SELECT * FROM (`tbl_ks_0001`) JOIN `tbl_ks_global_one` WHERE `tbl_ks_0001`.`unshard_col`=3 AND `tbl_ks_global_one`.`name`='haha'", 3104 }, 3105 }, 3106 "slice-1": { 3107 "db_ks": { 3108 "SELECT * FROM (`tbl_ks_0002`) JOIN `tbl_ks_global_one` WHERE `tbl_ks_0002`.`unshard_col`=3 AND `tbl_ks_global_one`.`name`='haha'", 3109 "SELECT * FROM (`tbl_ks_0003`) JOIN `tbl_ks_global_one` WHERE `tbl_ks_0003`.`unshard_col`=3 AND `tbl_ks_global_one`.`name`='haha'", 3110 }, 3111 }, 3112 }, 3113 }, 3114 { 3115 db: "db_ks", 3116 sql: "select * from tbl_ks, tbl_ks_global_one, tbl_ks_global_two where tbl_ks.unshard_col = 3 and tbl_ks_global_one.name='haha' and tbl_ks_global_two.gender='female'", 3117 sqls: map[string]map[string][]string{ 3118 "slice-0": { 3119 "db_ks": { 3120 "SELECT * FROM ((`tbl_ks_0000`) JOIN `tbl_ks_global_one`) JOIN `tbl_ks_global_two` WHERE `tbl_ks_0000`.`unshard_col`=3 AND `tbl_ks_global_one`.`name`='haha' AND `tbl_ks_global_two`.`gender`='female'", 3121 "SELECT * FROM ((`tbl_ks_0001`) JOIN `tbl_ks_global_one`) JOIN `tbl_ks_global_two` WHERE `tbl_ks_0001`.`unshard_col`=3 AND `tbl_ks_global_one`.`name`='haha' AND `tbl_ks_global_two`.`gender`='female'", 3122 }, 3123 }, 3124 "slice-1": { 3125 "db_ks": { 3126 "SELECT * FROM ((`tbl_ks_0002`) JOIN `tbl_ks_global_one`) JOIN `tbl_ks_global_two` WHERE `tbl_ks_0002`.`unshard_col`=3 AND `tbl_ks_global_one`.`name`='haha' AND `tbl_ks_global_two`.`gender`='female'", 3127 "SELECT * FROM ((`tbl_ks_0003`) JOIN `tbl_ks_global_one`) JOIN `tbl_ks_global_two` WHERE `tbl_ks_0003`.`unshard_col`=3 AND `tbl_ks_global_one`.`name`='haha' AND `tbl_ks_global_two`.`gender`='female'", 3128 }, 3129 }, 3130 }, 3131 }, 3132 } 3133 3134 for _, test := range tests { 3135 t.Run(test.sql, getTestFunc(ns, test)) 3136 } 3137 } 3138 3139 func TestSelectGlobalTableKingshard(t *testing.T) { 3140 ns, err := preparePlanInfo() 3141 if err != nil { 3142 t.Fatalf("prepare namespace error: %v", err) 3143 } 3144 3145 tests := []SQLTestcase{ 3146 { 3147 db: "db_ks", 3148 sql: "select * from tbl_ks_global_one where name='haha'", 3149 sqls: map[string]map[string][]string{ 3150 "slice-0": { 3151 "db_ks": { 3152 "SELECT * FROM `tbl_ks_global_one` WHERE `name`='haha'", 3153 }, 3154 }, 3155 }, 3156 }, 3157 { 3158 db: "db_ks", 3159 sql: "select * from db_ks.tbl_ks_global_one, tbl_ks_global_two where tbl_ks_global_one.name='haha' and tbl_ks_global_two.name='hehe'", 3160 sqls: map[string]map[string][]string{ 3161 "slice-0": { 3162 "db_ks": { 3163 "SELECT * FROM (`db_ks`.`tbl_ks_global_one`) JOIN `tbl_ks_global_two` WHERE `tbl_ks_global_one`.`name`='haha' AND `tbl_ks_global_two`.`name`='hehe'", 3164 }, 3165 }, 3166 }, 3167 }, 3168 } 3169 3170 for _, test := range tests { 3171 t.Run(test.sql, getTestFunc(ns, test)) 3172 } 3173 } 3174 3175 func TestSelectShardTableWithGlobalTableMycat(t *testing.T) { 3176 ns, err := preparePlanInfo() 3177 if err != nil { 3178 t.Fatalf("prepare namespace error: %v", err) 3179 } 3180 3181 tests := []SQLTestcase{ 3182 { 3183 db: "db_mycat", 3184 sql: "select * from tbl_mycat, tbl_mycat_global_one where tbl_mycat.id = 3 and tbl_mycat_global_one.name='haha'", 3185 sqls: map[string]map[string][]string{ 3186 "slice-1": { 3187 "db_mycat_3": { 3188 "SELECT * FROM (`tbl_mycat`) JOIN `tbl_mycat_global_one` WHERE `tbl_mycat`.`id`=3 AND `tbl_mycat_global_one`.`name`='haha'", 3189 }, 3190 }, 3191 }, 3192 }, 3193 { 3194 db: "db_mycat", 3195 sql: "select * from db_mycat.tbl_mycat, db_mycat.tbl_mycat_global_one where db_mycat.tbl_mycat.id = 3 and db_mycat.tbl_mycat_global_one.name='haha'", 3196 sqls: map[string]map[string][]string{ 3197 "slice-1": { 3198 "db_mycat_3": { 3199 "SELECT * FROM (`db_mycat_3`.`tbl_mycat`) JOIN `db_mycat_3`.`tbl_mycat_global_one` WHERE `db_mycat_3`.`tbl_mycat`.`id`=3 AND `db_mycat_3`.`tbl_mycat_global_one`.`name`='haha'", 3200 }, 3201 }, 3202 }, 3203 }, 3204 { 3205 db: "db_mycat", 3206 sql: "select * from tbl_mycat, tbl_mycat_global_one where tbl_mycat.unshard_col = 3 and tbl_mycat_global_one.name='haha'", 3207 sqls: map[string]map[string][]string{ 3208 "slice-0": { 3209 "db_mycat_0": { 3210 "SELECT * FROM (`tbl_mycat`) JOIN `tbl_mycat_global_one` WHERE `tbl_mycat`.`unshard_col`=3 AND `tbl_mycat_global_one`.`name`='haha'", 3211 }, 3212 "db_mycat_1": { 3213 "SELECT * FROM (`tbl_mycat`) JOIN `tbl_mycat_global_one` WHERE `tbl_mycat`.`unshard_col`=3 AND `tbl_mycat_global_one`.`name`='haha'", 3214 }, 3215 }, 3216 "slice-1": { 3217 "db_mycat_2": { 3218 "SELECT * FROM (`tbl_mycat`) JOIN `tbl_mycat_global_one` WHERE `tbl_mycat`.`unshard_col`=3 AND `tbl_mycat_global_one`.`name`='haha'", 3219 }, 3220 "db_mycat_3": { 3221 "SELECT * FROM (`tbl_mycat`) JOIN `tbl_mycat_global_one` WHERE `tbl_mycat`.`unshard_col`=3 AND `tbl_mycat_global_one`.`name`='haha'", 3222 }, 3223 }, 3224 }, 3225 }, 3226 { 3227 db: "db_mycat", 3228 sql: "select * from tbl_mycat, tbl_mycat_global_one, tbl_mycat_global_two where tbl_mycat.unshard_col = 3 and tbl_mycat_global_one.name='haha' and tbl_mycat_global_two.gender='female'", 3229 sqls: map[string]map[string][]string{ 3230 "slice-0": { 3231 "db_mycat_0": { 3232 "SELECT * FROM ((`tbl_mycat`) JOIN `tbl_mycat_global_one`) JOIN `tbl_mycat_global_two` WHERE `tbl_mycat`.`unshard_col`=3 AND `tbl_mycat_global_one`.`name`='haha' AND `tbl_mycat_global_two`.`gender`='female'", 3233 }, 3234 "db_mycat_1": { 3235 "SELECT * FROM ((`tbl_mycat`) JOIN `tbl_mycat_global_one`) JOIN `tbl_mycat_global_two` WHERE `tbl_mycat`.`unshard_col`=3 AND `tbl_mycat_global_one`.`name`='haha' AND `tbl_mycat_global_two`.`gender`='female'", 3236 }, 3237 }, 3238 "slice-1": { 3239 "db_mycat_2": { 3240 "SELECT * FROM ((`tbl_mycat`) JOIN `tbl_mycat_global_one`) JOIN `tbl_mycat_global_two` WHERE `tbl_mycat`.`unshard_col`=3 AND `tbl_mycat_global_one`.`name`='haha' AND `tbl_mycat_global_two`.`gender`='female'", 3241 }, 3242 "db_mycat_3": { 3243 "SELECT * FROM ((`tbl_mycat`) JOIN `tbl_mycat_global_one`) JOIN `tbl_mycat_global_two` WHERE `tbl_mycat`.`unshard_col`=3 AND `tbl_mycat_global_one`.`name`='haha' AND `tbl_mycat_global_two`.`gender`='female'", 3244 }, 3245 }, 3246 }, 3247 }, 3248 } 3249 3250 for _, test := range tests { 3251 t.Run(test.sql, getTestFunc(ns, test)) 3252 } 3253 } 3254 3255 func TestSelectGlobalTableMycat(t *testing.T) { 3256 ns, err := preparePlanInfo() 3257 if err != nil { 3258 t.Fatalf("prepare namespace error: %v", err) 3259 } 3260 3261 tests := []SQLTestcase{ 3262 { 3263 db: "db_mycat", 3264 sql: "select * from tbl_mycat_global_one where name='haha'", 3265 sqls: map[string]map[string][]string{ 3266 "slice-0": { 3267 "db_mycat_0": { 3268 "SELECT * FROM `tbl_mycat_global_one` WHERE `name`='haha'", 3269 }, 3270 }, 3271 }, 3272 }, 3273 { 3274 db: "db_mycat", 3275 sql: "select * from db_mycat.tbl_mycat_global_one where name='haha'", 3276 sqls: map[string]map[string][]string{ 3277 "slice-0": { 3278 "db_mycat_0": { 3279 "SELECT * FROM `db_mycat_0`.`tbl_mycat_global_one` WHERE `name`='haha'", 3280 }, 3281 }, 3282 }, 3283 }, 3284 { 3285 db: "db_mycat", 3286 sql: "select * from db_mycat.tbl_mycat_global_one where db_mycat.tbl_mycat_global_one.name='haha'", 3287 sqls: map[string]map[string][]string{ 3288 "slice-0": { 3289 "db_mycat_0": { 3290 "SELECT * FROM `db_mycat_0`.`tbl_mycat_global_one` WHERE `db_mycat_0`.`tbl_mycat_global_one`.`name`='haha'", 3291 }, 3292 }, 3293 }, 3294 }, 3295 { 3296 db: "db_mycat", 3297 sql: "select * from db_mycat.tbl_mycat_global_one, tbl_mycat_global_two where tbl_mycat_global_one.name='haha' and tbl_mycat_global_two.name='hehe'", 3298 sqls: map[string]map[string][]string{ 3299 "slice-0": { 3300 "db_mycat_0": { 3301 "SELECT * FROM (`db_mycat_0`.`tbl_mycat_global_one`) JOIN `tbl_mycat_global_two` WHERE `tbl_mycat_global_one`.`name`='haha' AND `tbl_mycat_global_two`.`name`='hehe'", 3302 }, 3303 }, 3304 }, 3305 }, 3306 { 3307 db: "db_mycat", 3308 sql: "select * from db_mycat.tbl_mycat_global_one, db_mycat.tbl_mycat_global_two where db_mycat.tbl_mycat_global_one.name='haha' and db_mycat.tbl_mycat_global_two.name='hehe'", 3309 sqls: map[string]map[string][]string{ 3310 "slice-0": { 3311 "db_mycat_0": { 3312 "SELECT * FROM (`db_mycat_0`.`tbl_mycat_global_one`) JOIN `db_mycat_0`.`tbl_mycat_global_two` WHERE `db_mycat_0`.`tbl_mycat_global_one`.`name`='haha' AND `db_mycat_0`.`tbl_mycat_global_two`.`name`='hehe'", 3313 }, 3314 }, 3315 }, 3316 }, 3317 } 3318 3319 for _, test := range tests { 3320 t.Run(test.sql, getTestFunc(ns, test)) 3321 } 3322 } 3323 3324 func TestSelectMycatGroupByDatabase(t *testing.T) { 3325 ns, err := preparePlanInfo() 3326 if err != nil { 3327 t.Fatalf("prepare namespace error: %v", err) 3328 } 3329 3330 tests := []SQLTestcase{ 3331 { 3332 db: "db_mycat", 3333 sql: "select database(), count(id) from tbl_mycat group by database()", 3334 sqls: map[string]map[string][]string{ 3335 "slice-0": { 3336 "db_mycat_0": { 3337 "SELECT DATABASE(),COUNT(`id`),DATABASE() FROM `tbl_mycat` GROUP BY DATABASE()", 3338 }, 3339 "db_mycat_1": { 3340 "SELECT DATABASE(),COUNT(`id`),DATABASE() FROM `tbl_mycat` GROUP BY DATABASE()", 3341 }, 3342 }, 3343 "slice-1": { 3344 "db_mycat_2": { 3345 "SELECT DATABASE(),COUNT(`id`),DATABASE() FROM `tbl_mycat` GROUP BY DATABASE()", 3346 }, 3347 "db_mycat_3": { 3348 "SELECT DATABASE(),COUNT(`id`),DATABASE() FROM `tbl_mycat` GROUP BY DATABASE()", 3349 }, 3350 }, 3351 }, 3352 }, 3353 { 3354 db: "db_mycat", 3355 sql: "select database(), count(id) from tbl_mycat where database()='db_mycat_1' group by database()", 3356 sqls: map[string]map[string][]string{ 3357 "slice-0": { 3358 "db_mycat_1": { 3359 "SELECT DATABASE(),COUNT(`id`) FROM `tbl_mycat` WHERE DATABASE()='db_mycat_1' GROUP BY DATABASE()", 3360 }, 3361 }, 3362 }, 3363 }, 3364 } 3365 3366 for _, test := range tests { 3367 t.Run(test.sql, getTestFunc(ns, test)) 3368 } 3369 } 3370 3371 func TestSelectMycatOrderByDatabase(t *testing.T) { 3372 ns, err := preparePlanInfo() 3373 if err != nil { 3374 t.Fatalf("prepare namespace error: %v", err) 3375 } 3376 3377 tests := []SQLTestcase{ 3378 { 3379 db: "db_mycat", 3380 sql: "select database(), count(id) from tbl_mycat group by database() order by database()", 3381 sqls: map[string]map[string][]string{ 3382 "slice-0": { 3383 "db_mycat_0": { 3384 "SELECT DATABASE(),COUNT(`id`),DATABASE(),DATABASE() FROM `tbl_mycat` GROUP BY DATABASE() ORDER BY DATABASE()", 3385 }, 3386 "db_mycat_1": { 3387 "SELECT DATABASE(),COUNT(`id`),DATABASE(),DATABASE() FROM `tbl_mycat` GROUP BY DATABASE() ORDER BY DATABASE()", 3388 }, 3389 }, 3390 "slice-1": { 3391 "db_mycat_2": { 3392 "SELECT DATABASE(),COUNT(`id`),DATABASE(),DATABASE() FROM `tbl_mycat` GROUP BY DATABASE() ORDER BY DATABASE()", 3393 }, 3394 "db_mycat_3": { 3395 "SELECT DATABASE(),COUNT(`id`),DATABASE(),DATABASE() FROM `tbl_mycat` GROUP BY DATABASE() ORDER BY DATABASE()", 3396 }, 3397 }, 3398 }, 3399 }, 3400 { 3401 db: "db_mycat", 3402 sql: "select database(), count(id) from tbl_mycat where database() in ('db_mycat_1','db_mycat_2') group by database() order by database()", 3403 sqls: map[string]map[string][]string{ 3404 "slice-0": { 3405 "db_mycat_0": { 3406 "SELECT DATABASE(),COUNT(`id`),DATABASE(),DATABASE() FROM `tbl_mycat` WHERE DATABASE() IN ('db_mycat_1','db_mycat_2') GROUP BY DATABASE() ORDER BY DATABASE()", 3407 }, 3408 "db_mycat_1": { 3409 "SELECT DATABASE(),COUNT(`id`),DATABASE(),DATABASE() FROM `tbl_mycat` WHERE DATABASE() IN ('db_mycat_1','db_mycat_2') GROUP BY DATABASE() ORDER BY DATABASE()", 3410 }, 3411 }, 3412 "slice-1": { 3413 "db_mycat_2": { 3414 "SELECT DATABASE(),COUNT(`id`),DATABASE(),DATABASE() FROM `tbl_mycat` WHERE DATABASE() IN ('db_mycat_1','db_mycat_2') GROUP BY DATABASE() ORDER BY DATABASE()", 3415 }, 3416 "db_mycat_3": { 3417 "SELECT DATABASE(),COUNT(`id`),DATABASE(),DATABASE() FROM `tbl_mycat` WHERE DATABASE() IN ('db_mycat_1','db_mycat_2') GROUP BY DATABASE() ORDER BY DATABASE()", 3418 }, 3419 }, 3420 }, 3421 }, 3422 } 3423 3424 for _, test := range tests { 3425 t.Run(test.sql, getTestFunc(ns, test)) 3426 } 3427 } 3428 3429 func TestSelectForceIndexDatabase(t *testing.T) { 3430 ns, err := preparePlanInfo() 3431 if err != nil { 3432 t.Fatalf("prepare namespace error: %v", err) 3433 } 3434 3435 tests := []SQLTestcase{ 3436 { 3437 db: "db_mycat", 3438 sql: "select * from tbl_mycat force index(id, name) where id > 100 and name = `zhangsan`", 3439 sqls: map[string]map[string][]string{ 3440 "slice-0": { 3441 "db_mycat_0": { 3442 "SELECT * FROM `tbl_mycat` FORCE INDEX (`id`, `name`) WHERE `id`>100 AND `name`=`zhangsan`", 3443 }, 3444 "db_mycat_1": { 3445 "SELECT * FROM `tbl_mycat` FORCE INDEX (`id`, `name`) WHERE `id`>100 AND `name`=`zhangsan`", 3446 }, 3447 }, 3448 "slice-1": { 3449 "db_mycat_2": { 3450 "SELECT * FROM `tbl_mycat` FORCE INDEX (`id`, `name`) WHERE `id`>100 AND `name`=`zhangsan`", 3451 }, 3452 "db_mycat_3": { 3453 "SELECT * FROM `tbl_mycat` FORCE INDEX (`id`, `name`) WHERE `id`>100 AND `name`=`zhangsan`", 3454 }, 3455 }, 3456 }, 3457 }, 3458 } 3459 3460 for _, test := range tests { 3461 t.Run(test.sql, getTestFunc(ns, test)) 3462 } 3463 } 3464 3465 func TestSelectOrderByAliasColumn(t *testing.T) { 3466 ns, err := preparePlanInfo() 3467 if err != nil { 3468 t.Fatalf("prepare namespace error: %v", err) 3469 } 3470 3471 tests := []SQLTestcase{ 3472 { 3473 db: "db_mycat", 3474 sql: "select count(id) as a, uid from tbl_mycat where uid = 2 order by a", 3475 sqls: map[string]map[string][]string{ 3476 "slice-0": { 3477 "db_mycat_0": { 3478 "SELECT COUNT(`id`) AS `a`,`uid` FROM `tbl_mycat` WHERE `uid`=2 ORDER BY `a`", 3479 }, 3480 "db_mycat_1": { 3481 "SELECT COUNT(`id`) AS `a`,`uid` FROM `tbl_mycat` WHERE `uid`=2 ORDER BY `a`", 3482 }, 3483 }, 3484 "slice-1": { 3485 "db_mycat_2": { 3486 "SELECT COUNT(`id`) AS `a`,`uid` FROM `tbl_mycat` WHERE `uid`=2 ORDER BY `a`", 3487 }, 3488 "db_mycat_3": { 3489 "SELECT COUNT(`id`) AS `a`,`uid` FROM `tbl_mycat` WHERE `uid`=2 ORDER BY `a`", 3490 }, 3491 }, 3492 }, 3493 }, 3494 { 3495 db: "db_mycat", 3496 sql: "select id as a, uid from tbl_mycat where uid = 2 order by id", 3497 sqls: map[string]map[string][]string{ 3498 "slice-0": { 3499 "db_mycat_0": { 3500 "SELECT `id` AS `a`,`uid` FROM `tbl_mycat` WHERE `uid`=2 ORDER BY `id`", 3501 }, 3502 "db_mycat_1": { 3503 "SELECT `id` AS `a`,`uid` FROM `tbl_mycat` WHERE `uid`=2 ORDER BY `id`", 3504 }, 3505 }, 3506 "slice-1": { 3507 "db_mycat_2": { 3508 "SELECT `id` AS `a`,`uid` FROM `tbl_mycat` WHERE `uid`=2 ORDER BY `id`", 3509 }, 3510 "db_mycat_3": { 3511 "SELECT `id` AS `a`,`uid` FROM `tbl_mycat` WHERE `uid`=2 ORDER BY `id`", 3512 }, 3513 }, 3514 }, 3515 }, 3516 } 3517 3518 for _, test := range tests { 3519 t.Run(test.sql, getTestFunc(ns, test)) 3520 } 3521 } 3522 3523 func TestSelectGroupByAliasColumn(t *testing.T) { 3524 ns, err := preparePlanInfo() 3525 if err != nil { 3526 t.Fatalf("prepare namespace error: %v", err) 3527 } 3528 3529 tests := []SQLTestcase{ 3530 { 3531 db: "db_mycat", 3532 sql: "select count(id) as a, uid from tbl_mycat where uid = 2 group by a", 3533 sqls: map[string]map[string][]string{ 3534 "slice-0": { 3535 "db_mycat_0": { 3536 "SELECT COUNT(`id`) AS `a`,`uid` FROM `tbl_mycat` WHERE `uid`=2 GROUP BY `a`", 3537 }, 3538 "db_mycat_1": { 3539 "SELECT COUNT(`id`) AS `a`,`uid` FROM `tbl_mycat` WHERE `uid`=2 GROUP BY `a`", 3540 }, 3541 }, 3542 "slice-1": { 3543 "db_mycat_2": { 3544 "SELECT COUNT(`id`) AS `a`,`uid` FROM `tbl_mycat` WHERE `uid`=2 GROUP BY `a`", 3545 }, 3546 "db_mycat_3": { 3547 "SELECT COUNT(`id`) AS `a`,`uid` FROM `tbl_mycat` WHERE `uid`=2 GROUP BY `a`", 3548 }, 3549 }, 3550 }, 3551 }, 3552 { 3553 db: "db_mycat", 3554 sql: "select id as a, uid from tbl_mycat where uid = 2 group by id", 3555 sqls: map[string]map[string][]string{ 3556 "slice-0": { 3557 "db_mycat_0": { 3558 "SELECT `id` AS `a`,`uid` FROM `tbl_mycat` WHERE `uid`=2 GROUP BY `id`", 3559 }, 3560 "db_mycat_1": { 3561 "SELECT `id` AS `a`,`uid` FROM `tbl_mycat` WHERE `uid`=2 GROUP BY `id`", 3562 }, 3563 }, 3564 "slice-1": { 3565 "db_mycat_2": { 3566 "SELECT `id` AS `a`,`uid` FROM `tbl_mycat` WHERE `uid`=2 GROUP BY `id`", 3567 }, 3568 "db_mycat_3": { 3569 "SELECT `id` AS `a`,`uid` FROM `tbl_mycat` WHERE `uid`=2 GROUP BY `id`", 3570 }, 3571 }, 3572 }, 3573 }, 3574 } 3575 3576 for _, test := range tests { 3577 t.Run(test.sql, getTestFunc(ns, test)) 3578 } 3579 } 3580 3581 func prepareShardKingshardRouter() (*router.Router, error) { 3582 nsStr := ` 3583 { 3584 "name": "gaea_namespace_1", 3585 "online": true, 3586 "read_only": true, 3587 "allowed_dbs": { 3588 "test": true 3589 }, 3590 "default_phy_dbs": { 3591 "test": "db_mycat_0" 3592 }, 3593 "slices": [ 3594 { 3595 "name": "slice-0", 3596 "user_name": "root", 3597 "password": "root", 3598 "master": "127.0.0.1:3306", 3599 "capacity": 64, 3600 "max_capacity": 128, 3601 "idle_timeout": 3600 3602 }, 3603 { 3604 "name": "slice-1", 3605 "user_name": "root", 3606 "password": "root", 3607 "master": "127.0.0.1:3307", 3608 "capacity": 64, 3609 "max_capacity": 128, 3610 "idle_timeout": 3600 3611 } 3612 ], 3613 "shard_rules": [ 3614 { 3615 "db": "test", 3616 "table": "tbl_ks", 3617 "type": "hash", 3618 "key": "id", 3619 "locations": [ 3620 2, 3621 2 3622 ], 3623 "slices": [ 3624 "slice-0", 3625 "slice-1" 3626 ] 3627 }, 3628 { 3629 "db": "test", 3630 "table": "test_hash_1", 3631 "type": "hash", 3632 "key": "id", 3633 "locations": [ 3634 2, 3635 2 3636 ], 3637 "slices": [ 3638 "slice-0", 3639 "slice-1" 3640 ] 3641 }, 3642 { 3643 "db": "test", 3644 "table": "tbl_ks_child", 3645 "type": "linked", 3646 "parent_table": "tbl_ks", 3647 "key": "id" 3648 } 3649 ], 3650 "users": [ 3651 { 3652 "user_name": "test_shard_hash", 3653 "password": "test_shard_hash", 3654 "namespace": "gaea_namespace_1", 3655 "rw_flag": 2, 3656 "rw_split": 1 3657 } 3658 ], 3659 "default_slice": "slice-0" 3660 } 3661 ` 3662 3663 nsModel, err := createNamespace(nsStr) 3664 if err != nil { 3665 return nil, err 3666 } 3667 3668 return createRouter(nsModel) 3669 }