MySQL Bugs: #118535: Arithmetic operations fail on fulltext-indexed fields inserted with DISABLE KEYS

Bug #118535	Arithmetic operations fail on fulltext-indexed fields inserted with DISABLE KEYS
Submitted:	26 Jun 2025 17:43	Modified:	27 Jun 2025 19:27
Reporter:	Matthew Westcott	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: FULLTEXT search	Severity:	S3 (Non-critical)
Version:	9.3.0, 8.4.5, 8.0.42	OS:	Ubuntu (25.04 Server)
Assigned to:		CPU Architecture:	x86

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;

The exact error raised is:

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

Hello Matthew Westcott,

Thank you for the report and test case.

regards,
Umesh

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;

I reproduced both cases on MySQL Community Server 8.0.42 using the
official Linux x86_64 generic binary.

Running the following after ENABLE KEYS or after adding the second
FULLTEXT index provides an alternative workaround:

ANALYZE TABLE articles;

In the DISABLE KEYS case, the MATCH() calculation failed with error
1690 before ANALYZE TABLE and succeeded afterward. I also repeated the
second-FULLTEXT-index case with five independent fresh schemas; all
five failed before ANALYZE TABLE and succeeded afterward. The row count
remained unchanged, so no temporary insert/delete was required.

We encountered this in Tiki, an open-source web application whose MySQL
search backend creates FULLTEXT indexes on demand after populating its
search table, then uses MATCH() results to calculate weighted relevance
scores. ANALYZE TABLE resolves the failure in this workflow, but remains
a workaround waiting for a permanent fix.