Bug #56225 Implement MERGE algorithm for views which use UNION ALL

• Submitted: 24 Aug 2010 16:59
• Reporter: Sveta Smirnova
• Status: Verified
• Category: MySQL Server: Views
• Severity: S4 (Feature request)
• Version: 5.0, 5.1, 5.6.99, 6.0
• OS: Any
• CPU Architecture: Any

[24 Aug 2010 16:59] Sveta Smirnova

Description:
Currently merge algorithm can not be used for views which contain UNION ALL, although creating temporary table not always needed when using such views.

See http://forums.mysql.com/read.php?100,56681,56681 for detailed discussion about the problem.

How to repeat:
create table one (
id int(11) default null,
descr varchar(255)
) Engine = MyISAM;

create table two (
id int(11) default null,
descr varchar(255)
) Engine = MyISAM;

create algorithm=merge view v as
select * from one
union all
select * from two ;

insert into one values(1, 'fdsd');
insert into two values(1, 'fdsd');

flush status;
show status like '%tmp%';
select descr from v where id > 1000 ;
show status like '%tmp%';

Result:

create table one (
id int(11) default null,
descr varchar(255)
) Engine = MyISAM;
create table two (
id int(11) default null,
descr varchar(255)
) Engine = MyISAM;
create algorithm=merge view v as
select * from one
union all
select * from two ;
Warnings:
Warning 1354    View merge algorithm can't be used here for now (assumed undefined algorithm)
insert into one values(1, 'fdsd');
insert into two values(1, 'fdsd');
flush status;
show status like '%tmp%';
Variable_name   Value
Created_tmp_disk_tables 0
Created_tmp_files       0
Created_tmp_tables      0
select descr from v where id > 1000 ;
descr
show status like '%tmp%';
Variable_name   Value
Created_tmp_disk_tables 0
Created_tmp_files       0
Created_tmp_tables      2

Suggested fix:
Implement MERGE algorithm for such views.