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
The MATCH function searches for a specified item in a range of cells, and then returns the relative position of that item in the range. For example, if the range A1:A3 contains the values 5, 25, and 38, then the formula =MATCH(25,A1:A3,0) returns the number 2, because 25 is the second item in the range.
If you believe that the data is present in the spreadsheet, but MATCH is unable to locate it, it may be because: The cell has unexpected characters or hidden spaces. The cell may not be formatted as a correct data type. For example, the cell has numerical values, but it may be formatted as Text.
If the compared cells are exactly equal, the function returns TRUE, FALSE otherwise. And then, the MATCH function compares TRUE (which is its lookup_value ) with each value in the array returned by EXACT, and returns the position of the first match.
When you combine each one of them with an IF statement, they read like this: AND – =IF(AND(Something is True, Something else is True), Value if True, Value if False) OR – =IF(OR(Something is True, Something else is True), Value if True, Value if False) NOT – =IF(NOT(Something is True), Value if True, Value if False)
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%' )
);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With