PolarDB:Generated columns

Limitations

Generated columns in PolarDB-X share MySQL's standard constraints and add several distributed-specific ones.

Shared with MySQL

• Default values are not supported.

• AUTO_INCREMENT cannot be set on a generated column, and a generated column cannot reference an AUTO_INCREMENT column.

• Non-deterministic functions — such as UUID(), CONNECTION_ID(), and NOW() — cannot be used in a generated column expression.

• Variables cannot be used in expressions.

• Subqueries cannot be used in expressions.

• Values of generated columns cannot be specified in INSERT or UPDATE statements. The database always computes them automatically.

PolarDB-X-specific

• Generated columns cannot be added to tables that have cold data archiving enabled.

• VIRTUAL and STORED columns cannot be used as partition keys, primary keys, or unique keys.

• VIRTUAL and STORED columns cannot reference stored functions.

• If a global secondary index (GSI) includes VIRTUAL or STORED generated columns, the GSI must also include every column referenced in those generated columns' expressions.

• A LOGICAL generated column cannot reference a VIRTUAL or STORED generated column in its expression.

• The type of a LOGICAL generated column and the types of columns referenced in its expression cannot be changed after creation.

• LOGICAL generated columns and their referenced columns support only these data types:

  • Integer types: BIGINT, INT, MEDIUMINT, SMALLINT, TINYINT

  • Date types: DATETIME, DATE, TIMESTAMP (columns with ON UPDATE CURRENT_TIMESTAMP are not supported)

  • String types: CHAR, VARCHAR

Examples: VIRTUAL and STORED columns

Example 1: Auto-compute a derived value

Create a table where the hypotenuse of a right triangle is computed as a generated column:

CREATE TABLE triangle (
  sidea DOUBLE,
  sideb DOUBLE,
  sidec DOUBLE AS (SQRT(sidea * sidea + sideb * sideb))
);

INSERT INTO triangle (sidea, sideb) VALUES (1, 1), (3, 4), (6, 8);

SELECT * FROM triangle;

Result:

+-------+-------+--------------------+
| sidea | sideb | sidec              |
+-------+-------+--------------------+
|   1.0 |   1.0 | 1.4142135623730951 |
|   3.0 |   4.0 |                5.0 |
|   6.0 |   8.0 |               10.0 |
+-------+-------+--------------------+

Example 2: Generated column in a partitioned table

Create a partitioned table with a VIRTUAL generated column b:

