[jira] [Created] (CALCITE-5225) No match found for function signature TO_CHAR()

["杨晓晨 (Jira)" <ji...@apache.org>]

杨晓晨 created CALCITE-5225:
----------------------------

             Summary: 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: 杨晓晨
         Attachments: image-2022-08-02-17-07-11-242.png, image-2022-08-02-17-08-02-305.png

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)