| Bug #91131 | Select Distinct Combined with UUID() Doesn't Filter Duplicate Values | ||
|---|---|---|---|
| Submitted: | 4 Jun 2018 13:28 | Modified: | 6 Jun 2018 10:40 |
| Reporter: | Steve McKee | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 8.0.11 | OS: | Windows |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | distinct, UUID | ||
[5 Jun 2018 4:37]
MySQL Verification Team
Hello Steve McKee, Thank you for the report and test case. Thanks, Umesh
[5 Jun 2018 4:38]
MySQL Verification Team
-- [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22/mysql-test: ./mtr bug91131 Logging: ./mtr bug91131 MySQL Version 5.7.22 Checking supported features... - SSL connections supported Collecting tests... Checking leftover processes... Removing old var directory... Creating var directory '/export/umesh/server/binaries/GABuilds/mysql-5.7.22/mysql-test/var'... Installing system database... Using parallel: 1 ============================================================================== TEST RESULT TIME (ms) or COMMENT -------------------------------------------------------------------------- worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009 CREATE TABLE `employee` (`employee_id` int(11) NOT NULL AUTO_INCREMENT,`first_name` varchar(255) NOT NULL,`last_name` varchar(255) NOT NULL,`type` varchar(255) NOT NULL,PRIMARY KEY (`employee_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE `employee_type` (`employee_type_id` int(11) NOT NULL AUTO_INCREMENT,`type` varchar(255) NOT NULL,`uuid` char(38) NOT NULL,PRIMARY KEY (`employee_type_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; insert into employee (first_name, last_name, type) values ('Employee', 'One', 'customer service'); insert into employee (first_name, last_name, type) values ('Employee', 'Two', 'customer service'); insert into employee (first_name, last_name, type) values ('Employee', 'Three', 'manager'); select distinct type, UUID() from employee; type UUID() customer service ef6e062d-6879-11e8-a56e-0010e05f3e06 manager ef6e0645-6879-11e8-a56e-0010e05f3e06 insert into employee_type (type, uuid) select distinct type, UUID() from employee; select * from employee_type; employee_type_id type uuid 1 customer service ef6e210f-6879-11e8-a56e-0010e05f3e06 2 manager ef6e2283-6879-11e8-a56e-0010e05f3e06 drop tables employee; drop tables employee_type; main.bug91131 [ pass ] 13 -------------------------------------------------------------------------- The servers were restarted 0 times Spent 0.013 of 4 seconds executing testcases Completed: All 1 tests were successful. -- [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-8.0.11/mysql-test: ./mtr bug91131 --nowarnings Logging: ./mtr bug91131 --nowarnings 2018-06-05T04:35:13.532557Z 0 [System] [MY-010116] [Server] /export/umesh/server/binaries/GABuilds/mysql-8.0.11/bin/mysqld (mysqld 8.0.11) starting as process 21825 MySQL Version 8.0.11 Checking supported features... - SSL connections supported Collecting tests... Checking leftover processes... Removing old var directory... Creating var directory '/export/umesh/server/binaries/GABuilds/mysql-8.0.11/mysql-test/var'... Installing system database... Using parallel: 1 ============================================================================== TEST RESULT TIME (ms) or COMMENT -------------------------------------------------------------------------- worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009 main.bug91131 [ fail ] Test ended at 2018-06-05 06:35:18 Result file '/export/umesh/server/binaries/GABuilds/mysql-8.0.11/mysql-test/r/bug91131.result' doesn't exist. Either create a result file or disable check-testcases and run the test case. Use --nocheck-testcases option to disable check-testcases. Mysqltest client output from logfile ----------- MYSQLTEST OUTPUT START ----------- CREATE TABLE `employee` (`employee_id` int(11) NOT NULL AUTO_INCREMENT,`first_name` varchar(255) NOT NULL,`last_name` varchar(255) NOT NULL,`type` varchar(255) NOT NULL,PRIMARY KEY (`employee_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE `employee_type` (`employee_type_id` int(11) NOT NULL AUTO_INCREMENT,`type` varchar(255) NOT NULL,`uuid` char(38) NOT NULL,PRIMARY KEY (`employee_type_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; insert into employee (first_name, last_name, type) values ('Employee', 'One', 'customer service'); insert into employee (first_name, last_name, type) values ('Employee', 'Two', 'customer service'); insert into employee (first_name, last_name, type) values ('Employee', 'Three', 'manager'); select distinct type, UUID() from employee; type UUID() customer service d99ea3c9-6879-11e8-90e0-0010e05f3e06 customer service d99ea416-6879-11e8-90e0-0010e05f3e06 manager d99ea427-6879-11e8-90e0-0010e05f3e06 insert into employee_type (type, uuid) select distinct type, UUID() from employee; select * from employee_type; employee_type_id type uuid 1 customer service d99ec6f4-6879-11e8-90e0-0010e05f3e06 2 customer service d99ec738-6879-11e8-90e0-0010e05f3e06 3 manager d99ec75b-6879-11e8-90e0-0010e05f3e06 drop tables employee; drop tables employee_type;
[6 Jun 2018 10:40]
Roy Lyseng
This looks like a bug in 5.7 that was inadvertently fixed in 8.0 When we perform a SELECT without DISTINCT, we get three distinct rows (due to UUID): select type, UUID() from employee; +------------------+--------------------------------------+ | type | UUID() | +------------------+--------------------------------------+ | customer service | a3d3f6f8-6893-11e8-a412-0010e0bd3744 | | customer service | a3d3f817-6893-11e8-a412-0010e0bd3744 | | manager | a3d3f867-6893-11e8-a412-0010e0bd3744 | +------------------+--------------------------------------+ from this follows that select distinct type, UUID() from employee; should give the same result, as there are no duplicates. So this seems like a bug that was fixed in 8.0. You can use the following corrected INSERT statement to insert the distinct rows, extended with unique UUID values per row: INSERT INTO employee_type (type, uuid) SELECT *, UUID() FROM (SELECT DISTINCT type FROM employee) AS dt;
Description: We're in the process of upgrading from MySQL 5.7 to 8.0.11, and some queries in our application have broken. It seems populating a table with a UUID from another table without one does not work correctly when combined with a distinct select. How to repeat: CREATE TABLE `employee` ( `employee_id` int(11) NOT NULL AUTO_INCREMENT, `first_name` varchar(255) NOT NULL, `last_name` varchar(255) NOT NULL, `type` varchar(255) NOT NULL, PRIMARY KEY (`employee_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `employee_type` ( `employee_type_id` int(11) NOT NULL AUTO_INCREMENT, `type` varchar(255) NOT NULL, `uuid` char(38) NOT NULL, PRIMARY KEY (`employee_type_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into employee (first_name, last_name, type) values ('Employee', 'One', 'customer service'); insert into employee (first_name, last_name, type) values ('Employee', 'Two', 'customer service'); insert into employee (first_name, last_name, type) values ('Employee', 'Three', 'manager'); insert into employee_type (type, uuid) select distinct type, UUID() from employee; select * from employee_type; MySQL 5.7.22: +------------------+------------------+--------------------------------------+ | employee_type_id | type | uuid | +------------------+------------------+--------------------------------------+ | 1 | customer service | b6468021-67fa-11e8-8bd6-0050568b7d57 | | 2 | manager | b6469a2b-67fa-11e8-8bd6-0050568b7d57 | +------------------+------------------+--------------------------------------+ MySQL 8.0.11: +------------------+------------------+--------------------------------------+ | employee_type_id | type | uuid | +------------------+------------------+--------------------------------------+ | 1 | customer service | 271d2904-67f8-11e8-9148-847beb3f9f7d | | 2 | customer service | 271d294d-67f8-11e8-9148-847beb3f9f7d | | 3 | manager | 271d2963-67f8-11e8-9148-847beb3f9f7d | +------------------+------------------+--------------------------------------+