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/08 20:26:39 UTC
svn commit: r1454504 - in /hive/branches/ptf-windowing:
common/src/java/org/apache/hadoop/hive/conf/
ql/src/java/org/apache/hadoop/hive/ql/parse/
ql/src/test/queries/clientpositive/ ql/src/test/results/clientpositive/
Author: hashutosh
Date: Fri Mar 8 19:26:38 2013
New Revision: 1454504
URL: http://svn.apache.org/r1454504
Log:
HIVE-4126 [jira] remove support for lead/lag UDFs outside of UDAF args
(Harish Butani via Ashutosh Chauhan)
Summary:
remove support for lead/lag UDFs outside of UDAF args
Select Expressions such as
p_size - lead(p_size,1)
are currently handled as non aggregation expressions done after all over clauses are evaluated.
Once we allow different partitions in a single select list(Jira 4041), these become ambiguous.
the equivalent way to do such things is either to use lead/lag UDAFs with expressions ( support added with Jira 4081)
stack windowing clauses with inline queries. select lead(r,1).. from (select rank() as r....)...
Test Plan: existing tests
Reviewers: JIRA, ashutoshc
Reviewed By: ashutoshc
Differential Revision: https://reviews.facebook.net/D9105
Modified:
hive/branches/ptf-windowing/common/src/java/org/apache/hadoop/hive/conf/HiveConf.java
hive/branches/ptf-windowing/ql/src/java/org/apache/hadoop/hive/ql/parse/SemanticAnalyzer.java
hive/branches/ptf-windowing/ql/src/test/queries/clientpositive/leadlag.q
hive/branches/ptf-windowing/ql/src/test/queries/clientpositive/leadlag_queries.q
hive/branches/ptf-windowing/ql/src/test/queries/clientpositive/ptf.q
hive/branches/ptf-windowing/ql/src/test/queries/clientpositive/windowing.q
hive/branches/ptf-windowing/ql/src/test/queries/clientpositive/windowing_expressions.q
hive/branches/ptf-windowing/ql/src/test/results/clientpositive/leadlag.q.out
hive/branches/ptf-windowing/ql/src/test/results/clientpositive/leadlag_queries.q.out
hive/branches/ptf-windowing/ql/src/test/results/clientpositive/ptf.q.out
hive/branches/ptf-windowing/ql/src/test/results/clientpositive/windowing.q.out
hive/branches/ptf-windowing/ql/src/test/results/clientpositive/windowing_expressions.q.out
Modified: hive/branches/ptf-windowing/common/src/java/org/apache/hadoop/hive/conf/HiveConf.java
URL: http://svn.apache.org/viewvc/hive/branches/ptf-windowing/common/src/java/org/apache/hadoop/hive/conf/HiveConf.java?rev=1454504&r1=1454503&r2=1454504&view=diff
==============================================================================
--- hive/branches/ptf-windowing/common/src/java/org/apache/hadoop/hive/conf/HiveConf.java (original)
+++ hive/branches/ptf-windowing/common/src/java/org/apache/hadoop/hive/conf/HiveConf.java Fri Mar 8 19:26:38 2013
@@ -728,8 +728,9 @@ public class HiveConf extends Configurat
// ptf partition constants
HIVE_PTF_PARTITION_PERSISTENCE_CLASS("hive.ptf.partition.persistence",
- "org.apache.hadoop.hive.ql.exec.PTFPersistence$PartitionedByteBasedList"),
- HIVE_PTF_PARTITION_PERSISTENT_SIZE("hive.ptf.partition.persistence.memsize", (int) Math.pow(2, (6 + 10 + 10)) ), // 64MB
+ "org.apache.hadoop.hive.ql.exec.PTFPersistence$PartitionedByteBasedList"),
+ HIVE_PTF_PARTITION_PERSISTENT_SIZE("hive.ptf.partition.persistence.memsize",
+ (int) Math.pow(2, (5 + 10 + 10)) ), // 32MB
;
public final String varname;
Modified: hive/branches/ptf-windowing/ql/src/java/org/apache/hadoop/hive/ql/parse/SemanticAnalyzer.java
URL: http://svn.apache.org/viewvc/hive/branches/ptf-windowing/ql/src/java/org/apache/hadoop/hive/ql/parse/SemanticAnalyzer.java?rev=1454504&r1=1454503&r2=1454504&view=diff
==============================================================================
--- hive/branches/ptf-windowing/ql/src/java/org/apache/hadoop/hive/ql/parse/SemanticAnalyzer.java (original)
+++ hive/branches/ptf-windowing/ql/src/java/org/apache/hadoop/hive/ql/parse/SemanticAnalyzer.java Fri Mar 8 19:26:38 2013
@@ -9942,25 +9942,6 @@ public class SemanticAnalyzer extends Ba
return false;
}
- /*
- * If there are only Lead and Lags, for now treat them as a Window Expression
- * until we remove support for Wdw Expressions.
- */
- if (!hasWindowSpec ) {
- boolean onlyLL = true;
- for(ASTNode function : functions) {
- String fnName = function.getChild(0).getText().toLowerCase();
- if ( !FunctionRegistry.LAG_FUNC_NAME.equals(fnName) &&
- !FunctionRegistry.LEAD_FUNC_NAME.equals(fnName) ) {
- onlyLL = false;
- break;
- }
- }
- if (onlyLL ) {
- return false;
- }
- }
-
WindowingSpec spec = qb.getWindowingSpec(dest);
if(spec == null) {
queryProperties.setHasWindowing(true);
@@ -10133,32 +10114,11 @@ public class SemanticAnalyzer extends Ba
* - Select tree form is: ^(TOK_SELECT ^(TOK_SELECTEXPR...) ^(TOK_SELECTEXPR...) ...)
* - A Select Item form is: ^(TOK_SELEXPR selectExpression Identifier* window_specification?)
*
- * We need to extract the SelectList any SelectItems that must be handled during
- * Windowing processing. These are:
- * - SelectItems that have a window_specification
- * - SelectItems that invoke row navigation functions: Lead/Lag.
- *
- * Do we need to change the SelectList in any way?
- * - initially we thought of replacing the selectExpressions handled by Windowing
- * with a ASTNode that is of type Identfier and
- * references the alias to the orginal expression. Why?
- * - the output of processing the PTF Operator that handles windowing will
- * contain the values of the Windowing expressions.
- * - the final Select Op that is a child of the above PTF Op can get these values
- * from its input by referring to the computed
- * windowing expression via its alias.
- * - but this is not needed. Why?
- * - When transforming a AST tree to an ExprNodeDesc the TypeCheckFactory checks
- * if there is a mapping from an AST tree to an output
- * column in the InputResolver; if there is it uses the alias for the Output column
- * - This is how values get handed from a GBy Op to the next Select Op;
- * - we need the same thing to happen.
+ * See checkAndExtractWindowFunctionsInSelect for rules on what makes a UDAF invocation
+ * a Windowing Function invocation
*/
private void handleWindowingExprsInSelectList(QB qb, String dest, ASTNode selectNode)
throws SemanticException {
- TreeWizard tw = new TreeWizard(ParseDriver.adaptor, HiveParser.tokenNames);
- CheckLeadLagInSelectExprs checkLLFunctions = new CheckLeadLagInSelectExprs(qb, dest);
-
for(int i=0; i < selectNode.getChildCount(); i++)
{
ASTNode selectExpr = (ASTNode) selectNode.getChild(i);
@@ -10168,44 +10128,6 @@ public class SemanticAnalyzer extends Ba
}
boolean hasWindowingExprs = checkAndExtractWindowFunctionsInSelect(qb, selectExpr, dest);
- if ( !hasWindowingExprs ) {
- checkLLFunctions.reset();
- tw.visit(selectExpr, HiveParser.TOK_FUNCTION, checkLLFunctions);
-
- if ( checkLLFunctions.isError() ) {
- throw new SemanticException(generateErrorMessage(selectExpr,
- checkLLFunctions.getErrString()));
- }
-
- hasWindowingExprs = checkLLFunctions.hasLeadLagExprs();
-
- if ( hasWindowingExprs )
- {
- ASTNode expr = (ASTNode)selectExpr.getChild(0);
- WindowingSpec spec = qb.getWindowingSpec(dest);
- if(spec == null) {
- queryProperties.setHasWindowing(true);
- spec = new WindowingSpec();
- qb.addDestToWindowingSpec(dest, spec);
- }
-
- String alias;
- int childCount = selectExpr.getChildCount();
- /*
- * @revisit what if there are multiple Identifiers(lateral view)
- */
- if ( childCount >= 2 ) {
- alias = getColAlias(selectExpr, null, null, true, -1)[1];
- }
- else {
- int wColIdx = spec.getWindowExpressions() == null ? 0 :
- spec.getWindowExpressions().size();
- alias = "_wcol" + wColIdx;
- }
- spec.addExpression(expr, alias);
- qb.getParseInfo().addWindowingExprToClause(dest, expr);
- }
- }
}
}
Modified: 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=1454504&r1=1454503&r2=1454504&view=diff
==============================================================================
--- hive/branches/ptf-windowing/ql/src/test/queries/clientpositive/leadlag.q (original)
+++ hive/branches/ptf-windowing/ql/src/test/queries/clientpositive/leadlag.q Fri Mar 8 19:26:38 2013
@@ -20,7 +20,7 @@ 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
+p_size, p_size - lag(p_size,1,p_size) as deltaSz
from noop(on part
partition by p_mfgr
order by p_name
@@ -31,14 +31,14 @@ 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
+p_size, p_size - lag(p_size,1,p_size) 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
+p1.p_size, p1.p_size - lag(p1.p_size,1,p1.p_size) 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 ;
@@ -60,18 +60,20 @@ sort by p_mfgr
window w1 as (rows between 2 preceding and 2 following) ;
-- 6. testRankInLead
+select p_mfgr, p_name, p_size, r1,
+lead(r1,1,r1) as deltaRank over (distribute by p_mfgr sort by p_name)
+from (
select p_mfgr, p_name, p_size,
-rank() as r1,
-lead(rank(), 1) as deltaRank
+rank() as r1
from part
distribute by p_mfgr
-sort by p_name;
+sort by p_name) a;
-- 7. testLeadWithPTF
select p_mfgr, p_name,
rank() as r,
dense_rank() as dr,
-p_size, p_size - lead(p_size,1) as deltaSz
+p_size, p_size - lead(p_size,1,p_size) as deltaSz
from noop(on part
partition by p_mfgr
order by p_name
@@ -85,3 +87,4 @@ lead(p_retailprice) as l1 over(),
lag(p_retailprice) as l2 over()
from part
order by p_name;
+
Modified: hive/branches/ptf-windowing/ql/src/test/queries/clientpositive/leadlag_queries.q
URL: http://svn.apache.org/viewvc/hive/branches/ptf-windowing/ql/src/test/queries/clientpositive/leadlag_queries.q?rev=1454504&r1=1454503&r2=1454504&view=diff
==============================================================================
--- hive/branches/ptf-windowing/ql/src/test/queries/clientpositive/leadlag_queries.q (original)
+++ hive/branches/ptf-windowing/ql/src/test/queries/clientpositive/leadlag_queries.q Fri Mar 8 19:26:38 2013
@@ -19,13 +19,13 @@ lead(p_retailprice) as l1 over (partitio
lead(p_retailprice,1) as l2 over (partition by p_mfgr order by p_name),
lead(p_retailprice,1,10) as l3 over (partition by p_mfgr order by p_name),
lead(p_retailprice,1, p_retailprice) as l4 over (partition by p_mfgr order by p_name),
-p_retailprice - lead(p_retailprice,1)
+p_retailprice - lead(p_retailprice,1,p_retailprice)
from part;
-- 2.testLeadUDAFPartSz1
select p_mfgr, p_name, p_retailprice,
lead(p_retailprice,1) over (partition by p_mfgr, p_name ),
-p_retailprice - lead(p_retailprice,1)
+p_retailprice - lead(p_retailprice,1,p_retailprice)
from part;
-- 3.testLagUDAF
@@ -34,20 +34,20 @@ lag(p_retailprice,1) as l1 over (partiti
lag(p_retailprice) as l2 over (partition by p_mfgr order by p_name),
lag(p_retailprice,1, p_retailprice) as l3 over (partition by p_mfgr order by p_name),
lag(p_retailprice,1,10) as l4 over (partition by p_mfgr order by p_name),
-p_retailprice - lag(p_retailprice,1)
+p_retailprice - lag(p_retailprice,1,p_retailprice)
from part;
-- 4.testLagUDAFPartSz1
select p_mfgr, p_name, p_retailprice,
lag(p_retailprice,1) over (partition by p_mfgr, p_name ),
-p_retailprice - lag(p_retailprice,1)
+p_retailprice - lag(p_retailprice,1,p_retailprice)
from part;
-- 5.testLeadLagUDAF
select p_mfgr, p_retailprice,
lead(p_retailprice,1) as l1 over (partition by p_mfgr order by p_name),
lead(p_retailprice,1, p_retailprice) as l2 over (partition by p_mfgr order by p_name),
-p_retailprice - lead(p_retailprice,1),
+p_retailprice - lead(p_retailprice,1,p_retailprice),
lag(p_retailprice,1) as l3 over (partition by p_mfgr order by p_name),
lag(p_retailprice,1, p_retailprice) as l4 over (partition by p_mfgr order by p_name)
from part;
\ No newline at end of file
Modified: 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=1454504&r1=1454503&r2=1454504&view=diff
==============================================================================
--- hive/branches/ptf-windowing/ql/src/test/queries/clientpositive/ptf.q (original)
+++ hive/branches/ptf-windowing/ql/src/test/queries/clientpositive/ptf.q Fri Mar 8 19:26:38 2013
@@ -27,7 +27,7 @@ from noop(on part
-- 2. testJoinWithNoop
select p_mfgr, p_name,
-p_size, p_size - lag(p_size,1) as deltaSz
+p_size, p_size - lag(p_size,1,p_size) 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)
@@ -54,7 +54,7 @@ from noop(on part
select p_mfgr, p_name, p_size,
rank() as r,
dense_rank() as dr,
-p_size, p_size - lag(p_size,1) as deltaSz
+p_size, p_size - lag(p_size,1,p_size) as deltaSz
from noop(on part
partition by p_mfgr
order by p_name
@@ -67,7 +67,7 @@ sort by p_name;
select p_mfgr, p_name, p_size,
rank() as r,
dense_rank() as dr,
-p_size, p_size - lag(p_size,1) as deltaSz
+p_size, p_size - lag(p_size,1,p_size) as deltaSz
from noop(on part
partition by p_mfgr
order by p_name
@@ -145,7 +145,7 @@ 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
+abc.p_size, abc.p_size - lag(abc.p_size,1,abc.p_size) as deltaSz
from noop(on part
partition by p_mfgr
order by p_name
Modified: hive/branches/ptf-windowing/ql/src/test/queries/clientpositive/windowing.q
URL: http://svn.apache.org/viewvc/hive/branches/ptf-windowing/ql/src/test/queries/clientpositive/windowing.q?rev=1454504&r1=1454503&r2=1454504&view=diff
==============================================================================
--- hive/branches/ptf-windowing/ql/src/test/queries/clientpositive/windowing.q (original)
+++ hive/branches/ptf-windowing/ql/src/test/queries/clientpositive/windowing.q Fri Mar 8 19:26:38 2013
@@ -28,7 +28,7 @@ sort by p_name;
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
+p_size, p_size - lag(p_size,1,p_size) as deltaSz
from part
group by p_mfgr, p_name, p_size
distribute by p_mfgr
@@ -38,7 +38,7 @@ sort by p_name ;
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
+p_size, p_size - lag(p_size,1,p_size) as deltaSz
from part
group by p_mfgr, p_name, p_size
having p_size > 0
@@ -58,7 +58,7 @@ 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
+p_size, p_size - lag(p_size,1,p_size) as deltaSz
from part
distribute by p_mfgr
sort by p_name;
@@ -70,7 +70,7 @@ 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
+p_size, p_size - lag(p_size,1,p_size) as deltaSz
from part
distribute by p_mfgr
sort by p_name
@@ -81,7 +81,7 @@ 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
+abc.p_size, abc.p_size - lag(abc.p_size,1,abc.p_size) as deltaSz
from noop(on part
partition by p_mfgr
order by p_name
@@ -343,7 +343,7 @@ select * from part_3;
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
+p_size, p_size - lag(p_size,1,p_size) as deltaSz
from part
group by p_mfgr, p_name, p_size
having p_size > 0
@@ -481,4 +481,4 @@ select p_name, p_retailprice,
avg(p_retailprice) over()
from part
order by p_name;
-
\ No newline at end of file
+
Modified: hive/branches/ptf-windowing/ql/src/test/queries/clientpositive/windowing_expressions.q
URL: http://svn.apache.org/viewvc/hive/branches/ptf-windowing/ql/src/test/queries/clientpositive/windowing_expressions.q?rev=1454504&r1=1454503&r2=1454504&view=diff
==============================================================================
--- hive/branches/ptf-windowing/ql/src/test/queries/clientpositive/windowing_expressions.q (original)
+++ hive/branches/ptf-windowing/ql/src/test/queries/clientpositive/windowing_expressions.q Fri Mar 8 19:26:38 2013
@@ -43,7 +43,6 @@ sort by p_retailprice;
select p_mfgr, p_retailprice, p_size,
rank() as r,
- lag(rank(),1) as pr,
sum(p_retailprice) as s2 over (rows between unbounded preceding and current row),
sum(p_retailprice) - 5 as s1 over (rows between unbounded preceding and current row)
from part
Modified: 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=1454504&r1=1454503&r2=1454504&view=diff
==============================================================================
--- hive/branches/ptf-windowing/ql/src/test/results/clientpositive/leadlag.q.out (original)
+++ hive/branches/ptf-windowing/ql/src/test/results/clientpositive/leadlag.q.out Fri Mar 8 19:26:38 2013
@@ -40,7 +40,7 @@ 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
+p_size, p_size - lag(p_size,1,p_size) as deltaSz
from noop(on part
partition by p_mfgr
order by p_name
@@ -53,7 +53,7 @@ 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
+p_size, p_size - lag(p_size,1,p_size) as deltaSz
from noop(on part
partition by p_mfgr
order by p_name
@@ -92,7 +92,7 @@ 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
+p_size, p_size - lag(p_size,1,p_size) as deltaSz
from part
distribute by p_mfgr
sort by p_name
@@ -104,7 +104,7 @@ 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
+p_size, p_size - lag(p_size,1,p_size) as deltaSz
from part
distribute by p_mfgr
sort by p_name
@@ -139,7 +139,7 @@ Manufacturer#5 almond aquamarine dodger
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
+p1.p_size, p1.p_size - lag(p1.p_size,1,p1.p_size) 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
@@ -148,7 +148,7 @@ 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
+p1.p_size, p1.p_size - lag(p1.p_size,1,p1.p_size) 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
@@ -276,22 +276,26 @@ Manufacturer#5 almond antique sky peru o
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, r1,
+lead(r1,1,r1) as deltaRank over (distribute by p_mfgr sort by p_name)
+from (
select p_mfgr, p_name, p_size,
-rank() as r1,
-lead(rank(), 1) as deltaRank
+rank() as r1
from part
distribute by p_mfgr
-sort by p_name
+sort by p_name) a
PREHOOK: type: QUERY
PREHOOK: Input: default@part
#### A masked pattern was here ####
POSTHOOK: query: -- 6. testRankInLead
+select p_mfgr, p_name, p_size, r1,
+lead(r1,1,r1) as deltaRank over (distribute by p_mfgr sort by p_name)
+from (
select p_mfgr, p_name, p_size,
-rank() as r1,
-lead(rank(), 1) as deltaRank
+rank() as r1
from part
distribute by p_mfgr
-sort by p_name
+sort by p_name) a
POSTHOOK: type: QUERY
POSTHOOK: Input: default@part
#### A masked pattern was here ####
@@ -325,7 +329,7 @@ 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
+p_size, p_size - lead(p_size,1,p_size) as deltaSz
from noop(on part
partition by p_mfgr
order by p_name
@@ -339,7 +343,7 @@ 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
+p_size, p_size - lead(p_size,1,p_size) as deltaSz
from noop(on part
partition by p_mfgr
order by p_name
Modified: hive/branches/ptf-windowing/ql/src/test/results/clientpositive/leadlag_queries.q.out
URL: http://svn.apache.org/viewvc/hive/branches/ptf-windowing/ql/src/test/results/clientpositive/leadlag_queries.q.out?rev=1454504&r1=1454503&r2=1454504&view=diff
==============================================================================
--- hive/branches/ptf-windowing/ql/src/test/results/clientpositive/leadlag_queries.q.out (original)
+++ hive/branches/ptf-windowing/ql/src/test/results/clientpositive/leadlag_queries.q.out Fri Mar 8 19:26:38 2013
@@ -37,7 +37,7 @@ lead(p_retailprice) as l1 over (partitio
lead(p_retailprice,1) as l2 over (partition by p_mfgr order by p_name),
lead(p_retailprice,1,10) as l3 over (partition by p_mfgr order by p_name),
lead(p_retailprice,1, p_retailprice) as l4 over (partition by p_mfgr order by p_name),
-p_retailprice - lead(p_retailprice,1)
+p_retailprice - lead(p_retailprice,1,p_retailprice)
from part
PREHOOK: type: QUERY
PREHOOK: Input: default@part
@@ -48,7 +48,7 @@ lead(p_retailprice) as l1 over (partitio
lead(p_retailprice,1) as l2 over (partition by p_mfgr order by p_name),
lead(p_retailprice,1,10) as l3 over (partition by p_mfgr order by p_name),
lead(p_retailprice,1, p_retailprice) as l4 over (partition by p_mfgr order by p_name),
-p_retailprice - lead(p_retailprice,1)
+p_retailprice - lead(p_retailprice,1,p_retailprice)
from part
POSTHOOK: type: QUERY
POSTHOOK: Input: default@part
@@ -82,7 +82,7 @@ Manufacturer#5 1464.48 NULL NULL 10.0 14
PREHOOK: query: -- 2.testLeadUDAFPartSz1
select p_mfgr, p_name, p_retailprice,
lead(p_retailprice,1) over (partition by p_mfgr, p_name ),
-p_retailprice - lead(p_retailprice,1)
+p_retailprice - lead(p_retailprice,1,p_retailprice)
from part
PREHOOK: type: QUERY
PREHOOK: Input: default@part
@@ -90,7 +90,7 @@ PREHOOK: Input: default@part
POSTHOOK: query: -- 2.testLeadUDAFPartSz1
select p_mfgr, p_name, p_retailprice,
lead(p_retailprice,1) over (partition by p_mfgr, p_name ),
-p_retailprice - lead(p_retailprice,1)
+p_retailprice - lead(p_retailprice,1,p_retailprice)
from part
POSTHOOK: type: QUERY
POSTHOOK: Input: default@part
@@ -127,7 +127,7 @@ lag(p_retailprice,1) as l1 over (partiti
lag(p_retailprice) as l2 over (partition by p_mfgr order by p_name),
lag(p_retailprice,1, p_retailprice) as l3 over (partition by p_mfgr order by p_name),
lag(p_retailprice,1,10) as l4 over (partition by p_mfgr order by p_name),
-p_retailprice - lag(p_retailprice,1)
+p_retailprice - lag(p_retailprice,1,p_retailprice)
from part
PREHOOK: type: QUERY
PREHOOK: Input: default@part
@@ -138,7 +138,7 @@ lag(p_retailprice,1) as l1 over (partiti
lag(p_retailprice) as l2 over (partition by p_mfgr order by p_name),
lag(p_retailprice,1, p_retailprice) as l3 over (partition by p_mfgr order by p_name),
lag(p_retailprice,1,10) as l4 over (partition by p_mfgr order by p_name),
-p_retailprice - lag(p_retailprice,1)
+p_retailprice - lag(p_retailprice,1,p_retailprice)
from part
POSTHOOK: type: QUERY
POSTHOOK: Input: default@part
@@ -172,7 +172,7 @@ Manufacturer#5 1464.48 1018.1 1018.1 101
PREHOOK: query: -- 4.testLagUDAFPartSz1
select p_mfgr, p_name, p_retailprice,
lag(p_retailprice,1) over (partition by p_mfgr, p_name ),
-p_retailprice - lag(p_retailprice,1)
+p_retailprice - lag(p_retailprice,1,p_retailprice)
from part
PREHOOK: type: QUERY
PREHOOK: Input: default@part
@@ -180,7 +180,7 @@ PREHOOK: Input: default@part
POSTHOOK: query: -- 4.testLagUDAFPartSz1
select p_mfgr, p_name, p_retailprice,
lag(p_retailprice,1) over (partition by p_mfgr, p_name ),
-p_retailprice - lag(p_retailprice,1)
+p_retailprice - lag(p_retailprice,1,p_retailprice)
from part
POSTHOOK: type: QUERY
POSTHOOK: Input: default@part
@@ -215,7 +215,7 @@ PREHOOK: query: -- 5.testLeadLagUDAF
select p_mfgr, p_retailprice,
lead(p_retailprice,1) as l1 over (partition by p_mfgr order by p_name),
lead(p_retailprice,1, p_retailprice) as l2 over (partition by p_mfgr order by p_name),
-p_retailprice - lead(p_retailprice,1),
+p_retailprice - lead(p_retailprice,1,p_retailprice),
lag(p_retailprice,1) as l3 over (partition by p_mfgr order by p_name),
lag(p_retailprice,1, p_retailprice) as l4 over (partition by p_mfgr order by p_name)
from part
@@ -226,7 +226,7 @@ POSTHOOK: query: -- 5.testLeadLagUDAF
select p_mfgr, p_retailprice,
lead(p_retailprice,1) as l1 over (partition by p_mfgr order by p_name),
lead(p_retailprice,1, p_retailprice) as l2 over (partition by p_mfgr order by p_name),
-p_retailprice - lead(p_retailprice,1),
+p_retailprice - lead(p_retailprice,1,p_retailprice),
lag(p_retailprice,1) as l3 over (partition by p_mfgr order by p_name),
lag(p_retailprice,1, p_retailprice) as l4 over (partition by p_mfgr order by p_name)
from part
Modified: hive/branches/ptf-windowing/ql/src/test/results/clientpositive/ptf.q.out
URL: http://svn.apache.org/viewvc/hive/branches/ptf-windowing/ql/src/test/results/clientpositive/ptf.q.out?rev=1454504&r1=1454503&r2=1454504&view=diff
==============================================================================
--- hive/branches/ptf-windowing/ql/src/test/results/clientpositive/ptf.q.out (original)
+++ hive/branches/ptf-windowing/ql/src/test/results/clientpositive/ptf.q.out Fri Mar 8 19:26:38 2013
@@ -87,7 +87,7 @@ Manufacturer#5 almond aquamarine dodger
Manufacturer#5 almond azure blanched chiffon midnight 23 5 5 7672.66
PREHOOK: query: -- 2. testJoinWithNoop
select p_mfgr, p_name,
-p_size, p_size - lag(p_size,1) as deltaSz
+p_size, p_size - lag(p_size,1,p_size) 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)
@@ -98,7 +98,7 @@ PREHOOK: Input: default@part
#### A masked pattern was here ####
POSTHOOK: query: -- 2. testJoinWithNoop
select p_mfgr, p_name,
-p_size, p_size - lag(p_size,1) as deltaSz
+p_size, p_size - lag(p_size,1,p_size) 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)
@@ -231,7 +231,7 @@ PREHOOK: query: -- 5. testPTFAndWhereWit
select p_mfgr, p_name, p_size,
rank() as r,
dense_rank() as dr,
-p_size, p_size - lag(p_size,1) as deltaSz
+p_size, p_size - lag(p_size,1,p_size) as deltaSz
from noop(on part
partition by p_mfgr
order by p_name
@@ -246,7 +246,7 @@ POSTHOOK: query: -- 5. testPTFAndWhereWi
select p_mfgr, p_name, p_size,
rank() as r,
dense_rank() as dr,
-p_size, p_size - lag(p_size,1) as deltaSz
+p_size, p_size - lag(p_size,1,p_size) as deltaSz
from noop(on part
partition by p_mfgr
order by p_name
@@ -287,7 +287,7 @@ PREHOOK: query: -- 6. testSWQAndPTFAndGB
select p_mfgr, p_name, p_size,
rank() as r,
dense_rank() as dr,
-p_size, p_size - lag(p_size,1) as deltaSz
+p_size, p_size - lag(p_size,1,p_size) as deltaSz
from noop(on part
partition by p_mfgr
order by p_name
@@ -302,7 +302,7 @@ POSTHOOK: query: -- 6. testSWQAndPTFAndG
select p_mfgr, p_name, p_size,
rank() as r,
dense_rank() as dr,
-p_size, p_size - lag(p_size,1) as deltaSz
+p_size, p_size - lag(p_size,1,p_size) as deltaSz
from noop(on part
partition by p_mfgr
order by p_name
@@ -673,7 +673,7 @@ 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
+abc.p_size, abc.p_size - lag(abc.p_size,1,abc.p_size) as deltaSz
from noop(on part
partition by p_mfgr
order by p_name
@@ -689,7 +689,7 @@ 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
+abc.p_size, abc.p_size - lag(abc.p_size,1,abc.p_size) as deltaSz
from noop(on part
partition by p_mfgr
order by p_name
Modified: hive/branches/ptf-windowing/ql/src/test/results/clientpositive/windowing.q.out
URL: http://svn.apache.org/viewvc/hive/branches/ptf-windowing/ql/src/test/results/clientpositive/windowing.q.out?rev=1454504&r1=1454503&r2=1454504&view=diff
==============================================================================
--- hive/branches/ptf-windowing/ql/src/test/results/clientpositive/windowing.q.out (original)
+++ hive/branches/ptf-windowing/ql/src/test/results/clientpositive/windowing.q.out Fri Mar 8 19:26:38 2013
@@ -87,7 +87,7 @@ PREHOOK: query: -- 2. testGroupByWithPar
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
+p_size, p_size - lag(p_size,1,p_size) as deltaSz
from part
group by p_mfgr, p_name, p_size
distribute by p_mfgr
@@ -99,7 +99,7 @@ POSTHOOK: query: -- 2. testGroupByWithPa
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
+p_size, p_size - lag(p_size,1,p_size) as deltaSz
from part
group by p_mfgr, p_name, p_size
distribute by p_mfgr
@@ -136,7 +136,7 @@ PREHOOK: query: -- 3. testGroupByHavingW
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
+p_size, p_size - lag(p_size,1,p_size) as deltaSz
from part
group by p_mfgr, p_name, p_size
having p_size > 0
@@ -149,7 +149,7 @@ POSTHOOK: query: -- 3. testGroupByHaving
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
+p_size, p_size - lag(p_size,1,p_size) as deltaSz
from part
group by p_mfgr, p_name, p_size
having p_size > 0
@@ -233,7 +233,7 @@ 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
+p_size, p_size - lag(p_size,1,p_size) as deltaSz
from part
distribute by p_mfgr
sort by p_name
@@ -246,7 +246,7 @@ 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
+p_size, p_size - lag(p_size,1,p_size) as deltaSz
from part
distribute by p_mfgr
sort by p_name
@@ -286,7 +286,7 @@ 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
+p_size, p_size - lag(p_size,1,p_size) as deltaSz
from part
distribute by p_mfgr
sort by p_name
@@ -301,7 +301,7 @@ 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
+p_size, p_size - lag(p_size,1,p_size) as deltaSz
from part
distribute by p_mfgr
sort by p_name
@@ -340,7 +340,7 @@ 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
+abc.p_size, abc.p_size - lag(abc.p_size,1,abc.p_size) as deltaSz
from noop(on part
partition by p_mfgr
order by p_name
@@ -355,7 +355,7 @@ 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
+abc.p_size, abc.p_size - lag(abc.p_size,1,abc.p_size) as deltaSz
from noop(on part
partition by p_mfgr
order by p_name
@@ -1581,7 +1581,7 @@ PREHOOK: query: -- 26. testGroupByHaving
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
+p_size, p_size - lag(p_size,1,p_size) as deltaSz
from part
group by p_mfgr, p_name, p_size
having p_size > 0
@@ -1594,7 +1594,7 @@ POSTHOOK: query: -- 26. testGroupByHavin
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
+p_size, p_size - lag(p_size,1,p_size) as deltaSz
from part
group by p_mfgr, p_name, p_size
having p_size > 0
Modified: hive/branches/ptf-windowing/ql/src/test/results/clientpositive/windowing_expressions.q.out
URL: http://svn.apache.org/viewvc/hive/branches/ptf-windowing/ql/src/test/results/clientpositive/windowing_expressions.q.out?rev=1454504&r1=1454503&r2=1454504&view=diff
==============================================================================
--- hive/branches/ptf-windowing/ql/src/test/results/clientpositive/windowing_expressions.q.out (original)
+++ hive/branches/ptf-windowing/ql/src/test/results/clientpositive/windowing_expressions.q.out Fri Mar 8 19:26:38 2013
@@ -122,7 +122,6 @@ Manufacturer#5 1788.73 2 true true
Manufacturer#5 1789.69 31 true true
PREHOOK: query: select p_mfgr, p_retailprice, p_size,
rank() as r,
- lag(rank(),1) as pr,
sum(p_retailprice) as s2 over (rows between unbounded preceding and current row),
sum(p_retailprice) - 5 as s1 over (rows between unbounded preceding and current row)
from part
@@ -133,7 +132,6 @@ PREHOOK: Input: default@part
#### A masked pattern was here ####
POSTHOOK: query: select p_mfgr, p_retailprice, p_size,
rank() as r,
- lag(rank(),1) as pr,
sum(p_retailprice) as s2 over (rows between unbounded preceding and current row),
sum(p_retailprice) - 5 as s1 over (rows between unbounded preceding and current row)
from part
@@ -142,32 +140,32 @@ sort by p_retailprice
POSTHOOK: type: QUERY
POSTHOOK: Input: default@part
#### A masked pattern was here ####
-Manufacturer#1 1173.15 2 1 1 1173.15 1168.15
-Manufacturer#1 1173.15 2 1 1 2346.3 2341.3
-Manufacturer#1 1414.42 28 3 1 3760.7200000000003 3755.7200000000003
-Manufacturer#1 1602.59 6 4 3 5363.31 5358.31
-Manufacturer#1 1632.66 42 5 4 6995.97 6990.97
-Manufacturer#1 1753.76 34 6 5 8749.73 8744.73
-Manufacturer#2 1690.68 14 1 1 1690.68 1685.68
-Manufacturer#2 1698.66 25 2 1 3389.34 3384.34
-Manufacturer#2 1701.6 18 3 2 5090.9400000000005 5085.9400000000005
-Manufacturer#2 1800.7 40 4 3 6891.64 6886.64
-Manufacturer#2 2031.98 2 5 4 8923.62 8918.62
-Manufacturer#3 1190.27 14 1 1 1190.27 1185.27
-Manufacturer#3 1337.29 45 2 1 2527.56 2522.56
-Manufacturer#3 1410.39 19 3 2 3937.95 3932.95
-Manufacturer#3 1671.68 17 4 3 5609.63 5604.63
-Manufacturer#3 1922.98 1 5 4 7532.610000000001 7527.610000000001
-Manufacturer#4 1206.26 27 1 1 1206.26 1201.26
-Manufacturer#4 1290.35 12 2 1 2496.6099999999997 2491.6099999999997
-Manufacturer#4 1375.42 39 3 2 3872.0299999999997 3867.0299999999997
-Manufacturer#4 1620.67 10 4 3 5492.7 5487.7
-Manufacturer#4 1844.92 7 5 4 7337.62 7332.62
-Manufacturer#5 1018.1 46 1 1 1018.1 1013.1
-Manufacturer#5 1464.48 23 2 1 2482.58 2477.58
-Manufacturer#5 1611.66 6 3 2 4094.24 4089.24
-Manufacturer#5 1788.73 2 4 3 5882.969999999999 5877.969999999999
-Manufacturer#5 1789.69 31 5 4 7672.66 7667.66
+Manufacturer#1 1173.15 2 1 1173.15 1168.15
+Manufacturer#1 1173.15 2 1 2346.3 2341.3
+Manufacturer#1 1414.42 28 3 3760.7200000000003 3755.7200000000003
+Manufacturer#1 1602.59 6 4 5363.31 5358.31
+Manufacturer#1 1632.66 42 5 6995.97 6990.97
+Manufacturer#1 1753.76 34 6 8749.73 8744.73
+Manufacturer#2 1690.68 14 1 1690.68 1685.68
+Manufacturer#2 1698.66 25 2 3389.34 3384.34
+Manufacturer#2 1701.6 18 3 5090.9400000000005 5085.9400000000005
+Manufacturer#2 1800.7 40 4 6891.64 6886.64
+Manufacturer#2 2031.98 2 5 8923.62 8918.62
+Manufacturer#3 1190.27 14 1 1190.27 1185.27
+Manufacturer#3 1337.29 45 2 2527.56 2522.56
+Manufacturer#3 1410.39 19 3 3937.95 3932.95
+Manufacturer#3 1671.68 17 4 5609.63 5604.63
+Manufacturer#3 1922.98 1 5 7532.610000000001 7527.610000000001
+Manufacturer#4 1206.26 27 1 1206.26 1201.26
+Manufacturer#4 1290.35 12 2 2496.6099999999997 2491.6099999999997
+Manufacturer#4 1375.42 39 3 3872.0299999999997 3867.0299999999997
+Manufacturer#4 1620.67 10 4 5492.7 5487.7
+Manufacturer#4 1844.92 7 5 7337.62 7332.62
+Manufacturer#5 1018.1 46 1 1018.1 1013.1
+Manufacturer#5 1464.48 23 2 2482.58 2477.58
+Manufacturer#5 1611.66 6 3 4094.24 4089.24
+Manufacturer#5 1788.73 2 4 5882.969999999999 5877.969999999999
+Manufacturer#5 1789.69 31 5 7672.66 7667.66
PREHOOK: query: select s, si, f, si - lead(f, 3) over (partition by t order by bo desc) from over10k limit 100
PREHOOK: type: QUERY
PREHOOK: Input: default@over10k