Bug #91091 Inconsistent SELECT...ORDER BY results with euckr charset / euckr_bin collation
Submitted: 31 May 13:08 Modified: 31 May 13:37
Reporter: Marcos Albe Email Updates:
Status: Verified 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 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 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 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 13:37] Umesh Shastry
Hello Marcos,

Thank you for the report and test case.

Thanks,
Umesh
[31 May 13:38] Umesh Shastry
-- 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 8:38] Sveta Smirnova
Bug is not repeatable with 8.0
[19 Jun 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 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 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.