0

If I make a like query as below:

SELECT style, description FROM parent_products WHERE description LIKE ('01940E1%');

then result I get is a single row with two fields which is working as expected

style: 01940E1
description: 01940E1 TShirt TShirts Alternat Alternative

However, when I do full text search on it SELECT style, description FROM parent_products WHERE MATCH(description) AGAINST ("01940E1" IN BOOLEAN MODE) I am not getting any results.

Note: I am using AWS Serverless Aurora - MySQL

prasun
  • 7,073
  • 9
  • 41
  • 59
  • Can you try `AGAINST ("01940E1*" IN ...` in case there is some unprintable character behind the "1"? Do you run these queries in an app/php? Then maybe verify that these are actually the queries that are executed (e.g. try them directly in the rds query editor). Otherwise I don't see any problem with your query, it should work as expected. – Solarflare Jan 21 '21 at 11:30
  • @Solarflare running directly in SQL using dbeaver https://dbeaver.io/, probably, they are using jdbc driver. I have already tried appending '*' approach, that did not help either. I also tried node.js https://github.com/mysqljs/mysql, that too failed with same approach. Currently, wont be able to RDS console as data API is disabled in this db instance. – prasun Jan 21 '21 at 12:03
  • dbeaver is fine, I just wanted to exclude an underlying error in e.g. your php code that would result in a different query to actually run. Another idea: the fulltext index only includes values that are already commited. If you just inserted that value and didn't commit yet, this would be the expected behaviour (`like` finds, `match` doesn`t). Otherwise, maybe try to drop and recreate your fulltext index. – Solarflare Jan 21 '21 at 12:15
  • @Solarflare not sure if E1 is some special or reserved word, it works with everything else eg: 04441C1 441C1 – prasun Jan 21 '21 at 12:28
  • Well, the "E1" could be read as an exponent (e.g. 01940E1 read as a number is 19400), but I don't see a way this can be autocasted here in those queries (if those are your queries). As a string, the word starts at the "0" for the fulltext index, so "E" and "C" shouldn't make a difference. – Solarflare Jan 21 '21 at 13:08

0 Answers0