Debian Codegraph @

mysql-test/suite/innodb/r/innodb-virtual-columns2.result

raw
1 CREATE TABLE t1 (
2 y YEAR NULL,
3 i SMALLINT NULL,
4 c VARCHAR(8) NOT NULL DEFAULT '',
5 vi SMALLINT AS (i) VIRTUAL,
6 INDEX(y)
7 ) ENGINE=InnoDB;
8 SHOW CREATE TABLE t1;
9 Table Create Table
10 t1 CREATE TABLE `t1` (
11 `y` year(4) DEFAULT NULL,
12 `i` smallint(6) DEFAULT NULL,
13 `c` varchar(8) NOT NULL DEFAULT '',
14 `vi` smallint(6) GENERATED ALWAYS AS (`i`) VIRTUAL,
15 KEY `y` (`y`)
16 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
17 SELECT * FROM t1 WHERE y BETWEEN 2012 AND 2016 FOR UPDATE;
18 y i c vi
19 INSERT INTO t1 (i) VALUES (1),(2);
20 SELECT * FROM t1 WHERE y BETWEEN 2012 AND 2016 FOR UPDATE;
21 y i c vi
22 DROP TABLE t1;
23 CREATE TABLE t1 (
24 y YEAR NULL,
25 i SMALLINT NULL,
26 vi SMALLINT AS (i) VIRTUAL,
27 INDEX(y)
28 ) ENGINE=InnoDB;
29 SHOW CREATE TABLE t1;
30 Table Create Table
31 t1 CREATE TABLE `t1` (
32 `y` year(4) DEFAULT NULL,
33 `i` smallint(6) DEFAULT NULL,
34 `vi` smallint(6) GENERATED ALWAYS AS (`i`) VIRTUAL,
35 KEY `y` (`y`)
36 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
37 SELECT * FROM t1 WHERE y BETWEEN 2012 AND 2016 FOR UPDATE;
38 y i vi
39 INSERT INTO t1 (i) VALUES (1),(2);
40 SELECT * FROM t1 WHERE y BETWEEN 2012 AND 2016 FOR UPDATE;
41 y i vi
42 DROP TABLE t1;
43 CREATE TABLE t1 (
44 y YEAR NULL,
45 i SMALLINT NULL,
46 b BLOB NULL,
47 vi SMALLINT AS (i) VIRTUAL,
48 INDEX(y)
49 ) ENGINE=InnoDB;
50 SHOW CREATE TABLE t1;
51 Table Create Table
52 t1 CREATE TABLE `t1` (
53 `y` year(4) DEFAULT NULL,
54 `i` smallint(6) DEFAULT NULL,
55 `b` blob DEFAULT NULL,
56 `vi` smallint(6) GENERATED ALWAYS AS (`i`) VIRTUAL,
57 KEY `y` (`y`)
58 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
59 SELECT * FROM t1 WHERE y BETWEEN 2012 AND 2016 FOR UPDATE;
60 y i b vi
61 INSERT INTO t1 (i) VALUES (1),(2);
62 SELECT * FROM t1 WHERE y BETWEEN 2012 AND 2016 FOR UPDATE;
63 y i b vi
64 DROP TABLE t1;
65 #
66 # MDEV-23632 ALTER TABLE...ADD KEY creates corrupted index on virtual column
67 #
68 CREATE TABLE t1(a INT PRIMARY KEY, b INT, g INT GENERATED ALWAYS AS(b)VIRTUAL) ENGINE=InnoDB;
69 INSERT INTO t1 VALUES (1,1,default);
70 ALTER TABLE t1 ADD COLUMN c INT;
71 ALTER TABLE t1 ADD KEY(g);
72 CHECK TABLE t1;
73 Table Op Msg_type Msg_text
74 test.t1 check status OK
75 SELECT g FROM t1 FORCE INDEX (g);
76 g
77 1
78 DROP TABLE t1;
79 CREATE TABLE t1(a INT, b INT, g INT GENERATED ALWAYS AS(b)VIRTUAL) ENGINE=InnoDB;
80 INSERT INTO t1 VALUES (1,1,default);
81 ALTER TABLE t1 ADD COLUMN c INT PRIMARY KEY;
82 ALTER TABLE t1 ADD KEY(g);
83 CHECK TABLE t1;
84 Table Op Msg_type Msg_text
85 test.t1 check status OK
86 SELECT g FROM t1 FORCE INDEX (g);
87 g
88 1
89 DROP TABLE t1;