gatorsmile commented on a change in pull request #24640: [SPARK-27770] [SQL] [TEST] Port AGGREGATES.sql [Part 1] URL: https://github.com/apache/spark/pull/24640#discussion_r285399971
########## File path: sql/core/src/test/resources/sql-tests/inputs/aggregates_part1.sql ########## @@ -0,0 +1,151 @@ +-- +-- Portions Copyright (c) 1996-2019, PostgreSQL Global Development Group +-- +-- +-- AGGREGATES [Part 1] +-- https://github.com/postgres/postgres/blob/02ddd499322ab6f2f0d58692955dc9633c2150fc/src/test/regress/sql/aggregates.sql#L1-L143 + +-- avoid bit-exact output here because operations may not be bit-exact. +-- SET extra_float_digits = 0; + +SELECT avg(four) AS avg_1 FROM onek; + +SELECT avg(a) AS avg_32 FROM aggtest WHERE a < 100; + +-- In 7.1, avg(float4) is computed using float8 arithmetic. +-- Round the result to 3 digits to avoid platform-specific results. + +select CAST(avg(b) AS Decimal(10,3)) AS avg_107_943 FROM aggtest; +-- `student` has a column with data type POINT, which is not supported by Spark [SPARK-27766] +-- SELECT avg(gpa) AS avg_3_4 FROM ONLY student; + +SELECT sum(four) AS sum_1500 FROM onek; +SELECT sum(a) AS sum_198 FROM aggtest; +SELECT sum(b) AS avg_431_773 FROM aggtest; +-- `student` has a column with data type POINT, which is not supported by Spark [SPARK-27766] +-- SELECT sum(gpa) AS avg_6_8 FROM ONLY student; + +SELECT max(four) AS max_3 FROM onek; +SELECT max(a) AS max_100 FROM aggtest; +SELECT max(aggtest.b) AS max_324_78 FROM aggtest; +-- `student` has a column with data type POINT, which is not supported by Spark [SPARK-27766] +-- SELECT max(student.gpa) AS max_3_7 FROM student; + +SELECT stddev_pop(b) FROM aggtest; +SELECT stddev_samp(b) FROM aggtest; +SELECT var_pop(b) FROM aggtest; +SELECT var_samp(b) FROM aggtest; + +SELECT stddev_pop(CAST(b AS Decimal(38,0))) FROM aggtest; +SELECT stddev_samp(CAST(b AS Decimal(38,0))) FROM aggtest; +SELECT var_pop(CAST(b AS Decimal(38,0))) FROM aggtest; +SELECT var_samp(CAST(b AS Decimal(38,0))) FROM aggtest; + +-- population variance is defined for a single tuple, sample variance +-- is not +SELECT var_pop(1.0), var_samp(2.0); +SELECT stddev_pop(CAST(3.0 AS Decimal(38,0))), stddev_samp(CAST(4.0 AS Decimal(38,0))); + + +-- verify correct results for null and NaN inputs +select sum(CAST(null AS int)) from range(1,4); +select sum(CAST(null AS long)) from range(1,4); +select sum(CAST(null AS Decimal(38,0))) from range(1,4); +select sum(CAST(null AS DOUBLE)) from range(1,4); +select avg(CAST(null AS int)) from range(1,4); +select avg(CAST(null AS long)) from range(1,4); +select avg(CAST(null AS Decimal(38,0))) from range(1,4); +select avg(CAST(null AS DOUBLE)) from range(1,4); +select sum(CAST('NaN' AS DOUBLE)) from range(1,4); +select avg(CAST('NaN' AS DOUBLE)) from range(1,4); + +-- [SPARK-27768] verify correct results for infinite inputs Review comment: Updated the JIRA and make the description more accurate. ---------------------------------------------------------------- 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. For queries about this service, please contact Infrastructure at: us...@infra.apache.org With regards, Apache Git Services --------------------------------------------------------------------- To unsubscribe, e-mail: reviews-unsubscr...@spark.apache.org For additional commands, e-mail: reviews-h...@spark.apache.org
