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)




      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.


        Issue Links



              greenman Ian Gilfillan
              elenst Elena Stepanova
              0 Vote for this issue
              3 Start watching this issue



                Git Integration

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