Re: [DISCUSS] Towards Cascades Optimizer

[Jinpeng Wu]

Hi, Xiening.

Regarding calculating the logical cost, here are some ways I though:
1. Logical rel may implement their own computeSelfCost method. Some
rels can provide such information, for example the
LogicalProject/LogicalFilter contains nearly the same information as their
physical implementations. If we don't have enough confidence, just return
zeroCost is also OK, as it only affects pruning.
2. Logical rel  tells its parents what its physical input could be after
implementation. Then the problem come back to calculating lower bound of a
physical rel.
There should always be ways. The only problem is how to find a pretty one.

Regarding the risk, new planner do have different risk. It is not because
new planner could stop us doing something wrong but we can decide when to
use the new one. Some scenarios:
1. If modifying the VolcanoPlanner directly, the only way user could
control the risk is not to upgrade calcite version until it is considered
stable. You know, it is quite different from keeping calcite updated and
switching to the new planner at a proper time.
2. It is very importance for SLA control. For the important business and
jobs, we may keep using the old and stable planner. And use the new one
only for jobs that have fault tolerance. And this helps testing new planner
with actual scenarios.
3. It is helpful when upgrading online services. When the new planner
happened to have some bugs, we can switch to the old planner directly
without rollback the whole service.
4. With all these ways to prevent issues becoming disasters, we are not
vulnerable to making mistakes. This not only enables faster iterations but
also let us have enough time to resolve big bugs, like considering it in
detail and applying a time-consuming refactoring for it. To work around a
critical bug using tricky ways usually introduces more issues.

Thanks,
Jinpeng

On Tue, Apr 21, 2020 at 2:04 AM Xiening Dai  wrote:

> Hi Jinpeng,
>
> Regarding this comment - I believe there are ways to calculate the logical
> cost, but I think it’s not that simple as  "cardinality * unit_copy_cost.”,
> would  you provide more details of other different ways? Just the algorithm
> description or pseudo code would help us understand. Thanks.
>
> Regarding the approach of creating new planner, I don’t think a new
> planner would lower the risk. We don’t know what we don’t know. If we
> introduced an issue while modifying the planner, most likely we would do
> the same with new planner class. A new planner doesn’t necessarily prevent
> the issue from happening, but just delay its surfacing, which is worse IMO.
>
> There’s one obvious benefit with new planner is that we can provide some
> sort of isolation so the change won’t cause test baseline updates, which
> could be painful at times.  We should see if we could use planner config to
> achieve the same if we decide to just modify the current planner.
>
>
> > On Apr 20, 2020, at 8:32 AM, Haisheng Yuan  wrote:
> >
> > Igor,
> >
> > That's great.
> >
> > On 2020/04/20 11:17:49, Seliverstov Igor  wrote:
> >> Haisheng, Xiening,
> >>
> >> Ok, Now I see how it should work.
> >>
> >> Thanks for your replies.
> >>
> >> Regards,
> >> Igor
> >>
> >>> 20 апр. 2020 г., в 09:56, Seliverstov Igor 
> написал(а):
> >>>
> >>> Haisheng, Xiening,
> >>>
> >>> Thanks for clarifying.
> >>>
> >>> In this proposal, we are not trying to split logical and physical
> planning entirely. - actually I was in doubt about an idea of entire
> splitting logical and physical phases, if you aren't going to, I have no
> objections.
> >>>
> >>> But it returns me to my first question: how we will propagate traits
> in bottom-up manner using proposed approach. (See [DISCUSS] Proposal to add
> API to force rules matching specific rels for details)
> >>>
> >>> One of inconveniences of current VolcanoPlanner implementation is
> amount of tricks that we need to get desired behaviour. It would be great
> if some of issues (or all of them) were solved in the new approach.
> >>>
> >>> Regards,
> >>> Igor
> >>>
> >>> пн, 20 апр. 2020 г., 7:02 Xiening Dai  xndai@gmail.com>>:
> >>> Hi Igor,
> >>>
> >>> Your comment - "because actual cost may be calculated correctly using
> physical operators only. So won't be able to implement Branch and Bound
> Space Pruning.“ is actually not true. In Cascade’s lower bound / upper
> bound pruning algorithm, you can get cost lower bound of input RelNode
> using cardinality * unit_copy_cost. The unit_copy_cost is a constant, which
> stands for the minimal cost for processing a tuple from the input. So this
> will be the minimal cost the input RelNode can achieve, and if this is
> indeed larger than the current best cost, this input node can be pruned.
> >>>
> >>> In this proposal, we are not trying to split logical and physical
> planning entirely. But for any given equivalent set, we would need to
> finish exploration before implementation. But for the entire memo tree,
> each set could be in different 

Re: RelMetadataQuery.getRowCount stackoverflow

[Haisheng Yuan]

Can you add a reproducible test case and log a JIRA? It may be different with 
CALCITE-2057. People who is interested will investigate the issue.

On 2020/04/21 04:24:31, JiaTao Tao  wrote: 
> Thanks
> I didn't add any new rule, just these:
> 
> CONSTANT_REDUCTION_RULES
> ABSTRACT_RELATIONAL_RULES
> BASE_RULES
> ABSTRACT_RULES
> ENUMERABLE_RULES
> 
> So this is a bug, or it just because of the call stack is too deep(if this,
> I can adjust JVM parameter).
> 
> Regards!
> 
> Aron Tao
> 
> 
> Scott Reynolds  于2020年4月21日周二 上午1:10写道：
> 
> > I have had this happen numerous times when writing new planner rules. Most
> > of the time my rule is missing some boolean logic to prevent itself from
> > transforming the call. This results in the rule continuously transforming
> > it's previous transformations.
> >
> > I can usually see this happening when I add a
> > System.out.println(RelOptUtil.dumpPlan()) to the line before the
> > call.transformTo(newRelationNode)
> >
> > On Mon, Apr 20, 2020 at 3:13 AM JiaTao Tao  wrote:
> >
> > > Hi
> > > Has anyone encountered this problem before? Just a simple query(no more
> > > than 20 lines, two joins, no union).
> > >
> > > And I see this ticket:
> > https://issues.apache.org/jira/browse/CALCITE-2057,
> > > but there's no follow up, also I see flink may occur this problem(
> > > https://developer.aliyun.com/ask/129548)
> > >
> > > java.lang.StackOverflowError
> > > at java.util.HashMap.hash(HashMap.java:339)
> > > at java.util.HashMap.put(HashMap.java:612)
> > > at
> > >
> > com.google.common.collect.StandardTable.getOrCreate(StandardTable.java:165)
> > > at
> > com.google.common.collect.StandardTable.put(StandardTable.java:174)
> > > at
> > com.google.common.collect.HashBasedTable.put(HashBasedTable.java:55)
> > > at GeneratedMetadataHandler_RowCount.getRowCount(Unknown Source)
> > > at
> > >
> > org.apache.calcite.rel.metadata.RelMetadataQuery.getRowCount(RelMetadataQuery.java:208)
> > > at
> > >
> > org.apache.calcite.rel.metadata.RelMdRowCount.getRowCount(RelMdRowCount.java:72)
> > > at GeneratedMetadataHandler_RowCount.getRowCount_$(Unknown Source)
> > > at GeneratedMetadataHandler_RowCount.getRowCount(Unknown Source)
> > > at ...
> > >
> > > Regards!
> > >
> > > Aron Tao
> > >
> >
> 

