[jira] [Commented] (CALCITE-3197) Convert data of Timestamp/Time/Date as original form when enumerating from ArrayTable
[ https://issues.apache.org/jira/browse/CALCITE-3197?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16890125#comment-16890125 ] jin xing commented on CALCITE-3197: --- THX for your shepherd ~ [~julianhyde] Now I can understand the point :) > Convert data of Timestamp/Time/Date as original form when enumerating from > ArrayTable > - > > Key: CALCITE-3197 > URL: https://issues.apache.org/jira/browse/CALCITE-3197 > Project: Calcite > Issue Type: Bug > Components: core >Reporter: jin xing >Priority: Major > Labels: pull-request-available > Time Spent: 10m > Remaining Estimate: 0h > > In current implementation ColumnLoader, data of > {{Rep.JAVA_SQL_TIMESTAMP/Rep.JAVA_SQL_TIME/Rep.JAVA_SQL_DATE}} are converted > as numeric during loading. > https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/adapter/clone/ColumnLoader.java#L234 > But current code seems forgot to revert the data back to original form when > enumerating. > As a result, below test is failing now > {code:java} > // MaterializationTest.java > @Test public void testTimestampType() { > String sql = "select \"eventid\", \"ts\"\n" > + "from \"events\"\n" > + "where \"eventid\" > 5"; > checkMaterialize(sql, sql); > }{code} > For type of {{Rep.JAVA_SQL_TIMESTAMP/Rep.JAVA_SQL_TIME/Rep.JAVA_SQL_DATE}}, > cursor acesses by {{TimestampAccessor/TimeAccessor/DateAccessor}}, which > expect column value as {{Timestamp/Time/Date}}. > It make sense to 'unwrap' the data as original form when enumerating from > {{ArrayTable}}. -- This message was sent by Atlassian JIRA (v7.6.14#76016)
[jira] [Comment Edited] (CALCITE-3197) Convert data of Timestamp/Time/Date as original form when enumerating from ArrayTable
[ https://issues.apache.org/jira/browse/CALCITE-3197?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16890125#comment-16890125 ] jin xing edited comment on CALCITE-3197 at 7/22/19 12:42 PM: - THX for your shepherd ~ [~julianhyde] I got the point now. was (Author: jinxing6...@126.com): THX for your shepherd ~ [~julianhyde] Now I can understand the point :) > Convert data of Timestamp/Time/Date as original form when enumerating from > ArrayTable > - > > Key: CALCITE-3197 > URL: https://issues.apache.org/jira/browse/CALCITE-3197 > Project: Calcite > Issue Type: Bug > Components: core >Reporter: jin xing >Priority: Major > Labels: pull-request-available > Time Spent: 10m > Remaining Estimate: 0h > > In current implementation ColumnLoader, data of > {{Rep.JAVA_SQL_TIMESTAMP/Rep.JAVA_SQL_TIME/Rep.JAVA_SQL_DATE}} are converted > as numeric during loading. > https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/adapter/clone/ColumnLoader.java#L234 > But current code seems forgot to revert the data back to original form when > enumerating. > As a result, below test is failing now > {code:java} > // MaterializationTest.java > @Test public void testTimestampType() { > String sql = "select \"eventid\", \"ts\"\n" > + "from \"events\"\n" > + "where \"eventid\" > 5"; > checkMaterialize(sql, sql); > }{code} > For type of {{Rep.JAVA_SQL_TIMESTAMP/Rep.JAVA_SQL_TIME/Rep.JAVA_SQL_DATE}}, > cursor acesses by {{TimestampAccessor/TimeAccessor/DateAccessor}}, which > expect column value as {{Timestamp/Time/Date}}. > It make sense to 'unwrap' the data as original form when enumerating from > {{ArrayTable}}. -- This message was sent by Atlassian JIRA (v7.6.14#76016)
[jira] [Closed] (CALCITE-3197) Convert data of Timestamp/Time/Date as original form when enumerating from ArrayTable
[ https://issues.apache.org/jira/browse/CALCITE-3197?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] jin xing closed CALCITE-3197. - Resolution: Invalid > Convert data of Timestamp/Time/Date as original form when enumerating from > ArrayTable > - > > Key: CALCITE-3197 > URL: https://issues.apache.org/jira/browse/CALCITE-3197 > Project: Calcite > Issue Type: Bug > Components: core >Reporter: jin xing >Priority: Major > Labels: pull-request-available > Time Spent: 20m > Remaining Estimate: 0h > > In current implementation ColumnLoader, data of > {{Rep.JAVA_SQL_TIMESTAMP/Rep.JAVA_SQL_TIME/Rep.JAVA_SQL_DATE}} are converted > as numeric during loading. > https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/adapter/clone/ColumnLoader.java#L234 > But current code seems forgot to revert the data back to original form when > enumerating. > As a result, below test is failing now > {code:java} > // MaterializationTest.java > @Test public void testTimestampType() { > String sql = "select \"eventid\", \"ts\"\n" > + "from \"events\"\n" > + "where \"eventid\" > 5"; > checkMaterialize(sql, sql); > }{code} > For type of {{Rep.JAVA_SQL_TIMESTAMP/Rep.JAVA_SQL_TIME/Rep.JAVA_SQL_DATE}}, > cursor acesses by {{TimestampAccessor/TimeAccessor/DateAccessor}}, which > expect column value as {{Timestamp/Time/Date}}. > It make sense to 'unwrap' the data as original form when enumerating from > {{ArrayTable}}. -- This message was sent by Atlassian JIRA (v7.6.14#76016)
[jira] [Created] (CALCITE-3206) Better error message when param type incompatibility
jin xing created CALCITE-3206: - Summary: Better error message when param type incompatibility Key: CALCITE-3206 URL: https://issues.apache.org/jira/browse/CALCITE-3206 Project: Calcite Issue Type: Improvement Components: core Reporter: jin xing -- This message was sent by Atlassian JIRA (v7.6.14#76016)
[jira] [Updated] (CALCITE-3206) Better error message when param type incompatibility
[ https://issues.apache.org/jira/browse/CALCITE-3206?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] jin xing updated CALCITE-3206: -- Description: In current code, when we query like below {code:java} @Test public void test() { CalciteAssert.model(HR_FKUK_MODEL) .query("select * from \"events\" where \"eventid\"='123'") .returns(CalciteAssert.outputResult()); } {code} Where {{eventid}} is of INTEGER type, Calcite will throw exception as below {code:java} Caused by: org.codehaus.commons.compiler.CompileException: Line 13, Column 100: Cannot compare types "int" and "java.lang.String" at org.codehaus.janino.UnitCompiler.compileError(UnitCompiler.java:12211) at org.codehaus.janino.UnitCompiler.compileBoolean2(UnitCompiler.java:4239) at org.codehaus.janino.UnitCompiler.access$6600(UnitCompiler.java:215) at org.codehaus.janino.UnitCompiler$14.visitBinaryOperation(UnitCompiler.java:3957) at org.codehaus.janino.UnitCompiler$14.visitBinaryOperation(UnitCompiler.java:3935) at org.codehaus.janino.Java$BinaryOperation.accept(Java.java:4864) at org.codehaus.janino.UnitCompiler.compileBoolean(UnitCompiler.java:3935) at org.codehaus.janino.UnitCompiler.compile2(UnitCompiler.java:2475) {code} It's kind of hard to understand for user to refine the sql > Better error message when param type incompatibility > > > Key: CALCITE-3206 > URL: https://issues.apache.org/jira/browse/CALCITE-3206 > Project: Calcite > Issue Type: Improvement > Components: core >Reporter: jin xing >Priority: Major > > In current code, when we query like below > {code:java} > @Test public void test() { > CalciteAssert.model(HR_FKUK_MODEL) > .query("select * from \"events\" where \"eventid\"='123'") > .returns(CalciteAssert.outputResult()); > } > {code} > Where {{eventid}} is of INTEGER type, Calcite will throw exception as below > {code:java} > Caused by: org.codehaus.commons.compiler.CompileException: Line 13, Column > 100: Cannot compare types "int" and "java.lang.String" > at > org.codehaus.janino.UnitCompiler.compileError(UnitCompiler.java:12211) > at > org.codehaus.janino.UnitCompiler.compileBoolean2(UnitCompiler.java:4239) > at org.codehaus.janino.UnitCompiler.access$6600(UnitCompiler.java:215) > at > org.codehaus.janino.UnitCompiler$14.visitBinaryOperation(UnitCompiler.java:3957) > at > org.codehaus.janino.UnitCompiler$14.visitBinaryOperation(UnitCompiler.java:3935) > at org.codehaus.janino.Java$BinaryOperation.accept(Java.java:4864) > at > org.codehaus.janino.UnitCompiler.compileBoolean(UnitCompiler.java:3935) > at org.codehaus.janino.UnitCompiler.compile2(UnitCompiler.java:2475) > {code} > It's kind of hard to understand for user to refine the sql -- This message was sent by Atlassian JIRA (v7.6.14#76016)
[jira] [Updated] (CALCITE-3206) Better error message when param type incompatibility
[ https://issues.apache.org/jira/browse/CALCITE-3206?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] ASF GitHub Bot updated CALCITE-3206: Labels: pull-request-available (was: ) > Better error message when param type incompatibility > > > Key: CALCITE-3206 > URL: https://issues.apache.org/jira/browse/CALCITE-3206 > Project: Calcite > Issue Type: Improvement > Components: core >Reporter: jin xing >Priority: Major > Labels: pull-request-available > > In current code, when we query like below > {code:java} > @Test public void test() { > CalciteAssert.model(HR_FKUK_MODEL) > .query("select * from \"events\" where \"eventid\"='123'") > .returns(CalciteAssert.outputResult()); > } > {code} > Where {{eventid}} is of INTEGER type, Calcite will throw exception as below > {code:java} > Caused by: org.codehaus.commons.compiler.CompileException: Line 13, Column > 100: Cannot compare types "int" and "java.lang.String" > at > org.codehaus.janino.UnitCompiler.compileError(UnitCompiler.java:12211) > at > org.codehaus.janino.UnitCompiler.compileBoolean2(UnitCompiler.java:4239) > at org.codehaus.janino.UnitCompiler.access$6600(UnitCompiler.java:215) > at > org.codehaus.janino.UnitCompiler$14.visitBinaryOperation(UnitCompiler.java:3957) > at > org.codehaus.janino.UnitCompiler$14.visitBinaryOperation(UnitCompiler.java:3935) > at org.codehaus.janino.Java$BinaryOperation.accept(Java.java:4864) > at > org.codehaus.janino.UnitCompiler.compileBoolean(UnitCompiler.java:3935) > at org.codehaus.janino.UnitCompiler.compile2(UnitCompiler.java:2475) > {code} > It's kind of hard to understand for user to refine the sql -- This message was sent by Atlassian JIRA (v7.6.14#76016)
[jira] [Created] (CALCITE-3207) Bug of 'JoinRelNode Convert SqlStatement'
Xurenhe created CALCITE-3207: Summary: Bug of 'JoinRelNode Convert SqlStatement' Key: CALCITE-3207 URL: https://issues.apache.org/jira/browse/CALCITE-3207 Project: Calcite Issue Type: Bug Components: core Reporter: Xurenhe Hi all I have a problem when using calcite, which is 'relnode convert sql statement' example: ``` Input relNode: LogicalProject(name=[$1], score=[$4]) LogicalJoin(condition=[AND(=($0, $3), LIKE($0, 'lucy'))], joinType=[left]) LogicalTableScan(table=[[db0, user_table]]) LogicalTableScan(table=[[db0, score_table]]) Expect output sql statement: select name, score from user_table left join on score_table and name like 'lucy' ``` this relNode cannot convert sql statement. I try to debug it, `org.apache.calcite.rel.rel2sql.SqlImplementor#convertConditionToSqlNode` may forgot to handle this case, which sqlKind is SqlKind.LIKE. If it is a bug, I am willing to fix it. Thanks. -- This message was sent by Atlassian JIRA (v7.6.14#76016)
[jira] [Commented] (CALCITE-3207) Bug of 'JoinRelNode Convert SqlStatement'
[ https://issues.apache.org/jira/browse/CALCITE-3207?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16890195#comment-16890195 ] jin xing commented on CALCITE-3207: --- Hi [~wojustme] I create a test like below {code:java} // RelToSqlConverterTest.java @Test public void testSelectQueryWithGroupByHaving2() { String query = " select \"product\".\"product_id\"\n" + "from \"product\"\n" + "inner join \"sales_fact_1997\"\n" + "on \"product\".\"product_id\" = \"sales_fact_1997\".\"product_id\"" + "and \"product\".\"product_name\" like 'apple'\n"; sql(query); } {code} The output is like below: {code:java} SELECT "t"."product_id" FROM (SELECT "product_class_id", "product_id", "brand_name", "product_name", "SKU", "SRP", "gross_weight", "net_weight", "recyclable_package", "low_fat", "units_per_case", "cases_per_pallet", "shelf_width", "shelf_height", "shelf_depth", "product_name" LIKE 'apple' AS "$f15" FROM "foodmart"."product") AS "t" INNER JOIN "foodmart"."sales_fact_1997" ON "t"."product_id" = "sales_fact_1997"."product_id" AND "t"."$f15" {code} Seems different from your description. Could you please create a test case in RelToSqlConverterTest.java to illustrate your issue ? > Bug of 'JoinRelNode Convert SqlStatement' > - > > Key: CALCITE-3207 > URL: https://issues.apache.org/jira/browse/CALCITE-3207 > Project: Calcite > Issue Type: Bug > Components: core >Reporter: Xurenhe >Priority: Minor > > Hi all > I have a problem when using calcite, which is 'relnode convert sql statement' > example: > ``` > Input relNode: > LogicalProject(name=[$1], score=[$4]) > LogicalJoin(condition=[AND(=($0, $3), LIKE($0, 'lucy'))], joinType=[left]) > LogicalTableScan(table=[[db0, user_table]]) > LogicalTableScan(table=[[db0, score_table]]) > Expect output sql statement: > select name, score > from user_table > left join on score_table > and name like 'lucy' > ``` > this relNode cannot convert sql statement. > I try to debug it, > `org.apache.calcite.rel.rel2sql.SqlImplementor#convertConditionToSqlNode` may > forgot to handle this case, which sqlKind is SqlKind.LIKE. > If it is a bug, I am willing to fix it. > Thanks. -- This message was sent by Atlassian JIRA (v7.6.14#76016)
[jira] [Comment Edited] (CALCITE-3207) Bug of 'JoinRelNode Convert SqlStatement'
[ https://issues.apache.org/jira/browse/CALCITE-3207?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16890195#comment-16890195 ] jin xing edited comment on CALCITE-3207 at 7/22/19 2:15 PM: Hi [~wojustme] I create a test like below {code:java} // RelToSqlConverterTest.java @Test public void testSelectQueryWithGroupByHaving2() { String query = " select \"product\".\"product_id\"\n" + "from \"product\"\n" + "inner join \"sales_fact_1997\"\n" + "on \"product\".\"product_id\" = \"sales_fact_1997\".\"product_id\"" + "and \"product\".\"product_name\" like 'apple'\n"; sql(query); } {code} The output is like below: {code:java} SELECT "t"."product_id" FROM (SELECT "product_class_id", "product_id", "brand_name", "product_name", "SKU", "SRP", "gross_weight", "net_weight", "recyclable_package", "low_fat", "units_per_case", "cases_per_pallet", "shelf_width", "shelf_height", "shelf_depth", "product_name" LIKE 'apple' AS "$f15" FROM "foodmart"."product") AS "t" INNER JOIN "foodmart"."sales_fact_1997" ON "t"."product_id" = "sales_fact_1997"."product_id" AND "t"."$f15" {code} Seems different from your description. Could you please create a test case in RelToSqlConverterTest.java to illustrate your issue ? was (Author: jinxing6...@126.com): Hi [~wojustme] I create a test like below {code:java} // RelToSqlConverterTest.java @Test public void testSelectQueryWithGroupByHaving2() { String query = " select \"product\".\"product_id\"\n" + "from \"product\"\n" + "inner join \"sales_fact_1997\"\n" + "on \"product\".\"product_id\" = \"sales_fact_1997\".\"product_id\"" + "and \"product\".\"product_name\" like 'apple'\n"; sql(query); } {code} The output is like below: {code:java} SELECT "t"."product_id" FROM (SELECT "product_class_id", "product_id", "brand_name", "product_name", "SKU", "SRP", "gross_weight", "net_weight", "recyclable_package", "low_fat", "units_per_case", "cases_per_pallet", "shelf_width", "shelf_height", "shelf_depth", "product_name" LIKE 'apple' AS "$f15" FROM "foodmart"."product") AS "t" INNER JOIN "foodmart"."sales_fact_1997" ON "t"."product_id" = "sales_fact_1997"."product_id" AND "t"."$f15" {code} Seems different from your description. Could you please create a test case in RelToSqlConverterTest.java to illustrate your issue ? > Bug of 'JoinRelNode Convert SqlStatement' > - > > Key: CALCITE-3207 > URL: https://issues.apache.org/jira/browse/CALCITE-3207 > Project: Calcite > Issue Type: Bug > Components: core >Reporter: Xurenhe >Priority: Minor > > Hi all > I have a problem when using calcite, which is 'relnode convert sql statement' > example: > ``` > Input relNode: > LogicalProject(name=[$1], score=[$4]) > LogicalJoin(condition=[AND(=($0, $3), LIKE($0, 'lucy'))], joinType=[left]) > LogicalTableScan(table=[[db0, user_table]]) > LogicalTableScan(table=[[db0, score_table]]) > Expect output sql statement: > select name, score > from user_table > left join on score_table > and name like 'lucy' > ``` > this relNode cannot convert sql statement. > I try to debug it, > `org.apache.calcite.rel.rel2sql.SqlImplementor#convertConditionToSqlNode` may > forgot to handle this case, which sqlKind is SqlKind.LIKE. > If it is a bug, I am willing to fix it. > Thanks. -- This message was sent by Atlassian JIRA (v7.6.14#76016)
[jira] [Comment Edited] (CALCITE-3207) Bug of 'JoinRelNode Convert SqlStatement'
[ https://issues.apache.org/jira/browse/CALCITE-3207?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16890195#comment-16890195 ] jin xing edited comment on CALCITE-3207 at 7/22/19 2:15 PM: Hi [~wojustme] I create a test like below {code:java} // RelToSqlConverterTest.java @Test public void testSelectQueryWithGroupByHaving2() { String query = " select \"product\".\"product_id\"\n" + "from \"product\"\n" + "inner join \"sales_fact_1997\"\n" + "on \"product\".\"product_id\" = \"sales_fact_1997\".\"product_id\"" + "and \"product\".\"product_name\" like 'apple'\n"; sql(query); } {code} The output is like below: {code:java} SELECT "t"."product_id" FROM (SELECT "product_class_id", "product_id", "brand_name", "product_name", "SKU", "SRP", "gross_weight", "net_weight", "recyclable_package", "low_fat", "units_per_case", "cases_per_pallet", "shelf_width", "shelf_height", "shelf_depth", "product_name" LIKE 'apple' AS "$f15" FROM "foodmart"."product") AS "t" INNER JOIN "foodmart"."sales_fact_1997" ON "t"."product_id" = "sales_fact_1997"."product_id" AND "t"."$f15" {code} Seems different from your description. Could you please create a test case in RelToSqlConverterTest.java to illustrate your issue ? was (Author: jinxing6...@126.com): Hi [~wojustme] I create a test like below {code:java} // RelToSqlConverterTest.java @Test public void testSelectQueryWithGroupByHaving2() { String query = " select \"product\".\"product_id\"\n" + "from \"product\"\n" + "inner join \"sales_fact_1997\"\n" + "on \"product\".\"product_id\" = \"sales_fact_1997\".\"product_id\"" + "and \"product\".\"product_name\" like 'apple'\n"; sql(query); } {code} The output is like below: {code:java} SELECT "t"."product_id" FROM (SELECT "product_class_id", "product_id", "brand_name", "product_name", "SKU", "SRP", "gross_weight", "net_weight", "recyclable_package", "low_fat", "units_per_case", "cases_per_pallet", "shelf_width", "shelf_height", "shelf_depth", "product_name" LIKE 'apple' AS "$f15" FROM "foodmart"."product") AS "t" INNER JOIN "foodmart"."sales_fact_1997" ON "t"."product_id" = "sales_fact_1997"."product_id" AND "t"."$f15" {code} Seems different from your description. Could you please create a test case in RelToSqlConverterTest.java to illustrate your issue ? > Bug of 'JoinRelNode Convert SqlStatement' > - > > Key: CALCITE-3207 > URL: https://issues.apache.org/jira/browse/CALCITE-3207 > Project: Calcite > Issue Type: Bug > Components: core >Reporter: Xurenhe >Priority: Minor > > Hi all > I have a problem when using calcite, which is 'relnode convert sql statement' > example: > ``` > Input relNode: > LogicalProject(name=[$1], score=[$4]) > LogicalJoin(condition=[AND(=($0, $3), LIKE($0, 'lucy'))], joinType=[left]) > LogicalTableScan(table=[[db0, user_table]]) > LogicalTableScan(table=[[db0, score_table]]) > Expect output sql statement: > select name, score > from user_table > left join on score_table > and name like 'lucy' > ``` > this relNode cannot convert sql statement. > I try to debug it, > `org.apache.calcite.rel.rel2sql.SqlImplementor#convertConditionToSqlNode` may > forgot to handle this case, which sqlKind is SqlKind.LIKE. > If it is a bug, I am willing to fix it. > Thanks. -- This message was sent by Atlassian JIRA (v7.6.14#76016)
[jira] [Updated] (CALCITE-3207) Bug of 'JoinRelNode Convert SqlStatement'
[ https://issues.apache.org/jira/browse/CALCITE-3207?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Xurenhe updated CALCITE-3207: - Description: Hi all I have a problem when using calcite, which is 'relnode convert sql statement' example: ``` @Test public void testLeftJoinRel2Sql() { final RelBuilder builder = relBuilder(); final RelNode rel = builder .scan("EMP") .scan("DEPT") .join(JoinRelType.LEFT, builder.and( builder.call(SqlStdOperatorTable.EQUALS, builder.field(2, 0, "DEPTNO"), builder.field(2, 1, "DEPTNO") ), builder.call(SqlStdOperatorTable.LIKE, builder.field(2, 1, "DNAME"), builder.literal("ACCOUNTING" .build(); final String sql = toSql(rel); final String expectedSql = "SELECT *\n" + "FROM \"scott\".\"EMP\"\n" + "LEFT JOIN \"scott\".\"DEPT\" ON \"EMP\".\"DEPTNO\" = \"DEPT\".\"DEPTNO\"\n" + "AND \"DEPT\".\"DNAME\" LIKE 'ACCOUNTING'"; assertThat(sql, isLinux(expectedSql)); } ``` this relNode cannot convert sql statement. I try to debug it, `org.apache.calcite.rel.rel2sql.SqlImplementor#convertConditionToSqlNode` may forgot to handle this case, which sqlKind is SqlKind.LIKE. If it is a bug, I am willing to fix it. Thanks. was: Hi all I have a problem when using calcite, which is 'relnode convert sql statement' example: ``` Input relNode: LogicalProject(name=[$1], score=[$4]) LogicalJoin(condition=[AND(=($0, $3), LIKE($0, 'lucy'))], joinType=[left]) LogicalTableScan(table=[[db0, user_table]]) LogicalTableScan(table=[[db0, score_table]]) Expect output sql statement: select name, score from user_table left join on score_table and name like 'lucy' ``` this relNode cannot convert sql statement. I try to debug it, `org.apache.calcite.rel.rel2sql.SqlImplementor#convertConditionToSqlNode` may forgot to handle this case, which sqlKind is SqlKind.LIKE. If it is a bug, I am willing to fix it. Thanks. > Bug of 'JoinRelNode Convert SqlStatement' > - > > Key: CALCITE-3207 > URL: https://issues.apache.org/jira/browse/CALCITE-3207 > Project: Calcite > Issue Type: Bug > Components: core >Reporter: Xurenhe >Priority: Minor > > Hi all > I have a problem when using calcite, which is 'relnode convert sql statement' > example: > ``` > @Test public void testLeftJoinRel2Sql() { > final RelBuilder builder = relBuilder(); > final RelNode rel = builder > .scan("EMP") > .scan("DEPT") > .join(JoinRelType.LEFT, builder.and( > builder.call(SqlStdOperatorTable.EQUALS, > builder.field(2, 0, "DEPTNO"), > builder.field(2, 1, "DEPTNO") > ), builder.call(SqlStdOperatorTable.LIKE, > builder.field(2, 1, "DNAME"), > builder.literal("ACCOUNTING" > .build(); > final String sql = toSql(rel); > final String expectedSql = "SELECT *\n" + > "FROM \"scott\".\"EMP\"\n" + > "LEFT JOIN \"scott\".\"DEPT\" ON \"EMP\".\"DEPTNO\" = \"DEPT\".\"DEPTNO\"\n" > + > "AND \"DEPT\".\"DNAME\" LIKE 'ACCOUNTING'"; > assertThat(sql, isLinux(expectedSql)); > } > ``` > this relNode cannot convert sql statement. > I try to debug it, > `org.apache.calcite.rel.rel2sql.SqlImplementor#convertConditionToSqlNode` may > forgot to handle this case, which sqlKind is SqlKind.LIKE. > If it is a bug, I am willing to fix it. > Thanks. > -- This message was sent by Atlassian JIRA (v7.6.14#76016)
[jira] [Updated] (CALCITE-3207) Bug of 'JoinRelNode Convert SqlStatement'
[ https://issues.apache.org/jira/browse/CALCITE-3207?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Xurenhe updated CALCITE-3207: - Description: Hi all I have a problem when using calcite, which is 'relnode convert sql statement' example: // RelToSqlConverterTest.java @Test public void testLeftJoinRel2Sql() \{ final RelBuilder builder = relBuilder(); final RelNode rel = builder .scan("EMP") .scan("DEPT") .join(JoinRelType.LEFT, builder.and( builder.call(SqlStdOperatorTable.EQUALS, builder.field(2, 0, "DEPTNO"), builder.field(2, 1, "DEPTNO") ), builder.call(SqlStdOperatorTable.LIKE, builder.field(2, 1, "DNAME"), builder.literal("ACCOUNTING" .build(); final String sql = toSql(rel); final String expectedSql = "SELECT *\n" + "FROM \"scott\".\"EMP\"\n" + "LEFT JOIN \"scott\".\"DEPT\" ON \"EMP\".\"DEPTNO\" = \"DEPT\".\"DEPTNO\"\n" + "AND \"DEPT\".\"DNAME\" LIKE 'ACCOUNTING'"; assertThat(sql, isLinux(expectedSql)); } this relNode cannot convert sql statement. I try to debug it, `org.apache.calcite.rel.rel2sql.SqlImplementor#convertConditionToSqlNode` may forgot to handle this case, which sqlKind is SqlKind.LIKE. If it is a bug, I am willing to fix it. Thanks. was: Hi all I have a problem when using calcite, which is 'relnode convert sql statement' example: ``` @Test public void testLeftJoinRel2Sql() { final RelBuilder builder = relBuilder(); final RelNode rel = builder .scan("EMP") .scan("DEPT") .join(JoinRelType.LEFT, builder.and( builder.call(SqlStdOperatorTable.EQUALS, builder.field(2, 0, "DEPTNO"), builder.field(2, 1, "DEPTNO") ), builder.call(SqlStdOperatorTable.LIKE, builder.field(2, 1, "DNAME"), builder.literal("ACCOUNTING" .build(); final String sql = toSql(rel); final String expectedSql = "SELECT *\n" + "FROM \"scott\".\"EMP\"\n" + "LEFT JOIN \"scott\".\"DEPT\" ON \"EMP\".\"DEPTNO\" = \"DEPT\".\"DEPTNO\"\n" + "AND \"DEPT\".\"DNAME\" LIKE 'ACCOUNTING'"; assertThat(sql, isLinux(expectedSql)); } ``` this relNode cannot convert sql statement. I try to debug it, `org.apache.calcite.rel.rel2sql.SqlImplementor#convertConditionToSqlNode` may forgot to handle this case, which sqlKind is SqlKind.LIKE. If it is a bug, I am willing to fix it. Thanks. > Bug of 'JoinRelNode Convert SqlStatement' > - > > Key: CALCITE-3207 > URL: https://issues.apache.org/jira/browse/CALCITE-3207 > Project: Calcite > Issue Type: Bug > Components: core >Reporter: Xurenhe >Priority: Minor > > Hi all > I have a problem when using calcite, which is 'relnode convert sql statement' > example: > // RelToSqlConverterTest.java > @Test public void testLeftJoinRel2Sql() \{ > final RelBuilder builder = relBuilder(); > final RelNode rel = builder > .scan("EMP") > .scan("DEPT") > .join(JoinRelType.LEFT, builder.and( > builder.call(SqlStdOperatorTable.EQUALS, > builder.field(2, 0, "DEPTNO"), > builder.field(2, 1, "DEPTNO") > ), builder.call(SqlStdOperatorTable.LIKE, > builder.field(2, 1, "DNAME"), > builder.literal("ACCOUNTING" > .build(); > final String sql = toSql(rel); > final String expectedSql = "SELECT *\n" + > "FROM \"scott\".\"EMP\"\n" + > "LEFT JOIN \"scott\".\"DEPT\" ON \"EMP\".\"DEPTNO\" = > \"DEPT\".\"DEPTNO\"\n" + > "AND \"DEPT\".\"DNAME\" LIKE 'ACCOUNTING'"; > assertThat(sql, isLinux(expectedSql)); > } > this relNode cannot convert sql statement. > I try to debug it, > `org.apache.calcite.rel.rel2sql.SqlImplementor#convertConditionToSqlNode` may > forgot to handle this case, which sqlKind is SqlKind.LIKE. > If it is a bug, I am willing to fix it. > Thanks. > -- This message was sent by Atlassian JIRA (v7.6.14#76016)
[jira] [Updated] (CALCITE-3207) Bug of 'JoinRelNode Convert SqlStatement'
[ https://issues.apache.org/jira/browse/CALCITE-3207?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Xurenhe updated CALCITE-3207: - Description: Hi all I have a problem when using calcite, which is 'relnode convert sql statement' {code:java} //代码占位符 {code} example: this relNode cannot convert sql statement. I try to debug it, `org.apache.calcite.rel.rel2sql.SqlImplementor#convertConditionToSqlNode` may forgot to handle this case, which sqlKind is SqlKind.LIKE. If it is a bug, I am willing to fix it. Thanks. was: Hi all I have a problem when using calcite, which is 'relnode convert sql statement' example: // RelToSqlConverterTest.java @Test public void testLeftJoinRel2Sql() \{ final RelBuilder builder = relBuilder(); final RelNode rel = builder .scan("EMP") .scan("DEPT") .join(JoinRelType.LEFT, builder.and( builder.call(SqlStdOperatorTable.EQUALS, builder.field(2, 0, "DEPTNO"), builder.field(2, 1, "DEPTNO") ), builder.call(SqlStdOperatorTable.LIKE, builder.field(2, 1, "DNAME"), builder.literal("ACCOUNTING" .build(); final String sql = toSql(rel); final String expectedSql = "SELECT *\n" + "FROM \"scott\".\"EMP\"\n" + "LEFT JOIN \"scott\".\"DEPT\" ON \"EMP\".\"DEPTNO\" = \"DEPT\".\"DEPTNO\"\n" + "AND \"DEPT\".\"DNAME\" LIKE 'ACCOUNTING'"; assertThat(sql, isLinux(expectedSql)); } this relNode cannot convert sql statement. I try to debug it, `org.apache.calcite.rel.rel2sql.SqlImplementor#convertConditionToSqlNode` may forgot to handle this case, which sqlKind is SqlKind.LIKE. If it is a bug, I am willing to fix it. Thanks. > Bug of 'JoinRelNode Convert SqlStatement' > - > > Key: CALCITE-3207 > URL: https://issues.apache.org/jira/browse/CALCITE-3207 > Project: Calcite > Issue Type: Bug > Components: core >Reporter: Xurenhe >Priority: Minor > > Hi all > I have a problem when using calcite, which is 'relnode convert sql statement' > {code:java} > //代码占位符 > {code} > example: > this relNode cannot convert sql statement. > I try to debug it, > `org.apache.calcite.rel.rel2sql.SqlImplementor#convertConditionToSqlNode` may > forgot to handle this case, which sqlKind is SqlKind.LIKE. > If it is a bug, I am willing to fix it. > Thanks. > -- This message was sent by Atlassian JIRA (v7.6.14#76016)
[jira] [Updated] (CALCITE-3207) Bug of 'JoinRelNode Convert SqlStatement'
[ https://issues.apache.org/jira/browse/CALCITE-3207?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Xurenhe updated CALCITE-3207: - Description: Hi all I have a problem when using calcite, which is 'relnode convert sql statement' {code:java} //代码占位符 {code} example: this relNode cannot convert sql statement. I try to debug it, `org.apache.calcite.rel.rel2sql.SqlImplementor#convertConditionToSqlNode` may forgot to handle this case, which sqlKind is SqlKind.LIKE. If it is a bug, I am willing to fix it. Thanks. was: Hi all I have a problem when using calcite, which is 'relnode convert sql statement' {code:java} //代码占位符 {code} example: this relNode cannot convert sql statement. I try to debug it, `org.apache.calcite.rel.rel2sql.SqlImplementor#convertConditionToSqlNode` may forgot to handle this case, which sqlKind is SqlKind.LIKE. If it is a bug, I am willing to fix it. Thanks. > Bug of 'JoinRelNode Convert SqlStatement' > - > > Key: CALCITE-3207 > URL: https://issues.apache.org/jira/browse/CALCITE-3207 > Project: Calcite > Issue Type: Bug > Components: core >Reporter: Xurenhe >Priority: Minor > > Hi all > I have a problem when using calcite, which is 'relnode convert sql statement' > {code:java} > //代码占位符 > {code} > example: > this relNode cannot convert sql statement. > I try to debug it, > `org.apache.calcite.rel.rel2sql.SqlImplementor#convertConditionToSqlNode` may > forgot to handle this case, which sqlKind is SqlKind.LIKE. > If it is a bug, I am willing to fix it. > Thanks. > -- This message was sent by Atlassian JIRA (v7.6.14#76016)
[jira] [Updated] (CALCITE-3207) Bug of 'JoinRelNode Convert SqlStatement'
[ https://issues.apache.org/jira/browse/CALCITE-3207?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Xurenhe updated CALCITE-3207: - Attachment: image-2019-07-22-23-15-32-107.png > Bug of 'JoinRelNode Convert SqlStatement' > - > > Key: CALCITE-3207 > URL: https://issues.apache.org/jira/browse/CALCITE-3207 > Project: Calcite > Issue Type: Bug > Components: core >Reporter: Xurenhe >Priority: Minor > Attachments: image-2019-07-22-23-15-32-107.png > > > Hi all > I have a problem when using calcite, which is 'relnode convert sql statement' > {code:java} > //代码占位符 > {code} > example: > this relNode cannot convert sql statement. > I try to debug it, > `org.apache.calcite.rel.rel2sql.SqlImplementor#convertConditionToSqlNode` may > forgot to handle this case, which sqlKind is SqlKind.LIKE. > If it is a bug, I am willing to fix it. > Thanks. > -- This message was sent by Atlassian JIRA (v7.6.14#76016)
[jira] [Updated] (CALCITE-3207) Bug of 'JoinRelNode Convert SqlStatement'
[ https://issues.apache.org/jira/browse/CALCITE-3207?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Xurenhe updated CALCITE-3207: - Description: Hi all I have a problem when using calcite, which is 'relnode convert sql statement example: !image-2019-07-22-23-15-32-107.png! this relNode cannot convert sql statement. I try to debug it, `org.apache.calcite.rel.rel2sql.SqlImplementor#convertConditionToSqlNode` may forgot to handle this case, which sqlKind is SqlKind.LIKE. If it is a bug, I am willing to fix it. Thanks. was: Hi all I have a problem when using calcite, which is 'relnode convert sql statement' {code:java} //代码占位符 {code} example: this relNode cannot convert sql statement. I try to debug it, `org.apache.calcite.rel.rel2sql.SqlImplementor#convertConditionToSqlNode` may forgot to handle this case, which sqlKind is SqlKind.LIKE. If it is a bug, I am willing to fix it. Thanks. > Bug of 'JoinRelNode Convert SqlStatement' > - > > Key: CALCITE-3207 > URL: https://issues.apache.org/jira/browse/CALCITE-3207 > Project: Calcite > Issue Type: Bug > Components: core >Reporter: Xurenhe >Priority: Minor > Attachments: image-2019-07-22-23-15-32-107.png > > > Hi all > I have a problem when using calcite, which is 'relnode convert sql statement > example: > !image-2019-07-22-23-15-32-107.png! > this relNode cannot convert sql statement. > I try to debug it, > `org.apache.calcite.rel.rel2sql.SqlImplementor#convertConditionToSqlNode` may > forgot to handle this case, which sqlKind is SqlKind.LIKE. > If it is a bug, I am willing to fix it. > Thanks. > -- This message was sent by Atlassian JIRA (v7.6.14#76016)
[jira] [Updated] (CALCITE-3207) Bug of 'JoinRelNode Convert SqlStatement'
[ https://issues.apache.org/jira/browse/CALCITE-3207?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Xurenhe updated CALCITE-3207: - Description: Hi all I have a problem when using calcite, which is 'relnode convert sql statement example: I create a test like below {code:java} // RelToSqlConverterTest.java @Test public void testLeftJoinRel2Sql() { final RelBuilder builder = relBuilder(); final RelNode rel = builder .scan("EMP") .scan("DEPT") .join(JoinRelType.LEFT, builder.and( builder.call(SqlStdOperatorTable.EQUALS, builder.field(2, 0, "DEPTNO"), builder.field(2, 1, "DEPTNO") ), builder.call(SqlStdOperatorTable.LIKE, builder.field(2, 1, "DNAME"), builder.literal("ACCOUNTING" .build(); final String sql = toSql(rel); final String expectedSql = "SELECT *\n" + "FROM \"scott\".\"EMP\"\n" + "LEFT JOIN \"scott\".\"DEPT\" ON \"EMP\".\"DEPTNO\" = \"DEPT\".\"DEPTNO\"\n" + "AND \"DEPT\".\"DNAME\" LIKE 'ACCOUNTING'"; assertThat(sql, isLinux(expectedSql)); }{code} this relNode cannot convert sql statement. I try to debug it, `org.apache.calcite.rel.rel2sql.SqlImplementor#convertConditionToSqlNode` may forgot to handle this case, which sqlKind is SqlKind.LIKE. If it is a bug, I am willing to fix it. Thanks. was: Hi all I have a problem when using calcite, which is 'relnode convert sql statement example: !image-2019-07-22-23-15-32-107.png! this relNode cannot convert sql statement. I try to debug it, `org.apache.calcite.rel.rel2sql.SqlImplementor#convertConditionToSqlNode` may forgot to handle this case, which sqlKind is SqlKind.LIKE. If it is a bug, I am willing to fix it. Thanks. > Bug of 'JoinRelNode Convert SqlStatement' > - > > Key: CALCITE-3207 > URL: https://issues.apache.org/jira/browse/CALCITE-3207 > Project: Calcite > Issue Type: Bug > Components: core >Reporter: Xurenhe >Priority: Minor > Attachments: image-2019-07-22-23-15-32-107.png > > > Hi all > I have a problem when using calcite, which is 'relnode convert sql statement > example: > > I create a test like below > {code:java} > // RelToSqlConverterTest.java > @Test public void testLeftJoinRel2Sql() { > final RelBuilder builder = relBuilder(); > final RelNode rel = builder > .scan("EMP") > .scan("DEPT") > .join(JoinRelType.LEFT, builder.and( > builder.call(SqlStdOperatorTable.EQUALS, > builder.field(2, 0, "DEPTNO"), > builder.field(2, 1, "DEPTNO") > ), builder.call(SqlStdOperatorTable.LIKE, > builder.field(2, 1, "DNAME"), > builder.literal("ACCOUNTING" > .build(); > final String sql = toSql(rel); > final String expectedSql = "SELECT *\n" + > "FROM \"scott\".\"EMP\"\n" + > "LEFT JOIN \"scott\".\"DEPT\" ON \"EMP\".\"DEPTNO\" = > \"DEPT\".\"DEPTNO\"\n" + > "AND \"DEPT\".\"DNAME\" LIKE 'ACCOUNTING'"; > assertThat(sql, isLinux(expectedSql)); > }{code} > > this relNode cannot convert sql statement. > I try to debug it, > `org.apache.calcite.rel.rel2sql.SqlImplementor#convertConditionToSqlNode` may > forgot to handle this case, which sqlKind is SqlKind.LIKE. > If it is a bug, I am willing to fix it. > Thanks. > -- This message was sent by Atlassian JIRA (v7.6.14#76016)
[jira] [Updated] (CALCITE-3198) ReduceExpressionsRule.FILTER_INSTANCE does not reduce 'NOT(x=a AND x=b)'
[ https://issues.apache.org/jira/browse/CALCITE-3198?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Ruben Quesada Lopez updated CALCITE-3198: - Description: Currently, ReduceExpressionsRule.FILTER_INSTANCE can successfully reduce a query like this one (see RelOptRulesTest#testReduceConstantsDup): {code} // query: select d.deptno from dept d where d.deptno=7 and d.deptno=8 // plan before: LogicalProject(DEPTNO=[$0]) LogicalFilter(condition=[AND(=($0, 7), =($0, 8))]) LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) // plan after: LogicalProject(DEPTNO=[$0]) LogicalValues(tuples=[[]]) {code} As we can see, since the filter is 'always false', the LogicalTableScan+LogicalFilter are correctly replaced by an empty LogicalValues. However, the same filter with a NOT expression, is not correctly simplified: {code} // query: select d.deptno from dept d where not(d.deptno=7 and d.deptno=8) // plan before: LogicalProject(DEPTNO=[$0]) LogicalFilter(condition=[NOT(AND(=($0, 7), =($0, 8)))]) LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) // plan after (actual, NOT distributivity for AND): LogicalProject(DEPTNO=[$0]) LogicalFilter(condition=[OR(<>($0, 7), <>($0, 8))]) LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) // plan after (expected, when $0's type is NOT nullable, filter removed): LogicalProject(DEPTNO=[$0]) LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) // plan after (expected, when $0's type IS nullable, filter simplified as IS NOT NULL): LogicalProject(DEPTNO=[$0]) LogicalFilter(condition=[$0 IS NOT NULL]) LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) {code} was: Currently, ReduceExpressionsRule.FILTER_INSTANCE can successfully reduce a query like this one (see RelOptRulesTest#testReduceConstantsDup): {code} // query: select d.deptno from dept d where d.deptno=7 and d.deptno=8 // plan before: LogicalProject(DEPTNO=[$0]) LogicalFilter(condition=[AND(=($0, 7), =($0, 8))]) LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) // plan after: LogicalProject(DEPTNO=[$0]) LogicalValues(tuples=[[]]) {code} As we can see, since the filter is 'always false', the LogicalTableScan+LogicalFilter are correctly replaced by an empty LogicalValues. However, the same filter with a NOT expression, is not correctly simplified: {code} // query: select d.deptno from dept d where not(d.deptno=7 and d.deptno=8) // plan before: LogicalProject(DEPTNO=[$0]) LogicalFilter(condition=[NOT(AND(=($0, 7), =($0, 8)))]) LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) // plan after (actual, NOT distributivity for AND): LogicalProject(DEPTNO=[$0]) LogicalFilter(condition=[OR(<>($0, 7), <>($0, 8))]) LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) // plan after (expected, filter removed): LogicalProject(DEPTNO=[$0]) LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) {code} Since the filter is the negated of an 'always false filter' (the one used in the previous query), it is therefore an 'always true filter', so the expected behavior is that the LogicalFilter should be removed, and it is not. > ReduceExpressionsRule.FILTER_INSTANCE does not reduce 'NOT(x=a AND x=b)' > > > Key: CALCITE-3198 > URL: https://issues.apache.org/jira/browse/CALCITE-3198 > Project: Calcite > Issue Type: Bug >Affects Versions: 1.20.0 >Reporter: Ruben Quesada Lopez >Assignee: Ruben Quesada Lopez >Priority: Minor > Labels: pull-request-available > Fix For: 1.21.0 > > Time Spent: 2h 40m > Remaining Estimate: 0h > > Currently, ReduceExpressionsRule.FILTER_INSTANCE can successfully reduce a > query like this one (see RelOptRulesTest#testReduceConstantsDup): > {code} > // query: > select d.deptno from dept d where d.deptno=7 and d.deptno=8 > // plan before: > LogicalProject(DEPTNO=[$0]) > LogicalFilter(condition=[AND(=($0, 7), =($0, 8))]) > LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) > // plan after: > LogicalProject(DEPTNO=[$0]) > LogicalValues(tuples=[[]]) > {code} > As we can see, since the filter is 'always false', the > LogicalTableScan+LogicalFilter are correctly replaced by an empty > LogicalValues. > However, the same filter with a NOT expression, is not correctly simplified: > {code} > // query: > select d.deptno from dept d where not(d.deptno=7 and d.deptno=8) > // plan before: > LogicalProject(DEPTNO=[$0]) > LogicalFilter(condition=[NOT(AND(=($0, 7), =($0, 8)))]) > LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) > // plan after (actual, NOT distributivity for AND): > LogicalProject(DEPTNO=[$0]) > LogicalFilter(condition=[OR(<>($0, 7), <>($0, 8))]) > LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) > // plan after (expected, when $0's type is NOT nullable, filter removed): > LogicalProject(DEPTNO
[jira] [Comment Edited] (CALCITE-3198) ReduceExpressionsRule.FILTER_INSTANCE does not reduce 'NOT(x=a AND x=b)'
[ https://issues.apache.org/jira/browse/CALCITE-3198?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16885045#comment-16885045 ] Ruben Quesada Lopez edited comment on CALCITE-3198 at 7/22/19 3:38 PM: --- Same result is obtained with the following test, which is maybe more straightforward (and by solving this, the one in the description might be also solved as a side effect): {code} // query: select d.deptno from dept d where d.deptno<>7 or d.deptno<>8 // plan before: LogicalProject(DEPTNO=[$0]) LogicalFilter(condition=[OR(<>($0, 7), <>($0, 8))]) LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) // plan after (actual, unchanged): LogicalProject(DEPTNO=[$0]) LogicalFilter(condition=[OR(<>($0, 7), <>($0, 8))]) LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) // plan after (expected, filter removed): LogicalProject(DEPTNO=[$0]) LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) {code} was (Author: rubenql): Same result is obtained with the following test, which is maybe more straightforward (and by solving this, the one in the description might be also solved as a side effect): {code} // query: select d.deptno from dept d where d.deptno<>7 or d.deptno=8 // plan before: LogicalProject(DEPTNO=[$0]) LogicalFilter(condition=[OR(<>($0, 7), <>($0, 8))]) LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) // plan after (actual, unchanged): LogicalProject(DEPTNO=[$0]) LogicalFilter(condition=[OR(<>($0, 7), <>($0, 8))]) LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) // plan after (expected, filter removed): LogicalProject(DEPTNO=[$0]) LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) {code} > ReduceExpressionsRule.FILTER_INSTANCE does not reduce 'NOT(x=a AND x=b)' > > > Key: CALCITE-3198 > URL: https://issues.apache.org/jira/browse/CALCITE-3198 > Project: Calcite > Issue Type: Bug >Affects Versions: 1.20.0 >Reporter: Ruben Quesada Lopez >Assignee: Ruben Quesada Lopez >Priority: Minor > Labels: pull-request-available > Fix For: 1.21.0 > > Time Spent: 2h 40m > Remaining Estimate: 0h > > Currently, ReduceExpressionsRule.FILTER_INSTANCE can successfully reduce a > query like this one (see RelOptRulesTest#testReduceConstantsDup): > {code} > // query: > select d.deptno from dept d where d.deptno=7 and d.deptno=8 > // plan before: > LogicalProject(DEPTNO=[$0]) > LogicalFilter(condition=[AND(=($0, 7), =($0, 8))]) > LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) > // plan after: > LogicalProject(DEPTNO=[$0]) > LogicalValues(tuples=[[]]) > {code} > As we can see, since the filter is 'always false', the > LogicalTableScan+LogicalFilter are correctly replaced by an empty > LogicalValues. > However, the same filter with a NOT expression, is not correctly simplified: > {code} > // query: > select d.deptno from dept d where not(d.deptno=7 and d.deptno=8) > // plan before: > LogicalProject(DEPTNO=[$0]) > LogicalFilter(condition=[NOT(AND(=($0, 7), =($0, 8)))]) > LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) > // plan after (actual, NOT distributivity for AND): > LogicalProject(DEPTNO=[$0]) > LogicalFilter(condition=[OR(<>($0, 7), <>($0, 8))]) > LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) > // plan after (expected, when $0's type is NOT nullable, filter removed): > LogicalProject(DEPTNO=[$0]) > LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) > // plan after (expected, when $0's type IS nullable, filter simplified as IS > NOT NULL): > LogicalProject(DEPTNO=[$0]) > LogicalFilter(condition=[$0 IS NOT NULL]) > LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) > {code} -- This message was sent by Atlassian JIRA (v7.6.14#76016)
[jira] [Comment Edited] (CALCITE-3198) ReduceExpressionsRule.FILTER_INSTANCE does not reduce 'NOT(x=a AND x=b)'
[ https://issues.apache.org/jira/browse/CALCITE-3198?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16885045#comment-16885045 ] Ruben Quesada Lopez edited comment on CALCITE-3198 at 7/22/19 3:38 PM: --- Same result is obtained with the following test, which is maybe more straightforward (and by solving this, the one in the description might be also solved as a side effect): {code} // query: select d.deptno from dept d where d.deptno<>7 or d.deptno<>8 // plan before: LogicalProject(DEPTNO=[$0]) LogicalFilter(condition=[OR(<>($0, 7), <>($0, 8))]) LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) // plan after (actual, unchanged): LogicalProject(DEPTNO=[$0]) LogicalFilter(condition=[OR(<>($0, 7), <>($0, 8))]) LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) // plan after (expected, when $0's type is NOT nullable, filter removed): LogicalProject(DEPTNO=[$0]) LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) // plan after (expected, when $0's type IS nullable, filter simplified as IS NOT NULL): LogicalProject(DEPTNO=[$0]) LogicalFilter(condition=[$0 IS NOT NULL]) LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) {code} was (Author: rubenql): Same result is obtained with the following test, which is maybe more straightforward (and by solving this, the one in the description might be also solved as a side effect): {code} // query: select d.deptno from dept d where d.deptno<>7 or d.deptno<>8 // plan before: LogicalProject(DEPTNO=[$0]) LogicalFilter(condition=[OR(<>($0, 7), <>($0, 8))]) LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) // plan after (actual, unchanged): LogicalProject(DEPTNO=[$0]) LogicalFilter(condition=[OR(<>($0, 7), <>($0, 8))]) LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) // plan after (expected, filter removed): LogicalProject(DEPTNO=[$0]) LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) {code} > ReduceExpressionsRule.FILTER_INSTANCE does not reduce 'NOT(x=a AND x=b)' > > > Key: CALCITE-3198 > URL: https://issues.apache.org/jira/browse/CALCITE-3198 > Project: Calcite > Issue Type: Bug >Affects Versions: 1.20.0 >Reporter: Ruben Quesada Lopez >Assignee: Ruben Quesada Lopez >Priority: Minor > Labels: pull-request-available > Fix For: 1.21.0 > > Time Spent: 2h 40m > Remaining Estimate: 0h > > Currently, ReduceExpressionsRule.FILTER_INSTANCE can successfully reduce a > query like this one (see RelOptRulesTest#testReduceConstantsDup): > {code} > // query: > select d.deptno from dept d where d.deptno=7 and d.deptno=8 > // plan before: > LogicalProject(DEPTNO=[$0]) > LogicalFilter(condition=[AND(=($0, 7), =($0, 8))]) > LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) > // plan after: > LogicalProject(DEPTNO=[$0]) > LogicalValues(tuples=[[]]) > {code} > As we can see, since the filter is 'always false', the > LogicalTableScan+LogicalFilter are correctly replaced by an empty > LogicalValues. > However, the same filter with a NOT expression, is not correctly simplified: > {code} > // query: > select d.deptno from dept d where not(d.deptno=7 and d.deptno=8) > // plan before: > LogicalProject(DEPTNO=[$0]) > LogicalFilter(condition=[NOT(AND(=($0, 7), =($0, 8)))]) > LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) > // plan after (actual, NOT distributivity for AND): > LogicalProject(DEPTNO=[$0]) > LogicalFilter(condition=[OR(<>($0, 7), <>($0, 8))]) > LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) > // plan after (expected, when $0's type is NOT nullable, filter removed): > LogicalProject(DEPTNO=[$0]) > LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) > // plan after (expected, when $0's type IS nullable, filter simplified as IS > NOT NULL): > LogicalProject(DEPTNO=[$0]) > LogicalFilter(condition=[$0 IS NOT NULL]) > LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) > {code} -- This message was sent by Atlassian JIRA (v7.6.14#76016)
[jira] [Commented] (CALCITE-3207) Bug of 'JoinRelNode Convert SqlStatement'
[ https://issues.apache.org/jira/browse/CALCITE-3207?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16890590#comment-16890590 ] Danny Chan commented on CALCITE-3207: - Hi, [~wojustme], it seems a bug there, and it would be great if you can supply a PR for fixing ~ > Bug of 'JoinRelNode Convert SqlStatement' > - > > Key: CALCITE-3207 > URL: https://issues.apache.org/jira/browse/CALCITE-3207 > Project: Calcite > Issue Type: Bug > Components: core >Reporter: Xurenhe >Priority: Minor > Attachments: image-2019-07-22-23-15-32-107.png > > > Hi all > I have a problem when using calcite, which is 'relnode convert sql statement > example: > > I create a test like below > {code:java} > // RelToSqlConverterTest.java > @Test public void testLeftJoinRel2Sql() { > final RelBuilder builder = relBuilder(); > final RelNode rel = builder > .scan("EMP") > .scan("DEPT") > .join(JoinRelType.LEFT, builder.and( > builder.call(SqlStdOperatorTable.EQUALS, > builder.field(2, 0, "DEPTNO"), > builder.field(2, 1, "DEPTNO") > ), builder.call(SqlStdOperatorTable.LIKE, > builder.field(2, 1, "DNAME"), > builder.literal("ACCOUNTING" > .build(); > final String sql = toSql(rel); > final String expectedSql = "SELECT *\n" + > "FROM \"scott\".\"EMP\"\n" + > "LEFT JOIN \"scott\".\"DEPT\" ON \"EMP\".\"DEPTNO\" = > \"DEPT\".\"DEPTNO\"\n" + > "AND \"DEPT\".\"DNAME\" LIKE 'ACCOUNTING'"; > assertThat(sql, isLinux(expectedSql)); > }{code} > > this relNode cannot convert sql statement. > I try to debug it, > `org.apache.calcite.rel.rel2sql.SqlImplementor#convertConditionToSqlNode` may > forgot to handle this case, which sqlKind is SqlKind.LIKE. > If it is a bug, I am willing to fix it. > Thanks. > -- This message was sent by Atlassian JIRA (v7.6.14#76016)
[jira] [Commented] (CALCITE-3206) Better error message when param type incompatibility
[ https://issues.apache.org/jira/browse/CALCITE-3206?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16890606#comment-16890606 ] Danny Chan commented on CALCITE-3206: - Sorry, i don't think we should make any decision and throw assertion error like this, the StandardConvertletTable.java did some work for the function call's operands type consistency, but it does not guarantee this, and it only touch the standard operator table, how about the UDF/UDAF ? Also calcite allows some comparable types to be compared directly, like a .string and numeric, so please do not make the change to these tests, this destroy the semantics. > Better error message when param type incompatibility > > > Key: CALCITE-3206 > URL: https://issues.apache.org/jira/browse/CALCITE-3206 > Project: Calcite > Issue Type: Improvement > Components: core >Reporter: jin xing >Priority: Major > Labels: pull-request-available > Time Spent: 20m > Remaining Estimate: 0h > > In current code, when we query like below > {code:java} > @Test public void test() { > CalciteAssert.model(HR_FKUK_MODEL) > .query("select * from \"events\" where \"eventid\"='123'") > .returns(CalciteAssert.outputResult()); > } > {code} > Where {{eventid}} is of INTEGER type, Calcite will throw exception as below > {code:java} > Caused by: org.codehaus.commons.compiler.CompileException: Line 13, Column > 100: Cannot compare types "int" and "java.lang.String" > at > org.codehaus.janino.UnitCompiler.compileError(UnitCompiler.java:12211) > at > org.codehaus.janino.UnitCompiler.compileBoolean2(UnitCompiler.java:4239) > at org.codehaus.janino.UnitCompiler.access$6600(UnitCompiler.java:215) > at > org.codehaus.janino.UnitCompiler$14.visitBinaryOperation(UnitCompiler.java:3957) > at > org.codehaus.janino.UnitCompiler$14.visitBinaryOperation(UnitCompiler.java:3935) > at org.codehaus.janino.Java$BinaryOperation.accept(Java.java:4864) > at > org.codehaus.janino.UnitCompiler.compileBoolean(UnitCompiler.java:3935) > at org.codehaus.janino.UnitCompiler.compile2(UnitCompiler.java:2475) > {code} > It's kind of hard to understand for user to refine the sql -- This message was sent by Atlassian JIRA (v7.6.14#76016)
[jira] [Commented] (CALCITE-3206) Better error message when param type incompatibility
[ https://issues.apache.org/jira/browse/CALCITE-3206?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16890610#comment-16890610 ] Danny Chan commented on CALCITE-3206: - For implicit type coercion, there is another issue CALCITE-2302, you can help to review the code if Interested. > Better error message when param type incompatibility > > > Key: CALCITE-3206 > URL: https://issues.apache.org/jira/browse/CALCITE-3206 > Project: Calcite > Issue Type: Improvement > Components: core >Reporter: jin xing >Priority: Major > Labels: pull-request-available > Time Spent: 20m > Remaining Estimate: 0h > > In current code, when we query like below > {code:java} > @Test public void test() { > CalciteAssert.model(HR_FKUK_MODEL) > .query("select * from \"events\" where \"eventid\"='123'") > .returns(CalciteAssert.outputResult()); > } > {code} > Where {{eventid}} is of INTEGER type, Calcite will throw exception as below > {code:java} > Caused by: org.codehaus.commons.compiler.CompileException: Line 13, Column > 100: Cannot compare types "int" and "java.lang.String" > at > org.codehaus.janino.UnitCompiler.compileError(UnitCompiler.java:12211) > at > org.codehaus.janino.UnitCompiler.compileBoolean2(UnitCompiler.java:4239) > at org.codehaus.janino.UnitCompiler.access$6600(UnitCompiler.java:215) > at > org.codehaus.janino.UnitCompiler$14.visitBinaryOperation(UnitCompiler.java:3957) > at > org.codehaus.janino.UnitCompiler$14.visitBinaryOperation(UnitCompiler.java:3935) > at org.codehaus.janino.Java$BinaryOperation.accept(Java.java:4864) > at > org.codehaus.janino.UnitCompiler.compileBoolean(UnitCompiler.java:3935) > at org.codehaus.janino.UnitCompiler.compile2(UnitCompiler.java:2475) > {code} > It's kind of hard to understand for user to refine the sql -- This message was sent by Atlassian JIRA (v7.6.14#76016)
[jira] [Commented] (CALCITE-3207) Bug of 'JoinRelNode Convert SqlStatement'
[ https://issues.apache.org/jira/browse/CALCITE-3207?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16890614#comment-16890614 ] Xurenhe commented on CALCITE-3207: -- Hi [~jinxing6...@126.com], thx for your comment,I update my issue with unit test. > Bug of 'JoinRelNode Convert SqlStatement' > - > > Key: CALCITE-3207 > URL: https://issues.apache.org/jira/browse/CALCITE-3207 > Project: Calcite > Issue Type: Bug > Components: core >Reporter: Xurenhe >Priority: Minor > Attachments: image-2019-07-22-23-15-32-107.png > > > Hi all > I have a problem when using calcite, which is 'relnode convert sql statement > example: > > I create a test like below > {code:java} > // RelToSqlConverterTest.java > @Test public void testLeftJoinRel2Sql() { > final RelBuilder builder = relBuilder(); > final RelNode rel = builder > .scan("EMP") > .scan("DEPT") > .join(JoinRelType.LEFT, builder.and( > builder.call(SqlStdOperatorTable.EQUALS, > builder.field(2, 0, "DEPTNO"), > builder.field(2, 1, "DEPTNO") > ), builder.call(SqlStdOperatorTable.LIKE, > builder.field(2, 1, "DNAME"), > builder.literal("ACCOUNTING" > .build(); > final String sql = toSql(rel); > final String expectedSql = "SELECT *\n" + > "FROM \"scott\".\"EMP\"\n" + > "LEFT JOIN \"scott\".\"DEPT\" ON \"EMP\".\"DEPTNO\" = > \"DEPT\".\"DEPTNO\"\n" + > "AND \"DEPT\".\"DNAME\" LIKE 'ACCOUNTING'"; > assertThat(sql, isLinux(expectedSql)); > }{code} > > this relNode cannot convert sql statement. > I try to debug it, > `org.apache.calcite.rel.rel2sql.SqlImplementor#convertConditionToSqlNode` may > forgot to handle this case, which sqlKind is SqlKind.LIKE. > If it is a bug, I am willing to fix it. > Thanks. > -- This message was sent by Atlassian JIRA (v7.6.14#76016)
[jira] [Commented] (CALCITE-3207) Bug of 'JoinRelNode Convert SqlStatement'
[ https://issues.apache.org/jira/browse/CALCITE-3207?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16890615#comment-16890615 ] Xurenhe commented on CALCITE-3207: -- [~danny0405] thx for your comment, I will make PR for fixing it later. > Bug of 'JoinRelNode Convert SqlStatement' > - > > Key: CALCITE-3207 > URL: https://issues.apache.org/jira/browse/CALCITE-3207 > Project: Calcite > Issue Type: Bug > Components: core >Reporter: Xurenhe >Priority: Minor > Attachments: image-2019-07-22-23-15-32-107.png > > > Hi all > I have a problem when using calcite, which is 'relnode convert sql statement > example: > > I create a test like below > {code:java} > // RelToSqlConverterTest.java > @Test public void testLeftJoinRel2Sql() { > final RelBuilder builder = relBuilder(); > final RelNode rel = builder > .scan("EMP") > .scan("DEPT") > .join(JoinRelType.LEFT, builder.and( > builder.call(SqlStdOperatorTable.EQUALS, > builder.field(2, 0, "DEPTNO"), > builder.field(2, 1, "DEPTNO") > ), builder.call(SqlStdOperatorTable.LIKE, > builder.field(2, 1, "DNAME"), > builder.literal("ACCOUNTING" > .build(); > final String sql = toSql(rel); > final String expectedSql = "SELECT *\n" + > "FROM \"scott\".\"EMP\"\n" + > "LEFT JOIN \"scott\".\"DEPT\" ON \"EMP\".\"DEPTNO\" = > \"DEPT\".\"DEPTNO\"\n" + > "AND \"DEPT\".\"DNAME\" LIKE 'ACCOUNTING'"; > assertThat(sql, isLinux(expectedSql)); > }{code} > > this relNode cannot convert sql statement. > I try to debug it, > `org.apache.calcite.rel.rel2sql.SqlImplementor#convertConditionToSqlNode` may > forgot to handle this case, which sqlKind is SqlKind.LIKE. > If it is a bug, I am willing to fix it. > Thanks. > -- This message was sent by Atlassian JIRA (v7.6.14#76016)
[jira] [Commented] (CALCITE-3206) Better error message when param type incompatibility
[ https://issues.apache.org/jira/browse/CALCITE-3206?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16890684#comment-16890684 ] jin xing commented on CALCITE-3206: --- THX [~danny0405] I will close this issue > Better error message when param type incompatibility > > > Key: CALCITE-3206 > URL: https://issues.apache.org/jira/browse/CALCITE-3206 > Project: Calcite > Issue Type: Improvement > Components: core >Reporter: jin xing >Priority: Major > Labels: pull-request-available > Time Spent: 40m > Remaining Estimate: 0h > > In current code, when we query like below > {code:java} > @Test public void test() { > CalciteAssert.model(HR_FKUK_MODEL) > .query("select * from \"events\" where \"eventid\"='123'") > .returns(CalciteAssert.outputResult()); > } > {code} > Where {{eventid}} is of INTEGER type, Calcite will throw exception as below > {code:java} > Caused by: org.codehaus.commons.compiler.CompileException: Line 13, Column > 100: Cannot compare types "int" and "java.lang.String" > at > org.codehaus.janino.UnitCompiler.compileError(UnitCompiler.java:12211) > at > org.codehaus.janino.UnitCompiler.compileBoolean2(UnitCompiler.java:4239) > at org.codehaus.janino.UnitCompiler.access$6600(UnitCompiler.java:215) > at > org.codehaus.janino.UnitCompiler$14.visitBinaryOperation(UnitCompiler.java:3957) > at > org.codehaus.janino.UnitCompiler$14.visitBinaryOperation(UnitCompiler.java:3935) > at org.codehaus.janino.Java$BinaryOperation.accept(Java.java:4864) > at > org.codehaus.janino.UnitCompiler.compileBoolean(UnitCompiler.java:3935) > at org.codehaus.janino.UnitCompiler.compile2(UnitCompiler.java:2475) > {code} > It's kind of hard to understand for user to refine the sql -- This message was sent by Atlassian JIRA (v7.6.14#76016)
[jira] [Closed] (CALCITE-3206) Better error message when param type incompatibility
[ https://issues.apache.org/jira/browse/CALCITE-3206?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] jin xing closed CALCITE-3206. - Resolution: Duplicate > Better error message when param type incompatibility > > > Key: CALCITE-3206 > URL: https://issues.apache.org/jira/browse/CALCITE-3206 > Project: Calcite > Issue Type: Improvement > Components: core >Reporter: jin xing >Priority: Major > Labels: pull-request-available > Time Spent: 40m > Remaining Estimate: 0h > > In current code, when we query like below > {code:java} > @Test public void test() { > CalciteAssert.model(HR_FKUK_MODEL) > .query("select * from \"events\" where \"eventid\"='123'") > .returns(CalciteAssert.outputResult()); > } > {code} > Where {{eventid}} is of INTEGER type, Calcite will throw exception as below > {code:java} > Caused by: org.codehaus.commons.compiler.CompileException: Line 13, Column > 100: Cannot compare types "int" and "java.lang.String" > at > org.codehaus.janino.UnitCompiler.compileError(UnitCompiler.java:12211) > at > org.codehaus.janino.UnitCompiler.compileBoolean2(UnitCompiler.java:4239) > at org.codehaus.janino.UnitCompiler.access$6600(UnitCompiler.java:215) > at > org.codehaus.janino.UnitCompiler$14.visitBinaryOperation(UnitCompiler.java:3957) > at > org.codehaus.janino.UnitCompiler$14.visitBinaryOperation(UnitCompiler.java:3935) > at org.codehaus.janino.Java$BinaryOperation.accept(Java.java:4864) > at > org.codehaus.janino.UnitCompiler.compileBoolean(UnitCompiler.java:3935) > at org.codehaus.janino.UnitCompiler.compile2(UnitCompiler.java:2475) > {code} > It's kind of hard to understand for user to refine the sql -- This message was sent by Atlassian JIRA (v7.6.14#76016)