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