Re: RelMetadataQuery.getRowCount stackoverflow

[JiaTao Tao]

Thanks
I didn't add any new rule, just these:

CONSTANT_REDUCTION_RULES
ABSTRACT_RELATIONAL_RULES
BASE_RULES
ABSTRACT_RULES
ENUMERABLE_RULES

So this is a bug, or it just because of the call stack is too deep(if this,
I can adjust JVM parameter).

Regards!

Aron Tao


Scott Reynolds  于2020年4月21日周二 上午1:10写道：

> I have had this happen numerous times when writing new planner rules. Most
> of the time my rule is missing some boolean logic to prevent itself from
> transforming the call. This results in the rule continuously transforming
> it's previous transformations.
>
> I can usually see this happening when I add a
> System.out.println(RelOptUtil.dumpPlan()) to the line before the
> call.transformTo(newRelationNode)
>
> On Mon, Apr 20, 2020 at 3:13 AM JiaTao Tao  wrote:
>
> > Hi
> > Has anyone encountered this problem before? Just a simple query(no more
> > than 20 lines, two joins, no union).
> >
> > And I see this ticket:
> https://issues.apache.org/jira/browse/CALCITE-2057,
> > but there's no follow up, also I see flink may occur this problem(
> > https://developer.aliyun.com/ask/129548)
> >
> > java.lang.StackOverflowError
> > at java.util.HashMap.hash(HashMap.java:339)
> > at java.util.HashMap.put(HashMap.java:612)
> > at
> >
> com.google.common.collect.StandardTable.getOrCreate(StandardTable.java:165)
> > at
> com.google.common.collect.StandardTable.put(StandardTable.java:174)
> > at
> com.google.common.collect.HashBasedTable.put(HashBasedTable.java:55)
> > at GeneratedMetadataHandler_RowCount.getRowCount(Unknown Source)
> > at
> >
> org.apache.calcite.rel.metadata.RelMetadataQuery.getRowCount(RelMetadataQuery.java:208)
> > at
> >
> org.apache.calcite.rel.metadata.RelMdRowCount.getRowCount(RelMdRowCount.java:72)
> > at GeneratedMetadataHandler_RowCount.getRowCount_$(Unknown Source)
> > at GeneratedMetadataHandler_RowCount.getRowCount(Unknown Source)
> > at ...
> >
> > Regards!
> >
> > Aron Tao
> >
>

Calcite-Master - Build # 1708 - Still Failing

[Apache Jenkins Server]

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

Status: Still Failing

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

Understanding annotations of SqlGroupingFunction

[ZZY]

Hi, Hyde:
It's confused me that some annotations in
Calcite(org.apache.calcite.sql.fun.SqlGroupingFunction.java) :
/**
 * The {@code GROUPING} function.
 *
 * Accepts 1 or more arguments.
 * Example: {@code GROUPING(deptno, gender)} returns
 * 3 if both deptno and gender are being grouped,
 * 2 if only deptno is being grouped,
 * 1 if only gender is being groped,
 * 0 if neither deptno nor gender are being grouped.
 *
 * This function is defined in the SQL standard.
 * {@code GROUPING_ID} is a non-standard synonym.
 *
 * Some examples are in {@code agg.iq}.
 */

The annotations above seems conflicts with other implementations like Hive(
https://cwiki.apache.org/confluence/display/Hive/Enhanced+Aggregation%2C+Cube%2C+Grouping+and+Rollup?spm=ata.13261165.0.0.528c6dfcXalQFy#EnhancedAggregation,Cube,GroupingandRollup-Groupingfunction
)

Notice that: "The grouping function indicates whether an expression in a
GROUP BY clause is aggregated or not for a given row. The value 0
represents a column that is part of the grouping set, while the value 1
represents a column that is not part of the grouping set. "


It is clearly that 0 and 1 bit have different interpretation  between
annotations in Calcite and in Hive. And I did not figure out why...

Any feedback can give me on this would be highly appreciated.

Best regards!

Re: [DISCUSS] Towards Cascades Optimizer

[Haisheng Yuan]

Hi Hanumath,

The trait in the example is for distribution only for brevity, not including 
collation. No matter it is hash join or merge join or nestedloop join, the same 
distribution applied.

> Are you planning to use the same interface as that of VolcanoPlanner?
Yes, not only for compatibility, but also because the design of RelNode and 
Rule plays a huge impact on how planner can be refactored. They are integrated 
components, not really separable. I have no choice.

> The concern about the optimizer can take up huge time for optimizing large 
> queries is a genuine. 
Because very few efforts were spent on improving speed of the core framework 
for the last 10 years. Most of queries(MV putting side), as long as there is no 
dynamic programming style join reordering, should be optimized in a reasonable 
span of time, otherwise it is a **bug**.  Alibaba MaxCompute processed millions 
of queries per day, they are mission-critical and time-critical, all have 
optimization and execution time limit. We spent a lot of efforts on improving 
optimization latency, all the changes we made on Calcite will be battlefield 
tested. The inefficiency issue caused by inefficient search strategy and lack 
of trait propagation mechanism, that is experienced by other Calcite based 
systems, will be resolved by this proposal. If you genuinely want 
iteration-based optimization, you will be disappointed. Because even Cascades' 
first adopter SQL Server, doesn't provide iteration-based optimization, instead 
it has a phase with very limited optimization rules for transactional 
processing. You can't have your cake and eat it.

I am inclined to modify on VolcanoPlanner. A separate new planner won't make it 
move fast and lower online production risk. No body knows the pain on 
maintaining 2 optimizers more than me. Ask Greenplum database developers how 
they feel about working on Orca and Postres planner.

Haisheng

