hive-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From hashut...@apache.org
Subject svn commit: r1453155 - in /hive/branches/ptf-windowing/ql/src: java/org/apache/hadoop/hive/ql/exec/ java/org/apache/hadoop/hive/ql/parse/ java/org/apache/hadoop/hive/ql/udf/generic/ test/queries/clientpositive/ test/results/clientpositive/
Date Wed, 06 Mar 2013 03:16:29 GMT
Author: hashutosh
Date: Wed Mar  6 03:16:29 2013
New Revision: 1453155

URL: http://svn.apache.org/r1453155
Log:
HIVE-4081 [jira] allow expressions with over clause
(Harish Butani via Ashutosh Chauhan)

Summary:
fix lag amt less than part size issue; add wdw expr tests

remove current restriction where only a UDAF invocation is allowed with a windowing specification

Test Plan: included

Reviewers: JIRA, ashutoshc

Reviewed By: ashutoshc

CC: brock

Differential Revision: https://reviews.facebook.net/D9135

Added:
    hive/branches/ptf-windowing/ql/src/test/queries/clientpositive/windowing_expressions.q
    hive/branches/ptf-windowing/ql/src/test/results/clientpositive/windowing_expressions.q.out
Modified:
    hive/branches/ptf-windowing/ql/src/java/org/apache/hadoop/hive/ql/exec/FunctionRegistry.java
    hive/branches/ptf-windowing/ql/src/java/org/apache/hadoop/hive/ql/parse/SemanticAnalyzer.java
    hive/branches/ptf-windowing/ql/src/java/org/apache/hadoop/hive/ql/parse/TypeCheckProcFactory.java
    hive/branches/ptf-windowing/ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDAFLag.java

