Bug #95218 Virtual generated column altered unexpectedly when table definition changed
Submitted: 2 May 3:59 Modified: 2 May 12:43
Reporter: Joseph Choi Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DDL Severity:S2 (Serious)
Version:5.7.25, 5.7.26 OS:Microsoft Windows (7)
Assigned to: CPU Architecture:x86 (32-bit)
Tags: ALTER TABLE, ucs2, virtual generated column

[2 May 3:59] Joseph Choi
Description:
The virtual generated column of ucs2 charset changed whenever the table definition is changed, either when another unrelated column is modified, or a new index is added to the table.

The NULL character '\0' is prepend to the string literal within the virtual generated column defintion every time when the table definition is changed.

This seems to affect ucs2 columns only, utf8 columns are not affected.  It seems that when the engine rewrites the table definition, it parses the ucs2-encoded column definition with a wrong charset, and the re-encodes it back into ucs2.

How to repeat:
mysql> CREATE TABLE `t1` (
         `path` varchar(255) CHARACTER SET ucs2 COLLATE ucs2_bin NOT NULL,
         `file_name` varchar(255) CHARACTER SET ucs2 COLLATE ucs2_bin
           GENERATED ALWAYS AS (substring_index(`path`, '\\', -1)) VIRTUAL NOT NULL,
         `file_size` bigint,
         PRIMARY KEY (`path`)
       );

mysql> INSERT INTO `t1` (`path`) VALUES ('\\path\\to\\file');

mysql> SELECT `path`, `file_name` FROM `t1`;
+-------------+---------+
|path         |file_name|
+-------------+---------+
|\path\to\file|file     |
+-------------+---------+

mysql> ALTER TABLE `t1` MODIFY COLUMN `file_size` bigint DEFAULT 0;

mysql> SELECT `path`, `file_name` FROM `t1`;
+-------------+-------------+
|path         |file_name    |
+-------------+-------------+
|\path\to\file|\path\to\file|
+-------------+-------------+