On 2020/04/20 18:07:06, hanu mapr  wrote: 
> Hello Haisheng,
> 
> 
> Thanks for the detailed analysis on the support for cascades framework. I
> am quite interested to be part of the new optimization framework. I believe
> this a very important infrastructural work to make calcite a robust query
> optimizer.
> 
> 
> I like your approach on the trait propagation and derivation of the traits
> from child nodes. I have a question though in the example you provided.
> Shouldn't HashJoin make more sense in place of MergeJoin as it can
> passThrough the traits like Hash whereas MergeJoin needs the sorted
> traits.(Please correct me if I am missing anything here).
> 
> 
> How are you imagining the new Optimizer interface from user point of view.
> Are you planning to use the same interface as that of VolcanoPlanner?
> 
> 
> The concern about the optimizer can take up huge time for optimizing large
> queries is a genuine. At the time when I was part of writing an optimizer
> for a federated engine, I also dealt with it by making the search
> time-bound. As mentioned even iteration based approach might be viable but
> I think a little more thought might be needed to finalize.
> 
> 
> I am of the opinion that option b add a new Planner might be the right
> approach. We can continuously work on the new optimizer and make it robust
> so that switching to the new optimizer can be seamless and can be
> controlled by the user. From my experience any bug in the optimizer is
> quite subtle and can have high magnitude degradation in performance, so
> tweaking the existing VolcanoOptimizer can be risky.
> 
> 
> Thanks,
> 
> -Hanumath Maduri
> 
> On Mon, Apr 20, 2020 at 11:04 AM Xiening Dai  wrote:
> 
> > Hi Jinpeng,
> >
> > Regarding this comment - I believe there are ways to calculate the logical
> > cost, but I think it’s not that simple as  "cardinality * unit_copy_cost.”,
> > would  you provide more details of other different ways? Just the algorithm
> > description or pseudo code would help us understand. Thanks.
> >
> > Regarding the approach of creating new planner, I don’t think a new
> > planner would lower the risk. We don’t know what we don’t know. If we
> > introduced an issue while modifying the planner, most likely we would do
> > the same with new planner class. A new planner doesn’t necessarily prevent
> > the issue from happening, but just delay its surfacing, which is worse IMO.
> >
> > There’s one obvious benefit with new planner is that we can provide some
> > sort of isolation so the change won’t cause test baseline updates, which
> > could be painful at times.  We should see if we could use planner config to
> > achieve the same if we decide to just modify the current planner.
> >
> >
> > > On Apr 20, 2020, at 8:32 AM, Haisheng Yuan  wrote:
> > >
> > > Igor,
> > >
> > > That's great.
> > >
> > > On 2020/04/20 11:17:49, Seliverstov Igor  wrote:
> > >> Haisheng, Xiening,
> > >>
> > >> Ok, Now I see how it should work.
> > >>
> > >> Thanks for your replies.
> > >>
> > >> Regards,
> > >> Igor
> > >>
> > >>> 

[jira] [Created] (CALCITE-3941) Add the default mode to the path in the Json functions.

[Forward Xu (Jira)]

Forward Xu created CALCITE-3941:
---

 Summary: Add the default mode to the path in the Json functions.
 Key: CALCITE-3941
 URL: https://issues.apache.org/jira/browse/CALCITE-3941
 Project: Calcite
  Issue Type: Improvement
  Components: core
Reporter: Forward Xu
Assignee: Forward Xu


Add the default mode to the path in the JSON functions.

before:

json_exists ('\{"foo": "bar"}', 'lax $ .foo') or

json_exists ('\{"foo": "bar"}', 'strict $ .foo')

after:

json_exists ('\{"foo": "bar"}', '$ .foo')



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

Re: [discuss] Add the default mode to the path in the Json functions.

[Forward Xu]

Hi Julian,
Thank you for bringing such a good shared link, there are some areas in the
JSON function that need to be improved and implemented. I will continue to
improve it.

Forward

Julian Hyde  于2020年4月21日周二 上午1:28写道：

> Speaking of JSON functions, JOOQ creator Lukas Eder has been giving JSON
> functions in MySQL/MariaDB a good workout over the last few days. It’s
> amusing to read what he has discovered: https://twitter.com/lukaseder <
> https://twitter.com/lukaseder>
>
> Julian
>
>
> > On Apr 20, 2020, at 8:27 AM, Stamatis Zampetakis 
> wrote:
> >
> > Hi,
> >
> > I know that Oracle uses lax by default [1] but I don't remember what
> other
> > DBMS do.
> >
> > In any case adopting a default mode sounds like a reasonable thing to do.
> >
> > Best,
> > Stamatis
> >
> > [1] https://docs.oracle.com/database/121/ADXDB/json.htm#ADXDB6259
> >
> >
> > On Mon, Apr 20, 2020, 7:28 AM Chunwei Lei 
> wrote:
> >
> >> Thank you for proposing this, Forward.
> >>
> >> I am wondering whether it is really useful for users who want to use
> these
> >> functions
> >> since they probably know what the syntax is. But personally I would
> like to
> >> have the
> >> default mode since it is more convenient.
> >>
> >>
> >> Best,
> >> Chunwei
> >>
> >>
> >> On Sun, Apr 19, 2020 at 9:51 AM Forward Xu 
> wrote:
> >>
> >>> hi everyone, I recently found some discussable optimizations when I
> >>> contributed the flip-90 [1] Json functions:
> >>> The current json functions in calcite: JSON_EXISTS, JSON_VALUE,
> >> JSON_QUERY,
> >>> JSON_OBJECT, JSON_OBJECTAGG, JSON_ARRAY, JSON_ARRAYAGG and IS JSON
> >>> predication functions. These functions are implemented based on the SQL
> >>> 2016-2017 standard [2]. According to this standard, the path of the
> json
> >>> function must be used in one of strict or lax mode. such as:
> >>> json_exists ('{"foo": "bar"}', 'lax $ .foo') or
> >>> json_exists ('{"foo": "bar"}', 'strict $ .foo')
> >>> Can we give a default mode to simplify the use of lax and strict. For
> >>> example, we default to lax mode. In this way, the use of our json
> >> function
> >>> can be simplified to:
> >>> json_exists ('{"foo": "bar"}', '$ .foo')
> >>> Implementation idea improvement JsonFunctions jsonApiCommonSyntax path
> >>> judgment to increase the default lax mode logic.
> >>> Of course, these changes are not described in SQL2016-2017.
> >>> I want to hear your opinion here.
> >>>
> >>> [1]
> >>>
> >>
> https://cwiki.apache.org/confluence/pages/viewpage.action?pageId=141724550
> >>> [2]
> >>>
> >>>
> >>
> https://standards.iso.org/ittf/PubliclyAvailableStandards/c067367_ISO_IEC_TR_19075-6_2017.zip
> >>>
> >>
>
>

Re: Stored Proc to Relational Expression

[Julian Hyde]

Calcite relational expressions can represent SELECT, INSERT etc. but not 
procedural code. It’s a direction we could consider going.

RexProgram is the closest thing we currently have to procedural code in the 
algebra - single assignment of variables, use of variables in expressions 
assigning to other variables - but it is a long way short because there are no 
loops.

> On Apr 19, 2020, at 12:47 PM, Ravi Kapoor  wrote:
> 
> Hi Team,
> 
> I have my use where I need to convert my dialect specific stored procedure
> constructs like while loop, If then else to Rel expression
> 
> Basically this can contain control flow statements like below
> 
> DECLARE heads BOOL;
> DECLARE heads_count INT64 DEFAULT 0;
> LOOP
>  SET heads = RAND() < 0.5;
>  IF heads THEN
>SELECT 'Heads!';
>SET heads_count = heads_count + 1;
>  ELSE
>SELECT 'Tails!';
>BREAK;
>  END IF;
> END LOOP;
> SELECT CONCAT(CAST(heads_count AS STRING), ' heads in a row');
> 
> 
> I can create a Java AST model from the linq4j provided by calcite however
> this is only going to generate Java Result and I believe its only used by
> the calcite for relational expressions of enumerable calling convention
> which is used by adapters which does not support core relational operations
> right?
> 
> Is there a way I can convert the stored proc constructs into some canonical
> form like Rel Tree and back to Stored proc of target dialect.
> -- 
> 
> Thanks,
> Ravi

