Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Minor
-
Resolution: Unresolved
-
10.3.20, 10.4.11, 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
Description
I find little inconsistency when fulltext query contains percent sign (%) with combination of * wildcard (in both boolean mode and natural language mode)
Since * and % are not considered as special operators in fulltext natural language mode I don't see reason why SQL queries containing those characters should fail due to syntax error.
https://mariadb.com/kb/en/full-text-index-overview/
"IN NATURAL LANGUAGE MODE is the default type of full-text search, and the keywords can be omitted. There are no special operators, and searches consist of one or more comma-separated keywords."
Issue is only present in InnoDB (not MyISAM)
|
|
CREATE TABLE `tab1` (
|
`id` INT(11) NOT NULL, |
`str` TEXT NOT NULL,
|
PRIMARY KEY (`id`),
|
FULLTEXT INDEX `fulltext1` (`str`)
|
)
|
ENGINE=InnoDB
|
;
|
|
-- those queries fail (both in natural language and boolean mode) |
select * from tab1 where match(str) against ('%*'); |
select * from tab1 where match(str) against ('%*%'); |
select * from tab1 where match(str) against ('*%*'); |
select * from tab1 where match(str) against ('%*%%'); |
select * from tab1 where match(str) against ('%*%*'); |
select * from tab1 where match(str) against ('%*%%*'); |
select * from tab1 where match(str) against ('*'); -- this is obvious in boolean mode, but not sure why is this problem in natural language mode |
|
/* SQL chyba (1064): syntax error, unexpected $end, expecting FTS_TERM or FTS_NUMB or '*' */
|
|
-- suprisingly those queries work fine
|
select * from tab1 where match(str) against ('**'); |
select * from tab1 where match(str) against ('%'); |
select * from tab1 where match(str) against ('%%'); |
select * from tab1 where match(str) against ('%**'); |
select * from tab1 where match(str) against ('%%**'); |
As You can see one person can get a headache if You try to escape query rather than just remove any special characters from string.