Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-21750

InnoDB fulltext query containing percent and wildcard character reports syntax error, unexpected $end, expecting FTS_TERM or FTS_NUMB or '*'

    XMLWordPrintable

Details

    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.

      Attachments

        Activity

          People

            thiru Thirunarayanan Balathandayuthapani
            techi602 Michal Vrchota
            Votes:
            1 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.