Mapping arithmetic '+' operator with Date and Timestamp operands to target dialect functions

[Sachin Rohaj]

Hello Xing,

No I will not be maintaining a customized `SqlImplementor`, rather I'll be
making changes in the existing `SqlImplementor`.

Regards,
Sachin

Calcite-Master - Build # 1317 - Failure

[Apache Jenkins Server]

The Apache Jenkins build system has built Calcite-Master (build #1317)

Status: Failure

Check console output at https://builds.apache.org/job/Calcite-Master/1317/ to 
view the results.

Re: [DISCUSS] Towards Calcite 1.21.0

[Danny Chan]

Thanks for the review for CALCITE-2302, Julian.

Best,
Danny Chan
在 2019年8月26日 +0800 PM12:27，Julian Hyde ，写道：
> Sounds good.
> * I am reviewing 3122 and will commit shortly.
> * I see Danny has asked me to final review 2302. I'll do that tomorrow.
> * Who owns 1581?
>
> Julian
>
> On Sun, Aug 25, 2019 at 1:28 PM Stamatis Zampetakis  wrote:
> >
> > Hello,
> >
> > According to the discussion in JIRA the following issues seem to be very
> > close to been resolved:
> > https://issues.apache.org/jira/browse/CALCITE-1581
> > https://issues.apache.org/jira/browse/CALCITE-2302
> > https://issues.apache.org/jira/browse/CALCITE-3122
> >
> > I will wait for those to be merged and then I will start the release
> > process. I would like to kindly ask the reviewers involved to check that
> > there is nothing more left to do.
> >
> > I will start moving the remaining issues to 1.22.0. Let me know if there is
> > anything else that we should include in this release.
> >
> > Best,
> > Stamatis
> >
> > On Mon, Aug 19, 2019, 11:04 PM Julian Hyde  wrote:
> >
> > > +1
> > >
> > > I’ve poked Khai Tran re. 3122 (Pig).
> > >
> > > > On Aug 16, 2019, at 11:20 PM, Stamatis Zampetakis 
> > > wrote:
> > > >
> > > > Hi all,
> > > >
> > > > The release is advancing quite well, yet we have 22 issues marked for
> > > > 1.21.0 [1].
> > > >
> > > > From those, the following 5 seem to be the most important:
> > > >
> > > > https://issues.apache.org/jira/browse/CALCITE-1581
> > > > https://issues.apache.org/jira/browse/CALCITE-2302
> > > > https://issues.apache.org/jira/browse/CALCITE-3122
> > > > https://issues.apache.org/jira/browse/CALCITE-3224
> > > > https://issues.apache.org/jira/browse/CALCITE-3228
> > > >
> > > > I will try to have an RC for the 26th of August so let's try to get them
> > > in.
> > > >
> > > > [1]
> > > >
> > > https://issues.apache.org/jira/secure/Dashboard.jspa?selectPageId=12333950
> > > >
> > > > On Thu, Aug 1, 2019 at 8:51 AM Danny Chan  wrote:
> > > >
> > > > > Let’s get the CALCITE-2302[1]: the full implicit type cast support 
> > > > > into
> > > > > 1.21 !
> > > > >
> > > > > [1] https://github.com/apache/calcite/pull/706
> > > > >
> > > > > Best,
> > > > > Danny Chan
> > > > > 在 2019年7月21日 +0800 PM3:28，Stamatis Zampetakis ，写道：
> > > > > > Hi all,
> > > > > >
> > > > > > Approximately one month has passed from the previous release 
> > > > > > (Calcite
> > > > > > 1.20.0) and I was thinking that it would be nice to have the next
> > > release
> > > > > > out by the end of August. To do this I think we should try to have 
> > > > > > an
> > > RC
> > > > > > around the 20 of August.
> > > > > >
> > > > > > The progress towards the next release can be seen in [1]. As you can
> > > > > > observe we do not have many issues marked for fixing for 1.21.0 but 
> > > > > > we
> > > do
> > > > > > have many pull requests.
> > > > > >
> > > > > > I would like to invite committers to go over the existing PRs and 
> > > > > > for
> > > > > those
> > > > > > that we plan to review and finalize in the next month or so set the 
> > > > > > fix
> > > > > > version to 1.21.0 adding an appropriate comment.
> > > > > >
> > > > > > In term of priorities, I think we should emphasize on finalizing and
> > > > > > merging PRs that are blocking other open source projects and 
> > > > > > commercial
> > > > > > products from upgrading to the latest release. If it is not already
> > > done
> > > > > > please assign this issues the highest priority (blocker) and set the
> > > fix
> > > > > > version to 1.21.0.
> > > > > >
> > > > > > Apart from very important issues it makes sense to treat PRs in FIFO
> > > > > order.
> > > > > > Contributors who submit a PR early will certainly get discouraged to
> > > > > > contribute again if we never merge these PRs in time.
> > > > > >
> > > > > > Don't hesitate to reply to this thread if the plan above is not
> > > > > convenient
> > > > > > for you!
> > > > > >
> > > > > > Best,
> > > > > > Stamatis
> > > > > >
> > > > > > [1]
> > > > > >
> > > > >
> > > https://issues.apache.org/jira/secure/Dashboard.jspa?selectPageId=12333950
> > > > >
> > >
> > >

[jira] [Created] (CALCITE-3297) PigToSqlAggregateRule should be applied on multi-set projection to produce an optimal plan

[Khai Tran (Jira)]

Khai Tran created CALCITE-3297:
--

 Summary: PigToSqlAggregateRule should be applied on multi-set 
projection to produce an optimal plan
 Key: CALCITE-3297
 URL: https://issues.apache.org/jira/browse/CALCITE-3297
 Project: Calcite
  Issue Type: Bug
  Components: piglet
Reporter: Khai Tran


Fixed testMultisetProjection() in PigRelOpTest.java to make sure the 
PigToSqlAggregateRule is applied to produce an optimized plan for doing 
multiset projection after COLLECT() agg func.

The optimization rule was blocked by change in CALCITE-3138.



--
This message was sent by Atlassian Jira
(v8.3.2#803003)

[jira] [Created] (CALCITE-3296) Decorrelator gives empty result after decorrelating sort rel with null offset and fetch

[Juhwan Kim (Jira)]

Juhwan Kim created CALCITE-3296:
---

 Summary: Decorrelator gives empty result after decorrelating sort 
rel with null offset and fetch
 Key: CALCITE-3296
 URL: https://issues.apache.org/jira/browse/CALCITE-3296
 Project: Calcite
  Issue Type: Bug
Reporter: Juhwan Kim
Assignee: Juhwan Kim


It seems like I introduced a bug with the recent decorrelator patch. Sort rel 
with null offset and fetch would end up becoming an empty value since 
decorrelator would call sortLimit builder with 0 limit and 0 offset. It should 
pass negative values to builder when values are null.

https://github.com/apache/calcite/blob/e863294ccfbed9dd520c999f75ed0bbe03f9fb1d/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java#L423



--
This message was sent by Atlassian Jira
(v8.3.2#803003)

Re: [DISCUSS] Towards Calcite 1.21.0

[Julian Hyde]

Regarding 3122. The fix looks good to me, but one test is broken when I merge 
in the fix to [CALCITE-3138] "RelStructuredTypeFlattener doesn't restructure 
ROW type fields”.

So, we are holding until I figure with Khai Tran (author of 3122) and Igor 
Guzenko (author or 3138) out how to combine the two fixes, or if we decide to 
just disable that test.

Follow the discussion here: 
https://issues.apache.org/jira/browse/CALCITE-3122?focusedCommentId=16916028=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-16916028
 

 

Julian



> On Aug 26, 2019, at 4:48 AM, Chunwei Lei  wrote:
> 
> Thanks for your work, Stamatis.
> 
> As for 1581, I have reviewed it and it looks good to me. So I am +1 for the
> change.
> 
> 
> 
> Best,
> Chunwei
> 
> 
> On Mon, Aug 26, 2019 at 1:39 PM Stamatis Zampetakis 
> wrote:
> 
>> I see that Chunwei has reviewed 1581 and he gave a +1 so I suppose it is
>> ready to go.
>> 
>> On Mon, Aug 26, 2019, 7:27 AM Julian Hyde  wrote:
>> 
>>> Sounds good.
>>> * I am reviewing 3122 and will commit shortly.
>>> * I see Danny has asked me to final review 2302. I'll do that tomorrow.
>>> * Who owns 1581?
>>> 
>>> Julian
>>> 
>>> On Sun, Aug 25, 2019 at 1:28 PM Stamatis Zampetakis 
>>> wrote:
 
 Hello,
 
 According to the discussion in JIRA the following issues seem to be
>> very
 close to been resolved:
 https://issues.apache.org/jira/browse/CALCITE-1581
 https://issues.apache.org/jira/browse/CALCITE-2302
 https://issues.apache.org/jira/browse/CALCITE-3122
 
 I will wait for those to be merged and then I will start the release
 process. I would like to kindly ask the reviewers involved to check
>> that
 there is nothing more left to do.
 
 I will start moving the remaining issues to 1.22.0. Let me know if
>> there
>>> is
 anything else that we should include in this release.
 
 Best,
 Stamatis
 
 On Mon, Aug 19, 2019, 11:04 PM Julian Hyde  wrote:
 
> +1
> 
> I’ve poked Khai Tran re. 3122 (Pig).
> 
>> On Aug 16, 2019, at 11:20 PM, Stamatis Zampetakis <
>> zabe...@gmail.com
 
> wrote:
>> 
>> Hi all,
>> 
>> The release is advancing quite well, yet we have 22 issues marked
>> for
>> 1.21.0 [1].
>> 
>> From those, the following 5 seem to be the most important:
>> 
>> https://issues.apache.org/jira/browse/CALCITE-1581
>> https://issues.apache.org/jira/browse/CALCITE-2302
>> https://issues.apache.org/jira/browse/CALCITE-3122
>> https://issues.apache.org/jira/browse/CALCITE-3224
>> https://issues.apache.org/jira/browse/CALCITE-3228
>> 
>> I will try to have an RC for the 26th of August so let's try to get
>>> them
> in.
>> 
>> [1]
>> 
> 
>>> 
>> https://issues.apache.org/jira/secure/Dashboard.jspa?selectPageId=12333950
>> 
>> On Thu, Aug 1, 2019 at 8:51 AM Danny Chan 
>>> wrote:
>> 
>>> Let’s get the CALCITE-2302[1]: the full implicit type cast support
>>> into
>>> 1.21 !
>>> 
>>> [1] https://github.com/apache/calcite/pull/706
>>> 
>>> Best,
>>> Danny Chan
>>> 在 2019年7月21日 +0800 PM3:28，Stamatis Zampetakis >>> ，写道：
 Hi all,
 
 Approximately one month has passed from the previous release
>>> (Calcite
 1.20.0) and I was thinking that it would be nice to have the next
> release
 out by the end of August. To do this I think we should try to
>> have
>>> an
> RC
 around the 20 of August.
 
 The progress towards the next release can be seen in [1]. As you
>>> can
 observe we do not have many issues marked for fixing for 1.21.0
>>> but we
> do
 have many pull requests.
 
 I would like to invite committers to go over the existing PRs and
>>> for
>>> those
 that we plan to review and finalize in the next month or so set
>>> the fix
 version to 1.21.0 adding an appropriate comment.
 
 In term of priorities, I think we should emphasize on finalizing
>>> and
 merging PRs that are blocking other open source projects and
>>> commercial
 products from upgrading to the latest release. If it is not
>> already
> done
 please assign this issues the highest priority (blocker) and set
>>> the
> fix
 version to 1.21.0.
 
 Apart from very important issues it makes sense to treat PRs in
>>> FIFO
>>> order.
 Contributors who submit a PR early will certainly get discouraged
>>> to
 contribute again if we never merge these PRs in time.
 
 Don't hesitate to reply to this thread if the plan above is not
>>> convenient
 for you!
 
 Best,
 Stamatis

Re: Preserving CAST of STRING operands in comparison operator

[Julian Hyde]

I might be mistaken, but disabling stripCastFromString() for some dialects and 
not others doesn’t sound like it’s solving the root cause of the problem.

Julian


> On Aug 26, 2019, at 7:49 AM, Soma Mondal  wrote:
> 
> Hi Julian,
> 
> 2 tests failed when I made the stripCastFromString() no-op.
> 
>   1.
> 
>   testDb2DialectSelectQueryWithGroup
>   2.
> 
>   testSelectQueryWithGroup
> 
> Above tests pretty much do the same thing and basically strip the cast from
> String literal something like this:
> 
> Expected:
> 
> SELECT COUNT(*), SUM(employee_id)
> 
> FROM foodmart.reserve_employee
> 
> WHERE hire_date > '2015-01-01' AND (position_title = 'SDE' OR
> position_title = 'SDM')
> 
> GROUP BY store_id, position_title
> 
> But with no-op we get this:
> 
> SELECT COUNT(*), SUM(employee_id)
> 
> FROM foodmart.reserve_employee
> 
> WHERE hire_date > CAST('2015-01-01' AS TIMESTAMP(0)) AND (position_title =
> 'SDE' OR position_title = 'SDM')
> 
> GROUP BY store_id, position_title
> 
> Can I go ahead and make changes where calls to stripCastFromString() will
> be skipped for specific dialects?
> 
> Regards,
> 
> Soma
> 
> 
> On Fri, 23 Aug 2019 at 16:02, Soma Mondal 
> wrote:
> 
>> Hello,
>> 
>> We have a REL which has this information
>> select * from employee where employee_id = cast('12' as float);
>> 
>> but Calcite removes the CAST from the STRING literal('12' in our case).
>> select * from employee where employee_id = '12';
>> 
>> There are dialects which needs explicit casting in the above case and we
>> need to maintain the CAST in our dialect.
>> Calcite removes the cast in SqlImplementor's stripCastFromString()
>> method. I would like to understand why Calcite removes the CAST and shall
>> we go ahead and make the changes in Calcite to maintain the CAST.
>> 
>> Thanks & Regards,
>> Soma Mondal
>> 

Re: Preserving CAST of STRING operands in comparison operator

[Soma Mondal]

Hi Julian,

2 tests failed when I made the stripCastFromString() no-op.

   1.

   testDb2DialectSelectQueryWithGroup
   2.

   testSelectQueryWithGroup

Above tests pretty much do the same thing and basically strip the cast from
String literal something like this:

Expected:

SELECT COUNT(*), SUM(employee_id)

FROM foodmart.reserve_employee

WHERE hire_date > '2015-01-01' AND (position_title = 'SDE' OR
position_title = 'SDM')

GROUP BY store_id, position_title

But with no-op we get this:

SELECT COUNT(*), SUM(employee_id)

FROM foodmart.reserve_employee

WHERE hire_date > CAST('2015-01-01' AS TIMESTAMP(0)) AND (position_title =
'SDE' OR position_title = 'SDM')

GROUP BY store_id, position_title

Can I go ahead and make changes where calls to stripCastFromString() will
be skipped for specific dialects?

Regards,

Soma


On Fri, 23 Aug 2019 at 16:02, Soma Mondal 
wrote:

> Hello,
>
> We have a REL which has this information
> select * from employee where employee_id = cast('12' as float);
>
> but Calcite removes the CAST from the STRING literal('12' in our case).
> select * from employee where employee_id = '12';
>
> There are dialects which needs explicit casting in the above case and we
> need to maintain the CAST in our dialect.
> Calcite removes the cast in SqlImplementor's stripCastFromString()
> method. I would like to understand why Calcite removes the CAST and shall
> we go ahead and make the changes in Calcite to maintain the CAST.
>
> Thanks & Regards,
> Soma Mondal
>

Using Spark 2.4.0 as Execution Engine with Apache Calcite

[Shubham Kumar]

Hey,

I am trying to optimize queries by creating materialized views in Hive and
then exposing them to Calcite for optimized query rewrite.

I wanted to know that how mature is the spark adapter of Calcite i.e. Let's
say while making the connection I set parameter spark=true
.
Will the workflow be like this?

i) Calcite using Hive's MV's for query rewrites.
ii) When query comes ; optimized query rewrite is done by Calcite.
iii) This is converted to spark convention and successfully executed using
Spark as the engine.

I had a doubt since in many places it's mentioned that spark adapter is not
production ready or powerful enough and has been developed for
compatibility till spark 1.x only.

Also, can we extract out the query rewrite of Calcite using some API before
it is actually executed?
-- 
Thanks & Regards
Shubham Kumar

[jira] [Created] (CALCITE-3295) Aggregate call name lost in serialized json string.

[Wang Yanlin (Jira)]

Wang Yanlin created CALCITE-3295:


 Summary: Aggregate call name lost in serialized json string.
 Key: CALCITE-3295
 URL: https://issues.apache.org/jira/browse/CALCITE-3295
 Project: Calcite
  Issue Type: Bug
Reporter: Wang Yanlin


When serialize a relnode to json string, the name of aggregate call was lost.
 For a sql string like this
{code:java}
SELECT max(SAL) as max_sal from EMP group by JOB;
{code}
The serialized json string is like this
{code:java}
{
  "rels": [
{
  "id": "0",
  "relOp": "LogicalTableScan",
  "table": [
"scott",
"EMP"
  ],
  "inputs": []
},
{
  "id": "1",
  "relOp": "LogicalProject",
  "fields": [
"JOB",
"SAL"
  ],
  "exprs": [
{
  "input": 2,
  "name": "$2"
},
{
  "input": 5,
  "name": "$5"
}
  ]
},
{
  "id": "2",
  "relOp": "LogicalAggregate",
  "group": [
0
  ],
  "aggs": [
{
  "agg": {
"name": "MAX",
"kind": "MAX",
"syntax": "FUNCTION"
  },
  "type": {
"type": "DECIMAL",
"nullable": true,
"precision": 7,
"scale": 2
  },
  "distinct": false,
  "operands": [
1
  ],
}
  ]
},
{
  "id": "3",
  "relOp": "LogicalProject",
  "fields": [
"max_sal"
  ],
  "exprs": [
{
  "input": 1,
  "name": "$1"
}
  ]
}
  ]
}
{code}
Lost the name of max call.





