Bug #118535 Arithmetic operations fail on fulltext-indexed fields inserted with DISABLE KEYS

• Submitted: 26 Jun 17:43
• Modified: 27 Jun 19:27
• Reporter: Matthew Westcott
• Status: Verified
• Category: MySQL Server: FULLTEXT search
• Severity: S3 (Non-critical)
• Version: 9.3.0, 8.4.5, 8.0.42
• OS: Ubuntu (25.04 Server)
• CPU Architecture: x86

[26 Jun 17:43] Matthew Westcott

Description:
If a row is inserted into a table with a FULLTEXT index on it while DISABLE KEYS is in place - as done by mysqldump in its default configuration - then any subsequent MATCH (...) AGAINST (...) expression run on that field will return a value that is apparently zero but fails with a "DOUBLE value is out of range" error when any arithmetic operation is performed on it. The problem goes away once further rows are inserted into the table with ENABLE KEYS active, even if those rows are immediately deleted.

How to repeat:
From a clean database, run the following queries:

CREATE TABLE articles (
  id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
  title TEXT,
  FULLTEXT (title)
) ENGINE=InnoDB;

/*!40000 ALTER TABLE `articles` DISABLE KEYS */;
INSERT INTO articles (title) VALUES ('test');
/*!40000 ALTER TABLE `articles` ENABLE KEYS */;

-- fails with "DOUBLE value is out of range"
SELECT MATCH (title) AGAINST ('test') * 1 FROM articles;

INSERT INTO articles (title) VALUES ('foo');
DELETE FROM articles where title = 'foo';

-- now succeeds
SELECT MATCH (title) AGAINST ('test') * 1 FROM articles;

[26 Jun 18:12] Matthew Westcott

The exact error raised is:

ERROR 1690 (22003): DOUBLE value is out of range in '((match `testdb`.`articles`.`title` against ('test')) * 1)'

[27 Jun 7:15] MySQL Verification Team

Hello Matthew Westcott,

Thank you for the report and test case.

regards,
Umesh

[27 Jun 19:27] Matthew Westcott

Have just found that the same thing happens if ALTER TABLE is used to add another fulltext index on the same table (even on an unrelated field) after the insertion:

(tested on 8.0.42)

CREATE TABLE articles (
  `id` int NOT NULL AUTO_INCREMENT,
  `title` text,
  `other` text,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `fulltext_title` (`title`)
) ENGINE=InnoDB;

INSERT INTO articles (title, other) VALUES ('test', 'test');

-- Query succeeds
SELECT MATCH (title) AGAINST ('test') * 1 FROM articles;

ALTER TABLE articles ADD FULLTEXT INDEX `fulltext_other` (`other`);

-- Query fails with ERROR 1690 (22003): DOUBLE value is out of range in '((match `testdb`.`articles`.`title` against ('test')) * 1)'
SELECT MATCH (title) AGAINST ('test') * 1 FROM articles;

INSERT INTO articles (title, other) VALUES ('foo', 'foo');
DELETE FROM articles WHERE title = 'foo';

-- Query now succeeds again
SELECT MATCH (title) AGAINST ('test') * 1 FROM articles;