Bug #79571 "select distinct" are returning incorrect values

• Submitted: 9 Dec 2015 10:02
• Modified: 2 Dec 2019 22:09
• Reporter: Su Dylan
• Status: Closed
• Category: MySQL Server: DML
• Severity: S2 (Serious)
• Version: 5.0/5.1/5.5/5.6/5.7
• OS: Any
• CPU Architecture: Any

[9 Dec 2015 10:02] Su Dylan

Description:
Output:
=======
mysql> drop table if exists t1;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t1(a bigint);
insert into t1 Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values (-9223372036854775808), (-9223372036854775807), (9223372036854775806), (9223372036854775807);
ct ceil(a) from t1 ordeQuery OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select ceil(a) from t1 order by 1;
ct dis+----------------------+
| ceil(a)              |
+----------------------+
| -9223372036854775808 |
| -9223372036854775807 |
|  9223372036854775806 |
|  9223372036854775807 |
+----------------------+
4 rows in set, 4 warnings (0.00 sec)

mysql> select distinct ceil(a) from t1 order by 1;
+-------------------+
| ceil(a)           |
+-------------------+
| -9999999999999999 |
|  9999999999999999 |
+-------------------+
2 rows in set (0.00 sec)

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

Problem:
========
With distinct, the output value is seriously wrong.

How to repeat:
drop table if exists t1;
create table t1(a bigint);
insert into t1 values (-9223372036854775808), (-9223372036854775807), (9223372036854775806), (9223372036854775807);
select ceil(a) from t1 order by 1;
select distinct ceil(a) from t1 order by 1;

Suggested fix:
With distinct, the output values should be the distinct subset of the original output set.

[9 Dec 2015 12:06] MySQL Verification Team

Result from 5.0/5.5/5.6/5.7

Attachment: result_bug79571.txt (text/plain), 5.61 KiB.

[9 Dec 2015 12:09] MySQL Verification Team

Thank you for the bug report. All version affected: 5.0/5.1/5.5/5.6/5.7.

[2 Dec 2019 22:09] Roy Lyseng

Posted by developer:
 
Fixed in 8.0.18