github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/ddl/index_metadata.sql (about) 1 drop database if exists db6; 2 create database db6; 3 use db6; 4 5 drop table if exists t1; 6 create table t1( 7 deptno int unsigned, 8 dname varchar(15), 9 loc varchar(50), 10 unique key(deptno) 11 ); 12 select 13 `idx`.`name`, 14 `idx`.`type`, 15 `idx`.`name`, 16 `idx`.`is_visible`, 17 `idx`.`hidden`, 18 `idx`.`comment`, 19 `tbl`.`relname`, 20 `idx`.`column_name`, 21 `idx`.`ordinal_position`, 22 `idx`.`options` 23 from 24 `mo_catalog`.`mo_indexes` `idx` join `mo_catalog`.`mo_tables` `tbl` on (`idx`.`table_id` = `tbl`.`rel_id`) 25 where `tbl`.`relname` = 't1' and `tbl`.`reldatabase` = 'db6' and `idx`.`column_name` NOT LIKE '__mo_alias_%'; 26 27 select 28 table_schema, 29 table_name, 30 non_unique, 31 index_schema, 32 index_name, 33 seq_in_index, 34 column_name, 35 index_comment 36 from 37 information_schema.statistics where table_schema = 'db6' and table_name = 't1'; 38 39 40 drop table if exists t2; 41 create table t2( 42 empno int unsigned auto_increment, 43 ename varchar(15), 44 job varchar(10), 45 mgr int unsigned , 46 hiredate date, 47 sal decimal(7,2), 48 comm decimal(7,2), 49 deptno int unsigned, 50 primary key(empno) 51 ); 52 select 53 `idx`.`name`, 54 `idx`.`type`, 55 `idx`.`name`, 56 `idx`.`is_visible`, 57 `idx`.`hidden`, 58 `idx`.`comment`, 59 `tbl`.`relname`, 60 `idx`.`column_name`, 61 `idx`.`ordinal_position`, 62 `idx`.`options` 63 from 64 `mo_catalog`.`mo_indexes` `idx` join `mo_catalog`.`mo_tables` `tbl` on (`idx`.`table_id` = `tbl`.`rel_id`) 65 where `tbl`.`relname` = 't2' and `tbl`.`reldatabase` = 'db6' and `idx`.`column_name` NOT LIKE '__mo_alias_%'; 66 67 select 68 table_schema, 69 table_name, 70 non_unique, 71 index_schema, 72 index_name, 73 seq_in_index, 74 column_name, 75 index_comment 76 from 77 information_schema.statistics where table_schema = 'db6' and table_name = 't2'; 78 79 drop table if exists t3; 80 create table t3( 81 empno int unsigned, 82 ename varchar(15), 83 job varchar(10), 84 mgr int unsigned , 85 hiredate date, 86 sal decimal(7,2), 87 comm decimal(7,2), 88 deptno int unsigned, 89 unique key(empno, ename) 90 ); 91 select 92 `idx`.`name`, 93 `idx`.`type`, 94 `idx`.`name`, 95 `idx`.`is_visible`, 96 `idx`.`hidden`, 97 `idx`.`comment`, 98 `tbl`.`relname`, 99 `idx`.`column_name`, 100 `idx`.`ordinal_position`, 101 `idx`.`options` 102 from 103 `mo_catalog`.`mo_indexes` `idx` join `mo_catalog`.`mo_tables` `tbl` on (`idx`.`table_id` = `tbl`.`rel_id`) 104 where `tbl`.`relname` = 't3' and `tbl`.`reldatabase` = 'db6' and `idx`.`column_name` NOT LIKE '__mo_alias_%'; 105 106 select 107 table_schema, 108 table_name, 109 non_unique, 110 index_schema, 111 index_name, 112 seq_in_index, 113 column_name, 114 index_comment 115 from 116 information_schema.statistics where table_schema = 'db6' and table_name = 't3'; 117 118 drop table if exists t4; 119 create table t4( 120 empno int unsigned, 121 ename varchar(15), 122 job varchar(10), 123 mgr int unsigned , 124 hiredate date, 125 sal decimal(7,2), 126 comm decimal(7,2), 127 deptno int unsigned, 128 index(empno, ename, job) 129 ); 130 select 131 `idx`.`name`, 132 `idx`.`type`, 133 `idx`.`name`, 134 `idx`.`is_visible`, 135 `idx`.`hidden`, 136 `idx`.`comment`, 137 `tbl`.`relname`, 138 `idx`.`column_name`, 139 `idx`.`ordinal_position`, 140 `idx`.`options` 141 from 142 `mo_catalog`.`mo_indexes` `idx` join `mo_catalog`.`mo_tables` `tbl` on (`idx`.`table_id` = `tbl`.`rel_id`) 143 where `tbl`.`relname` = 't4' and `tbl`.`reldatabase` = 'db6' and `idx`.`column_name` NOT LIKE '__mo_alias_%'; 144 145 select 146 table_schema, 147 table_name, 148 non_unique, 149 index_schema, 150 index_name, 151 seq_in_index, 152 column_name, 153 index_comment 154 from 155 information_schema.statistics where table_schema = 'db6' and table_name = 't4'; 156 157 drop table if exists t5; 158 create table t5( 159 empno int unsigned, 160 ename varchar(15), 161 job varchar(10), 162 mgr int unsigned , 163 hiredate date, 164 sal decimal(7,2), 165 comm decimal(7,2), 166 deptno int unsigned, 167 primary key(empno, ename) 168 ); 169 select 170 `idx`.`name`, 171 `idx`.`type`, 172 `idx`.`name`, 173 `idx`.`is_visible`, 174 `idx`.`hidden`, 175 `idx`.`comment`, 176 `tbl`.`relname`, 177 `idx`.`column_name`, 178 `idx`.`ordinal_position`, 179 `idx`.`options` 180 from 181 `mo_catalog`.`mo_indexes` `idx` join `mo_catalog`.`mo_tables` `tbl` on (`idx`.`table_id` = `tbl`.`rel_id`) 182 where `tbl`.`relname` = 't5' and `tbl`.`reldatabase` = 'db6' and `idx`.`column_name` NOT LIKE '__mo_alias_%'; 183 184 select 185 table_schema, 186 table_name, 187 non_unique, 188 index_schema, 189 index_name, 190 seq_in_index, 191 column_name, 192 index_comment 193 from 194 information_schema.statistics where table_schema = 'db6' and table_name = 't5'; 195 196 drop table if exists t6; 197 create table t6( 198 empno int unsigned, 199 ename varchar(15), 200 job varchar(10), 201 mgr int unsigned , 202 hiredate date, 203 sal decimal(7,2), 204 comm decimal(7,2), 205 deptno int unsigned 206 ); 207 select 208 `idx`.`name`, 209 `idx`.`type`, 210 `idx`.`name`, 211 `idx`.`is_visible`, 212 `idx`.`hidden`, 213 `idx`.`comment`, 214 `tbl`.`relname`, 215 `idx`.`column_name`, 216 `idx`.`ordinal_position`, 217 `idx`.`options` 218 from 219 `mo_catalog`.`mo_indexes` `idx` join `mo_catalog`.`mo_tables` `tbl` on (`idx`.`table_id` = `tbl`.`rel_id`) 220 where `tbl`.`relname` = 't6' and `tbl`.`reldatabase` = 'db6' and `idx`.`column_name` NOT LIKE '__mo_alias_%'; 221 222 select 223 table_schema, 224 table_name, 225 non_unique, 226 index_schema, 227 index_name, 228 seq_in_index, 229 column_name, 230 index_comment 231 from 232 information_schema.statistics where table_schema = 'db6' and table_name = 't6'; 233 234 drop table if exists t7; 235 create table t7( 236 col1 int unsigned, 237 col2 varchar(15), 238 col3 varchar(10), 239 col4 int unsigned, 240 col5 date, 241 col6 decimal(7,2), 242 col7 decimal(7,2), 243 col8 int unsigned, 244 unique index(col1,col2), 245 unique index(col3,col6) 246 ); 247 select 248 `idx`.`name`, 249 `idx`.`type`, 250 `idx`.`name`, 251 `idx`.`is_visible`, 252 `idx`.`hidden`, 253 `idx`.`comment`, 254 `tbl`.`relname`, 255 `idx`.`column_name`, 256 `idx`.`ordinal_position`, 257 `idx`.`options` 258 from 259 `mo_catalog`.`mo_indexes` `idx` join `mo_catalog`.`mo_tables` `tbl` on (`idx`.`table_id` = `tbl`.`rel_id`) 260 where `tbl`.`relname` = 't7' and `tbl`.`reldatabase` = 'db6' and `idx`.`column_name` NOT LIKE '__mo_alias_%'; 261 262 select 263 table_schema, 264 table_name, 265 non_unique, 266 index_schema, 267 index_name, 268 seq_in_index, 269 column_name, 270 index_comment 271 from 272 information_schema.statistics where table_schema = 'db6' and table_name = 't7'; 273 274 drop table if exists t8; 275 create table t8( 276 empno int unsigned primary key, 277 ename varchar(15), 278 job varchar(10), 279 mgr int unsigned , 280 hiredate date, 281 sal decimal(7,2), 282 comm decimal(7,2), 283 deptno int unsigned, 284 unique key(empno, ename) 285 ); 286 select 287 `idx`.`name`, 288 `idx`.`type`, 289 `idx`.`name`, 290 `idx`.`is_visible`, 291 `idx`.`hidden`, 292 `idx`.`comment`, 293 `tbl`.`relname`, 294 `idx`.`column_name`, 295 `idx`.`ordinal_position`, 296 `idx`.`options` 297 from 298 `mo_catalog`.`mo_indexes` `idx` join `mo_catalog`.`mo_tables` `tbl` on (`idx`.`table_id` = `tbl`.`rel_id`) 299 where `tbl`.`relname` = 't8' and `tbl`.`reldatabase` = 'db6' and `idx`.`column_name` NOT LIKE '__mo_alias_%'; 300 301 select 302 table_schema, 303 table_name, 304 non_unique, 305 index_schema, 306 index_name, 307 seq_in_index, 308 column_name, 309 index_comment 310 from 311 information_schema.statistics where table_schema = 'db6' and table_name = 't8'; 312 313 drop table if exists t9; 314 create table t9(id int PRIMARY KEY,name VARCHAR(255),age int); 315 insert into t9 values(1,"Abby", 24); 316 insert into t9 values(2,"Bob", 25); 317 insert into t9 values(3,"Carol", 23); 318 insert into t9 values(4,"Dora", 29); 319 select 320 `idx`.`name`, 321 `idx`.`type`, 322 `idx`.`name`, 323 `idx`.`is_visible`, 324 `idx`.`hidden`, 325 `idx`.`comment`, 326 `tbl`.`relname`, 327 `idx`.`column_name`, 328 `idx`.`ordinal_position`, 329 `idx`.`options` 330 from 331 `mo_catalog`.`mo_indexes` `idx` join `mo_catalog`.`mo_tables` `tbl` on (`idx`.`table_id` = `tbl`.`rel_id`) 332 where `tbl`.`relname` = 't9' and `tbl`.`reldatabase` = 'db6' and `idx`.`column_name` NOT LIKE '__mo_alias_%'; 333 create unique index idx on t9(name); 334 select 335 `idx`.`name`, 336 `idx`.`type`, 337 `idx`.`name`, 338 `idx`.`is_visible`, 339 `idx`.`hidden`, 340 `idx`.`comment`, 341 `tbl`.`relname`, 342 `idx`.`column_name`, 343 `idx`.`ordinal_position`, 344 `idx`.`options` 345 from 346 `mo_catalog`.`mo_indexes` `idx` join `mo_catalog`.`mo_tables` `tbl` on (`idx`.`table_id` = `tbl`.`rel_id`) 347 where `tbl`.`relname` = 't9' and `idx`.`column_name` NOT LIKE '__mo_alias_%'; 348 349 select 350 table_schema, 351 table_name, 352 non_unique, 353 index_schema, 354 index_name, 355 seq_in_index, 356 column_name, 357 index_comment 358 from 359 information_schema.statistics where table_schema = 'db6' and table_name = 't9'; 360 361 select * from t9; 362 drop table t9; 363 364 365 drop table if exists t10; 366 create table t10 ( 367 col1 bigint primary key, 368 col2 varchar(25), 369 col3 float, 370 col4 varchar(50) 371 ); 372 select 373 `idx`.`name`, 374 `idx`.`type`, 375 `idx`.`name`, 376 `idx`.`is_visible`, 377 `idx`.`hidden`, 378 `idx`.`comment`, 379 `tbl`.`relname`, 380 `idx`.`column_name`, 381 `idx`.`ordinal_position`, 382 `idx`.`options` 383 from 384 `mo_catalog`.`mo_indexes` `idx` join `mo_catalog`.`mo_tables` `tbl` on (`idx`.`table_id` = `tbl`.`rel_id`) 385 where `tbl`.`relname` = 't10' and `tbl`.`reldatabase` = 'db6' and `idx`.`column_name` NOT LIKE '__mo_alias_%'; 386 create unique index idx on t10(col2) comment 'create varchar index'; 387 select 388 `idx`.`name`, 389 `idx`.`type`, 390 `idx`.`name`, 391 `idx`.`is_visible`, 392 `idx`.`hidden`, 393 `idx`.`comment`, 394 `tbl`.`relname`, 395 `idx`.`column_name`, 396 `idx`.`ordinal_position`, 397 `idx`.`options` 398 from 399 `mo_catalog`.`mo_indexes` `idx` join `mo_catalog`.`mo_tables` `tbl` on (`idx`.`table_id` = `tbl`.`rel_id`) 400 where `tbl`.`relname` = 't10' and `tbl`.`reldatabase` = 'db6' and `idx`.`column_name` NOT LIKE '__mo_alias_%'; 401 402 select 403 table_schema, 404 table_name, 405 non_unique, 406 index_schema, 407 index_name, 408 seq_in_index, 409 column_name, 410 index_comment 411 from 412 information_schema.statistics where table_schema = 'db6' and table_name = 't10'; 413 414 insert into t10 values(1,"Abby", 24,'zbcvdf'); 415 insert into t10 values(2,"Bob", 25,'zbcvdf'); 416 insert into t10 values(3,"Carol", 23,'zbcvdf'); 417 insert into t10 values(4,"Dora", 29,'zbcvdf'); 418 select * from t10; 419 drop table t10; 420 421 drop table if exists t11; 422 create table t11(a int, b int,c varchar(20)); 423 select 424 `idx`.`name`, 425 `idx`.`type`, 426 `idx`.`name`, 427 `idx`.`is_visible`, 428 `idx`.`hidden`, 429 `idx`.`comment`, 430 `tbl`.`relname`, 431 `idx`.`column_name`, 432 `idx`.`ordinal_position`, 433 `idx`.`options` 434 from 435 `mo_catalog`.`mo_indexes` `idx` join `mo_catalog`.`mo_tables` `tbl` on (`idx`.`table_id` = `tbl`.`rel_id`) 436 where `tbl`.`relname` = 't11' and `tbl`.`reldatabase` = 'db6' and `idx`.`column_name` NOT LIKE '__mo_alias_%'; 437 create index x11 ON t11(a) comment 'xxxxxxx'; 438 create index x12 ON t11(b, c) comment 'yyyyyyyyy'; 439 select 440 `idx`.`name`, 441 `idx`.`type`, 442 `idx`.`name`, 443 `idx`.`is_visible`, 444 `idx`.`hidden`, 445 `idx`.`comment`, 446 `tbl`.`relname`, 447 `idx`.`column_name`, 448 `idx`.`ordinal_position`, 449 `idx`.`options` 450 from 451 `mo_catalog`.`mo_indexes` `idx` join `mo_catalog`.`mo_tables` `tbl` on (`idx`.`table_id` = `tbl`.`rel_id`) 452 where `tbl`.`relname` = 't11' and `tbl`.`reldatabase` = 'db6' and `idx`.`column_name` NOT LIKE '__mo_alias_%'; 453 454 select 455 table_schema, 456 table_name, 457 non_unique, 458 index_schema, 459 index_name, 460 seq_in_index, 461 column_name, 462 index_comment 463 from 464 information_schema.statistics where table_schema = 'db6' and table_name = 't11'; 465 466 drop index x11 on t11; 467 drop index x12 on t11; 468 select 469 `idx`.`name`, 470 `idx`.`type`, 471 `idx`.`name`, 472 `idx`.`is_visible`, 473 `idx`.`hidden`, 474 `idx`.`comment`, 475 `tbl`.`relname`, 476 `idx`.`column_name`, 477 `idx`.`ordinal_position`, 478 `idx`.`options` 479 from 480 `mo_catalog`.`mo_indexes` `idx` join `mo_catalog`.`mo_tables` `tbl` on (`idx`.`table_id` = `tbl`.`rel_id`) 481 where `tbl`.`relname` = 't11' and `tbl`.`reldatabase` = 'db6' and `idx`.`column_name` NOT LIKE '__mo_alias_%'; 482 drop table t11; 483 484 select 485 table_schema, 486 table_name, 487 non_unique, 488 index_schema, 489 index_name, 490 seq_in_index, 491 column_name, 492 index_comment 493 from 494 information_schema.statistics where table_schema = 'db6' and table_name = 't11'; 495 496 drop table if exists t12; 497 create table t12(a int, b int,c varchar(20), primary key(a)); 498 create index idx_1 on t12(a, b) comment 'xxxxxxx'; 499 select 500 `idx`.`name`, 501 `idx`.`type`, 502 `idx`.`name`, 503 `idx`.`is_visible`, 504 `idx`.`hidden`, 505 `idx`.`comment`, 506 `tbl`.`relname`, 507 `idx`.`column_name`, 508 `idx`.`ordinal_position`, 509 `idx`.`options` 510 from 511 `mo_catalog`.`mo_indexes` `idx` join `mo_catalog`.`mo_tables` `tbl` on (`idx`.`table_id` = `tbl`.`rel_id`) 512 where `tbl`.`relname` = 't12' and `tbl`.`reldatabase` = 'db6' and `idx`.`column_name` NOT LIKE '__mo_alias_%'; 513 514 select 515 table_schema, 516 table_name, 517 non_unique, 518 index_schema, 519 index_name, 520 seq_in_index, 521 column_name, 522 index_comment 523 from 524 information_schema.statistics where table_schema = 'db6' and table_name = 't12'; 525 526 create index idx_1 on t12(a, b); 527 select 528 `idx`.`name`, 529 `idx`.`type`, 530 `idx`.`name`, 531 `idx`.`is_visible`, 532 `idx`.`hidden`, 533 `idx`.`comment`, 534 `tbl`.`relname`, 535 `idx`.`column_name`, 536 `idx`.`ordinal_position`, 537 `idx`.`options` 538 from 539 `mo_catalog`.`mo_indexes` `idx` join `mo_catalog`.`mo_tables` `tbl` on (`idx`.`table_id` = `tbl`.`rel_id`) 540 where `tbl`.`relname` = 't12' and `tbl`.`reldatabase` = 'db6' and `idx`.`column_name` NOT LIKE '__mo_alias_%'; 541 542 select 543 table_schema, 544 table_name, 545 non_unique, 546 index_schema, 547 index_name, 548 seq_in_index, 549 column_name, 550 index_comment 551 from 552 information_schema.statistics where table_schema = 'db6' and table_name = 't12'; 553 554 drop index idx_1 on t12; 555 select 556 `idx`.`name`, 557 `idx`.`type`, 558 `idx`.`name`, 559 `idx`.`is_visible`, 560 `idx`.`hidden`, 561 `idx`.`comment`, 562 `tbl`.`relname`, 563 `idx`.`column_name`, 564 `idx`.`ordinal_position`, 565 `idx`.`options` 566 from 567 `mo_catalog`.`mo_indexes` `idx` join `mo_catalog`.`mo_tables` `tbl` on (`idx`.`table_id` = `tbl`.`rel_id`) 568 where `tbl`.`relname` = 't12' and `tbl`.`reldatabase` = 'db6' and `idx`.`column_name` NOT LIKE '__mo_alias_%'; 569 570 select 571 table_schema, 572 table_name, 573 non_unique, 574 index_schema, 575 index_name, 576 seq_in_index, 577 column_name, 578 index_comment 579 from 580 information_schema.statistics where table_schema = 'db6' and table_name = 't12'; 581 582 drop index idx_1 on t12; 583 drop table t12; 584 drop database db6;