OSDir


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: Oracle ROWNUM


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