Re: [DISCUSS] Towards Cascades Optimizer

[hanu mapr]

Hello Haisheng,


Thanks for the detailed analysis on the support for cascades framework. I
am quite interested to be part of the new optimization framework. I believe
this a very important infrastructural work to make calcite a robust query
optimizer.


I like your approach on the trait propagation and derivation of the traits
from child nodes. I have a question though in the example you provided.
Shouldn't HashJoin make more sense in place of MergeJoin as it can
passThrough the traits like Hash whereas MergeJoin needs the sorted
traits.(Please correct me if I am missing anything here).


How are you imagining the new Optimizer interface from user point of view.
Are you planning to use the same interface as that of VolcanoPlanner?


The concern about the optimizer can take up huge time for optimizing large
queries is a genuine. At the time when I was part of writing an optimizer
for a federated engine, I also dealt with it by making the search
time-bound. As mentioned even iteration based approach might be viable but
I think a little more thought might be needed to finalize.


I am of the opinion that option b add a new Planner might be the right
approach. We can continuously work on the new optimizer and make it robust
so that switching to the new optimizer can be seamless and can be
controlled by the user. From my experience any bug in the optimizer is
quite subtle and can have high magnitude degradation in performance, so
tweaking the existing VolcanoOptimizer can be risky.


Thanks,

-Hanumath Maduri

On Mon, Apr 20, 2020 at 11:04 AM Xiening Dai  wrote:

> Hi Jinpeng,
>
> Regarding this comment - I believe there are ways to calculate the logical
> cost, but I think it’s not that simple as  "cardinality * unit_copy_cost.”,
> would  you provide more details of other different ways? Just the algorithm
> description or pseudo code would help us understand. Thanks.
>
> Regarding the approach of creating new planner, I don’t think a new
> planner would lower the risk. We don’t know what we don’t know. If we
> introduced an issue while modifying the planner, most likely we would do
> the same with new planner class. A new planner doesn’t necessarily prevent
> the issue from happening, but just delay its surfacing, which is worse IMO.
>
> There’s one obvious benefit with new planner is that we can provide some
> sort of isolation so the change won’t cause test baseline updates, which
> could be painful at times.  We should see if we could use planner config to
> achieve the same if we decide to just modify the current planner.
>
>
> > On Apr 20, 2020, at 8:32 AM, Haisheng Yuan  wrote:
> >
> > Igor,
> >
> > That's great.
> >
> > On 2020/04/20 11:17:49, Seliverstov Igor  wrote:
> >> Haisheng, Xiening,
> >>
> >> Ok, Now I see how it should work.
> >>
> >> Thanks for your replies.
> >>
> >> Regards,
> >> Igor
> >>
> >>> 20 апр. 2020 г., в 09:56, Seliverstov Igor 
> написал(а):
> >>>
> >>> Haisheng, Xiening,
> >>>
> >>> Thanks for clarifying.
> >>>
> >>> In this proposal, we are not trying to split logical and physical
> planning entirely. - actually I was in doubt about an idea of entire
> splitting logical and physical phases, if you aren't going to, I have no
> objections.
> >>>
> >>> But it returns me to my first question: how we will propagate traits
> in bottom-up manner using proposed approach. (See [DISCUSS] Proposal to add
> API to force rules matching specific rels for details)
> >>>
> >>> One of inconveniences of current VolcanoPlanner implementation is
> amount of tricks that we need to get desired behaviour. It would be great
> if some of issues (or all of them) were solved in the new approach.
> >>>
> >>> Regards,
> >>> Igor
> >>>
> >>> пн, 20 апр. 2020 г., 7:02 Xiening Dai  xndai@gmail.com>>:
> >>> Hi Igor,
> >>>
> >>> Your comment - "because actual cost may be calculated correctly using
> physical operators only. So won't be able to implement Branch and Bound
> Space Pruning.“ is actually not true. In Cascade’s lower bound / upper
> bound pruning algorithm, you can get cost lower bound of input RelNode
> using cardinality * unit_copy_cost. The unit_copy_cost is a constant, which
> stands for the minimal cost for processing a tuple from the input. So this
> will be the minimal cost the input RelNode can achieve, and if this is
> indeed larger than the current best cost, this input node can be pruned.
> >>>
> >>> In this proposal, we are not trying to split logical and physical
> planning entirely. But for any given equivalent set, we would need to
> finish exploration before implementation. But for the entire memo tree,
> each set could be in different planning stage, and an equivalent set can be
> pruned even before it’s implemented. A text book example of aforementioned
> “lower bound / upper bound pruning” would be the join ordering case.
> >>>
> >>> Regarding #3, I think we can still achieve that (partially) through
> this proposal. Remember every time when we start the optimization, we 

Re: [DISCUSS] Towards Cascades Optimizer

[Xiening Dai]

Hi Jinpeng,

Regarding this comment - I believe there are ways to calculate the logical 
cost, but I think it’s not that simple as  "cardinality * unit_copy_cost.”, 
would  you provide more details of other different ways? Just the algorithm 
description or pseudo code would help us understand. Thanks.

Regarding the approach of creating new planner, I don’t think a new planner 
would lower the risk. We don’t know what we don’t know. If we introduced an 
issue while modifying the planner, most likely we would do the same with new 
planner class. A new planner doesn’t necessarily prevent the issue from 
happening, but just delay its surfacing, which is worse IMO.

There’s one obvious benefit with new planner is that we can provide some sort 
of isolation so the change won’t cause test baseline updates, which could be 
painful at times.  We should see if we could use planner config to achieve the 
same if we decide to just modify the current planner.


