I recommend that you make ROWNUM a 0-parameter function. Then write a planner rule to handle it. I strongly recommend that you do not apply the rule during parsing or validation.
Let’s have further conversation on a JIRA case. > On Jun 12, 2018, at 3:46 AM, Kiril Menshikov <[email protected]> wrote: > > 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 >>>> >>>> >> >> >> >>
