Using MATCH with OR returns results that satisfy none of the conditions

Question

Using MySQL 5.6, this request

SELECT foo
FROM bar
WHERE groupId = '1'
  AND MATCH (foo) AGAINST ('"myQuery"' IN BOOLEAN MODE);

and

SELECT foo
FROM bar
WHERE groupId = '1'
  AND foo like '%myQuery%';

returns both correct results, but when I combine the two with:

SELECT foo
FROM bar
WHERE groupId = '1'
  AND (
    MATCH (foo) AGAINST ('"myQuery"' IN BOOLEAN MODE)
    OR foo LIKE '%myQuery%'
  );

I got some extra results, which do not appear in any of the first two requests, and didn't contain myQuery at all.

Is there any trick with the parenthesis that I missed?
Or could it be related to any sort of index cache? Sometimes the results are correct, and suddenly, there are not anymore.

I also tried with

WHERE (
  groupId = '1' AND MATCH (foo) AGAINST ('"myQuery"' IN BOOLEAN MODE)
) OR (
  groupId = '1' AND foo like '%myQuery%'
);

Edit: here are the results of my requests, with myQuery = 'gold'.

The 1st and 2nd one returns:

'Fancy gold'
'Nice gold'
'Super Nice gold'
'Ugly gold'

The last one returns:

'Cornices diamond'
'Custom'
'Fancy gold'
'Nice gold'
'Super Nice gold'
'Ugly gold'

One other thing I noticed, I ran Optimize table bar, and then results are correct. I ran again 1st request again, and then the 3rd results are not correct anymore. So I really suspect something related to the full-text index.

Edit 2: Here is a dbFiddle : https://www.db-fiddle.com/f/iSXdTK7EzfoQ46RgDX7wF3/1

Answer 1

A self-join seems to get around the problem by tricking the db into thinking that it is not the same name column in the two predicates:

SELECT 
   t1.name 
FROM
    m as t1
    INNER JOIN m as t2 ON t1.id = t2.id 
WHERE
    t1.sId = 'N'
    AND (
      MATCH (t1.`name`) AGAINST ('"foo"' IN BOOLEAN MODE)
      OR t2.`name` LIKE '%foo%'
    );

As I mentioned in a comment above, in the original SQL, it seems that if the MATCH gives a TRUE result for any row, the LIKE will match to anything. This works around the problem by treating the two name columns as different even though they are from the same table.

EDIT: Interestingly, a correlated sub-select doesn't have the same beneficial effect:

SELECT 
   t1.name 
FROM
    m as t1
WHERE
    t1.sId = 'N'
    AND (
      MATCH (t1.`name`) AGAINST ('"foo"' IN BOOLEAN MODE)
      OR 1 = (SELECT 1 
              FROM m as t2 
              WHERE t1.id = t2.id 
              AND t2.`name` LIKE '%foo%' ) 
    );

Answer 2

I have a very strong feeling that what you described is what you would want for the queries to look like, but that you are actually using code to compose the queries and you have a bug in your code that makes the myQuery null... then you are matching against '%%' which always matches.

When you invoke a bug, you should post the actual queries, not some wishful interpretation of what the queries might look like when transcribed to actually fit the purpose.

Are you maybe using ? ? placeholders and you forgot to set a second value equal to the 1st when data binding?

Answer 3

This seems give the result that you want

SELECT 
   name
FROM
    m
WHERE
    sId = 'N'
    AND MATCH (`name`) AGAINST ('"foo"' IN BOOLEAN MODE)
    OR `name` LIKE '%foo%';