You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "ASF GitHub Bot (Jira)" <ji...@apache.org> on 2026/05/28 12:01:00 UTC

[jira] [Updated] (CALCITE-7563) Oracle dialect generates invalid CAST to VARCHAR without precision

     [ https://issues.apache.org/jira/browse/CALCITE-7563?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

ASF GitHub Bot updated CALCITE-7563:
------------------------------------
    Labels: pull-request-available  (was: )

> Oracle dialect generates invalid CAST to VARCHAR without precision
> ------------------------------------------------------------------
>
>                 Key: CALCITE-7563
>                 URL: https://issues.apache.org/jira/browse/CALCITE-7563
>             Project: Calcite
>          Issue Type: Bug
>          Components: core
>            Reporter: zzwqqq
>            Priority: Major
>              Labels: pull-request-available
>
> OracleSqlDialect may generate invalid SQL when casting to VARCHAR without precision.
> Minimal reproduction:
> {code:sql}
> SELECT CAST("store_id" AS VARCHAR)
> FROM "foodmart"."expense_fact"
> {code}
> RelToSqlConverter with OracleSqlDialect currently may generate:
> {code:sql}
> SELECT CAST("store_id" AS VARCHAR)
> FROM "foodmart"."expense_fact"
> {code}
> Oracle rejects this because VARCHAR is currently synonymous with VARCHAR2, and VARCHAR2 requires a size (ORA-00906: missing left parenthesis, https://onecompiler.com/oracle/44qkw7vap).
> SqlFunctions that return VARCHAR without precision, such as SqlStdOperatorTable#REPLACE, can also trigger the issue.
> A possible fix is to update OracleSqlDialect#getCastSpec. If the target type is VARCHAR and precision is not specified, use the Oracle type system's max VARCHAR precision:
> {code:java}
> int precision = getTypeSystem().getMaxPrecision(SqlTypeName.VARCHAR);
> {code}
> Oracle documentation:
> * CAST type_name must be a built-in data type or collection type:
> https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/CAST.html
> * VARCHAR2 syntax is VARCHAR2(size [BYTE | CHAR]), and Oracle documents that "You must specify size for VARCHAR2":
> https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/Data-Types.html
> * VARCHAR is currently synonymous with VARCHAR2:
> https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/Data-Types.html



--
This message was sent by Atlassian Jira
(v8.20.10#820010)