[GitHub] [spark] AngersZhuuuu commented on a change in pull request #29087: [SPARK-28227][SQL] Support projection, aggregate/window functions, and lateral view in the TRANSFORM clause

[GitBox <gi...@apache.org>]

AngersZhuuuu commented on a change in pull request #29087:
URL: https://github.com/apache/spark/pull/29087#discussion_r612276650



##########
File path: sql/core/src/test/resources/sql-tests/inputs/transform.sql
##########
@@ -184,6 +190,132 @@ SELECT a, b, decode(c, 'UTF-8'), d, e, f, g, h, i, j, k, l FROM (
   FROM t
 ) tmp;
 
+SELECT TRANSFORM(b, a, CAST(c AS STRING))
+  USING 'cat' AS (a, b, c)
+FROM script_trans
+WHERE a <= 4;
+
+SELECT TRANSFORM(1, 2, 3)
+  USING 'cat' AS (a, b, c)
+FROM script_trans
+WHERE a <= 4;
+
+SELECT TRANSFORM(1, 2)
+  USING 'cat' AS (a INT, b INT)
+FROM script_trans
+LIMIT 1;
+
+SELECT TRANSFORM(
+  b AS d5, a,
+  CASE
+    WHEN c > 100 THEN 1
+    WHEN c < 100 THEN 2
+  ELSE 3 END)
+  USING 'cat' AS (a, b,  c)
+FROM script_trans
+WHERE a <= 4;
+
+SELECT TRANSFORM(b, a, c + 1)
+  USING 'cat' AS (a, b, c)
+FROM script_trans
+WHERE a <= 4;
+
+SELECT TRANSFORM(*)
+  USING 'cat' AS (a, b, c)
+FROM script_trans
+WHERE a <= 4;
+
+SELECT TRANSFORM(b AS d, MAX(a) as max_a, CAST(SUM(c) AS STRING))
+  USING 'cat' AS (a, b, c)
+FROM script_trans
+WHERE a <= 4
+GROUP BY b;
+
+SELECT TRANSFORM(b AS d, MAX(a) FILTER (WHERE a > 3) AS max_a, CAST(SUM(c) AS STRING))
+  USING 'cat' AS (a,b,c)
+FROM script_trans
+WHERE a <= 4
+GROUP BY b;
+
+SELECT TRANSFORM(b, MAX(a) as max_a, CAST(sum(c) AS STRING))
+  USING 'cat' AS (a, b, c)
+FROM script_trans
+WHERE a <= 2
+GROUP BY b;
+
+SELECT TRANSFORM(b, MAX(a) as max_a, CAST(SUM(c) AS STRING))
+  USING 'cat' AS (a, b, c)
+FROM script_trans
+WHERE a <= 4
+GROUP BY b
+HAVING max_a > 0;
+
+SELECT TRANSFORM(b, MAX(a) as max_a, CAST(SUM(c) AS STRING))
+  USING 'cat' AS (a, b, c)
+FROM script_trans
+WHERE a <= 4
+GROUP BY b
+HAVING max(a) > 1;
+
+SELECT TRANSFORM(b, MAX(a) OVER w as max_a, CAST(SUM(c) OVER w AS STRING))
+  USING 'cat' AS (a, b, c)
+FROM script_trans
+WHERE a <= 4
+WINDOW w AS (PARTITION BY b ORDER BY a);
+
+SELECT TRANSFORM(b, MAX(a) as max_a, CAST(SUM(c) AS STRING), myCol, myCol2)
+  USING 'cat' AS (a, b, c, d, e)
+FROM script_trans
+LATERAL VIEW explode(array(array(1,2,3))) myTable AS myCol
+LATERAL VIEW explode(myTable.myCol) myTable2 AS myCol2
+WHERE a <= 4
+GROUP BY b, myCol, myCol2
+HAVING max(a) > 1;
+
+FROM(
+  FROM script_trans
+  SELECT TRANSFORM(a, b)
+    USING 'cat' AS (`a` INT, b STRING)
+) t
+SELECT a + 1;
+
+FROM(
+  SELECT TRANSFORM(a, SUM(b) b)
+    USING 'cat' AS (`a` INT, b STRING)
+  FROM script_trans
+  GROUP BY a
+) t
+SELECT (b + 1) AS result
+ORDER BY result;
+
+MAP k / 10 USING 'cat' AS (one) FROM (SELECT 10 AS k);
+
+FROM (SELECT 1 AS key, 100 AS value) src
+MAP src.*, src.key, CAST(src.key / 10 AS INT), CAST(src.key % 10 AS INT), src.value
+  USING 'cat' AS (k, v, tkey, ten, one, tvalue);
+
+SELECT TRANSFORM(1)
+  USING 'cat' AS (a)
+FROM script_trans
+HAVING true;
+
+SET spark.sql.legacy.parser.havingWithoutGroupByAsWhere=true;
+
+SELECT TRANSFORM(1)
+  USING 'cat' AS (a)
+FROM script_trans
+HAVING true;
+
+SET spark.sql.legacy.parser.havingWithoutGroupByAsWhere=false;
+
+SET spark.sql.parser.quotedRegexColumnNames=true;
+
+SELECT TRANSFORM(`(a|b)?+.+`)
+ USING 'cat' AS (c)
+FROM script_trans;
+
+SET spark.sql.parser.quotedRegexColumnNames=false;
+

Review comment:
       Will raise a follow up soon




-- 
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:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org