| Bug #119995 | Function find_in_set return wrong result | ||
|---|---|---|---|
| Submitted: | 6 Mar 9:51 | Modified: | 27 Apr 10:07 |
| Reporter: | Jingqi Tian (OCA) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 8.0.45 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[9 Mar 8:51]
Roy Lyseng
Thank you for the bug report. Verified as described.
[9 Mar 9:55]
Jingqi Tian
Hello, Roy Lyseng, this is my bugfix of bug#119995 based on 9.6.0. (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
Contribution: bug#119995.diff (application/octet-stream, text), 1.42 KiB.
[24 Apr 8:19]
Roy Lyseng
Thank you for the contribution! However, we decided that this optimization had an effect in very few cases, and that the code could be simplified (and thus become less error-prone) by removing the optimization.
[27 Apr 10:07]
Edward Gilmore
Posted by developer: Added the following note to the MySQL Server 9.7.0 release notes: Under certain circumstances, find_in_set() could return incorrect results. Our thanks to Jingqi Tian for the contribution.
Description: The function find_in_set may return wrong result. How to repeat: 1. Set collation_connection set collation_connection = 'utf8mb4_0900_ai_ci'; 2. Execute query select find_in_set('ắ', 'a'); +-------------------------+ | find_in_set('ắ', 'a') | +-------------------------+ | 0 | +-------------------------+ However, when the collation_connection is 'utf8mb4_0900_ai_ci', 'ắ' should be equal to 'a'. The result should be 1. To further prove that this is a bug, we can test: select find_in_set('ắ', 'a,b,c'); +-----------------------------+ | find_in_set('ắ', 'a,b,c') | +-----------------------------+ | 1 | +-----------------------------+ The result is 1. Clearly, 'ắ' should be equal to 'a'.