2
SELECT
  `name`,
  MATCH (`name`) AGAINST ('"gold"' IN BOOLEAN MODE) as score
FROM
foo
WHERE 
  group = '1' 
  AND  MATCH (`name`) AGAINST ('"gold"' IN BOOLEAN MODE) 
ORDER BY score;

returns 4 results :

name            | score
----------------|--------------------
Nice Gold       | 0.06243875250220299
Super Nice gold | 0.06243875250220299
Ugly gold       | 0.06243875250220299
Fancy gold      | 0.06243875250220299

Now I would like to use the score alias in the WHERE clause to increase readability, but as it's not recognized by WHERE, I'm using HAVING like :

SELECT
  `name`,
  MATCH (`name`) AGAINST ('"gold"' IN BOOLEAN MODE) as score
FROM
foo
WHERE group = '1'
HAVING score > 0
ORDER BY score;

and now, some results are really weird :

name            | score
----------------|--------------------
Nice Gold       | 0.06243875250220299
Super Nice gold | 0.06243875250220299
Nice Diamond    | 0
Custom          | 0
Ugly gold       | 0.06243875250220299
Fancy gold      | 0.06243875250220299

Why are Nice Diamond and Custom displayed ? I'm using Aurora MySql on AWS RDS.

GMB
  • 216,147
  • 25
  • 84
  • 135
egoz
  • 354
  • 3
  • 16
  • 1
    Having is checking the values AFTER aggregating. Where the WHERE clause is excluding rows BEFORE they are aggregated. E.g. Where 1,2,3,4,5 > 3 will just return 4 and 5 but HAVING > 3 will return the full aggregated set because the total is > 3 – Jon Sep 09 '19 at 15:10
  • @Jon - what aggregation? There is no `GROUP BY` here. – Martin Smith Sep 09 '19 at 15:17
  • well if you're using HAVING you should have a GROUP BY. I didn't read his full post. – Jon Sep 09 '19 at 15:26
  • 1
    @RaymondNijland - I'm not arguing that MySQL is standards compliant. This would be a scalar aggregate in other systems and not allow non aggregated columns to be selected. I am just saying that I don't see the relevance of the first comment to answering the question asked. There is no aggregation as far as I can see so how can the values pre and post aggregation be different? – Martin Smith Sep 09 '19 at 15:40
  • 1
    Well @MartinSmith point taken ... There is a valid overriding SQL 1999+ (optional) rule on that SQL 92 rule that you are mentioning which is called functional dependency but that is not the case here as fulltext indexes are not unique or primary... *" There is no aggregation as far as I can see so how can the values pre and post aggregation be different?"* I am not sure i have a feeling that the cause is that the topicstarter has mixed WHERE with HAVING on a calculated column... We need to have the topicstarter table structure -> `SHOW CREATE TABLE foo` to give better answers. – Raymond Nijland Sep 09 '19 at 15:54
  • 1
    Since it appears MATCH is resulting in floating point values, it might simply be that "0" is not really "0". You could try something like `HAVING score > 0.00000000001` – Uueerdo Sep 09 '19 at 16:07
  • 1
    Mysql with non strict mode is so confusing – Oto Shavadze Sep 09 '19 at 16:13
  • @Uueerdo - How would that work with the implicit cast to boolean in the first query? Would MySQL treat values slightly greater than `0` but very small as false? – Martin Smith Sep 09 '19 at 16:15
  • @MartinSmith I have no idea, just throwing out ideas; but I figure it is possible some behind the scenes implementation details decide to convert the direct result of MATCH (in the WHERE) differently than the datatype it ends up using it's field in the resultset (when checking in the HAVING). – Uueerdo Sep 09 '19 at 16:19
  • i think that Amazon recompiled MySQL with some (buggy) changes regarding implicit datatype casts, as i [tested](https://www.db-fiddle.com/f/iyLeGSmhCvWQMhCbH8DyvD/2) with a minimal table structure needed to run the topicstarters query on multiple vanilla MySQL versions and i was not able to reproduce the topicstarters results on anny vanilla MySQL version.... – Raymond Nijland Sep 09 '19 at 16:24
  • 1
    @egoz . . . Something is wrong with your question. The results are also not being sorted. Perhaps you have a semicolon after the `where` clause. – Gordon Linoff Sep 09 '19 at 16:40
  • @GordonLinoff, Yes it's really weird, but there is not semicolon... – egoz Sep 10 '19 at 07:25
  • What is the `CREATE TABLE`? – Martin Smith Sep 10 '19 at 08:57

0 Answers0