wangyum commented on a change in pull request #24743: [SPARK-27883][SQL] Port AGGREGATES.sql [Part 2] URL: https://github.com/apache/spark/pull/24743#discussion_r289697281
########## File path: sql/core/src/test/resources/sql-tests/inputs/pgSQL/aggregates_part2.sql ########## @@ -7,207 +7,211 @@ -- Test handling of Params within aggregate arguments in hashed aggregation. -- Per bug report from Jeevan Chalke. -explain (verbose, costs off) -select s1, s2, sm -from generate_series(1, 3) s1, - lateral (select s2, sum(s1 + s2) sm - from generate_series(1, 3) s2 group by s2) ss -order by 1, 2; -select s1, s2, sm -from generate_series(1, 3) s1, - lateral (select s2, sum(s1 + s2) sm - from generate_series(1, 3) s2 group by s2) ss -order by 1, 2; - -explain (verbose, costs off) -select array(select sum(x+y) s - from generate_series(1,3) y group by y order by s) - from generate_series(1,3) x; -select array(select sum(x+y) s - from generate_series(1,3) y group by y order by s) - from generate_series(1,3) x; - +-- [SPARK-27877] Implement SQL-standard LATERAL subqueries +-- explain (verbose, costs off) +-- select s1, s2, sm +-- from generate_series(1, 3) s1, +-- lateral (select s2, sum(s1 + s2) sm +-- from generate_series(1, 3) s2 group by s2) ss +-- order by 1, 2; +-- select s1, s2, sm +-- from generate_series(1, 3) s1, +-- lateral (select s2, sum(s1 + s2) sm +-- from generate_series(1, 3) s2 group by s2) ss +-- order by 1, 2; + +-- [SPARK-27878] Support ARRAY(sub-SELECT) expressions +-- explain (verbose, costs off) +-- select array(select sum(x+y) s +-- from generate_series(1,3) y group by y order by s) +-- from generate_series(1,3) x; +-- select array(select sum(x+y) s +-- from generate_series(1,3) y group by y order by s) +-- from generate_series(1,3) x; + +-- [SPARK-27879] Implement bitwise integer aggregates(BIT_AND and BIT_OR) -- -- test for bitwise integer aggregates -- -CREATE TEMPORARY TABLE bitwise_test( - i2 INT2, - i4 INT4, - i8 INT8, - i INTEGER, - x INT2, - y BIT(4) -); +-- CREATE TEMPORARY TABLE bitwise_test( +-- i2 INT2, +-- i4 INT4, +-- i8 INT8, +-- i INTEGER, +-- x INT2, +-- y BIT(4) +-- ); -- empty case -SELECT - BIT_AND(i2) AS "?", - BIT_OR(i4) AS "?" -FROM bitwise_test; - -COPY bitwise_test FROM STDIN NULL 'null'; -1 1 1 1 1 B0101 -3 3 3 null 2 B0100 -7 7 7 3 4 B1100 -\. - -SELECT - BIT_AND(i2) AS "1", - BIT_AND(i4) AS "1", - BIT_AND(i8) AS "1", - BIT_AND(i) AS "?", - BIT_AND(x) AS "0", - BIT_AND(y) AS "0100", - - BIT_OR(i2) AS "7", - BIT_OR(i4) AS "7", - BIT_OR(i8) AS "7", - BIT_OR(i) AS "?", - BIT_OR(x) AS "7", - BIT_OR(y) AS "1101" -FROM bitwise_test; +-- SELECT +-- BIT_AND(i2) AS "?", +-- BIT_OR(i4) AS "?" +-- FROM bitwise_test; + +-- COPY bitwise_test FROM STDIN NULL 'null'; +-- 1 1 1 1 1 B0101 +-- 3 3 3 null 2 B0100 +-- 7 7 7 3 4 B1100 +-- \. + +-- SELECT +-- BIT_AND(i2) AS "1", +-- BIT_AND(i4) AS "1", +-- BIT_AND(i8) AS "1", +-- BIT_AND(i) AS "?", +-- BIT_AND(x) AS "0", +-- BIT_AND(y) AS "0100", +-- +-- BIT_OR(i2) AS "7", +-- BIT_OR(i4) AS "7", +-- BIT_OR(i8) AS "7", +-- BIT_OR(i) AS "?", +-- BIT_OR(x) AS "7", +-- BIT_OR(y) AS "1101" +-- FROM bitwise_test; -- -- test boolean aggregates -- -- first test all possible transition and final states -SELECT - -- boolean and transitions - -- null because strict - booland_statefunc(NULL, NULL) IS NULL AS "t", - booland_statefunc(TRUE, NULL) IS NULL AS "t", - booland_statefunc(FALSE, NULL) IS NULL AS "t", - booland_statefunc(NULL, TRUE) IS NULL AS "t", - booland_statefunc(NULL, FALSE) IS NULL AS "t", - -- and actual computations - booland_statefunc(TRUE, TRUE) AS "t", - NOT booland_statefunc(TRUE, FALSE) AS "t", - NOT booland_statefunc(FALSE, TRUE) AS "t", - NOT booland_statefunc(FALSE, FALSE) AS "t"; - -SELECT - -- boolean or transitions - -- null because strict - boolor_statefunc(NULL, NULL) IS NULL AS "t", - boolor_statefunc(TRUE, NULL) IS NULL AS "t", - boolor_statefunc(FALSE, NULL) IS NULL AS "t", - boolor_statefunc(NULL, TRUE) IS NULL AS "t", - boolor_statefunc(NULL, FALSE) IS NULL AS "t", - -- actual computations - boolor_statefunc(TRUE, TRUE) AS "t", - boolor_statefunc(TRUE, FALSE) AS "t", - boolor_statefunc(FALSE, TRUE) AS "t", - NOT boolor_statefunc(FALSE, FALSE) AS "t"; - -CREATE TEMPORARY TABLE bool_test( - b1 BOOL, - b2 BOOL, - b3 BOOL, - b4 BOOL); +-- SELECT Review comment: Do you mean: ```sql select NULL and NULL is null AS "t" ``` ? ---------------------------------------------------------------- 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
