Debian Codegraph @

mysql-test/suite/innodb/r/innodb-index_ucs2.result

raw
1 create table t1(a int not null, b int, c char(10), d varchar(20), primary key (a)) engine = innodb default charset=ucs2;
2 Warnings:
3 Warning 1287 'ucs2' is deprecated and will be removed in a future release. Please use utf8mb4 instead
4 insert into t1 values (1,1,'ab','ab'),(2,2,'ac','ac'),(3,2,'ad','ad'),(4,4,'afe','afe');
5 commit;
6 alter table t1 add unique index (b);
7 ERROR 23000: Duplicate entry '2' for key 't1.b'
8 insert into t1 values(8,9,'fff','fff');
9 select * from t1;
10 a b c d
11 1 1 ab ab
12 2 2 ac ac
13 3 2 ad ad
14 4 4 afe afe
15 8 9 fff fff
16 show create table t1;
17 Table Create Table
18 t1 CREATE TABLE `t1` (
19 `a` int NOT NULL,
20 `b` int DEFAULT NULL,
21 `c` char(10) DEFAULT NULL,
22 `d` varchar(20) DEFAULT NULL,
23 PRIMARY KEY (`a`)
24 ) ENGINE=InnoDB DEFAULT CHARSET=ucs2
25 alter table t1 add index (b);
26 insert into t1 values(10,10,'kkk','iii');
27 select * from t1;
28 a b c d
29 1 1 ab ab
30 2 2 ac ac
31 3 2 ad ad
32 4 4 afe afe
33 8 9 fff fff
34 10 10 kkk iii
35 select * from t1 force index(b) order by b;
36 a b c d
37 1 1 ab ab
38 2 2 ac ac
39 3 2 ad ad
40 4 4 afe afe
41 8 9 fff fff
42 10 10 kkk iii
43 explain select * from t1 force index(b) order by b;
44 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
45 1 SIMPLE t1 NULL index NULL b 5 NULL 6 100.00 NULL
46 Warnings:
47 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` FORCE INDEX (`b`) order by `test`.`t1`.`b`
48 show create table t1;
49 Table Create Table
50 t1 CREATE TABLE `t1` (
51 `a` int NOT NULL,
52 `b` int DEFAULT NULL,
53 `c` char(10) DEFAULT NULL,
54 `d` varchar(20) DEFAULT NULL,
55 PRIMARY KEY (`a`),
56 KEY `b` (`b`)
57 ) ENGINE=InnoDB DEFAULT CHARSET=ucs2
58 alter table t1 add unique index (c), add index (d);
59 insert into t1 values(11,11,'aaa','mmm');
60 select * from t1;
61 a b c d
62 1 1 ab ab
63 2 2 ac ac
64 3 2 ad ad
65 4 4 afe afe
66 8 9 fff fff
67 10 10 kkk iii
68 11 11 aaa mmm
69 select * from t1 force index(b) order by b;
70 a b c d
71 1 1 ab ab
72 2 2 ac ac
73 3 2 ad ad
74 4 4 afe afe
75 8 9 fff fff
76 10 10 kkk iii
77 11 11 aaa mmm
78 select * from t1 force index(c) order by c;
79 a b c d
80 11 11 aaa mmm
81 1 1 ab ab
82 2 2 ac ac
83 3 2 ad ad
84 4 4 afe afe
85 8 9 fff fff
86 10 10 kkk iii
87 select * from t1 force index(d) order by d;
88 a b c d
89 1 1 ab ab
90 2 2 ac ac
91 3 2 ad ad
92 4 4 afe afe
93 8 9 fff fff
94 10 10 kkk iii
95 11 11 aaa mmm
96 explain select * from t1 force index(b) order by b;
97 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
98 1 SIMPLE t1 NULL index NULL b 5 NULL 7 100.00 NULL
99 Warnings:
100 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` FORCE INDEX (`b`) order by `test`.`t1`.`b`
101 explain select * from t1 force index(c) order by c;
102 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
103 1 SIMPLE t1 NULL index NULL c 21 NULL 7 100.00 NULL
104 Warnings:
105 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` FORCE INDEX (`c`) order by `test`.`t1`.`c`
106 explain select * from t1 force index(d) order by d;
107 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
108 1 SIMPLE t1 NULL index NULL d 43 NULL 7 100.00 NULL
109 Warnings:
110 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` FORCE INDEX (`d`) order by `test`.`t1`.`d`
111 show create table t1;
112 Table Create Table
113 t1 CREATE TABLE `t1` (
114 `a` int NOT NULL,
115 `b` int DEFAULT NULL,
116 `c` char(10) DEFAULT NULL,
117 `d` varchar(20) DEFAULT NULL,
118 PRIMARY KEY (`a`),
119 UNIQUE KEY `c` (`c`),
120 KEY `b` (`b`),
121 KEY `d` (`d`)
122 ) ENGINE=InnoDB DEFAULT CHARSET=ucs2
123 check table t1;
124 Table Op Msg_type Msg_text
125 test.t1 check status OK
126 drop table t1;