Yes, that was hidden field, without validation failed. Should I add the rule? Or the transformation should happen during the SQL parsing?
I see that MYSQL lex create SqlOrderBy class with fetch. I want the SQL parser to recognize limits. Like it works for MYSQL. But from the other hand, this might be not the right task for SQL parser. I will create JIRA and will contribute to it. But need more time to find the right solution. On Fri, Jun 8, 2018 at 12:47 PM, Kiril Menshikov <[email protected]> wrote: > I’m curious how you represented ROWNUM. Did you make it a hidden field in > your table? That > approach has problems, because in, say, a join query, ROWNUM is a property of > the row, not > of either of the source tables. > > I think I would represent it as a zero-argument function (like CURRENT_DATE) > and mark it non-deterministic > so that it cannot be pushed down. > > “WHERE ROWNUM < constant” can be converted to a LIMIT. > > “SELECT ROWNUM” can be converted to “SELECT RANK() OVER ()” or something > similar. > > Please log a JIRA case for this, and we’d be happy to accept it as a > contribution. > > Julian > > > > On Jun 8, 2018, at 6:37 AM, Michael Mior <[email protected]> wrote: > > > > 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 > >> > >> > > > >
