MySQL Bugs: #76244: MySQL does order of operations incorrectly on distinct numeric string

Bug #76244	MySQL does order of operations incorrectly on distinct numeric string
Submitted:	10 Mar 2015 16:31	Modified:	25 Jul 2018 13:55
Reporter:	Brandon Johnson	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.6.18, 5.1.73+	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
This test below fails:

CREATE TABLE `l` (
  `a_id` int(11) NOT NULL,
  `l_id` int(11) NOT NULL,
  `amount` decimal(5,2) DEFAULT NULL
);
INSERT INTO `l` VALUES (1,1,'50.00'),(2,2,'50.00'),(3,3,'60.00'),(3,4,'70.00'),(2,5,'40.00'),(3,6,'20.00'),(2,7,'50.00'),(3,8,'10.00');

mysql> select distinct(concat(l.amount, 'x', l.l_id)) from l; -- number of rows I want.
+---------------------------------+
| (concat(l.amount, 'x', l.l_id)) |
+---------------------------------+
| 50.00x1                         |
| 50.00x2                         |
| 60.00x3                         |
| 70.00x4                         |
| 40.00x5                         |
| 20.00x6                         |
| 50.00x7                         |
| 10.00x8                         |
+---------------------------------+
8 rows in set (0.00 sec)

mysql> select distinct(concat(l.amount, 'x', l.l_id))*1 from l; -- This drops the mathematically calculated duplicates, even though the math is outside of the distinct.
+-----------------------------------+
| (concat(l.amount, 'x', l.l_id))*1 |
+-----------------------------------+
|                                50 |
|                                60 |
|                                70 |
|                                40 |
|                                20 |
|                                10 |
+-----------------------------------+
6 rows in set (0.00 sec)

Anyone know why?

Yes, I'm aware that data can be gathered via
"select sum(l.amount) from l group by l_id;" but this method should work as well. It would greatly improve the performance of many:many:many queries with mathematical values (basically being the math equivalent to group_concat for strings in this case).

How to repeat:
See description.

Suggested fix:
Correct the order of operations of the distinct and *1

Please check when converting data type the type is floating number:

miguel@miguel-lap:~/dbs $ 5.6/bin/mysql -uroot -p --socket=/tmp/mysql56.sock --local-infile=1 --prompt="mysql 5.6 > " --column-type-info
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.42 Source distribution BUILD: 2018-JUN-13

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.6 > use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql 5.6 > select distinct(concat(l.amount, 'x', l.l_id)) from l;  
Field   1:  `(concat(l.amount, 'x', l.l_id))`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  utf8_general_ci (33)
Length:     57
Max_length: 7
Decimals:   0
Flags:      NUM 

+---------------------------------+
| (concat(l.amount, 'x', l.l_id)) |
+---------------------------------+
| 50.00x1                         |
| 50.00x2                         |
| 60.00x3                         |
| 70.00x4                         |
| 40.00x5                         |
| 20.00x6                         |
| 50.00x7                         |
| 10.00x8                         |
+---------------------------------+
8 rows in set (0,00 sec)

mysql 5.6 > select distinct(concat(l.amount, 'x', l.l_id))*1 from l;                                                                      Field   1:  `(concat(l.amount, 'x', l.l_id))*1`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       DOUBLE
Collation:  binary (63)
Length:     23
Max_length: 2
Decimals:   31
Flags:      NUM 

+-----------------------------------+
| (concat(l.amount, 'x', l.l_id))*1 |
+-----------------------------------+
|                                50 |
|                                60 |
|                                70 |
|                                40 |
|                                20 |
|                                10 |
+-----------------------------------+
6 rows in set (0,00 sec)

mysql 5.6 >

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".