Debian Codegraph @

mysql-test/suite/innodb/t/innodb_bug51378.test

raw
1 # This is the test for bug 51378. Unique index created
2 # through "create index" and "alter table add unique index"
3 # interfaces should not be treated as primary index if indexed
4 # columns contain one or more column prefix(es) (only prefix/part of
5 # the column is indexed)
6 # On the other hand, if there is a unique index covers all
7 # columns of a table, and they are non-null columns, and
8 # full length of the column are indexed, then this index
9 # will be created as primary index
10 # Following queries test various scenario, no mismatch
11 # error message should be printed.
12
13 # Create a table contains a BLOB column
14 create table bug51378 (
15 col1 int not null,
16 col2 blob not null,
17 col3 time not null) engine = innodb;
18
19 # Create following unique indexes on 'col1' and 'col2(31)'
20 # of the table, the index should not be treated as primary
21 # key because it indexes only first 31 bytes of col2.
22 # Thus it contains "column prefix", and will not be
23 # upgraded to primary index.
24 # There should not be mismatch message printed in the
25 # errorlog
26 create unique index idx on bug51378(col1, col2(31));
27
28 alter table bug51378 add unique index idx2(col1, col2(31));
29
30 # Unique index on 'col1' and 'col3' will be created as primary index,
31 # since the index does not contain column prefix
32 create unique index idx3 on bug51378(col1, col3);
33
34 # Show create table would show idx3 created as unique index, internally,
35 # idx3 is treated as primary index both by MySQL and Innodb
36 SHOW CREATE TABLE bug51378;
37
38 # "GEN_CLUST_INDEX" will be re-created as default primary index
39 # after idx3 is dropped
40 drop index idx3 on bug51378;
41
42 SHOW CREATE TABLE bug51378;
43
44 # Or we can add the primary key through alter table interfaces
45 alter table bug51378 add primary key idx3(col1, col2(31));
46
47 SHOW CREATE TABLE bug51378;
48
49 drop table bug51378;
50
51 # Or we can create such primary key through create table interfaces
52 create table bug51378 (
53 col1 int not null,
54 col2 blob not null,
55 col3 time not null, primary key(col1, col2(31))) engine = innodb;
56
57 # Unique index on one or more column prefix(es) will be created
58 # as non-cluster index
59 create unique index idx on bug51378(col1, col2(31));
60
61 SHOW CREATE TABLE bug51378;
62
63 drop table bug51378;
64
65 # If a table has a NULLABLE column, unique index on it will not
66 # be treated as primary index.
67 create table bug51378 (
68 col1 int not null,
69 col2 int ) engine = innodb;
70
71 # This will be created as non-cluster index since col2 is nullable
72 create unique index idx on bug51378(col1, col2);
73
74 SHOW CREATE TABLE bug51378;
75
76 drop table bug51378;