> On Apr 20, 2020, at 8:32 AM, Haisheng Yuan  wrote:
> 
> Igor,
> 
> That's great.
> 
> On 2020/04/20 11:17:49, Seliverstov Igor  wrote: 
>> Haisheng, Xiening,
>> 
>> Ok, Now I see how it should work.
>> 
>> Thanks for your replies.
>> 
>> Regards,
>> Igor
>> 
>>> 20 апр. 2020 г., в 09:56, Seliverstov Igor  
>>> написал(а):
>>> 
>>> Haisheng, Xiening,
>>> 
>>> Thanks for clarifying. 
>>> 
>>> In this proposal, we are not trying to split logical and physical planning 
>>> entirely. - actually I was in doubt about an idea of entire splitting 
>>> logical and physical phases, if you aren't going to, I have no objections.
>>> 
>>> But it returns me to my first question: how we will propagate traits in 
>>> bottom-up manner using proposed approach. (See [DISCUSS] Proposal to add 
>>> API to force rules matching specific rels for details)
>>> 
>>> One of inconveniences of current VolcanoPlanner implementation is amount of 
>>> tricks that we need to get desired behaviour. It would be great if some of 
>>> issues (or all of them) were solved in the new approach.
>>> 
>>> Regards,
>>> Igor
>>> 
>>> пн, 20 апр. 2020 г., 7:02 Xiening Dai >> >:
>>> Hi Igor,
>>> 
>>> Your comment - "because actual cost may be calculated correctly using 
>>> physical operators only. So won't be able to implement Branch and Bound 
>>> Space Pruning.“ is actually not true. In Cascade’s lower bound / upper 
>>> bound pruning algorithm, you can get cost lower bound of input RelNode 
>>> using cardinality * unit_copy_cost. The unit_copy_cost is a constant, which 
>>> stands for the minimal cost for processing a tuple from the input. So this 
>>> will be the minimal cost the input RelNode can achieve, and if this is 
>>> indeed larger than the current best cost, this input node can be pruned. 
>>> 
>>> In this proposal, we are not trying to split logical and physical planning 
>>> entirely. But for any given equivalent set, we would need to finish 
>>> exploration before implementation. But for the entire memo tree, each set 
>>> could be in different planning stage, and an equivalent set can be pruned 
>>> even before it’s implemented. A text book example of aforementioned “lower 
>>> bound / upper bound pruning” would be the join ordering case.
>>> 
>>> Regarding #3, I think we can still achieve that (partially) through this 
>>> proposal. Remember every time when we start the optimization, we pass down 
>>> an upper bound limit. Initially this upper bound for root is infinite - 
>>> meaning that no feasible plan is available. Every time when we find a 
>>> physical plan we update this upper bound, then start the search again. We 
>>> could stop the search when the cost is less than a pre-defined threshold - 
>>> which gives you a “good enough” plan with early termination. Still this 
>>> wouldn't avoid the logical exploration. For that, you would probably 
>>> archive through rule configurations, and avoid some expansive 
>>> transformation to keep the cost down. 
>>> 
>>> 
 On Apr 19, 2020, at 7:30 PM, Haisheng Yuan >>> > wrote:
 
 Igor,
 
 a) Given current Calcite's stats derivation strategy, mixing logical and 
 physical planning won't make it better. I hope you went through my email 
 to the end, currently operators inside a memo group don't share stats 
 info, each operator's stats may differ with the other one, and the 
 RelSubset's best node and stats may vary during optimization. So logical 
 and physical rule pruning are not safe at the moment, otherwise it almost 
 implies changing downstream project's cost model silently. 
 
 On the other hand, ensuring child group exploration task finish first will 
 make rule mutual exclusivity check possible, like the result of 
 ReduceExpressionRule won't need trigger the same rule again, The join 
 result of JoinCommuteRule won't need trigger JoinCommuteRule and 
 ReduceExpressionRule again. 
 
 More importantly, 

Re: [discuss] Add the default mode to the path in the Json functions.

[Julian Hyde]

Speaking of JSON functions, JOOQ creator Lukas Eder has been giving JSON 
functions in MySQL/MariaDB a good workout over the last few days. It’s amusing 
to read what he has discovered: https://twitter.com/lukaseder 
 

Julian


> On Apr 20, 2020, at 8:27 AM, Stamatis Zampetakis  wrote:
> 
> Hi,
> 
> I know that Oracle uses lax by default [1] but I don't remember what other
> DBMS do.
> 
> In any case adopting a default mode sounds like a reasonable thing to do.
> 
> Best,
> Stamatis
> 
> [1] https://docs.oracle.com/database/121/ADXDB/json.htm#ADXDB6259
> 
> 
> On Mon, Apr 20, 2020, 7:28 AM Chunwei Lei  wrote:
> 
>> Thank you for proposing this, Forward.
>> 
>> I am wondering whether it is really useful for users who want to use these
>> functions
>> since they probably know what the syntax is. But personally I would like to
>> have the
>> default mode since it is more convenient.
>> 
>> 
>> Best,
>> Chunwei
>> 
>> 
>> On Sun, Apr 19, 2020 at 9:51 AM Forward Xu  wrote:
>> 
>>> hi everyone, I recently found some discussable optimizations when I
>>> contributed the flip-90 [1] Json functions:
>>> The current json functions in calcite: JSON_EXISTS, JSON_VALUE,
>> JSON_QUERY,
>>> JSON_OBJECT, JSON_OBJECTAGG, JSON_ARRAY, JSON_ARRAYAGG and IS JSON
>>> predication functions. These functions are implemented based on the SQL
>>> 2016-2017 standard [2]. According to this standard, the path of the json
>>> function must be used in one of strict or lax mode. such as:
>>> json_exists ('{"foo": "bar"}', 'lax $ .foo') or
>>> json_exists ('{"foo": "bar"}', 'strict $ .foo')
>>> Can we give a default mode to simplify the use of lax and strict. For
>>> example, we default to lax mode. In this way, the use of our json
>> function
>>> can be simplified to:
>>> json_exists ('{"foo": "bar"}', '$ .foo')
>>> Implementation idea improvement JsonFunctions jsonApiCommonSyntax path
>>> judgment to increase the default lax mode logic.
>>> Of course, these changes are not described in SQL2016-2017.
>>> I want to hear your opinion here.
>>> 
>>> [1]
>>> 
>> https://cwiki.apache.org/confluence/pages/viewpage.action?pageId=141724550
>>> [2]
>>> 
>>> 
>> https://standards.iso.org/ittf/PubliclyAvailableStandards/c067367_ISO_IEC_TR_19075-6_2017.zip
>>> 
>> 

Re: RelMetadataQuery.getRowCount stackoverflow

[Scott Reynolds]

I have had this happen numerous times when writing new planner rules. Most
of the time my rule is missing some boolean logic to prevent itself from
transforming the call. This results in the rule continuously transforming
it's previous transformations.

I can usually see this happening when I add a
System.out.println(RelOptUtil.dumpPlan()) to the line before the
call.transformTo(newRelationNode)

On Mon, Apr 20, 2020 at 3:13 AM JiaTao Tao  wrote:

> Hi
> Has anyone encountered this problem before? Just a simple query(no more
> than 20 lines, two joins, no union).
>
> And I see this ticket: https://issues.apache.org/jira/browse/CALCITE-2057,
> but there's no follow up, also I see flink may occur this problem(
> https://developer.aliyun.com/ask/129548)
>
> java.lang.StackOverflowError
> at java.util.HashMap.hash(HashMap.java:339)
> at java.util.HashMap.put(HashMap.java:612)
> at
> com.google.common.collect.StandardTable.getOrCreate(StandardTable.java:165)
> at com.google.common.collect.StandardTable.put(StandardTable.java:174)
> at com.google.common.collect.HashBasedTable.put(HashBasedTable.java:55)
> at GeneratedMetadataHandler_RowCount.getRowCount(Unknown Source)
> at
> org.apache.calcite.rel.metadata.RelMetadataQuery.getRowCount(RelMetadataQuery.java:208)
> at
> org.apache.calcite.rel.metadata.RelMdRowCount.getRowCount(RelMdRowCount.java:72)
> at GeneratedMetadataHandler_RowCount.getRowCount_$(Unknown Source)
> at GeneratedMetadataHandler_RowCount.getRowCount(Unknown Source)
> at ...
>
> Regards!
>
> Aron Tao
>

Re: [DISCUSS] Towards Cascades Optimizer

[Haisheng Yuan]

Igor,

That's great.

