MySQL Bugs: #91091: Inconsistent SELECT...ORDER BY results with euckr charset / euckr

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

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"

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.

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

Hello Marcos,

Thank you for the report and test case.

Thanks,
Umesh

-- 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

Bug is not repeatable with 8.0

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.

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.

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.

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