Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-33318

ORDER BY COLLATE improperly applied to non-character columns

Details

    Description

      When explicitly specifying collation on a non-character sort column , the column type is not taken into account and it may be sorted incorrectly. In 10.4 an error was thrown, but in 10.6 and 10.11 no error is thrown but sort results are unpredictable.

      create table ct (ts datetime);
      		 
      insert into ct select now();
      		 
      -- repeat a few more times
      		 
      select * from ct order by ts COLLATE utf8_bin;
      		 
      +---------------------+
      		 
      | ts                  |
      		 
      +---------------------+
      		 
      | 2024-01-26 10:25:18 |
      		 
      | 2024-01-26 10:25:41 |
      		 
      | 2024-01-26 10:25:40 |
      		 
      | 2024-01-26 10:25:38 |
      		 
      | 2024-01-26 10:25:34 |
      		 
      | 2024-01-26 10:25:31 |
      		 
      | 2024-01-26 10:25:28 |
      		 
      | 2024-01-26 10:25:25 |
      		 
      | 2024-01-26 10:25:22 |
      		 
      | 2024-01-26 10:25:44 |
      		 
      +---------------------+
      		 
      10 rows in set (0.000 sec)
      		 
      -- note that timestamps are not actually sorted correctly and come in some pseudo-random order, not even order of insertion.

      in 10.4 the same SQL results in

      ERROR 1253 (42000): COLLATION 'utf8_bin' is not valid for CHARACTER SET 'latin1'

      which is confusing because timestamp columns don't have collation, but at least it hints that there is a problem with the statement. Later versions silently produce wrong results instead.

      Attachments

        Issue Links

          is caused by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-24584 Selecting INT column with COLLATE utf8mb4_general_ci throws an error

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            People

              serg Sergei Golubchik
              vladzakh Vladimir "Bob" Zakharychev
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.