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

Document that NATURAL_SORT_KEY virtual column should be longer than the base column (and other notes)

    XMLWordPrintable

Details

    Description

      Contrary to the example in the blog post about natural sort, the virtual column should be longer than the base column. This is due to the specifics of the design, which makes values in the virtual columns be longer than the originally inserted values.

      We can't know at the table creation how much longer it should be, so I suppose there is no point throwing an error or a warning upon table creation, but at least it should be documented. Otherwise, even a simple example like the on in the blog post only works by pure chance.

      Here is the same example, only instead of ('a1') it inserts ('a10'):

      MariaDB [test]> create table t(c varchar(3), k varchar(3) as (natural_sort_key(c)) invisible);
      Query OK, 0 rows affected (0.038 sec)
       
      MariaDB [test]> insert into t(c) values ('b1'),('a2'),('a11'),('a10');
      Query OK, 4 rows affected (0.006 sec)
      Records: 4  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> select * from t order by k;
      +------+
      | c    |
      +------+
      | a2   |
      | a11  |
      | a10  |
      | b1   |
      +------+
      4 rows in set (0.001 sec)
      

      This is because both a11 and a10 cause an overflow in column k.
      The overflow is currently undetected due to the bug MDEV-24582, but even if it is detected and causes an error or truncation with a warning, it is hardly what the users want. Instead, they should make sure that the column is longer from the start.

      Attachments

        Issue Links

          Activity

            People

              greenman Ian Gilfillan
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.