sequelize: FullText support syntax incorrect for MySQL

Issue Creation Checklist

[x ] I have read the contribution guidelines

Bug Description

Since Sequelize 6.5.0, there is FullText support, but it is based on Postgres which uses a completely different syntax from MySQL. We used a hack before to get this working on MySQL, but, with the new version, that hack doesn’t work anymore.

What do you expect to happen?

Before 6.5.0 work around

We use a searchText column for all our models with a FULLTEXT index applied.

What we want to add to the query:

Sequelize.literal(`MATCH (`Faq`.`searchText`) AGAINST(\'"test label" test* label*\' IN BOOLEAN MODE)`)

The reason we add both "test" and test* is that we want to ensure “as you type” hit, but will get a preference for an exact match.

What is actually happening?

After the 6.5.0 upgrade, we use

    searchText: {
        [Op.match]: '"test label" test* label*',
    },

and this gets generated

`Faq`.`searchText` @@ \'\\"test\\" test* \\"label\\" label*\'

which gets rejected by MySQL

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@@ '\\"test\\" test* \\"label\\" label*')' at line 1

Additional context

Environment

Sequelize version: 6.13.0
Node.js version: 16.13.1
If TypeScript related: TypeScript version: 4.5.4

Bug Report Checklist

How does this problem relate to dialects?

I think this problem happens regardless of the dialect.
[x ] I think this problem happens only for the following dialect(s): MySQL
I don’t know, I was using PUT-YOUR-DIALECT-HERE, with connector library version XXX and database version XXX

Would you be willing to resolve this issue by submitting a Pull Request?

Yes, I have the time and I know how to start.
Yes, I have the time but I don’t know how to start, I would need guidance.
[x ] No, I don’t have the time, although I believe I could do it if I had the time…
No, I don’t have the time and I wouldn’t even know how to start.

About this issue

Original URL
State: closed
Created 2 years ago
Comments: 17 (8 by maintainers)

Most upvoted comments

Well doing something like

User.findAll({
  where: {
    firstName:  sql`MATCH (${attr('firstName')}) AGAINST ('abc' IN NATURAL LANGUAGE MODE)`
  },
});

would not make sense, you can’t compare an attribute to a literal ; we don’t know where the attribute should go. If you want to combine it with other clauses, simply use or or and:

User.findAll({
  where: and(
    sql`MATCH (${attr('firstName')}) AGAINST ('abc' IN NATURAL LANGUAGE MODE)`,
    { id: 14 },
  ),
});

This also means this is already possible today. The sql & attr features would make it better but you can just do this today:

import { literal, and } from 'sequelize';

User.findAll({
  where: and(
    literal("MATCH (`first_name`) AGAINST ('abc' IN NATURAL LANGUAGE MODE)"),
    { id: 14 },
  ),
});

The only thing we need to do (for this issue) is make sequelize throw with a clear error message when Op.match is used with the mysql dialect

ephys on Mar 7, 2022

This occurred because Sequelize exported from sequelize-typescript was used instead of Sequelize exported from sequelize itself.

mschipperheyn on Apr 1, 2022

Ok, I will have a go at a reproduction. First of all I will do a fresh install and see if there isn’t some kind of lingering issue there somehow. Oh wait a minute. We are using sequelize-typescript as an abstraction layer. That must be it somehow.

mschipperheyn on Mar 10, 2022

Supporting literal in Op.match is something I plan on doing with https://github.com/sequelize/sequelize/pull/14022 and https://github.com/sequelize/sequelize/pull/14020

ephys on Feb 25, 2022