[MDEV-12976] '+test +10' doesn't match 'test 10' Created: 2017-06-02  Updated: 2017-06-03

Status: Confirmed
Project: MariaDB Server
Component/s: Full-text Search
Affects Version/s: 10.1.23
Fix Version/s: 10.1

Type: Bug Priority: Minor
Reporter: Olaf van der Spek Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Debian


 Description   

Yes, 10 is shorter then min-token-size, but test is not. IMO it should use the index to find all rows matching test, fetch them and then check for 10.

CREATE TABLE IF NOT EXISTS `test` (
 
  `f0` varchar(255) NOT NULL
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
 
 
INSERT INTO `test` (`f0`) VALUES
 
('test 10');
 
 
 
ALTER TABLE `test` ADD FULLTEXT KEY `f0` (`f0`);
 
 
 
SELECT * FROM `test` WHERE match (f0) against ('+test +10' in boolean mode)



 Comments   
Comment by Sergei Golubchik [ 2017-06-03 ]

It works with MyISAM, but not quite:

MariaDB [test]> CREATE TABLE `test` ( `f0` varchar(255) NOT NULL);
 
MariaDB [test]> INSERT INTO `test` (`f0`) VALUES ('test 10');
 
MariaDB [test]> ALTER TABLE `test` ADD FULLTEXT KEY `f0` (`f0`);
 
MariaDB [test]> SELECT * FROM `test` WHERE match (f0) against ('+test +10' in boolean mode);
 
+---------+
 
| f0      |
 
+---------+
 
| test 10 |
 
+---------+
 
MariaDB [test]> SELECT * FROM `test` WHERE match (f0) against ('+test -10' in boolean mode);
 
+---------+
 
| f0      |
 
+---------+
 
| test 10 |
 
+---------+

It looks like InnoDB takes your «"10" must be present» wish literally, but cannot find any row with it, because "10" is not present in the index (too short words are not indexed). While MyISAM seems to consistently ignore short words both when indexing and in queries.

Generated at Thu Feb 08 08:01:57 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.