Bug #72605 Syntax error fulltext search InnoDB tables 5.6.16

Description:
This works for MyISAM but not InnoDB tales.

MySQL Error   : syntax error, unexpected '+', expecting FTS_TERM or FTS_NUMB or '*'
Error Number  : 1064

SELECT
                COUNT(imageid) AS imagecount
        FROM dbtech_gallery_images
        WHERE
                instanceid      = 1 &&
                approved                = 1 &&
                deleted         = 0 &&
        MATCH
                (title, text, tagging, username)
        AGAINST
                ('+jewellery* +jewel* +crystal* +fashion* +* +abstract* ' IN BOOLEAN MODE);
Ca

How to repeat:
Use syntax similar to the above against a InnoDB table vs similar MyISAM table.

Changed category to FULLTEXT search

Thank you for the bug report. Please provide the complete test case create table, insert data etc. Thanks.

-- full example of bug

CREATE TABLE atest_table (
 imageid int(10) unsigned NOT NULL AUTO_INCREMENT,
 title varchar(300) CHARACTER SET utf8 NOT NULL,
 text mediumtext CHARACTER SET utf8,
 PRIMARY KEY (imageid),
 FULLTEXT KEY search_index (title,text)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

insert into atest_table (title,text)
values
 ('Test 1','Test 1 description'),
 ('Jewel','Crystal Jewellery'),
 ('Line 3','Line 1 description');

SELECT
    COUNT(imageid) AS imagecount
        FROM atest_table
        WHERE
        MATCH
                (title, text)
        AGAINST
                ('+jewel* +* +crystal* ' IN BOOLEAN MODE);

-- MySQL said: 
-- #1064 - syntax error, unexpected '+', expecting FTS_TERM or FTS_NUMB or '*' 

ALTER TABLE `atest_table` ENGINE = MyISAM;

SELECT
    COUNT(imageid) AS imagecount
        FROM atest_table
        WHERE
        MATCH
                (title, text)
        AGAINST
                ('+jewel* +* +crystal* ' IN BOOLEAN MODE);
-- Result:			
-- imagecount
-- 1

drop table if exists t1;
create table t1(a text,fulltext index a(a))engine=innodb;
select match(a) against ('+* +a* ' in boolean mode) from t1;

Hello Kym,

Thank you for the report.
Verified as described.

Thanks,
Umesh

Thank you Shane for the simplified test case.

mysql> drop table if exists t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table t1(a text,fulltext index a(a))engine=innodb;
Query OK, 0 rows affected (0.12 sec)

mysql> select match(a) against ('+* +a* ' in boolean mode) from t1;
ERROR 1064 (42000): syntax error, unexpected '+', expecting FTS_TERM or FTS_NUMB or '*'
mysql>
mysql>

// With MyISAM

mysql> drop table if exists t1;
Query OK, 0 rows affected (0.02 sec)

mysql> create table t1(a text,fulltext index a(a))engine=myisam;
Query OK, 0 rows affected (0.01 sec)

mysql> select match(a) against ('+* +a* ' in boolean mode) from t1;
Empty set (0.00 sec)

mysql> select version();
+-------------------------------------------+
| version()                                 |
+-------------------------------------------+
| 5.6.19-enterprise-commercial-advanced-log |
+-------------------------------------------+
1 row in set (0.00 sec)

Thanks guys.  
This is my first bug report.
I'm not a DBA, but am the tech admin for a forum http://www.ausphotography.net.au
I have general development skills

Since MySQL 5.6 we've changed most of our tables to InnoDB which has been a positive move in terms of stability; i.e. MyISAM tables would have errors every few weeks.

This error is localised to the photo Gallery addon search function.  Which being a photography forum is rather important :)

The real problem is "AGAINST ('+*' IN BOOLEAN MODE)". If we remove '+*' from
the fts query string, we will get correct result.

Hi Kym, why do you need '+*' in fts query? it's meaningless and useless.

The code in question is generated by a plugin to the vBulletin forum system.
I agree the +* is stupid, but is not in my hands.

The code works for MyISAM tables and not for InnoDB - that is still an issue.

Hi Kym, we decided to document syntax differences, and wouldn't fix the bug(IMO, it's not a bug, but a real compatible issue). Sorry for the inconvenience. I'd strongly recommend you to file a bug to "vBulletin". It could be a simple fix, I guess. Hope you can go around this soon.

Thanks again for the bug report.

Thanks, I've reported the issue to the vBulletin 3rd Party addon developer and fixed the code myself for our site. (the "+*) is not generated.

As said, there is a difference between MyISAM and InnoDB when ideally they should be the same

Invalid full-text search queries for InnoDB ('+*' , '+-', '+-good') are now documented here:

http://dev.mysql.com/doc/refman/5.6/en/fulltext-boolean.html
http://dev.mysql.com/doc/refman/5.7/en/fulltext-boolean.html

Thank you for the bug report.