On 2020/04/20 11:17:49, Seliverstov Igor  wrote: 
> Haisheng, Xiening,
> 
> Ok, Now I see how it should work.
> 
> Thanks for your replies.
> 
> Regards,
> Igor
> 
> > 20 апр. 2020 г., в 09:56, Seliverstov Igor  
> > написал(а):
> > 
> > Haisheng, Xiening,
> > 
> > Thanks for clarifying. 
> > 
> > In this proposal, we are not trying to split logical and physical planning 
> > entirely. - actually I was in doubt about an idea of entire splitting 
> > logical and physical phases, if you aren't going to, I have no objections.
> > 
> > But it returns me to my first question: how we will propagate traits in 
> > bottom-up manner using proposed approach. (See [DISCUSS] Proposal to add 
> > API to force rules matching specific rels for details)
> > 
> > One of inconveniences of current VolcanoPlanner implementation is amount of 
> > tricks that we need to get desired behaviour. It would be great if some of 
> > issues (or all of them) were solved in the new approach.
> > 
> > Regards,
> > Igor
> > 
> > пн, 20 апр. 2020 г., 7:02 Xiening Dai  > >:
> > Hi Igor,
> > 
> > Your comment - "because actual cost may be calculated correctly using 
> > physical operators only. So won't be able to implement Branch and Bound 
> > Space Pruning.“ is actually not true. In Cascade’s lower bound / upper 
> > bound pruning algorithm, you can get cost lower bound of input RelNode 
> > using cardinality * unit_copy_cost. The unit_copy_cost is a constant, which 
> > stands for the minimal cost for processing a tuple from the input. So this 
> > will be the minimal cost the input RelNode can achieve, and if this is 
> > indeed larger than the current best cost, this input node can be pruned. 
> > 
> > In this proposal, we are not trying to split logical and physical planning 
> > entirely. But for any given equivalent set, we would need to finish 
> > exploration before implementation. But for the entire memo tree, each set 
> > could be in different planning stage, and an equivalent set can be pruned 
> > even before it’s implemented. A text book example of aforementioned “lower 
> > bound / upper bound pruning” would be the join ordering case.
> > 
> > Regarding #3, I think we can still achieve that (partially) through this 
> > proposal. Remember every time when we start the optimization, we pass down 
> > an upper bound limit. Initially this upper bound for root is infinite - 
> > meaning that no feasible plan is available. Every time when we find a 
> > physical plan we update this upper bound, then start the search again. We 
> > could stop the search when the cost is less than a pre-defined threshold - 
> > which gives you a “good enough” plan with early termination. Still this 
> > wouldn't avoid the logical exploration. For that, you would probably 
> > archive through rule configurations, and avoid some expansive 
> > transformation to keep the cost down. 
> > 
> > 
> > > On Apr 19, 2020, at 7:30 PM, Haisheng Yuan  > > > wrote:
> > > 
> > > Igor,
> > > 
> > > a) Given current Calcite's stats derivation strategy, mixing logical and 
> > > physical planning won't make it better. I hope you went through my email 
> > > to the end, currently operators inside a memo group don't share stats 
> > > info, each operator's stats may differ with the other one, and the 
> > > RelSubset's best node and stats may vary during optimization. So logical 
> > > and physical rule pruning are not safe at the moment, otherwise it almost 
> > > implies changing downstream project's cost model silently. 
> > > 
> > > On the other hand, ensuring child group exploration task finish first 
> > > will make rule mutual exclusivity check possible, like the result of 
> > > ReduceExpressionRule won't need trigger the same rule again, The join 
> > > result of JoinCommuteRule won't need trigger JoinCommuteRule and 
> > > ReduceExpressionRule again. 
> > > 
> > > More importantly, most if not all the the long planning queries in 
> > > Calcite are not caused by too many alternatives, but mutual triggering, 
> > > or cyclic triggering, which can be avoided by customizing the rules 
> > > instead of using the default one. Unless you use dynamic programming 
> > > (Calcite use heuristic) on join reordering (left-deep, right-deep, 
> > > bushy), space pruning won't help make long / infinite running query 
> > > faster.
> > > 
> > > b) No evidence shows current version of Calcite will return the most 
> > > promising plan in first planning iteration. Instead of praying for 
> > > getting good enough plan in the first iteration, why not focus on fixing 
> > > rules that causes the issue?
> > > 
> > > c) That is not the goal.
> > > 
> > > On 2020/04/19 15:14:57, Seliverstov Igor  > > > wrote: 
> > >> Haisheng,
> > >> 
> > >> From my point of view splitting logical and physical planning parts 
> > >> isn’t a good idea. 
> > >> 
> > >> I think so because actual 

Re: [discuss] Add the default mode to the path in the Json functions.

[Stamatis Zampetakis]

Hi,

I know that Oracle uses lax by default [1] but I don't remember what other
DBMS do.

In any case adopting a default mode sounds like a reasonable thing to do.

Best,
Stamatis

[1] https://docs.oracle.com/database/121/ADXDB/json.htm#ADXDB6259


On Mon, Apr 20, 2020, 7:28 AM Chunwei Lei  wrote:

> Thank you for proposing this, Forward.
>
>  I am wondering whether it is really useful for users who want to use these
> functions
> since they probably know what the syntax is. But personally I would like to
> have the
> default mode since it is more convenient.
>
>
> Best,
> Chunwei
>
>
> On Sun, Apr 19, 2020 at 9:51 AM Forward Xu  wrote:
>
> > hi everyone, I recently found some discussable optimizations when I
> > contributed the flip-90 [1] Json functions:
> > The current json functions in calcite: JSON_EXISTS, JSON_VALUE,
> JSON_QUERY,
> > JSON_OBJECT, JSON_OBJECTAGG, JSON_ARRAY, JSON_ARRAYAGG and IS JSON
> > predication functions. These functions are implemented based on the SQL
> > 2016-2017 standard [2]. According to this standard, the path of the json
> > function must be used in one of strict or lax mode. such as:
> > json_exists ('{"foo": "bar"}', 'lax $ .foo') or
> > json_exists ('{"foo": "bar"}', 'strict $ .foo')
> > Can we give a default mode to simplify the use of lax and strict. For
> > example, we default to lax mode. In this way, the use of our json
> function
> > can be simplified to:
> > json_exists ('{"foo": "bar"}', '$ .foo')
> > Implementation idea improvement JsonFunctions jsonApiCommonSyntax path
> > judgment to increase the default lax mode logic.
> > Of course, these changes are not described in SQL2016-2017.
> > I want to hear your opinion here.
> >
> > [1]
> >
> https://cwiki.apache.org/confluence/pages/viewpage.action?pageId=141724550
> > [2]
> >
> >
> https://standards.iso.org/ittf/PubliclyAvailableStandards/c067367_ISO_IEC_TR_19075-6_2017.zip
> >
>

Calcite-Master - Build # 1707 - Failure

[Apache Jenkins Server]

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

Status: Failure

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

Re: [DISCUSS] Towards Cascades Optimizer

[Seliverstov Igor]

Haisheng, Xiening,

Ok, Now I see how it should work.

Thanks for your replies.

Regards,
Igor

