somandal commented on code in PR #10635:
URL: https://github.com/apache/pinot/pull/10635#discussion_r1174694860
##########
pinot-query-runtime/src/test/resources/queries/WindowFunctions.json:
##########
@@ -51,6 +51,50 @@
[768]
]
},
+ {
+ "description": "Single empty OVER() count(*) without select col",
+ "sql": "SELECT COUNT(*) OVER() FROM {tbl}",
Review Comment:
yes this query works in PostgreSQL:
```
dvdrental=# select ROW_NUMBER() OVER() from payment;
row_number
------------
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
dvdrental=# select COUNT(*) OVER() from payment;
count
-------
14596
14596
14596
14596
14596
14596
14596
14596
14596
14596
14596
14596
14596
14596
14596
14596
14596
14596
14596
14596
14596
```
##########
pinot-query-runtime/src/test/resources/queries/WindowFunctions.json:
##########
@@ -51,6 +51,50 @@
[768]
]
},
+ {
+ "description": "Single empty OVER() count(*) without select col",
+ "sql": "SELECT COUNT(*) OVER() FROM {tbl}",
+ "outputs": [
+ [16],
+ [16],
+ [16],
+ [16],
+ [16],
+ [16],
+ [16],
+ [16],
+ [16],
+ [16],
+ [16],
+ [16],
+ [16],
+ [16],
+ [16],
+ [16]
+ ]
+ },
+ {
+ "description": "Single empty OVER() count(*) without select col",
+ "sql": "SELECT 42, COUNT(*) OVER() FROM {tbl}",
Review Comment:
Yes this works in PostgreSQL:
```
dvdrental=# select 42, COUNT(*) OVER() from payment;
?column? | count
----------+-------
42 | 14596
42 | 14596
42 | 14596
42 | 14596
42 | 14596
42 | 14596
42 | 14596
42 | 14596
42 | 14596
42 | 14596
42 | 14596
42 | 14596
42 | 14596
42 | 14596
42 | 14596
42 | 14596
42 | 14596
42 | 14596
42 | 14596
42 | 14596
42 | 14596
```
There are many types of custom window frames that works in PostgreSQL but
isn't allowed by Apache Calcite. I'll work on a document to list out the ones I
know about. I assume that if we want to move towards PostgreSQL compliance, we
want to allow these kinds of queries, right?
##########
pinot-query-planner/src/test/resources/queries/WindowFunctionPlans.json:
##########
@@ -14,6 +14,33 @@
"\n"
]
},
+ {
+ "description": "single empty OVER() count(*) only",
+ "sql": "EXPLAIN PLAN FOR SELECT COUNT(*) OVER() FROM a",
+ "output": [
+ "Execution Plan",
+ "\nLogicalProject(w0$o0=[$1])",
+ "\n LogicalWindow(window#0=[window(aggs [COUNT()])])",
+ "\n LogicalExchange(distribution=[hash])",
+ "\n LogicalProject(winLiteral=[0])",
+ "\n LogicalTableScan(table=[[a]])",
+ "\n"
+ ]
+ },
+ {
+ "description": "single empty OVER() literal, count(*) only",
+ "sql": "EXPLAIN PLAN FOR SELECT 42, COUNT(*) OVER() FROM a",
Review Comment:
This works in PostgreSQL:
```
dvdrental=# select amount, ROW_NUMBER() OVER() from payment;
amount | row_number
--------+------------
7.99 | 1
1.99 | 2
7.99 | 3
2.99 | 4
7.99 | 5
5.99 | 6
5.99 | 7
5.99 | 8
2.99 | 9
4.99 | 10
6.99 | 11
0.99 | 12
0.99 | 13
6.99 | 14
8.99 | 15
0.99 | 16
3.99 | 17
4.99 | 18
0.99 | 19
0.99 | 20
4.99 | 21
```
It should also work today for the support added so far in Pinot because
Calcite will include the `columnName` column in the project list correctly.
Example plans:
```
Query: SELECT a.col1, COUNT(*) OVER() FROM a
Explain plan:
Execution Plan
LogicalWindow(window#0=[window(aggs [COUNT()])])
LogicalExchange(distribution=[hash])
LogicalProject(col1=[$0])
LogicalTableScan(table=[[a]])
Query: SELECT COUNT(a.col1) OVER() FROM a
Explain plan:
Execution Plan
LogicalProject($0=[$1])
LogicalWindow(window#0=[window(aggs [COUNT($0)])])
LogicalExchange(distribution=[hash])
LogicalProject(col1=[$0])
LogicalTableScan(table=[[a]])
```
--
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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]