--
This message was sent by Atlassian Jira
(v8.3.2#803003)

[jira] [Created] (CALCITE-3294) Implement FINAL Clause for MATCH_RECOGNIZE

[Julian Feinauer (Jira)]

Julian Feinauer created CALCITE-3294:


 Summary: Implement FINAL Clause for MATCH_RECOGNIZE
 Key: CALCITE-3294
 URL: https://issues.apache.org/jira/browse/CALCITE-3294
 Project: Calcite
  Issue Type: Improvement
Reporter: Julian Feinauer


With CALCITE-1935 the initial support for the MATCH_RECOGNIZE clause was 
introduced. But it is still lacking several features.
One of them is the FINAL Clause which forces the `MEASURE` to act global on all 
Tuples which were matched by the pattern.

See 
https://oracle-base.com/articles/12c/pattern-matching-in-oracle-database-12cr1

An example query would be:

{code}
SELECT *
FROM sales_history MATCH_RECOGNIZE (
 ORDER BY tstamp
 MEASURES  
   LAST(A.tstamp) AS ts_prev,
   FINAL LAST(A.tstamp) AS ts_last
 ALL ROWS PER MATCH
 PATTERN (A+)
 DEFINE
   A AS A.units_sold > 10
   ) MR
ORDER BY MR.product, MR.start_tstamp;
{code}

Here, the query matches for each sequence of rows which all have `units_sold > 
10`.
For the column `ts_prev` it always shows the timestamp of the timestamp of the 
previous match (the row before).
But for `ts_last` it shows the SAME value for each column as the `FINAL` 
modifier changes teh behavior to apply the `LAST` operator to the record set 
(similar to a window aggregation on the machted subset of rows).



--
This message was sent by Atlassian Jira
(v8.3.2#803003)

Re: [DISCUSS] Towards Calcite 1.21.0

[Chunwei Lei]

Thanks for your work, Stamatis.

As for 1581, I have reviewed it and it looks good to me. So I am +1 for the
change.



Best,
Chunwei


On Mon, Aug 26, 2019 at 1:39 PM Stamatis Zampetakis 
wrote:

> I see that Chunwei has reviewed 1581 and he gave a +1 so I suppose it is
> ready to go.
>
> On Mon, Aug 26, 2019, 7:27 AM Julian Hyde  wrote:
>
> > Sounds good.
> > * I am reviewing 3122 and will commit shortly.
> > * I see Danny has asked me to final review 2302. I'll do that tomorrow.
> > * Who owns 1581?
> >
> > Julian
> >
> > On Sun, Aug 25, 2019 at 1:28 PM Stamatis Zampetakis 
> > wrote:
> > >
> > > Hello,
> > >
> > > According to the discussion in JIRA the following issues seem to be
> very
> > > close to been resolved:
> > > https://issues.apache.org/jira/browse/CALCITE-1581
> > > https://issues.apache.org/jira/browse/CALCITE-2302
> > > https://issues.apache.org/jira/browse/CALCITE-3122
> > >
> > > I will wait for those to be merged and then I will start the release
> > > process. I would like to kindly ask the reviewers involved to check
> that
> > > there is nothing more left to do.
> > >
> > > I will start moving the remaining issues to 1.22.0. Let me know if
> there
> > is
> > > anything else that we should include in this release.
> > >
> > > Best,
> > > Stamatis
> > >
> > > On Mon, Aug 19, 2019, 11:04 PM Julian Hyde  wrote:
> > >
> > > > +1
> > > >
> > > > I’ve poked Khai Tran re. 3122 (Pig).
> > > >
> > > > > On Aug 16, 2019, at 11:20 PM, Stamatis Zampetakis <
> zabe...@gmail.com
> > >
> > > > wrote:
> > > > >
> > > > > Hi all,
> > > > >
> > > > > The release is advancing quite well, yet we have 22 issues marked
> for
> > > > > 1.21.0 [1].
> > > > >
> > > > > From those, the following 5 seem to be the most important:
> > > > >
> > > > > https://issues.apache.org/jira/browse/CALCITE-1581
> > > > > https://issues.apache.org/jira/browse/CALCITE-2302
> > > > > https://issues.apache.org/jira/browse/CALCITE-3122
> > > > > https://issues.apache.org/jira/browse/CALCITE-3224
> > > > > https://issues.apache.org/jira/browse/CALCITE-3228
> > > > >
> > > > > I will try to have an RC for the 26th of August so let's try to get
> > them
> > > > in.
> > > > >
> > > > > [1]
> > > > >
> > > >
> >
> https://issues.apache.org/jira/secure/Dashboard.jspa?selectPageId=12333950
> > > > >
> > > > > On Thu, Aug 1, 2019 at 8:51 AM Danny Chan 
> > wrote:
> > > > >
> > > > >> Let’s get the CALCITE-2302[1]: the full implicit type cast support
> > into
> > > > >> 1.21 !
> > > > >>
> > > > >> [1] https://github.com/apache/calcite/pull/706
> > > > >>
> > > > >> Best,
> > > > >> Danny Chan
> > > > >> 在 2019年7月21日 +0800 PM3:28，Stamatis Zampetakis  > >，写道：
> > > > >>> Hi all,
> > > > >>>
> > > > >>> Approximately one month has passed from the previous release
> > (Calcite
> > > > >>> 1.20.0) and I was thinking that it would be nice to have the next
> > > > release
> > > > >>> out by the end of August. To do this I think we should try to
> have
> > an
> > > > RC
> > > > >>> around the 20 of August.
> > > > >>>
> > > > >>> The progress towards the next release can be seen in [1]. As you
> > can
> > > > >>> observe we do not have many issues marked for fixing for 1.21.0
> > but we
> > > > do
> > > > >>> have many pull requests.
> > > > >>>
> > > > >>> I would like to invite committers to go over the existing PRs and
> > for
> > > > >> those
> > > > >>> that we plan to review and finalize in the next month or so set
> > the fix
> > > > >>> version to 1.21.0 adding an appropriate comment.
> > > > >>>
> > > > >>> In term of priorities, I think we should emphasize on finalizing
> > and
> > > > >>> merging PRs that are blocking other open source projects and
> > commercial
> > > > >>> products from upgrading to the latest release. If it is not
> already
> > > > done
> > > > >>> please assign this issues the highest priority (blocker) and set
> > the
> > > > fix
> > > > >>> version to 1.21.0.
> > > > >>>
> > > > >>> Apart from very important issues it makes sense to treat PRs in
> > FIFO
> > > > >> order.
> > > > >>> Contributors who submit a PR early will certainly get discouraged
> > to
> > > > >>> contribute again if we never merge these PRs in time.
> > > > >>>
> > > > >>> Don't hesitate to reply to this thread if the plan above is not
> > > > >> convenient
> > > > >>> for you!
> > > > >>>
> > > > >>> Best,
> > > > >>> Stamatis
> > > > >>>
> > > > >>> [1]
> > > > >>>
> > > > >>
> > > >
> >
> https://issues.apache.org/jira/secure/Dashboard.jspa?selectPageId=12333950
> > > > >>
> > > >
> > > >
> >
>

Re: PLC4X Request Optimization

[Julian Feinauer]

Hi Julian,

thank you very much for your insights.
Your analysis is very detailed and I agree with all your suggestions.
For myself I started to realize the "big" differences between Calcites Use Case 
and the PLC4X Case.
I will definitely have a look into the book advised by you (I like optimization 
quite a lot).

So, to sum up this thread, thanks all of you for your help and your suggestions.
I will bring a summary of this discussion and all next steps now over to the 
PLC4X list if someone is still interested to follow this topic : )

JulianF

Am 23.08.19, 18:12 schrieb "Julian Hyde" :

The first step is to realize that you are looking at an optimization 
problem. Congratulations, you’ve done that. 

The next step is to evaluate the possible optimization techniques. Volcano 
is suitable in cases where there is dataflow - where the solution is a tree or 
DAG. It doesn’t seem that you have that problem. So Volcano is not a good 
candidate, I agree. 

It is important that you separate cost model and allowable transformation 
rules from the optimization algorithms. Then you can try different algorithms.

If I were you, I would also consult a good book on the design of optimizing 
compilers - e.g the New Dragon book. They have techniques for dataflow 
optimization, dead code elimination, register assignment, loop unwinding. If 
those problems match your problems then maybe a compilation framework like LLVM 
or Graal would give you what you need. 

> On Aug 23, 2019, at 7:11 AM, Julian Feinauer 
 wrote:
> 
> Hi, 
> 
> a short update on this matter.
> We had a meetup yesterday with the PLC4X community and I prepared a very 
first "demo" implementation of the Optimizer / Framework.
> I tried to organize it after the Calcite / Volcano approach.
> 
> We had several discussions and experimented a bit and some things that 
came to my mind why I'm unsure whether the volcano framework really fits best 
here, or some other approach.
> 
> * Usually we have a pretty big set of "Operators" (in our case field 
requests in comparison to Calcites RelNodes):
> In regular cases they could be 10 but also quite often up to 100 (which 
is rather rare for 'sane' queries, I assume)
> * We have very few rules:
> In fact, we may have two or three rules (protocol specific), but usually 
of the form 'merge two field requests into one' or 'split one field request 
into two'
> * We have no tree structure, but everything is 'flat'
> 
> With the above setup its pretty obvious that we cannot profit from 
Volcanos dynamic programming approach. Furthermore, with the simple approach of 
applying all suitable rules to all possible candidates the state space explodes 
with O(n!) (where n could be large, see above).
> 
> So I think our best bet atm would be to exploit all possible spaces (but 
with excessive pruning) or use some other sort of algorithms like simple 
gradient descent (I think our convergence behavior should be quite nice if the 
cost function is "smooth" enough) or stochastic optimization like cross entropy 
or simulated annealing.
> 
> I just wanted to give some feedback back to the list as many people 
joined the discussion and had interesting ideas. And I still think that there 
is an overlap in whats done but the 'common core' is smaller than I initially 
assumed (e.g., some query optimizers AFAIR use approaches like simulated 
annealing).
> 
> Best
> Julian
> 
> Am 20.08.19, 15:38 schrieb "Julian Feinauer" 
:
> 
>Hi Stamatis,
> 
>thanks for your response.
>I think my brain just needs a bit more time to get really deep into 
those advanced planning topics (its sometimes slow on adopting...).
>But I will look through it.
>We have a meetup this week and will discuss the matter and how to 
setup everything to enable some optimization at first (introducing cost 
estimates and such) and then I will again have a deeper look and perhaps 
prepare a test case or a runnable test.
>Then its probably the easiest to reason about.
> 
>Julian
> 
>Am 20.08.19, 15:19 schrieb "Stamatis Zampetakis" :
> 
>Hi Julian F,
> 
>I admit that I didn't really get your example but talking about 
'batch
>request optimization'
>and 'collapsing "overlapping" but not equal requests' I get the 
impression
>that the problem
>is optimizing sets of queries which may have common 
sub-expressions;
>the problem is usually referred to as multi-query optimization and 
is
>indeed relevant with
>the Spool operator mentioned by Julian H.
> 
>If that's the case then the most relevant work that I can think of 
is [1],
>which solves the problem
>by slightly modifying the search strategy of the Volcano 

Re: Match Converter Rule based on Child Nodes

[XING JIN]

Hmm,, I get it
So maybe you can try HepPlanner with match order of BOTTOM_UP;
Or if VolcanoPlanner is necessary, how about call the optimization multiple
times, e.g. wrap it with a loop

Jin

rahul patwari  于2019年8月23日周五 上午10:59写道：

> Hi Jin,
>
> We wanted to transform LogicalJoin to different Join Types in the external
> system depending on the LogicalJoin Child Properties(which are in external
> convention).
> As LogicalJoin can be a child of LogicalJoin, because of VolcanoPlanner's
> top-down approach, the child LogicalJoin is not converted first and we end
> up in "Not enough rules to find the Cheapest Plan".
>
> Regards,
> Rahul
>
> On Fri, Aug 23, 2019 at 8:17 AM XING JIN  wrote:
>
> > If I understand correctly, you can try below steps:
> > 1. Create a rule of Converter to convert child nodes to append external
> > Convention;
> > 2. Create a rule of RelOptRule to convert the parent node -- check the
> > Convention of child node when matching
> >
> > Is it applicable ?
> >
> > rahul patwari  于2019年8月22日周四 下午11:18写道：
> >
> > > Hi,
> > >
> > > The properties of the child nodes correspond to the external
> Convention.
> > > So, the child nodes have to be converted before the parent can be
> > > transformed.
> > > If the property doesn't match (or) if the property cannot be
> > > determined(child node not converted), the rule cannot be applied. So,
> we
> > > end up in "Not enough Rules to find the Cheapest Plan".
> > > Is there a way to convert the Child nodes before the parent?
> > > Can VolcanoPlanner be configured to work in bottom-up fashion?
> > >
> > > Regards,
> > > Rahul
> > >
> > > On Thu, Aug 22, 2019 at 3:33 PM XING JIN 
> > wrote:
> > >
> > > > I guess in Rahul's case, the child node of join should be converted
> > > first,
> > > > thus physical properties will be appended.
> > > > But RelNode doesn't have a reference to parent, thus an independent
> > > > RelOptRule is needed to convert LogicalJoin.
> > > > Just as Michael said, you need to override the method of
> > > RelOptRule#matches
> > > > and check the properties of child node.
> > > >
> > > > Best,
> > > > Jin
> > > >
> > > > Stamatis Zampetakis  于2019年8月21日周三 下午5:50写道：
> > > >
> > > > > The Volcano planner works in a top-down fashion. It starts by
> > > > transforming
> > > > > the root and move towards the leafs of the plan. Due to this when
> > > > > transforming a logical join its inputs are still in the logical
> > > > convention
> > > > > so in principle they should not have any physical properties.
> > > > >
> > > > > Normally when the physical join algorithm is chosen the respective
> > rule
> > > > > should be responsible for demanding the inputs of the join to have
> > > > certain
> > > > > properties.
> > > > >
> > > > > Long story short, I think in your use case you should not make the
> > rule
> > > > > match based on the properties of the child nodes but it should
> match
> > > > > unconditionally and if necessary demand some properties in its
> > inputs.
> > > > If I
> > > > > remember well the EnumerableMergeJoinRule follows this approach so
> > you
> > > > can
> > > > > have a look there.
> > > > >
> > > > > Best,
> > > > > Stamatis
> > > > >
> > > > > On Tue, Aug 20, 2019, 5:07 PM Michael Mior 
> wrote:
> > > > >
> > > > > > If you just want to control whether the rule gets applied, you
> can
> > > > > > override RelOptRule#matches which canreturns a boolean indicating
> > > > > > whether the rule should be applied.
> > > > > > --
> > > > > > Michael Mior
> > > > > > mm...@apache.org
> > > > > >
> > > > > > Le ven. 9 août 2019 à 08:48, rahul patwari
> > > > > >  a écrit :
> > > > > > >
> > > > > > > Hi,
> > > > > > >
> > > > > > > We want to create a ConverterRule which converts the default
> > > calling
> > > > > > > Convention to external storage-specific calling convention
> > > depending
> > > > on
> > > > > > the
> > > > > > > Children nodes, like RelOptRule.
> > > > > > >
> > > > > > > For example, depending on the properties of the child nodes, we
> > > want
> > > > to
> > > > > > > convert LogicalJoin to external system's specific Join
> > > > implementation.
> > > > > > >
> > > > > > > Currently, ConverterRule
> > > > > > > <
> > > > > >
> > > > >
> > > >
> > >
> >
> https://github.com/apache/calcite/blob/5212d6c47e36995943f4d955a1714bf03eb08e7e/core/src/main/java/org/apache/calcite/rel/convert/ConverterRule.java#L75
> > > > > > >
> > > > > > > cannot take Children and Child Policy is
> > > > > > RelOptRuleOperandChildPolicy.ANY.
> > > > > > >
> > > > > > > What is the preferred way to achieve this task?
> > > > > > >
> > > > > > > Thanks,
> > > > > > > Rahul
> > > > > >
> > > > >
> > > >
> > >
> >
>

[jira] [Created] (CALCITE-3293) Add strcmp function

[xzh_dz (Jira)]

xzh_dz created CALCITE-3293:
---

 Summary: Add strcmp function
 Key: CALCITE-3293
 URL: https://issues.apache.org/jira/browse/CALCITE-3293
 Project: Calcite
  Issue Type: Improvement
Reporter: xzh_dz


Add strcmp function



--
This message was sent by Atlassian Jira
(v8.3.2#803003)

Re: Mapping arithmetic '+' operator with Date and Timestamp operands to target dialect functions

[XING JIN]

I guess in your approach, you need to maintain a customized
`SqlImplementor` ?
How about add a RelOptRule and detect pattern of "date + interval '1' DAY"
and use a RexShuttle to convert to "target_func(date, interval '1' DAY)"
Thus only a RelOptRule needs to be maintained.

Sachin Rohaj  于2019年8月23日周五 下午11:32写道：

> Hello,
>
> In one of my use case i am working on DATE and TIMESTAMP based operands. We
> have
> select date + interval '1' DAY from table;
>
> I need to map this to target dialect.
> select target_func(date, interval '1' DAY) from table;
>
> *Approach*: I am planning to intercept the '+' operator in the
> 'SqlImplementor' class using 'call.type.getSqlTypeName()' and delegate that
> responsibility to get the 'target_func' from the specific dialect.
> For exampe: 'dialect.getTargetFunction(SqlTypeName)'.
>
> Thanks,
> Sachin
>