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

Can't specify collation for virtual columns

    XMLWordPrintable

    Details

      Description

      You can specify the character set for a virtual column but not the collation. I.e. the following is not allowed:

      CREATE TABLE t1(c1 CHAR(10), c2 CHAR(10) COLLATE utf8_bin AS(c1));
      

      but the following is allowed:

      CREATE TABLE t1(c1 CHAR(10), c2 CHAR(10) CHARACTER SET utf8 AS(c1));
      

      The reason why one would want to do this would be to allowing several indexes on the same column using different collations. This is possible to achieve using a workaround, and once this is done index lookups and sorting works as expected. To create a table with UTF8 column and having both swedish and german collations:

      CREATE TABLE t1(c1 CHAR(10) CHARACTER SET utf8 COLLATE utf8_swedish_ci,
        c2 CHAR(10) AS (c1),
        KEY(c1),
        KEY(c2)) CHARACTER SET utf8 COLLATE utf8_german2_ci;
      

      Using this construct I can now select from the table and use an indexed sort using either german or swedish collations. But I can not explicitly set the collation of the virtual column here, I can only set the source column (c1) to a dofferent collation than the default for the table and let the table default affect the virtual column.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              serg Sergei Golubchik
              Reporter:
              karlsson Anders Karlsson
              Votes:
              4 Vote for this issue
              Watchers:
              8 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: