[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] [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] [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&focusedCommentId=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] [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&focusedCommentId=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-1581) UDTF like in hive
[ https://issues.apache.org/jira/browse/CALCITE-1581?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16802805#comment-16802805 ] pengzhiwei commented on CALCITE-1581: - Hi [~sihuazhou],are you still working on this issue ? if not,I will continue work on it. > 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: udtf > > 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&focusedCommentId=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] [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&focusedCommentId=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] [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&focusedCommentId=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] [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&focusedCommentId=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&focusedCommentId=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-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&focusedCommentId=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&focusedCommentId=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] [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] [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.20.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: 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&focusedCommentId=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] [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&focusedCommentId=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] [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&focusedCommentId=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] [Commented] (CALCITE-1581) UDTF like in hive
[ https://issues.apache.org/jira/browse/CALCITE-1581?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=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&focusedCommentId=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] [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&focusedCommentId=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&focusedCommentId=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] [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] [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&focusedCommentId=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] [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&focusedCommentId=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-1581) UDTF like in hive
[ https://issues.apache.org/jira/browse/CALCITE-1581?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=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-1581) UDTF like in hive
[ https://issues.apache.org/jira/browse/CALCITE-1581?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=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-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&focusedCommentId=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-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&focusedCommentId=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&focusedCommentId=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] [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] [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] [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) 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] [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&focusedCommentId=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] [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] [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] [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] [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] [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&focusedCommentId=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] [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&focusedCommentId=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 java.lang.reflect.Constructor.newIn
[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] [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] [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&focusedCommentId=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] [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&focusedCommentId=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-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&focusedCommentId=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] [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&focusedCommentId=16823641#comment-16823641 ] pengzhiwei commented on CALCITE-2453: - Hi [~Chunwei Lei], It works well for me in the case which mixed "select" with the "insert" according to you implement. Thanks! > 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] [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&focusedCommentId=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] [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&focusedCommentId=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] [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&focusedCommentId=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&focusedCommentId=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] [Commented] (CALCITE-1581) UDTF like in hive
[ https://issues.apache.org/jira/browse/CALCITE-1581?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=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-1581) UDTF like in hive
[ https://issues.apache.org/jira/browse/CALCITE-1581?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=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] [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&focusedCommentId=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&focusedCommentId=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] [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] [Commented] (CALCITE-1581) UDTF like in hive
[ https://issues.apache.org/jira/browse/CALCITE-1581?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=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] [Commented] (CALCITE-1581) UDTF like in hive
[ https://issues.apache.org/jira/browse/CALCITE-1581?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=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&focusedCommentId=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&focusedCommentId=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] [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] [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] [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 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: Summary: Missing Order logical plan for insert statement (was: Missing order by logical plan for insert statement) > Missing Order 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: 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] [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&focusedCommentId=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] [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&focusedCommentId=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] [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&focusedCommentId=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&focusedCommentId=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] [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] [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] [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] [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&focusedCommentId=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] [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&focusedCommentId=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-3738) Missing LogicalSort for INSERT statement
[ https://issues.apache.org/jira/browse/CALCITE-3738?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=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-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&focusedCommentId=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] [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&focusedCommentId=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] [Created] (CALCITE-2311) case when returns redundant white space
pengzhiwei created CALCITE-2311: --- Summary: case when returns redundant white space Key: CALCITE-2311 URL: https://issues.apache.org/jira/browse/CALCITE-2311 Project: Calcite Issue Type: Bug Components: core Affects Versions: 1.1.0-incubating Reporter: pengzhiwei Assignee: Julian Hyde Fix For: 1.17.0 If case when has two brach which returns string literal,redundant white space will append to the short brach.For example: {code:java} case when id>0 then 'a' else 'abc' end{code} will actually be convert to {code:java} case when id>0 then 'a ' else 'abc' end{code} -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Updated] (CALCITE-2311) case when returns redundant white space
[ https://issues.apache.org/jira/browse/CALCITE-2311?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] pengzhiwei updated CALCITE-2311: Description: If the case-when expression has two branches which returns string literal,redundant white space will append to the short string literal after the ReduceExpressionsRule. For example: {code:java} case when id>0 then 'a' else 'abc' end{code} will actually be convert to {code:java} case when id>0 then 'a ' else 'abc' end{code} The reason is that the type of 'a' is CHAR(1),but CHAR(3) for 'abc',so the return type of the case-when is CHAR(3). The RexBuilder#ensureType() method append a cast convertion for 'a' to cast it to CHAR(3) type.The ReduceExpressionsRule execute the cast convertion which fill two whilte space to 'a'. was: If case when has two brach which returns string literal,redundant white space will append to the short brach.For example: {code:java} case when id>0 then 'a' else 'abc' end{code} will actually be convert to {code:java} case when id>0 then 'a ' else 'abc' end{code} > case when returns redundant white space > --- > > Key: CALCITE-2311 > URL: https://issues.apache.org/jira/browse/CALCITE-2311 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.1.0-incubating >Reporter: pengzhiwei >Assignee: Julian Hyde >Priority: Major > Fix For: 1.17.0 > > > If the case-when expression has two branches which returns string > literal,redundant white space will append to the short string literal after > the ReduceExpressionsRule. For example: > {code:java} > case when id>0 then 'a' else 'abc' end{code} > will actually be convert to > {code:java} > case when id>0 then 'a ' else 'abc' end{code} > > The reason is that the type of 'a' is CHAR(1),but CHAR(3) for 'abc',so the > return type of the case-when is CHAR(3). The RexBuilder#ensureType() method > append a cast convertion for 'a' to cast it to CHAR(3) type.The > ReduceExpressionsRule execute the cast convertion which fill two whilte space > to 'a'. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Updated] (CALCITE-2311) case when returns redundant white space
[ https://issues.apache.org/jira/browse/CALCITE-2311?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] pengzhiwei updated CALCITE-2311: Fix Version/s: (was: 1.17.0) 1.12.0 > case when returns redundant white space > --- > > Key: CALCITE-2311 > URL: https://issues.apache.org/jira/browse/CALCITE-2311 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.1.0-incubating >Reporter: pengzhiwei >Assignee: Julian Hyde >Priority: Major > Fix For: 1.12.0 > > > If the case-when expression has two branches which returns string > literal,redundant white space will append to the short string literal after > the ReduceExpressionsRule. For example: > {code:java} > case when id>0 then 'a' else 'abc' end{code} > will actually be convert to > {code:java} > case when id>0 then 'a ' else 'abc' end{code} > > The reason is that the type of 'a' is CHAR(1),but CHAR(3) for 'abc',so the > return type of the case-when is CHAR(3). The RexBuilder#ensureType() method > append a cast convertion for 'a' to cast it to CHAR(3) type.The > ReduceExpressionsRule execute the cast convertion which fill two whilte space > to 'a'. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Updated] (CALCITE-2311) case when returns redundant white space
[ https://issues.apache.org/jira/browse/CALCITE-2311?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] pengzhiwei updated CALCITE-2311: Affects Version/s: (was: 1.1.0-incubating) 1.12.0 Fix Version/s: (was: 1.12.0) 1.17.0 > case when returns redundant white space > --- > > Key: CALCITE-2311 > URL: https://issues.apache.org/jira/browse/CALCITE-2311 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.12.0 >Reporter: pengzhiwei >Assignee: Julian Hyde >Priority: Major > Fix For: 1.17.0 > > > If the case-when expression has two branches which returns string > literal,redundant white space will append to the short string literal after > the ReduceExpressionsRule. For example: > {code:java} > case when id>0 then 'a' else 'abc' end{code} > will actually be convert to > {code:java} > case when id>0 then 'a ' else 'abc' end{code} > > The reason is that the type of 'a' is CHAR(1),but CHAR(3) for 'abc',so the > return type of the case-when is CHAR(3). The RexBuilder#ensureType() method > append a cast convertion for 'a' to cast it to CHAR(3) type.The > ReduceExpressionsRule execute the cast convertion which fill two whilte space > to 'a'. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Created] (CALCITE-2382) Column not found when subquery join lateral table function
pengzhiwei created CALCITE-2382: --- Summary: Column not found when subquery join lateral table function Key: CALCITE-2382 URL: https://issues.apache.org/jira/browse/CALCITE-2382 Project: Calcite Issue Type: Bug Components: core Affects Versions: 1.16.0 Reporter: pengzhiwei Assignee: Julian Hyde When use subquery join lateral table function as followed: {code:java} select * from ( select 5 as f0 from s.t ) a,lateral table(s.GenerateStrings(f0)) as t(n, c) where char_length(c) > 3 {code} 'SqlValidatorException: Column 'F0' not found in any table' has thrown out. I find the reason is that SqlValidatorImpl add the Identifier 's.t' namespace to the children of tableScope,but not the subquey's namespace,just as the code show bellow in SqlValidatorImpl#registerFrom: {code:java} case IDENTIFIER: ... if (tableScope == null) { tableScope = new TableScope(parentScope, node); } tableScope.addChild(newNs, alias, forceNullable);{code} I think the namespace of JoinScope's left child should be right for the child of tableScope which was referenced by the table function,just as the code show bellow in SqlValidatorImpl#registerFrom: {code:java} case COLLECTION_TABLE: TableScope tableScope = null; if(parentScope instanceof JoinScope) { tableScope = new TableScope(new EmptyScope(this),operand); JoinScope scope = (JoinScope) parentScope; SqlValidatorNamespace leftNs = scope.getChildren().get(0); tableScope.addChild(leftNs,scope.children.get(0).name,forceNullable); } {code} After this modification,the case upon runs well. Is there any problem for this modification? Any suggestions is welcomed.Thanks a lot. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Updated] (CALCITE-2382) Column not found when subquery join lateral table function
[ https://issues.apache.org/jira/browse/CALCITE-2382?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] pengzhiwei updated CALCITE-2382: Description: When using subquery join lateral table function as followed: {code:java} select * from ( select 5 as f0 from s.t ) a,lateral table(s.GenerateStrings(f0)) as t(n, c) where char_length(c) > 3 {code} 'SqlValidatorException: Column 'F0' not found in any table' has thrown out. I find the reason is that SqlValidatorImpl add the Identifier 's.t' namespace to the children of tableScope,but not the subquey's namespace,just as the code show bellow in SqlValidatorImpl#registerFrom: {code:java} case IDENTIFIER: ... if (tableScope == null) { tableScope = new TableScope(parentScope, node); } tableScope.addChild(newNs, alias, forceNullable);{code} I think the namespace of JoinScope's left child should be right for the child of tableScope which was referenced by the table function,just as the code show bellow in SqlValidatorImpl#registerFrom: {code:java} case COLLECTION_TABLE: TableScope tableScope = null; if(parentScope instanceof JoinScope) { tableScope = new TableScope(new EmptyScope(this),operand); JoinScope scope = (JoinScope) parentScope; SqlValidatorNamespace leftNs = scope.getChildren().get(0); tableScope.addChild(leftNs,scope.children.get(0).name,forceNullable); } {code} After this modification,the case upon runs well. Is there any problem for this modification? Any suggestions is welcomed.Thanks a lot. was: When use subquery join lateral table function as followed: {code:java} select * from ( select 5 as f0 from s.t ) a,lateral table(s.GenerateStrings(f0)) as t(n, c) where char_length(c) > 3 {code} 'SqlValidatorException: Column 'F0' not found in any table' has thrown out. I find the reason is that SqlValidatorImpl add the Identifier 's.t' namespace to the children of tableScope,but not the subquey's namespace,just as the code show bellow in SqlValidatorImpl#registerFrom: {code:java} case IDENTIFIER: ... if (tableScope == null) { tableScope = new TableScope(parentScope, node); } tableScope.addChild(newNs, alias, forceNullable);{code} I think the namespace of JoinScope's left child should be right for the child of tableScope which was referenced by the table function,just as the code show bellow in SqlValidatorImpl#registerFrom: {code:java} case COLLECTION_TABLE: TableScope tableScope = null; if(parentScope instanceof JoinScope) { tableScope = new TableScope(new EmptyScope(this),operand); JoinScope scope = (JoinScope) parentScope; SqlValidatorNamespace leftNs = scope.getChildren().get(0); tableScope.addChild(leftNs,scope.children.get(0).name,forceNullable); } {code} After this modification,the case upon runs well. Is there any problem for this modification? Any suggestions is welcomed.Thanks a lot. > Column not found when subquery join lateral table function > -- > > Key: CALCITE-2382 > URL: https://issues.apache.org/jira/browse/CALCITE-2382 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.16.0 >Reporter: pengzhiwei >Assignee: Julian Hyde >Priority: Critical > > When using subquery join lateral table function as followed: > > {code:java} > select * from ( select 5 as f0 from s.t ) a,lateral > table(s.GenerateStrings(f0)) as t(n, c) where char_length(c) > 3 > {code} > 'SqlValidatorException: Column 'F0' not found in any table' has thrown out. > I find the reason is that SqlValidatorImpl add the Identifier 's.t' > namespace to the children of tableScope,but not the subquey's namespace,just > as the code show bellow in SqlValidatorImpl#registerFrom: > > {code:java} > case IDENTIFIER: > ... > if (tableScope == null) { > tableScope = new TableScope(parentScope, node); > } > tableScope.addChild(newNs, alias, forceNullable);{code} > > > I think the namespace of JoinScope's left child should be right for the > child of tableScope which was referenced by the table function,just as the > code show bellow in SqlValidatorImpl#registerFrom: > {code:java} > case COLLECTION_TABLE: > > TableScope tableScope = null; > if(parentScope instanceof JoinScope) { > tableScope = new TableScope(new EmptyScope(this),operand); > JoinScope scope = (JoinScope) parentScope; > SqlValidatorNamespace leftNs = scope.getChildren().get(0); > tableScope.addChild(leftNs,scope.children.get(0).name,forceNullable); > } > {code} > After this modification,the case upon runs well. > Is there any problem for this modification? Any suggestions is > welcomed.Thanks a lot. > > > > -- This message was sent by Atlassian JIRA (v7.6.
[jira] [Updated] (CALCITE-2382) Column not found when subquery join lateral table function
[ https://issues.apache.org/jira/browse/CALCITE-2382?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] pengzhiwei updated CALCITE-2382: Description: When using subquery join lateral table function as followed: {code:java} select * from ( select 5 as f0 from s.t ) a,lateral table(s.GenerateStrings(f0)) as t(n, c) where char_length(c) > 3 {code} 'SqlValidatorException: Column 'F0' not found in any table' was thrown out. I find the reason is that SqlValidatorImpl add the Identifier 's.t' namespace to the children of tableScope,but not the subquey's namespace,just as the code show bellow in SqlValidatorImpl#registerFrom: {code:java} case IDENTIFIER: ... if (tableScope == null) { tableScope = new TableScope(parentScope, node); } tableScope.addChild(newNs, alias, forceNullable);{code} I think the namespace of JoinScope's left child should be right for the child of tableScope which was referenced by the table function,just as the code show bellow in SqlValidatorImpl#registerFrom: {code:java} case COLLECTION_TABLE: TableScope tableScope = null; if(parentScope instanceof JoinScope) { tableScope = new TableScope(new EmptyScope(this),operand); JoinScope scope = (JoinScope) parentScope; SqlValidatorNamespace leftNs = scope.getChildren().get(0); tableScope.addChild(leftNs,scope.children.get(0).name,forceNullable); } {code} After this modification,the case upon runs well. Is there any problem for this modification? Any suggestions is welcomed.Thanks a lot. was: When using subquery join lateral table function as followed: {code:java} select * from ( select 5 as f0 from s.t ) a,lateral table(s.GenerateStrings(f0)) as t(n, c) where char_length(c) > 3 {code} 'SqlValidatorException: Column 'F0' not found in any table' has thrown out. I find the reason is that SqlValidatorImpl add the Identifier 's.t' namespace to the children of tableScope,but not the subquey's namespace,just as the code show bellow in SqlValidatorImpl#registerFrom: {code:java} case IDENTIFIER: ... if (tableScope == null) { tableScope = new TableScope(parentScope, node); } tableScope.addChild(newNs, alias, forceNullable);{code} I think the namespace of JoinScope's left child should be right for the child of tableScope which was referenced by the table function,just as the code show bellow in SqlValidatorImpl#registerFrom: {code:java} case COLLECTION_TABLE: TableScope tableScope = null; if(parentScope instanceof JoinScope) { tableScope = new TableScope(new EmptyScope(this),operand); JoinScope scope = (JoinScope) parentScope; SqlValidatorNamespace leftNs = scope.getChildren().get(0); tableScope.addChild(leftNs,scope.children.get(0).name,forceNullable); } {code} After this modification,the case upon runs well. Is there any problem for this modification? Any suggestions is welcomed.Thanks a lot. > Column not found when subquery join lateral table function > -- > > Key: CALCITE-2382 > URL: https://issues.apache.org/jira/browse/CALCITE-2382 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.16.0 >Reporter: pengzhiwei >Assignee: Julian Hyde >Priority: Critical > > When using subquery join lateral table function as followed: > > {code:java} > select * from ( select 5 as f0 from s.t ) a,lateral > table(s.GenerateStrings(f0)) as t(n, c) where char_length(c) > 3 > {code} > 'SqlValidatorException: Column 'F0' not found in any table' was thrown out. > I find the reason is that SqlValidatorImpl add the Identifier 's.t' > namespace to the children of tableScope,but not the subquey's namespace,just > as the code show bellow in SqlValidatorImpl#registerFrom: > > {code:java} > case IDENTIFIER: > ... > if (tableScope == null) { > tableScope = new TableScope(parentScope, node); > } > tableScope.addChild(newNs, alias, forceNullable);{code} > > > I think the namespace of JoinScope's left child should be right for the > child of tableScope which was referenced by the table function,just as the > code show bellow in SqlValidatorImpl#registerFrom: > {code:java} > case COLLECTION_TABLE: > > TableScope tableScope = null; > if(parentScope instanceof JoinScope) { > tableScope = new TableScope(new EmptyScope(this),operand); > JoinScope scope = (JoinScope) parentScope; > SqlValidatorNamespace leftNs = scope.getChildren().get(0); > tableScope.addChild(leftNs,scope.children.get(0).name,forceNullable); > } > {code} > After this modification,the case upon runs well. > Is there any problem for this modification? Any suggestions is > welcomed.Thanks a lot. > > > > -- This message was sent by Atlassian JIRA (v7.
[jira] [Updated] (CALCITE-2382) Column not found when subquery join lateral table function
[ https://issues.apache.org/jira/browse/CALCITE-2382?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] pengzhiwei updated CALCITE-2382: Description: When using subquery join lateral table function as followed: {code:java} select * from ( select 5 as f0 from s.t ) a,lateral table(s.GenerateStrings(f0)) as t(n, c) where char_length(c) > 3 {code} 'SqlValidatorException: Column 'F0' not found in any table' was thrown out. I find the reason is that SqlValidatorImpl add the namespace of Identifier 's.t' to the children of tableScope,but not the subquey's namespace,just as the code show bellow in SqlValidatorImpl#registerFrom: {code:java} case IDENTIFIER: ... if (tableScope == null) { tableScope = new TableScope(parentScope, node); } tableScope.addChild(newNs, alias, forceNullable);{code} I think the namespace of JoinScope's left child should be the children of tableScope which was referenced by the table function,just as the code show bellow in SqlValidatorImpl#registerFrom: {code:java} case COLLECTION_TABLE: TableScope tableScope = null; if(parentScope instanceof JoinScope) { tableScope = new TableScope(new EmptyScope(this),operand); JoinScope scope = (JoinScope) parentScope; SqlValidatorNamespace leftNs = scope.getChildren().get(0); tableScope.addChild(leftNs,scope.children.get(0).name,forceNullable); } {code} After this modification,the case upon runs well. Is there any problem for this modification? Any suggestions is welcomed.Thanks a lot. was: When using subquery join lateral table function as followed: {code:java} select * from ( select 5 as f0 from s.t ) a,lateral table(s.GenerateStrings(f0)) as t(n, c) where char_length(c) > 3 {code} 'SqlValidatorException: Column 'F0' not found in any table' was thrown out. I find the reason is that SqlValidatorImpl add the Identifier 's.t' namespace to the children of tableScope,but not the subquey's namespace,just as the code show bellow in SqlValidatorImpl#registerFrom: {code:java} case IDENTIFIER: ... if (tableScope == null) { tableScope = new TableScope(parentScope, node); } tableScope.addChild(newNs, alias, forceNullable);{code} I think the namespace of JoinScope's left child should be right for the child of tableScope which was referenced by the table function,just as the code show bellow in SqlValidatorImpl#registerFrom: {code:java} case COLLECTION_TABLE: TableScope tableScope = null; if(parentScope instanceof JoinScope) { tableScope = new TableScope(new EmptyScope(this),operand); JoinScope scope = (JoinScope) parentScope; SqlValidatorNamespace leftNs = scope.getChildren().get(0); tableScope.addChild(leftNs,scope.children.get(0).name,forceNullable); } {code} After this modification,the case upon runs well. Is there any problem for this modification? Any suggestions is welcomed.Thanks a lot. > Column not found when subquery join lateral table function > -- > > Key: CALCITE-2382 > URL: https://issues.apache.org/jira/browse/CALCITE-2382 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.16.0 >Reporter: pengzhiwei >Assignee: Julian Hyde >Priority: Critical > > When using subquery join lateral table function as followed: > > {code:java} > select * from ( select 5 as f0 from s.t ) a,lateral > table(s.GenerateStrings(f0)) as t(n, c) where char_length(c) > 3 > {code} > 'SqlValidatorException: Column 'F0' not found in any table' was thrown out. > I find the reason is that SqlValidatorImpl add the namespace of Identifier > 's.t' to the children of tableScope,but not the subquey's namespace,just as > the code show bellow in SqlValidatorImpl#registerFrom: > {code:java} > case IDENTIFIER: > ... > if (tableScope == null) { > tableScope = new TableScope(parentScope, node); > } > tableScope.addChild(newNs, alias, forceNullable);{code} > I think the namespace of JoinScope's left child should be the children of > tableScope which was referenced by the table function,just as the code show > bellow in SqlValidatorImpl#registerFrom: > {code:java} > case COLLECTION_TABLE: > > TableScope tableScope = null; > if(parentScope instanceof JoinScope) { > tableScope = new TableScope(new EmptyScope(this),operand); > JoinScope scope = (JoinScope) parentScope; > SqlValidatorNamespace leftNs = scope.getChildren().get(0); > tableScope.addChild(leftNs,scope.children.get(0).name,forceNullable); > } > {code} > After this modification,the case upon runs well. > Is there any problem for this modification? Any suggestions is > welcomed.Thanks a lot. > > > > -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Updated] (CALCITE-2382) Column not found when subquery join lateral table function
[ https://issues.apache.org/jira/browse/CALCITE-2382?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] pengzhiwei updated CALCITE-2382: Attachment: 屏幕快照 2018-06-27 下午12.39.46.png > Column not found when subquery join lateral table function > -- > > Key: CALCITE-2382 > URL: https://issues.apache.org/jira/browse/CALCITE-2382 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.16.0 >Reporter: pengzhiwei >Assignee: Julian Hyde >Priority: Critical > Attachments: 屏幕快照 2018-06-27 下午12.39.46.png > > > When using subquery join lateral table function as followed: > > {code:java} > select * from ( select 5 as f0 from s.t ) a,lateral > table(s.GenerateStrings(f0)) as t(n, c) where char_length(c) > 3 > {code} > 'SqlValidatorException: Column 'F0' not found in any table' was thrown out. > I find the reason is that SqlValidatorImpl add the namespace of Identifier > 's.t' to the children of tableScope,but not the subquey's namespace,just as > the code show bellow in SqlValidatorImpl#registerFrom: > {code:java} > case IDENTIFIER: > ... > if (tableScope == null) { > tableScope = new TableScope(parentScope, node); > } > tableScope.addChild(newNs, alias, forceNullable);{code} > I think the namespace of JoinScope's left child should be the children of > tableScope which was referenced by the table function,just as the code show > bellow in SqlValidatorImpl#registerFrom: > {code:java} > case COLLECTION_TABLE: > > TableScope tableScope = null; > if(parentScope instanceof JoinScope) { > tableScope = new TableScope(new EmptyScope(this),operand); > JoinScope scope = (JoinScope) parentScope; > SqlValidatorNamespace leftNs = scope.getChildren().get(0); > tableScope.addChild(leftNs,scope.children.get(0).name,forceNullable); > } > {code} > After this modification,the case upon runs well. > Is there any problem for this modification? Any suggestions is > welcomed.Thanks a lot. > > > > -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Commented] (CALCITE-2382) Column not found when subquery join lateral table function
[ https://issues.apache.org/jira/browse/CALCITE-2382?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16524568#comment-16524568 ] pengzhiwei commented on CALCITE-2382: - Hello Julian,I have test on top of your branch.A exception has thrown out as follow: {code:java} Caused by: org.apache.calcite.sql.validate.SqlValidatorException: Duplicate relation name 'T' in FROM clause at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:463) at org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:572) ... 50 more {code} I find the reason is that the namespace of s.t has add twice in the JoinScope. I fix it as the code show bellow In SqlValidatorImpl: !屏幕快照 2018-06-27 下午12.39.46.png! Now it work well. > Column not found when subquery join lateral table function > -- > > Key: CALCITE-2382 > URL: https://issues.apache.org/jira/browse/CALCITE-2382 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.16.0 >Reporter: pengzhiwei >Assignee: Julian Hyde >Priority: Critical > Attachments: 屏幕快照 2018-06-27 下午12.39.46.png > > > When using subquery join lateral table function as followed: > > {code:java} > select * from ( select 5 as f0 from s.t ) a,lateral > table(s.GenerateStrings(f0)) as t(n, c) where char_length(c) > 3 > {code} > 'SqlValidatorException: Column 'F0' not found in any table' was thrown out. > I find the reason is that SqlValidatorImpl add the namespace of Identifier > 's.t' to the children of tableScope,but not the subquey's namespace,just as > the code show bellow in SqlValidatorImpl#registerFrom: > {code:java} > case IDENTIFIER: > ... > if (tableScope == null) { > tableScope = new TableScope(parentScope, node); > } > tableScope.addChild(newNs, alias, forceNullable);{code} > I think the namespace of JoinScope's left child should be the children of > tableScope which was referenced by the table function,just as the code show > bellow in SqlValidatorImpl#registerFrom: > {code:java} > case COLLECTION_TABLE: > > TableScope tableScope = null; > if(parentScope instanceof JoinScope) { > tableScope = new TableScope(new EmptyScope(this),operand); > JoinScope scope = (JoinScope) parentScope; > SqlValidatorNamespace leftNs = scope.getChildren().get(0); > tableScope.addChild(leftNs,scope.children.get(0).name,forceNullable); > } > {code} > After this modification,the case upon runs well. > Is there any problem for this modification? Any suggestions is > welcomed.Thanks a lot. > > > > -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Comment Edited] (CALCITE-2382) Column not found when subquery join lateral table function
[ https://issues.apache.org/jira/browse/CALCITE-2382?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16524568#comment-16524568 ] pengzhiwei edited comment on CALCITE-2382 at 6/27/18 4:52 AM: -- Hello Julian,I have test on top of your branch.A exception has thrown out as follow: {code:java} Caused by: org.apache.calcite.sql.validate.SqlValidatorException: Duplicate relation name 'T' in FROM clause at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:463) at org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:572) ... 50 more {code} I find the reason is that the namespace of s.t has add twice in the JoinScope. I fix it as the code show bellow In SqlValidatorImpl: !屏幕快照 2018-06-27 下午12.39.46.png! Now it work well. The table function test PR is here: https://github.com/apache/calcite/pull/743/commits/8c5ea5a6bc0d1274ad59c2405af188c9a5664724 was (Author: pzw2018): Hello Julian,I have test on top of your branch.A exception has thrown out as follow: {code:java} Caused by: org.apache.calcite.sql.validate.SqlValidatorException: Duplicate relation name 'T' in FROM clause at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:463) at org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:572) ... 50 more {code} I find the reason is that the namespace of s.t has add twice in the JoinScope. I fix it as the code show bellow In SqlValidatorImpl: !屏幕快照 2018-06-27 下午12.39.46.png! Now it work well. > Column not found when subquery join lateral table function > -- > > Key: CALCITE-2382 > URL: https://issues.apache.org/jira/browse/CALCITE-2382 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.16.0 >Reporter: pengzhiwei >Assignee: Julian Hyde >Priority: Critical > Attachments: 屏幕快照 2018-06-27 下午12.39.46.png > > > When using subquery join lateral table function as followed: > > {code:java} > select * from ( select 5 as f0 from s.t ) a,lateral > table(s.GenerateStrings(f0)) as t(n, c) where char_length(c) > 3 > {code} > 'SqlValidatorException: Column 'F0' not found in any table' was thrown out. > I find the reason is that SqlValidatorImpl add the namespace of Identifier > 's.t' to the children of tableScope,but not the subquey's namespace,just as > the code show bellow in SqlValidatorImpl#registerFrom: > {code:java} > case IDENTIFIER: > ... > if (tableScope == null) { > tableScope = new TableScope(parentScope, node); > } > tableScope.addChild(newNs, alias, forceNullable);{code} > I think the namespace of JoinScope's left child should be the children of > tableScope which was referenced by the table function,just as the code show > bellow in SqlValidatorImpl#registerFrom: > {code:java} > case COLLECTION_TABLE: > > TableScope tableScope = null; > if(parentScope instanceof JoinScope) { > tableScope = new TableScope(new EmptyScope(this),operand); > JoinScope scope = (JoinScope) parentScope; > SqlValidatorNamespace leftNs = scope.getChildren().get(0); > tableScope.addChild(leftNs,scope.children.get(0).name,forceNullable); > } > {code} > After this modification,the case upon runs well. > Is there any problem for this modification? Any suggestions is > welcomed.Thanks a lot. > > > > -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Comment Edited] (CALCITE-2382) Column not found when subquery join lateral table function
[ https://issues.apache.org/jira/browse/CALCITE-2382?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16524568#comment-16524568 ] pengzhiwei edited comment on CALCITE-2382 at 6/27/18 4:55 AM: -- Hello Julian,I have test on top of your branch.A exception has thrown out as follow: {code:java} Caused by: org.apache.calcite.sql.validate.SqlValidatorException: Duplicate relation name 'T' in FROM clause at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:463) at org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:572) ... 50 more {code} I find the reason is that the namespace of s.t has added twice in the JoinScope. I fix it as the code show bellow In SqlValidatorImpl: !屏幕快照 2018-06-27 下午12.39.46.png! Now it work well. The table function test PR is here: [https://github.com/apache/calcite/pull/743/commits/8c5ea5a6bc0d1274ad59c2405af188c9a5664724] was (Author: pzw2018): Hello Julian,I have test on top of your branch.A exception has thrown out as follow: {code:java} Caused by: org.apache.calcite.sql.validate.SqlValidatorException: Duplicate relation name 'T' in FROM clause at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:463) at org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:572) ... 50 more {code} I find the reason is that the namespace of s.t has add twice in the JoinScope. I fix it as the code show bellow In SqlValidatorImpl: !屏幕快照 2018-06-27 下午12.39.46.png! Now it work well. The table function test PR is here: https://github.com/apache/calcite/pull/743/commits/8c5ea5a6bc0d1274ad59c2405af188c9a5664724 > Column not found when subquery join lateral table function > -- > > Key: CALCITE-2382 > URL: https://issues.apache.org/jira/browse/CALCITE-2382 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.16.0 >Reporter: pengzhiwei >Assignee: Julian Hyde >Priority: Critical > Attachments: 屏幕快照 2018-06-27 下午12.39.46.png > > > When using subquery join lateral table function as followed: > > {code:java} > select * from ( select 5 as f0 from s.t ) a,lateral > table(s.GenerateStrings(f0)) as t(n, c) where char_length(c) > 3 > {code} > 'SqlValidatorException: Column 'F0' not found in any table' was thrown out. > I find the reason is that SqlValidatorImpl add the namespace of Identifier > 's.t' to the children of tableScope,but not the subquey's namespace,just as > the code show bellow in SqlValidatorImpl#registerFrom: > {code:java} > case IDENTIFIER: > ... > if (tableScope == null) { > tableScope = new TableScope(parentScope, node); > } > tableScope.addChild(newNs, alias, forceNullable);{code} > I think the namespace of JoinScope's left child should be the children of > tableScope which was referenced by the table function,just as the code show > bellow in SqlValidatorImpl#registerFrom: > {code:java} > case COLLECTION_TABLE: > > TableScope tableScope = null; > if(parentScope instanceof JoinScope) { > tableScope = new TableScope(new EmptyScope(this),operand); > JoinScope scope = (JoinScope) parentScope; > SqlValidatorNamespace leftNs = scope.getChildren().get(0); > tableScope.addChild(leftNs,scope.children.get(0).name,forceNullable); > } > {code} > After this modification,the case upon runs well. > Is there any problem for this modification? Any suggestions is > welcomed.Thanks a lot. > > > > -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Comment Edited] (CALCITE-2382) Column not found when subquery join lateral table function
[ https://issues.apache.org/jira/browse/CALCITE-2382?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16524568#comment-16524568 ] pengzhiwei edited comment on CALCITE-2382 at 6/27/18 5:00 AM: -- Hello Julian,I have test on top of your branch.An exception has thrown out as follow: {code:java} Caused by: org.apache.calcite.sql.validate.SqlValidatorException: Duplicate relation name 'T' in FROM clause at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:463) at org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:572) ... 50 more {code} I find the reason is that the namespace of s.t has added twice in the JoinScope. I fix it as the code show bellow In SqlValidatorImpl: !屏幕快照 2018-06-27 下午12.39.46.png! Now it works well. The table function test PR is here: [https://github.com/apache/calcite/pull/743/commits/8c5ea5a6bc0d1274ad59c2405af188c9a5664724] was (Author: pzw2018): Hello Julian,I have test on top of your branch.A exception has thrown out as follow: {code:java} Caused by: org.apache.calcite.sql.validate.SqlValidatorException: Duplicate relation name 'T' in FROM clause at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:463) at org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:572) ... 50 more {code} I find the reason is that the namespace of s.t has added twice in the JoinScope. I fix it as the code show bellow In SqlValidatorImpl: !屏幕快照 2018-06-27 下午12.39.46.png! Now it work well. The table function test PR is here: [https://github.com/apache/calcite/pull/743/commits/8c5ea5a6bc0d1274ad59c2405af188c9a5664724] > Column not found when subquery join lateral table function > -- > > Key: CALCITE-2382 > URL: https://issues.apache.org/jira/browse/CALCITE-2382 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.16.0 >Reporter: pengzhiwei >Assignee: Julian Hyde >Priority: Critical > Attachments: 屏幕快照 2018-06-27 下午12.39.46.png > > > When using subquery join lateral table function as followed: > > {code:java} > select * from ( select 5 as f0 from s.t ) a,lateral > table(s.GenerateStrings(f0)) as t(n, c) where char_length(c) > 3 > {code} > 'SqlValidatorException: Column 'F0' not found in any table' was thrown out. > I find the reason is that SqlValidatorImpl add the namespace of Identifier > 's.t' to the children of tableScope,but not the subquey's namespace,just as > the code show bellow in SqlValidatorImpl#registerFrom: > {code:java} > case IDENTIFIER: > ... > if (tableScope == null) { > tableScope = new TableScope(parentScope, node); > } > tableScope.addChild(newNs, alias, forceNullable);{code} > I think the namespace of JoinScope's left child should be the children of > tableScope which was referenced by the table function,just as the code show > bellow in SqlValidatorImpl#registerFrom: > {code:java} > case COLLECTION_TABLE: > > TableScope tableScope = null; > if(parentScope instanceof JoinScope) { > tableScope = new TableScope(new EmptyScope(this),operand); > JoinScope scope = (JoinScope) parentScope; > SqlValidatorNamespace leftNs = scope.getChildren().get(0); > tableScope.addChild(leftNs,scope.children.get(0).name,forceNullable); > } > {code} > After this modification,the case upon runs well. > Is there any problem for this modification? Any suggestions is > welcomed.Thanks a lot. > > > > -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Updated] (CALCITE-2382) Column not found when subquery join lateral table function
[ https://issues.apache.org/jira/browse/CALCITE-2382?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] pengzhiwei updated CALCITE-2382: Description: # When using subquery join lateral table function as followed: {code:java} select * from ( select 5 as f0 from s.t ) a,lateral table(s.GenerateStrings(f0)) as t(n, c) where char_length(c) > 3 {code} 'SqlValidatorException: Column 'F0' not found in any table' was thrown out. I find the reason is that SqlValidatorImpl add the namespace of Identifier 's.t' to the children of tableScope,but not the subquey's namespace,just as the code show bellow in SqlValidatorImpl#registerFrom: {code:java} case IDENTIFIER: ... if (tableScope == null) { tableScope = new TableScope(parentScope, node); } tableScope.addChild(newNs, alias, forceNullable);{code} I think the namespace of JoinScope's left child should be the children of tableScope which was referenced by the table function,just as the code show bellow in SqlValidatorImpl#registerFrom: {code:java} case COLLECTION_TABLE: TableScope tableScope = null; if(parentScope instanceof JoinScope) { tableScope = new TableScope(new EmptyScope(this),operand); JoinScope scope = (JoinScope) parentScope; SqlValidatorNamespace leftNs = scope.getChildren().get(0); tableScope.addChild(leftNs,scope.children.get(0).name,forceNullable); } {code} After this modification,the case upon runs well. Is there any problem for this modification? Any suggestions is welcomed.Thanks a lot. was: When using subquery join lateral table function as followed: {code:java} select * from ( select 5 as f0 from s.t ) a,lateral table(s.GenerateStrings(f0)) as t(n, c) where char_length(c) > 3 {code} 'SqlValidatorException: Column 'F0' not found in any table' was thrown out. I find the reason is that SqlValidatorImpl add the namespace of Identifier 's.t' to the children of tableScope,but not the subquey's namespace,just as the code show bellow in SqlValidatorImpl#registerFrom: {code:java} case IDENTIFIER: ... if (tableScope == null) { tableScope = new TableScope(parentScope, node); } tableScope.addChild(newNs, alias, forceNullable);{code} I think the namespace of JoinScope's left child should be the children of tableScope which was referenced by the table function,just as the code show bellow in SqlValidatorImpl#registerFrom: {code:java} case COLLECTION_TABLE: TableScope tableScope = null; if(parentScope instanceof JoinScope) { tableScope = new TableScope(new EmptyScope(this),operand); JoinScope scope = (JoinScope) parentScope; SqlValidatorNamespace leftNs = scope.getChildren().get(0); tableScope.addChild(leftNs,scope.children.get(0).name,forceNullable); } {code} After this modification,the case upon runs well. Is there any problem for this modification? Any suggestions is welcomed.Thanks a lot. > Column not found when subquery join lateral table function > -- > > Key: CALCITE-2382 > URL: https://issues.apache.org/jira/browse/CALCITE-2382 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.16.0 >Reporter: pengzhiwei >Assignee: Julian Hyde >Priority: Critical > Attachments: 屏幕快照 2018-06-27 下午12.39.46.png > > > # When using subquery join lateral table function as followed: > > {code:java} > select * from ( select 5 as f0 from s.t ) a,lateral > table(s.GenerateStrings(f0)) as t(n, c) where char_length(c) > 3 > {code} > 'SqlValidatorException: Column 'F0' not found in any table' was thrown out. > I find the reason is that SqlValidatorImpl add the namespace of Identifier > 's.t' to the children of tableScope,but not the subquey's namespace,just as > the code show bellow in SqlValidatorImpl#registerFrom: > {code:java} > case IDENTIFIER: > ... > if (tableScope == null) { > tableScope = new TableScope(parentScope, node); > } > tableScope.addChild(newNs, alias, forceNullable);{code} > I think the namespace of JoinScope's left child should be the children of > tableScope which was referenced by the table function,just as the code show > bellow in SqlValidatorImpl#registerFrom: > {code:java} > case COLLECTION_TABLE: > > TableScope tableScope = null; > if(parentScope instanceof JoinScope) { > tableScope = new TableScope(new EmptyScope(this),operand); > JoinScope scope = (JoinScope) parentScope; > SqlValidatorNamespace leftNs = scope.getChildren().get(0); > tableScope.addChild(leftNs,scope.children.get(0).name,forceNullable); > } > {code} > After this modification,the case upon runs well. > Is there any problem for this modification? Any suggestions is > welcomed.Thanks a lot. > > > > -- This message was sen
[jira] [Comment Edited] (CALCITE-2382) Column not found when subquery join lateral table function
[ https://issues.apache.org/jira/browse/CALCITE-2382?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16524568#comment-16524568 ] pengzhiwei edited comment on CALCITE-2382 at 6/29/18 2:42 AM: -- Hello [~julianhyde],I have test on top of your branch.An exception has thrown out as follow: {code:java} Caused by: org.apache.calcite.sql.validate.SqlValidatorException: Duplicate relation name 'T' in FROM clause at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:463) at org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:572) ... 50 more {code} I find the reason is that the namespace of s.t has added twice in the JoinScope. I fix it as the code show bellow In SqlValidatorImpl: !屏幕快照 2018-06-27 下午12.39.46.png! Now it works well. The table function test PR is here: [https://github.com/apache/calcite/pull/743/commits/8c5ea5a6bc0d1274ad59c2405af188c9a5664724] was (Author: pzw2018): Hello Julian,I have test on top of your branch.An exception has thrown out as follow: {code:java} Caused by: org.apache.calcite.sql.validate.SqlValidatorException: Duplicate relation name 'T' in FROM clause at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:463) at org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:572) ... 50 more {code} I find the reason is that the namespace of s.t has added twice in the JoinScope. I fix it as the code show bellow In SqlValidatorImpl: !屏幕快照 2018-06-27 下午12.39.46.png! Now it works well. The table function test PR is here: [https://github.com/apache/calcite/pull/743/commits/8c5ea5a6bc0d1274ad59c2405af188c9a5664724] > Column not found when subquery join lateral table function > -- > > Key: CALCITE-2382 > URL: https://issues.apache.org/jira/browse/CALCITE-2382 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.16.0 >Reporter: pengzhiwei >Assignee: Julian Hyde >Priority: Critical > Attachments: 屏幕快照 2018-06-27 下午12.39.46.png > > > # When using subquery join lateral table function as followed: > > {code:java} > select * from ( select 5 as f0 from s.t ) a,lateral > table(s.GenerateStrings(f0)) as t(n, c) where char_length(c) > 3 > {code} > 'SqlValidatorException: Column 'F0' not found in any table' was thrown out. > I find the reason is that SqlValidatorImpl add the namespace of Identifier > 's.t' to the children of tableScope,but not the subquey's namespace,just as > the code show bellow in SqlValidatorImpl#registerFrom: > {code:java} > case IDENTIFIER: > ... > if (tableScope == null) { > tableScope = new TableScope(parentScope, node); > } > tableScope.addChild(newNs, alias, forceNullable);{code} > I think the namespace of JoinScope's left child should be the children of > tableScope which was referenced by the table function,just as the code show > bellow in SqlValidatorImpl#registerFrom: > {code:java} > case COLLECTION_TABLE: > > TableScope tableScope = null; > if(parentScope instanceof JoinScope) { > tableScope = new TableScope(new EmptyScope(this),operand); > JoinScope scope = (JoinScope) parentScope; > SqlValidatorNamespace leftNs = scope.getChildren().get(0); > tableScope.addChild(leftNs,scope.children.get(0).name,forceNullable); > } > {code} > After this modification,the case upon runs well. > Is there any problem for this modification? Any suggestions is > welcomed.Thanks a lot. > > > > -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Created] (CALCITE-2393) RexSimplify optimization error for expression 'x is not null and x <> 5'
pengzhiwei created CALCITE-2393: --- Summary: RexSimplify optimization error for expression 'x is not null and x <> 5' Key: CALCITE-2393 URL: https://issues.apache.org/jira/browse/CALCITE-2393 Project: Calcite Issue Type: Bug Components: core Affects Versions: 1.16.0 Reporter: pengzhiwei Assignee: Julian Hyde Fix For: 1.17.0 After the optimization of RexSimplify,the expression 'x is not null and x <> 5' (x is nullable) is optimized to 'x<> 5' (RexSimplify#unknownAsFalse is true). I think it is not a correct result because when x is null, null<> 5 should return true,while 'x is not null and x <> 5' return false. I think the optimization for "is not null" in RexSimplify#simplifyAnd2ForUnknownAsFalse should exclude the case of not-equal operator. I really confused with this optimization, can you give some suggestion,thanks! -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Commented] (CALCITE-2393) RexSimplify optimization error for expression 'x is not null and x <> 5'
[ https://issues.apache.org/jira/browse/CALCITE-2393?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16530770#comment-16530770 ] pengzhiwei commented on CALCITE-2393: - Thank [~kgyrtkirk] and [~julianhyde] very much! I have learned a lot. > RexSimplify optimization error for expression 'x is not null and x <> 5' > - > > Key: CALCITE-2393 > URL: https://issues.apache.org/jira/browse/CALCITE-2393 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.16.0 >Reporter: pengzhiwei >Assignee: Julian Hyde >Priority: Major > Fix For: 1.17.0 > > > After the optimization of RexSimplify,the expression 'x is not null and x > <> 5' (x is nullable) is optimized to 'x<> 5' (RexSimplify#unknownAsFalse is > true). I think it is not a correct result because when x is null, null<> 5 > should return true,while 'x is not null and x <> 5' return false. > I think the optimization for "is not null" in > RexSimplify#simplifyAnd2ForUnknownAsFalse should exclude the case of > not-equal operator. > I really confused with this optimization, can you give some > suggestion,thanks! > > > -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Closed] (CALCITE-2393) RexSimplify optimization error for expression 'x is not null and x <> 5'
[ https://issues.apache.org/jira/browse/CALCITE-2393?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] pengzhiwei closed CALCITE-2393. --- Resolution: Not A Problem > RexSimplify optimization error for expression 'x is not null and x <> 5' > - > > Key: CALCITE-2393 > URL: https://issues.apache.org/jira/browse/CALCITE-2393 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.16.0 >Reporter: pengzhiwei >Assignee: Julian Hyde >Priority: Major > Fix For: 1.17.0 > > > After the optimization of RexSimplify,the expression 'x is not null and x > <> 5' (x is nullable) is optimized to 'x<> 5' (RexSimplify#unknownAsFalse is > true). I think it is not a correct result because when x is null, null<> 5 > should return true,while 'x is not null and x <> 5' return false. > I think the optimization for "is not null" in > RexSimplify#simplifyAnd2ForUnknownAsFalse should exclude the case of > not-equal operator. > I really confused with this optimization, can you give some > suggestion,thanks! > > > -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Created] (CALCITE-2424) AggregateProjectPullUpConstantsRule throws AssertionError when sql has "where 'a' is null"
pengzhiwei created CALCITE-2424: --- Summary: AggregateProjectPullUpConstantsRule throws AssertionError when sql has "where 'a' is null" Key: CALCITE-2424 URL: https://issues.apache.org/jira/browse/CALCITE-2424 Project: Calcite Issue Type: Bug Components: core Affects Versions: 1.16.0 Reporter: pengzhiwei Assignee: Julian Hyde Fix For: 1.17.0 Attachments: 屏幕快照 2018-07-23 下午4.09.55.png, 屏幕快照 2018-07-23 下午4.11.36.png In the sql shown bellow: {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} I find the problem arises out of the "where 'a' is null" filter condition which is always false.And it result in the relBuilder#aggregate failed to generate the new aggregate RelNode in the code as followed in AggregateProjectPullUpConstantsRule: !屏幕快照 2018-07-23 下午4.11.36.png! I think we should do nothing in the ruler in such case when the relBuilder failed to generate the new aggreate RelNode. I have submit a PR,can you have a check? Thank you. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Updated] (CALCITE-2424) AggregateProjectPullUpConstantsRule throws AssertionError when sql has "where 'a' is null"
[ https://issues.apache.org/jira/browse/CALCITE-2424?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] pengzhiwei updated CALCITE-2424: Description: In the sql shown bellow: {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} I find the problem arises out of the "where 'a' is null" filter condition which is always false.And it result in the relBuilder#aggregate failed to generate the new aggregate RelNode in the code as followed in AggregateProjectPullUpConstantsRule: !屏幕快照 2018-07-23 下午4.11.36.png! I think we should do nothing in the ruler in such case when the relBuilder failed to generate the new aggreate RelNode. I have submit a PR,can you have a check? Thank you. Here is the PR:https://github.com/apache/calcite/pull/767 was: In the sql shown bellow: {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} I find the problem arises out of the "where 'a' is null" filter condition which is always false.And it result in the relBuilder#aggregate failed to generate the new aggregate RelNode in the code as followed in AggregateProjectPullUpConstantsRule: !屏幕快照 2018-07-23 下午4.11.36.png! I think we should do nothing in the ruler in such case when the relBuilder failed to generate the new aggreate RelNode. I have submit a PR,can you have a check? Thank you. > AggregateProjectPullUpConstantsRule throws AssertionError when sql has > "where 'a' is null" > > > Key: CALCITE-2424 > URL: https://issues.apache.org/jira/browse/CALCITE-2424 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.16.0 >Reporter: pengzhiwei >Assignee: Julian Hyde >Priority: Major > Fix For: 1.17.0 > > Attachments: 屏幕快照 2018-07-23 下午4.09.55.png, 屏幕快照 2018-07-23 > 下午4.11.36.png > > > In the sql shown bellow: > {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.
[jira] [Updated] (CALCITE-2424) AggregateProjectPullUpConstantsRule throws AssertionError when sql has "where 'a' is null"
[ https://issues.apache.org/jira/browse/CALCITE-2424?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] pengzhiwei updated CALCITE-2424: Description: In the sql shown bellow: {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} I find the problem arises out of the "where 'a' is null" filter condition which is always false.And it result in the relBuilder#aggregate failed to generate the new aggregate RelNode in the code as followed in AggregateProjectPullUpConstantsRule: !屏幕快照 2018-07-23 下午4.11.36.png! I think we should do nothing in the ruler in such case when the relBuilder failed to generate the new aggreate RelNode. I have submit a PR,can you have a check? Thank you. Here is the PR: https://github.com/apache/calcite/pull/767/commits/70def02bce30392a5ea0c4af904016e3a77b9af1 was: In the sql shown bellow: {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} I find the problem arises out of the "where 'a' is null" filter condition which is always false.And it result in the relBuilder#aggregate failed to generate the new aggregate RelNode in the code as followed in AggregateProjectPullUpConstantsRule: !屏幕快照 2018-07-23 下午4.11.36.png! I think we should do nothing in the ruler in such case when the relBuilder failed to generate the new aggreate RelNode. I have submit a PR,can you have a check? Thank you. Here is the PR:https://github.com/apache/calcite/pull/767 > AggregateProjectPullUpConstantsRule throws AssertionError when sql has > "where 'a' is null" > > > Key: CALCITE-2424 > URL: https://issues.apache.org/jira/browse/CALCITE-2424 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.16.0 >Reporter: pengzhiwei >Assignee: Julian Hyde >Priority: Major > Fix For: 1.17.0 > > Attachments: 屏幕快照 2018-07-23 下午4.09.55.png, 屏幕快照 2018-07-23 > 下午4.11.36.png > > > In the sql shown bellow: > {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
[jira] [Updated] (CALCITE-2424) AggregateProjectPullUpConstantsRule throws AssertionError when sql contains "where 'a' is null"
[ https://issues.apache.org/jira/browse/CALCITE-2424?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] pengzhiwei updated CALCITE-2424: Summary: AggregateProjectPullUpConstantsRule throws AssertionError when sql contains "where 'a' is null" (was: AggregateProjectPullUpConstantsRule throws AssertionError when sql has "where 'a' is null") > AggregateProjectPullUpConstantsRule throws AssertionError when sql contains > "where 'a' is null" > - > > Key: CALCITE-2424 > URL: https://issues.apache.org/jira/browse/CALCITE-2424 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.16.0 >Reporter: pengzhiwei >Assignee: Julian Hyde >Priority: Major > Fix For: 1.17.0 > > Attachments: 屏幕快照 2018-07-23 下午4.09.55.png, 屏幕快照 2018-07-23 > 下午4.11.36.png > > > In the sql shown bellow: > {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} > I find the problem arises out of the "where 'a' is null" filter condition > which is always false.And it result in the relBuilder#aggregate failed to > generate the new aggregate RelNode in the code as followed in > AggregateProjectPullUpConstantsRule: > !屏幕快照 2018-07-23 下午4.11.36.png! > I think we should do nothing in the ruler in such case when the relBuilder > failed to generate the new aggreate RelNode. > I have submit a PR,can you have a check? Thank you. > Here is the PR: > https://github.com/apache/calcite/pull/767/commits/70def02bce30392a5ea0c4af904016e3a77b9af1 -- This message was sent by Atlassian JIRA (v7.6.3#76005)