MySQL Bugs: #95041: select with union cause ERROR1169(23000)Can't write,because of unique constraint

Bug #95041	select with union cause ERROR1169(23000)Can't write,because of unique constraint
Submitted:	17 Apr 2019 3:12	Modified:	1 Jun 2019 2:31
Reporter:	peng wang	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.6.41	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
1. select from union table count greater than 3 cause ERROR 1169 (23000): Can't write, because of unique constraint, to table '/rdsdbdata/tmp/#sql_17a1_1' exception under mysql 5.6.41

2. we test the same case with mysql5.5 and mysql5.7, the result are all ok.

3. those selected union tables has no unique constraint.

How to repeat:
CREATE TABLE `tars_property_2019040800` (
  `stattime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `f_date` date NOT NULL DEFAULT '1970-01-01',
  `f_tflag` varchar(8) NOT NULL DEFAULT '',
  `master_name` varchar(128) NOT NULL DEFAULT '',
  `master_ip` varchar(16) DEFAULT NULL,
  `property_name` varchar(100) DEFAULT NULL,
  `set_name` varchar(15) NOT NULL DEFAULT '',
  `set_area` varchar(15) NOT NULL DEFAULT '',
  `set_id` varchar(15) NOT NULL DEFAULT '',
  `policy` varchar(20) DEFAULT NULL,
  `value` varchar(255) DEFAULT NULL,
  KEY `f_date` (`f_date`,`f_tflag`,`master_name`,`master_ip`,`property_name`,`policy`),
  KEY `IDX_MASTER_NAME` (`master_name`),
  KEY `IDX_MASTER_IP` (`master_ip`),
  KEY `IDX_TIME` (`stattime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ;

create table tars_property_2019040801 like tars_property_2019040800;
create table tars_property_2019040802 like tars_property_2019040800;
create table tars_property_2019040803 like tars_property_2019040800;

# import some test data.

test result under mysql 5.6.41:

# select with union 4 tables throws exception.
mysql> select UNIX_TIMESTAMP(stattime) as time_sec,value from (select * from tars_property_2019040800 where master_name like 'hcg.%' and property_name like 'hcg.%' union select * from tars_property_2019040801 where master_name like 'hcg.%' and property_name like 'hcg.%' union select * from tars_property_2019040802 where master_name like 'hcg.%' and property_name like 'hcg.%' union select * from tars_property_2019040803 where master_name like 'hcg.%' and property_name like 'hcg.%') as union_4tables where master_name="hcg.WSTubeRouteServer" and property_name="hcg.tube.conn.cnt" and policy="Max" order by time_sec asc;
ERROR 1169 (23000): Can't write, because of unique constraint, to table '/rdsdbdata/tmp/#sql_18f5_1'

# select with union 3 tables is ok.

Please, add some data to insert into table(s) to demonstrate the problem.

thanks for your reply.
you can download the test data from the link below.

https://www.dropbox.com/s/throtdfyxnsnl5f/tars_property.sql?dl=0

Hi,

Thank you for your bug report.

We can not work on it, since we do not have access to DropBox.  Hence, please use the above "Files" tab and upload your data to our SFTP server. Data will be visible only to Oracle employees ...

Also, do confirm that this problem did not occur on either 5.5 or 5.7.

the test data to reproduce this bug

Attachment: mysql-bug-data-95041.zip (application/zip, text), 536.77 KiB.

Hi,

Thank you for your data.

I have got the same outcome as you, so I am verifying this bug.

However, I must point out that if it is fixed in 5.7, there could be chance that this bug might not be fixed. This is going to be decided internally and when decision is made, it will be made public on this page.

ok, thanks for your working about this bug.