> 20 апр. 2020 г., в 09:56, Seliverstov Igor  написал(а):
> 
> Haisheng, Xiening,
> 
> Thanks for clarifying. 
> 
> In this proposal, we are not trying to split logical and physical planning 
> entirely. - actually I was in doubt about an idea of entire splitting logical 
> and physical phases, if you aren't going to, I have no objections.
> 
> But it returns me to my first question: how we will propagate traits in 
> bottom-up manner using proposed approach. (See [DISCUSS] Proposal to add API 
> to force rules matching specific rels for details)
> 
> One of inconveniences of current VolcanoPlanner implementation is amount of 
> tricks that we need to get desired behaviour. It would be great if some of 
> issues (or all of them) were solved in the new approach.
> 
> Regards,
> Igor
> 
> пн, 20 апр. 2020 г., 7:02 Xiening Dai  >:
> Hi Igor,
> 
> Your comment - "because actual cost may be calculated correctly using 
> physical operators only. So won't be able to implement Branch and Bound Space 
> Pruning.“ is actually not true. In Cascade’s lower bound / upper bound 
> pruning algorithm, you can get cost lower bound of input RelNode using 
> cardinality * unit_copy_cost. The unit_copy_cost is a constant, which stands 
> for the minimal cost for processing a tuple from the input. So this will be 
> the minimal cost the input RelNode can achieve, and if this is indeed larger 
> than the current best cost, this input node can be pruned. 
> 
> In this proposal, we are not trying to split logical and physical planning 
> entirely. But for any given equivalent set, we would need to finish 
> exploration before implementation. But for the entire memo tree, each set 
> could be in different planning stage, and an equivalent set can be pruned 
> even before it’s implemented. A text book example of aforementioned “lower 
> bound / upper bound pruning” would be the join ordering case.
> 
> Regarding #3, I think we can still achieve that (partially) through this 
> proposal. Remember every time when we start the optimization, we pass down an 
> upper bound limit. Initially this upper bound for root is infinite - meaning 
> that no feasible plan is available. Every time when we find a physical plan 
> we update this upper bound, then start the search again. We could stop the 
> search when the cost is less than a pre-defined threshold - which gives you a 
> “good enough” plan with early termination. Still this wouldn't avoid the 
> logical exploration. For that, you would probably archive through rule 
> configurations, and avoid some expansive transformation to keep the cost 
> down. 
> 
> 
> > On Apr 19, 2020, at 7:30 PM, Haisheng Yuan  > > wrote:
> > 
> > Igor,
> > 
> > a) Given current Calcite's stats derivation strategy, mixing logical and 
> > physical planning won't make it better. I hope you went through my email to 
> > the end, currently operators inside a memo group don't share stats info, 
> > each operator's stats may differ with the other one, and the RelSubset's 
> > best node and stats may vary during optimization. So logical and physical 
> > rule pruning are not safe at the moment, otherwise it almost implies 
> > changing downstream project's cost model silently. 
> > 
> > On the other hand, ensuring child group exploration task finish first will 
> > make rule mutual exclusivity check possible, like the result of 
> > ReduceExpressionRule won't need trigger the same rule again, The join 
> > result of JoinCommuteRule won't need trigger JoinCommuteRule and 
> > ReduceExpressionRule again. 
> > 
> > More importantly, most if not all the the long planning queries in Calcite 
> > are not caused by too many alternatives, but mutual triggering, or cyclic 
> > triggering, which can be avoided by customizing the rules instead of using 
> > the default one. Unless you use dynamic programming (Calcite use heuristic) 
> > on join reordering (left-deep, right-deep, bushy), space pruning won't help 
> > make long / infinite running query faster.
> > 
> > b) No evidence shows current version of Calcite will return the most 
> > promising plan in first planning iteration. Instead of praying for getting 
> > good enough plan in the first iteration, why not focus on fixing rules that 
> > causes the issue?
> > 
> > c) That is not the goal.
> > 
> > On 2020/04/19 15:14:57, Seliverstov Igor  > > wrote: 
> >> Haisheng,
> >> 
> >> From my point of view splitting logical and physical planning parts isn’t 
> >> a good idea. 
> >> 
> >> I think so because actual cost may be calculated correctly using physical 
> >> operators only. So that we:
> >> a) won't be able to implement Branch and Bound Space Pruning (as far as I 
> >> understand, at exploring time there are no physical operators, no correct 
> >> costs, but assumptions only, I don’t think we 

RelMetadataQuery.getRowCount stackoverflow

[JiaTao Tao]

Hi
Has anyone encountered this problem before? Just a simple query(no more
than 20 lines, two joins, no union).

And I see this ticket: https://issues.apache.org/jira/browse/CALCITE-2057,
but there's no follow up, also I see flink may occur this problem(
https://developer.aliyun.com/ask/129548)

java.lang.StackOverflowError
at java.util.HashMap.hash(HashMap.java:339)
at java.util.HashMap.put(HashMap.java:612)
at 
com.google.common.collect.StandardTable.getOrCreate(StandardTable.java:165)
at com.google.common.collect.StandardTable.put(StandardTable.java:174)
at com.google.common.collect.HashBasedTable.put(HashBasedTable.java:55)
at GeneratedMetadataHandler_RowCount.getRowCount(Unknown Source)
at 
org.apache.calcite.rel.metadata.RelMetadataQuery.getRowCount(RelMetadataQuery.java:208)
at 
org.apache.calcite.rel.metadata.RelMdRowCount.getRowCount(RelMdRowCount.java:72)
at GeneratedMetadataHandler_RowCount.getRowCount_$(Unknown Source)
at GeneratedMetadataHandler_RowCount.getRowCount(Unknown Source)
at ...

Regards!

Aron Tao

Re: [DISCUSS] Towards Cascades Optimizer

[Seliverstov Igor]

Haisheng, Xiening,

Thanks for clarifying.

*In this proposal, we are not trying to split logical and physical planning
entirely. *- actually I was in doubt about an idea of entire splitting
logical and physical phases, if you aren't going to, I have no objections.

But it returns me to my first question: how we will propagate traits in
bottom-up manner using proposed approach. (See* [DISCUSS] Proposal to add
API to force rules matching specific rels* for details)

One of inconveniences of current VolcanoPlanner implementation is amount of
tricks that we need to get desired behaviour. It would be great if some of
issues (or all of them) were solved in the new approach.

Regards,
Igor

пн, 20 апр. 2020 г., 7:02 Xiening Dai :

