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

Submitted: 27 Oct 2015 12:39
Reporter: Ivan Kostoski Email Updates:
Status: Open Impact on me:
None 
Category:MySQL Server: FULLTEXT search Severity:S4 (Feature request)
Version:5.7.9 OS:Any
Assigned to: 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

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.038 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.