Description:
Hi dear all,
Sample table is:
CREATE TABLE `sbtest4` (
`id` int(10) unsigned NOT NULL,
`k` int(10) unsigned NOT NULL DEFAULT '0',
`c` varchar(250) DEFAULT NULL,
`pad` char(60) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (k)
(PARTITION p1 VALUES LESS THAN (100) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (1000) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (10000) ENGINE = InnoDB,
PARTITION p4 VALUES LESS THAN (10001) ENGINE = InnoDB) */
select count(*) from sbtest4;
+----------+
| count(*) |
+----------+
| 40000 |
+----------+
1 row in set (0.34 sec)
Trying to add generated column:
With STORED:
alter table sbtest4 add column json_test_s json generated always as (json_array(k,c,pad)) stored;
Query OK, 40000 rows affected (4.89 sec)
Records: 40000 Duplicates: 0 Warnings: 0
Wit VIRTUAL:
alter table sbtest4 add column json_test_v json generated always as (json_array(k,c,pad)) virtual;
Query OK, 40000 rows affected (5.32 sec)
Records: 40000 Duplicates: 0 Warnings: 0
With ordinary table:
alter table sbtest1 add column json_test_v json generated always as (json_array(k,c,pad)) virtual;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
The situation is getting serious when I tried to drop virtual column for partitioned table:
alter table sbtest4 drop column json_test_v;
Query OK, 40000 rows affected (5.23 sec)
Records: 40000 Duplicates: 0 Warnings: 0
So it takes same amount of time to drop virtual column as for "usual" column with partitioned table.
Again same thing will not affected by ordinary table:
alter table sbtest1 drop column `json_test_v`;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
How to repeat:
See description