Skip to content

ParseException due to ARRAY_AGG() and SAFE_CAST() #1622

Closed
Closed
ParseException due to ARRAY_AGG() and SAFE_CAST()#1622

Description

@Tianqi1006
Tianqi1006
opened on Aug 24, 2022

Hi,

We recently found this ParseException due to the use of ARRAY_AGG() and SAFE_CAST() from the built-in functions provided by Google BigQuery. However, when I tried to validate the SQL syntax, I got this error:

ParseException: Cannot parse statement: Encountered unexpected token: "(" "(" at line 1, column 17.

Was expecting one of:

"&"
"::"
";"
"<<"
">>"
"ACTION"
"ACTIVE"
"ALGORITHM"
"ARCHIVE"
"ARRAY"
"AS"
"AT"
"BYTE"
"CASCADE"
"CASE"
"CAST"
"CHANGE"
"CHAR"
"CHARACTER"
"CHECKPOINT"
"COLLATE"
"COLUMN"
"COLUMNS"
"COMMENT"
"COMMIT"
"CONNECT"
"COSTS"
"CYCLE"
"DBA_RECYCLEBIN"
"DEFAULT"
"DESC"
"DESCRIBE"
"DISABLE"
"DISCONNECT"
"DIV"
"DO"
"DUMP"
"DUPLICATE"
"EMIT"
"ENABLE"
"END"
"EXCLUDE"
"EXTRACT"
"FALSE"
"FILTER"
"FIRST"
"FLUSH"
"FN"
"FOLLOWING"
"FORMAT"
"FULLTEXT"
"GROUP"
"HAVING"
"HISTORY"
"INDEX"
"INSERT"
"INTERVAL"
"INTO"
"ISNULL"
"JSON"
"KEY"
"LAST"
"LEADING"
"LINK"
"LOCAL"
"LOG"
"MATERIALIZED"
"NO"
"NOLOCK"
"NULLS"
"OF"
"OPEN"
"OVER"
"PARALLEL"
"PARTITION"
"PATH"
"PERCENT"
"PRECISION"
"PRIMARY"
"PRIOR"
"QUERY"
"QUIESCE"
"RANGE"
"READ"
"RECYCLEBIN"
"REGISTER"
"REPLACE"
"RESTRICTED"
"RESUME"
"ROW"
"ROWS"
"SCHEMA"
"SEPARATOR"
"SEQUENCE"
"SESSION"
"SHUTDOWN"
"SIBLINGS"
"SIGNED"
"SIZE"
"SKIP"
"START"
"SUSPEND"
"SWITCH"
"SYNONYM"
"SYSTEM"
"TABLE"
"TABLESPACE"
"TEMP"
"TEMPORARY"
"TIMEOUT"
"TO"
"TOP"
"TRUE"
"TRUNCATE"
"TRY_CAST"
"TYPE"
"UNQIESCE"
"UNSIGNED"
"USER"
"VALIDATE"
"VALUE"
"VALUES"
"VIEW"
"WINDOW"
"XML"
"ZONE"
"["
"^"
"|"
<EOF>
<K_DATETIMELITERAL>
<K_DATE_LITERAL>
<K_NEXTVAL>
<K_STRING_FUNCTION_NAME>
<S_CHAR_LITERAL>
<S_IDENTIFIER>
<S_QUOTED_IDENTIFIER>

The SQL statement looks like this:
select array_agg(safe_cast(n as Float64)) as temp from UNNEST(SPLIT("0,0,0,0,0",",")) as n

We are wondering is there a way to allow ARRAY_AGG() and SAFE_CAST() without throwing exceptions?

Thank you very much in advance,
Tianqi

Activity

  1. Tianqi1006
    changed the title [-]ParseException due to ARRAY_AGG() and SAFE_CAST[/-] [+]ParseException due to ARRAY_AGG() and SAFE_CAST()[/+] on Aug 24, 2022

  2. manticore-projects commented on Aug 24, 2022

    @manticore-projects
    manticore-projects
    on Aug 24, 2022
    Contributor

    Greetings,

    The problem is about SAFE_CAST(x AS y), which is not a regular function but a special expression.
    We will need to extend the CAST() expression accordingly.

  3. Tianqi1006 commented on Aug 25, 2022

    @Tianqi1006
    Tianqi1006
    on Aug 25, 2022
    Author

    Greetings,

    Thanks a lot for quickly identifying the root cause of the problem. Just would like to follow up with you and see when approximately will this change be applied / released? Any details would be greatly appreciated.

    Best regards,
    Tianqi

  4. dequn commented on Sep 19, 2022

    @dequn
    dequn
    on Sep 19, 2022
    Contributor

    Hi @manticore-projects , not only SAFE_CAST, but also ARRAY_AGG can not be fully correctly parsed.
    failed cases:

    1. ARRAY_AGG(x LMIT 10)
    2. ARRAY_AGG( x IGNORE NULLS ORDER BY x)
    3. ARRAY_AGG( DISTINCT x)
      Google ARRAY_AGG references: https://cloud.google.com/bigquery/docs/reference/standard-sql/aggregate_functions#array_agg
      JsqlParser Version: 4.5

  5. manticore-projects commented on Sep 19, 2022

    @manticore-projects
    manticore-projects
    on Sep 19, 2022
    Contributor
    1. `ARRAY_AGG(x LMIT 10)`
2. `ARRAY_AGG( x IGNORE NULLS ORDER BY x)`
3. `ARRAY_AGG( DISTINCT x)`
   Google ARRAY_AGG references: https://cloud.google.com/bigquery/docs/reference/standard-sql/aggregate_functions#array_agg

    Certainly, because all of those are mot standard compliant, but Google BigQuery specific.
    You will need to sponsor or provide an implementation for such special function parameters.

  6. dequn
    added a commit that references this issue on Sep 19, 2022

    Support BigQuery SAFE_CAST (JSQLParser#1622)

    f480602

  7. dequn commented on Sep 19, 2022

    @dequn
    dequn
    on Sep 19, 2022
    Contributor

    @manticore-projects Thanks, I add SAFE_CAST support and create a PR.

  8. wumpz
    added a commit that references this issue on Sep 20, 2022

    Support BigQuery SAFE_CAST (#1622) (#1634)

    d9985ae

  9. wumpz commented on Sep 20, 2022

    @wumpz
    wumpz
    on Sep 20, 2022
    Member

    Do I get this right, that this issue is not yet complete by the PR?

  10. dequn commented on Sep 21, 2022

    @dequn
    dequn
    on Sep 21, 2022
    Contributor

    @wumpz Yes, It only solves SAFE_CAST related problem, ARRAY_AGG is not included in this PR.

  11. manticore-projects commented on Sep 2, 2023

    @manticore-projects
    manticore-projects
    on Sep 2, 2023
    Contributor

    Continued as Feature request under #1856

  12. manticore-projects
    closed this as completedon Sep 2, 2023
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions