MySQL Bugs: #100196: The results of REGEXP

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

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  |
+-----+

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

I tried with 8.0.20, and there is no such issue.

Thank you for confirming, closing the bug for now.

regards,
Umesh