udf-aggregates_part1.sql.out

-- Automatically generated by SQLQueryTestSuite
-- Number of queries: 43


-- !query
SELECT avg(udf(four)) AS avg_1 FROM onek
-- !query schema
struct<avg_1:double>
-- !query output
1.5


-- !query
SELECT udf(avg(a)) AS avg_32 FROM aggtest WHERE a < 100
-- !query schema
struct<avg_32:double>
-- !query output
32.666666666666664


-- !query
select CAST(avg(udf(b)) AS Decimal(10,3)) AS avg_107_943 FROM aggtest
-- !query schema
struct<avg_107_943:decimal(10,3)>
-- !query output
107.943


-- !query
SELECT sum(udf(four)) AS sum_1500 FROM onek
-- !query schema
struct<sum_1500:bigint>
-- !query output
1500


-- !query
SELECT udf(sum(a)) AS sum_198 FROM aggtest
-- !query schema
struct<sum_198:bigint>
-- !query output
198


-- !query
SELECT udf(udf(sum(b))) AS avg_431_773 FROM aggtest
-- !query schema
struct<avg_431_773:double>
-- !query output
431.77260909229517


-- !query
SELECT udf(max(four)) AS max_3 FROM onek
-- !query schema
struct<max_3:int>
-- !query output
3


-- !query
SELECT max(udf(a)) AS max_100 FROM aggtest
-- !query schema
struct<max_100:int>
-- !query output
100


-- !query
SELECT udf(udf(max(aggtest.b))) AS max_324_78 FROM aggtest
-- !query schema
struct<max_324_78:float>
-- !query output
324.78


-- !query
SELECT stddev_pop(udf(b)) FROM aggtest
-- !query schema
struct<stddev_pop(udf(b)):double>
-- !query output
131.10703231895047


-- !query
SELECT udf(stddev_samp(b)) FROM aggtest
-- !query schema
struct<udf(stddev_samp(b)):double>
-- !query output
151.38936080399804


-- !query
SELECT var_pop(udf(b)) FROM aggtest
-- !query schema
struct<var_pop(udf(b)):double>
-- !query output
17189.053923482323


-- !query
SELECT udf(var_samp(b)) FROM aggtest
-- !query schema
struct<udf(var_samp(b)):double>
-- !query output
22918.738564643096


-- !query
SELECT udf(stddev_pop(CAST(b AS Decimal(38,0)))) FROM aggtest
-- !query schema
struct<udf(stddev_pop(CAST(b AS DECIMAL(38,0)))):double>
-- !query output
131.18117242958306


-- !query
SELECT stddev_samp(CAST(udf(b) AS Decimal(38,0))) FROM aggtest
-- !query schema
struct<stddev_samp(CAST(udf(b) AS DECIMAL(38,0))):double>
-- !query output
151.47497042966097


-- !query
SELECT udf(var_pop(CAST(b AS Decimal(38,0)))) FROM aggtest
-- !query schema
struct<udf(var_pop(CAST(b AS DECIMAL(38,0)))):double>
-- !query output
17208.5


-- !query
SELECT var_samp(udf(CAST(b AS Decimal(38,0)))) FROM aggtest
-- !query schema
struct<var_samp(udf(CAST(b AS DECIMAL(38,0)))):double>
-- !query output
22944.666666666668


-- !query
SELECT udf(var_pop(1.0)), var_samp(udf(2.0))
-- !query schema
struct<udf(var_pop(1.0)):double,var_samp(udf(2.0)):double>
-- !query output
0.0	NULL


-- !query
SELECT stddev_pop(udf(CAST(3.0 AS Decimal(38,0)))), stddev_samp(CAST(udf(4.0) AS Decimal(38,0)))
-- !query schema
struct<stddev_pop(udf(CAST(3.0 AS DECIMAL(38,0)))):double,stddev_samp(CAST(udf(4.0) AS DECIMAL(38,0))):double>
-- !query output
0.0	NULL


-- !query
select sum(udf(CAST(null AS int))) from range(1,4)
-- !query schema
struct<sum(udf(CAST(NULL AS INT))):bigint>
-- !query output
NULL


-- !query
select sum(udf(CAST(null AS long))) from range(1,4)
-- !query schema
struct<sum(udf(CAST(NULL AS BIGINT))):bigint>
-- !query output
NULL


-- !query
select sum(udf(CAST(null AS Decimal(38,0)))) from range(1,4)
-- !query schema
struct<sum(udf(CAST(NULL AS DECIMAL(38,0)))):decimal(38,0)>
-- !query output
NULL


-- !query
select sum(udf(CAST(null AS DOUBLE))) from range(1,4)
-- !query schema
struct<sum(udf(CAST(NULL AS DOUBLE))):double>
-- !query output
NULL


-- !query
select avg(udf(CAST(null AS int))) from range(1,4)
-- !query schema
struct<avg(udf(CAST(NULL AS INT))):double>
-- !query output
NULL


-- !query
select avg(udf(CAST(null AS long))) from range(1,4)
-- !query schema
struct<avg(udf(CAST(NULL AS BIGINT))):double>
-- !query output
NULL


-- !query
select avg(udf(CAST(null AS Decimal(38,0)))) from range(1,4)
-- !query schema
struct<avg(udf(CAST(NULL AS DECIMAL(38,0)))):decimal(38,4)>
-- !query output
NULL


-- !query
select avg(udf(CAST(null AS DOUBLE))) from range(1,4)
-- !query schema
struct<avg(udf(CAST(NULL AS DOUBLE))):double>
-- !query output
NULL


