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: | |
Category: | MySQL Server: DML | Severity: | S2 (Serious) |
Version: | 8.0.12 | OS: | Windows |
Assigned to: | CPU Architecture: | x86 | |
Tags: | distinct, FUNCTION, REGEXP_REPLACE |
[13 Dec 2018 10:00]
Suhail Alkowaileet
[13 Dec 2018 10:55]
MySQL Verification Team
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)
[13 Dec 2018 10:58]
MySQL Verification Team
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>