http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/materialized_view_rewrite_5.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/materialized_view_rewrite_5.q b/ql/src/test/queries/clientpositive/materialized_view_rewrite_5.q index 9ffae37..2964c83 100644 --- a/ql/src/test/queries/clientpositive/materialized_view_rewrite_5.q +++ b/ql/src/test/queries/clientpositive/materialized_view_rewrite_5.q @@ -7,291 +7,291 @@ set hive.strict.checks.cartesian.product=false; set hive.stats.fetch.column.stats=true; set hive.materializedview.rewriting=true; -create table emps ( +create table emps_n2 ( empid int, deptno int, name varchar(256), salary float, commission int) stored as orc TBLPROPERTIES ('transactional'='true'); -insert into emps values (100, 10, 'Bill', 10000, 1000), (200, 20, 'Eric', 8000, 500), +insert into emps_n2 values (100, 10, 'Bill', 10000, 1000), (200, 20, 'Eric', 8000, 500), (150, 10, 'Sebastian', 7000, null), (110, 10, 'Theodore', 10000, 250), (110, 10, 'Bill', 10000, 250); -analyze table emps compute statistics for columns; +analyze table emps_n2 compute statistics for columns; -create table depts ( +create table depts_n1 ( deptno int, name varchar(256), locationid int) stored as orc TBLPROPERTIES ('transactional'='true'); -insert into depts values (10, 'Sales', 10), (30, 'Marketing', null), (20, 'HR', 20); -analyze table depts compute statistics for columns; +insert into depts_n1 values (10, 'Sales', 10), (30, 'Marketing', null), (20, 'HR', 20); +analyze table depts_n1 compute statistics for columns; -create table dependents ( +create table dependents_n1 ( empid int, name varchar(256)) stored as orc TBLPROPERTIES ('transactional'='true'); -insert into dependents values (10, 'Michael'), (10, 'Jane'); -analyze table dependents compute statistics for columns; +insert into dependents_n1 values (10, 'Michael'), (10, 'Jane'); +analyze table dependents_n1 compute statistics for columns; -create table locations ( +create table locations_n1 ( locationid int, name varchar(256)) stored as orc TBLPROPERTIES ('transactional'='true'); -insert into locations values (10, 'San Francisco'), (10, 'San Diego'); -analyze table locations compute statistics for columns; +insert into locations_n1 values (10, 'San Francisco'), (10, 'San Diego'); +analyze table locations_n1 compute statistics for columns; -alter table emps add constraint pk1 primary key (empid) disable novalidate rely; -alter table depts add constraint pk2 primary key (deptno) disable novalidate rely; -alter table dependents add constraint pk3 primary key (empid) disable novalidate rely; -alter table locations add constraint pk4 primary key (locationid) disable novalidate rely; +alter table emps_n2 add constraint pk1 primary key (empid) disable novalidate rely; +alter table depts_n1 add constraint pk2 primary key (deptno) disable novalidate rely; +alter table dependents_n1 add constraint pk3 primary key (empid) disable novalidate rely; +alter table locations_n1 add constraint pk4 primary key (locationid) disable novalidate rely; -alter table emps add constraint fk1 foreign key (deptno) references depts(deptno) disable novalidate rely; -alter table depts add constraint fk2 foreign key (locationid) references locations(locationid) disable novalidate rely; +alter table emps_n2 add constraint fk1 foreign key (deptno) references depts_n1(deptno) disable novalidate rely; +alter table depts_n1 add constraint fk2 foreign key (locationid) references locations_n1(locationid) disable novalidate rely; -alter table emps change column deptno deptno int constraint nn1 not null disable novalidate rely; -alter table depts change column locationid locationid int constraint nn2 not null disable novalidate rely; +alter table emps_n2 change column deptno deptno int constraint nn1 not null disable novalidate rely; +alter table depts_n1 change column locationid locationid int constraint nn2 not null disable novalidate rely; -- EXAMPLE 8 -create materialized view mv1 enable rewrite as -select name, deptno, salary from emps where deptno > 15 group by name, deptno, salary; -analyze table mv1 compute statistics for columns; +create materialized view mv1_n1 enable rewrite as +select name, deptno, salary from emps_n2 where deptno > 15 group by name, deptno, salary; +analyze table mv1_n1 compute statistics for columns; explain -select name from emps where deptno >= 20 group by name; +select name from emps_n2 where deptno >= 20 group by name; -select name from emps where deptno >= 20 group by name; +select name from emps_n2 where deptno >= 20 group by name; -drop materialized view mv1; +drop materialized view mv1_n1; -- EXAMPLE 12 -create materialized view mv1 enable rewrite as +create materialized view mv1_n1 enable rewrite as select name, deptno, salary, count(*) as c, sum(empid) as s -from emps where deptno >= 15 group by name, deptno, salary; -analyze table mv1 compute statistics for columns; +from emps_n2 where deptno >= 15 group by name, deptno, salary; +analyze table mv1_n1 compute statistics for columns; explain select name, sum(empid) as s -from emps where deptno > 15 group by name; +from emps_n2 where deptno > 15 group by name; select name, sum(empid) as s -from emps where deptno > 15 group by name; +from emps_n2 where deptno > 15 group by name; -drop materialized view mv1; +drop materialized view mv1_n1; -- EXAMPLE 22 -create materialized view mv1 enable rewrite as -select depts.deptno, dependents.empid -from depts -join dependents on (depts.name = dependents.name) -join locations on (locations.name = dependents.name) -join emps on (emps.deptno = depts.deptno) -where depts.deptno > 10 and depts.deptno < 20 -group by depts.deptno, dependents.empid; -analyze table mv1 compute statistics for columns; +create materialized view mv1_n1 enable rewrite as +select depts_n1.deptno, dependents_n1.empid +from depts_n1 +join dependents_n1 on (depts_n1.name = dependents_n1.name) +join locations_n1 on (locations_n1.name = dependents_n1.name) +join emps_n2 on (emps_n2.deptno = depts_n1.deptno) +where depts_n1.deptno > 10 and depts_n1.deptno < 20 +group by depts_n1.deptno, dependents_n1.empid; +analyze table mv1_n1 compute statistics for columns; explain -select dependents.empid -from depts -join dependents on (depts.name = dependents.name) -join locations on (locations.name = dependents.name) -join emps on (emps.deptno = depts.deptno) -where depts.deptno > 11 and depts.deptno < 19 -group by dependents.empid; - -select dependents.empid -from depts -join dependents on (depts.name = dependents.name) -join locations on (locations.name = dependents.name) -join emps on (emps.deptno = depts.deptno) -where depts.deptno > 11 and depts.deptno < 19 -group by dependents.empid; - -drop materialized view mv1; +select dependents_n1.empid +from depts_n1 +join dependents_n1 on (depts_n1.name = dependents_n1.name) +join locations_n1 on (locations_n1.name = dependents_n1.name) +join emps_n2 on (emps_n2.deptno = depts_n1.deptno) +where depts_n1.deptno > 11 and depts_n1.deptno < 19 +group by dependents_n1.empid; + +select dependents_n1.empid +from depts_n1 +join dependents_n1 on (depts_n1.name = dependents_n1.name) +join locations_n1 on (locations_n1.name = dependents_n1.name) +join emps_n2 on (emps_n2.deptno = depts_n1.deptno) +where depts_n1.deptno > 11 and depts_n1.deptno < 19 +group by dependents_n1.empid; + +drop materialized view mv1_n1; -- EXAMPLE 24 -create materialized view mv1 enable rewrite as -select empid, depts.deptno, count(*) as c, sum(empid) as s -from emps join depts using (deptno) -group by empid, depts.deptno; -analyze table mv1 compute statistics for columns; +create materialized view mv1_n1 enable rewrite as +select empid, depts_n1.deptno, count(*) as c, sum(empid) as s +from emps_n2 join depts_n1 using (deptno) +group by empid, depts_n1.deptno; +analyze table mv1_n1 compute statistics for columns; explain -select deptno from emps group by deptno; +select deptno from emps_n2 group by deptno; -select deptno from emps group by deptno; +select deptno from emps_n2 group by deptno; -drop materialized view mv1; +drop materialized view mv1_n1; -- EXAMPLE 26 -create materialized view mv1 enable rewrite as -select empid, depts.deptno, count(*) as c, sum(empid) as s -from emps join depts using (deptno) -group by empid, depts.deptno; -analyze table mv1 compute statistics for columns; +create materialized view mv1_n1 enable rewrite as +select empid, depts_n1.deptno, count(*) as c, sum(empid) as s +from emps_n2 join depts_n1 using (deptno) +group by empid, depts_n1.deptno; +analyze table mv1_n1 compute statistics for columns; explain select deptno, empid, sum(empid) as s, count(*) as c -from emps group by empid, deptno; +from emps_n2 group by empid, deptno; select deptno, empid, sum(empid) as s, count(*) as c -from emps group by empid, deptno; +from emps_n2 group by empid, deptno; -drop materialized view mv1; +drop materialized view mv1_n1; -- EXAMPLE 30 -create materialized view mv1 enable rewrite as -select dependents.empid, emps.deptno, sum(salary) as s -from emps -join dependents on (emps.empid = dependents.empid) -group by dependents.empid, emps.deptno; -analyze table mv1 compute statistics for columns; +create materialized view mv1_n1 enable rewrite as +select dependents_n1.empid, emps_n2.deptno, sum(salary) as s +from emps_n2 +join dependents_n1 on (emps_n2.empid = dependents_n1.empid) +group by dependents_n1.empid, emps_n2.deptno; +analyze table mv1_n1 compute statistics for columns; explain -select dependents.empid, sum(salary) as s -from emps -join depts on (emps.deptno = depts.deptno) -join dependents on (emps.empid = dependents.empid) -group by dependents.empid; +select dependents_n1.empid, sum(salary) as s +from emps_n2 +join depts_n1 on (emps_n2.deptno = depts_n1.deptno) +join dependents_n1 on (emps_n2.empid = dependents_n1.empid) +group by dependents_n1.empid; -select dependents.empid, sum(salary) as s -from emps -join depts on (emps.deptno = depts.deptno) -join dependents on (emps.empid = dependents.empid) -group by dependents.empid; +select dependents_n1.empid, sum(salary) as s +from emps_n2 +join depts_n1 on (emps_n2.deptno = depts_n1.deptno) +join dependents_n1 on (emps_n2.empid = dependents_n1.empid) +group by dependents_n1.empid; -drop materialized view mv1; +drop materialized view mv1_n1; -- EXAMPLE 31 -create materialized view mv1 enable rewrite as -select dependents.empid, emps.deptno, sum(salary) as s -from emps -join dependents on (emps.empid = dependents.empid) -group by dependents.empid, emps.deptno; -analyze table mv1 compute statistics for columns; +create materialized view mv1_n1 enable rewrite as +select dependents_n1.empid, emps_n2.deptno, sum(salary) as s +from emps_n2 +join dependents_n1 on (emps_n2.empid = dependents_n1.empid) +group by dependents_n1.empid, emps_n2.deptno; +analyze table mv1_n1 compute statistics for columns; explain -select depts.name, sum(salary) as s -from emps -join depts on (emps.deptno = depts.deptno) -join dependents on (emps.empid = dependents.empid) -group by depts.name; +select depts_n1.name, sum(salary) as s +from emps_n2 +join depts_n1 on (emps_n2.deptno = depts_n1.deptno) +join dependents_n1 on (emps_n2.empid = dependents_n1.empid) +group by depts_n1.name; -select depts.name, sum(salary) as s -from emps -join depts on (emps.deptno = depts.deptno) -join dependents on (emps.empid = dependents.empid) -group by depts.name; +select depts_n1.name, sum(salary) as s +from emps_n2 +join depts_n1 on (emps_n2.deptno = depts_n1.deptno) +join dependents_n1 on (emps_n2.empid = dependents_n1.empid) +group by depts_n1.name; -drop materialized view mv1; +drop materialized view mv1_n1; -- EXAMPLE 41 -create materialized view mv1 enable rewrite as +create materialized view mv1_n1 enable rewrite as select a.empid deptno from -(select * from emps where empid = 1) a -join depts on (a.deptno = depts.deptno) -join dependents on (a.empid = dependents.empid); -analyze table mv1 compute statistics for columns; +(select * from emps_n2 where empid = 1) a +join depts_n1 on (a.deptno = depts_n1.deptno) +join dependents_n1 on (a.empid = dependents_n1.empid); +analyze table mv1_n1 compute statistics for columns; explain select a.empid from -(select * from emps where empid = 1) a -join dependents on (a.empid = dependents.empid); +(select * from emps_n2 where empid = 1) a +join dependents_n1 on (a.empid = dependents_n1.empid); select a.empid from -(select * from emps where empid = 1) a -join dependents on (a.empid = dependents.empid); +(select * from emps_n2 where empid = 1) a +join dependents_n1 on (a.empid = dependents_n1.empid); -drop materialized view mv1; +drop materialized view mv1_n1; -- EXAMPLE 42 -create materialized view mv1 enable rewrite as +create materialized view mv1_n1 enable rewrite as select a.empid, a.deptno from -(select * from emps where empid = 1) a -join depts on (a.deptno = depts.deptno) -join dependents on (a.empid = dependents.empid); -analyze table mv1 compute statistics for columns; +(select * from emps_n2 where empid = 1) a +join depts_n1 on (a.deptno = depts_n1.deptno) +join dependents_n1 on (a.empid = dependents_n1.empid); +analyze table mv1_n1 compute statistics for columns; explain select a.empid from -(select * from emps where empid = 1) a -join dependents on (a.empid = dependents.empid); +(select * from emps_n2 where empid = 1) a +join dependents_n1 on (a.empid = dependents_n1.empid); select a.empid from -(select * from emps where empid = 1) a -join dependents on (a.empid = dependents.empid); +(select * from emps_n2 where empid = 1) a +join dependents_n1 on (a.empid = dependents_n1.empid); -drop materialized view mv1; +drop materialized view mv1_n1; -- EXAMPLE 43 -create materialized view mv1 enable rewrite as +create materialized view mv1_n1 enable rewrite as select empid deptno from -(select * from emps where empid = 1) a -join depts on (a.deptno = depts.deptno); -analyze table mv1 compute statistics for columns; +(select * from emps_n2 where empid = 1) a +join depts_n1 on (a.deptno = depts_n1.deptno); +analyze table mv1_n1 compute statistics for columns; explain -select empid from emps where empid = 1; +select empid from emps_n2 where empid = 1; -select empid from emps where empid = 1; +select empid from emps_n2 where empid = 1; -drop materialized view mv1; +drop materialized view mv1_n1; -- EXAMPLE 44 -create materialized view mv1 enable rewrite as -select emps.empid, emps.deptno from emps -join depts on (emps.deptno = depts.deptno) -join dependents on (emps.empid = dependents.empid) -where emps.empid = 1; -analyze table mv1 compute statistics for columns; +create materialized view mv1_n1 enable rewrite as +select emps_n2.empid, emps_n2.deptno from emps_n2 +join depts_n1 on (emps_n2.deptno = depts_n1.deptno) +join dependents_n1 on (emps_n2.empid = dependents_n1.empid) +where emps_n2.empid = 1; +analyze table mv1_n1 compute statistics for columns; explain -select emps.empid from emps -join dependents on (emps.empid = dependents.empid) -where emps.empid = 1; +select emps_n2.empid from emps_n2 +join dependents_n1 on (emps_n2.empid = dependents_n1.empid) +where emps_n2.empid = 1; -select emps.empid from emps -join dependents on (emps.empid = dependents.empid) -where emps.empid = 1; +select emps_n2.empid from emps_n2 +join dependents_n1 on (emps_n2.empid = dependents_n1.empid) +where emps_n2.empid = 1; -drop materialized view mv1; +drop materialized view mv1_n1; -- EXAMPLE 45a -create materialized view mv1 enable rewrite as -select emps.empid, emps.deptno from emps -join depts a on (emps.deptno=a.deptno) -join depts b on (emps.deptno=b.deptno) -join dependents on (emps.empid = dependents.empid) -where emps.empid = 1; -analyze table mv1 compute statistics for columns; +create materialized view mv1_n1 enable rewrite as +select emps_n2.empid, emps_n2.deptno from emps_n2 +join depts_n1 a on (emps_n2.deptno=a.deptno) +join depts_n1 b on (emps_n2.deptno=b.deptno) +join dependents_n1 on (emps_n2.empid = dependents_n1.empid) +where emps_n2.empid = 1; +analyze table mv1_n1 compute statistics for columns; explain -select emps.empid from emps -join dependents on (emps.empid = dependents.empid) -where emps.empid = 1; +select emps_n2.empid from emps_n2 +join dependents_n1 on (emps_n2.empid = dependents_n1.empid) +where emps_n2.empid = 1; -select emps.empid from emps -join dependents on (emps.empid = dependents.empid) -where emps.empid = 1; +select emps_n2.empid from emps_n2 +join dependents_n1 on (emps_n2.empid = dependents_n1.empid) +where emps_n2.empid = 1; -drop materialized view mv1; +drop materialized view mv1_n1; -- EXAMPLE 45b -create materialized view mv1 enable rewrite as -select emps.empid, emps.deptno from emps -join depts a on (emps.deptno=a.deptno) -join depts b on (emps.deptno=b.deptno) -join dependents on (emps.empid = dependents.empid) -where emps.name = 'Sebastian'; -analyze table mv1 compute statistics for columns; +create materialized view mv1_n1 enable rewrite as +select emps_n2.empid, emps_n2.deptno from emps_n2 +join depts_n1 a on (emps_n2.deptno=a.deptno) +join depts_n1 b on (emps_n2.deptno=b.deptno) +join dependents_n1 on (emps_n2.empid = dependents_n1.empid) +where emps_n2.name = 'Sebastian'; +analyze table mv1_n1 compute statistics for columns; explain -select emps.empid from emps -join dependents on (emps.empid = dependents.empid) -where emps.name = 'Sebastian'; +select emps_n2.empid from emps_n2 +join dependents_n1 on (emps_n2.empid = dependents_n1.empid) +where emps_n2.name = 'Sebastian'; -select emps.empid from emps -join dependents on (emps.empid = dependents.empid) -where emps.name = 'Sebastian'; +select emps_n2.empid from emps_n2 +join dependents_n1 on (emps_n2.empid = dependents_n1.empid) +where emps_n2.name = 'Sebastian'; -drop materialized view mv1; +drop materialized view mv1_n1;
http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/materialized_view_rewrite_7.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/materialized_view_rewrite_7.q b/ql/src/test/queries/clientpositive/materialized_view_rewrite_7.q index e39c223..7d2eb48 100644 --- a/ql/src/test/queries/clientpositive/materialized_view_rewrite_7.q +++ b/ql/src/test/queries/clientpositive/materialized_view_rewrite_7.q @@ -7,132 +7,132 @@ set hive.strict.checks.cartesian.product=false; set hive.stats.fetch.column.stats=true; set hive.materializedview.rewriting=true; -create table emps ( +create table emps_n8 ( empid int, deptno int, name varchar(256), salary float, commission int) stored as orc TBLPROPERTIES ('transactional'='true'); -insert into emps values (100, 10, 'Bill', 10000, 1000), (200, 20, 'Eric', 8000, 500), +insert into emps_n8 values (100, 10, 'Bill', 10000, 1000), (200, 20, 'Eric', 8000, 500), (150, 10, 'Sebastian', 7000, null), (110, 10, 'Theodore', 10000, 250); -analyze table emps compute statistics for columns; +analyze table emps_n8 compute statistics for columns; -create table depts ( +create table depts_n6 ( deptno int, name varchar(256), locationid int) stored as orc TBLPROPERTIES ('transactional'='true'); -insert into depts values (10, 'Sales', 10), (30, 'Marketing', null), (20, 'HR', 20); -analyze table depts compute statistics for columns; +insert into depts_n6 values (10, 'Sales', 10), (30, 'Marketing', null), (20, 'HR', 20); +analyze table depts_n6 compute statistics for columns; -create table dependents ( +create table dependents_n4 ( empid int, name varchar(256)) stored as orc TBLPROPERTIES ('transactional'='true'); -insert into dependents values (10, 'Michael'), (10, 'Jane'); -analyze table dependents compute statistics for columns; +insert into dependents_n4 values (10, 'Michael'), (10, 'Jane'); +analyze table dependents_n4 compute statistics for columns; -create table locations ( +create table locations_n4 ( locationid int, name varchar(256)) stored as orc TBLPROPERTIES ('transactional'='true'); -insert into locations values (10, 'San Francisco'), (10, 'San Diego'); -analyze table locations compute statistics for columns; +insert into locations_n4 values (10, 'San Francisco'), (10, 'San Diego'); +analyze table locations_n4 compute statistics for columns; -alter table emps add constraint pk1 primary key (empid) disable novalidate rely; -alter table depts add constraint pk2 primary key (deptno) disable novalidate rely; -alter table dependents add constraint pk3 primary key (empid) disable novalidate rely; -alter table locations add constraint pk4 primary key (locationid) disable novalidate rely; +alter table emps_n8 add constraint pk1 primary key (empid) disable novalidate rely; +alter table depts_n6 add constraint pk2 primary key (deptno) disable novalidate rely; +alter table dependents_n4 add constraint pk3 primary key (empid) disable novalidate rely; +alter table locations_n4 add constraint pk4 primary key (locationid) disable novalidate rely; -alter table emps add constraint fk1 foreign key (deptno) references depts(deptno) disable novalidate rely; -alter table depts add constraint fk2 foreign key (locationid) references locations(locationid) disable novalidate rely; +alter table emps_n8 add constraint fk1 foreign key (deptno) references depts_n6(deptno) disable novalidate rely; +alter table depts_n6 add constraint fk2 foreign key (locationid) references locations_n4(locationid) disable novalidate rely; -alter table emps change column deptno deptno int constraint nn1 not null disable novalidate rely; -alter table depts change column locationid locationid int constraint nn2 not null disable novalidate rely; +alter table emps_n8 change column deptno deptno int constraint nn1 not null disable novalidate rely; +alter table depts_n6 change column locationid locationid int constraint nn2 not null disable novalidate rely; -- EXAMPLE 21 -- WORKS NOW -create materialized view mv1 enable rewrite as -select depts.deptno, dependents.empid -from depts -join dependents on (depts.name = dependents.name) -join locations on (locations.name = dependents.name) -join emps on (emps.deptno = depts.deptno) -where depts.deptno > 11 -group by depts.deptno, dependents.empid; -analyze table mv1 compute statistics for columns; +create materialized view mv1_n4 enable rewrite as +select depts_n6.deptno, dependents_n4.empid +from depts_n6 +join dependents_n4 on (depts_n6.name = dependents_n4.name) +join locations_n4 on (locations_n4.name = dependents_n4.name) +join emps_n8 on (emps_n8.deptno = depts_n6.deptno) +where depts_n6.deptno > 11 +group by depts_n6.deptno, dependents_n4.empid; +analyze table mv1_n4 compute statistics for columns; explain -select dependents.empid, depts.deptno -from depts -join dependents on (depts.name = dependents.name) -join locations on (locations.name = dependents.name) -join emps on (emps.deptno = depts.deptno) -where depts.deptno > 10 -group by dependents.empid, depts.deptno; - -select dependents.empid, depts.deptno -from depts -join dependents on (depts.name = dependents.name) -join locations on (locations.name = dependents.name) -join emps on (emps.deptno = depts.deptno) -where depts.deptno > 10 -group by dependents.empid, depts.deptno; - -drop materialized view mv1; +select dependents_n4.empid, depts_n6.deptno +from depts_n6 +join dependents_n4 on (depts_n6.name = dependents_n4.name) +join locations_n4 on (locations_n4.name = dependents_n4.name) +join emps_n8 on (emps_n8.deptno = depts_n6.deptno) +where depts_n6.deptno > 10 +group by dependents_n4.empid, depts_n6.deptno; + +select dependents_n4.empid, depts_n6.deptno +from depts_n6 +join dependents_n4 on (depts_n6.name = dependents_n4.name) +join locations_n4 on (locations_n4.name = dependents_n4.name) +join emps_n8 on (emps_n8.deptno = depts_n6.deptno) +where depts_n6.deptno > 10 +group by dependents_n4.empid, depts_n6.deptno; + +drop materialized view mv1_n4; -- EXAMPLE 33 -create materialized view mv1 enable rewrite as -select depts.deptno, dependents.empid, count(emps.salary) as s -from depts -join dependents on (depts.name = dependents.name) -join locations on (locations.name = dependents.name) -join emps on (emps.deptno = depts.deptno) -where depts.deptno > 11 and depts.deptno < 19 -group by depts.deptno, dependents.empid; -analyze table mv1 compute statistics for columns; +create materialized view mv1_n4 enable rewrite as +select depts_n6.deptno, dependents_n4.empid, count(emps_n8.salary) as s +from depts_n6 +join dependents_n4 on (depts_n6.name = dependents_n4.name) +join locations_n4 on (locations_n4.name = dependents_n4.name) +join emps_n8 on (emps_n8.deptno = depts_n6.deptno) +where depts_n6.deptno > 11 and depts_n6.deptno < 19 +group by depts_n6.deptno, dependents_n4.empid; +analyze table mv1_n4 compute statistics for columns; explain -select dependents.empid, count(emps.salary) + 1 -from depts -join dependents on (depts.name = dependents.name) -join locations on (locations.name = dependents.name) -join emps on (emps.deptno = depts.deptno) -where depts.deptno > 10 and depts.deptno < 20 -group by dependents.empid; - -select dependents.empid, count(emps.salary) + 1 -from depts -join dependents on (depts.name = dependents.name) -join locations on (locations.name = dependents.name) -join emps on (emps.deptno = depts.deptno) -where depts.deptno > 10 and depts.deptno < 20 -group by dependents.empid; - -drop materialized view mv1; +select dependents_n4.empid, count(emps_n8.salary) + 1 +from depts_n6 +join dependents_n4 on (depts_n6.name = dependents_n4.name) +join locations_n4 on (locations_n4.name = dependents_n4.name) +join emps_n8 on (emps_n8.deptno = depts_n6.deptno) +where depts_n6.deptno > 10 and depts_n6.deptno < 20 +group by dependents_n4.empid; + +select dependents_n4.empid, count(emps_n8.salary) + 1 +from depts_n6 +join dependents_n4 on (depts_n6.name = dependents_n4.name) +join locations_n4 on (locations_n4.name = dependents_n4.name) +join emps_n8 on (emps_n8.deptno = depts_n6.deptno) +where depts_n6.deptno > 10 and depts_n6.deptno < 20 +group by dependents_n4.empid; + +drop materialized view mv1_n4; -- EXAMPLE 40 -- REWRITING HAPPENS BUT DISCARDED -- DUE TO COST EXCEPT WITH HEURISTICS -create materialized view mv1 enable rewrite as -select depts.deptno, dependents.empid -from depts -join dependents on (depts.name = dependents.name) -join emps on (emps.deptno = depts.deptno) -where depts.deptno >= 10; -analyze table mv1 compute statistics for columns; +create materialized view mv1_n4 enable rewrite as +select depts_n6.deptno, dependents_n4.empid +from depts_n6 +join dependents_n4 on (depts_n6.name = dependents_n4.name) +join emps_n8 on (emps_n8.deptno = depts_n6.deptno) +where depts_n6.deptno >= 10; +analyze table mv1_n4 compute statistics for columns; explain -select dependents.empid -from depts -join dependents on (depts.name = dependents.name) -join emps on (emps.deptno = depts.deptno) -where depts.deptno > 0; - -select dependents.empid -from depts -join dependents on (depts.name = dependents.name) -join emps on (emps.deptno = depts.deptno) -where depts.deptno > 0; - -drop materialized view mv1; +select dependents_n4.empid +from depts_n6 +join dependents_n4 on (depts_n6.name = dependents_n4.name) +join emps_n8 on (emps_n8.deptno = depts_n6.deptno) +where depts_n6.deptno > 0; + +select dependents_n4.empid +from depts_n6 +join dependents_n4 on (depts_n6.name = dependents_n4.name) +join emps_n8 on (emps_n8.deptno = depts_n6.deptno) +where depts_n6.deptno > 0; + +drop materialized view mv1_n4; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/materialized_view_rewrite_9.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/materialized_view_rewrite_9.q b/ql/src/test/queries/clientpositive/materialized_view_rewrite_9.q index fda6d03..97700b0 100644 --- a/ql/src/test/queries/clientpositive/materialized_view_rewrite_9.q +++ b/ql/src/test/queries/clientpositive/materialized_view_rewrite_9.q @@ -7,7 +7,7 @@ set hive.strict.checks.cartesian.product=false; set hive.stats.fetch.column.stats=true; set hive.materializedview.rewriting=true; -create table if not exists source_table_001 ( +create table if not exists source_table_001_n0 ( MY_DATE timestamp, MY_ID bigint, MY_ID2 bigint, @@ -17,56 +17,56 @@ UP_VOLUME bigint ) stored AS ORC TBLPROPERTIES("transactional"="true"); -insert into table source_table_001 +insert into table source_table_001_n0 values ('2010-10-10 00:00:00', 1, 1, 'env', 1, 1); -analyze table source_table_001 compute statistics for columns; +analyze table source_table_001_n0 compute statistics for columns; -CREATE MATERIALIZED VIEW source_table_001_mv ENABLE REWRITE AS +CREATE MATERIALIZED VIEW source_table_001_mv_n0 ENABLE REWRITE AS SELECT SUM(A.DOWN_VOLUME) AS DOWN_VOLUME_SUM, SUM(A.UP_VOLUME) AS UP_VOLUME_SUM, A.MY_ID,A.MY_DATE,A.MY_ID2,A.ENVIRONMENT -from source_table_001 AS A +from source_table_001_n0 AS A group by A.MY_ID,A.MY_ID2,A.ENVIRONMENT,A.MY_DATE; -analyze table source_table_001_mv compute statistics for columns; +analyze table source_table_001_mv_n0 compute statistics for columns; explain select SUM(A.DOWN_VOLUME) AS DOWNLOAD_VOLUME_BYTES, FLOOR(A.MY_DATE to hour),A.MY_ID2,A.ENVIRONMENT -FROM source_table_001 AS A +FROM source_table_001_n0 AS A group by A.MY_ID,A.MY_ID2,A.ENVIRONMENT,FLOOR(A.MY_DATE to hour); -DROP MATERIALIZED VIEW source_table_001_mv; +DROP MATERIALIZED VIEW source_table_001_mv_n0; -CREATE MATERIALIZED VIEW source_table_001_mv ENABLE REWRITE AS +CREATE MATERIALIZED VIEW source_table_001_mv_n0 ENABLE REWRITE AS SELECT SUM(A.DOWN_VOLUME) AS DOWN_VOLUME_SUM, SUM(A.UP_VOLUME) AS UP_VOLUME_SUM, A.MY_ID,FLOOR(A.MY_DATE to hour),A.MY_ID2,A.ENVIRONMENT -from source_table_001 AS A +from source_table_001_n0 AS A group by A.MY_ID,A.MY_ID2,A.ENVIRONMENT,FLOOR(A.MY_DATE to hour); -analyze table source_table_001_mv compute statistics for columns; +analyze table source_table_001_mv_n0 compute statistics for columns; explain select SUM(A.DOWN_VOLUME) AS DOWNLOAD_VOLUME_BYTES, FLOOR(A.MY_DATE to day),A.MY_ID2,A.ENVIRONMENT -FROM source_table_001 AS A +FROM source_table_001_n0 AS A group by A.MY_ID,A.MY_ID2,A.ENVIRONMENT,FLOOR(A.MY_DATE to day); explain select SUM(A.DOWN_VOLUME) AS DOWNLOAD_VOLUME_BYTES, FLOOR(A.MY_DATE to hour),A.MY_ID2,A.ENVIRONMENT -FROM source_table_001 AS A +FROM source_table_001_n0 AS A group by A.MY_ID,A.MY_ID2,A.ENVIRONMENT,FLOOR(A.MY_DATE to hour); explain select SUM(A.DOWN_VOLUME) AS DOWNLOAD_VOLUME_BYTES, FLOOR(A.MY_DATE to second),A.MY_ID2,A.ENVIRONMENT -FROM source_table_001 AS A +FROM source_table_001_n0 AS A group by A.MY_ID,A.MY_ID2,A.ENVIRONMENT,FLOOR(A.MY_DATE to second); -DROP MATERIALIZED VIEW source_table_001_mv; +DROP MATERIALIZED VIEW source_table_001_mv_n0; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/materialized_view_rewrite_ssb.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/materialized_view_rewrite_ssb.q b/ql/src/test/queries/clientpositive/materialized_view_rewrite_ssb.q index ca9157e..880f4f5 100644 --- a/ql/src/test/queries/clientpositive/materialized_view_rewrite_ssb.q +++ b/ql/src/test/queries/clientpositive/materialized_view_rewrite_ssb.q @@ -7,7 +7,7 @@ set hive.strict.checks.cartesian.product=false; set hive.materializedview.rewriting=true; set hive.stats.column.autogather=true; -CREATE TABLE `customer_ext`( +CREATE TABLE `customer_ext_n0`( `c_custkey` bigint, `c_name` string, `c_address` string, @@ -20,9 +20,9 @@ ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE; -LOAD DATA LOCAL INPATH '../../data/files/ssb/customer/' into table `customer_ext`; +LOAD DATA LOCAL INPATH '../../data/files/ssb/customer/' into table `customer_ext_n0`; -CREATE TABLE `customer`( +CREATE TABLE `customer_n1`( `c_custkey` bigint, `c_name` string, `c_address` string, @@ -35,10 +35,10 @@ CREATE TABLE `customer`( STORED AS ORC TBLPROPERTIES ('transactional'='true'); -INSERT INTO `customer` -SELECT * FROM `customer_ext`; +INSERT INTO `customer_n1` +SELECT * FROM `customer_ext_n0`; -CREATE TABLE `dates_ext`( +CREATE TABLE `dates_ext_n0`( `d_datekey` bigint, `d_date` string, `d_dayofweek` string, @@ -60,9 +60,9 @@ ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE; -LOAD DATA LOCAL INPATH '../../data/files/ssb/date/' into table `dates_ext`; +LOAD DATA LOCAL INPATH '../../data/files/ssb/date/' into table `dates_ext_n0`; -CREATE TABLE `dates`( +CREATE TABLE `dates_n0`( `d_datekey` bigint, `d_date` string, `d_dayofweek` string, @@ -85,10 +85,10 @@ CREATE TABLE `dates`( STORED AS ORC TBLPROPERTIES ('transactional'='true'); -INSERT INTO `dates` -SELECT * FROM `dates_ext`; +INSERT INTO `dates_n0` +SELECT * FROM `dates_ext_n0`; -CREATE TABLE `ssb_part_ext`( +CREATE TABLE `ssb_part_ext_n0`( `p_partkey` bigint, `p_name` string, `p_mfgr` string, @@ -102,9 +102,9 @@ ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE; -LOAD DATA LOCAL INPATH '../../data/files/ssb/part/' into table `ssb_part_ext`; +LOAD DATA LOCAL INPATH '../../data/files/ssb/part/' into table `ssb_part_ext_n0`; -CREATE TABLE `ssb_part`( +CREATE TABLE `ssb_part_n0`( `p_partkey` bigint, `p_name` string, `p_mfgr` string, @@ -118,10 +118,10 @@ CREATE TABLE `ssb_part`( STORED AS ORC TBLPROPERTIES ('transactional'='true'); -INSERT INTO `ssb_part` -SELECT * FROM `ssb_part_ext`; +INSERT INTO `ssb_part_n0` +SELECT * FROM `ssb_part_ext_n0`; -CREATE TABLE `supplier_ext`( +CREATE TABLE `supplier_ext_n0`( `s_suppkey` bigint, `s_name` string, `s_address` string, @@ -133,9 +133,9 @@ ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE; -LOAD DATA LOCAL INPATH '../../data/files/ssb/supplier/' into table `supplier_ext`; +LOAD DATA LOCAL INPATH '../../data/files/ssb/supplier/' into table `supplier_ext_n0`; -CREATE TABLE `supplier`( +CREATE TABLE `supplier_n0`( `s_suppkey` bigint, `s_name` string, `s_address` string, @@ -147,10 +147,10 @@ CREATE TABLE `supplier`( STORED AS ORC TBLPROPERTIES ('transactional'='true'); -INSERT INTO `supplier` -SELECT * FROM `supplier_ext`; +INSERT INTO `supplier_n0` +SELECT * FROM `supplier_ext_n0`; -CREATE TABLE `lineorder_ext`( +CREATE TABLE `lineorder_ext_n0`( `lo_orderkey` bigint, `lo_linenumber` int, `lo_custkey` bigint not null disable rely, @@ -172,9 +172,9 @@ ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE; -LOAD DATA LOCAL INPATH '../../data/files/ssb/lineorder/' into table `lineorder_ext`; +LOAD DATA LOCAL INPATH '../../data/files/ssb/lineorder/' into table `lineorder_ext_n0`; -CREATE TABLE `lineorder`( +CREATE TABLE `lineorder_n0`( `lo_orderkey` bigint, `lo_linenumber` int, `lo_custkey` bigint not null disable rely, @@ -193,23 +193,23 @@ CREATE TABLE `lineorder`( `lo_commitdate` bigint, `lo_shipmode` string, primary key (`lo_orderkey`) disable rely, - constraint fk1 foreign key (`lo_custkey`) references `customer`(`c_custkey`) disable rely, - constraint fk2 foreign key (`lo_orderdate`) references `dates`(`d_datekey`) disable rely, - constraint fk3 foreign key (`lo_partkey`) references `ssb_part`(`p_partkey`) disable rely, - constraint fk4 foreign key (`lo_suppkey`) references `supplier`(`s_suppkey`) disable rely) + constraint fk1 foreign key (`lo_custkey`) references `customer_n1`(`c_custkey`) disable rely, + constraint fk2 foreign key (`lo_orderdate`) references `dates_n0`(`d_datekey`) disable rely, + constraint fk3 foreign key (`lo_partkey`) references `ssb_part_n0`(`p_partkey`) disable rely, + constraint fk4 foreign key (`lo_suppkey`) references `supplier_n0`(`s_suppkey`) disable rely) STORED AS ORC TBLPROPERTIES ('transactional'='true'); -INSERT INTO `lineorder` -SELECT * FROM `lineorder_ext`; +INSERT INTO `lineorder_n0` +SELECT * FROM `lineorder_ext_n0`; -analyze table customer compute statistics for columns; -analyze table dates compute statistics for columns; -analyze table ssb_part compute statistics for columns; -analyze table supplier compute statistics for columns; -analyze table lineorder compute statistics for columns; +analyze table customer_n1 compute statistics for columns; +analyze table dates_n0 compute statistics for columns; +analyze table ssb_part_n0 compute statistics for columns; +analyze table supplier_n0 compute statistics for columns; +analyze table lineorder_n0 compute statistics for columns; -CREATE MATERIALIZED VIEW `ssb_mv` ENABLE REWRITE +CREATE MATERIALIZED VIEW `ssb_mv_n0` ENABLE REWRITE AS SELECT c_city, @@ -231,7 +231,7 @@ SELECT lo_extendedprice * lo_discount discounted_price, lo_revenue - lo_supplycost net_revenue FROM - customer, dates, lineorder, ssb_part, supplier + customer_n1, dates_n0, lineorder_n0, ssb_part_n0, supplier_n0 where lo_orderdate = d_datekey and lo_partkey = p_partkey @@ -243,7 +243,7 @@ explain select sum(lo_extendedprice*lo_discount) as revenue from - lineorder, dates + lineorder_n0, dates_n0 where lo_orderdate = d_datekey and d_year = 1993 @@ -255,7 +255,7 @@ explain select sum(lo_extendedprice*lo_discount) as revenue from - lineorder, dates + lineorder_n0, dates_n0 where lo_orderdate = d_datekey and d_yearmonthnum = 199401 @@ -267,7 +267,7 @@ explain select sum(lo_extendedprice*lo_discount) as revenue from - lineorder, dates + lineorder_n0, dates_n0 where lo_orderdate = d_datekey and d_weeknuminyear = 6 @@ -280,7 +280,7 @@ explain select sum(lo_revenue) as lo_revenue, d_year, p_brand1 from - lineorder, dates, ssb_part, supplier + lineorder_n0, dates_n0, ssb_part_n0, supplier_n0 where lo_orderdate = d_datekey and lo_partkey = p_partkey @@ -297,7 +297,7 @@ explain select sum(lo_revenue) as lo_revenue, d_year, p_brand1 from - lineorder, dates, ssb_part, supplier + lineorder_n0, dates_n0, ssb_part_n0, supplier_n0 where lo_orderdate = d_datekey and lo_partkey = p_partkey @@ -314,7 +314,7 @@ explain select sum(lo_revenue) as lo_revenue, d_year, p_brand1 from - lineorder, dates, ssb_part, supplier + lineorder_n0, dates_n0, ssb_part_n0, supplier_n0 where lo_orderdate = d_datekey and lo_partkey = p_partkey @@ -332,7 +332,7 @@ select c_nation, s_nation, d_year, sum(lo_revenue) as lo_revenue from - customer, lineorder, supplier, dates + customer_n1, lineorder_n0, supplier_n0, dates_n0 where lo_custkey = c_custkey and lo_suppkey = s_suppkey @@ -350,7 +350,7 @@ explain select c_city, s_city, d_year, sum(lo_revenue) as lo_revenue from - customer, lineorder, supplier, dates + customer_n1, lineorder_n0, supplier_n0, dates_n0 where lo_custkey = c_custkey and lo_suppkey = s_suppkey @@ -368,7 +368,7 @@ explain select c_city, s_city, d_year, sum(lo_revenue) as lo_revenue from - customer, lineorder, supplier, dates + customer_n1, lineorder_n0, supplier_n0, dates_n0 where lo_custkey = c_custkey and lo_suppkey = s_suppkey @@ -386,7 +386,7 @@ explain select c_city, s_city, d_year, sum(lo_revenue) as lo_revenue from - customer, lineorder, supplier, dates + customer_n1, lineorder_n0, supplier_n0, dates_n0 where lo_custkey = c_custkey and lo_suppkey = s_suppkey @@ -405,7 +405,7 @@ select d_year, c_nation, sum(lo_revenue - lo_supplycost) as profit from - dates, customer, supplier, ssb_part, lineorder + dates_n0, customer_n1, supplier_n0, ssb_part_n0, lineorder_n0 where lo_custkey = c_custkey and lo_suppkey = s_suppkey @@ -425,7 +425,7 @@ select d_year, s_nation, p_category, sum(lo_revenue - lo_supplycost) as profit from - dates, customer, supplier, ssb_part, lineorder + dates_n0, customer_n1, supplier_n0, ssb_part_n0, lineorder_n0 where lo_custkey = c_custkey and lo_suppkey = s_suppkey @@ -446,7 +446,7 @@ select d_year, s_city, p_brand1, sum(lo_revenue - lo_supplycost) as profit from - dates, customer, supplier, ssb_part, lineorder + dates_n0, customer_n1, supplier_n0, ssb_part_n0, lineorder_n0 where lo_custkey = c_custkey and lo_suppkey = s_suppkey @@ -461,4 +461,4 @@ group by order by d_year, s_city, p_brand1; -DROP MATERIALIZED VIEW `ssb_mv`; +DROP MATERIALIZED VIEW `ssb_mv_n0`; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/materialized_view_rewrite_ssb_2.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/materialized_view_rewrite_ssb_2.q b/ql/src/test/queries/clientpositive/materialized_view_rewrite_ssb_2.q index 6752f87..6a92d9a 100644 --- a/ql/src/test/queries/clientpositive/materialized_view_rewrite_ssb_2.q +++ b/ql/src/test/queries/clientpositive/materialized_view_rewrite_ssb_2.q @@ -22,7 +22,7 @@ STORED AS TEXTFILE; LOAD DATA LOCAL INPATH '../../data/files/ssb/customer/' into table `customer_ext`; -CREATE TABLE `customer`( +CREATE TABLE `customer_n0`( `c_custkey` bigint, `c_name` string, `c_address` string, @@ -35,7 +35,7 @@ CREATE TABLE `customer`( STORED AS ORC TBLPROPERTIES ('transactional'='true'); -INSERT INTO `customer` +INSERT INTO `customer_n0` SELECT * FROM `customer_ext`; CREATE TABLE `dates_ext`( @@ -193,7 +193,7 @@ CREATE TABLE `lineorder`( `lo_commitdate` bigint, `lo_shipmode` string, primary key (`lo_orderkey`) disable rely, - constraint fk1 foreign key (`lo_custkey`) references `customer`(`c_custkey`) disable rely, + constraint fk1 foreign key (`lo_custkey`) references `customer_n0`(`c_custkey`) disable rely, constraint fk2 foreign key (`lo_orderdate`) references `dates`(`d_datekey`) disable rely, constraint fk3 foreign key (`lo_partkey`) references `ssb_part`(`p_partkey`) disable rely, constraint fk4 foreign key (`lo_suppkey`) references `supplier`(`s_suppkey`) disable rely) @@ -203,7 +203,7 @@ TBLPROPERTIES ('transactional'='true'); INSERT INTO `lineorder` SELECT * FROM `lineorder_ext`; -analyze table customer compute statistics for columns; +analyze table customer_n0 compute statistics for columns; analyze table dates compute statistics for columns; analyze table ssb_part compute statistics for columns; analyze table supplier compute statistics for columns; @@ -232,7 +232,7 @@ SELECT lo_extendedprice * lo_discount discounted_price, lo_revenue - lo_supplycost net_revenue FROM - customer, dates, lineorder, ssb_part, supplier + customer_n0, dates, lineorder, ssb_part, supplier where lo_orderdate = d_datekey and lo_partkey = p_partkey @@ -333,7 +333,7 @@ select c_nation, s_nation, d_year, sum(lo_revenue) as lo_revenue from - customer, lineorder, supplier, dates + customer_n0, lineorder, supplier, dates where lo_custkey = c_custkey and lo_suppkey = s_suppkey @@ -351,7 +351,7 @@ explain select c_city, s_city, d_year, sum(lo_revenue) as lo_revenue from - customer, lineorder, supplier, dates + customer_n0, lineorder, supplier, dates where lo_custkey = c_custkey and lo_suppkey = s_suppkey @@ -369,7 +369,7 @@ explain select c_city, s_city, d_year, sum(lo_revenue) as lo_revenue from - customer, lineorder, supplier, dates + customer_n0, lineorder, supplier, dates where lo_custkey = c_custkey and lo_suppkey = s_suppkey @@ -387,7 +387,7 @@ explain select c_city, s_city, d_year, sum(lo_revenue) as lo_revenue from - customer, lineorder, supplier, dates + customer_n0, lineorder, supplier, dates where lo_custkey = c_custkey and lo_suppkey = s_suppkey @@ -406,7 +406,7 @@ select d_year, c_nation, sum(lo_revenue - lo_supplycost) as profit from - dates, customer, supplier, ssb_part, lineorder + dates, customer_n0, supplier, ssb_part, lineorder where lo_custkey = c_custkey and lo_suppkey = s_suppkey @@ -426,7 +426,7 @@ select d_year, s_nation, p_category, sum(lo_revenue - lo_supplycost) as profit from - dates, customer, supplier, ssb_part, lineorder + dates, customer_n0, supplier, ssb_part, lineorder where lo_custkey = c_custkey and lo_suppkey = s_suppkey @@ -447,7 +447,7 @@ select d_year, s_city, p_brand1, sum(lo_revenue - lo_supplycost) as profit from - dates, customer, supplier, ssb_part, lineorder + dates, customer_n0, supplier, ssb_part, lineorder where lo_custkey = c_custkey and lo_suppkey = s_suppkey http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/merge1.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/merge1.q b/ql/src/test/queries/clientpositive/merge1.q index bd8a857..8c2e6b3 100644 --- a/ql/src/test/queries/clientpositive/merge1.q +++ b/ql/src/test/queries/clientpositive/merge1.q @@ -6,30 +6,30 @@ set hive.merge.sparkfiles=true; -- SORT_QUERY_RESULTS -create table dest1(key int, val int); +create table dest1_n145(key int, val int); explain -insert overwrite table dest1 +insert overwrite table dest1_n145 select key, count(1) from src group by key; -insert overwrite table dest1 +insert overwrite table dest1_n145 select key, count(1) from src group by key; -select * from dest1; +select * from dest1_n145; -drop table dest1; +drop table dest1_n145; -create table test_src(key string, value string) partitioned by (ds string); -create table dest1(key string); +create table test_src_n2(key string, value string) partitioned by (ds string); +create table dest1_n145(key string); -insert overwrite table test_src partition(ds='101') select * from src; -insert overwrite table test_src partition(ds='102') select * from src; +insert overwrite table test_src_n2 partition(ds='101') select * from src; +insert overwrite table test_src_n2 partition(ds='102') select * from src; explain -insert overwrite table dest1 select key from test_src; -insert overwrite table dest1 select key from test_src; +insert overwrite table dest1_n145 select key from test_src_n2; +insert overwrite table dest1_n145 select key from test_src_n2; set hive.merge.smallfiles.avgsize=16; explain -insert overwrite table dest1 select key from test_src; -insert overwrite table dest1 select key from test_src; +insert overwrite table dest1_n145 select key from test_src_n2; +insert overwrite table dest1_n145 select key from test_src_n2; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/merge2.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/merge2.q b/ql/src/test/queries/clientpositive/merge2.q index e3ec76a..a95cc88 100644 --- a/ql/src/test/queries/clientpositive/merge2.q +++ b/ql/src/test/queries/clientpositive/merge2.q @@ -11,31 +11,31 @@ set mapred.max.split.size=256; -- SORT_QUERY_RESULTS -create table test1(key int, val int); +create table test1_n10(key int, val int); explain -insert overwrite table test1 +insert overwrite table test1_n10 select key, count(1) from src group by key; -insert overwrite table test1 +insert overwrite table test1_n10 select key, count(1) from src group by key; -select * from test1; +select * from test1_n10; -drop table test1; +drop table test1_n10; -create table test_src(key string, value string) partitioned by (ds string); -create table test1(key string); +create table test_src_n0(key string, value string) partitioned by (ds string); +create table test1_n10(key string); -insert overwrite table test_src partition(ds='101') select * from src; -insert overwrite table test_src partition(ds='102') select * from src; +insert overwrite table test_src_n0 partition(ds='101') select * from src; +insert overwrite table test_src_n0 partition(ds='102') select * from src; explain -insert overwrite table test1 select key from test_src; -insert overwrite table test1 select key from test_src; +insert overwrite table test1_n10 select key from test_src_n0; +insert overwrite table test1_n10 select key from test_src_n0; set hive.merge.smallfiles.avgsize=16; explain -insert overwrite table test1 select key from test_src; -insert overwrite table test1 select key from test_src; +insert overwrite table test1_n10 select key from test_src_n0; +insert overwrite table test1_n10 select key from test_src_n0; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/merge_dynamic_partition.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/merge_dynamic_partition.q b/ql/src/test/queries/clientpositive/merge_dynamic_partition.q index 26a7931..adcbf97 100644 --- a/ql/src/test/queries/clientpositive/merge_dynamic_partition.q +++ b/ql/src/test/queries/clientpositive/merge_dynamic_partition.q @@ -8,14 +8,14 @@ set hive.exec.dynamic.partition.mode=nonstrict; -- SORT_QUERY_RESULTS -create table srcpart_merge_dp like srcpart; +create table srcpart_merge_dp_n1 like srcpart; -create table merge_dynamic_part like srcpart; +create table merge_dynamic_part_n1 like srcpart; -load data local inpath '../../data/files/srcbucket20.txt' INTO TABLE srcpart_merge_dp partition(ds='2008-04-08', hr=11); -load data local inpath '../../data/files/srcbucket21.txt' INTO TABLE srcpart_merge_dp partition(ds='2008-04-08', hr=11); -load data local inpath '../../data/files/srcbucket22.txt' INTO TABLE srcpart_merge_dp partition(ds='2008-04-08', hr=11); -load data local inpath '../../data/files/srcbucket23.txt' INTO TABLE srcpart_merge_dp partition(ds='2008-04-08', hr=11); +load data local inpath '../../data/files/srcbucket20.txt' INTO TABLE srcpart_merge_dp_n1 partition(ds='2008-04-08', hr=11); +load data local inpath '../../data/files/srcbucket21.txt' INTO TABLE srcpart_merge_dp_n1 partition(ds='2008-04-08', hr=11); +load data local inpath '../../data/files/srcbucket22.txt' INTO TABLE srcpart_merge_dp_n1 partition(ds='2008-04-08', hr=11); +load data local inpath '../../data/files/srcbucket23.txt' INTO TABLE srcpart_merge_dp_n1 partition(ds='2008-04-08', hr=11); set hive.input.format=org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat; set hive.merge.mapfiles=false; @@ -23,11 +23,11 @@ set hive.merge.mapredfiles=false; set hive.merge.smallfiles.avgsize=1000000000; set hive.optimize.sort.dynamic.partition=false; explain -insert overwrite table merge_dynamic_part partition (ds='2008-04-08', hr) select key, value, hr from srcpart_merge_dp where ds='2008-04-08'; -insert overwrite table merge_dynamic_part partition (ds='2008-04-08', hr) select key, value, hr from srcpart_merge_dp where ds='2008-04-08'; +insert overwrite table merge_dynamic_part_n1 partition (ds='2008-04-08', hr) select key, value, hr from srcpart_merge_dp_n1 where ds='2008-04-08'; +insert overwrite table merge_dynamic_part_n1 partition (ds='2008-04-08', hr) select key, value, hr from srcpart_merge_dp_n1 where ds='2008-04-08'; -select * from merge_dynamic_part; -show table extended like `merge_dynamic_part`; +select * from merge_dynamic_part_n1; +show table extended like `merge_dynamic_part_n1`; set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat; @@ -35,20 +35,20 @@ set hive.merge.mapfiles=true; set hive.merge.mapredfiles=true; set hive.merge.smallfiles.avgsize=1000000000; explain -insert overwrite table merge_dynamic_part partition (ds='2008-04-08', hr=11) select key, value from srcpart_merge_dp where ds='2008-04-08'; -insert overwrite table merge_dynamic_part partition (ds='2008-04-08', hr=11) select key, value from srcpart_merge_dp where ds='2008-04-08'; +insert overwrite table merge_dynamic_part_n1 partition (ds='2008-04-08', hr=11) select key, value from srcpart_merge_dp_n1 where ds='2008-04-08'; +insert overwrite table merge_dynamic_part_n1 partition (ds='2008-04-08', hr=11) select key, value from srcpart_merge_dp_n1 where ds='2008-04-08'; -select * from merge_dynamic_part; -show table extended like `merge_dynamic_part`; +select * from merge_dynamic_part_n1; +show table extended like `merge_dynamic_part_n1`; set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat; set hive.merge.mapfiles=true; set hive.merge.mapredfiles=true; set hive.merge.smallfiles.avgsize=1000000000; explain -insert overwrite table merge_dynamic_part partition (ds, hr) select key, value, ds, hr from srcpart_merge_dp where ds='2008-04-08' and hr=11; -insert overwrite table merge_dynamic_part partition (ds, hr) select key, value, ds, hr from srcpart_merge_dp where ds='2008-04-08' and hr=11;; +insert overwrite table merge_dynamic_part_n1 partition (ds, hr) select key, value, ds, hr from srcpart_merge_dp_n1 where ds='2008-04-08' and hr=11; +insert overwrite table merge_dynamic_part_n1 partition (ds, hr) select key, value, ds, hr from srcpart_merge_dp_n1 where ds='2008-04-08' and hr=11;; -select * from merge_dynamic_part; -show table extended like `merge_dynamic_part`; +select * from merge_dynamic_part_n1; +show table extended like `merge_dynamic_part_n1`; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/merge_dynamic_partition2.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/merge_dynamic_partition2.q b/ql/src/test/queries/clientpositive/merge_dynamic_partition2.q index 6bfa5d9..94c5c97 100644 --- a/ql/src/test/queries/clientpositive/merge_dynamic_partition2.q +++ b/ql/src/test/queries/clientpositive/merge_dynamic_partition2.q @@ -5,16 +5,16 @@ set hive.strict.checks.bucketing=false; set hive.exec.dynamic.partition=true; set hive.exec.dynamic.partition.mode=nonstrict; -create table srcpart_merge_dp like srcpart; +create table srcpart_merge_dp_n0 like srcpart; -create table merge_dynamic_part like srcpart; +create table merge_dynamic_part_n0 like srcpart; -load data local inpath '../../data/files/srcbucket20.txt' INTO TABLE srcpart_merge_dp partition(ds='2008-04-08', hr=11); -load data local inpath '../../data/files/srcbucket21.txt' INTO TABLE srcpart_merge_dp partition(ds='2008-04-08', hr=11); -load data local inpath '../../data/files/srcbucket22.txt' INTO TABLE srcpart_merge_dp partition(ds='2008-04-08', hr=11); -load data local inpath '../../data/files/srcbucket23.txt' INTO TABLE srcpart_merge_dp partition(ds='2008-04-08', hr=11); -load data local inpath '../../data/files/srcbucket0.txt' INTO TABLE srcpart_merge_dp partition(ds='2008-04-08', hr=12); -load data local inpath '../../data/files/srcbucket1.txt' INTO TABLE srcpart_merge_dp partition(ds='2008-04-08', hr=12); +load data local inpath '../../data/files/srcbucket20.txt' INTO TABLE srcpart_merge_dp_n0 partition(ds='2008-04-08', hr=11); +load data local inpath '../../data/files/srcbucket21.txt' INTO TABLE srcpart_merge_dp_n0 partition(ds='2008-04-08', hr=11); +load data local inpath '../../data/files/srcbucket22.txt' INTO TABLE srcpart_merge_dp_n0 partition(ds='2008-04-08', hr=11); +load data local inpath '../../data/files/srcbucket23.txt' INTO TABLE srcpart_merge_dp_n0 partition(ds='2008-04-08', hr=11); +load data local inpath '../../data/files/srcbucket0.txt' INTO TABLE srcpart_merge_dp_n0 partition(ds='2008-04-08', hr=12); +load data local inpath '../../data/files/srcbucket1.txt' INTO TABLE srcpart_merge_dp_n0 partition(ds='2008-04-08', hr=12); set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat; @@ -25,8 +25,8 @@ set hive.exec.compress.output=false; set hive.optimize.sort.dynamic.partition=false; explain -insert overwrite table merge_dynamic_part partition (ds='2008-04-08', hr) select key, value, hr from srcpart_merge_dp where ds='2008-04-08'; -insert overwrite table merge_dynamic_part partition (ds='2008-04-08', hr) select key, value, hr from srcpart_merge_dp where ds='2008-04-08'; +insert overwrite table merge_dynamic_part_n0 partition (ds='2008-04-08', hr) select key, value, hr from srcpart_merge_dp_n0 where ds='2008-04-08'; +insert overwrite table merge_dynamic_part_n0 partition (ds='2008-04-08', hr) select key, value, hr from srcpart_merge_dp_n0 where ds='2008-04-08'; -show table extended like `merge_dynamic_part`; +show table extended like `merge_dynamic_part_n0`; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/merge_dynamic_partition3.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/merge_dynamic_partition3.q b/ql/src/test/queries/clientpositive/merge_dynamic_partition3.q index e3070b1..8915efa 100644 --- a/ql/src/test/queries/clientpositive/merge_dynamic_partition3.q +++ b/ql/src/test/queries/clientpositive/merge_dynamic_partition3.q @@ -7,26 +7,26 @@ set hive.exec.dynamic.partition.mode=nonstrict; -- SORT_QUERY_RESULTS -create table srcpart_merge_dp like srcpart; +create table srcpart_merge_dp_n2 like srcpart; -create table merge_dynamic_part like srcpart; +create table merge_dynamic_part_n2 like srcpart; -load data local inpath '../../data/files/srcbucket20.txt' INTO TABLE srcpart_merge_dp partition(ds='2008-04-08', hr=11); -load data local inpath '../../data/files/srcbucket21.txt' INTO TABLE srcpart_merge_dp partition(ds='2008-04-08', hr=11); -load data local inpath '../../data/files/srcbucket22.txt' INTO TABLE srcpart_merge_dp partition(ds='2008-04-08', hr=11); -load data local inpath '../../data/files/srcbucket23.txt' INTO TABLE srcpart_merge_dp partition(ds='2008-04-08', hr=11); +load data local inpath '../../data/files/srcbucket20.txt' INTO TABLE srcpart_merge_dp_n2 partition(ds='2008-04-08', hr=11); +load data local inpath '../../data/files/srcbucket21.txt' INTO TABLE srcpart_merge_dp_n2 partition(ds='2008-04-08', hr=11); +load data local inpath '../../data/files/srcbucket22.txt' INTO TABLE srcpart_merge_dp_n2 partition(ds='2008-04-08', hr=11); +load data local inpath '../../data/files/srcbucket23.txt' INTO TABLE srcpart_merge_dp_n2 partition(ds='2008-04-08', hr=11); -load data local inpath '../../data/files/srcbucket20.txt' INTO TABLE srcpart_merge_dp partition(ds='2008-04-08', hr=12); -load data local inpath '../../data/files/srcbucket21.txt' INTO TABLE srcpart_merge_dp partition(ds='2008-04-08', hr=12); -load data local inpath '../../data/files/srcbucket22.txt' INTO TABLE srcpart_merge_dp partition(ds='2008-04-08', hr=12); -load data local inpath '../../data/files/srcbucket23.txt' INTO TABLE srcpart_merge_dp partition(ds='2008-04-08', hr=12); +load data local inpath '../../data/files/srcbucket20.txt' INTO TABLE srcpart_merge_dp_n2 partition(ds='2008-04-08', hr=12); +load data local inpath '../../data/files/srcbucket21.txt' INTO TABLE srcpart_merge_dp_n2 partition(ds='2008-04-08', hr=12); +load data local inpath '../../data/files/srcbucket22.txt' INTO TABLE srcpart_merge_dp_n2 partition(ds='2008-04-08', hr=12); +load data local inpath '../../data/files/srcbucket23.txt' INTO TABLE srcpart_merge_dp_n2 partition(ds='2008-04-08', hr=12); -load data local inpath '../../data/files/kv1.txt' INTO TABLE srcpart_merge_dp partition(ds='2008-04-09', hr=11); -load data local inpath '../../data/files/kv2.txt' INTO TABLE srcpart_merge_dp partition(ds='2008-04-09', hr=11); -load data local inpath '../../data/files/kv1.txt' INTO TABLE srcpart_merge_dp partition(ds='2008-04-09', hr=12); -load data local inpath '../../data/files/kv2.txt' INTO TABLE srcpart_merge_dp partition(ds='2008-04-09', hr=12); +load data local inpath '../../data/files/kv1.txt' INTO TABLE srcpart_merge_dp_n2 partition(ds='2008-04-09', hr=11); +load data local inpath '../../data/files/kv2.txt' INTO TABLE srcpart_merge_dp_n2 partition(ds='2008-04-09', hr=11); +load data local inpath '../../data/files/kv1.txt' INTO TABLE srcpart_merge_dp_n2 partition(ds='2008-04-09', hr=12); +load data local inpath '../../data/files/kv2.txt' INTO TABLE srcpart_merge_dp_n2 partition(ds='2008-04-09', hr=12); -show partitions srcpart_merge_dp; +show partitions srcpart_merge_dp_n2; set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat; set hive.merge.mapfiles=true; @@ -35,10 +35,10 @@ set hive.merge.smallfiles.avgsize=3000; set hive.exec.compress.output=false; explain -insert overwrite table merge_dynamic_part partition (ds, hr) select key, value, ds, hr from srcpart_merge_dp where ds>='2008-04-08'; +insert overwrite table merge_dynamic_part_n2 partition (ds, hr) select key, value, ds, hr from srcpart_merge_dp_n2 where ds>='2008-04-08'; -insert overwrite table merge_dynamic_part partition (ds, hr) select key, value, ds, hr from srcpart_merge_dp where ds>='2008-04-08'; +insert overwrite table merge_dynamic_part_n2 partition (ds, hr) select key, value, ds, hr from srcpart_merge_dp_n2 where ds>='2008-04-08'; -select ds, hr, count(1) from merge_dynamic_part where ds>='2008-04-08' group by ds, hr; +select ds, hr, count(1) from merge_dynamic_part_n2 where ds>='2008-04-08' group by ds, hr; -show table extended like `merge_dynamic_part`; +show table extended like `merge_dynamic_part_n2`; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/merge_dynamic_partition4.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/merge_dynamic_partition4.q b/ql/src/test/queries/clientpositive/merge_dynamic_partition4.q index f94db72..813ad8c 100644 --- a/ql/src/test/queries/clientpositive/merge_dynamic_partition4.q +++ b/ql/src/test/queries/clientpositive/merge_dynamic_partition4.q @@ -6,26 +6,26 @@ set hive.mapred.mode=nonstrict; -- this test verifies that the block merge task that can follow a query to generate dynamic -- partitions does not produce incorrect results by dropping partitions -create table srcpart_merge_dp like srcpart; +create table srcpart_merge_dp_n4 like srcpart; -create table srcpart_merge_dp_rc like srcpart; -alter table srcpart_merge_dp_rc set fileformat RCFILE; +create table srcpart_merge_dp_rc_n1 like srcpart; +alter table srcpart_merge_dp_rc_n1 set fileformat RCFILE; -create table merge_dynamic_part like srcpart; -alter table merge_dynamic_part set fileformat RCFILE; +create table merge_dynamic_part_n3 like srcpart; +alter table merge_dynamic_part_n3 set fileformat RCFILE; -load data local inpath '../../data/files/srcbucket20.txt' INTO TABLE srcpart_merge_dp partition(ds='2008-04-08', hr=11); -load data local inpath '../../data/files/srcbucket21.txt' INTO TABLE srcpart_merge_dp partition(ds='2008-04-08', hr=11); -load data local inpath '../../data/files/srcbucket22.txt' INTO TABLE srcpart_merge_dp partition(ds='2008-04-08', hr=11); -load data local inpath '../../data/files/srcbucket23.txt' INTO TABLE srcpart_merge_dp partition(ds='2008-04-08', hr=11); +load data local inpath '../../data/files/srcbucket20.txt' INTO TABLE srcpart_merge_dp_n4 partition(ds='2008-04-08', hr=11); +load data local inpath '../../data/files/srcbucket21.txt' INTO TABLE srcpart_merge_dp_n4 partition(ds='2008-04-08', hr=11); +load data local inpath '../../data/files/srcbucket22.txt' INTO TABLE srcpart_merge_dp_n4 partition(ds='2008-04-08', hr=11); +load data local inpath '../../data/files/srcbucket23.txt' INTO TABLE srcpart_merge_dp_n4 partition(ds='2008-04-08', hr=11); -load data local inpath '../../data/files/srcbucket20.txt' INTO TABLE srcpart_merge_dp partition(ds='2008-04-08', hr=12); -load data local inpath '../../data/files/srcbucket21.txt' INTO TABLE srcpart_merge_dp partition(ds='2008-04-08', hr=12); -load data local inpath '../../data/files/srcbucket22.txt' INTO TABLE srcpart_merge_dp partition(ds='2008-04-08', hr=12); -load data local inpath '../../data/files/srcbucket23.txt' INTO TABLE srcpart_merge_dp partition(ds='2008-04-08', hr=12); +load data local inpath '../../data/files/srcbucket20.txt' INTO TABLE srcpart_merge_dp_n4 partition(ds='2008-04-08', hr=12); +load data local inpath '../../data/files/srcbucket21.txt' INTO TABLE srcpart_merge_dp_n4 partition(ds='2008-04-08', hr=12); +load data local inpath '../../data/files/srcbucket22.txt' INTO TABLE srcpart_merge_dp_n4 partition(ds='2008-04-08', hr=12); +load data local inpath '../../data/files/srcbucket23.txt' INTO TABLE srcpart_merge_dp_n4 partition(ds='2008-04-08', hr=12); -insert overwrite table srcpart_merge_dp_rc partition (ds = '2008-04-08', hr) -select key, value, hr from srcpart_merge_dp where ds = '2008-04-08'; +insert overwrite table srcpart_merge_dp_rc_n1 partition (ds = '2008-04-08', hr) +select key, value, hr from srcpart_merge_dp_n4 where ds = '2008-04-08'; set hive.input.format=org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat; set hive.merge.mapfiles=true; @@ -36,12 +36,12 @@ set hive.exec.dynamic.partition=true; set hive.exec.dynamic.partition.mode=nonstrict; explain -insert overwrite table merge_dynamic_part partition (ds = '2008-04-08', hr) -select key, value, if(key % 2 == 0, 'a1', 'b1') as hr from srcpart_merge_dp_rc where ds = '2008-04-08'; +insert overwrite table merge_dynamic_part_n3 partition (ds = '2008-04-08', hr) +select key, value, if(key % 2 == 0, 'a1', 'b1') as hr from srcpart_merge_dp_rc_n1 where ds = '2008-04-08'; -insert overwrite table merge_dynamic_part partition (ds = '2008-04-08', hr) -select key, value, if(key % 2 == 0, 'a1', 'b1') as hr from srcpart_merge_dp_rc where ds = '2008-04-08'; +insert overwrite table merge_dynamic_part_n3 partition (ds = '2008-04-08', hr) +select key, value, if(key % 2 == 0, 'a1', 'b1') as hr from srcpart_merge_dp_rc_n1 where ds = '2008-04-08'; -show partitions merge_dynamic_part; +show partitions merge_dynamic_part_n3; -select count(*) from merge_dynamic_part; +select count(*) from merge_dynamic_part_n3; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/merge_empty.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/merge_empty.q b/ql/src/test/queries/clientpositive/merge_empty.q index 83c44b5..40e6942 100644 --- a/ql/src/test/queries/clientpositive/merge_empty.q +++ b/ql/src/test/queries/clientpositive/merge_empty.q @@ -4,12 +4,12 @@ set hive.merge.sparkfiles=true; set hive.auto.convert.join=false; set mapreduce.job.reduces=1000; -create table dummy (a string); -insert overwrite directory '/tmp/test' select src.key from src join dummy on src.key = dummy.a; +create table dummy_n3 (a string); +insert overwrite directory '/tmp/test' select src.key from src join dummy_n3 on src.key = dummy_n3.a; dfs -ls /tmp/test; -- verify that this doesn't merge for bucketed tables -create table foo (a bigint, b string) clustered by (a) into 256 buckets; -create table bar (a bigint, b string); -insert overwrite table foo select * from bar; -dfs -ls ${hiveconf:hive.metastore.warehouse.dir}/foo; +create table foo_n6 (a bigint, b string) clustered by (a) into 256 buckets; +create table bar_n1 (a bigint, b string); +insert overwrite table foo_n6 select * from bar_n1; +dfs -ls ${hiveconf:hive.metastore.warehouse.dir}/foo_n6; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/mergejoin.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/mergejoin.q b/ql/src/test/queries/clientpositive/mergejoin.q index 0f37002..8636f13 100644 --- a/ql/src/test/queries/clientpositive/mergejoin.q +++ b/ql/src/test/queries/clientpositive/mergejoin.q @@ -25,136 +25,136 @@ select * from src a join src1 b on a.key = b.key; select * from src a join src1 b on a.key = b.key; -CREATE TABLE srcbucket_mapjoin(key int, value string) partitioned by (ds string) CLUSTERED BY (key) INTO 2 BUCKETS STORED AS TEXTFILE; -CREATE TABLE tab_part (key int, value string) PARTITIONED BY(ds STRING) CLUSTERED BY (key) SORTED BY (key) INTO 4 BUCKETS STORED AS ORCFILE; -CREATE TABLE srcbucket_mapjoin_part (key int, value string) partitioned by (ds string) CLUSTERED BY (key) INTO 4 BUCKETS STORED AS TEXTFILE; +CREATE TABLE srcbucket_mapjoin_n16(key int, value string) partitioned by (ds string) CLUSTERED BY (key) INTO 2 BUCKETS STORED AS TEXTFILE; +CREATE TABLE tab_part_n10 (key int, value string) PARTITIONED BY(ds STRING) CLUSTERED BY (key) SORTED BY (key) INTO 4 BUCKETS STORED AS ORCFILE; +CREATE TABLE srcbucket_mapjoin_part_n17 (key int, value string) partitioned by (ds string) CLUSTERED BY (key) INTO 4 BUCKETS STORED AS TEXTFILE; -load data local inpath '../../data/files/bmj/000000_0' INTO TABLE srcbucket_mapjoin partition(ds='2008-04-08'); -load data local inpath '../../data/files/bmj1/000001_0' INTO TABLE srcbucket_mapjoin partition(ds='2008-04-08'); +load data local inpath '../../data/files/bmj/000000_0' INTO TABLE srcbucket_mapjoin_n16 partition(ds='2008-04-08'); +load data local inpath '../../data/files/bmj1/000001_0' INTO TABLE srcbucket_mapjoin_n16 partition(ds='2008-04-08'); -load data local inpath '../../data/files/bmj/000000_0' INTO TABLE srcbucket_mapjoin_part partition(ds='2008-04-08'); -load data local inpath '../../data/files/bmj/000001_0' INTO TABLE srcbucket_mapjoin_part partition(ds='2008-04-08'); -load data local inpath '../../data/files/bmj/000002_0' INTO TABLE srcbucket_mapjoin_part partition(ds='2008-04-08'); -load data local inpath '../../data/files/bmj/000003_0' INTO TABLE srcbucket_mapjoin_part partition(ds='2008-04-08'); +load data local inpath '../../data/files/bmj/000000_0' INTO TABLE srcbucket_mapjoin_part_n17 partition(ds='2008-04-08'); +load data local inpath '../../data/files/bmj/000001_0' INTO TABLE srcbucket_mapjoin_part_n17 partition(ds='2008-04-08'); +load data local inpath '../../data/files/bmj/000002_0' INTO TABLE srcbucket_mapjoin_part_n17 partition(ds='2008-04-08'); +load data local inpath '../../data/files/bmj/000003_0' INTO TABLE srcbucket_mapjoin_part_n17 partition(ds='2008-04-08'); set hive.optimize.bucketingsorting=false; -insert overwrite table tab_part partition (ds='2008-04-08') -select key,value from srcbucket_mapjoin_part; +insert overwrite table tab_part_n10 partition (ds='2008-04-08') +select key,value from srcbucket_mapjoin_part_n17; -CREATE TABLE tab(key int, value string) PARTITIONED BY(ds STRING) CLUSTERED BY (key) SORTED BY (key) INTO 2 BUCKETS STORED AS ORCFILE; -insert overwrite table tab partition (ds='2008-04-08') -select key,value from srcbucket_mapjoin; +CREATE TABLE tab_n9(key int, value string) PARTITIONED BY(ds STRING) CLUSTERED BY (key) SORTED BY (key) INTO 2 BUCKETS STORED AS ORCFILE; +insert overwrite table tab_n9 partition (ds='2008-04-08') +select key,value from srcbucket_mapjoin_n16; explain vectorization detail select count(*) -from tab a join tab_part b on a.key = b.key; +from tab_n9 a join tab_part_n10 b on a.key = b.key; -select * from tab a join tab_part b on a.key = b.key; +select * from tab_n9 a join tab_part_n10 b on a.key = b.key; set hive.join.emit.interval=2; -select * from tab a join tab_part b on a.key = b.key; +select * from tab_n9 a join tab_part_n10 b on a.key = b.key; explain vectorization detail select count(*) -from tab a left outer join tab_part b on a.key = b.key; +from tab_n9 a left outer join tab_part_n10 b on a.key = b.key; select count(*) -from tab a left outer join tab_part b on a.key = b.key; +from tab_n9 a left outer join tab_part_n10 b on a.key = b.key; explain vectorization detail select count (*) -from tab a right outer join tab_part b on a.key = b.key; +from tab_n9 a right outer join tab_part_n10 b on a.key = b.key; select count (*) -from tab a right outer join tab_part b on a.key = b.key; +from tab_n9 a right outer join tab_part_n10 b on a.key = b.key; explain vectorization detail select count(*) -from tab a full outer join tab_part b on a.key = b.key; +from tab_n9 a full outer join tab_part_n10 b on a.key = b.key; select count(*) -from tab a full outer join tab_part b on a.key = b.key; +from tab_n9 a full outer join tab_part_n10 b on a.key = b.key; explain vectorization detail -select count(*) from tab a join tab_part b on a.key = b.key join src1 c on a.value = c.value; -select count(*) from tab a join tab_part b on a.key = b.key join src1 c on a.value = c.value; +select count(*) from tab_n9 a join tab_part_n10 b on a.key = b.key join src1 c on a.value = c.value; +select count(*) from tab_n9 a join tab_part_n10 b on a.key = b.key join src1 c on a.value = c.value; explain vectorization detail -select count(*) from tab a join tab_part b on a.value = b.value; -select count(*) from tab a join tab_part b on a.value = b.value; +select count(*) from tab_n9 a join tab_part_n10 b on a.value = b.value; +select count(*) from tab_n9 a join tab_part_n10 b on a.value = b.value; explain vectorization detail -select count(*) from (select s1.key as key, s1.value as value from tab s1 join tab s3 on s1.key=s3.key +select count(*) from (select s1.key as key, s1.value as value from tab_n9 s1 join tab_n9 s3 on s1.key=s3.key UNION ALL -select s2.key as key, s2.value as value from tab s2 -) a join tab_part b on (a.key = b.key); +select s2.key as key, s2.value as value from tab_n9 s2 +) a join tab_part_n10 b on (a.key = b.key); explain vectorization detail -select count(*) from tab a join tab_part b on a.value = b.value; -select count(*) from tab a join tab_part b on a.value = b.value; +select count(*) from tab_n9 a join tab_part_n10 b on a.value = b.value; +select count(*) from tab_n9 a join tab_part_n10 b on a.value = b.value; explain vectorization detail -select count(*) from tab a join tab_part b on a.key = b.key join src1 c on a.value = c.value; -select count(*) from tab a join tab_part b on a.key = b.key join src1 c on a.value = c.value; +select count(*) from tab_n9 a join tab_part_n10 b on a.key = b.key join src1 c on a.value = c.value; +select count(*) from tab_n9 a join tab_part_n10 b on a.key = b.key join src1 c on a.value = c.value; explain vectorization detail -select count(*) from (select s1.key as key, s1.value as value from tab s1 join tab s3 on s1.key=s3.key +select count(*) from (select s1.key as key, s1.value as value from tab_n9 s1 join tab_n9 s3 on s1.key=s3.key UNION ALL -select s2.key as key, s2.value as value from tab s2 -) a join tab_part b on (a.key = b.key); +select s2.key as key, s2.value as value from tab_n9 s2 +) a join tab_part_n10 b on (a.key = b.key); explain vectorization detail select count(*) from (select rt1.id from -(select t1.key as id, t1.value as od from tab t1 order by id, od) rt1) vt1 +(select t1.key as id, t1.value as od from tab_n9 t1 order by id, od) rt1) vt1 join (select rt2.id from -(select t2.key as id, t2.value as od from tab_part t2 order by id, od) rt2) vt2 +(select t2.key as id, t2.value as od from tab_part_n10 t2 order by id, od) rt2) vt2 where vt1.id=vt2.id; select count(*) from (select rt1.id from -(select t1.key as id, t1.value as od from tab t1 order by id, od) rt1) vt1 +(select t1.key as id, t1.value as od from tab_n9 t1 order by id, od) rt1) vt1 join (select rt2.id from -(select t2.key as id, t2.value as od from tab_part t2 order by id, od) rt2) vt2 +(select t2.key as id, t2.value as od from tab_part_n10 t2 order by id, od) rt2) vt2 where vt1.id=vt2.id; set mapred.reduce.tasks=3; -select * from (select * from tab where tab.key = 0)a full outer join (select * from tab_part where tab_part.key = 98)b on a.key = b.key; -select * from (select * from tab where tab.key = 0)a right outer join (select * from tab_part where tab_part.key = 98)b on a.key = b.key; +select * from (select * from tab_n9 where tab_n9.key = 0)a full outer join (select * from tab_part_n10 where tab_part_n10.key = 98)b on a.key = b.key; +select * from (select * from tab_n9 where tab_n9.key = 0)a right outer join (select * from tab_part_n10 where tab_part_n10.key = 98)b on a.key = b.key; select * from -(select * from tab where tab.key = 0)a +(select * from tab_n9 where tab_n9.key = 0)a full outer join -(select * from tab_part where tab_part.key = 98)b join tab_part c on a.key = b.key and b.key = c.key; +(select * from tab_part_n10 where tab_part_n10.key = 98)b join tab_part_n10 c on a.key = b.key and b.key = c.key; select * from -(select * from tab where tab.key = 0)a +(select * from tab_n9 where tab_n9.key = 0)a full outer join -(select * from tab_part where tab_part.key = 98)b on a.key = b.key join tab_part c on b.key = c.key; +(select * from tab_part_n10 where tab_part_n10.key = 98)b on a.key = b.key join tab_part_n10 c on b.key = c.key; select * from -(select * from tab where tab.key = 0)a +(select * from tab_n9 where tab_n9.key = 0)a join -(select * from tab_part where tab_part.key = 98)b full outer join tab_part c on a.key = b.key and b.key = c.key; +(select * from tab_part_n10 where tab_part_n10.key = 98)b full outer join tab_part_n10 c on a.key = b.key and b.key = c.key; select * from -(select * from tab where tab.key = 0)a +(select * from tab_n9 where tab_n9.key = 0)a join -(select * from tab_part where tab_part.key = 98)b on a.key = b.key full outer join tab_part c on b.key = c.key; +(select * from tab_part_n10 where tab_part_n10.key = 98)b on a.key = b.key full outer join tab_part_n10 c on b.key = c.key; set hive.cbo.enable = false; select * from -(select * from tab where tab.key = 0)a +(select * from tab_n9 where tab_n9.key = 0)a full outer join -(select * from tab_part where tab_part.key = 98)b join tab_part c on a.key = b.key and b.key = c.key; +(select * from tab_part_n10 where tab_part_n10.key = 98)b join tab_part_n10 c on a.key = b.key and b.key = c.key; select * from -(select * from tab where tab.key = 0)a +(select * from tab_n9 where tab_n9.key = 0)a join -(select * from tab_part where tab_part.key = 98)b full outer join tab_part c on a.key = b.key and b.key = c.key; +(select * from tab_part_n10 where tab_part_n10.key = 98)b full outer join tab_part_n10 c on a.key = b.key and b.key = c.key; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/mergejoins.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/mergejoins.q b/ql/src/test/queries/clientpositive/mergejoins.q index 2b1ecba..fb5c9ac 100644 --- a/ql/src/test/queries/clientpositive/mergejoins.q +++ b/ql/src/test/queries/clientpositive/mergejoins.q @@ -1,12 +1,12 @@ --! qt:dataset:src set hive.mapred.mode=nonstrict; -create table a (val1 int, val2 int); -create table b (val1 int, val2 int); -create table c (val1 int, val2 int); -create table d (val1 int, val2 int); -create table e (val1 int, val2 int); +create table a_n0 (val1 int, val2 int); +create table b_n0 (val1 int, val2 int); +create table c_n0 (val1 int, val2 int); +create table d_n0 (val1 int, val2 int); +create table e_n0 (val1 int, val2 int); -explain select * from a join b on a.val1=b.val1 join c on a.val1=c.val1 join d on a.val1=d.val1 join e on a.val2=e.val2; +explain select * from a_n0 join b_n0 on a_n0.val1=b_n0.val1 join c_n0 on a_n0.val1=c_n0.val1 join d_n0 on a_n0.val1=d_n0.val1 join e_n0 on a_n0.val2=e_n0.val2; --HIVE-3070 filter on outer join condition removed while merging join tree -explain select * from src a join src b on a.key=b.key left outer join src c on b.key=c.key and b.key<10; +explain select * from src a_n0 join src b_n0 on a_n0.key=b_n0.key left outer join src c_n0 on b_n0.key=c_n0.key and b_n0.key<10; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/mergejoins_mixed.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/mergejoins_mixed.q b/ql/src/test/queries/clientpositive/mergejoins_mixed.q index 51f04cd..f2fbfa9 100644 --- a/ql/src/test/queries/clientpositive/mergejoins_mixed.q +++ b/ql/src/test/queries/clientpositive/mergejoins_mixed.q @@ -1,44 +1,44 @@ set hive.mapred.mode=nonstrict; -- HIVE-3464 -create table a (key string, value string); +create table a_n5 (key string, value string); --- (a-b-c-d) +-- (a_n5-b-c-d) explain -select * from a join a b on (a.key=b.key) left outer join a c on (b.key=c.key) left outer join a d on (a.key=d.key); +select * from a_n5 join a_n5 b on (a_n5.key=b.key) left outer join a_n5 c on (b.key=c.key) left outer join a_n5 d on (a_n5.key=d.key); explain -select * from a join a b on (a.key=b.key) left outer join a c on (b.key=c.key) right outer join a d on (a.key=d.key); +select * from a_n5 join a_n5 b on (a_n5.key=b.key) left outer join a_n5 c on (b.key=c.key) right outer join a_n5 d on (a_n5.key=d.key); explain -select * from a join a b on (a.key=b.key) right outer join a c on (b.key=c.key) left outer join a d on (a.key=d.key); +select * from a_n5 join a_n5 b on (a_n5.key=b.key) right outer join a_n5 c on (b.key=c.key) left outer join a_n5 d on (a_n5.key=d.key); explain -select * from a join a b on (a.key=b.key) right outer join a c on (b.key=c.key) right outer join a d on (a.key=d.key); +select * from a_n5 join a_n5 b on (a_n5.key=b.key) right outer join a_n5 c on (b.key=c.key) right outer join a_n5 d on (a_n5.key=d.key); --- ((a-b-d)-c) (reordered) +-- ((a_n5-b-d)-c) (reordered) explain -select * from a join a b on (a.key=b.key) left outer join a c on (b.value=c.key) left outer join a d on (a.key=d.key); +select * from a_n5 join a_n5 b on (a_n5.key=b.key) left outer join a_n5 c on (b.value=c.key) left outer join a_n5 d on (a_n5.key=d.key); explain -select * from a join a b on (a.key=b.key) right outer join a c on (b.value=c.key) right outer join a d on (a.key=d.key); +select * from a_n5 join a_n5 b on (a_n5.key=b.key) right outer join a_n5 c on (b.value=c.key) right outer join a_n5 d on (a_n5.key=d.key); explain -select * from a join a b on (a.key=b.key) full outer join a c on (b.value=c.key) full outer join a d on (a.key=d.key); +select * from a_n5 join a_n5 b on (a_n5.key=b.key) full outer join a_n5 c on (b.value=c.key) full outer join a_n5 d on (a_n5.key=d.key); --- (((a-b)-c)-d) +-- (((a_n5-b)-c)-d) explain -select * from a join a b on (a.key=b.key) left outer join a c on (b.value=c.key) right outer join a d on (a.key=d.key); +select * from a_n5 join a_n5 b on (a_n5.key=b.key) left outer join a_n5 c on (b.value=c.key) right outer join a_n5 d on (a_n5.key=d.key); explain -select * from a join a b on (a.key=b.key) left outer join a c on (b.value=c.key) full outer join a d on (a.key=d.key); +select * from a_n5 join a_n5 b on (a_n5.key=b.key) left outer join a_n5 c on (b.value=c.key) full outer join a_n5 d on (a_n5.key=d.key); explain -select * from a join a b on (a.key=b.key) right outer join a c on (b.value=c.key) left outer join a d on (a.key=d.key); +select * from a_n5 join a_n5 b on (a_n5.key=b.key) right outer join a_n5 c on (b.value=c.key) left outer join a_n5 d on (a_n5.key=d.key); explain -select * from a join a b on (a.key=b.key) right outer join a c on (b.value=c.key) full outer join a d on (a.key=d.key); +select * from a_n5 join a_n5 b on (a_n5.key=b.key) right outer join a_n5 c on (b.value=c.key) full outer join a_n5 d on (a_n5.key=d.key); --- ((a-b)-c-d) +-- ((a_n5-b)-c-d) explain -select * from a join a b on (a.key=b.key) left outer join a c on (b.value=c.key) left outer join a d on (c.key=d.key); +select * from a_n5 join a_n5 b on (a_n5.key=b.key) left outer join a_n5 c on (b.value=c.key) left outer join a_n5 d on (c.key=d.key); http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/metadata_only_queries.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/metadata_only_queries.q b/ql/src/test/queries/clientpositive/metadata_only_queries.q index bcf320b..cdbb76e 100644 --- a/ql/src/test/queries/clientpositive/metadata_only_queries.q +++ b/ql/src/test/queries/clientpositive/metadata_only_queries.q @@ -3,7 +3,7 @@ set hive.mapred.mode=nonstrict; set hive.explain.user=false; set hive.compute.query.using.stats=true; set hive.stats.autogather=true; -create table over10k( +create table over10k_n12( t tinyint, si smallint, i int, @@ -18,7 +18,7 @@ create table over10k( row format delimited fields terminated by '|'; -load data local inpath '../../data/files/over10k' into table over10k; +load data local inpath '../../data/files/over10k' into table over10k_n12; create table stats_tbl( t tinyint, @@ -47,11 +47,11 @@ create table stats_tbl_part( bin binary) partitioned by (dt string); -insert overwrite table stats_tbl select * from over10k; +insert overwrite table stats_tbl select * from over10k_n12; -insert into table stats_tbl_part partition (dt='2010') select * from over10k where t>0 and t<30; -insert into table stats_tbl_part partition (dt='2011') select * from over10k where t>30 and t<60; -insert into table stats_tbl_part partition (dt='2012') select * from over10k where t>60; +insert into table stats_tbl_part partition (dt='2010') select * from over10k_n12 where t>0 and t<30; +insert into table stats_tbl_part partition (dt='2011') select * from over10k_n12 where t>30 and t<60; +insert into table stats_tbl_part partition (dt='2012') select * from over10k_n12 where t>60; explain select count(*), sum(1), sum(0.2), count(1), count(s), count(bo), count(bin), count(si), max(i), min(b) from stats_tbl;