MySQL Bugs: #106814: The type varchar can't use <auto_key0> if optimizer don't do derived

Bug #106814	The type varchar can't use <auto_key0> if optimizer don't do derived_merge
Submitted:	24 Mar 2022 5:05	Modified:	24 Mar 2022 14:43
Reporter:	xincheng xie	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	8.0.18	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The type varchar  can't use <auto_key0>  if optimizer don't do derived_merge
As far as the article(https://dev.mysql.com/doc/refman/8.0/en/derived-table-optimization.html) is concerned,if i use " group by " in the  subquery ,
the optimizer chooses the materialization strategy rather than merging for a derived table.And I could repeat the optimization if the subquery's column is bigint
but i cant do that if  Type is varchar .

The statement is as follows：

select count(1) from  receipt_info a
 left join 
 (select depT_code from 
 db_mklij_xingcheng_stat.department_time 
 group by depT_code)
 b 
 on a.depT_code =b.depT_code ;
explain shows：

1	PRIMARY	a		index		idx_dept_code	518		18497	100	Using index
1	PRIMARY	<derived2>		ALL					9222	100	Using where; Using join buffer (Block Nested Loop)
2	DERIVED	department_time		index	IDX_DEPTCODE	IDX_DEPTCODE	1023		9222	100	Using index

select count(1) from  receipt_info a
 left join 
 (select depT_id from 
 db_mklij_xingcheng_stat.department_time 
 group by depT_id)
 b 
 on a.depT_code =b.depT_id 

1	PRIMARY	a		index		idx_dept_code	518		18497	100	Using index
1	PRIMARY	<derived2>		ref	<auto_key0>	<auto_key0>	5	db_mklij_xingcheng_stat.a.dept_code	10	100	Using where; Using index
2	DERIVED	department_time		index	idx_departtime_id	idx_departtime_id	10		9222	100	Using index
;

AND the difference between them only is that depT_id is bigint and depT_code  is varchar.
And no index on those columns.

 

How to repeat:
i dump the structure and some of the data for you.

the data file

Attachment: mysql-bug-data-106814.zip (application/x-zip-compressed, text), 171.85 KiB.

the data file

Attachment: mysql-bug-data-106814.zip (application/x-zip-compressed, text), 172.97 KiB.

Hi Mr. xie,

Thank you for your bug report.

However, this is not a bug.

We repeated your behaviour on the latest 8.0 release and plans are the same. 

This is expected behaviour, because you can not use proper join in the case where common column is badly defined. For example, like belonging to different domains, which is your case.

Not a bug.