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 <notify...@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" <ramkrs...@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 <ramkrs...@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: ch...@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
> >>
> >
