Build tested: 1.4.0-1

[dlee@master centos7]$ cat gitversionInfo.txt
server commit:
67452bc
engine commit:
4d2a159

Autopilot test case: features.windowFunctions

There seems to be an issue with window functions and it is affective all window functions. The issue with the query selects from a subquery, it returns only one row, instead of N rows in the table. The query in question is in the following general syntax pattern:

select o_custkey, corr(o_custkey,o_orderkey) OVER (PARTITION BY 55 ) from (select * from orders where o_custkey <= 20000) s order by 1, 2;

The following syntax pattern seems to be ok

select o_custkey, corr(o_custkey,o_orderkey) OVER (PARTITION BY abs(o_custkey)+2 ORDER BY o_custkey ,o_orderkey ,o_orderdate ROWS BETWEEN 15 FOLLOWING AND 15 FOLLOWING) from orders order by 1, 2;

Here is a window function test result summary comparison:

1.4.0-1

function precision numQuery numQueryDiff numVariance sumVariance minVariance maxVariance avgVariance
=========================== ========= ======== ============ =========== ====================== =============== ====================== ======================
Avg.diff.txt 8 248 142 206284 13043710.435600 0.000000 1815.000000 63.2318087472
corr.diff.txt 8 248 138 186432 231983.344320 0.000000 1815.025505 1.2443322194
Count.diff.txt 8 248 124 186000 22088960.000000 0.000000 1815.000000 118.7578494624
covar_pop.diff.txt 8 248 147 215730 21122815.832862 0.000000 2637.796900 97.9132055480
covar_samp.diff.txt 8 248 147 215934 21644713.796250 0.000000 2813.650000 100.2376364827
Cume_dist.diff.txt 8 16 12 312 0.000000 0.000000 0.000000 0.0000000000
Dense_rank.diff.txt 8 16 0
First_value.diff.txt 8 240 120 180000 239544924.000000 0.000000 5988.000000 1330.8051333333
Lag.diff.txt 8 16 8 12000 32608284.000000 0.000000 5988.000000 2717.3570000000
Last_value.diff.txt 8 240 120 180000 656600170.000000 0.000000 5988.000000 3647.7787222222
Lead.diff.txt 8 16 8 12000 35658852.000000 0.000000 5988.000000 2971.5710000000
Max.diff.txt 8 248 124 186000 14046204.000000 0.000000 1815.000000 75.5172258065
Median.diff.txt 8 8 0
Min.diff.txt 8 248 124 186000 12012344.000000 0.000000 1815.000000 64.5824946237
Nth_value.diff.txt 8 240 120 180000 8811858.000000 0.000000 1815.000000 48.9547666667
Ntile.diff.txt 8 16 0
Percentile_cont.diff.txt 8 8 0
Percentile_disc.diff.txt 8 8 0
Percent_rank.diff.txt 8 16 16 428 0.000000 0.000000 0.000000 0.0000000000
Rank.diff.txt 8 16 0
regr_avgx.diff.txt 8 248 221 263108 486859254.984325 0.000000 5988.000000 1850.4160078155
regr_avgy.diff.txt 8 248 146 208176 13395249.917502 0.000000 1815.000000 64.3457935473
regr_count.diff.txt 8 248 132 190566 22093526.000000 0.000000 1815.000000 115.9363475121
regr_intercept.diff.txt 8 248 147 214912 10199974.908664 0.000000 1815.000000 47.4611697284
regr_r2.diff.txt 8 248 144 187328 330519.275030 0.000000 1815.000000 1.7643879988
regr_slope.diff.txt 8 248 138 186436 225067.079732 0.000000 1815.000012 1.2072082631
regr_sxx.diff.txt 8 248 216 279481 64608666176491.073277 0.000000 4500077623.000000 231173733.3718251805
regr_sxy.diff.txt 8 248 144 206414 11066253852.336044 0.000000 2078128.200000 53611.9345215734
regr_syy.diff.txt 8 248 138 205598 25542705463.576672 0.000000 2824635.600000 124236.1572757355
Row_number.diff.txt 8 16 0
Stddev.diff.txt 8 248 147 218716 820978.636244 0.000000 1815.000000 3.7536286154
Stddev_pop.diff.txt 8 248 147 218208 819882.530728 0.000000 1815.000000 3.7573440512
Stddev_samp.diff.txt 8 248 147 217104 820428.464276 0.000000 1815.000000 3.7789652161
Sum.diff.txt 8 248 124 186000 1555572814.000000 0.000000 114240.000000 8363.2946989247
Variance.diff.txt 8 248 147 218716 20066125.300368 0.000000 1884.346630 91.7451183286
Var_pop.diff.txt 8 248 144 211080 20030987.140316 0.000000 1883.090400 94.8976082069
Var_samp.diff.txt 8 248 147 212942 20048356.676540 0.000000 1884.346630 94.1493771851

