You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Kiril Menshikov <po...@kiril.me> on 2018/06/08 10:47:42 UTC
Oracle ROWNUM
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
Re: Oracle ROWNUM
Posted by Julian Hyde <jh...@apache.org>.
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
> mmior@apache.org
>
>
> Le ven. 8 juin 2018 à 07:54, Kiril Menshikov <po...@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
>>
>>
Re: Oracle ROWNUM
Posted by Michael Mior <mm...@apache.org>.
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@apache.org
Le ven. 8 juin 2018 à 07:54, Kiril Menshikov <po...@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
>
>
Re: Oracle ROWNUM
Posted by Julian Hyde <jh...@apache.org>.
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 <po...@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 <po...@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
>>> mmior@apache.org
>>>
>>>
>>> Le ven. 8 juin 2018 à 07:54, Kiril Menshikov <po...@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
>>>>
>>>>
>>
>>
>>
>>
Re: Oracle ROWNUM
Posted by Kiril Menshikov <po...@kiril.me>.
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 <po...@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
> > mmior@apache.org
> >
> >
> > Le ven. 8 juin 2018 à 07:54, Kiril Menshikov <po...@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
> >>
> >>
>
>
>
>