Unfortunately Calcite doesn't currently identify the fact that ROWNUM refers to the row number and not just some field in the table. One approach would be to write a rule which matches filters on ROWNUM and converts them to sorts (with no ordering) and the proper value of fetch and offset. -- Michael Mior [email protected]
Le ven. 8 juin 2018 à 07:54, Kiril Menshikov <[email protected]> a écrit : > Hi, > > I am trying to convert Oracle SQL to Redshift with some optimizations. > Everything works good except ROWNUMs. Parser accept it as a filed and > convert it to the same statement. But I want to get limit statement. Does > anybody had similar problem? Or can point me how to transform statement or > change rel algebra? > > *Example:* > Oracle: SELECT NAME FROM USERS WHERE ROWNUM <= 5; > Redshift: SELECT NAME FROM USERS LIMIT 5; > > *Code sample:* > String sql = “select name form users where rownum <= 5”; > SqlParser.Config config = SqlParser.configBuilder() > .setLex(Lex.ORACLE) > .setConformance(SqlConformanceEnum.ORACLE_12) > .build(); > DataSource dataSource = JdbcSchema.dataSource("jdbc:oracle:thin:….", > "oracle.jdbc.OracleDriver", “user", “pass”); > SchemaPlus rootSchema = Frameworks.createRootSchema(false); > > JdbcSchema schema = JdbcSchema.create(rootSchema, “o", dataSource, null, > “my") ; > SchemaPlus instrumentation = rootSchema.add(“my", schema); > final FrameworkConfig config = Frameworks.newConfigBuilder() > .parserConfig(parserConfig) > .defaultSchema(instrumentation) > .traitDefs(null) > .costFactory(null) > .context(Contexts.EMPTY_CONTEXT) > .ruleSets(RuleSets.ofList()) > .programs(programs) > .typeSystem(RelDataTypeSystem.DEFAULT) > .build(); > Planner planner = Frameworks.getPlanner(config); > SqlNode sqlNode = planner.parse(sql); > SqlNode validatedNode = planner.validate(sqlNode); > SqlDialect sqlDialect = SqlDialect.DatabaseProduct.REDSHIFT.getDialect(); > String convertedSql = parse.toSqlString(sqlDialect).getSql(); // will do > the same SELECT NAME as name FROM USERS WHERE ROWNUM <= 5; > > Thanks, > -Kiril > >
