| 1 |
# |
| 2 |
# Bug#22017616: ASSERTION FAILED: TABLE_SHARE->IS_MISSING_PRIMARY_KEY() |
| 3 |
# == M_PREBUILT->CLUST_IND |
| 4 |
# |
| 5 |
# Ensure that adding indexes with virtual columns are not promoted to |
| 6 |
# primary keys |
| 7 |
# |
| 8 |
# Base line with normal column - should be promoted |
| 9 |
CREATE TABLE t0(a INT NOT NULL) ENGINE=INNODB; |
| 10 |
ALTER TABLE t0 ADD UNIQUE INDEX (a); |
| 11 |
# Case a: Create table with virtual unique not null column |
| 12 |
CREATE TABLE t1(a POINT GENERATED ALWAYS AS (POINT(1,1)) VIRTUAL UNIQUE) ENGINE=INNODB; |
| 13 |
SELECT * FROM t1; |
| 14 |
a |
| 15 |
# Case b: Create table with index on virtual point column |
| 16 |
CREATE TABLE t2(a POINT GENERATED ALWAYS AS (POINT(1,1)) VIRTUAL, UNIQUE INDEX no_pk(a(1))) ENGINE=INNODB; |
| 17 |
SELECT * FROM t2; |
| 18 |
a |
| 19 |
# Case c: Add unique index on virtual point column |
| 20 |
CREATE TABLE t3(a POINT GENERATED ALWAYS AS (POINT(1,1)) VIRTUAL) |
| 21 |
ENGINE=INNODB; |
| 22 |
ALTER TABLE t3 ADD UNIQUE INDEX (a(1)); |
| 23 |
SELECT * FROM t3; |
| 24 |
a |
| 25 |
# Case d: Add unique index on virtual blob column |
| 26 |
CREATE TABLE t4 (a BLOB, b BLOB GENERATED ALWAYS AS (a) VIRTUAL) ENGINE=INNODB; |
| 27 |
ALTER TABLE t4 ADD UNIQUE INDEX (b(1)); |
| 28 |
SELECT * FROM t4; |
| 29 |
a b |
| 30 |
# Query I_S to verify that 'a' is promoted to pk only when it |
| 31 |
# isn't virtual |
| 32 |
SELECT T.NAME AS TABLE_NAME, I.NAME AS INDEX_NAME, |
| 33 |
CASE (I.TYPE & 3) |
| 34 |
WHEN 3 THEN "yes" |
| 35 |
ELSE "no" END AS IS_PRIMARY_KEY, |
| 36 |
F.NAME AS FIELD_NAME, F.POS AS FIELD_POS FROM |
| 37 |
INFORMATION_SCHEMA.INNODB_SYS_TABLES AS T JOIN |
| 38 |
INFORMATION_SCHEMA.INNODB_SYS_INDEXES AS I JOIN |
| 39 |
INFORMATION_SCHEMA.INNODB_SYS_FIELDS AS F |
| 40 |
ON I.INDEX_ID = F.INDEX_ID AND I.TABLE_ID = T.TABLE_ID |
| 41 |
WHERE T.NAME LIKE 'test/t%'; |
| 42 |
TABLE_NAME INDEX_NAME IS_PRIMARY_KEY FIELD_NAME FIELD_POS |
| 43 |
test/t0 a yes a 0 |
| 44 |
test/t1 a no a 0 |
| 45 |
test/t2 no_pk no a 0 |
| 46 |
test/t3 a no a 0 |
| 47 |
test/t4 b no b 0 |
| 48 |
DROP TABLE t0; |
| 49 |
DROP TABLE t1; |
| 50 |
DROP TABLE t2; |
| 51 |
DROP TABLE t3; |
| 52 |
DROP TABLE t4; |