You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Danny Chan <yu...@gmail.com> on 2019/09/03 08:18:42 UTC
[DISCUSS] Support Sql Hint for Calcite
Hi, fellows, I’m here again :)
This time I want to have a full discussion about CALCITE-482, which is an issue fired at 27/Nov/14 by Vladimir Sitnikov.
Almost every sql vendor supports sql hint for their production version DB engines, so it would be nice if we have a framework in Calcite to support this, so that the engines that built based on CALCITE would have the ability to extend and have their custom sql hint implementations.
In April this year I have fired an initial discussion about this topic[1], and I’m glad that we have some agreements for the design.
Recently I have fired a PR[3] and write a design doc[2] mostly based on the discussion[1], so feel free to give some feedback here so we can make the hint framework more flexible.
Any suggestions are appreciated.
[1] https://lists.apache.org/list.html?dev@calcite.apache.org:dfr=2019-4-1|dto=2019-4-30:How%20to%20traverse
[2] https://docs.google.com/document/d/1mykz-w2t1Yw7CH6NjUWpWqCAf_6YNKxSc59gXafrNCs/edit?usp=sharing
[3] https://github.com/apache/calcite/pull/1354
Best,
Danny Chan
Re: [DISCUSS] Support Sql Hint for Calcite
Posted by Danny Chan <yu...@gmail.com>.
Thanks Vladimir, I would try to implement some hints in Apache Flink, and
hope it would also helps you ~
Best,
Danny
Vladimir Ozerov <pp...@gmail.com>于2019年11月14日 周四下午7:54写道：
> Hi Danny,
>
> Thank you very much for making this happen. Query hints are a very valuable
> addition to the product.
>
> Regards,
> Vladimir.
>
> вс, 10 нояб. 2019 г. в 05:58, Danny Chan <yu...@gmail.com>:
>
> > Hi, fellows, I’m planning to merge the hints PR in the following week,
> I’m
> > very appreciated if you have other more review comment address. [1]
> >
> > Or if you have other thoughts, please address it here :)
> >
> > [1] https://github.com/apache/calcite/pull/1354
> >
> > Best,
> > Danny Chan
> > 在 2019年10月30日 +0800 AM1:42，Julian Hyde <jh...@apache.org>，写道：
> > > Sure, we can make sure something gets into 1.22. There is consensus
> > about the parser extensions, whereas the extensions to RelNode and the
> > planner engine are a little more experimental. So let’s go forward with
> > that, stating which parts we think are likely to change.
> > >
> > > Julian
> > >
> > >
> > > > On Oct 29, 2019, at 2:09 AM, Seliverstov Igor <gv...@gmail.com>
> > wrote:
> > > >
> > > > Colleagues,
> > > >
> > > > Not only Hazelcast and Apache Flink are interested in SQL hints.
> > Apache Ignite community is working on Calcite integration too, it’s
> > important for us to have appropriate API at current development stage.
> This
> > case we’ll be able to adapt our solution for SQL hints usage, probably
> > determining additional approach weaknesses or inconveniences.
> > > >
> > > > Regards,
> > > > Igor
> > > >
> > > > > 29 окт. 2019 г., в 11:51, Danny Chan <yu...@gmail.com>
> > написал(а):
> > > > >
> > > > > Julian, can we make some effort to push this feature into release
> > 1.22, there are users like Vladimir Ozerov from Hazelcast that are
> > interesting on this feature, also the Apache Flink.
> > > > >
> > > > > I agree that this internal design is not that perfect, at this
> > moment, we may hardly to conclude a perfect solution, but at least, the
> > syntax would remain unchanged in the future.
> > > > >
> > > > > So can we mark this feature as experimental and we can promote the
> > internal design when accept more feedbacks from the Calcite uses (from
> > Apache Flink or from users like Vladimir).
> > > > >
> > > > > Best,
> > > > > Danny Chan
> > > > > 在 2019年10月18日 +0800 AM4:55，Julian Hyde <jh...@apache.org>，写道：
> > > > > > I wonder whether it is possible to add some kind of “action
> > handler” to the planner engine, called, for example, when a rule has
> fired
> > and is registering the RelNode created by the rule. People can write
> their
> > own action handlers to copy hints around. Since the action handlers are
> the
> > user’s code, they can iterate faster to find a hint-propagation strategy
> > that works in practice.
> > > > > >
> > > > > > Another idea is to use VolcanoPlanner.Provenance[1]. A RelNode
> can
> > find its ancestor RelNodes, and the rules that fired to create it. So it
> > can grab hints from those ancestors. It does not need to copy those hints
> > onto itself.
> > > > > >
> > > > > > Julian
> > > > > >
> > > > > > [1]
> >
> https://calcite.apache.org/apidocs/org/apache/calcite/plan/volcano/VolcanoPlanner.Provenance.html
> > <
> >
> https://calcite.apache.org/apidocs/org/apache/calcite/plan/volcano/VolcanoPlanner.Provenance.html
> > >
> > > > > >
> > > > > > > On Oct 16, 2019, at 8:38 PM, Haisheng Yuan <
> > h.yuan@alibaba-inc.com> wrote:
> > > > > > >
> > > > > > > Julian,
> > > > > > > Your concern is very valid, and that is also our main concern.
> > > > > > > I was thinking whether we can put hint into the MEMO group, so
> > that both logical and physical expression in the same group can share the
> > same hint, without copying the hint explicitly. But for newly generated
> > expression that doesn't belong to the original group, we still need to
> copy
> > hints. What's worse, in HepPlanner, there is no such concept, we may
> still
> > need to copy hints explicity in planner rules, if we want to keep the
> hint,
> > which is burdensome.
> > > > > > >
> > > > > > > - Haisheng
> > > > > > >
> > > > > > >
> > ------------------------------------------------------------------
> > > > > > > 发件人：Danny Chan<yu...@gmail.com>
> > > > > > > 日 期：2019年10月16日 14:54:46
> > > > > > > 收件人：<de...@calcite.apache.org>
> > > > > > > 主 题：Re: [DISCUSS] Support Sql Hint for Calcite
> > > > > > >
> > > > > > > Thanks for the clarification.
> > > > > > >
> > > > > > > I understand you worried. Yes, the effort/memory would be
> wasted
> > or meaningless if hints are not used. This is just what a hint does, it
> is
> > a “hint” and non-mandatory, but we should give the chance to let user see
> > them, it is the use that decide if to use the hints and how to use them.
> > For big queries I have no confidence to cover the corner cases. So can we
> > mark this feature as experimental and used for simple queries(no
> > decorrelation) first ?
> > > > > > >
> > > > > > > For “reversible”, during the implementation, I try to make the
> > modifications non-invasive with the current codes. That is why I made all
> > the interfaces about the hint into one class named RelWithHInt. Different
> > with trait, I didn’t force users to pass in the hints in the RelNode
> > constructor. I think if is not a bigwork if we want to remove the API.
> > > > > > >
> > > > > > > Best,
> > > > > > > Danny Chan
> > > > > > > 在 2019年10月16日 +0800 AM11:14，Julian Hyde <jh...@apache.org>，写道：
> > > > > > > > By “skeptical” I mean that I think we can come up with a
> > mechanism to copy hints when applying planner rules, but even when we
> have
> > implemented that mechanism there will be many cases where people want a
> > hint and that hint is not copied to the RelNode where it is needed, and
> > many other cases where we spend the effort/memory of copying the hint to
> a
> > RelNode and the hint is not used.
> > > > > > > >
> > > > > > > > By “reversible” I mean if we come up with an API that does
> not
> > work, how do we change or remove that API without people complaining?
> > > > > > > >
> > > > > > > > Julian
> > > > > > > >
> > > > > > > >
> > > > > > > > > On Oct 15, 2019, at 7:11 PM, Danny Chan <
> > yuzhao.cyz@gmail.com> wrote:
> > > > > > > > >
> > > > > > > > > Thanks Julian
> > > > > > > > >
> > > > > > > > > > I am skeptical that RelWithHint will work for large
> > queries.
> > > > > > > > >
> > > > > > > > > For “skeptical” do you mean how to transfer the hints
> during
> > rule planning ? I’m also not that confident yet.
> > > > > > > > >
> > > > > > > > > > How do we introduce it in a reversible way
> > > > > > > > > Do you mean transform the RelWithHint back into the SqlHint
> > ? I didn’t implement it in current patch, but I think we have the ability
> > to do that because we have a inheritPath for each RelWithHint, we can
> > collect all the hints together and merge them into the SqlHints, then
> > propagate these SqlHints to the SqlNodes.
> > > > > > > > >
> > > > > > > > > > What are the other options?
> > > > > > > > > Do you mean the way to transfer hints during planning ? I
> > have no other options yet.
> > > > > > > > >
> > > > > > > > > Best,
> > > > > > > > > Danny Chan
> > > > > > > > > 在 2019年10月16日 +0800 AM8:03，dev@calcite.apache.org，写道：
> > > > > > > > > >
> > > > > > > > > > I am skeptical that RelWithHint will work for large
> > queries.
> > > > > > > >
> > > > > > >
> > > > > >
> > > >
> > >
> >
>
Re: [DISCUSS] Support Sql Hint for Calcite
Posted by Vladimir Ozerov <pp...@gmail.com>.
Hi Danny,
Thank you very much for making this happen. Query hints are a very valuable
addition to the product.
Regards,
Vladimir.
вс, 10 нояб. 2019 г. в 05:58, Danny Chan <yu...@gmail.com>:
> Hi, fellows, I’m planning to merge the hints PR in the following week, I’m
> very appreciated if you have other more review comment address. [1]
>
> Or if you have other thoughts, please address it here :)
>
> [1] https://github.com/apache/calcite/pull/1354
>
> Best,
> Danny Chan
> 在 2019年10月30日 +0800 AM1:42，Julian Hyde <jh...@apache.org>，写道：
> > Sure, we can make sure something gets into 1.22. There is consensus
> about the parser extensions, whereas the extensions to RelNode and the
> planner engine are a little more experimental. So let’s go forward with
> that, stating which parts we think are likely to change.
> >
> > Julian
> >
> >
> > > On Oct 29, 2019, at 2:09 AM, Seliverstov Igor <gv...@gmail.com>
> wrote:
> > >
> > > Colleagues,
> > >
> > > Not only Hazelcast and Apache Flink are interested in SQL hints.
> Apache Ignite community is working on Calcite integration too, it’s
> important for us to have appropriate API at current development stage. This
> case we’ll be able to adapt our solution for SQL hints usage, probably
> determining additional approach weaknesses or inconveniences.
> > >
> > > Regards,
> > > Igor
> > >
> > > > 29 окт. 2019 г., в 11:51, Danny Chan <yu...@gmail.com>
> написал(а):
> > > >
> > > > Julian, can we make some effort to push this feature into release
> 1.22, there are users like Vladimir Ozerov from Hazelcast that are
> interesting on this feature, also the Apache Flink.
> > > >
> > > > I agree that this internal design is not that perfect, at this
> moment, we may hardly to conclude a perfect solution, but at least, the
> syntax would remain unchanged in the future.
> > > >
> > > > So can we mark this feature as experimental and we can promote the
> internal design when accept more feedbacks from the Calcite uses (from
> Apache Flink or from users like Vladimir).
> > > >
> > > > Best,
> > > > Danny Chan
> > > > 在 2019年10月18日 +0800 AM4:55，Julian Hyde <jh...@apache.org>，写道：
> > > > > I wonder whether it is possible to add some kind of “action
> handler” to the planner engine, called, for example, when a rule has fired
> and is registering the RelNode created by the rule. People can write their
> own action handlers to copy hints around. Since the action handlers are the
> user’s code, they can iterate faster to find a hint-propagation strategy
> that works in practice.
> > > > >
> > > > > Another idea is to use VolcanoPlanner.Provenance[1]. A RelNode can
> find its ancestor RelNodes, and the rules that fired to create it. So it
> can grab hints from those ancestors. It does not need to copy those hints
> onto itself.
> > > > >
> > > > > Julian
> > > > >
> > > > > [1]
> https://calcite.apache.org/apidocs/org/apache/calcite/plan/volcano/VolcanoPlanner.Provenance.html
> <
> https://calcite.apache.org/apidocs/org/apache/calcite/plan/volcano/VolcanoPlanner.Provenance.html
> >
> > > > >
> > > > > > On Oct 16, 2019, at 8:38 PM, Haisheng Yuan <
> h.yuan@alibaba-inc.com> wrote:
> > > > > >
> > > > > > Julian,
> > > > > > Your concern is very valid, and that is also our main concern.
> > > > > > I was thinking whether we can put hint into the MEMO group, so
> that both logical and physical expression in the same group can share the
> same hint, without copying the hint explicitly. But for newly generated
> expression that doesn't belong to the original group, we still need to copy
> hints. What's worse, in HepPlanner, there is no such concept, we may still
> need to copy hints explicity in planner rules, if we want to keep the hint,
> which is burdensome.
> > > > > >
> > > > > > - Haisheng
> > > > > >
> > > > > >
> ------------------------------------------------------------------
> > > > > > 发件人：Danny Chan<yu...@gmail.com>
> > > > > > 日 期：2019年10月16日 14:54:46
> > > > > > 收件人：<de...@calcite.apache.org>
> > > > > > 主 题：Re: [DISCUSS] Support Sql Hint for Calcite
> > > > > >
> > > > > > Thanks for the clarification.
> > > > > >
> > > > > > I understand you worried. Yes, the effort/memory would be wasted
> or meaningless if hints are not used. This is just what a hint does, it is
> a “hint” and non-mandatory, but we should give the chance to let user see
> them, it is the use that decide if to use the hints and how to use them.
> For big queries I have no confidence to cover the corner cases. So can we
> mark this feature as experimental and used for simple queries(no
> decorrelation) first ?
> > > > > >
> > > > > > For “reversible”, during the implementation, I try to make the
> modifications non-invasive with the current codes. That is why I made all
> the interfaces about the hint into one class named RelWithHInt. Different
> with trait, I didn’t force users to pass in the hints in the RelNode
> constructor. I think if is not a bigwork if we want to remove the API.
> > > > > >
> > > > > > Best,
> > > > > > Danny Chan
> > > > > > 在 2019年10月16日 +0800 AM11:14，Julian Hyde <jh...@apache.org>，写道：
> > > > > > > By “skeptical” I mean that I think we can come up with a
> mechanism to copy hints when applying planner rules, but even when we have
> implemented that mechanism there will be many cases where people want a
> hint and that hint is not copied to the RelNode where it is needed, and
> many other cases where we spend the effort/memory of copying the hint to a
> RelNode and the hint is not used.
> > > > > > >
> > > > > > > By “reversible” I mean if we come up with an API that does not
> work, how do we change or remove that API without people complaining?
> > > > > > >
> > > > > > > Julian
> > > > > > >
> > > > > > >
> > > > > > > > On Oct 15, 2019, at 7:11 PM, Danny Chan <
> yuzhao.cyz@gmail.com> wrote:
> > > > > > > >
> > > > > > > > Thanks Julian
> > > > > > > >
> > > > > > > > > I am skeptical that RelWithHint will work for large
> queries.
> > > > > > > >
> > > > > > > > For “skeptical” do you mean how to transfer the hints during
> rule planning ? I’m also not that confident yet.
> > > > > > > >
> > > > > > > > > How do we introduce it in a reversible way
> > > > > > > > Do you mean transform the RelWithHint back into the SqlHint
> ? I didn’t implement it in current patch, but I think we have the ability
> to do that because we have a inheritPath for each RelWithHint, we can
> collect all the hints together and merge them into the SqlHints, then
> propagate these SqlHints to the SqlNodes.
> > > > > > > >
> > > > > > > > > What are the other options?
> > > > > > > > Do you mean the way to transfer hints during planning ? I
> have no other options yet.
> > > > > > > >
> > > > > > > > Best,
> > > > > > > > Danny Chan
> > > > > > > > 在 2019年10月16日 +0800 AM8:03，dev@calcite.apache.org，写道：
> > > > > > > > >
> > > > > > > > > I am skeptical that RelWithHint will work for large
> queries.
> > > > > > >
> > > > > >
> > > > >
> > >
> >
>
Re: [DISCUSS] Support Sql Hint for Calcite
Posted by Danny Chan <yu...@gmail.com>.
Hi, fellows, I’m planning to merge the hints PR in the following week, I’m very appreciated if you have other more review comment address. [1]
Or if you have other thoughts, please address it here :)
[1] https://github.com/apache/calcite/pull/1354
Best,
Danny Chan
在 2019年10月30日 +0800 AM1:42，Julian Hyde <jh...@apache.org>，写道：
> Sure, we can make sure something gets into 1.22. There is consensus about the parser extensions, whereas the extensions to RelNode and the planner engine are a little more experimental. So let’s go forward with that, stating which parts we think are likely to change.
>
> Julian
>
>
> > On Oct 29, 2019, at 2:09 AM, Seliverstov Igor <gv...@gmail.com> wrote:
> >
> > Colleagues,
> >
> > Not only Hazelcast and Apache Flink are interested in SQL hints. Apache Ignite community is working on Calcite integration too, it’s important for us to have appropriate API at current development stage. This case we’ll be able to adapt our solution for SQL hints usage, probably determining additional approach weaknesses or inconveniences.
> >
> > Regards,
> > Igor
> >
> > > 29 окт. 2019 г., в 11:51, Danny Chan <yu...@gmail.com> написал(а):
> > >
> > > Julian, can we make some effort to push this feature into release 1.22, there are users like Vladimir Ozerov from Hazelcast that are interesting on this feature, also the Apache Flink.
> > >
> > > I agree that this internal design is not that perfect, at this moment, we may hardly to conclude a perfect solution, but at least, the syntax would remain unchanged in the future.
> > >
> > > So can we mark this feature as experimental and we can promote the internal design when accept more feedbacks from the Calcite uses (from Apache Flink or from users like Vladimir).
> > >
> > > Best,
> > > Danny Chan
> > > 在 2019年10月18日 +0800 AM4:55，Julian Hyde <jh...@apache.org>，写道：
> > > > I wonder whether it is possible to add some kind of “action handler” to the planner engine, called, for example, when a rule has fired and is registering the RelNode created by the rule. People can write their own action handlers to copy hints around. Since the action handlers are the user’s code, they can iterate faster to find a hint-propagation strategy that works in practice.
> > > >
> > > > Another idea is to use VolcanoPlanner.Provenance[1]. A RelNode can find its ancestor RelNodes, and the rules that fired to create it. So it can grab hints from those ancestors. It does not need to copy those hints onto itself.
> > > >
> > > > Julian
> > > >
> > > > [1] https://calcite.apache.org/apidocs/org/apache/calcite/plan/volcano/VolcanoPlanner.Provenance.html <https://calcite.apache.org/apidocs/org/apache/calcite/plan/volcano/VolcanoPlanner.Provenance.html>
> > > >
> > > > > On Oct 16, 2019, at 8:38 PM, Haisheng Yuan <h....@alibaba-inc.com> wrote:
> > > > >
> > > > > Julian,
> > > > > Your concern is very valid, and that is also our main concern.
> > > > > I was thinking whether we can put hint into the MEMO group, so that both logical and physical expression in the same group can share the same hint, without copying the hint explicitly. But for newly generated expression that doesn't belong to the original group, we still need to copy hints. What's worse, in HepPlanner, there is no such concept, we may still need to copy hints explicity in planner rules, if we want to keep the hint, which is burdensome.
> > > > >
> > > > > - Haisheng
> > > > >
> > > > > ------------------------------------------------------------------
> > > > > 发件人：Danny Chan<yu...@gmail.com>
> > > > > 日 期：2019年10月16日 14:54:46
> > > > > 收件人：<de...@calcite.apache.org>
> > > > > 主 题：Re: [DISCUSS] Support Sql Hint for Calcite
> > > > >
> > > > > Thanks for the clarification.
> > > > >
> > > > > I understand you worried. Yes, the effort/memory would be wasted or meaningless if hints are not used. This is just what a hint does, it is a “hint” and non-mandatory, but we should give the chance to let user see them, it is the use that decide if to use the hints and how to use them. For big queries I have no confidence to cover the corner cases. So can we mark this feature as experimental and used for simple queries(no decorrelation) first ?
> > > > >
> > > > > For “reversible”, during the implementation, I try to make the modifications non-invasive with the current codes. That is why I made all the interfaces about the hint into one class named RelWithHInt. Different with trait, I didn’t force users to pass in the hints in the RelNode constructor. I think if is not a bigwork if we want to remove the API.
> > > > >
> > > > > Best,
> > > > > Danny Chan
> > > > > 在 2019年10月16日 +0800 AM11:14，Julian Hyde <jh...@apache.org>，写道：
> > > > > > By “skeptical” I mean that I think we can come up with a mechanism to copy hints when applying planner rules, but even when we have implemented that mechanism there will be many cases where people want a hint and that hint is not copied to the RelNode where it is needed, and many other cases where we spend the effort/memory of copying the hint to a RelNode and the hint is not used.
> > > > > >
> > > > > > By “reversible” I mean if we come up with an API that does not work, how do we change or remove that API without people complaining?
> > > > > >
> > > > > > Julian
> > > > > >
> > > > > >
> > > > > > > On Oct 15, 2019, at 7:11 PM, Danny Chan <yu...@gmail.com> wrote:
> > > > > > >
> > > > > > > Thanks Julian
> > > > > > >
> > > > > > > > I am skeptical that RelWithHint will work for large queries.
> > > > > > >
> > > > > > > For “skeptical” do you mean how to transfer the hints during rule planning ? I’m also not that confident yet.
> > > > > > >
> > > > > > > > How do we introduce it in a reversible way
> > > > > > > Do you mean transform the RelWithHint back into the SqlHint ? I didn’t implement it in current patch, but I think we have the ability to do that because we have a inheritPath for each RelWithHint, we can collect all the hints together and merge them into the SqlHints, then propagate these SqlHints to the SqlNodes.
> > > > > > >
> > > > > > > > What are the other options?
> > > > > > > Do you mean the way to transfer hints during planning ? I have no other options yet.
> > > > > > >
> > > > > > > Best,
> > > > > > > Danny Chan
> > > > > > > 在 2019年10月16日 +0800 AM8:03，dev@calcite.apache.org，写道：
> > > > > > > >
> > > > > > > > I am skeptical that RelWithHint will work for large queries.
> > > > > >
> > > > >
> > > >
> >
>
Re: [DISCUSS] Support Sql Hint for Calcite
Posted by Julian Hyde <jh...@apache.org>.
Sure, we can make sure something gets into 1.22. There is consensus about the parser extensions, whereas the extensions to RelNode and the planner engine are a little more experimental. So let’s go forward with that, stating which parts we think are likely to change.
Julian
> On Oct 29, 2019, at 2:09 AM, Seliverstov Igor <gv...@gmail.com> wrote:
>
> Colleagues,
>
> Not only Hazelcast and Apache Flink are interested in SQL hints. Apache Ignite community is working on Calcite integration too, it’s important for us to have appropriate API at current development stage. This case we’ll be able to adapt our solution for SQL hints usage, probably determining additional approach weaknesses or inconveniences.
>
> Regards,
> Igor
>
>> 29 окт. 2019 г., в 11:51, Danny Chan <yu...@gmail.com> написал(а):
>>
>> Julian, can we make some effort to push this feature into release 1.22, there are users like Vladimir Ozerov from Hazelcast that are interesting on this feature, also the Apache Flink.
>>
>> I agree that this internal design is not that perfect, at this moment, we may hardly to conclude a perfect solution, but at least, the syntax would remain unchanged in the future.
>>
>> So can we mark this feature as experimental and we can promote the internal design when accept more feedbacks from the Calcite uses (from Apache Flink or from users like Vladimir).
>>
>> Best,
>> Danny Chan
>> 在 2019年10月18日 +0800 AM4:55，Julian Hyde <jh...@apache.org>，写道：
>>> I wonder whether it is possible to add some kind of “action handler” to the planner engine, called, for example, when a rule has fired and is registering the RelNode created by the rule. People can write their own action handlers to copy hints around. Since the action handlers are the user’s code, they can iterate faster to find a hint-propagation strategy that works in practice.
>>>
>>> Another idea is to use VolcanoPlanner.Provenance[1]. A RelNode can find its ancestor RelNodes, and the rules that fired to create it. So it can grab hints from those ancestors. It does not need to copy those hints onto itself.
>>>
>>> Julian
>>>
>>> [1] https://calcite.apache.org/apidocs/org/apache/calcite/plan/volcano/VolcanoPlanner.Provenance.html <https://calcite.apache.org/apidocs/org/apache/calcite/plan/volcano/VolcanoPlanner.Provenance.html>
>>>
>>>> On Oct 16, 2019, at 8:38 PM, Haisheng Yuan <h....@alibaba-inc.com> wrote:
>>>>
>>>> Julian,
>>>> Your concern is very valid, and that is also our main concern.
>>>> I was thinking whether we can put hint into the MEMO group, so that both logical and physical expression in the same group can share the same hint, without copying the hint explicitly. But for newly generated expression that doesn't belong to the original group, we still need to copy hints. What's worse, in HepPlanner, there is no such concept, we may still need to copy hints explicity in planner rules, if we want to keep the hint, which is burdensome.
>>>>
>>>> - Haisheng
>>>>
>>>> ------------------------------------------------------------------
>>>> 发件人：Danny Chan<yu...@gmail.com>
>>>> 日 期：2019年10月16日 14:54:46
>>>> 收件人：<de...@calcite.apache.org>
>>>> 主 题：Re: [DISCUSS] Support Sql Hint for Calcite
>>>>
>>>> Thanks for the clarification.
>>>>
>>>> I understand you worried. Yes, the effort/memory would be wasted or meaningless if hints are not used. This is just what a hint does, it is a “hint” and non-mandatory, but we should give the chance to let user see them, it is the use that decide if to use the hints and how to use them. For big queries I have no confidence to cover the corner cases. So can we mark this feature as experimental and used for simple queries(no decorrelation) first ?
>>>>
>>>> For “reversible”, during the implementation, I try to make the modifications non-invasive with the current codes. That is why I made all the interfaces about the hint into one class named RelWithHInt. Different with trait, I didn’t force users to pass in the hints in the RelNode constructor. I think if is not a bigwork if we want to remove the API.
>>>>
>>>> Best,
>>>> Danny Chan
>>>> 在 2019年10月16日 +0800 AM11:14，Julian Hyde <jh...@apache.org>，写道：
>>>>> By “skeptical” I mean that I think we can come up with a mechanism to copy hints when applying planner rules, but even when we have implemented that mechanism there will be many cases where people want a hint and that hint is not copied to the RelNode where it is needed, and many other cases where we spend the effort/memory of copying the hint to a RelNode and the hint is not used.
>>>>>
>>>>> By “reversible” I mean if we come up with an API that does not work, how do we change or remove that API without people complaining?
>>>>>
>>>>> Julian
>>>>>
>>>>>
>>>>>> On Oct 15, 2019, at 7:11 PM, Danny Chan <yu...@gmail.com> wrote:
>>>>>>
>>>>>> Thanks Julian
>>>>>>
>>>>>>> I am skeptical that RelWithHint will work for large queries.
>>>>>>
>>>>>> For “skeptical” do you mean how to transfer the hints during rule planning ? I’m also not that confident yet.
>>>>>>
>>>>>>> How do we introduce it in a reversible way
>>>>>> Do you mean transform the RelWithHint back into the SqlHint ? I didn’t implement it in current patch, but I think we have the ability to do that because we have a inheritPath for each RelWithHint, we can collect all the hints together and merge them into the SqlHints, then propagate these SqlHints to the SqlNodes.
>>>>>>
>>>>>>> What are the other options?
>>>>>> Do you mean the way to transfer hints during planning ? I have no other options yet.
>>>>>>
>>>>>> Best,
>>>>>> Danny Chan
>>>>>> 在 2019年10月16日 +0800 AM8:03，dev@calcite.apache.org，写道：
>>>>>>>
>>>>>>> I am skeptical that RelWithHint will work for large queries.
>>>>>
>>>>
>>>
>
Re: [DISCUSS] Support Sql Hint for Calcite
Posted by Seliverstov Igor <gv...@gmail.com>.
Colleagues,
Not only Hazelcast and Apache Flink are interested in SQL hints. Apache Ignite community is working on Calcite integration too, it’s important for us to have appropriate API at current development stage. This case we’ll be able to adapt our solution for SQL hints usage, probably determining additional approach weaknesses or inconveniences.
Regards,
Igor
> 29 окт. 2019 г., в 11:51, Danny Chan <yu...@gmail.com> написал(а):
>
> Julian, can we make some effort to push this feature into release 1.22, there are users like Vladimir Ozerov from Hazelcast that are interesting on this feature, also the Apache Flink.
>
> I agree that this internal design is not that perfect, at this moment, we may hardly to conclude a perfect solution, but at least, the syntax would remain unchanged in the future.
>
> So can we mark this feature as experimental and we can promote the internal design when accept more feedbacks from the Calcite uses (from Apache Flink or from users like Vladimir).
>
> Best,
> Danny Chan
> 在 2019年10月18日 +0800 AM4:55，Julian Hyde <jh...@apache.org>，写道：
>> I wonder whether it is possible to add some kind of “action handler” to the planner engine, called, for example, when a rule has fired and is registering the RelNode created by the rule. People can write their own action handlers to copy hints around. Since the action handlers are the user’s code, they can iterate faster to find a hint-propagation strategy that works in practice.
>>
>> Another idea is to use VolcanoPlanner.Provenance[1]. A RelNode can find its ancestor RelNodes, and the rules that fired to create it. So it can grab hints from those ancestors. It does not need to copy those hints onto itself.
>>
>> Julian
>>
>> [1] https://calcite.apache.org/apidocs/org/apache/calcite/plan/volcano/VolcanoPlanner.Provenance.html <https://calcite.apache.org/apidocs/org/apache/calcite/plan/volcano/VolcanoPlanner.Provenance.html>
>>
>>> On Oct 16, 2019, at 8:38 PM, Haisheng Yuan <h....@alibaba-inc.com> wrote:
>>>
>>> Julian,
>>> Your concern is very valid, and that is also our main concern.
>>> I was thinking whether we can put hint into the MEMO group, so that both logical and physical expression in the same group can share the same hint, without copying the hint explicitly. But for newly generated expression that doesn't belong to the original group, we still need to copy hints. What's worse, in HepPlanner, there is no such concept, we may still need to copy hints explicity in planner rules, if we want to keep the hint, which is burdensome.
>>>
>>> - Haisheng
>>>
>>> ------------------------------------------------------------------
>>> 发件人：Danny Chan<yu...@gmail.com>
>>> 日 期：2019年10月16日 14:54:46
>>> 收件人：<de...@calcite.apache.org>
>>> 主 题：Re: [DISCUSS] Support Sql Hint for Calcite
>>>
>>> Thanks for the clarification.
>>>
>>> I understand you worried. Yes, the effort/memory would be wasted or meaningless if hints are not used. This is just what a hint does, it is a “hint” and non-mandatory, but we should give the chance to let user see them, it is the use that decide if to use the hints and how to use them. For big queries I have no confidence to cover the corner cases. So can we mark this feature as experimental and used for simple queries(no decorrelation) first ?
>>>
>>> For “reversible”, during the implementation, I try to make the modifications non-invasive with the current codes. That is why I made all the interfaces about the hint into one class named RelWithHInt. Different with trait, I didn’t force users to pass in the hints in the RelNode constructor. I think if is not a bigwork if we want to remove the API.
>>>
>>> Best,
>>> Danny Chan
>>> 在 2019年10月16日 +0800 AM11:14，Julian Hyde <jh...@apache.org>，写道：
>>>> By “skeptical” I mean that I think we can come up with a mechanism to copy hints when applying planner rules, but even when we have implemented that mechanism there will be many cases where people want a hint and that hint is not copied to the RelNode where it is needed, and many other cases where we spend the effort/memory of copying the hint to a RelNode and the hint is not used.
>>>>
>>>> By “reversible” I mean if we come up with an API that does not work, how do we change or remove that API without people complaining?
>>>>
>>>> Julian
>>>>
>>>>
>>>>> On Oct 15, 2019, at 7:11 PM, Danny Chan <yu...@gmail.com> wrote:
>>>>>
>>>>> Thanks Julian
>>>>>
>>>>>> I am skeptical that RelWithHint will work for large queries.
>>>>>
>>>>> For “skeptical” do you mean how to transfer the hints during rule planning ? I’m also not that confident yet.
>>>>>
>>>>>> How do we introduce it in a reversible way
>>>>> Do you mean transform the RelWithHint back into the SqlHint ? I didn’t implement it in current patch, but I think we have the ability to do that because we have a inheritPath for each RelWithHint, we can collect all the hints together and merge them into the SqlHints, then propagate these SqlHints to the SqlNodes.
>>>>>
>>>>>> What are the other options?
>>>>> Do you mean the way to transfer hints during planning ? I have no other options yet.
>>>>>
>>>>> Best,
>>>>> Danny Chan
>>>>> 在 2019年10月16日 +0800 AM8:03，dev@calcite.apache.org，写道：
>>>>>>
>>>>>> I am skeptical that RelWithHint will work for large queries.
>>>>
>>>
>>
Re: [DISCUSS] Support Sql Hint for Calcite
Posted by Danny Chan <yu...@gmail.com>.
Julian, can we make some effort to push this feature into release 1.22, there are users like Vladimir Ozerov from Hazelcast that are interesting on this feature, also the Apache Flink.
I agree that this internal design is not that perfect, at this moment, we may hardly to conclude a perfect solution, but at least, the syntax would remain unchanged in the future.
So can we mark this feature as experimental and we can promote the internal design when accept more feedbacks from the Calcite uses (from Apache Flink or from users like Vladimir).
Best,
Danny Chan
在 2019年10月18日 +0800 AM4:55，Julian Hyde <jh...@apache.org>，写道：
> I wonder whether it is possible to add some kind of “action handler” to the planner engine, called, for example, when a rule has fired and is registering the RelNode created by the rule. People can write their own action handlers to copy hints around. Since the action handlers are the user’s code, they can iterate faster to find a hint-propagation strategy that works in practice.
>
> Another idea is to use VolcanoPlanner.Provenance[1]. A RelNode can find its ancestor RelNodes, and the rules that fired to create it. So it can grab hints from those ancestors. It does not need to copy those hints onto itself.
>
> Julian
>
> [1] https://calcite.apache.org/apidocs/org/apache/calcite/plan/volcano/VolcanoPlanner.Provenance.html <https://calcite.apache.org/apidocs/org/apache/calcite/plan/volcano/VolcanoPlanner.Provenance.html>
>
> > On Oct 16, 2019, at 8:38 PM, Haisheng Yuan <h....@alibaba-inc.com> wrote:
> >
> > Julian,
> > Your concern is very valid, and that is also our main concern.
> > I was thinking whether we can put hint into the MEMO group, so that both logical and physical expression in the same group can share the same hint, without copying the hint explicitly. But for newly generated expression that doesn't belong to the original group, we still need to copy hints. What's worse, in HepPlanner, there is no such concept, we may still need to copy hints explicity in planner rules, if we want to keep the hint, which is burdensome.
> >
> > - Haisheng
> >
> > ------------------------------------------------------------------
> > 发件人：Danny Chan<yu...@gmail.com>
> > 日 期：2019年10月16日 14:54:46
> > 收件人：<de...@calcite.apache.org>
> > 主 题：Re: [DISCUSS] Support Sql Hint for Calcite
> >
> > Thanks for the clarification.
> >
> > I understand you worried. Yes, the effort/memory would be wasted or meaningless if hints are not used. This is just what a hint does, it is a “hint” and non-mandatory, but we should give the chance to let user see them, it is the use that decide if to use the hints and how to use them. For big queries I have no confidence to cover the corner cases. So can we mark this feature as experimental and used for simple queries(no decorrelation) first ?
> >
> > For “reversible”, during the implementation, I try to make the modifications non-invasive with the current codes. That is why I made all the interfaces about the hint into one class named RelWithHInt. Different with trait, I didn’t force users to pass in the hints in the RelNode constructor. I think if is not a bigwork if we want to remove the API.
> >
> > Best,
> > Danny Chan
> > 在 2019年10月16日 +0800 AM11:14，Julian Hyde <jh...@apache.org>，写道：
> > > By “skeptical” I mean that I think we can come up with a mechanism to copy hints when applying planner rules, but even when we have implemented that mechanism there will be many cases where people want a hint and that hint is not copied to the RelNode where it is needed, and many other cases where we spend the effort/memory of copying the hint to a RelNode and the hint is not used.
> > >
> > > By “reversible” I mean if we come up with an API that does not work, how do we change or remove that API without people complaining?
> > >
> > > Julian
> > >
> > >
> > > > On Oct 15, 2019, at 7:11 PM, Danny Chan <yu...@gmail.com> wrote:
> > > >
> > > > Thanks Julian
> > > >
> > > > > I am skeptical that RelWithHint will work for large queries.
> > > >
> > > > For “skeptical” do you mean how to transfer the hints during rule planning ? I’m also not that confident yet.
> > > >
> > > > > How do we introduce it in a reversible way
> > > > Do you mean transform the RelWithHint back into the SqlHint ? I didn’t implement it in current patch, but I think we have the ability to do that because we have a inheritPath for each RelWithHint, we can collect all the hints together and merge them into the SqlHints, then propagate these SqlHints to the SqlNodes.
> > > >
> > > > > What are the other options?
> > > > Do you mean the way to transfer hints during planning ? I have no other options yet.
> > > >
> > > > Best,
> > > > Danny Chan
> > > > 在 2019年10月16日 +0800 AM8:03，dev@calcite.apache.org，写道：
> > > > >
> > > > > I am skeptical that RelWithHint will work for large queries.
> > >
> >
>
Re: [DISCUSS] Support Sql Hint for Calcite
Posted by Danny Chan <yu...@gmail.com>.
Thanks Julian, for current patch, I choose 1 because it can applied both to Hep and Volcano. I make these latest changes:
• Add a new interface RelOptRuleCall.transformTo(RelNode, Map, BiFunction) to make the hints copy strategy overridable
• Cache the hint strategies into RelOptCluster so that user can query the strategies during rule planning
Another reason I didn’t choose 2 is that a RelNode’s parent node may also be derived from a Rule matching, so I have to lookup recursively to find the real original node for the hints.
Best,
Danny Chan
在 2019年10月18日 +0800 AM4:55，Julian Hyde <jh...@apache.org>，写道：
> I wonder whether it is possible to add some kind of “action handler” to the planner engine, called, for example, when a rule has fired and is registering the RelNode created by the rule. People can write their own action handlers to copy hints around. Since the action handlers are the user’s code, they can iterate faster to find a hint-propagation strategy that works in practice.
>
> Another idea is to use VolcanoPlanner.Provenance[1]. A RelNode can find its ancestor RelNodes, and the rules that fired to create it. So it can grab hints from those ancestors. It does not need to copy those hints onto itself.
>
> Julian
>
> [1] https://calcite.apache.org/apidocs/org/apache/calcite/plan/volcano/VolcanoPlanner.Provenance.html <https://calcite.apache.org/apidocs/org/apache/calcite/plan/volcano/VolcanoPlanner.Provenance.html>
>
> > On Oct 16, 2019, at 8:38 PM, Haisheng Yuan <h....@alibaba-inc.com> wrote:
> >
> > Julian,
> > Your concern is very valid, and that is also our main concern.
> > I was thinking whether we can put hint into the MEMO group, so that both logical and physical expression in the same group can share the same hint, without copying the hint explicitly. But for newly generated expression that doesn't belong to the original group, we still need to copy hints. What's worse, in HepPlanner, there is no such concept, we may still need to copy hints explicity in planner rules, if we want to keep the hint, which is burdensome.
> >
> > - Haisheng
> >
> > ------------------------------------------------------------------
> > 发件人：Danny Chan<yu...@gmail.com>
> > 日 期：2019年10月16日 14:54:46
> > 收件人：<de...@calcite.apache.org>
> > 主 题：Re: [DISCUSS] Support Sql Hint for Calcite
> >
> > Thanks for the clarification.
> >
> > I understand you worried. Yes, the effort/memory would be wasted or meaningless if hints are not used. This is just what a hint does, it is a “hint” and non-mandatory, but we should give the chance to let user see them, it is the use that decide if to use the hints and how to use them. For big queries I have no confidence to cover the corner cases. So can we mark this feature as experimental and used for simple queries(no decorrelation) first ?
> >
> > For “reversible”, during the implementation, I try to make the modifications non-invasive with the current codes. That is why I made all the interfaces about the hint into one class named RelWithHInt. Different with trait, I didn’t force users to pass in the hints in the RelNode constructor. I think if is not a bigwork if we want to remove the API.
> >
> > Best,
> > Danny Chan
> > 在 2019年10月16日 +0800 AM11:14，Julian Hyde <jh...@apache.org>，写道：
> > > By “skeptical” I mean that I think we can come up with a mechanism to copy hints when applying planner rules, but even when we have implemented that mechanism there will be many cases where people want a hint and that hint is not copied to the RelNode where it is needed, and many other cases where we spend the effort/memory of copying the hint to a RelNode and the hint is not used.
> > >
> > > By “reversible” I mean if we come up with an API that does not work, how do we change or remove that API without people complaining?
> > >
> > > Julian
> > >
> > >
> > > > On Oct 15, 2019, at 7:11 PM, Danny Chan <yu...@gmail.com> wrote:
> > > >
> > > > Thanks Julian
> > > >
> > > > > I am skeptical that RelWithHint will work for large queries.
> > > >
> > > > For “skeptical” do you mean how to transfer the hints during rule planning ? I’m also not that confident yet.
> > > >
> > > > > How do we introduce it in a reversible way
> > > > Do you mean transform the RelWithHint back into the SqlHint ? I didn’t implement it in current patch, but I think we have the ability to do that because we have a inheritPath for each RelWithHint, we can collect all the hints together and merge them into the SqlHints, then propagate these SqlHints to the SqlNodes.
> > > >
> > > > > What are the other options?
> > > > Do you mean the way to transfer hints during planning ? I have no other options yet.
> > > >
> > > > Best,
> > > > Danny Chan
> > > > 在 2019年10月16日 +0800 AM8:03，dev@calcite.apache.org，写道：
> > > > >
> > > > > I am skeptical that RelWithHint will work for large queries.
> > >
> >
>
Re: [DISCUSS] Support Sql Hint for Calcite
Posted by Julian Hyde <jh...@apache.org>.
I wonder whether it is possible to add some kind of “action handler” to the planner engine, called, for example, when a rule has fired and is registering the RelNode created by the rule. People can write their own action handlers to copy hints around. Since the action handlers are the user’s code, they can iterate faster to find a hint-propagation strategy that works in practice.
Another idea is to use VolcanoPlanner.Provenance[1]. A RelNode can find its ancestor RelNodes, and the rules that fired to create it. So it can grab hints from those ancestors. It does not need to copy those hints onto itself.
Julian
[1] https://calcite.apache.org/apidocs/org/apache/calcite/plan/volcano/VolcanoPlanner.Provenance.html <https://calcite.apache.org/apidocs/org/apache/calcite/plan/volcano/VolcanoPlanner.Provenance.html>
> On Oct 16, 2019, at 8:38 PM, Haisheng Yuan <h....@alibaba-inc.com> wrote:
>
> Julian,
> Your concern is very valid, and that is also our main concern.
> I was thinking whether we can put hint into the MEMO group, so that both logical and physical expression in the same group can share the same hint, without copying the hint explicitly. But for newly generated expression that doesn't belong to the original group, we still need to copy hints. What's worse, in HepPlanner, there is no such concept, we may still need to copy hints explicity in planner rules, if we want to keep the hint, which is burdensome.
>
> - Haisheng
>
> ------------------------------------------------------------------
> 发件人：Danny Chan<yu...@gmail.com>
> 日 期：2019年10月16日 14:54:46
> 收件人：<de...@calcite.apache.org>
> 主 题：Re: [DISCUSS] Support Sql Hint for Calcite
>
> Thanks for the clarification.
>
> I understand you worried. Yes, the effort/memory would be wasted or meaningless if hints are not used. This is just what a hint does, it is a “hint” and non-mandatory, but we should give the chance to let user see them, it is the use that decide if to use the hints and how to use them. For big queries I have no confidence to cover the corner cases. So can we mark this feature as experimental and used for simple queries(no decorrelation) first ?
>
> For “reversible”, during the implementation, I try to make the modifications non-invasive with the current codes. That is why I made all the interfaces about the hint into one class named RelWithHInt. Different with trait, I didn’t force users to pass in the hints in the RelNode constructor. I think if is not a bigwork if we want to remove the API.
>
> Best,
> Danny Chan
> 在 2019年10月16日 +0800 AM11:14，Julian Hyde <jh...@apache.org>，写道：
>> By “skeptical” I mean that I think we can come up with a mechanism to copy hints when applying planner rules, but even when we have implemented that mechanism there will be many cases where people want a hint and that hint is not copied to the RelNode where it is needed, and many other cases where we spend the effort/memory of copying the hint to a RelNode and the hint is not used.
>>
>> By “reversible” I mean if we come up with an API that does not work, how do we change or remove that API without people complaining?
>>
>> Julian
>>
>>
>>> On Oct 15, 2019, at 7:11 PM, Danny Chan <yu...@gmail.com> wrote:
>>>
>>> Thanks Julian
>>>
>>>> I am skeptical that RelWithHint will work for large queries.
>>>
>>> For “skeptical” do you mean how to transfer the hints during rule planning ? I’m also not that confident yet.
>>>
>>>> How do we introduce it in a reversible way
>>> Do you mean transform the RelWithHint back into the SqlHint ? I didn’t implement it in current patch, but I think we have the ability to do that because we have a inheritPath for each RelWithHint, we can collect all the hints together and merge them into the SqlHints, then propagate these SqlHints to the SqlNodes.
>>>
>>>> What are the other options?
>>> Do you mean the way to transfer hints during planning ? I have no other options yet.
>>>
>>> Best,
>>> Danny Chan
>>> 在 2019年10月16日 +0800 AM8:03，dev@calcite.apache.org，写道：
>>>>
>>>> I am skeptical that RelWithHint will work for large queries.
>>
>
Re: Re: [DISCUSS] Support Sql Hint for Calcite
Posted by Haisheng Yuan <h....@alibaba-inc.com>.
Julian,
Your concern is very valid, and that is also our main concern.
I was thinking whether we can put hint into the MEMO group, so that both logical and physical expression in the same group can share the same hint, without copying the hint explicitly. But for newly generated expression that doesn't belong to the original group, we still need to copy hints. What's worse, in HepPlanner, there is no such concept, we may still need to copy hints explicity in planner rules, if we want to keep the hint, which is burdensome.
- Haisheng
------------------------------------------------------------------
发件人：Danny Chan<yu...@gmail.com>
日 期：2019年10月16日 14:54:46
收件人：<de...@calcite.apache.org>
主 题：Re: [DISCUSS] Support Sql Hint for Calcite
Thanks for the clarification.
I understand you worried. Yes, the effort/memory would be wasted or meaningless if hints are not used. This is just what a hint does, it is a “hint” and non-mandatory, but we should give the chance to let user see them, it is the use that decide if to use the hints and how to use them. For big queries I have no confidence to cover the corner cases. So can we mark this feature as experimental and used for simple queries(no decorrelation) first ?
For “reversible”, during the implementation, I try to make the modifications non-invasive with the current codes. That is why I made all the interfaces about the hint into one class named RelWithHInt. Different with trait, I didn’t force users to pass in the hints in the RelNode constructor. I think if is not a bigwork if we want to remove the API.
Best,
Danny Chan
在 2019年10月16日 +0800 AM11:14，Julian Hyde <jh...@apache.org>，写道：
> By “skeptical” I mean that I think we can come up with a mechanism to copy hints when applying planner rules, but even when we have implemented that mechanism there will be many cases where people want a hint and that hint is not copied to the RelNode where it is needed, and many other cases where we spend the effort/memory of copying the hint to a RelNode and the hint is not used.
>
> By “reversible” I mean if we come up with an API that does not work, how do we change or remove that API without people complaining?
>
> Julian
>
>
> > On Oct 15, 2019, at 7:11 PM, Danny Chan <yu...@gmail.com> wrote:
> >
> > Thanks Julian
> >
> > > I am skeptical that RelWithHint will work for large queries.
> >
> > For “skeptical” do you mean how to transfer the hints during rule planning ? I’m also not that confident yet.
> >
> > > How do we introduce it in a reversible way
> > Do you mean transform the RelWithHint back into the SqlHint ? I didn’t implement it in current patch, but I think we have the ability to do that because we have a inheritPath for each RelWithHint, we can collect all the hints together and merge them into the SqlHints, then propagate these SqlHints to the SqlNodes.
> >
> > > What are the other options?
> > Do you mean the way to transfer hints during planning ? I have no other options yet.
> >
> > Best,
> > Danny Chan
> > 在 2019年10月16日 +0800 AM8:03，dev@calcite.apache.org，写道：
> > >
> > > I am skeptical that RelWithHint will work for large queries.
>
Re: [DISCUSS] Support Sql Hint for Calcite
Posted by Danny Chan <yu...@gmail.com>.
Thanks for the clarification.
I understand you worried. Yes, the effort/memory would be wasted or meaningless if hints are not used. This is just what a hint does, it is a “hint” and non-mandatory, but we should give the chance to let user see them, it is the use that decide if to use the hints and how to use them. For big queries I have no confidence to cover the corner cases. So can we mark this feature as experimental and used for simple queries(no decorrelation) first ?
For “reversible”, during the implementation, I try to make the modifications non-invasive with the current codes. That is why I made all the interfaces about the hint into one class named RelWithHInt. Different with trait, I didn’t force users to pass in the hints in the RelNode constructor. I think if is not a bigwork if we want to remove the API.
Best,
Danny Chan
在 2019年10月16日 +0800 AM11:14，Julian Hyde <jh...@apache.org>，写道：
> By “skeptical” I mean that I think we can come up with a mechanism to copy hints when applying planner rules, but even when we have implemented that mechanism there will be many cases where people want a hint and that hint is not copied to the RelNode where it is needed, and many other cases where we spend the effort/memory of copying the hint to a RelNode and the hint is not used.
>
> By “reversible” I mean if we come up with an API that does not work, how do we change or remove that API without people complaining?
>
> Julian
>
>
> > On Oct 15, 2019, at 7:11 PM, Danny Chan <yu...@gmail.com> wrote:
> >
> > Thanks Julian
> >
> > > I am skeptical that RelWithHint will work for large queries.
> >
> > For “skeptical” do you mean how to transfer the hints during rule planning ? I’m also not that confident yet.
> >
> > > How do we introduce it in a reversible way
> > Do you mean transform the RelWithHint back into the SqlHint ? I didn’t implement it in current patch, but I think we have the ability to do that because we have a inheritPath for each RelWithHint, we can collect all the hints together and merge them into the SqlHints, then propagate these SqlHints to the SqlNodes.
> >
> > > What are the other options?
> > Do you mean the way to transfer hints during planning ? I have no other options yet.
> >
> > Best,
> > Danny Chan
> > 在 2019年10月16日 +0800 AM8:03，dev@calcite.apache.org，写道：
> > >
> > > I am skeptical that RelWithHint will work for large queries.
>
Re: [DISCUSS] Support Sql Hint for Calcite
Posted by Julian Hyde <jh...@apache.org>.
By “skeptical” I mean that I think we can come up with a mechanism to copy hints when applying planner rules, but even when we have implemented that mechanism there will be many cases where people want a hint and that hint is not copied to the RelNode where it is needed, and many other cases where we spend the effort/memory of copying the hint to a RelNode and the hint is not used.
By “reversible” I mean if we come up with an API that does not work, how do we change or remove that API without people complaining?
Julian
> On Oct 15, 2019, at 7:11 PM, Danny Chan <yu...@gmail.com> wrote:
>
> Thanks Julian
>
>> I am skeptical that RelWithHint will work for large queries.
>
> For “skeptical” do you mean how to transfer the hints during rule planning ? I’m also not that confident yet.
>
>> How do we introduce it in a reversible way
> Do you mean transform the RelWithHint back into the SqlHint ? I didn’t implement it in current patch, but I think we have the ability to do that because we have a inheritPath for each RelWithHint, we can collect all the hints together and merge them into the SqlHints, then propagate these SqlHints to the SqlNodes.
>
>> What are the other options?
> Do you mean the way to transfer hints during planning ? I have no other options yet.
>
> Best,
> Danny Chan
> 在 2019年10月16日 +0800 AM8:03，dev@calcite.apache.org，写道：
>>
>> I am skeptical that RelWithHint will work for large queries.
Re: [DISCUSS] Support Sql Hint for Calcite
Posted by Danny Chan <yu...@gmail.com>.
Thanks Julian
> I am skeptical that RelWithHint will work for large queries.
For “skeptical” do you mean how to transfer the hints during rule planning ? I’m also not that confident yet.
> How do we introduce it in a reversible way
Do you mean transform the RelWithHint back into the SqlHint ? I didn’t implement it in current patch, but I think we have the ability to do that because we have a inheritPath for each RelWithHint, we can collect all the hints together and merge them into the SqlHints, then propagate these SqlHints to the SqlNodes.
> What are the other options?
Do you mean the way to transfer hints during planning ? I have no other options yet.
Best,
Danny Chan
在 2019年10月16日 +0800 AM8:03，dev@calcite.apache.org，写道：
>
> I am skeptical that RelWithHint will work for large queries.
Re: [DISCUSS] Support Sql Hint for Calcite
Posted by Julian Hyde <jh...@apache.org>.
I am skeptical that RelWithHint will work for large queries. How do we introduce it in a reversible way? What are the other options?
> On Oct 10, 2019, at 1:29 AM, Danny Chan <yu...@gmail.com> wrote:
>
> Hi, Julian, do you think we can make some effort to push this feature into release 1.22 ? The Flink community kind of need this feature in the near future.
>
> The most controversial part is how to transfer the hints during planning phrase, my initial idea is:
>> To copy the hints automatedly in the method RelOptRuleCall#transformTo for the new rel node from the >original rel node if both of them are all RelWithHint.
>
> I have added some simple planning test cases and it works as expected, but I’m not sure if this can fits all the corner cases(very probably not).
>
>
> The other part works great as the design doc describes.
>
> Best,
> Danny Chan
> 在 2019年9月5日 +0800 AM10:12，Julian Hyde <jh...@apache.org>，写道：
>> Thanks for continuing to push on this!
>>
>> I don’t much like the MSSQL-style syntax for table hints. It adds a new use of the WITH keyword that is unrelated to the use of WITH for common-table expressions. Instead of
>>
>> select /*+ NO_HASH_JOIN, RESOURCE(mem='128mb', parallelism='24') */
>> from
>> emp with (INDEX(idx1, idx2))
>> join
>> dept with (PROPERTIES(k1='v1', k2='v2'))
>> on
>> emp.deptno = dept.deptno
>>
>> could we do
>>
>> select /*+ NO_HASH_JOIN, RESOURCE(mem='128mb', parallelism='24') */
>> from
>> emp /*+ INDEX(idx1, idx2) */
>> join
>> dept /*+ PROPERTIES(k1='v1', k2='v2’) */
>> on
>> emp.deptno = dept.deptno
>>
>>
>> In some of the classes you have public fields of type ImmutableList. This makes it difficult to coexist in an environment that uses a different version of Guava, or shades Guava. Probably better to make them of type List. (You don’t need to change ImmutableBitSet; it’s not a Guava class.)
>>
>> There is one argument where a List<Integer> is assigned to an ImmutableBitSet. Make it an Iterable<Integer>, and people can pass in an existing ImmutableBitSet without copying.
>>
>> Julian
>>
>>
>>> On Sep 3, 2019, at 1:18 AM, Danny Chan <yu...@gmail.com> wrote:
>>>
>>> Hi, fellows, I’m here again :)
>>>
>>> This time I want to have a full discussion about CALCITE-482, which is an issue fired at 27/Nov/14 by Vladimir Sitnikov.
>>>
>>> Almost every sql vendor supports sql hint for their production version DB engines, so it would be nice if we have a framework in Calcite to support this, so that the engines that built based on CALCITE would have the ability to extend and have their custom sql hint implementations.
>>>
>>> In April this year I have fired an initial discussion about this topic[1], and I’m glad that we have some agreements for the design.
>>>
>>> Recently I have fired a PR[3] and write a design doc[2] mostly based on the discussion[1], so feel free to give some feedback here so we can make the hint framework more flexible.
>>>
>>> Any suggestions are appreciated.
>>>
>>>
>>> [1] https://lists.apache.org/list.html?dev@calcite.apache.org:dfr=2019-4-1|dto=2019-4-30:How%20to%20traverse
>>> [2] https://docs.google.com/document/d/1mykz-w2t1Yw7CH6NjUWpWqCAf_6YNKxSc59gXafrNCs/edit?usp=sharing
>>> [3] https://github.com/apache/calcite/pull/1354
>>>
>>> Best,
>>> Danny Chan
>>
Re: [DISCUSS] Support Sql Hint for Calcite
Posted by Danny Chan <yu...@gmail.com>.
Hi, Julian, do you think we can make some effort to push this feature into release 1.22 ? The Flink community kind of need this feature in the near future.
The most controversial part is how to transfer the hints during planning phrase, my initial idea is:
>To copy the hints automatedly in the method RelOptRuleCall#transformTo for the new rel node from the >original rel node if both of them are all RelWithHint.
I have added some simple planning test cases and it works as expected, but I’m not sure if this can fits all the corner cases(very probably not).
The other part works great as the design doc describes.
Best,
Danny Chan
在 2019年9月5日 +0800 AM10:12，Julian Hyde <jh...@apache.org>，写道：
> Thanks for continuing to push on this!
>
> I don’t much like the MSSQL-style syntax for table hints. It adds a new use of the WITH keyword that is unrelated to the use of WITH for common-table expressions. Instead of
>
> select /*+ NO_HASH_JOIN, RESOURCE(mem='128mb', parallelism='24') */
> from
> emp with (INDEX(idx1, idx2))
> join
> dept with (PROPERTIES(k1='v1', k2='v2'))
> on
> emp.deptno = dept.deptno
>
> could we do
>
> select /*+ NO_HASH_JOIN, RESOURCE(mem='128mb', parallelism='24') */
> from
> emp /*+ INDEX(idx1, idx2) */
> join
> dept /*+ PROPERTIES(k1='v1', k2='v2’) */
> on
> emp.deptno = dept.deptno
>
>
> In some of the classes you have public fields of type ImmutableList. This makes it difficult to coexist in an environment that uses a different version of Guava, or shades Guava. Probably better to make them of type List. (You don’t need to change ImmutableBitSet; it’s not a Guava class.)
>
> There is one argument where a List<Integer> is assigned to an ImmutableBitSet. Make it an Iterable<Integer>, and people can pass in an existing ImmutableBitSet without copying.
>
> Julian
>
>
> > On Sep 3, 2019, at 1:18 AM, Danny Chan <yu...@gmail.com> wrote:
> >
> > Hi, fellows, I’m here again :)
> >
> > This time I want to have a full discussion about CALCITE-482, which is an issue fired at 27/Nov/14 by Vladimir Sitnikov.
> >
> > Almost every sql vendor supports sql hint for their production version DB engines, so it would be nice if we have a framework in Calcite to support this, so that the engines that built based on CALCITE would have the ability to extend and have their custom sql hint implementations.
> >
> > In April this year I have fired an initial discussion about this topic[1], and I’m glad that we have some agreements for the design.
> >
> > Recently I have fired a PR[3] and write a design doc[2] mostly based on the discussion[1], so feel free to give some feedback here so we can make the hint framework more flexible.
> >
> > Any suggestions are appreciated.
> >
> >
> > [1] https://lists.apache.org/list.html?dev@calcite.apache.org:dfr=2019-4-1|dto=2019-4-30:How%20to%20traverse
> > [2] https://docs.google.com/document/d/1mykz-w2t1Yw7CH6NjUWpWqCAf_6YNKxSc59gXafrNCs/edit?usp=sharing
> > [3] https://github.com/apache/calcite/pull/1354
> >
> > Best,
> > Danny Chan
>
Re: [DISCUSS] Support Sql Hint for Calcite
Posted by Danny Chan <yu...@gmail.com>.
Vladimir, really thanks for providing these practical hints.
I’m not planning to add any hints implementation in the first PR, I just want to add a framework to let us add a hint implementation conveniently. I would very appreciate for it if you can give some suggestions to make the framework more flexible.
Best,
Danny Chan
在 2019年9月5日 +0800 PM8:42，dev@calcite.apache.org，写道：
>
> Vladimir
Re: [DISCUSS] Support Sql Hint for Calcite
Posted by Vladimir Sitnikov <si...@gmail.com>.
Frankly speaking I would refrain from adding `table WITH ...`
It is not clear if you allow to add hints at subquery level. I think we
should allow that.
For instance, https://github.com/ossc-db/pg_hint_plan allows top-level
hints only, and that is really hard to use when SQL is build dynamically.
In my experience (it comes mostly from OracleDB), the most important/often
used hints are as follows:
* no_merge
select * from (select /*+ no_merge*/ from ...) subquery, emp e where
subquery.id=42
The hint makes optimizer treat braces that surround no_merge subquery as
strict, so it does not bring new tables inside, and it does not allow for
the tables to escape.
However optimizer is allowed to push predicates (e.g. subquery.id=42 can be
pushed to subquery)
^^^ this is very important hint. And in PostgreSQL world they often use
OFFSET 0 or something like that.
* leading(table_name_a, table_name_b, table_name_c) to specify which tables
should be used for staring the query.
* index(table_name index_name)
* cardinality(table_name 1000) to augment cardinality estimations. This is
especially handy for cases like table(...) when optimizer just can't
estimate properly.
* use_nl / use_hash to specify nested loops vs hash join
* resource management hints like parallel(table, 8).
Note: the exact hint names can vary, however it would be nice if we could
capture and express the above intentions.
I'm not sure if there are legal issues with use of Oracle-named hints,
however they seem to be quite popular in the wild.
PS I'm really glad the topic is pushed forward, however I can't contribute
much at this point :-/
Vladimir
Re: [DISCUSS] Support Sql Hint for Calcite
Posted by Danny Chan <yu...@gmail.com>.
Julian, thanks so much for providing the reference and the historical note.
I have no strong objections for the table hints format you proposed, overall, the grammar look more unified and concise.
Also many thanks for the suggestions of the code.
Best,
Danny Chan
在 2019年9月5日 +0800 AM10:43，Julian Hyde <jh...@apache.org>，写道：
> A historical note. Microsoft SQL Server inherited its hint syntax from Sybase a very long time ago. (See “Transact SQL Programming”[1], page 632, “Optimizer hints”. The book was written in 1999, and covers Microsoft SQL Server 6.5 / 7.0 and Sybase Adaptive Server 11.5, but the syntax very likely predates Sybase 4.3, from which Microsoft SQL Server was forked in 1993.)
>
> Microsoft later added the WITH keyword to make it less ambiguous, and has now deprecated the syntax that does not use WITH.
>
> They are forced to keep the syntax for backwards compatibility but that doesn’t mean that we should shoulder their burden.
>
> I think formatted comments are the right container for hints because it allows us to change the hint syntax without changing the SQL parser, and makes clear that we are at liberty to ignore hints entirely.
>
> Julian
>
> [1] https://www.amazon.com/s?k=9781565924017 <https://www.amazon.com/s?k=9781565924017>
>
> > On Sep 4, 2019, at 7:12 PM, Julian Hyde <jh...@apache.org> wrote:
> >
> > Thanks for continuing to push on this!
> >
> > I don’t much like the MSSQL-style syntax for table hints. It adds a new use of the WITH keyword that is unrelated to the use of WITH for common-table expressions. Instead of
> >
> > select /*+ NO_HASH_JOIN, RESOURCE(mem='128mb', parallelism='24') */
> > from
> > emp with (INDEX(idx1, idx2))
> > join
> > dept with (PROPERTIES(k1='v1', k2='v2'))
> > on
> > emp.deptno = dept.deptno
> >
> > could we do
> >
> > select /*+ NO_HASH_JOIN, RESOURCE(mem='128mb', parallelism='24') */
> > from
> > emp /*+ INDEX(idx1, idx2) */
> > join
> > dept /*+ PROPERTIES(k1='v1', k2='v2’) */
> > on
> > emp.deptno = dept.deptno
> >
> >
> > In some of the classes you have public fields of type ImmutableList. This makes it difficult to coexist in an environment that uses a different version of Guava, or shades Guava. Probably better to make them of type List. (You don’t need to change ImmutableBitSet; it’s not a Guava class.)
> >
> > There is one argument where a List<Integer> is assigned to an ImmutableBitSet. Make it an Iterable<Integer>, and people can pass in an existing ImmutableBitSet without copying.
> >
> > Julian
> >
> >
> > > On Sep 3, 2019, at 1:18 AM, Danny Chan <yu...@gmail.com> wrote:
> > >
> > > Hi, fellows, I’m here again :)
> > >
> > > This time I want to have a full discussion about CALCITE-482, which is an issue fired at 27/Nov/14 by Vladimir Sitnikov.
> > >
> > > Almost every sql vendor supports sql hint for their production version DB engines, so it would be nice if we have a framework in Calcite to support this, so that the engines that built based on CALCITE would have the ability to extend and have their custom sql hint implementations.
> > >
> > > In April this year I have fired an initial discussion about this topic[1], and I’m glad that we have some agreements for the design.
> > >
> > > Recently I have fired a PR[3] and write a design doc[2] mostly based on the discussion[1], so feel free to give some feedback here so we can make the hint framework more flexible.
> > >
> > > Any suggestions are appreciated.
> > >
> > >
> > > [1] https://lists.apache.org/list.html?dev@calcite.apache.org:dfr=2019-4-1|dto=2019-4-30:How%20to%20traverse
> > > [2] https://docs.google.com/document/d/1mykz-w2t1Yw7CH6NjUWpWqCAf_6YNKxSc59gXafrNCs/edit?usp=sharing
> > > [3] https://github.com/apache/calcite/pull/1354
> > >
> > > Best,
> > > Danny Chan
> >
>
Re: [DISCUSS] Support Sql Hint for Calcite
Posted by Julian Hyde <jh...@apache.org>.
A historical note. Microsoft SQL Server inherited its hint syntax from Sybase a very long time ago. (See “Transact SQL Programming”[1], page 632, “Optimizer hints”. The book was written in 1999, and covers Microsoft SQL Server 6.5 / 7.0 and Sybase Adaptive Server 11.5, but the syntax very likely predates Sybase 4.3, from which Microsoft SQL Server was forked in 1993.)
Microsoft later added the WITH keyword to make it less ambiguous, and has now deprecated the syntax that does not use WITH.
They are forced to keep the syntax for backwards compatibility but that doesn’t mean that we should shoulder their burden.
I think formatted comments are the right container for hints because it allows us to change the hint syntax without changing the SQL parser, and makes clear that we are at liberty to ignore hints entirely.
Julian
[1] https://www.amazon.com/s?k=9781565924017 <https://www.amazon.com/s?k=9781565924017>
> On Sep 4, 2019, at 7:12 PM, Julian Hyde <jh...@apache.org> wrote:
>
> Thanks for continuing to push on this!
>
> I don’t much like the MSSQL-style syntax for table hints. It adds a new use of the WITH keyword that is unrelated to the use of WITH for common-table expressions. Instead of
>
> select /*+ NO_HASH_JOIN, RESOURCE(mem='128mb', parallelism='24') */
> from
> emp with (INDEX(idx1, idx2))
> join
> dept with (PROPERTIES(k1='v1', k2='v2'))
> on
> emp.deptno = dept.deptno
>
> could we do
>
> select /*+ NO_HASH_JOIN, RESOURCE(mem='128mb', parallelism='24') */
> from
> emp /*+ INDEX(idx1, idx2) */
> join
> dept /*+ PROPERTIES(k1='v1', k2='v2’) */
> on
> emp.deptno = dept.deptno
>
>
> In some of the classes you have public fields of type ImmutableList. This makes it difficult to coexist in an environment that uses a different version of Guava, or shades Guava. Probably better to make them of type List. (You don’t need to change ImmutableBitSet; it’s not a Guava class.)
>
> There is one argument where a List<Integer> is assigned to an ImmutableBitSet. Make it an Iterable<Integer>, and people can pass in an existing ImmutableBitSet without copying.
>
> Julian
>
>
>> On Sep 3, 2019, at 1:18 AM, Danny Chan <yu...@gmail.com> wrote:
>>
>> Hi, fellows, I’m here again :)
>>
>> This time I want to have a full discussion about CALCITE-482, which is an issue fired at 27/Nov/14 by Vladimir Sitnikov.
>>
>> Almost every sql vendor supports sql hint for their production version DB engines, so it would be nice if we have a framework in Calcite to support this, so that the engines that built based on CALCITE would have the ability to extend and have their custom sql hint implementations.
>>
>> In April this year I have fired an initial discussion about this topic[1], and I’m glad that we have some agreements for the design.
>>
>> Recently I have fired a PR[3] and write a design doc[2] mostly based on the discussion[1], so feel free to give some feedback here so we can make the hint framework more flexible.
>>
>> Any suggestions are appreciated.
>>
>>
>> [1] https://lists.apache.org/list.html?dev@calcite.apache.org:dfr=2019-4-1|dto=2019-4-30:How%20to%20traverse
>> [2] https://docs.google.com/document/d/1mykz-w2t1Yw7CH6NjUWpWqCAf_6YNKxSc59gXafrNCs/edit?usp=sharing
>> [3] https://github.com/apache/calcite/pull/1354
>>
>> Best,
>> Danny Chan
>
Re: [DISCUSS] Support Sql Hint for Calcite
Posted by Julian Hyde <jh...@apache.org>.
Thanks for continuing to push on this!
I don’t much like the MSSQL-style syntax for table hints. It adds a new use of the WITH keyword that is unrelated to the use of WITH for common-table expressions. Instead of
select /*+ NO_HASH_JOIN, RESOURCE(mem='128mb', parallelism='24') */
from
emp with (INDEX(idx1, idx2))
join
dept with (PROPERTIES(k1='v1', k2='v2'))
on
emp.deptno = dept.deptno
could we do
select /*+ NO_HASH_JOIN, RESOURCE(mem='128mb', parallelism='24') */
from
emp /*+ INDEX(idx1, idx2) */
join
dept /*+ PROPERTIES(k1='v1', k2='v2’) */
on
emp.deptno = dept.deptno
In some of the classes you have public fields of type ImmutableList. This makes it difficult to coexist in an environment that uses a different version of Guava, or shades Guava. Probably better to make them of type List. (You don’t need to change ImmutableBitSet; it’s not a Guava class.)
There is one argument where a List<Integer> is assigned to an ImmutableBitSet. Make it an Iterable<Integer>, and people can pass in an existing ImmutableBitSet without copying.
Julian
> On Sep 3, 2019, at 1:18 AM, Danny Chan <yu...@gmail.com> wrote:
>
> Hi, fellows, I’m here again :)
>
> This time I want to have a full discussion about CALCITE-482, which is an issue fired at 27/Nov/14 by Vladimir Sitnikov.
>
> Almost every sql vendor supports sql hint for their production version DB engines, so it would be nice if we have a framework in Calcite to support this, so that the engines that built based on CALCITE would have the ability to extend and have their custom sql hint implementations.
>
> In April this year I have fired an initial discussion about this topic[1], and I’m glad that we have some agreements for the design.
>
> Recently I have fired a PR[3] and write a design doc[2] mostly based on the discussion[1], so feel free to give some feedback here so we can make the hint framework more flexible.
>
> Any suggestions are appreciated.
>
>
> [1] https://lists.apache.org/list.html?dev@calcite.apache.org:dfr=2019-4-1|dto=2019-4-30:How%20to%20traverse
> [2] https://docs.google.com/document/d/1mykz-w2t1Yw7CH6NjUWpWqCAf_6YNKxSc59gXafrNCs/edit?usp=sharing
> [3] https://github.com/apache/calcite/pull/1354
>
> Best,
> Danny Chan