MySQL Bugs: #93588: regexp_replace() doesn't work when using distinct

Bug #93588	regexp_replace() doesn't work when using distinct
Submitted:	13 Dec 2018 10:00	Modified:	13 Dec 2018 10:55
Reporter:	Suhail Alkowaileet	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	8.0.12	OS:	Microsoft Windows
Assigned to:		CPU Architecture:	x86
Tags:	distinct, FUNCTION, REGEXP_REPLACE

Description:
# Test script:
#-------------

mysql> CREATE TABLE `myproj`.`new_table` (  `id` INT NOT NULL,  `name` VARCHAR(45) NULL,  PRIMARY KEY (`id`))ENGINE = MEMORY;

Query OK, 0 rows affected (0.11 sec)

mysql> INSERT INTO `myproj`.`new_table` (`id`, `name`) VALUES (1, 'aaaa'); \
INSERT INTO `myproj`.`new_table` (`id`, `name`) VALUES (2, 'aabbb'); \
INSERT INTO `myproj`.`new_table` (`id`, `name`) VALUES (3, 'aaxccc');

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

# regexp_replace() works perfectly when using it alone.
#------------------------------------------------------
mysql> SELECT regexp_replace(name, '[bB]', 'a') FROM myproj.new_table;
+-----------------------------------+
| regexp_replace(name, '[bB]', 'a') |
+-----------------------------------+
| aaaa                              |
| aaaaa                             |
| aaxccc                            |
+-----------------------------------+
3 rows in set (0.00 sec)

# And here when using it with distinct.
#------------------------------------------------------
mysql> SELECT distinct regexp_replace(name, '[bB]', 'a') FROM myproj.new_table;
+-----------------------------------+
| regexp_replace(name, '[bB]', 'a') |
+-----------------------------------+
|                                   |
+-----------------------------------+
1 row in set (0.00 sec)

How to repeat:
Please see the example above.

Thank you for the bug report. I couldn't repeat with recent source server:

C:\DBS>c:\dbs\8.0\bin\mysql -uroot --port=3580 -p --prompt="mysql 8.0 > " --default-character-set=utf8mb4
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.15 Source distribution BUILD: 2018-DEC-11

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 8.0 > use myproj;
Database changed
mysql 8.0 > CREATE TABLE `myproj`.`new_table` (  `id` INT NOT NULL,  `name` VARCHAR(45) NULL,  PRIMARY KEY (`id`))ENGINE = MEMORY;
Query OK, 0 rows affected (0.01 sec)

mysql 8.0 > INSERT INTO `myproj`.`new_table` (`id`, `name`) VALUES (1, 'aaaa'); \
Query OK, 1 row affected (0.02 sec)

mysql 8.0 > INSERT INTO `myproj`.`new_table` (`id`, `name`) VALUES (2, 'aabbb'); \
Query OK, 1 row affected (0.00 sec)

mysql 8.0 > INSERT INTO `myproj`.`new_table` (`id`, `name`) VALUES (3, 'aaxccc');
Query OK, 1 row affected (0.00 sec)

mysql 8.0 > SELECT regexp_replace(name, '[bB]', 'a') FROM myproj.new_table;
+-----------------------------------+
| regexp_replace(name, '[bB]', 'a') |
+-----------------------------------+
| aaaa                              |
| aaaaa                             |
| aaxccc                            |
+-----------------------------------+
3 rows in set (0.02 sec)

mysql 8.0 > SELECT distinct regexp_replace(name, '[bB]', 'a') FROM myproj.new_table;
+-----------------------------------+
| regexp_replace(name, '[bB]', 'a') |
+-----------------------------------+
| aaaa                              |
| aaaaa                             |
| aaxccc                            |
+-----------------------------------+
3 rows in set (0.00 sec)

Repeatable with current released version:
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 8
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> create database myproj;
Query OK, 1 row affected (0.10 sec)

mysql> use myproj;
Database changed
mysql> CREATE TABLE `myproj`.`new_table` (  `id` INT NOT NULL,  `name` VARCHAR(45) NULL,  PRIMARY KEY (`id`))ENGINE = MEMORY;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO `myproj`.`new_table` (`id`, `name`) VALUES (1, 'aaaa'); \
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO `myproj`.`new_table` (`id`, `name`) VALUES (2, 'aabbb'); \
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `myproj`.`new_table` (`id`, `name`) VALUES (3, 'aaxccc');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT regexp_replace(name, '[bB]', 'a') FROM myproj.new_table;
+-----------------------------------+
| regexp_replace(name, '[bB]', 'a') |
+-----------------------------------+
| aaaa                              |
| aaaaa                             |
| aaxccc                            |
+-----------------------------------+
3 rows in set (0.03 sec)

mysql> SELECT distinct regexp_replace(name, '[bB]', 'a') FROM myproj.new_table;
+-----------------------------------+
| regexp_replace(name, '[bB]', 'a') |
+-----------------------------------+
|                                   |
+-----------------------------------+
1 row in set (0.01 sec)

mysql>