I have a problem which I have been able to recreate with two very simple tables. The tables were defined as follows:
create table Temp_Table_MyISAM(
id INT UNSIGNED AUTO_INCREMENT,
code VARCHAR(10) NOT NULL,
name VARCHAR(256) NOT NULL,
PRIMARY KEY (id),
KEY (code),
FULLTEXT (name)
) ENGINE = MYISAM;
create table Temp_Table_InnoDB(
id INT UNSIGNED AUTO_INCREMENT,
code VARCHAR(10) NOT NULL,
name VARCHAR(256) NOT NULL,
PRIMARY KEY (id),
KEY (code),
FULLTEXT (name)
);
Each table has two rows, as can be seen from the result of the following two queries:
select * from Temp_Table_MyISAM;
+----+---------+----------------+
| id | code | name |
+----+---------+----------------+
| 1 | AC-7865 | 38 NORTHRIDGE |
| 2 | DE-3514 | POLARIS VENTRI |
+----+---------+----------------+
select * from Temp_Table_InnoDB;
+----+---------+----------------+
| id | code | name |
+----+---------+----------------+
| 1 | AC-7865 | 38 NORTHRIDGE |
| 2 | DE-3514 | POLARIS VENTRI |
+----+---------+----------------+
When I do a FULLTEXT search on the MyISAM table, I don't get any hits
MariaDB [stackoverflow]> SELECT name, code FROM Temp_Table_MyISAM WHERE MATCH(name) AGAINST('38');
Empty set (0.00 sec)MariaDB [stackoverflow]> SELECT name, code FROM Temp_Table_MyISAM WHERE MATCH(name) AGAINST('POLARIS');
Empty set (0.00 sec)
When I do a FULLTEXT search on the InnoDB table, I get a hit only when the pattern to be matched does not start with a numeric value
MariaDB [stackoverflow]> SELECT name, code FROM Temp_Table_InnoDB WHERE MATCH(name) AGAINST('38');
Empty set (0.00 sec)MariaDB [stackoverflow]> SELECT name, code FROM Temp_Table_InnoDB WHERE MATCH(name) AGAINST('POLARIS');
+----------------+---------+
| name | code |
+----------------+---------+
| POLARIS VENTRI | DE-3514 |
+----------------+---------+
Any insight would be appreciated.
