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]

Reply via email to