[
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)
