Uploaded image for project: 'Percona Server'
  1. Percona Server
  2. PS-4513

Inconsistent SELECT...ORDER BY results with euckr charset / euckr_bin collation

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Done
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: 5.7.18-16
    • Fix Version/s: 5.6.41-84.1, 5.7.23-23
    • Component/s: None
    • Environment:

      Any Percona Server or MySQL starting with 5.7.18

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

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

      We also know 5.7.17 is NOT affected; bug starts with 5.7.18

      Further, it 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
      

       

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

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                yura.sorokin Yura Sorokin
                Reporter:
                marcos.albe Marcos Albe
              • Votes:
                1 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0 minutes
                  0m
                  Logged:
                  Time Spent - 1 day, 4 hours
                  1d 4h