zzwqqq created CALCITE-7563:
-------------------------------
Summary: 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
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)
