Bug #81951 strcmp returns unexpected value

• Submitted: 21 Jun 2016 11:15
• Modified: 21 Jun 2016 12:21
• Reporter: 帅 Bang
• Status: Verified
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.6, 5.6.31, 5.5.50
• OS: Linux
• CPU Architecture: Any

[21 Jun 2016 11:15] 帅 Bang

Description:
mysql> select collation('1e100');
+--------------------+
| collation('1e100') |
+--------------------+
| utf8mb4_general_ci |
+--------------------+
1 row in set (0.00 sec)

mysql> select collation(1e100);
+------------------+
| collation(1e100) |
+------------------+
| binary           |
+------------------+
1 row in set (0.00 sec)

while, here comes the problem: 

mysql> select strcmp('1e100', 1e100);
+------------------------+
| strcmp('1e100', 1e100) |
+------------------------+
|                      0 |
+------------------------+
1 row in set (0.00 sec)

mysql> select strcmp('1E100', 1e100);
+------------------------+
| strcmp('1E100', 1e100) |
+------------------------+
|                     -1 |
+------------------------+
1 row in set (0.00 sec)

we can make a reasonable deduction that mysql uses collation utf8mb4_bin to compare them which is not reasonable

BTW, I found that mariadb gives a  reasonable result:

mariadb> select strcmp('1e100', 1e100);
+------------------------+
| strcmp('1e100', 1e100) |
+------------------------+
|                      0 |
+------------------------+
1 row in set (0.00 sec)

mariadb> select strcmp('1E100', 1e100);
+------------------------+
| strcmp('1E100', 1e100) |
+------------------------+
|                     0 |
+------------------------+
1 row in set (0.00 sec)

How to repeat:
select strcmp('1e100', 1e100);

select strcmp('1E100', 1e100);

Suggested fix:
strcmp('1E100', 1e100) returns 0 rather than -1

[21 Jun 2016 12:21] MySQL Verification Team

Hello Bang,

Thank you for the report and test case.

Thanks,
Umesh