There is a difference in results between MySQL5 and MySQL8 versions in a SELECT statement that uses an aggregate function. I want to know the reason.
SELECT
A.student_id,
A.school_id,
max(B.number) AS max_number
FROM A
LEFT JOIN B
ON B.student_id = A.student_id
WHERE A.student_id = 1
AND B.del_flg = 0
MySQL5
| A.student_id | A.school_id | max_number |
|---|---|---|
| 1 | 1 | NULL |
MySQL8
| A.student_id | A.school_id | max_number |
|---|---|---|
| NULL | NULL | NULL |
I want to make sure that the result does not change even if the version is upgraded to MySQL8.
A simple SELECT statement without an aggregate function yields 0 rows in both MySQL versions. However, using aggregate functions gives different results.
Are aggregate functions in MySQL 5 buggy?
