Repository: hive Updated Branches: refs/heads/master 3c1dfe379 -> d97e4874d
HIVE-16178: corr/covar_samp UDAF standard compliance (Zoltan Haindrich, reviewed by Ashutosh Chauhan) Project: http://git-wip-us.apache.org/repos/asf/hive/repo Commit: http://git-wip-us.apache.org/repos/asf/hive/commit/d97e4874 Tree: http://git-wip-us.apache.org/repos/asf/hive/tree/d97e4874 Diff: http://git-wip-us.apache.org/repos/asf/hive/diff/d97e4874 Branch: refs/heads/master Commit: d97e4874dc0e09c535e8cb908f6b17698e49a5d6 Parents: 3c1dfe3 Author: Zoltan Haindrich <k...@rxd.hu> Authored: Tue Mar 21 07:37:28 2017 +0100 Committer: Zoltan Haindrich <k...@rxd.hu> Committed: Tue Mar 21 18:08:41 2017 +0100 ---------------------------------------------------------------------- .../generic/GenericUDAFBinarySetFunctions.java | 28 +++------- .../ql/udf/generic/GenericUDAFCorrelation.java | 26 ++++++---- .../generic/GenericUDAFCovarianceSample.java | 18 +++---- .../TestGenericUDAFBinarySetFunctions.java | 6 +-- .../queries/clientpositive/cbo_rp_windowing_2.q | 2 +- .../clientpositive/udaf_binarysetfunctions.q | 1 + ql/src/test/queries/clientpositive/windowing.q | 2 +- .../llap/cbo_rp_windowing_2.q.out | 54 ++++++++++---------- .../results/clientpositive/llap/windowing.q.out | 54 ++++++++++---------- .../clientpositive/spark/windowing.q.out | 54 ++++++++++---------- .../udaf_binarysetfunctions.q.out | 10 ++-- .../test/results/clientpositive/udaf_corr.q.out | 13 +++-- .../clientpositive/udaf_covar_samp.q.out | 16 +++--- 13 files changed, 142 insertions(+), 142 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/hive/blob/d97e4874/ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDAFBinarySetFunctions.java ---------------------------------------------------------------------- diff --git a/ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDAFBinarySetFunctions.java b/ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDAFBinarySetFunctions.java index e799a94..674c527 100644 --- a/ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDAFBinarySetFunctions.java +++ b/ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDAFBinarySetFunctions.java @@ -294,19 +294,16 @@ public class GenericUDAFBinarySetFunctions extends AbstractGenericUDAFResolver { return new Evaluator(); } - /** - * NOTE: corr is declared as corr(x,y) instead corr(y,x) - */ private static class Evaluator extends GenericUDAFCorrelationEvaluator { @Override public Object terminate(AggregationBuffer agg) throws HiveException { StdAgg myagg = (StdAgg) agg; - if (myagg.count < 2 || myagg.yvar == 0.0d) { + if (myagg.count < 2 || myagg.xvar == 0.0d) { return null; } else { - getResult().set(myagg.covar / myagg.yvar); + getResult().set(myagg.covar / myagg.xvar); return getResult(); } } @@ -328,23 +325,20 @@ public class GenericUDAFBinarySetFunctions extends AbstractGenericUDAFResolver { return new Evaluator(); } - /** - * NOTE: corr is declared as corr(x,y) instead corr(y,x) - */ private static class Evaluator extends GenericUDAFCorrelationEvaluator { @Override public Object terminate(AggregationBuffer agg) throws HiveException { StdAgg myagg = (StdAgg) agg; - if (myagg.count < 2 || myagg.yvar == 0.0d) { + if (myagg.count < 2 || myagg.xvar == 0.0d) { return null; } DoubleWritable result = getResult(); - if (myagg.xvar == 0.0d) { + if (myagg.yvar == 0.0d) { result.set(1.0d); } else { - result.set(myagg.covar * myagg.covar / myagg.yvar / myagg.xvar); + result.set(myagg.covar * myagg.covar / myagg.xvar / myagg.yvar); } return result; } @@ -365,9 +359,6 @@ public class GenericUDAFBinarySetFunctions extends AbstractGenericUDAFResolver { return new Evaluator(); } - /** - * NOTE: corr is declared as corr(x,y) instead corr(y,x) - */ private static class Evaluator extends GenericUDAFCorrelationEvaluator { @Override @@ -398,21 +389,18 @@ public class GenericUDAFBinarySetFunctions extends AbstractGenericUDAFResolver { return new Evaluator(); } - /** - * NOTE: corr is declared as corr(x,y) instead corr(y,x) - */ private static class Evaluator extends GenericUDAFCorrelationEvaluator { @Override public Object terminate(AggregationBuffer agg) throws HiveException { StdAgg myagg = (StdAgg) agg; - if (myagg.count == 0) { + if (myagg.count == 0 || myagg.xvar == 0.0d) { return null; } DoubleWritable result = getResult(); - double slope = myagg.covar / myagg.yvar; - result.set(myagg.xavg - slope * myagg.yavg); + double slope = myagg.covar / myagg.xvar; + result.set(myagg.yavg - slope * myagg.xavg); return result; } } http://git-wip-us.apache.org/repos/asf/hive/blob/d97e4874/ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDAFCorrelation.java ---------------------------------------------------------------------- diff --git a/ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDAFCorrelation.java b/ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDAFCorrelation.java index 8056931..61b777a 100644 --- a/ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDAFCorrelation.java +++ b/ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDAFCorrelation.java @@ -61,11 +61,14 @@ import org.apache.hadoop.io.LongWritable; * */ @Description(name = "corr", - value = "_FUNC_(x,y) - Returns the Pearson coefficient of correlation\n" + value = "_FUNC_(y,x) - Returns the Pearson coefficient of correlation\n" + "between a set of number pairs", extended = "The function takes as arguments any pair of numeric types and returns a double.\n" - + "Any pair with a NULL is ignored. If the function is applied to an empty set or\n" - + "a singleton set, NULL will be returned. Otherwise, it computes the following:\n" + + "Any pair with a NULL is ignored.\n" + + "If applied to an empty set: NULL is returned.\n" + + "If N*SUM(x*x) = SUM(x)*SUM(x): NULL is returned.\n" + + "If N*SUM(y*y) = SUM(y)*SUM(y): NULL is returned.\n" + + "Otherwise, it computes the following:\n" + " COVAR_POP(x,y)/(STDDEV_POP(x)*STDDEV_POP(y))\n" + "where neither x nor y is null,\n" + "COVAR_POP is the population covariance,\n" @@ -180,8 +183,8 @@ public class GenericUDAFCorrelation extends AbstractGenericUDAFResolver { // init input if (mode == Mode.PARTIAL1 || mode == Mode.COMPLETE) { assert (parameters.length == 2); - xInputOI = (PrimitiveObjectInspector) parameters[0]; - yInputOI = (PrimitiveObjectInspector) parameters[1]; + yInputOI = (PrimitiveObjectInspector) parameters[0]; + xInputOI = (PrimitiveObjectInspector) parameters[1]; } else { assert (parameters.length == 1); soi = (StructObjectInspector) parameters[0]; @@ -279,8 +282,8 @@ public class GenericUDAFCorrelation extends AbstractGenericUDAFResolver { @Override public void iterate(AggregationBuffer agg, Object[] parameters) throws HiveException { assert (parameters.length == 2); - Object px = parameters[0]; - Object py = parameters[1]; + Object py = parameters[0]; + Object px = parameters[1]; if (px != null && py != null) { StdAgg myagg = (StdAgg) agg; double vx = PrimitiveObjectInspectorUtils.getDouble(px, xInputOI); @@ -360,15 +363,16 @@ public class GenericUDAFCorrelation extends AbstractGenericUDAFResolver { public Object terminate(AggregationBuffer agg) throws HiveException { StdAgg myagg = (StdAgg) agg; - if (myagg.count < 2) { // SQL standard - return null for zero or one pair + if (myagg.count == 0 || myagg.xvar == 0.0d || myagg.yvar == 0.0d) { return null; } else { - getResult().set( + DoubleWritable result = getResult(); + result.set( myagg.covar - / java.lang.Math.sqrt(myagg.xvar) / java.lang.Math.sqrt(myagg.yvar) + / java.lang.Math.sqrt(myagg.xvar) ); - return getResult(); + return result; } } http://git-wip-us.apache.org/repos/asf/hive/blob/d97e4874/ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDAFCovarianceSample.java ---------------------------------------------------------------------- diff --git a/ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDAFCovarianceSample.java b/ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDAFCovarianceSample.java index de0f153..64d4de9 100644 --- a/ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDAFCovarianceSample.java +++ b/ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDAFCovarianceSample.java @@ -21,6 +21,7 @@ import org.apache.hadoop.hive.ql.exec.Description; import org.apache.hadoop.hive.ql.exec.UDFArgumentTypeException; import org.apache.hadoop.hive.ql.metadata.HiveException; import org.apache.hadoop.hive.ql.parse.SemanticException; +import org.apache.hadoop.hive.serde2.io.DoubleWritable; import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector; import org.apache.hadoop.hive.serde2.typeinfo.PrimitiveTypeInfo; import org.apache.hadoop.hive.serde2.typeinfo.TypeInfo; @@ -33,8 +34,10 @@ import org.apache.hadoop.hive.serde2.typeinfo.TypeInfo; @Description(name = "covar_samp", value = "_FUNC_(x,y) - Returns the sample covariance of a set of number pairs", extended = "The function takes as arguments any pair of numeric types and returns a double.\n" - + "Any pair with a NULL is ignored. If the function is applied to an empty set, NULL\n" - + "will be returned. Otherwise, it computes the following:\n" + + "Any pair with a NULL is ignored.\n" + + "If applied to an empty set: NULL is returned.\n" + + "If applied to a set with a single element: NULL is returned.\n" + + "Otherwise, it computes the following:\n" + " (SUM(x*y)-SUM(x)*SUM(y)/COUNT(x,y))/(COUNT(x,y)-1)\n" + "where neither x nor y is null.") public class GenericUDAFCovarianceSample extends GenericUDAFCovariance { @@ -107,15 +110,12 @@ public class GenericUDAFCovarianceSample extends GenericUDAFCovariance { public Object terminate(AggregationBuffer agg) throws HiveException { StdAgg myagg = (StdAgg) agg; - if (myagg.count == 0) { // SQL standard - return null for zero elements + if (myagg.count <= 1) { return null; } else { - if (myagg.count > 1) { - getResult().set(myagg.covar / (myagg.count - 1)); - } else { // the covariance of a singleton set is always 0 - getResult().set(0); - } - return getResult(); + DoubleWritable result = getResult(); + result.set(myagg.covar / (myagg.count - 1)); + return result; } } } http://git-wip-us.apache.org/repos/asf/hive/blob/d97e4874/ql/src/test/org/apache/hadoop/hive/ql/udf/generic/TestGenericUDAFBinarySetFunctions.java ---------------------------------------------------------------------- diff --git a/ql/src/test/org/apache/hadoop/hive/ql/udf/generic/TestGenericUDAFBinarySetFunctions.java b/ql/src/test/org/apache/hadoop/hive/ql/udf/generic/TestGenericUDAFBinarySetFunctions.java index 584caf1..caa9b6a 100644 --- a/ql/src/test/org/apache/hadoop/hive/ql/udf/generic/TestGenericUDAFBinarySetFunctions.java +++ b/ql/src/test/org/apache/hadoop/hive/ql/udf/generic/TestGenericUDAFBinarySetFunctions.java @@ -57,7 +57,7 @@ public class TestGenericUDAFBinarySetFunctions { ret.add(new Object[] { "empty", RowSetGenerator.generate(0, new RowSetGenerator.DoubleSequence(0), new RowSetGenerator.DoubleSequence(0)) }); ret.add(new Object[] { "lonely", RowSetGenerator.generate(1, - new RowSetGenerator.DoubleSequence(0), new RowSetGenerator.DoubleSequence(0)) }); + new RowSetGenerator.DoubleSequence(10), new RowSetGenerator.DoubleSequence(10)) }); ret.add(new Object[] { "seq/seq+10", RowSetGenerator.generate(10, new RowSetGenerator.DoubleSequence(0), new RowSetGenerator.DoubleSequence(10)) }); ret.add(new Object[] { "seq/null", RowSetGenerator.generate(10, @@ -265,7 +265,6 @@ public class TestGenericUDAFBinarySetFunctions { } @Test - @Ignore("HIVE-16178 should fix this") public void corr() throws Exception { RegrIntermediate expected = RegrIntermediate.computeFor(rowSet); validateUDAF(expected.corr(), new GenericUDAFCorrelation()); @@ -278,7 +277,6 @@ public class TestGenericUDAFBinarySetFunctions { } @Test - @Ignore("HIVE-16178 should fix this") public void covar_samp() throws Exception { RegrIntermediate expected = RegrIntermediate.computeFor(rowSet); validateUDAF(expected.covar_samp(), new GenericUDAFCovarianceSample()); @@ -327,7 +325,7 @@ public class TestGenericUDAFBinarySetFunctions { public Double intercept() { double xx = n * sum_x2 - sum_x * sum_x; - if (n == 0) + if (n == 0 || xx == 0.0d) return null; return (sum_y * sum_x2 - sum_x * sum_xy) / xx; } http://git-wip-us.apache.org/repos/asf/hive/blob/d97e4874/ql/src/test/queries/clientpositive/cbo_rp_windowing_2.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/cbo_rp_windowing_2.q b/ql/src/test/queries/clientpositive/cbo_rp_windowing_2.q index 1877927..b84a660 100644 --- a/ql/src/test/queries/clientpositive/cbo_rp_windowing_2.q +++ b/ql/src/test/queries/clientpositive/cbo_rp_windowing_2.q @@ -185,7 +185,7 @@ 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, +round(corr(p_size, p_retailprice) over w1,5) 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); http://git-wip-us.apache.org/repos/asf/hive/blob/d97e4874/ql/src/test/queries/clientpositive/udaf_binarysetfunctions.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/udaf_binarysetfunctions.q b/ql/src/test/queries/clientpositive/udaf_binarysetfunctions.q index 2039312..254ac15 100644 --- a/ql/src/test/queries/clientpositive/udaf_binarysetfunctions.q +++ b/ql/src/test/queries/clientpositive/udaf_binarysetfunctions.q @@ -1,3 +1,4 @@ +drop table t; create table t (id int,px int,y decimal,x decimal); insert into t values (101,1,1,1); http://git-wip-us.apache.org/repos/asf/hive/blob/d97e4874/ql/src/test/queries/clientpositive/windowing.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/windowing.q b/ql/src/test/queries/clientpositive/windowing.q index 19fa1ad..5de2b0e 100644 --- a/ql/src/test/queries/clientpositive/windowing.q +++ b/ql/src/test/queries/clientpositive/windowing.q @@ -182,7 +182,7 @@ 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, +round(corr(p_size, p_retailprice) over w1,5) 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); http://git-wip-us.apache.org/repos/asf/hive/blob/d97e4874/ql/src/test/results/clientpositive/llap/cbo_rp_windowing_2.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/llap/cbo_rp_windowing_2.q.out b/ql/src/test/results/clientpositive/llap/cbo_rp_windowing_2.q.out index d4ab0f9..ad1e033 100644 --- a/ql/src/test/results/clientpositive/llap/cbo_rp_windowing_2.q.out +++ b/ql/src/test/results/clientpositive/llap/cbo_rp_windowing_2.q.out @@ -843,7 +843,7 @@ 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, +round(corr(p_size, p_retailprice) over w1,5) 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) @@ -855,39 +855,39 @@ 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, +round(corr(p_size, p_retailprice) over w1,5) 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) POSTHOOK: type: QUERY POSTHOOK: Input: default@part #### A masked pattern was here #### -Manufacturer#1 almond antique burnished rose metallic 2 258.10677784349235 258.10677784349235 [2,34,6] 66619.10876874991 0.811328754177887 2801.7074999999995 +Manufacturer#1 almond antique burnished rose metallic 2 258.10677784349235 258.10677784349235 [2,34,6] 66619.10876874991 0.81133 2801.7074999999995 Manufacturer#1 almond antique burnished rose metallic 2 273.70217881648074 273.70217881648074 [2,34] 74912.8826888888 1.0 4128.782222222221 -Manufacturer#1 almond antique chartreuse lavender yellow 34 230.90151585470358 230.90151585470358 [2,34,6,28] 53315.51002399992 0.695639377397664 2210.7864 -Manufacturer#1 almond antique salmon chartreuse burlywood 6 202.73109328368946 202.73109328368946 [2,34,6,28,42] 41099.896184 0.630785977101214 2009.9536000000007 -Manufacturer#1 almond aquamarine burnished black steel 28 121.6064517973862 121.6064517973862 [34,6,28,42] 14788.129118750014 0.2036684720435979 331.1337500000004 -Manufacturer#1 almond aquamarine pink moccasin thistle 42 96.5751586416853 96.5751586416853 [6,28,42] 9326.761266666683 -1.4442181184933883E-4 -0.20666666666708502 -Manufacturer#2 almond antique violet chocolate turquoise 14 142.2363169751898 142.2363169751898 [14,40,2] 20231.169866666663 -0.49369526554523185 -1113.7466666666658 -Manufacturer#2 almond antique violet turquoise frosted 40 137.76306498840682 137.76306498840682 [14,40,2,25] 18978.662075 -0.5205630897335946 -1004.4812499999995 -Manufacturer#2 almond aquamarine midnight light salmon 2 130.03972279269132 130.03972279269132 [14,40,2,25,18] 16910.329504000005 -0.46908967495720255 -766.1791999999995 -Manufacturer#2 almond aquamarine rose maroon antique 25 135.55100986344584 135.55100986344584 [40,2,25,18] 18374.07627499999 -0.6091405874714462 -1128.1787499999987 -Manufacturer#2 almond aquamarine sandy cyan gainsboro 18 156.44019460768044 156.44019460768044 [2,25,18] 24473.534488888927 -0.9571686373491608 -1441.4466666666676 -Manufacturer#3 almond antique chartreuse khaki white 17 196.7742266885805 196.7742266885805 [17,14,19] 38720.09628888887 0.5557168646224995 224.6944444444446 -Manufacturer#3 almond antique forest lavender goldenrod 14 275.14144189852607 275.14144189852607 [17,14,19,1] 75702.81305 -0.6720833036576083 -1296.9000000000003 -Manufacturer#3 almond antique metallic orange dim 19 260.23473614412046 260.23473614412046 [17,14,19,1,45] 67722.117896 -0.5703526513979519 -2129.0664 -Manufacturer#3 almond antique misty red olive 1 275.9139962356932 275.9139962356932 [14,19,1,45] 76128.53331875012 -0.577476899644802 -2547.7868749999993 -Manufacturer#3 almond antique olive coral navajo 45 260.5815918713796 260.5815918713796 [19,1,45] 67902.76602222225 -0.8710736366736884 -4099.731111111111 -Manufacturer#4 almond antique gainsboro frosted violet 10 170.13011889596618 170.13011889596618 [10,39,27] 28944.25735555559 -0.6656975320098423 -1347.4777777777779 -Manufacturer#4 almond antique violet mint lemon 39 242.26834609323197 242.26834609323197 [10,39,27,7] 58693.95151875002 -0.8051852719193339 -2537.328125 -Manufacturer#4 almond aquamarine floral ivory bisque 27 234.10001662537326 234.10001662537326 [10,39,27,7,12] 54802.817784000035 -0.6046935574240581 -1719.8079999999995 -Manufacturer#4 almond aquamarine yellow dodger mint 7 247.3342714197732 247.3342714197732 [39,27,7,12] 61174.24181875003 -0.5508665654707869 -1719.0368749999975 -Manufacturer#4 almond azure aquamarine papaya violet 12 283.3344330566893 283.3344330566893 [27,7,12] 80278.40095555557 -0.7755740084632333 -1867.4888888888881 -Manufacturer#5 almond antique blue firebrick mint 31 83.69879024746363 83.69879024746363 [31,6,2] 7005.487488888913 0.39004303087285047 418.9233333333353 -Manufacturer#5 almond antique medium spring khaki 6 316.68049612345885 316.68049612345885 [31,6,2,46] 100286.53662500004 -0.713612911776183 -4090.853749999999 -Manufacturer#5 almond antique sky peru orange 2 285.40506298242155 285.40506298242155 [31,6,2,46,23] 81456.04997600002 -0.712858514567818 -3297.2011999999986 -Manufacturer#5 almond aquamarine dodger light gainsboro 46 285.43749038756283 285.43749038756283 [6,2,46,23] 81474.56091875004 -0.984128787153391 -4871.028125000002 -Manufacturer#5 almond azure blanched chiffon midnight 23 315.9225931564038 315.9225931564038 [2,46,23] 99807.08486666664 -0.9978877469246936 -5664.856666666666 +Manufacturer#1 almond antique chartreuse lavender yellow 34 230.90151585470358 230.90151585470358 [2,34,6,28] 53315.51002399992 0.69564 2210.7864 +Manufacturer#1 almond antique salmon chartreuse burlywood 6 202.73109328368946 202.73109328368946 [2,34,6,28,42] 41099.896184 0.63079 2009.9536000000007 +Manufacturer#1 almond aquamarine burnished black steel 28 121.6064517973862 121.6064517973862 [34,6,28,42] 14788.129118750014 0.20367 331.1337500000004 +Manufacturer#1 almond aquamarine pink moccasin thistle 42 96.5751586416853 96.5751586416853 [6,28,42] 9326.761266666683 -1.4E-4 -0.20666666666708502 +Manufacturer#2 almond antique violet chocolate turquoise 14 142.2363169751898 142.2363169751898 [14,40,2] 20231.169866666663 -0.4937 -1113.7466666666658 +Manufacturer#2 almond antique violet turquoise frosted 40 137.76306498840682 137.76306498840682 [14,40,2,25] 18978.662075 -0.52056 -1004.4812499999995 +Manufacturer#2 almond aquamarine midnight light salmon 2 130.03972279269132 130.03972279269132 [14,40,2,25,18] 16910.329504000005 -0.46909 -766.1791999999995 +Manufacturer#2 almond aquamarine rose maroon antique 25 135.55100986344584 135.55100986344584 [40,2,25,18] 18374.07627499999 -0.60914 -1128.1787499999987 +Manufacturer#2 almond aquamarine sandy cyan gainsboro 18 156.44019460768044 156.44019460768044 [2,25,18] 24473.534488888927 -0.95717 -1441.4466666666676 +Manufacturer#3 almond antique chartreuse khaki white 17 196.7742266885805 196.7742266885805 [17,14,19] 38720.09628888887 0.55572 224.6944444444446 +Manufacturer#3 almond antique forest lavender goldenrod 14 275.14144189852607 275.14144189852607 [17,14,19,1] 75702.81305 -0.67208 -1296.9000000000003 +Manufacturer#3 almond antique metallic orange dim 19 260.23473614412046 260.23473614412046 [17,14,19,1,45] 67722.117896 -0.57035 -2129.0664 +Manufacturer#3 almond antique misty red olive 1 275.9139962356932 275.9139962356932 [14,19,1,45] 76128.53331875012 -0.57748 -2547.7868749999993 +Manufacturer#3 almond antique olive coral navajo 45 260.5815918713796 260.5815918713796 [19,1,45] 67902.76602222225 -0.87107 -4099.731111111111 +Manufacturer#4 almond antique gainsboro frosted violet 10 170.13011889596618 170.13011889596618 [10,39,27] 28944.25735555559 -0.6657 -1347.4777777777779 +Manufacturer#4 almond antique violet mint lemon 39 242.26834609323197 242.26834609323197 [10,39,27,7] 58693.95151875002 -0.80519 -2537.328125 +Manufacturer#4 almond aquamarine floral ivory bisque 27 234.10001662537326 234.10001662537326 [10,39,27,7,12] 54802.817784000035 -0.60469 -1719.8079999999995 +Manufacturer#4 almond aquamarine yellow dodger mint 7 247.3342714197732 247.3342714197732 [39,27,7,12] 61174.24181875003 -0.55087 -1719.0368749999975 +Manufacturer#4 almond azure aquamarine papaya violet 12 283.3344330566893 283.3344330566893 [27,7,12] 80278.40095555557 -0.77557 -1867.4888888888881 +Manufacturer#5 almond antique blue firebrick mint 31 83.69879024746363 83.69879024746363 [31,6,2] 7005.487488888913 0.39004 418.9233333333353 +Manufacturer#5 almond antique medium spring khaki 6 316.68049612345885 316.68049612345885 [31,6,2,46] 100286.53662500004 -0.71361 -4090.853749999999 +Manufacturer#5 almond antique sky peru orange 2 285.40506298242155 285.40506298242155 [31,6,2,46,23] 81456.04997600002 -0.71286 -3297.2011999999986 +Manufacturer#5 almond aquamarine dodger light gainsboro 46 285.43749038756283 285.43749038756283 [6,2,46,23] 81474.56091875004 -0.98413 -4871.028125000002 +Manufacturer#5 almond azure blanched chiffon midnight 23 315.9225931564038 315.9225931564038 [2,46,23] 99807.08486666664 -0.99789 -5664.856666666666 PREHOOK: query: 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, http://git-wip-us.apache.org/repos/asf/hive/blob/d97e4874/ql/src/test/results/clientpositive/llap/windowing.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/llap/windowing.q.out b/ql/src/test/results/clientpositive/llap/windowing.q.out index 86804f2..468b67e 100644 --- a/ql/src/test/results/clientpositive/llap/windowing.q.out +++ b/ql/src/test/results/clientpositive/llap/windowing.q.out @@ -843,7 +843,7 @@ 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, +round(corr(p_size, p_retailprice) over w1,5) 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) @@ -855,39 +855,39 @@ 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, +round(corr(p_size, p_retailprice) over w1,5) 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) POSTHOOK: type: QUERY POSTHOOK: Input: default@part #### A masked pattern was here #### -Manufacturer#1 almond antique burnished rose metallic 2 258.10677784349235 258.10677784349235 [2,34,6] 66619.10876874991 0.811328754177887 2801.7074999999995 +Manufacturer#1 almond antique burnished rose metallic 2 258.10677784349235 258.10677784349235 [2,34,6] 66619.10876874991 0.81133 2801.7074999999995 Manufacturer#1 almond antique burnished rose metallic 2 273.70217881648074 273.70217881648074 [2,34] 74912.8826888888 1.0 4128.782222222221 -Manufacturer#1 almond antique chartreuse lavender yellow 34 230.90151585470358 230.90151585470358 [2,34,6,28] 53315.51002399992 0.695639377397664 2210.7864 -Manufacturer#1 almond antique salmon chartreuse burlywood 6 202.73109328368946 202.73109328368946 [2,34,6,28,42] 41099.896184 0.630785977101214 2009.9536000000007 -Manufacturer#1 almond aquamarine burnished black steel 28 121.6064517973862 121.6064517973862 [34,6,28,42] 14788.129118750014 0.2036684720435979 331.1337500000004 -Manufacturer#1 almond aquamarine pink moccasin thistle 42 96.5751586416853 96.5751586416853 [6,28,42] 9326.761266666683 -1.4442181184933883E-4 -0.20666666666708502 -Manufacturer#2 almond antique violet chocolate turquoise 14 142.2363169751898 142.2363169751898 [14,40,2] 20231.169866666663 -0.49369526554523185 -1113.7466666666658 -Manufacturer#2 almond antique violet turquoise frosted 40 137.76306498840682 137.76306498840682 [14,40,2,25] 18978.662075 -0.5205630897335946 -1004.4812499999995 -Manufacturer#2 almond aquamarine midnight light salmon 2 130.03972279269132 130.03972279269132 [14,40,2,25,18] 16910.329504000005 -0.46908967495720255 -766.1791999999995 -Manufacturer#2 almond aquamarine rose maroon antique 25 135.55100986344584 135.55100986344584 [40,2,25,18] 18374.07627499999 -0.6091405874714462 -1128.1787499999987 -Manufacturer#2 almond aquamarine sandy cyan gainsboro 18 156.44019460768044 156.44019460768044 [2,25,18] 24473.534488888927 -0.9571686373491608 -1441.4466666666676 -Manufacturer#3 almond antique chartreuse khaki white 17 196.7742266885805 196.7742266885805 [17,14,19] 38720.09628888887 0.5557168646224995 224.6944444444446 -Manufacturer#3 almond antique forest lavender goldenrod 14 275.14144189852607 275.14144189852607 [17,14,19,1] 75702.81305 -0.6720833036576083 -1296.9000000000003 -Manufacturer#3 almond antique metallic orange dim 19 260.23473614412046 260.23473614412046 [17,14,19,1,45] 67722.117896 -0.5703526513979519 -2129.0664 -Manufacturer#3 almond antique misty red olive 1 275.9139962356932 275.9139962356932 [14,19,1,45] 76128.53331875012 -0.577476899644802 -2547.7868749999993 -Manufacturer#3 almond antique olive coral navajo 45 260.5815918713796 260.5815918713796 [19,1,45] 67902.76602222225 -0.8710736366736884 -4099.731111111111 -Manufacturer#4 almond antique gainsboro frosted violet 10 170.13011889596618 170.13011889596618 [10,39,27] 28944.25735555559 -0.6656975320098423 -1347.4777777777779 -Manufacturer#4 almond antique violet mint lemon 39 242.26834609323197 242.26834609323197 [10,39,27,7] 58693.95151875002 -0.8051852719193339 -2537.328125 -Manufacturer#4 almond aquamarine floral ivory bisque 27 234.10001662537326 234.10001662537326 [10,39,27,7,12] 54802.817784000035 -0.6046935574240581 -1719.8079999999995 -Manufacturer#4 almond aquamarine yellow dodger mint 7 247.3342714197732 247.3342714197732 [39,27,7,12] 61174.24181875003 -0.5508665654707869 -1719.0368749999975 -Manufacturer#4 almond azure aquamarine papaya violet 12 283.3344330566893 283.3344330566893 [27,7,12] 80278.40095555557 -0.7755740084632333 -1867.4888888888881 -Manufacturer#5 almond antique blue firebrick mint 31 83.69879024746363 83.69879024746363 [31,6,2] 7005.487488888913 0.39004303087285047 418.9233333333353 -Manufacturer#5 almond antique medium spring khaki 6 316.68049612345885 316.68049612345885 [31,6,2,46] 100286.53662500004 -0.713612911776183 -4090.853749999999 -Manufacturer#5 almond antique sky peru orange 2 285.40506298242155 285.40506298242155 [31,6,2,46,23] 81456.04997600002 -0.712858514567818 -3297.2011999999986 -Manufacturer#5 almond aquamarine dodger light gainsboro 46 285.43749038756283 285.43749038756283 [6,2,46,23] 81474.56091875004 -0.984128787153391 -4871.028125000002 -Manufacturer#5 almond azure blanched chiffon midnight 23 315.9225931564038 315.9225931564038 [2,46,23] 99807.08486666664 -0.9978877469246936 -5664.856666666666 +Manufacturer#1 almond antique chartreuse lavender yellow 34 230.90151585470358 230.90151585470358 [2,34,6,28] 53315.51002399992 0.69564 2210.7864 +Manufacturer#1 almond antique salmon chartreuse burlywood 6 202.73109328368946 202.73109328368946 [2,34,6,28,42] 41099.896184 0.63079 2009.9536000000007 +Manufacturer#1 almond aquamarine burnished black steel 28 121.6064517973862 121.6064517973862 [34,6,28,42] 14788.129118750014 0.20367 331.1337500000004 +Manufacturer#1 almond aquamarine pink moccasin thistle 42 96.5751586416853 96.5751586416853 [6,28,42] 9326.761266666683 -1.4E-4 -0.20666666666708502 +Manufacturer#2 almond antique violet chocolate turquoise 14 142.2363169751898 142.2363169751898 [14,40,2] 20231.169866666663 -0.4937 -1113.7466666666658 +Manufacturer#2 almond antique violet turquoise frosted 40 137.76306498840682 137.76306498840682 [14,40,2,25] 18978.662075 -0.52056 -1004.4812499999995 +Manufacturer#2 almond aquamarine midnight light salmon 2 130.03972279269132 130.03972279269132 [14,40,2,25,18] 16910.329504000005 -0.46909 -766.1791999999995 +Manufacturer#2 almond aquamarine rose maroon antique 25 135.55100986344584 135.55100986344584 [40,2,25,18] 18374.07627499999 -0.60914 -1128.1787499999987 +Manufacturer#2 almond aquamarine sandy cyan gainsboro 18 156.44019460768044 156.44019460768044 [2,25,18] 24473.534488888927 -0.95717 -1441.4466666666676 +Manufacturer#3 almond antique chartreuse khaki white 17 196.7742266885805 196.7742266885805 [17,14,19] 38720.09628888887 0.55572 224.6944444444446 +Manufacturer#3 almond antique forest lavender goldenrod 14 275.14144189852607 275.14144189852607 [17,14,19,1] 75702.81305 -0.67208 -1296.9000000000003 +Manufacturer#3 almond antique metallic orange dim 19 260.23473614412046 260.23473614412046 [17,14,19,1,45] 67722.117896 -0.57035 -2129.0664 +Manufacturer#3 almond antique misty red olive 1 275.9139962356932 275.9139962356932 [14,19,1,45] 76128.53331875012 -0.57748 -2547.7868749999993 +Manufacturer#3 almond antique olive coral navajo 45 260.5815918713796 260.5815918713796 [19,1,45] 67902.76602222225 -0.87107 -4099.731111111111 +Manufacturer#4 almond antique gainsboro frosted violet 10 170.13011889596618 170.13011889596618 [10,39,27] 28944.25735555559 -0.6657 -1347.4777777777779 +Manufacturer#4 almond antique violet mint lemon 39 242.26834609323197 242.26834609323197 [10,39,27,7] 58693.95151875002 -0.80519 -2537.328125 +Manufacturer#4 almond aquamarine floral ivory bisque 27 234.10001662537326 234.10001662537326 [10,39,27,7,12] 54802.817784000035 -0.60469 -1719.8079999999995 +Manufacturer#4 almond aquamarine yellow dodger mint 7 247.3342714197732 247.3342714197732 [39,27,7,12] 61174.24181875003 -0.55087 -1719.0368749999975 +Manufacturer#4 almond azure aquamarine papaya violet 12 283.3344330566893 283.3344330566893 [27,7,12] 80278.40095555557 -0.77557 -1867.4888888888881 +Manufacturer#5 almond antique blue firebrick mint 31 83.69879024746363 83.69879024746363 [31,6,2] 7005.487488888913 0.39004 418.9233333333353 +Manufacturer#5 almond antique medium spring khaki 6 316.68049612345885 316.68049612345885 [31,6,2,46] 100286.53662500004 -0.71361 -4090.853749999999 +Manufacturer#5 almond antique sky peru orange 2 285.40506298242155 285.40506298242155 [31,6,2,46,23] 81456.04997600002 -0.71286 -3297.2011999999986 +Manufacturer#5 almond aquamarine dodger light gainsboro 46 285.43749038756283 285.43749038756283 [6,2,46,23] 81474.56091875004 -0.98413 -4871.028125000002 +Manufacturer#5 almond azure blanched chiffon midnight 23 315.9225931564038 315.9225931564038 [2,46,23] 99807.08486666664 -0.99789 -5664.856666666666 PREHOOK: query: 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, http://git-wip-us.apache.org/repos/asf/hive/blob/d97e4874/ql/src/test/results/clientpositive/spark/windowing.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/spark/windowing.q.out b/ql/src/test/results/clientpositive/spark/windowing.q.out index e72babc..d4be0b3 100644 --- a/ql/src/test/results/clientpositive/spark/windowing.q.out +++ b/ql/src/test/results/clientpositive/spark/windowing.q.out @@ -843,7 +843,7 @@ 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, +round(corr(p_size, p_retailprice) over w1,5) 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) @@ -855,39 +855,39 @@ 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, +round(corr(p_size, p_retailprice) over w1,5) 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) POSTHOOK: type: QUERY POSTHOOK: Input: default@part #### A masked pattern was here #### -Manufacturer#1 almond antique burnished rose metallic 2 258.10677784349235 258.10677784349235 [2,34,6] 66619.10876874991 0.811328754177887 2801.7074999999995 +Manufacturer#1 almond antique burnished rose metallic 2 258.10677784349235 258.10677784349235 [2,34,6] 66619.10876874991 0.81133 2801.7074999999995 Manufacturer#1 almond antique burnished rose metallic 2 273.70217881648074 273.70217881648074 [2,34] 74912.8826888888 1.0 4128.782222222221 -Manufacturer#1 almond antique chartreuse lavender yellow 34 230.90151585470358 230.90151585470358 [2,34,6,28] 53315.51002399992 0.695639377397664 2210.7864 -Manufacturer#1 almond antique salmon chartreuse burlywood 6 202.73109328368946 202.73109328368946 [2,34,6,28,42] 41099.896184 0.630785977101214 2009.9536000000007 -Manufacturer#1 almond aquamarine burnished black steel 28 121.6064517973862 121.6064517973862 [34,6,28,42] 14788.129118750014 0.2036684720435979 331.1337500000004 -Manufacturer#1 almond aquamarine pink moccasin thistle 42 96.5751586416853 96.5751586416853 [6,28,42] 9326.761266666683 -1.4442181184933883E-4 -0.20666666666708502 -Manufacturer#2 almond antique violet chocolate turquoise 14 142.2363169751898 142.2363169751898 [14,40,2] 20231.169866666663 -0.49369526554523185 -1113.7466666666658 -Manufacturer#2 almond antique violet turquoise frosted 40 137.76306498840682 137.76306498840682 [14,40,2,25] 18978.662075 -0.5205630897335946 -1004.4812499999995 -Manufacturer#2 almond aquamarine midnight light salmon 2 130.03972279269132 130.03972279269132 [14,40,2,25,18] 16910.329504000005 -0.46908967495720255 -766.1791999999995 -Manufacturer#2 almond aquamarine rose maroon antique 25 135.55100986344584 135.55100986344584 [40,2,25,18] 18374.07627499999 -0.6091405874714462 -1128.1787499999987 -Manufacturer#2 almond aquamarine sandy cyan gainsboro 18 156.44019460768044 156.44019460768044 [2,25,18] 24473.534488888927 -0.9571686373491608 -1441.4466666666676 -Manufacturer#3 almond antique chartreuse khaki white 17 196.7742266885805 196.7742266885805 [17,14,19] 38720.09628888887 0.5557168646224995 224.6944444444446 -Manufacturer#3 almond antique forest lavender goldenrod 14 275.14144189852607 275.14144189852607 [17,14,19,1] 75702.81305 -0.6720833036576083 -1296.9000000000003 -Manufacturer#3 almond antique metallic orange dim 19 260.23473614412046 260.23473614412046 [17,14,19,1,45] 67722.117896 -0.5703526513979519 -2129.0664 -Manufacturer#3 almond antique misty red olive 1 275.9139962356932 275.9139962356932 [14,19,1,45] 76128.53331875012 -0.577476899644802 -2547.7868749999993 -Manufacturer#3 almond antique olive coral navajo 45 260.5815918713796 260.5815918713796 [19,1,45] 67902.76602222225 -0.8710736366736884 -4099.731111111111 -Manufacturer#4 almond antique gainsboro frosted violet 10 170.13011889596618 170.13011889596618 [10,39,27] 28944.25735555559 -0.6656975320098423 -1347.4777777777779 -Manufacturer#4 almond antique violet mint lemon 39 242.26834609323197 242.26834609323197 [10,39,27,7] 58693.95151875002 -0.8051852719193339 -2537.328125 -Manufacturer#4 almond aquamarine floral ivory bisque 27 234.10001662537326 234.10001662537326 [10,39,27,7,12] 54802.817784000035 -0.6046935574240581 -1719.8079999999995 -Manufacturer#4 almond aquamarine yellow dodger mint 7 247.3342714197732 247.3342714197732 [39,27,7,12] 61174.24181875003 -0.5508665654707869 -1719.0368749999975 -Manufacturer#4 almond azure aquamarine papaya violet 12 283.3344330566893 283.3344330566893 [27,7,12] 80278.40095555557 -0.7755740084632333 -1867.4888888888881 -Manufacturer#5 almond antique blue firebrick mint 31 83.69879024746363 83.69879024746363 [31,6,2] 7005.487488888913 0.39004303087285047 418.9233333333353 -Manufacturer#5 almond antique medium spring khaki 6 316.68049612345885 316.68049612345885 [31,6,2,46] 100286.53662500004 -0.713612911776183 -4090.853749999999 -Manufacturer#5 almond antique sky peru orange 2 285.40506298242155 285.40506298242155 [31,6,2,46,23] 81456.04997600002 -0.712858514567818 -3297.2011999999986 -Manufacturer#5 almond aquamarine dodger light gainsboro 46 285.43749038756283 285.43749038756283 [6,2,46,23] 81474.56091875004 -0.984128787153391 -4871.028125000002 -Manufacturer#5 almond azure blanched chiffon midnight 23 315.9225931564038 315.9225931564038 [2,46,23] 99807.08486666664 -0.9978877469246936 -5664.856666666666 +Manufacturer#1 almond antique chartreuse lavender yellow 34 230.90151585470358 230.90151585470358 [2,34,6,28] 53315.51002399992 0.69564 2210.7864 +Manufacturer#1 almond antique salmon chartreuse burlywood 6 202.73109328368946 202.73109328368946 [2,34,6,28,42] 41099.896184 0.63079 2009.9536000000007 +Manufacturer#1 almond aquamarine burnished black steel 28 121.6064517973862 121.6064517973862 [34,6,28,42] 14788.129118750014 0.20367 331.1337500000004 +Manufacturer#1 almond aquamarine pink moccasin thistle 42 96.5751586416853 96.5751586416853 [6,28,42] 9326.761266666683 -1.4E-4 -0.20666666666708502 +Manufacturer#2 almond antique violet chocolate turquoise 14 142.2363169751898 142.2363169751898 [14,40,2] 20231.169866666663 -0.4937 -1113.7466666666658 +Manufacturer#2 almond antique violet turquoise frosted 40 137.76306498840682 137.76306498840682 [14,40,2,25] 18978.662075 -0.52056 -1004.4812499999995 +Manufacturer#2 almond aquamarine midnight light salmon 2 130.03972279269132 130.03972279269132 [14,40,2,25,18] 16910.329504000005 -0.46909 -766.1791999999995 +Manufacturer#2 almond aquamarine rose maroon antique 25 135.55100986344584 135.55100986344584 [40,2,25,18] 18374.07627499999 -0.60914 -1128.1787499999987 +Manufacturer#2 almond aquamarine sandy cyan gainsboro 18 156.44019460768044 156.44019460768044 [2,25,18] 24473.534488888927 -0.95717 -1441.4466666666676 +Manufacturer#3 almond antique chartreuse khaki white 17 196.7742266885805 196.7742266885805 [17,14,19] 38720.09628888887 0.55572 224.6944444444446 +Manufacturer#3 almond antique forest lavender goldenrod 14 275.14144189852607 275.14144189852607 [17,14,19,1] 75702.81305 -0.67208 -1296.9000000000003 +Manufacturer#3 almond antique metallic orange dim 19 260.23473614412046 260.23473614412046 [17,14,19,1,45] 67722.117896 -0.57035 -2129.0664 +Manufacturer#3 almond antique misty red olive 1 275.9139962356932 275.9139962356932 [14,19,1,45] 76128.53331875012 -0.57748 -2547.7868749999993 +Manufacturer#3 almond antique olive coral navajo 45 260.5815918713796 260.5815918713796 [19,1,45] 67902.76602222225 -0.87107 -4099.731111111111 +Manufacturer#4 almond antique gainsboro frosted violet 10 170.13011889596618 170.13011889596618 [10,39,27] 28944.25735555559 -0.6657 -1347.4777777777779 +Manufacturer#4 almond antique violet mint lemon 39 242.26834609323197 242.26834609323197 [10,39,27,7] 58693.95151875002 -0.80519 -2537.328125 +Manufacturer#4 almond aquamarine floral ivory bisque 27 234.10001662537326 234.10001662537326 [10,39,27,7,12] 54802.817784000035 -0.60469 -1719.8079999999995 +Manufacturer#4 almond aquamarine yellow dodger mint 7 247.3342714197732 247.3342714197732 [39,27,7,12] 61174.24181875003 -0.55087 -1719.0368749999975 +Manufacturer#4 almond azure aquamarine papaya violet 12 283.3344330566893 283.3344330566893 [27,7,12] 80278.40095555557 -0.77557 -1867.4888888888881 +Manufacturer#5 almond antique blue firebrick mint 31 83.69879024746363 83.69879024746363 [31,6,2] 7005.487488888913 0.39004 418.9233333333353 +Manufacturer#5 almond antique medium spring khaki 6 316.68049612345885 316.68049612345885 [31,6,2,46] 100286.53662500004 -0.71361 -4090.853749999999 +Manufacturer#5 almond antique sky peru orange 2 285.40506298242155 285.40506298242155 [31,6,2,46,23] 81456.04997600002 -0.71286 -3297.2011999999986 +Manufacturer#5 almond aquamarine dodger light gainsboro 46 285.43749038756283 285.43749038756283 [6,2,46,23] 81474.56091875004 -0.98413 -4871.028125000002 +Manufacturer#5 almond azure blanched chiffon midnight 23 315.9225931564038 315.9225931564038 [2,46,23] 99807.08486666664 -0.99789 -5664.856666666666 PREHOOK: query: 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, http://git-wip-us.apache.org/repos/asf/hive/blob/d97e4874/ql/src/test/results/clientpositive/udaf_binarysetfunctions.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/udaf_binarysetfunctions.q.out b/ql/src/test/results/clientpositive/udaf_binarysetfunctions.q.out index 9de3dd9..267a2ba 100644 --- a/ql/src/test/results/clientpositive/udaf_binarysetfunctions.q.out +++ b/ql/src/test/results/clientpositive/udaf_binarysetfunctions.q.out @@ -1,3 +1,7 @@ +PREHOOK: query: drop table t +PREHOOK: type: DROPTABLE +POSTHOOK: query: drop table t +POSTHOOK: type: DROPTABLE PREHOOK: query: create table t (id int,px int,y decimal,x decimal) PREHOOK: type: CREATETABLE PREHOOK: Output: database:default @@ -419,12 +423,12 @@ POSTHOOK: type: QUERY POSTHOOK: Input: default@t #### A masked pattern was here #### 1 1.25 1.25 1.0 1.66667 1.25 4 1.0 0.0 1.0 5.0 5.0 5.0 2.50000 2.50000 4 -2 1.25 0.0 NaN 0.0 0.0 4 0.0 1.0 1.0 5.0 0.0 0.0 2.50000 1.00000 4 -3 0.0 1.25 NaN 0.0 0.0 4 NULL NaN NULL 0.0 5.0 0.0 1.00000 2.50000 4 +2 1.25 0.0 NULL 0.0 0.0 4 0.0 1.0 1.0 5.0 0.0 0.0 2.50000 1.00000 4 +3 0.0 1.25 NULL 0.0 0.0 4 NULL NULL NULL 0.0 5.0 0.0 1.00000 2.50000 4 4 1.25 1.25 1.0 1.66667 1.25 4 1.0 -10.0 1.0 5.0 5.0 5.0 12.50000 2.50000 4 5 NULL 1.25 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL NULL 0 6 1.25 NULL NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL NULL 0 -7 0.0 0.0 NULL 0.0 0.0 1 NULL NaN NULL 0.0 0.0 0.0 1.00000 1.00000 1 +7 0.0 0.0 NULL NULL 0.0 1 NULL NULL NULL 0.0 0.0 0.0 1.00000 1.00000 1 PREHOOK: query: select id,regr_count(y,x) over (partition by px) from t order by id PREHOOK: type: QUERY PREHOOK: Input: default@t http://git-wip-us.apache.org/repos/asf/hive/blob/d97e4874/ql/src/test/results/clientpositive/udaf_corr.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/udaf_corr.q.out b/ql/src/test/results/clientpositive/udaf_corr.q.out index b061763..cd00e3f 100644 --- a/ql/src/test/results/clientpositive/udaf_corr.q.out +++ b/ql/src/test/results/clientpositive/udaf_corr.q.out @@ -28,17 +28,20 @@ PREHOOK: query: DESCRIBE FUNCTION corr PREHOOK: type: DESCFUNCTION POSTHOOK: query: DESCRIBE FUNCTION corr POSTHOOK: type: DESCFUNCTION -corr(x,y) - Returns the Pearson coefficient of correlation +corr(y,x) - Returns the Pearson coefficient of correlation between a set of number pairs PREHOOK: query: DESCRIBE FUNCTION EXTENDED corr PREHOOK: type: DESCFUNCTION POSTHOOK: query: DESCRIBE FUNCTION EXTENDED corr POSTHOOK: type: DESCFUNCTION -corr(x,y) - Returns the Pearson coefficient of correlation +corr(y,x) - Returns the Pearson coefficient of correlation between a set of number pairs The function takes as arguments any pair of numeric types and returns a double. -Any pair with a NULL is ignored. If the function is applied to an empty set or -a singleton set, NULL will be returned. Otherwise, it computes the following: +Any pair with a NULL is ignored. +If applied to an empty set: NULL is returned. +If N*SUM(x*x) = SUM(x)*SUM(x): NULL is returned. +If N*SUM(y*y) = SUM(y)*SUM(y): NULL is returned. +Otherwise, it computes the following: COVAR_POP(x,y)/(STDDEV_POP(x)*STDDEV_POP(y)) where neither x nor y is null, COVAR_POP is the population covariance, @@ -94,7 +97,7 @@ POSTHOOK: query: SELECT corr(b, c) FROM covar_tab POSTHOOK: type: QUERY POSTHOOK: Input: default@covar_tab #### A masked pattern was here #### -0.6633880657639323 +0.6633880657639326 PREHOOK: query: DROP TABLE covar_tab PREHOOK: type: DROPTABLE PREHOOK: Input: default@covar_tab http://git-wip-us.apache.org/repos/asf/hive/blob/d97e4874/ql/src/test/results/clientpositive/udaf_covar_samp.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/udaf_covar_samp.q.out b/ql/src/test/results/clientpositive/udaf_covar_samp.q.out index a009f68..1f56c0c 100644 --- a/ql/src/test/results/clientpositive/udaf_covar_samp.q.out +++ b/ql/src/test/results/clientpositive/udaf_covar_samp.q.out @@ -35,8 +35,10 @@ POSTHOOK: query: DESCRIBE FUNCTION EXTENDED covar_samp POSTHOOK: type: DESCFUNCTION covar_samp(x,y) - Returns the sample covariance of a set of number pairs The function takes as arguments any pair of numeric types and returns a double. -Any pair with a NULL is ignored. If the function is applied to an empty set, NULL -will be returned. Otherwise, it computes the following: +Any pair with a NULL is ignored. +If applied to an empty set: NULL is returned. +If applied to a set with a single element: NULL is returned. +Otherwise, it computes the following: (SUM(x*y)-SUM(x)*SUM(y)/COUNT(x,y))/(COUNT(x,y)-1) where neither x nor y is null. Function class:org.apache.hadoop.hive.ql.udf.generic.GenericUDAFCovarianceSample @@ -67,7 +69,7 @@ POSTHOOK: query: SELECT covar_samp(b, c) FROM covar_tab WHERE a = 3 POSTHOOK: type: QUERY POSTHOOK: Input: default@covar_tab #### A masked pattern was here #### -0.0 +NULL PREHOOK: query: SELECT a, covar_samp(b, c) FROM covar_tab GROUP BY a ORDER BY a PREHOOK: type: QUERY PREHOOK: Input: default@covar_tab @@ -78,10 +80,10 @@ POSTHOOK: Input: default@covar_tab #### A masked pattern was here #### 1 NULL 2 NULL -3 0.0 -4 0.0 -5 0.0 -6 0.0 +3 NULL +4 NULL +5 NULL +6 NULL PREHOOK: query: SELECT ROUND(covar_samp(b, c), 5) FROM covar_tab PREHOOK: type: QUERY PREHOOK: Input: default@covar_tab