[
https://issues.apache.org/jira/browse/CALCITE-5225?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
杨晓晨 updated CALCITE-5225:
-------------------------
Description:
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！
{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!
was:
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！
> 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！
> {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!
>
>
>
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
