[jira] [Commented] (CALCITE-5109) RelMdAllPredicates support to analyze left-out-join
[ https://issues.apache.org/jira/browse/CALCITE-5109?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17529318#comment-17529318 ] Ziwei Liu commented on CALCITE-5109: I want to know that you want to reuse the current rewrite rules to support outer join or you will create other rule or some code to support it. > RelMdAllPredicates support to analyze left-out-join > --- > > Key: CALCITE-5109 > URL: https://issues.apache.org/jira/browse/CALCITE-5109 > Project: Calcite > Issue Type: Wish > Components: core >Reporter: Xurenhe >Priority: Major > Labels: pull-request-available > Time Spent: 10m > Remaining Estimate: 0h > > > {code:java} > select * from > left_sub_query {left_where_condition} > join {join_type} > right_sub_query {right_where_condition} > on {on_condition} > where {where_condition}{code} > > As we know: > * join_type is {*}left out join{*}: > ** *left_where_condition* could be pulled to *where_condition* > ** *right_where_condition* could be pulled to *on_condition* > * join_type is *right out join:* > ** *left_where_condition* could be pulled to *on_condition* > ** *right_where_condition* could be pulled to *where_condition* > > There are a large number of star-model in the warehouse. Rewriting query by > materialized view is a very effective way to speed up query. > > I'm expanding [*MaterializedViewRule#perform*|#L175]]to support rewriting > query when meeting left-out-join, and found that > [*RelMdAllPredicates*|#L193]] didn't support to analyze left-join. > Could calcite support it? This feature is very important for expanding > *MaterializedViewRule#perform* > -- This message was sent by Atlassian Jira (v8.20.7#820007)
[jira] [Commented] (CALCITE-4910) Enhance simplify to reduce ((A OR D) AND (A OR C) AND A AND B) to (A AND B)
[ https://issues.apache.org/jira/browse/CALCITE-4910?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17477622#comment-17477622 ] Ziwei Liu commented on CALCITE-4910: I think, if unknown is false, we don't use pull-up predicate correctly in simplifyIsNotNull and simplifyIsNull not. {code:java} if (predicates.pulledUpPredicates.contains(a)) { return rexBuilder.makeLiteral(true); } {code} We only return true when pull up predicate contain the current node. If unknown as false and pull up predicate is A > 1, I think A IS NOT NULL also can be simplified to true. But if unknown is unknown, this may not be useful. We need to compare the term contained in AND and OR. Only when A equals to A, then A AND B AND (A OR C) can be simplified to A AND B > Enhance simplify to reduce ((A OR D) AND (A OR C) AND A AND B) to (A AND B) > --- > > Key: CALCITE-4910 > URL: https://issues.apache.org/jira/browse/CALCITE-4910 > Project: Calcite > Issue Type: Improvement >Reporter: Ziwei Liu >Assignee: Ziwei Liu >Priority: Major > Labels: pull-request-available > Time Spent: 1h 50m > Remaining Estimate: 0h > > Considering this condition, (a or d) and (a or c) and a and b. This condition > can be simplified to a and b, but now simplify can not do this. > This case is found out by materialized view test: > {code:java} > materialized view: > select 'a',empid,deptno,salary as s from emps where (replace(name,'e','a') is > not null or replace(name,'a','c') is not null) > and (replace(name,'c','b') is not null or replace(name,'n','d') is not null) > sql: > select salary +1 as s,deptno from emps where > replace(name,'c','b')is not null and replace(name,'a','c') is not null and > salary>10 > The sql will not be rewritten by materialized view because when splitFilter, > $3 > 10 and REPLACE($2, 'a', 'c') is not null and REPLACE($2, 'c', 'b') is > not null and (REPLACE($2, 'a', 'c') is not null or REPLACE($2, 'e', 'a') is > not null) > and (REPLACE($2, 'c', 'b') is not null or REPLACE($2, 'n', 'd') is not null) > can not be simplified to > $3 > 10 and REPLACE($2, 'a', 'c') is not null and REPLACE($2, 'c', 'b') is > not null > materialized view: > select empid,deptno,salary as s from emps where > (replace(name,'e','a') ='aaa' or replace(name,'a','c') ='aaa') > and > (replace(name,'c','b') ='aaa' or replace(name,'n','d') ='aaa') > sql: > select salary as s,deptno from emps where > replace(name,'c','b') ='aaa' is not null > and replace(name,'a','c') ='aaa' > This case also can not be rewritten.{code} -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Commented] (CALCITE-4983) When using mv rewriting, if aggregate has grouping sets, not handle the query's condition properly.
[ https://issues.apache.org/jira/browse/CALCITE-4983?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17475899#comment-17475899 ] Ziwei Liu commented on CALCITE-4983: When rewriting Aggregate, the pulled-up condition's fields must be included in all grouping sets. This test case is created by myself, not in the code of master's branch. > When using mv rewriting, if aggregate has grouping sets, not handle the > query's condition properly. > --- > > Key: CALCITE-4983 > URL: https://issues.apache.org/jira/browse/CALCITE-4983 > Project: Calcite > Issue Type: Bug >Reporter: Ziwei Liu >Assignee: Ziwei Liu >Priority: Major > > In method unifyAggregates, if agg using grouping sets, doesn't handle the > condition need to pull up. > Here is the code: > {code:java} > if (targetGroupGenAggCalls.isEmpty()) { > List compenProjs = MutableRels.createProjectExprs(target, > projects); > RexProgram compenRexProgram = RexProgram.create( > target.rowType, compenProjs, targetCond, query.rowType, rexBuilder); > result = MutableCalc.of(target, compenRexProgram); > } else { > // here has a problem: doesn't handle the targetCond > result = MutableAggregate.of(target, > target.groupSet, target.groupSets, targetGroupGenAggCalls); > } {code} > Here is an example: > {code:java} > mv: select empid, deptno from emps group by grouping sets ((empid, > deptno),(empid)) > sql: select empid, count(distinct deptno) from emps where empid>100 > group by grouping sets ((empid, deptno), (empid)) {code} > the result plan is: > {code:java} > LogicalCalc(expr#0..2=[{inputs}], deptno=[$t1], EXPR$1=[$t2]) > LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}]], > EXPR$1=[COUNT(DISTINCT $1)]) > EnumerableTableScan(table=[[hr, MV0]]) {code} > We can see that this plan doesn't handle the condition empid>100 > -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Commented] (CALCITE-4983) When using mv rewriting, if aggregate has grouping sets, not handle the query's condition properly.
[ https://issues.apache.org/jira/browse/CALCITE-4983?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17475281#comment-17475281 ] Ziwei Liu commented on CALCITE-4983: Thanks a lot, I have updated the summary. > When using mv rewriting, if aggregate has grouping sets, not handle the > query's condition properly. > --- > > Key: CALCITE-4983 > URL: https://issues.apache.org/jira/browse/CALCITE-4983 > Project: Calcite > Issue Type: Bug >Reporter: Ziwei Liu >Assignee: Ziwei Liu >Priority: Major > > In method unifyAggregates, if agg using grouping sets, doesn't handle the > condition need to pull up. > Here is the code: > {code:java} > if (targetGroupGenAggCalls.isEmpty()) { > List compenProjs = MutableRels.createProjectExprs(target, > projects); > RexProgram compenRexProgram = RexProgram.create( > target.rowType, compenProjs, targetCond, query.rowType, rexBuilder); > result = MutableCalc.of(target, compenRexProgram); > } else { > // here has a problem: doesn't handle the targetCond > result = MutableAggregate.of(target, > target.groupSet, target.groupSets, targetGroupGenAggCalls); > } {code} > Here is an example: > {code:java} > mv: select empid, deptno from emps group by grouping sets ((empid, > deptno),(empid)) > sql: select empid, count(distinct deptno) from emps where empid>100 > group by grouping sets ((empid, deptno), (empid)) {code} > the result plan is: > {code:java} > LogicalCalc(expr#0..2=[{inputs}], deptno=[$t1], EXPR$1=[$t2]) > LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}]], > EXPR$1=[COUNT(DISTINCT $1)]) > EnumerableTableScan(table=[[hr, MV0]]) {code} > We can see that this plan doesn't handle the condition empid>100 > -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Updated] (CALCITE-4983) When using mv rewriting, if aggregate has grouping sets, not handle the query's condition properly.
[ https://issues.apache.org/jira/browse/CALCITE-4983?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Ziwei Liu updated CALCITE-4983: --- Summary: When using mv rewriting, if aggregate has grouping sets, not handle the query's condition properly. (was: In SubstitutionVisitor's unifyAggregates, if Aggregate has grouping sets, we need to handle the condition needs to pull up.) > When using mv rewriting, if aggregate has grouping sets, not handle the > query's condition properly. > --- > > Key: CALCITE-4983 > URL: https://issues.apache.org/jira/browse/CALCITE-4983 > Project: Calcite > Issue Type: Bug >Reporter: Ziwei Liu >Assignee: Ziwei Liu >Priority: Major > > In method unifyAggregates, if agg using grouping sets, doesn't handle the > condition need to pull up. > Here is the code: > {code:java} > if (targetGroupGenAggCalls.isEmpty()) { > List compenProjs = MutableRels.createProjectExprs(target, > projects); > RexProgram compenRexProgram = RexProgram.create( > target.rowType, compenProjs, targetCond, query.rowType, rexBuilder); > result = MutableCalc.of(target, compenRexProgram); > } else { > // here has a problem: doesn't handle the targetCond > result = MutableAggregate.of(target, > target.groupSet, target.groupSets, targetGroupGenAggCalls); > } {code} > Here is an example: > {code:java} > mv: select empid, deptno from emps group by grouping sets ((empid, > deptno),(empid)) > sql: select empid, count(distinct deptno) from emps where empid>100 > group by grouping sets ((empid, deptno), (empid)) {code} > the result plan is: > {code:java} > LogicalCalc(expr#0..2=[{inputs}], deptno=[$t1], EXPR$1=[$t2]) > LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}]], > EXPR$1=[COUNT(DISTINCT $1)]) > EnumerableTableScan(table=[[hr, MV0]]) {code} > We can see that this plan doesn't handle the condition empid>100 > -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Updated] (CALCITE-4983) In SubstitutionVisitor's unifyAggregates, if Aggregate has grouping sets, we need to handle the condition needs to pull up.
[ https://issues.apache.org/jira/browse/CALCITE-4983?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Ziwei Liu updated CALCITE-4983: --- Description: In method unifyAggregates, if agg using grouping sets, doesn't handle the condition need to pull up. Here is the code: {code:java} if (targetGroupGenAggCalls.isEmpty()) { List compenProjs = MutableRels.createProjectExprs(target, projects); RexProgram compenRexProgram = RexProgram.create( target.rowType, compenProjs, targetCond, query.rowType, rexBuilder); result = MutableCalc.of(target, compenRexProgram); } else { // here has a problem: doesn't handle the targetCond result = MutableAggregate.of(target, target.groupSet, target.groupSets, targetGroupGenAggCalls); } {code} Here is an example: {code:java} mv: select empid, deptno from emps group by grouping sets ((empid, deptno),(empid)) sql: select empid, count(distinct deptno) from emps where empid>100 group by grouping sets ((empid, deptno), (empid)) {code} the result plan is: {code:java} LogicalCalc(expr#0..2=[{inputs}], deptno=[$t1], EXPR$1=[$t2]) LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}]], EXPR$1=[COUNT(DISTINCT $1)]) EnumerableTableScan(table=[[hr, MV0]]) {code} We can see that this plan doesn't handle the condition empid>100 was: In method unifyAggregates, if agg using grouping sets, doesn't handle the condition need to pull up. Here is the code: {code:java} if (targetGroupGenAggCalls.isEmpty()) { List compenProjs = MutableRels.createProjectExprs(target, projects); RexProgram compenRexProgram = RexProgram.create( target.rowType, compenProjs, targetCond, query.rowType, rexBuilder); result = MutableCalc.of(target, compenRexProgram); } else { // here has a problem: doesn't handle the targetCond result = MutableAggregate.of(target, target.groupSet, target.groupSets, targetGroupGenAggCalls); } {code} Here is an example: {code:java} sql: select empid, deptno from emps group by grouping sets ((empid, deptno),(empid)) mv: select empid, count(distinct deptno) from emps where empid>100 group by grouping sets ((empid, deptno), (empid)) {code} the result plan is: {code:java} LogicalCalc(expr#0..2=[{inputs}], deptno=[$t1], EXPR$1=[$t2]) LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}]], EXPR$1=[COUNT(DISTINCT $1)]) EnumerableTableScan(table=[[hr, MV0]]) {code} We can see that this plan doesn't handle the condition empid>100 > In SubstitutionVisitor's unifyAggregates, if Aggregate has grouping sets, we > need to handle the condition needs to pull up. > --- > > Key: CALCITE-4983 > URL: https://issues.apache.org/jira/browse/CALCITE-4983 > Project: Calcite > Issue Type: Bug >Reporter: Ziwei Liu >Assignee: Ziwei Liu >Priority: Major > > In method unifyAggregates, if agg using grouping sets, doesn't handle the > condition need to pull up. > Here is the code: > {code:java} > if (targetGroupGenAggCalls.isEmpty()) { > List compenProjs = MutableRels.createProjectExprs(target, > projects); > RexProgram compenRexProgram = RexProgram.create( > target.rowType, compenProjs, targetCond, query.rowType, rexBuilder); > result = MutableCalc.of(target, compenRexProgram); > } else { > // here has a problem: doesn't handle the targetCond > result = MutableAggregate.of(target, > target.groupSet, target.groupSets, targetGroupGenAggCalls); > } {code} > Here is an example: > {code:java} > mv: select empid, deptno from emps group by grouping sets ((empid, > deptno),(empid)) > sql: select empid, count(distinct deptno) from emps where empid>100 > group by grouping sets ((empid, deptno), (empid)) {code} > the result plan is: > {code:java} > LogicalCalc(expr#0..2=[{inputs}], deptno=[$t1], EXPR$1=[$t2]) > LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}]], > EXPR$1=[COUNT(DISTINCT $1)]) > EnumerableTableScan(table=[[hr, MV0]]) {code} > We can see that this plan doesn't handle the condition empid>100 > -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Commented] (CALCITE-4983) In SubstitutionVisitor's unifyAggregates, if Aggregate has grouping sets, we need to handle the condition needs to pull up.
[ https://issues.apache.org/jira/browse/CALCITE-4983?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17473252#comment-17473252 ] Ziwei Liu commented on CALCITE-4983: Here is the pr https://github.com/apache/calcite/pull/2687 > In SubstitutionVisitor's unifyAggregates, if Aggregate has grouping sets, we > need to handle the condition needs to pull up. > --- > > Key: CALCITE-4983 > URL: https://issues.apache.org/jira/browse/CALCITE-4983 > Project: Calcite > Issue Type: Bug >Reporter: Ziwei Liu >Assignee: Ziwei Liu >Priority: Major > > In method unifyAggregates, if agg using grouping sets, doesn't handle the > condition need to pull up. > Here is the code: > {code:java} > if (targetGroupGenAggCalls.isEmpty()) { > List compenProjs = MutableRels.createProjectExprs(target, > projects); > RexProgram compenRexProgram = RexProgram.create( > target.rowType, compenProjs, targetCond, query.rowType, rexBuilder); > result = MutableCalc.of(target, compenRexProgram); > } else { > // here has a problem: doesn't handle the targetCond > result = MutableAggregate.of(target, > target.groupSet, target.groupSets, targetGroupGenAggCalls); > } {code} > Here is an example: > {code:java} > sql: select empid, deptno from emps group by grouping sets ((empid, > deptno),(empid)) > mv: select empid, count(distinct deptno) from emps where empid>100 > group by grouping sets ((empid, deptno), (empid)) {code} > the result plan is: > {code:java} > LogicalCalc(expr#0..2=[{inputs}], deptno=[$t1], EXPR$1=[$t2]) > LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}]], > EXPR$1=[COUNT(DISTINCT $1)]) > EnumerableTableScan(table=[[hr, MV0]]) {code} > We can see that this plan doesn't handle the condition empid>100 > -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Updated] (CALCITE-4983) In SubstitutionVisitor's unifyAggregates, if Aggregate has grouping sets, we need to handle the condition needs to pull up.
[ https://issues.apache.org/jira/browse/CALCITE-4983?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Ziwei Liu updated CALCITE-4983: --- Summary: In SubstitutionVisitor's unifyAggregates, if Aggregate has grouping sets, we need to handle the condition needs to pull up. (was: In SubstitutionVisitor's unifyAggregates, if Aggregate has grouping sets, we need to handle the condition need to pull up.) > In SubstitutionVisitor's unifyAggregates, if Aggregate has grouping sets, we > need to handle the condition needs to pull up. > --- > > Key: CALCITE-4983 > URL: https://issues.apache.org/jira/browse/CALCITE-4983 > Project: Calcite > Issue Type: Bug >Reporter: Ziwei Liu >Assignee: Ziwei Liu >Priority: Major > > In method unifyAggregates, if agg using grouping sets, doesn't handle the > condition need to pull up. > Here is the code: > {code:java} > if (targetGroupGenAggCalls.isEmpty()) { > List compenProjs = MutableRels.createProjectExprs(target, > projects); > RexProgram compenRexProgram = RexProgram.create( > target.rowType, compenProjs, targetCond, query.rowType, rexBuilder); > result = MutableCalc.of(target, compenRexProgram); > } else { > // here has a problem: doesn't handle the targetCond > result = MutableAggregate.of(target, > target.groupSet, target.groupSets, targetGroupGenAggCalls); > } {code} > Here is an example: > {code:java} > sql: select empid, deptno from emps group by grouping sets ((empid, > deptno),(empid)) > mv: select empid, count(distinct deptno) from emps where empid>100 > group by grouping sets ((empid, deptno), (empid)) {code} > the result plan is: > {code:java} > LogicalCalc(expr#0..2=[{inputs}], deptno=[$t1], EXPR$1=[$t2]) > LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}]], > EXPR$1=[COUNT(DISTINCT $1)]) > EnumerableTableScan(table=[[hr, MV0]]) {code} > We can see that this plan doesn't handle the condition empid>100 > -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Commented] (CALCITE-4983) In SubstitutionVisitor's unifyAggregates, if Aggregate has grouping sets, we need to handle the condition needs to pull up.
[ https://issues.apache.org/jira/browse/CALCITE-4983?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17473250#comment-17473250 ] Ziwei Liu commented on CALCITE-4983: Thanks a lot, I have updated the description and the summary. > In SubstitutionVisitor's unifyAggregates, if Aggregate has grouping sets, we > need to handle the condition needs to pull up. > --- > > Key: CALCITE-4983 > URL: https://issues.apache.org/jira/browse/CALCITE-4983 > Project: Calcite > Issue Type: Bug >Reporter: Ziwei Liu >Assignee: Ziwei Liu >Priority: Major > > In method unifyAggregates, if agg using grouping sets, doesn't handle the > condition need to pull up. > Here is the code: > {code:java} > if (targetGroupGenAggCalls.isEmpty()) { > List compenProjs = MutableRels.createProjectExprs(target, > projects); > RexProgram compenRexProgram = RexProgram.create( > target.rowType, compenProjs, targetCond, query.rowType, rexBuilder); > result = MutableCalc.of(target, compenRexProgram); > } else { > // here has a problem: doesn't handle the targetCond > result = MutableAggregate.of(target, > target.groupSet, target.groupSets, targetGroupGenAggCalls); > } {code} > Here is an example: > {code:java} > sql: select empid, deptno from emps group by grouping sets ((empid, > deptno),(empid)) > mv: select empid, count(distinct deptno) from emps where empid>100 > group by grouping sets ((empid, deptno), (empid)) {code} > the result plan is: > {code:java} > LogicalCalc(expr#0..2=[{inputs}], deptno=[$t1], EXPR$1=[$t2]) > LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}]], > EXPR$1=[COUNT(DISTINCT $1)]) > EnumerableTableScan(table=[[hr, MV0]]) {code} > We can see that this plan doesn't handle the condition empid>100 > -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Updated] (CALCITE-4983) In SubstitutionVisitor's unifyAggregates, if there has grouping sets, we need to handle the condition need to pull up.
[ https://issues.apache.org/jira/browse/CALCITE-4983?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Ziwei Liu updated CALCITE-4983: --- Summary: In SubstitutionVisitor's unifyAggregates, if there has grouping sets, we need to handle the condition need to pull up. (was: In SubstitutionVisitor, when unify Aggregate,) > In SubstitutionVisitor's unifyAggregates, if there has grouping sets, we need > to handle the condition need to pull up. > -- > > Key: CALCITE-4983 > URL: https://issues.apache.org/jira/browse/CALCITE-4983 > Project: Calcite > Issue Type: Bug >Reporter: Ziwei Liu >Assignee: Ziwei Liu >Priority: Major > > In method unifyAggregates, if agg using grouping sets, doesn't handle the > condition need to pull up. > Here is the code: > {code:java} > if (targetGroupGenAggCalls.isEmpty()) { > List compenProjs = MutableRels.createProjectExprs(target, > projects); > RexProgram compenRexProgram = RexProgram.create( > target.rowType, compenProjs, targetCond, query.rowType, rexBuilder); > result = MutableCalc.of(target, compenRexProgram); > } else { > // here has a problem: doesn't handle the targetCond > result = MutableAggregate.of(target, > target.groupSet, target.groupSets, targetGroupGenAggCalls); > } {code} > Here is an example: > {code:java} > sql: select empid, deptno from emps group by grouping sets ((empid, > deptno),(empid)) > mv: select empid, count(distinct deptno) from emps where empid>100 > group by grouping sets ((empid, deptno), (empid)) {code} > the result plan is: > {code:java} > LogicalCalc(expr#0..2=[{inputs}], deptno=[$t1], EXPR$1=[$t2]) > LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}]], > EXPR$1=[COUNT(DISTINCT $1)]) > EnumerableTableScan(table=[[hr, MV0]]) {code} > We can see that this plan doesn't handle the condition empid>100 > -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Updated] (CALCITE-4983) In SubstitutionVisitor's unifyAggregates, if Aggregate has grouping sets, we need to handle the condition need to pull up.
[ https://issues.apache.org/jira/browse/CALCITE-4983?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Ziwei Liu updated CALCITE-4983: --- Summary: In SubstitutionVisitor's unifyAggregates, if Aggregate has grouping sets, we need to handle the condition need to pull up. (was: In SubstitutionVisitor's unifyAggregates, if there has grouping sets, we need to handle the condition need to pull up.) > In SubstitutionVisitor's unifyAggregates, if Aggregate has grouping sets, we > need to handle the condition need to pull up. > -- > > Key: CALCITE-4983 > URL: https://issues.apache.org/jira/browse/CALCITE-4983 > Project: Calcite > Issue Type: Bug >Reporter: Ziwei Liu >Assignee: Ziwei Liu >Priority: Major > > In method unifyAggregates, if agg using grouping sets, doesn't handle the > condition need to pull up. > Here is the code: > {code:java} > if (targetGroupGenAggCalls.isEmpty()) { > List compenProjs = MutableRels.createProjectExprs(target, > projects); > RexProgram compenRexProgram = RexProgram.create( > target.rowType, compenProjs, targetCond, query.rowType, rexBuilder); > result = MutableCalc.of(target, compenRexProgram); > } else { > // here has a problem: doesn't handle the targetCond > result = MutableAggregate.of(target, > target.groupSet, target.groupSets, targetGroupGenAggCalls); > } {code} > Here is an example: > {code:java} > sql: select empid, deptno from emps group by grouping sets ((empid, > deptno),(empid)) > mv: select empid, count(distinct deptno) from emps where empid>100 > group by grouping sets ((empid, deptno), (empid)) {code} > the result plan is: > {code:java} > LogicalCalc(expr#0..2=[{inputs}], deptno=[$t1], EXPR$1=[$t2]) > LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}]], > EXPR$1=[COUNT(DISTINCT $1)]) > EnumerableTableScan(table=[[hr, MV0]]) {code} > We can see that this plan doesn't handle the condition empid>100 > -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Updated] (CALCITE-4983) In SubstitutionVisitor, handle condition when unify Aggregate.
[ https://issues.apache.org/jira/browse/CALCITE-4983?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Ziwei Liu updated CALCITE-4983: --- Description: In method unifyAggregates, if agg using grouping sets, doesn't handle the condition need to pull up. Here is the code: {code:java} if (targetGroupGenAggCalls.isEmpty()) { List compenProjs = MutableRels.createProjectExprs(target, projects); RexProgram compenRexProgram = RexProgram.create( target.rowType, compenProjs, targetCond, query.rowType, rexBuilder); result = MutableCalc.of(target, compenRexProgram); } else { // here has a problem: doesn't handle the targetCond result = MutableAggregate.of(target, target.groupSet, target.groupSets, targetGroupGenAggCalls); } {code} Here is an example: {code:java} sql: select empid, deptno from emps group by grouping sets ((empid, deptno),(empid)) mv: select empid, count(distinct deptno) from emps where empid>100 group by grouping sets ((empid, deptno), (empid)) {code} the result plan is: {code:java} LogicalCalc(expr#0..2=[{inputs}], deptno=[$t1], EXPR$1=[$t2]) LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}]], EXPR$1=[COUNT(DISTINCT $1)]) EnumerableTableScan(table=[[hr, MV0]]) {code} We can see that this plan doesn't handle the condition empid>100 was: In method unifyAggregates, if agg using grouping sets, doesn't handle the condition need to pull up. Here is the code: {code:java} if (targetGroupGenAggCalls.isEmpty()) { List compenProjs = MutableRels.createProjectExprs(target, projects); RexProgram compenRexProgram = RexProgram.create( target.rowType, compenProjs, targetCond, query.rowType, rexBuilder); result = MutableCalc.of(target, compenRexProgram); } else { // here has a problem: doesn't handle the targetCond result = MutableAggregate.of(target, target.groupSet, target.groupSets, targetGroupGenAggCalls); } {code} Here is an example: {code:java} sql: select empid, deptno from emps group by grouping sets ((empid, deptno),(empid)) mv: select empid, count(distinct deptno) from emps where empid>100 group by grouping sets ((empid, deptno), (empid)) {code} the result plan is: {code:java} LogicalCalc(expr#0..2=[{inputs}], deptno=[$t1], EXPR$1=[$t2]) LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}]], EXPR$1=[COUNT(DISTINCT $1)]) EnumerableTableScan(table=[[hr, MV0]]) {code} We can see that this plan doesn't handle the condition empid>100 > In SubstitutionVisitor, handle condition when unify Aggregate. > -- > > Key: CALCITE-4983 > URL: https://issues.apache.org/jira/browse/CALCITE-4983 > Project: Calcite > Issue Type: Bug >Reporter: Ziwei Liu >Assignee: Ziwei Liu >Priority: Major > > In method unifyAggregates, if agg using grouping sets, doesn't handle the > condition need to pull up. > Here is the code: > {code:java} > if (targetGroupGenAggCalls.isEmpty()) { > List compenProjs = MutableRels.createProjectExprs(target, > projects); > RexProgram compenRexProgram = RexProgram.create( > target.rowType, compenProjs, targetCond, query.rowType, rexBuilder); > result = MutableCalc.of(target, compenRexProgram); > } else { > // here has a problem: doesn't handle the targetCond > result = MutableAggregate.of(target, > target.groupSet, target.groupSets, targetGroupGenAggCalls); > } {code} > Here is an example: > {code:java} > sql: select empid, deptno from emps group by grouping sets ((empid, > deptno),(empid)) > mv: select empid, count(distinct deptno) from emps where empid>100 > group by grouping sets ((empid, deptno), (empid)) {code} > the result plan is: > {code:java} > LogicalCalc(expr#0..2=[{inputs}], deptno=[$t1], EXPR$1=[$t2]) > LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}]], > EXPR$1=[COUNT(DISTINCT $1)]) > EnumerableTableScan(table=[[hr, MV0]]) {code} > We can see that this plan doesn't handle the condition empid>100 > -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Updated] (CALCITE-4983) In SubstitutionVisitor, when unify Aggregate,
[ https://issues.apache.org/jira/browse/CALCITE-4983?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Ziwei Liu updated CALCITE-4983: --- Summary: In SubstitutionVisitor, when unify Aggregate, (was: In SubstitutionVisitor, handle condition when unify Aggregate.) > In SubstitutionVisitor, when unify Aggregate, > - > > Key: CALCITE-4983 > URL: https://issues.apache.org/jira/browse/CALCITE-4983 > Project: Calcite > Issue Type: Bug >Reporter: Ziwei Liu >Assignee: Ziwei Liu >Priority: Major > > In method unifyAggregates, if agg using grouping sets, doesn't handle the > condition need to pull up. > Here is the code: > {code:java} > if (targetGroupGenAggCalls.isEmpty()) { > List compenProjs = MutableRels.createProjectExprs(target, > projects); > RexProgram compenRexProgram = RexProgram.create( > target.rowType, compenProjs, targetCond, query.rowType, rexBuilder); > result = MutableCalc.of(target, compenRexProgram); > } else { > // here has a problem: doesn't handle the targetCond > result = MutableAggregate.of(target, > target.groupSet, target.groupSets, targetGroupGenAggCalls); > } {code} > Here is an example: > {code:java} > sql: select empid, deptno from emps group by grouping sets ((empid, > deptno),(empid)) > mv: select empid, count(distinct deptno) from emps where empid>100 > group by grouping sets ((empid, deptno), (empid)) {code} > the result plan is: > {code:java} > LogicalCalc(expr#0..2=[{inputs}], deptno=[$t1], EXPR$1=[$t2]) > LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}]], > EXPR$1=[COUNT(DISTINCT $1)]) > EnumerableTableScan(table=[[hr, MV0]]) {code} > We can see that this plan doesn't handle the condition empid>100 > -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Updated] (CALCITE-4983) In SubstitutionVisitor, handle condition when unify Aggregate.
[ https://issues.apache.org/jira/browse/CALCITE-4983?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Ziwei Liu updated CALCITE-4983: --- Description: In method unifyAggregates, if agg using grouping sets, doesn't handle the condition need to pull up. Here is the code: {code:java} if (targetGroupGenAggCalls.isEmpty()) { List compenProjs = MutableRels.createProjectExprs(target, projects); RexProgram compenRexProgram = RexProgram.create( target.rowType, compenProjs, targetCond, query.rowType, rexBuilder); result = MutableCalc.of(target, compenRexProgram); } else { // here has a problem: doesn't handle the targetCond result = MutableAggregate.of(target, target.groupSet, target.groupSets, targetGroupGenAggCalls); } {code} Here is an example: {code:java} sql: select empid, deptno from emps group by grouping sets ((empid, deptno),(empid)) mv: select empid, count(distinct deptno) from emps where empid>100 group by grouping sets ((empid, deptno), (empid)) {code} the result plan is: {code:java} LogicalCalc(expr#0..2=[{inputs}], deptno=[$t1], EXPR$1=[$t2]) LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}]], EXPR$1=[COUNT(DISTINCT $1)]) EnumerableTableScan(table=[[hr, MV0]]) {code} We can see that this plan doesn't handle the condition empid>100 was: In method unifyAggregates, if agg using grouping sets, doesn't handle the condition need to pull up. Here is the code: {code:java} if (targetGroupGenAggCalls.isEmpty()) { List compenProjs = MutableRels.createProjectExprs(target, projects); RexProgram compenRexProgram = RexProgram.create( target.rowType, compenProjs, targetCond, query.rowType, rexBuilder); result = MutableCalc.of(target, compenRexProgram); } else { // here has a problem: doesn't handle the targetCond result = MutableAggregate.of(target, target.groupSet, target.groupSets, targetGroupGenAggCalls); } {code} > In SubstitutionVisitor, handle condition when unify Aggregate. > -- > > Key: CALCITE-4983 > URL: https://issues.apache.org/jira/browse/CALCITE-4983 > Project: Calcite > Issue Type: Bug >Reporter: Ziwei Liu >Assignee: Ziwei Liu >Priority: Major > > In method unifyAggregates, if agg using grouping sets, doesn't handle the > condition need to pull up. > Here is the code: > > {code:java} > if (targetGroupGenAggCalls.isEmpty()) { > List compenProjs = MutableRels.createProjectExprs(target, > projects); > RexProgram compenRexProgram = RexProgram.create( > target.rowType, compenProjs, targetCond, query.rowType, rexBuilder); > result = MutableCalc.of(target, compenRexProgram); > } else { > // here has a problem: doesn't handle the targetCond > result = MutableAggregate.of(target, > target.groupSet, target.groupSets, targetGroupGenAggCalls); > } {code} > Here is an example: > {code:java} > sql: select empid, deptno from emps group by grouping sets ((empid, > deptno),(empid)) > mv: select empid, count(distinct deptno) from emps where empid>100 > group by grouping sets ((empid, deptno), (empid)) {code} > the result plan is: > {code:java} > LogicalCalc(expr#0..2=[{inputs}], deptno=[$t1], EXPR$1=[$t2]) > LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}]], > EXPR$1=[COUNT(DISTINCT $1)]) > EnumerableTableScan(table=[[hr, MV0]]) {code} > We can see that this plan doesn't handle the condition empid>100 > -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Updated] (CALCITE-4983) In SubstitutionVisitor, handle condition when unify Aggregate.
[ https://issues.apache.org/jira/browse/CALCITE-4983?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Ziwei Liu updated CALCITE-4983: --- Description: In method unifyAggregates, if agg using grouping sets, doesn't handle the condition need to pull up. Here is the code: {code:java} if (targetGroupGenAggCalls.isEmpty()) { List compenProjs = MutableRels.createProjectExprs(target, projects); RexProgram compenRexProgram = RexProgram.create( target.rowType, compenProjs, targetCond, query.rowType, rexBuilder); result = MutableCalc.of(target, compenRexProgram); } else { // here has a problem: doesn't handle the targetCond result = MutableAggregate.of(target, target.groupSet, target.groupSets, targetGroupGenAggCalls); } {code} was: In method unifyAggregates, if agg using grouping sets and there has condition need to pull up. Here is the code: {code:java} if (targetGroupGenAggCalls.isEmpty()) { List compenProjs = MutableRels.createProjectExprs(target, projects); RexProgram compenRexProgram = RexProgram.create( target.rowType, compenProjs, targetCond, query.rowType, rexBuilder); result = MutableCalc.of(target, compenRexProgram); } else { // here has a problem: doesn't handle the targetCond result = MutableAggregate.of(target, target.groupSet, target.groupSets, targetGroupGenAggCalls); } {code} > In SubstitutionVisitor, handle condition when unify Aggregate. > -- > > Key: CALCITE-4983 > URL: https://issues.apache.org/jira/browse/CALCITE-4983 > Project: Calcite > Issue Type: Bug >Reporter: Ziwei Liu >Assignee: Ziwei Liu >Priority: Major > > In method unifyAggregates, if agg using grouping sets, doesn't handle the > condition need to pull up. > Here is the code: > {code:java} > if (targetGroupGenAggCalls.isEmpty()) { > List compenProjs = MutableRels.createProjectExprs(target, > projects); > RexProgram compenRexProgram = RexProgram.create( > target.rowType, compenProjs, targetCond, query.rowType, rexBuilder); > result = MutableCalc.of(target, compenRexProgram); > } else { > // here has a problem: doesn't handle the targetCond > result = MutableAggregate.of(target, > target.groupSet, target.groupSets, targetGroupGenAggCalls); > } {code} -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Updated] (CALCITE-4983) In SubstitutionVisitor, handle condition when unify Aggregate.
[ https://issues.apache.org/jira/browse/CALCITE-4983?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Ziwei Liu updated CALCITE-4983: --- Summary: In SubstitutionVisitor, handle condition when unify Aggregate. (was: In SubstitutionVisitor, create a Calc when there has condition need pull up.) > In SubstitutionVisitor, handle condition when unify Aggregate. > -- > > Key: CALCITE-4983 > URL: https://issues.apache.org/jira/browse/CALCITE-4983 > Project: Calcite > Issue Type: Bug >Reporter: Ziwei Liu >Assignee: Ziwei Liu >Priority: Major > > In method unifyAggregates, if agg using grouping sets and there has condition > need to pull up. > Here is the code: > {code:java} > if (targetGroupGenAggCalls.isEmpty()) { > List compenProjs = MutableRels.createProjectExprs(target, > projects); > RexProgram compenRexProgram = RexProgram.create( > target.rowType, compenProjs, targetCond, query.rowType, rexBuilder); > result = MutableCalc.of(target, compenRexProgram); > } else { > // here has a problem: doesn't handle the targetCond > result = MutableAggregate.of(target, > target.groupSet, target.groupSets, targetGroupGenAggCalls); > } {code} -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Assigned] (CALCITE-4983) In SubstitutionVisitor, create a Calc when there has condition need pull up.
[ https://issues.apache.org/jira/browse/CALCITE-4983?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Ziwei Liu reassigned CALCITE-4983: -- Assignee: Ziwei Liu > In SubstitutionVisitor, create a Calc when there has condition need pull up. > > > Key: CALCITE-4983 > URL: https://issues.apache.org/jira/browse/CALCITE-4983 > Project: Calcite > Issue Type: Bug >Reporter: Ziwei Liu >Assignee: Ziwei Liu >Priority: Major > > In method unifyAggregates, if agg using grouping sets and there has condition > need to pull up. > Here is the code: > {code:java} > if (targetGroupGenAggCalls.isEmpty()) { > List compenProjs = MutableRels.createProjectExprs(target, > projects); > RexProgram compenRexProgram = RexProgram.create( > target.rowType, compenProjs, targetCond, query.rowType, rexBuilder); > result = MutableCalc.of(target, compenRexProgram); > } else { > // here has a problem: doesn't handle the targetCond > result = MutableAggregate.of(target, > target.groupSet, target.groupSets, targetGroupGenAggCalls); > } {code} -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Updated] (CALCITE-4983) In SubstitutionVisitor, create a Calc when there has condition need pull up.
[ https://issues.apache.org/jira/browse/CALCITE-4983?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Ziwei Liu updated CALCITE-4983: --- Description: In method unifyAggregates, if agg using grouping sets and there has condition need to pull up. Here is the code: {code:java} if (targetGroupGenAggCalls.isEmpty()) { List compenProjs = MutableRels.createProjectExprs(target, projects); RexProgram compenRexProgram = RexProgram.create( target.rowType, compenProjs, targetCond, query.rowType, rexBuilder); result = MutableCalc.of(target, compenRexProgram); } else { // here has a problem: doesn't handle the targetCond result = MutableAggregate.of(target, target.groupSet, target.groupSets, targetGroupGenAggCalls); } {code} > In SubstitutionVisitor, create a Calc when there has condition need pull up. > > > Key: CALCITE-4983 > URL: https://issues.apache.org/jira/browse/CALCITE-4983 > Project: Calcite > Issue Type: Bug >Reporter: Ziwei Liu >Priority: Major > > In method unifyAggregates, if agg using grouping sets and there has condition > need to pull up. > Here is the code: > {code:java} > if (targetGroupGenAggCalls.isEmpty()) { > List compenProjs = MutableRels.createProjectExprs(target, > projects); > RexProgram compenRexProgram = RexProgram.create( > target.rowType, compenProjs, targetCond, query.rowType, rexBuilder); > result = MutableCalc.of(target, compenRexProgram); > } else { > // here has a problem: doesn't handle the targetCond > result = MutableAggregate.of(target, > target.groupSet, target.groupSets, targetGroupGenAggCalls); > } {code} -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Created] (CALCITE-4983) In SubstitutionVisitor, create a Calc when there has condition need pull up.
Ziwei Liu created CALCITE-4983: -- Summary: In SubstitutionVisitor, create a Calc when there has condition need pull up. Key: CALCITE-4983 URL: https://issues.apache.org/jira/browse/CALCITE-4983 Project: Calcite Issue Type: Bug Reporter: Ziwei Liu -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Commented] (CALCITE-4910) Enhance simplify to reduce ((A OR D) AND (A OR C) AND A AND B) to (A AND B)
[ https://issues.apache.org/jira/browse/CALCITE-4910?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17469060#comment-17469060 ] Ziwei Liu commented on CALCITE-4910: Thanks a lot for the comment. I have update the code. You can review it again. > Enhance simplify to reduce ((A OR D) AND (A OR C) AND A AND B) to (A AND B) > --- > > Key: CALCITE-4910 > URL: https://issues.apache.org/jira/browse/CALCITE-4910 > Project: Calcite > Issue Type: Improvement >Reporter: Ziwei Liu >Assignee: Ziwei Liu >Priority: Major > Labels: pull-request-available > Time Spent: 10m > Remaining Estimate: 0h > > Considering this condition, (a or d) and (a or c) and a and b. This condition > can be simplified to a and b, but now simplify can not do this. > This case is found out by materialized view test: > {code:java} > materialized view: > select 'a',empid,deptno,salary as s from emps where (replace(name,'e','a') is > not null or replace(name,'a','c') is not null) > and (replace(name,'c','b') is not null or replace(name,'n','d') is not null) > sql: > select salary +1 as s,deptno from emps where > replace(name,'c','b')is not null and replace(name,'a','c') is not null and > salary>10 > The sql will not be rewritten by materialized view because when splitFilter, > $3 > 10 and REPLACE($2, 'a', 'c') is not null and REPLACE($2, 'c', 'b') is > not null and (REPLACE($2, 'a', 'c') is not null or REPLACE($2, 'e', 'a') is > not null) > and (REPLACE($2, 'c', 'b') is not null or REPLACE($2, 'n', 'd') is not null) > can not be simplified to > $3 > 10 and REPLACE($2, 'a', 'c') is not null and REPLACE($2, 'c', 'b') is > not null > materialized view: > select empid,deptno,salary as s from emps where > (replace(name,'e','a') ='aaa' or replace(name,'a','c') ='aaa') > and > (replace(name,'c','b') ='aaa' or replace(name,'n','d') ='aaa') > sql: > select salary as s,deptno from emps where > replace(name,'c','b') ='aaa' is not null > and replace(name,'a','c') ='aaa' > This case also can not be rewritten.{code} -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Updated] (CALCITE-4910) Enhance simplify to reduce ((A OR D) AND (A OR C) AND A AND B) to (A AND B)
[ https://issues.apache.org/jira/browse/CALCITE-4910?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Ziwei Liu updated CALCITE-4910: --- Summary: Enhance simplify to reduce ((A OR D) AND (A OR C) AND A AND B) to (A AND B) (was: Enhance simplify to reduce ((a or d) and (a or c) and a and b) to (a and b)) > Enhance simplify to reduce ((A OR D) AND (A OR C) AND A AND B) to (A AND B) > --- > > Key: CALCITE-4910 > URL: https://issues.apache.org/jira/browse/CALCITE-4910 > Project: Calcite > Issue Type: Improvement >Reporter: Ziwei Liu >Assignee: Ziwei Liu >Priority: Major > Labels: pull-request-available > Time Spent: 10m > Remaining Estimate: 0h > > Considering this condition, (a or d) and (a or c) and a and b. This condition > can be simplified to a and b, but now simplify can not do this. > This case is found out by materialized view test: > {code:java} > materialized view: > select 'a',empid,deptno,salary as s from emps where (replace(name,'e','a') is > not null or replace(name,'a','c') is not null) > and (replace(name,'c','b') is not null or replace(name,'n','d') is not null) > sql: > select salary +1 as s,deptno from emps where > replace(name,'c','b')is not null and replace(name,'a','c') is not null and > salary>10 > The sql will not be rewritten by materialized view because when splitFilter, > $3 > 10 and REPLACE($2, 'a', 'c') is not null and REPLACE($2, 'c', 'b') is > not null and (REPLACE($2, 'a', 'c') is not null or REPLACE($2, 'e', 'a') is > not null) > and (REPLACE($2, 'c', 'b') is not null or REPLACE($2, 'n', 'd') is not null) > can not be simplified to > $3 > 10 and REPLACE($2, 'a', 'c') is not null and REPLACE($2, 'c', 'b') is > not null > materialized view: > select empid,deptno,salary as s from emps where > (replace(name,'e','a') ='aaa' or replace(name,'a','c') ='aaa') > and > (replace(name,'c','b') ='aaa' or replace(name,'n','d') ='aaa') > sql: > select salary as s,deptno from emps where > replace(name,'c','b') ='aaa' is not null > and replace(name,'a','c') ='aaa' > This case also can not be rewritten.{code} -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Commented] (CALCITE-4910) Enhance simplify to reduce ((a or d) and (a or c) and a and b) to (a and b)
[ https://issues.apache.org/jira/browse/CALCITE-4910?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17468412#comment-17468412 ] Ziwei Liu commented on CALCITE-4910: I have submit the code [https://github.com/apache/calcite/pull/2673] Please review the code when you available. > Enhance simplify to reduce ((a or d) and (a or c) and a and b) to (a and b) > --- > > Key: CALCITE-4910 > URL: https://issues.apache.org/jira/browse/CALCITE-4910 > Project: Calcite > Issue Type: Improvement >Reporter: Ziwei Liu >Assignee: Ziwei Liu >Priority: Major > Labels: pull-request-available > Time Spent: 10m > Remaining Estimate: 0h > > Considering this condition, (a or d) and (a or c) and a and b. This condition > can be simplified to a and b, but now simplify can not do this. > This case is found out by materialized view test: > {code:java} > materialized view: > select 'a',empid,deptno,salary as s from emps where (replace(name,'e','a') is > not null or replace(name,'a','c') is not null) > and (replace(name,'c','b') is not null or replace(name,'n','d') is not null) > sql: > select salary +1 as s,deptno from emps where > replace(name,'c','b')is not null and replace(name,'a','c') is not null and > salary>10 > The sql will not be rewritten by materialized view because when splitFilter, > $3 > 10 and REPLACE($2, 'a', 'c') is not null and REPLACE($2, 'c', 'b') is > not null and (REPLACE($2, 'a', 'c') is not null or REPLACE($2, 'e', 'a') is > not null) > and (REPLACE($2, 'c', 'b') is not null or REPLACE($2, 'n', 'd') is not null) > can not be simplified to > $3 > 10 and REPLACE($2, 'a', 'c') is not null and REPLACE($2, 'c', 'b') is > not null > materialized view: > select empid,deptno,salary as s from emps where > (replace(name,'e','a') ='aaa' or replace(name,'a','c') ='aaa') > and > (replace(name,'c','b') ='aaa' or replace(name,'n','d') ='aaa') > sql: > select salary as s,deptno from emps where > replace(name,'c','b') ='aaa' is not null > and replace(name,'a','c') ='aaa' > This case also can not be rewritten.{code} -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Commented] (CALCITE-4964) Reduce recursion when push predicate into CASE
[ https://issues.apache.org/jira/browse/CALCITE-4964?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17465931#comment-17465931 ] Ziwei Liu commented on CALCITE-4964: Thanks a lot, I will try to test it. > Reduce recursion when push predicate into CASE > -- > > Key: CALCITE-4964 > URL: https://issues.apache.org/jira/browse/CALCITE-4964 > Project: Calcite > Issue Type: Improvement >Reporter: Ziwei Liu >Assignee: Ziwei Liu >Priority: Major > > I think CaseShuttle do not need a loop of for(;; ).When a predicate is pushed > into case, we only need to check the new RexCall whether need to push > predicate into the child operand, not need to do a new recursion. > So I think CaseShuttle can be reduced like: > {code:java} > @Override public RexNode visitCall(RexCall call) { > call = (RexCall) super.visitCall(call); > if (call instanceof OdpsLambdaRexCall) { > return call; > } > final RexCall old = call; > call = ReduceExpressionsRule.pushPredicateIntoCase(call); > if (call == old) { > return call; > } else { > boolean containCase = false; > List newOps = new ArrayList<>(call.getOperands().size()); > // call will be like case when c1 then f(x1 ...) > // check whether need push f into x1 > for (int i = 0; i < call.getOperands().size(); i ++) { > RexNode op = call.getOperands().get(i); > RexNode newOp = op; > if (i % 2 == 1 || i == call.getOperands().size() - 1) { > if (op instanceof RexCall) { > newOp = ReduceExpressionsRule.pushPredicateIntoCase((RexCall) > op); > } > } > if (op != newOp) { > containCase = true; > } > newOps.add(newOp); > } > if (!containCase) { > return call; > } else { > return call.clone(call.getType(), newOps); > } > } > } {code} -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Commented] (CALCITE-4964) Reduce recursion when push predicate into CASE
[ https://issues.apache.org/jira/browse/CALCITE-4964?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17465726#comment-17465726 ] Ziwei Liu commented on CALCITE-4964: The original code is {code:java} @Override public RexNode visitCall(RexCall call) { for (;;) { call = (RexCall) super.visitCall(call); final RexCall old = call; call = pushPredicateIntoCase(call); if (call == old) { return call; } } } {code} If call != old, then new call will continue the depth-first traversal, and this traversal may not need to do. eg: select case when value >300 and id > 100 then 1 when value > 200 and id > 50 then 2 else 3 as type from src where type = 3; Then Filter will be EQ(CASE(...)). After push EQ into CASE, this condition does not need to push EQ into inner predicate, so just need traverse the new call's operand. If the when's predicate is more complicated than this example, the traversal will save more time. > Reduce recursion when push predicate into CASE > -- > > Key: CALCITE-4964 > URL: https://issues.apache.org/jira/browse/CALCITE-4964 > Project: Calcite > Issue Type: Improvement >Reporter: Ziwei Liu >Assignee: Ziwei Liu >Priority: Major > > I think CaseShuttle do not need a loop of for(;; ).When a predicate is pushed > into case, we only need to check the new RexCall whether need to push > predicate into the child operand, not need to do a new recursion. > So I think CaseShuttle can be reduced like: > {code:java} > @Override public RexNode visitCall(RexCall call) { > call = (RexCall) super.visitCall(call); > if (call instanceof OdpsLambdaRexCall) { > return call; > } > final RexCall old = call; > call = ReduceExpressionsRule.pushPredicateIntoCase(call); > if (call == old) { > return call; > } else { > boolean containCase = false; > List newOps = new ArrayList<>(call.getOperands().size()); > // call will be like case when c1 then f(x1 ...) > // check whether need push f into x1 > for (int i = 0; i < call.getOperands().size(); i ++) { > RexNode op = call.getOperands().get(i); > RexNode newOp = op; > if (i % 2 == 1 || i == call.getOperands().size() - 1) { > if (op instanceof RexCall) { > newOp = ReduceExpressionsRule.pushPredicateIntoCase((RexCall) > op); > } > } > if (op != newOp) { > containCase = true; > } > newOps.add(newOp); > } > if (!containCase) { > return call; > } else { > return call.clone(call.getType(), newOps); > } > } > } {code} -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Updated] (CALCITE-4964) Reduce recursion when push predicate into CASE
[ https://issues.apache.org/jira/browse/CALCITE-4964?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Ziwei Liu updated CALCITE-4964: --- Summary: Reduce recursion when push predicate into CASE (was: reduce recursion when push predicate into case) > Reduce recursion when push predicate into CASE > -- > > Key: CALCITE-4964 > URL: https://issues.apache.org/jira/browse/CALCITE-4964 > Project: Calcite > Issue Type: Improvement >Reporter: Ziwei Liu >Assignee: Ziwei Liu >Priority: Major > > I think CaseShuttle do not need a loop of for(;; ).When a predicate is pushed > into case, we only need to check the new RexCall whether need to push > predicate into the child operand, not need to do a new recursion. > So I think CaseShuttle can be reduced like: > {code:java} > @Override public RexNode visitCall(RexCall call) { > call = (RexCall) super.visitCall(call); > if (call instanceof OdpsLambdaRexCall) { > return call; > } > final RexCall old = call; > call = ReduceExpressionsRule.pushPredicateIntoCase(call); > if (call == old) { > return call; > } else { > boolean containCase = false; > List newOps = new ArrayList<>(call.getOperands().size()); > // call will be like case when c1 then f(x1 ...) > // check whether need push f into x1 > for (int i = 0; i < call.getOperands().size(); i ++) { > RexNode op = call.getOperands().get(i); > RexNode newOp = op; > if (i % 2 == 1 || i == call.getOperands().size() - 1) { > if (op instanceof RexCall) { > newOp = ReduceExpressionsRule.pushPredicateIntoCase((RexCall) > op); > } > } > if (op != newOp) { > containCase = true; > } > newOps.add(newOp); > } > if (!containCase) { > return call; > } else { > return call.clone(call.getType(), newOps); > } > } > } {code} -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Commented] (CALCITE-4964) reduce recursion when push predicate into case
[ https://issues.apache.org/jira/browse/CALCITE-4964?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17465120#comment-17465120 ] Ziwei Liu commented on CALCITE-4964: Sorry that I did not describe this Jira clearly. I have updated the description. It's a performance improvement. Thanks. > reduce recursion when push predicate into case > -- > > Key: CALCITE-4964 > URL: https://issues.apache.org/jira/browse/CALCITE-4964 > Project: Calcite > Issue Type: Improvement >Reporter: Ziwei Liu >Assignee: Ziwei Liu >Priority: Major > > I think CaseShuttle do not need a loop of for(;; ).When a predicate is pushed > into case, we only need to check the new RexCall whether need to push > predicate into the child operand, not need to do a new recursion. > So I think CaseShuttle can be reduced like: > {code:java} > @Override public RexNode visitCall(RexCall call) { > call = (RexCall) super.visitCall(call); > if (call instanceof OdpsLambdaRexCall) { > return call; > } > final RexCall old = call; > call = ReduceExpressionsRule.pushPredicateIntoCase(call); > if (call == old) { > return call; > } else { > boolean containCase = false; > List newOps = new ArrayList<>(call.getOperands().size()); > // call will be like case when c1 then f(x1 ...) > // check whether need push f into x1 > for (int i = 0; i < call.getOperands().size(); i ++) { > RexNode op = call.getOperands().get(i); > RexNode newOp = op; > if (i % 2 == 1 || i == call.getOperands().size() - 1) { > if (op instanceof RexCall) { > newOp = ReduceExpressionsRule.pushPredicateIntoCase((RexCall) > op); > } > } > if (op != newOp) { > containCase = true; > } > newOps.add(newOp); > } > if (!containCase) { > return call; > } else { > return call.clone(call.getType(), newOps); > } > } > } {code} -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Updated] (CALCITE-4964) reduce recursion when push predicate into case
[ https://issues.apache.org/jira/browse/CALCITE-4964?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Ziwei Liu updated CALCITE-4964: --- Description: I think CaseShuttle do not need a loop of for(;; ).When a predicate is pushed into case, we only need to check the new RexCall whether need to push predicate into the child operand, not need to do a new recursion. So I think CaseShuttle can be reduced like: {code:java} @Override public RexNode visitCall(RexCall call) { call = (RexCall) super.visitCall(call); if (call instanceof OdpsLambdaRexCall) { return call; } final RexCall old = call; call = ReduceExpressionsRule.pushPredicateIntoCase(call); if (call == old) { return call; } else { boolean containCase = false; List newOps = new ArrayList<>(call.getOperands().size()); // call will be like case when c1 then f(x1 ...) // check whether need push f into x1 for (int i = 0; i < call.getOperands().size(); i ++) { RexNode op = call.getOperands().get(i); RexNode newOp = op; if (i % 2 == 1 || i == call.getOperands().size() - 1) { if (op instanceof RexCall) { newOp = ReduceExpressionsRule.pushPredicateIntoCase((RexCall) op); } } if (op != newOp) { containCase = true; } newOps.add(newOp); } if (!containCase) { return call; } else { return call.clone(call.getType(), newOps); } } } {code} was: I think CaseShuttle has some CaseShuttle's recursion can be reduced like: {code:java} @Override public RexNode visitCall(RexCall call) { call = (RexCall) super.visitCall(call); if (call instanceof OdpsLambdaRexCall) { return call; } final RexCall old = call; call = ReduceExpressionsRule.pushPredicateIntoCase(call); if (call == old) { return call; } else { boolean containCase = false; List newOps = new ArrayList<>(call.getOperands().size()); // call will be like case when c1 then f(x1 ...) // check whether need push f into x1 for (int i = 0; i < call.getOperands().size(); i ++) { RexNode op = call.getOperands().get(i); RexNode newOp = op; if (i % 2 == 1 || i == call.getOperands().size() - 1) { if (op instanceof RexCall) { newOp = ReduceExpressionsRule.pushPredicateIntoCase((RexCall) op); } } if (op != newOp) { containCase = true; } newOps.add(newOp); } if (!containCase) { return call; } else { return call.clone(call.getType(), newOps); } } } {code} > reduce recursion when push predicate into case > -- > > Key: CALCITE-4964 > URL: https://issues.apache.org/jira/browse/CALCITE-4964 > Project: Calcite > Issue Type: Improvement >Reporter: Ziwei Liu >Assignee: Ziwei Liu >Priority: Major > > I think CaseShuttle do not need a loop of for(;; ).When a predicate is pushed > into case, we only need to check the new RexCall whether need to push > predicate into the child operand, not need to do a new recursion. > So I think CaseShuttle can be reduced like: > {code:java} > @Override public RexNode visitCall(RexCall call) { > call = (RexCall) super.visitCall(call); > if (call instanceof OdpsLambdaRexCall) { > return call; > } > final RexCall old = call; > call = ReduceExpressionsRule.pushPredicateIntoCase(call); > if (call == old) { > return call; > } else { > boolean containCase = false; > List newOps = new ArrayList<>(call.getOperands().size()); > // call will be like case when c1 then f(x1 ...) > // check whether need push f into x1 > for (int i = 0; i < call.getOperands().size(); i ++) { > RexNode op = call.getOperands().get(i); > RexNode newOp = op; > if (i % 2 == 1 || i == call.getOperands().size() - 1) { > if (op instanceof RexCall) { > newOp = ReduceExpressionsRule.pushPredicateIntoCase((RexCall) > op); > } > } > if (op != newOp) { > containCase = true; > } > newOps.add(newOp); > } > if (!containCase) { > return call; > } else { > return call.clone(call.getType(), newOps); > } > } > } {code} -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Updated] (CALCITE-4964) reduce recursion when push predicate into case
[ https://issues.apache.org/jira/browse/CALCITE-4964?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Ziwei Liu updated CALCITE-4964: --- Description: I think CaseShuttle has some CaseShuttle's recursion can be reduced like: {code:java} @Override public RexNode visitCall(RexCall call) { call = (RexCall) super.visitCall(call); if (call instanceof OdpsLambdaRexCall) { return call; } final RexCall old = call; call = ReduceExpressionsRule.pushPredicateIntoCase(call); if (call == old) { return call; } else { boolean containCase = false; List newOps = new ArrayList<>(call.getOperands().size()); // call will be like case when c1 then f(x1 ...) // check whether need push f into x1 for (int i = 0; i < call.getOperands().size(); i ++) { RexNode op = call.getOperands().get(i); RexNode newOp = op; if (i % 2 == 1 || i == call.getOperands().size() - 1) { if (op instanceof RexCall) { newOp = ReduceExpressionsRule.pushPredicateIntoCase((RexCall) op); } } if (op != newOp) { containCase = true; } newOps.add(newOp); } if (!containCase) { return call; } else { return call.clone(call.getType(), newOps); } } } {code} was: CaseShuttle's recursion can be reduced like: {code:java} @Override public RexNode visitCall(RexCall call) { call = (RexCall) super.visitCall(call); if (call instanceof OdpsLambdaRexCall) { return call; } final RexCall old = call; call = ReduceExpressionsRule.pushPredicateIntoCase(call); if (call == old) { return call; } else { boolean containCase = false; List newOps = new ArrayList<>(call.getOperands().size()); // call will be like case when c1 then f(x1 ...) // check whether need push f into x1 for (int i = 0; i < call.getOperands().size(); i ++) { RexNode op = call.getOperands().get(i); RexNode newOp = op; if (i % 2 == 1 || i == call.getOperands().size() - 1) { if (op instanceof RexCall) { newOp = ReduceExpressionsRule.pushPredicateIntoCase((RexCall) op); } } if (op != newOp) { containCase = true; } newOps.add(newOp); } if (!containCase) { return call; } else { return call.clone(call.getType(), newOps); } } } {code} > reduce recursion when push predicate into case > -- > > Key: CALCITE-4964 > URL: https://issues.apache.org/jira/browse/CALCITE-4964 > Project: Calcite > Issue Type: Improvement >Reporter: Ziwei Liu >Assignee: Ziwei Liu >Priority: Major > > I think CaseShuttle has some > CaseShuttle's recursion can be reduced like: > {code:java} > @Override public RexNode visitCall(RexCall call) { > call = (RexCall) super.visitCall(call); > if (call instanceof OdpsLambdaRexCall) { > return call; > } > final RexCall old = call; > call = ReduceExpressionsRule.pushPredicateIntoCase(call); > if (call == old) { > return call; > } else { > boolean containCase = false; > List newOps = new ArrayList<>(call.getOperands().size()); > // call will be like case when c1 then f(x1 ...) > // check whether need push f into x1 > for (int i = 0; i < call.getOperands().size(); i ++) { > RexNode op = call.getOperands().get(i); > RexNode newOp = op; > if (i % 2 == 1 || i == call.getOperands().size() - 1) { > if (op instanceof RexCall) { > newOp = ReduceExpressionsRule.pushPredicateIntoCase((RexCall) > op); > } > } > if (op != newOp) { > containCase = true; > } > newOps.add(newOp); > } > if (!containCase) { > return call; > } else { > return call.clone(call.getType(), newOps); > } > } > } {code} -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Assigned] (CALCITE-4964) reduce recursion when push predicate into case
[ https://issues.apache.org/jira/browse/CALCITE-4964?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Ziwei Liu reassigned CALCITE-4964: -- Assignee: Ziwei Liu > reduce recursion when push predicate into case > -- > > Key: CALCITE-4964 > URL: https://issues.apache.org/jira/browse/CALCITE-4964 > Project: Calcite > Issue Type: Improvement >Reporter: Ziwei Liu >Assignee: Ziwei Liu >Priority: Major > > CaseShuttle's recursion can be reduced like: > {code:java} > @Override public RexNode visitCall(RexCall call) { > call = (RexCall) super.visitCall(call); > if (call instanceof OdpsLambdaRexCall) { > return call; > } > final RexCall old = call; > call = ReduceExpressionsRule.pushPredicateIntoCase(call); > if (call == old) { > return call; > } else { > boolean containCase = false; > List newOps = new ArrayList<>(call.getOperands().size()); > // call will be like case when c1 then f(x1 ...) > // check whether need push f into x1 > for (int i = 0; i < call.getOperands().size(); i ++) { > RexNode op = call.getOperands().get(i); > RexNode newOp = op; > if (i % 2 == 1 || i == call.getOperands().size() - 1) { > if (op instanceof RexCall) { > newOp = ReduceExpressionsRule.pushPredicateIntoCase((RexCall) > op); > } > } > if (op != newOp) { > containCase = true; > } > newOps.add(newOp); > } > if (!containCase) { > return call; > } else { > return call.clone(call.getType(), newOps); > } > } > } {code} -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Created] (CALCITE-4964) reduce recursion when push predicate into case
Ziwei Liu created CALCITE-4964: -- Summary: reduce recursion when push predicate into case Key: CALCITE-4964 URL: https://issues.apache.org/jira/browse/CALCITE-4964 Project: Calcite Issue Type: Improvement Reporter: Ziwei Liu CaseShuttle's recursion can be reduced like: {code:java} @Override public RexNode visitCall(RexCall call) { call = (RexCall) super.visitCall(call); if (call instanceof OdpsLambdaRexCall) { return call; } final RexCall old = call; call = ReduceExpressionsRule.pushPredicateIntoCase(call); if (call == old) { return call; } else { boolean containCase = false; List newOps = new ArrayList<>(call.getOperands().size()); // call will be like case when c1 then f(x1 ...) // check whether need push f into x1 for (int i = 0; i < call.getOperands().size(); i ++) { RexNode op = call.getOperands().get(i); RexNode newOp = op; if (i % 2 == 1 || i == call.getOperands().size() - 1) { if (op instanceof RexCall) { newOp = ReduceExpressionsRule.pushPredicateIntoCase((RexCall) op); } } if (op != newOp) { containCase = true; } newOps.add(newOp); } if (!containCase) { return call; } else { return call.clone(call.getType(), newOps); } } } {code} -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Updated] (CALCITE-4910) Enhance simplify to reduce ((a or d) and (a or c) and a and b) to (a and b)
[ https://issues.apache.org/jira/browse/CALCITE-4910?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Ziwei Liu updated CALCITE-4910: --- Description: Considering this condition, (a or d) and (a or c) and a and b. This condition can be simplified to a and b, but now simplify can not do this. This case is found out by materialized view test: {code:java} materialized view: select 'a',empid,deptno,salary as s from emps where (replace(name,'e','a') is not null or replace(name,'a','c') is not null) and (replace(name,'c','b') is not null or replace(name,'n','d') is not null) sql: select salary +1 as s,deptno from emps where replace(name,'c','b')is not null and replace(name,'a','c') is not null and salary>10 The sql will not be rewritten by materialized view because when splitFilter, $3 > 10 and REPLACE($2, 'a', 'c') is not null and REPLACE($2, 'c', 'b') is not null and (REPLACE($2, 'a', 'c') is not null or REPLACE($2, 'e', 'a') is not null) and (REPLACE($2, 'c', 'b') is not null or REPLACE($2, 'n', 'd') is not null) can not be simplified to $3 > 10 and REPLACE($2, 'a', 'c') is not null and REPLACE($2, 'c', 'b') is not null materialized view: select empid,deptno,salary as s from emps where (replace(name,'e','a') ='aaa' or replace(name,'a','c') ='aaa') and (replace(name,'c','b') ='aaa' or replace(name,'n','d') ='aaa') sql: select salary as s,deptno from emps where replace(name,'c','b') ='aaa' is not null and replace(name,'a','c') ='aaa' This case also can not be rewritten.{code} was: Considering this condition, (a or d) and (a or c) and a and b. This condition can be simplified to a and b, but now simplify can not do this. This case is found out by materialized view test: {code:java} materialized view: select 'a',empid,deptno,salary as s from emps where (replace(name,'e','a') is not null or replace(name,'a','c') is not null) and (replace(name,'c','b') is not null or replace(name,'n','d') is not null) sql: select salary +1 as s,deptno from emps where replace(name,'c','b')is not null and replace(name,'a','c') is not null and salary>10 The sql will not be rewritten by materialized view because when splitFilter, $3 > 10 and REPLACE($2, 'a', 'c') is not null and REPLACE($2, 'c', 'b') is not null and (REPLACE($2, 'a', 'c') is not null or REPLACE($2, 'e', 'a') is not null) and (REPLACE($2, 'c', 'b') is not null or REPLACE($2, 'n', 'd') is not null) can not be simplified to $3 > 10 and REPLACE($2, 'a', 'c') is not null and REPLACE($2, 'c', 'b') is not null {code} > Enhance simplify to reduce ((a or d) and (a or c) and a and b) to (a and b) > --- > > Key: CALCITE-4910 > URL: https://issues.apache.org/jira/browse/CALCITE-4910 > Project: Calcite > Issue Type: Improvement >Reporter: Ziwei Liu >Assignee: Ziwei Liu >Priority: Major > > Considering this condition, (a or d) and (a or c) and a and b. This condition > can be simplified to a and b, but now simplify can not do this. > This case is found out by materialized view test: > {code:java} > materialized view: > select 'a',empid,deptno,salary as s from emps where (replace(name,'e','a') is > not null or replace(name,'a','c') is not null) > and (replace(name,'c','b') is not null or replace(name,'n','d') is not null) > sql: > select salary +1 as s,deptno from emps where > replace(name,'c','b')is not null and replace(name,'a','c') is not null and > salary>10 > The sql will not be rewritten by materialized view because when splitFilter, > $3 > 10 and REPLACE($2, 'a', 'c') is not null and REPLACE($2, 'c', 'b') is > not null and (REPLACE($2, 'a', 'c') is not null or REPLACE($2, 'e', 'a') is > not null) > and (REPLACE($2, 'c', 'b') is not null or REPLACE($2, 'n', 'd') is not null) > can not be simplified to > $3 > 10 and REPLACE($2, 'a', 'c') is not null and REPLACE($2, 'c', 'b') is > not null > materialized view: > select empid,deptno,salary as s from emps where > (replace(name,'e','a') ='aaa' or replace(name,'a','c') ='aaa') > and > (replace(name,'c','b') ='aaa' or replace(name,'n','d') ='aaa') > sql: > select salary as s,deptno from emps where > replace(name,'c','b') ='aaa' is not null > and replace(name,'a','c') ='aaa' > This case also can not be rewritten.{code} -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Updated] (CALCITE-4910) Enhance simplify to reduce ((a or d) and (a or c) and a and b) to (a and b)
[ https://issues.apache.org/jira/browse/CALCITE-4910?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Ziwei Liu updated CALCITE-4910: --- Description: Considering this condition, (a or d) and (a or c) and a and b. This condition can be simplified to a and b, but now simplify can not do this. This case is found out by materialized view test: {code:java} materialized view: select 'a',empid,deptno,salary as s from emps where (replace(name,'e','a') is not null or replace(name,'a','c') is not null) and (replace(name,'c','b') is not null or replace(name,'n','d') is not null) sql: select salary +1 as s,deptno from emps where replace(name,'c','b')is not null and replace(name,'a','c') is not null and salary>10 The sql will not be rewritten by materialized view because when splitFilter, $3 > 10 and REPLACE($2, 'a', 'c') is not null and REPLACE($2, 'c', 'b') is not null and (REPLACE($2, 'a', 'c') is not null or REPLACE($2, 'e', 'a') is not null) and (REPLACE($2, 'c', 'b') is not null or REPLACE($2, 'n', 'd') is not null) can not be simplified to $3 > 10 and REPLACE($2, 'a', 'c') is not null and REPLACE($2, 'c', 'b') is not null {code} was: Considering this condition, (a or d) and (a or c) and a and b. This condition can be simplified to a and b, but now simplify can not do this. This case is found out by materialized view test: {code} materialized view: select 'a',empid,deptno,salary as s from emps where (replace(name,'e','a') is not null or replace(name,'a','c') is not null) and (replace(name,'c','b') is not null or replace(name,'n','d') is not null) sql: select salary +1 as s,deptno from emps where replace(name,'c','b') is not null and replace(name,'a','c') is not null and salary>10 The sql will not be rewritten by materialized view because when splitFilter, $3 > 10 and REPLACE($2, 'a', 'c') is not null and REPLACE($2, 'c', 'b') is not null and (REPLACE($2, 'a', 'c') is not null or REPLACE($2, 'e', 'a') is not null) and (REPLACE($2, 'c', 'b') is not null or REPLACE($2, 'n', 'd') is not null) can not be simplified to $3 > 10 and REPLACE($2, 'a', 'c') is not null and REPLACE($2, 'c', 'b') is not null {code} > Enhance simplify to reduce ((a or d) and (a or c) and a and b) to (a and b) > --- > > Key: CALCITE-4910 > URL: https://issues.apache.org/jira/browse/CALCITE-4910 > Project: Calcite > Issue Type: Improvement >Reporter: Ziwei Liu >Assignee: Ziwei Liu >Priority: Major > > Considering this condition, (a or d) and (a or c) and a and b. This condition > can be simplified to a and b, but now simplify can not do this. > This case is found out by materialized view test: > {code:java} > materialized view: > select 'a',empid,deptno,salary as s from emps where (replace(name,'e','a') is > not null or replace(name,'a','c') is not null) > and (replace(name,'c','b') is not null or replace(name,'n','d') is not null) > sql: > select salary +1 as s,deptno from emps where > replace(name,'c','b')is not null and replace(name,'a','c') is not null and > salary>10 > The sql will not be rewritten by materialized view because when splitFilter, > $3 > 10 and REPLACE($2, 'a', 'c') is not null and REPLACE($2, 'c', 'b') is > not null and (REPLACE($2, 'a', 'c') is not null or REPLACE($2, 'e', 'a') is > not null) > and (REPLACE($2, 'c', 'b') is not null or REPLACE($2, 'n', 'd') is not null) > can not be simplified to > $3 > 10 and REPLACE($2, 'a', 'c') is not null and REPLACE($2, 'c', 'b') is > not null > > {code} -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Updated] (CALCITE-4909) In SubstituteVisitor, if there is a calc on target's agg, the query will not be rewritten by materialized view.
[ https://issues.apache.org/jira/browse/CALCITE-4909?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Ziwei Liu updated CALCITE-4909: --- Description: {code:java} matialized view: select 'a',empid,deptno,sum(salary) as s from emps group by empid,deptno; sql: select sum(salary) +1 as s,deptno from emps group by deptno; {code} In substitute processing, this case will not be rewritten by materialized view because there is a Calc RelNode on target's agg. We may need a rule AggregateOnCalcToCalcOnAggregateUnifyRule to handle this condition. was: matialized view: select 'a',empid,deptno,sum(salary) as s from emps group by empid,deptno; sql: select sum(salary) +1 as s,deptno from emps group by deptno; In substitute processing, this case will not be rewritten by materialized view because there is a Calc RelNode on target's agg. We may need a rule AggregateOnCalcToCalcOnAggregateUnifyRule to handle this condition. > In SubstituteVisitor, if there is a calc on target's agg, the query will not > be rewritten by materialized view. > --- > > Key: CALCITE-4909 > URL: https://issues.apache.org/jira/browse/CALCITE-4909 > Project: Calcite > Issue Type: Improvement >Reporter: Ziwei Liu >Assignee: Ziwei Liu >Priority: Major > > {code:java} > matialized view: select 'a',empid,deptno,sum(salary) as s from emps group by > empid,deptno; > sql: select sum(salary) +1 as s,deptno from emps group by deptno; > {code} > In substitute processing, this case will not be rewritten by materialized > view because there is a Calc RelNode on target's agg. We may need a rule > AggregateOnCalcToCalcOnAggregateUnifyRule to handle this condition. -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Commented] (CALCITE-4910) Enhance simplify to reduce ((a or d) and (a or c) and a and b) to (a and b)
[ https://issues.apache.org/jira/browse/CALCITE-4910?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17462552#comment-17462552 ] Ziwei Liu commented on CALCITE-4910: Thanks for all the comment. I have updated the description. I know that for sample expression like equal expression can be simplified, but if the expression is more complicated, it will not be simplified. And I will submit a pr as soon as possible. Thanks. > Enhance simplify to reduce ((a or d) and (a or c) and a and b) to (a and b) > --- > > Key: CALCITE-4910 > URL: https://issues.apache.org/jira/browse/CALCITE-4910 > Project: Calcite > Issue Type: Improvement >Reporter: Ziwei Liu >Assignee: Ziwei Liu >Priority: Major > > Considering this condition, (a or d) and (a or c) and a and b. This condition > can be simplified to a and b, but now simplify can not do this. > This case is found out by materialized view test: > {code} > materialized view: select 'a',empid,deptno,salary as s from emps where > (replace(name,'e','a') is not null or replace(name,'a','c') is not null) and > (replace(name,'c','b') is not null or replace(name,'n','d') is not null) > sql: select salary +1 as s,deptno from emps where > replace(name,'c','b') > is not null and replace(name,'a','c') is not null and salary>10 > The sql will not be rewritten by materialized view because when splitFilter, > $3 > 10 and REPLACE($2, 'a', 'c') is not null and REPLACE($2, 'c', 'b') is > not null and (REPLACE($2, 'a', 'c') is not null or REPLACE($2, 'e', 'a') is > not null) > and (REPLACE($2, 'c', 'b') is not null or REPLACE($2, 'n', 'd') is not null) > can not be simplified to > $3 > 10 and REPLACE($2, 'a', 'c') is not null and REPLACE($2, 'c', 'b') is > not null > > {code} -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Comment Edited] (CALCITE-4910) Enhance simplify to reduce ((a or d) and (a or c) and a and b) to (a and b)
[ https://issues.apache.org/jira/browse/CALCITE-4910?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17462552#comment-17462552 ] Ziwei Liu edited comment on CALCITE-4910 at 12/20/21, 11:15 AM: Thanks for all the comments. I have updated the description. I know that for sample expression like equal expression can be simplified, but if the expression is more complicated, it will not be simplified. And I will submit a pr as soon as possible. Thanks. was (Author: ziwei liu): Thanks for all the comment. I have updated the description. I know that for sample expression like equal expression can be simplified, but if the expression is more complicated, it will not be simplified. And I will submit a pr as soon as possible. Thanks. > Enhance simplify to reduce ((a or d) and (a or c) and a and b) to (a and b) > --- > > Key: CALCITE-4910 > URL: https://issues.apache.org/jira/browse/CALCITE-4910 > Project: Calcite > Issue Type: Improvement >Reporter: Ziwei Liu >Assignee: Ziwei Liu >Priority: Major > > Considering this condition, (a or d) and (a or c) and a and b. This condition > can be simplified to a and b, but now simplify can not do this. > This case is found out by materialized view test: > {code} > materialized view: select 'a',empid,deptno,salary as s from emps where > (replace(name,'e','a') is not null or replace(name,'a','c') is not null) and > (replace(name,'c','b') is not null or replace(name,'n','d') is not null) > sql: select salary +1 as s,deptno from emps where > replace(name,'c','b') > is not null and replace(name,'a','c') is not null and salary>10 > The sql will not be rewritten by materialized view because when splitFilter, > $3 > 10 and REPLACE($2, 'a', 'c') is not null and REPLACE($2, 'c', 'b') is > not null and (REPLACE($2, 'a', 'c') is not null or REPLACE($2, 'e', 'a') is > not null) > and (REPLACE($2, 'c', 'b') is not null or REPLACE($2, 'n', 'd') is not null) > can not be simplified to > $3 > 10 and REPLACE($2, 'a', 'c') is not null and REPLACE($2, 'c', 'b') is > not null > > {code} -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Updated] (CALCITE-4910) Enhance simplify to reduce ((a or d) and (a or c) and a and b) to (a and b)
[ https://issues.apache.org/jira/browse/CALCITE-4910?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Ziwei Liu updated CALCITE-4910: --- Description: Considering this condition, (a or d) and (a or c) and a and b. This condition can be simplified to a and b, but now simplify can not do this. This case is found out by materialized view test: {code} materialized view: select 'a',empid,deptno,salary as s from emps where (replace(name,'e','a') is not null or replace(name,'a','c') is not null) and (replace(name,'c','b') is not null or replace(name,'n','d') is not null) sql: select salary +1 as s,deptno from emps where replace(name,'c','b') is not null and replace(name,'a','c') is not null and salary>10 The sql will not be rewritten by materialized view because when splitFilter, $3 > 10 and REPLACE($2, 'a', 'c') is not null and REPLACE($2, 'c', 'b') is not null and (REPLACE($2, 'a', 'c') is not null or REPLACE($2, 'e', 'a') is not null) and (REPLACE($2, 'c', 'b') is not null or REPLACE($2, 'n', 'd') is not null) can not be simplified to $3 > 10 and REPLACE($2, 'a', 'c') is not null and REPLACE($2, 'c', 'b') is not null {code} was: Considering this condition, (a or d) and (a or c) and a and b. This condition can be simplified to a and b, but now simplify can not do this. This case is found out by materialized view test: {code} materialized view: select 'a',\"empid\",\"deptno\",\"salary\" as s from \"emps\" where (replace(\"name\",'\\2','a') is not null or replace(\"name\",'a[[:alpha:]]{5}','c') is not null) and (replace(\"name\",'[[:blank:]][[:alnum:]]*','b') is not null or replace(\"name\",'(.*)(.)$','d') is not null) query: select \"salary\" +1 as s,\"deptno\" from \"emps\" where replace(\"name\",'[[:blank:]][[:alnum:]]*','b' ) is not null and replace(\"name\",'a[[:alpha:]]{5}','c') is not null and \"salary\">10 {code} > Enhance simplify to reduce ((a or d) and (a or c) and a and b) to (a and b) > --- > > Key: CALCITE-4910 > URL: https://issues.apache.org/jira/browse/CALCITE-4910 > Project: Calcite > Issue Type: Improvement >Reporter: Ziwei Liu >Assignee: Ziwei Liu >Priority: Major > > Considering this condition, (a or d) and (a or c) and a and b. This condition > can be simplified to a and b, but now simplify can not do this. > This case is found out by materialized view test: > {code} > materialized view: select 'a',empid,deptno,salary as s from emps where > (replace(name,'e','a') is not null or replace(name,'a','c') is not null) and > (replace(name,'c','b') is not null or replace(name,'n','d') is not null) > sql: select salary +1 as s,deptno from emps where > replace(name,'c','b') > is not null and replace(name,'a','c') is not null and salary>10 > The sql will not be rewritten by materialized view because when splitFilter, > $3 > 10 and REPLACE($2, 'a', 'c') is not null and REPLACE($2, 'c', 'b') is > not null and (REPLACE($2, 'a', 'c') is not null or REPLACE($2, 'e', 'a') is > not null) > and (REPLACE($2, 'c', 'b') is not null or REPLACE($2, 'n', 'd') is not null) > can not be simplified to > $3 > 10 and REPLACE($2, 'a', 'c') is not null and REPLACE($2, 'c', 'b') is > not null > > {code} -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Updated] (CALCITE-4909) In SubstituteVisitor, if there is a calc on target's agg, the query will not be rewritten by materialized view.
[ https://issues.apache.org/jira/browse/CALCITE-4909?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Ziwei Liu updated CALCITE-4909: --- Description: matialized view: select 'a',empid,deptno,sum(salary) as s from emps group by empid,deptno; sql: select sum(salary) +1 as s,deptno from emps group by deptno; In substitute processing, this case will not be rewritten by materialized view because there is a Calc RelNode on target's agg. We may need a rule AggregateOnCalcToCalcOnAggregateUnifyRule to handle this condition. was: sql("select 'a',\"empid\",\"deptno\",sum(\"salary\") as s from \"emps\" group by \"empid\",\"deptno\"", "select sum(\"salary\") +1 as s,\"deptno\" from \"emps\" group by \"deptno\"").noMat(); In substitute processing, this case will not be rewritten by materialized view because there is a Calc RelNode on target's agg. We may need a rule AggregateOnCalcToCalcOnAggregateUnifyRule to handle this condition. > In SubstituteVisitor, if there is a calc on target's agg, the query will not > be rewritten by materialized view. > --- > > Key: CALCITE-4909 > URL: https://issues.apache.org/jira/browse/CALCITE-4909 > Project: Calcite > Issue Type: Improvement >Reporter: Ziwei Liu >Assignee: Ziwei Liu >Priority: Major > > matialized view: select 'a',empid,deptno,sum(salary) as s from emps group by > empid,deptno; > sql: select sum(salary) +1 as s,deptno from emps group by deptno; > In substitute processing, this case will not be rewritten by materialized > view because there is a Calc RelNode on target's agg. We may need a rule > AggregateOnCalcToCalcOnAggregateUnifyRule to handle this condition. -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Updated] (CALCITE-4909) In SubstituteVisitor, if there is a calc on target's agg, the query will not be rewritten by materialized view.
[ https://issues.apache.org/jira/browse/CALCITE-4909?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Ziwei Liu updated CALCITE-4909: --- Description: sql("select 'a',\"empid\",\"deptno\",sum(\"salary\") as s from \"emps\" group by \"empid\",\"deptno\"", "select sum(\"salary\") +1 as s,\"deptno\" from \"emps\" group by \"deptno\"").noMat(); In substitute processing, this case will not be rewritten by materialized view because there is a Calc RelNode on target's agg. We may need a rule AggregateOnCalcToCalcOnAggregateUnifyRule to handle this condition. was: sql("select 'a',\"empid\",\"deptno\",sum(\"salary\") as s from \"emps\" group by \"empid\",\"deptno\"", "select sum(\"salary\") +1 as s,\"deptno\" from \"emps\" group by \"deptno\"").noMat(); In substitute processing, this case will not be rewritten by materialized view because there is a calc on target's agg. We may need a rule AggregateOnCalcToCalcOnAggregateUnifyRule to handle this condition. > In SubstituteVisitor, if there is a calc on target's agg, the query will not > be rewritten by materialized view. > --- > > Key: CALCITE-4909 > URL: https://issues.apache.org/jira/browse/CALCITE-4909 > Project: Calcite > Issue Type: Improvement >Reporter: Ziwei Liu >Assignee: Ziwei Liu >Priority: Major > > sql("select 'a',\"empid\",\"deptno\",sum(\"salary\") as s from \"emps\" group > by \"empid\",\"deptno\"", "select sum(\"salary\") +1 as s,\"deptno\" from > \"emps\" group by \"deptno\"").noMat(); > In substitute processing, this case will not be rewritten by materialized > view because there is a Calc RelNode on target's agg. We may need a rule > AggregateOnCalcToCalcOnAggregateUnifyRule to handle this condition. -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Assigned] (CALCITE-4910) Enhance simplify to reduce ((a or d) and (a or c) and a and b) to (a and b)
[ https://issues.apache.org/jira/browse/CALCITE-4910?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Ziwei Liu reassigned CALCITE-4910: -- Assignee: Ziwei Liu > Enhance simplify to reduce ((a or d) and (a or c) and a and b) to (a and b) > --- > > Key: CALCITE-4910 > URL: https://issues.apache.org/jira/browse/CALCITE-4910 > Project: Calcite > Issue Type: Improvement >Reporter: Ziwei Liu >Assignee: Ziwei Liu >Priority: Major > > Considering this condition, (a or d) and (a or c) and a and b. This condition > can be simplified to a and b, but now simplify can not do this. > This case is found out by materialized view test: > materialized view: select 'a',\"empid\",\"deptno\",\"salary\" as s from > \"emps\" where (replace(\"name\",'\\2','a') is not null or > replace(\"name\",'a[[:alpha:]]{5}','c') is not null) and > (replace(\"name\",'[[:blank:]][[:alnum:]]*','b') is not null or > replace(\"name\",'(.*)(.)$','d') is not null) > query: select \"salary\" +1 as s,\"deptno\" from \"emps\" where > replace(\"name\",'[[:blank:]][[:alnum:]]*','b' ) is not null and > replace(\"name\",'a[[:alpha:]]{5}','c') is not null and \"salary\">10 -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Created] (CALCITE-4910) Enhance simplify to reduce ((a or d) and (a or c) and a and b) to (a and b)
Ziwei Liu created CALCITE-4910: -- Summary: Enhance simplify to reduce ((a or d) and (a or c) and a and b) to (a and b) Key: CALCITE-4910 URL: https://issues.apache.org/jira/browse/CALCITE-4910 Project: Calcite Issue Type: Improvement Reporter: Ziwei Liu Considering this condition, (a or d) and (a or c) and a and b. This condition can be simplified to a and b, but now simplify can not do this. This case is found out by materialized view test: materialized view: select 'a',\"empid\",\"deptno\",\"salary\" as s from \"emps\" where (replace(\"name\",'\\2','a') is not null or replace(\"name\",'a[[:alpha:]]{5}','c') is not null) and (replace(\"name\",'[[:blank:]][[:alnum:]]*','b') is not null or replace(\"name\",'(.*)(.)$','d') is not null) query: select \"salary\" +1 as s,\"deptno\" from \"emps\" where replace(\"name\",'[[:blank:]][[:alnum:]]*','b' ) is not null and replace(\"name\",'a[[:alpha:]]{5}','c') is not null and \"salary\">10 -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Created] (CALCITE-4909) In SubstituteVisitor, if there is a calc on target's agg, the query will not be rewritten by materialized view.
Ziwei Liu created CALCITE-4909: -- Summary: In SubstituteVisitor, if there is a calc on target's agg, the query will not be rewritten by materialized view. Key: CALCITE-4909 URL: https://issues.apache.org/jira/browse/CALCITE-4909 Project: Calcite Issue Type: Improvement Reporter: Ziwei Liu Assignee: Ziwei Liu sql("select 'a',\"empid\",\"deptno\",sum(\"salary\") as s from \"emps\" group by \"empid\",\"deptno\"", "select sum(\"salary\") +1 as s,\"deptno\" from \"emps\" group by \"deptno\"").noMat(); In substitute processing, this case will not be rewritten by materialized view because there is a calc on target's agg. We may need a rule AggregateOnCalcToCalcOnAggregateUnifyRule to handle this condition. -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Comment Edited] (CALCITE-4817) Expand SubstitutionVisitor of Aggregate with max/min, which column is the group by list of target
[ https://issues.apache.org/jira/browse/CALCITE-4817?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17426099#comment-17426099 ] Ziwei Liu edited comment on CALCITE-4817 at 10/8/21, 10:33 AM: --- this change may have some problem, considering this case final String mv = "" + "select "name", "deptno", "empid", min("commission")\n" + "from "emps" group by "name", "deptno", "empid""; final String query = "" + "select "name", max("deptno"), count(distinct "empid"), min("commission")\n" + "from "emps" group by "name""; mv already has a column of min(commission), but it's getDistinctOptionality is IGNORED, then it will not use a RollUp aggregation if ((aggregateCall.isDistinct() \|\| aggregation.getDistinctOptionality() == Optionality.IGNORED) && aggregateCall.getArgList().size() == 1) this condition need be like if ((aggregateCall.isDistinct() \|\| aggregation.getDistinctOptionality() == Optionality.IGNORED && target.aggCalls.indexOf(aggregateCall) < 0) && aggregateCall.getArgList().size() == 1) was (Author: ziwei liu): this change may have some problem, consider this case final String mv = "" + "select "name", "deptno", "empid", min("commission")\n" + "from "emps" group by "name", "deptno", "empid""; final String query = "" + "select "name", max("deptno"), count(distinct "empid"), min("commission")\n" + "from "emps" group by "name""; mv already has a column of min(commission), but it's getDistinctOptionality is IGNORED, then it will not use a RollUp aggregation, if ((aggregateCall.isDistinct() \|\| aggregation.getDistinctOptionality() == Optionality.IGNORED) && aggregateCall.getArgList().size() == 1) this condition need be like if ((aggregateCall.isDistinct() \|\| aggregation.getDistinctOptionality() == Optionality.IGNORED && target.aggCalls.indexOf(aggregateCall) < 0) && aggregateCall.getArgList().size() == 1) > Expand SubstitutionVisitor of Aggregate with max/min, which column is the > group by list of target > - > > Key: CALCITE-4817 > URL: https://issues.apache.org/jira/browse/CALCITE-4817 > Project: Calcite > Issue Type: Bug > Components: core >Reporter: Xurenhe >Assignee: Xurenhe >Priority: Major > Labels: pull-request-available > Fix For: 1.28.0 > > Time Spent: 0.5h > Remaining Estimate: 0h > > Now, SubstitutionVisitor support `Query with distinct aggregate on the column > is matched to the group list existing in target`, as CALCITE-4374 describes. > But. It's fail, when query aggregate with max/min based on the column, which > is in the group by list of target. > Materialized view recognition should also success. > {code:java} > //代码占位符 > @Test void testQueryNoDistinctOptionalityAggCallColInTargetGroupBy1() { > final String mv = "" > + "select \"name\", \"deptno\" " > + "from \"emps\" group by \"name\", \"deptno\""; > final String query = "" > + "select \"name\", min(\"deptno\")\n" > + "from \"emps\" group by \"name\""; > sql(mv, query).ok(); > } > {code} -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Comment Edited] (CALCITE-4817) Expand SubstitutionVisitor of Aggregate with max/min, which column is the group by list of target
[ https://issues.apache.org/jira/browse/CALCITE-4817?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17426099#comment-17426099 ] Ziwei Liu edited comment on CALCITE-4817 at 10/8/21, 10:32 AM: --- this change may have some problem, consider this case final String mv = "" + "select "name", "deptno", "empid", min("commission")\n" + "from "emps" group by "name", "deptno", "empid""; final String query = "" + "select "name", max("deptno"), count(distinct "empid"), min("commission")\n" + "from "emps" group by "name""; mv already has a column of min(commission), but it's getDistinctOptionality is Ignore, then it will not use a RollUp aggregation, if ((aggregateCall.isDistinct() \|\| aggregation.getDistinctOptionality() == Optionality.IGNORED) && aggregateCall.getArgList().size() == 1) this condition need be like if ((aggregateCall.isDistinct() \|\| aggregation.getDistinctOptionality() == Optionality.IGNORED && target.aggCalls.indexOf(aggregateCall) < 0) && aggregateCall.getArgList().size() == 1) was (Author: ziwei liu): this change may have some problem, consider this case final String mv = "" + "select "name", "deptno", "empid", min("commission")\n" + "from "emps" group by "name", "deptno", "empid""; final String query = "" + "select "name", max("deptno"), count(distinct "empid"), min("commission")\n" + "from "emps" group by "name""; mv already has a column of min(commission), but it's getDistinctOptionality is Ignore, then it will not use a RollUp aggregation, if ((aggregateCall.isDistinct() \|\| aggregation.getDistinctOptionality() == Optionality.IGNORED) && aggregateCall.getArgList().size() == 1) this condition need be like if ((aggregateCall.isDistinct() || aggregation.getDistinctOptionality() == Optionality.IGNORED && target.aggCalls.indexOf(aggregateCall) < 0) && aggregateCall.getArgList().size() == 1) > Expand SubstitutionVisitor of Aggregate with max/min, which column is the > group by list of target > - > > Key: CALCITE-4817 > URL: https://issues.apache.org/jira/browse/CALCITE-4817 > Project: Calcite > Issue Type: Bug > Components: core >Reporter: Xurenhe >Assignee: Xurenhe >Priority: Major > Labels: pull-request-available > Fix For: 1.28.0 > > Time Spent: 0.5h > Remaining Estimate: 0h > > Now, SubstitutionVisitor support `Query with distinct aggregate on the column > is matched to the group list existing in target`, as CALCITE-4374 describes. > But. It's fail, when query aggregate with max/min based on the column, which > is in the group by list of target. > Materialized view recognition should also success. > {code:java} > //代码占位符 > @Test void testQueryNoDistinctOptionalityAggCallColInTargetGroupBy1() { > final String mv = "" > + "select \"name\", \"deptno\" " > + "from \"emps\" group by \"name\", \"deptno\""; > final String query = "" > + "select \"name\", min(\"deptno\")\n" > + "from \"emps\" group by \"name\""; > sql(mv, query).ok(); > } > {code} -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Comment Edited] (CALCITE-4817) Expand SubstitutionVisitor of Aggregate with max/min, which column is the group by list of target
[ https://issues.apache.org/jira/browse/CALCITE-4817?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17426099#comment-17426099 ] Ziwei Liu edited comment on CALCITE-4817 at 10/8/21, 10:32 AM: --- this change may have some problem, consider this case final String mv = "" + "select "name", "deptno", "empid", min("commission")\n" + "from "emps" group by "name", "deptno", "empid""; final String query = "" + "select "name", max("deptno"), count(distinct "empid"), min("commission")\n" + "from "emps" group by "name""; mv already has a column of min(commission), but it's getDistinctOptionality is IGNORED, then it will not use a RollUp aggregation, if ((aggregateCall.isDistinct() \|\| aggregation.getDistinctOptionality() == Optionality.IGNORED) && aggregateCall.getArgList().size() == 1) this condition need be like if ((aggregateCall.isDistinct() \|\| aggregation.getDistinctOptionality() == Optionality.IGNORED && target.aggCalls.indexOf(aggregateCall) < 0) && aggregateCall.getArgList().size() == 1) was (Author: ziwei liu): this change may have some problem, consider this case final String mv = "" + "select "name", "deptno", "empid", min("commission")\n" + "from "emps" group by "name", "deptno", "empid""; final String query = "" + "select "name", max("deptno"), count(distinct "empid"), min("commission")\n" + "from "emps" group by "name""; mv already has a column of min(commission), but it's getDistinctOptionality is Ignore, then it will not use a RollUp aggregation, if ((aggregateCall.isDistinct() \|\| aggregation.getDistinctOptionality() == Optionality.IGNORED) && aggregateCall.getArgList().size() == 1) this condition need be like if ((aggregateCall.isDistinct() \|\| aggregation.getDistinctOptionality() == Optionality.IGNORED && target.aggCalls.indexOf(aggregateCall) < 0) && aggregateCall.getArgList().size() == 1) > Expand SubstitutionVisitor of Aggregate with max/min, which column is the > group by list of target > - > > Key: CALCITE-4817 > URL: https://issues.apache.org/jira/browse/CALCITE-4817 > Project: Calcite > Issue Type: Bug > Components: core >Reporter: Xurenhe >Assignee: Xurenhe >Priority: Major > Labels: pull-request-available > Fix For: 1.28.0 > > Time Spent: 0.5h > Remaining Estimate: 0h > > Now, SubstitutionVisitor support `Query with distinct aggregate on the column > is matched to the group list existing in target`, as CALCITE-4374 describes. > But. It's fail, when query aggregate with max/min based on the column, which > is in the group by list of target. > Materialized view recognition should also success. > {code:java} > //代码占位符 > @Test void testQueryNoDistinctOptionalityAggCallColInTargetGroupBy1() { > final String mv = "" > + "select \"name\", \"deptno\" " > + "from \"emps\" group by \"name\", \"deptno\""; > final String query = "" > + "select \"name\", min(\"deptno\")\n" > + "from \"emps\" group by \"name\""; > sql(mv, query).ok(); > } > {code} -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Comment Edited] (CALCITE-4817) Expand SubstitutionVisitor of Aggregate with max/min, which column is the group by list of target
[ https://issues.apache.org/jira/browse/CALCITE-4817?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17426099#comment-17426099 ] Ziwei Liu edited comment on CALCITE-4817 at 10/8/21, 10:31 AM: --- this change may have some problem, consider this case final String mv = "" + "select "name", "deptno", "empid", min("commission")\n" + "from "emps" group by "name", "deptno", "empid""; final String query = "" + "select "name", max("deptno"), count(distinct "empid"), min("commission")\n" + "from "emps" group by "name""; mv already has a column of min(commission), but it's getDistinctOptionality is Ignore, then it will not use a RollUp aggregation, if ((aggregateCall.isDistinct() \|\| aggregation.getDistinctOptionality() == Optionality.IGNORED) && aggregateCall.getArgList().size() == 1) this condition need be like if ((aggregateCall.isDistinct() || aggregation.getDistinctOptionality() == Optionality.IGNORED && target.aggCalls.indexOf(aggregateCall) < 0) && aggregateCall.getArgList().size() == 1) was (Author: ziwei liu): this change may have some problem, consider this case final String mv = "" + "select "name", "deptno", "empid", min("commission")\n" + "from "emps" group by "name", "deptno", "empid""; final String query = "" + "select "name", max("deptno"), count(distinct "empid"), min("commission")\n" + "from "emps" group by "name""; mv already has a column of min(commission), but it's getDistinctOptionality is Ignore, then it will not use a RollUp aggregation, if ((aggregateCall.isDistinct() || aggregation.getDistinctOptionality() == Optionality.IGNORED) && aggregateCall.getArgList().size() == 1) this condition need be like if ((aggregateCall.isDistinct() || aggregation.getDistinctOptionality() == Optionality.IGNORED && target.aggCalls.indexOf(aggregateCall) < 0) && aggregateCall.getArgList().size() == 1) > Expand SubstitutionVisitor of Aggregate with max/min, which column is the > group by list of target > - > > Key: CALCITE-4817 > URL: https://issues.apache.org/jira/browse/CALCITE-4817 > Project: Calcite > Issue Type: Bug > Components: core >Reporter: Xurenhe >Assignee: Xurenhe >Priority: Major > Labels: pull-request-available > Fix For: 1.28.0 > > Time Spent: 0.5h > Remaining Estimate: 0h > > Now, SubstitutionVisitor support `Query with distinct aggregate on the column > is matched to the group list existing in target`, as CALCITE-4374 describes. > But. It's fail, when query aggregate with max/min based on the column, which > is in the group by list of target. > Materialized view recognition should also success. > {code:java} > //代码占位符 > @Test void testQueryNoDistinctOptionalityAggCallColInTargetGroupBy1() { > final String mv = "" > + "select \"name\", \"deptno\" " > + "from \"emps\" group by \"name\", \"deptno\""; > final String query = "" > + "select \"name\", min(\"deptno\")\n" > + "from \"emps\" group by \"name\""; > sql(mv, query).ok(); > } > {code} -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Comment Edited] (CALCITE-4817) Expand SubstitutionVisitor of Aggregate with max/min, which column is the group by list of target
[ https://issues.apache.org/jira/browse/CALCITE-4817?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17426099#comment-17426099 ] Ziwei Liu edited comment on CALCITE-4817 at 10/8/21, 10:31 AM: --- this change may have some problem, consider this case final String mv = "" + "select "name", "deptno", "empid", min("commission")\n" + "from "emps" group by "name", "deptno", "empid""; final String query = "" + "select "name", max("deptno"), count(distinct "empid"), min("commission")\n" + "from "emps" group by "name""; mv already has a column of min(commission), but it's getDistinctOptionality is Ignore, then it will not use a RollUp aggregation, if ((aggregateCall.isDistinct() || aggregation.getDistinctOptionality() == Optionality.IGNORED) && aggregateCall.getArgList().size() == 1) this condition need be like if ((aggregateCall.isDistinct() || aggregation.getDistinctOptionality() == Optionality.IGNORED && target.aggCalls.indexOf(aggregateCall) < 0) && aggregateCall.getArgList().size() == 1) was (Author: ziwei liu): this change may have some problem, consider this case final String mv = "" + "select "name", "deptno", "empid", min("commission")\n" + "from "emps" group by "name", "deptno", "empid""; final String query = "" + "select "name", max("deptno"), count(distinct "empid"), min("commission")\n" + "from "emps" group by "name""; mv already has a column of min(commission), but it's getDistinctOptionality is Ignore, then it will not use a RollUp aggregation, if ((aggregateCall.isDistinct() || aggregation.getDistinctOptionality() == Optionality.IGNORED) && aggregateCall.getArgList().size() == 1) this condition need be like if ((aggregateCall.isDistinct() || aggregation.getDistinctOptionality() == Optionality.IGNORED && target.aggCalls.indexOf(aggregateCall) < 0) && aggregateCall.getArgList().size() == 1) > Expand SubstitutionVisitor of Aggregate with max/min, which column is the > group by list of target > - > > Key: CALCITE-4817 > URL: https://issues.apache.org/jira/browse/CALCITE-4817 > Project: Calcite > Issue Type: Bug > Components: core >Reporter: Xurenhe >Assignee: Xurenhe >Priority: Major > Labels: pull-request-available > Fix For: 1.28.0 > > Time Spent: 0.5h > Remaining Estimate: 0h > > Now, SubstitutionVisitor support `Query with distinct aggregate on the column > is matched to the group list existing in target`, as CALCITE-4374 describes. > But. It's fail, when query aggregate with max/min based on the column, which > is in the group by list of target. > Materialized view recognition should also success. > {code:java} > //代码占位符 > @Test void testQueryNoDistinctOptionalityAggCallColInTargetGroupBy1() { > final String mv = "" > + "select \"name\", \"deptno\" " > + "from \"emps\" group by \"name\", \"deptno\""; > final String query = "" > + "select \"name\", min(\"deptno\")\n" > + "from \"emps\" group by \"name\""; > sql(mv, query).ok(); > } > {code} -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Comment Edited] (CALCITE-4817) Expand SubstitutionVisitor of Aggregate with max/min, which column is the group by list of target
[ https://issues.apache.org/jira/browse/CALCITE-4817?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17426099#comment-17426099 ] Ziwei Liu edited comment on CALCITE-4817 at 10/8/21, 10:26 AM: --- this change may have some problem, consider this case final String mv = "" + "select "name", "deptno", "empid", min("commission")\n" + "from "emps" group by "name", "deptno", "empid""; final String query = "" + "select "name", max("deptno"), count(distinct "empid"), min("commission")\n" + "from "emps" group by "name""; mv already has a column of min(commission), but it's getDistinctOptionality is Ignore, then it will not use a RollUp aggregation, if ((aggregateCall.isDistinct() || aggregation.getDistinctOptionality() == Optionality.IGNORED) && aggregateCall.getArgList().size() == 1) this condition need be like if ((aggregateCall.isDistinct() || aggregation.getDistinctOptionality() == Optionality.IGNORED && target.aggCalls.indexOf(aggregateCall) < 0) && aggregateCall.getArgList().size() == 1) was (Author: ziwei liu): this change may have some problem, consider this case final String mv = "" + "select "name", "deptno", "empid", min("commission")\n" + "from "emps" group by "name", "deptno", "empid""; final String query = "" + "select "name", max("deptno"), count(distinct "empid"), min("commission")\n" + "from "emps" group by "name""; mv already has a column of min(commission), but it's getDistinctOptionality is Ignore, then it will not use a RollUp aggregation, if ((aggregateCall.isDistinct() || aggregation.getDistinctOptionality() == Optionality.IGNORED) && aggregateCall.getArgList().size() == 1) this condition need be like if ((aggregateCall.isDistinct() || aggregation.getDistinctOptionality() == Optionality.IGNORED && target.aggCalls.indexOf(aggregateCall) < 0) && aggregateCall.getArgList().size() == 1) > Expand SubstitutionVisitor of Aggregate with max/min, which column is the > group by list of target > - > > Key: CALCITE-4817 > URL: https://issues.apache.org/jira/browse/CALCITE-4817 > Project: Calcite > Issue Type: Bug > Components: core >Reporter: Xurenhe >Assignee: Xurenhe >Priority: Major > Labels: pull-request-available > Fix For: 1.28.0 > > Time Spent: 0.5h > Remaining Estimate: 0h > > Now, SubstitutionVisitor support `Query with distinct aggregate on the column > is matched to the group list existing in target`, as CALCITE-4374 describes. > But. It's fail, when query aggregate with max/min based on the column, which > is in the group by list of target. > Materialized view recognition should also success. > {code:java} > //代码占位符 > @Test void testQueryNoDistinctOptionalityAggCallColInTargetGroupBy1() { > final String mv = "" > + "select \"name\", \"deptno\" " > + "from \"emps\" group by \"name\", \"deptno\""; > final String query = "" > + "select \"name\", min(\"deptno\")\n" > + "from \"emps\" group by \"name\""; > sql(mv, query).ok(); > } > {code} -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Commented] (CALCITE-4817) Expand SubstitutionVisitor of Aggregate with max/min, which column is the group by list of target
[ https://issues.apache.org/jira/browse/CALCITE-4817?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17426099#comment-17426099 ] Ziwei Liu commented on CALCITE-4817: this change may have some problem, consider this case final String mv = "" + "select "name", "deptno", "empid", min("commission")\n" + "from "emps" group by "name", "deptno", "empid""; final String query = "" + "select "name", max("deptno"), count(distinct "empid"), min("commission")\n" + "from "emps" group by "name""; mv already has a column of min(commission), but it's getDistinctOptionality is Ignore, then it will not use a RollUp aggregation, if ((aggregateCall.isDistinct() || aggregation.getDistinctOptionality() == Optionality.IGNORED) && aggregateCall.getArgList().size() == 1) this condition need be like if ((aggregateCall.isDistinct() || aggregation.getDistinctOptionality() == Optionality.IGNORED && target.aggCalls.indexOf(aggregateCall) < 0) && aggregateCall.getArgList().size() == 1) > Expand SubstitutionVisitor of Aggregate with max/min, which column is the > group by list of target > - > > Key: CALCITE-4817 > URL: https://issues.apache.org/jira/browse/CALCITE-4817 > Project: Calcite > Issue Type: Bug > Components: core >Reporter: Xurenhe >Assignee: Xurenhe >Priority: Major > Labels: pull-request-available > Fix For: 1.28.0 > > Time Spent: 0.5h > Remaining Estimate: 0h > > Now, SubstitutionVisitor support `Query with distinct aggregate on the column > is matched to the group list existing in target`, as CALCITE-4374 describes. > But. It's fail, when query aggregate with max/min based on the column, which > is in the group by list of target. > Materialized view recognition should also success. > {code:java} > //代码占位符 > @Test void testQueryNoDistinctOptionalityAggCallColInTargetGroupBy1() { > final String mv = "" > + "select \"name\", \"deptno\" " > + "from \"emps\" group by \"name\", \"deptno\""; > final String query = "" > + "select \"name\", min(\"deptno\")\n" > + "from \"emps\" group by \"name\""; > sql(mv, query).ok(); > } > {code} -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Commented] (CALCITE-4525) Pull up predicate will lost some predicates when project contains same RexInputRef.
[ https://issues.apache.org/jira/browse/CALCITE-4525?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17301577#comment-17301577 ] Ziwei Liu commented on CALCITE-4525: SELECT * FROM ( SELECT deptno AS x, deptno AS y FROM dept WHERE deptno= 20) left join employee right on left.x=right.deptno The RelMdPredicates.getPredicates got from left is y=20 Actually, x=20 is a predicate can be got, but lost > Pull up predicate will lost some predicates when project contains same > RexInputRef. > > > Key: CALCITE-4525 > URL: https://issues.apache.org/jira/browse/CALCITE-4525 > Project: Calcite > Issue Type: Improvement >Reporter: Ziwei Liu >Assignee: Ziwei Liu >Priority: Major > > If project like this a=$0, b=$0 > Pull up predicate is $0='a' > when pull up through project, predicate will only contain b='a' -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Updated] (CALCITE-4525) Pull up predicate will lost some predicates when project contains same RexInputRef.
[ https://issues.apache.org/jira/browse/CALCITE-4525?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Ziwei Liu updated CALCITE-4525: --- Summary: Pull up predicate will lost some predicates when project contains same RexInputRef. (was: Pull up predicate will loss some predicates when project contains same RexInputRef. ) > Pull up predicate will lost some predicates when project contains same > RexInputRef. > > > Key: CALCITE-4525 > URL: https://issues.apache.org/jira/browse/CALCITE-4525 > Project: Calcite > Issue Type: Improvement >Reporter: Ziwei Liu >Assignee: Ziwei Liu >Priority: Major > > If project like this a=$0, b=$0 > Pull up predicate is $0='a' > when pull up through project, predicate will only contain b='a' -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Updated] (CALCITE-4525) Pull up predicate will loss some predicates when project contains same RexInputRef.
[ https://issues.apache.org/jira/browse/CALCITE-4525?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Ziwei Liu updated CALCITE-4525: --- Description: If project like this a=$0, b=$0 Pull up predicate is $0='a' when pull up through project, predicate will only contain b='a' > Pull up predicate will loss some predicates when project contains same > RexInputRef. > > > Key: CALCITE-4525 > URL: https://issues.apache.org/jira/browse/CALCITE-4525 > Project: Calcite > Issue Type: Improvement >Reporter: Ziwei Liu >Assignee: Ziwei Liu >Priority: Major > > If project like this a=$0, b=$0 > Pull up predicate is $0='a' > when pull up through project, predicate will only contain b='a' -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Created] (CALCITE-4525) Pull up predicate will loss some predicates when project contains same RexInputRef.
Ziwei Liu created CALCITE-4525: -- Summary: Pull up predicate will loss some predicates when project contains same RexInputRef. Key: CALCITE-4525 URL: https://issues.apache.org/jira/browse/CALCITE-4525 Project: Calcite Issue Type: Improvement Reporter: Ziwei Liu Assignee: Ziwei Liu -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Closed] (CALCITE-4432) When the RelNode's input is the same subset as the node belonged to, not choose this node as best.
[ https://issues.apache.org/jira/browse/CALCITE-4432?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Ziwei Liu closed CALCITE-4432. -- Resolution: Not A Bug > When the RelNode's input is the same subset as the node belonged to, not > choose this node as best. > -- > > Key: CALCITE-4432 > URL: https://issues.apache.org/jira/browse/CALCITE-4432 > Project: Calcite > Issue Type: Bug > Components: core >Reporter: Ziwei Liu >Assignee: Ziwei Liu >Priority: Major > > If a subset have a cyclic node, the node's input is this subset itself. If > the beset -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Updated] (CALCITE-4432) When the RelNode's input is the same subset as the node belonged to, not choose this node as best.
[ https://issues.apache.org/jira/browse/CALCITE-4432?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Ziwei Liu updated CALCITE-4432: --- Description: If a subset have a cyclic node, the node's input is this subset itself. If the beset > When the RelNode's input is the same subset as the node belonged to, not > choose this node as best. > -- > > Key: CALCITE-4432 > URL: https://issues.apache.org/jira/browse/CALCITE-4432 > Project: Calcite > Issue Type: Bug > Components: core >Reporter: Ziwei Liu >Assignee: Ziwei Liu >Priority: Major > > If a subset have a cyclic node, the node's input is this subset itself. If > the beset -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Updated] (CALCITE-4432) When the RelNode's input is the same subset as the node belonged to, not choose this node as best.
[ https://issues.apache.org/jira/browse/CALCITE-4432?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Ziwei Liu updated CALCITE-4432: --- Summary: When the RelNode's input is the same subset as the node belonged to, not choose this node as best. (was: When the RelNode's input is the same subset as the subset node belonged to, not choose this node as best.) > When the RelNode's input is the same subset as the node belonged to, not > choose this node as best. > -- > > Key: CALCITE-4432 > URL: https://issues.apache.org/jira/browse/CALCITE-4432 > Project: Calcite > Issue Type: Bug > Components: core >Reporter: Ziwei Liu >Assignee: Ziwei Liu >Priority: Major > -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Created] (CALCITE-4432) When the RelNode's input is the same subset as the subset node belonged to, not choose this node as best.
Ziwei Liu created CALCITE-4432: -- Summary: When the RelNode's input is the same subset as the subset node belonged to, not choose this node as best. Key: CALCITE-4432 URL: https://issues.apache.org/jira/browse/CALCITE-4432 Project: Calcite Issue Type: Bug Components: core Reporter: Ziwei Liu Assignee: Ziwei Liu -- This message was sent by Atlassian Jira (v8.3.4#803005)