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
;