mysql> SHOW CREATE TABLE `t1`\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `path` varchar(255) CHARACTER SET ucs2 COLLATE ucs2_bin NOT NULL,
  `file_name` varchar(255) CHARACTER SET ucs2 COLLATE ucs2_bin GENERATED ALWAYS AS (substring_index(`path`,'\0\\',-(1))) VIRTUAL NOT NULL,
  `file_size` bigint(20) DEFAULT '0',
  PRIMARY KEY (`path`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

mysql> CREATE INDEX `i1` ON `t1` (`file_name`);

mysql> SHOW CREATE TABLE `t1`\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `path` varchar(255) CHARACTER SET ucs2 COLLATE ucs2_bin NOT NULL,
  `file_name` varchar(255) CHARACTER SET ucs2 COLLATE ucs2_bin GENERATED ALWAYS AS (substring_index(`path`,'\0\0\0\\',-(1))) VIRTUAL NOT NULL,
  `file_size` bigint(20) DEFAULT '0',
  PRIMARY KEY (`path`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci
[2 May 12:43] Umesh Shastry
Hello Joseph,

Thank you for the report and test case.
Verified as described with 5.7.26 build.

regards,
Umesh
[2 May 12:44] Umesh Shastry
-
- 5.7.26 (lowest version checked 5.7.11 - same behavior too)

bin/mysql -uroot -S /tmp/mysql_ushastry.sock                          
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.26 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database if not exists test;
use test;
CREATE TABLE `t1` (
Query OK, 1 row affected (0.00 sec)

mysql> use test;
         `path` varchar(255) CHARACTER SET ucs2 COLLATE ucs2_bin NOT NULL,
         `file_name` varchar(255) CHARACTER SET ucs2 COLLATE ucs2_bin
           GENERATED ALWAYS AS (substring_index(`path`, '\\', -1)) VIRTUAL NOT NULL,
         `file_size` bigint,
         PRIMARY KEY (`path`)
       );Database changed
mysql> CREATE TABLE `t1` (
    ->          `path` varchar(255) CHARACTER SET ucs2 COLLATE ucs2_bin NOT NULL,
    ->          `file_name` varchar(255) CHARACTER SET ucs2 COLLATE ucs2_bin
    ->            GENERATED ALWAYS AS (substring_index(`path`, '\\', -1)) VIRTUAL NOT NULL,
    ->          `file_size` bigint,
    ->          PRIMARY KEY (`path`)
    ->        );
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW CREATE TABLE `t1`\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `path` varchar(255) CHARACTER SET ucs2 COLLATE ucs2_bin NOT NULL,
  `file_name` varchar(255) CHARACTER SET ucs2 COLLATE ucs2_bin GENERATED ALWAYS AS (substring_index(`path`,'\\',-(1))) VIRTUAL NOT NULL,
  `file_size` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`path`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.02 sec)

mysql> INSERT INTO `t1` (`path`) VALUES ('\\path\\to\\file');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT `path`, `file_name` FROM `t1`;
+---------------+-----------+
| path          | file_name |
+---------------+-----------+
| \path\to\file | file      |
+---------------+-----------+
1 row in set (0.00 sec)

mysql> ALTER TABLE `t1` MODIFY COLUMN `file_size` bigint DEFAULT 0;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE `t1`\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `path` varchar(255) CHARACTER SET ucs2 COLLATE ucs2_bin NOT NULL,
  `file_name` varchar(255) CHARACTER SET ucs2 COLLATE ucs2_bin GENERATED ALWAYS AS (substring_index(`path`,'\0\\',-(1))) VIRTUAL NOT NULL,
  `file_size` bigint(20) DEFAULT '0',
  PRIMARY KEY (`path`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> SELECT `path`, `file_name` FROM `t1`;
+---------------+---------------+
| path          | file_name     |
+---------------+---------------+
| \path\to\file | \path\to\file |
+---------------+---------------+
1 row in set (0.00 sec)

mysql> CREATE INDEX `i1` ON `t1` (`file_name`);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE `t1`\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `path` varchar(255) CHARACTER SET ucs2 COLLATE ucs2_bin NOT NULL,
  `file_name` varchar(255) CHARACTER SET ucs2 COLLATE ucs2_bin GENERATED ALWAYS AS (substring_index(`path`,'\0\0\0\\',-(1))) VIRTUAL NOT NULL,
  `file_size` bigint(20) DEFAULT '0',
  PRIMARY KEY (`path`),
  KEY `i1` (`file_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> SELECT `path`, `file_name` FROM `t1`;
+---------------+---------------+
| path          | file_name     |
+---------------+---------------+
| \path\to\file | \path\to\file |
+---------------+---------------+
1 row in set (0.00 sec)
[2 May 12:45] Umesh Shastry
- ALTER is DDL, changing category to DDL. 
Please don't change category!!
[2 May 12:46] Umesh Shastry
- 8.0.16 - looks okay to me(at least gen col value is not messed)

bin/mysql -uroot -S /tmp/mysql_ushastry.sock     Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.16 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test;
Query OK, 1 row affected (0.01 sec)

mysql> use test
Database changed
mysql> create database if not exists test;
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> use test;
Database changed
mysql> CREATE TABLE `t1` (
    ->          `path` varchar(255) CHARACTER SET ucs2 COLLATE ucs2_bin NOT NULL,
    ->          `file_name` varchar(255) CHARACTER SET ucs2 COLLATE ucs2_bin
    ->            GENERATED ALWAYS AS (substring_index(`path`, '\\', -1)) VIRTUAL NOT NULL,
    ->          `file_size` bigint,
    ->          PRIMARY KEY (`path`)
    ->        );
Query OK, 0 rows affected (0.03 sec)

mysql> SHOW CREATE TABLE `t1`\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `path` varchar(255) CHARACTER SET ucs2 COLLATE ucs2_bin NOT NULL,
  `file_name` varchar(255) CHARACTER SET ucs2 COLLATE ucs2_bin GENERATED ALWAYS AS (substring_index(`path`,_ucs2'\0\\',-(1))) VIRTUAL NOT NULL,
  `file_size` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`path`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)

mysql> INSERT INTO `t1` (`path`) VALUES ('\\path\\to\\file');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT `path`, `file_name` FROM `t1`;
+---------------+-----------+
| path          | file_name |
+---------------+-----------+
| \path\to\file | file      |
+---------------+-----------+
1 row in set (0.00 sec)

mysql> ALTER TABLE `t1` MODIFY COLUMN `file_size` bigint DEFAULT 0;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE `t1`\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `path` varchar(255) CHARACTER SET ucs2 COLLATE ucs2_bin NOT NULL,
  `file_name` varchar(255) CHARACTER SET ucs2 COLLATE ucs2_bin GENERATED ALWAYS AS (substring_index(`path`,_ucs2'\0\\',-(1))) VIRTUAL NOT NULL,
  `file_size` bigint(20) DEFAULT '0',
  PRIMARY KEY (`path`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> SELECT `path`, `file_name` FROM `t1`;
+---------------+-----------+
| path          | file_name |
+---------------+-----------+
| \path\to\file | file      |
+---------------+-----------+
1 row in set (0.00 sec)

mysql> CREATE INDEX `i1` ON `t1` (`file_name`);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE `t1`\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `path` varchar(255) CHARACTER SET ucs2 COLLATE ucs2_bin NOT NULL,
  `file_name` varchar(255) CHARACTER SET ucs2 COLLATE ucs2_bin GENERATED ALWAYS AS (substring_index(`path`,_ucs2'\0\\',-(1))) VIRTUAL NOT NULL,
  `file_size` bigint(20) DEFAULT '0',
  PRIMARY KEY (`path`),
  KEY `i1` (`file_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> SELECT `path`, `file_name` FROM `t1`;
+---------------+-----------+
| path          | file_name |
+---------------+-----------+
| \path\to\file | file      |
+---------------+-----------+
1 row in set (0.00 sec)