Bug #81940 Order by substr() returns incorrect result
Submitted: 21 Jun 2016 3:31 Modified: 24 Dec 2019 13:28
Reporter: Su Dylan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.7.8, 5.5.50, 5.6.31,5.7.13 OS:Any
Assigned to: CPU Architecture:Any

[21 Jun 2016 3:31] Su Dylan
Description:
Output:
===
mysql> create table t1 (a decimal(5,2));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values (1.1), (22.2), (333.33);
 round(a, 20), substr(round(a, 20), 7, 89)Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select a, round(a, 20), substr(round(a, 20), 7, 89) from t1 order by 3;
+--------+--------------------------+-----------------------------+
| a      | round(a, 20)             | substr(round(a, 20), 7, 89) |
+--------+--------------------------+-----------------------------+
| 333.33 | 333.33000000000000000000 | 000000000000000000          |
|  22.20 |  22.20000000000000000000 | 00000000000000000           |
|   1.10 |   1.10000000000000000000 | 0000000000000000            |
+--------+--------------------------+-----------------------------+
3 rows in set (0.00 sec)

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

Problem:
===
The following result is expected:
+--------+--------------------------+-----------------------------+
| a      | round(a, 20)             | substr(round(a, 20), 7, 89) |
+--------+--------------------------+-----------------------------+
|   1.10 |   1.10000000000000000000 | 0000000000000000            |
|  22.20 |  22.20000000000000000000 | 00000000000000000           |
| 333.33 | 333.33000000000000000000 | 000000000000000000          |
+--------+--------------------------+-----------------------------+
3 rows in set (0.00 sec)

How to repeat:

drop table if exists t1;
create table t1 (a decimal(5,2));
insert into t1 values (1.1), (22.2), (333.33);
select a, round(a, 20), substr(round(a, 20), 7, 89) from t1 order by 3;

Suggested fix:
Result the following result:
+--------+--------------------------+-----------------------------+
| a      | round(a, 20)             | substr(round(a, 20), 7, 89) |
+--------+--------------------------+-----------------------------+
|   1.10 |   1.10000000000000000000 | 0000000000000000            |
|  22.20 |  22.20000000000000000000 | 00000000000000000           |
| 333.33 | 333.33000000000000000000 | 000000000000000000          |
+--------+--------------------------+-----------------------------+
3 rows in set (0.00 sec)
[21 Jun 2016 4:55] MySQL Verification Team
Hello Su Dylan,

Thank you for the report and test case.
Observed this with 5.5.50, 5.6.31 and 5.7.13.

Thanks,
Umesh
[24 Dec 2019 13:28] Roy Lyseng
Posted by developer:
 
Fixed in 5.7.29