gatorsmile commented on a change in pull request #24743: [WIP][SPARK-27883][SQL] Port AGGREGATES.sql [Part 2] URL: https://github.com/apache/spark/pull/24743#discussion_r289277282
########## File path: sql/core/src/test/resources/sql-tests/inputs/pgSQL/aggregates_part2.sql ########## @@ -0,0 +1,217 @@ +-- +-- Portions Copyright (c) 1996-2019, PostgreSQL Global Development Group +-- +-- +-- AGGREGATES [Part 2] +-- https://github.com/postgres/postgres/blob/REL_12_BETA1/src/test/regress/sql/aggregates.sql#L145-L350 + +-- Test handling of Params within aggregate arguments in hashed aggregation. +-- Per bug report from Jeevan Chalke. +-- [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) +-- ); + +-- 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; + +-- +-- 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"; + +-- [SPARK-27880] Implement boolean aggregates(BOOL_AND, BOOL_OR and EVERY) +-- CREATE TEMPORARY TABLE bool_test( +-- b1 BOOL, +-- b2 BOOL, +-- b3 BOOL, +-- b4 BOOL); + +-- empty case +-- SELECT +-- BOOL_AND(b1) AS "n", +-- BOOL_OR(b3) AS "n" +-- FROM bool_test; + +-- COPY bool_test FROM STDIN NULL 'null'; +-- TRUE null FALSE null +-- FALSE TRUE null null +-- null TRUE FALSE null +-- \. + +-- SELECT +-- BOOL_AND(b1) AS "f", +-- BOOL_AND(b2) AS "t", +-- BOOL_AND(b3) AS "f", +-- BOOL_AND(b4) AS "n", +-- BOOL_AND(NOT b2) AS "f", +-- BOOL_AND(NOT b3) AS "t" +-- FROM bool_test; + +-- SELECT +-- EVERY(b1) AS "f", +-- EVERY(b2) AS "t", +-- EVERY(b3) AS "f", +-- EVERY(b4) AS "n", +-- EVERY(NOT b2) AS "f", +-- EVERY(NOT b3) AS "t" +-- FROM bool_test; + +-- SELECT +-- BOOL_OR(b1) AS "t", +-- BOOL_OR(b2) AS "t", +-- BOOL_OR(b3) AS "f", +-- BOOL_OR(b4) AS "n", +-- BOOL_OR(NOT b2) AS "f", +-- BOOL_OR(NOT b3) AS "t" +-- FROM bool_test; + +-- +-- Test cases that should be optimized into indexscans instead of +-- the generic aggregate implementation. +-- + +-- Basic cases +explain Review comment: These plans are not useful for us. Can we skip them? ---------------------------------------------------------------- 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: [email protected] With regards, Apache Git Services --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