Modified: hive/branches/ptf-windowing/ql/src/java/org/apache/hadoop/hive/ql/exec/FunctionRegistry.java
URL: http://svn.apache.org/viewvc/hive/branches/ptf-windowing/ql/src/java/org/apache/hadoop/hive/ql/exec/FunctionRegistry.java?rev=1453155&r1=1453154&r2=1453155&view=diff
==============================================================================
--- hive/branches/ptf-windowing/ql/src/java/org/apache/hadoop/hive/ql/exec/FunctionRegistry.java
(original)
+++ hive/branches/ptf-windowing/ql/src/java/org/apache/hadoop/hive/ql/exec/FunctionRegistry.java
Wed Mar  6 03:16:29 2013
@@ -281,8 +281,10 @@ public final class FunctionRegistry {
   static Map<String, PTFFunctionInfo> tableFunctions = Collections.synchronizedMap(new
LinkedHashMap<String, PTFFunctionInfo>());
   static Map<String, WindowFunctionInfo> windowFunctions = Collections.synchronizedMap(new
LinkedHashMap<String, WindowFunctionInfo>());
 
-  public static final ArrayList<String> RANKING_FUNCTIONS = new  ArrayList<String>();
-  public static final ArrayList<String> NAVIGATION_FUNCTIONS = new  ArrayList<String>();
+  /*
+   * UDAFS that only work when the input rows have an order.
+   */
+  public static final HashSet<String> UDAFS_IMPLY_ORDER = new HashSet<String>();
 
   static {
     registerUDF("concat", UDFConcat.class, false);
@@ -535,14 +537,14 @@ public final class FunctionRegistry {
     registerWindowFunction(LEAD_FUNC_NAME, new GenericUDAFLead(), false);
     registerWindowFunction(LAG_FUNC_NAME, new GenericUDAFLag(), false);
 
-    RANKING_FUNCTIONS.add("rank");
-    RANKING_FUNCTIONS.add("dense_rank");
-    RANKING_FUNCTIONS.add("percent_rank");
-
-    NAVIGATION_FUNCTIONS.add(LEAD_FUNC_NAME);
-    NAVIGATION_FUNCTIONS.add(LAG_FUNC_NAME);
-    NAVIGATION_FUNCTIONS.add("first_value");
-    NAVIGATION_FUNCTIONS.add("last_value");
+    UDAFS_IMPLY_ORDER.add("rank");
+    UDAFS_IMPLY_ORDER.add("dense_rank");
+    UDAFS_IMPLY_ORDER.add("percent_rank");
+    UDAFS_IMPLY_ORDER.add("cume_dist");
+    UDAFS_IMPLY_ORDER.add(LEAD_FUNC_NAME);
+    UDAFS_IMPLY_ORDER.add(LAG_FUNC_NAME);
+    UDAFS_IMPLY_ORDER.add("first_value");
+    UDAFS_IMPLY_ORDER.add("last_value");
 
     registerTableFunction(NOOP_TABLE_FUNCTION, NoopResolver.class);
     registerTableFunction(NOOP_MAP_TABLE_FUNCTION, NoopWithMapResolver.class);
@@ -1475,17 +1477,15 @@ public final class FunctionRegistry {
     windowFunctions.put(name.toLowerCase(), wInfo);
   }
 
-  public static boolean isWindowFunction(String name)
-  {
-     WindowFunctionInfo wFInfo = windowFunctions.get(name.toLowerCase());
-     return wFInfo != null;
-  }
-
   public static WindowFunctionInfo getWindowFunctionInfo(String name)
   {
     return windowFunctions.get(name.toLowerCase());
   }
 
+  public static boolean impliesOrder(String functionName) {
+    return functionName == null ? false : UDAFS_IMPLY_ORDER.contains(functionName.toLowerCase());
+  }
+
   static void registerHiveUDAFsAsWindowFunctions()
   {
     Set<String> fNames = getFunctionNames();

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=1453155&r1=1453154&r2=1453155&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
Wed Mar  6 03:16:29 2013
@@ -21,7 +21,6 @@ package org.apache.hadoop.hive.ql.parse;
 import java.io.IOException;
 import java.io.Serializable;
 import java.util.ArrayList;
-import java.util.Collection;
 import java.util.HashMap;
 import java.util.HashSet;
 import java.util.Iterator;
@@ -86,6 +85,7 @@ import org.apache.hadoop.hive.ql.exec.Ta
 import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
 import org.apache.hadoop.hive.ql.exec.UnionOperator;
 import org.apache.hadoop.hive.ql.exec.Utilities;
+import org.apache.hadoop.hive.ql.exec.WindowFunctionInfo;
 import org.apache.hadoop.hive.ql.hooks.ReadEntity;
 import org.apache.hadoop.hive.ql.hooks.WriteEntity;
 import org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;
@@ -390,27 +390,26 @@ public class SemanticAnalyzer extends Ba
   private LinkedHashMap<String, ASTNode> doPhase1GetAggregationsFromSelect(
       ASTNode selExpr, QB qb, String dest) {
 
-    ArrayList<ASTNode> wdwingexprNodes = null;
-    if ( queryProperties.hasWindowing() && qb.getWindowingSpec(dest) != null ) {
-      WindowingSpec wdwSpec = qb.getWindowingSpec(dest);
-      HashMap<String, WindowExpressionSpec> aliasToWdwExprs = wdwSpec.getAliasToWdwExpr();
-      wdwingexprNodes = new ArrayList<ASTNode>();
-      for (WindowExpressionSpec exprSpec : aliasToWdwExprs.values()) {
-        wdwingexprNodes.add(exprSpec.getExpression());
-      }
-    }
-
     // Iterate over the selects search for aggregation Trees.
     // Use String as keys to eliminate duplicate trees.
     LinkedHashMap<String, ASTNode> aggregationTrees = new LinkedHashMap<String,
ASTNode>();
     for (int i = 0; i < selExpr.getChildCount(); ++i) {
       ASTNode sel = (ASTNode) selExpr.getChild(i);
-      if(queryProperties.hasWindowing() && qb.getWindowingSpec(dest) != null ){
-        if(wdwingexprNodes.contains(sel.getChild(0))){
-          continue;
+      doPhase1GetAllAggregations((ASTNode) sel.getChild(0), aggregationTrees);
+    }
+
+    /*
+     * remove any aggregation to be handled by Windowing.
+     */
+    if ( queryProperties.hasWindowing() && qb.getWindowingSpec(dest) != null ) {
+      HashMap<String, ASTNode> aliasToWdwExprs = qb.getParseInfo().getWindowingExprsForClause(dest);
+      LinkedHashMap<String, ASTNode> aggTreesMinusWindowing = new LinkedHashMap<String,
ASTNode>();
+      for(Map.Entry<String,ASTNode> entry : aggregationTrees.entrySet()) {
+        if ( !aliasToWdwExprs.containsKey(entry.getKey())) {
+          aggTreesMinusWindowing.put(entry.getKey(), entry.getValue());
         }
       }
-      doPhase1GetAllAggregations((ASTNode) sel.getChild(0), aggregationTrees);
+      aggregationTrees = aggTreesMinusWindowing;
     }
     return aggregationTrees;
   }
@@ -9924,18 +9923,10 @@ public class SemanticAnalyzer extends Ba
 
   /*
    * - A Select Item form is: ^(TOK_SELEXPR selectExpression Identifier* window_specification?)
-   * What constitutes a Windowing Select Expression:
-   * 1.
-   * - It must be a UDAF function invocation
-   * - It must have a Windowing Spec
-   * 2.
-   * - It must be a Ranking or Navigation Function invocation which is also a UDAF function
-   *   (so no lead/lag)
-   * 3.
-   * - It must be a UDAF function invocation
-   * - must have an argument which invokes Lead/Lag
-   * Error Expressions are:
-   * - a Window Spec without a UDAF invocation: we don't support expressions i Windowing.
+   * What makes a UDAF invocation a Windowing Function invocation:
+   * 1. It appears in a SelectExpr that as a WindowSpec
+   * 2. It is a UDAF that implies order (FunctionRegistry.impliesOrder)
+   * 3. It contains lead/lag UDF invocations in its args.
    */
   private boolean checkAndExtractWindowFunctionsInSelect(QB qb, ASTNode selectExpr, String
dest)
       throws SemanticException {
@@ -9944,95 +9935,136 @@ public class SemanticAnalyzer extends Ba
     ASTNode windowSpec = (ASTNode) selectExpr.getChild(childCount - 1);
 
     boolean hasWindowSpec = windowSpec.getType() == HiveParser.TOK_WINDOWSPEC;
-    ASTNode function = (ASTNode) selectExpr.getChild(0);
-    boolean isFunction = ( function.getType() == HiveParser.TOK_FUNCTION ||
-        function.getType() == HiveParser.TOK_FUNCTIONDI ||
-        function.getType() == HiveParser.TOK_FUNCTIONSTAR );
 
-    if ( !isFunction ) {
+    ArrayList<ASTNode> functions =
+        extractWindowingUDAFs((ASTNode) selectExpr.getChild(0), !hasWindowSpec);
+    if ( functions.size() == 0 ) {
       return false;
     }
 
-    String fnName = function.getChild(0).getText().toLowerCase();
-    if(!FunctionRegistry.isWindowFunction(fnName)) {
-      // Its either UDF, UDTF or lead or lag function.
-      if (hasWindowSpec){
-        // We currently supports windowing only with UDAFs, so if there is a windowing spec
-        // thats the error condition.
-        throw new SemanticException(generateErrorMessage(selectExpr,
-          "Currently windowing Specification can only be associated with a UDAF " +
-          "invocation or navigation functions"));
-      } else {
+    /*
+     * 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;
       }
     }
 
-    boolean hasLLArgs = false;
-    boolean isRankingOrNavFunction = FunctionRegistry.RANKING_FUNCTIONS.contains(fnName)
||
-        FunctionRegistry.NAVIGATION_FUNCTIONS.contains(fnName);
+    WindowingSpec spec = qb.getWindowingSpec(dest);
+    if(spec == null) {
+      queryProperties.setHasWindowing(true);
+      spec = new WindowingSpec();
+      qb.addDestToWindowingSpec(dest, spec);
+    }
 
-    /*
-     * 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;
+    HashMap<String, ASTNode> wExprsInDest = qb.getParseInfo().getWindowingExprsForClause(dest);
+    int wColIdx = spec.getWindowExpressions() == null ? 0 : spec.getWindowExpressions().size();
+    for(ASTNode function : functions) {
+      WindowFunctionSpec wFnSpec = processWindowFunction(function,
+          hasWindowSpec ? windowSpec : null);
+
+      /*
+       * If this is a duplicate invocation of a function; don't add to WindowingSpec.
+       */
+      if ( wExprsInDest != null &&
+          wExprsInDest.containsKey(wFnSpec.getExpression().toStringTree())) {
+        continue;
+      }
+      wFnSpec.setAlias("_wcol" + wColIdx++);
+      spec.addWindowFunction(wFnSpec);
+      qb.getParseInfo().addWindowingExprToClause(dest, wFnSpec.getExpression());
     }
+    return true;
+  }
 
-    /*
-     * If Windowing Function has LeadLag expression in its args,
-     * then it will be handled by WindowingTabFunc.
-     */
-    if (!isRankingOrNavFunction ) {
-      // but lead/lag are not supported as an argument of ranking or navigation function.
-      TreeWizard tw = new TreeWizard(ParseDriver.adaptor, HiveParser.tokenNames);
-      CheckLeadLagInSelectExprs checkLLFunctions = new CheckLeadLagInSelectExprs(qb, dest);
-      for(int i=1; !hasLLArgs && i < function.getChildCount(); i++) {
+  /*
+   * return the UDAFs within the expressionTree.
+   * If implyOrder is true, then only return the invocations that:
+   * - are for UDAFs that implyOrder (FunctionRegistry.implyOrder)
+   * - or contain a Lead/Lag UDF invocation in their arguments
+   * If implyOrder is false, then return all UDAF invocations.
+   */
+  private ArrayList<ASTNode> extractWindowingUDAFs(ASTNode expressionTree, boolean
implyOrder) {
+    ArrayList<ASTNode> aggregations = new ArrayList<ASTNode>();
+    extractWindowingUDAFs(expressionTree, aggregations);
+    if (!implyOrder) {
+      return aggregations;
+    }
+    ArrayList<ASTNode> wdwUDAFs = new ArrayList<ASTNode>();
+    for(ASTNode function : aggregations) {
+      String fnName = function.getChild(0).getText().toLowerCase();
+      if ( FunctionRegistry.impliesOrder(fnName)) {
+        wdwUDAFs.add(function);
+        continue;
+      }
+      boolean hasLLInArgs = false;
+      for(int i=1; i < function.getChildCount(); i++) {
         ASTNode child = (ASTNode) function.getChild(i);
-        tw.visit(child, HiveParser.TOK_FUNCTION, checkLLFunctions);
-        if ( checkLLFunctions.isError() ) {
-          throw new SemanticException(generateErrorMessage(selectExpr,
-              checkLLFunctions.getErrString()));
+        hasLLInArgs = containsLeadLagUDF(child);
+        if (hasLLInArgs) {
+          break;
         }
-        hasLLArgs = checkLLFunctions.hasLeadLagExprs();
+      }
+      if (hasLLInArgs) {
+        wdwUDAFs.add(function);
       }
     }
+    return wdwUDAFs;
+  }
 
-    if (hasWindowSpec || isRankingOrNavFunction || hasLLArgs) {
-      /*
-       * @revisit: what should I do if there are more than 3 children;
-       * i.e. more than one Identifier.
-       */
+  private void extractWindowingUDAFs(ASTNode expressionTree,
+      ArrayList<ASTNode> aggregations) {
+    int exprTokenType = expressionTree.getToken().getType();
+    if (exprTokenType == HiveParser.TOK_FUNCTION
+        || exprTokenType == HiveParser.TOK_FUNCTIONDI
+        || exprTokenType == HiveParser.TOK_FUNCTIONSTAR) {
+      assert (expressionTree.getChildCount() != 0);
+      if (expressionTree.getChild(0).getType() == HiveParser.Identifier) {
+        String functionName = unescapeIdentifier(expressionTree.getChild(0)
+            .getText());
+        WindowFunctionInfo fi = FunctionRegistry.getWindowFunctionInfo(functionName);
+        if (fi != null) {
+          aggregations.add(expressionTree);
+          return;
+        }
+      }
+    }
+    for (int i = 0; i < expressionTree.getChildCount(); i++) {
+      extractWindowingUDAFs((ASTNode) expressionTree.getChild(i),
+          aggregations);
+    }
+  }
 
-      String alias = null;
-      ASTNode secondChildNode = (ASTNode) selectExpr.getChild(1);
-        if ( childCount > 1 && secondChildNode.getType() == HiveParser.Identifier)
{
-          alias = secondChildNode.getText();
+  private boolean containsLeadLagUDF(ASTNode expressionTree) {
+    int exprTokenType = expressionTree.getToken().getType();
+    if (exprTokenType == HiveParser.TOK_FUNCTION) {
+      assert (expressionTree.getChildCount() != 0);
+      if (expressionTree.getChild(0).getType() == HiveParser.Identifier) {
+        String functionName = unescapeIdentifier(expressionTree.getChild(0)
+            .getText());
+        functionName = functionName.toLowerCase();
+        if ( FunctionRegistry.LAG_FUNC_NAME.equals(functionName) ||
+            FunctionRegistry.LEAD_FUNC_NAME.equals(functionName)
+            ) {
+          return true;
         }
-        else {
-          /*
-           * generate an alias using the function AST tree.
-           * - calling getColAlias with a null InputResolver and null defaultName.
-           * - so includeFunctionName should be true.
-           */
-          String[] colAlias = getColAlias(function, null, null, true, -1);
-          alias = colAlias[1];
-         }
-
-      WindowingSpec spec = qb.getWindowingSpec(dest);
-      if(spec == null) {
-        queryProperties.setHasWindowing(true);
-        spec = new WindowingSpec();
-        qb.addDestToWindowingSpec(dest, spec);
       }
-      WindowFunctionSpec wFnSpec = processWindowFunction(function,
-          hasWindowSpec ? windowSpec : null);
-      wFnSpec.setAlias(alias);
-      spec.addWindowFunction(wFnSpec);
-      qb.getParseInfo().addWindowingExprToClause(dest, wFnSpec.getExpression());
-      return true;
+    }
+    for (int i = 0; i < expressionTree.getChildCount(); i++) {
+      if ( containsLeadLagUDF((ASTNode) expressionTree.getChild(i))) {
+        return true;
+      }
     }
     return false;
   }
@@ -10051,6 +10083,12 @@ public class SemanticAnalyzer extends Ba
       this.dest = dest;
     }
 
+    void reset() {
+      hasLeadLagExprs = false;
+      error = false;
+      errString = null;
+    }
+
     @Override
     public void visit(Object t, Object parent, int childIndex, Map labels)
     {
@@ -10074,15 +10112,6 @@ public class SemanticAnalyzer extends Ba
       {
         hasLeadLagExprs = true;
       }
-      else if ( FunctionRegistry.NAVIGATION_FUNCTIONS.contains(fnName)) {
-        error = true;
-        errString = "Currently you cannot use a Navigation Functions: " +
-        		"first_value, last_value in expressions";
-      }
-      else if ( FunctionRegistry.RANKING_FUNCTIONS.contains(fnName)) {
-        error = true;
-        errString = "Currently you cannot use a Ranking Functions: in expressions";
-      }
     }
 
     public boolean hasLeadLagExprs() {
@@ -10140,6 +10169,7 @@ 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() ) {
@@ -10164,16 +10194,13 @@ public class SemanticAnalyzer extends Ba
           /*
             * @revisit what if there are multiple Identifiers(lateral view)
            */
-          if ( childCount < 2 ) {
-            /*
-             * generate an alias using the function AST tree.
-             * - calling getColAlias with a null InputResolver and null defaultName.
-             * - so includeFunctionName should be true.
-             */
-            alias = getColAlias(expr, null, null, true, -1)[1];
+          if ( childCount >= 2 ) {
+            alias = getColAlias(selectExpr, null, null, true, -1)[1];
           }
           else {
-            alias = selectExpr.getChild(1).getText();
+            int wColIdx = spec.getWindowExpressions() == null ? 0 :
+              spec.getWindowExpressions().size();
+            alias = "_wcol" + wColIdx;
           }
           spec.addExpression(expr, alias);
           qb.getParseInfo().addWindowingExprToClause(dest, expr);
@@ -10529,30 +10556,30 @@ public class SemanticAnalyzer extends Ba
    */
   private void moveaggregationExprsToWindowingSpec(QB currQB, String dest)
       throws SemanticException {
-      HashMap<String, ASTNode> aggregationTree =
+      HashMap<String, ASTNode> aggregations =
           currQB.getParseInfo().getAggregationExprsForClause(dest);
-      if((aggregationTree != null) && !(aggregationTree.isEmpty()) ){
+      if((aggregations != null) && !(aggregations.isEmpty()) ){
         WindowingSpec spec = currQB.getWindowingSpec(dest);
         if(spec == null){
           queryProperties.setHasWindowing(true);
           spec = new WindowingSpec();
           currQB.addDestToWindowingSpec(dest, spec);
         }
-        Collection<ASTNode> aggrExprs = aggregationTree.values();
-        for (ASTNode expr : aggrExprs) {
-          String alias = currQB.getParseInfo().getExprToColumnAlias(expr);
-          if ( alias == null ) {
-            /*
-             * generate an alias using the function AST tree.
-             * - calling getColAlias with a null InputResolver and null defaultName.
-             * - so includeFunctionName should be true.
-             */
-            String[] colAlias = getColAlias(expr, null, null, true, -1);
-            alias = colAlias[1];
-          }
-          WindowFunctionSpec wFn = processWindowFunction(expr, null);
-          wFn.setAlias(alias);
-          spec.addWindowFunction(wFn);
+        HashMap<String, ASTNode> wExprsInDest = qb.getParseInfo().getWindowingExprsForClause(dest);
+        int wColIdx = spec.getWindowExpressions() == null ? 0 : spec.getWindowExpressions().size();
+        for (ASTNode expr : aggregations.values()) {
+          WindowFunctionSpec wFnSpec = processWindowFunction(expr, null);
+          wFnSpec.setAlias("_wcol" + wColIdx++);
+
+          /*
+           * If this is a duplicate invocation of a function; don't add to WindowingSpec.
+           */
+          if ( wExprsInDest != null &&
+              wExprsInDest.containsKey(wFnSpec.getExpression().toStringTree())) {
+            continue;
+          }
+          spec.addWindowFunction(wFnSpec);
+          currQB.getParseInfo().addWindowingExprToClause(dest, wFnSpec.getExpression());
           currQB.getParseInfo().getAllExprToColumnAlias().remove(expr);
         }
         currQB.getParseInfo().clearAggregationExprsForClause(dest);

Modified: hive/branches/ptf-windowing/ql/src/java/org/apache/hadoop/hive/ql/parse/TypeCheckProcFactory.java
URL: http://svn.apache.org/viewvc/hive/branches/ptf-windowing/ql/src/java/org/apache/hadoop/hive/ql/parse/TypeCheckProcFactory.java?rev=1453155&r1=1453154&r2=1453155&view=diff
==============================================================================
--- hive/branches/ptf-windowing/ql/src/java/org/apache/hadoop/hive/ql/parse/TypeCheckProcFactory.java
(original)
+++ hive/branches/ptf-windowing/ql/src/java/org/apache/hadoop/hive/ql/parse/TypeCheckProcFactory.java
Wed Mar  6 03:16:29 2013
@@ -486,6 +486,7 @@ public final class TypeCheckProcFactory 
     static HashMap<Integer, String> specialUnaryOperatorTextHashMap;
     static HashMap<Integer, String> specialFunctionTextHashMap;
     static HashMap<Integer, String> conversionFunctionTextHashMap;
+    static HashSet<Integer> windowingTokens;
     static {
       specialUnaryOperatorTextHashMap = new HashMap<Integer, String>();
       specialUnaryOperatorTextHashMap.put(HiveParser.PLUS, "positive");
@@ -516,6 +517,22 @@ public final class TypeCheckProcFactory 
           serdeConstants.TIMESTAMP_TYPE_NAME);
       conversionFunctionTextHashMap.put(HiveParser.TOK_DECIMAL,
           serdeConstants.DECIMAL_TYPE_NAME);
+
+      windowingTokens = new HashSet<Integer>();
+      windowingTokens.add(HiveParser.KW_OVER);
+      windowingTokens.add(HiveParser.TOK_PARTITIONINGSPEC);
+      windowingTokens.add(HiveParser.TOK_DISTRIBUTEBY);
+      windowingTokens.add(HiveParser.TOK_SORTBY);
+      windowingTokens.add(HiveParser.TOK_CLUSTERBY);
+      windowingTokens.add(HiveParser.TOK_WINDOWSPEC);
+      windowingTokens.add(HiveParser.TOK_WINDOWRANGE);
+      windowingTokens.add(HiveParser.TOK_WINDOWVALUES);
+      windowingTokens.add(HiveParser.KW_UNBOUNDED);
+      windowingTokens.add(HiveParser.KW_PRECEDING);
+      windowingTokens.add(HiveParser.KW_FOLLOWING);
+      windowingTokens.add(HiveParser.KW_CURRENT);
+      windowingTokens.add(HiveParser.TOK_TABSORTCOLNAMEASC);
+      windowingTokens.add(HiveParser.TOK_TABSORTCOLNAMEDESC);
     }
 
     public static boolean isRedundantConversionFunction(ASTNode expr,
@@ -862,6 +879,20 @@ public final class TypeCheckProcFactory 
 
       ASTNode expr = (ASTNode) nd;
 
+      /*
+       * A Windowing specification get added as a child to a UDAF invocation to distinguish
it
+       * from similar UDAFs but on different windows.
+       * The UDAF is translated to a WindowFunction invocation in the PTFTranslator.
+       * So here we just return null for tokens that appear in a Window Specification.
+       * When the traversal reaches up to the UDAF invocation its ExprNodeDesc is build using
the
+       * ColumnInfo in the InputRR. This is similar to how UDAFs are handled in Select lists.
+       * The difference is that there is translation for Window related tokens, so we just
+       * return null;
+       */
+      if ( windowingTokens.contains(expr.getType())) {
+        return null;
+      }
+
       // If the first child is a TOK_TABLE_OR_COL, and nodeOutput[0] is NULL,
       // and the operator is a DOT, then it's a table column reference.
       if (expr.getType() == HiveParser.DOT

Modified: hive/branches/ptf-windowing/ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDAFLag.java
URL: http://svn.apache.org/viewvc/hive/branches/ptf-windowing/ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDAFLag.java?rev=1453155&r1=1453154&r2=1453155&view=diff
==============================================================================
--- hive/branches/ptf-windowing/ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDAFLag.java
(original)
+++ hive/branches/ptf-windowing/ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDAFLag.java
Wed Mar  6 03:16:29 2013
@@ -81,6 +81,15 @@ public class GenericUDAFLag extends Gene
     }
 
     public Object terminate() {
+
+      /*
+       * if partition is smaller than the lagAmt;
+       * the entire partition is in lagValues.
+       */
+      if ( values.size() < lagAmt ) {
+        return lagValues;
+      }
+
       int lastIdx = values.size() - 1;
       for(int i = 0; i < lagAmt; i++) {
         values.remove(lastIdx - i);

Added: 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=1453155&view=auto
==============================================================================
--- hive/branches/ptf-windowing/ql/src/test/queries/clientpositive/windowing_expressions.q
(added)
+++ hive/branches/ptf-windowing/ql/src/test/queries/clientpositive/windowing_expressions.q
Wed Mar  6 03:16:29 2013
@@ -0,0 +1,56 @@
+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;
+
+drop table over10k;
+
+create table over10k(
+           t tinyint,
+           si smallint,
+           i int,
+           b bigint,
+           f float,
+           d double,
+           bo boolean,
+           s string,
+	   ts timestamp, 
+           dec decimal,  
+           bin binary)
+       row format delimited
+       fields terminated by '|';
+
+load data local inpath '../data/files/over10k' into table over10k;
+
+select p_mfgr, p_retailprice, p_size,
+round(sum(p_retailprice),2) = round((sum(lag(p_retailprice,1)) - first_value(p_retailprice))
+ last_value(p_retailprice),2) ,
+max(p_retailprice) - min(p_retailprice) = last_value(p_retailprice) - first_value(p_retailprice)
+from part
+distribute by p_mfgr
+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
+distribute by p_mfgr
+sort by p_retailprice;
+
+select s, si, f, si - lead(f, 3) over (partition by t order by bo desc) from over10k limit
100;
+select s, i, i - lead(i, 3, 0) over (partition by si order by i) from over10k limit 100;
+select s, si, d, si - lag(d, 3) over (partition by b order by si) from over10k limit 100;
+select s, lag(s, 3, 'fred') over (partition by f order by b) from over10k limit 100;
\ No newline at end of file

Added: 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=1453155&view=auto
==============================================================================
--- hive/branches/ptf-windowing/ql/src/test/results/clientpositive/windowing_expressions.q.out
(added)
+++ hive/branches/ptf-windowing/ql/src/test/results/clientpositive/windowing_expressions.q.out
Wed Mar  6 03:16:29 2013
@@ -0,0 +1,602 @@
+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: drop table over10k
+PREHOOK: type: DROPTABLE
+POSTHOOK: query: drop table over10k
+POSTHOOK: type: DROPTABLE
+PREHOOK: query: create table over10k(
+           t tinyint,
+           si smallint,
+           i int,
+           b bigint,
+           f float,
+           d double,
+           bo boolean,
+           s string,
+	   ts timestamp, 
+           dec decimal,  
+           bin binary)
+       row format delimited
+       fields terminated by '|'
+PREHOOK: type: CREATETABLE
+POSTHOOK: query: create table over10k(
+           t tinyint,
+           si smallint,
+           i int,
+           b bigint,
+           f float,
+           d double,
+           bo boolean,
+           s string,
+	   ts timestamp, 
+           dec decimal,  
+           bin binary)
+       row format delimited
+       fields terminated by '|'
+POSTHOOK: type: CREATETABLE
+POSTHOOK: Output: default@over10k
+PREHOOK: query: load data local inpath '../data/files/over10k' into table over10k
+PREHOOK: type: LOAD
+PREHOOK: Output: default@over10k
+POSTHOOK: query: load data local inpath '../data/files/over10k' into table over10k
+POSTHOOK: type: LOAD
+POSTHOOK: Output: default@over10k
+PREHOOK: query: select p_mfgr, p_retailprice, p_size,
+round(sum(p_retailprice),2) = round((sum(lag(p_retailprice,1)) - first_value(p_retailprice))
+ last_value(p_retailprice),2) ,
+max(p_retailprice) - min(p_retailprice) = last_value(p_retailprice) - first_value(p_retailprice)
+from part
+distribute by p_mfgr
+sort by p_retailprice
+PREHOOK: type: QUERY
+PREHOOK: Input: default@part
+#### A masked pattern was here ####
+POSTHOOK: query: select p_mfgr, p_retailprice, p_size,
+round(sum(p_retailprice),2) = round((sum(lag(p_retailprice,1)) - first_value(p_retailprice))
+ last_value(p_retailprice),2) ,
+max(p_retailprice) - min(p_retailprice) = last_value(p_retailprice) - first_value(p_retailprice)
+from part
+distribute by p_mfgr
+sort by p_retailprice
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@part
+#### A masked pattern was here ####
+Manufacturer#1	1173.15	2	true	true
+Manufacturer#1	1173.15	2	true	true
+Manufacturer#1	1414.42	28	true	true
+Manufacturer#1	1602.59	6	true	true
+Manufacturer#1	1632.66	42	true	true
+Manufacturer#1	1753.76	34	true	true
+Manufacturer#2	1690.68	14	true	true
+Manufacturer#2	1698.66	25	true	true
+Manufacturer#2	1701.6	18	true	true
+Manufacturer#2	1800.7	40	true	true
+Manufacturer#2	2031.98	2	true	true
+Manufacturer#3	1190.27	14	true	true
+Manufacturer#3	1337.29	45	true	true
+Manufacturer#3	1410.39	19	true	true
+Manufacturer#3	1671.68	17	true	true
+Manufacturer#3	1922.98	1	true	true
+Manufacturer#4	1206.26	27	true	true
+Manufacturer#4	1290.35	12	true	true
+Manufacturer#4	1375.42	39	true	true
+Manufacturer#4	1620.67	10	true	true
+Manufacturer#4	1844.92	7	true	true
+Manufacturer#5	1018.1	46	true	true
+Manufacturer#5	1464.48	23	true	true
+Manufacturer#5	1611.66	6	true	true
+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
+distribute by p_mfgr
+sort by p_retailprice
+PREHOOK: type: QUERY
+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
+distribute by p_mfgr
+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
+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
+#### A masked pattern was here ####
+POSTHOOK: query: select s, si, f, si - lead(f, 3) over (partition by t order by bo desc)
from over10k limit 100
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@over10k
+#### A masked pattern was here ####
+sarah polk	325	24.97	295.89
+mike young	504	28.26	418.22
+xavier johnson	419	25.68	414.72
+irene steinbeck	420	29.11	417.0
+calvin quirinius	316	85.78	249.85
+gabriella hernandez	457	4.28	403.72
+victor polk	370	3.0	313.83002
+calvin hernandez	446	66.15	359.9
+irene robinson	442	53.28	425.28
+david xylophone	389	56.17	362.56
+jessica ichabod	309	86.1	230.69
+mike hernandez	491	16.72	477.61
+fred robinson	409	26.44	358.62
+fred davidson	507	78.31	471.26
+tom quirinius	492	13.39	426.86
+priscilla king	285	50.38	230.56
+luke thompson	414	35.74	331.49
+mike quirinius	300	65.14	223.07
+irene van buren	454	54.44	436.25
+quinn quirinius	352	82.51	282.23
+zach nixon	386	76.93	301.56
+holly zipper	506	17.75	440.39
+gabriella brown	270	69.77	232.81
+victor van buren	263	84.44	190.03
+yuri laertes	450	65.61	392.28
+luke polk	480	37.19	432.41
+irene ichabod	474	72.97	434.39
+mike zipper	441	57.72	354.41998
+alice robinson	423	47.59	404.01
+calvin hernandez	507	39.61	493.04
+ulysses ovid	500	86.58	409.14
+wendy laertes	405	18.99	322.11
+bob laertes	487	13.96	395.69
+irene white	261	90.86	250.33
+irene davidson	495	82.89	488.83
+sarah garcia	399	91.31	307.88
+bob laertes	376	10.67	360.62
+zach carson	381	6.17	350.7
+luke falkner	441	91.12	409.34
+victor young	263	15.38	185.18
+alice brown	425	30.3	417.14
+gabriella king	474	31.66	444.05
+sarah nixon	378	77.82	328.47
+calvin laertes	329	7.86	317.5
+fred polk	295	29.95	276.57
+tom ichabod	292	49.53	287.59
+victor thompson	294	11.5	226.78
+luke laertes	428	18.43	414.83
+yuri thompson	267	4.41	236.2
+katie king	396	67.22	320.43
+gabriella nixon	405	13.17	394.26
+sarah johnson	337	30.8	240.63
+calvin carson	447	75.57	384.23
+wendy davidson	449	10.74	403.83002
+fred underhill	489	96.37	411.1
+jessica king	435	62.77	365.2
+luke xylophone	431	45.17	383.12
+fred underhill	473	77.9	375.88
+gabriella van buren	276	69.8	273.65
+victor king	444	47.88	392.91998
+alice garcia	486	97.12	437.11
+ethan polk	260	2.35	239.64
+gabriella underhill	329	51.08	292.44
+yuri carson	489	48.89	443.61
+holly quirinius	363	20.36	279.69
+jessica garcia	400	36.56	354.9
+mike johnson	278	45.39	198.86
+xavier falkner	434	83.31	354.79
+sarah laertes	262	45.1	250.61
+victor underhill	434	79.14	372.59
+tom johnson	320	79.21	237.89
+david underhill	311	11.39	276.22
+yuri hernandez	413	61.41	391.86
+tom davidson	336	82.11	309.09
+ethan steinbeck	461	34.78	384.96
+priscilla miller	267	21.14	168.91
+mike johnson	286	26.91	203.20999
+luke young	276	76.04	195.76999
+ethan king	279	98.09	215.19
+fred van buren	501	82.79	441.02
+alice nixon	299	80.23	246.4
+xavier robinson	469	63.81	433.31
+bob miller	484	59.98	434.58002
+irene quirinius	486	52.6	441.41998
+ethan underhill	299	35.69	258.51
+wendy polk	352	49.42	264.9
+gabriella garcia	395	44.58	317.65
+oscar laertes	432	40.49	429.61
+gabriella ichabod	403	87.1	368.52
+sarah carson	493	77.35	415.3
+david white	432	2.39	355.13
+quinn thompson	372	34.48	277.82
+rachel underhill	282	77.7	278.48
+holly laertes	306	76.87	239.05
+quinn carson	361	94.18	352.49
+mike nixon	434	3.52	352.15
+sarah falkner	360	66.95	355.31
+wendy xylophone	274	8.51	245.64
+tom allen	373	81.85	308.18
+irene laertes	402	4.69	329.72
+PREHOOK: query: select s, i, i - lead(i, 3, 0) over (partition by si order by i) from over10k
limit 100
+PREHOOK: type: QUERY
+PREHOOK: Input: default@over10k
+#### A masked pattern was here ####
+POSTHOOK: query: select s, i, i - lead(i, 3, 0) over (partition by si order by i) from over10k
limit 100
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@over10k
+#### A masked pattern was here ####
+wendy garcia	65540	-18
+ethan thompson	65543	-20
+zach nixon	65549	-31
+alice robinson	65558	-28
+wendy nixon	65563	-33
+victor robinson	65580	-19
+ethan falkner	65586	-18
+victor davidson	65596	-17
+xavier quirinius	65599	-14
+fred quirinius	65604	-11
+xavier van buren	65613	-3
+nick zipper	65613	-7
+victor johnson	65615	-12
+alice ovid	65616	-24
+xavier ovid	65620	-23
+ulysses white	65627	-24
+sarah white	65640	-13
+calvin young	65643	-25
+victor thompson	65651	-42
+calvin johnson	65653	-53
+irene polk	65668	-45
+zach underhill	65693	-38
+quinn hernandez	65706	-27
+rachel ovid	65713	-24
+gabriella falkner	65731	-7
+zach white	65733	-8
+fred hernandez	65737	-7
+rachel ellison	65738	-6
+oscar steinbeck	65741	-6
+tom allen	65744	-8
+alice ellison	65744	-19
+quinn quirinius	65747	-31
+victor hernandez	65752	-26
+holly xylophone	65763	-26
+david davidson	65778	65778
+ulysses young	65778	65778
+sarah brown	65789	65789
+xavier brown	65541	-16
+zach hernandez	65542	-18
+katie ichabod	65547	-19
+oscar young	65557	-15
+holly white	65560	-14
+priscilla laertes	65566	-9
+ethan king	65572	-6
+zach hernandez	65574	-10
+oscar thompson	65575	-13
+victor xylophone	65578	-16
+gabriella ellison	65584	-26
+nick quirinius	65588	-22
+holly robinson	65594	-18
+yuri brown	65610	-16
+alice xylophone	65610	-21
+sarah hernandez	65612	-26
+katie garcia	65626	-28
+jessica laertes	65631	-23
+ethan underhill	65638	-17
+priscilla thompson	65654	-37
+irene young	65654	-40
+luke quirinius	65655	-44
+david brown	65691	-20
+luke falkner	65694	-18
+priscilla miller	65699	-20
+rachel robinson	65711	-9
+ethan polk	65712	-10
+wendy brown	65719	-13
+mike underhill	65720	-18
+zach underhill	65722	-26
+nick zipper	65732	-20
+fred brown	65738	-18
+ulysses young	65748	-23
+nick davidson	65752	-19
+fred zipper	65756	-15
+yuri nixon	65771	-10
+zach zipper	65771	-19
+zach hernandez	65771	65771
+alice underhill	65781	65781
+oscar laertes	65790	65790
+sarah zipper	65546	-19
+luke ovid	65551	-17
+bob falkner	65551	-17
+katie allen	65565	-4
+zach steinbeck	65568	-5
+nick falkner	65568	-11
+oscar van buren	65569	-13
+gabriella young	65573	-11
+jessica ichabod	65579	-24
+david garcia	65582	-24
+nick xylophone	65584	-27
+calvin johnson	65603	-14
+xavier zipper	65606	-50
+alice nixon	65611	-58
+jessica laertes	65617	-62
+fred king	65656	-61
+priscilla underhill	65669	-48
+priscilla zipper	65679	-45
+sarah polk	65717	-11
+nick king	65717	-17
+irene quirinius	65724	-28
+tom laertes	65728	-25
+yuri johnson	65734	-27
+PREHOOK: query: select s, si, d, si - lag(d, 3) over (partition by b order by si) from over10k
limit 100
+PREHOOK: type: QUERY
+PREHOOK: Input: default@over10k
+#### A masked pattern was here ####
+POSTHOOK: query: select s, si, d, si - lag(d, 3) over (partition by b order by si) from over10k
limit 100
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@over10k
+#### A masked pattern was here ####
+jessica ellison	262	30.41	NULL
+david young	266	45.12	NULL
+jessica steinbeck	274	2.15	NULL
+david zipper	275	43.45	244.59
+zach nixon	283	15.95	237.88
+holly allen	285	24.37	282.85
+irene garcia	292	33.54	248.55
+ulysses xylophone	292	44.66	276.05
+irene van buren	309	35.81	284.63
+sarah miller	312	6.65	278.46
+victor garcia	312	39.14	267.34000000000003
+ethan ichabod	319	29.4	283.19
+wendy falkner	322	10.02	315.35
+oscar miller	324	25.95	284.86
+david ovid	332	28.34	302.6
+alice zipper	333	3.38	322.98
+yuri nixon	333	8.28	307.05
+ulysses nixon	335	18.48	306.66
+david ovid	336	9.36	332.62
+calvin falkner	337	17.63	328.72
+katie quirinius	349	11.3	330.52
+quinn miller	351	22.46	341.64
+victor xylophone	357	38.58	339.37
+ethan garcia	368	9.2	356.7
+nick steinbeck	395	37.54	372.54
+ulysses ichabod	415	47.61	376.42
+rachel thompson	416	37.99	406.8
+calvin young	418	47.22	380.46
+katie xylophone	425	32.59	377.39
+nick quirinius	429	19.63	391.01
+ethan ellison	453	47.92	405.78
+irene nixon	454	48.03	421.40999999999997
+bob steinbeck	462	47.04	442.37
+luke robinson	462	47.48	414.08
+tom hernandez	467	29.36	418.97
+gabriella steinbeck	467	9.35	419.96
+irene polk	485	14.26	437.52
+mike xylophone	494	36.92	464.64
+calvin allen	499	39.99	489.65
+quinn steinbeck	503	16.62	488.74
+ulysses garcia	263	31.85	NULL
+calvin thompson	263	30.87	NULL
+rachel quirinius	263	29.46	NULL
+mike steinbeck	266	48.57	234.15
+rachel young	275	14.75	244.13
+tom king	278	31.11	248.54
+oscar robinson	283	30.35	234.43
+zach allen	284	1.88	269.25
+bob king	308	27.61	276.89
+ulysses allen	310	22.77	279.65
+fred nixon	317	0.48	315.12
+gabriella robinson	321	0.33	293.39
+bob johnson	325	9.61	302.23
+rachel davidson	335	2.34	334.52
+fred brown	337	5.8	336.67
+wendy ellison	350	20.25	340.39
+zach falkner	391	13.67	388.66
+katie xylophone	410	39.09	404.2
+holly king	413	3.56	392.75
+sarah van buren	417	7.81	403.33
+calvin van buren	430	36.01	390.90999999999997
+katie white	434	33.56	430.44
+oscar quirinius	454	7.03	446.19
+zach young	505	18.19	468.99
+gabriella robinson	506	12.8	472.44
+sarah xylophone	507	16.09	499.97
+rachel thompson	267	46.87	NULL
+gabriella van buren	271	41.04	NULL
+mike steinbeck	284	11.44	NULL
+luke falkner	293	40.67	246.13
+ethan ovid	293	2.08	251.96
+irene nixon	321	24.35	309.56
+mike van buren	327	2.58	286.33
+ulysses robinson	329	26.64	326.92
+quinn laertes	332	10.71	307.65
+tom polk	346	34.03	343.42
+jessica johnson	352	45.71	325.36
+xavier davidson	354	33.9	343.29
+wendy nixon	364	29.42	329.97
+jessica quirinius	375	47.33	329.29
+xavier brown	376	26.17	342.1
+gabriella davidson	383	18.87	353.58
+jessica brown	388	34.09	340.67
+gabriella garcia	391	32.44	364.83
+ethan miller	396	49.07	377.13
+bob garcia	416	7.82	381.90999999999997
+priscilla hernandez	416	29.94	383.56
+holly nixon	419	17.81	369.93
+nick underhill	429	39.54	421.18
+xavier falkner	434	0.88	404.06
+luke robinson	461	44.02	443.19
+bob underhill	465	22.58	425.46
+ulysses king	483	37.98	482.12
+jessica miller	486	26.14	441.98
+bob ovid	493	9.7	470.42
+alice falkner	500	37.85	462.02
+quinn xylophone	267	49.8	NULL
+gabriella thompson	268	17.15	NULL
+calvin xylophone	275	49.32	NULL
+gabriella zipper	279	30.41	229.2
+PREHOOK: query: select s, lag(s, 3, 'fred') over (partition by f order by b) from over10k
limit 100
+PREHOOK: type: QUERY
+PREHOOK: Input: default@over10k
+#### A masked pattern was here ####
+POSTHOOK: query: select s, lag(s, 3, 'fred') over (partition by f order by b) from over10k
limit 100
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@over10k
+#### A masked pattern was here ####
+yuri thompson	fred
+bob ichabod	fred
+luke king	fred
+luke steinbeck	fred
+fred zipper	fred
+quinn miller	fred
+calvin van buren	fred
+holly steinbeck	fred
+david davidson	fred
+calvin thompson	fred
+calvin quirinius	fred
+david ovid	fred
+holly thompson	fred
+nick zipper	fred
+victor steinbeck	fred
+victor robinson	fred
+zach ovid	fred
+ulysses zipper	fred
+luke falkner	fred
+irene thompson	fred
+yuri johnson	fred
+ulysses falkner	fred
+gabriella robinson	fred
+alice robinson	fred
+priscilla xylophone	fred
+david laertes	fred
+mike underhill	fred
+victor van buren	fred
+holly falkner	fred
+priscilla falkner	fred
+ethan ovid	fred
+luke zipper	fred
+mike steinbeck	fred
+calvin white	fred
+alice quirinius	fred
+irene miller	fred
+wendy polk	fred
+nick young	fred
+yuri davidson	fred
+ethan ellison	fred
+zach hernandez	fred
+wendy miller	fred
+katie underhill	fred
+irene zipper	fred
+holly allen	fred
+quinn brown	fred
+calvin ovid	fred
+zach robinson	fred
+nick miller	fred
+mike allen	fred
+yuri van buren	fred
+priscilla young	fred
+zach miller	fred
+victor xylophone	fred
+sarah falkner	fred
+rachel ichabod	fred
+alice robinson	fred
+calvin ovid	fred
+calvin ovid	fred
+luke laertes	fred
+david hernandez	fred
+alice ovid	fred
+luke quirinius	fred
+oscar white	fred
+zach falkner	fred
+rachel thompson	fred
+priscilla king	fred
+xavier polk	fred
+wendy ichabod	fred
+rachel ovid	fred
+wendy allen	fred
+luke brown	fred
+mike brown	fred
+oscar ichabod	fred
+xavier garcia	fred
+yuri brown	fred
+bob xylophone	fred
+luke davidson	fred
+ethan quirinius	fred
+zach davidson	fred
+irene miller	fred
+wendy king	fred
+bob zipper	fred
+sarah thompson	fred
+bob carson	fred
+bob laertes	fred
+xavier allen	fred
+sarah robinson	fred
+david king	fred
+oscar davidson	fred
+victor hernandez	fred
+wendy polk	fred
+david ellison	fred
+ulysses johnson	fred
+jessica ovid	fred
+bob king	fred
+ulysses garcia	fred
+irene falkner	fred
+holly robinson	fred
+yuri white	fred



Mime
View raw message