> Hi Igor,
>
> Your comment - "because actual cost may be calculated correctly using
> physical operators only. So won't be able to implement Branch and Bound
> Space Pruning.“ is actually not true. In Cascade’s lower bound / upper
> bound pruning algorithm, you can get cost lower bound of input RelNode
> using cardinality * unit_copy_cost. The unit_copy_cost is a constant, which
> stands for the minimal cost for processing a tuple from the input. So this
> will be the minimal cost the input RelNode can achieve, and if this is
> indeed larger than the current best cost, this input node can be pruned.
>
> In this proposal, we are not trying to split logical and physical planning
> entirely. But for any given equivalent set, we would need to finish
> exploration before implementation. But for the entire memo tree, each set
> could be in different planning stage, and an equivalent set can be pruned
> even before it’s implemented. A text book example of aforementioned “lower
> bound / upper bound pruning” would be the join ordering case.
>
> Regarding #3, I think we can still achieve that (partially) through this
> proposal. Remember every time when we start the optimization, we pass down
> an upper bound limit. Initially this upper bound for root is infinite -
> meaning that no feasible plan is available. Every time when we find a
> physical plan we update this upper bound, then start the search again. We
> could stop the search when the cost is less than a pre-defined threshold -
> which gives you a “good enough” plan with early termination. Still this
> wouldn't avoid the logical exploration. For that, you would probably
> archive through rule configurations, and avoid some expansive
> transformation to keep the cost down.
>
>
> > On Apr 19, 2020, at 7:30 PM, Haisheng Yuan  wrote:
> >
> > Igor,
> >
> > a) Given current Calcite's stats derivation strategy, mixing logical and
> physical planning won't make it better. I hope you went through my email to
> the end, currently operators inside a memo group don't share stats info,
> each operator's stats may differ with the other one, and the RelSubset's
> best node and stats may vary during optimization. So logical and physical
> rule pruning are not safe at the moment, otherwise it almost implies
> changing downstream project's cost model silently.
> >
> > On the other hand, ensuring child group exploration task finish first
> will make rule mutual exclusivity check possible, like the result of
> ReduceExpressionRule won't need trigger the same rule again, The join
> result of JoinCommuteRule won't need trigger JoinCommuteRule and
> ReduceExpressionRule again.
> >
> > More importantly, most if not all the the long planning queries in
> Calcite are not caused by too many alternatives, but mutual triggering, or
> cyclic triggering, which can be avoided by customizing the rules instead of
> using the default one. Unless you use dynamic programming (Calcite use
> heuristic) on join reordering (left-deep, right-deep, bushy), space pruning
> won't help make long / infinite running query faster.
> >
> > b) No evidence shows current version of Calcite will return the most
> promising plan in first planning iteration. Instead of praying for getting
> good enough plan in the first iteration, why not focus on fixing rules that
> causes the issue?
> >
> > c) That is not the goal.
> >
> > On 2020/04/19 15:14:57, Seliverstov Igor  wrote:
> >> Haisheng,
> >>
> >> From my point of view splitting logical and physical planning parts
> isn’t a good idea.
> >>
> >> I think so because actual cost may be calculated correctly using
> physical operators only. So that we:
> >> a) won't be able to implement Branch and Bound Space Pruning (as far as
> I understand, at exploring time there are no physical operators, no correct
> costs, but assumptions only, I don’t think we should rely on them)
> >> b) won’t be able to get good enough plan (without Branch and Bound
> Space Pruning it’s non-trivial task to get right search direction and most
> promising plans in first planning iterations)
> >> c) won’t be able to tune the good enough plan during several similar
> queries are executed
> >>
> >> Regards,
> >> Igor
> >>
> >>
> >>> 19 апр. 2020 г., в 17:37, Haisheng Yuan  

Re: [DISCUSS] Towards Cascades Optimizer

[吴金朋]

Hi, Haisheng and Igor.

I think we do need the ability for logical space pruning. But we can
achieve it step by step. In the first trial, we implement and optimize a
rel only after it is fully explored. And then, after solving problems like
group sharing stats and logical cost accuracy, we move it forward.
Haisheng's current design does not prevent future refactoring.

I believe there are ways to calculate the logical cost, but I think it's
not that simple as  "cardinality * unit_copy_cost.". The "current best
cost" is computed by the actual cost model from physical plan, and it is
not comparable to such a simple cost model. To achieve this goal, it may
require some modification to the RelNode interface.


For the discussion mentioned in last part of Haisheng's proposal, I am more
into the second way: Add a new Planner. Here is my opinions:

1. This proposal is a long term work item. It may take months or even years
before it is ready for production. The product I am working on is serving
millions of queries every day. Sometimes a simple change may cause big
problems. I cannot afford the risk and testing efforts if there are always
so many big changes every release.

2. This work is more an exploration than a step by step work. Sometimes you
may prefer an implementation and after moving forward to the next goal you
find another design may be better. It may contain refactoring on
refactoring. Modifying the VolcanoPlanner directly not only introduces more
risk but also makes it more difficult to design as you need to keep
backward compatible always in mind. With a new planner, we can try things
wilder. We can even commit some useless staged code to help cooperations.

3. Though it is a new planner, it still satisfy the Planner interface. And
I see not difficulties switching between VolcanoPlanner and the new one.
Actually a simple factory that creates planner instances by config is the
only effort it needs. This applies to test cases, too. So all the tests are
sharable. And this feature, switching between old and new implementation
with one config, is very helpful for online service publishing.

4. The new planner can derive from the VolcanoPlanner and share the same
data structure. So it does not introduce more coding efforts than modifying
the VolcanoPlanner directly. And, with such derivation, after some
achievements are considered stable and helpful, it is also possible to
apply the changes to the VolcanoPlanner directly to help improving the old
planner.


Xiening Dai  于2020年4月20日周一 下午12:02写道：

> Hi Igor,
>
> Your comment - "because actual cost may be calculated correctly using
> physical operators only. So won't be able to implement Branch and Bound
> Space Pruning.“ is actually not true. In Cascade’s lower bound / upper
> bound pruning algorithm, you can get cost lower bound of input RelNode
> using cardinality * unit_copy_cost. The unit_copy_cost is a constant, which
> stands for the minimal cost for processing a tuple from the input. So this
> will be the minimal cost the input RelNode can achieve, and if this is
> indeed larger than the current best cost, this input node can be pruned.
>
> In this proposal, we are not trying to split logical and physical planning
> entirely. But for any given equivalent set, we would need to finish
> exploration before implementation. But for the entire memo tree, each set
> could be in different planning stage, and an equivalent set can be pruned
> even before it’s implemented. A text book example of aforementioned “lower
> bound / upper bound pruning” would be the join ordering case.
>
> Regarding #3, I think we can still achieve that (partially) through this
> proposal. Remember every time when we start the optimization, we pass down
> an upper bound limit. Initially this upper bound for root is infinite -
> meaning that no feasible plan is available. Every time when we find a
> physical plan we update this upper bound, then start the search again. We
> could stop the search when the cost is less than a pre-defined threshold -
> which gives you a “good enough” plan with early termination. Still this
> wouldn't avoid the logical exploration. For that, you would probably
> archive through rule configurations, and avoid some expansive
> transformation to keep the cost down.
>
>
> > On Apr 19, 2020, at 7:30 PM, Haisheng Yuan  wrote:
> >
> > Igor,
> >
> > a) Given current Calcite's stats derivation strategy, mixing logical and
> physical planning won't make it better. I hope you went through my email to
> the end, currently operators inside a memo group don't share stats info,
> each operator's stats may differ with the other one, and the RelSubset's
> best node and stats may vary during optimization. So logical and physical
> rule pruning are not safe at the moment, otherwise it almost implies
> changing downstream project's cost model silently.
> >
> > On the other hand, ensuring child group exploration task finish first
> will make rule mutual exclusivity