mirror of
https://github.com/MariaDB/server.git
synced 2025-02-07 06:12:18 +01:00
89 lines
2.4 KiB
Text
89 lines
2.4 KiB
Text
CREATE TABLE t1 (
|
|
y YEAR NULL,
|
|
i SMALLINT NULL,
|
|
c VARCHAR(8) NOT NULL DEFAULT '',
|
|
vi SMALLINT AS (i) VIRTUAL,
|
|
INDEX(y)
|
|
) ENGINE=InnoDB;
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`y` year(4) DEFAULT NULL,
|
|
`i` smallint(6) DEFAULT NULL,
|
|
`c` varchar(8) NOT NULL DEFAULT '',
|
|
`vi` smallint(6) GENERATED ALWAYS AS (`i`) VIRTUAL,
|
|
KEY `y` (`y`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
SELECT * FROM t1 WHERE y BETWEEN 2012 AND 2016 FOR UPDATE;
|
|
y i c vi
|
|
INSERT INTO t1 (i) VALUES (1),(2);
|
|
SELECT * FROM t1 WHERE y BETWEEN 2012 AND 2016 FOR UPDATE;
|
|
y i c vi
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1 (
|
|
y YEAR NULL,
|
|
i SMALLINT NULL,
|
|
vi SMALLINT AS (i) VIRTUAL,
|
|
INDEX(y)
|
|
) ENGINE=InnoDB;
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`y` year(4) DEFAULT NULL,
|
|
`i` smallint(6) DEFAULT NULL,
|
|
`vi` smallint(6) GENERATED ALWAYS AS (`i`) VIRTUAL,
|
|
KEY `y` (`y`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
SELECT * FROM t1 WHERE y BETWEEN 2012 AND 2016 FOR UPDATE;
|
|
y i vi
|
|
INSERT INTO t1 (i) VALUES (1),(2);
|
|
SELECT * FROM t1 WHERE y BETWEEN 2012 AND 2016 FOR UPDATE;
|
|
y i vi
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1 (
|
|
y YEAR NULL,
|
|
i SMALLINT NULL,
|
|
b BLOB NULL,
|
|
vi SMALLINT AS (i) VIRTUAL,
|
|
INDEX(y)
|
|
) ENGINE=InnoDB;
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`y` year(4) DEFAULT NULL,
|
|
`i` smallint(6) DEFAULT NULL,
|
|
`b` blob DEFAULT NULL,
|
|
`vi` smallint(6) GENERATED ALWAYS AS (`i`) VIRTUAL,
|
|
KEY `y` (`y`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
SELECT * FROM t1 WHERE y BETWEEN 2012 AND 2016 FOR UPDATE;
|
|
y i b vi
|
|
INSERT INTO t1 (i) VALUES (1),(2);
|
|
SELECT * FROM t1 WHERE y BETWEEN 2012 AND 2016 FOR UPDATE;
|
|
y i b vi
|
|
DROP TABLE t1;
|
|
#
|
|
# MDEV-23632 ALTER TABLE...ADD KEY creates corrupted index on virtual column
|
|
#
|
|
CREATE TABLE t1(a INT PRIMARY KEY, b INT, g INT GENERATED ALWAYS AS(b)VIRTUAL) ENGINE=InnoDB;
|
|
INSERT INTO t1 VALUES (1,1,default);
|
|
ALTER TABLE t1 ADD COLUMN c INT;
|
|
ALTER TABLE t1 ADD KEY(g);
|
|
CHECK TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check status OK
|
|
SELECT g FROM t1 FORCE INDEX (g);
|
|
g
|
|
1
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1(a INT, b INT, g INT GENERATED ALWAYS AS(b)VIRTUAL) ENGINE=InnoDB;
|
|
INSERT INTO t1 VALUES (1,1,default);
|
|
ALTER TABLE t1 ADD COLUMN c INT PRIMARY KEY;
|
|
ALTER TABLE t1 ADD KEY(g);
|
|
CHECK TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check status OK
|
|
SELECT g FROM t1 FORCE INDEX (g);
|
|
g
|
|
1
|
|
DROP TABLE t1;
|