You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Hongze Zhang <no...@126.com> on 2019/03/08 09:56:28 UTC
Re[2]: Calcite-RelNode clarifications
Hi RamKrishna,
As you said:
> So, basically I need to add 2 Hours to CurrentTime.
I think the generated function call "TIMESTAMPADD(HOUR, 2,
CURRENT_TIMESTAMP)" already did that.
If what you want is to compare it's value with a MySQL's Date (with
format xxxx-xx-xx), based on Christopher's suggestion I think you can
write:
```
builder.call(SqlStdOperatorTable.FLOOR, ImmutableList.of(timeAdd,
builder.getRexBuilder().makeFlag(TimeUnitRange.DAY)))
```
Calcite will generate MySQL-dialect SQL like
"DATE_FORMAT(TIMESTAMPADD(HOUR, 2, CURRENT_TIMESTAMP), '%Y-%m-%d')"
which produces String result with Date precision. Or you can simply use
operator CAST:
```
builder.cast(timeAdd, SqlTypeName.DATE))
```
to extract Date part from the variable "timeAdd". Both of the two ways
should give you result that is comparable with coulmn "HIRE_TIME".
Best,
Hongze
------ Original Message ------
From: "Rakesh Nair" <ra...@gmail.com>
To: dev@calcite.apache.org
Sent: 2019/3/8 16:34:47
Subject: Re: Calcite-RelNode clarifications
>Hello Mr. Baynes,
>Thanks for responding.
>1. Luckily I was able to solve it on my own, thanks anyway. Moving on..
>2. Let me explain my scenario in detail, you see we have an application
>that queries multiple dbs like Mysql, Oracle, etc..with some static queries
>which we are maintaining in their respective dialects for all these dbs.
>Now we're trying to maintain a Generic RelNode class for different queries
>and then convert it to respective Db dialect as and when required. For eg:
>If I need to run a *select * from `emp`* query in Mysql. I will create a
>relNode like so,
>*RelNode node = builder.scan("emp").build();* and then convert it to its
>corresponding Mysql query using,
>
>*SqlDialect dialect = SqlDialect.DatabaseProduct.MYSQL.getDialect();*
>*RelToSqlConverter converter = new RelToSqlConverter(dialect);*
>*SqlNode sqlNode = converter.visitChild(0, node).asStatement();*
>*String query = sqlNode.toSqlString(dialect).getSql();*
>If I need to convert the same query to its Oracle counterpart i just need
>to initialise dialect as Oracle dialect. Now for most of the queries, I've
>been able to create its corresponding RelNode. But in some queries I'm
>getting stuck which I've mentioned as below:
>Now I have another MySql query like so,
>*SELECT * FROM `emp` WHERE HIRE_TIME = DATE_ADD( NOW(), INTERVAL 2 HOUR);*
>Now I woud like to create the corresponding RelNode for this query. So far
>this is what I've been able to do:
>
>*builder.scan("emp");*
>*ImmutableList<RexNode> constExps =
>ImmutableList.of(builder.getRexBuilder().makeFlag(TimeUnitRange.HOUR),
>builder.literal(2), builder.call(SqlStdOperatorTable.CURRENT_TIMESTAMP));*
>
>*RelDataType any = builder.getTypeFactory().createSqlType(SqlTypeName.ANY);*
>
>*RexNode timeAdd = builder.getRexBuilder().makeCall(any,
>SqlStdOperatorTable.TIMESTAMP_ADD, constExps);*
>*RelNode node = builder.filter(builder.equals(builder.field("HIRE_TIME"),
>timeAdd)).build();*
>
>And the corresponding MySql is coming out to be like this:
>
>*SELECT * FROM `db`.`emp` WHERE `HIRE_TIME` = TIMESTAMPADD(HOUR, 2,
>CURRENT_TIMESTAMP) *which is clearly not correct.
>
>So, basically I need to add 2 Hours to CurrentTime. Can you explain how to
>accomplish this using RelBuilder(with a RelNode example if possible).
>
>Thanks and Regards,
>RamKrishna.
>
>On Thu, Mar 7, 2019 at 9:34 PM Chris Baynes <ch...@contiamo.com> wrote:
>
>> 1. Could you give an example of what your expected output/query is here?
>> I'm not sure what you're trying to achieve.
>> 2. There are mappings in MysqlSqlDialect.java from Calcite FLOOR to Mysql
>> DATE_FORMAT. So what you want in your RelNode is something like
>> FLOOR(HIRE_DATE TO day)
>>
>> On Tue, Mar 5, 2019 at 6:41 PM Rakesh Nair <ra...@gmail.com> wrote:
>>
>> > Hi,
>> > Sorry to be a bother, but I've been sitting on this for quite a while now
>> > and would really like to clear it. So I've been trying to use the
>> > RelBuilder for building relational expressions.
>> > 1. I'm trying to build the relational expressions for using Trim()
>> > function. This si what I've done so far:
>> > RelNode node =
>> > builder.scan("emp").project(builder.call(SqlStdOperatorTable.TRIM,
>> > builder.getRexBuilder().makeFlag(Flag.BOTH),builder.literal(" "),
>> > builder.field("EMPNAME"))).build();
>> > Query Explain:
>> > LogicalProject($f0=[TRIM(FLAG(BOTH), ' ', $2)])
>> > LogicalTableScan(table=[[hr, emp]])
>> > Corresponding Mysql Query:
>> > SELECT TRIM(BOTH ' ' FROM `EMPNAME`) AS `$f0`
>> > FROM `hr`.`emp`
>> > Could somebody tell me what I'm doing wrong here?
>> > 2. How can I convert the following Mysql Query:
>> > SELECT DATE_FORMAT(HIRE_DATE,'%Y/%m/%d'), EMPNAME FROM `emp` WHERE
>> > DATE_FORMAT(HIRE_DATE,'%Y/%m/%d') = DATE_FORMAT(NOW(),'%Y/%m/%d');
>> > to its corresponding Relational Algebra using RelBuilder? Simply put how
>> > can i convert the date formats in RelBuilder?
>> >
>> > Thanks and Regards,
>> > RamKrishna.
>> >
>>
>>
>> --
>>
>> *Christopher Baynes*
>> Lead Developer
>>
>> *Contiamo – all your data in one place*
>>
>> Stresemannstraße 123 (c/o WeWork) | 10963 Berlin | Germany
>>
>> E-mail: chris@contiamo.com
>>
>> Web: www.contiamo.com
>> <
>> http://t.sidekickopen65.com/e1t/c/5/f18dQhb0S7lC8dDMPbW2n0x6l2B9nMJW7t5XZs4X9YtjW8q-fZW65jv3RW2zhrDH56dLV8f5DKhvM02?t=http%3A%2F%2Fwww.contiamo.com%2F&si=5165279625740288&pi=bff9f6a3-d8a4-4bf6-87d5-a5464041547d
>> >
>>
>> Contiamo GmbH, Sitz der Gesellschaft: Berlin
>> HR Berlin-Charlottenburg, HRB Nr. 156569
>> Geschäftsführer: Lucia Hegenbartova, Michael Franzkowiak
>>
>
Re: Calcite-RelNode clarifications
Posted by Chris Baynes <ch...@contiamo.com>.
For oracle you probably want to generate something like
SELECT * FROM "hr"."emp" WHERE "HIRE_DATE" = TRUNC(CURRENT_TIMESTAMP +
INTERVAL '2' HOUR, 'SECOND')
I believe the `+ INTERVAL '2' HOUR` syntax is supported by calcite. So if
you manage to construct it with the builder it should be pushed down.
If you want to construct the query in one way (with timestampadd) and have
it work for both databases then you'll have to change the OracleSqlDialect
to support this.
In that case please log a jira issue as Hongze suggests.
Chris
On Fri, Mar 8, 2019 at 6:01 PM Hongze Zhang <no...@126.com> wrote:
> If the function TIMESTAMPADD is not supported in Oracle, in general the
> OracleSqlDialect should translate it to some other expressions or calls.
> But it seems that currently the dialect does not provide the
> translation[1].
>
> It would be great if you can log a JIRA case to describe your issue, or
> even contribute a PR to solve the problem.
>
>
> Best,
> Hongze
>
>
> [1]
> https://github.com/apache/calcite/blob/50012d221d6e9a5e42a957ab0723e0f706b25be3/core/src/main/java/org/apache/calcite/sql/dialect/OracleSqlDialect.java#L55
>
> > On Mar 8, 2019, at 7:13 PM, Rakesh Nair <ra...@gmail.com> wrote:
> >
> > TIMESTAMPADD
>
>
--
*Christopher Baynes*
Lead Developer
*Contiamo – all your data in one place*
Stresemannstraße 123 (c/o WeWork) | 10963 Berlin | Germany
E-mail: chris@contiamo.com
Web: www.contiamo.com
<http://t.sidekickopen65.com/e1t/c/5/f18dQhb0S7lC8dDMPbW2n0x6l2B9nMJW7t5XZs4X9YtjW8q-fZW65jv3RW2zhrDH56dLV8f5DKhvM02?t=http%3A%2F%2Fwww.contiamo.com%2F&si=5165279625740288&pi=bff9f6a3-d8a4-4bf6-87d5-a5464041547d>
Contiamo GmbH, Sitz der Gesellschaft: Berlin
HR Berlin-Charlottenburg, HRB Nr. 156569
Geschäftsführer: Lucia Hegenbartova, Michael Franzkowiak
Re: Calcite-RelNode clarifications
Posted by Hongze Zhang <no...@126.com>.
If the function TIMESTAMPADD is not supported in Oracle, in general the OracleSqlDialect should translate it to some other expressions or calls. But it seems that currently the dialect does not provide the translation[1].
It would be great if you can log a JIRA case to describe your issue, or even contribute a PR to solve the problem.
Best,
Hongze
[1] https://github.com/apache/calcite/blob/50012d221d6e9a5e42a957ab0723e0f706b25be3/core/src/main/java/org/apache/calcite/sql/dialect/OracleSqlDialect.java#L55
> On Mar 8, 2019, at 7:13 PM, Rakesh Nair <ra...@gmail.com> wrote:
>
> TIMESTAMPADD
Re: Re[2]: Calcite-RelNode clarifications
Posted by Rakesh Nair <ra...@gmail.com>.
Hello Hongze,
Thanks for responding. Your suggestion worked splendidly. I've got one more
clarification if its not too much trouble. The following RelNode:
*RelDataType any = builder.getTypeFactory().createSqlType(SqlTypeName.ANY);*
*ImmutableList<RexNode> constExps =
ImmutableList.of(builder.getRexBuilder().makeFlag(TimeUnitRange.HOUR),
builder.literal(2), builder.call(SqlStdOperatorTable.CURRENT_TIMESTAMP));*
*RexNode timeAdd = builder.getRexBuilder().makeCall(any,
SqlStdOperatorTable.TIMESTAMP_ADD, constExps);*
*RelNode node =
builder.filter(builder.equals(builder.field("HIRE_TIME"),builder.call(SqlStdOperatorTable.FLOOR,
ImmutableList.of(timeAdd,
builder.getRexBuilder().makeFlag(TimeUnitRange.SECOND))))).build();*
creates the following Mysql Query:
*SELECT * FROM `db`.`emp` WHERE `HIRE_TIME` =
DATE_FORMAT(TIMESTAMPADD(HOUR, 2, CURRENT_TIMESTAMP), '%Y-%m-%d %H:%i:%s')*
But when I convert the dialect from *Mysql* *to Oracle*, it creates the
following Oracle query:
*SELECT * FROM "hr"."emp" WHERE "HIRE_DATE" = TRUNC(TIMESTAMPADD(HOUR, 2,
CURRENT_TIMESTAMP), 'SECOND');*
Clearly, *TIMESTAMPADD is not a valid identifier in Oracle.*
So am I doing something wrong here? Hope to hear from you soon.
Thanks and Regards,
RamKrishna.
On Fri, Mar 8, 2019 at 3:33 PM Hongze Zhang <no...@126.com> wrote:
> Hi RamKrishna,
>
> As you said:
>
> > So, basically I need to add 2 Hours to CurrentTime.
>
> I think the generated function call "TIMESTAMPADD(HOUR, 2,
> CURRENT_TIMESTAMP)" already did that.
> If what you want is to compare it's value with a MySQL's Date (with
> format xxxx-xx-xx), based on Christopher's suggestion I think you can
> write:
>
> ```
> builder.call(SqlStdOperatorTable.FLOOR, ImmutableList.of(timeAdd,
> builder.getRexBuilder().makeFlag(TimeUnitRange.DAY)))
> ```
>
> Calcite will generate MySQL-dialect SQL like
> "DATE_FORMAT(TIMESTAMPADD(HOUR, 2, CURRENT_TIMESTAMP), '%Y-%m-%d')"
> which produces String result with Date precision. Or you can simply use
> operator CAST:
>
> ```
> builder.cast(timeAdd, SqlTypeName.DATE))
> ```
> to extract Date part from the variable "timeAdd". Both of the two ways
> should give you result that is comparable with coulmn "HIRE_TIME".
>
> Best,
> Hongze
>
> ------ Original Message ------
> From: "Rakesh Nair" <ra...@gmail.com>
> To: dev@calcite.apache.org
> Sent: 2019/3/8 16:34:47
> Subject: Re: Calcite-RelNode clarifications
>
> >Hello Mr. Baynes,
> >Thanks for responding.
> >1. Luckily I was able to solve it on my own, thanks anyway. Moving on..
> >2. Let me explain my scenario in detail, you see we have an application
> >that queries multiple dbs like Mysql, Oracle, etc..with some static
> queries
> >which we are maintaining in their respective dialects for all these dbs.
> >Now we're trying to maintain a Generic RelNode class for different queries
> >and then convert it to respective Db dialect as and when required. For eg:
> >If I need to run a *select * from `emp`* query in Mysql. I will create a
> >relNode like so,
> >*RelNode node = builder.scan("emp").build();* and then convert it to its
> >corresponding Mysql query using,
> >
> >*SqlDialect dialect = SqlDialect.DatabaseProduct.MYSQL.getDialect();*
> >*RelToSqlConverter converter = new RelToSqlConverter(dialect);*
> >*SqlNode sqlNode = converter.visitChild(0, node).asStatement();*
> >*String query = sqlNode.toSqlString(dialect).getSql();*
> >If I need to convert the same query to its Oracle counterpart i just need
> >to initialise dialect as Oracle dialect. Now for most of the queries, I've
> >been able to create its corresponding RelNode. But in some queries I'm
> >getting stuck which I've mentioned as below:
> >Now I have another MySql query like so,
> >*SELECT * FROM `emp` WHERE HIRE_TIME = DATE_ADD( NOW(), INTERVAL 2 HOUR);*
> >Now I woud like to create the corresponding RelNode for this query. So far
> >this is what I've been able to do:
> >
> >*builder.scan("emp");*
> >*ImmutableList<RexNode> constExps =
> >ImmutableList.of(builder.getRexBuilder().makeFlag(TimeUnitRange.HOUR),
> >builder.literal(2), builder.call(SqlStdOperatorTable.CURRENT_TIMESTAMP));*
> >
> >*RelDataType any =
> builder.getTypeFactory().createSqlType(SqlTypeName.ANY);*
> >
> >*RexNode timeAdd = builder.getRexBuilder().makeCall(any,
> >SqlStdOperatorTable.TIMESTAMP_ADD, constExps);*
> >*RelNode node = builder.filter(builder.equals(builder.field("HIRE_TIME"),
> >timeAdd)).build();*
> >
> >And the corresponding MySql is coming out to be like this:
> >
> >*SELECT * FROM `db`.`emp` WHERE `HIRE_TIME` = TIMESTAMPADD(HOUR, 2,
> >CURRENT_TIMESTAMP) *which is clearly not correct.
> >
> >So, basically I need to add 2 Hours to CurrentTime. Can you explain how to
> >accomplish this using RelBuilder(with a RelNode example if possible).
> >
> >Thanks and Regards,
> >RamKrishna.
> >
> >On Thu, Mar 7, 2019 at 9:34 PM Chris Baynes <ch...@contiamo.com> wrote:
> >
> >> 1. Could you give an example of what your expected output/query is
> here?
> >> I'm not sure what you're trying to achieve.
> >> 2. There are mappings in MysqlSqlDialect.java from Calcite FLOOR to
> Mysql
> >> DATE_FORMAT. So what you want in your RelNode is something like
> >> FLOOR(HIRE_DATE TO day)
> >>
> >> On Tue, Mar 5, 2019 at 6:41 PM Rakesh Nair <ra...@gmail.com>
> wrote:
> >>
> >> > Hi,
> >> > Sorry to be a bother, but I've been sitting on this for quite a
> while now
> >> > and would really like to clear it. So I've been trying to use the
> >> > RelBuilder for building relational expressions.
> >> > 1. I'm trying to build the relational expressions for using Trim()
> >> > function. This si what I've done so far:
> >> > RelNode node =
> >> > builder.scan("emp").project(builder.call(SqlStdOperatorTable.TRIM,
> >> > builder.getRexBuilder().makeFlag(Flag.BOTH),builder.literal(" "),
> >> > builder.field("EMPNAME"))).build();
> >> > Query Explain:
> >> > LogicalProject($f0=[TRIM(FLAG(BOTH), ' ', $2)])
> >> > LogicalTableScan(table=[[hr, emp]])
> >> > Corresponding Mysql Query:
> >> > SELECT TRIM(BOTH ' ' FROM `EMPNAME`) AS `$f0`
> >> > FROM `hr`.`emp`
> >> > Could somebody tell me what I'm doing wrong here?
> >> > 2. How can I convert the following Mysql Query:
> >> > SELECT DATE_FORMAT(HIRE_DATE,'%Y/%m/%d'), EMPNAME FROM `emp` WHERE
> >> > DATE_FORMAT(HIRE_DATE,'%Y/%m/%d') = DATE_FORMAT(NOW(),'%Y/%m/%d');
> >> > to its corresponding Relational Algebra using RelBuilder? Simply put
> how
> >> > can i convert the date formats in RelBuilder?
> >> >
> >> > Thanks and Regards,
> >> > RamKrishna.
> >> >
> >>
> >>
> >> --
> >>
> >> *Christopher Baynes*
> >> Lead Developer
> >>
> >> *Contiamo – all your data in one place*
> >>
> >> Stresemannstraße 123 (c/o WeWork) | 10963 Berlin | Germany
> >>
> >> E-mail: chris@contiamo.com
> >>
> >> Web: www.contiamo.com
> >> <
> >>
> http://t.sidekickopen65.com/e1t/c/5/f18dQhb0S7lC8dDMPbW2n0x6l2B9nMJW7t5XZs4X9YtjW8q-fZW65jv3RW2zhrDH56dLV8f5DKhvM02?t=http%3A%2F%2Fwww.contiamo.com%2F&si=5165279625740288&pi=bff9f6a3-d8a4-4bf6-87d5-a5464041547d
> >> >
> >>
> >> Contiamo GmbH, Sitz der Gesellschaft: Berlin
> >> HR Berlin-Charlottenburg, HRB Nr. 156569
> >> Geschäftsführer: Lucia Hegenbartova, Michael Franzkowiak
> >>
> >