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/04/02 16:16:37 UTC
svn commit: r1463556 [9/15] - in /hive/trunk:
common/src/java/org/apache/hadoop/hive/conf/ data/files/ ql/if/
ql/src/gen/thrift/gen-cpp/
ql/src/gen/thrift/gen-javabean/org/apache/hadoop/hive/ql/plan/api/
ql/src/gen/thrift/gen-php/ ql/src/gen/thrift/gen...
Added: hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/udf/ptf/WindowingTableFunction.java
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/udf/ptf/WindowingTableFunction.java?rev=1463556&view=auto
==============================================================================
--- hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/udf/ptf/WindowingTableFunction.java (added)
+++ hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/udf/ptf/WindowingTableFunction.java Tue Apr 2 14:16:34 2013
@@ -0,0 +1,852 @@
+/**
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.hadoop.hive.ql.udf.ptf;
+
+import java.util.AbstractList;
+import java.util.ArrayList;
+import java.util.List;
+
+import org.apache.hadoop.hive.ql.exec.PTFOperator;
+import org.apache.hadoop.hive.ql.exec.PTFPartition;
+import org.apache.hadoop.hive.ql.exec.PTFPartition.PTFPartitionIterator;
+import org.apache.hadoop.hive.ql.metadata.HiveException;
+import org.apache.hadoop.hive.ql.parse.PTFInvocationSpec.Order;
+import org.apache.hadoop.hive.ql.parse.SemanticException;
+import org.apache.hadoop.hive.ql.parse.WindowingSpec.BoundarySpec;
+import org.apache.hadoop.hive.ql.parse.WindowingSpec.Direction;
+import org.apache.hadoop.hive.ql.plan.PTFDesc;
+import org.apache.hadoop.hive.ql.plan.PTFDesc.BoundaryDef;
+import org.apache.hadoop.hive.ql.plan.PTFDesc.PTFExpressionDef;
+import org.apache.hadoop.hive.ql.plan.PTFDesc.PartitionedTableFunctionDef;
+import org.apache.hadoop.hive.ql.plan.PTFDesc.ValueBoundaryDef;
+import org.apache.hadoop.hive.ql.plan.PTFDesc.WindowFrameDef;
+import org.apache.hadoop.hive.ql.plan.PTFDesc.WindowFunctionDef;
+import org.apache.hadoop.hive.ql.plan.PTFDesc.WindowTableFunctionDef;
+import org.apache.hadoop.hive.ql.udf.generic.GenericUDAFEvaluator;
+import org.apache.hadoop.hive.ql.udf.generic.GenericUDAFEvaluator.AggregationBuffer;
+import org.apache.hadoop.hive.serde2.SerDeException;
+import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspectorUtils;
+import org.apache.hadoop.hive.serde2.objectinspector.PrimitiveObjectInspector;
+import org.apache.hadoop.hive.serde2.objectinspector.StructField;
+import org.apache.hadoop.hive.serde2.objectinspector.StructObjectInspector;
+import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorUtils;
+
+public class WindowingTableFunction extends TableFunctionEvaluator
+{
+
+ @Override
+ public PTFPartition execute(PTFPartition iPart)
+ throws HiveException
+ {
+ WindowTableFunctionDef wFnDef = (WindowTableFunctionDef) getTableDef();
+ PTFPartitionIterator<Object> pItr = iPart.iterator();
+ PTFOperator.connectLeadLagFunctionsToPartition(ptfDesc, pItr);
+
+ if ( outputPartition == null ) {
+ outputPartition = new PTFPartition(getPartitionClass(),
+ getPartitionMemSize(), wFnDef.getOutputFromWdwFnProcessing().getSerde(), OI);
+ }
+ else {
+ outputPartition.reset();
+ }
+
+ execute(pItr, outputPartition);
+ return outputPartition;
+ }
+
+ @SuppressWarnings({ "unchecked", "rawtypes" })
+ @Override
+ public void execute(PTFPartitionIterator<Object> pItr, PTFPartition outP) throws HiveException
+ {
+ ArrayList<List<?>> oColumns = new ArrayList<List<?>>();
+ PTFPartition iPart = pItr.getPartition();
+ StructObjectInspector inputOI;
+ try {
+ inputOI = (StructObjectInspector) iPart.getSerDe().getObjectInspector();
+ } catch (SerDeException se) {
+ throw new HiveException(se);
+ }
+
+ WindowTableFunctionDef wTFnDef = (WindowTableFunctionDef) getTableDef();
+ Order order = wTFnDef.getOrder().getExpressions().get(0).getOrder();
+
+ for(WindowFunctionDef wFn : wTFnDef.getWindowFunctions())
+ {
+ boolean processWindow = processWindow(wFn);
+ pItr.reset();
+ if ( !processWindow )
+ {
+ GenericUDAFEvaluator fEval = wFn.getWFnEval();
+ Object[] args = new Object[wFn.getArgs() == null ? 0 : wFn.getArgs().size()];
+ AggregationBuffer aggBuffer = fEval.getNewAggregationBuffer();
+ while(pItr.hasNext())
+ {
+ Object row = pItr.next();
+ int i =0;
+ if ( wFn.getArgs() != null ) {
+ for(PTFExpressionDef arg : wFn.getArgs())
+ {
+ args[i++] = arg.getExprEvaluator().evaluate(row);
+ }
+ }
+ fEval.aggregate(aggBuffer, args);
+ }
+ Object out = fEval.evaluate(aggBuffer);
+ if ( !wFn.isPivotResult())
+ {
+ out = new SameList(iPart.size(), out);
+ }
+ oColumns.add((List<?>)out);
+ }
+ else
+ {
+ oColumns.add(executeFnwithWindow(getQueryDef(), wFn, iPart, order));
+ }
+ }
+
+ /*
+ * Output Columns in the following order
+ * - the columns representing the output from Window Fns
+ * - the input Rows columns
+ */
+
+ for(int i=0; i < iPart.size(); i++)
+ {
+ ArrayList oRow = new ArrayList();
+ Object iRow = iPart.getAt(i);
+
+ for(int j=0; j < oColumns.size(); j++)
+ {
+ oRow.add(oColumns.get(j).get(i));
+ }
+
+ for(StructField f : inputOI.getAllStructFieldRefs())
+ {
+ oRow.add(inputOI.getStructFieldData(iRow, f));
+ }
+
+ outP.append(oRow);
+ }
+ }
+
+ private boolean processWindow(WindowFunctionDef wFn) {
+ WindowFrameDef frame = wFn.getWindowFrame();
+ if ( frame == null ) {
+ return false;
+ }
+ if ( frame.getStart().getAmt() == BoundarySpec.UNBOUNDED_AMOUNT &&
+ frame.getEnd().getAmt() == BoundarySpec.UNBOUNDED_AMOUNT ) {
+ return false;
+ }
+ return true;
+ }
+
+ public static class WindowingTableFunctionResolver extends TableFunctionResolver
+ {
+ /*
+ * OI of object constructed from output of Wdw Fns; before it is put
+ * in the Wdw Processing Partition. Set by Translator/Deserializer.
+ */
+ private transient StructObjectInspector wdwProcessingOutputOI;
+
+ public StructObjectInspector getWdwProcessingOutputOI() {
+ return wdwProcessingOutputOI;
+ }
+
+ public void setWdwProcessingOutputOI(StructObjectInspector wdwProcessingOutputOI) {
+ this.wdwProcessingOutputOI = wdwProcessingOutputOI;
+ }
+
+ @Override
+ protected TableFunctionEvaluator createEvaluator(PTFDesc ptfDesc, PartitionedTableFunctionDef tDef)
+ {
+
+ return new WindowingTableFunction();
+ }
+
+ @Override
+ public void setupOutputOI() throws SemanticException {
+ setOutputOI(wdwProcessingOutputOI);
+ }
+
+ /*
+ * Setup the OI based on the:
+ * - Input TableDef's columns
+ * - the Window Functions.
+ */
+ @Override
+ public void initializeOutputOI() throws HiveException
+ {
+ setupOutputOI();
+ }
+
+
+ @Override
+ public boolean transformsRawInput()
+ {
+ return false;
+ }
+
+ /*
+ * (non-Javadoc)
+ * @see org.apache.hadoop.hive.ql.udf.ptf.TableFunctionResolver#carryForwardNames()
+ * Setting to true is correct only for special internal Functions.
+ */
+ @Override
+ public boolean carryForwardNames() {
+ return true;
+ }
+
+ /*
+ * (non-Javadoc)
+ * @see org.apache.hadoop.hive.ql.udf.ptf.TableFunctionResolver#getOutputNames()
+ * Set to null only because carryForwardNames is true.
+ */
+ @Override
+ public ArrayList<String> getOutputColumnNames() {
+ return null;
+ }
+
+ }
+
+ ArrayList<Object> executeFnwithWindow(PTFDesc ptfDesc,
+ WindowFunctionDef wFnDef,
+ PTFPartition iPart,
+ Order order)
+ throws HiveException
+ {
+ ArrayList<Object> vals = new ArrayList<Object>();
+
+ GenericUDAFEvaluator fEval = wFnDef.getWFnEval();
+
+ Object[] args = new Object[wFnDef.getArgs() == null ? 0 : wFnDef.getArgs().size()];
+ for(int i=0; i < iPart.size(); i++)
+ {
+ AggregationBuffer aggBuffer = fEval.getNewAggregationBuffer();
+ Range rng = getRange(wFnDef, i, iPart, order);
+ PTFPartitionIterator<Object> rItr = rng.iterator();
+ PTFOperator.connectLeadLagFunctionsToPartition(ptfDesc, rItr);
+ while(rItr.hasNext())
+ {
+ Object row = rItr.next();
+ int j = 0;
+ if ( wFnDef.getArgs() != null ) {
+ for(PTFExpressionDef arg : wFnDef.getArgs())
+ {
+ args[j++] = arg.getExprEvaluator().evaluate(row);
+ }
+ }
+ fEval.aggregate(aggBuffer, args);
+ }
+ Object out = fEval.evaluate(aggBuffer);
+ out = ObjectInspectorUtils.copyToStandardObject(out, wFnDef.getOI());
+ vals.add(out);
+ }
+ return vals;
+ }
+
+ Range getRange(WindowFunctionDef wFnDef, int currRow, PTFPartition p, Order order) throws HiveException
+ {
+ BoundaryDef startB = wFnDef.getWindowFrame().getStart();
+ BoundaryDef endB = wFnDef.getWindowFrame().getEnd();
+ boolean rowFrame = true;
+
+ if ( startB instanceof ValueBoundaryDef || endB instanceof ValueBoundaryDef) {
+ rowFrame = false;
+ }
+
+ int start, end;
+
+ if (rowFrame) {
+ start = getRowBoundaryStart(startB, currRow);
+ end = getRowBoundaryEnd(endB, currRow, p);
+ }
+ else {
+ ValueBoundaryScanner vbs;
+ if ( startB instanceof ValueBoundaryDef ) {
+ vbs = ValueBoundaryScanner.getScanner((ValueBoundaryDef)startB, order);
+ }
+ else {
+ vbs = ValueBoundaryScanner.getScanner((ValueBoundaryDef)endB, order);
+ }
+ vbs.reset(startB);
+ start = vbs.computeStart(currRow, p);
+ vbs.reset(endB);
+ end = vbs.computeEnd(currRow, p);
+ }
+ start = start < 0 ? 0 : start;
+ end = end > p.size() ? p.size() : end;
+ return new Range(start, end, p);
+ }
+
+ int getRowBoundaryStart(BoundaryDef b, int currRow) throws HiveException {
+ Direction d = b.getDirection();
+ int amt = b.getAmt();
+ switch(d) {
+ case PRECEDING:
+ if (amt == BoundarySpec.UNBOUNDED_AMOUNT) {
+ return 0;
+ }
+ else {
+ return currRow - amt;
+ }
+ case CURRENT:
+ return currRow;
+ case FOLLOWING:
+ return currRow + amt;
+ }
+ throw new HiveException("Unknown Start Boundary Direction: " + d);
+ }
+
+ int getRowBoundaryEnd(BoundaryDef b, int currRow, PTFPartition p) throws HiveException {
+ Direction d = b.getDirection();
+ int amt = b.getAmt();
+ switch(d) {
+ case PRECEDING:
+ if ( amt == 0 ) {
+ return currRow + 1;
+ }
+ return currRow - amt;
+ case CURRENT:
+ return currRow + 1;
+ case FOLLOWING:
+ if (amt == BoundarySpec.UNBOUNDED_AMOUNT) {
+ return p.size();
+ }
+ else {
+ return currRow + amt + 1;
+ }
+ }
+ throw new HiveException("Unknown End Boundary Direction: " + d);
+ }
+
+ static class Range
+ {
+ int start;
+ int end;
+ PTFPartition p;
+
+ public Range(int start, int end, PTFPartition p)
+ {
+ super();
+ this.start = start;
+ this.end = end;
+ this.p = p;
+ }
+
+ public PTFPartitionIterator<Object> iterator()
+ {
+ return p.range(start, end);
+ }
+ }
+
+ /*
+ * - starting from the given rowIdx scan in the given direction until a row's expr
+ * evaluates to an amt that crosses the 'amt' threshold specified in the ValueBoundaryDef.
+ */
+ static abstract class ValueBoundaryScanner
+ {
+ BoundaryDef bndDef;
+ Order order;
+ PTFExpressionDef expressionDef;
+
+ public ValueBoundaryScanner(BoundaryDef bndDef, Order order, PTFExpressionDef expressionDef)
+ {
+ this.bndDef = bndDef;
+ this.order = order;
+ this.expressionDef = expressionDef;
+ }
+
+ public void reset(BoundaryDef bndDef) {
+ this.bndDef = bndDef;
+ }
+
+ /*
+| Use | Boundary1.type | Boundary1. amt | Sort Key | Order | Behavior |
+| Case | | | | | |
+|------+----------------+----------------+----------+-------+-----------------------------------|
+| 1. | PRECEDING | UNB | ANY | ANY | start = 0 |
+| 2. | PRECEDING | unsigned int | NULL | ASC | start = 0 |
+| 3. | | | | DESC | scan backwards to row R2 |
+| | | | | | such that R2.sk is not null |
+| | | | | | start = R2.idx + 1 |
+| 4. | PRECEDING | unsigned int | not NULL | DESC | scan backwards until row R2 |
+| | | | | | such that R2.sk - R.sk > amt |
+| | | | | | start = R2.idx + 1 |
+| 5. | PRECEDING | unsigned int | not NULL | ASC | scan backward until row R2 |
+| | | | | | such that R.sk - R2.sk > bnd1.amt |
+| | | | | | start = R2.idx + 1 |
+| 6. | CURRENT ROW | | NULL | ANY | scan backwards until row R2 |
+| | | | | | such that R2.sk is not null |
+| | | | | | start = R2.idx + 1 |
+| 7. | CURRENT ROW | | not NULL | ANY | scan backwards until row R2 |
+| | | | | | such R2.sk != R.sk |
+| | | | | | start = R2.idx + 1 |
+| 8. | FOLLOWING | UNB | ANY | ANY | Error |
+| 9. | FOLLOWING | unsigned int | NULL | DESC | start = partition.size |
+| 10. | | | | ASC | scan forward until R2 |
+| | | | | | such that R2.sk is not null |
+| | | | | | start = R2.idx |
+| 11. | FOLLOWING | unsigned int | not NULL | DESC | scan forward until row R2 |
+| | | | | | such that R.sk - R2.sk > amt |
+| | | | | | start = R2.idx |
+| 12. | | | | ASC | scan forward until row R2 |
+| | | | | | such that R2.sk - R.sk > amt |
+|------+----------------+----------------+----------+-------+-----------------------------------|
+ */
+ protected int computeStart(int rowIdx, PTFPartition p) throws HiveException {
+ switch(bndDef.getDirection()) {
+ case PRECEDING:
+ return computeStartPreceding(rowIdx, p);
+ case CURRENT:
+ return computeStartCurrentRow(rowIdx, p);
+ case FOLLOWING:
+ default:
+ return computeStartFollowing(rowIdx, p);
+ }
+ }
+
+ /*
+ *
+ */
+ protected int computeStartPreceding(int rowIdx, PTFPartition p) throws HiveException {
+ int amt = bndDef.getAmt();
+ // Use Case 1.
+ if ( amt == BoundarySpec.UNBOUNDED_AMOUNT ) {
+ return 0;
+ }
+ Object sortKey = computeValue(p.getAt(rowIdx));
+
+ if ( sortKey == null ) {
+ // Use Case 2.
+ if ( order == Order.ASC ) {
+ return 0;
+ }
+ else { // Use Case 3.
+ while ( sortKey == null && rowIdx >= 0 ) {
+ --rowIdx;
+ if ( rowIdx >= 0 ) {
+ sortKey = computeValue(p.getAt(rowIdx));
+ }
+ }
+ return rowIdx+1;
+ }
+ }
+
+ Object rowVal = sortKey;
+ int r = rowIdx;
+
+ // Use Case 4.
+ if ( order == Order.DESC ) {
+ while (r >= 0 && !isGreater(rowVal, sortKey, amt) ) {
+ r--;
+ if ( r >= 0 ) {
+ rowVal = computeValue(p.getAt(r));
+ }
+ }
+ return r + 1;
+ }
+ else { // Use Case 5.
+ while (r >= 0 && !isGreater(sortKey, rowVal, amt) ) {
+ r--;
+ if ( r >= 0 ) {
+ rowVal = computeValue(p.getAt(r));
+ }
+ }
+ return r + 1;
+ }
+ }
+
+ protected int computeStartCurrentRow(int rowIdx, PTFPartition p) throws HiveException {
+ Object sortKey = computeValue(p.getAt(rowIdx));
+
+ // Use Case 6.
+ if ( sortKey == null ) {
+ while ( sortKey == null && rowIdx >= 0 ) {
+ --rowIdx;
+ if ( rowIdx >= 0 ) {
+ sortKey = computeValue(p.getAt(rowIdx));
+ }
+ }
+ return rowIdx+1;
+ }
+
+ Object rowVal = sortKey;
+ int r = rowIdx;
+
+ // Use Case 7.
+ while (r >= 0 && isEqual(rowVal, sortKey) ) {
+ r--;
+ if ( r >= 0 ) {
+ rowVal = computeValue(p.getAt(r));
+ }
+ }
+ return r + 1;
+ }
+
+ protected int computeStartFollowing(int rowIdx, PTFPartition p) throws HiveException {
+ int amt = bndDef.getAmt();
+ Object sortKey = computeValue(p.getAt(rowIdx));
+
+ Object rowVal = sortKey;
+ int r = rowIdx;
+
+ if ( sortKey == null ) {
+ // Use Case 9.
+ if ( order == Order.DESC) {
+ return p.size();
+ }
+ else { // Use Case 10.
+ while (r < p.size() && rowVal == null ) {
+ r++;
+ if ( r < p.size() ) {
+ rowVal = computeValue(p.getAt(r));
+ }
+ }
+ return r;
+ }
+ }
+
+ // Use Case 11.
+ if ( order == Order.DESC) {
+ while (r < p.size() && !isGreater(sortKey, rowVal, amt) ) {
+ r++;
+ if ( r < p.size() ) {
+ rowVal = computeValue(p.getAt(r));
+ }
+ }
+ return r;
+ }
+ else { // Use Case 12.
+ while (r < p.size() && !isGreater(rowVal, sortKey, amt) ) {
+ r++;
+ if ( r < p.size() ) {
+ rowVal = computeValue(p.getAt(r));
+ }
+ }
+ return r;
+ }
+ }
+
+ /*
+| Use | Boundary2.type | Boundary2.amt | Sort Key | Order | Behavior |
+| Case | | | | | |
+|------+----------------+---------------+----------+-------+-----------------------------------|
+| 1. | PRECEDING | UNB | ANY | ANY | Error |
+| 2. | PRECEDING | unsigned int | NULL | DESC | end = partition.size() |
+| 3. | | | | ASC | end = 0 |
+| 4. | PRECEDING | unsigned int | not null | DESC | scan backward until row R2 |
+| | | | | | such that R2.sk - R.sk > bnd.amt |
+| | | | | | end = R2.idx + 1 |
+| 5. | PRECEDING | unsigned int | not null | ASC | scan backward until row R2 |
+| | | | | | such that R.sk - R2.sk > bnd.amt |
+| | | | | | end = R2.idx + 1 |
+| 6. | CURRENT ROW | | NULL | ANY | scan forward until row R2 |
+| | | | | | such that R2.sk is not null |
+| | | | | | end = R2.idx |
+| 7. | CURRENT ROW | | not null | ANY | scan forward until row R2 |
+| | | | | | such that R2.sk != R.sk |
+| | | | | | end = R2.idx |
+| 8. | FOLLOWING | UNB | ANY | ANY | end = partition.size() |
+| 9. | FOLLOWING | unsigned int | NULL | DESC | end = partition.size() |
+| 10. | | | | ASC | scan forward until row R2 |
+| | | | | | such that R2.sk is not null |
+| | | | | | end = R2.idx |
+| 11. | FOLLOWING | unsigned int | not NULL | DESC | scan forward until row R2 |
+| | | | | | such R.sk - R2.sk > bnd.amt |
+| | | | | | end = R2.idx |
+| 12. | | | | ASC | scan forward until row R2 |
+| | | | | | such R2.sk - R2.sk > bnd.amt |
+| | | | | | end = R2.idx |
+|------+----------------+---------------+----------+-------+-----------------------------------|
+ */
+ protected int computeEnd(int rowIdx, PTFPartition p) throws HiveException {
+ switch(bndDef.getDirection()) {
+ case PRECEDING:
+ return computeEndPreceding(rowIdx, p);
+ case CURRENT:
+ return computeEndCurrentRow(rowIdx, p);
+ case FOLLOWING:
+ default:
+ return computeEndFollowing(rowIdx, p);
+ }
+ }
+
+ protected int computeEndPreceding(int rowIdx, PTFPartition p) throws HiveException {
+ int amt = bndDef.getAmt();
+ // Use Case 1.
+ // amt == UNBOUNDED, is caught during translation
+
+ Object sortKey = computeValue(p.getAt(rowIdx));
+
+ if ( sortKey == null ) {
+ // Use Case 2.
+ if ( order == Order.DESC ) {
+ return p.size();
+ }
+ else { // Use Case 3.
+ return 0;
+ }
+ }
+
+ Object rowVal = sortKey;
+ int r = rowIdx;
+
+ // Use Case 4.
+ if ( order == Order.DESC ) {
+ while (r >= 0 && !isGreater(rowVal, sortKey, amt) ) {
+ r--;
+ if ( r >= 0 ) {
+ rowVal = computeValue(p.getAt(r));
+ }
+ }
+ return r + 1;
+ }
+ else { // Use Case 5.
+ while (r >= 0 && !isGreater(sortKey, rowVal, amt) ) {
+ r--;
+ if ( r >= 0 ) {
+ rowVal = computeValue(p.getAt(r));
+ }
+ }
+ return r + 1;
+ }
+ }
+
+ protected int computeEndCurrentRow(int rowIdx, PTFPartition p) throws HiveException {
+ Object sortKey = computeValue(p.getAt(rowIdx));
+
+ // Use Case 6.
+ if ( sortKey == null ) {
+ while ( sortKey == null && rowIdx < p.size() ) {
+ ++rowIdx;
+ if ( rowIdx < p.size() ) {
+ sortKey = computeValue(p.getAt(rowIdx));
+ }
+ }
+ return rowIdx;
+ }
+
+ Object rowVal = sortKey;
+ int r = rowIdx;
+
+ // Use Case 7.
+ while (r < p.size() && isEqual(sortKey, rowVal) ) {
+ r++;
+ if ( r < p.size() ) {
+ rowVal = computeValue(p.getAt(r));
+ }
+ }
+ return r;
+ }
+
+ protected int computeEndFollowing(int rowIdx, PTFPartition p) throws HiveException {
+ int amt = bndDef.getAmt();
+
+ // Use Case 8.
+ if ( amt == BoundarySpec.UNBOUNDED_AMOUNT ) {
+ return p.size();
+ }
+ Object sortKey = computeValue(p.getAt(rowIdx));
+
+ Object rowVal = sortKey;
+ int r = rowIdx;
+
+ if ( sortKey == null ) {
+ // Use Case 9.
+ if ( order == Order.DESC) {
+ return p.size();
+ }
+ else { // Use Case 10.
+ while (r < p.size() && rowVal == null ) {
+ r++;
+ if ( r < p.size() ) {
+ rowVal = computeValue(p.getAt(r));
+ }
+ }
+ return r;
+ }
+ }
+
+ // Use Case 11.
+ if ( order == Order.DESC) {
+ while (r < p.size() && !isGreater(sortKey, rowVal, amt) ) {
+ r++;
+ if ( r < p.size() ) {
+ rowVal = computeValue(p.getAt(r));
+ }
+ }
+ return r;
+ }
+ else { // Use Case 12.
+ while (r < p.size() && !isGreater(rowVal, sortKey, amt) ) {
+ r++;
+ if ( r < p.size() ) {
+ rowVal = computeValue(p.getAt(r));
+ }
+ }
+ return r;
+ }
+ }
+
+ public Object computeValue(Object row) throws HiveException
+ {
+ Object o = expressionDef.getExprEvaluator().evaluate(row);
+ return ObjectInspectorUtils.copyToStandardObject(o, expressionDef.getOI());
+ }
+
+ public abstract boolean isGreater(Object v1, Object v2, int amt);
+
+ public abstract boolean isEqual(Object v1, Object v2);
+
+
+ @SuppressWarnings("incomplete-switch")
+ public static ValueBoundaryScanner getScanner(ValueBoundaryDef vbDef, Order order) throws HiveException
+ {
+ PrimitiveObjectInspector pOI = (PrimitiveObjectInspector) vbDef.getOI();
+ switch(pOI.getPrimitiveCategory())
+ {
+ case BYTE:
+ case INT:
+ case LONG:
+ case SHORT:
+ case TIMESTAMP:
+ return new LongValueBoundaryScanner(vbDef, order, vbDef.getExpressionDef());
+ case DOUBLE:
+ case FLOAT:
+ return new DoubleValueBoundaryScanner(vbDef, order, vbDef.getExpressionDef());
+ case STRING:
+ return new StringValueBoundaryScanner(vbDef, order, vbDef.getExpressionDef());
+ }
+ throw new HiveException(
+ String.format("Internal Error: attempt to setup a Window for datatype %s",
+ pOI.getPrimitiveCategory()));
+ }
+ }
+
+ public static class LongValueBoundaryScanner extends ValueBoundaryScanner
+ {
+ public LongValueBoundaryScanner(BoundaryDef bndDef, Order order, PTFExpressionDef expressionDef)
+ {
+ super(bndDef,order,expressionDef);
+ }
+
+ @Override
+ public boolean isGreater(Object v1, Object v2, int amt)
+ {
+ long l1 = PrimitiveObjectInspectorUtils.getLong(v1,
+ (PrimitiveObjectInspector) expressionDef.getOI());
+ long l2 = PrimitiveObjectInspectorUtils.getLong(v2,
+ (PrimitiveObjectInspector) expressionDef.getOI());
+ return (l1 -l2) > amt;
+ }
+
+ @Override
+ public boolean isEqual(Object v1, Object v2)
+ {
+ long l1 = PrimitiveObjectInspectorUtils.getLong(v1,
+ (PrimitiveObjectInspector) expressionDef.getOI());
+ long l2 = PrimitiveObjectInspectorUtils.getLong(v2,
+ (PrimitiveObjectInspector) expressionDef.getOI());
+ return l1 == l2;
+ }
+ }
+
+ public static class DoubleValueBoundaryScanner extends ValueBoundaryScanner
+ {
+ public DoubleValueBoundaryScanner(BoundaryDef bndDef, Order order, PTFExpressionDef expressionDef)
+ {
+ super(bndDef,order,expressionDef);
+ }
+
+ @Override
+ public boolean isGreater(Object v1, Object v2, int amt)
+ {
+ double d1 = PrimitiveObjectInspectorUtils.getDouble(v1,
+ (PrimitiveObjectInspector) expressionDef.getOI());
+ double d2 = PrimitiveObjectInspectorUtils.getDouble(v2,
+ (PrimitiveObjectInspector) expressionDef.getOI());
+ return (d1 -d2) > amt;
+ }
+
+ @Override
+ public boolean isEqual(Object v1, Object v2)
+ {
+ double d1 = PrimitiveObjectInspectorUtils.getDouble(v1,
+ (PrimitiveObjectInspector) expressionDef.getOI());
+ double d2 = PrimitiveObjectInspectorUtils.getDouble(v2,
+ (PrimitiveObjectInspector) expressionDef.getOI());
+ return d1 == d2;
+ }
+ }
+
+ public static class StringValueBoundaryScanner extends ValueBoundaryScanner
+ {
+ public StringValueBoundaryScanner(BoundaryDef bndDef, Order order, PTFExpressionDef expressionDef)
+ {
+ super(bndDef,order,expressionDef);
+ }
+
+ @Override
+ public boolean isGreater(Object v1, Object v2, int amt)
+ {
+ String s1 = PrimitiveObjectInspectorUtils.getString(v1,
+ (PrimitiveObjectInspector) expressionDef.getOI());
+ String s2 = PrimitiveObjectInspectorUtils.getString(v2,
+ (PrimitiveObjectInspector) expressionDef.getOI());
+ return s1 != null && s2 != null && s1.compareTo(s2) > 0;
+ }
+
+ @Override
+ public boolean isEqual(Object v1, Object v2)
+ {
+ String s1 = PrimitiveObjectInspectorUtils.getString(v1,
+ (PrimitiveObjectInspector) expressionDef.getOI());
+ String s2 = PrimitiveObjectInspectorUtils.getString(v2,
+ (PrimitiveObjectInspector) expressionDef.getOI());
+ return (s1 == null && s2 == null) || s1.equals(s2);
+ }
+ }
+
+ public static class SameList<E> extends AbstractList<E>
+ {
+ int sz;
+ E val;
+
+ public SameList(int sz, E val)
+ {
+ this.sz = sz;
+ this.val = val;
+ }
+
+ @Override
+ public E get(int index)
+ {
+ return val;
+ }
+
+ @Override
+ public int size()
+ {
+ return sz;
+ }
+
+ }
+
+}
Added: hive/trunk/ql/src/test/queries/clientnegative/ptf_negative_AggrFuncsWithNoGBYNoPartDef.q
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/queries/clientnegative/ptf_negative_AggrFuncsWithNoGBYNoPartDef.q?rev=1463556&view=auto
==============================================================================
--- hive/trunk/ql/src/test/queries/clientnegative/ptf_negative_AggrFuncsWithNoGBYNoPartDef.q (added)
+++ hive/trunk/ql/src/test/queries/clientnegative/ptf_negative_AggrFuncsWithNoGBYNoPartDef.q Tue Apr 2 14:16:34 2013
@@ -0,0 +1,20 @@
+DROP TABLE part;
+
+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;
+
+-- testAggrFuncsWithNoGBYNoPartDef
+select p_mfgr,
+sum(p_retailprice) as s1
+from part;
\ No newline at end of file
Added: hive/trunk/ql/src/test/queries/clientnegative/ptf_negative_AmbiguousWindowDefn.q
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/queries/clientnegative/ptf_negative_AmbiguousWindowDefn.q?rev=1463556&view=auto
==============================================================================
--- hive/trunk/ql/src/test/queries/clientnegative/ptf_negative_AmbiguousWindowDefn.q (added)
+++ hive/trunk/ql/src/test/queries/clientnegative/ptf_negative_AmbiguousWindowDefn.q Tue Apr 2 14:16:34 2013
@@ -0,0 +1,28 @@
+DROP TABLE part;
+
+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;
+
+-- testAmbiguousWindowDefn
+select p_mfgr, p_name, p_size,
+sum(p_size) over (w1) as s1,
+sum(p_size) over (w2) as s2,
+sum(p_size) over (w3) as s3
+from part
+distribute by p_mfgr
+sort by p_mfgr
+window w1 as (rows between 2 preceding and 2 following),
+ w2 as (rows between unbounded preceding and current row),
+ w3 as w3;
+
Added: hive/trunk/ql/src/test/queries/clientnegative/ptf_negative_DistributeByOrderBy.q
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/queries/clientnegative/ptf_negative_DistributeByOrderBy.q?rev=1463556&view=auto
==============================================================================
--- hive/trunk/ql/src/test/queries/clientnegative/ptf_negative_DistributeByOrderBy.q (added)
+++ hive/trunk/ql/src/test/queries/clientnegative/ptf_negative_DistributeByOrderBy.q Tue Apr 2 14:16:34 2013
@@ -0,0 +1,19 @@
+DROP TABLE part;
+
+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
+);
+
+-- testPartitonBySortBy
+select p_mfgr, p_name, p_size,
+sum(p_retailprice) over (distribute by p_mfgr order by p_mfgr) as s1
+from part
+;
Added: hive/trunk/ql/src/test/queries/clientnegative/ptf_negative_DuplicateWindowAlias.q
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/queries/clientnegative/ptf_negative_DuplicateWindowAlias.q?rev=1463556&view=auto
==============================================================================
--- hive/trunk/ql/src/test/queries/clientnegative/ptf_negative_DuplicateWindowAlias.q (added)
+++ hive/trunk/ql/src/test/queries/clientnegative/ptf_negative_DuplicateWindowAlias.q Tue Apr 2 14:16:34 2013
@@ -0,0 +1,22 @@
+DROP TABLE part;
+
+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
+);
+
+-- testDuplicateWindowAlias
+select p_mfgr, p_name, p_size,
+sum(p_size) over (w1) as s1,
+sum(p_size) over (w2) as s2
+from part
+window w1 as (partition by p_mfgr order by p_mfgr rows between 2 preceding and 2 following),
+ w2 as w1,
+ w2 as (rows between unbounded preceding and current row);
Added: hive/trunk/ql/src/test/queries/clientnegative/ptf_negative_HavingLeadWithNoGBYNoWindowing.q
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/queries/clientnegative/ptf_negative_HavingLeadWithNoGBYNoWindowing.q?rev=1463556&view=auto
==============================================================================
--- hive/trunk/ql/src/test/queries/clientnegative/ptf_negative_HavingLeadWithNoGBYNoWindowing.q (added)
+++ hive/trunk/ql/src/test/queries/clientnegative/ptf_negative_HavingLeadWithNoGBYNoWindowing.q Tue Apr 2 14:16:34 2013
@@ -0,0 +1,22 @@
+DROP TABLE part;
+
+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;
+
+-- testHavingLeadWithNoGBYNoWindowing
+select p_mfgr,p_name, p_size
+from part
+having lead(p_size, 1) <= p_size
+distribute by p_mfgr
+sort by p_name;
Added: hive/trunk/ql/src/test/queries/clientnegative/ptf_negative_HavingLeadWithPTF.q
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/queries/clientnegative/ptf_negative_HavingLeadWithPTF.q?rev=1463556&view=auto
==============================================================================
--- hive/trunk/ql/src/test/queries/clientnegative/ptf_negative_HavingLeadWithPTF.q (added)
+++ hive/trunk/ql/src/test/queries/clientnegative/ptf_negative_HavingLeadWithPTF.q Tue Apr 2 14:16:34 2013
@@ -0,0 +1,22 @@
+DROP TABLE part;
+
+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
+);
+
+-- testHavingLeadWithPTF
+select p_mfgr,p_name, p_size
+from noop(on part
+partition by p_mfgr
+order by p_name)
+having lead(p_size, 1) <= p_size
+distribute by p_mfgr
+sort by p_name;
Added: hive/trunk/ql/src/test/queries/clientnegative/ptf_negative_InvalidValueBoundary.q
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/queries/clientnegative/ptf_negative_InvalidValueBoundary.q?rev=1463556&view=auto
==============================================================================
--- hive/trunk/ql/src/test/queries/clientnegative/ptf_negative_InvalidValueBoundary.q (added)
+++ hive/trunk/ql/src/test/queries/clientnegative/ptf_negative_InvalidValueBoundary.q Tue Apr 2 14:16:34 2013
@@ -0,0 +1,21 @@
+DROP TABLE part;
+
+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,
+ p_complex array<int>
+);
+
+-- testInvalidValueBoundary
+select p_mfgr,p_name, p_size,
+sum(p_size) over (w1) as s ,
+dense_rank() as dr
+from part
+window w1 as (partition by p_mfgr order by p_complex range between 2 preceding and current row);
Added: hive/trunk/ql/src/test/queries/clientnegative/ptf_negative_JoinWithAmbigousAlias.q
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/queries/clientnegative/ptf_negative_JoinWithAmbigousAlias.q?rev=1463556&view=auto
==============================================================================
--- hive/trunk/ql/src/test/queries/clientnegative/ptf_negative_JoinWithAmbigousAlias.q (added)
+++ hive/trunk/ql/src/test/queries/clientnegative/ptf_negative_JoinWithAmbigousAlias.q Tue Apr 2 14:16:34 2013
@@ -0,0 +1,20 @@
+DROP TABLE part;
+
+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
+);
+
+-- testJoinWithAmbigousAlias
+select abc.*
+from noop(on part
+partition by p_mfgr
+order by p_name
+) abc join part on abc.p_partkey = p1.p_partkey;
Added: hive/trunk/ql/src/test/queries/clientnegative/ptf_negative_PartitionBySortBy.q
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/queries/clientnegative/ptf_negative_PartitionBySortBy.q?rev=1463556&view=auto
==============================================================================
--- hive/trunk/ql/src/test/queries/clientnegative/ptf_negative_PartitionBySortBy.q (added)
+++ hive/trunk/ql/src/test/queries/clientnegative/ptf_negative_PartitionBySortBy.q Tue Apr 2 14:16:34 2013
@@ -0,0 +1,19 @@
+DROP TABLE part;
+
+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
+);
+
+-- testPartitonBySortBy
+select p_mfgr, p_name, p_size,
+sum(p_retailprice) over (partition by p_mfgr sort by p_mfgr) as s1
+from part
+;
Added: hive/trunk/ql/src/test/queries/clientnegative/ptf_negative_WhereWithRankCond.q
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/queries/clientnegative/ptf_negative_WhereWithRankCond.q?rev=1463556&view=auto
==============================================================================
--- hive/trunk/ql/src/test/queries/clientnegative/ptf_negative_WhereWithRankCond.q (added)
+++ hive/trunk/ql/src/test/queries/clientnegative/ptf_negative_WhereWithRankCond.q Tue Apr 2 14:16:34 2013
@@ -0,0 +1,23 @@
+DROP TABLE part;
+
+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;
+
+-- testWhereWithRankCond
+select p_mfgr,p_name, p_size,
+rank() as r
+from part
+where r < 4
+distribute by p_mfgr
+sort by p_mfgr;
Added: hive/trunk/ql/src/test/queries/clientnegative/ptf_window_boundaries.q
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/queries/clientnegative/ptf_window_boundaries.q?rev=1463556&view=auto
==============================================================================
--- hive/trunk/ql/src/test/queries/clientnegative/ptf_window_boundaries.q (added)
+++ hive/trunk/ql/src/test/queries/clientnegative/ptf_window_boundaries.q Tue Apr 2 14:16:34 2013
@@ -0,0 +1,17 @@
+-- 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
+);
+
+select p_mfgr, p_name, p_size,
+ sum(p_retailprice) over (rows unbounded following) as s1
+ from part distribute by p_mfgr sort by p_name;
+
Added: hive/trunk/ql/src/test/queries/clientnegative/ptf_window_boundaries2.q
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/queries/clientnegative/ptf_window_boundaries2.q?rev=1463556&view=auto
==============================================================================
--- hive/trunk/ql/src/test/queries/clientnegative/ptf_window_boundaries2.q (added)
+++ hive/trunk/ql/src/test/queries/clientnegative/ptf_window_boundaries2.q Tue Apr 2 14:16:34 2013
@@ -0,0 +1,17 @@
+-- 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
+);
+
+select p_mfgr, p_name, p_size,
+ sum(p_retailprice) over (range unbounded following) as s1
+ from part distribute by p_mfgr sort by p_name;
+
Added: hive/trunk/ql/src/test/queries/clientpositive/leadlag.q
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/queries/clientpositive/leadlag.q?rev=1463556&view=auto
==============================================================================
--- hive/trunk/ql/src/test/queries/clientpositive/leadlag.q (added)
+++ hive/trunk/ql/src/test/queries/clientpositive/leadlag.q Tue Apr 2 14:16:34 2013
@@ -0,0 +1,82 @@
+DROP TABLE part;
+
+-- data setup
+CREATE TABLE part(
+ p_partkey INT,
+ p_name STRING,
+ p_mfgr STRING,
+ p_brand STRING,
+ p_type STRING,
+ p_size INT,
+ p_container STRING,
+ p_retailprice DOUBLE,
+ p_comment STRING
+);
+
+LOAD DATA LOCAL INPATH '../data/files/part_tiny.txt' overwrite into table part;
+
+--1. testLagWithPTFWindowing
+select p_mfgr, p_name,
+rank() over (partition by p_mfgr order by p_name) as r,
+dense_rank() over (partition by p_mfgr order by p_name) as dr,
+p_retailprice, sum(p_retailprice) over (partition by p_mfgr order by p_name rows between unbounded preceding and current row) as s1,
+p_size, p_size - lag(p_size,1,p_size) over (partition by p_mfgr order by p_name) as deltaSz
+from noop(on part
+partition by p_mfgr
+order by p_name
+);
+
+-- 2. testLagWithWindowingNoPTF
+select p_mfgr, p_name,
+rank() over (partition by p_mfgr order by p_name) as r,
+dense_rank() over (partition by p_mfgr order by p_name) as dr,
+p_retailprice, sum(p_retailprice) over (partition by p_mfgr order by p_name rows between unbounded preceding and current row) as s1,
+p_size, p_size - lag(p_size,1,p_size) over (partition by p_mfgr order by p_name) as deltaSz
+from part
+;
+
+-- 3. testJoinWithLag
+select p1.p_mfgr, p1.p_name,
+p1.p_size, p1.p_size - lag(p1.p_size,1,p1.p_size) over( distribute by p1.p_mfgr sort by p1.p_name) as deltaSz
+from part p1 join part p2 on p1.p_partkey = p2.p_partkey
+ ;
+
+-- 4. testLagInSum
+select p_mfgr,p_name, p_size,
+sum(p_size - lag(p_size,1)) over(distribute by p_mfgr sort by p_mfgr ) as deltaSum
+from part
+window w1 as (rows between 2 preceding and 2 following) ;
+
+-- 5. testLagInSumOverWindow
+select p_mfgr,p_name, p_size,
+sum(p_size - lag(p_size,1)) over w1 as deltaSum
+from part
+window w1 as (distribute by p_mfgr sort by p_mfgr rows between 2 preceding and 2 following) ;
+
+-- 6. testRankInLead
+select p_mfgr, p_name, p_size, r1,
+lead(r1,1,r1) over (distribute by p_mfgr sort by p_name) as deltaRank
+from (
+select p_mfgr, p_name, p_size,
+rank() over(distribute by p_mfgr sort by p_name) as r1
+from part
+) a;
+
+-- 7. testLeadWithPTF
+select p_mfgr, p_name,
+rank() over(distribute by p_mfgr sort by p_name) as r,
+dense_rank() over(distribute by p_mfgr sort by p_name) as dr,
+p_size, p_size - lead(p_size,1,p_size) over(distribute by p_mfgr sort by p_name) as deltaSz
+from noop(on part
+partition by p_mfgr
+order by p_name
+)
+;
+
+-- 8. testOverNoPartitionMultipleAggregate
+select p_name, p_retailprice,
+lead(p_retailprice) over() as l1 ,
+lag(p_retailprice) over() as l2
+from part
+order by p_name;
+
Added: hive/trunk/ql/src/test/queries/clientpositive/leadlag_queries.q
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/queries/clientpositive/leadlag_queries.q?rev=1463556&view=auto
==============================================================================
--- hive/trunk/ql/src/test/queries/clientpositive/leadlag_queries.q (added)
+++ hive/trunk/ql/src/test/queries/clientpositive/leadlag_queries.q Tue Apr 2 14:16:34 2013
@@ -0,0 +1,53 @@
+-- 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) over (partition by p_mfgr order by p_name) as l1,
+lead(p_retailprice,1) over (partition by p_mfgr order by p_name) as l2,
+lead(p_retailprice,1,10) over (partition by p_mfgr order by p_name) as l3,
+lead(p_retailprice,1, p_retailprice) over (partition by p_mfgr order by p_name) as l4,
+p_retailprice - lead(p_retailprice,1,p_retailprice) over (partition by p_mfgr order by p_name)
+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) over (partition by p_mfgr, p_name)
+from part;
+
+-- 3.testLagUDAF
+select p_mfgr, p_retailprice,
+lag(p_retailprice,1) over (partition by p_mfgr order by p_name) as l1,
+lag(p_retailprice) over (partition by p_mfgr order by p_name) as l2,
+lag(p_retailprice,1, p_retailprice) over (partition by p_mfgr order by p_name) as l3,
+lag(p_retailprice,1,10) over (partition by p_mfgr order by p_name) as l4,
+p_retailprice - lag(p_retailprice,1,p_retailprice) over (partition by p_mfgr order by p_name)
+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) over (partition by p_mfgr, p_name)
+from part;
+
+-- 5.testLeadLagUDAF
+select p_mfgr, p_retailprice,
+lead(p_retailprice,1) over (partition by p_mfgr order by p_name) as l1,
+lead(p_retailprice,1, p_retailprice) over (partition by p_mfgr order by p_name) as l2,
+p_retailprice - lead(p_retailprice,1,p_retailprice) over (partition by p_mfgr order by p_name),
+lag(p_retailprice,1) over (partition by p_mfgr order by p_name) as l3,
+lag(p_retailprice,1, p_retailprice) over (partition by p_mfgr order by p_name) as l4
+from part;
Added: hive/trunk/ql/src/test/queries/clientpositive/ptf.q
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/queries/clientpositive/ptf.q?rev=1463556&view=auto
==============================================================================
--- hive/trunk/ql/src/test/queries/clientpositive/ptf.q (added)
+++ hive/trunk/ql/src/test/queries/clientpositive/ptf.q Tue Apr 2 14:16:34 2013
@@ -0,0 +1,308 @@
+DROP TABLE part;
+
+-- data setup
+CREATE TABLE part(
+ p_partkey INT,
+ p_name STRING,
+ p_mfgr STRING,
+ p_brand STRING,
+ p_type STRING,
+ p_size INT,
+ p_container STRING,
+ p_retailprice DOUBLE,
+ p_comment STRING
+);
+
+LOAD DATA LOCAL INPATH '../data/files/part_tiny.txt' overwrite into table part;
+
+--1. test1
+select p_mfgr, p_name, p_size,
+rank() over (partition by p_mfgr order by p_name) as r,
+dense_rank() over (partition by p_mfgr order by p_name) as dr,
+sum(p_retailprice) over (partition by p_mfgr order by p_name rows between unbounded preceding and current row) as s1
+from noop(on part
+ partition by p_mfgr
+ order by p_name
+ );
+
+-- 2. testJoinWithNoop
+select p_mfgr, p_name,
+p_size, p_size - lag(p_size,1,p_size) over (partition by p_mfgr order by p_name) 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)
+;
+
+-- 3. testOnlyPTF
+select p_mfgr, p_name, p_size
+from noop(on part
+partition by p_mfgr
+order by p_name);
+
+-- 4. testPTFAlias
+select p_mfgr, p_name, p_size,
+rank() over (partition by p_mfgr order by p_name) as r,
+dense_rank() over (partition by p_mfgr order by p_name) as dr,
+sum(p_retailprice) over (partition by p_mfgr order by p_name rows between unbounded preceding and current row) as s1
+from noop(on part
+ partition by p_mfgr
+ order by p_name
+ ) abc;
+
+-- 5. testPTFAndWhereWithWindowing
+select p_mfgr, p_name, p_size,
+rank() over (partition by p_mfgr order by p_name) as r,
+dense_rank() over (partition by p_mfgr order by p_name) as dr,
+p_size, p_size - lag(p_size,1,p_size) over (partition by p_mfgr order by p_name) as deltaSz
+from noop(on part
+ partition by p_mfgr
+ order by p_name
+ )
+;
+
+-- 6. testSWQAndPTFAndGBy
+select p_mfgr, p_name, p_size,
+rank() over (partition by p_mfgr order by p_name) as r,
+dense_rank() over (partition by p_mfgr order by p_name) as dr,
+p_size, p_size - lag(p_size,1,p_size) over (partition by p_mfgr order by p_name) as deltaSz
+from noop(on part
+ partition by p_mfgr
+ order by p_name
+ )
+group by p_mfgr, p_name, p_size
+;
+
+-- 7. testJoin
+select abc.*
+from noop(on part
+partition by p_mfgr
+order by p_name
+) abc join part p1 on abc.p_partkey = p1.p_partkey;
+
+-- 8. testJoinRight
+select abc.*
+from part p1 join noop(on part
+partition by p_mfgr
+order by p_name
+) abc on abc.p_partkey = p1.p_partkey;
+
+-- 9. testNoopWithMap
+select p_mfgr, p_name, p_size,
+rank() over (partition by p_mfgr order by p_name, p_size desc) as r
+from noopwithmap(on part
+partition by p_mfgr
+order by p_name, p_size desc);
+
+-- 10. testNoopWithMapWithWindowing
+select p_mfgr, p_name, p_size,
+rank() over (partition by p_mfgr order by p_name) as r,
+dense_rank() over (partition by p_mfgr order by p_name) as dr,
+sum(p_retailprice) over (partition by p_mfgr order by p_name rows between unbounded preceding and current row) as s1
+from noopwithmap(on part
+ partition by p_mfgr
+ order by p_name);
+
+-- 11. testHavingWithWindowingPTFNoGBY
+select p_mfgr, p_name, p_size,
+rank() over (partition by p_mfgr order by p_name) as r,
+dense_rank() over (partition by p_mfgr order by p_name) as dr,
+sum(p_retailprice) over (partition by p_mfgr order by p_name rows between unbounded preceding and current row) as s1
+from noop(on part
+partition by p_mfgr
+order by p_name)
+;
+
+-- 12. testFunctionChain
+select p_mfgr, p_name, p_size,
+rank() over (partition by p_mfgr order by p_name) as r,
+dense_rank() over (partition by p_mfgr order by p_name) as dr,
+sum(p_retailprice) over (partition by p_mfgr order by p_name rows between unbounded preceding and current row) as s1
+from noop(on noopwithmap(on noop(on part
+partition by p_mfgr
+order by p_mfgr, p_name
+)));
+
+-- 13. testPTFAndWindowingInSubQ
+select p_mfgr, p_name,
+sub1.cd, sub1.s1
+from (select p_mfgr, p_name,
+count(p_size) over (partition by p_mfgr order by p_name) as cd,
+p_retailprice,
+sum(p_retailprice) over w1 as s1
+from noop(on part
+partition by p_mfgr
+order by p_name)
+window w1 as (partition by p_mfgr order by p_name rows between 2 preceding and 2 following)
+) sub1 ;
+
+-- 14. testPTFJoinWithWindowingWithCount
+select abc.p_mfgr, abc.p_name,
+rank() over (distribute by abc.p_mfgr sort by abc.p_name) as r,
+dense_rank() over (distribute by abc.p_mfgr sort by abc.p_name) as dr,
+count(abc.p_name) over (distribute by abc.p_mfgr sort by abc.p_name) as cd,
+abc.p_retailprice, sum(abc.p_retailprice) over (distribute by abc.p_mfgr sort by abc.p_name rows between unbounded preceding and current row) as s1,
+abc.p_size, abc.p_size - lag(abc.p_size,1,abc.p_size) over (distribute by abc.p_mfgr sort by abc.p_name) as deltaSz
+from noop(on part
+partition by p_mfgr
+order by p_name
+) abc join part p1 on abc.p_partkey = p1.p_partkey
+;
+
+-- 15. testDistinctInSelectWithPTF
+select DISTINCT p_mfgr, p_name, p_size
+from noop(on part
+partition by p_mfgr
+order by p_name);
+
+
+-- 16. testViewAsTableInputToPTF
+create view IF NOT EXISTS mfgr_price_view as
+select p_mfgr, p_brand,
+sum(p_retailprice) as s
+from part
+group by p_mfgr, p_brand;
+
+select p_mfgr, p_brand, s,
+sum(s) over w1 as s1
+from noop(on mfgr_price_view
+partition by p_mfgr
+order by p_mfgr)
+window w1 as ( partition by p_mfgr order by p_brand rows between 2 preceding and current row);
+
+-- 17. testMultipleInserts2SWQsWithPTF
+CREATE TABLE part_4(
+p_mfgr STRING,
+p_name STRING,
+p_size INT,
+r INT,
+dr INT,
+s DOUBLE);
+
+CREATE TABLE part_5(
+p_mfgr STRING,
+p_name STRING,
+p_size INT,
+s1 INT,
+s2 INT,
+r INT,
+dr INT,
+cud DOUBLE,
+fv1 INT);
+
+from noop(on part
+partition by p_mfgr
+order by p_name)
+INSERT OVERWRITE TABLE part_4 select p_mfgr, p_name, p_size,
+rank() over (distribute by p_mfgr sort by p_name) as r,
+dense_rank() over (distribute by p_mfgr sort by p_name) as dr,
+sum(p_retailprice) over (distribute by p_mfgr sort by p_name rows between unbounded preceding and current row) as s
+INSERT OVERWRITE TABLE part_5 select p_mfgr,p_name, p_size,
+sum(p_size) over (distribute by p_mfgr sort by p_mfgr, p_name rows between unbounded preceding and current row) as s1,
+sum(p_size) over (distribute by p_mfgr sort by p_size range between 5 preceding and current row) as s2,
+rank() over (distribute by p_mfgr sort by p_mfgr, p_name) as r,
+dense_rank() over (distribute by p_mfgr sort by p_mfgr, p_name) as dr,
+cume_dist() over (distribute by p_mfgr sort by p_mfgr, p_name) as cud,
+first_value(p_size, true) over w1 as fv1
+window w1 as (distribute by p_mfgr sort by p_mfgr, p_name rows between 2 preceding and 2 following);
+
+select * from part_4;
+
+select * from part_5;
+
+-- 18. testMulti2OperatorsFunctionChainWithMap
+select p_mfgr, p_name,
+rank() over (partition by p_mfgr,p_name) as r,
+dense_rank() over (partition by p_mfgr,p_name) as dr,
+p_size, sum(p_size) over (partition by p_mfgr,p_name rows between unbounded preceding and current row) as s1
+from noop(on
+ noopwithmap(on
+ noop(on
+ noop(on part
+ partition by p_mfgr
+ order by p_mfgr)
+ )
+ partition by p_mfgr,p_name
+ order by p_mfgr,p_name)
+ partition by p_mfgr,p_name
+ order by p_mfgr,p_name) ;
+
+-- 19. testMulti3OperatorsFunctionChain
+select p_mfgr, p_name,
+rank() over (partition by p_mfgr order by p_name) as r,
+dense_rank() over (partition by p_mfgr order by p_name) as dr,
+p_size, sum(p_size) over (partition by p_mfgr order by p_name rows between unbounded preceding and current row) as s1
+from noop(on
+ noop(on
+ noop(on
+ noop(on part
+ partition by p_mfgr
+ order by p_mfgr)
+ )
+ partition by p_mfgr,p_name
+ order by p_mfgr,p_name)
+ partition by p_mfgr
+ order by p_mfgr ) ;
+
+-- 20. testMultiOperatorChainWithNoWindowing
+select p_mfgr, p_name,
+rank() over (partition by p_mfgr order by p_name) as r,
+dense_rank() over (partition by p_mfgr order by p_name) as dr,
+p_size, sum(p_size) over (partition by p_mfgr order by p_name) as s1
+from noop(on
+ noop(on
+ noop(on
+ noop(on part
+ partition by p_mfgr,p_name
+ order by p_mfgr,p_name)
+ )
+ partition by p_mfgr
+ order by p_mfgr));
+
+
+-- 21. testMultiOperatorChainEndsWithNoopMap
+select p_mfgr, p_name,
+rank() over (partition by p_mfgr,p_name) as r,
+dense_rank() over (partition by p_mfgr,p_name) as dr,
+p_size, sum(p_size) over (partition by p_mfgr,p_name rows between unbounded preceding and current row) as s1
+from noopwithmap(on
+ noop(on
+ noop(on
+ noop(on part
+ partition by p_mfgr,p_name
+ order by p_mfgr,p_name)
+ )
+ partition by p_mfgr
+ order by p_mfgr)
+ partition by p_mfgr,p_name
+ order by p_mfgr,p_name);
+
+-- 22. testMultiOperatorChainWithDiffPartitionForWindow1
+select p_mfgr, p_name,
+rank() over (partition by p_mfgr,p_name order by p_mfgr,p_name) as r,
+dense_rank() over (partition by p_mfgr,p_name order by p_mfgr,p_name) as dr,
+p_size,
+sum(p_size) over (partition by p_mfgr,p_name order by p_mfgr,p_name rows between unbounded preceding and current row) as s1,
+sum(p_size) over (partition by p_mfgr,p_name order by p_mfgr,p_name rows between unbounded preceding and current row) as s2
+from noop(on
+ noopwithmap(on
+ noop(on part
+ partition by p_mfgr, p_name
+ order by p_mfgr, p_name)
+ partition by p_mfgr
+ order by p_mfgr
+ ));
+
+-- 23. testMultiOperatorChainWithDiffPartitionForWindow2
+select p_mfgr, p_name,
+rank() over (partition by p_mfgr order by p_mfgr) as r,
+dense_rank() over (partition by p_mfgr order by p_mfgr) as dr,
+p_size,
+sum(p_size) over (partition by p_mfgr order by p_mfgr rows between unbounded preceding and current row) as s1,
+sum(p_size) over (partition by p_mfgr order by p_mfgr rows between unbounded preceding and current row) as s2
+from noopwithmap(on
+ noop(on
+ noop(on part
+ partition by p_mfgr, p_name
+ order by p_mfgr, p_name)
+ ));
+
Added: hive/trunk/ql/src/test/queries/clientpositive/ptf_general_queries.q
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/queries/clientpositive/ptf_general_queries.q?rev=1463556&view=auto
==============================================================================
--- hive/trunk/ql/src/test/queries/clientpositive/ptf_general_queries.q (added)
+++ hive/trunk/ql/src/test/queries/clientpositive/ptf_general_queries.q Tue Apr 2 14:16:34 2013
@@ -0,0 +1,34 @@
+DROP TABLE part;
+
+-- data setup
+CREATE TABLE part(
+ p_partkey INT,
+ p_name STRING,
+ p_mfgr STRING,
+ p_brand STRING,
+ p_type STRING,
+ p_size INT,
+ p_container STRING,
+ p_retailprice DOUBLE,
+ p_comment STRING
+);
+
+LOAD DATA LOCAL INPATH '../data/files/part_tiny.txt' overwrite into table part;
+
+-- 1. testNoPTFNoWindowing
+select p_mfgr, p_name, p_size
+from part
+distribute by p_mfgr
+sort by p_name ;
+
+-- 2. testUDAFsNoWindowingNoPTFNoGBY
+select p_mfgr,p_name, p_retailprice,
+sum(p_retailprice) over(partition by p_mfgr order by p_mfgr) as s,
+min(p_retailprice) over(partition by p_mfgr order by p_mfgr) as mi,
+max(p_retailprice) over(partition by p_mfgr order by p_mfgr) as ma,
+avg(p_retailprice) over(partition by p_mfgr order by p_mfgr) as av
+from part
+;
+
+-- 3. testConstExprInSelect
+select 'tst1' as key, count(1) as value from part;
Added: hive/trunk/ql/src/test/queries/clientpositive/ptf_npath.q
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/queries/clientpositive/ptf_npath.q?rev=1463556&view=auto
==============================================================================
--- hive/trunk/ql/src/test/queries/clientpositive/ptf_npath.q (added)
+++ hive/trunk/ql/src/test/queries/clientpositive/ptf_npath.q Tue Apr 2 14:16:34 2013
@@ -0,0 +1,36 @@
+DROP TABLE flights_tiny;
+
+create table flights_tiny (
+ORIGIN_CITY_NAME string,
+DEST_CITY_NAME string,
+YEAR int,
+MONTH int,
+DAY_OF_MONTH int,
+ARR_DELAY float,
+FL_NUM string
+);
+
+LOAD DATA LOCAL INPATH '../data/files/flights_tiny.txt' OVERWRITE INTO TABLE flights_tiny;
+
+-- 1. basic Npath test
+select origin_city_name, fl_num, year, month, day_of_month, sz, tpath
+from npath(on
+ flights_tiny
+ distribute by fl_num
+ sort by year, month, day_of_month
+ arg1('LATE.LATE+'),
+ arg2('LATE'), arg3(arr_delay > 15),
+ arg4('origin_city_name, fl_num, year, month, day_of_month, size(tpath) as sz, tpath as tpath')
+ );
+
+-- 2. Npath on 1 partition
+select origin_city_name, fl_num, year, month, day_of_month, sz, tpath
+from npath(on
+ flights_tiny
+ sort by year, month, day_of_month
+ arg1('LATE.LATE+'),
+ arg2('LATE'), arg3(arr_delay > 15),
+ arg4('origin_city_name, fl_num, year, month, day_of_month, size(tpath) as sz, tpath as tpath')
+ )
+where fl_num = 1142;
+
\ No newline at end of file
Added: hive/trunk/ql/src/test/queries/clientpositive/ptf_rcfile.q
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/queries/clientpositive/ptf_rcfile.q?rev=1463556&view=auto
==============================================================================
--- hive/trunk/ql/src/test/queries/clientpositive/ptf_rcfile.q (added)
+++ hive/trunk/ql/src/test/queries/clientpositive/ptf_rcfile.q Tue Apr 2 14:16:34 2013
@@ -0,0 +1,24 @@
+DROP TABLE part_rc;
+
+CREATE TABLE part_rc(
+ 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
+) STORED AS RCFILE ;
+
+LOAD DATA LOCAL INPATH '../data/files/part.rc' overwrite into table part_rc;
+
+-- testWindowingPTFWithPartRC
+select p_mfgr, p_name, p_size,
+rank() over (partition by p_mfgr order by p_name) as r,
+dense_rank() over (partition by p_mfgr order by p_name) as dr,
+sum(p_retailprice) over (partition by p_mfgr order by p_name rows between unbounded preceding and current row) as s1
+from noop(on part_rc
+partition by p_mfgr
+order by p_name);
Added: hive/trunk/ql/src/test/queries/clientpositive/ptf_seqfile.q
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/queries/clientpositive/ptf_seqfile.q?rev=1463556&view=auto
==============================================================================
--- hive/trunk/ql/src/test/queries/clientpositive/ptf_seqfile.q (added)
+++ hive/trunk/ql/src/test/queries/clientpositive/ptf_seqfile.q Tue Apr 2 14:16:34 2013
@@ -0,0 +1,24 @@
+DROP TABLE part_seq;
+
+CREATE TABLE part_seq(
+ 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
+) STORED AS SEQUENCEFILE ;
+
+LOAD DATA LOCAL INPATH '../data/files/part.seq' overwrite into table part_seq;
+
+-- testWindowingPTFWithPartSeqFile
+select p_mfgr, p_name, p_size,
+rank() over (partition by p_mfgr order by p_name) as r,
+dense_rank() over (partition by p_mfgr order by p_name) as dr,
+sum(p_retailprice) over (partition by p_mfgr order by p_name rows between unbounded preceding and current row) as s1
+from noop(on part_seq
+partition by p_mfgr
+order by p_name);
Added: hive/trunk/ql/src/test/queries/clientpositive/windowing.q
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/queries/clientpositive/windowing.q?rev=1463556&view=auto
==============================================================================
--- hive/trunk/ql/src/test/queries/clientpositive/windowing.q (added)
+++ hive/trunk/ql/src/test/queries/clientpositive/windowing.q Tue Apr 2 14:16:34 2013
@@ -0,0 +1,427 @@
+DROP TABLE part;
+
+-- data setup
+CREATE TABLE part(
+ p_partkey INT,
+ p_name STRING,
+ p_mfgr STRING,
+ p_brand STRING,
+ p_type STRING,
+ p_size INT,
+ p_container STRING,
+ p_retailprice DOUBLE,
+ p_comment STRING
+);
+
+LOAD DATA LOCAL INPATH '../data/files/part_tiny.txt' overwrite into table part;
+
+-- 1. testWindowing
+select p_mfgr, p_name, p_size,
+rank() over(distribute by p_mfgr sort by p_name) as r,
+dense_rank() over(distribute by p_mfgr sort by p_name) as dr,
+sum(p_retailprice) over (distribute by p_mfgr sort by p_name rows between unbounded preceding and current row) as s1
+from part
+;
+
+-- 2. testGroupByWithPartitioning
+select p_mfgr, p_name, p_size,
+min(p_retailprice),
+rank() over(distribute by p_mfgr sort by p_name)as r,
+dense_rank() over(distribute by p_mfgr sort by p_name) as dr,
+p_size, p_size - lag(p_size,1,p_size) over(distribute by p_mfgr sort by p_name) as deltaSz
+from part
+group by p_mfgr, p_name, p_size
+;
+
+-- 3. testGroupByHavingWithSWQ
+select p_mfgr, p_name, p_size, min(p_retailprice),
+rank() over(distribute by p_mfgr sort by p_name) as r,
+dense_rank() over(distribute by p_mfgr sort by p_name) as dr,
+p_size, p_size - lag(p_size,1,p_size) over(distribute by p_mfgr sort by p_name) as deltaSz
+from part
+group by p_mfgr, p_name, p_size
+having p_size > 0
+;
+
+-- 4. testCount
+select p_mfgr, p_name,
+count(p_size) over(distribute by p_mfgr sort by p_name) as cd
+from part
+;
+
+-- 5. testCountWithWindowingUDAF
+select p_mfgr, p_name,
+rank() over(distribute by p_mfgr sort by p_name) as r,
+dense_rank() over(distribute by p_mfgr sort by p_name) as dr,
+count(p_size) over(distribute by p_mfgr sort by p_name) as cd,
+p_retailprice, sum(p_retailprice) over (distribute by p_mfgr sort by p_name rows between unbounded preceding and current row) as s1,
+p_size, p_size - lag(p_size,1,p_size) over(distribute by p_mfgr sort by p_name) as deltaSz
+from part
+;
+
+-- 6. testCountInSubQ
+select sub1.r, sub1.dr, sub1.cd, sub1.s1, sub1.deltaSz
+from (select p_mfgr, p_name,
+rank() over(distribute by p_mfgr sort by p_name) as r,
+dense_rank() over(distribute by p_mfgr sort by p_name) as dr,
+count(p_size) over(distribute by p_mfgr sort by p_name) as cd,
+p_retailprice, sum(p_retailprice) over (distribute by p_mfgr sort by p_name rows between unbounded preceding and current row) as s1,
+p_size, p_size - lag(p_size,1,p_size) over(distribute by p_mfgr sort by p_name) as deltaSz
+from part
+) sub1;
+
+-- 7. testJoinWithWindowingAndPTF
+select abc.p_mfgr, abc.p_name,
+rank() over(distribute by abc.p_mfgr sort by abc.p_name) as r,
+dense_rank() over(distribute by abc.p_mfgr sort by abc.p_name) as dr,
+abc.p_retailprice, sum(abc.p_retailprice) over (distribute by abc.p_mfgr sort by abc.p_name rows between unbounded preceding and current row) as s1,
+abc.p_size, abc.p_size - lag(abc.p_size,1,abc.p_size) over(distribute by abc.p_mfgr sort by abc.p_name) as deltaSz
+from noop(on part
+partition by p_mfgr
+order by p_name
+) abc join part p1 on abc.p_partkey = p1.p_partkey
+;
+
+-- 8. testMixedCaseAlias
+select p_mfgr, p_name, p_size,
+rank() over(distribute by p_mfgr sort by p_name, p_size desc) as R
+from part
+;
+
+-- 9. testHavingWithWindowingNoGBY
+select p_mfgr, p_name, p_size,
+rank() over(distribute by p_mfgr sort by p_name) as r,
+dense_rank() over(distribute by p_mfgr sort by p_name) as dr,
+sum(p_retailprice) over (distribute by p_mfgr sort by p_name rows between unbounded preceding and current row) as s1
+from part
+;
+
+-- 10. testHavingWithWindowingCondRankNoGBY
+select p_mfgr, p_name, p_size,
+rank() over(distribute by p_mfgr sort by p_name) as r,
+dense_rank() over(distribute by p_mfgr sort by p_name) as dr,
+sum(p_retailprice) over (distribute by p_mfgr sort by p_name rows between unbounded preceding and current row) as s1
+from part
+;
+
+-- 11. testFirstLast
+select p_mfgr,p_name, p_size,
+sum(p_size) over (distribute by p_mfgr sort by p_mfgr rows between current row and current row) as s2,
+first_value(p_size) over w1 as f,
+last_value(p_size, false) over w1 as l
+from part
+window w1 as (distribute by p_mfgr sort by p_mfgr rows between 2 preceding and 2 following);
+
+-- 12. testFirstLastWithWhere
+select p_mfgr,p_name, p_size,
+rank() over(distribute by p_mfgr sort by p_mfgr) as r,
+sum(p_size) over (distribute by p_mfgr sort by p_mfgr rows between current row and current row) as s2,
+first_value(p_size) over w1 as f,
+last_value(p_size, false) over w1 as l
+from part
+where p_mfgr = 'Manufacturer#3'
+window w1 as (distribute by p_mfgr sort by p_mfgr rows between 2 preceding and 2 following);
+
+-- 13. testSumWindow
+select p_mfgr,p_name, p_size,
+sum(p_size) over w1 as s1,
+sum(p_size) over (distribute by p_mfgr sort by p_mfgr rows between current row and current row) as s2
+from part
+window w1 as (distribute by p_mfgr sort by p_mfgr rows between 2 preceding and 2 following);
+
+-- 14. testNoSortClause
+select p_mfgr,p_name, p_size,
+rank() over(distribute by p_mfgr) as r, dense_rank() over(distribute by p_mfgr) as dr
+from part
+window w1 as (distribute by p_mfgr rows between 2 preceding and 2 following);
+
+-- 15. testExpressions
+select p_mfgr,p_name, p_size,
+rank() over(distribute by p_mfgr sort by p_mfgr) as r,
+dense_rank() over(distribute by p_mfgr sort by p_mfgr) as dr,
+cume_dist() over(distribute by p_mfgr sort by p_mfgr) as cud,
+percent_rank() over(distribute by p_mfgr sort by p_mfgr) as pr,
+ntile(3) over(distribute by p_mfgr sort by p_mfgr) as nt,
+count(p_size) over(distribute by p_mfgr sort by p_mfgr) as ca,
+avg(p_size) over(distribute by p_mfgr sort by p_mfgr) as avg,
+stddev(p_size) over(distribute by p_mfgr sort by p_mfgr) as st,
+first_value(p_size % 5) over(distribute by p_mfgr sort by p_mfgr) as fv,
+last_value(p_size) over(distribute by p_mfgr sort by p_mfgr) as lv,
+first_value(p_size) over w1 as fvW1
+from part
+window w1 as (distribute by p_mfgr sort by p_mfgr, p_name rows between 2 preceding and 2 following);
+
+-- 16. testMultipleWindows
+select p_mfgr,p_name, p_size,
+ rank() over(distribute by p_mfgr sort by p_mfgr) as r,
+ dense_rank() over(distribute by p_mfgr sort by p_mfgr) as dr,
+cume_dist() over(distribute by p_mfgr sort by p_mfgr) as cud,
+sum(p_size) over (distribute by p_mfgr sort by p_mfgr, p_name rows between unbounded preceding and current row) as s1,
+sum(p_size) over (distribute by p_mfgr sort by p_size range between 5 preceding and current row) as s2,
+first_value(p_size) over w1 as fv1
+from part
+window w1 as (distribute by p_mfgr sort by p_mfgr, p_name rows between 2 preceding and 2 following);
+
+-- 17. testCountStar
+select p_mfgr,p_name, p_size,
+count(*) over(distribute by p_mfgr sort by p_mfgr ) as c,
+count(p_size) over(distribute by p_mfgr sort by p_mfgr) as ca,
+first_value(p_size) over w1 as fvW1
+from part
+window w1 as (distribute by p_mfgr sort by p_mfgr, p_name rows between 2 preceding and 2 following);
+
+-- 18. testUDAFs
+select p_mfgr,p_name, p_size,
+sum(p_retailprice) over w1 as s,
+min(p_retailprice) over w1 as mi,
+max(p_retailprice) over w1 as ma,
+avg(p_retailprice) over w1 as ag
+from part
+window w1 as (distribute by p_mfgr sort by p_mfgr, p_name rows between 2 preceding and 2 following);
+
+-- 19. testUDAFsWithGBY
+select p_mfgr,p_name, p_size, p_retailprice,
+sum(p_retailprice) over w1 as s,
+min(p_retailprice) as mi ,
+max(p_retailprice) as ma ,
+avg(p_retailprice) over w1 as ag
+from part
+group by p_mfgr,p_name, p_size, p_retailprice
+window w1 as (distribute by p_mfgr sort by p_mfgr, p_name rows between 2 preceding and 2 following);
+
+-- 20. testSTATs
+select p_mfgr,p_name, p_size,
+stddev(p_retailprice) over w1 as sdev,
+stddev_pop(p_retailprice) over w1 as sdev_pop,
+collect_set(p_size) over w1 as uniq_size,
+variance(p_retailprice) over w1 as var,
+corr(p_size, p_retailprice) over w1 as cor,
+covar_pop(p_size, p_retailprice) over w1 as covarp
+from part
+window w1 as (distribute by p_mfgr sort by p_mfgr, p_name rows between 2 preceding and 2 following);
+
+-- 21. testDISTs
+select p_mfgr,p_name, p_size,
+histogram_numeric(p_retailprice, 5) over w1 as hist,
+percentile(p_partkey, 0.5) over w1 as per,
+row_number() over(distribute by p_mfgr sort by p_mfgr) as rn
+from part
+window w1 as (distribute by p_mfgr sort by p_mfgr, p_name rows between 2 preceding and 2 following);
+
+-- 22. testViewAsTableInputWithWindowing
+create view IF NOT EXISTS mfgr_price_view as
+select p_mfgr, p_brand,
+sum(p_retailprice) as s
+from part
+group by p_mfgr, p_brand;
+
+select p_mfgr, p_brand, s,
+sum(s) over w1 as s1
+from mfgr_price_view
+window w1 as (distribute by p_mfgr sort by p_mfgr rows between 2 preceding and current row);
+
+-- 23. testCreateViewWithWindowingQuery
+create view IF NOT EXISTS mfgr_brand_price_view as
+select p_mfgr, p_brand,
+sum(p_retailprice) over w1 as s
+from part
+window w1 as (distribute by p_mfgr sort by p_mfgr rows between 2 preceding and current row);
+
+select * from mfgr_brand_price_view;
+
+-- 24. testLateralViews
+select p_mfgr, p_name,
+lv_col, p_size, sum(p_size) over w1 as s
+from (select p_mfgr, p_name, p_size, array(1,2,3) arr from part) p
+lateral view explode(arr) part_lv as lv_col
+window w1 as (distribute by p_mfgr sort by p_name rows between 2 preceding and current row);
+
+-- 25. testMultipleInserts3SWQs
+CREATE TABLE part_1(
+p_mfgr STRING,
+p_name STRING,
+p_size INT,
+r INT,
+dr INT,
+s DOUBLE);
+
+CREATE TABLE part_2(
+p_mfgr STRING,
+p_name STRING,
+p_size INT,
+r INT,
+dr INT,
+cud INT,
+s1 DOUBLE,
+s2 DOUBLE,
+fv1 INT);
+
+CREATE TABLE part_3(
+p_mfgr STRING,
+p_name STRING,
+p_size INT,
+c INT,
+ca INT,
+fv INT);
+
+from part
+INSERT OVERWRITE TABLE part_1
+select p_mfgr, p_name, p_size,
+rank() over(distribute by p_mfgr sort by p_name ) as r,
+dense_rank() over(distribute by p_mfgr sort by p_name ) as dr,
+sum(p_retailprice) over (distribute by p_mfgr sort by p_name rows between unbounded preceding and current row) as s
+INSERT OVERWRITE TABLE part_2
+select p_mfgr,p_name, p_size,
+rank() over(distribute by p_mfgr sort by p_mfgr) as r,
+dense_rank() over(distribute by p_mfgr sort by p_mfgr) as dr,
+cume_dist() over(distribute by p_mfgr sort by p_mfgr) as cud,
+sum(p_size) over (distribute by p_mfgr sort by p_mfgr, p_name rows between unbounded preceding and current row) as s1,
+sum(p_size) over (distribute by p_mfgr sort by p_size range between 5 preceding and current row) as s2,
+first_value(p_size) over w1 as fv1
+window w1 as (distribute by p_mfgr sort by p_mfgr, p_name rows between 2 preceding and 2 following)
+INSERT OVERWRITE TABLE part_3
+select p_mfgr,p_name, p_size,
+count(*) over(distribute by p_mfgr sort by p_mfgr) as c,
+count(p_size) over(distribute by p_mfgr sort by p_mfgr) as ca,
+first_value(p_size) over w1 as fv
+window w1 as (distribute by p_mfgr sort by p_mfgr, p_name rows between 2 preceding and 2 following);
+
+select * from part_1;
+
+select * from part_2;
+
+select * from part_3;
+
+-- 26. testGroupByHavingWithSWQAndAlias
+select p_mfgr, p_name, p_size, min(p_retailprice) as mi,
+rank() over(distribute by p_mfgr sort by p_name) as r,
+dense_rank() over(distribute by p_mfgr sort by p_name) as dr,
+p_size, p_size - lag(p_size,1,p_size) over(distribute by p_mfgr sort by p_name) as deltaSz
+from part
+group by p_mfgr, p_name, p_size
+having p_size > 0
+;
+
+-- 27. testMultipleRangeWindows
+select p_mfgr,p_name, p_size,
+sum(p_size) over (distribute by p_mfgr sort by p_size range between 10 preceding and current row) as s2,
+sum(p_size) over (distribute by p_mfgr sort by p_size range between current row and 10 following ) as s1
+from part
+window w1 as (rows between 2 preceding and 2 following);
+
+-- 28. testPartOrderInUDAFInvoke
+select p_mfgr, p_name, p_size,
+sum(p_size) over (partition by p_mfgr order by p_name rows between 2 preceding and 2 following) as s
+from part;
+
+-- 29. testPartOrderInWdwDef
+select p_mfgr, p_name, p_size,
+sum(p_size) over w1 as s
+from part
+window w1 as (partition by p_mfgr order by p_name rows between 2 preceding and 2 following);
+
+-- 30. testDefaultPartitioningSpecRules
+select p_mfgr, p_name, p_size,
+sum(p_size) over w1 as s,
+sum(p_size) over w2 as s2
+from part
+window w1 as (distribute by p_mfgr sort by p_name rows between 2 preceding and 2 following),
+ w2 as (partition by p_mfgr order by p_name);
+
+-- 31. testWindowCrossReference
+select p_mfgr, p_name, p_size,
+sum(p_size) over w1 as s1,
+sum(p_size) over w2 as s2
+from part
+window w1 as (partition by p_mfgr order by p_mfgr rows between 2 preceding and 2 following),
+ w2 as w1;
+
+
+-- 32. testWindowInheritance
+select p_mfgr, p_name, p_size,
+sum(p_size) over w1 as s1,
+sum(p_size) over w2 as s2
+from part
+window w1 as (partition by p_mfgr order by p_mfgr rows between 2 preceding and 2 following),
+ w2 as (w1 rows between unbounded preceding and current row);
+
+
+-- 33. testWindowForwardReference
+select p_mfgr, p_name, p_size,
+sum(p_size) over w1 as s1,
+sum(p_size) over w2 as s2,
+sum(p_size) over w3 as s3
+from part
+window w1 as (distribute by p_mfgr sort by p_mfgr rows between 2 preceding and 2 following),
+ w2 as w3,
+ w3 as (distribute by p_mfgr sort by p_mfgr rows between unbounded preceding and current row);
+
+
+-- 34. testWindowDefinitionPropagation
+select p_mfgr, p_name, p_size,
+sum(p_size) over w1 as s1,
+sum(p_size) over w2 as s2,
+sum(p_size) over (w3 rows between 2 preceding and 2 following) as s3
+from part
+window w1 as (distribute by p_mfgr sort by p_mfgr rows between 2 preceding and 2 following),
+ w2 as w3,
+ w3 as (distribute by p_mfgr sort by p_mfgr rows between unbounded preceding and current row);
+
+-- 35. testDistinctWithWindowing
+select DISTINCT p_mfgr, p_name, p_size,
+sum(p_size) over w1 as s
+from part
+window w1 as (distribute by p_mfgr sort by p_name rows between 2 preceding and 2 following);
+
+-- 36. testRankWithPartitioning
+select p_mfgr, p_name, p_size,
+rank() over (partition by p_mfgr order by p_name ) as r
+from part;
+
+-- 37. testPartitioningVariousForms
+select p_mfgr, p_name, p_size,
+sum(p_retailprice) over (partition by p_mfgr order by p_mfgr) as s1,
+min(p_retailprice) over (partition by p_mfgr) as s2,
+max(p_retailprice) over (distribute by p_mfgr sort by p_mfgr) as s3,
+avg(p_retailprice) over (distribute by p_mfgr) as s4,
+count(p_retailprice) over (cluster by p_mfgr ) as s5
+from part;
+
+-- 38. testPartitioningVariousForms2
+select p_mfgr, p_name, p_size,
+sum(p_retailprice) over (partition by p_mfgr, p_name order by p_mfgr, p_name rows between unbounded preceding and current row) as s1,
+min(p_retailprice) over (distribute by p_mfgr, p_name sort by p_mfgr, p_name rows between unbounded preceding and current row) as s2,
+max(p_retailprice) over (partition by p_mfgr, p_name order by p_name) as s3
+from part;
+
+-- 39. testUDFOnOrderCols
+select p_mfgr, p_type, substr(p_type, 2) as short_ptype,
+rank() over (partition by p_mfgr order by substr(p_type, 2)) as r
+from part;
+
+-- 40. testNoBetweenForRows
+select p_mfgr, p_name, p_size,
+ sum(p_retailprice) over (distribute by p_mfgr sort by p_name rows unbounded preceding) as s1
+ from part ;
+
+-- 41. testNoBetweenForRange
+select p_mfgr, p_name, p_size,
+ sum(p_retailprice) over (distribute by p_mfgr sort by p_size range unbounded preceding) as s1
+ from part ;
+
+-- 42. testUnboundedFollowingForRows
+select p_mfgr, p_name, p_size,
+ sum(p_retailprice) over (distribute by p_mfgr sort by p_name rows between current row and unbounded following) as s1
+ from part ;
+
+-- 43. testUnboundedFollowingForRange
+select p_mfgr, p_name, p_size,
+ sum(p_retailprice) over (distribute by p_mfgr sort by p_size range between current row and unbounded following) as s1
+ from part ;
+
+-- 44. testOverNoPartitionSingleAggregate
+select p_name, p_retailprice,
+avg(p_retailprice) over()
+from part
+order by p_name;
+
Added: hive/trunk/ql/src/test/queries/clientpositive/windowing_columnPruning.q
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/queries/clientpositive/windowing_columnPruning.q?rev=1463556&view=auto
==============================================================================
--- hive/trunk/ql/src/test/queries/clientpositive/windowing_columnPruning.q (added)
+++ hive/trunk/ql/src/test/queries/clientpositive/windowing_columnPruning.q Tue Apr 2 14:16:34 2013
@@ -0,0 +1,32 @@
+DROP TABLE part;
+
+-- data setup
+CREATE TABLE part(
+ p_partkey INT,
+ p_name STRING,
+ p_mfgr STRING,
+ p_brand STRING,
+ p_type STRING,
+ p_size INT,
+ p_container STRING,
+ p_retailprice DOUBLE,
+ p_comment STRING
+);
+
+LOAD DATA LOCAL INPATH '../data/files/part_tiny.txt' overwrite into table part;
+
+-- 1. testQueryLevelPartitionColsNotInSelect
+select p_size,
+sum(p_retailprice) over (distribute by p_mfgr sort by p_name rows between unbounded preceding and current row) as s1
+from part
+ ;
+
+-- 2. testWindowPartitionColsNotInSelect
+select p_size,
+sum(p_retailprice) over (distribute by p_mfgr sort by p_name rows between unbounded preceding and current row) as s1
+from part;
+
+-- 3. testHavingColNotInSelect
+select p_mfgr,
+sum(p_retailprice) over (distribute by p_mfgr sort by p_name rows between unbounded preceding and current row) as s1
+from part;
Added: hive/trunk/ql/src/test/queries/clientpositive/windowing_expressions.q
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/queries/clientpositive/windowing_expressions.q?rev=1463556&view=auto
==============================================================================
--- hive/trunk/ql/src/test/queries/clientpositive/windowing_expressions.q (added)
+++ hive/trunk/ql/src/test/queries/clientpositive/windowing_expressions.q Tue Apr 2 14:16:34 2013
@@ -0,0 +1,59 @@
+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)
+ over(distribute by p_mfgr sort by p_retailprice),
+max(p_retailprice) - min(p_retailprice) = last_value(p_retailprice) - first_value(p_retailprice)
+ over(distribute by p_mfgr sort by p_retailprice)
+from part
+;
+
+select p_mfgr, p_retailprice, p_size,
+rank() over (distribute by p_mfgr sort by p_retailprice) as r,
+sum(p_retailprice) over (distribute by p_mfgr sort by p_retailprice rows between unbounded preceding and current row) as s2,
+sum(p_retailprice) - 5 over (distribute by p_mfgr sort by p_retailprice rows between unbounded preceding and current row) as s1
+from part
+;
+
+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;
+
+select p_mfgr, avg(p_retailprice) over(partition by p_mfgr, p_type order by p_mfgr) from part;
+
+select p_mfgr, avg(p_retailprice) over(partition by p_mfgr order by p_type,p_mfgr rows between unbounded preceding and current row) from part;
Added: hive/trunk/ql/src/test/queries/clientpositive/windowing_multipartitioning.q
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/queries/clientpositive/windowing_multipartitioning.q?rev=1463556&view=auto
==============================================================================
--- hive/trunk/ql/src/test/queries/clientpositive/windowing_multipartitioning.q (added)
+++ hive/trunk/ql/src/test/queries/clientpositive/windowing_multipartitioning.q Tue Apr 2 14:16:34 2013
@@ -0,0 +1,42 @@
+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 s, rank() over (partition by s order by si), sum(b) over (partition by s order by si) from over10k limit 100;
+
+select s,
+rank() over (partition by s order by dec desc),
+sum(b) over (partition by s order by ts desc)
+from over10k
+where s = 'tom allen' or s = 'bob steinbeck';
+
+select s, sum(i) over (partition by s), sum(f) over (partition by si) from over10k where s = 'tom allen' or s = 'bob steinbeck' ;
+
+select s, rank() over (partition by s order by bo), rank() over (partition by si order by bin desc) from over10k
+where s = 'tom allen' or s = 'bob steinbeck';
+
+select s, sum(f) over (partition by i), row_number() over () from over10k where s = 'tom allen' or s = 'bob steinbeck';
+
+select s, rank() over w1,
+rank() over w2
+from over10k
+where s = 'tom allen' or s = 'bob steinbeck'
+window
+w1 as (partition by s order by dec),
+w2 as (partition by si order by f)
+;
\ No newline at end of file
Added: hive/trunk/ql/src/test/queries/clientpositive/windowing_navfn.q
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/queries/clientpositive/windowing_navfn.q?rev=1463556&view=auto
==============================================================================
--- hive/trunk/ql/src/test/queries/clientpositive/windowing_navfn.q (added)
+++ hive/trunk/ql/src/test/queries/clientpositive/windowing_navfn.q Tue Apr 2 14:16:34 2013
@@ -0,0 +1,30 @@
+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 s, row_number() over (partition by d order by dec) from over10k limit 100;
+
+select i, lead(s) over (partition by bin order by d desc) from over10k limit 100;
+
+select i, lag(dec) over (partition by i order by s) from over10k limit 100;
+
+select s, last_value(t) over (partition by d order by f) from over10k limit 100;
+
+select s, first_value(s) over (partition by bo order by s) from over10k limit 100;
+
+
Added: hive/trunk/ql/src/test/queries/clientpositive/windowing_ntile.q
URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/queries/clientpositive/windowing_ntile.q?rev=1463556&view=auto
==============================================================================
--- hive/trunk/ql/src/test/queries/clientpositive/windowing_ntile.q (added)
+++ hive/trunk/ql/src/test/queries/clientpositive/windowing_ntile.q Tue Apr 2 14:16:34 2013
@@ -0,0 +1,28 @@
+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 i, ntile(10) over (partition by s) from over10k limit 100;
+
+select s, ntile(100) over (partition by i) from over10k limit 100;
+
+select f, ntile(4) over (partition by d) from over10k limit 100;
+
+select d, ntile(1000) over (partition by dec) from over10k limit 100;
+
+