Bug #78977 Enable InnoDB fulltext index to use generated FTS_DOC_ID column

• Submitted: 27 Oct 2015 12:39
• Reporter: Ivan Kostoski
• Status: Open
• Category: MySQL Server: FULLTEXT search
• Severity: S4 (Feature request)
• Version: 5.7.9
• OS: Any
• CPU Architecture: Any
• Tags: innodb fulltext fts generated virtual fts_doc_id

[27 Oct 2015 12:39] Ivan Kostoski

Description:
Currently InnoDB fulltext index requires FTS_DOC_ID column as documented in https://dev.mysql.com/doc/refman/5.6/en/innodb-fulltext-index.html. 

Please enable usage of generated virtual columns (which are defined as BIGINT NOT NULL) as FTS_DOC_ID.

Alternatively, please add syntax to FULLTEXT INDEX creation statement to select uniquely indexed integer column, possibly of smaller size than BIGINT, to be used as FTS_DOC_ID.

How to repeat:
CREATE TABLE test (
  other_id INT UNSIGNED NOT NULL PRIMARY KEY, 
  data TEXT, 
  FTS_DOC_ID BIGINT GENERATED ALWAYS AS (other_id),
  UNIQUE KEY FTS_DOC_ID_INDEX(FTS_DOC_ID)
) ENGINE=InnoDB;

INSERT INTO test(other_id, data) VALUES (1, 'some'), (2, 'text');

ALTER TABLE test ADD FULLTEXT KEY ft_data(data);

Reports:
ERROR 1797 (HY000): Column 'FTS_DOC_ID' is of wrong type for an InnoDB FULLTEXT index