1.2.4-1

function precision numQuery numQueryDiff numVariance sumVariance minVariance maxVariance avgVariance
=========================== ========= ======== ============ =========== ====================== =============== ====================== ======================
Avg.diff.txt 8 248 36 40568 1.038000 0.000000 0.000050 0.0000255867
corr.diff.txt 8 248 28 864 0.000032 0.000000 0.000001 0.0000000370
Count.diff.txt 8 248 0
covar_pop.diff.txt 8 248 46 59460 0.305184 0.000000 0.000050 0.0000051326
covar_samp.diff.txt 8 248 46 59868 0.321652 0.000000 0.000050 0.0000053727
Cume_dist.diff.txt 8 16 12 312 0.000000 0.000000 0.000000 0.0000000000
Dense_rank.diff.txt 8 16 0
First_value.diff.txt 8 240 0
Lag.diff.txt 8 16 0
Last_value.diff.txt 8 240 0
Lead.diff.txt 8 16 0
Max.diff.txt 8 248 0
Median.diff.txt 8 8 0
Min.diff.txt 8 248 0
Nth_value.diff.txt 8 240 0
Ntile.diff.txt 8 16 0
Percentile_cont.diff.txt 8 8 0
Percentile_disc.diff.txt 8 8 0
Percent_rank.diff.txt 8 16 16 428 0.000000 0.000000 0.000000 0.0000000000
Rank.diff.txt 8 16 0
regr_avgx.diff.txt 8 248 194 149630 21153488.002280 0.000010 5988.000000 141.3719708767
regr_avgy.diff.txt 8 248 52 49700 703081.038000 0.000000 149.000000 14.1464997586
regr_count.diff.txt 8 248 16 9132 9132.000000 1.000000 1.000000 1.0000000000
regr_intercept.diff.txt 8 248 46 57824 0.011064 0.000000 0.000001 0.0000001913
regr_r2.diff.txt 8 248 40 2656 0.000012 0.000000 0.000001 0.0000000045
regr_slope.diff.txt 8 248 28 872 0.000000 0.000000 0.000000 0.0000000000
regr_sxx.diff.txt 8 248 184 186962 13884.962654 0.000000 0.500000 0.0742662287
regr_sxy.diff.txt 8 248 40 40828 99.122156 0.000000 0.049896 0.0024277985
regr_syy.diff.txt 8 248 28 39196 527.189092 0.000000 37.741936 0.0134500738
Row_number.diff.txt 8 16 0
Stddev.diff.txt 8 248 46 65432 1101.988608 0.002905 0.712623 0.0168417381
Stddev_pop.diff.txt 8 248 46 64416 1.490112 0.000000 0.000050 0.0000231326
Stddev_samp.diff.txt 8 248 46 62208 1.644672 0.000000 0.000050 0.0000264383
Sum.diff.txt 8 248 0
Variance.diff.txt 8 248 46 65432 35538.722568 0.001058 39.609390 0.5431397874
Var_pop.diff.txt 8 248 40 50160 1.270316 0.000000 0.000050 0.0000253253
Var_samp.diff.txt 8 248 46 53884 1.474912 0.000000 0.000050 0.0000273720