[GitHub] [pinot] walterddr edited a comment on pull request #7678: Fixing DISTINCT with AS function

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

walterddr edited a comment on pull request #7678:
URL: https://github.com/apache/pinot/pull/7678#issuecomment-958456613


   i think the fundamental problem here is that `distinct` is modeled as a function. where distinct is actually a selectNode attribute in calcite see: https://github.com/apache/calcite/blob/82dd78a14f6aef2eeec2f9c94978d04b4acc5359/core/src/main/java/org/apache/calcite/sql/SqlSelect.java#L140-L142.
   
   For example as the query @Jackie-Jiang mentioned is not a valid SQL. this is because the following:
   ```
   select CAST(runs AS string) as a, CAST(num AS int) as b from baseballStats GROUP BY 1, 2
   ```
   actually should be modified as:
   ```
   SELECT DISTINCT -- notice that DISTINCT is a modifier of SELECT, not the 2 projection elements.
     CAST(runs AS string) as a, 
     CAST(num AS int) as b
   FROM
     baseballStats 
   GROUP BY 1, 2
   ```
   see calcite syntactic note:
   ```
   select:
         SELECT [ hintComment ] [ STREAM ] [ ALL | DISTINCT ]
             { * | projectItem [, projectItem ]* }
         FROM tableExpression
         [ WHERE booleanExpression ]
         [ GROUP BY { groupItem [, groupItem ]* } ]
         [ HAVING booleanExpression ]
         [ WINDOW windowName AS windowSpec [, windowName AS windowSpec ]* ]
   ```
   DISTINCT is a modifier of the SELECT, not the underlying projectItems. 


-- 
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-unsubscribe@pinot.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org