Bug #100196 The results of REGEXP_SUBSTR() are different
Submitted: 13 Jul 2020 2:19 Modified: 14 Jul 2020 8:35
Reporter: Cheng Zhou Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0.18 OS:Any
Assigned to: CPU Architecture:Any

[13 Jul 2020 2:19] Cheng Zhou
Description:
Although they are the same string, the results of REGEXP_SUBSTR() are different when the first parameter is a string constant or a column of a table.

How to repeat:
1、create table if not exists tdup(a int not null primary key,b varchar(10));
2、insert into tdup(a,b) values(5 ,'11a22A33a');
3、
mysql> SELECT REGEXP_SUBSTR(b,'[^A]+',1,1) AS STR FROM tdup where a = 5;
+-------+
| STR   |
+-------+
| 11a22 |
+-------+

4、
mysql> SELECT REGEXP_SUBSTR('11a22A33a','[^A]+',1,1) AS STR;
+-----+
| STR |
+-----+
| 11  |
+-----+
[13 Jul 2020 8:20] MySQL Verification Team
Hello Cheng Zhou,

Thank you for the report and test case.
I quickly tried with the provided test case but not seeing any issues(with 8.0.20). Could you please try with latest GA build and report us back with configuration file used or if you are trying with source build then exact options used with cmake for the source build so that we can try at our end. Thank you.

-
 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.20 MySQL Community Server - GPL

Copyright (c) 2000, 2020, 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 table if not exists tdup(a int not null primary key,b varchar(10));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into tdup(a,b) values(5 ,'11a22A33a');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT REGEXP_SUBSTR(b,'[^A]+',1,1) AS STR FROM tdup where a = 5;
+------+
| STR  |
+------+
| 11   |
+------+
1 row in set (0.01 sec)

mysql> SELECT REGEXP_SUBSTR('11a22A33a','[^A]+',1,1) AS STR;
+-----+
| STR |
+-----+
| 11  |
+-----+
1 row in set (0.00 sec)

mysql> \s
--------------
bin/mysql  Ver 8.0.20 for Linux on x86_64 (MySQL Community Server - GPL)

Connection id:          7
Current database:       test
Current user:           root@localhost
SSL:                    Not in use
Current pager:          more
Using outfile:          ''
Using delimiter:        ;
Server version:         8.0.20 MySQL Community Server - GPL
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
UNIX socket:            /tmp/mysql_ushastry.sock
Binary data as:         Hexadecimal
Uptime:                 41 sec

Threads: 2  Questions: 14  Slow queries: 0  Opens: 143  Flush tables: 3  Open tables: 61  Queries per second avg: 0.341
--------------

regards,
Umesh
[14 Jul 2020 8:21] Cheng Zhou
I tried with 8.0.20, and there is no such issue.
[14 Jul 2020 8:35] MySQL Verification Team
Thank you for confirming, closing the bug for now.

regards,
Umesh