diff --git ql/src/java/org/apache/hadoop/hive/ql/exec/FunctionRegistry.java ql/src/java/org/apache/hadoop/hive/ql/exec/FunctionRegistry.java index 2c2cde4..5e10788 100644 --- ql/src/java/org/apache/hadoop/hive/ql/exec/FunctionRegistry.java +++ ql/src/java/org/apache/hadoop/hive/ql/exec/FunctionRegistry.java @@ -149,6 +149,8 @@ import org.apache.hadoop.hive.ql.udf.generic.GenericUDAFPercentRank; import org.apache.hadoop.hive.ql.udf.generic.GenericUDAFPercentileApprox; import org.apache.hadoop.hive.ql.udf.generic.GenericUDAFRank; +import org.apache.hadoop.hive.ql.udf.generic.GenericUDAFLead; +import org.apache.hadoop.hive.ql.udf.generic.GenericUDAFLag; import org.apache.hadoop.hive.ql.udf.generic.GenericUDAFResolver; import org.apache.hadoop.hive.ql.udf.generic.GenericUDAFResolver2; import org.apache.hadoop.hive.ql.udf.generic.GenericUDAFRowNumber; @@ -528,6 +530,8 @@ registerWindowFunction("ntile", new GenericUDAFNTile()); registerWindowFunction("first_value", new GenericUDAFFirstValue()); registerWindowFunction("last_value", new GenericUDAFLastValue()); + registerWindowFunction("lead", new GenericUDAFLead(), false); + registerWindowFunction("lag", new GenericUDAFLag(), false); RANKING_FUNCTIONS.add("rank"); RANKING_FUNCTIONS.add("dense_rank"); @@ -855,6 +859,46 @@ public static GenericUDAFEvaluator getGenericUDAFEvaluator(String name, return udafEvaluator; } + @SuppressWarnings("deprecation") + public static GenericUDAFEvaluator getGenericWindowingEvaluator(String name, + List argumentOIs, boolean isDistinct, + boolean isAllColumns) throws SemanticException { + + if ( name.toLowerCase().equals(LEAD_FUNC_NAME) || + name.toLowerCase().equals(LAG_FUNC_NAME) ) { + + WindowFunctionInfo finfo = windowFunctions.get(name.toLowerCase()); + if (finfo == null) { + return null; + } + GenericUDAFResolver udafResolver = finfo.getfInfo().getGenericUDAFResolver(); + if (udafResolver == null) { + return null; + } + + GenericUDAFEvaluator udafEvaluator = null; + ObjectInspector args[] = new ObjectInspector[argumentOIs.size()]; + // Can't use toArray here because Java is dumb when it comes to + // generics + arrays. + for (int ii = 0; ii < argumentOIs.size(); ++ii) { + args[ii] = argumentOIs.get(ii); + } + + GenericUDAFParameterInfo paramInfo = + new SimpleGenericUDAFParameterInfo( + args, isDistinct, isAllColumns); + if (udafResolver instanceof GenericUDAFResolver2) { + udafEvaluator = + ((GenericUDAFResolver2) udafResolver).getEvaluator(paramInfo); + } else { + udafEvaluator = udafResolver.getEvaluator(paramInfo.getParameters()); + } + return udafEvaluator; + + } + return getGenericUDAFEvaluator(name, argumentOIs, isDistinct, isAllColumns); + } + /** * This method is shared between UDFRegistry and UDAFRegistry. methodName will * be "evaluate" for UDFRegistry, and "aggregate"/"evaluate"/"evaluatePartial" @@ -1417,8 +1461,21 @@ private FunctionRegistry() { public static void registerWindowFunction(String name, GenericUDAFResolver wFn) { - registerGenericUDAF(true, name, wFn); - FunctionInfo fInfo = getFunctionInfo(name); + registerWindowFunction(name, wFn, true); + } + + public static void registerWindowFunction(String name, GenericUDAFResolver wFn, boolean registerAsUDAF) + { + FunctionInfo fInfo = null; + if (registerAsUDAF) { + registerGenericUDAF(true, name, wFn); + fInfo = getFunctionInfo(name); + } + else { + fInfo = new FunctionInfo(true, + name.toLowerCase(), wFn); + } + WindowFunctionInfo wInfo = new WindowFunctionInfo(fInfo); windowFunctions.put(name.toLowerCase(), wInfo); } diff --git ql/src/java/org/apache/hadoop/hive/ql/parse/PTFTranslator.java ql/src/java/org/apache/hadoop/hive/ql/parse/PTFTranslator.java index c78b8d6..7170452 100644 --- ql/src/java/org/apache/hadoop/hive/ql/parse/PTFTranslator.java +++ ql/src/java/org/apache/hadoop/hive/ql/parse/PTFTranslator.java @@ -686,7 +686,7 @@ static void setupWdwFnEvaluator(WindowFunctionDef def) throws HiveException funcArgOIs = argOIs.toArray(funcArgOIs); } - GenericUDAFEvaluator wFnEval = FunctionRegistry.getGenericUDAFEvaluator(def.getName(), argOIs, + GenericUDAFEvaluator wFnEval = FunctionRegistry.getGenericWindowingEvaluator(def.getName(), argOIs, def.isDistinct(), def.isStar()); ObjectInspector OI = wFnEval.init(GenericUDAFEvaluator.Mode.COMPLETE, funcArgOIs); def.setWFnEval(wFnEval); diff --git ql/src/java/org/apache/hadoop/hive/ql/parse/SemanticAnalyzer.java ql/src/java/org/apache/hadoop/hive/ql/parse/SemanticAnalyzer.java index 010be70..42f45f0 100644 --- ql/src/java/org/apache/hadoop/hive/ql/parse/SemanticAnalyzer.java +++ ql/src/java/org/apache/hadoop/hive/ql/parse/SemanticAnalyzer.java @@ -9937,6 +9937,17 @@ private boolean checkAndExtractWindowFunctionsInSelect(QB qb, ASTNode selectExpr boolean hasLLArgs = false; boolean isRankingOrNavFunction = FunctionRegistry.RANKING_FUNCTIONS.contains(fnName) || FunctionRegistry.NAVIGATION_FUNCTIONS.contains(fnName); + + /* + * treat Lead & Lag as a UDF if there is no WindowSpec. + */ + if (!hasWindowSpec + && + (fnName.equals(FunctionRegistry.LAG_FUNC_NAME) || fnName + .equals(FunctionRegistry.LEAD_FUNC_NAME))) { + return false; + } + /* * If Windowing Function has LeadLag expression in its args, * then it will be handled by WindowingTabFunc. diff --git ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDAFLag.java ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDAFLag.java new file mode 100644 index 0000000..e786e76 --- /dev/null +++ ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDAFLag.java @@ -0,0 +1,174 @@ +package org.apache.hadoop.hive.ql.udf.generic; + +import java.util.ArrayList; + +import org.apache.commons.logging.Log; +import org.apache.commons.logging.LogFactory; +import org.apache.hadoop.hive.ql.exec.Description; +import org.apache.hadoop.hive.ql.exec.UDFArgumentTypeException; +import org.apache.hadoop.hive.ql.exec.WindowFunctionDescription; +import org.apache.hadoop.hive.ql.metadata.HiveException; +import org.apache.hadoop.hive.ql.parse.SemanticException; +import org.apache.hadoop.hive.ql.udf.generic.GenericUDAFEvaluator.AggregationBuffer; +import org.apache.hadoop.hive.serde2.objectinspector.ConstantObjectInspector; +import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector; +import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspectorFactory; +import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspectorUtils; +import org.apache.hadoop.hive.serde2.objectinspector.PrimitiveObjectInspector; +import org.apache.hadoop.io.IntWritable; + +@WindowFunctionDescription +( + description = @Description( + name = "lag", + value = "_FUNC_(expr, amt, default)" + ), + supportsWindow = false, + pivotResult = true +) +public class GenericUDAFLag extends AbstractGenericUDAFResolver { + + static final Log LOG = LogFactory.getLog(GenericUDAFLag.class.getName()); + + @Override + public GenericUDAFLagEvaluator getEvaluator(GenericUDAFParameterInfo parameters) + throws SemanticException { + + ObjectInspector[] paramOIs = parameters.getParameterObjectInspectors(); + + if (paramOIs.length != 2 && paramOIs.length != 3) { + throw new UDFArgumentTypeException(paramOIs.length - 1, + "Incorrect invocation of Lag: _FUNC_(expr, amt, default)"); + } + + ObjectInspector lagAmtOI = paramOIs[1]; + + if ( !ObjectInspectorUtils.isConstantObjectInspector(lagAmtOI) || + (lagAmtOI.getCategory() != ObjectInspector.Category.PRIMITIVE) || + ((PrimitiveObjectInspector)lagAmtOI).getPrimitiveCategory() != + PrimitiveObjectInspector.PrimitiveCategory.INT ) + { + throw new UDFArgumentTypeException(0, + "Lag amount must be a integer value " + + lagAmtOI.getTypeName() + " was passed as parameter 1."); + } + + if (paramOIs.length == 3) { + if (!ObjectInspectorUtils.compareTypes(paramOIs[0], paramOIs[2])) { + throw new UDFArgumentTypeException(2, + "Default Value Expression is not the same type as lag expression " + + paramOIs[2].getTypeName() + " was passed as parameter 1."); + } + } + + Object lagAmt = ((ConstantObjectInspector)lagAmtOI). + getWritableConstantValue(); + + GenericUDAFLagEvaluator eval = new GenericUDAFLagEvaluator(); + eval.setLagAmt(((IntWritable)lagAmt).get()); + return eval; + } + + public static class GenericUDAFLagEvaluator extends GenericUDAFEvaluator { + + ObjectInspector[] inputOI; + int lagAmt; + + @Override + public ObjectInspector init(Mode m, ObjectInspector[] parameters) throws HiveException + { + super.init(m, parameters); + if (m != Mode.COMPLETE) + { + throw new HiveException( + "Only COMPLETE mode supported for Lag function"); + } + + inputOI = parameters; + + return ObjectInspectorFactory.getStandardListObjectInspector( + ObjectInspectorUtils + .getStandardObjectInspector(parameters[0])); + } + + public int getLagAmt() { + return lagAmt; + } + + public void setLagAmt(int lagAmt) { + this.lagAmt = lagAmt; + } + + @Override + public AggregationBuffer getNewAggregationBuffer() throws HiveException { + LagBuffer lb = new LagBuffer(); + lb.initialize(lagAmt); + return lb; + } + + @Override + public void reset(AggregationBuffer agg) throws HiveException { + ((LagBuffer)agg).initialize(lagAmt); + } + + @Override + public void iterate(AggregationBuffer agg, Object[] parameters) throws HiveException { + + + Object lagExprVal = ObjectInspectorUtils.copyToStandardObject(parameters[0], + inputOI[0]); + Object defaultVal = parameters.length > 2 ? ObjectInspectorUtils.copyToStandardObject( + parameters[2], + inputOI[2]) : null; + ((LagBuffer)agg).addRow(lagExprVal, defaultVal); + } + + @Override + public Object terminatePartial(AggregationBuffer agg) throws HiveException { + throw new HiveException("terminatePartial not supported"); + } + + @Override + public void merge(AggregationBuffer agg, Object partial) throws HiveException { + throw new HiveException("merge not supported"); + } + + @Override + public Object terminate(AggregationBuffer agg) throws HiveException { + return ((LagBuffer)agg).terminate(); + } + + } + + static class LagBuffer implements AggregationBuffer { + ArrayList values; + int lagAmt; + ArrayList lagValues; + int lastRowIdx; + + void initialize(int lagAmt) { + this.lagAmt = lagAmt; + lagValues = new ArrayList(lagAmt); + values = new ArrayList(); + lastRowIdx = -1; + } + + void addRow(Object currValue, Object defaultValue) { + int row = lastRowIdx + 1; + if ( row < lagAmt) { + lagValues.add(defaultValue); + } + values.add(currValue); + lastRowIdx++; + } + + Object terminate() { + int lastIdx = values.size() - 1; + for(int i = 0; i < lagAmt; i++) { + values.remove(lastIdx - i); + } + values.addAll(0, lagValues); + return values; + } + } +} diff --git ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDAFLead.java ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDAFLead.java new file mode 100644 index 0000000..477fadc --- /dev/null +++ ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDAFLead.java @@ -0,0 +1,185 @@ +package org.apache.hadoop.hive.ql.udf.generic; + +import java.util.ArrayList; + +import org.apache.commons.logging.Log; +import org.apache.commons.logging.LogFactory; +import org.apache.hadoop.hive.ql.exec.Description; +import org.apache.hadoop.hive.ql.exec.UDFArgumentTypeException; +import org.apache.hadoop.hive.ql.exec.WindowFunctionDescription; +import org.apache.hadoop.hive.ql.metadata.HiveException; +import org.apache.hadoop.hive.ql.parse.SemanticException; +import org.apache.hadoop.hive.ql.udf.generic.GenericUDAFEvaluator.AggregationBuffer; +import org.apache.hadoop.hive.serde2.objectinspector.ConstantObjectInspector; +import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector; +import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspectorFactory; +import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspectorUtils; +import org.apache.hadoop.hive.serde2.objectinspector.PrimitiveObjectInspector; +import org.apache.hadoop.io.IntWritable; + +@WindowFunctionDescription +( + description = @Description( + name = "lead", + value = "_FUNC_(expr, amt, default)" + ), + supportsWindow = false, + pivotResult = true +) +public class GenericUDAFLead extends AbstractGenericUDAFResolver { + + static final Log LOG = LogFactory.getLog(GenericUDAFLead.class.getName()); + + @Override + public GenericUDAFLeadEvaluator getEvaluator(GenericUDAFParameterInfo parameters) + throws SemanticException { + + ObjectInspector[] paramOIs = parameters.getParameterObjectInspectors(); + + if (paramOIs.length != 2 && paramOIs.length != 3) { + throw new UDFArgumentTypeException(paramOIs.length - 1, + "Incorrect invocation of Lead: _FUNC_(expr, amt, default)"); + } + + ObjectInspector leadAmtOI = paramOIs[1]; + + if ( !ObjectInspectorUtils.isConstantObjectInspector(leadAmtOI) || + (leadAmtOI.getCategory() != ObjectInspector.Category.PRIMITIVE) || + ((PrimitiveObjectInspector)leadAmtOI).getPrimitiveCategory() != + PrimitiveObjectInspector.PrimitiveCategory.INT ) + { + throw new UDFArgumentTypeException(0, + "Lead amount must be a integer value " + + leadAmtOI.getTypeName() + " was passed as parameter 1."); + } + + if (paramOIs.length == 3) { + if (!ObjectInspectorUtils.compareTypes(paramOIs[0], paramOIs[2])) { + throw new UDFArgumentTypeException(2, + "Default Value Expression is not the same type as lead expression " + + paramOIs[2].getTypeName() + " was passed as parameter 1."); + } + } + + Object leadAmt = ((ConstantObjectInspector)leadAmtOI). + getWritableConstantValue(); + + GenericUDAFLeadEvaluator eval = new GenericUDAFLeadEvaluator(); + eval.setLeadAmt(((IntWritable)leadAmt).get()); + return eval; + } + + public static class GenericUDAFLeadEvaluator extends GenericUDAFEvaluator { + + ObjectInspector[] inputOI; + int leadAmt; + + @Override + public ObjectInspector init(Mode m, ObjectInspector[] parameters) throws HiveException + { + super.init(m, parameters); + if (m != Mode.COMPLETE) + { + throw new HiveException( + "Only COMPLETE mode supported for Lead function"); + } + + inputOI = parameters; + + return ObjectInspectorFactory.getStandardListObjectInspector( + ObjectInspectorUtils + .getStandardObjectInspector(parameters[0])); + } + + public int getLeadAmt() { + return leadAmt; + } + + public void setLeadAmt(int leadAmt) { + this.leadAmt = leadAmt; + } + + @Override + public AggregationBuffer getNewAggregationBuffer() throws HiveException { + LeadBuffer lb = new LeadBuffer(); + lb.initialize(leadAmt); + return lb; + } + + @Override + public void reset(AggregationBuffer agg) throws HiveException { + ((LeadBuffer)agg).initialize(leadAmt); + } + + @Override + public void iterate(AggregationBuffer agg, Object[] parameters) throws HiveException { + + + Object leadExprVal = ObjectInspectorUtils.copyToStandardObject(parameters[0], + inputOI[0]); + Object defaultVal = parameters.length > 2 ? ObjectInspectorUtils.copyToStandardObject( + parameters[2], + inputOI[2]) : null; + ((LeadBuffer)agg).addRow(leadExprVal, defaultVal); + } + + @Override + public Object terminatePartial(AggregationBuffer agg) throws HiveException { + throw new HiveException("terminatePartial not supported"); + } + + @Override + public void merge(AggregationBuffer agg, Object partial) throws HiveException { + throw new HiveException("merge not supported"); + } + + @Override + public Object terminate(AggregationBuffer agg) throws HiveException { + return ((LeadBuffer)agg).terminate(); + } + + } + + static class LeadBuffer implements AggregationBuffer { + ArrayList values; + int leadAmt; + Object[] leadWindow; + int nextPosInWindow; + int lastRowIdx; + + void initialize(int leadAmt) { + this.leadAmt = leadAmt; + values = new ArrayList(); + leadWindow = new Object[leadAmt]; + nextPosInWindow = 0; + lastRowIdx = -1; + } + + void addRow(Object leadExprValue, Object defaultValue) { + int row = lastRowIdx + 1; + int leadRow = row - leadAmt; + if ( leadRow >= 0 ) { + values.add(leadExprValue); + } + leadWindow[nextPosInWindow] = defaultValue; + nextPosInWindow = (nextPosInWindow + 1) % leadAmt; + lastRowIdx++; + } + + Object terminate() { + /* + * if there are fewer than leadAmt values in leadWindow; start reading from the first position. + * Otherwise the window starts from nextPosInWindow. + */ + if ( lastRowIdx < leadAmt ) { + nextPosInWindow = 0; + } + for(int i=0; i < leadAmt; i++) { + values.add(leadWindow[nextPosInWindow]); + nextPosInWindow = (nextPosInWindow + 1) % leadAmt; + } + return values; + } + + } +} diff --git ql/src/test/queries/clientpositive/leadlag_queries.q ql/src/test/queries/clientpositive/leadlag_queries.q new file mode 100644 index 0000000..ba35822 --- /dev/null +++ ql/src/test/queries/clientpositive/leadlag_queries.q @@ -0,0 +1,40 @@ +-- 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. testLeadUDAF +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) +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) +from part; + +-- 3.testLagUDAF +select p_mfgr, p_retailprice, +lag(p_retailprice,1) as l1 over (partition by p_mfgr order by p_name), +lag(p_retailprice,1, p_retailprice) as l2 over (partition by p_mfgr order by p_name), +p_retailprice - lag(p_retailprice,1) +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) +from part; diff --git ql/src/test/results/clientpositive/leadlag_queries.q.out ql/src/test/results/clientpositive/leadlag_queries.q.out new file mode 100644 index 0000000..6eff989 --- /dev/null +++ ql/src/test/results/clientpositive/leadlag_queries.q.out @@ -0,0 +1,205 @@ +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. testLeadUDAF +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) +from part +PREHOOK: type: QUERY +PREHOOK: Input: default@part +#### A masked pattern was here #### +POSTHOOK: query: -- 1. testLeadUDAF +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) +from part +POSTHOOK: type: QUERY +POSTHOOK: Input: default@part +#### A masked pattern was here #### +Manufacturer#1 1173.15 1173.15 1173.15 0.0 +Manufacturer#1 1173.15 1753.76 1753.76 -580.6099999999999 +Manufacturer#1 1753.76 1602.59 1602.59 151.17000000000007 +Manufacturer#1 1602.59 1414.42 1414.42 188.16999999999985 +Manufacturer#1 1414.42 1632.66 1632.66 -218.24 +Manufacturer#1 1632.66 NULL 1632.66 0.0 +Manufacturer#2 1690.68 1800.7 1800.7 -110.01999999999998 +Manufacturer#2 1800.7 2031.98 2031.98 -231.27999999999997 +Manufacturer#2 2031.98 1698.66 1698.66 333.31999999999994 +Manufacturer#2 1698.66 1701.6 1701.6 -2.939999999999827 +Manufacturer#2 1701.6 NULL 1701.6 0.0 +Manufacturer#3 1671.68 1190.27 1190.27 481.4100000000001 +Manufacturer#3 1190.27 1410.39 1410.39 -220.12000000000012 +Manufacturer#3 1410.39 1922.98 1922.98 -512.5899999999999 +Manufacturer#3 1922.98 1337.29 1337.29 585.69 +Manufacturer#3 1337.29 NULL 1337.29 0.0 +Manufacturer#4 1620.67 1375.42 1375.42 245.25 +Manufacturer#4 1375.42 1206.26 1206.26 169.16000000000008 +Manufacturer#4 1206.26 1844.92 1844.92 -638.6600000000001 +Manufacturer#4 1844.92 1290.35 1290.35 554.5700000000002 +Manufacturer#4 1290.35 NULL 1290.35 0.0 +Manufacturer#5 1789.69 1611.66 1611.66 178.02999999999997 +Manufacturer#5 1611.66 1788.73 1788.73 -177.06999999999994 +Manufacturer#5 1788.73 1018.1 1018.1 770.63 +Manufacturer#5 1018.1 1464.48 1464.48 -446.38 +Manufacturer#5 1464.48 NULL 1464.48 0.0 +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) +from part +PREHOOK: type: QUERY +PREHOOK: Input: default@part +#### A masked pattern was here #### +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) +from part +POSTHOOK: type: QUERY +POSTHOOK: Input: default@part +#### A masked pattern was here #### +Manufacturer#1 almond antique burnished rose metallic 1173.15 1173.15 0.0 +Manufacturer#1 almond antique burnished rose metallic 1173.15 NULL 0.0 +Manufacturer#1 almond antique chartreuse lavender yellow 1753.76 NULL 0.0 +Manufacturer#1 almond antique salmon chartreuse burlywood 1602.59 NULL 0.0 +Manufacturer#1 almond aquamarine burnished black steel 1414.42 NULL 0.0 +Manufacturer#1 almond aquamarine pink moccasin thistle 1632.66 NULL 0.0 +Manufacturer#2 almond antique violet chocolate turquoise 1690.68 NULL 0.0 +Manufacturer#2 almond antique violet turquoise frosted 1800.7 NULL 0.0 +Manufacturer#2 almond aquamarine midnight light salmon 2031.98 NULL 0.0 +Manufacturer#2 almond aquamarine rose maroon antique 1698.66 NULL 0.0 +Manufacturer#2 almond aquamarine sandy cyan gainsboro 1701.6 NULL 0.0 +Manufacturer#3 almond antique chartreuse khaki white 1671.68 NULL 0.0 +Manufacturer#3 almond antique forest lavender goldenrod 1190.27 NULL 0.0 +Manufacturer#3 almond antique metallic orange dim 1410.39 NULL 0.0 +Manufacturer#3 almond antique misty red olive 1922.98 NULL 0.0 +Manufacturer#3 almond antique olive coral navajo 1337.29 NULL 0.0 +Manufacturer#4 almond antique gainsboro frosted violet 1620.67 NULL 0.0 +Manufacturer#4 almond antique violet mint lemon 1375.42 NULL 0.0 +Manufacturer#4 almond aquamarine floral ivory bisque 1206.26 NULL 0.0 +Manufacturer#4 almond aquamarine yellow dodger mint 1844.92 NULL 0.0 +Manufacturer#4 almond azure aquamarine papaya violet 1290.35 NULL 0.0 +Manufacturer#5 almond antique blue firebrick mint 1789.69 NULL 0.0 +Manufacturer#5 almond antique medium spring khaki 1611.66 NULL 0.0 +Manufacturer#5 almond antique sky peru orange 1788.73 NULL 0.0 +Manufacturer#5 almond aquamarine dodger light gainsboro 1018.1 NULL 0.0 +Manufacturer#5 almond azure blanched chiffon midnight 1464.48 NULL 0.0 +PREHOOK: query: -- 3.testLagUDAF +select p_mfgr, p_retailprice, +lag(p_retailprice,1) as l1 over (partition by p_mfgr order by p_name), +lag(p_retailprice,1, p_retailprice) as l2 over (partition by p_mfgr order by p_name), +p_retailprice - lag(p_retailprice,1) +from part +PREHOOK: type: QUERY +PREHOOK: Input: default@part +#### A masked pattern was here #### +POSTHOOK: query: -- 3.testLagUDAF +select p_mfgr, p_retailprice, +lag(p_retailprice,1) as l1 over (partition by p_mfgr order by p_name), +lag(p_retailprice,1, p_retailprice) as l2 over (partition by p_mfgr order by p_name), +p_retailprice - lag(p_retailprice,1) +from part +POSTHOOK: type: QUERY +POSTHOOK: Input: default@part +#### A masked pattern was here #### +Manufacturer#1 1173.15 NULL 1173.15 0.0 +Manufacturer#1 1173.15 1173.15 1173.15 0.0 +Manufacturer#1 1753.76 1173.15 1173.15 580.6099999999999 +Manufacturer#1 1602.59 1753.76 1753.76 -151.17000000000007 +Manufacturer#1 1414.42 1602.59 1602.59 -188.16999999999985 +Manufacturer#1 1632.66 1414.42 1414.42 218.24 +Manufacturer#2 1690.68 NULL 1690.68 0.0 +Manufacturer#2 1800.7 1690.68 1690.68 110.01999999999998 +Manufacturer#2 2031.98 1800.7 1800.7 231.27999999999997 +Manufacturer#2 1698.66 2031.98 2031.98 -333.31999999999994 +Manufacturer#2 1701.6 1698.66 1698.66 2.939999999999827 +Manufacturer#3 1671.68 NULL 1671.68 0.0 +Manufacturer#3 1190.27 1671.68 1671.68 -481.4100000000001 +Manufacturer#3 1410.39 1190.27 1190.27 220.12000000000012 +Manufacturer#3 1922.98 1410.39 1410.39 512.5899999999999 +Manufacturer#3 1337.29 1922.98 1922.98 -585.69 +Manufacturer#4 1620.67 NULL 1620.67 0.0 +Manufacturer#4 1375.42 1620.67 1620.67 -245.25 +Manufacturer#4 1206.26 1375.42 1375.42 -169.16000000000008 +Manufacturer#4 1844.92 1206.26 1206.26 638.6600000000001 +Manufacturer#4 1290.35 1844.92 1844.92 -554.5700000000002 +Manufacturer#5 1789.69 NULL 1789.69 0.0 +Manufacturer#5 1611.66 1789.69 1789.69 -178.02999999999997 +Manufacturer#5 1788.73 1611.66 1611.66 177.06999999999994 +Manufacturer#5 1018.1 1788.73 1788.73 -770.63 +Manufacturer#5 1464.48 1018.1 1018.1 446.38 +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) +from part +PREHOOK: type: QUERY +PREHOOK: Input: default@part +#### A masked pattern was here #### +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) +from part +POSTHOOK: type: QUERY +POSTHOOK: Input: default@part +#### A masked pattern was here #### +Manufacturer#1 almond antique burnished rose metallic 1173.15 NULL 0.0 +Manufacturer#1 almond antique burnished rose metallic 1173.15 1173.15 0.0 +Manufacturer#1 almond antique chartreuse lavender yellow 1753.76 NULL 0.0 +Manufacturer#1 almond antique salmon chartreuse burlywood 1602.59 NULL 0.0 +Manufacturer#1 almond aquamarine burnished black steel 1414.42 NULL 0.0 +Manufacturer#1 almond aquamarine pink moccasin thistle 1632.66 NULL 0.0 +Manufacturer#2 almond antique violet chocolate turquoise 1690.68 NULL 0.0 +Manufacturer#2 almond antique violet turquoise frosted 1800.7 NULL 0.0 +Manufacturer#2 almond aquamarine midnight light salmon 2031.98 NULL 0.0 +Manufacturer#2 almond aquamarine rose maroon antique 1698.66 NULL 0.0 +Manufacturer#2 almond aquamarine sandy cyan gainsboro 1701.6 NULL 0.0 +Manufacturer#3 almond antique chartreuse khaki white 1671.68 NULL 0.0 +Manufacturer#3 almond antique forest lavender goldenrod 1190.27 NULL 0.0 +Manufacturer#3 almond antique metallic orange dim 1410.39 NULL 0.0 +Manufacturer#3 almond antique misty red olive 1922.98 NULL 0.0 +Manufacturer#3 almond antique olive coral navajo 1337.29 NULL 0.0 +Manufacturer#4 almond antique gainsboro frosted violet 1620.67 NULL 0.0 +Manufacturer#4 almond antique violet mint lemon 1375.42 NULL 0.0 +Manufacturer#4 almond aquamarine floral ivory bisque 1206.26 NULL 0.0 +Manufacturer#4 almond aquamarine yellow dodger mint 1844.92 NULL 0.0 +Manufacturer#4 almond azure aquamarine papaya violet 1290.35 NULL 0.0 +Manufacturer#5 almond antique blue firebrick mint 1789.69 NULL 0.0 +Manufacturer#5 almond antique medium spring khaki 1611.66 NULL 0.0 +Manufacturer#5 almond antique sky peru orange 1788.73 NULL 0.0 +Manufacturer#5 almond aquamarine dodger light gainsboro 1018.1 NULL 0.0 +Manufacturer#5 almond azure blanched chiffon midnight 1464.48 NULL 0.0