[
https://issues.apache.org/jira/browse/CALCITE-5225?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17574150#comment-17574150
]
杨晓晨 commented on CALCITE-5225:
------------------------------
After I removed the to_char field in SQL, I found that the SYSDATE field could
not be recognized. How to solve it?
SQL：SELECT a.ID, to_char( SYSDATE, 'yyyy/mm/dd'), b.AGE FROM HDC_ODS.DEMO a
LEFT JOIN HDC_ODS.DEMO1 b ON a.ID = b.ID
{code:java}
Caused by: org.apache.calcite.runtime.CalciteContextException: From line 1,
column 23 to line 1, column 29: Column 'SYSDATE' not found in any table at
sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at
sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at
sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at
org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:505) at
org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:932) at
org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:917) at
org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:5266)
at
org.apache.calcite.sql.validate.DelegatingScope.fullyQualify(DelegatingScope.java:273)
at
org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visit(SqlValidatorImpl.java:6415)
at
org.apache.calcite.sql.validate.SqlValidatorImpl$SelectExpander.visit(SqlValidatorImpl.java:6580)
at
org.apache.calcite.sql.validate.SqlValidatorImpl$SelectExpander.visit(SqlValidatorImpl.java:6566)
at org.apache.calcite.sql.SqlIdentifier.accept(SqlIdentifier.java:324) at
org.apache.calcite.sql.util.SqlShuttle$CallCopyingArgHandler.visitChild(SqlShuttle.java:134)
at
org.apache.calcite.sql.util.SqlShuttle$CallCopyingArgHandler.visitChild(SqlShuttle.java:101)
at org.apache.calcite.sql.SqlOperator.acceptCall(SqlOperator.java:954) at
org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visitScoped(SqlValidatorImpl.java:6435)
at
org.apache.calcite.sql.validate.SqlScopedShuttle.visit(SqlScopedShuttle.java:54)
at
org.apache.calcite.sql.validate.SqlScopedShuttle.visit(SqlScopedShuttle.java:37)
at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:161) at
org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.go(SqlValidatorImpl.java:6404)
at
org.apache.calcite.sql.validate.SqlValidatorImpl.expandSelectExpr(SqlValidatorImpl.java:5993)
at
org.apache.calcite.sql.validate.SqlValidatorImpl.expandSelectItem(SqlValidatorImpl.java:434)
at
org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelectList(SqlValidatorImpl.java:4409)
at
org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3652)
at
org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:64)
at
org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:89)
at
org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:1100)
at
org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:1071)
at org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:247) at
org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:1046)
at
org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:752)
at org.apache.calcite.prepare.PlannerImpl.validate(PlannerImpl.java:224)
... 2 moreCaused by: org.apache.calcite.sql.validate.SqlValidatorException:
Column 'SYSDATE' not found in any table at
sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at
sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at
sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at
org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:505) at
org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:599) ... 30
more {code}
> No match found for function signature TO_CHAR(<CHARACTER>)
> ----------------------------------------------------------
>
> Key: CALCITE-5225
> URL: https://issues.apache.org/jira/browse/CALCITE-5225
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.30.0
> Environment: {code:java}
> public class Example1 {
> public static void main(String[] args) throws Exception {
> String sql = "SELECT a.ID, to_char( a.NAME ), to_char( SYSDATE,
> 'yyyy/mm/dd'), b.AGE FROM HDC_ODS.DEMO a LEFT JOIN HDC_ODS.DEMO1 b ON a.ID =
> b.ID";
> runProjectQueryWithLex(Lex.ORACLE, sql);
> }
> private static void runProjectQueryWithLex(Lex lex, String sql)
> throws SqlParseException, ValidationException,
> RelConversionException {
> SqlParser.Config javaLex =
> SqlParser.configBuilder().setLex(lex).build();
> Planner planner = getPlanner(null, javaLex,
> Programs.ofRules(Programs.RULE_SET));
> SqlNode parse = planner.parse(sql);
> SqlNode validate = planner.validate(parse);
> System.out.println(validate);
> }
> private static Planner getPlanner(List<RelTraitDef> traitDefs,
> SqlParser.Config parserConfig,
> Program... programs) {
> CalciteSchema calciteSchema = CalciteSchema.createRootSchema(true,
> true);
> SchemaPlus rootSchema = calciteSchema.plus();
> //final SchemaPlus rootSchema = Frameworks.createRootSchema(true);
> DataSource dataSource =
> JdbcSchema.dataSource("jdbc:oracle:thin:@//XXX:1521/ORCL?fun=oracle",
> "oracle.jdbc.driver.OracleDriver", "xxx", "xxxx");
> final JdbcCatalogSchema schema =
> JdbcCatalogSchema.create(null, "", dataSource, "PUBLIC");
> final CalciteSchema rootSchema0 =
> CalciteSchema.createRootSchema(false, false, "", schema);
> final val schemaMap = rootSchema0.getSubSchemaMap();
> schemaMap.forEach((key, value) -> {
> rootSchema.add(key, value.schema);
> });
> SqlOperatorTable opTab =
> SqlLibraryOperatorTableFactory.INSTANCE.getOperatorTable(
>
> EnumSet.of(SqlLibrary.ORACLE,SqlLibrary.STANDARD,SqlLibrary.SPATIAL));
> final FrameworkConfig config = Frameworks.newConfigBuilder()
> .parserConfig(parserConfig)
> .defaultSchema(rootSchema)
> .traitDefs(traitDefs)
> .programs(programs)
> .operatorTable(opTab)
> .build();
> return Frameworks.getPlanner(config);
> }
> }
> {code}
>
> Oracle Table
> DEMO:
> !image-2022-08-02-17-07-11-242.png!
> DEMO1:
> !image-2022-08-02-17-08-02-305.png!
>
>
>
>
> Reporter: 杨晓晨
> Priority: Major
> Attachments: image-2022-08-02-17-07-11-242.png,
> image-2022-08-02-17-08-02-305.png
>
> Original Estimate: 1h
> Remaining Estimate: 1h
>
> I want to implement a function that can parse and verify Oracle SQL.
> But there are some problems in verifying the Oracle function, it seems that
> some functions are not supported, but it can run normally in Oracle! How
> should I solve it? Modify which parameters of Calcite, it is best to support
> most of the functions of Oracle!!
> Many thanks！
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
