HIVE-10140 : Window boundary is not compared correctly (Aihua Xu via Ashutosh Chauhan)
Signed-off-by: Ashutosh Chauhan <hashut...@apache.org> Project: http://git-wip-us.apache.org/repos/asf/hive/repo Commit: http://git-wip-us.apache.org/repos/asf/hive/commit/e720b586 Tree: http://git-wip-us.apache.org/repos/asf/hive/tree/e720b586 Diff: http://git-wip-us.apache.org/repos/asf/hive/diff/e720b586 Branch: refs/heads/branch-1.0 Commit: e720b586bf0b6d881a58ddf38fecf132bccf9011 Parents: 20e1cc8 Author: Aihua Xu <aihu...@gmail.com> Authored: Thu Apr 30 09:42:00 2015 -0700 Committer: Pengcheng Xiong <pxi...@apache.org> Committed: Tue Aug 11 14:17:41 2015 -0700 ---------------------------------------------------------------------- .../hadoop/hive/ql/parse/WindowingSpec.java | 7 +- .../clientpositive/windowing_windowspec.q | 2 + .../clientpositive/windowing_windowspec.q.out | 108 +++++++++++++++++++ 3 files changed, 115 insertions(+), 2 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/hive/blob/e720b586/ql/src/java/org/apache/hadoop/hive/ql/parse/WindowingSpec.java ---------------------------------------------------------------------- diff --git a/ql/src/java/org/apache/hadoop/hive/ql/parse/WindowingSpec.java b/ql/src/java/org/apache/hadoop/hive/ql/parse/WindowingSpec.java index 28afc6b..83f3513 100644 --- a/ql/src/java/org/apache/hadoop/hive/ql/parse/WindowingSpec.java +++ b/ql/src/java/org/apache/hadoop/hive/ql/parse/WindowingSpec.java @@ -613,8 +613,10 @@ public class WindowingSpec { if (c != 0) { return c; } + RangeBoundarySpec rb = (RangeBoundarySpec) other; - return amt - rb.amt; + // Valid range is "range/rows between 10 preceding and 2 preceding" for preceding case + return this.direction == Direction.PRECEDING ? rb.amt - amt : amt - rb.amt; } } @@ -712,7 +714,8 @@ public class WindowingSpec { return c; } ValueBoundarySpec vb = (ValueBoundarySpec) other; - return amt - vb.amt; + // Valid range is "range/rows between 10 preceding and 2 preceding" for preceding case + return this.direction == Direction.PRECEDING ? vb.amt - amt : amt - vb.amt; } } http://git-wip-us.apache.org/repos/asf/hive/blob/e720b586/ql/src/test/queries/clientpositive/windowing_windowspec.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/windowing_windowspec.q b/ql/src/test/queries/clientpositive/windowing_windowspec.q index 6d8ce67..2055e9d 100644 --- a/ql/src/test/queries/clientpositive/windowing_windowspec.q +++ b/ql/src/test/queries/clientpositive/windowing_windowspec.q @@ -31,6 +31,8 @@ select s, sum(i) over(partition by ts order by s) from over10k limit 100; select f, sum(f) over (partition by ts order by f range between unbounded preceding and current row) from over10k limit 100; +select f, sum(f) over (partition by ts order by f rows between 2 preceding and 1 preceding) from over10k limit 100; + select s, i, round(avg(d) over (partition by s order by i) / 10.0 , 2) from over10k limit 7; select s, i, round((avg(d) over w1 + 10.0) - (avg(d) over w1 - 10.0),2) from over10k window w1 as (partition by s order by i) limit 7; http://git-wip-us.apache.org/repos/asf/hive/blob/e720b586/ql/src/test/results/clientpositive/windowing_windowspec.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/windowing_windowspec.q.out b/ql/src/test/results/clientpositive/windowing_windowspec.q.out index 00af6b8..de4ae97 100644 --- a/ql/src/test/results/clientpositive/windowing_windowspec.q.out +++ b/ql/src/test/results/clientpositive/windowing_windowspec.q.out @@ -800,6 +800,114 @@ POSTHOOK: Input: default@over10k 71.68 722.6499947607517 79.46 802.1099938452244 80.02 882.1299904882908 +PREHOOK: query: select f, sum(f) over (partition by ts order by f rows between 2 preceding and 1 preceding) from over10k limit 100 +PREHOOK: type: QUERY +PREHOOK: Input: default@over10k +#### A masked pattern was here #### +POSTHOOK: query: select f, sum(f) over (partition by ts order by f rows between 2 preceding and 1 preceding) from over10k limit 100 +POSTHOOK: type: QUERY +POSTHOOK: Input: default@over10k +#### A masked pattern was here #### +3.17 14.0600004196167 +10.89 28.600000381469727 +14.54 43.38000011444092 +14.78 58.0600004196167 +17.85 67.78000068664551 +20.61 81.9300012588501 +28.69 96.3700008392334 +29.22 109.69000053405762 +31.17 127.42999839782715 +38.35 137.3499984741211 +38.61 147.60999870300293 +39.48 156.97999954223633 +40.54 160.22999954223633 +41.6 167.70000076293945 +46.08 182.5800018310547 +54.36 198.97999954223633 +56.94 222.3400001525879 +64.96 249.7799949645996 +73.52 273.99999618530273 +78.58 298.4700012207031 +81.41 318.2200012207031 +84.71 332.1300048828125 +87.43 344.9100036621094 +91.36 356.45999908447266 +92.96 366.79000091552734 +95.04 279.36000061035156 +0.83 2.8199999928474426 +1.99 6.550000011920929 +3.73 15.409999668598175 +8.86 25.199999570846558 +10.62 34.52999925613403 +11.32 43.6299991607666 +12.83 49.46999931335449 +14.7 53.80999946594238 +14.96 60.06999969482422 +17.58 66.34000015258789 +19.1 72.65000057220459 +21.01 84.64000129699707 +26.95 94.29000091552734 +27.23 104.26000022888184 +29.07 112.95999908447266 +29.71 117.8499984741211 +31.84 122.55999946594238 +31.94 128.80999946594238 +35.32 136.42000007629395 +37.32 143.07999992370605 +38.5 153.22000122070312 +42.08 162.20000076293945 +44.3 169.54000091552734 +44.66 177.88000106811523 +46.84 184.68999862670898 +48.89 190.02999877929688 +49.64 195.64999771118164 +50.28 200.89999771118164 +52.09 205.2699966430664 +53.26 209.71999740600586 +54.09 215.88999938964844 +56.45 220.55999755859375 +56.76 228.70999908447266 +61.41 236.5 +61.88 243.07999801635742 +63.03 250.87000274658203 +64.55 258.08000564575195 +68.62 272.3300018310547 +76.13 288.3500061035156 +79.05 304.2300033569336 +80.43 317.02000427246094 +81.41 323.74000549316406 +82.85 328.67000579833984 +83.98 332.4500045776367 +84.21 336.59000396728516 +85.55 341.67000579833984 +87.93 346.62000274658203 +88.93 356.6800003051758 +94.27 370.57999420166016 +99.45 282.6499938964844 +0.36 0.8400000035762787 +0.48 1.6300000250339508 +0.79 2.9000000059604645 +1.27 7.020000010728836 +4.48 15.540000021457672 +9.0 38.02000045776367 +23.27 61.87999963760376 +25.13 82.73999977111816 +25.34 99.64999961853027 +25.91 105.38999938964844 +29.01 110.72999954223633 +30.47 123.34000015258789 +37.95 136.72999954223633 +39.3 153.6299991607666 +45.91 175.5999984741211 +52.44 191.74999618530273 +54.1 209.14999771118164 +56.7 222.0099983215332 +58.77 231.6599998474121 +62.09 245.7599983215332 +68.2 260.73999786376953 +71.68 281.4299964904785 +79.46 299.35999298095703 +80.02 312.4499969482422 PREHOOK: query: select s, i, round(avg(d) over (partition by s order by i) / 10.0 , 2) from over10k limit 7 PREHOOK: type: QUERY PREHOOK: Input: default@over10k
