Re: Mysql 8.0.22 - Indexes on virtual columns do not update

[Vasilis Kioustelidis]

I've managed to replicate the behaviour by creating a small lab using docker. I've created two docker instances named olap_replica_v8.0.22_a and olap_replica_v8.0.22_b. The instance olap_replica_v8.0.22_a holds the 'original' data and olap_replica_v8.0.22_b is the replica that contains the same table+generated columns+indexes.

The original data table on olap_replica_v8.0.22_a is as follows:
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| ID | int unsigned | NO | PRI | NULL | auto_increment |
| TM_TS | varchar(14) | YES | | NULL | |
| DATA | blob | YES | | NULL | |
+-------+--------------+------+-----+---------+----------------+


Next I created the replica table on olap_replica_v8.0.22_b as follows:
+--------+--------------+------+-----+---------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------------------+
| ID | int unsigned | NO | PRI | NULL | auto_increment |
| TM_TS | varchar(14) | YES | | NULL | |
| DATA | blob | YES | | NULL | |
| s_date | date | YES | MUL | NULL | STORED GENERATED |
| v_date | date | YES | MUL | NULL | VIRTUAL GENERATED |
| s_hash | char(40) | YES | MUL | NULL | STORED GENERATED |
| v_hash | char(40) | YES | MUL | NULL | VIRTUAL GENERATED |
+--------+--------------+------+-----+---------+-------------------+

And the indexes on the computed columns:
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| log_data | 0 | PRIMARY | 1 | ID | A | 10 | NULL | NULL | | BTREE | | | YES | NULL |
| log_data | 1 | k_s_date | 1 | s_date | A | 1 | NULL | NULL | YES | BTREE | | | YES | NULL |
| log_data | 1 | k_s_hash | 1 | s_hash | A | 10 | NULL | NULL | YES | BTREE | | | YES | NULL |
| log_data | 1 | k_v_date | 1 | v_date | A | 1 | NULL | NULL | YES | BTREE | | | YES | NULL |
| log_data | 1 | k_v_hash | 1 | v_hash | A | 10 | NULL | NULL | YES | BTREE | | | YES | NULL |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

Finally I populated the original data using random values:
+----+----------+----------------------------------------------------------------------------------------------------------------------------------------+
| ID | TM_TS | DATA |
+----+----------+----------------------------------------------------------------------------------------------------------------------------------------+
| 1 | 20201211 | 0x302E36313537343138363136323632363534302E36313537343138363136323632363534302E36313537343138363136323632363534 |
| 2 | 20201211 | 0x302E3331313939303536343937393630343033302E3331313939303536343937393630343033302E3331313939303536343937393630343033 |
| 3 | 20201211 | 0x302E37313237323732373037353238363839302E37313237323732373037353238363839302E37313237323732373037353238363839 |
| 4 | 20201211 | 0x302E36323736363436383935353931373732302E36323736363436383935353931373732302E36323736363436383935353931373732 |
| 5 | 20201211 | 0x302E3030303134313636363237303932343432313238302E3030303134313636363237303932343432313238302E3030303134313636363237303932343432313238 |
| 6 | 20201211 | 0x302E3131373731343239333431303733353438302E3131373731343239333431303733353438302E3131373731343239333431303733353438 |
| 7 | 20201211 | 0x302E35383831343634393839373435343931302E35383831343634393839373435343931302E35383831343634393839373435343931 |
| 8 | 20201211 | 0x302E35383735383936343533373431383431302E35383735383936343533373431383431302E35383735383936343533373431383431 |
| 9 | 20201211 | 0x302E3137333530383736303638303931383336302E3137333530383736303638303931383336302E3137333530383736303638303931383336 |
| 10 | 20201211 | 0x302E3130343737343839383031353638343335302E3130343737343839383031353638343335302E3130343737343839383031353638343335 |
+----+----------+----------------------------------------------------------------------------------------------------------------------------------------+


