Re: Oracle ROWNUM
Yes, that was hidden field, without validation failed.
Should I add the rule? Or the transformation should happen during the SQL
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 <post@xxxxxxxx> 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.
> > On Jun 8, 2018, at 6:37 AM, Michael Mior <mmior@xxxxxxxxxx> 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
> > mmior@xxxxxxxxxx
> > Le ven. 8 juin 2018 à 07:54, Kiril Menshikov <post@xxxxxxxx> 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