Bug #93630 regexp_substr does not work in virtual column

• Submitted: 16 Dec 2018 15:10
• Modified: 16 Dec 2018 21:35
• Reporter: sinai yoktan
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 8.0.11
• OS: Any
• CPU Architecture: Any
• Tags: regex virtual column

[16 Dec 2018 15:10] sinai yoktan

Description:
virtual column that is created using the regexp_substr function returns 0 instead of the expected value

How to repeat:

CREATE TABLE `test_table` (
  `id` int(11) NOT NULL,
  `test` varchar(45) GENERATED ALWAYS AS (regexp_substr('X.Y.Z','[^.]+',1,2)) VIRTUAL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO test_table(id) VALUES (1);

SELECT * FROM test_table;

[16 Dec 2018 15:29] MySQL Verification Team

Thank you for the bug report. Please paste here the result you get.

C:\DBS>8.0\bin\mysql -uroot -p
Enter password: **********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.13 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> USE test
Database changed
mysql> CREATE TABLE `test_table` (
    ->   `id` int(11) NOT NULL,
    ->   `test` varchar(45) GENERATED ALWAYS AS (regexp_substr('X.Y.Z','[^.]+',1,2)) VIRTUAL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Query OK, 0 rows affected (0.10 sec)

mysql> INSERT INTO test_table(id) VALUES (1);
Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM test_table;
+----+------+
| id | test |
+----+------+
|  1 | Y    |
+----+------+
1 row in set (0.00 sec)

mysql>

[16 Dec 2018 16:22] sinai yoktan

i was able to reproduce the bug on mysql version 8.0.11 via docker, but not on mysql 8.0.13 (via docker)

it appears that this issue was resolved,

thanks

[16 Dec 2018 21:35] MySQL Verification Team

Thank you for the feedback.