EVOLUTION-MANAGER

Edit File: main_alter_table.result

# # Bug#22017616: ASSERTION FAILED: TABLE_SHARE->IS_MISSING_PRIMARY_KEY() # == M_PREBUILT->CLUST_IND # # Ensure that adding indexes with virtual columns are not promoted to # primary keys # # Base line with normal column - should be promoted CREATE TABLE t0(a INT NOT NULL) ENGINE=INNODB; ALTER TABLE t0 ADD UNIQUE INDEX (a); # Case a: Create table with virtual unique not null column CREATE TABLE t1(a POINT GENERATED ALWAYS AS (POINT(1,1)) VIRTUAL UNIQUE) ENGINE=INNODB; SELECT * FROM t1; a # Case b: Create table with index on virtual point column CREATE TABLE t2(a POINT GENERATED ALWAYS AS (POINT(1,1)) VIRTUAL, UNIQUE INDEX no_pk(a(1))) ENGINE=INNODB; SELECT * FROM t2; a # Case c: Add unique index on virtual point column CREATE TABLE t3(a POINT GENERATED ALWAYS AS (POINT(1,1)) VIRTUAL) ENGINE=INNODB; ALTER TABLE t3 ADD UNIQUE INDEX (a(1)); SELECT * FROM t3; a # Case d: Add unique index on virtual blob column CREATE TABLE t4 (a BLOB, b BLOB GENERATED ALWAYS AS (a) VIRTUAL) ENGINE=INNODB; ALTER TABLE t4 ADD UNIQUE INDEX (b(1)); SELECT * FROM t4; a b # Query I_S to verify that 'a' is promoted to pk only when it # isn't virtual SELECT T.NAME AS TABLE_NAME, I.NAME AS INDEX_NAME, CASE (I.TYPE & 3) WHEN 3 THEN "yes" ELSE "no" END AS IS_PRIMARY_KEY, F.NAME AS FIELD_NAME, F.POS AS FIELD_POS FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES AS T JOIN INFORMATION_SCHEMA.INNODB_SYS_INDEXES AS I JOIN INFORMATION_SCHEMA.INNODB_SYS_FIELDS AS F ON I.INDEX_ID = F.INDEX_ID AND I.TABLE_ID = T.TABLE_ID WHERE T.NAME LIKE 'test/%'; TABLE_NAME INDEX_NAME IS_PRIMARY_KEY FIELD_NAME FIELD_POS test/t0 a yes a 0 test/t1 a no a 0 test/t2 no_pk no a 0 test/t3 a no a 0 test/t4 b no b 0 DROP TABLE t0; DROP TABLE t1; DROP TABLE t2; DROP TABLE t3; DROP TABLE t4;