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 <p...@kiril.me> 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 <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