The same rows on the replica however are different (stored columns come up as NULL) and the indexes do not work:
+----+----------+----------------------------------------------------------------------------------------------------------------------------------------+--------+------------+--------+------------------------------------------+
| ID | TM_TS | DATA | s_date | v_date | s_hash | v_hash |
+----+----------+----------------------------------------------------------------------------------------------------------------------------------------+--------+------------+--------+------------------------------------------+
| 1 | 20201211 | 0x302E36313537343138363136323632363534302E36313537343138363136323632363534302E36313537343138363136323632363534 | NULL | 2020-12-11 | NULL | 768b42b27c912d774b4112c94c000235384a86da |
| 2 | 20201211 | 0x302E3331313939303536343937393630343033302E3331313939303536343937393630343033302E3331313939303536343937393630343033 | NULL | 2020-12-11 | NULL | 89fb345354fa330cfa83ceee888f6da10f0aaf3e |
| 3 | 20201211 | 0x302E37313237323732373037353238363839302E37313237323732373037353238363839302E37313237323732373037353238363839 | NULL | 2020-12-11 | NULL | 50656f30f4265bf1e330b8e54eff3c7e7e941cb7 |
| 4 | 20201211 | 0x302E36323736363436383935353931373732302E36323736363436383935353931373732302E36323736363436383935353931373732 | NULL | 2020-12-11 | NULL | 3db8647974731b9185310c779517eed590a2643e |
| 5 | 20201211 | 0x302E3030303134313636363237303932343432313238302E3030303134313636363237303932343432313238302E3030303134313636363237303932343432313238 | NULL | 2020-12-11 | NULL | bfbc30a6d13f8e21ad9263f8f6c70c2de5f26e8d |
| 6 | 20201211 | 0x302E3131373731343239333431303733353438302E3131373731343239333431303733353438302E3131373731343239333431303733353438 | NULL | 2020-12-11 | NULL | aa1194aaa2f95c6bf1f050c5bcac8899249b8d9e |
| 7 | 20201211 | 0x302E35383831343634393839373435343931302E35383831343634393839373435343931302E35383831343634393839373435343931 | NULL | 2020-12-11 | NULL | 9a2d77ed383dc5731cd740ca585b67b77e760306 |
| 8 | 20201211 | 0x302E35383735383936343533373431383431302E35383735383936343533373431383431302E35383735383936343533373431383431 | NULL | 2020-12-11 | NULL | caed9858d0278b5a18c6a580bb6cca7716da6acb |
| 9 | 20201211 | 0x302E3137333530383736303638303931383336302E3137333530383736303638303931383336302E3137333530383736303638303931383336 | NULL | 2020-12-11 | NULL | a757ea18725fa483f7d5ffd257db8eff6583b685 |
| 10 | 20201211 | 0x302E3130343737343839383031353638343335302E3130343737343839383031353638343335302E3130343737343839383031353638343335 | NULL | 2020-12-11 | NULL | f8e3f8936f3918f3409375df01921f567ae2a875 |
+----+----------+----------------------------------------------------------------------------------------------------------------------------------------+--------+------------+--------+------------------------------------------+

mysql> select * from virtual_test.log_data where v_date=CURDATE();
Empty set (0.00 sec)

mysql> select * from virtual_test.log_data where s_date=CURDATE();
Empty set (0.01 sec)

mysql> select * from virtual_test.log_data where v_hash='f8e3f8936f3918f3409375df01921f567ae2a875';
Empty set (0.01 sec)

mysql> select * from virtual_test.log_data where v_hash=s_hash;
Empty set (0.00 sec)

mysql> select v_hash,s_hash from virtual_test.log_data where v_hash=s_hash;
Empty set (0.00 sec)

Finally, I recreated all generated columns and indexes and the values were computed correctly:

ALTER TABLE virtual_test.log_data DROP COLUMN `s_date`,DROP COLUMN `v_date`,DROP COLUMN `s_hash`,DROP COLUMN `v_hash`, DROP KEY `k_s_date`,DROP KEY `k_v_date`,DROP KEY `k_s_hash`,DROP KEY `k_v_hash`, ADD COLUMN `s_date` date GENERATED ALWAYS AS (cast(`tm_ts` as date)) STORED, ADD COLUMN `v_date` date GENERATED ALWAYS AS (cast(`tm_ts` as date)) VIRTUAL, ADD COLUMN `s_hash` char(40) GENERATED ALWAYS AS (sha(hex(`DATA`))) STORED, ADD COLUMN `v_hash` char(40) GENERATED ALWAYS AS (sha(hex(`DATA`))) VIRTUAL, ADD KEY `k_s_date`(s_date), ADD KEY `k_v_date`(v_date), ADD KEY `k_s_hash`(s_hash), ADD KEY `k_v_hash`(v_hash) ;

