Bug #111663 Same data, same SQL, return different results

• Submitted: 5 Jul 2023 7:38
• Modified: 12 Oct 2023 16:07
• Reporter: v v
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 5.7.42
• OS: CentOS (3.10.0-957.21.2.el7.x86_64)
• CPU Architecture: x86
• Tags: regression

[5 Jul 2023 7:38] v v

Description:
Same 4 rows, same SQL, different insert order, return different results!

How to repeat:
-------------- MySQL Version 5.7.23 --------------
-------------- TEST 1 --------------
drop table test_table;
create table test_table(id int,company varchar(10),department varchar(10));

insert into test_table values(1,'COM A','DEP A');
insert into test_table values(2,'COM B','DEP A');
insert into test_table values(3,'','DEP A');
insert into test_table values(4,'COM B','DEP A');

SELECT tmp.constr, COUNT(DISTINCT(id)) AS ids
FROM (
    SELECT id,concat(department, '-', company) as constr FROM test_table
) tmp
GROUP BY tmp.constr
ORDER BY tmp.constr DESC, ids DESC;
+-------------+-----+
| constr      | ids |
+-------------+-----+
| DEP A-COM B |   1 |
| DEP A-COM B |   1 |
| DEP A-COM A |   1 |
| DEP A-      |   1 |
+-------------+-----+
-------------- TEST 1 --------------

-------------- TEST 2 --------------
drop table test_table;
create table test_table(id int,company varchar(10),department varchar(10));

insert into test_table values(1,'COM A','DEP A');
insert into test_table values(2,'COM B','DEP A');
insert into test_table values(4,'COM B','DEP A'); # swap line 4 
insert into test_table values(3,'','DEP A'); # swap line 3

SELECT tmp.constr, COUNT(DISTINCT(id)) AS ids
FROM (
    SELECT id,concat(department, '-', company) as constr FROM test_table
) tmp
GROUP BY tmp.constr
ORDER BY tmp.constr DESC, ids DESC;
+-------------+-----+
| constr      | ids |
+-------------+-----+
| DEP A-COM B |   2 |
| DEP A-COM A |   1 |
| DEP A-      |   1 |
+-------------+-----+
-------------- TEST 2 --------------

[5 Jul 2023 7:49] v v

Test 8.0 is same results, 8.0 is OK!

[5 Jul 2023 8:13] v v

I tested 3 versions: 5.7.23, 5.7.37, 8.0.32.
5.7.23, 5.7.37 return different results, 5.7 abnormal!
8.0.32 return same results, 8.0 is OK!

[5 Jul 2023 8:24] v v

5.7 last two versions 5.7.41, 5.7.42 return different results, 5.7 abnormal!

[5 Jul 2023 9:20] MySQL Verification Team

Hello!

Thank you for the report and test case.
Observed that 5.7.42 is affected(8.0.33 seems to not affected).

regards,
Umesh

[12 Oct 2023 16:07] Jon Stephens

Fixed in MySQL 8.0.2 as part of WL#9603 (Non-aggregate Window Functions).

Closed.