You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@hive.apache.org by ha...@apache.org on 2013/03/04 04:01:07 UTC
svn commit: r1452174 [1/5] - in /hive/branches/ptf-windowing/ql/src/test:
queries/clientpositive/ results/clientpositive/
Author: hashutosh
Date: Mon Mar 4 03:01:06 2013
New Revision: 1452174
URL: http://svn.apache.org/r1452174
Log:
HIVE-4082 [jira] Break up ptf tests in PTF, Windowing and Lead/Lag tests
(Prajakta Kalmegh via Ashutosh Chauhan)
Summary: HIVE-4082: Refactor tests
Test Plan: EMPTY
Reviewers: JIRA, ashutoshc
Reviewed By: ashutoshc
Differential Revision: https://reviews.facebook.net/D9033
Added:
hive/branches/ptf-windowing/ql/src/test/queries/clientpositive/leadlag.q
hive/branches/ptf-windowing/ql/src/test/queries/clientpositive/ptf.q
hive/branches/ptf-windowing/ql/src/test/queries/clientpositive/ptf_npath.q
hive/branches/ptf-windowing/ql/src/test/queries/clientpositive/windowing.q
- copied, changed from r1452169, hive/branches/ptf-windowing/ql/src/test/queries/clientpositive/ptf_general_queries.q
hive/branches/ptf-windowing/ql/src/test/results/clientpositive/leadlag.q.out
hive/branches/ptf-windowing/ql/src/test/results/clientpositive/ptf.q.out
hive/branches/ptf-windowing/ql/src/test/results/clientpositive/ptf_npath.q.out
hive/branches/ptf-windowing/ql/src/test/results/clientpositive/windowing.q.out
- copied, changed from r1452169, hive/branches/ptf-windowing/ql/src/test/results/clientpositive/ptf_general_queries.q.out
Removed:
hive/branches/ptf-windowing/ql/src/test/queries/clientpositive/ptf_over_no_partition_by.q
hive/branches/ptf-windowing/ql/src/test/queries/clientpositive/ptf_window_boundaries.q
hive/branches/ptf-windowing/ql/src/test/results/clientpositive/ptf_over_no_partition_by.q.out
hive/branches/ptf-windowing/ql/src/test/results/clientpositive/ptf_window_boundaries.q.out
Modified:
hive/branches/ptf-windowing/ql/src/test/queries/clientpositive/ptf_general_queries.q
hive/branches/ptf-windowing/ql/src/test/results/clientpositive/ptf_general_queries.q.out
Added: hive/branches/ptf-windowing/ql/src/test/queries/clientpositive/leadlag.q
URL: http://svn.apache.org/viewvc/hive/branches/ptf-windowing/ql/src/test/queries/clientpositive/leadlag.q?rev=1452174&view=auto
==============================================================================
--- hive/branches/ptf-windowing/ql/src/test/queries/clientpositive/leadlag.q (added)
+++ hive/branches/ptf-windowing/ql/src/test/queries/clientpositive/leadlag.q Mon Mar 4 03:01:06 2013
@@ -0,0 +1,87 @@
+DROP TABLE part;
+
+-- data setup
+CREATE TABLE part(
+ p_partkey INT,
+ p_name STRING,
+ p_mfgr STRING,
+ p_brand STRING,
+ p_type STRING,
+ p_size INT,
+ p_container STRING,
+ p_retailprice DOUBLE,
+ p_comment STRING
+);
+
+LOAD DATA LOCAL INPATH '../data/files/part_tiny.txt' overwrite into table part;
+
+--1. testLagWithPTFWindowing
+select p_mfgr, p_name,
+rank() as r,
+dense_rank() as dr,
+p_retailprice, sum(p_retailprice) as s1 over (partition by p_mfgr order by p_name rows between unbounded preceding and current row),
+p_size, p_size - lag(p_size,1) as deltaSz
+from noop(on part
+partition by p_mfgr
+order by p_name
+);
+
+-- 2. testLagWithWindowingNoPTF
+select p_mfgr, p_name,
+rank() as r,
+dense_rank() as dr,
+p_retailprice, sum(p_retailprice) as s1 over (rows between unbounded preceding and current row),
+p_size, p_size - lag(p_size,1) as deltaSz
+from part
+distribute by p_mfgr
+sort by p_name ;
+
+-- 3. testJoinWithLag
+select p1.p_mfgr, p1.p_name,
+p1.p_size, p1.p_size - lag(p1.p_size,1) as deltaSz
+from part p1 join part p2 on p1.p_partkey = p2.p_partkey
+distribute by p1.p_mfgr
+sort by p1.p_name ;
+
+-- 4. testLagInSum
+select p_mfgr,p_name, p_size,
+sum(p_size - lag(p_size,1)) as deltaSum
+from part
+distribute by p_mfgr
+sort by p_mfgr
+window w1 as (rows between 2 preceding and 2 following) ;
+
+-- 5. testLagInSumOverWindow
+select p_mfgr,p_name, p_size,
+sum(p_size - lag(p_size,1)) as deltaSum over w1
+from part
+distribute by p_mfgr
+sort by p_mfgr
+window w1 as (rows between 2 preceding and 2 following) ;
+
+-- 6. testRankInLead
+select p_mfgr, p_name, p_size,
+rank() as r1,
+lead(rank(), 1) as deltaRank
+from part
+distribute by p_mfgr
+sort by p_name;
+
+-- 7. testLeadWithPTF
+select p_mfgr, p_name,
+rank() as r,
+dense_rank() as dr,
+p_size, p_size - lead(p_size,1) as deltaSz
+from noop(on part
+partition by p_mfgr
+order by p_name
+)
+distribute by p_mfgr
+sort by p_name;
+
+-- 8. testOverNoPartitionMultipleAggregate
+select p_name, p_retailprice,
+lead(p_retailprice) as l1 over(),
+lag(p_retailprice) as l2 over()
+from part
+order by p_name;
Added: hive/branches/ptf-windowing/ql/src/test/queries/clientpositive/ptf.q
URL: http://svn.apache.org/viewvc/hive/branches/ptf-windowing/ql/src/test/queries/clientpositive/ptf.q?rev=1452174&view=auto
==============================================================================
--- hive/branches/ptf-windowing/ql/src/test/queries/clientpositive/ptf.q (added)
+++ hive/branches/ptf-windowing/ql/src/test/queries/clientpositive/ptf.q Mon Mar 4 03:01:06 2013
@@ -0,0 +1,318 @@
+DROP TABLE part;
+
+-- data setup
+CREATE TABLE part(
+ p_partkey INT,
+ p_name STRING,
+ p_mfgr STRING,
+ p_brand STRING,
+ p_type STRING,
+ p_size INT,
+ p_container STRING,
+ p_retailprice DOUBLE,
+ p_comment STRING
+);
+
+LOAD DATA LOCAL INPATH '../data/files/part_tiny.txt' overwrite into table part;
+
+--1. test1
+select p_mfgr, p_name, p_size,
+rank() as r,
+dense_rank() as dr,
+sum(p_retailprice) as s1 over (partition by p_mfgr order by p_name rows between unbounded preceding and current row)
+from noop(on part
+ partition by p_mfgr
+ order by p_name
+ );
+
+-- 2. testJoinWithNoop
+select p_mfgr, p_name,
+p_size, p_size - lag(p_size,1) as deltaSz
+from noop (on (select p1.* from part p1 join part p2 on p1.p_partkey = p2.p_partkey) j
+distribute by j.p_mfgr
+sort by j.p_name)
+distribute by p_mfgr
+sort by p_name;
+
+-- 3. testOnlyPTF
+select p_mfgr, p_name, p_size
+from noop(on part
+partition by p_mfgr
+order by p_name);
+
+-- 4. testPTFAlias
+select p_mfgr, p_name, p_size,
+rank() as r,
+dense_rank() as dr,
+sum(p_retailprice) as s1 over (partition by p_mfgr order by p_name rows between unbounded preceding and current row)
+from noop(on part
+ partition by p_mfgr
+ order by p_name
+ ) abc;
+
+-- 5. testPTFAndWhereWithWindowing
+select p_mfgr, p_name, p_size,
+rank() as r,
+dense_rank() as dr,
+p_size, p_size - lag(p_size,1) as deltaSz
+from noop(on part
+ partition by p_mfgr
+ order by p_name
+ )
+having p_size > 0
+distribute by p_mfgr
+sort by p_name;
+
+-- 6. testSWQAndPTFAndGBy
+select p_mfgr, p_name, p_size,
+rank() as r,
+dense_rank() as dr,
+p_size, p_size - lag(p_size,1) as deltaSz
+from noop(on part
+ partition by p_mfgr
+ order by p_name
+ )
+group by p_mfgr, p_name, p_size
+distribute by p_mfgr
+sort by p_name;
+
+-- 7. testJoin
+select abc.*
+from noop(on part
+partition by p_mfgr
+order by p_name
+) abc join part p1 on abc.p_partkey = p1.p_partkey;
+
+-- 8. testJoinRight
+select abc.*
+from part p1 join noop(on part
+partition by p_mfgr
+order by p_name
+) abc on abc.p_partkey = p1.p_partkey;
+
+-- 9. testNoopWithMap
+select p_mfgr, p_name, p_size,
+rank() as r over (partition by p_mfgr order by p_name, p_size desc)
+from noopwithmap(on part
+partition by p_mfgr
+order by p_name, p_size desc);
+
+-- 10. testNoopWithMapWithWindowing
+select p_mfgr, p_name, p_size,
+rank() as r,
+dense_rank() as dr,
+sum(p_retailprice) as s1 over (partition by p_mfgr order by p_name rows between unbounded preceding and current row)
+from noopwithmap(on part
+ partition by p_mfgr
+ order by p_name);
+
+-- 11. testHavingWithWindowingPTFNoGBY
+select p_mfgr, p_name, p_size,
+rank() as r,
+dense_rank() as dr,
+sum(p_retailprice) as s1 over (partition by p_mfgr order by p_name rows between unbounded preceding and current row)
+from noop(on part
+partition by p_mfgr
+order by p_name)
+having rank() < 4;
+
+-- 12. testFunctionChain
+select p_mfgr, p_name, p_size,
+rank() as r,
+dense_rank() as dr,
+sum(p_retailprice) as s1 over (partition by p_mfgr order by p_name rows between unbounded preceding and current row)
+from noop(on noopwithmap(on noop(on part
+partition by p_mfgr
+order by p_mfgr, p_name
+)));
+
+-- 13. testPTFAndWindowingInSubQ
+select p_mfgr, p_name,
+sub1.cd, sub1.s1
+from (select p_mfgr, p_name,
+count(p_size) as cd,
+p_retailprice,
+sum(p_retailprice) as s1 over w1
+from noop(on part
+partition by p_mfgr
+order by p_name)
+window w1 as (partition by p_mfgr order by p_name rows between 2 preceding and 2 following)
+) sub1 ;
+
+-- 14. testPTFJoinWithWindowingWithCount
+select abc.p_mfgr, abc.p_name,
+rank() as r,
+dense_rank() as dr,
+count(abc.p_name) as cd,
+abc.p_retailprice, sum(abc.p_retailprice) as s1 over (rows between unbounded preceding and current row),
+abc.p_size, abc.p_size - lag(abc.p_size,1) as deltaSz
+from noop(on part
+partition by p_mfgr
+order by p_name
+) abc join part p1 on abc.p_partkey = p1.p_partkey
+distribute by abc.p_mfgr
+sort by abc.p_name;
+
+-- 15. testDistinctInSelectWithPTF
+select DISTINCT p_mfgr, p_name, p_size
+from noop(on part
+partition by p_mfgr
+order by p_name);
+
+
+-- 16. testViewAsTableInputToPTF
+create view IF NOT EXISTS mfgr_price_view as
+select p_mfgr, p_brand,
+sum(p_retailprice) as s
+from part
+group by p_mfgr, p_brand;
+
+select p_mfgr, p_brand, s,
+sum(s) as s1 over w1
+from noop(on mfgr_price_view
+partition by p_mfgr
+order by p_mfgr)
+window w1 as ( partition by p_mfgr order by p_brand rows between 2 preceding and current row);
+
+-- 17. testMultipleInserts2SWQsWithPTF
+CREATE TABLE part_4(
+p_mfgr STRING,
+p_name STRING,
+p_size INT,
+r INT,
+dr INT,
+s DOUBLE);
+
+CREATE TABLE part_5(
+p_mfgr STRING,
+p_name STRING,
+p_size INT,
+s1 INT,
+s2 INT,
+r INT,
+dr INT,
+cud DOUBLE,
+fv1 INT);
+
+from noop(on part
+partition by p_mfgr
+order by p_name)
+INSERT OVERWRITE TABLE part_4 select p_mfgr, p_name, p_size,
+rank() as r,
+dense_rank() as dr,
+sum(p_retailprice) as s over (rows between unbounded preceding and current row)
+distribute by p_mfgr
+sort by p_name
+INSERT OVERWRITE TABLE part_5 select p_mfgr,p_name, p_size,
+sum(p_size) as s1 over (rows between unbounded preceding and current row),
+sum(p_size) as s2 over (range between p_size 5 less and current row),
+rank() as r,
+dense_rank() as dr,
+cume_dist() as cud,
+first_value(p_size, true) as fv1 over w1
+having p_size > 5
+distribute by p_mfgr
+sort by p_mfgr, p_name
+window w1 as (rows between 2 preceding and 2 following);
+
+select * from part_4;
+
+select * from part_5;
+
+-- 18. testMulti2OperatorsFunctionChainWithMap
+select p_mfgr, p_name,
+rank() as r,
+dense_rank() as dr,
+p_size, sum(p_size) as s1 over (partition by p_mfgr,p_name rows between unbounded preceding and current row)
+from noop(on
+ noopwithmap(on
+ noop(on
+ noop(on part
+ partition by p_mfgr
+ order by p_mfgr)
+ )
+ partition by p_mfgr,p_name
+ order by p_mfgr,p_name)
+ partition by p_mfgr,p_name
+ order by p_mfgr,p_name) ;
+
+-- 19. testMulti3OperatorsFunctionChain
+select p_mfgr, p_name,
+rank() as r,
+dense_rank() as dr,
+p_size, sum(p_size) as s1 over (partition by p_mfgr order by p_name rows between unbounded preceding and current row)
+from noop(on
+ noop(on
+ noop(on
+ noop(on part
+ partition by p_mfgr
+ order by p_mfgr)
+ )
+ partition by p_mfgr,p_name
+ order by p_mfgr,p_name)
+ partition by p_mfgr
+ order by p_mfgr ) ;
+
+-- 20. testMultiOperatorChainWithNoWindowing
+select p_mfgr, p_name,
+rank() as r over (partition by p_mfgr order by p_name),
+dense_rank() as dr,
+p_size, sum(p_size) as s1
+from noop(on
+ noop(on
+ noop(on
+ noop(on part
+ partition by p_mfgr,p_name
+ order by p_mfgr,p_name)
+ )
+ partition by p_mfgr
+ order by p_mfgr));
+
+
+-- 21. testMultiOperatorChainEndsWithNoopMap
+select p_mfgr, p_name,
+rank() as r,
+dense_rank() as dr,
+p_size, sum(p_size) as s1 over (partition by p_mfgr,p_name rows between unbounded preceding and current row)
+from noopwithmap(on
+ noop(on
+ noop(on
+ noop(on part
+ partition by p_mfgr,p_name
+ order by p_mfgr,p_name)
+ )
+ partition by p_mfgr
+ order by p_mfgr)
+ partition by p_mfgr,p_name
+ order by p_mfgr,p_name);
+
+-- 22. testMultiOperatorChainWithDiffPartitionForWindow1
+select p_mfgr, p_name,
+rank() as r,
+dense_rank() as dr,
+p_size,
+sum(p_size) as s1 over (rows between unbounded preceding and current row),
+sum(p_size) as s2 over (partition by p_mfgr,p_name order by p_mfgr,p_name rows between unbounded preceding and current row)
+from noop(on
+ noopwithmap(on
+ noop(on part
+ partition by p_mfgr, p_name
+ order by p_mfgr, p_name)
+ partition by p_mfgr
+ order by p_mfgr
+ ));
+
+-- 23. testMultiOperatorChainWithDiffPartitionForWindow2
+select p_mfgr, p_name,
+rank() as r,
+dense_rank() as dr,
+p_size,
+sum(p_size) as s1 over (rows between unbounded preceding and current row),
+sum(p_size) as s2 over (partition by p_mfgr order by p_mfgr rows between unbounded preceding and current row)
+from noopwithmap(on
+ noop(on
+ noop(on part
+ partition by p_mfgr, p_name
+ order by p_mfgr, p_name)
+ ));
+
Modified: hive/branches/ptf-windowing/ql/src/test/queries/clientpositive/ptf_general_queries.q
URL: http://svn.apache.org/viewvc/hive/branches/ptf-windowing/ql/src/test/queries/clientpositive/ptf_general_queries.q?rev=1452174&r1=1452173&r2=1452174&view=diff
==============================================================================
--- hive/branches/ptf-windowing/ql/src/test/queries/clientpositive/ptf_general_queries.q (original)
+++ hive/branches/ptf-windowing/ql/src/test/queries/clientpositive/ptf_general_queries.q Mon Mar 4 03:01:06 2013
@@ -1,5 +1,4 @@
DROP TABLE part;
-DROP TABLE flights_tiny;
-- data setup
CREATE TABLE part(
@@ -16,443 +15,13 @@ CREATE TABLE part(
LOAD DATA LOCAL INPATH '../data/files/part_tiny.txt' overwrite into table part;
-create table flights_tiny (
-ORIGIN_CITY_NAME string,
-DEST_CITY_NAME string,
-YEAR int,
-MONTH int,
-DAY_OF_MONTH int,
-ARR_DELAY float,
-FL_NUM string
-);
-
-LOAD DATA LOCAL INPATH '../data/files/flights_tiny.txt' OVERWRITE INTO TABLE flights_tiny;
-
---1. test1
-select p_mfgr, p_name, p_size,
-rank() as r,
-dense_rank() as dr,
-sum(p_retailprice) as s1 over (partition by p_mfgr order by p_name rows between unbounded preceding and current row)
-from noop(on part
- partition by p_mfgr
- order by p_name
- );
-
--- 2. test1NoPTF
-select p_mfgr, p_name, p_size,
-rank() as r,
-dense_rank() as dr,
-sum(p_retailprice) as s1 over (rows between unbounded preceding and current row)
-from part
-distribute by p_mfgr
-sort by p_name;
-
---3. testLeadLag
-select p_mfgr, p_name,
-rank() as r,
-dense_rank() as dr,
-p_retailprice, sum(p_retailprice) as s1 over (partition by p_mfgr order by p_name rows between unbounded preceding and current row),
-p_size, p_size - lag(p_size,1) as deltaSz
-from noop(on part
-partition by p_mfgr
-order by p_name
-);
-
--- 4. testLeadLagNoPTF
-select p_mfgr, p_name,
-rank() as r,
-dense_rank() as dr,
-p_retailprice, sum(p_retailprice) as s1 over (rows between unbounded preceding and current row),
-p_size, p_size - lag(p_size,1) as deltaSz
-from part
-distribute by p_mfgr
-sort by p_name ;
-
--- 5. testLeadLagNoPTFNoWindowing
+-- 1. testNoPTFNoWindowing
select p_mfgr, p_name, p_size
from part
distribute by p_mfgr
sort by p_name ;
-
--- 6. testJoinWithLeadLag
-select p1.p_mfgr, p1.p_name,
-p1.p_size, p1.p_size - lag(p1.p_size,1) as deltaSz
-from part p1 join part p2 on p1.p_partkey = p2.p_partkey
-distribute by p1.p_mfgr
-sort by p1.p_name ;
-
--- 7. testJoinWithNoop
-select p_mfgr, p_name,
-p_size, p_size - lag(p_size,1) as deltaSz
-from noop (on (select p1.* from part p1 join part p2 on p1.p_partkey = p2.p_partkey) j
-distribute by j.p_mfgr
-sort by j.p_name)
-distribute by p_mfgr
-sort by p_name;
-
--- 8. testGroupByWithSWQ
-select p_mfgr, p_name, p_size, min(p_retailprice),
-rank() as r,
-dense_rank() as dr,
-p_size, p_size - lag(p_size,1) as deltaSz
-from part
-group by p_mfgr, p_name, p_size
-distribute by p_mfgr
-sort by p_name ;
-
--- 9. testGroupByHavingWithSWQ
-select p_mfgr, p_name, p_size, min(p_retailprice),
-rank() as r,
-dense_rank() as dr,
-p_size, p_size - lag(p_size,1) as deltaSz
-from part
-group by p_mfgr, p_name, p_size
-having p_size > 0
-distribute by p_mfgr
-sort by p_name ;
--- 10. testOnlyPTF
-select p_mfgr, p_name, p_size
-from noop(on part
-partition by p_mfgr
-order by p_name);
-
--- 11. testAlias
-select p_mfgr, p_name, p_size,
-rank() as r,
-dense_rank() as dr,
-sum(p_retailprice) as s1 over (partition by p_mfgr order by p_name rows between unbounded preceding and current row)
-from noop(on part
- partition by p_mfgr
- order by p_name
- ) abc;
-
--- 12. testSWQAndPTFAndWhere
-select p_mfgr, p_name, p_size,
-rank() as r,
-dense_rank() as dr,
-p_size, p_size - lag(p_size,1) as deltaSz
-from noop(on part
- partition by p_mfgr
- order by p_name
- )
-having p_size > 0
-distribute by p_mfgr
-sort by p_name;
-
--- 13. testSWQAndPTFAndGBy
-select p_mfgr, p_name, p_size,
-rank() as r,
-dense_rank() as dr,
-p_size, p_size - lag(p_size,1) as deltaSz
-from noop(on part
- partition by p_mfgr
- order by p_name
- )
-group by p_mfgr, p_name, p_size
-distribute by p_mfgr
-sort by p_name;
-
--- 14. testCountNoWindowing
-select p_mfgr, p_name,
-rank() as r,
-dense_rank() as dr,
-count(p_size) as cd
-from part
-distribute by p_mfgr
-sort by p_name;
-
--- 15. testCountWithWindowing
-select p_mfgr, p_name,
-rank() as r,
-dense_rank() as dr,
-count(p_size) as cd,
-p_retailprice, sum(p_retailprice) as s1 over (rows between unbounded preceding and current row),
-p_size, p_size - lag(p_size,1) as deltaSz
-from part
-distribute by p_mfgr
-sort by p_name;
-
-
--- 16. testCountInSubQ
-select sub1.r, sub1.dr, sub1.cd, sub1.s1, sub1.deltaSz
-from (select p_mfgr, p_name,
-rank() as r,
-dense_rank() as dr,
-count(p_size) as cd,
-p_retailprice, sum(p_retailprice) as s1 over (rows between unbounded preceding and current row),
-p_size, p_size - lag(p_size,1) as deltaSz
-from part
-distribute by p_mfgr
-sort by p_name
-) sub1;
-
--- 17. testJoin
-select abc.*
-from noop(on part
-partition by p_mfgr
-order by p_name
-) abc join part p1 on abc.p_partkey = p1.p_partkey;
-
--- 18. testJoinRight
-select abc.*
-from part p1 join noop(on part
-partition by p_mfgr
-order by p_name
-) abc on abc.p_partkey = p1.p_partkey;
-
--- 19. testJoinWithWindowing
-select abc.p_mfgr, abc.p_name,
-rank() as r,
-dense_rank() as dr,
-abc.p_retailprice, sum(abc.p_retailprice) as s1 over (rows between unbounded preceding and current row),
-abc.p_size, abc.p_size - lag(abc.p_size,1) as deltaSz
-from noop(on part
-partition by p_mfgr
-order by p_name
-) abc join part p1 on abc.p_partkey = p1.p_partkey
-distribute by abc.p_mfgr
-sort by abc.p_name ;
-
--- 20. testMixedCaseAlias
-select p_mfgr, p_name, p_size, rank() as R
-from part
-distribute by p_mfgr
-sort by p_name, p_size desc;
-
--- 21. testNoopWithMap
-select p_mfgr, p_name, p_size,
-rank() as r over (partition by p_mfgr order by p_name, p_size desc)
-from noopwithmap(on part
-partition by p_mfgr
-order by p_name, p_size desc);
-
--- 22. testNoopWithMapWithWindowing
-select p_mfgr, p_name, p_size,
-rank() as r,
-dense_rank() as dr,
-sum(p_retailprice) as s1 over (partition by p_mfgr order by p_name rows between unbounded preceding and current row)
-from noopwithmap(on part
- partition by p_mfgr
- order by p_name);
-
--- 23. testHavingWithWindowingNoGBY
-select p_mfgr, p_name, p_size,
-rank() as r,
-dense_rank() as dr,
-sum(p_retailprice) as s1 over (rows between unbounded preceding and current row)
-from part
-having p_size > 5
-distribute by p_mfgr
-sort by p_name;
-
--- 24. testHavingWithWindowingCondRankNoGBY
-select p_mfgr, p_name, p_size,
-rank() as r,
-dense_rank() as dr,
-sum(p_retailprice) as s1 over (rows between unbounded preceding and current row)
-from part
-having rank() < 4
-distribute by p_mfgr
-sort by p_name;
-
--- 25. testHavingWithWindowingPTFNoGBY
-select p_mfgr, p_name, p_size,
-rank() as r,
-dense_rank() as dr,
-sum(p_retailprice) as s1 over (partition by p_mfgr order by p_name rows between unbounded preceding and current row)
-from noop(on part
-partition by p_mfgr
-order by p_name)
-having rank() < 4;
-
--- 26. testFirstLast
-select p_mfgr,p_name, p_size,
-sum(p_size) as s2 over (rows between current row and current row),
-first_value(p_size) as f over w1 ,
-last_value(p_size, false) as l over w1
-from part
-distribute by p_mfgr
-sort by p_mfgr
-window w1 as (rows between 2 preceding and 2 following);
-
--- 27. testFirstLastWithWhere
-select p_mfgr,p_name, p_size,
-rank() as r,
-sum(p_size) as s2 over (rows between current row and current row),
-first_value(p_size) as f over w1,
-last_value(p_size, false) as l over w1
-from part
-where p_mfgr = 'Manufacturer#3'
-distribute by p_mfgr
-sort by p_mfgr
-window w1 as (rows between 2 preceding and 2 following);
-
--- 28. testSumDelta
-select p_mfgr,p_name, p_size,
-sum(p_size - lag(p_size,1)) as deltaSum over w1
-from part
-distribute by p_mfgr
-sort by p_mfgr
-window w1 as (rows between 2 preceding and 2 following) ;
-
--- 29. testSumWindow
-select p_mfgr,p_name, p_size,
-sum(p_size) as s1 over w1,
-sum(p_size) as s2 over (rows between current row and current row)
-from part
-distribute by p_mfgr
-sort by p_mfgr
-window w1 as (rows between 2 preceding and 2 following);
-
--- 30. testNoSortClause
-select p_mfgr,p_name, p_size,
-rank() as r, dense_rank() as dr
-from part
-distribute by p_mfgr
-window w1 as (rows between 2 preceding and 2 following);
-
--- 31. testExpressions
-select p_mfgr,p_name, p_size,
-rank() as r,
-dense_rank() as dr,
-cume_dist() as cud,
-percent_rank() as pr,
-ntile(3) as nt,
-count(p_size) as ca,
-avg(p_size) as avg,
-stddev(p_size) as st,
-first_value(p_size % 5) as fv,
-last_value(p_size) as lv,
-first_value(p_size, true) as fvW1 over w1
-from part
-having p_size > 5
-distribute by p_mfgr
-sort by p_mfgr, p_name
-window w1 as (rows between 2 preceding and 2 following);
-
--- 32. testMultipleWindows
-select p_mfgr,p_name, p_size,
- rank() as r, dense_rank() as dr,
-cume_dist() as cud,
-sum(p_size) as s1 over (rows between unbounded preceding and current row),
-sum(p_size) as s2 over (range between p_size 5 less and current row),
-first_value(p_size, true) as fv1 over w1
-from part
-having p_size > 5
-distribute by p_mfgr
-sort by p_mfgr, p_name
-window w1 as (rows between 2 preceding and 2 following);
-
--- 33. testFunctionChain
-select p_mfgr, p_name, p_size,
-rank() as r,
-dense_rank() as dr,
-sum(p_retailprice) as s1 over (partition by p_mfgr order by p_name rows between unbounded preceding and current row)
-from noop(on noopwithmap(on noop(on part
-partition by p_mfgr
-order by p_mfgr, p_name
-)));
-
--- 34. testPTFAndWindowingInSubQ
-select p_mfgr, p_name,
-sub1.cd, sub1.s1
-from (select p_mfgr, p_name,
-count(p_size) as cd,
-p_retailprice,
-sum(p_retailprice) as s1 over w1
-from noop(on part
-partition by p_mfgr
-order by p_name)
-window w1 as (partition by p_mfgr order by p_name rows between 2 preceding and 2 following)
-) sub1 ;
-
--- 35. testCountStar
-select p_mfgr,p_name, p_size,
-count(*) as c,
-count(p_size) as ca,
-first_value(p_size, true) as fvW1 over w1
-from part
-having p_size > 5
-distribute by p_mfgr
-sort by p_mfgr, p_name
-window w1 as (rows between 2 preceding and 2 following);
-
--- 36. testJoinWithWindowingWithCount
-select abc.p_mfgr, abc.p_name,
-rank() as r,
-dense_rank() as dr,
-count(abc.p_name) as cd,
-abc.p_retailprice, sum(abc.p_retailprice) as s1 over (rows between unbounded preceding and current row),
-abc.p_size, abc.p_size - lag(abc.p_size,1) as deltaSz
-from noop(on part
-partition by p_mfgr
-order by p_name
-) abc join part p1 on abc.p_partkey = p1.p_partkey
-distribute by abc.p_mfgr
-sort by abc.p_name;
-
--- 37. testSumDelta
-select p_mfgr,p_name, p_size,
-sum(p_size - lag(p_size,1)) as deltaSum
-from part
-distribute by p_mfgr
-sort by p_mfgr
-window w1 as (rows between 2 preceding and 2 following) ;
-
--- 38. testUDAFs
-select p_mfgr,p_name, p_size,
-sum(p_retailprice) as s over w1,
-min(p_retailprice) as mi over w1,
-max(p_retailprice) as ma over w1,
-avg(p_retailprice) as ag over w1
-from part
-distribute by p_mfgr
-sort by p_mfgr, p_name
-window w1 as (rows between 2 preceding and 2 following);
-
--- 39. testUDAFsWithPTFWithGBY
-select p_mfgr,p_name, p_size, p_retailprice,
-sum(p_retailprice) as s over w1,
-min(p_retailprice) as mi ,
-max(p_retailprice) as ma ,
-avg(p_retailprice) as ag over w1
-from part
-group by p_mfgr,p_name, p_size, p_retailprice
-distribute by p_mfgr
-sort by p_mfgr, p_name
-window w1 as (rows between 2 preceding and 2 following);
-
--- 40. testSTATs
-select p_mfgr,p_name, p_size,
-stddev(p_retailprice) as sdev over w1,
-stddev_pop(p_retailprice) as sdev_pop over w1,
-collect_set(p_size) as uniq_size over w1,
-variance(p_retailprice) as var over w1,
-corr(p_size, p_retailprice) as cor over w1,
-covar_pop(p_size, p_retailprice) as covarp over w1
-from part
-distribute by p_mfgr
-sort by p_mfgr, p_name
-window w1 as (rows between 2 preceding and 2 following);
-
--- 41. testDISTs
-select p_mfgr,p_name, p_size,
-histogram_numeric(p_retailprice, 5) as hist over w1,
-percentile(p_partkey, 0.5) as per over w1,
-row_number() as rn
-from part
-distribute by p_mfgr
-sort by p_mfgr, p_name
-window w1 as (rows between 2 preceding and 2 following);
-
--- 42. testDistinctInSelectWithPTF
-select DISTINCT p_mfgr, p_name, p_size
-from noop(on part
-partition by p_mfgr
-order by p_name);
-
--- 43. testUDAFsNoWindowingNoPTFNoGBY
+-- 2. testUDAFsNoWindowingNoPTFNoGBY
select p_mfgr,p_name, p_retailprice,
sum(p_retailprice) as s,
min(p_retailprice) as mi,
@@ -461,378 +30,6 @@ avg(p_retailprice) as av
from part
distribute by p_mfgr
sort by p_mfgr, p_name;
-
--- 44. testViewAsTableInputWithWindowing
-create view IF NOT EXISTS mfgr_price_view as
-select p_mfgr, p_brand,
-sum(p_retailprice) as s
-from part
-group by p_mfgr, p_brand;
-
-select p_mfgr, p_brand, s,
-sum(s) as s1 over w1
-from mfgr_price_view
-distribute by p_mfgr
-sort by p_mfgr
-window w1 as (rows between 2 preceding and current row);
-
--- 45. testViewAsTableInputToPTF
-select p_mfgr, p_brand, s,
-sum(s) as s1 over w1
-from noop(on mfgr_price_view
-partition by p_mfgr
-order by p_mfgr)
-window w1 as ( partition by p_mfgr order by p_brand rows between 2 preceding and current row);
-
--- 46. testCreateViewWithWindowingQuery
-create view IF NOT EXISTS mfgr_brand_price_view as
-select p_mfgr, p_brand,
-sum(p_retailprice) as s over w1
-from part
-distribute by p_mfgr
-sort by p_mfgr
-window w1 as (rows between 2 preceding and current row);
-
-select * from mfgr_brand_price_view;
-
--- 47. testLateralViews
-select p_mfgr, p_name,
-lv_col, p_size, sum(p_size) as s over w1
-from (select p_mfgr, p_name, p_size, array(1,2,3) arr from part) p
-lateral view explode(arr) part_lv as lv_col
-distribute by p_mfgr
-sort by p_name
-window w1 as (rows between 2 preceding and current row);
--- 48. testConstExprInSelect
+-- 3. testConstExprInSelect
select 'tst1' as key, count(1) as value from part;
-
--- 49. testMultipleInserts3SWQs
-CREATE TABLE part_1(
-p_mfgr STRING,
-p_name STRING,
-p_size INT,
-r INT,
-dr INT,
-s DOUBLE);
-
-CREATE TABLE part_2(
-p_mfgr STRING,
-p_name STRING,
-p_size INT,
-r INT,
-dr INT,
-cud INT,
-s1 DOUBLE,
-s2 DOUBLE,
-fv1 INT);
-
-CREATE TABLE part_3(
-p_mfgr STRING,
-p_name STRING,
-p_size INT,
-c INT,
-ca INT,
-fv INT);
-
-from part
-INSERT OVERWRITE TABLE part_1
-select p_mfgr, p_name, p_size,
-rank() as r,
-dense_rank() as dr,
-sum(p_retailprice) as s over (rows between unbounded preceding and current row)
-distribute by p_mfgr
-sort by p_name
-INSERT OVERWRITE TABLE part_2
-select p_mfgr,p_name, p_size,
-rank() as r, dense_rank() as dr,
-cume_dist() as cud,
-sum(p_size) as s1 over (rows between unbounded preceding and current row),
-sum(p_size) as s2 over (range between p_size 5 less and current row),
-first_value(p_size, true) as fv1 over w1
-having p_size > 5
-distribute by p_mfgr
-sort by p_mfgr, p_name
-window w1 as (rows between 2 preceding and 2 following)
-INSERT OVERWRITE TABLE part_3
-select p_mfgr,p_name, p_size,
-count(*) as c,
-count(p_size) as ca,
-first_value(p_size, true) as fv over w1
-having p_size > 5
-distribute by p_mfgr
-sort by p_mfgr, p_name
-window w1 as (rows between 2 preceding and 2 following);
-
-select * from part_1;
-
-select * from part_2;
-
-select * from part_3;
-
--- 50. testGroupByHavingWithSWQAndAlias
-select p_mfgr, p_name, p_size, min(p_retailprice) as mi,
-rank() as r,
-dense_rank() as dr,
-p_size, p_size - lag(p_size,1) as deltaSz
-from part
-group by p_mfgr, p_name, p_size
-having p_size > 0
-distribute by p_mfgr
-sort by p_name;
-
--- 51. testMultipleRangeWindows
-select p_mfgr,p_name, p_size,
-sum(p_size) as s2 over (range between p_size 10 less and current row),
-sum(p_size) as s1 over (range between current row and p_size 10 more )
-from part
-distribute by p_mfgr
-sort by p_mfgr, p_size
-window w1 as (rows between 2 preceding and 2 following);
-
--- 52. testMultipleInserts2SWQsWithPTF
-CREATE TABLE part_4(
-p_mfgr STRING,
-p_name STRING,
-p_size INT,
-r INT,
-dr INT,
-s DOUBLE);
-
-CREATE TABLE part_5(
-p_mfgr STRING,
-p_name STRING,
-p_size INT,
-s1 INT,
-s2 INT,
-r INT,
-dr INT,
-cud DOUBLE,
-fv1 INT);
-
-from noop(on part
-partition by p_mfgr
-order by p_name)
-INSERT OVERWRITE TABLE part_4 select p_mfgr, p_name, p_size,
-rank() as r,
-dense_rank() as dr,
-sum(p_retailprice) as s over (rows between unbounded preceding and current row)
-distribute by p_mfgr
-sort by p_name
-INSERT OVERWRITE TABLE part_5 select p_mfgr,p_name, p_size,
-sum(p_size) as s1 over (rows between unbounded preceding and current row),
-sum(p_size) as s2 over (range between p_size 5 less and current row),
-rank() as r,
-dense_rank() as dr,
-cume_dist() as cud,
-first_value(p_size, true) as fv1 over w1
-having p_size > 5
-distribute by p_mfgr
-sort by p_mfgr, p_name
-window w1 as (rows between 2 preceding and 2 following);
-
-select * from part_4;
-
-select * from part_5;
-
--- 53. testPartOrderInUDAFInvoke
-select p_mfgr, p_name, p_size,
-sum(p_size) as s over (partition by p_mfgr order by p_name rows between 2 preceding and 2 following)
-from part;
-
--- 54. testPartOrderInWdwDef
-select p_mfgr, p_name, p_size,
-sum(p_size) as s over w1
-from part
-window w1 as (partition by p_mfgr order by p_name rows between 2 preceding and 2 following);
-
--- 55. testDefaultPartitioningSpecRules
-select p_mfgr, p_name, p_size,
-sum(p_size) as s over w1,
- sum(p_size) as s2 over w2
-from part
-sort by p_name
-window w1 as (partition by p_mfgr rows between 2 preceding and 2 following),
- w2 as (partition by p_mfgr order by p_name);
-
--- 56. testWindowCrossReference
-select p_mfgr, p_name, p_size,
-sum(p_size) as s1 over w1,
-sum(p_size) as s2 over w2
-from part
-window w1 as (partition by p_mfgr order by p_mfgr rows between 2 preceding and 2 following),
- w2 as w1;
-
-
--- 57. testWindowInheritance
-select p_mfgr, p_name, p_size,
-sum(p_size) as s1 over w1,
-sum(p_size) as s2 over w2
-from part
-window w1 as (partition by p_mfgr order by p_mfgr rows between 2 preceding and 2 following),
- w2 as (w1 rows between unbounded preceding and current row);
-
-
--- 58. testWindowForwardReference
-select p_mfgr, p_name, p_size,
-sum(p_size) as s1 over w1,
-sum(p_size) as s2 over w2,
-sum(p_size) as s3 over w3
-from part
-distribute by p_mfgr
-sort by p_mfgr
-window w1 as (rows between 2 preceding and 2 following),
- w2 as w3,
- w3 as (rows between unbounded preceding and current row);
-
-
--- 59. testWindowDefinitionPropagation
-select p_mfgr, p_name, p_size,
-sum(p_size) as s1 over w1,
-sum(p_size) as s2 over w2,
-sum(p_size) as s3 over (w3 rows between 2 preceding and 2 following)
-from part
-distribute by p_mfgr
-sort by p_mfgr
-window w1 as (rows between 2 preceding and 2 following),
- w2 as w3,
- w3 as (rows between unbounded preceding and current row);
-
--- 60. testDistinctWithWindowing
-select DISTINCT p_mfgr, p_name, p_size,
-sum(p_size) as s over w1
-from part
-distribute by p_mfgr
-sort by p_name
-window w1 as (rows between 2 preceding and 2 following);
-
--- 61. testMulti2OperatorsFunctionChainWithMap
-select p_mfgr, p_name,
-rank() as r,
-dense_rank() as dr,
-p_size, sum(p_size) as s1 over (partition by p_mfgr,p_name rows between unbounded preceding and current row)
-from noop(on
- noopwithmap(on
- noop(on
- noop(on part
- partition by p_mfgr
- order by p_mfgr)
- )
- partition by p_mfgr,p_name
- order by p_mfgr,p_name)
- partition by p_mfgr,p_name
- order by p_mfgr,p_name) ;
-
--- 62. testMulti3OperatorsFunctionChain
-select p_mfgr, p_name,
-rank() as r,
-dense_rank() as dr,
-p_size, sum(p_size) as s1 over (partition by p_mfgr order by p_name rows between unbounded preceding and current row)
-from noop(on
- noop(on
- noop(on
- noop(on part
- partition by p_mfgr
- order by p_mfgr)
- )
- partition by p_mfgr,p_name
- order by p_mfgr,p_name)
- partition by p_mfgr
- order by p_mfgr ) ;
-
--- 63. testMultiOperatorChainWithNoWindowing
-select p_mfgr, p_name,
-rank() as r over (partition by p_mfgr order by p_name),
-dense_rank() as dr,
-p_size, sum(p_size) as s1
-from noop(on
- noop(on
- noop(on
- noop(on part
- partition by p_mfgr,p_name
- order by p_mfgr,p_name)
- )
- partition by p_mfgr
- order by p_mfgr));
-
-
--- 64. testMultiOperatorChainEndsWithNoopMap
-select p_mfgr, p_name,
-rank() as r,
-dense_rank() as dr,
-p_size, sum(p_size) as s1 over (partition by p_mfgr,p_name rows between unbounded preceding and current row)
-from noopwithmap(on
- noop(on
- noop(on
- noop(on part
- partition by p_mfgr,p_name
- order by p_mfgr,p_name)
- )
- partition by p_mfgr
- order by p_mfgr)
- partition by p_mfgr,p_name
- order by p_mfgr,p_name);
-
---65. testMultiOperatorChainWithDiffPartitionForWindow1
-select p_mfgr, p_name,
-rank() as r,
-dense_rank() as dr,
-p_size,
-sum(p_size) as s1 over (rows between unbounded preceding and current row),
-sum(p_size) as s2 over (partition by p_mfgr,p_name order by p_mfgr,p_name rows between unbounded preceding and current row)
-from noop(on
- noopwithmap(on
- noop(on part
- partition by p_mfgr, p_name
- order by p_mfgr, p_name)
- partition by p_mfgr
- order by p_mfgr
- ));
-
---66. testMultiOperatorChainWithDiffPartitionForWindow2
-select p_mfgr, p_name,
-rank() as r,
-dense_rank() as dr,
-p_size,
-sum(p_size) as s1 over (rows between unbounded preceding and current row),
-sum(p_size) as s2 over (partition by p_mfgr order by p_mfgr rows between unbounded preceding and current row)
-from noopwithmap(on
- noop(on
- noop(on part
- partition by p_mfgr, p_name
- order by p_mfgr, p_name)
- ));
-
--- 67. basic Npath test
-select origin_city_name, fl_num, year, month, day_of_month, sz, tpath
-from npath(on
- flights_tiny
- distribute by fl_num
- sort by year, month, day_of_month
- arg1('LATE.LATE+'),
- arg2('LATE'), arg3(arr_delay > 15),
- arg4('origin_city_name, fl_num, year, month, day_of_month, size(tpath) as sz, tpath as tpath')
- );
-
--- 68. testRankWithPartitioning
-select p_mfgr, p_name, p_size,
-rank() as r over (partition by p_mfgr order by p_name )
-from part;
-
--- 69. testPartitioningVariousForms
-select p_mfgr, p_name, p_size,
-sum(p_retailprice) as s1 over (partition by p_mfgr order by p_mfgr),
-min(p_retailprice) as s2 over (partition by p_mfgr),
-max(p_retailprice) as s3 over (distribute by p_mfgr sort by p_mfgr),
-avg(p_retailprice) as s4 over (distribute by p_mfgr),
-count(p_retailprice) as s5 over (cluster by p_mfgr )
-from part
-;
-
--- 70. testPartitioningVariousForms2
-select p_mfgr, p_name, p_size,
-sum(p_retailprice) as s1 over (partition by p_mfgr, p_name order by p_mfgr, p_name rows between unbounded preceding and current row),
-min(p_retailprice) as s2 over (distribute by p_mfgr, p_name sort by p_mfgr, p_name rows between unbounded preceding and current row),
-max(p_retailprice) as s3 over (cluster by p_mfgr, p_name )
-from part
-;
Added: hive/branches/ptf-windowing/ql/src/test/queries/clientpositive/ptf_npath.q
URL: http://svn.apache.org/viewvc/hive/branches/ptf-windowing/ql/src/test/queries/clientpositive/ptf_npath.q?rev=1452174&view=auto
==============================================================================
--- hive/branches/ptf-windowing/ql/src/test/queries/clientpositive/ptf_npath.q (added)
+++ hive/branches/ptf-windowing/ql/src/test/queries/clientpositive/ptf_npath.q Mon Mar 4 03:01:06 2013
@@ -0,0 +1,24 @@
+DROP TABLE flights_tiny;
+
+create table flights_tiny (
+ORIGIN_CITY_NAME string,
+DEST_CITY_NAME string,
+YEAR int,
+MONTH int,
+DAY_OF_MONTH int,
+ARR_DELAY float,
+FL_NUM string
+);
+
+LOAD DATA LOCAL INPATH '../data/files/flights_tiny.txt' OVERWRITE INTO TABLE flights_tiny;
+
+-- 1. basic Npath test
+select origin_city_name, fl_num, year, month, day_of_month, sz, tpath
+from npath(on
+ flights_tiny
+ distribute by fl_num
+ sort by year, month, day_of_month
+ arg1('LATE.LATE+'),
+ arg2('LATE'), arg3(arr_delay > 15),
+ arg4('origin_city_name, fl_num, year, month, day_of_month, size(tpath) as sz, tpath as tpath')
+ );
Copied: hive/branches/ptf-windowing/ql/src/test/queries/clientpositive/windowing.q (from r1452169, hive/branches/ptf-windowing/ql/src/test/queries/clientpositive/ptf_general_queries.q)
URL: http://svn.apache.org/viewvc/hive/branches/ptf-windowing/ql/src/test/queries/clientpositive/windowing.q?p2=hive/branches/ptf-windowing/ql/src/test/queries/clientpositive/windowing.q&p1=hive/branches/ptf-windowing/ql/src/test/queries/clientpositive/ptf_general_queries.q&r1=1452169&r2=1452174&rev=1452174&view=diff
==============================================================================
--- hive/branches/ptf-windowing/ql/src/test/queries/clientpositive/ptf_general_queries.q (original)
+++ hive/branches/ptf-windowing/ql/src/test/queries/clientpositive/windowing.q Mon Mar 4 03:01:06 2013
@@ -1,5 +1,4 @@
DROP TABLE part;
-DROP TABLE flights_tiny;
-- data setup
CREATE TABLE part(
@@ -16,29 +15,7 @@ CREATE TABLE part(
LOAD DATA LOCAL INPATH '../data/files/part_tiny.txt' overwrite into table part;
-create table flights_tiny (
-ORIGIN_CITY_NAME string,
-DEST_CITY_NAME string,
-YEAR int,
-MONTH int,
-DAY_OF_MONTH int,
-ARR_DELAY float,
-FL_NUM string
-);
-
-LOAD DATA LOCAL INPATH '../data/files/flights_tiny.txt' OVERWRITE INTO TABLE flights_tiny;
-
---1. test1
-select p_mfgr, p_name, p_size,
-rank() as r,
-dense_rank() as dr,
-sum(p_retailprice) as s1 over (partition by p_mfgr order by p_name rows between unbounded preceding and current row)
-from noop(on part
- partition by p_mfgr
- order by p_name
- );
-
--- 2. test1NoPTF
+-- 1. testWindowing
select p_mfgr, p_name, p_size,
rank() as r,
dense_rank() as dr,
@@ -47,50 +24,7 @@ from part
distribute by p_mfgr
sort by p_name;
---3. testLeadLag
-select p_mfgr, p_name,
-rank() as r,
-dense_rank() as dr,
-p_retailprice, sum(p_retailprice) as s1 over (partition by p_mfgr order by p_name rows between unbounded preceding and current row),
-p_size, p_size - lag(p_size,1) as deltaSz
-from noop(on part
-partition by p_mfgr
-order by p_name
-);
-
--- 4. testLeadLagNoPTF
-select p_mfgr, p_name,
-rank() as r,
-dense_rank() as dr,
-p_retailprice, sum(p_retailprice) as s1 over (rows between unbounded preceding and current row),
-p_size, p_size - lag(p_size,1) as deltaSz
-from part
-distribute by p_mfgr
-sort by p_name ;
-
--- 5. testLeadLagNoPTFNoWindowing
-select p_mfgr, p_name, p_size
-from part
-distribute by p_mfgr
-sort by p_name ;
-
--- 6. testJoinWithLeadLag
-select p1.p_mfgr, p1.p_name,
-p1.p_size, p1.p_size - lag(p1.p_size,1) as deltaSz
-from part p1 join part p2 on p1.p_partkey = p2.p_partkey
-distribute by p1.p_mfgr
-sort by p1.p_name ;
-
--- 7. testJoinWithNoop
-select p_mfgr, p_name,
-p_size, p_size - lag(p_size,1) as deltaSz
-from noop (on (select p1.* from part p1 join part p2 on p1.p_partkey = p2.p_partkey) j
-distribute by j.p_mfgr
-sort by j.p_name)
-distribute by p_mfgr
-sort by p_name;
-
--- 8. testGroupByWithSWQ
+-- 2. testGroupByWithPartitioning
select p_mfgr, p_name, p_size, min(p_retailprice),
rank() as r,
dense_rank() as dr,
@@ -99,8 +33,8 @@ from part
group by p_mfgr, p_name, p_size
distribute by p_mfgr
sort by p_name ;
-
--- 9. testGroupByHavingWithSWQ
+
+-- 3. testGroupByHavingWithSWQ
select p_mfgr, p_name, p_size, min(p_retailprice),
rank() as r,
dense_rank() as dr,
@@ -110,59 +44,15 @@ group by p_mfgr, p_name, p_size
having p_size > 0
distribute by p_mfgr
sort by p_name ;
-
--- 10. testOnlyPTF
-select p_mfgr, p_name, p_size
-from noop(on part
-partition by p_mfgr
-order by p_name);
-
--- 11. testAlias
-select p_mfgr, p_name, p_size,
-rank() as r,
-dense_rank() as dr,
-sum(p_retailprice) as s1 over (partition by p_mfgr order by p_name rows between unbounded preceding and current row)
-from noop(on part
- partition by p_mfgr
- order by p_name
- ) abc;
-
--- 12. testSWQAndPTFAndWhere
-select p_mfgr, p_name, p_size,
-rank() as r,
-dense_rank() as dr,
-p_size, p_size - lag(p_size,1) as deltaSz
-from noop(on part
- partition by p_mfgr
- order by p_name
- )
-having p_size > 0
-distribute by p_mfgr
-sort by p_name;
-
--- 13. testSWQAndPTFAndGBy
-select p_mfgr, p_name, p_size,
-rank() as r,
-dense_rank() as dr,
-p_size, p_size - lag(p_size,1) as deltaSz
-from noop(on part
- partition by p_mfgr
- order by p_name
- )
-group by p_mfgr, p_name, p_size
-distribute by p_mfgr
-sort by p_name;
--- 14. testCountNoWindowing
+-- 4. testCount
select p_mfgr, p_name,
-rank() as r,
-dense_rank() as dr,
count(p_size) as cd
from part
distribute by p_mfgr
sort by p_name;
--- 15. testCountWithWindowing
+-- 5. testCountWithWindowingUDAF
select p_mfgr, p_name,
rank() as r,
dense_rank() as dr,
@@ -173,8 +63,7 @@ from part
distribute by p_mfgr
sort by p_name;
-
--- 16. testCountInSubQ
+-- 6. testCountInSubQ
select sub1.r, sub1.dr, sub1.cd, sub1.s1, sub1.deltaSz
from (select p_mfgr, p_name,
rank() as r,
@@ -187,21 +76,7 @@ distribute by p_mfgr
sort by p_name
) sub1;
--- 17. testJoin
-select abc.*
-from noop(on part
-partition by p_mfgr
-order by p_name
-) abc join part p1 on abc.p_partkey = p1.p_partkey;
-
--- 18. testJoinRight
-select abc.*
-from part p1 join noop(on part
-partition by p_mfgr
-order by p_name
-) abc on abc.p_partkey = p1.p_partkey;
-
--- 19. testJoinWithWindowing
+-- 7. testJoinWithWindowingAndPTF
select abc.p_mfgr, abc.p_name,
rank() as r,
dense_rank() as dr,
@@ -214,29 +89,13 @@ order by p_name
distribute by abc.p_mfgr
sort by abc.p_name ;
--- 20. testMixedCaseAlias
+-- 8. testMixedCaseAlias
select p_mfgr, p_name, p_size, rank() as R
from part
distribute by p_mfgr
sort by p_name, p_size desc;
--- 21. testNoopWithMap
-select p_mfgr, p_name, p_size,
-rank() as r over (partition by p_mfgr order by p_name, p_size desc)
-from noopwithmap(on part
-partition by p_mfgr
-order by p_name, p_size desc);
-
--- 22. testNoopWithMapWithWindowing
-select p_mfgr, p_name, p_size,
-rank() as r,
-dense_rank() as dr,
-sum(p_retailprice) as s1 over (partition by p_mfgr order by p_name rows between unbounded preceding and current row)
-from noopwithmap(on part
- partition by p_mfgr
- order by p_name);
-
--- 23. testHavingWithWindowingNoGBY
+-- 9. testHavingWithWindowingNoGBY
select p_mfgr, p_name, p_size,
rank() as r,
dense_rank() as dr,
@@ -246,7 +105,7 @@ having p_size > 5
distribute by p_mfgr
sort by p_name;
--- 24. testHavingWithWindowingCondRankNoGBY
+-- 10. testHavingWithWindowingCondRankNoGBY
select p_mfgr, p_name, p_size,
rank() as r,
dense_rank() as dr,
@@ -256,17 +115,7 @@ having rank() < 4
distribute by p_mfgr
sort by p_name;
--- 25. testHavingWithWindowingPTFNoGBY
-select p_mfgr, p_name, p_size,
-rank() as r,
-dense_rank() as dr,
-sum(p_retailprice) as s1 over (partition by p_mfgr order by p_name rows between unbounded preceding and current row)
-from noop(on part
-partition by p_mfgr
-order by p_name)
-having rank() < 4;
-
--- 26. testFirstLast
+-- 11. testFirstLast
select p_mfgr,p_name, p_size,
sum(p_size) as s2 over (rows between current row and current row),
first_value(p_size) as f over w1 ,
@@ -276,7 +125,7 @@ distribute by p_mfgr
sort by p_mfgr
window w1 as (rows between 2 preceding and 2 following);
--- 27. testFirstLastWithWhere
+-- 12. testFirstLastWithWhere
select p_mfgr,p_name, p_size,
rank() as r,
sum(p_size) as s2 over (rows between current row and current row),
@@ -288,15 +137,7 @@ distribute by p_mfgr
sort by p_mfgr
window w1 as (rows between 2 preceding and 2 following);
--- 28. testSumDelta
-select p_mfgr,p_name, p_size,
-sum(p_size - lag(p_size,1)) as deltaSum over w1
-from part
-distribute by p_mfgr
-sort by p_mfgr
-window w1 as (rows between 2 preceding and 2 following) ;
-
--- 29. testSumWindow
+-- 13. testSumWindow
select p_mfgr,p_name, p_size,
sum(p_size) as s1 over w1,
sum(p_size) as s2 over (rows between current row and current row)
@@ -305,14 +146,14 @@ distribute by p_mfgr
sort by p_mfgr
window w1 as (rows between 2 preceding and 2 following);
--- 30. testNoSortClause
+-- 14. testNoSortClause
select p_mfgr,p_name, p_size,
rank() as r, dense_rank() as dr
from part
distribute by p_mfgr
window w1 as (rows between 2 preceding and 2 following);
--- 31. testExpressions
+-- 15. testExpressions
select p_mfgr,p_name, p_size,
rank() as r,
dense_rank() as dr,
@@ -331,7 +172,7 @@ distribute by p_mfgr
sort by p_mfgr, p_name
window w1 as (rows between 2 preceding and 2 following);
--- 32. testMultipleWindows
+-- 16. testMultipleWindows
select p_mfgr,p_name, p_size,
rank() as r, dense_rank() as dr,
cume_dist() as cud,
@@ -344,30 +185,7 @@ distribute by p_mfgr
sort by p_mfgr, p_name
window w1 as (rows between 2 preceding and 2 following);
--- 33. testFunctionChain
-select p_mfgr, p_name, p_size,
-rank() as r,
-dense_rank() as dr,
-sum(p_retailprice) as s1 over (partition by p_mfgr order by p_name rows between unbounded preceding and current row)
-from noop(on noopwithmap(on noop(on part
-partition by p_mfgr
-order by p_mfgr, p_name
-)));
-
--- 34. testPTFAndWindowingInSubQ
-select p_mfgr, p_name,
-sub1.cd, sub1.s1
-from (select p_mfgr, p_name,
-count(p_size) as cd,
-p_retailprice,
-sum(p_retailprice) as s1 over w1
-from noop(on part
-partition by p_mfgr
-order by p_name)
-window w1 as (partition by p_mfgr order by p_name rows between 2 preceding and 2 following)
-) sub1 ;
-
--- 35. testCountStar
+-- 17. testCountStar
select p_mfgr,p_name, p_size,
count(*) as c,
count(p_size) as ca,
@@ -378,29 +196,7 @@ distribute by p_mfgr
sort by p_mfgr, p_name
window w1 as (rows between 2 preceding and 2 following);
--- 36. testJoinWithWindowingWithCount
-select abc.p_mfgr, abc.p_name,
-rank() as r,
-dense_rank() as dr,
-count(abc.p_name) as cd,
-abc.p_retailprice, sum(abc.p_retailprice) as s1 over (rows between unbounded preceding and current row),
-abc.p_size, abc.p_size - lag(abc.p_size,1) as deltaSz
-from noop(on part
-partition by p_mfgr
-order by p_name
-) abc join part p1 on abc.p_partkey = p1.p_partkey
-distribute by abc.p_mfgr
-sort by abc.p_name;
-
--- 37. testSumDelta
-select p_mfgr,p_name, p_size,
-sum(p_size - lag(p_size,1)) as deltaSum
-from part
-distribute by p_mfgr
-sort by p_mfgr
-window w1 as (rows between 2 preceding and 2 following) ;
-
--- 38. testUDAFs
+-- 18. testUDAFs
select p_mfgr,p_name, p_size,
sum(p_retailprice) as s over w1,
min(p_retailprice) as mi over w1,
@@ -411,7 +207,7 @@ distribute by p_mfgr
sort by p_mfgr, p_name
window w1 as (rows between 2 preceding and 2 following);
--- 39. testUDAFsWithPTFWithGBY
+-- 19. testUDAFsWithGBY
select p_mfgr,p_name, p_size, p_retailprice,
sum(p_retailprice) as s over w1,
min(p_retailprice) as mi ,
@@ -423,7 +219,7 @@ distribute by p_mfgr
sort by p_mfgr, p_name
window w1 as (rows between 2 preceding and 2 following);
--- 40. testSTATs
+-- 20. testSTATs
select p_mfgr,p_name, p_size,
stddev(p_retailprice) as sdev over w1,
stddev_pop(p_retailprice) as sdev_pop over w1,
@@ -436,7 +232,7 @@ distribute by p_mfgr
sort by p_mfgr, p_name
window w1 as (rows between 2 preceding and 2 following);
--- 41. testDISTs
+-- 21. testDISTs
select p_mfgr,p_name, p_size,
histogram_numeric(p_retailprice, 5) as hist over w1,
percentile(p_partkey, 0.5) as per over w1,
@@ -446,23 +242,7 @@ distribute by p_mfgr
sort by p_mfgr, p_name
window w1 as (rows between 2 preceding and 2 following);
--- 42. testDistinctInSelectWithPTF
-select DISTINCT p_mfgr, p_name, p_size
-from noop(on part
-partition by p_mfgr
-order by p_name);
-
--- 43. testUDAFsNoWindowingNoPTFNoGBY
-select p_mfgr,p_name, p_retailprice,
-sum(p_retailprice) as s,
-min(p_retailprice) as mi,
-max(p_retailprice) as ma,
-avg(p_retailprice) as av
-from part
-distribute by p_mfgr
-sort by p_mfgr, p_name;
-
--- 44. testViewAsTableInputWithWindowing
+-- 22. testViewAsTableInputWithWindowing
create view IF NOT EXISTS mfgr_price_view as
select p_mfgr, p_brand,
sum(p_retailprice) as s
@@ -476,15 +256,7 @@ distribute by p_mfgr
sort by p_mfgr
window w1 as (rows between 2 preceding and current row);
--- 45. testViewAsTableInputToPTF
-select p_mfgr, p_brand, s,
-sum(s) as s1 over w1
-from noop(on mfgr_price_view
-partition by p_mfgr
-order by p_mfgr)
-window w1 as ( partition by p_mfgr order by p_brand rows between 2 preceding and current row);
-
--- 46. testCreateViewWithWindowingQuery
+-- 23. testCreateViewWithWindowingQuery
create view IF NOT EXISTS mfgr_brand_price_view as
select p_mfgr, p_brand,
sum(p_retailprice) as s over w1
@@ -495,7 +267,7 @@ window w1 as (rows between 2 preceding a
select * from mfgr_brand_price_view;
--- 47. testLateralViews
+-- 24. testLateralViews
select p_mfgr, p_name,
lv_col, p_size, sum(p_size) as s over w1
from (select p_mfgr, p_name, p_size, array(1,2,3) arr from part) p
@@ -503,11 +275,8 @@ lateral view explode(arr) part_lv as lv_
distribute by p_mfgr
sort by p_name
window w1 as (rows between 2 preceding and current row);
-
--- 48. testConstExprInSelect
-select 'tst1' as key, count(1) as value from part;
--- 49. testMultipleInserts3SWQs
+-- 25. testMultipleInserts3SWQs
CREATE TABLE part_1(
p_mfgr STRING,
p_name STRING,
@@ -570,7 +339,7 @@ select * from part_2;
select * from part_3;
--- 50. testGroupByHavingWithSWQAndAlias
+-- 26. testGroupByHavingWithSWQAndAlias
select p_mfgr, p_name, p_size, min(p_retailprice) as mi,
rank() as r,
dense_rank() as dr,
@@ -581,7 +350,7 @@ having p_size > 0
distribute by p_mfgr
sort by p_name;
--- 51. testMultipleRangeWindows
+-- 27. testMultipleRangeWindows
select p_mfgr,p_name, p_size,
sum(p_size) as s2 over (range between p_size 10 less and current row),
sum(p_size) as s1 over (range between current row and p_size 10 more )
@@ -590,63 +359,18 @@ distribute by p_mfgr
sort by p_mfgr, p_size
window w1 as (rows between 2 preceding and 2 following);
--- 52. testMultipleInserts2SWQsWithPTF
-CREATE TABLE part_4(
-p_mfgr STRING,
-p_name STRING,
-p_size INT,
-r INT,
-dr INT,
-s DOUBLE);
-
-CREATE TABLE part_5(
-p_mfgr STRING,
-p_name STRING,
-p_size INT,
-s1 INT,
-s2 INT,
-r INT,
-dr INT,
-cud DOUBLE,
-fv1 INT);
-
-from noop(on part
-partition by p_mfgr
-order by p_name)
-INSERT OVERWRITE TABLE part_4 select p_mfgr, p_name, p_size,
-rank() as r,
-dense_rank() as dr,
-sum(p_retailprice) as s over (rows between unbounded preceding and current row)
-distribute by p_mfgr
-sort by p_name
-INSERT OVERWRITE TABLE part_5 select p_mfgr,p_name, p_size,
-sum(p_size) as s1 over (rows between unbounded preceding and current row),
-sum(p_size) as s2 over (range between p_size 5 less and current row),
-rank() as r,
-dense_rank() as dr,
-cume_dist() as cud,
-first_value(p_size, true) as fv1 over w1
-having p_size > 5
-distribute by p_mfgr
-sort by p_mfgr, p_name
-window w1 as (rows between 2 preceding and 2 following);
-
-select * from part_4;
-
-select * from part_5;
-
--- 53. testPartOrderInUDAFInvoke
+-- 28. testPartOrderInUDAFInvoke
select p_mfgr, p_name, p_size,
sum(p_size) as s over (partition by p_mfgr order by p_name rows between 2 preceding and 2 following)
from part;
--- 54. testPartOrderInWdwDef
+-- 29. testPartOrderInWdwDef
select p_mfgr, p_name, p_size,
sum(p_size) as s over w1
from part
window w1 as (partition by p_mfgr order by p_name rows between 2 preceding and 2 following);
--- 55. testDefaultPartitioningSpecRules
+-- 30. testDefaultPartitioningSpecRules
select p_mfgr, p_name, p_size,
sum(p_size) as s over w1,
sum(p_size) as s2 over w2
@@ -655,7 +379,7 @@ sort by p_name
window w1 as (partition by p_mfgr rows between 2 preceding and 2 following),
w2 as (partition by p_mfgr order by p_name);
--- 56. testWindowCrossReference
+-- 31. testWindowCrossReference
select p_mfgr, p_name, p_size,
sum(p_size) as s1 over w1,
sum(p_size) as s2 over w2
@@ -664,7 +388,7 @@ window w1 as (partition by p_mfgr order
w2 as w1;
--- 57. testWindowInheritance
+-- 32. testWindowInheritance
select p_mfgr, p_name, p_size,
sum(p_size) as s1 over w1,
sum(p_size) as s2 over w2
@@ -673,7 +397,7 @@ window w1 as (partition by p_mfgr order
w2 as (w1 rows between unbounded preceding and current row);
--- 58. testWindowForwardReference
+-- 33. testWindowForwardReference
select p_mfgr, p_name, p_size,
sum(p_size) as s1 over w1,
sum(p_size) as s2 over w2,
@@ -686,7 +410,7 @@ window w1 as (rows between 2 preceding a
w3 as (rows between unbounded preceding and current row);
--- 59. testWindowDefinitionPropagation
+-- 34. testWindowDefinitionPropagation
select p_mfgr, p_name, p_size,
sum(p_size) as s1 over w1,
sum(p_size) as s2 over w2,
@@ -698,7 +422,7 @@ window w1 as (rows between 2 preceding a
w2 as w3,
w3 as (rows between unbounded preceding and current row);
--- 60. testDistinctWithWindowing
+-- 35. testDistinctWithWindowing
select DISTINCT p_mfgr, p_name, p_size,
sum(p_size) as s over w1
from part
@@ -706,133 +430,55 @@ distribute by p_mfgr
sort by p_name
window w1 as (rows between 2 preceding and 2 following);
--- 61. testMulti2OperatorsFunctionChainWithMap
-select p_mfgr, p_name,
-rank() as r,
-dense_rank() as dr,
-p_size, sum(p_size) as s1 over (partition by p_mfgr,p_name rows between unbounded preceding and current row)
-from noop(on
- noopwithmap(on
- noop(on
- noop(on part
- partition by p_mfgr
- order by p_mfgr)
- )
- partition by p_mfgr,p_name
- order by p_mfgr,p_name)
- partition by p_mfgr,p_name
- order by p_mfgr,p_name) ;
-
--- 62. testMulti3OperatorsFunctionChain
-select p_mfgr, p_name,
-rank() as r,
-dense_rank() as dr,
-p_size, sum(p_size) as s1 over (partition by p_mfgr order by p_name rows between unbounded preceding and current row)
-from noop(on
- noop(on
- noop(on
- noop(on part
- partition by p_mfgr
- order by p_mfgr)
- )
- partition by p_mfgr,p_name
- order by p_mfgr,p_name)
- partition by p_mfgr
- order by p_mfgr ) ;
-
--- 63. testMultiOperatorChainWithNoWindowing
-select p_mfgr, p_name,
-rank() as r over (partition by p_mfgr order by p_name),
-dense_rank() as dr,
-p_size, sum(p_size) as s1
-from noop(on
- noop(on
- noop(on
- noop(on part
- partition by p_mfgr,p_name
- order by p_mfgr,p_name)
- )
- partition by p_mfgr
- order by p_mfgr));
-
-
--- 64. testMultiOperatorChainEndsWithNoopMap
-select p_mfgr, p_name,
-rank() as r,
-dense_rank() as dr,
-p_size, sum(p_size) as s1 over (partition by p_mfgr,p_name rows between unbounded preceding and current row)
-from noopwithmap(on
- noop(on
- noop(on
- noop(on part
- partition by p_mfgr,p_name
- order by p_mfgr,p_name)
- )
- partition by p_mfgr
- order by p_mfgr)
- partition by p_mfgr,p_name
- order by p_mfgr,p_name);
-
---65. testMultiOperatorChainWithDiffPartitionForWindow1
-select p_mfgr, p_name,
-rank() as r,
-dense_rank() as dr,
-p_size,
-sum(p_size) as s1 over (rows between unbounded preceding and current row),
-sum(p_size) as s2 over (partition by p_mfgr,p_name order by p_mfgr,p_name rows between unbounded preceding and current row)
-from noop(on
- noopwithmap(on
- noop(on part
- partition by p_mfgr, p_name
- order by p_mfgr, p_name)
- partition by p_mfgr
- order by p_mfgr
- ));
-
---66. testMultiOperatorChainWithDiffPartitionForWindow2
-select p_mfgr, p_name,
-rank() as r,
-dense_rank() as dr,
-p_size,
-sum(p_size) as s1 over (rows between unbounded preceding and current row),
-sum(p_size) as s2 over (partition by p_mfgr order by p_mfgr rows between unbounded preceding and current row)
-from noopwithmap(on
- noop(on
- noop(on part
- partition by p_mfgr, p_name
- order by p_mfgr, p_name)
- ));
-
--- 67. basic Npath test
-select origin_city_name, fl_num, year, month, day_of_month, sz, tpath
-from npath(on
- flights_tiny
- distribute by fl_num
- sort by year, month, day_of_month
- arg1('LATE.LATE+'),
- arg2('LATE'), arg3(arr_delay > 15),
- arg4('origin_city_name, fl_num, year, month, day_of_month, size(tpath) as sz, tpath as tpath')
- );
-
--- 68. testRankWithPartitioning
+-- 36. testRankWithPartitioning
select p_mfgr, p_name, p_size,
rank() as r over (partition by p_mfgr order by p_name )
from part;
--- 69. testPartitioningVariousForms
+-- 37. testPartitioningVariousForms
select p_mfgr, p_name, p_size,
sum(p_retailprice) as s1 over (partition by p_mfgr order by p_mfgr),
min(p_retailprice) as s2 over (partition by p_mfgr),
max(p_retailprice) as s3 over (distribute by p_mfgr sort by p_mfgr),
avg(p_retailprice) as s4 over (distribute by p_mfgr),
count(p_retailprice) as s5 over (cluster by p_mfgr )
-from part
-;
+from part;
--- 70. testPartitioningVariousForms2
+-- 38. testPartitioningVariousForms2
select p_mfgr, p_name, p_size,
sum(p_retailprice) as s1 over (partition by p_mfgr, p_name order by p_mfgr, p_name rows between unbounded preceding and current row),
min(p_retailprice) as s2 over (distribute by p_mfgr, p_name sort by p_mfgr, p_name rows between unbounded preceding and current row),
max(p_retailprice) as s3 over (cluster by p_mfgr, p_name )
-from part
-;
+from part;
+
+-- 39. testUDFOnOrderCols
+select p_mfgr, p_type, substr(p_type, 2) as short_ptype,
+rank() as r over (partition by p_mfgr order by substr(p_type, 2))
+from part;
+
+-- 40. testNoBetweenForRows
+select p_mfgr, p_name, p_size,
+ sum(p_retailprice) as s1 over (rows unbounded preceding)
+ from part distribute by p_mfgr sort by p_name;
+
+-- 41. testNoBetweenForRange
+select p_mfgr, p_name, p_size,
+ sum(p_retailprice) as s1 over (range unbounded preceding)
+ from part distribute by p_mfgr sort by p_name;
+
+-- 42. testUnboundedFollowingForRows
+select p_mfgr, p_name, p_size,
+ sum(p_retailprice) as s1 over (rows between current row and unbounded following)
+ from part distribute by p_mfgr sort by p_name;
+
+-- 43. testUnboundedFollowingForRange
+select p_mfgr, p_name, p_size,
+ sum(p_retailprice) as s1 over (range between current row and unbounded following)
+ from part distribute by p_mfgr sort by p_name;
+
+-- 44. testOverNoPartitionSingleAggregate
+select p_name, p_retailprice,
+avg(p_retailprice) over()
+from part
+order by p_name;
+
\ No newline at end of file
Added: hive/branches/ptf-windowing/ql/src/test/results/clientpositive/leadlag.q.out
URL: http://svn.apache.org/viewvc/hive/branches/ptf-windowing/ql/src/test/results/clientpositive/leadlag.q.out?rev=1452174&view=auto
==============================================================================
--- hive/branches/ptf-windowing/ql/src/test/results/clientpositive/leadlag.q.out (added)
+++ hive/branches/ptf-windowing/ql/src/test/results/clientpositive/leadlag.q.out Mon Mar 4 03:01:06 2013
@@ -0,0 +1,421 @@
+PREHOOK: query: DROP TABLE part
+PREHOOK: type: DROPTABLE
+POSTHOOK: query: DROP TABLE part
+POSTHOOK: type: DROPTABLE
+PREHOOK: query: -- data setup
+CREATE TABLE part(
+ p_partkey INT,
+ p_name STRING,
+ p_mfgr STRING,
+ p_brand STRING,
+ p_type STRING,
+ p_size INT,
+ p_container STRING,
+ p_retailprice DOUBLE,
+ p_comment STRING
+)
+PREHOOK: type: CREATETABLE
+POSTHOOK: query: -- data setup
+CREATE TABLE part(
+ p_partkey INT,
+ p_name STRING,
+ p_mfgr STRING,
+ p_brand STRING,
+ p_type STRING,
+ p_size INT,
+ p_container STRING,
+ p_retailprice DOUBLE,
+ p_comment STRING
+)
+POSTHOOK: type: CREATETABLE
+POSTHOOK: Output: default@part
+PREHOOK: query: LOAD DATA LOCAL INPATH '../data/files/part_tiny.txt' overwrite into table part
+PREHOOK: type: LOAD
+PREHOOK: Output: default@part
+POSTHOOK: query: LOAD DATA LOCAL INPATH '../data/files/part_tiny.txt' overwrite into table part
+POSTHOOK: type: LOAD
+POSTHOOK: Output: default@part
+PREHOOK: query: --1. testLagWithPTFWindowing
+select p_mfgr, p_name,
+rank() as r,
+dense_rank() as dr,
+p_retailprice, sum(p_retailprice) as s1 over (partition by p_mfgr order by p_name rows between unbounded preceding and current row),
+p_size, p_size - lag(p_size,1) as deltaSz
+from noop(on part
+partition by p_mfgr
+order by p_name
+)
+PREHOOK: type: QUERY
+PREHOOK: Input: default@part
+#### A masked pattern was here ####
+POSTHOOK: query: --1. testLagWithPTFWindowing
+select p_mfgr, p_name,
+rank() as r,
+dense_rank() as dr,
+p_retailprice, sum(p_retailprice) as s1 over (partition by p_mfgr order by p_name rows between unbounded preceding and current row),
+p_size, p_size - lag(p_size,1) as deltaSz
+from noop(on part
+partition by p_mfgr
+order by p_name
+)
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@part
+#### A masked pattern was here ####
+Manufacturer#1 almond antique burnished rose metallic 1 1 1173.15 1173.15 2 0
+Manufacturer#1 almond antique burnished rose metallic 1 1 1173.15 2346.3 2 0
+Manufacturer#1 almond antique chartreuse lavender yellow 3 2 1753.76 4100.06 34 32
+Manufacturer#1 almond antique salmon chartreuse burlywood 4 3 1602.59 5702.650000000001 6 -28
+Manufacturer#1 almond aquamarine burnished black steel 5 4 1414.42 7117.070000000001 28 22
+Manufacturer#1 almond aquamarine pink moccasin thistle 6 5 1632.66 8749.730000000001 42 14
+Manufacturer#2 almond antique violet chocolate turquoise 1 1 1690.68 1690.68 14 0
+Manufacturer#2 almond antique violet turquoise frosted 2 2 1800.7 3491.38 40 26
+Manufacturer#2 almond aquamarine midnight light salmon 3 3 2031.98 5523.360000000001 2 -38
+Manufacturer#2 almond aquamarine rose maroon antique 4 4 1698.66 7222.02 25 23
+Manufacturer#2 almond aquamarine sandy cyan gainsboro 5 5 1701.6 8923.62 18 -7
+Manufacturer#3 almond antique chartreuse khaki white 1 1 1671.68 1671.68 17 0
+Manufacturer#3 almond antique forest lavender goldenrod 2 2 1190.27 2861.95 14 -3
+Manufacturer#3 almond antique metallic orange dim 3 3 1410.39 4272.34 19 5
+Manufacturer#3 almond antique misty red olive 4 4 1922.98 6195.32 1 -18
+Manufacturer#3 almond antique olive coral navajo 5 5 1337.29 7532.61 45 44
+Manufacturer#4 almond antique gainsboro frosted violet 1 1 1620.67 1620.67 10 0
+Manufacturer#4 almond antique violet mint lemon 2 2 1375.42 2996.09 39 29
+Manufacturer#4 almond aquamarine floral ivory bisque 3 3 1206.26 4202.35 27 -12
+Manufacturer#4 almond aquamarine yellow dodger mint 4 4 1844.92 6047.27 7 -20
+Manufacturer#4 almond azure aquamarine papaya violet 5 5 1290.35 7337.620000000001 12 5
+Manufacturer#5 almond antique blue firebrick mint 1 1 1789.69 1789.69 31 0
+Manufacturer#5 almond antique medium spring khaki 2 2 1611.66 3401.3500000000004 6 -25
+Manufacturer#5 almond antique sky peru orange 3 3 1788.73 5190.08 2 -4
+Manufacturer#5 almond aquamarine dodger light gainsboro 4 4 1018.1 6208.18 46 44
+Manufacturer#5 almond azure blanched chiffon midnight 5 5 1464.48 7672.66 23 -23
+PREHOOK: query: -- 2. testLagWithWindowingNoPTF
+select p_mfgr, p_name,
+rank() as r,
+dense_rank() as dr,
+p_retailprice, sum(p_retailprice) as s1 over (rows between unbounded preceding and current row),
+p_size, p_size - lag(p_size,1) as deltaSz
+from part
+distribute by p_mfgr
+sort by p_name
+PREHOOK: type: QUERY
+PREHOOK: Input: default@part
+#### A masked pattern was here ####
+POSTHOOK: query: -- 2. testLagWithWindowingNoPTF
+select p_mfgr, p_name,
+rank() as r,
+dense_rank() as dr,
+p_retailprice, sum(p_retailprice) as s1 over (rows between unbounded preceding and current row),
+p_size, p_size - lag(p_size,1) as deltaSz
+from part
+distribute by p_mfgr
+sort by p_name
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@part
+#### A masked pattern was here ####
+Manufacturer#1 almond antique burnished rose metallic 1 1 1173.15 1173.15 2 0
+Manufacturer#1 almond antique burnished rose metallic 1 1 1173.15 2346.3 2 0
+Manufacturer#1 almond antique chartreuse lavender yellow 3 2 1753.76 4100.06 34 32
+Manufacturer#1 almond antique salmon chartreuse burlywood 4 3 1602.59 5702.650000000001 6 -28
+Manufacturer#1 almond aquamarine burnished black steel 5 4 1414.42 7117.070000000001 28 22
+Manufacturer#1 almond aquamarine pink moccasin thistle 6 5 1632.66 8749.730000000001 42 14
+Manufacturer#2 almond antique violet chocolate turquoise 1 1 1690.68 1690.68 14 0
+Manufacturer#2 almond antique violet turquoise frosted 2 2 1800.7 3491.38 40 26
+Manufacturer#2 almond aquamarine midnight light salmon 3 3 2031.98 5523.360000000001 2 -38
+Manufacturer#2 almond aquamarine rose maroon antique 4 4 1698.66 7222.02 25 23
+Manufacturer#2 almond aquamarine sandy cyan gainsboro 5 5 1701.6 8923.62 18 -7
+Manufacturer#3 almond antique chartreuse khaki white 1 1 1671.68 1671.68 17 0
+Manufacturer#3 almond antique forest lavender goldenrod 2 2 1190.27 2861.95 14 -3
+Manufacturer#3 almond antique metallic orange dim 3 3 1410.39 4272.34 19 5
+Manufacturer#3 almond antique misty red olive 4 4 1922.98 6195.32 1 -18
+Manufacturer#3 almond antique olive coral navajo 5 5 1337.29 7532.61 45 44
+Manufacturer#4 almond antique gainsboro frosted violet 1 1 1620.67 1620.67 10 0
+Manufacturer#4 almond antique violet mint lemon 2 2 1375.42 2996.09 39 29
+Manufacturer#4 almond aquamarine floral ivory bisque 3 3 1206.26 4202.35 27 -12
+Manufacturer#4 almond aquamarine yellow dodger mint 4 4 1844.92 6047.27 7 -20
+Manufacturer#4 almond azure aquamarine papaya violet 5 5 1290.35 7337.620000000001 12 5
+Manufacturer#5 almond antique blue firebrick mint 1 1 1789.69 1789.69 31 0
+Manufacturer#5 almond antique medium spring khaki 2 2 1611.66 3401.3500000000004 6 -25
+Manufacturer#5 almond antique sky peru orange 3 3 1788.73 5190.08 2 -4
+Manufacturer#5 almond aquamarine dodger light gainsboro 4 4 1018.1 6208.18 46 44
+Manufacturer#5 almond azure blanched chiffon midnight 5 5 1464.48 7672.66 23 -23
+PREHOOK: query: -- 3. testJoinWithLag
+select p1.p_mfgr, p1.p_name,
+p1.p_size, p1.p_size - lag(p1.p_size,1) as deltaSz
+from part p1 join part p2 on p1.p_partkey = p2.p_partkey
+distribute by p1.p_mfgr
+sort by p1.p_name
+PREHOOK: type: QUERY
+PREHOOK: Input: default@part
+#### A masked pattern was here ####
+POSTHOOK: query: -- 3. testJoinWithLag
+select p1.p_mfgr, p1.p_name,
+p1.p_size, p1.p_size - lag(p1.p_size,1) as deltaSz
+from part p1 join part p2 on p1.p_partkey = p2.p_partkey
+distribute by p1.p_mfgr
+sort by p1.p_name
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@part
+#### A masked pattern was here ####
+Manufacturer#1 almond antique burnished rose metallic 2 0
+Manufacturer#1 almond antique burnished rose metallic 2 0
+Manufacturer#1 almond antique burnished rose metallic 2 0
+Manufacturer#1 almond antique burnished rose metallic 2 0
+Manufacturer#1 almond antique chartreuse lavender yellow 34 32
+Manufacturer#1 almond antique salmon chartreuse burlywood 6 -28
+Manufacturer#1 almond aquamarine burnished black steel 28 22
+Manufacturer#1 almond aquamarine pink moccasin thistle 42 14
+Manufacturer#2 almond antique violet chocolate turquoise 14 0
+Manufacturer#2 almond antique violet turquoise frosted 40 26
+Manufacturer#2 almond aquamarine midnight light salmon 2 -38
+Manufacturer#2 almond aquamarine rose maroon antique 25 23
+Manufacturer#2 almond aquamarine sandy cyan gainsboro 18 -7
+Manufacturer#3 almond antique chartreuse khaki white 17 0
+Manufacturer#3 almond antique forest lavender goldenrod 14 -3
+Manufacturer#3 almond antique metallic orange dim 19 5
+Manufacturer#3 almond antique misty red olive 1 -18
+Manufacturer#3 almond antique olive coral navajo 45 44
+Manufacturer#4 almond antique gainsboro frosted violet 10 0
+Manufacturer#4 almond antique violet mint lemon 39 29
+Manufacturer#4 almond aquamarine floral ivory bisque 27 -12
+Manufacturer#4 almond aquamarine yellow dodger mint 7 -20
+Manufacturer#4 almond azure aquamarine papaya violet 12 5
+Manufacturer#5 almond antique blue firebrick mint 31 0
+Manufacturer#5 almond antique medium spring khaki 6 -25
+Manufacturer#5 almond antique sky peru orange 2 -4
+Manufacturer#5 almond aquamarine dodger light gainsboro 46 44
+Manufacturer#5 almond azure blanched chiffon midnight 23 -23
+PREHOOK: query: -- 4. testLagInSum
+select p_mfgr,p_name, p_size,
+sum(p_size - lag(p_size,1)) as deltaSum
+from part
+distribute by p_mfgr
+sort by p_mfgr
+window w1 as (rows between 2 preceding and 2 following)
+PREHOOK: type: QUERY
+PREHOOK: Input: default@part
+#### A masked pattern was here ####
+POSTHOOK: query: -- 4. testLagInSum
+select p_mfgr,p_name, p_size,
+sum(p_size - lag(p_size,1)) as deltaSum
+from part
+distribute by p_mfgr
+sort by p_mfgr
+window w1 as (rows between 2 preceding and 2 following)
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@part
+#### A masked pattern was here ####
+Manufacturer#1 almond antique burnished rose metallic 2 40
+Manufacturer#1 almond antique chartreuse lavender yellow 34 40
+Manufacturer#1 almond antique burnished rose metallic 2 40
+Manufacturer#1 almond antique salmon chartreuse burlywood 6 40
+Manufacturer#1 almond aquamarine burnished black steel 28 40
+Manufacturer#1 almond aquamarine pink moccasin thistle 42 40
+Manufacturer#2 almond antique violet chocolate turquoise 14 4
+Manufacturer#2 almond antique violet turquoise frosted 40 4
+Manufacturer#2 almond aquamarine midnight light salmon 2 4
+Manufacturer#2 almond aquamarine rose maroon antique 25 4
+Manufacturer#2 almond aquamarine sandy cyan gainsboro 18 4
+Manufacturer#3 almond antique metallic orange dim 19 26
+Manufacturer#3 almond antique chartreuse khaki white 17 26
+Manufacturer#3 almond antique forest lavender goldenrod 14 26
+Manufacturer#3 almond antique misty red olive 1 26
+Manufacturer#3 almond antique olive coral navajo 45 26
+Manufacturer#4 almond antique gainsboro frosted violet 10 2
+Manufacturer#4 almond antique violet mint lemon 39 2
+Manufacturer#4 almond aquamarine floral ivory bisque 27 2
+Manufacturer#4 almond aquamarine yellow dodger mint 7 2
+Manufacturer#4 almond azure aquamarine papaya violet 12 2
+Manufacturer#5 almond antique blue firebrick mint 31 -8
+Manufacturer#5 almond antique medium spring khaki 6 -8
+Manufacturer#5 almond antique sky peru orange 2 -8
+Manufacturer#5 almond aquamarine dodger light gainsboro 46 -8
+Manufacturer#5 almond azure blanched chiffon midnight 23 -8
+PREHOOK: query: -- 5. testLagInSumOverWindow
+select p_mfgr,p_name, p_size,
+sum(p_size - lag(p_size,1)) as deltaSum over w1
+from part
+distribute by p_mfgr
+sort by p_mfgr
+window w1 as (rows between 2 preceding and 2 following)
+PREHOOK: type: QUERY
+PREHOOK: Input: default@part
+#### A masked pattern was here ####
+POSTHOOK: query: -- 5. testLagInSumOverWindow
+select p_mfgr,p_name, p_size,
+sum(p_size - lag(p_size,1)) as deltaSum over w1
+from part
+distribute by p_mfgr
+sort by p_mfgr
+window w1 as (rows between 2 preceding and 2 following)
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@part
+#### A masked pattern was here ####
+Manufacturer#1 almond antique burnished rose metallic 2 0
+Manufacturer#1 almond antique chartreuse lavender yellow 34 4
+Manufacturer#1 almond antique burnished rose metallic 2 26
+Manufacturer#1 almond antique salmon chartreuse burlywood 6 8
+Manufacturer#1 almond aquamarine burnished black steel 28 40
+Manufacturer#1 almond aquamarine pink moccasin thistle 42 36
+Manufacturer#2 almond antique violet chocolate turquoise 14 -12
+Manufacturer#2 almond antique violet turquoise frosted 40 11
+Manufacturer#2 almond aquamarine midnight light salmon 2 4
+Manufacturer#2 almond aquamarine rose maroon antique 25 -22
+Manufacturer#2 almond aquamarine sandy cyan gainsboro 18 16
+Manufacturer#3 almond antique metallic orange dim 19 -5
+Manufacturer#3 almond antique chartreuse khaki white 17 -18
+Manufacturer#3 almond antique forest lavender goldenrod 14 26
+Manufacturer#3 almond antique misty red olive 1 28
+Manufacturer#3 almond antique olive coral navajo 45 31
+Manufacturer#4 almond antique gainsboro frosted violet 10 17
+Manufacturer#4 almond antique violet mint lemon 39 -3
+Manufacturer#4 almond aquamarine floral ivory bisque 27 2
+Manufacturer#4 almond aquamarine yellow dodger mint 7 -27
+Manufacturer#4 almond azure aquamarine papaya violet 12 -15
+Manufacturer#5 almond antique blue firebrick mint 31 -29
+Manufacturer#5 almond antique medium spring khaki 6 15
+Manufacturer#5 almond antique sky peru orange 2 -8
+Manufacturer#5 almond aquamarine dodger light gainsboro 46 17
+Manufacturer#5 almond azure blanched chiffon midnight 23 21
+PREHOOK: query: -- 6. testRankInLead
+select p_mfgr, p_name, p_size,
+rank() as r1,
+lead(rank(), 1) as deltaRank
+from part
+distribute by p_mfgr
+sort by p_name
+PREHOOK: type: QUERY
+PREHOOK: Input: default@part
+#### A masked pattern was here ####
+POSTHOOK: query: -- 6. testRankInLead
+select p_mfgr, p_name, p_size,
+rank() as r1,
+lead(rank(), 1) as deltaRank
+from part
+distribute by p_mfgr
+sort by p_name
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@part
+#### A masked pattern was here ####
+Manufacturer#1 almond antique burnished rose metallic 2 1 1
+Manufacturer#1 almond antique burnished rose metallic 2 1 3
+Manufacturer#1 almond antique chartreuse lavender yellow 34 3 4
+Manufacturer#1 almond antique salmon chartreuse burlywood 6 4 5
+Manufacturer#1 almond aquamarine burnished black steel 28 5 6
+Manufacturer#1 almond aquamarine pink moccasin thistle 42 6 6
+Manufacturer#2 almond antique violet chocolate turquoise 14 1 2
+Manufacturer#2 almond antique violet turquoise frosted 40 2 3
+Manufacturer#2 almond aquamarine midnight light salmon 2 3 4
+Manufacturer#2 almond aquamarine rose maroon antique 25 4 5
+Manufacturer#2 almond aquamarine sandy cyan gainsboro 18 5 5
+Manufacturer#3 almond antique chartreuse khaki white 17 1 2
+Manufacturer#3 almond antique forest lavender goldenrod 14 2 3
+Manufacturer#3 almond antique metallic orange dim 19 3 4
+Manufacturer#3 almond antique misty red olive 1 4 5
+Manufacturer#3 almond antique olive coral navajo 45 5 5
+Manufacturer#4 almond antique gainsboro frosted violet 10 1 2
+Manufacturer#4 almond antique violet mint lemon 39 2 3
+Manufacturer#4 almond aquamarine floral ivory bisque 27 3 4
+Manufacturer#4 almond aquamarine yellow dodger mint 7 4 5
+Manufacturer#4 almond azure aquamarine papaya violet 12 5 5
+Manufacturer#5 almond antique blue firebrick mint 31 1 2
+Manufacturer#5 almond antique medium spring khaki 6 2 3
+Manufacturer#5 almond antique sky peru orange 2 3 4
+Manufacturer#5 almond aquamarine dodger light gainsboro 46 4 5
+Manufacturer#5 almond azure blanched chiffon midnight 23 5 5
+PREHOOK: query: -- 7. testLeadWithPTF
+select p_mfgr, p_name,
+rank() as r,
+dense_rank() as dr,
+p_size, p_size - lead(p_size,1) as deltaSz
+from noop(on part
+partition by p_mfgr
+order by p_name
+)
+distribute by p_mfgr
+sort by p_name
+PREHOOK: type: QUERY
+PREHOOK: Input: default@part
+#### A masked pattern was here ####
+POSTHOOK: query: -- 7. testLeadWithPTF
+select p_mfgr, p_name,
+rank() as r,
+dense_rank() as dr,
+p_size, p_size - lead(p_size,1) as deltaSz
+from noop(on part
+partition by p_mfgr
+order by p_name
+)
+distribute by p_mfgr
+sort by p_name
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@part
+#### A masked pattern was here ####
+Manufacturer#1 almond antique burnished rose metallic 1 1 2 0
+Manufacturer#1 almond antique burnished rose metallic 1 1 2 -32
+Manufacturer#1 almond antique chartreuse lavender yellow 3 2 34 28
+Manufacturer#1 almond antique salmon chartreuse burlywood 4 3 6 -22
+Manufacturer#1 almond aquamarine burnished black steel 5 4 28 -14
+Manufacturer#1 almond aquamarine pink moccasin thistle 6 5 42 0
+Manufacturer#2 almond antique violet chocolate turquoise 1 1 14 -26
+Manufacturer#2 almond antique violet turquoise frosted 2 2 40 38
+Manufacturer#2 almond aquamarine midnight light salmon 3 3 2 -23
+Manufacturer#2 almond aquamarine rose maroon antique 4 4 25 7
+Manufacturer#2 almond aquamarine sandy cyan gainsboro 5 5 18 0
+Manufacturer#3 almond antique chartreuse khaki white 1 1 17 3
+Manufacturer#3 almond antique forest lavender goldenrod 2 2 14 -5
+Manufacturer#3 almond antique metallic orange dim 3 3 19 18
+Manufacturer#3 almond antique misty red olive 4 4 1 -44
+Manufacturer#3 almond antique olive coral navajo 5 5 45 0
+Manufacturer#4 almond antique gainsboro frosted violet 1 1 10 -29
+Manufacturer#4 almond antique violet mint lemon 2 2 39 12
+Manufacturer#4 almond aquamarine floral ivory bisque 3 3 27 20
+Manufacturer#4 almond aquamarine yellow dodger mint 4 4 7 -5
+Manufacturer#4 almond azure aquamarine papaya violet 5 5 12 0
+Manufacturer#5 almond antique blue firebrick mint 1 1 31 25
+Manufacturer#5 almond antique medium spring khaki 2 2 6 4
+Manufacturer#5 almond antique sky peru orange 3 3 2 -44
+Manufacturer#5 almond aquamarine dodger light gainsboro 4 4 46 23
+Manufacturer#5 almond azure blanched chiffon midnight 5 5 23 0
+PREHOOK: query: -- 8. testOverNoPartitionMultipleAggregate
+select p_name, p_retailprice,
+lead(p_retailprice) as l1 over(),
+lag(p_retailprice) as l2 over()
+from part
+order by p_name
+PREHOOK: type: QUERY
+PREHOOK: Input: default@part
+#### A masked pattern was here ####
+POSTHOOK: query: -- 8. testOverNoPartitionMultipleAggregate
+select p_name, p_retailprice,
+lead(p_retailprice) as l1 over(),
+lag(p_retailprice) as l2 over()
+from part
+order by p_name
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@part
+#### A masked pattern was here ####
+almond antique blue firebrick mint 1789.69 1611.66 1290.35
+almond antique burnished rose metallic 1173.15 1753.76 1173.15
+almond antique burnished rose metallic 1173.15 1173.15 NULL
+almond antique chartreuse khaki white 1671.68 1190.27 1701.6
+almond antique chartreuse lavender yellow 1753.76 1602.59 1173.15
+almond antique forest lavender goldenrod 1190.27 1410.39 1671.68
+almond antique gainsboro frosted violet 1620.67 1375.42 1337.29
+almond antique medium spring khaki 1611.66 1788.73 1789.69
+almond antique metallic orange dim 1410.39 1922.98 1190.27
+almond antique misty red olive 1922.98 1337.29 1410.39
+almond antique olive coral navajo 1337.29 1620.67 1922.98
+almond antique salmon chartreuse burlywood 1602.59 1414.42 1753.76
+almond antique sky peru orange 1788.73 1018.1 1611.66
+almond antique violet chocolate turquoise 1690.68 1800.7 1632.66
+almond antique violet mint lemon 1375.42 1206.26 1620.67
+almond antique violet turquoise frosted 1800.7 2031.98 1690.68
+almond aquamarine burnished black steel 1414.42 1632.66 1602.59
+almond aquamarine dodger light gainsboro 1018.1 1464.48 1788.73
+almond aquamarine floral ivory bisque 1206.26 1844.92 1375.42
+almond aquamarine midnight light salmon 2031.98 1698.66 1800.7
+almond aquamarine pink moccasin thistle 1632.66 1690.68 1414.42
+almond aquamarine rose maroon antique 1698.66 1701.6 2031.98
+almond aquamarine sandy cyan gainsboro 1701.6 1671.68 1698.66
+almond aquamarine yellow dodger mint 1844.92 1290.35 1206.26
+almond azure aquamarine papaya violet 1290.35 1789.69 1844.92
+almond azure blanched chiffon midnight 1464.48 NULL 1018.1