[jira] [Comment Edited] (CALCITE-2772) Support varargs for user-defined functions (UDFs)
[ https://issues.apache.org/jira/browse/CALCITE-2772?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17167648#comment-17167648 ] pengzhiwei edited comment on CALCITE-2772 at 7/30/20, 5:55 AM: --- Thank [~Chunwei Lei],you can go on with this work. was (Author: pzw2018): Thank [~Chunwei Lei],you can you go on with this work. > Support varargs for user-defined functions (UDFs) > - > > Key: CALCITE-2772 > URL: https://issues.apache.org/jira/browse/CALCITE-2772 > Project: Calcite > Issue Type: Improvement > Components: core >Reporter: pengzhiwei >Assignee: pengzhiwei >Priority: Major > Labels: pull-request-available > Attachments: support_varargs_udf.patch > > Time Spent: 5h 20m > Remaining Estimate: 0h > > Support varargs for user-defined functions as the case followed: > {code:java} > public class ConcatWs { > public String eval(String sep, String... strs) {...} > }{code} > -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Commented] (CALCITE-2772) Support varargs for user-defined functions (UDFs)
[ https://issues.apache.org/jira/browse/CALCITE-2772?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17167648#comment-17167648 ] pengzhiwei commented on CALCITE-2772: - Thank [~Chunwei Lei],you can you go on with this work. > Support varargs for user-defined functions (UDFs) > - > > Key: CALCITE-2772 > URL: https://issues.apache.org/jira/browse/CALCITE-2772 > Project: Calcite > Issue Type: Improvement > Components: core >Reporter: pengzhiwei >Assignee: pengzhiwei >Priority: Major > Labels: pull-request-available > Attachments: support_varargs_udf.patch > > Time Spent: 5h 20m > Remaining Estimate: 0h > > Support varargs for user-defined functions as the case followed: > {code:java} > public class ConcatWs { > public String eval(String sep, String... strs) {...} > }{code} > -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Commented] (CALCITE-3738) Missing LogicalSort for INSERT statement
[ https://issues.apache.org/jira/browse/CALCITE-3738?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17028624#comment-17028624 ] pengzhiwei commented on CALCITE-3738: - Thank [~hyuan] for your help on this issue. > Missing LogicalSort for INSERT statement > > > Key: CALCITE-3738 > URL: https://issues.apache.org/jira/browse/CALCITE-3738 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.19.0 >Reporter: pengzhiwei >Assignee: pengzhiwei >Priority: Major > Labels: pull-request-available > Fix For: 1.22.0 > > Time Spent: 2h 10m > Remaining Estimate: 0h > > The following sql statement generates a wrong logical plan which missing the > LogicalSort : > {code:java} > insert into t select id*10,name from users order by name;{code} > the plan is : > {code:java} > LogicalTableModify(table=[[t]], operation=[INSERT], flattened=[false]) > LogicalProject(id=[*($0, 10)], name=[$1]) > LogicalTableScan(table=[[users]]) > {code} > This issue is introduced by the issue > https://issues.apache.org/jira/browse/CALCITE-2978 > which removes the sort by in sub-query.However the query in insert statement > is not a sub-query. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Commented] (CALCITE-3746) RexSimplify changes the order of IS NOT NULL in And RexNode
[ https://issues.apache.org/jira/browse/CALCITE-3746?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17019251#comment-17019251 ] pengzhiwei commented on CALCITE-3746: - Hi [~vladimirsitnikov] ,"length" is just an example.I mean any function "f". We cannot always think "f" cannot received "NULL" parameters. > RexSimplify changes the order of IS NOT NULL in And RexNode > --- > > Key: CALCITE-3746 > URL: https://issues.apache.org/jira/browse/CALCITE-3746 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.20.0 >Reporter: pengzhiwei >Assignee: pengzhiwei >Priority: Major > Labels: pull-request-available > Time Spent: 10m > Remaining Estimate: 0h > > The RexSimplify changes the order of IS NOT NULL in And RexNode.The following > expression > {code:java} > a is not null and length(a) > 0{code} > is optimazted to > {code:java} > length(a) > 0 and a is not null{code} > which will affect the logic short circuit for null-test. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Commented] (CALCITE-3746) RexSimplify changes the order of IS NOT NULL in And RexNode
[ https://issues.apache.org/jira/browse/CALCITE-3746?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17018796#comment-17018796 ] pengzhiwei commented on CALCITE-3746: - Hi [~vladimirsitnikov],the expression "a is not null and f(a)" compute "a is not null" first. If a is not null, then compute "f(a)",.It cannot avoid NPE in "f(a)".Howerver, after the optimization,"f(a)" execute first.This will throw NPE in "f(a)" if "f" cannot deal with the case "a = null". > RexSimplify changes the order of IS NOT NULL in And RexNode > --- > > Key: CALCITE-3746 > URL: https://issues.apache.org/jira/browse/CALCITE-3746 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.20.0 >Reporter: pengzhiwei >Assignee: pengzhiwei >Priority: Major > Labels: pull-request-available > Time Spent: 10m > Remaining Estimate: 0h > > The RexSimplify changes the order of IS NOT NULL in And RexNode.The following > expression > {code:java} > a is not null and length(a) > 0{code} > is optimazted to > {code:java} > length(a) > 0 and a is not null{code} > which will affect the logic short circuit for null-test. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Created] (CALCITE-3746) RexSimplify changes the order of IS NOT NULL in And RexNode
pengzhiwei created CALCITE-3746: --- Summary: RexSimplify changes the order of IS NOT NULL in And RexNode Key: CALCITE-3746 URL: https://issues.apache.org/jira/browse/CALCITE-3746 Project: Calcite Issue Type: Bug Components: core Affects Versions: 1.20.0 Reporter: pengzhiwei Assignee: pengzhiwei The RexSimplify changes the order of IS NOT NULL in And RexNode.The following expression {code:java} a is not null and length(a) > 0{code} is optimazted to {code:java} length(a) > 0 and a is not null{code} which will affect the logic short circuit for null-test. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Updated] (CALCITE-3738) Missing LogicalSort for Insert statement
[ https://issues.apache.org/jira/browse/CALCITE-3738?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] pengzhiwei updated CALCITE-3738: Summary: Missing LogicalSort for Insert statement (was: Missing LogicalSort for insert statement) > Missing LogicalSort for Insert statement > > > Key: CALCITE-3738 > URL: https://issues.apache.org/jira/browse/CALCITE-3738 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.19.0 >Reporter: pengzhiwei >Assignee: pengzhiwei >Priority: Major > Labels: pull-request-available > Time Spent: 1.5h > Remaining Estimate: 0h > > The following sql statement generates a wrong logical plan which missing the > LogicalSort : > {code:java} > insert into t select id*10,name from users order by name;{code} > the plan is : > {code:java} > LogicalTableModify(table=[[t]], operation=[INSERT], flattened=[false]) > LogicalProject(id=[*($0, 10)], name=[$1]) > LogicalTableScan(table=[[users]]) > {code} > This issue is introduced by the issue > https://issues.apache.org/jira/browse/CALCITE-2978 > which removes the sort by in sub-query.However the query in insert statement > is not a sub-query. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Reopened] (CALCITE-3738) Missing LogicalSort for insert statement
[ https://issues.apache.org/jira/browse/CALCITE-3738?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] pengzhiwei reopened CALCITE-3738: - > Missing LogicalSort for insert statement > > > Key: CALCITE-3738 > URL: https://issues.apache.org/jira/browse/CALCITE-3738 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.19.0 >Reporter: pengzhiwei >Assignee: pengzhiwei >Priority: Major > Labels: pull-request-available > Time Spent: 1.5h > Remaining Estimate: 0h > > The following sql statement generates a wrong logical plan which missing the > LogicalSort : > {code:java} > insert into t select id*10,name from users order by name;{code} > the plan is : > {code:java} > LogicalTableModify(table=[[t]], operation=[INSERT], flattened=[false]) > LogicalProject(id=[*($0, 10)], name=[$1]) > LogicalTableScan(table=[[users]]) > {code} > This issue is introduced by the issue > https://issues.apache.org/jira/browse/CALCITE-2978 > which removes the sort by in sub-query.However the query in insert statement > is not a sub-query. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Comment Edited] (CALCITE-3738) Missing LogicalSort for insert statement
[ https://issues.apache.org/jira/browse/CALCITE-3738?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17016606#comment-17016606 ] pengzhiwei edited comment on CALCITE-3738 at 1/16/20 7:12 AM: -- Hi [~julianhyde] and [~hyuan] , Thanks for your attention. I think the SQL is not fully equivalent to the generated RelNode if we remove the LogicalSort. Maybe in some usage scenario, the target table cannot guarantee the order of the input rows. But in some case, the target table really need the ordering. For example,I want to insert an ordered rows to a message queue which can guarantee the order of the input rows. With this default optimization,I cannot do this. So can we make this optimization as a RelOptRule, the physics engine can decide use or not use this rule according to the actual situation. was (Author: pzw2018): Hi [~julianhyde] and [~hyuan] , Thanks for you attention. I think the SQL is not fully equivalent to the generated RelNode if we remove the LogicalSort. Maybe in some usage scenario, the target table cannot guarantee the order of the input rows. But in some case, the target table really need the ordering. For example,I want to insert an ordered rows to a message queue which can guarantee the order of the input rows. With this default optimization,I cannot do this. So can we make this optimization as a RelOptRule, the physics engine can decide use or not use this rule according to the actual situation. > Missing LogicalSort for insert statement > > > Key: CALCITE-3738 > URL: https://issues.apache.org/jira/browse/CALCITE-3738 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.19.0 >Reporter: pengzhiwei >Assignee: pengzhiwei >Priority: Major > Labels: pull-request-available > Time Spent: 1.5h > Remaining Estimate: 0h > > The following sql statement generates a wrong logical plan which missing the > LogicalSort : > {code:java} > insert into t select id*10,name from users order by name;{code} > the plan is : > {code:java} > LogicalTableModify(table=[[t]], operation=[INSERT], flattened=[false]) > LogicalProject(id=[*($0, 10)], name=[$1]) > LogicalTableScan(table=[[users]]) > {code} > This issue is introduced by the issue > https://issues.apache.org/jira/browse/CALCITE-2978 > which removes the sort by in sub-query.However the query in insert statement > is not a sub-query. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Comment Edited] (CALCITE-3738) Missing LogicalSort for insert statement
[ https://issues.apache.org/jira/browse/CALCITE-3738?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17016606#comment-17016606 ] pengzhiwei edited comment on CALCITE-3738 at 1/16/20 7:12 AM: -- Hi [~julianhyde] and [~hyuan] , Thanks for you attention. I think the SQL is not fully equivalent to the generated RelNode if we remove the LogicalSort. Maybe in some usage scenario, the target table cannot guarantee the order of the input rows. But in some case, the target table really need the ordering. For example,I want to insert an ordered rows to a message queue which can guarantee the order of the input rows. With this default optimization,I cannot do this. So can we make this optimization as a RelOptRule, the physics engine can decide use or not use this rule according to the actual situation. was (Author: pzw2018): I think the SQL is not fully equivalent to the generated RelNode if we remove the LogicalSort. Maybe in some usage scenario, the target table cannot guarantee the order of the input rows. But in some case, the target table really need the ordering. For example,I want to insert an ordered rows to a message queue which can guarantee the order of the input rows. With this default optimization,I cannot do this. So can we make this optimization as a RelOptRule, the physics engine can decide use or not use this rule according to the actual situation. > Missing LogicalSort for insert statement > > > Key: CALCITE-3738 > URL: https://issues.apache.org/jira/browse/CALCITE-3738 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.19.0 >Reporter: pengzhiwei >Assignee: pengzhiwei >Priority: Major > Labels: pull-request-available > Time Spent: 1.5h > Remaining Estimate: 0h > > The following sql statement generates a wrong logical plan which missing the > LogicalSort : > {code:java} > insert into t select id*10,name from users order by name;{code} > the plan is : > {code:java} > LogicalTableModify(table=[[t]], operation=[INSERT], flattened=[false]) > LogicalProject(id=[*($0, 10)], name=[$1]) > LogicalTableScan(table=[[users]]) > {code} > This issue is introduced by the issue > https://issues.apache.org/jira/browse/CALCITE-2978 > which removes the sort by in sub-query.However the query in insert statement > is not a sub-query. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Comment Edited] (CALCITE-3738) Missing LogicalSort for insert statement
[ https://issues.apache.org/jira/browse/CALCITE-3738?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17016606#comment-17016606 ] pengzhiwei edited comment on CALCITE-3738 at 1/16/20 7:10 AM: -- I think the SQL is not fully equivalent to the generated RelNode if we remove the LogicalSort. Maybe in some usage scenario, the target table cannot guarantee the order of the input rows. But in some case, the target table really need the ordering. For example,I want to insert an ordered rows to a message queue which can guarantee the order of the input rows. With this default optimization,I cannot do this. So can we make this optimization as a RelOptRule, the physics engine can decide use or not use this rule according to the actual situation. was (Author: pzw2018): I think the SQL is not fully equivalent to the generated RelNode if we remove the LogicalSort. Maybe in some usage scenario, the target table cannot guarantee the order of the input rows. But in some case, the target table really need the ordering of the input rows. For example,I want to insert an ordered rows to a message queue which can guarantee the order of the input rows. With this default optimization,I cannot do this. So can we make this optimization as a RelOptRule, the physics engine can decide use or not use this rule according to the actual situation. > Missing LogicalSort for insert statement > > > Key: CALCITE-3738 > URL: https://issues.apache.org/jira/browse/CALCITE-3738 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.19.0 >Reporter: pengzhiwei >Assignee: pengzhiwei >Priority: Major > Labels: pull-request-available > Time Spent: 1.5h > Remaining Estimate: 0h > > The following sql statement generates a wrong logical plan which missing the > LogicalSort : > {code:java} > insert into t select id*10,name from users order by name;{code} > the plan is : > {code:java} > LogicalTableModify(table=[[t]], operation=[INSERT], flattened=[false]) > LogicalProject(id=[*($0, 10)], name=[$1]) > LogicalTableScan(table=[[users]]) > {code} > This issue is introduced by the issue > https://issues.apache.org/jira/browse/CALCITE-2978 > which removes the sort by in sub-query.However the query in insert statement > is not a sub-query. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Commented] (CALCITE-3738) Missing LogicalSort for insert statement
[ https://issues.apache.org/jira/browse/CALCITE-3738?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17016606#comment-17016606 ] pengzhiwei commented on CALCITE-3738: - I think the SQL is not fully equivalent to the generated RelNode if we remove the LogicalSort. Maybe in some usage scenario, the target table cannot guarantee the order of the input rows. But in some case, the target table really need the ordering of the input rows. For example,I want to insert an ordered rows to a message queue which can guarantee the order of the input rows. With this default optimization,I cannot do this. So can we make this optimization as a RelOptRule, the physics engine can decide use or not use this rule according to the actual situation. > Missing LogicalSort for insert statement > > > Key: CALCITE-3738 > URL: https://issues.apache.org/jira/browse/CALCITE-3738 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.19.0 >Reporter: pengzhiwei >Assignee: pengzhiwei >Priority: Major > Labels: pull-request-available > Time Spent: 1.5h > Remaining Estimate: 0h > > The following sql statement generates a wrong logical plan which missing the > LogicalSort : > {code:java} > insert into t select id*10,name from users order by name;{code} > the plan is : > {code:java} > LogicalTableModify(table=[[t]], operation=[INSERT], flattened=[false]) > LogicalProject(id=[*($0, 10)], name=[$1]) > LogicalTableScan(table=[[users]]) > {code} > This issue is introduced by the issue > https://issues.apache.org/jira/browse/CALCITE-2978 > which removes the sort by in sub-query.However the query in insert statement > is not a sub-query. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Updated] (CALCITE-3738) Missing LogicalSort for insert statement
[ https://issues.apache.org/jira/browse/CALCITE-3738?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] pengzhiwei updated CALCITE-3738: Summary: Missing LogicalSort for insert statement (was: Missing Order logical plan for insert statement) > Missing LogicalSort for insert statement > > > Key: CALCITE-3738 > URL: https://issues.apache.org/jira/browse/CALCITE-3738 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.19.0 >Reporter: pengzhiwei >Assignee: pengzhiwei >Priority: Major > Labels: pull-request-available > Time Spent: 1.5h > Remaining Estimate: 0h > > The following sql statement generates a wrong logical plan which missing the > LogicalSort : > {code:java} > insert into t select id*10,name from users order by name;{code} > the plan is : > {code:java} > LogicalTableModify(table=[[t]], operation=[INSERT], flattened=[false]) > LogicalProject(id=[*($0, 10)], name=[$1]) > LogicalTableScan(table=[[users]]) > {code} > This issue is introduced by the issue > https://issues.apache.org/jira/browse/CALCITE-2978 > which removes the sort by in sub-query.However the query in insert statement > is not a sub-query. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Updated] (CALCITE-3738) Missing order by logical plan for insert statement
[ https://issues.apache.org/jira/browse/CALCITE-3738?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] pengzhiwei updated CALCITE-3738: Description: The following sql statement generates a wrong logical plan which missing the LogicalSort : {code:java} insert into t select id*10,name from users order by name;{code} the plan is : {code:java} LogicalTableModify(table=[[t]], operation=[INSERT], flattened=[false]) LogicalProject(id=[*($0, 10)], name=[$1]) LogicalTableScan(table=[[users]]) {code} This issue is introduced by the issue https://issues.apache.org/jira/browse/CALCITE-2978 which removes the sort by in sub-query.However the query in insert statement is not a sub-query. was: The following sql statement generates a wrong logical plan which missing the LogicalSort : {code:java} insert into t select id*10,name from users order by name;{code} the plan is : {code:java} LogicalTableModify(table=[[t]], operation=[INSERT], flattened=[false]) LogicalProject(id=[*($0, 10)], name=[$1]) LogicalTableScan(table=[[users]]) {code} This issue is introduced by the issue https://issues.apache.org/jira/browse/CALCITE-2978 which removed the sort by in sub-query.However the query in insert statement is not a sub-query. > Missing order by logical plan for insert statement > -- > > Key: CALCITE-3738 > URL: https://issues.apache.org/jira/browse/CALCITE-3738 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.19.0 >Reporter: pengzhiwei >Assignee: pengzhiwei >Priority: Major > Labels: pull-request-available > Time Spent: 10m > Remaining Estimate: 0h > > The following sql statement generates a wrong logical plan which missing the > LogicalSort : > {code:java} > insert into t select id*10,name from users order by name;{code} > the plan is : > {code:java} > LogicalTableModify(table=[[t]], operation=[INSERT], flattened=[false]) > LogicalProject(id=[*($0, 10)], name=[$1]) > LogicalTableScan(table=[[users]]) > {code} > This issue is introduced by the issue > https://issues.apache.org/jira/browse/CALCITE-2978 > which removes the sort by in sub-query.However the query in insert statement > is not a sub-query. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Updated] (CALCITE-3738) Missing order by logical plan for insert statement
[ https://issues.apache.org/jira/browse/CALCITE-3738?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] pengzhiwei updated CALCITE-3738: Description: The following sql statement generates a wrong logical plan which missing the LogicalSort : {code:java} insert into t select id*10,name from users order by name;{code} the plan is : {code:java} LogicalTableModify(table=[[t]], operation=[INSERT], flattened=[false]) LogicalProject(id=[*($0, 10)], name=[$1]) LogicalTableScan(table=[[users]]) {code} This issue is introduced by the issue https://issues.apache.org/jira/browse/CALCITE-2978 which removed the sort by in sub-query.However the query in insert statement is not a sub-query. was: The following sql statement generates a wrong logical plan which missing the LogicalSort : {code:java} "insert into t select id*10,name from users order by name;{code} the plan is : {code:java} LogicalTableModify(table=[[t]], operation=[INSERT], flattened=[false]) LogicalProject(id=[*($0, 10)], name=[$1]) LogicalTableScan(table=[[users]]) {code} This issue is introduced by the issue https://issues.apache.org/jira/browse/CALCITE-2978 which removed the sort by in sub-query.However the query in insert statement is not a sub-query. > Missing order by logical plan for insert statement > -- > > Key: CALCITE-3738 > URL: https://issues.apache.org/jira/browse/CALCITE-3738 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.19.0 >Reporter: pengzhiwei >Assignee: pengzhiwei >Priority: Major > > The following sql statement generates a wrong logical plan which missing the > LogicalSort : > {code:java} > insert into t select id*10,name from users order by name;{code} > the plan is : > {code:java} > LogicalTableModify(table=[[t]], operation=[INSERT], flattened=[false]) > LogicalProject(id=[*($0, 10)], name=[$1]) > LogicalTableScan(table=[[users]]) > {code} > This issue is introduced by the issue > https://issues.apache.org/jira/browse/CALCITE-2978 > which removed the sort by in sub-query.However the query in insert statement > is not a sub-query. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Created] (CALCITE-3738) Missing order by logical plan for insert statement
pengzhiwei created CALCITE-3738: --- Summary: Missing order by logical plan for insert statement Key: CALCITE-3738 URL: https://issues.apache.org/jira/browse/CALCITE-3738 Project: Calcite Issue Type: Bug Components: core Affects Versions: 1.19.0 Reporter: pengzhiwei Assignee: pengzhiwei The following sql statement generates a wrong logical plan which missing the LogicalSort : {code:java} "insert into t select id*10,name from users order by name;{code} the plan is : {code:java} LogicalTableModify(table=[[t]], operation=[INSERT], flattened=[false]) LogicalProject(id=[*($0, 10)], name=[$1]) LogicalTableScan(table=[[users]]) {code} This issue is introduced by the issue https://issues.apache.org/jira/browse/CALCITE-2978 which removed the sort by in sub-query.However the query in insert statement is not a sub-query. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Commented] (CALCITE-1581) UDTF like in hive
[ https://issues.apache.org/jira/browse/CALCITE-1581?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16984934#comment-16984934 ] pengzhiwei commented on CALCITE-1581: - Hi [~Chunwei Lei], sorry late for the response. I have fixed most of the comments now. Please have a review.Thanks! > UDTF like in hive > - > > Key: CALCITE-1581 > URL: https://issues.apache.org/jira/browse/CALCITE-1581 > Project: Calcite > Issue Type: New Feature >Reporter: Xiaoyong Deng >Assignee: pengzhiwei >Priority: Major > Labels: pull-request-available, udtf > Fix For: 1.22.0 > > Time Spent: 10.5h > Remaining Estimate: 0h > > Support one row in and multi-column/multi-row out(one-to-many mapping), just > like udtf in hive. > The query would like this: > {code} > select > func(c0, c1) as (f0, f1, f2) > from table_name; > {code} > c0 and c1 are 'table_name' columns. f0, f1 and f2 are new generated columns. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Commented] (CALCITE-1581) UDTF like in hive
[ https://issues.apache.org/jira/browse/CALCITE-1581?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16938354#comment-16938354 ] pengzhiwei commented on CALCITE-1581: - Hi [~julianhyde], can you take a look at this pr again? I hope we can finish it at 1.22.0. > UDTF like in hive > - > > Key: CALCITE-1581 > URL: https://issues.apache.org/jira/browse/CALCITE-1581 > Project: Calcite > Issue Type: New Feature >Reporter: Xiaoyong Deng >Assignee: pengzhiwei >Priority: Major > Labels: pull-request-available, udtf > Fix For: 1.22.0 > > Time Spent: 8h 10m > Remaining Estimate: 0h > > Support one row in and multi-column/multi-row out(one-to-many mapping), just > like udtf in hive. > The query would like this: > {code} > select > func(c0, c1) as (f0, f1, f2) > from table_name; > {code} > c0 and c1 are 'table_name' columns. f0, f1 and f2 are new generated columns. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Commented] (CALCITE-1581) UDTF like in hive
[ https://issues.apache.org/jira/browse/CALCITE-1581?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16917387#comment-16917387 ] pengzhiwei commented on CALCITE-1581: - Thank [~julianhyde] and [~zabetak], I have fix the choice conflict.Please have a review again. And I agree with you on removing minor comments. > UDTF like in hive > - > > Key: CALCITE-1581 > URL: https://issues.apache.org/jira/browse/CALCITE-1581 > Project: Calcite > Issue Type: New Feature >Reporter: Xiaoyong Deng >Assignee: pengzhiwei >Priority: Major > Labels: pull-request-available, udtf > Fix For: 1.21.0 > > Time Spent: 8h 10m > Remaining Estimate: 0h > > Support one row in and multi-column/multi-row out(one-to-many mapping), just > like udtf in hive. > The query would like this: > {code} > select > func(c0, c1) as (f0, f1, f2) > from table_name; > {code} > c0 and c1 are 'table_name' columns. f0, f1 and f2 are new generated columns. -- This message was sent by Atlassian Jira (v8.3.2#803003)
[jira] [Commented] (CALCITE-1581) UDTF like in hive
[ https://issues.apache.org/jira/browse/CALCITE-1581?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16908665#comment-16908665 ] pengzhiwei commented on CALCITE-1581: - Thanks very much for you review [~Chunwei Lei], I will fix these issues soon. > UDTF like in hive > - > > Key: CALCITE-1581 > URL: https://issues.apache.org/jira/browse/CALCITE-1581 > Project: Calcite > Issue Type: New Feature >Reporter: Xiaoyong Deng >Assignee: pengzhiwei >Priority: Major > Labels: pull-request-available, udtf > Fix For: 1.21.0 > > Time Spent: 6h 10m > Remaining Estimate: 0h > > Support one row in and multi-column/multi-row out(one-to-many mapping), just > like udtf in hive. > The query would like this: > {code} > select > func(c0, c1) as (f0, f1, f2) > from table_name; > {code} > c0 and c1 are 'table_name' columns. f0, f1 and f2 are new generated columns. -- This message was sent by Atlassian JIRA (v7.6.14#76016)
[jira] [Updated] (CALCITE-1581) UDTF like in hive
[ https://issues.apache.org/jira/browse/CALCITE-1581?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] pengzhiwei updated CALCITE-1581: Fix Version/s: 1.21.0 > UDTF like in hive > - > > Key: CALCITE-1581 > URL: https://issues.apache.org/jira/browse/CALCITE-1581 > Project: Calcite > Issue Type: New Feature >Reporter: Xiaoyong Deng >Assignee: pengzhiwei >Priority: Major > Labels: pull-request-available, udtf > Fix For: 1.21.0 > > Time Spent: 3h 10m > Remaining Estimate: 0h > > Support one row in and multi-column/multi-row out(one-to-many mapping), just > like udtf in hive. > The query would like this: > {code} > select > func(c0, c1) as (f0, f1, f2) > from table_name; > {code} > c0 and c1 are 'table_name' columns. f0, f1 and f2 are new generated columns. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Comment Edited] (CALCITE-1581) UDTF like in hive
[ https://issues.apache.org/jira/browse/CALCITE-1581?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16855663#comment-16855663 ] pengzhiwei edited comment on CALCITE-1581 at 6/4/19 1:33 PM: - Hi [~julianhyde],Thanks for you constructive suggestion. I have fixed most problem you mentioned and also added some test case. But there are some left. _The "handle..." method works in a mysterious way. Perhaps it writes into an IdentityHashMap, perhaps not._ The "selectTableFunctions" is used to associate the SqlSelect with the Table Function contains in the select list. In the "handleTableFunctionInSelect" method,I will always put it to the _IdentityHashMap_ except that there exist a validate exception .So I cannot understand "_perhaps it writes to perhaps not_" means. _The validator test ought to check that table functions are rejected even if they don't use the 'as (...)' syntax_ The sql without _'as (..'_ like _"select table_func(0) from emp"_ can pass the validator and generate the logical plan.It is not a new issue introduced by this PR,So I will fix it in another issue. Please have a review again when you have time,Thanks! . was (Author: pzw2018): Hi [~julianhyde],Thanks for you constructive suggestion. I have fixed most problem you mentioned and also added some test case. But there are some left. * _The "handle..." method works in a mysterious way. Perhaps it writes into an IdentityHashMap, perhaps not._ The "selectTableFunctions" is used to associate the SqlSelect with the Table Function contains in the select list. In the "handleTableFunctionInSelect" method,I will always put it to the _IdentityHashMap_ except that there exist a validate exception .So I cannot understand "_perhaps it writes to perhaps not_" means. _The validator test ought to check that table functions are rejected even if they don't use the 'as (...)' syntax_ The sql without _'as (..'_ like _"select table_func(0) from emp"_ can pass the validator and generate the logical plan.It is not a new issue introduced by this PR,So I will fix it in another issue. Please have a review again when you have time,Thanks! . > UDTF like in hive > - > > Key: CALCITE-1581 > URL: https://issues.apache.org/jira/browse/CALCITE-1581 > Project: Calcite > Issue Type: New Feature >Reporter: Xiaoyong Deng >Assignee: pengzhiwei >Priority: Major > Labels: pull-request-available, udtf > Fix For: 1.20.0 > > Time Spent: 3h 10m > Remaining Estimate: 0h > > Support one row in and multi-column/multi-row out(one-to-many mapping), just > like udtf in hive. > The query would like this: > {code} > select > func(c0, c1) as (f0, f1, f2) > from table_name; > {code} > c0 and c1 are 'table_name' columns. f0, f1 and f2 are new generated columns. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Comment Edited] (CALCITE-1581) UDTF like in hive
[ https://issues.apache.org/jira/browse/CALCITE-1581?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16855663#comment-16855663 ] pengzhiwei edited comment on CALCITE-1581 at 6/4/19 1:32 PM: - Hi [~julianhyde],Thanks for you constructive suggestion. I have fixed most problem you mentioned and also added some test case. But there are some left. * _The "handle..." method works in a mysterious way. Perhaps it writes into an IdentityHashMap, perhaps not._ The "selectTableFunctions" is used to associate the SqlSelect with the Table Function contains in the select list. In the "handleTableFunctionInSelect" method,I will always put it to the _IdentityHashMap_ except that there exist a validate exception .So I cannot understand "_perhaps it writes to perhaps not_" means. _The validator test ought to check that table functions are rejected even if they don't use the 'as (...)' syntax_ The sql without _'as (..'_ like _"select table_func(0) from emp"_ can pass the validator and generate the logical plan.It is not a new issue introduced by this PR,So I will fix it in another issue. Please have a review again when you have time,Thanks! . was (Author: pzw2018): Hi [~julianhyde],Thanks for you constructive suggestion. I have fixed most problem you mentioned and also added some test case. But there are some left. * _The "handle..." method works in a mysterious way. Perhaps it writes into an IdentityHashMap, perhaps not._ The "selectTableFunctions" is used to associate the SqlSelect with the Table Function contains in the select list. In the "handleTableFunctionInSelect" method,I will always put it to the _IdentityHashMap_ except that there exist a validate exception . _The validator test ought to check that table functions are rejected even if they don't use the 'as (...)' syntax_ The sql without _'as (..'_ like _"select table_func(0) from emp"_ can pass the validator and generate the logical plan.It is not a new issue introduced by this PR,So I will fix it in another issue. Please have a review again when you have time,Thanks! . > UDTF like in hive > - > > Key: CALCITE-1581 > URL: https://issues.apache.org/jira/browse/CALCITE-1581 > Project: Calcite > Issue Type: New Feature >Reporter: Xiaoyong Deng >Assignee: pengzhiwei >Priority: Major > Labels: pull-request-available, udtf > Fix For: 1.20.0 > > Time Spent: 3h 10m > Remaining Estimate: 0h > > Support one row in and multi-column/multi-row out(one-to-many mapping), just > like udtf in hive. > The query would like this: > {code} > select > func(c0, c1) as (f0, f1, f2) > from table_name; > {code} > c0 and c1 are 'table_name' columns. f0, f1 and f2 are new generated columns. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Comment Edited] (CALCITE-1581) UDTF like in hive
[ https://issues.apache.org/jira/browse/CALCITE-1581?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16855663#comment-16855663 ] pengzhiwei edited comment on CALCITE-1581 at 6/4/19 1:30 PM: - Hi [~julianhyde],Thanks for you constructive suggestion. I have fixed most problem you mentioned and also added some test case. But there are some left. * _The "handle..." method works in a mysterious way. Perhaps it writes into an IdentityHashMap, perhaps not._ The "selectTableFunctions" is used to associate the SqlSelect with the Table Function contains in the select list. In the "handleTableFunctionInSelect" method,I will always put it to the _IdentityHashMap_ except that there exist a validate exception . _The validator test ought to check that table functions are rejected even if they don't use the 'as (...)' syntax_ The sql without _'as (..'_ like _"select table_func(0) from emp"_ can pass the validator and generate the logical plan.It is not a new issue introduced by this PR,So I will fix it in another issue. Please have a review again when you have time,Thanks! . was (Author: pzw2018): Hi [~julianhyde],Thanks for you constructive suggestion. I have fixed most problem you mentioned and also added some test case. But there is one I am not very understand. * _The "handle..." method works in a mysterious way. Perhaps it writes into an IdentityHashMap, perhaps not._ The "selectTableFunctions" is used to associate the SqlSelect with the Table Function contains in the select list. In the "handleTableFunctionInSelect" method,I will always put it to the _IdentityHashMap_ except that there exist a validate exception . Please have a review again when you have time,Thanks! . > UDTF like in hive > - > > Key: CALCITE-1581 > URL: https://issues.apache.org/jira/browse/CALCITE-1581 > Project: Calcite > Issue Type: New Feature >Reporter: Xiaoyong Deng >Assignee: pengzhiwei >Priority: Major > Labels: pull-request-available, udtf > Fix For: 1.20.0 > > Time Spent: 3h 10m > Remaining Estimate: 0h > > Support one row in and multi-column/multi-row out(one-to-many mapping), just > like udtf in hive. > The query would like this: > {code} > select > func(c0, c1) as (f0, f1, f2) > from table_name; > {code} > c0 and c1 are 'table_name' columns. f0, f1 and f2 are new generated columns. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Commented] (CALCITE-1581) UDTF like in hive
[ https://issues.apache.org/jira/browse/CALCITE-1581?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16855663#comment-16855663 ] pengzhiwei commented on CALCITE-1581: - Hi [~julianhyde],Thanks for you constructive suggestion. I have fixed most problem you mentioned and also added some test case. But there is one I am not very understand. * _The "handle..." method works in a mysterious way. Perhaps it writes into an IdentityHashMap, perhaps not._ The "selectTableFunctions" is used to associate the SqlSelect with the Table Function contains in the select list. In the "handleTableFunctionInSelect" method,I will always put it to the _IdentityHashMap_ except that there exist a validate exception . Please have a review again when you have time,Thanks! . > UDTF like in hive > - > > Key: CALCITE-1581 > URL: https://issues.apache.org/jira/browse/CALCITE-1581 > Project: Calcite > Issue Type: New Feature >Reporter: Xiaoyong Deng >Assignee: pengzhiwei >Priority: Major > Labels: pull-request-available, udtf > Fix For: 1.20.0 > > Time Spent: 3h 10m > Remaining Estimate: 0h > > Support one row in and multi-column/multi-row out(one-to-many mapping), just > like udtf in hive. > The query would like this: > {code} > select > func(c0, c1) as (f0, f1, f2) > from table_name; > {code} > c0 and c1 are 'table_name' columns. f0, f1 and f2 are new generated columns. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Comment Edited] (CALCITE-3018) User defined scalar function which returns a Date/Timestamp value causes error.
[ https://issues.apache.org/jira/browse/CALCITE-3018?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16825093#comment-16825093 ] pengzhiwei edited comment on CALCITE-3018 at 4/24/19 12:52 PM: --- It seems that the execute result type is not match with the _ColumnMetaData.type.rep_ in the case: {code:java} select my_date_func(1),1 from xx; {code} The _EnumerableCalc_ will translate the _outputJavaType_ of the "select" to Object[].class, and the execute result type of my_date_func(1) will be "Date". However, the _ColumnMetaData.type.rep_ is a Integer type and a DateFromNumberAccessor was created as the following code which leading to this exception. {code:java} protected Accessor createAccessor(ColumnMetaData columnMetaData,... ...) { +140 case Types.DATE: +141 switch (columnMetaData.type.rep) { +142 case PRIMITIVE_INT: case INTEGER: case NUMBER: return new DateFromNumberAccessor(getter, localCalendar); case JAVA_SQL_DATE: return new DateAccessor(getter); default: throw new AssertionError("bad " + columnMetaData.type.rep); } {code} In another case: {code:java} select my_date_func(1) from xx; {code} The _EnumerableCalc_ translate the _outputJavaType of the "select" to "Integer",_ which is match with the _ColumnMetaData.type.rep._ So it can pass the test. was (Author: pzw2018): It seems that the execute result type is not match with the _ColumnMetaData.type.rep_ in the case: {code:java} select my_date_func(1),1 from xx; {code} The _EnumerableCalc_ will translate the _outputJavaType_ of the "select" to Object[].class, and the execute result type of my_date_func(1) will be "Date". However, the _ColumnMetaData.type.rep_ is a Integer type and a DateFromNumberAccessor was created as the following code which leading to this exception. {code:java} protected Accessor createAccessor(ColumnMetaData columnMetaData,... ...) { +140 case Types.DATE: +141 switch (columnMetaData.type.rep) { +142 case PRIMITIVE_INT: case INTEGER: case NUMBER: return new DateFromNumberAccessor(getter, localCalendar); case JAVA_SQL_DATE: return new DateAccessor(getter); default: throw new AssertionError("bad " + columnMetaData.type.rep); } {code} In another case: {code:java} select my_date_func(1) from xx; {code} The _EnumerableCalc_ translate the _outputJavaType of the "select" to "Integer",_ which is match with the _ColumnMetaData.type.rep._ So it can pass the test. > User defined scalar function which returns a Date/Timestamp value causes > error. > --- > > Key: CALCITE-3018 > URL: https://issues.apache.org/jira/browse/CALCITE-3018 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.19.0 >Reporter: fangyc >Priority: Major > > I've defined a user defined function that returns a date or timestamp and > register it on root schema : > {quote} public static java.sql.Date test(long value) { > return new java.sql.Date(value); > } > {quote} > > {quote}rootSchema.add("my_test", ScalarFunctionImpl.create(CalciteTest.class, > "test")); > {quote} > If I use this function in the select part of a query as the only column, the > result works fine: > {quote}select my_test(1) from northwind.product > {quote} > However, if I add an additional column in the select part, the error occurs: > {quote}select my_test(1), 2 from northwind.product > {quote} > Error message: > {quote} Exception in thread "main" java.lang.ClassCastException: > java.sql.Date cannot be cast to java.lang.Number > at > org.apache.calcite.avatica.util.AbstractCursor$NumberAccessor.getNumber(AbstractCursor.java:726) > at > org.apache.calcite.avatica.util.AbstractCursor$DateFromNumberAccessor.getDate(AbstractCursor.java:915) > at org.apache.calcite.avatica.AvaticaSite.get(AvaticaSite.java:326) > at > org.apache.calcite.avatica.AvaticaResultSet.getObject(AvaticaResultSet.java:393) > {quote} > > I tried with calcite.debug=true, and the difference seems as follows: > * 1 column > {quote}/* 51 */ public Object current() { > /* 52 */ return > org.apache.calcite.runtime.SqlFunctions.toIntOptional(CalciteTest.test(1)); > /* 53 */ } > {quote} * 2 columns > {quote}/* 51 */ public Object current() { > /* 52 */ return new Object[] { > /* 53 */ CalciteTest.test(1), > /* 54 */ 2}; > /* 55 */ } > {quote} -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Comment Edited] (CALCITE-3018) User defined scalar function which returns a Date/Timestamp value causes error.
[ https://issues.apache.org/jira/browse/CALCITE-3018?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16825093#comment-16825093 ] pengzhiwei edited comment on CALCITE-3018 at 4/24/19 12:47 PM: --- It seems that the execute result type is not match with the _ColumnMetaData.type.rep_ in the case: {code:java} select my_date_func(1),1 from xx; {code} The _EnumerableCalc_ will translate the _outputJavaType_ of the "select" to Object[].class, and the execute result type of my_date_func(1) will be "Date". However, the _ColumnMetaData.type.rep_ is a Integer type and a DateFromNumberAccessor was created as the following code which leading to this exception. {code:java} protected Accessor createAccessor(ColumnMetaData columnMetaData,... ...) { +140 case Types.DATE: +141 switch (columnMetaData.type.rep) { +142 case PRIMITIVE_INT: case INTEGER: case NUMBER: return new DateFromNumberAccessor(getter, localCalendar); case JAVA_SQL_DATE: return new DateAccessor(getter); default: throw new AssertionError("bad " + columnMetaData.type.rep); } {code} In another case: {code:java} select my_date_func(1) from xx; {code} The _EnumerableCalc_ translate the _outputJavaType of the "select" to "Integer",_ which is match with the _ColumnMetaData.type.rep._ So it can pass the test. was (Author: pzw2018): It seems that the execute result type is not match with the _ColumnMetaData.type.rep_ in the case: {code:java} select my_date_func(1),1 from xx; {code} The _EnumerableCalc_ will translate the _outputJavaType_ of the "select" to Object[].class, and the execute result type of my_date_func(1) will be "Date". However, the _ColumnMetaData.type.rep_ is a Integer type, which leading to this exception. {code:java} rotected Accessor createAccessor(ColumnMetaData columnMetaData,... ...) { +140 case Types.DATE: +141 switch (columnMetaData.type.rep) { +142 case PRIMITIVE_INT: case INTEGER: case NUMBER: return new DateFromNumberAccessor(getter, localCalendar); case JAVA_SQL_DATE: return new DateAccessor(getter); default: throw new AssertionError("bad " + columnMetaData.type.rep); } {code} In another case: {code:java} select my_date_func(1) from xx; {code} The _EnumerableCalc_ translate the _outputJavaType of the "select" to "Integer",_ which is match with the _ColumnMetaData.type.rep._ So it can pass the test. > User defined scalar function which returns a Date/Timestamp value causes > error. > --- > > Key: CALCITE-3018 > URL: https://issues.apache.org/jira/browse/CALCITE-3018 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.19.0 >Reporter: fangyc >Priority: Major > > I've defined a user defined function that returns a date or timestamp and > register it on root schema : > {quote} public static java.sql.Date test(long value) { > return new java.sql.Date(value); > } > {quote} > > {quote}rootSchema.add("my_test", ScalarFunctionImpl.create(CalciteTest.class, > "test")); > {quote} > If I use this function in the select part of a query as the only column, the > result works fine: > {quote}select my_test(1) from northwind.product > {quote} > However, if I add an additional column in the select part, the error occurs: > {quote}select my_test(1), 2 from northwind.product > {quote} > Error message: > {quote} Exception in thread "main" java.lang.ClassCastException: > java.sql.Date cannot be cast to java.lang.Number > at > org.apache.calcite.avatica.util.AbstractCursor$NumberAccessor.getNumber(AbstractCursor.java:726) > at > org.apache.calcite.avatica.util.AbstractCursor$DateFromNumberAccessor.getDate(AbstractCursor.java:915) > at org.apache.calcite.avatica.AvaticaSite.get(AvaticaSite.java:326) > at > org.apache.calcite.avatica.AvaticaResultSet.getObject(AvaticaResultSet.java:393) > {quote} > > I tried with calcite.debug=true, and the difference seems as follows: > * 1 column > {quote}/* 51 */ public Object current() { > /* 52 */ return > org.apache.calcite.runtime.SqlFunctions.toIntOptional(CalciteTest.test(1)); > /* 53 */ } > {quote} * 2 columns > {quote}/* 51 */ public Object current() { > /* 52 */ return new Object[] { > /* 53 */ CalciteTest.test(1), > /* 54 */ 2}; > /* 55 */ } > {quote} -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Comment Edited] (CALCITE-3018) User defined scalar function which returns a Date/Timestamp value causes error.
[ https://issues.apache.org/jira/browse/CALCITE-3018?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16825093#comment-16825093 ] pengzhiwei edited comment on CALCITE-3018 at 4/24/19 12:40 PM: --- It seems that the execute result type is not match with the _ColumnMetaData.type.rep_ in the case: {code:java} select my_date_func(1),1 from xx; {code} The _EnumerableCalc_ will translate the _outputJavaType_ of the "select" to Object[].class, and the execute result type of my_date_func(1) will be "Date". However, the _ColumnMetaData.type.rep_ is a Integer type, which leading to this exception. {code:java} rotected Accessor createAccessor(ColumnMetaData columnMetaData,... ...) { +140 case Types.DATE: +141 switch (columnMetaData.type.rep) { +142 case PRIMITIVE_INT: case INTEGER: case NUMBER: return new DateFromNumberAccessor(getter, localCalendar); case JAVA_SQL_DATE: return new DateAccessor(getter); default: throw new AssertionError("bad " + columnMetaData.type.rep); } {code} In another case: {code:java} select my_date_func(1) from xx; {code} The _EnumerableCalc_ translate the _outputJavaType of the "select" to "Integer",_ which is match with the _ColumnMetaData.type.rep._ So it can pass the test. was (Author: pzw2018): It seems that the execute result is not match with the _ColumnMetaData.type.rep_ in the case: {code:java} select my_date_func(1),1 from xx; {code} The _EnumerableCalc_ will translate the _outputJavaType_ of the "select" __ to Object[].class because the rowType of the "select" is a ArrayType, and the execute result of my_date_func(1) will be "Date". However, the _ColumnMetaData.type.rep_ is a Integer type, which leading to this exception. {code:java} rotected Accessor createAccessor(ColumnMetaData columnMetaData,... ...) { +140 case Types.DATE: +141 switch (columnMetaData.type.rep) { +142 case PRIMITIVE_INT: case INTEGER: case NUMBER: return new DateFromNumberAccessor(getter, localCalendar); case JAVA_SQL_DATE: return new DateAccessor(getter); default: throw new AssertionError("bad " + columnMetaData.type.rep); } {code} In another case: {code:java} select my_date_func(1) from xx; {code} The _EnumerableCalc_ will __ translate the outputJavaType of the "select" to "Integer", which is match with the _ColumnMetaData.type.rep._ So it can pass the test. > User defined scalar function which returns a Date/Timestamp value causes > error. > --- > > Key: CALCITE-3018 > URL: https://issues.apache.org/jira/browse/CALCITE-3018 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.19.0 >Reporter: fangyc >Priority: Major > > I've defined a user defined function that returns a date or timestamp and > register it on root schema : > {quote} public static java.sql.Date test(long value) { > return new java.sql.Date(value); > } > {quote} > > {quote}rootSchema.add("my_test", ScalarFunctionImpl.create(CalciteTest.class, > "test")); > {quote} > If I use this function in the select part of a query as the only column, the > result works fine: > {quote}select my_test(1) from northwind.product > {quote} > However, if I add an additional column in the select part, the error occurs: > {quote}select my_test(1), 2 from northwind.product > {quote} > Error message: > {quote} Exception in thread "main" java.lang.ClassCastException: > java.sql.Date cannot be cast to java.lang.Number > at > org.apache.calcite.avatica.util.AbstractCursor$NumberAccessor.getNumber(AbstractCursor.java:726) > at > org.apache.calcite.avatica.util.AbstractCursor$DateFromNumberAccessor.getDate(AbstractCursor.java:915) > at org.apache.calcite.avatica.AvaticaSite.get(AvaticaSite.java:326) > at > org.apache.calcite.avatica.AvaticaResultSet.getObject(AvaticaResultSet.java:393) > {quote} > > I tried with calcite.debug=true, and the difference seems as follows: > * 1 column > {quote}/* 51 */ public Object current() { > /* 52 */ return > org.apache.calcite.runtime.SqlFunctions.toIntOptional(CalciteTest.test(1)); > /* 53 */ } > {quote} * 2 columns > {quote}/* 51 */ public Object current() { > /* 52 */ return new Object[] { > /* 53 */ CalciteTest.test(1), > /* 54 */ 2}; > /* 55 */ } > {quote} -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Commented] (CALCITE-3018) User defined scalar function which returns a Date/Timestamp value causes error.
[ https://issues.apache.org/jira/browse/CALCITE-3018?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16825093#comment-16825093 ] pengzhiwei commented on CALCITE-3018: - It seems that the execute result is not match with the _ColumnMetaData.type.rep_ in the case: {code:java} select my_date_func(1),1 from xx; {code} The _EnumerableCalc_ will translate the _outputJavaType_ of the "select" __ to Object[].class because the rowType of the "select" is a ArrayType, and the execute result of my_date_func(1) will be "Date". However, the _ColumnMetaData.type.rep_ is a Integer type, which leading to this exception. {code:java} rotected Accessor createAccessor(ColumnMetaData columnMetaData,... ...) { +140 case Types.DATE: +141 switch (columnMetaData.type.rep) { +142 case PRIMITIVE_INT: case INTEGER: case NUMBER: return new DateFromNumberAccessor(getter, localCalendar); case JAVA_SQL_DATE: return new DateAccessor(getter); default: throw new AssertionError("bad " + columnMetaData.type.rep); } {code} In another case: {code:java} select my_date_func(1) from xx; {code} The _EnumerableCalc_ will __ translate the outputJavaType of the "select" to "Integer", which is match with the _ColumnMetaData.type.rep._ So it can pass the test. > User defined scalar function which returns a Date/Timestamp value causes > error. > --- > > Key: CALCITE-3018 > URL: https://issues.apache.org/jira/browse/CALCITE-3018 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.19.0 >Reporter: fangyc >Priority: Major > > I've defined a user defined function that returns a date or timestamp and > register it on root schema : > {quote} public static java.sql.Date test(long value) { > return new java.sql.Date(value); > } > {quote} > > {quote}rootSchema.add("my_test", ScalarFunctionImpl.create(CalciteTest.class, > "test")); > {quote} > If I use this function in the select part of a query as the only column, the > result works fine: > {quote}select my_test(1) from northwind.product > {quote} > However, if I add an additional column in the select part, the error occurs: > {quote}select my_test(1), 2 from northwind.product > {quote} > Error message: > {quote} Exception in thread "main" java.lang.ClassCastException: > java.sql.Date cannot be cast to java.lang.Number > at > org.apache.calcite.avatica.util.AbstractCursor$NumberAccessor.getNumber(AbstractCursor.java:726) > at > org.apache.calcite.avatica.util.AbstractCursor$DateFromNumberAccessor.getDate(AbstractCursor.java:915) > at org.apache.calcite.avatica.AvaticaSite.get(AvaticaSite.java:326) > at > org.apache.calcite.avatica.AvaticaResultSet.getObject(AvaticaResultSet.java:393) > {quote} > > I tried with calcite.debug=true, and the difference seems as follows: > * 1 column > {quote}/* 51 */ public Object current() { > /* 52 */ return > org.apache.calcite.runtime.SqlFunctions.toIntOptional(CalciteTest.test(1)); > /* 53 */ } > {quote} * 2 columns > {quote}/* 51 */ public Object current() { > /* 52 */ return new Object[] { > /* 53 */ CalciteTest.test(1), > /* 54 */ 2}; > /* 55 */ } > {quote} -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Commented] (CALCITE-2453) Enhance the SQL parser in order to optionally support semicolon at the end of the sql statements
[ https://issues.apache.org/jira/browse/CALCITE-2453?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16822840#comment-16822840 ] pengzhiwei commented on CALCITE-2453: - Hi [~Chunwei Lei], can you add a test which mixed the "select" statement with the "insert" statement? In our previous implement similar to you PR, there is conflict between them. > Enhance the SQL parser in order to optionally support semicolon at the end of > the sql statements > > > Key: CALCITE-2453 > URL: https://issues.apache.org/jira/browse/CALCITE-2453 > Project: Calcite > Issue Type: Improvement > Components: core >Affects Versions: next >Reporter: charbel yazbeck >Assignee: Chunwei Lei >Priority: Trivial > Labels: pull-request-available > Attachments: > 0001-CALCITE-2453-Allowing-SQL-statements-to-optionally-e.patch > > Time Spent: 1h 20m > Remaining Estimate: 0h > > Consists of adding [] in Parser.jj -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Comment Edited] (CALCITE-3000) Improve SqlUserDefinedFunction and SqlUserDefinedTableFunction to support overload method call
[ https://issues.apache.org/jira/browse/CALCITE-3000?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16822403#comment-16822403 ] pengzhiwei edited comment on CALCITE-3000 at 4/20/19 9:52 AM: -- Hi [~julianhyde], the description of the issue has been updated and a PR has pulled. Here is the mainly change list in this PR: # Add {{SqlUtil#findBestMatchMethod}} to support find best matched method in the function class. # Support dynamic type inference according to the calling argument types based on (1).({{SqlFunction#getReturnTypeInferenceForClass}}、 {{SqlFunction#getOperandTypeInferenceForClass }} and {{SqlFunction#getOperandTypeCheckerForClass}}) # Add {{argTypes}} and {{typeFactory}} parameters to {{ImplementableFunction#getImplementor}} to create {{CallImplementor}} according to the argument types. # Add {{argTypes}} and {{typeFactory}} parameters to {{TableFunction#getRowType}} to infer return type according to the argument types. # Add {{JavaScalarFunction}} and {{JavaTableFunction}} to represent the class-level function schema mapping a java class. was (Author: pzw2018): Hi [~julianhyde], the description of the issue has been updated and a PR has pulled. Here is the mainly change list in this PR: # Add {{SqlUtil#findBestMatchMethod}} to support find best matched method in the function class. # Support dynamic type inference according to the calling argument types based on (1).({{SqlFunction#getReturnTypeInferenceForClass}}、 {{SqlFunction#getOperandTypeInferenceForClass }} and {{SqlFunction#getOperandTypeCheckerForClass}}) # Add {{argTypes}} and {{typeFactory}} parameters to {{ImplementableFunction#getImplementor}} to create {{CallImplementor}} according to the argument types. # Add {{argTypes}} and {{typeFactory}} parameters to {{TableFunction#getRowType}} to infer return type according to the argument types. # Add {{JavaScalarFunction}} and {{JavaTableFunction}} to represent the function schema mapping a java class. > Improve SqlUserDefinedFunction and SqlUserDefinedTableFunction to support > overload method call > -- > > Key: CALCITE-3000 > URL: https://issues.apache.org/jira/browse/CALCITE-3000 > Project: Calcite > Issue Type: Improvement > Components: core >Reporter: pengzhiwei >Assignee: pengzhiwei >Priority: Major > Labels: pull-request-available > Time Spent: 40m > Remaining Estimate: 0h > > Here are User-defined scalar function class and table function class: > > {code:java} > @UDFDescription(name = "MY_UDF", category = > SqlFunctionCategory.USER_DEFINED_FUNCTION) > public static class MyUdfFunction { > public static String eval(String a) { > ... > } > public static String eval(String a, String b) { > > } > public static String eval(String a, int c) { > > } > } > {code} > {code:java} > @UDFDescription(name = "MY_UDTF", category = > SqlFunctionCategory.USER_DEFINED_TABLE_FUNCTION) > public static class MyUdtfFunction { > public static MyScannableTable eval(String a) { > ... > } > public static MyScannableTable eval(long a) { > ... > } > public static MyScannableTable eval(int a) { > ... > } > } > {code} > This issue wants to improve the SqlUserDefinedFunction and > SqlUserDefinedTableFunction to support the overload functions in both > User-defined scalar function class and table function class. > > > > -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Commented] (CALCITE-3000) Improve SqlUserDefinedFunction and SqlUserDefinedTableFunction to support overload method call
[ https://issues.apache.org/jira/browse/CALCITE-3000?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16822403#comment-16822403 ] pengzhiwei commented on CALCITE-3000: - Hi [~julianhyde], the description of the issue has been updated and a PR has pulled. Here is the mainly change list in this PR: # Add {{SqlUtil#findBestMatchMethod}} to support find best matched method in the function class. # Support dynamic type inference according to the calling argument types based on (1).({{SqlFunction#getReturnTypeInferenceForClass}}、 {{SqlFunction#getOperandTypeInferenceForClass }} and {{SqlFunction#getOperandTypeCheckerForClass}}) # Add {{argTypes}} and {{typeFactory}} parameters to {{ImplementableFunction#getImplementor}} to create {{CallImplementor}} according to the argument types. # Add {{argTypes}} and {{typeFactory}} parameters to {{TableFunction#getRowType}} to infer return type according to the argument types. # Add {{JavaScalarFunction}} and {{JavaTableFunction}} to represent the function schema mapping a java class. > Improve SqlUserDefinedFunction and SqlUserDefinedTableFunction to support > overload method call > -- > > Key: CALCITE-3000 > URL: https://issues.apache.org/jira/browse/CALCITE-3000 > Project: Calcite > Issue Type: Improvement > Components: core >Reporter: pengzhiwei >Assignee: pengzhiwei >Priority: Major > Labels: pull-request-available > Time Spent: 20m > Remaining Estimate: 0h > > Here are User-defined scalar function class and table function class: > > {code:java} > @UDFDescription(name = "MY_UDF", category = > SqlFunctionCategory.USER_DEFINED_FUNCTION) > public static class MyUdfFunction { > public static String eval(String a) { > ... > } > public static String eval(String a, String b) { > > } > public static String eval(String a, int c) { > > } > } > {code} > {code:java} > @UDFDescription(name = "MY_UDTF", category = > SqlFunctionCategory.USER_DEFINED_TABLE_FUNCTION) > public static class MyUdtfFunction { > public static MyScannableTable eval(String a) { > ... > } > public static MyScannableTable eval(long a) { > ... > } > public static MyScannableTable eval(int a) { > ... > } > } > {code} > This issue wants to improve the SqlUserDefinedFunction and > SqlUserDefinedTableFunction to support the overload functions in both > User-defined scalar function class and table function class. > > > > -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Issue Comment Deleted] (CALCITE-3000) Improve SqlUserDefinedFunction and SqlUserDefinedTableFunction to support overload method call
[ https://issues.apache.org/jira/browse/CALCITE-3000?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] pengzhiwei updated CALCITE-3000: Comment: was deleted (was: Hi [~julianhyde], I mean overload function.Thanks for your correction. Here is the example : {code:java} @Function(name = "my_udf") public class MyUdf { public static String eval(String arg0,String arg1) {...} public static String eval(long arg0, long arg1){...} public static String eval(int arg0, int arg1) {...} }{code} _MyUdf_ is a user defined scalar function named "my_udf" with multiple overload method. Currently the ModelHandler would register multiple ScalarFunctions to the schema for each method. I would like to mapping one SqlUserDefinedFunction to "my_udf" no mater how many overload functions it has just like the BuildIn operator. To implement this, I will extend the Types#lookupMethod: {code:java} public static Method lookupMethod(Class clazz,String name, Class... inputTypes) { // Find the best match method from the overload functions for the input types. }{code} In the SqlReturnTypeInference for SqlUserDefinedFunction, I can use the Types#lookupMethod to infer the return type : {code:java} SqlReturnTypeInference returnTypeInference = opBinding -> { .. List paramTypes = convertToJavaTypes( opBinding.collectOperandTypes(), opBinding.getTypeFactory()); Method method = Types.lookupMethod(udfClazz, "eval", paramTypes); return typeFactory.createType(method.getReturnType()); }{code} The similar thing can apply to SqlOperandTypeInference and SqlOperandTypeChecker. So we can implement a dynamic type inference for the overload functions in scalar function. And there is only one SqlUserDefinedFunction mapping to the sql function. We can also do the same thing for SqlUserDefinedTableFunction for the "my_udtf" as followed: {code:java} @Function(name = "my_udtf") public class MyUdtf { public static ScannableTable eval(String arg0,String arg1) {...} public static ScannableTable eval(long arg0, long arg1) {...} public static ScannableTable eval(int arg0, int arg1) {...} } {code} We mapping one SqlUserDefinedTableFunction to the "my_udtf" table function and support all the overload methods call. ) > Improve SqlUserDefinedFunction and SqlUserDefinedTableFunction to support > overload method call > -- > > Key: CALCITE-3000 > URL: https://issues.apache.org/jira/browse/CALCITE-3000 > Project: Calcite > Issue Type: Improvement > Components: core >Reporter: pengzhiwei >Assignee: pengzhiwei >Priority: Major > Labels: pull-request-available > Time Spent: 20m > Remaining Estimate: 0h > > Here are User-defined scalar function class and table function class: > > {code:java} > @UDFDescription(name = "MY_UDF", category = > SqlFunctionCategory.USER_DEFINED_FUNCTION) > public static class MyUdfFunction { > public static String eval(String a) { > ... > } > public static String eval(String a, String b) { > > } > public static String eval(String a, int c) { > > } > } > {code} > {code:java} > @UDFDescription(name = "MY_UDTF", category = > SqlFunctionCategory.USER_DEFINED_TABLE_FUNCTION) > public static class MyUdtfFunction { > public static MyScannableTable eval(String a) { > ... > } > public static MyScannableTable eval(long a) { > ... > } > public static MyScannableTable eval(int a) { > ... > } > } > {code} > This issue wants to improve the SqlUserDefinedFunction and > SqlUserDefinedTableFunction to support the overload functions in both > User-defined scalar function class and table function class. > > > > -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Updated] (CALCITE-3000) Improve SqlUserDefinedFunction and SqlUserDefinedTableFunction to support overload method call
[ https://issues.apache.org/jira/browse/CALCITE-3000?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] pengzhiwei updated CALCITE-3000: Description: Here are User-defined scalar function class and table function class: {code:java} @UDFDescription(name = "MY_UDF", category = SqlFunctionCategory.USER_DEFINED_FUNCTION) public static class MyUdfFunction { public static String eval(String a) { ... } public static String eval(String a, String b) { } public static String eval(String a, int c) { } } {code} {code:java} @UDFDescription(name = "MY_UDTF", category = SqlFunctionCategory.USER_DEFINED_TABLE_FUNCTION) public static class MyUdtfFunction { public static MyScannableTable eval(String a) { ... } public static MyScannableTable eval(long a) { ... } public static MyScannableTable eval(int a) { ... } } {code} This issue wants to improve the SqlUserDefinedFunction and SqlUserDefinedTableFunction to support the overload functions in both User-defined scalar function class and table function class. was: Here are User-defined scalar function class and table function class: {code:java} @UDFDescription(name = "MY_UDF", category = SqlFunctionCategory.USER_DEFINED_FUNCTION) public static class MyUdfFunction { public static String eval(String a) { ... } public static String eval(String a, String b) { } public static String eval(String a, int c) { } } {code} {code:java} @UDFDescription(name = "MY_UDTF", category = SqlFunctionCategory.USER_DEFINED_TABLE_FUNCTION) public static class MyUdtfFunction { public static MyScannableTable eval(String a) { ... } public static MyScannableTable eval(long a) { ... } public static MyScannableTable eval(int a) { ... } } {code} This issue wants to improve SqlUserDefinedFunction and > Improve SqlUserDefinedFunction and SqlUserDefinedTableFunction to support > overload method call > -- > > Key: CALCITE-3000 > URL: https://issues.apache.org/jira/browse/CALCITE-3000 > Project: Calcite > Issue Type: Improvement > Components: core >Reporter: pengzhiwei >Assignee: pengzhiwei >Priority: Major > Labels: pull-request-available > Time Spent: 20m > Remaining Estimate: 0h > > Here are User-defined scalar function class and table function class: > > {code:java} > @UDFDescription(name = "MY_UDF", category = > SqlFunctionCategory.USER_DEFINED_FUNCTION) > public static class MyUdfFunction { > public static String eval(String a) { > ... > } > public static String eval(String a, String b) { > > } > public static String eval(String a, int c) { > > } > } > {code} > {code:java} > @UDFDescription(name = "MY_UDTF", category = > SqlFunctionCategory.USER_DEFINED_TABLE_FUNCTION) > public static class MyUdtfFunction { > public static MyScannableTable eval(String a) { > ... > } > public static MyScannableTable eval(long a) { > ... > } > public static MyScannableTable eval(int a) { > ... > } > } > {code} > This issue wants to improve the SqlUserDefinedFunction and > SqlUserDefinedTableFunction to support the overload functions in both > User-defined scalar function class and table function class. > > > > -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Updated] (CALCITE-3000) Improve SqlUserDefinedFunction and SqlUserDefinedTableFunction to support overload method call
[ https://issues.apache.org/jira/browse/CALCITE-3000?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] pengzhiwei updated CALCITE-3000: Description: Here are User-defined scalar function class and table function class: {code:java} @UDFDescription(name = "MY_UDF", category = SqlFunctionCategory.USER_DEFINED_FUNCTION) public static class MyUdfFunction { public static String eval(String a) { ... } public static String eval(String a, String b) { } public static String eval(String a, int c) { } } {code} {code:java} @UDFDescription(name = "MY_UDTF", category = SqlFunctionCategory.USER_DEFINED_TABLE_FUNCTION) public static class MyUdtfFunction { public static MyScannableTable eval(String a) { ... } public static MyScannableTable eval(long a) { ... } public static MyScannableTable eval(int a) { ... } } {code} This issue wants to improve SqlUserDefinedFunction and was: Here are User-defined scalar function class and table function class: {code:java} @UDFDescription(name = "MY_UDF", category = SqlFunctionCategory.USER_DEFINED_FUNCTION) public static class MyUdfFunction { public static String eval(String a) { ... } public static String eval(String a, String b) { } public static String eval(String a, int c) { } } {code} {code:java} @UDFDescription(name = "MY_UDTF", category = SqlFunctionCategory.USER_DEFINED_TABLE_FUNCTION) public static class MyUdtfFunction { public static MyScannableTable eval(String a) { ... } public static MyScannableTable eval(long a) { ... } public static MyScannableTable eval(int a) { ... } } {code} This issue wants to improve > Improve SqlUserDefinedFunction and SqlUserDefinedTableFunction to support > overload method call > -- > > Key: CALCITE-3000 > URL: https://issues.apache.org/jira/browse/CALCITE-3000 > Project: Calcite > Issue Type: Improvement > Components: core >Reporter: pengzhiwei >Assignee: pengzhiwei >Priority: Major > Labels: pull-request-available > Time Spent: 20m > Remaining Estimate: 0h > > Here are User-defined scalar function class and table function class: > > {code:java} > @UDFDescription(name = "MY_UDF", category = > SqlFunctionCategory.USER_DEFINED_FUNCTION) > public static class MyUdfFunction { > public static String eval(String a) { > ... > } > public static String eval(String a, String b) { > > } > public static String eval(String a, int c) { > > } > } > {code} > {code:java} > @UDFDescription(name = "MY_UDTF", category = > SqlFunctionCategory.USER_DEFINED_TABLE_FUNCTION) > public static class MyUdtfFunction { > public static MyScannableTable eval(String a) { > ... > } > public static MyScannableTable eval(long a) { > ... > } > public static MyScannableTable eval(int a) { > ... > } > } > {code} > This issue wants to improve SqlUserDefinedFunction and > > > > > > > > -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Updated] (CALCITE-3000) Improve SqlUserDefinedFunction and SqlUserDefinedTableFunction to support overload method call
[ https://issues.apache.org/jira/browse/CALCITE-3000?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] pengzhiwei updated CALCITE-3000: Description: Here are User-defined scalar function class and table function class: {code:java} @UDFDescription(name = "MY_UDF", category = SqlFunctionCategory.USER_DEFINED_FUNCTION) public static class MyUdfFunction { public static String eval(String a) { ... } public static String eval(String a, String b) { } public static String eval(String a, int c) { } } {code} {code:java} @UDFDescription(name = "MY_UDTF", category = SqlFunctionCategory.USER_DEFINED_TABLE_FUNCTION) public static class MyUdtfFunction { public static MyScannableTable eval(String a) { ... } public static MyScannableTable eval(long a) { ... } public static MyScannableTable eval(int a) { ... } } {code} This issue wants to improve was: Currently the ModelHandler adds one Function to the schema for one java method. For the scalar function,ModelHandler resolves all the method in the class and translate each of them to ScalarFunction to support overload method call. But for TableFunction, this has not been support yet. Maybe we can support it as the scalar function does. However in that way, one sql function may match multiple Functions in the schema.I think this is not a good way. It is better to have only one Function in the schema for one sql function just like the BuildIn operator. I'd like to support a new operand type check strategy for SqlUserDefinedFunction and SqlUserDefinedTableFunction which can infer the operand type and return type according to the input parameter types. I will extend the Types#lookupMethod to support finding the best method in all of the override methods in a class. And the SqlReturnTypeInference、SqlOperandTypeInference and SqlOperandTypeChecker can use this to do the type inference according to the input types. I think it is a dynamically way compared with the original way. Any suggestion is welcomed,thanks! > Improve SqlUserDefinedFunction and SqlUserDefinedTableFunction to support > overload method call > -- > > Key: CALCITE-3000 > URL: https://issues.apache.org/jira/browse/CALCITE-3000 > Project: Calcite > Issue Type: Improvement > Components: core >Reporter: pengzhiwei >Assignee: pengzhiwei >Priority: Major > Labels: pull-request-available > Time Spent: 20m > Remaining Estimate: 0h > > Here are User-defined scalar function class and table function class: > > {code:java} > @UDFDescription(name = "MY_UDF", category = > SqlFunctionCategory.USER_DEFINED_FUNCTION) > public static class MyUdfFunction { > public static String eval(String a) { > ... > } > public static String eval(String a, String b) { > > } > public static String eval(String a, int c) { > > } > } > {code} > {code:java} > @UDFDescription(name = "MY_UDTF", category = > SqlFunctionCategory.USER_DEFINED_TABLE_FUNCTION) > public static class MyUdtfFunction { > public static MyScannableTable eval(String a) { > ... > } > public static MyScannableTable eval(long a) { > ... > } > public static MyScannableTable eval(int a) { > ... > } > } > {code} > This issue wants to improve > > > > > > > > -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Commented] (CALCITE-3002) Case statement fails with: SqlValidatorException: Cannot apply '=' to arguments of type ' = '.
[ https://issues.apache.org/jira/browse/CALCITE-3002?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16818603#comment-16818603 ] pengzhiwei commented on CALCITE-3002: - I think the case you show should be : {code:java} select (case when empno > 1 then 1 else empno end) from emps {code} or {code:java} select (case empno > 1 when true then 1 else empno end) from emps {code} > Case statement fails with: SqlValidatorException: Cannot apply '=' to > arguments of type ' = '. > > > Key: CALCITE-3002 > URL: https://issues.apache.org/jira/browse/CALCITE-3002 > Project: Calcite > Issue Type: Bug > Components: csv-adapter, druid >Affects Versions: 1.19.0 >Reporter: Egor Ryashin >Priority: Major > > Query like > {code:java} > select (case empno when empno > 1 then 1 else empno end) from emps{code} > produces an error: > {code} > java.lang.RuntimeException: java.sql.SQLException: Error while executing SQL > "select (case empno when empno > 1 then 1 else empno end) from emps": From > line 1, column 9 to line 1, column 55: Cannot apply '=' to arguments of type > ' = '. Supported form(s): ' = > ' at > org.apache.calcite.test.CsvTest$Fluent.ok(CsvTest.java:1021) at > org.apache.calcite.test.CsvTest.testSelectSingleProjectCase(CsvTest.java:188) > 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.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50) > at > org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12) > at > org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47) > at > org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17) > at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325) at > org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78) > at > org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57) > at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290) at > org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71) at > org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288) at > org.junit.runners.ParentRunner.access$000(ParentRunner.java:58) at > org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268) at > org.junit.runners.ParentRunner.run(ParentRunner.java:363) at > org.junit.runner.JUnitCore.run(JUnitCore.java:137) at > com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68) > at > com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:51) > at > com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:237) > at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70) > 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 > com.intellij.rt.execution.application.AppMain.main(AppMain.java:147) Caused > by: java.sql.SQLException: Error while executing SQL "select (case empno when > empno > 1 then 1 else empno end) from emps": From line 1, column 9 to line 1, > column 55: Cannot apply '=' to arguments of type ' = '. > Supported form(s): ' = ' at > org.apache.calcite.avatica.Helper.createException(Helper.java:56) at > org.apache.calcite.avatica.Helper.createException(Helper.java:41) at > org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:163) > at > org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:227) > at org.apache.calcite.test.CsvTest.checkSql(CsvTest.java:358) at > org.apache.calcite.test.CsvTest.access$300(CsvTest.java:67) at > org.apache.calcite.test.CsvTest$Fluent.ok(CsvTest.java:1018) ... 28 more > Caused by: org.apache.calcite.runtime.CalciteContextException: From line 1, > column 9 to line 1, column 55: Cannot apply '=' to arguments of type > ' = '. Supported form(s): ' = > ' at > sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at > sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) > at > sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) > at
[jira] [Commented] (CALCITE-3000) Improve SqlUserDefinedFunction and SqlUserDefinedTableFunction to support overload method call
[ https://issues.apache.org/jira/browse/CALCITE-3000?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16818596#comment-16818596 ] pengzhiwei commented on CALCITE-3000: - Hi [~julianhyde], I mean overload function.Thanks for your correction. Here is the example : {code:java} @Function(name = "my_udf") public class MyUdf { public static String eval(String arg0,String arg1) {...} public static String eval(long arg0, long arg1){...} public static String eval(int arg0, int arg1) {...} }{code} _MyUdf_ is a user defined scalar function named "my_udf" with multiple overload method. Currently the ModelHandler would register multiple ScalarFunctions to the schema for each method. I would like to mapping one SqlUserDefinedFunction to "my_udf" no mater how many overload functions it has just like the BuildIn operator. To implement this, I will extend the Types#lookupMethod: {code:java} public static Method lookupMethod(Class clazz,String name, Class... inputTypes) { // Find the best match method from the overload functions for the input types. }{code} In the SqlReturnTypeInference for SqlUserDefinedFunction, I can use the Types#lookupMethod to infer the return type : {code:java} SqlReturnTypeInference returnTypeInference = opBinding -> { .. List paramTypes = convertToJavaTypes( opBinding.collectOperandTypes(), opBinding.getTypeFactory()); Method method = Types.lookupMethod(udfClazz, "eval", paramTypes); return typeFactory.createType(method.getReturnType()); }{code} The similar thing can apply to SqlOperandTypeInference and SqlOperandTypeChecker. So we can implement a dynamic type inference for the overload functions in scalar function. And there is only one SqlUserDefinedFunction mapping to the sql function. We can also do the same thing for SqlUserDefinedTableFunction for the "my_udtf" as followed: {code:java} @Function(name = "my_udtf") public class MyUdtf { public static ScannableTable eval(String arg0,String arg1) {...} public static ScannableTable eval(long arg0, long arg1) {...} public static ScannableTable eval(int arg0, int arg1) {...} } {code} We mapping one SqlUserDefinedTableFunction to the "my_udtf" table function and support all the overload methods call. > Improve SqlUserDefinedFunction and SqlUserDefinedTableFunction to support > overload method call > -- > > Key: CALCITE-3000 > URL: https://issues.apache.org/jira/browse/CALCITE-3000 > Project: Calcite > Issue Type: Improvement > Components: core >Reporter: pengzhiwei >Assignee: pengzhiwei >Priority: Major > > Currently the ModelHandler adds one Function to the schema for one java > method. For the scalar function,ModelHandler resolves all the method in the > class and translate each of them to ScalarFunction to support overload method > call. But for TableFunction, this has not been support yet. > Maybe we can support it as the scalar function does. However in that way, > one sql function may match multiple Functions in the schema.I think this is > not a good way. It is better to have only one Function in the schema for one > sql function just like the BuildIn operator. > I'd like to support a new operand type check strategy for > SqlUserDefinedFunction and SqlUserDefinedTableFunction which can infer the > operand type and return type according to the input parameter types. > I will extend the Types#lookupMethod to support finding the best method in > all of the override methods in a class. And the > SqlReturnTypeInference、SqlOperandTypeInference and > SqlOperandTypeChecker can use this to do the type inference according to the > input types. I think it is a dynamically way compared with the original way. > Any suggestion is welcomed,thanks! > > > > > > > -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Updated] (CALCITE-3000) Improve SqlUserDefinedFunction and SqlUserDefinedTableFunction to support overload method call
[ https://issues.apache.org/jira/browse/CALCITE-3000?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] pengzhiwei updated CALCITE-3000: Description: Currently the ModelHandler adds one Function to the schema for one java method. For the scalar function,ModelHandler resolves all the method in the class and translate each of them to ScalarFunction to support overload method call. But for TableFunction, this has not been support yet. Maybe we can support it as the scalar function does. However in that way, one sql function may match multiple Functions in the schema.I think this is not a good way. It is better to have only one Function in the schema for one sql function just like the BuildIn operator. I'd like to support a new operand type check strategy for SqlUserDefinedFunction and SqlUserDefinedTableFunction which can infer the operand type and return type according to the input parameter types. I will extend the Types#lookupMethod to support finding the best method in all of the override methods in a class. And the SqlReturnTypeInference、SqlOperandTypeInference and SqlOperandTypeChecker can use this to do the type inference according to the input types. I think it is a dynamically way compared with the original way. Any suggestion is welcomed,thanks! was: Currently the ModelHandler adds one Function to the schema for one java method. For the scalar function,ModelHandler resolves all the method in the class and translate each of them to ScalarFunction to support override method call. But for TableFunction, this has not been support yet. Maybe we can support it as the scalar function does. However in that way, one sql function may match multiple Functions in the schema.I think this is not a good way. It is better to have only one Function in the schema for one sql function just like the BuildIn operator. I'd like to support a new operand type check strategy for SqlUserDefinedFunction and SqlUserDefinedTableFunction which can infer the operand type and return type according to the input parameter types. I will extend the Types#lookupMethod to support finding the best method in all of the override methods in a class. And the SqlReturnTypeInference、SqlOperandTypeInference and SqlOperandTypeChecker can use this to do the type inference according to the input types. I think it is a dynamically way compared with the original way. Any suggestion is welcomed,thanks! > Improve SqlUserDefinedFunction and SqlUserDefinedTableFunction to support > overload method call > -- > > Key: CALCITE-3000 > URL: https://issues.apache.org/jira/browse/CALCITE-3000 > Project: Calcite > Issue Type: Improvement > Components: core >Reporter: pengzhiwei >Assignee: pengzhiwei >Priority: Major > > Currently the ModelHandler adds one Function to the schema for one java > method. For the scalar function,ModelHandler resolves all the method in the > class and translate each of them to ScalarFunction to support overload method > call. But for TableFunction, this has not been support yet. > Maybe we can support it as the scalar function does. However in that way, > one sql function may match multiple Functions in the schema.I think this is > not a good way. It is better to have only one Function in the schema for one > sql function just like the BuildIn operator. > I'd like to support a new operand type check strategy for > SqlUserDefinedFunction and SqlUserDefinedTableFunction which can infer the > operand type and return type according to the input parameter types. > I will extend the Types#lookupMethod to support finding the best method in > all of the override methods in a class. And the > SqlReturnTypeInference、SqlOperandTypeInference and > SqlOperandTypeChecker can use this to do the type inference according to the > input types. I think it is a dynamically way compared with the original way. > Any suggestion is welcomed,thanks! > > > > > > > -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Updated] (CALCITE-3000) Improve SqlUserDefinedFunction and SqlUserDefinedTableFunction to support overload method call
[ https://issues.apache.org/jira/browse/CALCITE-3000?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] pengzhiwei updated CALCITE-3000: Summary: Improve SqlUserDefinedFunction and SqlUserDefinedTableFunction to support overload method call (was: Improve SqlUserDefinedFunction and SqlUserDefinedTableFunction to support override method call) > Improve SqlUserDefinedFunction and SqlUserDefinedTableFunction to support > overload method call > -- > > Key: CALCITE-3000 > URL: https://issues.apache.org/jira/browse/CALCITE-3000 > Project: Calcite > Issue Type: Improvement > Components: core >Reporter: pengzhiwei >Assignee: pengzhiwei >Priority: Major > > Currently the ModelHandler adds one Function to the schema for one java > method. For the scalar function,ModelHandler resolves all the method in the > class and translate each of them to ScalarFunction to support override method > call. But for TableFunction, this has not been support yet. > Maybe we can support it as the scalar function does. However in that way, > one sql function may match multiple Functions in the schema.I think this is > not a good way. It is better to have only one Function in the schema for one > sql function just like the BuildIn operator. > I'd like to support a new operand type check strategy for > SqlUserDefinedFunction and SqlUserDefinedTableFunction which can infer the > operand type and return type according to the input parameter types. > I will extend the Types#lookupMethod to support finding the best method in > all of the override methods in a class. And the > SqlReturnTypeInference、SqlOperandTypeInference and > SqlOperandTypeChecker can use this to do the type inference according to the > input types. I think it is a dynamically way compared with the original way. > Any suggestion is welcomed,thanks! > > > > > > > -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Created] (CALCITE-3000) Improve SqlUserDefinedFunction and SqlUserDefinedTableFunction to support override method call
pengzhiwei created CALCITE-3000: --- Summary: Improve SqlUserDefinedFunction and SqlUserDefinedTableFunction to support override method call Key: CALCITE-3000 URL: https://issues.apache.org/jira/browse/CALCITE-3000 Project: Calcite Issue Type: Improvement Components: core Reporter: pengzhiwei Assignee: pengzhiwei Currently the ModelHandler adds one Function to the schema for one java method. For the scalar function,ModelHandler resolves all the method in the class and translate each of them to ScalarFunction to support override method call. But for TableFunction, this has not been support yet. Maybe we can support it as the scalar function does. However in that way, one sql function may match multiple Functions in the schema.I think this is not a good way. It is better to have only one Function in the schema for one sql function just like the BuildIn operator. I'd like to support a new operand type check strategy for SqlUserDefinedFunction and SqlUserDefinedTableFunction which can infer the operand type and return type according to the input parameter types. I will extend the Types#lookupMethod to support finding the best method in all of the override methods in a class. And the SqlReturnTypeInference、SqlOperandTypeInference and SqlOperandTypeChecker can use this to do the type inference according to the input types. I think it is a dynamically way compared with the original way. Any suggestion is welcomed,thanks! -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Assigned] (CALCITE-1581) UDTF like in hive
[ https://issues.apache.org/jira/browse/CALCITE-1581?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] pengzhiwei reassigned CALCITE-1581: --- Assignee: pengzhiwei (was: Sihua Zhou) > UDTF like in hive > - > > Key: CALCITE-1581 > URL: https://issues.apache.org/jira/browse/CALCITE-1581 > Project: Calcite > Issue Type: New Feature >Reporter: Xiaoyong Deng >Assignee: pengzhiwei >Priority: Major > Labels: pull-request-available, udtf > Fix For: 1.20.0 > > Time Spent: 2h 50m > Remaining Estimate: 0h > > Support one row in and multi-column/multi-row out(one-to-many mapping), just > like udtf in hive. > The query would like this: > {code} > select > func(c0, c1) as (f0, f1, f2) > from table_name; > {code} > c0 and c1 are 'table_name' columns. f0, f1 and f2 are new generated columns. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Comment Edited] (CALCITE-2921) nullif(null,y) throws exception in verification
[ https://issues.apache.org/jira/browse/CALCITE-2921?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16804546#comment-16804546 ] pengzhiwei edited comment on CALCITE-2921 at 4/8/19 11:40 AM: -- Hi [~julianhyde], the pr has been updated, here is the mainly change list: * Forbid to return "NULL" type for SqlOperator. As it may cause uncertain semantics.For example, "Not(null) + 1" can pass the validate, if the "Not(null)"'s return type is "NULL". I have added a default type for most operator which may return "NULL" type. * add "allowNakedNull()" in SqlConformance. * fix other issue you mentioned in last review. Please have a look again,Thanks! was (Author: pzw2018): Hi [~julianhyde], the pr has been updated, here is the mainly change list: * The "NULL" return type of SqlOperator is forbidden. As it may cause uncertain semantics.For example, "Not(null) + 1" can pass the validate, if the "Not(null)"'s return type is "NULL". I have added a default type for most operator which may return "NULL" type. * add "allowNakedNull()" in SqlConformance. * fix other issue you mentioned in last review. Please have a look again,Thanks! > nullif(null,y) throws exception in verification > > > Key: CALCITE-2921 > URL: https://issues.apache.org/jira/browse/CALCITE-2921 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.18.0 >Reporter: pengzhiwei >Assignee: pengzhiwei >Priority: Critical > Labels: pull-request-available > Fix For: 1.20.0 > > Time Spent: 10m > Remaining Estimate: 0h > > Currently calcite will translate "nullif(x,y)" to a case-when expression,just > like "case when x = y then null else x".So when "x" is null literal,a > exception throws out as follow: > {code:java} > ELSE clause or at least one THEN clause must be non-NULL > {code} > I have test in mysql,"nullif(null,y) works well.So I think we should allow > this usage of "nullif". > There are two ways to fix this issue: > 1) Skip the check for "foundNotNull" in SqlCaseOperator#checkOperandTypes: > > {code:java} > if (!foundNotNull) { > // according to the sql standard we can not have all of the THEN > // statements and the ELSE returning null > if (throwOnFailure) { > throw callBinding.newError(RESOURCE.mustNotNullInElse()); > } > return false; > }{code} > However, as the comment says, we cannot have all of the THEN and ELSE > returning null. > 2) Disable the translation from nullif to case-when and keep "nullif" as it > is. > Any suggestion is welcomed,Thanks! -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Updated] (CALCITE-2982) SqlItemOperator should throw understandable exception message for incorrect field type
[ https://issues.apache.org/jira/browse/CALCITE-2982?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] pengzhiwei updated CALCITE-2982: Description: In the following sql: {code:java} select name[0] from dept {code} _name_ is a field of _VARCHAR_ type, the follow exception message is thrown out: {code:java} java.lang.AssertionError: VARCHAR at org.apache.calcite.sql.fun.SqlItemOperator.getChecker(SqlItemOperator.java:109) at org.apache.calcite.sql.fun.SqlItemOperator.checkOperandTypes(SqlItemOperator.java:91) at org.apache.calcite.sql.SqlOperator.validateOperands(SqlOperator.java:432) at org.apache.calcite.sql.SqlOperator.deriveType(SqlOperator.java:516) at org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:5607) at org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:5594) at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:139) {code} The exception message is hard to understand for user. I think a message like "Cannot apply item opeator to VARCHAR type for filed "name" at line xx column xx " is more friendly to the user. was: In the follow sql: {code:java} select name[0] from dept {code} _name_ is a field of _VARCHAR_ type, the follow exception message is thrown out: {code:java} java.lang.AssertionError: VARCHAR at org.apache.calcite.sql.fun.SqlItemOperator.getChecker(SqlItemOperator.java:109) at org.apache.calcite.sql.fun.SqlItemOperator.checkOperandTypes(SqlItemOperator.java:91) at org.apache.calcite.sql.SqlOperator.validateOperands(SqlOperator.java:432) at org.apache.calcite.sql.SqlOperator.deriveType(SqlOperator.java:516) at org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:5607) at org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:5594) at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:139) {code} The exception message is hard to understand for user. I think a message like "Cannot apply item opeator to VARCHAR type for filed "name" at line xx column xx " is more friendly to the user. > SqlItemOperator should throw understandable exception message for incorrect > field type > -- > > Key: CALCITE-2982 > URL: https://issues.apache.org/jira/browse/CALCITE-2982 > Project: Calcite > Issue Type: Bug > Components: core >Reporter: pengzhiwei >Assignee: pengzhiwei >Priority: Major > Labels: pull-request-available > Fix For: 1.20.0 > > Time Spent: 10m > Remaining Estimate: 0h > > In the following sql: > {code:java} > select name[0] from dept > {code} > _name_ is a field of _VARCHAR_ type, the follow exception message is thrown > out: > {code:java} > java.lang.AssertionError: VARCHAR > at > org.apache.calcite.sql.fun.SqlItemOperator.getChecker(SqlItemOperator.java:109) > at > org.apache.calcite.sql.fun.SqlItemOperator.checkOperandTypes(SqlItemOperator.java:91) > at org.apache.calcite.sql.SqlOperator.validateOperands(SqlOperator.java:432) > at org.apache.calcite.sql.SqlOperator.deriveType(SqlOperator.java:516) > at > org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:5607) > at > org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:5594) > at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:139) > {code} > The exception message is hard to understand for user. I think a message like > "Cannot apply item opeator to VARCHAR type for filed "name" at line xx > column xx " is more friendly to the user. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Updated] (CALCITE-2982) SqlItemOperator should throw understandable exception message for incorrect field type
[ https://issues.apache.org/jira/browse/CALCITE-2982?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] pengzhiwei updated CALCITE-2982: Summary: SqlItemOperator should throw understandable exception message for incorrect field type (was: SqlItemOpeator should throw understandable exception message for incorrect field type) > SqlItemOperator should throw understandable exception message for incorrect > field type > -- > > Key: CALCITE-2982 > URL: https://issues.apache.org/jira/browse/CALCITE-2982 > Project: Calcite > Issue Type: Bug > Components: core >Reporter: pengzhiwei >Assignee: pengzhiwei >Priority: Major > Fix For: 1.20.0 > > > In the follow sql: > {code:java} > select name[0] from dept > {code} > _name_ is a field of _VARCHAR_ type, the follow exception message is thrown > out: > {code:java} > java.lang.AssertionError: VARCHAR > at > org.apache.calcite.sql.fun.SqlItemOperator.getChecker(SqlItemOperator.java:109) > at > org.apache.calcite.sql.fun.SqlItemOperator.checkOperandTypes(SqlItemOperator.java:91) > at org.apache.calcite.sql.SqlOperator.validateOperands(SqlOperator.java:432) > at org.apache.calcite.sql.SqlOperator.deriveType(SqlOperator.java:516) > at > org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:5607) > at > org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:5594) > at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:139) > {code} > The exception message is hard to understand for user. I think a message like > "Cannot apply item opeator to VARCHAR type for filed "name" at line xx > column xx " is more friendly to the user. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Updated] (CALCITE-2982) SqlItemOpeator should throw understandable exception message for incorrect field type
[ https://issues.apache.org/jira/browse/CALCITE-2982?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] pengzhiwei updated CALCITE-2982: Description: In the follow sql: {code:java} select name[0] from dept {code} _name_ is a field of _VARCHAR_ type, the follow exception message is thrown out: {code:java} java.lang.AssertionError: VARCHAR at org.apache.calcite.sql.fun.SqlItemOperator.getChecker(SqlItemOperator.java:109) at org.apache.calcite.sql.fun.SqlItemOperator.checkOperandTypes(SqlItemOperator.java:91) at org.apache.calcite.sql.SqlOperator.validateOperands(SqlOperator.java:432) at org.apache.calcite.sql.SqlOperator.deriveType(SqlOperator.java:516) at org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:5607) at org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:5594) at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:139) {code} The exception message is hard to understand for user. I think a message like "Cannot apply item opeator to VARCHAR type for filed "name" at line xx column xx " is more friendly to the user. was: In the follow sql: {code:java} sql("select name[0] from dept").ok(); {code} _name_ is a field of _VARCHAR_ type, the follow exception message is thrown out: {code:java} java.lang.AssertionError: VARCHAR at org.apache.calcite.sql.fun.SqlItemOperator.getChecker(SqlItemOperator.java:109) at org.apache.calcite.sql.fun.SqlItemOperator.checkOperandTypes(SqlItemOperator.java:91) at org.apache.calcite.sql.SqlOperator.validateOperands(SqlOperator.java:432) at org.apache.calcite.sql.SqlOperator.deriveType(SqlOperator.java:516) at org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:5607) at org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:5594) at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:139) {code} The exception message is hard to understand for user. I think a message like "Cannot apply item opeator to VARCHAR type for filed "name" at line xx column xx " is more friendly to the user. > SqlItemOpeator should throw understandable exception message for incorrect > field type > - > > Key: CALCITE-2982 > URL: https://issues.apache.org/jira/browse/CALCITE-2982 > Project: Calcite > Issue Type: Bug > Components: core >Reporter: pengzhiwei >Assignee: pengzhiwei >Priority: Major > Fix For: 1.20.0 > > > In the follow sql: > {code:java} > select name[0] from dept > {code} > _name_ is a field of _VARCHAR_ type, the follow exception message is thrown > out: > {code:java} > java.lang.AssertionError: VARCHAR > at > org.apache.calcite.sql.fun.SqlItemOperator.getChecker(SqlItemOperator.java:109) > at > org.apache.calcite.sql.fun.SqlItemOperator.checkOperandTypes(SqlItemOperator.java:91) > at org.apache.calcite.sql.SqlOperator.validateOperands(SqlOperator.java:432) > at org.apache.calcite.sql.SqlOperator.deriveType(SqlOperator.java:516) > at > org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:5607) > at > org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:5594) > at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:139) > {code} > The exception message is hard to understand for user. I think a message like > "Cannot apply item opeator to VARCHAR type for filed "name" at line xx > column xx " is more friendly to the user. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Created] (CALCITE-2982) SqlItemOpeator should throw understandable exception message for incorrect field type
pengzhiwei created CALCITE-2982: --- Summary: SqlItemOpeator should throw understandable exception message for incorrect field type Key: CALCITE-2982 URL: https://issues.apache.org/jira/browse/CALCITE-2982 Project: Calcite Issue Type: Bug Components: core Reporter: pengzhiwei Assignee: pengzhiwei Fix For: 1.20.0 In the follow sql: {code:java} sql("select name[0] from dept").ok(); {code} _name_ is a field of _VARCHAR_ type, the follow exception message is thrown out: {code:java} java.lang.AssertionError: VARCHAR at org.apache.calcite.sql.fun.SqlItemOperator.getChecker(SqlItemOperator.java:109) at org.apache.calcite.sql.fun.SqlItemOperator.checkOperandTypes(SqlItemOperator.java:91) at org.apache.calcite.sql.SqlOperator.validateOperands(SqlOperator.java:432) at org.apache.calcite.sql.SqlOperator.deriveType(SqlOperator.java:516) at org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:5607) at org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:5594) at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:139) {code} The exception message is hard to understand for user. I think a message like "Cannot apply item opeator to VARCHAR type for filed "name" at line xx column xx " is more friendly to the user. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Commented] (CALCITE-2977) Exception is not thrown out when there are ambiguous field in select list
[ https://issues.apache.org/jira/browse/CALCITE-2977?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16812066#comment-16812066 ] pengzhiwei commented on CALCITE-2977: - Thank [~julianhyde] for take you time, Please have a review again. > Exception is not thrown out when there are ambiguous field in select list > -- > > Key: CALCITE-2977 > URL: https://issues.apache.org/jira/browse/CALCITE-2977 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.20.0 >Reporter: pengzhiwei >Assignee: pengzhiwei >Priority: Major > Labels: pull-request-available > Fix For: 1.20.0 > > Time Spent: 10m > Remaining Estimate: 0h > > In the following sql: > {code:java} > select t0 from (select 1 as t0, 2 as t0 from dept){code} > _SqlValidatorImpl_ does not throw an exception for the ambiguous field "t0". > > -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Comment Edited] (CALCITE-2977) Exception is not thrown out when there are ambiguous field in select list
[ https://issues.apache.org/jira/browse/CALCITE-2977?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16809618#comment-16809618 ] pengzhiwei edited comment on CALCITE-2977 at 4/4/19 8:44 AM: - I prefer to allow the case that the ambiguous field is not referenced but throw exception for that is referenced. A PR is made for this issue,please have a review,thanks! PS: I also fix same test case which enable the _CaseSensitive_ to _true_ ,But use the default _UnquotedCasing TO_UPPER,_ which make all identifier to upper and make the test for case-sensitive no meaning. . was (Author: pzw2018): I prefer to allow the case that the ambiguous field is not referenced and throw exception for that is referenced.A PR is made for this issue,please have a review,thanks! PS: I also fix same test case which enable the _CaseSensitive_ to _true_ ,But use the default _UnquotedCasing TO_UPPER,_ which make all identifier to upper and make the test for case-sensitive no meaning. . > Exception is not thrown out when there are ambiguous field in select list > -- > > Key: CALCITE-2977 > URL: https://issues.apache.org/jira/browse/CALCITE-2977 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.20.0 >Reporter: pengzhiwei >Assignee: pengzhiwei >Priority: Major > Labels: pull-request-available > Fix For: 1.20.0 > > Time Spent: 10m > Remaining Estimate: 0h > > In the following sql: > {code:java} > select t0 from (select 1 as t0, 2 as t0 from dept){code} > _SqlValidatorImpl_ does not throw an exception for the ambiguous field "t0". > > -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Commented] (CALCITE-2977) Exception is not thrown out when there are ambiguous field in select list
[ https://issues.apache.org/jira/browse/CALCITE-2977?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16809618#comment-16809618 ] pengzhiwei commented on CALCITE-2977: - I prefer to allow the case that the ambiguous field is not referenced and throw exception for that is referenced.A PR is made for this issue,please have a review,thanks! PS: I also fix same test case which enable the _CaseSensitive_ to _true_ ,But use the default _UnquotedCasing TO_UPPER,_ which make all identifier to upper and make the test for case-sensitive no meaning. . > Exception is not thrown out when there are ambiguous field in select list > -- > > Key: CALCITE-2977 > URL: https://issues.apache.org/jira/browse/CALCITE-2977 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.20.0 >Reporter: pengzhiwei >Assignee: pengzhiwei >Priority: Major > Labels: pull-request-available > Fix For: 1.20.0 > > Time Spent: 10m > Remaining Estimate: 0h > > In the following sql: > {code:java} > select t0 from (select 1 as t0, 2 as t0 from dept){code} > _SqlValidatorImpl_ does not throw an exception for the ambiguous field "t0". > > -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Comment Edited] (CALCITE-1581) UDTF like in hive
[ https://issues.apache.org/jira/browse/CALCITE-1581?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16808689#comment-16808689 ] pengzhiwei edited comment on CALCITE-1581 at 4/3/19 12:56 PM: -- Hi [~julianhyde], I have done the syntax validation in _SqlValidatorImpl_ and rewrite the plan in _SqlToRelConverter_. The PR has updated, please have a look again,Thanks! was (Author: pzw2018): Hi [~julianhyde], I have done the syntax validation in _SqlValidatorImpl_ and rewrite the plan in _SqlToRelConverter_. __The PR has updated, please have a look again,Thanks! > UDTF like in hive > - > > Key: CALCITE-1581 > URL: https://issues.apache.org/jira/browse/CALCITE-1581 > Project: Calcite > Issue Type: New Feature >Reporter: Xiaoyong Deng >Assignee: Sihua Zhou >Priority: Major > Labels: pull-request-available, udtf > Fix For: 1.20.0 > > Time Spent: 2h 50m > Remaining Estimate: 0h > > Support one row in and multi-column/multi-row out(one-to-many mapping), just > like udtf in hive. > The query would like this: > {code} > select > func(c0, c1) as (f0, f1, f2) > from table_name; > {code} > c0 and c1 are 'table_name' columns. f0, f1 and f2 are new generated columns. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Commented] (CALCITE-1581) UDTF like in hive
[ https://issues.apache.org/jira/browse/CALCITE-1581?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16808689#comment-16808689 ] pengzhiwei commented on CALCITE-1581: - Hi [~julianhyde], I have done the syntax validation in _SqlValidatorImpl_ and rewrite the plan in _SqlToRelConverter_. __The PR has updated, please have a look again,Thanks! > UDTF like in hive > - > > Key: CALCITE-1581 > URL: https://issues.apache.org/jira/browse/CALCITE-1581 > Project: Calcite > Issue Type: New Feature >Reporter: Xiaoyong Deng >Assignee: Sihua Zhou >Priority: Major > Labels: pull-request-available, udtf > Fix For: 1.20.0 > > Time Spent: 2h 50m > Remaining Estimate: 0h > > Support one row in and multi-column/multi-row out(one-to-many mapping), just > like udtf in hive. > The query would like this: > {code} > select > func(c0, c1) as (f0, f1, f2) > from table_name; > {code} > c0 and c1 are 'table_name' columns. f0, f1 and f2 are new generated columns. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Comment Edited] (CALCITE-2977) Exception is not thrown out when there are ambiguous field in select list
[ https://issues.apache.org/jira/browse/CALCITE-2977?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16808664#comment-16808664 ] pengzhiwei edited comment on CALCITE-2977 at 4/3/19 12:17 PM: -- I think the problem exist in the _RelDataTypeImpl#getField()_ method, which does not check the ambiguous filed. was (Author: pzw2018): I think this problem exist in the _RelDataTypeImpl#getField()_ method, which does not check the ambiguous filed. > Exception is not thrown out when there are ambiguous field in select list > -- > > Key: CALCITE-2977 > URL: https://issues.apache.org/jira/browse/CALCITE-2977 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.20.0 >Reporter: pengzhiwei >Assignee: pengzhiwei >Priority: Major > Fix For: 1.20.0 > > > In the following sql: > {code:java} > select t0 from (select 1 as t0, 2 as t0 from dept){code} > _SqlValidatorImpl_ does not throw an exception for the ambiguous field "t0". > > -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Commented] (CALCITE-2977) Exception is not thrown out when there are ambiguous field in select list
[ https://issues.apache.org/jira/browse/CALCITE-2977?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16808664#comment-16808664 ] pengzhiwei commented on CALCITE-2977: - I think this problem exist in the _RelDataTypeImpl#getField()_ method, which does not check the ambiguous filed. > Exception is not thrown out when there are ambiguous field in select list > -- > > Key: CALCITE-2977 > URL: https://issues.apache.org/jira/browse/CALCITE-2977 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.20.0 >Reporter: pengzhiwei >Assignee: pengzhiwei >Priority: Major > Fix For: 1.20.0 > > > In the following sql: > {code:java} > select t0 from (select 1 as t0, 2 as t0 from dept){code} > _SqlValidatorImpl_ does not throw an exception for the ambiguous field "t0". > > -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Created] (CALCITE-2977) Exception is not thrown out when there are ambiguous field in select list
pengzhiwei created CALCITE-2977: --- Summary: Exception is not thrown out when there are ambiguous field in select list Key: CALCITE-2977 URL: https://issues.apache.org/jira/browse/CALCITE-2977 Project: Calcite Issue Type: Bug Components: core Affects Versions: 1.20.0 Reporter: pengzhiwei Assignee: pengzhiwei Fix For: 1.20.0 In the following sql: {code:java} select t0 from (select 1 as t0, 2 as t0 from dept){code} _SqlValidatorImpl_ does not throw an exception for the ambiguous field "t0". -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Comment Edited] (CALCITE-1581) UDTF like in hive
[ https://issues.apache.org/jira/browse/CALCITE-1581?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16807398#comment-16807398 ] pengzhiwei edited comment on CALCITE-1581 at 4/2/19 4:15 AM: - [~julianhyde], I agree with you that we can modify the validator to work on the sugared syntax. In my previous thinking,I want to minimum the modification to support this syntax. So I rewrite the sqlNode to a already supported syntax(Lateral Table),just like the rewrite for "nulif" and other function. Since you have mentioned that rewrite would affect the error message, I will do the work in the _SqlToRelConverter_ .Thanks for you suggestion. was (Author: pzw2018): [~julianhyde], I agree with you that we can modify the validator to work on the sugared syntax. In my previous thinking,I want to minimum the modification to support this syntax. So I rewrite the sqlNode to a already supported syntax(Lateral Table),just like the rewrite for "nulif" and other function. Since you have mentioned that rewrite would affect the error message, I will do the work in the _SqlToRelConverter .Thanks for you suggestion. > UDTF like in hive > - > > Key: CALCITE-1581 > URL: https://issues.apache.org/jira/browse/CALCITE-1581 > Project: Calcite > Issue Type: New Feature >Reporter: Xiaoyong Deng >Assignee: Sihua Zhou >Priority: Major > Labels: pull-request-available, udtf > Fix For: 1.20.0 > > Time Spent: 2h 50m > Remaining Estimate: 0h > > Support one row in and multi-column/multi-row out(one-to-many mapping), just > like udtf in hive. > The query would like this: > {code} > select > func(c0, c1) as (f0, f1, f2) > from table_name; > {code} > c0 and c1 are 'table_name' columns. f0, f1 and f2 are new generated columns. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Comment Edited] (CALCITE-1581) UDTF like in hive
[ https://issues.apache.org/jira/browse/CALCITE-1581?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16807398#comment-16807398 ] pengzhiwei edited comment on CALCITE-1581 at 4/2/19 4:14 AM: - [~julianhyde], I agree with you that we can modify the validator to work on the sugared syntax. In my previous thinking,I want to minimum the modification to support this syntax. So I rewrite the sqlNode to a already supported syntax(Lateral Table),just like the rewrite for "nulif" and other function. Since you have mentioned that rewrite would affect the error message, I will do the work in the _SqlToRelConverter .Thanks for you suggestion. was (Author: pzw2018): [~julianhyde], I agree with you that we can modify the validator to work on the sugared syntax. In my previous thinking,I want to minimum the modification to support this syntax. So I rewrite the sqlNode to a already supported syntax(Lateral Table),just like the rewrite for "nulif" and other function. Since you have mentioned that rewrite would affect the error message, I will do the work in the _SqlToRelConverter ._Thanks for you suggestion. > UDTF like in hive > - > > Key: CALCITE-1581 > URL: https://issues.apache.org/jira/browse/CALCITE-1581 > Project: Calcite > Issue Type: New Feature >Reporter: Xiaoyong Deng >Assignee: Sihua Zhou >Priority: Major > Labels: pull-request-available, udtf > Fix For: 1.20.0 > > Time Spent: 2h 50m > Remaining Estimate: 0h > > Support one row in and multi-column/multi-row out(one-to-many mapping), just > like udtf in hive. > The query would like this: > {code} > select > func(c0, c1) as (f0, f1, f2) > from table_name; > {code} > c0 and c1 are 'table_name' columns. f0, f1 and f2 are new generated columns. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Comment Edited] (CALCITE-1581) UDTF like in hive
[ https://issues.apache.org/jira/browse/CALCITE-1581?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16807398#comment-16807398 ] pengzhiwei edited comment on CALCITE-1581 at 4/2/19 4:14 AM: - [~julianhyde], I agree with you that we can modify the validator to work on the sugared syntax. In my previous thinking,I want to minimum the modification to support this syntax. So I rewrite the sqlNode to a already supported syntax(Lateral Table),just like the rewrite for "nulif" and other function. Since you have mentioned that rewrite would affect the error message, I will do the work in the _SqlToRelConverter ._Thanks for you suggestion. was (Author: pzw2018): [~julianhyde], I agree with you that we can modify the validator to work on the sugared syntax. In my previous thinking,I want to minimum the modification to support this syntax. So I rewrite the sqlNode to a have supported syntax(Lateral Table),just like the rewrite for "nulif" and other function. Since you have mentioned that rewrite would affect the error message, I will do the work in the _SqlToRelConverter ._Thanks for you suggestion. > UDTF like in hive > - > > Key: CALCITE-1581 > URL: https://issues.apache.org/jira/browse/CALCITE-1581 > Project: Calcite > Issue Type: New Feature >Reporter: Xiaoyong Deng >Assignee: Sihua Zhou >Priority: Major > Labels: pull-request-available, udtf > Fix For: 1.20.0 > > Time Spent: 2h 50m > Remaining Estimate: 0h > > Support one row in and multi-column/multi-row out(one-to-many mapping), just > like udtf in hive. > The query would like this: > {code} > select > func(c0, c1) as (f0, f1, f2) > from table_name; > {code} > c0 and c1 are 'table_name' columns. f0, f1 and f2 are new generated columns. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Commented] (CALCITE-1581) UDTF like in hive
[ https://issues.apache.org/jira/browse/CALCITE-1581?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16807398#comment-16807398 ] pengzhiwei commented on CALCITE-1581: - [~julianhyde], I agree with you that we can modify the validator to work on the sugared syntax. In my previous thinking,I want to minimum the modification to support this syntax. So I rewrite the sqlNode to a have supported syntax(Lateral Table),just like the rewrite for "nulif" and other function. Since you have mentioned that rewrite would affect the error message, I will do the work in the _SqlToRelConverter ._Thanks for you suggestion. > UDTF like in hive > - > > Key: CALCITE-1581 > URL: https://issues.apache.org/jira/browse/CALCITE-1581 > Project: Calcite > Issue Type: New Feature >Reporter: Xiaoyong Deng >Assignee: Sihua Zhou >Priority: Major > Labels: pull-request-available, udtf > Fix For: 1.20.0 > > Time Spent: 2h 50m > Remaining Estimate: 0h > > Support one row in and multi-column/multi-row out(one-to-many mapping), just > like udtf in hive. > The query would like this: > {code} > select > func(c0, c1) as (f0, f1, f2) > from table_name; > {code} > c0 and c1 are 'table_name' columns. f0, f1 and f2 are new generated columns. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Comment Edited] (CALCITE-1581) UDTF like in hive
[ https://issues.apache.org/jira/browse/CALCITE-1581?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16807385#comment-16807385 ] pengzhiwei edited comment on CALCITE-1581 at 4/2/19 3:30 AM: - Hi [~julianhyde],Thanks for you review. ??That's a lot of rewrite code. It doesn't belong in SqlValidatorImpl. Maybe in SqlToRelConverter? It's usually best not to rewrite before validation - otherwise the error messages don't make sense to the user. If you really must rewrite before validation, could you use performUnconditionalRewrites? And move the code out of the SqlValidatorImpl.?? Well,I have thought of make the rewrite in SqlToRelConverter. However,It cannot pass the validation in SqlValidator.So I have to rewrite it before the validation start. Move the rewrite code to performUnconditionalRewrites seem to be a good idea.I will have a try. ??Please restore SqlToRelTestBase.tester to final?? I need to change the SqlConformance in _SqlToRelConverterTest_ to _HIVE._ So I need to make the _SqlToRelTestBase.tester_ modifiable just like the _SqlValidatorTestCase.tester._ was (Author: pzw2018): Hi [~julianhyde],Thanks for you review. ??That's a lot of rewrite code. It doesn't belong in SqlValidatorImpl. Maybe in SqlToRelConverter? It's usually best not to rewrite before validation - otherwise the error messages don't make sense to the user. If you really must rewrite before validation, could you use performUnconditionalRewrites? And move the code out of the SqlValidatorImpl.?? Well,I have thought of make the rewrite in SqlToRelConverter. However,It cannot pass the validation in SqlValidator.So I have to rewrite it before the validation start. Move the rewrite code to performUnconditionalRewrites seem to be a good idea. ??Please restore SqlToRelTestBase.tester to final?? I need to change the SqlConformance in _SqlToRelConverterTest_ to _HIVE._ So I need to make the _SqlToRelTestBase.tester_ modifiable just like the _SqlValidatorTestCase.tester._ > UDTF like in hive > - > > Key: CALCITE-1581 > URL: https://issues.apache.org/jira/browse/CALCITE-1581 > Project: Calcite > Issue Type: New Feature >Reporter: Xiaoyong Deng >Assignee: Sihua Zhou >Priority: Major > Labels: pull-request-available, udtf > Fix For: 1.20.0 > > Time Spent: 2h 50m > Remaining Estimate: 0h > > Support one row in and multi-column/multi-row out(one-to-many mapping), just > like udtf in hive. > The query would like this: > {code} > select > func(c0, c1) as (f0, f1, f2) > from table_name; > {code} > c0 and c1 are 'table_name' columns. f0, f1 and f2 are new generated columns. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Comment Edited] (CALCITE-1581) UDTF like in hive
[ https://issues.apache.org/jira/browse/CALCITE-1581?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16807385#comment-16807385 ] pengzhiwei edited comment on CALCITE-1581 at 4/2/19 3:29 AM: - Hi [~julianhyde],Thanks for you review. ??That's a lot of rewrite code. It doesn't belong in SqlValidatorImpl. Maybe in SqlToRelConverter? It's usually best not to rewrite before validation - otherwise the error messages don't make sense to the user. If you really must rewrite before validation, could you use performUnconditionalRewrites? And move the code out of the SqlValidatorImpl.?? Well,I have thought of make the rewrite in SqlToRelConverter. However,It cannot pass the validation in SqlValidator.So I have to rewrite it before the validation start. Move the rewrite code to performUnconditionalRewrites seem to be a good idea. ??Please restore SqlToRelTestBase.tester to final?? I need to change the SqlConformance in _SqlToRelConverterTest_ to _HIVE._ So I need to make the _SqlToRelTestBase.tester_ modifiable just like the _SqlValidatorTestCase.tester._ was (Author: pzw2018): Hi [~julianhyde],Thanks for you review. ??That's a lot of rewrite code. It doesn't belong in SqlValidatorImpl. Maybe in SqlToRelConverter? It's usually best not to rewrite before validation - otherwise the error messages don't make sense to the user. If you really must rewrite before validation, could you use performUnconditionalRewrites? And move the code out of the SqlValidatorImpl.?? Well,I have think of make the rewrite in SqlToRelConverter. However,It cannot pass the validation in SqlValidator.So I have to rewrite it before the validation start. Move the rewrite code to performUnconditionalRewrites seem to be a good idea. ??Please restore SqlToRelTestBase.tester to final?? I need to change the SqlConformance in _SqlToRelConverterTest_ to _HIVE._ So I need to make the _SqlToRelTestBase.tester_ modifiable just like the _SqlValidatorTestCase.tester._ > UDTF like in hive > - > > Key: CALCITE-1581 > URL: https://issues.apache.org/jira/browse/CALCITE-1581 > Project: Calcite > Issue Type: New Feature >Reporter: Xiaoyong Deng >Assignee: Sihua Zhou >Priority: Major > Labels: pull-request-available, udtf > Fix For: 1.20.0 > > Time Spent: 2h 50m > Remaining Estimate: 0h > > Support one row in and multi-column/multi-row out(one-to-many mapping), just > like udtf in hive. > The query would like this: > {code} > select > func(c0, c1) as (f0, f1, f2) > from table_name; > {code} > c0 and c1 are 'table_name' columns. f0, f1 and f2 are new generated columns. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Commented] (CALCITE-1581) UDTF like in hive
[ https://issues.apache.org/jira/browse/CALCITE-1581?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16807385#comment-16807385 ] pengzhiwei commented on CALCITE-1581: - Hi [~julianhyde],Thanks for you review. ??That's a lot of rewrite code. It doesn't belong in SqlValidatorImpl. Maybe in SqlToRelConverter? It's usually best not to rewrite before validation - otherwise the error messages don't make sense to the user. If you really must rewrite before validation, could you use performUnconditionalRewrites? And move the code out of the SqlValidatorImpl.?? Well,I have think of make the rewrite in SqlToRelConverter. However,It cannot pass the validation in SqlValidator.So I have to rewrite it before the validation start. Move the rewrite code to performUnconditionalRewrites seem to be a good idea. ??Please restore SqlToRelTestBase.tester to final?? I need to change the SqlConformance in _SqlToRelConverterTest_ to _HIVE._ So I need to make the _SqlToRelTestBase.tester_ modifiable just like the _SqlValidatorTestCase.tester._ > UDTF like in hive > - > > Key: CALCITE-1581 > URL: https://issues.apache.org/jira/browse/CALCITE-1581 > Project: Calcite > Issue Type: New Feature >Reporter: Xiaoyong Deng >Assignee: Sihua Zhou >Priority: Major > Labels: pull-request-available, udtf > Fix For: 1.20.0 > > Time Spent: 2h 50m > Remaining Estimate: 0h > > Support one row in and multi-column/multi-row out(one-to-many mapping), just > like udtf in hive. > The query would like this: > {code} > select > func(c0, c1) as (f0, f1, f2) > from table_name; > {code} > c0 and c1 are 'table_name' columns. f0, f1 and f2 are new generated columns. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Updated] (CALCITE-2921) nullif(null,y) throws exception in verification
[ https://issues.apache.org/jira/browse/CALCITE-2921?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] pengzhiwei updated CALCITE-2921: Fix Version/s: 1.20.0 > nullif(null,y) throws exception in verification > > > Key: CALCITE-2921 > URL: https://issues.apache.org/jira/browse/CALCITE-2921 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.18.0 >Reporter: pengzhiwei >Assignee: pengzhiwei >Priority: Critical > Labels: pull-request-available > Fix For: 1.20.0 > > Time Spent: 10m > Remaining Estimate: 0h > > Currently calcite will translate "nullif(x,y)" to a case-when expression,just > like "case when x = y then null else x".So when "x" is null literal,a > exception throws out as follow: > {code:java} > ELSE clause or at least one THEN clause must be non-NULL > {code} > I have test in mysql,"nullif(null,y) works well.So I think we should allow > this usage of "nullif". > There are two ways to fix this issue: > 1) Skip the check for "foundNotNull" in SqlCaseOperator#checkOperandTypes: > > {code:java} > if (!foundNotNull) { > // according to the sql standard we can not have all of the THEN > // statements and the ELSE returning null > if (throwOnFailure) { > throw callBinding.newError(RESOURCE.mustNotNullInElse()); > } > return false; > }{code} > However, as the comment says, we cannot have all of the THEN and ELSE > returning null. > 2) Disable the translation from nullif to case-when and keep "nullif" as it > is. > Any suggestion is welcomed,Thanks! -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Comment Edited] (CALCITE-2921) nullif(null,y) throws exception in verification
[ https://issues.apache.org/jira/browse/CALCITE-2921?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16804546#comment-16804546 ] pengzhiwei edited comment on CALCITE-2921 at 3/29/19 2:43 PM: -- Hi [~julianhyde], the pr has been updated, here is the mainly change list: * The "NULL" return type of SqlOperator is forbidden. As it may cause uncertain semantics.For example, "Not(null) + 1" can pass the validate, if the "Not(null)"'s return type is "NULL". I have added a default type for most operator which may return "NULL" type. * add "allowNakedNull()" in SqlConformance. * fix other issue you mentioned in last review. Please have a look again,Thanks! was (Author: pzw2018): Hi [~julianhyde], the pr has been updated, here is the mainly change list: * The "NULL" return type of SqlOperator is forbidden. As it may cause uncertain semantics.For example, "Not(null) + 1" can pass the validate, if the "Not(null)"'s return type is "NULL". I have added a default type for most operator which may return "NULL" type. * add "allowNakedNull()" in SqlConformance. * fix other issue you mentioned in last review. Please have a look again,Thanks! > nullif(null,y) throws exception in verification > > > Key: CALCITE-2921 > URL: https://issues.apache.org/jira/browse/CALCITE-2921 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.18.0 >Reporter: pengzhiwei >Assignee: pengzhiwei >Priority: Critical > Labels: pull-request-available > Time Spent: 10m > Remaining Estimate: 0h > > Currently calcite will translate "nullif(x,y)" to a case-when expression,just > like "case when x = y then null else x".So when "x" is null literal,a > exception throws out as follow: > {code:java} > ELSE clause or at least one THEN clause must be non-NULL > {code} > I have test in mysql,"nullif(null,y) works well.So I think we should allow > this usage of "nullif". > There are two ways to fix this issue: > 1) Skip the check for "foundNotNull" in SqlCaseOperator#checkOperandTypes: > > {code:java} > if (!foundNotNull) { > // according to the sql standard we can not have all of the THEN > // statements and the ELSE returning null > if (throwOnFailure) { > throw callBinding.newError(RESOURCE.mustNotNullInElse()); > } > return false; > }{code} > However, as the comment says, we cannot have all of the THEN and ELSE > returning null. > 2) Disable the translation from nullif to case-when and keep "nullif" as it > is. > Any suggestion is welcomed,Thanks! -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Comment Edited] (CALCITE-2921) nullif(null,y) throws exception in verification
[ https://issues.apache.org/jira/browse/CALCITE-2921?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16804546#comment-16804546 ] pengzhiwei edited comment on CALCITE-2921 at 3/29/19 2:42 PM: -- Hi [~julianhyde], the pr has been updated, here is the mainly change list: * The "NULL" return type of SqlOperator is forbidden. As it may cause uncertain semantics.For example, "Not(null) + 1" can pass the validate, if the "Not(null)"'s return type is "NULL". I have added a default type for most operator which may return "NULL" type. * add "allowNakedNull()" in SqlConformance. * fix other issue you mentioned in last review. Please have a look again,Thanks! was (Author: pzw2018): Hi [~julianhyde], the pr has been updated, here is the mainly change list: * The "NULL" return type of SqlOperator is forbidden. As it may cause uncertain semantics.For example, "Not(null) + 1" can pass the validate, if the "Not(null)"'s return type is "NULL". I have added a default type for most operator which may return "NULL" type. * add "allowNakedNull()" in SqlConformance. * fix other issue you mentioned in last review. Please have a look again,Thanks! > nullif(null,y) throws exception in verification > > > Key: CALCITE-2921 > URL: https://issues.apache.org/jira/browse/CALCITE-2921 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.18.0 >Reporter: pengzhiwei >Assignee: pengzhiwei >Priority: Critical > Labels: pull-request-available > Time Spent: 10m > Remaining Estimate: 0h > > Currently calcite will translate "nullif(x,y)" to a case-when expression,just > like "case when x = y then null else x".So when "x" is null literal,a > exception throws out as follow: > {code:java} > ELSE clause or at least one THEN clause must be non-NULL > {code} > I have test in mysql,"nullif(null,y) works well.So I think we should allow > this usage of "nullif". > There are two ways to fix this issue: > 1) Skip the check for "foundNotNull" in SqlCaseOperator#checkOperandTypes: > > {code:java} > if (!foundNotNull) { > // according to the sql standard we can not have all of the THEN > // statements and the ELSE returning null > if (throwOnFailure) { > throw callBinding.newError(RESOURCE.mustNotNullInElse()); > } > return false; > }{code} > However, as the comment says, we cannot have all of the THEN and ELSE > returning null. > 2) Disable the translation from nullif to case-when and keep "nullif" as it > is. > Any suggestion is welcomed,Thanks! -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Comment Edited] (CALCITE-2921) nullif(null,y) throws exception in verification
[ https://issues.apache.org/jira/browse/CALCITE-2921?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16804546#comment-16804546 ] pengzhiwei edited comment on CALCITE-2921 at 3/29/19 2:42 PM: -- Hi [~julianhyde], the pr has been updated, here is the mainly change list: * The "NULL" return type of SqlOperator is forbidden. As it may cause uncertain semantics.For example, "Not(null) + 1" can pass the validate, if the "Not(null)"'s return type is "NULL". I have added a default type for most operator which may return "NULL" type. * add "allowNakedNull()" in SqlConformance. * fix other issue you mentioned in last review. Please have a look again,Thanks! was (Author: pzw2018): Thanks [~julianhyde], I will continue the work. > nullif(null,y) throws exception in verification > > > Key: CALCITE-2921 > URL: https://issues.apache.org/jira/browse/CALCITE-2921 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.18.0 >Reporter: pengzhiwei >Assignee: pengzhiwei >Priority: Critical > Labels: pull-request-available > Time Spent: 10m > Remaining Estimate: 0h > > Currently calcite will translate "nullif(x,y)" to a case-when expression,just > like "case when x = y then null else x".So when "x" is null literal,a > exception throws out as follow: > {code:java} > ELSE clause or at least one THEN clause must be non-NULL > {code} > I have test in mysql,"nullif(null,y) works well.So I think we should allow > this usage of "nullif". > There are two ways to fix this issue: > 1) Skip the check for "foundNotNull" in SqlCaseOperator#checkOperandTypes: > > {code:java} > if (!foundNotNull) { > // according to the sql standard we can not have all of the THEN > // statements and the ELSE returning null > if (throwOnFailure) { > throw callBinding.newError(RESOURCE.mustNotNullInElse()); > } > return false; > }{code} > However, as the comment says, we cannot have all of the THEN and ELSE > returning null. > 2) Disable the translation from nullif to case-when and keep "nullif" as it > is. > Any suggestion is welcomed,Thanks! -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Comment Edited] (CALCITE-1581) UDTF like in hive
[ https://issues.apache.org/jira/browse/CALCITE-1581?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16804052#comment-16804052 ] pengzhiwei edited comment on CALCITE-1581 at 3/29/19 5:54 AM: -- Hi [~julianhyde], I have make a pr [https://github.com/apache/calcite/pull/1138] for this issue. In this pr,I mainly do the follow things: * Rewrite the "parser.jj" file to support "udtf() as (f0,f1)" in the sql parser stage with minimum modification. * add allowSelectTableFunction() in SqlConformance and also add a kind of Hive SqlConformance. * In SqlValidator stage, I rewrite the "udtf() as (f0,f1)" to the lateral-table sql-node. With this rewritting,there is nothing need to do in the SqlToRelConverter. was (Author: pzw2018): Hi [~julianhyde], I have make a pr [https://github.com/apache/calcite/pull/1138] for this issue. In this pr,I mainly do the follow things: * Rewrite the "parser.jj" file to support "udtf() as (f0,f1)" in the sql parser stage with minimum modification. * add allowSelectTableFunction() in SqlConformance and also add a kind of Hive SqlConformance. * In SqlValidator stage, I rewrite the "udtf() as (f0,f1)" to the lateral-table sql-node. With this rewritting,this is nothing need to do in the SqlToRelConverter. > UDTF like in hive > - > > Key: CALCITE-1581 > URL: https://issues.apache.org/jira/browse/CALCITE-1581 > Project: Calcite > Issue Type: New Feature >Reporter: Xiaoyong Deng >Assignee: Sihua Zhou >Priority: Major > Labels: pull-request-available, udtf > Time Spent: 2h > Remaining Estimate: 0h > > Support one row in and multi-column/multi-row out(one-to-many mapping), just > like udtf in hive. > The query would like this: > {code} > select > func(c0, c1) as (f0, f1, f2) > from table_name; > {code} > c0 and c1 are 'table_name' columns. f0, f1 and f2 are new generated columns. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Comment Edited] (CALCITE-2921) nullif(null,y) throws exception in verification
[ https://issues.apache.org/jira/browse/CALCITE-2921?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16804546#comment-16804546 ] pengzhiwei edited comment on CALCITE-2921 at 3/29/19 3:07 AM: -- Thanks [~julianhyde], I will continue the work. was (Author: pzw2018): Thanks [~julianhyde], I will continue work. > nullif(null,y) throws exception in verification > > > Key: CALCITE-2921 > URL: https://issues.apache.org/jira/browse/CALCITE-2921 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.18.0 >Reporter: pengzhiwei >Assignee: pengzhiwei >Priority: Critical > Labels: pull-request-available > Time Spent: 10m > Remaining Estimate: 0h > > Currently calcite will translate "nullif(x,y)" to a case-when expression,just > like "case when x = y then null else x".So when "x" is null literal,a > exception throws out as follow: > {code:java} > ELSE clause or at least one THEN clause must be non-NULL > {code} > I have test in mysql,"nullif(null,y) works well.So I think we should allow > this usage of "nullif". > There are two ways to fix this issue: > 1) Skip the check for "foundNotNull" in SqlCaseOperator#checkOperandTypes: > > {code:java} > if (!foundNotNull) { > // according to the sql standard we can not have all of the THEN > // statements and the ELSE returning null > if (throwOnFailure) { > throw callBinding.newError(RESOURCE.mustNotNullInElse()); > } > return false; > }{code} > However, as the comment says, we cannot have all of the THEN and ELSE > returning null. > 2) Disable the translation from nullif to case-when and keep "nullif" as it > is. > Any suggestion is welcomed,Thanks! -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Commented] (CALCITE-2921) nullif(null,y) throws exception in verification
[ https://issues.apache.org/jira/browse/CALCITE-2921?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16804546#comment-16804546 ] pengzhiwei commented on CALCITE-2921: - Thanks [~julianhyde], I will continue work. > nullif(null,y) throws exception in verification > > > Key: CALCITE-2921 > URL: https://issues.apache.org/jira/browse/CALCITE-2921 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.18.0 >Reporter: pengzhiwei >Assignee: pengzhiwei >Priority: Critical > Labels: pull-request-available > Time Spent: 10m > Remaining Estimate: 0h > > Currently calcite will translate "nullif(x,y)" to a case-when expression,just > like "case when x = y then null else x".So when "x" is null literal,a > exception throws out as follow: > {code:java} > ELSE clause or at least one THEN clause must be non-NULL > {code} > I have test in mysql,"nullif(null,y) works well.So I think we should allow > this usage of "nullif". > There are two ways to fix this issue: > 1) Skip the check for "foundNotNull" in SqlCaseOperator#checkOperandTypes: > > {code:java} > if (!foundNotNull) { > // according to the sql standard we can not have all of the THEN > // statements and the ELSE returning null > if (throwOnFailure) { > throw callBinding.newError(RESOURCE.mustNotNullInElse()); > } > return false; > }{code} > However, as the comment says, we cannot have all of the THEN and ELSE > returning null. > 2) Disable the translation from nullif to case-when and keep "nullif" as it > is. > Any suggestion is welcomed,Thanks! -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Commented] (CALCITE-1581) UDTF like in hive
[ https://issues.apache.org/jira/browse/CALCITE-1581?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16804052#comment-16804052 ] pengzhiwei commented on CALCITE-1581: - Hi [~julianhyde], I have make a pr [https://github.com/apache/calcite/pull/1138] for this issue. In this pr,I mainly do the follow things: * Rewrite the "parser.jj" file to support "udtf() as (f0,f1)" in the sql parser stage with minimum modification. * add allowSelectTableFunction() in SqlConformance and also add a kind of Hive SqlConformance. * In SqlValidator stage, I rewrite the "udtf() as (f0,f1)" to the lateral-table sql-node. With this rewritting,this is nothing need to do in the SqlToRelConverter. > UDTF like in hive > - > > Key: CALCITE-1581 > URL: https://issues.apache.org/jira/browse/CALCITE-1581 > Project: Calcite > Issue Type: New Feature >Reporter: Xiaoyong Deng >Assignee: Sihua Zhou >Priority: Major > Labels: pull-request-available, udtf > Time Spent: 10m > Remaining Estimate: 0h > > Support one row in and multi-column/multi-row out(one-to-many mapping), just > like udtf in hive. > The query would like this: > {code} > select > func(c0, c1) as (f0, f1, f2) > from table_name; > {code} > c0 and c1 are 'table_name' columns. f0, f1 and f2 are new generated columns. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Comment Edited] (CALCITE-2921) nullif(null,y) throws exception in verification
[ https://issues.apache.org/jira/browse/CALCITE-2921?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16796004#comment-16796004 ] pengzhiwei edited comment on CALCITE-2921 at 3/19/19 12:00 PM: --- Hi [~julianhyde], I try to make a pr to support null-literal in function call at [https://github.com/apache/calcite/pull/1117]. In this pr,I mapping the SqlTypeName#Null to a specific java class "Null" and allow null-literal matching any input type in the FamilyOperandTypeChecker. I works well in the test case. Can you take a look and give some suggestions ?Thanks! was (Author: pzw2018): Hi [~julianhyde], I try to make a pr to support null-literal in function call at [https://github.com/apache/calcite/pull/1117]. In this pr,I mapping the SqlTypeName#Null to a specific class "Null" and allow null-literal matching any input type in the FamilyOperandTypeChecker. I works well in the test case. Can you take a look and give some suggestions ?Thanks! > nullif(null,y) throws exception in verification > > > Key: CALCITE-2921 > URL: https://issues.apache.org/jira/browse/CALCITE-2921 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.18.0 >Reporter: pengzhiwei >Assignee: pengzhiwei >Priority: Critical > Labels: pull-request-available > Time Spent: 10m > Remaining Estimate: 0h > > Currently calcite will translate "nullif(x,y)" to a case-when expression,just > like "case when x = y then null else x".So when "x" is null literal,a > exception throws out as follow: > {code:java} > ELSE clause or at least one THEN clause must be non-NULL > {code} > I have test in mysql,"nullif(null,y) works well.So I think we should allow > this usage of "nullif". > There are two ways to fix this issue: > 1) Skip the check for "foundNotNull" in SqlCaseOperator#checkOperandTypes: > > {code:java} > if (!foundNotNull) { > // according to the sql standard we can not have all of the THEN > // statements and the ELSE returning null > if (throwOnFailure) { > throw callBinding.newError(RESOURCE.mustNotNullInElse()); > } > return false; > }{code} > However, as the comment says, we cannot have all of the THEN and ELSE > returning null. > 2) Disable the translation from nullif to case-when and keep "nullif" as it > is. > Any suggestion is welcomed,Thanks! -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Commented] (CALCITE-2921) nullif(null,y) throws exception in verification
[ https://issues.apache.org/jira/browse/CALCITE-2921?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16796004#comment-16796004 ] pengzhiwei commented on CALCITE-2921: - Hi [~julianhyde], I try to make a pr to support null-literal in function call at [https://github.com/apache/calcite/pull/1117]. In this pr,I mapping the SqlTypeName#Null to a specific class "Null" and allow null-literal matching any input type in the FamilyOperandTypeChecker. I works well in the test case. Can you take a look and give some suggestions ?Thanks! > nullif(null,y) throws exception in verification > > > Key: CALCITE-2921 > URL: https://issues.apache.org/jira/browse/CALCITE-2921 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.18.0 >Reporter: pengzhiwei >Assignee: pengzhiwei >Priority: Critical > Labels: pull-request-available > Time Spent: 10m > Remaining Estimate: 0h > > Currently calcite will translate "nullif(x,y)" to a case-when expression,just > like "case when x = y then null else x".So when "x" is null literal,a > exception throws out as follow: > {code:java} > ELSE clause or at least one THEN clause must be non-NULL > {code} > I have test in mysql,"nullif(null,y) works well.So I think we should allow > this usage of "nullif". > There are two ways to fix this issue: > 1) Skip the check for "foundNotNull" in SqlCaseOperator#checkOperandTypes: > > {code:java} > if (!foundNotNull) { > // according to the sql standard we can not have all of the THEN > // statements and the ELSE returning null > if (throwOnFailure) { > throw callBinding.newError(RESOURCE.mustNotNullInElse()); > } > return false; > }{code} > However, as the comment says, we cannot have all of the THEN and ELSE > returning null. > 2) Disable the translation from nullif to case-when and keep "nullif" as it > is. > Any suggestion is welcomed,Thanks! -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Updated] (CALCITE-2921) nullif(null,y) throws exception in verification
[ https://issues.apache.org/jira/browse/CALCITE-2921?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] pengzhiwei updated CALCITE-2921: Description: Currently calcite will translate "nullif(x,y)" to a case-when expression,just like "case when x = y then null else x".So when "x" is null literal,a exception throws out as follow: {code:java} ELSE clause or at least one THEN clause must be non-NULL {code} I have test in mysql,"nullif(null,y) works well.So I think we should allow this usage of "nullif". There are two ways to fix this issue: 1) Skip the check for "foundNotNull" in SqlCaseOperator#checkOperandTypes: {code:java} if (!foundNotNull) { // according to the sql standard we can not have all of the THEN // statements and the ELSE returning null if (throwOnFailure) { throw callBinding.newError(RESOURCE.mustNotNullInElse()); } return false; }{code} However, as the comment says, we cannot have all of the THEN and ELSE returning null. 2) Disable the translation from nullif to case-when and keep "nullif" as it is. Any suggestion is welcomed,Thanks! was: Currently calcite will translate "nullif(x,y)" to a case-when expression,just like "case when x = y then null else x".So when "x" is null literal,a exception throws out as follow: {code:java} ELSE clause or at least one THEN clause must be non-NULL {code} I have test in mysql,"nullif(null,y) works well.So I think we should allow this usage of "nullif". There are two ways to fix this issue: 1) Skip the check for "foundNotNull" in SqlCaseOperator#checkOperandTypes: {code:java} if (!foundNotNull) { // according to the sql standard we can not have all of the THEN // statements and the ELSE returning null if (throwOnFailure) { throw callBinding.newError(RESOURCE.mustNotNullInElse()); } return false; }{code} However, as the comment says, we cannot have all of the THEN and ELSE returning null. 2) Add a new SqlOperator like "NullifOperator" and disable the translation from nullif to case-when. Any suggestion is welcomed,Thanks! > nullif(null,y) throws exception in verification > > > Key: CALCITE-2921 > URL: https://issues.apache.org/jira/browse/CALCITE-2921 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.18.0 >Reporter: pengzhiwei >Assignee: pengzhiwei >Priority: Critical > > Currently calcite will translate "nullif(x,y)" to a case-when expression,just > like "case when x = y then null else x".So when "x" is null literal,a > exception throws out as follow: > {code:java} > ELSE clause or at least one THEN clause must be non-NULL > {code} > I have test in mysql,"nullif(null,y) works well.So I think we should allow > this usage of "nullif". > There are two ways to fix this issue: > 1) Skip the check for "foundNotNull" in SqlCaseOperator#checkOperandTypes: > > {code:java} > if (!foundNotNull) { > // according to the sql standard we can not have all of the THEN > // statements and the ELSE returning null > if (throwOnFailure) { > throw callBinding.newError(RESOURCE.mustNotNullInElse()); > } > return false; > }{code} > However, as the comment says, we cannot have all of the THEN and ELSE > returning null. > 2) Disable the translation from nullif to case-when and keep "nullif" as it > is. > Any suggestion is welcomed,Thanks! -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Created] (CALCITE-2921) nullif(null,y) throws exception in verification
pengzhiwei created CALCITE-2921: --- Summary: nullif(null,y) throws exception in verification Key: CALCITE-2921 URL: https://issues.apache.org/jira/browse/CALCITE-2921 Project: Calcite Issue Type: Bug Components: core Affects Versions: 1.18.0 Reporter: pengzhiwei Assignee: pengzhiwei Currently calcite will translate "nullif(x,y)" to a case-when expression,just like "case when x = y then null else x".So when "x" is null literal,a exception throws out as follow: {code:java} ELSE clause or at least one THEN clause must be non-NULL {code} I have test in mysql,"nullif(null,y) works well.So I think we should allow this usage of "nullif". There are two ways to fix this issue: 1) Skip the check for "foundNotNull" in SqlCaseOperator#checkOperandTypes: {code:java} if (!foundNotNull) { // according to the sql standard we can not have all of the THEN // statements and the ELSE returning null if (throwOnFailure) { throw callBinding.newError(RESOURCE.mustNotNullInElse()); } return false; }{code} However, as the comment says, we cannot have all of the THEN and ELSE returning null. 2) Add a new SqlOperator like "NullifOperator" and disable the translation from nullif to case-when. Any suggestion is welcomed,Thanks! -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Commented] (CALCITE-2792) Stackoverflow while evaluating filter with large number of OR conditions
[ https://issues.apache.org/jira/browse/CALCITE-2792?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16754576#comment-16754576 ] pengzhiwei commented on CALCITE-2792: - Well,this translation is common for the "OR" expression which looks like the "x = c1 or x= c2 or x=c3",not only for the "IN" expression.I think we can start from the set of values is in a scalar "OR" expression. > Stackoverflow while evaluating filter with large number of OR conditions > > > Key: CALCITE-2792 > URL: https://issues.apache.org/jira/browse/CALCITE-2792 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.18.0 >Reporter: Dirk Mahler >Assignee: Julian Hyde >Priority: Major > Attachments: calcite-stackoverflow.zip > > > As a workaround for CALCITE-2696 we're currently using OR conditions for > filtering values, e.g. instead of > {noformat} > ... WHERE value2 IN (1,2,3) > {noformat} > {noformat} > ... WHERE value2=1 OR value2=2 OR value2=3 > {noformat} > We're now hitting a StackOverflowError because the number of values in the > filter grows quite large (i.e. 1000-3000) and obviously the evaluation > recursive: > {noformat} > java.lang.StackOverflowError > at java.util.AbstractCollection.toArray(AbstractCollection.java:176) > at > org.apache.calcite.sql.util.SqlShuttle$CallCopyingArgHandler.(SqlShuttle.java:111) > at > org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visitScoped(SqlValidatorImpl.java:5699) > at > org.apache.calcite.sql.validate.SqlScopedShuttle.visit(SqlScopedShuttle.java:50) > at > org.apache.calcite.sql.validate.SqlScopedShuttle.visit(SqlScopedShuttle.java:33) > at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:138) > at > org.apache.calcite.sql.util.SqlShuttle$CallCopyingArgHandler.visitChild(SqlShuttle.java:134) > at > org.apache.calcite.sql.util.SqlShuttle$CallCopyingArgHandler.visitChild(SqlShuttle.java:101) > at org.apache.calcite.sql.SqlOperator.acceptCall(SqlOperator.java:865) > at > org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visitScoped(SqlValidatorImpl.java:5701) > at > org.apache.calcite.sql.validate.SqlScopedShuttle.visit(SqlScopedShuttle.java:50) > at > org.apache.calcite.sql.validate.SqlScopedShuttle.visit(SqlScopedShuttle.java:33) > at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:138) > at > org.apache.calcite.sql.util.SqlShuttle$CallCopyingArgHandler.visitChild(SqlShuttle.java:134) > at > org.apache.calcite.sql.util.SqlShuttle$CallCopyingArgHandler.visitChild(SqlShuttle.java:101) > at org.apache.calcite.sql.SqlOperator.acceptCall(SqlOperator.java:865) > at > org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visitScoped(SqlValidatorImpl.java:5701) > at > org.apache.calcite.sql.validate.SqlScopedShuttle.visit(SqlScopedShuttle.java:50) > at > org.apache.calcite.sql.validate.SqlScopedShuttle.visit(SqlScopedShuttle.java:33) > at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:138) > ... > {noformat} > We tried to increase the stack size of the virtual machine (-Xss8000k) but > this leads to a problem with Janino: > {noformat} > java.sql.SQLException: exception while executing query: Compiling "Buzz": > Code of method > "execute(Lorg/apache/calcite/interpreter/Context;[Ljava/lang/Object;)V" of > class "Buzz" grows beyond 64 KB > at org.apache.calcite.avatica.Helper.createException(Helper.java:56) > at org.apache.calcite.avatica.Helper.createException(Helper.java:41) > at > org.apache.calcite.avatica.AvaticaConnection.executeQueryInternal(AvaticaConnection.java:577) > at > org.apache.calcite.avatica.AvaticaPreparedStatement.executeQuery(AvaticaPreparedStatement.java:137) > at > org.apache.calcite.issue.StackOverflowTest.stackOverflow(StackOverflowTest.java:45) > 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.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50) > at > org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12) > at > org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47) > at > org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17) > at > org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26) > at >
[jira] [Commented] (CALCITE-2792) Stackoverflow while evaluating filter with large number of OR conditions
[ https://issues.apache.org/jira/browse/CALCITE-2792?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16753737#comment-16753737 ] pengzhiwei commented on CALCITE-2792: - {quote}Let's treat that as a different issue, because it's in a different part of the code (code generation rather than SQL validation). The "inline table" approach might help there; it might allow us to generate a HashMap that we could probe into. {quote} Hi [~julianhyde] , Can we translate the "x = c1 or x = c2 or x = c3" to "x in set(c1,c2,c3)" in the code generation stage? It does not need to add another representation for RexCall. And can solve the problem of performance. > Stackoverflow while evaluating filter with large number of OR conditions > > > Key: CALCITE-2792 > URL: https://issues.apache.org/jira/browse/CALCITE-2792 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.18.0 >Reporter: Dirk Mahler >Assignee: Julian Hyde >Priority: Major > Attachments: calcite-stackoverflow.zip > > > As a workaround for CALCITE-2696 we're currently using OR conditions for > filtering values, e.g. instead of > {noformat} > ... WHERE value2 IN (1,2,3) > {noformat} > {noformat} > ... WHERE value2=1 OR value2=2 OR value2=3 > {noformat} > We're now hitting a StackOverflowError because the number of values in the > filter grows quite large (i.e. 1000-3000) and obviously the evaluation > recursive: > {noformat} > java.lang.StackOverflowError > at java.util.AbstractCollection.toArray(AbstractCollection.java:176) > at > org.apache.calcite.sql.util.SqlShuttle$CallCopyingArgHandler.(SqlShuttle.java:111) > at > org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visitScoped(SqlValidatorImpl.java:5699) > at > org.apache.calcite.sql.validate.SqlScopedShuttle.visit(SqlScopedShuttle.java:50) > at > org.apache.calcite.sql.validate.SqlScopedShuttle.visit(SqlScopedShuttle.java:33) > at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:138) > at > org.apache.calcite.sql.util.SqlShuttle$CallCopyingArgHandler.visitChild(SqlShuttle.java:134) > at > org.apache.calcite.sql.util.SqlShuttle$CallCopyingArgHandler.visitChild(SqlShuttle.java:101) > at org.apache.calcite.sql.SqlOperator.acceptCall(SqlOperator.java:865) > at > org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visitScoped(SqlValidatorImpl.java:5701) > at > org.apache.calcite.sql.validate.SqlScopedShuttle.visit(SqlScopedShuttle.java:50) > at > org.apache.calcite.sql.validate.SqlScopedShuttle.visit(SqlScopedShuttle.java:33) > at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:138) > at > org.apache.calcite.sql.util.SqlShuttle$CallCopyingArgHandler.visitChild(SqlShuttle.java:134) > at > org.apache.calcite.sql.util.SqlShuttle$CallCopyingArgHandler.visitChild(SqlShuttle.java:101) > at org.apache.calcite.sql.SqlOperator.acceptCall(SqlOperator.java:865) > at > org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visitScoped(SqlValidatorImpl.java:5701) > at > org.apache.calcite.sql.validate.SqlScopedShuttle.visit(SqlScopedShuttle.java:50) > at > org.apache.calcite.sql.validate.SqlScopedShuttle.visit(SqlScopedShuttle.java:33) > at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:138) > ... > {noformat} > We tried to increase the stack size of the virtual machine (-Xss8000k) but > this leads to a problem with Janino: > {noformat} > java.sql.SQLException: exception while executing query: Compiling "Buzz": > Code of method > "execute(Lorg/apache/calcite/interpreter/Context;[Ljava/lang/Object;)V" of > class "Buzz" grows beyond 64 KB > at org.apache.calcite.avatica.Helper.createException(Helper.java:56) > at org.apache.calcite.avatica.Helper.createException(Helper.java:41) > at > org.apache.calcite.avatica.AvaticaConnection.executeQueryInternal(AvaticaConnection.java:577) > at > org.apache.calcite.avatica.AvaticaPreparedStatement.executeQuery(AvaticaPreparedStatement.java:137) > at > org.apache.calcite.issue.StackOverflowTest.stackOverflow(StackOverflowTest.java:45) > 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.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50) > at > org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12) > at >
[jira] [Comment Edited] (CALCITE-2792) Stackoverflow while evaluating filter with large number of OR conditions
[ https://issues.apache.org/jira/browse/CALCITE-2792?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16750750#comment-16750750 ] pengzhiwei edited comment on CALCITE-2792 at 1/24/19 6:17 AM: -- Hi [~julianhyde],the StackOverflowError comes from the recursive call in the SqlValidatorImpl#registerSubQueries,even if the OR RexCall has more than 2 arguments. Here is the code for this: {code:java} private void registerOperandSubQueries( SqlValidatorScope parentScope, SqlCall call, int operandOrdinal) { .. else if (node instanceof SqlCall) { validateNodeFeature(node); SqlCall call = (SqlCall) node; for (int i = 0; i < call.operandCount(); i++) { registerOperandSubQueries(parentScope, call, i); } ... } {code} Except the StackOverflowError, the code generated for "In" may exceed the limit of 64KB for java method when the IN constants list is to large as the case shown in this issue. was (Author: pzw2018): Hi [~julianhyde],the StackOverflowError comes from the recursive call in the SqlValidatorImpl#registerSubQueries,even if the OR RexCall has more than 2 arguments. Here is the code for this: {code:java} private void registerOperandSubQueries( SqlValidatorScope parentScope, SqlCall call, int operandOrdinal) { .. else if (node instanceof SqlCall) { validateNodeFeature(node); SqlCall call = (SqlCall) node; for (int i = 0; i < call.operandCount(); i++) { registerOperandSubQueries(parentScope, call, i); } ... } {code} > Stackoverflow while evaluating filter with large number of OR conditions > > > Key: CALCITE-2792 > URL: https://issues.apache.org/jira/browse/CALCITE-2792 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.18.0 >Reporter: Dirk Mahler >Assignee: Julian Hyde >Priority: Major > Attachments: calcite-stackoverflow.zip > > > As a workaround for CALCITE-2696 we're currently using OR conditions for > filtering values, e.g. instead of > {noformat} > ... WHERE value2 IN (1,2,3) > {noformat} > {noformat} > ... WHERE value2=1 OR value2=2 OR value2=3 > {noformat} > We're now hitting a StackOverflowError because the number of values in the > filter grows quite large (i.e. 1000-3000) and obviously the evaluation > recursive: > {noformat} > java.lang.StackOverflowError > at java.util.AbstractCollection.toArray(AbstractCollection.java:176) > at > org.apache.calcite.sql.util.SqlShuttle$CallCopyingArgHandler.(SqlShuttle.java:111) > at > org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visitScoped(SqlValidatorImpl.java:5699) > at > org.apache.calcite.sql.validate.SqlScopedShuttle.visit(SqlScopedShuttle.java:50) > at > org.apache.calcite.sql.validate.SqlScopedShuttle.visit(SqlScopedShuttle.java:33) > at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:138) > at > org.apache.calcite.sql.util.SqlShuttle$CallCopyingArgHandler.visitChild(SqlShuttle.java:134) > at > org.apache.calcite.sql.util.SqlShuttle$CallCopyingArgHandler.visitChild(SqlShuttle.java:101) > at org.apache.calcite.sql.SqlOperator.acceptCall(SqlOperator.java:865) > at > org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visitScoped(SqlValidatorImpl.java:5701) > at > org.apache.calcite.sql.validate.SqlScopedShuttle.visit(SqlScopedShuttle.java:50) > at > org.apache.calcite.sql.validate.SqlScopedShuttle.visit(SqlScopedShuttle.java:33) > at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:138) > at > org.apache.calcite.sql.util.SqlShuttle$CallCopyingArgHandler.visitChild(SqlShuttle.java:134) > at > org.apache.calcite.sql.util.SqlShuttle$CallCopyingArgHandler.visitChild(SqlShuttle.java:101) > at org.apache.calcite.sql.SqlOperator.acceptCall(SqlOperator.java:865) > at > org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visitScoped(SqlValidatorImpl.java:5701) > at > org.apache.calcite.sql.validate.SqlScopedShuttle.visit(SqlScopedShuttle.java:50) > at > org.apache.calcite.sql.validate.SqlScopedShuttle.visit(SqlScopedShuttle.java:33) > at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:138) > ... > {noformat} > We tried to increase the stack size of the virtual machine (-Xss8000k) but > this leads to a problem with Janino: > {noformat} > java.sql.SQLException: exception while executing query: Compiling "Buzz": > Code of method > "execute(Lorg/apache/calcite/interpreter/Context;[Ljava/lang/Object;)V" of > class "Buzz" grows beyond 64 KB > at org.apache.calcite.avatica.Helper.createException(Helper.java:56) > at org.apache.calcite.avatica.Helper.createException(Helper.java:41) > at >
[jira] [Commented] (CALCITE-2792) Stackoverflow while evaluating filter with large number of OR conditions
[ https://issues.apache.org/jira/browse/CALCITE-2792?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16750750#comment-16750750 ] pengzhiwei commented on CALCITE-2792: - Hi [~julianhyde],the StackOverflowError comes from the recursive call in the SqlValidatorImpl#registerSubQueries,even if the OR RexCall has more than 2 arguments. Here is the code for this: {code:java} private void registerOperandSubQueries( SqlValidatorScope parentScope, SqlCall call, int operandOrdinal) { .. else if (node instanceof SqlCall) { validateNodeFeature(node); SqlCall call = (SqlCall) node; for (int i = 0; i < call.operandCount(); i++) { registerOperandSubQueries(parentScope, call, i); } ... } {code} > Stackoverflow while evaluating filter with large number of OR conditions > > > Key: CALCITE-2792 > URL: https://issues.apache.org/jira/browse/CALCITE-2792 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.18.0 >Reporter: Dirk Mahler >Assignee: Julian Hyde >Priority: Major > Attachments: calcite-stackoverflow.zip > > > As a workaround for CALCITE-2696 we're currently using OR conditions for > filtering values, e.g. instead of > {noformat} > ... WHERE value2 IN (1,2,3) > {noformat} > {noformat} > ... WHERE value2=1 OR value2=2 OR value2=3 > {noformat} > We're now hitting a StackOverflowError because the number of values in the > filter grows quite large (i.e. 1000-3000) and obviously the evaluation > recursive: > {noformat} > java.lang.StackOverflowError > at java.util.AbstractCollection.toArray(AbstractCollection.java:176) > at > org.apache.calcite.sql.util.SqlShuttle$CallCopyingArgHandler.(SqlShuttle.java:111) > at > org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visitScoped(SqlValidatorImpl.java:5699) > at > org.apache.calcite.sql.validate.SqlScopedShuttle.visit(SqlScopedShuttle.java:50) > at > org.apache.calcite.sql.validate.SqlScopedShuttle.visit(SqlScopedShuttle.java:33) > at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:138) > at > org.apache.calcite.sql.util.SqlShuttle$CallCopyingArgHandler.visitChild(SqlShuttle.java:134) > at > org.apache.calcite.sql.util.SqlShuttle$CallCopyingArgHandler.visitChild(SqlShuttle.java:101) > at org.apache.calcite.sql.SqlOperator.acceptCall(SqlOperator.java:865) > at > org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visitScoped(SqlValidatorImpl.java:5701) > at > org.apache.calcite.sql.validate.SqlScopedShuttle.visit(SqlScopedShuttle.java:50) > at > org.apache.calcite.sql.validate.SqlScopedShuttle.visit(SqlScopedShuttle.java:33) > at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:138) > at > org.apache.calcite.sql.util.SqlShuttle$CallCopyingArgHandler.visitChild(SqlShuttle.java:134) > at > org.apache.calcite.sql.util.SqlShuttle$CallCopyingArgHandler.visitChild(SqlShuttle.java:101) > at org.apache.calcite.sql.SqlOperator.acceptCall(SqlOperator.java:865) > at > org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visitScoped(SqlValidatorImpl.java:5701) > at > org.apache.calcite.sql.validate.SqlScopedShuttle.visit(SqlScopedShuttle.java:50) > at > org.apache.calcite.sql.validate.SqlScopedShuttle.visit(SqlScopedShuttle.java:33) > at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:138) > ... > {noformat} > We tried to increase the stack size of the virtual machine (-Xss8000k) but > this leads to a problem with Janino: > {noformat} > java.sql.SQLException: exception while executing query: Compiling "Buzz": > Code of method > "execute(Lorg/apache/calcite/interpreter/Context;[Ljava/lang/Object;)V" of > class "Buzz" grows beyond 64 KB > at org.apache.calcite.avatica.Helper.createException(Helper.java:56) > at org.apache.calcite.avatica.Helper.createException(Helper.java:41) > at > org.apache.calcite.avatica.AvaticaConnection.executeQueryInternal(AvaticaConnection.java:577) > at > org.apache.calcite.avatica.AvaticaPreparedStatement.executeQuery(AvaticaPreparedStatement.java:137) > at > org.apache.calcite.issue.StackOverflowTest.stackOverflow(StackOverflowTest.java:45) > 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.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50) > at > org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12) >
[jira] [Commented] (CALCITE-2788) Building error for sub-project of calcite on "maven-checkstyle-plugin"
[ https://issues.apache.org/jira/browse/CALCITE-2788?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16749481#comment-16749481 ] pengzhiwei commented on CALCITE-2788: - Hi [~risdenk],the "top.dir" has redefined in every sub-project referring to the parent project path. So when we build in a sub-project,there is no problem to find the check style config file. > Building error for sub-project of calcite on "maven-checkstyle-plugin" > -- > > Key: CALCITE-2788 > URL: https://issues.apache.org/jira/browse/CALCITE-2788 > Project: Calcite > Issue Type: Bug > Components: babel, core, server >Affects Versions: 1.18.0 >Reporter: pengzhiwei >Assignee: Julian Hyde >Priority: Major > Fix For: 1.19.0 > > > When building a sub-project of calcite e.g. core,exception throws out as > follow: > {code:java} > Failed to execute goal > org.apache.maven.plugins:maven-checkstyle-plugin:3.0.0:check (validate) on > project calcite-core: Failed during checkstyle configuration: cannot > initialize module Header - illegal value > 'src/main/config/checkstyle/header.java.txt' for property 'headerFile' of > module Header: com.puppycrawl.tools.checkstyle.api.CheckstyleException: > Unable to find: src/main/config/checkstyle/header.java.txt -> [Help 1] > {code} > The reason for this exception is that the checker.xml use a location related > to the parent project for the header file,however the header file is not > located in the sub-project. > -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Assigned] (CALCITE-2788) Building error for sub-project of calcite on "maven-checkstyle-plugin"
[ https://issues.apache.org/jira/browse/CALCITE-2788?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] pengzhiwei reassigned CALCITE-2788: --- Assignee: pengzhiwei (was: Julian Hyde) > Building error for sub-project of calcite on "maven-checkstyle-plugin" > -- > > Key: CALCITE-2788 > URL: https://issues.apache.org/jira/browse/CALCITE-2788 > Project: Calcite > Issue Type: Bug > Components: babel, core, server >Affects Versions: 1.18.0 >Reporter: pengzhiwei >Assignee: pengzhiwei >Priority: Major > Fix For: 1.19.0 > > > When building a sub-project of calcite e.g. core,exception throws out as > follow: > {code:java} > Failed to execute goal > org.apache.maven.plugins:maven-checkstyle-plugin:3.0.0:check (validate) on > project calcite-core: Failed during checkstyle configuration: cannot > initialize module Header - illegal value > 'src/main/config/checkstyle/header.java.txt' for property 'headerFile' of > module Header: com.puppycrawl.tools.checkstyle.api.CheckstyleException: > Unable to find: src/main/config/checkstyle/header.java.txt -> [Help 1] > {code} > The reason for this exception is that the checker.xml use a location related > to the parent project for the header file,however the header file is not > located in the sub-project. > -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Commented] (CALCITE-2788) Building error for sub-project of calcite on "maven-checkstyle-plugin"
[ https://issues.apache.org/jira/browse/CALCITE-2788?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16749477#comment-16749477 ] pengzhiwei commented on CALCITE-2788: - Hi [~michaelmior],this patch only solves the issue that failing to find the check-style config file for sub-projects. The issue you mentioned that failing to find dependency for other sub-projects is not included in this patch.I think other dependent sub-projects should mvn install or deploy once before building the sub-project. > Building error for sub-project of calcite on "maven-checkstyle-plugin" > -- > > Key: CALCITE-2788 > URL: https://issues.apache.org/jira/browse/CALCITE-2788 > Project: Calcite > Issue Type: Bug > Components: babel, core, server >Affects Versions: 1.18.0 >Reporter: pengzhiwei >Assignee: Julian Hyde >Priority: Major > Fix For: 1.19.0 > > > When building a sub-project of calcite e.g. core,exception throws out as > follow: > {code:java} > Failed to execute goal > org.apache.maven.plugins:maven-checkstyle-plugin:3.0.0:check (validate) on > project calcite-core: Failed during checkstyle configuration: cannot > initialize module Header - illegal value > 'src/main/config/checkstyle/header.java.txt' for property 'headerFile' of > module Header: com.puppycrawl.tools.checkstyle.api.CheckstyleException: > Unable to find: src/main/config/checkstyle/header.java.txt -> [Help 1] > {code} > The reason for this exception is that the checker.xml use a location related > to the parent project for the header file,however the header file is not > located in the sub-project. > -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Comment Edited] (CALCITE-2788) Building error for sub-project of calcite on "maven-checkstyle-plugin"
[ https://issues.apache.org/jira/browse/CALCITE-2788?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16747619#comment-16747619 ] pengzhiwei edited comment on CALCITE-2788 at 1/21/19 3:57 AM: -- HI [~risdenk] I run the commond "mvn clean install " in the "core" or "server" module and exception throws out. I think there is a error in the config of the checkstyle file. was (Author: pzw2018): HI [~risdenk] I run the commond "mvn clean install " in the "core" or "server" module.And I find there is a error in the config of the checkstyle file. > Building error for sub-project of calcite on "maven-checkstyle-plugin" > -- > > Key: CALCITE-2788 > URL: https://issues.apache.org/jira/browse/CALCITE-2788 > Project: Calcite > Issue Type: Bug > Components: babel, core, server >Affects Versions: 1.18.0 >Reporter: pengzhiwei >Assignee: Julian Hyde >Priority: Major > Fix For: 1.19.0 > > > When building a sub-project of calcite e.g. core,exception throws out as > follow: > {code:java} > Failed to execute goal > org.apache.maven.plugins:maven-checkstyle-plugin:3.0.0:check (validate) on > project calcite-core: Failed during checkstyle configuration: cannot > initialize module Header - illegal value > 'src/main/config/checkstyle/header.java.txt' for property 'headerFile' of > module Header: com.puppycrawl.tools.checkstyle.api.CheckstyleException: > Unable to find: src/main/config/checkstyle/header.java.txt -> [Help 1] > {code} > The reason for this exception is that the checker.xml use a location related > to the parent project for the header file,however the header file is not > located in the sub-project. > -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Updated] (CALCITE-2788) Building error for sub-project of calcite on "maven-checkstyle-plugin"
[ https://issues.apache.org/jira/browse/CALCITE-2788?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] pengzhiwei updated CALCITE-2788: Summary: Building error for sub-project of calcite on "maven-checkstyle-plugin" (was: Building error for sub-project of calcite on maven-checkstyle-plugin) > Building error for sub-project of calcite on "maven-checkstyle-plugin" > -- > > Key: CALCITE-2788 > URL: https://issues.apache.org/jira/browse/CALCITE-2788 > Project: Calcite > Issue Type: Bug > Components: babel, core, server >Affects Versions: 1.18.0 >Reporter: pengzhiwei >Assignee: Julian Hyde >Priority: Major > Fix For: 1.19.0 > > > When building a sub-project of calcite e.g. core,exception throws out as > follow: > {code:java} > Failed to execute goal > org.apache.maven.plugins:maven-checkstyle-plugin:3.0.0:check (validate) on > project calcite-core: Failed during checkstyle configuration: cannot > initialize module Header - illegal value > 'src/main/config/checkstyle/header.java.txt' for property 'headerFile' of > module Header: com.puppycrawl.tools.checkstyle.api.CheckstyleException: > Unable to find: src/main/config/checkstyle/header.java.txt -> [Help 1] > {code} > The reason for this exception is that the checker.xml use a location related > to the parent project for the header file,however the header file is not > located in the sub-project. > -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Created] (CALCITE-2788) Building error for sub-project of calcite on maven-checkstyle-plugin
pengzhiwei created CALCITE-2788: --- Summary: Building error for sub-project of calcite on maven-checkstyle-plugin Key: CALCITE-2788 URL: https://issues.apache.org/jira/browse/CALCITE-2788 Project: Calcite Issue Type: Bug Components: babel, core, server Affects Versions: 1.18.0 Reporter: pengzhiwei Assignee: Julian Hyde Fix For: 1.19.0 When building a sub-project of calcite e.g. core,exception throws out as follow: {code:java} Failed to execute goal org.apache.maven.plugins:maven-checkstyle-plugin:3.0.0:check (validate) on project calcite-core: Failed during checkstyle configuration: cannot initialize module Header - illegal value 'src/main/config/checkstyle/header.java.txt' for property 'headerFile' of module Header: com.puppycrawl.tools.checkstyle.api.CheckstyleException: Unable to find: src/main/config/checkstyle/header.java.txt -> [Help 1] {code} The reason for this exception is that the checker.xml use a location related to the parent project for the header file,however the header file is not located in the sub-project. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Updated] (CALCITE-2772) Support varargs for user-defined functions (UDFs)
[ https://issues.apache.org/jira/browse/CALCITE-2772?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] pengzhiwei updated CALCITE-2772: Description: Support varargs for user-defined functions as the case followed: {code:java} public class ConcatWs { public String eval(String sep, String... strs) {...} }{code} was: Currently,calcite does not support variable parameter for UDF as the case shown below: {code:java} public class ConcatWs { public String eval(String sep, String... strs) {...} }{code} However,it much common for user who writes udf. > Support varargs for user-defined functions (UDFs) > - > > Key: CALCITE-2772 > URL: https://issues.apache.org/jira/browse/CALCITE-2772 > Project: Calcite > Issue Type: Improvement > Components: core >Reporter: pengzhiwei >Assignee: pengzhiwei >Priority: Major > > Support varargs for user-defined functions as the case followed: > {code:java} > public class ConcatWs { > public String eval(String sep, String... strs) {...} > }{code} > -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Created] (CALCITE-2772) Support variable parameter for udf
pengzhiwei created CALCITE-2772: --- Summary: Support variable parameter for udf Key: CALCITE-2772 URL: https://issues.apache.org/jira/browse/CALCITE-2772 Project: Calcite Issue Type: Improvement Components: core Reporter: pengzhiwei Assignee: pengzhiwei Currently,calcite does not support variable parameter for UDF as the case shown below: {code:java} public class ConcatWs { public String eval(String sep, String... strs) {...} }{code} However,it much common for user who writes udf. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Issue Comment Deleted] (CALCITE-2448) AggregateProjectPullUpConstantsRule throws AssertionError
[ https://issues.apache.org/jira/browse/CALCITE-2448?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] pengzhiwei updated CALCITE-2448: Comment: was deleted (was: Hi @[~atris] I have made a pr for this issue,can have a check for me,thanks! [https://github.com/apache/calcite/pull/789]) > AggregateProjectPullUpConstantsRule throws AssertionError > - > > Key: CALCITE-2448 > URL: https://issues.apache.org/jira/browse/CALCITE-2448 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.17.0 >Reporter: pengzhiwei >Assignee: pengzhiwei >Priority: Major > Attachments: 屏幕快照 2018-08-06 下午9.09.15.png, 屏幕快照 2018-08-06 > 下午9.13.56.png, 屏幕快照 2018-08-06 下午9.19.48.png, 屏幕快照 2018-08-06 下午9.19.48.png > > > In the sql as followed: > {code:java} > select 'a',-emp.deptno from emp where 'a' is null group by 'a',-emp.deptno > {code} > The AggregateProjectPullUpConstantsRule throws an Error like this: > {code:java} > java.lang.AssertionError: Cannot add expression of different type to set: > set type is RecordType(CHAR(1) CHARACTER SET "ISO-8859-1" COLLATE > "ISO-8859-1$en_US$primary" NOT NULL EXPR$0, INTEGER NOT NULL EXPR$1) NOT NULL > expression type is RecordType(CHAR(1) CHARACTER SET "ISO-8859-1" COLLATE > "ISO-8859-1$en_US$primary" NOT NULL EXPR$0, CHAR(1) CHARACTER SET > "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL EXPR$1) NOT NULL > set is rel#9:LogicalAggregate(input=HepRelVertex#8,group={0, 1}) > expression is LogicalProject#11 > at > org.apache.calcite.plan.RelOptUtil.verifyTypeEquivalence(RelOptUtil.java:380) > at org.apache.calcite.plan.hep.HepRuleCall.transformTo(HepRuleCall.java:57) > at org.apache.calcite.plan.RelOptRuleCall.transformTo(RelOptRuleCall.java:234) > at > org.apache.calcite.rel.rules.AggregateProjectPullUpConstantsRule.onMatch(AggregateProjectPullUpConstantsRule.java:194) > at > org.apache.calcite.plan.AbstractRelOptPlanner.fireRule(AbstractRelOptPlanner.java:315) > {code} > The reason is that the relBuilder#aggregate failed to generate new aggregate > node when the filer condition is always false,just return as followed in > RelBuilder: > !屏幕快照 2018-08-06 下午9.09.15.png! > And then the project created later after relBuilder#aggregate is not really > based on the aggregate node in AggregateProjectPullUpConstantsRule which > result in the error. > !屏幕快照 2018-08-06 下午9.13.56.png! > I fix the problem by add a logic if the relBuilder#aggregate has not > generated a aggregate node then just return this rule in > AggregateProjectPullUpConstantsRule ,just like this: > !屏幕快照 2018-08-06 下午9.19.48.png! > Can you have check for me,thanks! > -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Issue Comment Deleted] (CALCITE-2448) AggregateProjectPullUpConstantsRule throws AssertionError
[ https://issues.apache.org/jira/browse/CALCITE-2448?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] pengzhiwei updated CALCITE-2448: Comment: was deleted (was: Hi @[~Sergey Nuyanzin],In the command line mode,the AggregateProjectPullUpConstantsRule may have not fired.I have added a test in RelOptRulesTest,the problem really exists! ) > AggregateProjectPullUpConstantsRule throws AssertionError > - > > Key: CALCITE-2448 > URL: https://issues.apache.org/jira/browse/CALCITE-2448 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.17.0 >Reporter: pengzhiwei >Assignee: pengzhiwei >Priority: Major > Attachments: 屏幕快照 2018-08-06 下午9.09.15.png, 屏幕快照 2018-08-06 > 下午9.13.56.png, 屏幕快照 2018-08-06 下午9.19.48.png, 屏幕快照 2018-08-06 下午9.19.48.png > > > In the sql as followed: > {code:java} > select 'a',-emp.deptno from emp where 'a' is null group by 'a',-emp.deptno > {code} > The AggregateProjectPullUpConstantsRule throws an Error like this: > {code:java} > java.lang.AssertionError: Cannot add expression of different type to set: > set type is RecordType(CHAR(1) CHARACTER SET "ISO-8859-1" COLLATE > "ISO-8859-1$en_US$primary" NOT NULL EXPR$0, INTEGER NOT NULL EXPR$1) NOT NULL > expression type is RecordType(CHAR(1) CHARACTER SET "ISO-8859-1" COLLATE > "ISO-8859-1$en_US$primary" NOT NULL EXPR$0, CHAR(1) CHARACTER SET > "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL EXPR$1) NOT NULL > set is rel#9:LogicalAggregate(input=HepRelVertex#8,group={0, 1}) > expression is LogicalProject#11 > at > org.apache.calcite.plan.RelOptUtil.verifyTypeEquivalence(RelOptUtil.java:380) > at org.apache.calcite.plan.hep.HepRuleCall.transformTo(HepRuleCall.java:57) > at org.apache.calcite.plan.RelOptRuleCall.transformTo(RelOptRuleCall.java:234) > at > org.apache.calcite.rel.rules.AggregateProjectPullUpConstantsRule.onMatch(AggregateProjectPullUpConstantsRule.java:194) > at > org.apache.calcite.plan.AbstractRelOptPlanner.fireRule(AbstractRelOptPlanner.java:315) > {code} > The reason is that the relBuilder#aggregate failed to generate new aggregate > node when the filer condition is always false,just return as followed in > RelBuilder: > !屏幕快照 2018-08-06 下午9.09.15.png! > And then the project created later after relBuilder#aggregate is not really > based on the aggregate node in AggregateProjectPullUpConstantsRule which > result in the error. > !屏幕快照 2018-08-06 下午9.13.56.png! > I fix the problem by add a logic if the relBuilder#aggregate has not > generated a aggregate node then just return this rule in > AggregateProjectPullUpConstantsRule ,just like this: > !屏幕快照 2018-08-06 下午9.19.48.png! > Can you have check for me,thanks! > -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Assigned] (CALCITE-2448) AggregateProjectPullUpConstantsRule throws AssertionError
[ https://issues.apache.org/jira/browse/CALCITE-2448?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] pengzhiwei reassigned CALCITE-2448: --- Assignee: pengzhiwei (was: Julian Hyde) > AggregateProjectPullUpConstantsRule throws AssertionError > - > > Key: CALCITE-2448 > URL: https://issues.apache.org/jira/browse/CALCITE-2448 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.17.0 >Reporter: pengzhiwei >Assignee: pengzhiwei >Priority: Major > Attachments: 屏幕快照 2018-08-06 下午9.09.15.png, 屏幕快照 2018-08-06 > 下午9.13.56.png, 屏幕快照 2018-08-06 下午9.19.48.png, 屏幕快照 2018-08-06 下午9.19.48.png > > > In the sql as followed: > {code:java} > select 'a',-emp.deptno from emp where 'a' is null group by 'a',-emp.deptno > {code} > The AggregateProjectPullUpConstantsRule throws an Error like this: > {code:java} > java.lang.AssertionError: Cannot add expression of different type to set: > set type is RecordType(CHAR(1) CHARACTER SET "ISO-8859-1" COLLATE > "ISO-8859-1$en_US$primary" NOT NULL EXPR$0, INTEGER NOT NULL EXPR$1) NOT NULL > expression type is RecordType(CHAR(1) CHARACTER SET "ISO-8859-1" COLLATE > "ISO-8859-1$en_US$primary" NOT NULL EXPR$0, CHAR(1) CHARACTER SET > "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL EXPR$1) NOT NULL > set is rel#9:LogicalAggregate(input=HepRelVertex#8,group={0, 1}) > expression is LogicalProject#11 > at > org.apache.calcite.plan.RelOptUtil.verifyTypeEquivalence(RelOptUtil.java:380) > at org.apache.calcite.plan.hep.HepRuleCall.transformTo(HepRuleCall.java:57) > at org.apache.calcite.plan.RelOptRuleCall.transformTo(RelOptRuleCall.java:234) > at > org.apache.calcite.rel.rules.AggregateProjectPullUpConstantsRule.onMatch(AggregateProjectPullUpConstantsRule.java:194) > at > org.apache.calcite.plan.AbstractRelOptPlanner.fireRule(AbstractRelOptPlanner.java:315) > {code} > The reason is that the relBuilder#aggregate failed to generate new aggregate > node when the filer condition is always false,just return as followed in > RelBuilder: > !屏幕快照 2018-08-06 下午9.09.15.png! > And then the project created later after relBuilder#aggregate is not really > based on the aggregate node in AggregateProjectPullUpConstantsRule which > result in the error. > !屏幕快照 2018-08-06 下午9.13.56.png! > I fix the problem by add a logic if the relBuilder#aggregate has not > generated a aggregate node then just return this rule in > AggregateProjectPullUpConstantsRule ,just like this: > !屏幕快照 2018-08-06 下午9.19.48.png! > Can you have check for me,thanks! > -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Commented] (CALCITE-2696) Filter containing IN clause not passed to Enumerable.scan
[ https://issues.apache.org/jira/browse/CALCITE-2696?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16698911#comment-16698911 ] pengzhiwei commented on CALCITE-2696: - I think we should provide a config for user to keep "In" as it is. The problem in this issue will be solved and also we can have a better performance than set IN_SUB_QUERY_THRESHOLD to "Integer.MAX_VALUE". > Filter containing IN clause not passed to Enumerable.scan > - > > Key: CALCITE-2696 > URL: https://issues.apache.org/jira/browse/CALCITE-2696 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.17.0 >Reporter: Dirk Mahler >Assignee: Julian Hyde >Priority: Major > Attachments: calcite-in-clause.zip > > > I'm using the Calcite JDBC driver with an own SchemaFactory (defined by a > model property) that provides a schema containing a > ProjectableFilterableTable: > {code:java} > String model = "inline:" // > + "{" // > + " version: '1.0', " // > + " defaultSchema: 'test'," // > + " schemas: [" // > + " {" // > + " name: 'test'," // > + " type: 'custom'," // > + " factory: '" + TestSchemaFactory.class.getName() + "'" // > + " }" > + " ]" // > + "}"; > Properties properties = new Properties(); > properties.put(CalciteConnectionProperty.MODEL.camelName(), model); > connection = DriverManager.getConnection("jdbc:calcite:", properties); > {code} > > > {code:java} > class TestTable extends AbstractQueryableTable implements > ProjectableFilterableTable { > public Enumerable scan(DataContext root, List filters, > int[] projects) { > ... > } > ... > }{code} > > It maps to a Java class and provides two Integer typed columns "value1" and > "value2". > The following query leads to a quite expensive behavior in the scan method if > the following statement is executed: > > {code:java} > SELECT "value" FROM "TEST_TABLE" WHERE "value1" = 1 AND "value2" in > (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20) > {code} > The scan method is invoked with a filter that only covers the part "value1" = > 1, the IN clause is completely omitted. The result on the JDBC side is still > valid but in my case this still leads to a full scan of a large underlying > data set (millions of rows). > Interestingly the filter part reflecting the IN operator is provided if the > number of elements in the list is below 20. It seems that this is controlled > by > org.apache.calcite.sql2rel.SqlToRelConverter.Config#getInSubQueryThreshold. > It would at be very helpful if this behavior could be confgiured on the JDBC > property level. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Comment Edited] (CALCITE-2630) Convert SqlInOperator to In-Expression
[ https://issues.apache.org/jira/browse/CALCITE-2630?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16656440#comment-16656440 ] pengzhiwei edited comment on CALCITE-2630 at 10/19/18 8:33 AM: --- _you can have your optimized physical IN operator in your generated code_ [~julianhyde] "In Operator" has been translated to "OR" or "Join" after logical plan,If people want to build a physical IN operator in there own engine ,they may need to rebuild IN operator from the "OR" or "join" logical plan.It need lots of work. So why we not keep "IN" as it is and leave the space for engine to implement there own physical IN. Such are my thoughts for this question associated with my work on our sql-engine based on calcite. was (Author: pzw2018): _you can have your optimized physical IN operator in your generated code_ [~julianhyde] "In Operator" has been translated to "OR" or "Join" after logical plan,If people want to build a physical IN operator in there own engine ,they may need to rebuild IN operator from the "OR" or "join" logical plan.It need lot of work. So why we not keep "IN" as it is and leave the space for engine to implement there own physical IN. Such are my thoughts for this question associated with my work on our sql-engine based on calcite. > Convert SqlInOperator to In-Expression > -- > > Key: CALCITE-2630 > URL: https://issues.apache.org/jira/browse/CALCITE-2630 > Project: Calcite > Issue Type: Improvement > Components: core >Affects Versions: 1.17.0 >Reporter: pengzhiwei >Assignee: pengzhiwei >Priority: Major > > Currently Calcite translate "IN" to "OR" expression when the count of IN's > operands less than "inSubQueryThreshold" or to "Join" when the operands > count greater than "inSubQueryThreshold" to get better performance. > However this translation to "JOIN" is so complex. Especially when the "IN" > expression located in the "select" or "join on condition". > For example: > {code:java} > select case when deptno in (1,2) then 0 else 1 end from emp > {code} > the logical plan generated as follow: > {code:java} > LogicalProject(EXPR$0=[CASE(CAST(CASE(=($9, 0), false, IS NOT NULL($13), > true, IS NULL($11), null, <($10, $9), null, false)):BOOLEAN NOT NULL, 0, 1)]) > LogicalJoin(condition=[=($11, $12)], joinType=[left]) > LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], > SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], $f0=[$9], $f1=[$10], > DEPTNO0=[$7]) > LogicalJoin(condition=[true], joinType=[inner]) > LogicalTableScan(table=[[CATALOG, SALES, EMP]]) > LogicalAggregate(group=[{}], agg#0=[COUNT()], agg#1=[COUNT($0)]) > LogicalProject(ROW_VALUE=[$0], $f1=[true]) > LogicalValues(tuples=[[{ 1 }, { 2 }]]) > LogicalAggregate(group=[{0}], agg#0=[MIN($1)]) > LogicalProject(ROW_VALUE=[$0], $f1=[true]) > LogicalValues(tuples=[[{ 1 }, { 2 }]]) > {code} > The generated logical plan is so complex for such a simple sql! > I think we can treat "IN" as a function like "plus" and "minus".So there is > no translation on "IN" and just keep it as it is.This would be much clear in > the logical plan! > In the execute stage,We can provide a "InExpression": > {code:java} > InExpression(left,condition0,condition1,...) {code} > We can put all the constant conditions to a "Set".In that way,the > computational complexity can reduce from O(n)to O(1). > It would be much clear and have a good performance. > PS: "In sub-query" is not included in our talk. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Comment Edited] (CALCITE-2630) Convert SqlInOperator to In-Expression
[ https://issues.apache.org/jira/browse/CALCITE-2630?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16656440#comment-16656440 ] pengzhiwei edited comment on CALCITE-2630 at 10/19/18 8:30 AM: --- _you can have your optimized physical IN operator in your generated code_ [~julianhyde] "In Operator" has been translated to "OR" or "Join" after logical plan,If people want to build a physical IN operator in there own engine ,they may need to rebuild IN operator from the "OR" or "join" logical plan.It need lot of work. So why we not keep "IN" as it is and leave the space for engine to implement there own physical IN. Such are my thoughts for this question associated with my work on our sql-engine based on calcite. was (Author: pzw2018): _you can have your optimized physical IN operator in your generated code_ [~julianhyde] "In Operator" has been translated to "OR" or "Join" after logical plan,If people want to build a physical IN operator in there own engine ,they may need to rebuild IN operator from the "OR" or "join" logical plan.It need lot of work. So why we not keep "IN" as it is and leave the space for engine to implement there own physical IN. > Convert SqlInOperator to In-Expression > -- > > Key: CALCITE-2630 > URL: https://issues.apache.org/jira/browse/CALCITE-2630 > Project: Calcite > Issue Type: Improvement > Components: core >Affects Versions: 1.17.0 >Reporter: pengzhiwei >Assignee: pengzhiwei >Priority: Major > > Currently Calcite translate "IN" to "OR" expression when the count of IN's > operands less than "inSubQueryThreshold" or to "Join" when the operands > count greater than "inSubQueryThreshold" to get better performance. > However this translation to "JOIN" is so complex. Especially when the "IN" > expression located in the "select" or "join on condition". > For example: > {code:java} > select case when deptno in (1,2) then 0 else 1 end from emp > {code} > the logical plan generated as follow: > {code:java} > LogicalProject(EXPR$0=[CASE(CAST(CASE(=($9, 0), false, IS NOT NULL($13), > true, IS NULL($11), null, <($10, $9), null, false)):BOOLEAN NOT NULL, 0, 1)]) > LogicalJoin(condition=[=($11, $12)], joinType=[left]) > LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], > SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], $f0=[$9], $f1=[$10], > DEPTNO0=[$7]) > LogicalJoin(condition=[true], joinType=[inner]) > LogicalTableScan(table=[[CATALOG, SALES, EMP]]) > LogicalAggregate(group=[{}], agg#0=[COUNT()], agg#1=[COUNT($0)]) > LogicalProject(ROW_VALUE=[$0], $f1=[true]) > LogicalValues(tuples=[[{ 1 }, { 2 }]]) > LogicalAggregate(group=[{0}], agg#0=[MIN($1)]) > LogicalProject(ROW_VALUE=[$0], $f1=[true]) > LogicalValues(tuples=[[{ 1 }, { 2 }]]) > {code} > The generated logical plan is so complex for such a simple sql! > I think we can treat "IN" as a function like "plus" and "minus".So there is > no translation on "IN" and just keep it as it is.This would be much clear in > the logical plan! > In the execute stage,We can provide a "InExpression": > {code:java} > InExpression(left,condition0,condition1,...) {code} > We can put all the constant conditions to a "Set".In that way,the > computational complexity can reduce from O(n)to O(1). > It would be much clear and have a good performance. > PS: "In sub-query" is not included in our talk. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Commented] (CALCITE-2630) Convert SqlInOperator to In-Expression
[ https://issues.apache.org/jira/browse/CALCITE-2630?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16656440#comment-16656440 ] pengzhiwei commented on CALCITE-2630: - _you can have your optimized physical IN operator in your generated code_ [~julianhyde] "In Operator" has been translated to "OR" or "Join" after logical plan,If people want to build a physical IN operator in there own engine ,they may need to rebuild IN operator from the "OR" or "join" logical plan.It need lot of work. So why we not keep "IN" as it is and leave the space for engine to implement there own physical IN. > Convert SqlInOperator to In-Expression > -- > > Key: CALCITE-2630 > URL: https://issues.apache.org/jira/browse/CALCITE-2630 > Project: Calcite > Issue Type: Improvement > Components: core >Affects Versions: 1.17.0 >Reporter: pengzhiwei >Assignee: pengzhiwei >Priority: Major > > Currently Calcite translate "IN" to "OR" expression when the count of IN's > operands less than "inSubQueryThreshold" or to "Join" when the operands > count greater than "inSubQueryThreshold" to get better performance. > However this translation to "JOIN" is so complex. Especially when the "IN" > expression located in the "select" or "join on condition". > For example: > {code:java} > select case when deptno in (1,2) then 0 else 1 end from emp > {code} > the logical plan generated as follow: > {code:java} > LogicalProject(EXPR$0=[CASE(CAST(CASE(=($9, 0), false, IS NOT NULL($13), > true, IS NULL($11), null, <($10, $9), null, false)):BOOLEAN NOT NULL, 0, 1)]) > LogicalJoin(condition=[=($11, $12)], joinType=[left]) > LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], > SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], $f0=[$9], $f1=[$10], > DEPTNO0=[$7]) > LogicalJoin(condition=[true], joinType=[inner]) > LogicalTableScan(table=[[CATALOG, SALES, EMP]]) > LogicalAggregate(group=[{}], agg#0=[COUNT()], agg#1=[COUNT($0)]) > LogicalProject(ROW_VALUE=[$0], $f1=[true]) > LogicalValues(tuples=[[{ 1 }, { 2 }]]) > LogicalAggregate(group=[{0}], agg#0=[MIN($1)]) > LogicalProject(ROW_VALUE=[$0], $f1=[true]) > LogicalValues(tuples=[[{ 1 }, { 2 }]]) > {code} > The generated logical plan is so complex for such a simple sql! > I think we can treat "IN" as a function like "plus" and "minus".So there is > no translation on "IN" and just keep it as it is.This would be much clear in > the logical plan! > In the execute stage,We can provide a "InExpression": > {code:java} > InExpression(left,condition0,condition1,...) {code} > We can put all the constant conditions to a "Set".In that way,the > computational complexity can reduce from O(n)to O(1). > It would be much clear and have a good performance. > PS: "In sub-query" is not included in our talk. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Comment Edited] (CALCITE-2630) Convert SqlInOperator to In-Expression
[ https://issues.apache.org/jira/browse/CALCITE-2630?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16656240#comment-16656240 ] pengzhiwei edited comment on CALCITE-2630 at 10/19/18 8:01 AM: --- Hi [~julianhyde],We are talking about keeping "In (constant1,constant2,...)" as it is but not translate it to a very complex "join" logical plan as I have described in the description.We need not add another Rex Operator just like "plus","minus" and so on.It's just a RexCall with a SqlInOperator. In the runtime of calcite,we can provide a InExpression to compute the value of "in". We can put all the constants into a "Set" to reduce the computational complexity . I think it is much clear and good performance than translate "IN" to a complex "Join". was (Author: pzw2018): Hi [~julianhyde],We are talking about keeping "In (constant1,constant2,...)" as it is but not translate it to a very complex "join" logical plan as I have described in the description.We need not add another Rex Operator just like "plus","minus" and so on.It just a RexCall with a SqlInOperator. In the runtime of calcite,we can provide a InExpression to compute the value of "in". We can put all the constants into a "Set" to reduce the computational complexity . I think it is much clear and good performance than translate "IN" to a complex "Join". > Convert SqlInOperator to In-Expression > -- > > Key: CALCITE-2630 > URL: https://issues.apache.org/jira/browse/CALCITE-2630 > Project: Calcite > Issue Type: Improvement > Components: core >Affects Versions: 1.17.0 >Reporter: pengzhiwei >Assignee: pengzhiwei >Priority: Major > > Currently Calcite translate "IN" to "OR" expression when the count of IN's > operands less than "inSubQueryThreshold" or to "Join" when the operands > count greater than "inSubQueryThreshold" to get better performance. > However this translation to "JOIN" is so complex. Especially when the "IN" > expression located in the "select" or "join on condition". > For example: > {code:java} > select case when deptno in (1,2) then 0 else 1 end from emp > {code} > the logical plan generated as follow: > {code:java} > LogicalProject(EXPR$0=[CASE(CAST(CASE(=($9, 0), false, IS NOT NULL($13), > true, IS NULL($11), null, <($10, $9), null, false)):BOOLEAN NOT NULL, 0, 1)]) > LogicalJoin(condition=[=($11, $12)], joinType=[left]) > LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], > SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], $f0=[$9], $f1=[$10], > DEPTNO0=[$7]) > LogicalJoin(condition=[true], joinType=[inner]) > LogicalTableScan(table=[[CATALOG, SALES, EMP]]) > LogicalAggregate(group=[{}], agg#0=[COUNT()], agg#1=[COUNT($0)]) > LogicalProject(ROW_VALUE=[$0], $f1=[true]) > LogicalValues(tuples=[[{ 1 }, { 2 }]]) > LogicalAggregate(group=[{0}], agg#0=[MIN($1)]) > LogicalProject(ROW_VALUE=[$0], $f1=[true]) > LogicalValues(tuples=[[{ 1 }, { 2 }]]) > {code} > The generated logical plan is so complex for such a simple sql! > I think we can treat "IN" as a function like "plus" and "minus".So there is > no translation on "IN" and just keep it as it is.This would be much clear in > the logical plan! > In the execute stage,We can provide a "InExpression": > {code:java} > InExpression(left,condition0,condition1,...) {code} > We can put all the constant conditions to a "Set".In that way,the > computational complexity can reduce from O(n)to O(1). > It would be much clear and have a good performance. > PS: "In sub-query" is not included in our talk. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Comment Edited] (CALCITE-2630) Convert SqlInOperator to In-Expression
[ https://issues.apache.org/jira/browse/CALCITE-2630?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16656410#comment-16656410 ] pengzhiwei edited comment on CALCITE-2630 at 10/19/18 7:57 AM: --- _By adjusting the subQuertThreshold you can avoid the translation to a join and have always the OR expansion which does not seem much more complicated than the equivalent IN._ [~zabetak] The problem I meet in our engine is that when I adjust the _subQuertThreshold_ to a large value .e.g. 1000,the execution time by OR expression is intolerable. And I think that keeping SqlInOperator in RexCall should not affect other existing simplify rules.We can also add some new simplify rule for IN. For example,we can convert the or expression "x=constant1 or x= constant2..." to "x in (constant1,constant2...)" to reduce the computational complexity for O(n) to O(1). was (Author: pzw2018): _By adjusting the subQuertThreshold you can avoid the translation to a join and have always the OR expansion which does not seem much more complicated than the equivalent IN._ [~zabetak] The problem I meet in our engine is that when I adjust the _subQuertThreshold_ to a large value .e.g. 1000,the execution time by OR expression is intolerable. And I think the keep SqlInOperator in RexCall should not affect other existing simplify rules.We can also add some new simplify rule for IN. For example,we can convert the or expression "x=constant1 or x= constant2..." to "x in (constant1,constant2...)" to reduce the computational complexity for O(n) to O(1). > Convert SqlInOperator to In-Expression > -- > > Key: CALCITE-2630 > URL: https://issues.apache.org/jira/browse/CALCITE-2630 > Project: Calcite > Issue Type: Improvement > Components: core >Affects Versions: 1.17.0 >Reporter: pengzhiwei >Assignee: pengzhiwei >Priority: Major > > Currently Calcite translate "IN" to "OR" expression when the count of IN's > operands less than "inSubQueryThreshold" or to "Join" when the operands > count greater than "inSubQueryThreshold" to get better performance. > However this translation to "JOIN" is so complex. Especially when the "IN" > expression located in the "select" or "join on condition". > For example: > {code:java} > select case when deptno in (1,2) then 0 else 1 end from emp > {code} > the logical plan generated as follow: > {code:java} > LogicalProject(EXPR$0=[CASE(CAST(CASE(=($9, 0), false, IS NOT NULL($13), > true, IS NULL($11), null, <($10, $9), null, false)):BOOLEAN NOT NULL, 0, 1)]) > LogicalJoin(condition=[=($11, $12)], joinType=[left]) > LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], > SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], $f0=[$9], $f1=[$10], > DEPTNO0=[$7]) > LogicalJoin(condition=[true], joinType=[inner]) > LogicalTableScan(table=[[CATALOG, SALES, EMP]]) > LogicalAggregate(group=[{}], agg#0=[COUNT()], agg#1=[COUNT($0)]) > LogicalProject(ROW_VALUE=[$0], $f1=[true]) > LogicalValues(tuples=[[{ 1 }, { 2 }]]) > LogicalAggregate(group=[{0}], agg#0=[MIN($1)]) > LogicalProject(ROW_VALUE=[$0], $f1=[true]) > LogicalValues(tuples=[[{ 1 }, { 2 }]]) > {code} > The generated logical plan is so complex for such a simple sql! > I think we can treat "IN" as a function like "plus" and "minus".So there is > no translation on "IN" and just keep it as it is.This would be much clear in > the logical plan! > In the execute stage,We can provide a "InExpression": > {code:java} > InExpression(left,condition0,condition1,...) {code} > We can put all the constant conditions to a "Set".In that way,the > computational complexity can reduce from O(n)to O(1). > It would be much clear and have a good performance. > PS: "In sub-query" is not included in our talk. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Comment Edited] (CALCITE-2630) Convert SqlInOperator to In-Expression
[ https://issues.apache.org/jira/browse/CALCITE-2630?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16656410#comment-16656410 ] pengzhiwei edited comment on CALCITE-2630 at 10/19/18 7:56 AM: --- _By adjusting the subQuertThreshold you can avoid the translation to a join and have always the OR expansion which does not seem much more complicated than the equivalent IN._ [~zabetak] The problem I meet in our engine is that when I adjust the _subQuertThreshold_ to a large value .e.g. 1000,the execution time by OR expression is intolerable. And I think the keep SqlInOperator in RexCall should not affect other existing simplify rules.We can also add some new simplify rule for IN. For example,we can convert the or expression "x=constant1 or x= constant2..." to "x in (constant1,constant2...)" to reduce the computational complexity for O(n) to O(1). was (Author: pzw2018): _By adjusting the subQuertThreshold you can avoid the translation to a join and have always the OR expansion which does not seem much more complicated than the equivalent IN._ [~zabetak] The problem I meet in our engine is that when I adjust the _subQuertThreshold_ to a large value .e.g. 1000,the execution time by OR expression is intolerable. > Convert SqlInOperator to In-Expression > -- > > Key: CALCITE-2630 > URL: https://issues.apache.org/jira/browse/CALCITE-2630 > Project: Calcite > Issue Type: Improvement > Components: core >Affects Versions: 1.17.0 >Reporter: pengzhiwei >Assignee: pengzhiwei >Priority: Major > > Currently Calcite translate "IN" to "OR" expression when the count of IN's > operands less than "inSubQueryThreshold" or to "Join" when the operands > count greater than "inSubQueryThreshold" to get better performance. > However this translation to "JOIN" is so complex. Especially when the "IN" > expression located in the "select" or "join on condition". > For example: > {code:java} > select case when deptno in (1,2) then 0 else 1 end from emp > {code} > the logical plan generated as follow: > {code:java} > LogicalProject(EXPR$0=[CASE(CAST(CASE(=($9, 0), false, IS NOT NULL($13), > true, IS NULL($11), null, <($10, $9), null, false)):BOOLEAN NOT NULL, 0, 1)]) > LogicalJoin(condition=[=($11, $12)], joinType=[left]) > LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], > SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], $f0=[$9], $f1=[$10], > DEPTNO0=[$7]) > LogicalJoin(condition=[true], joinType=[inner]) > LogicalTableScan(table=[[CATALOG, SALES, EMP]]) > LogicalAggregate(group=[{}], agg#0=[COUNT()], agg#1=[COUNT($0)]) > LogicalProject(ROW_VALUE=[$0], $f1=[true]) > LogicalValues(tuples=[[{ 1 }, { 2 }]]) > LogicalAggregate(group=[{0}], agg#0=[MIN($1)]) > LogicalProject(ROW_VALUE=[$0], $f1=[true]) > LogicalValues(tuples=[[{ 1 }, { 2 }]]) > {code} > The generated logical plan is so complex for such a simple sql! > I think we can treat "IN" as a function like "plus" and "minus".So there is > no translation on "IN" and just keep it as it is.This would be much clear in > the logical plan! > In the execute stage,We can provide a "InExpression": > {code:java} > InExpression(left,condition0,condition1,...) {code} > We can put all the constant conditions to a "Set".In that way,the > computational complexity can reduce from O(n)to O(1). > It would be much clear and have a good performance. > PS: "In sub-query" is not included in our talk. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Commented] (CALCITE-2630) Convert SqlInOperator to In-Expression
[ https://issues.apache.org/jira/browse/CALCITE-2630?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16656410#comment-16656410 ] pengzhiwei commented on CALCITE-2630: - _By adjusting the subQuertThreshold you can avoid the translation to a join and have always the OR expansion which does not seem much more complicated than the equivalent IN._ [~zabetak] The problem I meet in our engine is that when I adjust the _subQuertThreshold_ to a large value .e.g. 1000,the execution time by OR expression is intolerable. > Convert SqlInOperator to In-Expression > -- > > Key: CALCITE-2630 > URL: https://issues.apache.org/jira/browse/CALCITE-2630 > Project: Calcite > Issue Type: Improvement > Components: core >Affects Versions: 1.17.0 >Reporter: pengzhiwei >Assignee: pengzhiwei >Priority: Major > > Currently Calcite translate "IN" to "OR" expression when the count of IN's > operands less than "inSubQueryThreshold" or to "Join" when the operands > count greater than "inSubQueryThreshold" to get better performance. > However this translation to "JOIN" is so complex. Especially when the "IN" > expression located in the "select" or "join on condition". > For example: > {code:java} > select case when deptno in (1,2) then 0 else 1 end from emp > {code} > the logical plan generated as follow: > {code:java} > LogicalProject(EXPR$0=[CASE(CAST(CASE(=($9, 0), false, IS NOT NULL($13), > true, IS NULL($11), null, <($10, $9), null, false)):BOOLEAN NOT NULL, 0, 1)]) > LogicalJoin(condition=[=($11, $12)], joinType=[left]) > LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], > SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], $f0=[$9], $f1=[$10], > DEPTNO0=[$7]) > LogicalJoin(condition=[true], joinType=[inner]) > LogicalTableScan(table=[[CATALOG, SALES, EMP]]) > LogicalAggregate(group=[{}], agg#0=[COUNT()], agg#1=[COUNT($0)]) > LogicalProject(ROW_VALUE=[$0], $f1=[true]) > LogicalValues(tuples=[[{ 1 }, { 2 }]]) > LogicalAggregate(group=[{0}], agg#0=[MIN($1)]) > LogicalProject(ROW_VALUE=[$0], $f1=[true]) > LogicalValues(tuples=[[{ 1 }, { 2 }]]) > {code} > The generated logical plan is so complex for such a simple sql! > I think we can treat "IN" as a function like "plus" and "minus".So there is > no translation on "IN" and just keep it as it is.This would be much clear in > the logical plan! > In the execute stage,We can provide a "InExpression": > {code:java} > InExpression(left,condition0,condition1,...) {code} > We can put all the constant conditions to a "Set".In that way,the > computational complexity can reduce from O(n)to O(1). > It would be much clear and have a good performance. > PS: "In sub-query" is not included in our talk. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Assigned] (CALCITE-2630) Convert SqlInOperator to In-Expression
[ https://issues.apache.org/jira/browse/CALCITE-2630?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] pengzhiwei reassigned CALCITE-2630: --- Assignee: pengzhiwei (was: Julian Hyde) > Convert SqlInOperator to In-Expression > -- > > Key: CALCITE-2630 > URL: https://issues.apache.org/jira/browse/CALCITE-2630 > Project: Calcite > Issue Type: Improvement > Components: core >Affects Versions: 1.17.0 >Reporter: pengzhiwei >Assignee: pengzhiwei >Priority: Major > > Currently Calcite translate "IN" to "OR" expression when the count of IN's > operands less than "inSubQueryThreshold" or to "Join" when the operands > count greater than "inSubQueryThreshold" to get better performance. > However this translation to "JOIN" is so complex. Especially when the "IN" > expression located in the "select" or "join on condition". > For example: > {code:java} > select case when deptno in (1,2) then 0 else 1 end from emp > {code} > the logical plan generated as follow: > {code:java} > LogicalProject(EXPR$0=[CASE(CAST(CASE(=($9, 0), false, IS NOT NULL($13), > true, IS NULL($11), null, <($10, $9), null, false)):BOOLEAN NOT NULL, 0, 1)]) > LogicalJoin(condition=[=($11, $12)], joinType=[left]) > LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], > SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], $f0=[$9], $f1=[$10], > DEPTNO0=[$7]) > LogicalJoin(condition=[true], joinType=[inner]) > LogicalTableScan(table=[[CATALOG, SALES, EMP]]) > LogicalAggregate(group=[{}], agg#0=[COUNT()], agg#1=[COUNT($0)]) > LogicalProject(ROW_VALUE=[$0], $f1=[true]) > LogicalValues(tuples=[[{ 1 }, { 2 }]]) > LogicalAggregate(group=[{0}], agg#0=[MIN($1)]) > LogicalProject(ROW_VALUE=[$0], $f1=[true]) > LogicalValues(tuples=[[{ 1 }, { 2 }]]) > {code} > The generated logical plan is so complex for such a simple sql! > I think we can treat "IN" as a function like "plus" and "minus".So there is > no translation on "IN" and just keep it as it is.This would be much clear in > the logical plan! > In the execute stage,We can provide a "InExpression": > {code:java} > InExpression(left,condition0,condition1,...) {code} > We can put all the constant conditions to a "Set".In that way,the > computational complexity can reduce from O(n)to O(1). > It would be much clear and have a good performance. > PS: "In sub-query" is not included in our talk. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Comment Edited] (CALCITE-2630) Convert SqlInOperator to In-Expression
[ https://issues.apache.org/jira/browse/CALCITE-2630?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16656256#comment-16656256 ] pengzhiwei edited comment on CALCITE-2630 at 10/19/18 6:51 AM: --- [~zabetak] You are right that it may break the 3rd party rules. Maybe we can add a config to enable it and disable it by default just like the "inSubQueryThreshold" does. was (Author: pzw2018): [~zabetak] You are right that it may break the 3rd party rules. Maybe we can add a config to enable it and disable by default just like the "inSubQueryThreshold" does. > Convert SqlInOperator to In-Expression > -- > > Key: CALCITE-2630 > URL: https://issues.apache.org/jira/browse/CALCITE-2630 > Project: Calcite > Issue Type: Improvement > Components: core >Affects Versions: 1.17.0 >Reporter: pengzhiwei >Assignee: Julian Hyde >Priority: Major > > Currently Calcite translate "IN" to "OR" expression when the count of IN's > operands less than "inSubQueryThreshold" or to "Join" when the operands > count greater than "inSubQueryThreshold" to get better performance. > However this translation to "JOIN" is so complex. Especially when the "IN" > expression located in the "select" or "join on condition". > For example: > {code:java} > select case when deptno in (1,2) then 0 else 1 end from emp > {code} > the logical plan generated as follow: > {code:java} > LogicalProject(EXPR$0=[CASE(CAST(CASE(=($9, 0), false, IS NOT NULL($13), > true, IS NULL($11), null, <($10, $9), null, false)):BOOLEAN NOT NULL, 0, 1)]) > LogicalJoin(condition=[=($11, $12)], joinType=[left]) > LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], > SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], $f0=[$9], $f1=[$10], > DEPTNO0=[$7]) > LogicalJoin(condition=[true], joinType=[inner]) > LogicalTableScan(table=[[CATALOG, SALES, EMP]]) > LogicalAggregate(group=[{}], agg#0=[COUNT()], agg#1=[COUNT($0)]) > LogicalProject(ROW_VALUE=[$0], $f1=[true]) > LogicalValues(tuples=[[{ 1 }, { 2 }]]) > LogicalAggregate(group=[{0}], agg#0=[MIN($1)]) > LogicalProject(ROW_VALUE=[$0], $f1=[true]) > LogicalValues(tuples=[[{ 1 }, { 2 }]]) > {code} > The generated logical plan is so complex for such a simple sql! > I think we can treat "IN" as a function like "plus" and "minus".So there is > no translation on "IN" and just keep it as it is.This would be much clear in > the logical plan! > In the execute stage,We can provide a "InExpression": > {code:java} > InExpression(left,condition0,condition1,...) {code} > We can put all the constant conditions to a "Set".In that way,the > computational complexity can reduce from O(n)to O(1). > It would be much clear and have a good performance. > PS: "In sub-query" is not included in our talk. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Comment Edited] (CALCITE-2630) Convert SqlInOperator to In-Expression
[ https://issues.apache.org/jira/browse/CALCITE-2630?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16656256#comment-16656256 ] pengzhiwei edited comment on CALCITE-2630 at 10/19/18 6:50 AM: --- [~zabetak] You are right that it may break the 3rd party rules. Maybe we add a config to enable it and disable by default just like the "inSubQueryThreshold" does. was (Author: pzw2018): [~zabetak] You are right that it may break the 3rd party rules. Should we add a config to enable it and disable by default just like the "inSubQueryThreshold" does ? > Convert SqlInOperator to In-Expression > -- > > Key: CALCITE-2630 > URL: https://issues.apache.org/jira/browse/CALCITE-2630 > Project: Calcite > Issue Type: Improvement > Components: core >Affects Versions: 1.17.0 >Reporter: pengzhiwei >Assignee: Julian Hyde >Priority: Major > > Currently Calcite translate "IN" to "OR" expression when the count of IN's > operands less than "inSubQueryThreshold" or to "Join" when the operands > count greater than "inSubQueryThreshold" to get better performance. > However this translation to "JOIN" is so complex. Especially when the "IN" > expression located in the "select" or "join on condition". > For example: > {code:java} > select case when deptno in (1,2) then 0 else 1 end from emp > {code} > the logical plan generated as follow: > {code:java} > LogicalProject(EXPR$0=[CASE(CAST(CASE(=($9, 0), false, IS NOT NULL($13), > true, IS NULL($11), null, <($10, $9), null, false)):BOOLEAN NOT NULL, 0, 1)]) > LogicalJoin(condition=[=($11, $12)], joinType=[left]) > LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], > SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], $f0=[$9], $f1=[$10], > DEPTNO0=[$7]) > LogicalJoin(condition=[true], joinType=[inner]) > LogicalTableScan(table=[[CATALOG, SALES, EMP]]) > LogicalAggregate(group=[{}], agg#0=[COUNT()], agg#1=[COUNT($0)]) > LogicalProject(ROW_VALUE=[$0], $f1=[true]) > LogicalValues(tuples=[[{ 1 }, { 2 }]]) > LogicalAggregate(group=[{0}], agg#0=[MIN($1)]) > LogicalProject(ROW_VALUE=[$0], $f1=[true]) > LogicalValues(tuples=[[{ 1 }, { 2 }]]) > {code} > The generated logical plan is so complex for such a simple sql! > I think we can treat "IN" as a function like "plus" and "minus".So there is > no translation on "IN" and just keep it as it is.This would be much clear in > the logical plan! > In the execute stage,We can provide a "InExpression": > {code:java} > InExpression(left,condition0,condition1,...) {code} > We can put all the constant conditions to a "Set".In that way,the > computational complexity can reduce from O(n)to O(1). > It would be much clear and have a good performance. > PS: "In sub-query" is not included in our talk. -- This message was sent by Atlassian JIRA (v7.6.3#76005)