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
>>
>>