-- !query
select sum(CAST(udf('NaN') AS DOUBLE)) from range(1,4)
-- !query schema
struct<sum(CAST(udf(NaN) AS DOUBLE)):double>
-- !query output
NaN


-- !query
select avg(CAST(udf('NaN') AS DOUBLE)) from range(1,4)
-- !query schema
struct<avg(CAST(udf(NaN) AS DOUBLE)):double>
-- !query output
NaN


-- !query
SELECT avg(CAST(udf(x) AS DOUBLE)), var_pop(CAST(udf(x) AS DOUBLE))
FROM (VALUES ('Infinity'), ('1')) v(x)
-- !query schema
struct<avg(CAST(udf(x) AS DOUBLE)):double,var_pop(CAST(udf(x) AS DOUBLE)):double>
-- !query output
Infinity	NaN


-- !query
SELECT avg(CAST(udf(x) AS DOUBLE)), var_pop(CAST(udf(x) AS DOUBLE))
FROM (VALUES ('Infinity'), ('Infinity')) v(x)
-- !query schema
struct<avg(CAST(udf(x) AS DOUBLE)):double,var_pop(CAST(udf(x) AS DOUBLE)):double>
-- !query output
Infinity	NaN


-- !query
SELECT avg(CAST(udf(x) AS DOUBLE)), var_pop(CAST(udf(x) AS DOUBLE))
FROM (VALUES ('-Infinity'), ('Infinity')) v(x)
-- !query schema
struct<avg(CAST(udf(x) AS DOUBLE)):double,var_pop(CAST(udf(x) AS DOUBLE)):double>
-- !query output
NaN	NaN


-- !query
SELECT avg(udf(CAST(x AS DOUBLE))), udf(var_pop(CAST(x AS DOUBLE)))
FROM (VALUES (100000003), (100000004), (100000006), (100000007)) v(x)
-- !query schema
struct<avg(udf(CAST(x AS DOUBLE))):double,udf(var_pop(CAST(x AS DOUBLE))):double>
-- !query output
1.00000005E8	2.5


-- !query
SELECT avg(udf(CAST(x AS DOUBLE))), udf(var_pop(CAST(x AS DOUBLE)))
FROM (VALUES (7000000000005), (7000000000007)) v(x)
-- !query schema
struct<avg(udf(CAST(x AS DOUBLE))):double,udf(var_pop(CAST(x AS DOUBLE))):double>
-- !query output
7.000000000006E12	1.0


-- !query
SELECT udf(covar_pop(b, udf(a))), covar_samp(udf(b), a) FROM aggtest
-- !query schema
struct<udf(covar_pop(b, udf(a))):double,covar_samp(udf(b), a):double>
-- !query output
653.6289553875104	871.5052738500139


-- !query
SELECT corr(b, udf(a)) FROM aggtest
-- !query schema
struct<corr(b, udf(a)):double>
-- !query output
0.1396345165178734


-- !query
SELECT count(udf(four)) AS cnt_1000 FROM onek
-- !query schema
struct<cnt_1000:bigint>
-- !query output
1000


-- !query
SELECT udf(count(DISTINCT four)) AS cnt_4 FROM onek
-- !query schema
struct<cnt_4:bigint>
-- !query output
4


-- !query
select ten, udf(count(*)), sum(udf(four)) from onek
group by ten order by ten
-- !query schema
struct<ten:int,udf(count(1)):bigint,sum(udf(four)):bigint>
-- !query output
0	100	100
1	100	200
2	100	100
3	100	200
4	100	100
5	100	200
6	100	100
7	100	200
8	100	100
9	100	200


-- !query
select ten, count(udf(four)), udf(sum(DISTINCT four)) from onek
group by ten order by ten
-- !query schema
struct<ten:int,count(udf(four)):bigint,udf(sum(DISTINCT four)):bigint>
-- !query output
0	100	2
1	100	4
2	100	2
3	100	4
4	100	2
5	100	4
6	100	2
7	100	4
8	100	2
9	100	4


-- !query
select ten, udf(sum(distinct four)) from onek a
group by ten
having exists (select 1 from onek b where udf(sum(distinct a.four)) = b.four)
-- !query schema
struct<ten:int,udf(sum(DISTINCT four)):bigint>
-- !query output
0	2
2	2
4	2
6	2
8	2


-- !query
select ten, sum(distinct four) from onek a
group by ten
having exists (select 1 from onek b
               where sum(distinct a.four + b.four) = udf(b.four))
-- !query schema
struct<>
-- !query output
org.apache.spark.sql.AnalysisException

Aggregate/Window/Generate expressions are not valid in where clause of the query.
Expression in where clause: [(sum(DISTINCT (outer(a.four) + b.four)) = CAST(CAST(udf(ansi_cast(four as string)) AS INT) AS BIGINT))]
Invalid expressions: [sum(DISTINCT (outer(a.four) + b.four))]


-- !query
select
  (select udf(max((select i.unique2 from tenk1 i where i.unique1 = o.unique1))))
from tenk1 o
-- !query schema
struct<>
-- !query output
org.apache.spark.sql.AnalysisException
cannot resolve 'o.unique1' given input columns: [i.even, i.fivethous, i.four, i.hundred, i.odd, i.string4, i.stringu1, i.stringu2, i.ten, i.tenthous, i.thousand, i.twenty, i.two, i.twothousand, i.unique1, i.unique2]; line 2 pos 67