gatorsmile 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_r290818062
##########
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:
You need to add parenthesis.
```
%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`
```
You will still see the difference. That is because of the strict mode.
Based on the doc of postgreSQL
https://www.postgresql.org/docs/9.5/sql-createfunction.html
, for strict mode,
> STRICT indicates that the function always returns null whenever any of its
arguments are null. If this parameter is specified, the function is not
executed when there are null arguments; instead a null result is assumed
automatically.
Our AND does not have a strict mode. It is fine to have a behavior
difference, but you need to update the comment to explain it.
----------------------------------------------------------------
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]
