Bug #108936 Value change caused by DISTINCT

• Submitted: 31 Oct 2022 12:58
• Modified: 13 Dec 2022 3:56
• Reporter: ZongYin Hao
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 8.0.30
• OS: Any
• CPU Architecture: Any
• Tags: distinct

[31 Oct 2022 12:58] ZongYin Hao

Description:
In theory, the result of sql2(DISTINCT) ⊆ the result of sql1:

SELECT ~f1 FROM (SELECT REPEAT(1234567890, 3) AS f1 FROM t) AS t1; -- sql1
SELECT ~f1 FROM (SELECT DISTINCT REPEAT(1234567890, 3) AS f1 FROM t) AS t1; -- sql2

However, the value 0 changed to 9223372036854775808 after adding DISTINCT, seems like a logical bug:

mysql> SELECT ~f1 FROM (SELECT REPEAT(1234567890, 3) AS f1 FROM t) AS t1; --sql1
+------+
| ~f1  |
+------+
|    0 |
|    0 |
|    0 |
+------+
3 rows in set, 3 warnings (0.00 sec)

mysql> SELECT ~f1 FROM (SELECT DISTINCT REPEAT(1234567890, 3) AS f1 FROM t) AS t1; --sql2
+---------------------+
| ~f1                 |
+---------------------+
| 9223372036854775808 |
+---------------------+
1 row in set, 1 warning (0.00 sec)

How to repeat:
drop table if exists t;
create table t (c1 int);
insert into t values (1), (2), (3);

SELECT ~f1 FROM (SELECT REPEAT(1234567890, 3) AS f1 FROM t) AS t1; -- sql1
SELECT ~f1 FROM (SELECT DISTINCT REPEAT(1234567890, 3) AS f1 FROM t) AS t1; -- sql2

Suggested fix:
I think sql2 should return 0.

[31 Oct 2022 14:16] MySQL Verification Team

Hello ZongYin Hao,

Thank you for the report and test case.

regards,
Umesh

[13 Dec 2022 3:56] ZongYin Hao

We look for the first version of the bug in all docker images (with format x.x.x) of https://hub.docker.com/_/mysql/tags 
We found that the bug first occurred in mysql:8.0.29, it cannot be reproduced in mysql:8.0.28:

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

mysql> SELECT ~f1 FROM (SELECT REPEAT(1234567890, 3) AS f1 FROM t) AS t1; -- sql1
+---------------------+
| ~f1                 |
+---------------------+
| 9223372036854775808 |
| 9223372036854775808 |
| 9223372036854775808 |
+---------------------+
3 rows in set (0.00 sec)

mysql> SELECT ~f1 FROM (SELECT DISTINCT REPEAT(1234567890, 3) AS f1 FROM t) AS t1; -- sql2
+---------------------+
| ~f1                 |
+---------------------+
| 9223372036854775808 |
+---------------------+
1 row in set, 1 warning (0.00 sec)

Hope it can be helpful for your debugging.