Bug #107201 Result of fulltext match is on the wrong row with GROUP BY (reproducible)
Submitted: 3 May 2022 18:13 Modified: 4 May 2022 5:50
Reporter: L B Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: FULLTEXT search Severity:S2 (Serious)
Version:8.0.29 OS:Ubuntu
Assigned to: CPU Architecture:x86

[3 May 2022 18:13] L B
Description:
The first query below yields this result when matching on "bbb":

+-------+---------------------+
| text1 | text1_match         |
+-------+---------------------+
| aaa   | 0.22764469683170319 |
| bbb   |                   0 |
| ccc   |                   0 |
+-------+---------------------+

The second query below (with complete GROUP BY) yields this result when matching on "bbb":

+-------+---------------------+
| text1 | text1_match         |
+-------+---------------------+
| aaa   |                   0 |
| bbb   | 0.22764469683170319 |
| ccc   |                   0 |
+-------+---------------------+

How to repeat:

-- Setup

create table text1 (
    id int,
    fooid int,
    text1 text,
    fulltext key text1 (text1),
    primary key (id)
  );

create table foo (
    id int,
    primary key (id)
  );

insert into text1 values (1, 1, 'aaa'), (2, 2, 'bbb'), (3, 3, 'ccc');

insert into foo values (1), (2), (3);

-- Yields wrong result

select text1
      ,match(text1.text1) against ('bbb' in boolean mode) text1_match

      from text1

      left join foo on text1.fooid=foo.id

      group by text1.id -- Note: incomplete group by

  ;

-- Yields correct result

select text1
      ,match(text1.text1) against ('bbb' in boolean mode) text1_match

      from text1

      left join foo on text1.fooid=foo.id

      group by text1.id, foo.id -- Note: we group on foo.id

  ;
[4 May 2022 5:50] MySQL Verification Team
Hello!

Thank you for the report and test case.

regards,
Umesh