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_r290729720
##########
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:
The behaviour different:
1. Spark SQL:
```sql
SELECT
-- boolean and transitions
-- null because strict
NULL and NULL IS NULL AS `t`,
TRUE and NULL IS NULL AS `t`,
FALSE and NULL IS NULL AS `t`,
NULL and TRUE IS NULL AS `t`,
NULL and FALSE IS NULL AS `t`,
-- and actual computations
TRUE and TRUE AS `t`,
NOT TRUE and FALSE AS `t`,
NOT FALSE and TRUE AS `t`,
NOT FALSE and FALSE AS `t`
-- !query 1 schema
struct<t:boolean,t:boolean,t:boolean,t:boolean,t:boolean,t:boolean,t:boolean,t:boolean,t:boolean>
-- !query 1 output
NULL true false false false true false true false
```
PostgreSQL:
```sql
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";
t | t | t | t | t | t | t | t | t
---+---+---+---+---+---+---+---+---
t | t | t | t | t | t | t | t | t
(1 row)
```
----------------------------------------------------------------
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]
