Bug #91091 Inconsistent SELECT...ORDER BY results with euckr charset / euckr_bin collation
Submitted: 31 May 2018 13:08 Modified: 16 Dec 2019 9:31
Reporter: Marcos Albe (OCA) Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:>= 5.7.18, 5.7.22 OS:Any
Assigned to: CPU Architecture:Any

[31 May 2018 13:08] Marcos Albe
Description:
A simple SELECT on a table with CHARSET=euckr COLLATE=euckr_bin returns different results each time is executed.

--------------
select c1 from t where c1='Eid4i*******11372012******O*****Sua9EiJ**************00' order by c1, c3
--------------

+---------------------------------------------------------+
| c1                                                      |
+---------------------------------------------------------+
| Eid4i*******11372012******O*****Sua9EiJ**************00 |
+---------------------------------------------------------+
1 row in set (0.00 sec)

--------------
select c1 from t where c1='Eid4i*******11372012******O*****Sua9EiJ**************00' order by c1, c3
--------------

+---------------------------------------------------------+
| c1                                                      |
+---------------------------------------------------------+
| Eid4i*******11372012******O*****Sua9EiJ**************00 |
| Eid4i*******11372012******O*****Sua9EiJ**************00 |
| Eid4i*******11372012******O*****Sua9EiJ**************00 |
+---------------------------------------------------------+
3 rows in set (0.00 sec)

Bug is not repeatable when using utf8 or latin1 (didn't tested others). 

Bug is not repeatable if sequence of statements is not executed before, but it's unclear what in the sequence is contributing to the bug (they are simple DELETE and SELECT queries).  So far we narrowed down to the sequence provided in the attached test.

How to repeat:
See attached test and simply run t.sh

Suggested fix:
Root cause is unclear, but for sure "make SELECT be deterministic regardless of character sets/collations"
[31 May 2018 13:09] Marcos Albe
Automated test to reproduce the issue; Run t.sh and then look at .txt outputs

Attachment: bug_data.zip (application/zip, text), 12.74 KiB.

[31 May 2018 13:14] Marcos Albe
Forgot to mention:

- 5.7.17 is NOT affected; bug starts with 5.7.18
- Does not happen if you remove c1 from the ORDER BY like:
select c1 from t where c1='Eid4i*******11372012******O*****Sua9EiJ**************00' order by  c3
[31 May 2018 13:37] MySQL Verification Team
Hello Marcos,

Thank you for the report and test case.

Thanks,
Umesh
[31 May 2018 13:38] MySQL Verification Team
-- 5.7.22

[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: ./bug_data/t.sh
--- e1.txt      2018-05-31 15:36:15.128725434 +0200
+++ e2.txt      2018-05-31 15:36:15.153725998 +0200
@@ -7,3 +7,5 @@ Eid4i*******11372012******haFEIouxia1Aro
 Eid4i*******11372012******haFEIouxia1Aroo2aaT_*****_******00
 c1
 Eid4i*******11372012******O*****Sua9EiJ**************00
+Eid4i*******11372012******O*****Sua9EiJ**************00
+Eid4i*******11372012******O*****Sua9EiJ**************00
[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: cat docs/INFO_SRC
commit: fc04cf3c6e54933e8720cd9754bef9acf9f36ccd
date: 2018-03-04 14:35:40 +0100
build-date: 2018-03-04 14:40:53 +0100
short: fc04cf3
branch: mysql-5.7.22-release

MySQL source 5.7.22
[2 Jun 2018 8:38] Sveta Smirnova
Bug is not repeatable with 8.0
[19 Jun 2018 15:41] Yura Sorokin
The issue has nothing to do with custom collations - it's a pure InnoDB bug.

It looks like the fix for Bug #23481444
"OPTIMISER CALL ROW_SEARCH_MVCC() AND READ THE INDEX APPLIED BY UNCOMMITTED ROWS" for 8.0
(https://github.com/mysql/mysql-server/commit/15dbdac4)
includes resetting 'm_end_range' member inside 'ha_innobase::reset_template()' in 'ha_innodb.cc' ('m_prebuilt->m_end_range = false'), whereas in the patches for 5.6 and 5.7 this statement is not included.
[19 Jun 2018 15:42] Yura Sorokin
Suggested patch for 5.6 (includes an MTR test case)

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: bug91091_5_6.diff (application/octet-stream, text), 69.48 KiB.

[19 Jun 2018 15:43] Yura Sorokin
Suggested patch for 5.7 (includes an MTR test case)

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: bug91091_5_7.diff (application/octet-stream, text), 69.14 KiB.

[22 Nov 2018 17:07] Yura Sorokin
This is a duplicate of 
https://bugs.mysql.com/bug.php?id=91032

Fixed in 5.7.24 and 8.0.13:

A query that scanned the primary key of a table did not return the
expected results