MySQL Bugs: #114666: Incorrect results for combination of left join, match & group by

Bug #114666	Incorrect results for combination of left join, match & group by
Submitted:	17 Apr 2024 11:28	Modified:	18 Apr 2024 10:23
Reporter:	Tom Ward	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: FULLTEXT search	Severity:	S6 (Debug Builds)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
With a combination of LEFT JOIN, MATCH and GROUP BY, MySQL 8 returns different and unexpected results compared to MySQL 5. If you remove any of these clauses, it returns correct results. 

How to repeat:
Create some tables:
```
 CREATE TABLE t1(id int primary KEY, a CHAR(20), fulltext(a)) ENGINE = InnoDB;
 INSERT INTO t1 VALUES(1, 'solar butterfly');
 INSERT INTO t1 VALUES(2, 'trees butterfly');
 CREATE TABLE t2(id int primary KEY, t1_id int, other int) ENGINE = InnoDB;
 INSERT INTO t2 VALUES (1,1,5), (2,1,6), (3,1,7), (4,1,8), (5,1,9), (6,2,10), (7,2,11), (8,2,12), (9,2,13), (10,2,14);
```

These queries return the expected results:

```
 SELECT id, MATCH(a) AGAINST('solar') FROM t1;
```
```
1	0.0906190574169159
2	0
```

```
 SELECT t1.id, MATCH(a) AGAINST ('solar') FROM t1 left join t2 on t1.id = t2.t1_id;
```
```
1	0.0906190574169159
1	0.0906190574169159
1	0.0906190574169159
1	0.0906190574169159
1	0.0906190574169159
2	0
2	0
2	0
2	0
2	0
```

But this query returns different results on `5.7.42-0ubuntu0.18.04.1` compared to `8.0.30`.

```
 SELECT t1.id, MATCH(a) AGAINST ('solar') FROM t1 left join t2 on t1.id = t2.t1_id group by t1.id;
```
v5.7 returns this:
```
1	0.0906190574169159
2	0
```
v8 returns this:
```
1	0
2	0
```

Suggested fix:
There is a workaround:

```
SELECT t1.id, MAX(MATCH(a) AGAINST ('solar')) FROM t1 left join t2 on t1.id = t2.t1_id group by t1.id;
```

which returns 

```
1	0.0906190574169159
2	0
```

as expected

Hi Mr. Ward,

Thank you for your bug report.

These are the results that we get with MySQL  8.0.36:

id	MATCH(a) AGAINST('solar')
1	0.0906190574169159
2	0

id	MATCH(a) AGAINST ('solar')
1	0.0906190574169159
1	0.0906190574169159
1	0.0906190574169159
1	0.0906190574169159
1	0.0906190574169159
2	0
2	0
2	0
2	0
2	0

These are the results that we get with MySQL 5.7.44:

id	MATCH(a) AGAINST('solar')
1	0.0906190574169159
2	0

id	MATCH(a) AGAINST ('solar')
1	0.0906190574169159
1	0.0906190574169159
1	0.0906190574169159
1	0.0906190574169159
1	0.0906190574169159
2	0
2	0
2	0
2	0
2	0

We also think that these are correct results, considering the LEFT JOIN .....

Can't repeat.

I just tried on a different ubuntu server using 8.0.36 and saw the same issue. 

mysql --version
mysql  Ver 8.0.36 for Linux on x86_64 (MySQL Community Server - GPL)

Package: mysql-server
Version: 8.0.36-1ubuntu20.04

---

mysql> CREATE TABLE t1(id int primary KEY, a CHAR(20), fulltext(a)) ENGINE = InnoDB;

Query OK, 0 rows affected (0.36 sec)

mysql>  INSERT INTO t1 VALUES(1, 'solar butterfly');
Query OK, 1 row affected (0.02 sec)

mysql>  INSERT INTO t1 VALUES(2, 'trees butterfly');
Query OK, 1 row affected (0.01 sec)

mysql>  CREATE TABLE t2(id int primary KEY, t1_id int, other int) ENGINE = InnoDB;
Query OK, 0 rows affected (0.05 sec)

mysql>  INSERT INTO t2 VALUES (1,1,5), (2,1,6), (3,1,7), (4,1,8), (5,1,9), (6,2,10), (7,2,11), (8,2,12), (9,2,13), (10,2,14);
Query OK, 10 rows affected (0.01 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql>
mysql> SELECT t1.id, MATCH(a) AGAINST ('solar') FROM t1 left join t2 on t1.id = t2.t1_id group by t1.id;
+----+----------------------------+
| id | MATCH(a) AGAINST ('solar') |
+----+----------------------------+
|  1 |                          0 |
|  2 |                          0 |
+----+----------------------------+
2 rows in set (0.01 sec)

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.36    |
+-----------+
1 row in set (0.00 sec)

Hi Mr. Ward,

We have tested your results on Oracle Linux 8.9, macOS 14.4 and Windows 10.

For each of these operating systems we used our binaries, downloadable from our site.

They all produced the same result, which is the one that we have published.

Hi there,
I have just downloaded & installed mysql-8.0.36-macos14-x86_64.dmg from your site at https://dev.mysql.com/downloads/file/?id=525501 MD5: 06e0752afc92ef8ba153236045249d92 and it reproduces the bug.

Hi,

In that case you are using some obsolete option in the configuration file.

Try to find out which .......

Here is how to reproduce the bug using the official MySQL 8.0.36-oracle docker image - the official docker image must also be using the same obsolete option in its default configuration file. 

---

$ docker run --name some-mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:8.0.36-oracle
Unable to find image 'mysql:8.0.36-oracle' locally
8.0.36-oracle: Pulling from library/mysql
bd37f6d99203: Pull complete
d2433cba0951: Pull complete
13702d9fe3c3: Pull complete
83bcc87284a1: Pull complete
c38d8660e1fa: Pull complete
7e1bc321f421: Pull complete
bddd54b9c549: Pull complete
4eaae1e844ac: Pull complete
5196e1e87d8f: Pull complete
6586d096303c: Pull complete
cf55ff1c80af: Pull complete
Digest: sha256:ba4d18421e71eb0e8429b5c8465c1bb30d17bc89c7e9ccd2a61b375b23ef93d9
Status: Downloaded newer image for mysql:8.0.36-oracle
24d41efc8985c84184eda3f3969115f40711bd148aa8ca667f296f64535924d5

$ docker exec some-mysql mysql --user=root -pmy-secret-pw -e "select @@version; use mysql; CREATE TABLE t1(id int primary KEY, a CHAR(20), fulltext(a)) ENGINE = InnoDB;
 INSERT INTO t1 VALUES(1, 'solar butterfly');
 INSERT INTO t1 VALUES(2, 'trees butterfly');
 CREATE TABLE t2(id int primary KEY, t1_id int, other int) ENGINE = InnoDB;
 INSERT INTO t2 VALUES (1,1,5), (2,1,6), (3,1,7), (4,1,8), (5,1,9), (6,2,10), (7,2,11), (8,2,12), (9,2,13), (10,2,14);
SELECT t1.id, MATCH(a) AGAINST ('solar') FROM t1 left join t2 on t1.id = t2.t1_id group by t1.id;
 "
mysql: [Warning] Using a password on the command line interface can be insecure.
@@version
8.0.36
id	MATCH(a) AGAINST ('solar')
1	0
2	0

Hi Mr. Ward,

We managed to reproduce the bug with our debug binary.

This is now a verified bug report.