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 <p...@kiril.me> 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 <mm...@apache.org> 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
> > mm...@apache.org
> >
> >
> > Le ven. 8 juin 2018 à 07:54, Kiril Menshikov <p...@kiril.me> 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
> >>
> >>
>
>
>
>

Reply via email to