CREATE TABLE `t1` (
  `a` int(11) NOT NULL,
  `b` int(11) GENERATED ALWAYS AS (`a` + 1),
  PRIMARY KEY (`a`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 dbpartition by hash(`a`);

INSERT INTO t1 (a) VALUES (1);

SELECT * FROM t1;

Result:

+---+---+
| a | b |
+---+---+
| 1 | 2 |
+---+---+

Examples: LOGICAL columns

LOGICAL generated columns are the right choice when you need flexible partitioning based on a derived value, or when you want to use a UDF in the expression.

Example 1: Partition by a substring of a string column

Use the last two characters of column b as the partition key:

CREATE TABLE `t2` (
  `a` int(11) NOT NULL,
  `b` varchar(32) DEFAULT NULL,
  `c` varchar(2) GENERATED ALWAYS AS (SUBSTR(`b`, -2)) LOGICAL,
  PRIMARY KEY (`a`),
  KEY `auto_shard_key_c` USING BTREE (`c`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 dbpartition by hash(`c`);

Example 2: Reference a UDF in a LOGICAL generated column

First, create the user-defined function my_abs:

DELIMITER &&
CREATE FUNCTION my_abs (
  a INT
)
RETURNS INT
BEGIN
  IF a < 0 THEN
    RETURN -a;
  ELSE
    RETURN a;
  END IF;
END&&
DELIMITER ;

Then create a table with a LOGICAL generated column that calls my_abs:

CREATE TABLE `t3` (
  `a` int(11) NOT NULL,
  `b` int(11) GENERATED ALWAYS AS (MY_ABS(`a`)) LOGICAL,
  PRIMARY KEY (`a`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 dbpartition by hash(`b`);

INSERT INTO t3 (a) VALUES (1), (-1);

Verify that queries on b are pushed down to the data node:

EXPLAIN SELECT * FROM t3 WHERE b = 1;

+-----------------------------------------------------------------------------------------------------------+
| LOGICAL EXECUTIONPLAN                                                                                     |
+-----------------------------------------------------------------------------------------------------------+
| LogicalView(tables="TEST_000002_GROUP.t3_WHHZ", sql="SELECT `a`, `b` FROM `t3` AS `t3` WHERE (`b` = ?)") |
+-----------------------------------------------------------------------------------------------------------+

SELECT * FROM t3 WHERE b = 1;

+----+------+
| a  | b    |
+----+------+
| -1 |    1 |
|  1 |    1 |
+----+------+

Limitations

Examples

Example 1: Local index on a VIRTUAL column

This example indexes a JSON field by extracting it into a VIRTUAL generated column. JSON columns cannot be indexed directly in PolarDB-X (or MySQL), so this pattern is the standard workaround:

CREATE TABLE t4 (
    a BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    c JSON,
    g INT AS (c->"$.id") VIRTUAL
) DBPARTITION BY HASH(a);

CREATE INDEX `i` ON `t4`(`g`);

INSERT INTO t4 (c) VALUES
  ('{"id": "1", "name": "Fred"}'),
  ('{"id": "2", "name": "Wilma"}'),
  ('{"id": "3", "name": "Barney"}'),
  ('{"id": "4", "name": "Betty"}');

EXPLAIN EXECUTE SELECT c->>"$.name" AS name FROM t4 WHERE g > 2;

+------+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id   | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+------+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| 1    | SIMPLE      | t4    | NULL       | range | i             | i    | 5       | NULL | 1    | 100      | Using where |
+------+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+

The execution plan confirms that index i is used.

Example 2: Local index on a LOGICAL column

CREATE TABLE t5 (
    a BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    c varchar(32),
    g char(2) AS (substr(`c`, 2)) LOGICAL
) DBPARTITION BY HASH(a);

CREATE INDEX `i` ON `t5`(`g`);

INSERT INTO t5 (c) VALUES
  ('1111'),
  ('1112'),
  ('1211'),
  ('1311');

EXPLAIN EXECUTE SELECT c AS name FROM t5 WHERE g = '11';

+------+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------+
| id   | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                    |
+------+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------+
| 1    | SIMPLE      | t5    | NULL       | ref  | i             | i    | 8       | NULL | 4    | 100.00   | Using XPlan, Using where |
+------+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------+

Example 3: Global index on a LOGICAL column

CREATE TABLE t6 (
    a BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    c varchar(32),
    g char(2) AS (substr(`c`, 2)) LOGICAL
) DBPARTITION BY HASH(a);

CREATE GLOBAL INDEX `g_i` ON `t6`(`g`) COVERING(`c`) DBPARTITION BY HASH(`g`);

INSERT INTO t6 (c) VALUES
  ('1111'),
  ('1112'),
  ('1211'),
  ('1311');

EXPLAIN SELECT c AS name FROM t6 WHERE g = '11';

+---------------------------------------------------------------------------------------------------------------------+
| LOGICAL EXECUTIONPLAN                                                                                               |
+---------------------------------------------------------------------------------------------------------------------+
| IndexScan(tables="TEST_DRDS_000000_GROUP.g_i_J1MT", sql="SELECT `c` AS `name` FROM `g_i` AS `g_i` WHERE (`g` = ?)") |
+---------------------------------------------------------------------------------------------------------------------+

Limitations

• Expression indexes are disabled by default. Enable the feature with:

  SET GLOBAL ENABLE_CREATE_EXPRESSION_INDEX = TRUE;

• Global indexes are not supported.

• Unique indexes are not supported.

• Expression indexes cannot be created in a CREATE TABLE statement. Create the table first, then use ALTER TABLE or CREATE INDEX.

• Dropping an expression index with DROP INDEX does not delete the auto-created generated columns. Delete them manually with ALTER TABLE in DRDS mode or AUTO mode.

Examples

Example 1: Create an expression index

1. Create the table:

   CREATE TABLE t7 (
       a BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
       c varchar(32)
   ) DBPARTITION BY HASH(a);

2. Create an expression index on substr(c, 2):

   CREATE INDEX `i` ON `t7`(substr(`c`, 2));

3. After creation, PolarDB-X rewrites the table structure to add a VIRTUAL generated column for each expression entry:

   CREATE TABLE `t7` (
     `a` bigint(20) NOT NULL AUTO_INCREMENT BY GROUP,
     `c` varchar(32) DEFAULT NULL,
     `i$0` varchar(32) GENERATED ALWAYS AS (substr(`c`, 2)) VIRTUAL,
     PRIMARY KEY (`a`),
     KEY `i` (`i$0`)
   ) ENGINE = InnoDB dbpartition by hash(`a`);

   Column i$0 holds the expression result, and index i is built on i$0.

4. Verify that the index is selected for queries on the expression:

   EXPLAIN EXECUTE SELECT * FROM t7 WHERE substr(`c`, 2) = '11';

   +------+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
   | id   | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
   +------+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
   | 1    | SIMPLE      | t7    | NULL       | ref  | i             | i    | 131     | const | 1    | 100      | NULL  |
   +------+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+

Example 2: Expression index with multiple expressions

When an index includes multiple expressions, PolarDB-X creates a separate generated column for each expression entry (non-expression entries are left as-is).

Create the index:

CREATE INDEX idx ON t8(
  a + 1,
  b,
  SUBSTR(c, 2)
);

Resulting table structure:

CREATE TABLE `t8` (
  `a` int(11) NOT NULL,
  `b` int(11) DEFAULT NULL,
  `c` varchar(32) DEFAULT NULL,
  `idx$0` bigint(20) GENERATED ALWAYS AS (`a` + 1) VIRTUAL,
  `idx$2` varchar(32) GENERATED ALWAYS AS (substr(`c`, 2)) VIRTUAL,
  PRIMARY KEY (`a`),
  KEY `idx` (`idx$0`, `b`, `idx$2`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 dbpartition by hash(`a`);

Columns idx$0 and idx$2 correspond to the first and third index entries. Column b is a regular column and needs no generated column wrapper.

Queries that filter on all three conditions benefit from the index:

EXPLAIN EXECUTE SELECT * FROM t8 WHERE a+1=10 AND b=20 AND SUBSTR(c,2)='ab';

+------+-------------+-------+------------+------+---------------+------+---------+-------------------+------+----------+-------+
| id   | select_type | table | partitions | type | possible_keys | key  | key_len | ref               | rows | filtered | Extra |
+------+-------------+-------+------------+------+---------------+------+---------+-------------------+------+----------+-------+
| 1    | SIMPLE      | t8    | NULL       | ref  | idx           | idx  | 145     | const,const,const | 1    | 100      | NULL  |
+------+-------------+-------+------------+------+---------------+------+---------+-------------------+------+----------+-------+