siddharthteotia commented on code in PR #9826:
URL: https://github.com/apache/pinot/pull/9826#discussion_r1027801868
##########
pinot-query-runtime/src/test/resources/queries/WithStatements.json:
##########
@@ -0,0 +1,81 @@
+{
+ "with_statement_tests": {
+ "psql": "7.8.1",
+ "tables": {
+ "tbl1": {
+ "schema": [
+ {"name": "strCol", "type": "STRING"},
+ {"name": "intCol", "type": "INT"}
+ ],
+ "inputs": [
+ ["a", 3],
+ ["b", 2],
+ ["c", 5],
+ ["a", 1]
+ ]
+ },
+ "tbl2": {
+ "schema": [
+ {"name": "strCol1", "type": "STRING"},
+ {"name": "strCol2", "type": "STRING"},
+ {"name": "intCol", "type": "INT"}
+ ],
+ "inputs": [
+ ["a", "foo", 1],
+ ["a", "bar", 2],
+ ["b", "alice", 42],
+ ["b", "bob", 196883]
+ ]
+ }
+ },
+ "queries": [
+ {
+ "description": "single with with group by",
+ "sql": "WITH w AS ( SELECT strCol, SUM(intCol) AS sumRes FROM {tbl1}
GROUP BY strCol ) SELECT strCol, strCol2, sumRes FROM w JOIN {tbl2} ON w.strCol
= {tbl2}.strCol1",
+ "outputs": [
+ ["a", "foo", 4],
+ ["a", "bar", 4],
+ ["b", "alice", 2],
+ ["b", "bob", 2]
+ ]
+ },
+ {
+ "description": "multi with table",
+ "sql": "WITH agg1 AS ( SELECT strCol, sum(intCol) AS sumVal FROM
{tbl1} GROUP BY strCol), agg2 AS (SELECT strCol1, avg(intCol) AS avgVal FROM
{tbl2} GROUP BY strCol1) SELECT strCol, sumVal - avgVal FROM agg1, agg2 WHERE
agg1.strCol = agg2.strCol1",
+ "outputs": [
+ ["a", 3],
+ ["b", -98460]
+ ]
+ },
+ {
+ "description": "with collapse into single query",
+ "sql": "WITH can_collapse AS ( SELECT strCol, intCol FROM {tbl1} )
SELECT * FROM can_collapse WHERE intCol > 2",
+ "outputs": [
+ ["a", 3],
+ ["c", 5]
+ ]
+ },
+ {
+ "description": "with statement table being reused multiple times",
+ "sql": "WITH reused AS ( SELECT strCol, intCol FROM {tbl1} ) SELECT
a.strCol, a.intCol, b.intCol FROM reused AS a JOIN reused AS b ON a.strCol =
b.strCol WHERE b.intCol > ( SELECT AVG(intCol) FROM reused )",
Review Comment:
Shall we add test for RECURSIVE CTE as well ?
--
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.
To unsubscribe, e-mail: commits-unsubscr...@pinot.apache.org
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@pinot.apache.org
For additional commands, e-mail: commits-h...@pinot.apache.org
