Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2.4, 10.3.11, 10.2.19
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
- duplicates
-
MDEV-23144 Combination of GENERATE ALWAYS and COLLATE does not work
- Closed
- is duplicated by
-
MDEV-20387 COLLATE option not accepted in generated column definition
- Closed
- links to
We have a system in place that mirrors data from an external source. Since the source data is case sensitive we default the mirrored tables to utf8_bin. Our software can override the collation for specific columns, if necessary. We fix some oddities in the source schema by augmenting the mirrored tables using generated columns.
At the moment we are working on indexing a generated column. Creating the index works fine, but the problem is the index should be case insensitive. For normal columns this is no problem, we can tell our software to change the DDL and set the collation of one column to something else, and that will work fine. For the generated column however, this is not possible because of this bug.
We could change our software to switch everything around: have the tables default to a case insensitive collation, and then by default set the collation for all columns back to utf8_bin. This would work, but we find it kind of risky (lots of testing with lots of data) and costly for something that is essentially a workaround for a bug in MariaDB.
I'd love to help fix this bug, but I have no experience with C/C++ or with database server software. Is there any way I can help progress this bug?