ggangadharan commented on code in PR #6898: URL: https://github.com/apache/hive/pull/6898#discussion_r4444826729
########## ql/src/test/queries/clientpositive/vector_ptf_variance.q: ########## @@ -0,0 +1,325 @@ +--! qt:dataset:alltypesorc +set hive.explain.user=false; +set hive.fetch.task.conversion=none; +set hive.vectorized.execution.enabled=true; +set hive.vectorized.execution.reduce.enabled=true; +-- Several batches per partition, so the evaluators work across batch boundaries. +set hive.vectorized.testing.reducer.batch.size=3; +-- SORT_QUERY_RESULTS + +-- Vectorized PTF for the variance family: +-- variance, var_pop, var_samp, std, stddev, stddev_pop, stddev_samp + +DROP TABLE IF EXISTS vector_ptf_variance_t; + +CREATE TABLE vector_ptf_variance_t(id int, p string, k int, ti tinyint, si smallint, i int, + bi bigint, f float, d double, dec_s decimal(10,2), dec_l decimal(38,18)) STORED AS ORC; + +INSERT INTO vector_ptf_variance_t VALUES +-- 'a': mixed values, nulls and ties on k +(1, 'a', 1, 1, 10, 100, 1000, 1.5, 1.25, 1.10, 1.123456789012345678), +(2, 'a', 1, 2, 20, 200, 2000, 2.5, 2.25, 2.20, 2.2), +(3, 'a', 2, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL), +(4, 'a', 3, 4, 40, 400, 4000, 4.5, 4.25, 4.40, 4.4), +(5, 'a', 3, 5, 50, 500, 5000, 5.5, 5.25, 5.50, 5.5), +(6, 'a', 5, -6, -60, -600, -6000, -6.5, -6.25, -6.60, -6.6), +(7, 'a', 8, 7, 70, 700, 7000, 7.5, 7.25, 7.70, 7.7), +(8, 'a', 9, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL), +-- 'b': single row, sample variants are null, population variants are 0 +(9, 'b', 1, 3, 30, 300, 3000, 3.5, 3.25, 3.30, 3.3), +-- 'c': only nulls +(10, 'c', 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL), +(11, 'c', 2, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL), +-- 'd': the same value repeated +(12, 'd', 1, 5, 5, 5, 5, 5.0, 5.0, 5.00, 5), +(13, 'd', 2, 5, 5, 5, 5, 5.0, 5.0, 5.00, 5), +(14, 'd', 3, 5, 5, 5, 5, 5.0, 5.0, 5.00, 5), +(15, 'd', 4, 5, 5, 5, 5, 5.0, 5.0, 5.00, 5), +-- 'e': leading nulls, then extreme values +(16, 'e', 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL), +(17, 'e', 2, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL), +(18, 'e', 3, 127, 32767, 2147483647, 9223372036854775807, 3.4E38, 1.0E150, 99999999.99, + 99999999999999999999.999999999999999999), +(19, 'e', 4, -128, -32768, -2147483648, -9223372036854775807, -3.4E38, -1.0E150, -99999999.99, + -99999999999999999999.999999999999999999), +(20, 'e', 5, 0, 0, 0, 0, 0, 0, 0, 0), +-- null partition +(21, NULL, 1, 1, 1, 1, 1, 1.0, 1.0, 1.00, 1), +(22, NULL, 2, 3, 3, 3, 3, 3.0, 3.0, 3.00, 3); + + +-- NON-VECTORIZED: baseline output the vectorized run must match +set hive.vectorized.execution.ptf.enabled=false; + +-- 1. All the function names with the default frame (RANGE UNBOUNDED PRECEDING AND CURRENT ROW, +-- ties on k) and all the input types over the whole partition +select id, p, k, d, +variance(d) over w1 as var, var_pop(d) over w1 as var_pop, var_samp(d) over w1 as var_samp, +std(d) over w1 as std, stddev(d) over w1 as sd, stddev_pop(d) over w1 as sd_pop, +stddev_samp(d) over w1 as sd_samp, +var_samp(ti) over w2 as ti, var_samp(si) over w2 as si, var_samp(i) over w2 as i, +var_samp(bi) over w2 as bi, var_samp(f) over w2 as f, var_samp(d) over w2 as d_part, +var_samp(dec_s) over w2 as dec_s, var_samp(dec_l) over w2 as dec_l +from vector_ptf_variance_t +window w1 as (partition by p order by k), w2 as (partition by p); + +-- 2. Streaming: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW +select id, p, k, +var_pop(ti) over w as ti, stddev_samp(si) over w as si, variance(i) over w as i, +stddev_pop(bi) over w as bi, var_samp(f) over w as f, stddev(d) over w as d, +var_samp(dec_s) over w as dec_s, stddev_samp(dec_l) over w as dec_l +from vector_ptf_variance_t +window w as (partition by p order by k, id rows between unbounded preceding and current row); + +-- 3. Bounded frames +select id, p, k, i, +var_samp(i) over (partition by p order by k, id rows between 2 preceding and current row) as r2p, +stddev_pop(i) over (partition by p order by k, id rows between 1 preceding and unbounded following) as r1p, +var_pop(i) over (partition by p order by k range between 2 preceding and current row) as rg2p, +stddev_samp(i) over (partition by p order by k, id rows between current row and unbounded following) as rcf +from vector_ptf_variance_t; + +-- 4. Streaming and non-streaming evaluators together with other functions in one PTF +select id, p, k, dec_s, +row_number() over (partition by p order by k, id) as rn, +stddev_samp(dec_s) over (partition by p order by k, id rows between unbounded preceding and current row) as sd_stream, +var_pop(dec_s) over (partition by p order by k, id) as var_range, +avg(dec_s) over (partition by p order by k, id) as av, +variance(dec_l) over (partition by p order by k, id rows between 2 preceding and current row) as var_rows +from vector_ptf_variance_t; + +-- 5. Expression and constant (repeating) arguments, ORDER BY without PARTITION BY and an empty +-- OVER clause +select id, p, k, +stddev_pop(si + 1) over (order by id) as sd_expr, +var_samp(d * 2) over (order by id rows between unbounded preceding and current row) as var_expr, +stddev_samp(f) over () as sd_all, +var_pop(2) over (partition by p order by k, id rows between unbounded preceding and current row) as var_const_stream, +stddev_samp(1.5) over (partition by p) as sd_const, +variance(cast(null as int)) over (partition by p order by k, id rows between unbounded preceding and current row) as var_null_stream, +stddev_pop(cast(null as double)) over (partition by p order by k) as sd_null +from vector_ptf_variance_t; + + +-- VECTORIZED: same queries with PTF vectorization on, results must be identical +set hive.vectorized.execution.ptf.enabled=true; + +explain vectorization expression +select id, p, k, d, +variance(d) over w1 as var, var_pop(d) over w1 as var_pop, var_samp(d) over w1 as var_samp, +std(d) over w1 as std, stddev(d) over w1 as sd, stddev_pop(d) over w1 as sd_pop, +stddev_samp(d) over w1 as sd_samp, +var_samp(ti) over w2 as ti, var_samp(si) over w2 as si, var_samp(i) over w2 as i, +var_samp(bi) over w2 as bi, var_samp(f) over w2 as f, var_samp(d) over w2 as d_part, +var_samp(dec_s) over w2 as dec_s, var_samp(dec_l) over w2 as dec_l +from vector_ptf_variance_t +window w1 as (partition by p order by k), w2 as (partition by p); + +select id, p, k, d, +variance(d) over w1 as var, var_pop(d) over w1 as var_pop, var_samp(d) over w1 as var_samp, +std(d) over w1 as std, stddev(d) over w1 as sd, stddev_pop(d) over w1 as sd_pop, +stddev_samp(d) over w1 as sd_samp, +var_samp(ti) over w2 as ti, var_samp(si) over w2 as si, var_samp(i) over w2 as i, +var_samp(bi) over w2 as bi, var_samp(f) over w2 as f, var_samp(d) over w2 as d_part, +var_samp(dec_s) over w2 as dec_s, var_samp(dec_l) over w2 as dec_l +from vector_ptf_variance_t +window w1 as (partition by p order by k), w2 as (partition by p); + +explain vectorization expression +select id, p, k, +var_pop(ti) over w as ti, stddev_samp(si) over w as si, variance(i) over w as i, +stddev_pop(bi) over w as bi, var_samp(f) over w as f, stddev(d) over w as d, +var_samp(dec_s) over w as dec_s, stddev_samp(dec_l) over w as dec_l +from vector_ptf_variance_t +window w as (partition by p order by k, id rows between unbounded preceding and current row); + +select id, p, k, +var_pop(ti) over w as ti, stddev_samp(si) over w as si, variance(i) over w as i, +stddev_pop(bi) over w as bi, var_samp(f) over w as f, stddev(d) over w as d, +var_samp(dec_s) over w as dec_s, stddev_samp(dec_l) over w as dec_l +from vector_ptf_variance_t +window w as (partition by p order by k, id rows between unbounded preceding and current row); + +select id, p, k, i, Review Comment: Added. It also confirms the bounded frames are fully vectorized: ROWS 2 PRECEDING~CURRENT, ROWS 1 PRECEDING~UNBOUNDED FOLLOWING and ROWS CURRENT~UNBOUNDED FOLLOWING run in one reducer, and RANGE 2 PRECEDING~CURRENT in another, all through VectorPTFEvaluatorVariance. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
