select sum(column) from table is not supported

[rupal-bq]

Query:

select sum(`dayOfWeek`) as `C1` from `kibana_sample_data_flights`

Response:

{
  "error": {
    "reason": "Invalid SQL query",
    "details": "Function [SUM] cannot work with [STRING]. Usage: SUM(NUMBER T) -> T",
    "type": "SemanticAnalysisException"
  },
  "status": 400
}

where the data type of dayOfWeek is integer

[rupal-bq]

The same issue occurs for columns with data types byte, double, float, half_float, integer, long, scaled_float, short while creating a graph using direct query mode in Power BI Desktop

[dai-chen]

After investigation, I found that the root cause is the definition for string literal and identifier is ambiguous in old grammar. In particular, an identifier (fullColumnName rule) could be a string literal (STRING_LITERAL). For function argument, it tries to match string literal first and then identifier. This caused dayOfWeek matched to string literal rather than an identifier. At backend, only backticks around an identifier will be removed. That's why the error above saying dayOfWeek is a string.

sql/legacy/src/main/antlr/OpenDistroSqlParser.g4

Line 368 in 35e37a3

: constant | fullColumnName | functionCall | expression

[dai-chen]

So there are two options to fix this issue:

1. [Quick fix] Adjust the order in functionArg and functionArgs rule to prioritize identifier matching: fullColumnName | constant | expression
2. [Better fix] Refactor string literal and identifier rule as what we do in new query engine: a string literal can be enclosed by single quotes only. An identifier is not a string literal but an ID or ID enclosed by either double quotes or backticks. Reference: master...dai-chen:fix-quoted-function-arg-issue

Since we have been working on a new engine, the quick fix is acceptable and avoid major impact on old parser. Need more test to decide.