[jira] [Commented] (CALCITE-5967) UnsupportedOperationException while implementing a call that requires a special collator
[ https://issues.apache.org/jira/browse/CALCITE-5967?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17761863#comment-17761863 ] Ruben Q L commented on CALCITE-5967: [~julianhyde] do you have any other remark? Otherwise I'll move forward and merge the patch into main. > UnsupportedOperationException while implementing a call that requires a > special collator > > > Key: CALCITE-5967 > URL: https://issues.apache.org/jira/browse/CALCITE-5967 > Project: Calcite > Issue Type: Bug > Components: core >Reporter: Ruben Q L >Assignee: Ruben Q L >Priority: Major > Labels: pull-request-available > Fix For: 1.36.0 > > > Regression introduced by a minor change within CALCITE-5914, detected while > testing a downstream project with the latest Calcite main. > CALCITE-5914 (see > [2a96512c|https://github.com/apache/calcite/commit/2a96512c352bda4a5d9c0c80730f5c115ac363d6]) > introduced this apparently innocuous change in > {{RexImpTable#AbstractRexCallImplementor#unboxIfNecessary}}: > {code} > // old > argValueList.stream() > .map(AbstractRexCallImplementor::unboxExpression) > .collect(Collectors.toList()); > => > // new > Util.transform(argValueList, > AbstractRexCallImplementor::unboxExpression); > {code} > Both expressions seem equivalent, however there is a subtle difference: the > old one returns an {{ArrayList}} (where we can add new elements); whereas the > new one returns a {{TransformingList}} that extends {{AbstractList}} and that > does not support {{List#add}}. > After calling {{unboxIfNecessary}}, we might need to modify the argument list > if we need a special collator to perform the operation: > {code} > private ParameterExpression genValueStatement(...) { > ... > optimizedArgValueList = unboxIfNecessary(optimizedArgValueList); > final Expression callValue = > implementSafe(translator, call, optimizedArgValueList); > ... > } > @Override Expression implementSafe(...) { > ... > final Expression fieldComparator = > generateCollatorExpression(relDataType0.getCollation()); > if (fieldComparator != null) { > argValueList.add(fieldComparator); // <--- > UnsupportedOperationException! > } > ... > {code} -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-5952) SemiJoinJoinTransposeRule should check if JoinType supports pushing predicates into its inputs
[ https://issues.apache.org/jira/browse/CALCITE-5952?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Leonid Chistov updated CALCITE-5952: Summary: SemiJoinJoinTransposeRule should check if JoinType supports pushing predicates into its inputs (was: Semi-Join incorrectly reordered with Left-Join by SemiJoinJoinTransposeRule) > SemiJoinJoinTransposeRule should check if JoinType supports pushing > predicates into its inputs > -- > > Key: CALCITE-5952 > URL: https://issues.apache.org/jira/browse/CALCITE-5952 > Project: Calcite > Issue Type: Bug >Affects Versions: 1.35.0 >Reporter: Leonid Chistov >Assignee: Leonid Chistov >Priority: Major > Labels: pull-request-available > > The following test will fail if added to RelOptRulesTest.java > {code:java} > @Test void testCanNotPushSemiJoinToRightJoinBranch() { > final Function relFn = b -> b > .scan("EMP") > .scan("DEPT") > .join(JoinRelType.LEFT, > b.equals( > b.field(2, 0, "DEPTNO"), > b.field(2, 1, "DEPTNO")) > ) > .scan("BONUS") > // semi join only relates to RHS fields of left join > .semiJoin(b.equals( > b.field(2, 0, "DNAME"), > b.field(2, 1, "JOB"))) > .build(); > relFn(relFn).withRule(CoreRules.SEMI_JOIN_JOIN_TRANSPOSE).checkUnchanged(); > } {code} > Produced plan will look like: > {code:java} > LogicalJoin(condition=[=($7, $8)], joinType=[left]) > LogicalTableScan(table=[[scott, EMP]]) > LogicalJoin(condition=[=($1, $4)], joinType=[semi]) > LogicalTableScan(table=[[scott, DEPT]]) > LogicalTableScan(table=[[scott, BONUS]]) {code} > Which is different from the original plan: > {code:java} > LogicalJoin(condition=[=($9, $12)], joinType=[semi]) > LogicalJoin(condition=[=($7, $8)], joinType=[left]) > LogicalTableScan(table=[[scott, EMP]]) > LogicalTableScan(table=[[scott, DEPT]]) > LogicalTableScan(table=[[scott, BONUS]]) {code} > This is not correct - in general case it is not correct to push semi-join to > right side of left-join. > The reason is the following: > Consider rows from *EMP* that have no matching rows in {*}DEPT{*}. These rows > will have *nulls* for *DEPT* columns in the result of left-join and they will > be rejected by the top semi-join. > But if we push semi-join to RHS of left-join, we are going to see rows from > *EMP* with *nulls* on the *DEPT* side in the final result. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-5952) Semi-Join incorrectly reordered with Left-Join by SemiJoinJoinTransposeRule
[ https://issues.apache.org/jira/browse/CALCITE-5952?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17761850#comment-17761850 ] Ruben Q L commented on CALCITE-5952: bq. My new proposal is "SemiJoinJoinTransposeRule should check if JoinType does support pushing predicates into its inputs" Sounds good (y) (nitpick: I'd simplify it a bit by using just "supports" instead of "does support") > Semi-Join incorrectly reordered with Left-Join by SemiJoinJoinTransposeRule > --- > > Key: CALCITE-5952 > URL: https://issues.apache.org/jira/browse/CALCITE-5952 > Project: Calcite > Issue Type: Bug >Affects Versions: 1.35.0 >Reporter: Leonid Chistov >Assignee: Leonid Chistov >Priority: Major > Labels: pull-request-available > > The following test will fail if added to RelOptRulesTest.java > {code:java} > @Test void testCanNotPushSemiJoinToRightJoinBranch() { > final Function relFn = b -> b > .scan("EMP") > .scan("DEPT") > .join(JoinRelType.LEFT, > b.equals( > b.field(2, 0, "DEPTNO"), > b.field(2, 1, "DEPTNO")) > ) > .scan("BONUS") > // semi join only relates to RHS fields of left join > .semiJoin(b.equals( > b.field(2, 0, "DNAME"), > b.field(2, 1, "JOB"))) > .build(); > relFn(relFn).withRule(CoreRules.SEMI_JOIN_JOIN_TRANSPOSE).checkUnchanged(); > } {code} > Produced plan will look like: > {code:java} > LogicalJoin(condition=[=($7, $8)], joinType=[left]) > LogicalTableScan(table=[[scott, EMP]]) > LogicalJoin(condition=[=($1, $4)], joinType=[semi]) > LogicalTableScan(table=[[scott, DEPT]]) > LogicalTableScan(table=[[scott, BONUS]]) {code} > Which is different from the original plan: > {code:java} > LogicalJoin(condition=[=($9, $12)], joinType=[semi]) > LogicalJoin(condition=[=($7, $8)], joinType=[left]) > LogicalTableScan(table=[[scott, EMP]]) > LogicalTableScan(table=[[scott, DEPT]]) > LogicalTableScan(table=[[scott, BONUS]]) {code} > This is not correct - in general case it is not correct to push semi-join to > right side of left-join. > The reason is the following: > Consider rows from *EMP* that have no matching rows in {*}DEPT{*}. These rows > will have *nulls* for *DEPT* columns in the result of left-join and they will > be rejected by the top semi-join. > But if we push semi-join to RHS of left-join, we are going to see rows from > *EMP* with *nulls* on the *DEPT* side in the final result. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-5952) Semi-Join incorrectly reordered with Left-Join by SemiJoinJoinTransposeRule
[ https://issues.apache.org/jira/browse/CALCITE-5952?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17761846#comment-17761846 ] Leonid Chistov commented on CALCITE-5952: - [~rubenql] Yes, in your example we push semi-join to the left side of the left join. When I was writing "should not push SemiJoin to the right (left) input of the Left (Right) Join", I thought that everyone would read it as: "should not push to the right input of Left Join and to the left input of the Right Join". = My new proposal is "SemiJoinJoinTransposeRule should check if JoinType does support pushing predicates into its inputs" = > perhaps it would be nice to add these examples of Left/Right Join where the >rule can be applied OK > Semi-Join incorrectly reordered with Left-Join by SemiJoinJoinTransposeRule > --- > > Key: CALCITE-5952 > URL: https://issues.apache.org/jira/browse/CALCITE-5952 > Project: Calcite > Issue Type: Bug >Affects Versions: 1.35.0 >Reporter: Leonid Chistov >Assignee: Leonid Chistov >Priority: Major > Labels: pull-request-available > > The following test will fail if added to RelOptRulesTest.java > {code:java} > @Test void testCanNotPushSemiJoinToRightJoinBranch() { > final Function relFn = b -> b > .scan("EMP") > .scan("DEPT") > .join(JoinRelType.LEFT, > b.equals( > b.field(2, 0, "DEPTNO"), > b.field(2, 1, "DEPTNO")) > ) > .scan("BONUS") > // semi join only relates to RHS fields of left join > .semiJoin(b.equals( > b.field(2, 0, "DNAME"), > b.field(2, 1, "JOB"))) > .build(); > relFn(relFn).withRule(CoreRules.SEMI_JOIN_JOIN_TRANSPOSE).checkUnchanged(); > } {code} > Produced plan will look like: > {code:java} > LogicalJoin(condition=[=($7, $8)], joinType=[left]) > LogicalTableScan(table=[[scott, EMP]]) > LogicalJoin(condition=[=($1, $4)], joinType=[semi]) > LogicalTableScan(table=[[scott, DEPT]]) > LogicalTableScan(table=[[scott, BONUS]]) {code} > Which is different from the original plan: > {code:java} > LogicalJoin(condition=[=($9, $12)], joinType=[semi]) > LogicalJoin(condition=[=($7, $8)], joinType=[left]) > LogicalTableScan(table=[[scott, EMP]]) > LogicalTableScan(table=[[scott, DEPT]]) > LogicalTableScan(table=[[scott, BONUS]]) {code} > This is not correct - in general case it is not correct to push semi-join to > right side of left-join. > The reason is the following: > Consider rows from *EMP* that have no matching rows in {*}DEPT{*}. These rows > will have *nulls* for *DEPT* columns in the result of left-join and they will > be rejected by the top semi-join. > But if we push semi-join to RHS of left-join, we are going to see rows from > *EMP* with *nulls* on the *DEPT* side in the final result. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-5952) Semi-Join incorrectly reordered with Left-Join by SemiJoinJoinTransposeRule
[ https://issues.apache.org/jira/browse/CALCITE-5952?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17761830#comment-17761830 ] Ruben Q L commented on CALCITE-5952: {quote}Maybe just "SemiJoinJoinTransposeRule should not push SemiJoin to the right (left) input of the Left (Right) Join" {quote} Mmmm, I think that is not entirely accurate, there are cases where Left (Right) Join can apply the rule: if the SemiJoin keys contains keys from X (Join's LHS in SemiJoinJoinTransposeRule example), then the rule can be applied on a LeftJoin (but not on a RightJoin), and vice-versa; right? {noformat} LogicalJoin(condition: emp.bonusId = bonus.id, joinType=[semi]) LogicalJoin(condition: emp.deptId = dept.id, joinType=[left]) LogicalTableScan(table=[[scott, EMP]]) LogicalTableScan(table=[[scott, DEPT]]) LogicalTableScan(table=[[scott, BONUS]]) => valid transformation LogicalJoin(condition: emp.deptId = dept.id, joinType=[left]) LogicalJoin(condition: emp.bonusId = bonus.id, joinType=[semi]) LogicalTableScan(table=[[scott, EMP]]) LogicalTableScan(table=[[scott, BONUS]]) LogicalTableScan(table=[[scott, DEPT]]) {noformat} If they do not exist, perhaps it would be nice to add these examples of Left/Right Join where the rule can be applied (still after your patch) in RelOptRulesTest.java, wdyt? > Semi-Join incorrectly reordered with Left-Join by SemiJoinJoinTransposeRule > --- > > Key: CALCITE-5952 > URL: https://issues.apache.org/jira/browse/CALCITE-5952 > Project: Calcite > Issue Type: Bug >Affects Versions: 1.35.0 >Reporter: Leonid Chistov >Assignee: Leonid Chistov >Priority: Major > Labels: pull-request-available > > The following test will fail if added to RelOptRulesTest.java > {code:java} > @Test void testCanNotPushSemiJoinToRightJoinBranch() { > final Function relFn = b -> b > .scan("EMP") > .scan("DEPT") > .join(JoinRelType.LEFT, > b.equals( > b.field(2, 0, "DEPTNO"), > b.field(2, 1, "DEPTNO")) > ) > .scan("BONUS") > // semi join only relates to RHS fields of left join > .semiJoin(b.equals( > b.field(2, 0, "DNAME"), > b.field(2, 1, "JOB"))) > .build(); > relFn(relFn).withRule(CoreRules.SEMI_JOIN_JOIN_TRANSPOSE).checkUnchanged(); > } {code} > Produced plan will look like: > {code:java} > LogicalJoin(condition=[=($7, $8)], joinType=[left]) > LogicalTableScan(table=[[scott, EMP]]) > LogicalJoin(condition=[=($1, $4)], joinType=[semi]) > LogicalTableScan(table=[[scott, DEPT]]) > LogicalTableScan(table=[[scott, BONUS]]) {code} > Which is different from the original plan: > {code:java} > LogicalJoin(condition=[=($9, $12)], joinType=[semi]) > LogicalJoin(condition=[=($7, $8)], joinType=[left]) > LogicalTableScan(table=[[scott, EMP]]) > LogicalTableScan(table=[[scott, DEPT]]) > LogicalTableScan(table=[[scott, BONUS]]) {code} > This is not correct - in general case it is not correct to push semi-join to > right side of left-join. > The reason is the following: > Consider rows from *EMP* that have no matching rows in {*}DEPT{*}. These rows > will have *nulls* for *DEPT* columns in the result of left-join and they will > be rejected by the top semi-join. > But if we push semi-join to RHS of left-join, we are going to see rows from > *EMP* with *nulls* on the *DEPT* side in the final result. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-5952) Semi-Join incorrectly reordered with Left-Join by SemiJoinJoinTransposeRule
[ https://issues.apache.org/jira/browse/CALCITE-5952?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17761825#comment-17761825 ] Leonid Chistov commented on CALCITE-5952: - [~rubenql] Maybe just "SemiJoinJoinTransposeRule should not push SemiJoin to the right (left) input of the Left (Right) Join" > Semi-Join incorrectly reordered with Left-Join by SemiJoinJoinTransposeRule > --- > > Key: CALCITE-5952 > URL: https://issues.apache.org/jira/browse/CALCITE-5952 > Project: Calcite > Issue Type: Bug >Affects Versions: 1.35.0 >Reporter: Leonid Chistov >Assignee: Leonid Chistov >Priority: Major > Labels: pull-request-available > > The following test will fail if added to RelOptRulesTest.java > {code:java} > @Test void testCanNotPushSemiJoinToRightJoinBranch() { > final Function relFn = b -> b > .scan("EMP") > .scan("DEPT") > .join(JoinRelType.LEFT, > b.equals( > b.field(2, 0, "DEPTNO"), > b.field(2, 1, "DEPTNO")) > ) > .scan("BONUS") > // semi join only relates to RHS fields of left join > .semiJoin(b.equals( > b.field(2, 0, "DNAME"), > b.field(2, 1, "JOB"))) > .build(); > relFn(relFn).withRule(CoreRules.SEMI_JOIN_JOIN_TRANSPOSE).checkUnchanged(); > } {code} > Produced plan will look like: > {code:java} > LogicalJoin(condition=[=($7, $8)], joinType=[left]) > LogicalTableScan(table=[[scott, EMP]]) > LogicalJoin(condition=[=($1, $4)], joinType=[semi]) > LogicalTableScan(table=[[scott, DEPT]]) > LogicalTableScan(table=[[scott, BONUS]]) {code} > Which is different from the original plan: > {code:java} > LogicalJoin(condition=[=($9, $12)], joinType=[semi]) > LogicalJoin(condition=[=($7, $8)], joinType=[left]) > LogicalTableScan(table=[[scott, EMP]]) > LogicalTableScan(table=[[scott, DEPT]]) > LogicalTableScan(table=[[scott, BONUS]]) {code} > This is not correct - in general case it is not correct to push semi-join to > right side of left-join. > The reason is the following: > Consider rows from *EMP* that have no matching rows in {*}DEPT{*}. These rows > will have *nulls* for *DEPT* columns in the result of left-join and they will > be rejected by the top semi-join. > But if we push semi-join to RHS of left-join, we are going to see rows from > *EMP* with *nulls* on the *DEPT* side in the final result. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Assigned] (CALCITE-5974) Es index mapping set dynamic_templates without properties causing bug
[ https://issues.apache.org/jira/browse/CALCITE-5974?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] zhaowang reassigned CALCITE-5974: - Assignee: zhaowang > Es index mapping set dynamic_templates without properties causing bug > - > > Key: CALCITE-5974 > URL: https://issues.apache.org/jira/browse/CALCITE-5974 > Project: Calcite > Issue Type: Bug >Affects Versions: 1.35.0 >Reporter: zhaowang >Assignee: zhaowang >Priority: Major > > When use es-adapter, we config a es cluster, it will fetchMapping during > initialization. > If a index set dynamic_templates bug no mappings like this: > > {code:java} > { > "test_index" : { > "mappings" : { > "dynamic_templates" : [ > { > "integers" : { > "match_mapping_type" : "long", > "mapping" : { > "type" : "integer" > } > } > } > ] > } > } > } {code} > > > org.apache.calcite.adapter.elasticsearch.ElasticsearchJson#visitMappingProperties > throw exception: > > {code:java} > java.lang.ClassCastException: > com.fasterxml.jackson.databind.node.ArrayNode cannot be cast to > com.fasterxml.jackson.databind.node.ObjectNode > at > > org.apache.calcite.adapter.elasticsearch.ElasticsearchJson.visitMappingProperties(ElasticsearchJson.java:133) > {code} > So ElasticsearchTransport initialize failed, all index of this es cluster > access failed. > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-5952) Semi-Join incorrectly reordered with Left-Join by SemiJoinJoinTransposeRule
[ https://issues.apache.org/jira/browse/CALCITE-5952?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17761799#comment-17761799 ] Ruben Q L commented on CALCITE-5952: Thanks [~lchistov1987]. IMO both tests are complementary (and relevant), if I had to chose, I'd prefer the runtime test; but the more tests, the better. Normally, the process that we follow to fix a bug is: - Produce a unit test that shows the bug, i.e. it fails. - Correct the issue. - The test from the 1st point now passes. In terms of the test for the 1st point, IMO it is better to show the issue on an actual query (ideally in SQL, but I agree that Quidem tests do have some limitation and there is some discussion going on). So I think it is more clear (and closer to a real-life scenario) producing a query plan that "suffers from the problem", either it throws and exception or it produces a wrong the result. I don't know what others might think, but these are my 2 cents on this topic. Regarding the PR, it looks good. Perhaps the Jira/PR tittle (and the commit message) is not entirely accurate: it talks about LeftJoin, but the problem can also occur with RightJoin. What about something along the lines: "SemiJoinJoinTransposeRule must not be applied if SemiJoin has keys from Join's LHS/RHS and Join type does not support pushing predicates into its left/right input"? (a bit long, I know, but I cant come up with a better title, suggestions are welcome :) > Semi-Join incorrectly reordered with Left-Join by SemiJoinJoinTransposeRule > --- > > Key: CALCITE-5952 > URL: https://issues.apache.org/jira/browse/CALCITE-5952 > Project: Calcite > Issue Type: Bug >Affects Versions: 1.35.0 >Reporter: Leonid Chistov >Assignee: Leonid Chistov >Priority: Major > Labels: pull-request-available > > The following test will fail if added to RelOptRulesTest.java > {code:java} > @Test void testCanNotPushSemiJoinToRightJoinBranch() { > final Function relFn = b -> b > .scan("EMP") > .scan("DEPT") > .join(JoinRelType.LEFT, > b.equals( > b.field(2, 0, "DEPTNO"), > b.field(2, 1, "DEPTNO")) > ) > .scan("BONUS") > // semi join only relates to RHS fields of left join > .semiJoin(b.equals( > b.field(2, 0, "DNAME"), > b.field(2, 1, "JOB"))) > .build(); > relFn(relFn).withRule(CoreRules.SEMI_JOIN_JOIN_TRANSPOSE).checkUnchanged(); > } {code} > Produced plan will look like: > {code:java} > LogicalJoin(condition=[=($7, $8)], joinType=[left]) > LogicalTableScan(table=[[scott, EMP]]) > LogicalJoin(condition=[=($1, $4)], joinType=[semi]) > LogicalTableScan(table=[[scott, DEPT]]) > LogicalTableScan(table=[[scott, BONUS]]) {code} > Which is different from the original plan: > {code:java} > LogicalJoin(condition=[=($9, $12)], joinType=[semi]) > LogicalJoin(condition=[=($7, $8)], joinType=[left]) > LogicalTableScan(table=[[scott, EMP]]) > LogicalTableScan(table=[[scott, DEPT]]) > LogicalTableScan(table=[[scott, BONUS]]) {code} > This is not correct - in general case it is not correct to push semi-join to > right side of left-join. > The reason is the following: > Consider rows from *EMP* that have no matching rows in {*}DEPT{*}. These rows > will have *nulls* for *DEPT* columns in the result of left-join and they will > be rejected by the top semi-join. > But if we push semi-join to RHS of left-join, we are going to see rows from > *EMP* with *nulls* on the *DEPT* side in the final result. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-5952) Semi-Join incorrectly reordered with Left-Join by SemiJoinJoinTransposeRule
[ https://issues.apache.org/jira/browse/CALCITE-5952?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17761799#comment-17761799 ] Ruben Q L edited comment on CALCITE-5952 at 9/4/23 11:54 AM: - Thanks [~lchistov1987]. IMO both tests are complementary (and relevant), if I had to chose, I'd prefer the runtime test; but the more tests, the better. Normally, the process that we follow to fix a bug is: - Produce a unit test that shows the bug, i.e. it fails. - Correct the issue. - The test from the 1st point now passes. In terms of the test for the 1st point, IMO it is better to show the issue on an actual query (ideally in SQL, but I agree that Quidem tests do have some limitation and there is some discussion going on). So I think it is more clear (and closer to a real-life scenario) producing a query plan that "suffers from the problem", either it throws and exception or it produces a wrong the result. I don't know what others might think, but these are my 2 cents on this topic. Regarding the PR, it looks good. Perhaps the Jira/PR tittle (and the commit message) is not entirely accurate: it talks about LeftJoin, but the problem can also occur with RightJoin. What about something along the lines: "SemiJoinJoinTransposeRule must not be applied if SemiJoin has keys from Join's LHS/RHS and Join type does not support pushing predicates into its left/right input"? (a bit long, I know, but I cant come up with a better title, suggestions are welcome :) ) was (Author: rubenql): Thanks [~lchistov1987]. IMO both tests are complementary (and relevant), if I had to chose, I'd prefer the runtime test; but the more tests, the better. Normally, the process that we follow to fix a bug is: - Produce a unit test that shows the bug, i.e. it fails. - Correct the issue. - The test from the 1st point now passes. In terms of the test for the 1st point, IMO it is better to show the issue on an actual query (ideally in SQL, but I agree that Quidem tests do have some limitation and there is some discussion going on). So I think it is more clear (and closer to a real-life scenario) producing a query plan that "suffers from the problem", either it throws and exception or it produces a wrong the result. I don't know what others might think, but these are my 2 cents on this topic. Regarding the PR, it looks good. Perhaps the Jira/PR tittle (and the commit message) is not entirely accurate: it talks about LeftJoin, but the problem can also occur with RightJoin. What about something along the lines: "SemiJoinJoinTransposeRule must not be applied if SemiJoin has keys from Join's LHS/RHS and Join type does not support pushing predicates into its left/right input"? (a bit long, I know, but I cant come up with a better title, suggestions are welcome :) > Semi-Join incorrectly reordered with Left-Join by SemiJoinJoinTransposeRule > --- > > Key: CALCITE-5952 > URL: https://issues.apache.org/jira/browse/CALCITE-5952 > Project: Calcite > Issue Type: Bug >Affects Versions: 1.35.0 >Reporter: Leonid Chistov >Assignee: Leonid Chistov >Priority: Major > Labels: pull-request-available > > The following test will fail if added to RelOptRulesTest.java > {code:java} > @Test void testCanNotPushSemiJoinToRightJoinBranch() { > final Function relFn = b -> b > .scan("EMP") > .scan("DEPT") > .join(JoinRelType.LEFT, > b.equals( > b.field(2, 0, "DEPTNO"), > b.field(2, 1, "DEPTNO")) > ) > .scan("BONUS") > // semi join only relates to RHS fields of left join > .semiJoin(b.equals( > b.field(2, 0, "DNAME"), > b.field(2, 1, "JOB"))) > .build(); > relFn(relFn).withRule(CoreRules.SEMI_JOIN_JOIN_TRANSPOSE).checkUnchanged(); > } {code} > Produced plan will look like: > {code:java} > LogicalJoin(condition=[=($7, $8)], joinType=[left]) > LogicalTableScan(table=[[scott, EMP]]) > LogicalJoin(condition=[=($1, $4)], joinType=[semi]) > LogicalTableScan(table=[[scott, DEPT]]) > LogicalTableScan(table=[[scott, BONUS]]) {code} > Which is different from the original plan: > {code:java} > LogicalJoin(condition=[=($9, $12)], joinType=[semi]) > LogicalJoin(condition=[=($7, $8)], joinType=[left]) > LogicalTableScan(table=[[scott, EMP]]) > LogicalTableScan(table=[[scott, DEPT]]) > LogicalTableScan(table=[[scott, BONUS]]) {code} > This is not correct - in general case it is not correct to push semi-join to > right side of left-join. > The reason is the following: > Consider rows from *EMP* that have no matching rows in {*}DEPT{*}. These rows > will have *nulls* for *DEPT* columns in the result of left-join and they will > be rejected by the top
[jira] [Updated] (CALCITE-5952) Semi-Join incorrectly reordered with Left-Join by SemiJoinJoinTransposeRule
[ https://issues.apache.org/jira/browse/CALCITE-5952?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Leonid Chistov updated CALCITE-5952: Labels: pull-request-available (was: ) > Semi-Join incorrectly reordered with Left-Join by SemiJoinJoinTransposeRule > --- > > Key: CALCITE-5952 > URL: https://issues.apache.org/jira/browse/CALCITE-5952 > Project: Calcite > Issue Type: Bug >Affects Versions: 1.35.0 >Reporter: Leonid Chistov >Assignee: Leonid Chistov >Priority: Major > Labels: pull-request-available > > The following test will fail if added to RelOptRulesTest.java > {code:java} > @Test void testCanNotPushSemiJoinToRightJoinBranch() { > final Function relFn = b -> b > .scan("EMP") > .scan("DEPT") > .join(JoinRelType.LEFT, > b.equals( > b.field(2, 0, "DEPTNO"), > b.field(2, 1, "DEPTNO")) > ) > .scan("BONUS") > // semi join only relates to RHS fields of left join > .semiJoin(b.equals( > b.field(2, 0, "DNAME"), > b.field(2, 1, "JOB"))) > .build(); > relFn(relFn).withRule(CoreRules.SEMI_JOIN_JOIN_TRANSPOSE).checkUnchanged(); > } {code} > Produced plan will look like: > {code:java} > LogicalJoin(condition=[=($7, $8)], joinType=[left]) > LogicalTableScan(table=[[scott, EMP]]) > LogicalJoin(condition=[=($1, $4)], joinType=[semi]) > LogicalTableScan(table=[[scott, DEPT]]) > LogicalTableScan(table=[[scott, BONUS]]) {code} > Which is different from the original plan: > {code:java} > LogicalJoin(condition=[=($9, $12)], joinType=[semi]) > LogicalJoin(condition=[=($7, $8)], joinType=[left]) > LogicalTableScan(table=[[scott, EMP]]) > LogicalTableScan(table=[[scott, DEPT]]) > LogicalTableScan(table=[[scott, BONUS]]) {code} > This is not correct - in general case it is not correct to push semi-join to > right side of left-join. > The reason is the following: > Consider rows from *EMP* that have no matching rows in {*}DEPT{*}. These rows > will have *nulls* for *DEPT* columns in the result of left-join and they will > be rejected by the top semi-join. > But if we push semi-join to RHS of left-join, we are going to see rows from > *EMP* with *nulls* on the *DEPT* side in the final result. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-5952) Semi-Join incorrectly reordered with Left-Join by SemiJoinJoinTransposeRule
[ https://issues.apache.org/jira/browse/CALCITE-5952?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17761757#comment-17761757 ] Leonid Chistov commented on CALCITE-5952: - [~rubenql] I have prepared a Pull Request: [https://github.com/apache/calcite/pull/3407] and added the runtime test also. But I don't really like it since it looks like this approach will force test authors to implement tests for optimization rules twice - one test for plan correctness and other for runtime correctness. Also, there is a new email thread about the same question [https://lists.apache.org/thread/r4yhn77m92fodmz8xm6k40sfd130w7hh,] which ends with an idea that we probably should establish proper infrastructure for specific rules testing in Quidem test. WDYT? > Semi-Join incorrectly reordered with Left-Join by SemiJoinJoinTransposeRule > --- > > Key: CALCITE-5952 > URL: https://issues.apache.org/jira/browse/CALCITE-5952 > Project: Calcite > Issue Type: Bug >Affects Versions: 1.35.0 >Reporter: Leonid Chistov >Assignee: Leonid Chistov >Priority: Major > > The following test will fail if added to RelOptRulesTest.java > {code:java} > @Test void testCanNotPushSemiJoinToRightJoinBranch() { > final Function relFn = b -> b > .scan("EMP") > .scan("DEPT") > .join(JoinRelType.LEFT, > b.equals( > b.field(2, 0, "DEPTNO"), > b.field(2, 1, "DEPTNO")) > ) > .scan("BONUS") > // semi join only relates to RHS fields of left join > .semiJoin(b.equals( > b.field(2, 0, "DNAME"), > b.field(2, 1, "JOB"))) > .build(); > relFn(relFn).withRule(CoreRules.SEMI_JOIN_JOIN_TRANSPOSE).checkUnchanged(); > } {code} > Produced plan will look like: > {code:java} > LogicalJoin(condition=[=($7, $8)], joinType=[left]) > LogicalTableScan(table=[[scott, EMP]]) > LogicalJoin(condition=[=($1, $4)], joinType=[semi]) > LogicalTableScan(table=[[scott, DEPT]]) > LogicalTableScan(table=[[scott, BONUS]]) {code} > Which is different from the original plan: > {code:java} > LogicalJoin(condition=[=($9, $12)], joinType=[semi]) > LogicalJoin(condition=[=($7, $8)], joinType=[left]) > LogicalTableScan(table=[[scott, EMP]]) > LogicalTableScan(table=[[scott, DEPT]]) > LogicalTableScan(table=[[scott, BONUS]]) {code} > This is not correct - in general case it is not correct to push semi-join to > right side of left-join. > The reason is the following: > Consider rows from *EMP* that have no matching rows in {*}DEPT{*}. These rows > will have *nulls* for *DEPT* columns in the result of left-join and they will > be rejected by the top semi-join. > But if we push semi-join to RHS of left-join, we are going to see rows from > *EMP* with *nulls* on the *DEPT* side in the final result. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-5975) percentile_cont function throws UnsupportedOperationException
[ https://issues.apache.org/jira/browse/CALCITE-5975?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] wqn updated CALCITE-5975: - Description: execute a sql like {code:java} SELECT percentile_cont(0.0005) within group (ORDER BY aa DESC) from table{code} it throws UnsupportedOperationException {code:java} Caused by: java.lang.UnsupportedOperationException: null at org.apache.calcite.sql.SqlOperatorBinding.getCollationType(SqlOperatorBinding.java:244) at org.apache.calcite.sql.type.ReturnTypes.lambda$static$25(ReturnTypes.java:1285) at org.apache.calcite.sql.SqlOperator.inferReturnType(SqlOperator.java:533) at org.apache.calcite.rel.core.AggregateCall.create(AggregateCall.java:198) at org.apache.calcite.tools.RelBuilder$AggCallImpl.aggregateCall(RelBuilder.java:4173) at org.apache.calcite.tools.RelBuilder.aggregate_(RelBuilder.java:2424) at org.apache.calcite.tools.RelBuilder.aggregate(RelBuilder.java:2348) at org.apache.calcite.sql2rel.RelFieldTrimmer.trimFields(RelFieldTrimmer.java:1102) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:532) at org.apache.calcite.sql2rel.RelFieldTrimmer.dispatchTrimFields(RelFieldTrimmer.java:283) at org.apache.calcite.sql2rel.RelFieldTrimmer.trimChild(RelFieldTrimmer.java:225) at org.apache.calcite.sql2rel.RelFieldTrimmer.trimFields(RelFieldTrimmer.java:494) at sun.reflect.GeneratedMethodAccessor1005.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:532) at org.apache.calcite.sql2rel.RelFieldTrimmer.dispatchTrimFields(RelFieldTrimmer.java:283) at org.apache.calcite.sql2rel.RelFieldTrimmer.trimChild(RelFieldTrimmer.java:225) at org.apache.calcite.sql2rel.RelFieldTrimmer.trimFields(RelFieldTrimmer.java:824) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:532) at org.apache.calcite.sql2rel.RelFieldTrimmer.dispatchTrimFields(RelFieldTrimmer.java:283) {code} was: execute a sql like {code:java} SELECT percentile_cont(0.0005) within group (ORDER BY a DESC) AS a from table{code} it throws UnsupportedOperationException {code:java} Caused by: java.lang.UnsupportedOperationException: null at org.apache.calcite.sql.SqlOperatorBinding.getCollationType(SqlOperatorBinding.java:244) at org.apache.calcite.sql.type.ReturnTypes.lambda$static$25(ReturnTypes.java:1285) at org.apache.calcite.sql.SqlOperator.inferReturnType(SqlOperator.java:533) at org.apache.calcite.rel.core.AggregateCall.create(AggregateCall.java:198) at org.apache.calcite.tools.RelBuilder$AggCallImpl.aggregateCall(RelBuilder.java:4173) at org.apache.calcite.tools.RelBuilder.aggregate_(RelBuilder.java:2424) at org.apache.calcite.tools.RelBuilder.aggregate(RelBuilder.java:2348) at org.apache.calcite.sql2rel.RelFieldTrimmer.trimFields(RelFieldTrimmer.java:1102) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:532) at org.apache.calcite.sql2rel.RelFieldTrimmer.dispatchTrimFields(RelFieldTrimmer.java:283) at org.apache.calcite.sql2rel.RelFieldTrimmer.trimChild(RelFieldTrimmer.java:225) at org.apache.calcite.sql2rel.RelFieldTrimmer.trimFields(RelFieldTrimmer.java:494) at sun.reflect.GeneratedMethodAccessor1005.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:532) at org.apache.calcite.sql2rel.RelFieldTrimmer.dispatchTrimFields(RelFieldTrimmer.java:283) at org.apache.calcite.sql2rel.RelFie
[jira] [Updated] (CALCITE-5975) percentile_cont function throws UnsupportedOperationException
[ https://issues.apache.org/jira/browse/CALCITE-5975?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] wqn updated CALCITE-5975: - Description: execute a sql like {code:java} SELECT percentile_cont(0.0005) within group (ORDER BY a DESC) AS a from table{code} it throws UnsupportedOperationException {code:java} Caused by: java.lang.UnsupportedOperationException: null at org.apache.calcite.sql.SqlOperatorBinding.getCollationType(SqlOperatorBinding.java:244) at org.apache.calcite.sql.type.ReturnTypes.lambda$static$25(ReturnTypes.java:1285) at org.apache.calcite.sql.SqlOperator.inferReturnType(SqlOperator.java:533) at org.apache.calcite.rel.core.AggregateCall.create(AggregateCall.java:198) at org.apache.calcite.tools.RelBuilder$AggCallImpl.aggregateCall(RelBuilder.java:4173) at org.apache.calcite.tools.RelBuilder.aggregate_(RelBuilder.java:2424) at org.apache.calcite.tools.RelBuilder.aggregate(RelBuilder.java:2348) at org.apache.calcite.sql2rel.RelFieldTrimmer.trimFields(RelFieldTrimmer.java:1102) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:532) at org.apache.calcite.sql2rel.RelFieldTrimmer.dispatchTrimFields(RelFieldTrimmer.java:283) at org.apache.calcite.sql2rel.RelFieldTrimmer.trimChild(RelFieldTrimmer.java:225) at org.apache.calcite.sql2rel.RelFieldTrimmer.trimFields(RelFieldTrimmer.java:494) at sun.reflect.GeneratedMethodAccessor1005.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:532) at org.apache.calcite.sql2rel.RelFieldTrimmer.dispatchTrimFields(RelFieldTrimmer.java:283) at org.apache.calcite.sql2rel.RelFieldTrimmer.trimChild(RelFieldTrimmer.java:225) at org.apache.calcite.sql2rel.RelFieldTrimmer.trimFields(RelFieldTrimmer.java:824) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:532) at org.apache.calcite.sql2rel.RelFieldTrimmer.dispatchTrimFields(RelFieldTrimmer.java:283) {code} was: execute a sql like SELECT SUM(compen_fund_use_amt_1d) AS b , percentile_cont(0.0005) within group (ORDER BY compen_fund_use_amt_1d DESC) AS a from data_analysis_holo.ads_tb_crm_as_compen_exp_threshold_coupon_user_index_d"" it throws UnsupportedOperationException > percentile_cont function throws UnsupportedOperationException > - > > Key: CALCITE-5975 > URL: https://issues.apache.org/jira/browse/CALCITE-5975 > Project: Calcite > Issue Type: Bug >Affects Versions: 1.35.0 >Reporter: wqn >Priority: Major > > execute a sql like > {code:java} > SELECT percentile_cont(0.0005) within group (ORDER BY a DESC) AS a from > table{code} > it throws UnsupportedOperationException > {code:java} > Caused by: java.lang.UnsupportedOperationException: null at > org.apache.calcite.sql.SqlOperatorBinding.getCollationType(SqlOperatorBinding.java:244) > at > org.apache.calcite.sql.type.ReturnTypes.lambda$static$25(ReturnTypes.java:1285) > at > org.apache.calcite.sql.SqlOperator.inferReturnType(SqlOperator.java:533) > at org.apache.calcite.rel.core.AggregateCall.create(AggregateCall.java:198) > at > org.apache.calcite.tools.RelBuilder$AggCallImpl.aggregateCall(RelBuilder.java:4173) > at > org.apache.calcite.tools.RelBuilder.aggregate_(RelBuilder.java:2424) > at org.apache.calcite.tools.RelBuilder.aggregate(RelBuilder.java:2348) > at > org.apache.calcite.sql2rel.RelFieldTrimmer.trimFields(RelFieldTrimmer.java:1102) > at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) > at > sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) > at > sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) > at java.lang.reflect.Method.invoke(Method.java:498) at > org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:532) at > org.apa
[jira] [Created] (CALCITE-5975) percentile_cont function throws UnsupportedOperationException
wqn created CALCITE-5975: Summary: percentile_cont function throws UnsupportedOperationException Key: CALCITE-5975 URL: https://issues.apache.org/jira/browse/CALCITE-5975 Project: Calcite Issue Type: Bug Affects Versions: 1.35.0 Reporter: wqn execute a sql like SELECT SUM(compen_fund_use_amt_1d) AS b , percentile_cont(0.0005) within group (ORDER BY compen_fund_use_amt_1d DESC) AS a from data_analysis_holo.ads_tb_crm_as_compen_exp_threshold_coupon_user_index_d"" it throws UnsupportedOperationException -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-5972) cannot execute a sql like 'insert into A select * from B'
[ https://issues.apache.org/jira/browse/CALCITE-5972?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17761712#comment-17761712 ] Alessandro Solimando commented on CALCITE-5972: --- For now I suggest to keep it open and decide what to do based on the discussion on the ML (resolve it, improve description if confirmed, duplicate if already existing, etc.) > cannot execute a sql like 'insert into A select * from B' > - > > Key: CALCITE-5972 > URL: https://issues.apache.org/jira/browse/CALCITE-5972 > Project: Calcite > Issue Type: Bug >Affects Versions: 1.35.0 >Reporter: Moccy.T >Priority: Blocker > > I have put the > {code:java} > org.apache.calcite.adapter.csv.CsvScannableTable{code} > and its related classes (Factory, Reader.., etc.) into my test project. > When I tried to execute a sql like > {code:java} > insert into mysql.calcite_test (select * from csv) {code} > an NPE was thrown. where `mysql` is recognised by JdbcSchema#Factory. When I > debugged, I found that the bind method of the dynamically generated Bindable > code called the JdbcTable's getModifiableCollection() method, which returned > null directly. the bind method's subsequent call to collection.size() threw > an NPE. this code is identified by the This code is generated by > EnumerableTableModify.class. > After that, I tried to execute > {code:java} > insert into mysql.calcite(select * from mysql2.calcite2){code} > There are two JdbcSchema#Factory recognised data sources here, but I found > that the code that was eventually generated by > JdbcToEnumerableConverter.class executed > {code:java} > insert into calcite(select * from mysql2.calcite){code} > in `mysql2` without performing the insert in the `mysql` library. > Are these issues intentional with calcite or are they bugs? > > -- This message was sent by Atlassian Jira (v8.20.10#820010)