mysql> select * from virtual_test.log_data where v_date=CURDATE();
+----+----------+----------------------------------------------------------------------------------------------------------------------------------------+------------+------------+------------------------------------------+------------------------------------------+
| ID | TM_TS | DATA | s_date | v_date | s_hash | v_hash |
+----+----------+----------------------------------------------------------------------------------------------------------------------------------------+------------+------------+------------------------------------------+------------------------------------------+
| 1 | 20201211 | 0x302E36313537343138363136323632363534302E36313537343138363136323632363534302E36313537343138363136323632363534 | 2020-12-11 | 2020-12-11 | 768b42b27c912d774b4112c94c000235384a86da | 768b42b27c912d774b4112c94c000235384a86da |
| 2 | 20201211 | 0x302E3331313939303536343937393630343033302E3331313939303536343937393630343033302E3331313939303536343937393630343033 | 2020-12-11 | 2020-12-11 | 89fb345354fa330cfa83ceee888f6da10f0aaf3e | 89fb345354fa330cfa83ceee888f6da10f0aaf3e |
| 3 | 20201211 | 0x302E37313237323732373037353238363839302E37313237323732373037353238363839302E37313237323732373037353238363839 | 2020-12-11 | 2020-12-11 | 50656f30f4265bf1e330b8e54eff3c7e7e941cb7 | 50656f30f4265bf1e330b8e54eff3c7e7e941cb7 |
| 4 | 20201211 | 0x302E36323736363436383935353931373732302E36323736363436383935353931373732302E36323736363436383935353931373732 | 2020-12-11 | 2020-12-11 | 3db8647974731b9185310c779517eed590a2643e | 3db8647974731b9185310c779517eed590a2643e |
| 5 | 20201211 | 0x302E3030303134313636363237303932343432313238302E3030303134313636363237303932343432313238302E3030303134313636363237303932343432313238 | 2020-12-11 | 2020-12-11 | bfbc30a6d13f8e21ad9263f8f6c70c2de5f26e8d | bfbc30a6d13f8e21ad9263f8f6c70c2de5f26e8d |
| 6 | 20201211 | 0x302E3131373731343239333431303733353438302E3131373731343239333431303733353438302E3131373731343239333431303733353438 | 2020-12-11 | 2020-12-11 | aa1194aaa2f95c6bf1f050c5bcac8899249b8d9e | aa1194aaa2f95c6bf1f050c5bcac8899249b8d9e |
| 7 | 20201211 | 0x302E35383831343634393839373435343931302E35383831343634393839373435343931302E35383831343634393839373435343931 | 2020-12-11 | 2020-12-11 | 9a2d77ed383dc5731cd740ca585b67b77e760306 | 9a2d77ed383dc5731cd740ca585b67b77e760306 |
| 8 | 20201211 | 0x302E35383735383936343533373431383431302E35383735383936343533373431383431302E35383735383936343533373431383431 | 2020-12-11 | 2020-12-11 | caed9858d0278b5a18c6a580bb6cca7716da6acb | caed9858d0278b5a18c6a580bb6cca7716da6acb |
| 9 | 20201211 | 0x302E3137333530383736303638303931383336302E3137333530383736303638303931383336302E3137333530383736303638303931383336 | 2020-12-11 | 2020-12-11 | a757ea18725fa483f7d5ffd257db8eff6583b685 | a757ea18725fa483f7d5ffd257db8eff6583b685 |
| 10 | 20201211 | 0x302E3130343737343839383031353638343335302E3130343737343839383031353638343335302E3130343737343839383031353638343335 | 2020-12-11 | 2020-12-11 | f8e3f8936f3918f3409375df01921f567ae2a875 | f8e3f8936f3918f3409375df01921f567ae2a875 |
+----+----------+----------------------------------------------------------------------------------------------------------------------------------------+------------+------------+------------------------------------------+------------------------------------------+

Vasilis Kioustelidis

Technical Director

EDPS S.A.
[phone], Mob: [phone],Fax:[phone]

Email:[e-mail] URL:http://www.edps.gr