[jira] [Assigned] (CALCITE-985) Validate MERGE
[ https://issues.apache.org/jira/browse/CALCITE-985?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Jiajun Xie reassigned CALCITE-985: -- Assignee: Jiajun Xie > Validate MERGE > -- > > Key: CALCITE-985 > URL: https://issues.apache.org/jira/browse/CALCITE-985 > Project: Calcite > Issue Type: Bug >Reporter: Julian Hyde >Assignee: Jiajun Xie >Priority: Major > > Now CALCITE-974 is fixed, we can validate INSERT, UPDATE and DELETE; > sql-to-rel conversion also succeeds, although I'm not sure we can execute. > MERGE does not yet pass validation. > I have added SqlToRelConverterTest.testMerge; we need one or two validator > fixes to make it pass. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-5805) SqlValidatorImpl throws AssertionError while validating MERGE statement
[
https://issues.apache.org/jira/browse/CALCITE-5805?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17738341#comment-17738341
]
Jiajun Xie commented on CALCITE-5805:
-
Thanks for your information.
It seems that I need to add more unit tests to ensure the completion of this
work(CALCITE-985).
> SqlValidatorImpl throws AssertionError while validating MERGE statement
> ---
>
> Key: CALCITE-5805
> URL: https://issues.apache.org/jira/browse/CALCITE-5805
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Jiajun Xie
>Assignee: Jiajun Xie
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.35.0
>
>
> For the unit test.
> {code:java}
> final String sql = "merge into empnullables e "
> + "using (select * from emp where deptno is null) t "
> + "on e.empno = t.empno "
> + "when matched then update "
> + "set ename = t.ename, deptno = t.deptno, sal = t.sal * .1 "
> + "when not matched then insert (empno, ename, deptno, sal) "
> + "values(t.empno, t.ename, 10, t.sal * .15)";
> sql(sql).ok(); // Expected it is ok, but failed{code}
> * If we enable `assert`, the error will be *AssertionError.*
> {code:java}
> java.lang.AssertionError
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3741)
> at
> org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:61)
> at
> org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:88)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:1144)
> at
> org.apache.calcite.sql.validate.AbstractNamespace.getRowType(AbstractNamespace.java:119)
> at
> org.apache.calcite.sql.validate.SelectNamespace.getRowType(SelectNamespace.java:32)
> at org.apache.calcite.sql.validate.ListScope.resolve(ListScope.java:203)
> at
> org.apache.calcite.sql.validate.DelegatingScope.fullyQualify(DelegatingScope.java:331)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visit(SqlValidatorImpl.java:6672)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visit(SqlValidatorImpl.java:6652)
> at org.apache.calcite.sql.SqlIdentifier.accept(SqlIdentifier.java:323)
> at
> org.apache.calcite.sql.util.SqlShuttle$CallCopyingArgHandler.visitChild(SqlShuttle.java:134)
> at
> org.apache.calcite.sql.util.SqlShuttle$CallCopyingArgHandler.visitChild(SqlShuttle.java:101)
> at org.apache.calcite.sql.SqlOperator.acceptCall(SqlOperator.java:956)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visitScoped(SqlValidatorImpl.java:6696)
> at
> org.apache.calcite.sql.validate.SqlScopedShuttle.visit(SqlScopedShuttle.java:54)
> at
> org.apache.calcite.sql.validate.SqlScopedShuttle.visit(SqlScopedShuttle.java:37)
> at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:166)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.go(SqlValidatorImpl.java:6661)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.expand(SqlValidatorImpl.java:6235)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateJoin(SqlValidatorImpl.java:3570)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:3444)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3792)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateMerge(SqlValidatorImpl.java:5263)
> {code}
> * If we disable `assert`, the error will be *NullPointerException.*
> {code:java}
> java.lang.NullPointerException: rowType
> at java.util.Objects.requireNonNull(Objects.java:228)
> at
> org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:62)
> at
> org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:88)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:1144)
> at
> org.apache.calcite.sql.validate.AbstractNamespace.getRowType(AbstractNamespace.java:119)
> at
> org.apache.calcite.sql.validate.SelectNamespace.getRowType(SelectNamespace.java:32)
> at org.apache.calcite.sql.validate.ListScope.resolve(ListScope.java:203)
> at
> org.apache.calcite.sql.validate.DelegatingScope.fullyQualify(DelegatingScope.java:331)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visit(SqlValidatorImpl.java:6672)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visit(SqlValidatorImpl.java:6652)
> at org.apache.calcite.sql.SqlIdentifier.accept(SqlIdentifier.java:323)
> at
>
[jira] [Commented] (CALCITE-5805) SqlValidatorImpl throws AssertionError while validating MERGE statement
[
https://issues.apache.org/jira/browse/CALCITE-5805?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17738078#comment-17738078
]
Jiajun Xie commented on CALCITE-5805:
-
`select * from emp where deptno is null` is a SqlNode that has been registered
twice.
I think we should avoid duplicate registrations in
`SqlValidatorImpl#registerNamespace`.
Here is PR: https://github.com/apache/calcite/pull/3283
> SqlValidatorImpl throws AssertionError while validating MERGE statement
> ---
>
> Key: CALCITE-5805
> URL: https://issues.apache.org/jira/browse/CALCITE-5805
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Jiajun Xie
>Assignee: Jiajun Xie
>Priority: Major
> Labels: pull-request-available
>
> For the unit test.
> {code:java}
> final String sql = "merge into empnullables e "
> + "using (select * from emp where deptno is null) t "
> + "on e.empno = t.empno "
> + "when matched then update "
> + "set ename = t.ename, deptno = t.deptno, sal = t.sal * .1 "
> + "when not matched then insert (empno, ename, deptno, sal) "
> + "values(t.empno, t.ename, 10, t.sal * .15)";
> sql(sql).ok(); // Expected it is ok, but failed{code}
> * If we enable `assert`, the error will be *AssertionError.*
> {code:java}
> java.lang.AssertionError
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3741)
> at
> org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:61)
> at
> org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:88)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:1144)
> at
> org.apache.calcite.sql.validate.AbstractNamespace.getRowType(AbstractNamespace.java:119)
> at
> org.apache.calcite.sql.validate.SelectNamespace.getRowType(SelectNamespace.java:32)
> at org.apache.calcite.sql.validate.ListScope.resolve(ListScope.java:203)
> at
> org.apache.calcite.sql.validate.DelegatingScope.fullyQualify(DelegatingScope.java:331)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visit(SqlValidatorImpl.java:6672)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visit(SqlValidatorImpl.java:6652)
> at org.apache.calcite.sql.SqlIdentifier.accept(SqlIdentifier.java:323)
> at
> org.apache.calcite.sql.util.SqlShuttle$CallCopyingArgHandler.visitChild(SqlShuttle.java:134)
> at
> org.apache.calcite.sql.util.SqlShuttle$CallCopyingArgHandler.visitChild(SqlShuttle.java:101)
> at org.apache.calcite.sql.SqlOperator.acceptCall(SqlOperator.java:956)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visitScoped(SqlValidatorImpl.java:6696)
> at
> org.apache.calcite.sql.validate.SqlScopedShuttle.visit(SqlScopedShuttle.java:54)
> at
> org.apache.calcite.sql.validate.SqlScopedShuttle.visit(SqlScopedShuttle.java:37)
> at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:166)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.go(SqlValidatorImpl.java:6661)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.expand(SqlValidatorImpl.java:6235)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateJoin(SqlValidatorImpl.java:3570)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:3444)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3792)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateMerge(SqlValidatorImpl.java:5263)
> {code}
> * If we disable `assert`, the error will be *NullPointerException.*
> {code:java}
> java.lang.NullPointerException: rowType
> at java.util.Objects.requireNonNull(Objects.java:228)
> at
> org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:62)
> at
> org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:88)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:1144)
> at
> org.apache.calcite.sql.validate.AbstractNamespace.getRowType(AbstractNamespace.java:119)
> at
> org.apache.calcite.sql.validate.SelectNamespace.getRowType(SelectNamespace.java:32)
> at org.apache.calcite.sql.validate.ListScope.resolve(ListScope.java:203)
> at
> org.apache.calcite.sql.validate.DelegatingScope.fullyQualify(DelegatingScope.java:331)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visit(SqlValidatorImpl.java:6672)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visit(SqlValidatorImpl.java:6652)
> at
[jira] [Updated] (CALCITE-5805) SqlValidatorImpl throws AssertionError while validating MERGE statement
[
https://issues.apache.org/jira/browse/CALCITE-5805?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Jiajun Xie updated CALCITE-5805:
Description:
For the unit test.
{code:java}
final String sql = "merge into empnullables e "
+ "using (select * from emp where deptno is null) t "
+ "on e.empno = t.empno "
+ "when matched then update "
+ "set ename = t.ename, deptno = t.deptno, sal = t.sal * .1 "
+ "when not matched then insert (empno, ename, deptno, sal) "
+ "values(t.empno, t.ename, 10, t.sal * .15)";
sql(sql).ok(); // Expected it is ok, but failed{code}
* If we enable `assert`, the error will be *AssertionError.*
{code:java}
java.lang.AssertionError
at
org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3741)
at
org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:61)
at
org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:88)
at
org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:1144)
at
org.apache.calcite.sql.validate.AbstractNamespace.getRowType(AbstractNamespace.java:119)
at
org.apache.calcite.sql.validate.SelectNamespace.getRowType(SelectNamespace.java:32)
at org.apache.calcite.sql.validate.ListScope.resolve(ListScope.java:203)
at
org.apache.calcite.sql.validate.DelegatingScope.fullyQualify(DelegatingScope.java:331)
at
org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visit(SqlValidatorImpl.java:6672)
at
org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visit(SqlValidatorImpl.java:6652)
at org.apache.calcite.sql.SqlIdentifier.accept(SqlIdentifier.java:323)
at
org.apache.calcite.sql.util.SqlShuttle$CallCopyingArgHandler.visitChild(SqlShuttle.java:134)
at
org.apache.calcite.sql.util.SqlShuttle$CallCopyingArgHandler.visitChild(SqlShuttle.java:101)
at org.apache.calcite.sql.SqlOperator.acceptCall(SqlOperator.java:956)
at
org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visitScoped(SqlValidatorImpl.java:6696)
at
org.apache.calcite.sql.validate.SqlScopedShuttle.visit(SqlScopedShuttle.java:54)
at
org.apache.calcite.sql.validate.SqlScopedShuttle.visit(SqlScopedShuttle.java:37)
at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:166)
at
org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.go(SqlValidatorImpl.java:6661)
at
org.apache.calcite.sql.validate.SqlValidatorImpl.expand(SqlValidatorImpl.java:6235)
at
org.apache.calcite.sql.validate.SqlValidatorImpl.validateJoin(SqlValidatorImpl.java:3570)
at
org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:3444)
at
org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3792)
at
org.apache.calcite.sql.validate.SqlValidatorImpl.validateMerge(SqlValidatorImpl.java:5263)
{code}
* If we disable `assert`, the error will be *NullPointerException.*
{code:java}
java.lang.NullPointerException: rowType
at java.util.Objects.requireNonNull(Objects.java:228)
at
org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:62)
at
org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:88)
at
org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:1144)
at
org.apache.calcite.sql.validate.AbstractNamespace.getRowType(AbstractNamespace.java:119)
at
org.apache.calcite.sql.validate.SelectNamespace.getRowType(SelectNamespace.java:32)
at org.apache.calcite.sql.validate.ListScope.resolve(ListScope.java:203)
at
org.apache.calcite.sql.validate.DelegatingScope.fullyQualify(DelegatingScope.java:331)
at
org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visit(SqlValidatorImpl.java:6672)
at
org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visit(SqlValidatorImpl.java:6652)
at org.apache.calcite.sql.SqlIdentifier.accept(SqlIdentifier.java:323)
at
org.apache.calcite.sql.util.SqlShuttle$CallCopyingArgHandler.visitChild(SqlShuttle.java:134)
at
org.apache.calcite.sql.util.SqlShuttle$CallCopyingArgHandler.visitChild(SqlShuttle.java:101)
at org.apache.calcite.sql.SqlOperator.acceptCall(SqlOperator.java:956)
at
org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visitScoped(SqlValidatorImpl.java:6696)
at
org.apache.calcite.sql.validate.SqlScopedShuttle.visit(SqlScopedShuttle.java:54)
at
org.apache.calcite.sql.validate.SqlScopedShuttle.visit(SqlScopedShuttle.java:37)
at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:166)
at
org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.go(SqlValidatorImpl.java:6661)
at
org.apache.calcite.sql.validate.SqlValidatorImpl.expand(SqlValidatorImpl.java:6235)
at
[jira] [Updated] (CALCITE-5805) SqlValidatorImpl throws AssertionError while validating MERGE statement
[
https://issues.apache.org/jira/browse/CALCITE-5805?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Jiajun Xie updated CALCITE-5805:
Description:
For the unit test.
{code:java}
final String sql = "merge into empnullables e "
+ "using (select * from emp where deptno is null) t "
+ "on e.empno = t.empno "
+ "when matched then update "
+ "set ename = t.ename, deptno = t.deptno, sal = t.sal * .1 "
+ "when not matched then insert (empno, ename, deptno, sal) "
+ "values(t.empno, t.ename, 10, t.sal * .15)";
sql(sql).ok(); // Expected it is ok, but failed{code}
* If we enable `assert`, the error will be {*}AssertionError{*}{*}{*}
{code:java}
java.lang.AssertionError
at
org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3741)
at
org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:61)
at
org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:88)
at
org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:1144)
at
org.apache.calcite.sql.validate.AbstractNamespace.getRowType(AbstractNamespace.java:119)
at
org.apache.calcite.sql.validate.SelectNamespace.getRowType(SelectNamespace.java:32)
at org.apache.calcite.sql.validate.ListScope.resolve(ListScope.java:203)
at
org.apache.calcite.sql.validate.DelegatingScope.fullyQualify(DelegatingScope.java:331)
at
org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visit(SqlValidatorImpl.java:6672)
at
org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visit(SqlValidatorImpl.java:6652)
at org.apache.calcite.sql.SqlIdentifier.accept(SqlIdentifier.java:323)
at
org.apache.calcite.sql.util.SqlShuttle$CallCopyingArgHandler.visitChild(SqlShuttle.java:134)
at
org.apache.calcite.sql.util.SqlShuttle$CallCopyingArgHandler.visitChild(SqlShuttle.java:101)
at org.apache.calcite.sql.SqlOperator.acceptCall(SqlOperator.java:956)
at
org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visitScoped(SqlValidatorImpl.java:6696)
at
org.apache.calcite.sql.validate.SqlScopedShuttle.visit(SqlScopedShuttle.java:54)
at
org.apache.calcite.sql.validate.SqlScopedShuttle.visit(SqlScopedShuttle.java:37)
at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:166)
at
org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.go(SqlValidatorImpl.java:6661)
at
org.apache.calcite.sql.validate.SqlValidatorImpl.expand(SqlValidatorImpl.java:6235)
at
org.apache.calcite.sql.validate.SqlValidatorImpl.validateJoin(SqlValidatorImpl.java:3570)
at
org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:3444)
at
org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3792)
at
org.apache.calcite.sql.validate.SqlValidatorImpl.validateMerge(SqlValidatorImpl.java:5263)
{code}
* If we disable `assert`, the error will be *NullPointerException*
{code:java}
java.lang.NullPointerException: rowType
at java.util.Objects.requireNonNull(Objects.java:228)
at
org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:62)
at
org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:88)
at
org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:1144)
at
org.apache.calcite.sql.validate.AbstractNamespace.getRowType(AbstractNamespace.java:119)
at
org.apache.calcite.sql.validate.SelectNamespace.getRowType(SelectNamespace.java:32)
at org.apache.calcite.sql.validate.ListScope.resolve(ListScope.java:203)
at
org.apache.calcite.sql.validate.DelegatingScope.fullyQualify(DelegatingScope.java:331)
at
org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visit(SqlValidatorImpl.java:6672)
at
org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visit(SqlValidatorImpl.java:6652)
at org.apache.calcite.sql.SqlIdentifier.accept(SqlIdentifier.java:323)
at
org.apache.calcite.sql.util.SqlShuttle$CallCopyingArgHandler.visitChild(SqlShuttle.java:134)
at
org.apache.calcite.sql.util.SqlShuttle$CallCopyingArgHandler.visitChild(SqlShuttle.java:101)
at org.apache.calcite.sql.SqlOperator.acceptCall(SqlOperator.java:956)
at
org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visitScoped(SqlValidatorImpl.java:6696)
at
org.apache.calcite.sql.validate.SqlScopedShuttle.visit(SqlScopedShuttle.java:54)
at
org.apache.calcite.sql.validate.SqlScopedShuttle.visit(SqlScopedShuttle.java:37)
at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:166)
at
org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.go(SqlValidatorImpl.java:6661)
at
org.apache.calcite.sql.validate.SqlValidatorImpl.expand(SqlValidatorImpl.java:6235)
at
[jira] [Updated] (CALCITE-5805) SqlValidatorImpl throws AssertionError while validating MERGE statement
[
https://issues.apache.org/jira/browse/CALCITE-5805?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Jiajun Xie updated CALCITE-5805:
Summary: SqlValidatorImpl throws AssertionError while validating MERGE
statement (was: SelectNamespace throws NullPointerException while validating
MERGE statement)
> SqlValidatorImpl throws AssertionError while validating MERGE statement
> ---
>
> Key: CALCITE-5805
> URL: https://issues.apache.org/jira/browse/CALCITE-5805
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Jiajun Xie
>Assignee: Jiajun Xie
>Priority: Major
>
> {code:java}
> final String sql = "merge into empnullables e "
> + "using (select * from emp where deptno is null) t "
> + "on e.empno = t.empno "
> + "when matched then update "
> + "set ename = t.ename, deptno = t.deptno, sal = t.sal * .1 "
> + "when not matched then insert (empno, ename, deptno, sal) "
> + "values(t.empno, t.ename, 10, t.sal * .15)";
> sql(sql).ok(); // Expected it is ok, but failed{code}
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-5805) SelectNamespace throws NullPointerException while validating MERGE statement
Jiajun Xie created CALCITE-5805:
---
Summary: SelectNamespace throws NullPointerException while
validating MERGE statement
Key: CALCITE-5805
URL: https://issues.apache.org/jira/browse/CALCITE-5805
Project: Calcite
Issue Type: Bug
Components: core
Reporter: Jiajun Xie
Assignee: Jiajun Xie
{code:java}
final String sql = "merge into empnullables e "
+ "using (select * from emp where deptno is null) t "
+ "on e.empno = t.empno "
+ "when matched then update "
+ "set ename = t.ename, deptno = t.deptno, sal = t.sal * .1 "
+ "when not matched then insert (empno, ename, deptno, sal) "
+ "values(t.empno, t.ename, 10, t.sal * .15)";
sql(sql).ok(); // Expected it is ok, but failed{code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5796) DELETE statement is not effective
[
https://issues.apache.org/jira/browse/CALCITE-5796?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17736693#comment-17736693
]
Jiajun Xie commented on CALCITE-5796:
-
When we execute `JdbcFrontLinqBackTest#testDelete`(Effective UT), the row is
employee class, not object array.
I think we should use `EnumerableDefaults#except` to delete row in
`EnumerableTableModify#implement`.
> DELETE statement is not effective
> --
>
> Key: CALCITE-5796
> URL: https://issues.apache.org/jira/browse/CALCITE-5796
> Project: Calcite
> Issue Type: Bug
> Components: core, linq4j
>Reporter: Jiajun Xie
>Priority: Major
> Attachments: image-2023-06-24-18-27-57-722.png
>
>
> I try to use `DELETE FROM t`, but the table was not affected.
>
> {code:java}
> static void erase(SqlIdentifier name, CalcitePrepare.Context context) {
> // Generate, prepare and execute an "DELETE FROM table" statement.
> // (It's a bit inefficient that we convert from SqlNode to SQL and back
> // again.)
> final FrameworkConfig config = Frameworks.newConfigBuilder()
> .defaultSchema(context.getRootSchema().plus())
> .build();
> final Planner planner = Frameworks.getPlanner(config);
> try {
> final StringBuilder buf = new StringBuilder();
> final SqlWriterConfig writerConfig =
> SqlPrettyWriter.config().withAlwaysUseParentheses(false);
> final SqlPrettyWriter w = new SqlPrettyWriter(writerConfig, buf);
> buf.append("DELETE FROM ");
> name.unparse(w, 0, 0);
> final String sql = buf.toString();
> final SqlNode query1 = planner.parse(sql);
> final SqlNode query2 = planner.validate(query1);
> final RelRoot r = planner.rel(query2);
> final PreparedStatement prepare =
> context.getRelRunner().prepareStatement(r.rel);
> int rowCount = prepare.executeUpdate();
> Util.discard(rowCount);
> prepare.close();
> } catch (SqlParseException | ValidationException
>| RelConversionException | SQLException e) {
> throw Util.throwAsRuntime(e);
> }
> } {code}
>
> Because we use `EnumerableDefaults#remove()` to delete target rows.
> The rows is object array, they can't match.
> !image-2023-06-24-18-27-57-722.png!
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Assigned] (CALCITE-5796) DELETE statement is not effective
[
https://issues.apache.org/jira/browse/CALCITE-5796?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Jiajun Xie reassigned CALCITE-5796:
---
Assignee: Jiajun Xie
> DELETE statement is not effective
> --
>
> Key: CALCITE-5796
> URL: https://issues.apache.org/jira/browse/CALCITE-5796
> Project: Calcite
> Issue Type: Bug
> Components: core, linq4j
>Reporter: Jiajun Xie
>Assignee: Jiajun Xie
>Priority: Major
> Attachments: image-2023-06-24-18-27-57-722.png
>
>
> I try to use `DELETE FROM t`, but the table was not affected.
>
> {code:java}
> static void erase(SqlIdentifier name, CalcitePrepare.Context context) {
> // Generate, prepare and execute an "DELETE FROM table" statement.
> // (It's a bit inefficient that we convert from SqlNode to SQL and back
> // again.)
> final FrameworkConfig config = Frameworks.newConfigBuilder()
> .defaultSchema(context.getRootSchema().plus())
> .build();
> final Planner planner = Frameworks.getPlanner(config);
> try {
> final StringBuilder buf = new StringBuilder();
> final SqlWriterConfig writerConfig =
> SqlPrettyWriter.config().withAlwaysUseParentheses(false);
> final SqlPrettyWriter w = new SqlPrettyWriter(writerConfig, buf);
> buf.append("DELETE FROM ");
> name.unparse(w, 0, 0);
> final String sql = buf.toString();
> final SqlNode query1 = planner.parse(sql);
> final SqlNode query2 = planner.validate(query1);
> final RelRoot r = planner.rel(query2);
> final PreparedStatement prepare =
> context.getRelRunner().prepareStatement(r.rel);
> int rowCount = prepare.executeUpdate();
> Util.discard(rowCount);
> prepare.close();
> } catch (SqlParseException | ValidationException
>| RelConversionException | SQLException e) {
> throw Util.throwAsRuntime(e);
> }
> } {code}
>
> Because we use `EnumerableDefaults#remove()` to delete target rows.
> The rows is object array, they can't match.
> !image-2023-06-24-18-27-57-722.png!
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-5796) DELETE statement is not effective
[
https://issues.apache.org/jira/browse/CALCITE-5796?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Jiajun Xie updated CALCITE-5796:
Environment: (was: I try to use `DELETE FROM t`, but the table was not
affected.
{code:java}
static void erase(SqlIdentifier name, CalcitePrepare.Context context) {
// Generate, prepare and execute an "DELETE FROM table" statement.
// (It's a bit inefficient that we convert from SqlNode to SQL and back
// again.)
final FrameworkConfig config = Frameworks.newConfigBuilder()
.defaultSchema(context.getRootSchema().plus())
.build();
final Planner planner = Frameworks.getPlanner(config);
try {
final StringBuilder buf = new StringBuilder();
final SqlWriterConfig writerConfig =
SqlPrettyWriter.config().withAlwaysUseParentheses(false);
final SqlPrettyWriter w = new SqlPrettyWriter(writerConfig, buf);
buf.append("DELETE FROM ");
name.unparse(w, 0, 0);
final String sql = buf.toString();
final SqlNode query1 = planner.parse(sql);
final SqlNode query2 = planner.validate(query1);
final RelRoot r = planner.rel(query2);
final PreparedStatement prepare =
context.getRelRunner().prepareStatement(r.rel);
int rowCount = prepare.executeUpdate();
Util.discard(rowCount);
prepare.close();
} catch (SqlParseException | ValidationException
| RelConversionException | SQLException e) {
throw Util.throwAsRuntime(e);
}
} {code}
Because we use `EnumerableDefaults#remove()` to delete target rows.
The rows is object array, they can't match.
!image-2023-06-24-18-27-57-722.png!)
> DELETE statement is not effective
> --
>
> Key: CALCITE-5796
> URL: https://issues.apache.org/jira/browse/CALCITE-5796
> Project: Calcite
> Issue Type: Bug
> Components: core, linq4j
>Reporter: Jiajun Xie
>Priority: Major
> Attachments: image-2023-06-24-18-27-57-722.png
>
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-5796) DELETE statement is not effective
[
https://issues.apache.org/jira/browse/CALCITE-5796?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Jiajun Xie updated CALCITE-5796:
Description:
I try to use `DELETE FROM t`, but the table was not affected.
{code:java}
static void erase(SqlIdentifier name, CalcitePrepare.Context context) {
// Generate, prepare and execute an "DELETE FROM table" statement.
// (It's a bit inefficient that we convert from SqlNode to SQL and back
// again.)
final FrameworkConfig config = Frameworks.newConfigBuilder()
.defaultSchema(context.getRootSchema().plus())
.build();
final Planner planner = Frameworks.getPlanner(config);
try {
final StringBuilder buf = new StringBuilder();
final SqlWriterConfig writerConfig =
SqlPrettyWriter.config().withAlwaysUseParentheses(false);
final SqlPrettyWriter w = new SqlPrettyWriter(writerConfig, buf);
buf.append("DELETE FROM ");
name.unparse(w, 0, 0);
final String sql = buf.toString();
final SqlNode query1 = planner.parse(sql);
final SqlNode query2 = planner.validate(query1);
final RelRoot r = planner.rel(query2);
final PreparedStatement prepare =
context.getRelRunner().prepareStatement(r.rel);
int rowCount = prepare.executeUpdate();
Util.discard(rowCount);
prepare.close();
} catch (SqlParseException | ValidationException
| RelConversionException | SQLException e) {
throw Util.throwAsRuntime(e);
}
} {code}
Because we use `EnumerableDefaults#remove()` to delete target rows.
The rows is object array, they can't match.
!image-2023-06-24-18-27-57-722.png!
> DELETE statement is not effective
> --
>
> Key: CALCITE-5796
> URL: https://issues.apache.org/jira/browse/CALCITE-5796
> Project: Calcite
> Issue Type: Bug
> Components: core, linq4j
>Reporter: Jiajun Xie
>Priority: Major
> Attachments: image-2023-06-24-18-27-57-722.png
>
>
> I try to use `DELETE FROM t`, but the table was not affected.
>
> {code:java}
> static void erase(SqlIdentifier name, CalcitePrepare.Context context) {
> // Generate, prepare and execute an "DELETE FROM table" statement.
> // (It's a bit inefficient that we convert from SqlNode to SQL and back
> // again.)
> final FrameworkConfig config = Frameworks.newConfigBuilder()
> .defaultSchema(context.getRootSchema().plus())
> .build();
> final Planner planner = Frameworks.getPlanner(config);
> try {
> final StringBuilder buf = new StringBuilder();
> final SqlWriterConfig writerConfig =
> SqlPrettyWriter.config().withAlwaysUseParentheses(false);
> final SqlPrettyWriter w = new SqlPrettyWriter(writerConfig, buf);
> buf.append("DELETE FROM ");
> name.unparse(w, 0, 0);
> final String sql = buf.toString();
> final SqlNode query1 = planner.parse(sql);
> final SqlNode query2 = planner.validate(query1);
> final RelRoot r = planner.rel(query2);
> final PreparedStatement prepare =
> context.getRelRunner().prepareStatement(r.rel);
> int rowCount = prepare.executeUpdate();
> Util.discard(rowCount);
> prepare.close();
> } catch (SqlParseException | ValidationException
>| RelConversionException | SQLException e) {
> throw Util.throwAsRuntime(e);
> }
> } {code}
>
> Because we use `EnumerableDefaults#remove()` to delete target rows.
> The rows is object array, they can't match.
> !image-2023-06-24-18-27-57-722.png!
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-5796) DELETE statement is not effective
Jiajun Xie created CALCITE-5796:
---
Summary: DELETE statement is not effective
Key: CALCITE-5796
URL: https://issues.apache.org/jira/browse/CALCITE-5796
Project: Calcite
Issue Type: Bug
Components: core, linq4j
Environment: I try to use `DELETE FROM t`, but the table was not
affected.
{code:java}
static void erase(SqlIdentifier name, CalcitePrepare.Context context) {
// Generate, prepare and execute an "DELETE FROM table" statement.
// (It's a bit inefficient that we convert from SqlNode to SQL and back
// again.)
final FrameworkConfig config = Frameworks.newConfigBuilder()
.defaultSchema(context.getRootSchema().plus())
.build();
final Planner planner = Frameworks.getPlanner(config);
try {
final StringBuilder buf = new StringBuilder();
final SqlWriterConfig writerConfig =
SqlPrettyWriter.config().withAlwaysUseParentheses(false);
final SqlPrettyWriter w = new SqlPrettyWriter(writerConfig, buf);
buf.append("DELETE FROM ");
name.unparse(w, 0, 0);
final String sql = buf.toString();
final SqlNode query1 = planner.parse(sql);
final SqlNode query2 = planner.validate(query1);
final RelRoot r = planner.rel(query2);
final PreparedStatement prepare =
context.getRelRunner().prepareStatement(r.rel);
int rowCount = prepare.executeUpdate();
Util.discard(rowCount);
prepare.close();
} catch (SqlParseException | ValidationException
| RelConversionException | SQLException e) {
throw Util.throwAsRuntime(e);
}
} {code}
Because we use `EnumerableDefaults#remove()` to delete target rows.
The rows is object array, they can't match.
!image-2023-06-24-18-27-57-722.png!
Reporter: Jiajun Xie
Attachments: image-2023-06-24-18-27-57-722.png
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-5688) Support TRUNCATE TABLE DDL statement in server module
[
https://issues.apache.org/jira/browse/CALCITE-5688?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17735957#comment-17735957
]
Jiajun Xie edited comment on CALCITE-5688 at 6/22/23 3:06 AM:
--
I implemented it in server module and changed the Jira summary.
was (Author: jiajunbernoulli):
I implement it in the server module and change the Jira summary.
> Support TRUNCATE TABLE DDL statement in server module
> -
>
> Key: CALCITE-5688
> URL: https://issues.apache.org/jira/browse/CALCITE-5688
> Project: Calcite
> Issue Type: Improvement
> Components: server
>Reporter: Sumeet
>Priority: Minor
> Labels: pull-request-available
>
> Calcite should support parse truncate syntax like SQL2011
> {code:java}
> ::=
> TRUNCATE TABLE [ ]
> ::=
> CONTINUE IDENTITY
> | RESTART IDENTITY {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-5688) Support TRUNCATE TABLE DDL statement in server module
[
https://issues.apache.org/jira/browse/CALCITE-5688?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Jiajun Xie updated CALCITE-5688:
Description:
Calcite should support parse truncate syntax like SQL2011
{code:java}
::=
TRUNCATE TABLE [ ]
::=
CONTINUE IDENTITY
| RESTART IDENTITY {code}
> Support TRUNCATE TABLE DDL statement in server module
> -
>
> Key: CALCITE-5688
> URL: https://issues.apache.org/jira/browse/CALCITE-5688
> Project: Calcite
> Issue Type: Improvement
> Components: server
>Reporter: Sumeet
>Priority: Minor
> Labels: pull-request-available
>
> Calcite should support parse truncate syntax like SQL2011
> {code:java}
> ::=
> TRUNCATE TABLE [ ]
> ::=
> CONTINUE IDENTITY
> | RESTART IDENTITY {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5688) Support TRUNCATE TABLE DDL statement in server module
[
https://issues.apache.org/jira/browse/CALCITE-5688?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17735957#comment-17735957
]
Jiajun Xie commented on CALCITE-5688:
-
I implement it in the server module and change the Jira summary.
> Support TRUNCATE TABLE DDL statement in server module
> -
>
> Key: CALCITE-5688
> URL: https://issues.apache.org/jira/browse/CALCITE-5688
> Project: Calcite
> Issue Type: Improvement
> Components: server
>Reporter: Sumeet
>Priority: Minor
> Labels: pull-request-available
>
> Calcite should support parse truncate syntax like SQL2011
> {code:java}
> ::=
> TRUNCATE TABLE [ ]
> ::=
> CONTINUE IDENTITY
> | RESTART IDENTITY {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-5688) Support TRUNCATE TABLE DDL statement in server module
[ https://issues.apache.org/jira/browse/CALCITE-5688?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Jiajun Xie updated CALCITE-5688: Component/s: server (was: babel) (was: core) > Support TRUNCATE TABLE DDL statement in server module > - > > Key: CALCITE-5688 > URL: https://issues.apache.org/jira/browse/CALCITE-5688 > Project: Calcite > Issue Type: Improvement > Components: server >Reporter: Sumeet >Priority: Minor > Labels: pull-request-available > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-5688) Support TRUNCATE TABLE DDL statement in server module
[ https://issues.apache.org/jira/browse/CALCITE-5688?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Jiajun Xie updated CALCITE-5688: Issue Type: Improvement (was: Task) Summary: Support TRUNCATE TABLE DDL statement in server module (was: Support TRUNCATE TABLE DDL statement in Babel parser) > Support TRUNCATE TABLE DDL statement in server module > - > > Key: CALCITE-5688 > URL: https://issues.apache.org/jira/browse/CALCITE-5688 > Project: Calcite > Issue Type: Improvement > Components: babel, core >Reporter: Sumeet >Priority: Minor > Labels: pull-request-available > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Resolved] (CALCITE-5751) Add ARRAY_APPEND, ARRAY_POSITION, ARRAY_REMOVE ARRAY_PREPEND for Spark dialect
[
https://issues.apache.org/jira/browse/CALCITE-5751?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Jiajun Xie resolved CALCITE-5751.
-
Resolution: Fixed
> Add ARRAY_APPEND, ARRAY_POSITION, ARRAY_REMOVE ARRAY_PREPEND for Spark dialect
> --
>
> Key: CALCITE-5751
> URL: https://issues.apache.org/jira/browse/CALCITE-5751
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.35.0
>Reporter: jackylau
>Assignee: jackylau
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.35.0
>
>
> h3.
> [array_append|https://spark.apache.org/docs/latest/api/sql/index.html#array_append]
> array_append(array, element) - Add the element at the end of the array passed
> as first argument. Type of element should be similar to type of the elements
> of the array. Null element is also appended into the array. But if the array
> passed, is NULL output is NULL
> *Examples:*
>
> {code:java}
> {code}
> *> SELECT array_append(array('b', 'd', 'c', 'a'), 'd');
> ["b","d","c","a","d"]
> > SELECT array_append(array(1, 2, 3, null), null);
> [1,2,3,null,null]
> > SELECT array_append(CAST(null as Array), 2);
> NULL*
>
> array_prepend is not in the docs, but in the code
> https://issues.apache.org/jira/browse/SPARK-41233
> {code:java}
> usage = """ _FUNC_(array, element) - Add the element at the beginning of the
> array passed as first argument. Type of element should be the same as the
> type of the elements of the array. Null element is also prepended to the
> array. But if the array passed is NULL output is NULL """, examples = """
> Examples: > SELECT _FUNC_(array('b', 'd', 'c', 'a'), 'd');
> ["d","b","d","c","a"] > SELECT _FUNC_(array(1, 2, 3, null), null);
> [null,1,2,3,null] > SELECT _FUNC_(CAST(null as Array), 2); NULL
> case class ArrayPrepend(left: Expression, right: Expression) extends
> RuntimeReplaceable {code}
> h3.
> [array_position|https://spark.apache.org/docs/latest/api/sql/index.html#array_position]
> array_position(array, element) - Returns the (1-based) index of the first
> element of the array as long.
> *Examples:*
>
> {code:java}
> > SELECT array_position(array(3, 2, 1), 1);
> 3 {code}
>
> h3.
> [array_remove|https://spark.apache.org/docs/latest/api/sql/index.html#array_remove]
> array_remove(array, element) - Remove all elements that equal to element from
> array.
> *Examples:*
>
> {code:java}
> > SELECT array_remove(array(1, 2, 3, null, 3), 3);
> [1,2,null] {code}
>
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5751) Add ARRAY_APPEND, ARRAY_POSITION, ARRAY_REMOVE ARRAY_PREPEND for Spark dialect
[
https://issues.apache.org/jira/browse/CALCITE-5751?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17735712#comment-17735712
]
Jiajun Xie commented on CALCITE-5751:
-
Fixed in
[a2d2a31|https://github.com/apache/calcite/commit/a2d2a31a70be3b20f3f2b8f311bf580dd9ae1e24]
[~jackylau] Thanks for your PR!
[~jhyde] Thanks for your review!
> Add ARRAY_APPEND, ARRAY_POSITION, ARRAY_REMOVE ARRAY_PREPEND for Spark dialect
> --
>
> Key: CALCITE-5751
> URL: https://issues.apache.org/jira/browse/CALCITE-5751
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.35.0
>Reporter: jackylau
>Assignee: jackylau
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.35.0
>
>
> h3.
> [array_append|https://spark.apache.org/docs/latest/api/sql/index.html#array_append]
> array_append(array, element) - Add the element at the end of the array passed
> as first argument. Type of element should be similar to type of the elements
> of the array. Null element is also appended into the array. But if the array
> passed, is NULL output is NULL
> *Examples:*
>
> {code:java}
> {code}
> *> SELECT array_append(array('b', 'd', 'c', 'a'), 'd');
> ["b","d","c","a","d"]
> > SELECT array_append(array(1, 2, 3, null), null);
> [1,2,3,null,null]
> > SELECT array_append(CAST(null as Array), 2);
> NULL*
>
> array_prepend is not in the docs, but in the code
> https://issues.apache.org/jira/browse/SPARK-41233
> {code:java}
> usage = """ _FUNC_(array, element) - Add the element at the beginning of the
> array passed as first argument. Type of element should be the same as the
> type of the elements of the array. Null element is also prepended to the
> array. But if the array passed is NULL output is NULL """, examples = """
> Examples: > SELECT _FUNC_(array('b', 'd', 'c', 'a'), 'd');
> ["d","b","d","c","a"] > SELECT _FUNC_(array(1, 2, 3, null), null);
> [null,1,2,3,null] > SELECT _FUNC_(CAST(null as Array), 2); NULL
> case class ArrayPrepend(left: Expression, right: Expression) extends
> RuntimeReplaceable {code}
> h3.
> [array_position|https://spark.apache.org/docs/latest/api/sql/index.html#array_position]
> array_position(array, element) - Returns the (1-based) index of the first
> element of the array as long.
> *Examples:*
>
> {code:java}
> > SELECT array_position(array(3, 2, 1), 1);
> 3 {code}
>
> h3.
> [array_remove|https://spark.apache.org/docs/latest/api/sql/index.html#array_remove]
> array_remove(array, element) - Remove all elements that equal to element from
> array.
> *Examples:*
>
> {code:java}
> > SELECT array_remove(array(1, 2, 3, null, 3), 3);
> [1,2,null] {code}
>
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5688) Support TRUNCATE TABLE DDL statement in Babel parser
[ https://issues.apache.org/jira/browse/CALCITE-5688?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17733889#comment-17733889 ] Jiajun Xie commented on CALCITE-5688: - A new SqlKind usually to be validated, so I need to write some codes in `SqlValidatorImpl.java`. (Maybe it break core). If no one deny, I will implement `truncate table` in the parser and validator. > Support TRUNCATE TABLE DDL statement in Babel parser > > > Key: CALCITE-5688 > URL: https://issues.apache.org/jira/browse/CALCITE-5688 > Project: Calcite > Issue Type: Task > Components: babel, core >Reporter: Sumeet >Priority: Minor > Labels: pull-request-available > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-5688) Support TRUNCATE TABLE DDL statement in Babel parser
[ https://issues.apache.org/jira/browse/CALCITE-5688?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17733850#comment-17733850 ] Jiajun Xie commented on CALCITE-5688: - Because [~VAE]'s comment. I'm thinking if I should change the JIRA title to support `TRUNCATE TABLE` in core parser and then I will add `SqlKind.TRUNCATE_TABLE`. > Support TRUNCATE TABLE DDL statement in Babel parser > > > Key: CALCITE-5688 > URL: https://issues.apache.org/jira/browse/CALCITE-5688 > Project: Calcite > Issue Type: Task > Components: babel, core >Reporter: Sumeet >Priority: Minor > Labels: pull-request-available > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-5688) Support TRUNCATE TABLE DDL statement in Babel parser
[ https://issues.apache.org/jira/browse/CALCITE-5688?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17733850#comment-17733850 ] Jiajun Xie edited comment on CALCITE-5688 at 6/18/23 5:08 AM: -- Because [~VAE]'s comment. I'm thinking if I should change the JIRA title to support `TRUNCATE TABLE` in *core* parser and then I will add `SqlKind.TRUNCATE_TABLE`. was (Author: jiajunbernoulli): Because [~VAE]'s comment. I'm thinking if I should change the JIRA title to support `TRUNCATE TABLE` in core parser and then I will add `SqlKind.TRUNCATE_TABLE`. > Support TRUNCATE TABLE DDL statement in Babel parser > > > Key: CALCITE-5688 > URL: https://issues.apache.org/jira/browse/CALCITE-5688 > Project: Calcite > Issue Type: Task > Components: babel, core >Reporter: Sumeet >Priority: Minor > Labels: pull-request-available > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-5688) Support TRUNCATE TABLE DDL statement in Babel parser
[
https://issues.apache.org/jira/browse/CALCITE-5688?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17733838#comment-17733838
]
Jiajun Xie edited comment on CALCITE-5688 at 6/18/23 2:44 AM:
--
hi [~sumeet.gajjar] :
I implemented SQL2011 syntax in Babel Parser
{code:java}
TRUNCATE TABLE
[ RESTART IDENTITY | CONTINUE IDENTITY ]{code}
You are you are co-author, would you please review it?
[https://github.com/apache/calcite/pull/3274]
was (Author: jiajunbernoulli):
hi [~sumeet.gajjar] :
I implemented SQL2011 syntax in Babel Parser
TRUNCATE TABLE
[ RESTART IDENTITY | CONTINUE IDENTITY ]
You are you are co-author, would you please review it?
https://github.com/apache/calcite/pull/3274
> Support TRUNCATE TABLE DDL statement in Babel parser
>
>
> Key: CALCITE-5688
> URL: https://issues.apache.org/jira/browse/CALCITE-5688
> Project: Calcite
> Issue Type: Task
> Components: babel, core
>Reporter: Sumeet
>Priority: Minor
> Labels: pull-request-available
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5688) Support TRUNCATE TABLE DDL statement in Babel parser
[ https://issues.apache.org/jira/browse/CALCITE-5688?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17733838#comment-17733838 ] Jiajun Xie commented on CALCITE-5688: - hi [~sumeet.gajjar] : I implemented SQL2011 syntax in Babel Parser TRUNCATE TABLE [ RESTART IDENTITY | CONTINUE IDENTITY ] You are you are co-author, would you please review it? https://github.com/apache/calcite/pull/3274 > Support TRUNCATE TABLE DDL statement in Babel parser > > > Key: CALCITE-5688 > URL: https://issues.apache.org/jira/browse/CALCITE-5688 > Project: Calcite > Issue Type: Task > Components: babel, core >Reporter: Sumeet >Priority: Minor > Labels: pull-request-available > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-5779) Implicit column alias for single-column table function should work
[
https://issues.apache.org/jira/browse/CALCITE-5779?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17733686#comment-17733686
]
Jiajun Xie commented on CALCITE-5779:
-
This seems to be a common bug.
Does anyone know how Flink solves it?
> Implicit column alias for single-column table function should work
> --
>
> Key: CALCITE-5779
> URL: https://issues.apache.org/jira/browse/CALCITE-5779
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.34.0
>Reporter: Dmitry Sysolyatin
>Assignee: Dmitry Sysolyatin
>Priority: Major
> Labels: pull-request-available
>
> Implicit column alias for single-column table function works only for UNNEST
> at the moment. But should work for every table function.
> For example, PostgreSQL:
> {code:java}
> CREATE OR REPLACE FUNCTION fun()
> RETURNS TABLE(col int)
> AS
> $$
> SELECT 1
> $$ LANGUAGE 'sql' VOLATILE;
> SELECT f.col, f FROM fun() as f;
> col | f
> -+---
>1 | 1
> (1 row)
> SELECT * FROM fun() as f;
> col
> -
>1
> (1 row)
> SELECT f FROM fun() as f;
> f
> ---
> 1
> (1 row){code}
>
> Calcite throws "Column 'i' not found in any table" exception for
> {code:java}
> SELECT i FROM table(generate_series(1,2)) as i
> {code}
> Where generate_series is user defined table function.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5760) SqlOperatorTest::testDateTrunc does not validate results
[
https://issues.apache.org/jira/browse/CALCITE-5760?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17731130#comment-17731130
]
Jiajun Xie commented on CALCITE-5760:
-
We can execute by using `Fixtures.forOperators(true)`.
Here is
[demo|https://github.com/apache/calcite/blob/2e3e4ae220a69a0936792caec1fae23d947e77f3/testkit/src/test/java/org/apache/calcite/test/FixtureTest.java#L89].
> SqlOperatorTest::testDateTrunc does not validate results
>
>
> Key: CALCITE-5760
> URL: https://issues.apache.org/jira/browse/CALCITE-5760
> Project: Calcite
> Issue Type: Bug
> Components: tests
>Affects Versions: 1.34.0
>Reporter: Mihai Budiu
>Priority: Minor
>
> I was trying to reproduce a bug where DATE_TRUNC returns an incorrect result
> when truncating a constant date to a MILLENNIUM.
> I found test cases for DATE_TRUNC in SqlOperatorTest; these test cases do
> specify the expected result, e.g.:
> f.checkScalar("date_trunc(date '2015-02-19', isoyear)",
> "2014-12-29", "DATE NOT NULL");
> However, the test fixture uses AbstractSqlTester::check, which ignores the
> result!
> As a proof, one can replace the results with arbitrary strings and the tests
> will still pass.
> I presume that this is not the intent of the test writers. How could this
> test be fixed to also check the results?
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Resolved] (CALCITE-5704) Add ARRAY_EXCEPT, ARRAY_INTERSECT and ARRAY_UNION for Spark dialect
[ https://issues.apache.org/jira/browse/CALCITE-5704?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Jiajun Xie resolved CALCITE-5704. - Resolution: Fixed > Add ARRAY_EXCEPT, ARRAY_INTERSECT and ARRAY_UNION for Spark dialect > --- > > Key: CALCITE-5704 > URL: https://issues.apache.org/jira/browse/CALCITE-5704 > Project: Calcite > Issue Type: Improvement > Components: core >Affects Versions: 1.35.0 >Reporter: jackylau >Assignee: jackylau >Priority: Major > Labels: pull-request-available > Fix For: 1.35.0 > > > array_union(array1, array2) - Returns an array of the elements in the union > of array1 and array2, without duplicates. > array_intersect(array1, array2) - Returns an array of the elements in the > intersection of array1 and array2, without duplicates. > array_except(array1, array2) - Returns an array of the elements in array1 but > not in array2, without duplicates. > For more details > [https://spark.apache.org/docs/latest/api/sql/index.html] -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-5704) Add ARRAY_EXCEPT, ARRAY_INTERSECT and ARRAY_UNION for Spark dialect
[ https://issues.apache.org/jira/browse/CALCITE-5704?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17730354#comment-17730354 ] Jiajun Xie commented on CALCITE-5704: - Fixed in [821f03b|https://github.com/apache/calcite/commit/821f03be6a99d44d2e9f9380354f28e884430602]. [~jackylau] Thanks for the PR. [~Runking] Thanks for your review. > Add ARRAY_EXCEPT, ARRAY_INTERSECT and ARRAY_UNION for Spark dialect > --- > > Key: CALCITE-5704 > URL: https://issues.apache.org/jira/browse/CALCITE-5704 > Project: Calcite > Issue Type: Improvement > Components: core >Affects Versions: 1.35.0 >Reporter: jackylau >Assignee: jackylau >Priority: Major > Labels: pull-request-available > Fix For: 1.35.0 > > > array_union(array1, array2) - Returns an array of the elements in the union > of array1 and array2, without duplicates. > array_intersect(array1, array2) - Returns an array of the elements in the > intersection of array1 and array2, without duplicates. > array_except(array1, array2) - Returns an array of the elements in array1 but > not in array2, without duplicates. > For more details > [https://spark.apache.org/docs/latest/api/sql/index.html] -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Resolved] (CALCITE-5710) Add ARRAY_MAX and ARRAY_MIN for Spark dialect
[ https://issues.apache.org/jira/browse/CALCITE-5710?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Jiajun Xie resolved CALCITE-5710. - Resolution: Fixed > Add ARRAY_MAX and ARRAY_MIN for Spark dialect > - > > Key: CALCITE-5710 > URL: https://issues.apache.org/jira/browse/CALCITE-5710 > Project: Calcite > Issue Type: Improvement > Components: core >Affects Versions: 1.34.0 >Reporter: jackylau >Priority: Major > Labels: pull-request-available > Fix For: 1.35.0 > > > ARRAY_MAX - Returns the maximum value in the array > ARRAY_MIN - Returns the minmum value in the array > > For more details > [https://spark.apache.org/docs/latest/api/sql/index.html] -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-5710) Add ARRAY_MAX and ARRAY_MIN for Spark dialect
[ https://issues.apache.org/jira/browse/CALCITE-5710?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17729132#comment-17729132 ] Jiajun Xie commented on CALCITE-5710: - Fixed in [baa6b10|https://github.com/apache/calcite/commit/baa6b104f33a851e419bd95e18acf3766aa19838]. Thanks for the PR [~jackylau] . Thanks for the review [~snuyanzin] , [~MasseGuillaume]. > Add ARRAY_MAX and ARRAY_MIN for Spark dialect > - > > Key: CALCITE-5710 > URL: https://issues.apache.org/jira/browse/CALCITE-5710 > Project: Calcite > Issue Type: Improvement > Components: core >Affects Versions: 1.34.0 >Reporter: jackylau >Priority: Major > Labels: pull-request-available > Fix For: 1.35.0 > > > ARRAY_MAX - Returns the maximum value in the array > ARRAY_MIN - Returns the minmum value in the array > > For more details > [https://spark.apache.org/docs/latest/api/sql/index.html] -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-5688) Support TRUNCATE TABLE DDL statement in Babel parser
[ https://issues.apache.org/jira/browse/CALCITE-5688?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17729035#comment-17729035 ] Jiajun Xie commented on CALCITE-5688: - [~sumeet.gajjar] Don't apologize, the community welcomes everyone who voluntarily participates. I often reply late because I only have time on weekends. > Support TRUNCATE TABLE DDL statement in Babel parser > > > Key: CALCITE-5688 > URL: https://issues.apache.org/jira/browse/CALCITE-5688 > Project: Calcite > Issue Type: Task > Components: babel, core >Reporter: Sumeet >Priority: Minor > Labels: pull-request-available > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-5739) Add MAP_FROM_ARRAYS for Spark dialect
[
https://issues.apache.org/jira/browse/CALCITE-5739?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17728899#comment-17728899
]
Jiajun Xie commented on CALCITE-5739:
-
[~jackylau] , Can you try to do similar functions in one PR? Now your PR always
conflicts with each other.
> Add MAP_FROM_ARRAYS for Spark dialect
> -
>
> Key: CALCITE-5739
> URL: https://issues.apache.org/jira/browse/CALCITE-5739
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.35.0
>Reporter: jackylau
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.35.0
>
>
> MAP_FROM_ARRAYS (array1, array2)
> * Returns a map created from an *array1* and *array2. Note that the lengths
> of two arrays should be the same
> {code:java}
> // code placeholder
> > SELECT map_from_arrays(array(1.0, 3.0), array('2', '4'));
> {1.0:"2",3.0:"4"} {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Resolved] (CALCITE-5714) Add MAP_ENTRIES for Spark dialect
[ https://issues.apache.org/jira/browse/CALCITE-5714?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Jiajun Xie resolved CALCITE-5714. - Resolution: Fixed > Add MAP_ENTRIES for Spark dialect > - > > Key: CALCITE-5714 > URL: https://issues.apache.org/jira/browse/CALCITE-5714 > Project: Calcite > Issue Type: Improvement > Components: core >Affects Versions: 1.35.0 >Reporter: jackylau >Priority: Major > Labels: pull-request-available > Fix For: 1.35.0 > > > MAP_ENTRIES - Returns an unordered array of all entries in the given map. > For more details > [https://spark.apache.org/docs/latest/sql-ref-functions-builtin.html] -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-5714) Add MAP_ENTRIES for Spark dialect
[
https://issues.apache.org/jira/browse/CALCITE-5714?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17728737#comment-17728737
]
Jiajun Xie commented on CALCITE-5714:
-
Fixed in
[c14e071.|https://github.com/apache/calcite/commit/c14e071590665a0ddda6c56cb95e2955fd8d5349]
[~jackylau] Thanks for the PR.{*}{*}
[~MasseGuillaume] Thanks for your review.
> Add MAP_ENTRIES for Spark dialect
> -
>
> Key: CALCITE-5714
> URL: https://issues.apache.org/jira/browse/CALCITE-5714
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.35.0
>Reporter: jackylau
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.35.0
>
>
> MAP_ENTRIES - Returns an unordered array of all entries in the given map.
> For more details
> [https://spark.apache.org/docs/latest/sql-ref-functions-builtin.html]
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Resolved] (CALCITE-5723) Oracle dialect generates SQL that cannot be recognized by lower version Oracle Server(<12) when unparsing OffsetFetch
[ https://issues.apache.org/jira/browse/CALCITE-5723?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Jiajun Xie resolved CALCITE-5723. - Resolution: Fixed > Oracle dialect generates SQL that cannot be recognized by lower version > Oracle Server(<12) when unparsing OffsetFetch > - > > Key: CALCITE-5723 > URL: https://issues.apache.org/jira/browse/CALCITE-5723 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.34.0 >Reporter: ZheHu >Assignee: ZheHu >Priority: Minor > Labels: pull-request-available > Fix For: 1.35.0 > > > Current OracleSqlDialect unparses offset/fetch using ANSI standard "OFFSET > offset ROWS FETCH NEXT fetch ROWS ONLY" syntax. However, Oracle supports > such syntax since 12c. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-5723) Oracle dialect generates SQL that cannot be recognized by lower version Oracle Server(<12) when unparsing OffsetFetch
[ https://issues.apache.org/jira/browse/CALCITE-5723?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17728527#comment-17728527 ] Jiajun Xie commented on CALCITE-5723: - Fixed in [d98f96d|https://github.com/apache/calcite/commit/d98f96d830e2114aff4166476ebedf6bd82a4c4e] [~VAE] Thanks for the pr. > Oracle dialect generates SQL that cannot be recognized by lower version > Oracle Server(<12) when unparsing OffsetFetch > - > > Key: CALCITE-5723 > URL: https://issues.apache.org/jira/browse/CALCITE-5723 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.34.0 >Reporter: ZheHu >Assignee: ZheHu >Priority: Minor > Labels: pull-request-available > Fix For: 1.35.0 > > > Current OracleSqlDialect unparses offset/fetch using ANSI standard "OFFSET > offset ROWS FETCH NEXT fetch ROWS ONLY" syntax. However, Oracle supports > such syntax since 12c. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-5506) RelToSqlConverter should retain the aggregation logic when Project without RexInputRef on the Aggregate
[
https://issues.apache.org/jira/browse/CALCITE-5506?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17727752#comment-17727752
]
Jiajun Xie commented on CALCITE-5506:
-
[~julianhyde] , Thank you.
> RelToSqlConverter should retain the aggregation logic when Project without
> RexInputRef on the Aggregate
> ---
>
> Key: CALCITE-5506
> URL: https://issues.apache.org/jira/browse/CALCITE-5506
> Project: Calcite
> Issue Type: Improvement
> Components: jdbc-adapter
>Reporter: Jiajun Xie
>Assignee: Jiajun Xie
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.35.0
>
> Time Spent: 20m
> Remaining Estimate: 0h
>
> Here is a sql
> {code:java}
> select l.v as l_cost
> from (
> select 0 as v,
> 1 as k
> ) l
> join (
> select sum("cost") as v,
> 1 as k
> from (
> select 1 as "cost"
> union
> select 2 as "cost"
> )
> ) r
> on l.k = r.k {code}
> Before trimming, the RelNode is
> {code:java}
> LogicalProject(L_COST=[$0])
> LogicalJoin(condition=[=($1, $3)], joinType=[inner])
> LogicalValues(tuples=[[{ 0, 1 }]])
> LogicalProject(V=[$0], K=[1])
> LogicalAggregate(group=[{}], V=[SUM($0)])
> LogicalUnion(all=[false])
> LogicalValues(tuples=[[{ 1 }]])
> LogicalValues(tuples=[[{ 2 }]]) {code}
> After trimming, the RelNode is
> {code:java}
> LogicalProject(L_COST=[$0])
> LogicalJoin(condition=[=($1, $2)], joinType=[inner])
> LogicalValues(tuples=[[{ 0, 1 }]])
> LogicalProject(K=[1])
> LogicalAggregate(group=[{}], DUMMY=[COUNT()])
> LogicalUnion(all=[false])
> LogicalValues(tuples=[[{ 1 }]])
> LogicalValues(tuples=[[{ 2 }]]){code}
> If we convert trimmed RelNode to sql, the sql will be
> {code:java}
> SELECT *
> FROM
> (VALUES (0, 1)) AS "t" ("V", "K")
> INNER JOIN
> (SELECT 1 AS "K" -- Missing SUM()
>FROM (SELECT *
> FROM (VALUES (1)) AS "t" ("cost")
> UNION
> SELECT *
> FROM (VALUES (2)) AS "t" ("cost")) AS "t2"
> ) AS "t4"
> ON "t"."K" = "t4"."K" {code}
> The origin sql only has one row result, but the new sql that be trimmed has
> two row result.
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5506) RelToSqlConverter should retain the aggregation logic when Project without RexInputRef on the Aggregate
[
https://issues.apache.org/jira/browse/CALCITE-5506?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17726877#comment-17726877
]
Jiajun Xie commented on CALCITE-5506:
-
[~julianhyde] Do you have time to submit your branch?
I am willing to create a new PR to merge your commit.
> RelToSqlConverter should retain the aggregation logic when Project without
> RexInputRef on the Aggregate
> ---
>
> Key: CALCITE-5506
> URL: https://issues.apache.org/jira/browse/CALCITE-5506
> Project: Calcite
> Issue Type: Improvement
> Components: jdbc-adapter
>Reporter: Jiajun Xie
>Assignee: Jiajun Xie
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.35.0
>
> Time Spent: 20m
> Remaining Estimate: 0h
>
> Here is a sql
> {code:java}
> select l.v as l_cost
> from (
> select 0 as v,
> 1 as k
> ) l
> join (
> select sum("cost") as v,
> 1 as k
> from (
> select 1 as "cost"
> union
> select 2 as "cost"
> )
> ) r
> on l.k = r.k {code}
> Before trimming, the RelNode is
> {code:java}
> LogicalProject(L_COST=[$0])
> LogicalJoin(condition=[=($1, $3)], joinType=[inner])
> LogicalValues(tuples=[[{ 0, 1 }]])
> LogicalProject(V=[$0], K=[1])
> LogicalAggregate(group=[{}], V=[SUM($0)])
> LogicalUnion(all=[false])
> LogicalValues(tuples=[[{ 1 }]])
> LogicalValues(tuples=[[{ 2 }]]) {code}
> After trimming, the RelNode is
> {code:java}
> LogicalProject(L_COST=[$0])
> LogicalJoin(condition=[=($1, $2)], joinType=[inner])
> LogicalValues(tuples=[[{ 0, 1 }]])
> LogicalProject(K=[1])
> LogicalAggregate(group=[{}], DUMMY=[COUNT()])
> LogicalUnion(all=[false])
> LogicalValues(tuples=[[{ 1 }]])
> LogicalValues(tuples=[[{ 2 }]]){code}
> If we convert trimmed RelNode to sql, the sql will be
> {code:java}
> SELECT *
> FROM
> (VALUES (0, 1)) AS "t" ("V", "K")
> INNER JOIN
> (SELECT 1 AS "K" -- Missing SUM()
>FROM (SELECT *
> FROM (VALUES (1)) AS "t" ("cost")
> UNION
> SELECT *
> FROM (VALUES (2)) AS "t" ("cost")) AS "t2"
> ) AS "t4"
> ON "t"."K" = "t4"."K" {code}
> The origin sql only has one row result, but the new sql that be trimmed has
> two row result.
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-5723) Oracle dialect generates SQL that cannot be recognized by lower version Oracle Server(<12) when unparsing OffsetFetch
[ https://issues.apache.org/jira/browse/CALCITE-5723?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17726870#comment-17726870 ] Jiajun Xie edited comment on CALCITE-5723 at 5/28/23 1:42 AM: -- Thank you for your explanation. The PR looks good and only one small question(comment on PR) that needs to be answered. was (Author: jiajunbernoulli): Thank you for your explanation. The PR looks good and only one small question that needs to be answered. > Oracle dialect generates SQL that cannot be recognized by lower version > Oracle Server(<12) when unparsing OffsetFetch > - > > Key: CALCITE-5723 > URL: https://issues.apache.org/jira/browse/CALCITE-5723 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.34.0 >Reporter: ZheHu >Assignee: ZheHu >Priority: Minor > Labels: pull-request-available > Fix For: 1.35.0 > > > Current OracleSqlDialect unparses offset/fetch using ANSI standard "OFFSET > offset ROWS FETCH NEXT fetch ROWS ONLY" syntax. However, Oracle supports > such syntax since 12c. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-5723) Oracle dialect generates SQL that cannot be recognized by lower version Oracle Server(<12) when unparsing OffsetFetch
[ https://issues.apache.org/jira/browse/CALCITE-5723?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17726870#comment-17726870 ] Jiajun Xie commented on CALCITE-5723: - Thank you for your explanation. The PR looks good and only one small question that needs to be answered. > Oracle dialect generates SQL that cannot be recognized by lower version > Oracle Server(<12) when unparsing OffsetFetch > - > > Key: CALCITE-5723 > URL: https://issues.apache.org/jira/browse/CALCITE-5723 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.34.0 >Reporter: ZheHu >Assignee: ZheHu >Priority: Minor > Labels: pull-request-available > Fix For: 1.35.0 > > > Current OracleSqlDialect unparses offset/fetch using ANSI standard "OFFSET > offset ROWS FETCH NEXT fetch ROWS ONLY" syntax. However, Oracle supports > such syntax since 12c. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-5725) Support Doris Dialect
[ https://issues.apache.org/jira/browse/CALCITE-5725?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17726869#comment-17726869 ] Jiajun Xie edited comment on CALCITE-5725 at 5/28/23 1:38 AM: -- 1. It's worth mentioning that babel parser can welcome various dialects, so we should try our best not to change the core parser. 2. Here is [PR|https://github.com/apache/calcite/pull/2764/files] to add FireboltSqlDialect when RelToSql, I believe it will be helpful to you. was (Author: jiajunbernoulli): 1. It's worth mentioning that BabelParser can welcome various dialects, so we should try our best not to change the core parser. 2. Here is [PR|https://github.com/apache/calcite/pull/2764/files] to add FireboltSqlDialect when RelToSql, I believe it will be helpful to you. > Support Doris Dialect > - > > Key: CALCITE-5725 > URL: https://issues.apache.org/jira/browse/CALCITE-5725 > Project: Calcite > Issue Type: New Feature > Components: core >Reporter: xingyuan cheng >Priority: Major > > Doris is one of the popular OLAP databases. The current version of calcite > does not support the dialect of Doris as input syntax, and it is also unable > to perform dialect conversion based on Doris. For example, doris is converted > to hive, doris is converted to flink, and spark is converted to doris -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-5725) Support Doris Dialect
[ https://issues.apache.org/jira/browse/CALCITE-5725?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17726869#comment-17726869 ] Jiajun Xie commented on CALCITE-5725: - 1. It's worth mentioning that BabelParser can welcome various dialects, so we should try our best not to change the core parser. 2. Here is [PR|https://github.com/apache/calcite/pull/2764/files] to add FireboltSqlDialect when RelToSql, I believe it will be helpful to you. > Support Doris Dialect > - > > Key: CALCITE-5725 > URL: https://issues.apache.org/jira/browse/CALCITE-5725 > Project: Calcite > Issue Type: New Feature > Components: core >Reporter: xingyuan cheng >Priority: Major > > Doris is one of the popular OLAP databases. The current version of calcite > does not support the dialect of Doris as input syntax, and it is also unable > to perform dialect conversion based on Doris. For example, doris is converted > to hive, doris is converted to flink, and spark is converted to doris -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-5724) Generated SQL uses literal values in ORDER BY clauses
[
https://issues.apache.org/jira/browse/CALCITE-5724?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17726867#comment-17726867
]
Jiajun Xie commented on CALCITE-5724:
-
Agree with Hyde.
Here is
[UT|https://github.com/apache/calcite/blob/e981e217d1835538c4bdfc9b4049a93fd64de904/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java#L1109]
for *CoreRules.SORT_REMOVE_CONSTANT_KEYS.*
> Generated SQL uses literal values in ORDER BY clauses
> -
>
> Key: CALCITE-5724
> URL: https://issues.apache.org/jira/browse/CALCITE-5724
> Project: Calcite
> Issue Type: Bug
>Reporter: Joey Moore
>Assignee: Joey Moore
>Priority: Major
>
> Current behavior in the SqlImplementor will generate SqlCharStringLiterals in
> ORDER BY fields when there is a Literal value in the SELECT clause. This
> happens in languages with isSortByOrdinal(). This leads to errors in dialects
> in which cannot have literal values in ORDER BY clauses such as BigQuery.
> Proposed fix is to use ordinals in all cases where a literal value is present
> in the SELECT clause.
> Example of current implementation:
> {code:java}
> select 3.14159265 as pi
> from \"product\"
> order by 1;
> {code}
> Will return
> {code:java}
> SELECT 3.14159265 AS \"PI\"
> FROM \"foodmart\".\"product\"
> ORDER BY '3.14159265'{code}
> Proposed implementation will return :
> {code:java}
> SELECT 3.14159265 AS \"PI\"
> FROM \"foodmart\".\"product\"
> ORDER BY 1{code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-5723) Oracle dialect generates SQL that cannot be recognized by lower version Oracle Server(<12) when unparsing OffsetFetch
[
https://issues.apache.org/jira/browse/CALCITE-5723?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17726789#comment-17726789
]
Jiajun Xie edited comment on CALCITE-5723 at 5/27/23 10:01 AM:
---
Does oracle(<12) support other similar syntax?
e.g.
{code:java}
limit ... offset ...{code}
was (Author: jiajunbernoulli):
Does oracle(<12) support other similar syntax?
> Oracle dialect generates SQL that cannot be recognized by lower version
> Oracle Server(<12) when unparsing OffsetFetch
> -
>
> Key: CALCITE-5723
> URL: https://issues.apache.org/jira/browse/CALCITE-5723
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.34.0
>Reporter: ZheHu
>Assignee: ZheHu
>Priority: Minor
> Labels: pull-request-available
> Fix For: 1.35.0
>
>
> Current OracleSqlDialect unparses offset/fetch using ANSI standard "OFFSET
> offset ROWS FETCH NEXT fetch ROWS ONLY" syntax. However, Oracle supports
> such syntax since 12c.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5723) Oracle dialect generates SQL that cannot be recognized by lower version Oracle Server(<12) when unparsing OffsetFetch
[ https://issues.apache.org/jira/browse/CALCITE-5723?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17726789#comment-17726789 ] Jiajun Xie commented on CALCITE-5723: - Does oracle(<12) support other similar syntax? > Oracle dialect generates SQL that cannot be recognized by lower version > Oracle Server(<12) when unparsing OffsetFetch > - > > Key: CALCITE-5723 > URL: https://issues.apache.org/jira/browse/CALCITE-5723 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.34.0 >Reporter: ZheHu >Assignee: ZheHu >Priority: Minor > Labels: pull-request-available > Fix For: 1.35.0 > > > Current OracleSqlDialect unparses offset/fetch using ANSI standard "OFFSET > offset ROWS FETCH NEXT fetch ROWS ONLY" syntax. However, Oracle supports > such syntax since 12c. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Resolved] (CALCITE-5695) Add MAP_KEYS and MAP_VALUES for Spark dialect
[
https://issues.apache.org/jira/browse/CALCITE-5695?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Jiajun Xie resolved CALCITE-5695.
-
Resolution: Fixed
> Add MAP_KEYS and MAP_VALUES for Spark dialect
> -
>
> Key: CALCITE-5695
> URL: https://issues.apache.org/jira/browse/CALCITE-5695
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.35.0
>Reporter: jackylau
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.35.0
>
>
> {{MAP_KEYS}} - Returns the keys of the map as an array, the order of the
> entries is not defined
> {{MAP_VALUES}} - Returns the values of the map as an array, the order of the
> entries is not defined
> For more details
> [https://spark.apache.org/docs/latest/sql-ref-functions-builtin.html]
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-5695) Add MAP_KEYS and MAP_VALUES for Spark dialect
[
https://issues.apache.org/jira/browse/CALCITE-5695?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17724580#comment-17724580
]
Jiajun Xie edited comment on CALCITE-5695 at 5/21/23 2:32 AM:
--
Fixed in
[9c33d7a.|https://github.com/apache/calcite/commit/9c33d7aeefe082bf5f7be617ef231e1285418a6c]
Thanks PR for [~jackylau].
Thanks review for [~julianhyde] , @[tanclary|https://github.com/tanclary],
[~FrankZou]
.
was (Author: jiajunbernoulli):
Fixed in
[9c33d7a.|https://github.com/apache/calcite/commit/9c33d7aeefe082bf5f7be617ef231e1285418a6c]
Thanks PR for [~jackylau].
Thanks review for [~julianhyde] , @[tanclary|https://github.com/tanclary],
@zoudan.
> Add MAP_KEYS and MAP_VALUES for Spark dialect
> -
>
> Key: CALCITE-5695
> URL: https://issues.apache.org/jira/browse/CALCITE-5695
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.35.0
>Reporter: jackylau
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.35.0
>
>
> {{MAP_KEYS}} - Returns the keys of the map as an array, the order of the
> entries is not defined
> {{MAP_VALUES}} - Returns the values of the map as an array, the order of the
> entries is not defined
> For more details
> [https://spark.apache.org/docs/latest/sql-ref-functions-builtin.html]
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5695) Add MAP_KEYS and MAP_VALUES for Spark dialect
[
https://issues.apache.org/jira/browse/CALCITE-5695?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17724580#comment-17724580
]
Jiajun Xie commented on CALCITE-5695:
-
Fixed in
[9c33d7a.|https://github.com/apache/calcite/commit/9c33d7aeefe082bf5f7be617ef231e1285418a6c]
Thanks PR for [~jackylau].
Thanks review for [~julianhyde] , @[tanclary|https://github.com/tanclary],
@zoudan.
> Add MAP_KEYS and MAP_VALUES for Spark dialect
> -
>
> Key: CALCITE-5695
> URL: https://issues.apache.org/jira/browse/CALCITE-5695
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.35.0
>Reporter: jackylau
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.35.0
>
>
> {{MAP_KEYS}} - Returns the keys of the map as an array, the order of the
> entries is not defined
> {{MAP_VALUES}} - Returns the values of the map as an array, the order of the
> entries is not defined
> For more details
> [https://spark.apache.org/docs/latest/sql-ref-functions-builtin.html]
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5695) Add MAP_KEYS and MAP_VALUES for Spark dialect
[
https://issues.apache.org/jira/browse/CALCITE-5695?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17724477#comment-17724477
]
Jiajun Xie commented on CALCITE-5695:
-
If there are no other suggestions, I will merge this PR tomorrow.
https://github.com/apache/calcite/pull/3194
> Add MAP_KEYS and MAP_VALUES for Spark dialect
> -
>
> Key: CALCITE-5695
> URL: https://issues.apache.org/jira/browse/CALCITE-5695
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.35.0
>Reporter: jackylau
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.35.0
>
>
> {{MAP_KEYS}} - Returns the keys of the map as an array, the order of the
> entries is not defined
> {{MAP_VALUES}} - Returns the values of the map as an array, the order of the
> entries is not defined
> For more details
> [https://spark.apache.org/docs/latest/sql-ref-functions-builtin.html]
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5700) Add ARRAY_SIZE and ARRAY_REPEAT for Spark dialect
[ https://issues.apache.org/jira/browse/CALCITE-5700?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17723767#comment-17723767 ] Jiajun Xie commented on CALCITE-5700: - Sorry, I ignore it. I will pay attention to future commit messages. > Add ARRAY_SIZE and ARRAY_REPEAT for Spark dialect > - > > Key: CALCITE-5700 > URL: https://issues.apache.org/jira/browse/CALCITE-5700 > Project: Calcite > Issue Type: Improvement > Components: core >Affects Versions: 1.35.0 >Reporter: jackylau >Priority: Major > Labels: pull-request-available > Fix For: 1.35.0 > > > ARRAY_SIZE - Returns the size of an array. > ARRAY_REPEAT - Returns the array containing element count times. > > For more details > https://spark.apache.org/docs/latest/api/sql/index.html -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-5700) Add ARRAY_SIZE and ARRAY_REPEAT for Spark dialect
[ https://issues.apache.org/jira/browse/CALCITE-5700?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17723315#comment-17723315 ] Jiajun Xie edited comment on CALCITE-5700 at 5/17/23 2:01 AM: -- [~jackylau] ,Thanks for PR. Fixed in [48f51ea.|https://github.com/apache/calcite/commit/48f51ea5d9443fb11e070eea094bf551c8ff22fc] was (Author: jiajunbernoulli): [~jackylau] ,Thanks for PR. Fixed in [48f51ea.|https://github.com/apache/calcite/commit/48f51ea5d9443fb11e070eea094bf551c8ff22fc] > Add ARRAY_SIZE and ARRAY_REPEAT for Spark dialect > - > > Key: CALCITE-5700 > URL: https://issues.apache.org/jira/browse/CALCITE-5700 > Project: Calcite > Issue Type: Improvement > Components: core >Affects Versions: 1.35.0 >Reporter: jackylau >Priority: Major > Labels: pull-request-available > Fix For: 1.35.0 > > > ARRAY_SIZE - Returns the size of an array. > ARRAY_REPEAT - Returns the array containing element count times. > > For more details > https://spark.apache.org/docs/latest/api/sql/index.html -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-5700) Add ARRAY_SIZE and ARRAY_REPEAT for Spark dialect
[ https://issues.apache.org/jira/browse/CALCITE-5700?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17723315#comment-17723315 ] Jiajun Xie edited comment on CALCITE-5700 at 5/17/23 2:01 AM: -- [~jackylau] ,Thanks for PR. Fixed in [48f51ea.|https://github.com/apache/calcite/commit/48f51ea5d9443fb11e070eea094bf551c8ff22fc] was (Author: jiajunbernoulli): Fixed in [48f51ea|https://github.com/apache/calcite/commit/48f51ea5d9443fb11e070eea094bf551c8ff22fc]. > Add ARRAY_SIZE and ARRAY_REPEAT for Spark dialect > - > > Key: CALCITE-5700 > URL: https://issues.apache.org/jira/browse/CALCITE-5700 > Project: Calcite > Issue Type: Improvement > Components: core >Affects Versions: 1.35.0 >Reporter: jackylau >Priority: Major > Labels: pull-request-available > Fix For: 1.35.0 > > > ARRAY_SIZE - Returns the size of an array. > ARRAY_REPEAT - Returns the array containing element count times. > > For more details > https://spark.apache.org/docs/latest/api/sql/index.html -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Resolved] (CALCITE-5700) Add ARRAY_SIZE and ARRAY_REPEAT for Spark dialect
[ https://issues.apache.org/jira/browse/CALCITE-5700?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Jiajun Xie resolved CALCITE-5700. - Resolution: Fixed > Add ARRAY_SIZE and ARRAY_REPEAT for Spark dialect > - > > Key: CALCITE-5700 > URL: https://issues.apache.org/jira/browse/CALCITE-5700 > Project: Calcite > Issue Type: Improvement > Components: core >Affects Versions: 1.35.0 >Reporter: jackylau >Priority: Major > Labels: pull-request-available > Fix For: 1.35.0 > > > ARRAY_SIZE - Returns the size of an array. > ARRAY_REPEAT - Returns the array containing element count times. > > For more details > https://spark.apache.org/docs/latest/api/sql/index.html -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-5700) Add ARRAY_SIZE and ARRAY_REPEAT for Spark dialect
[ https://issues.apache.org/jira/browse/CALCITE-5700?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17723315#comment-17723315 ] Jiajun Xie commented on CALCITE-5700: - Fixed in [48f51ea|https://github.com/apache/calcite/commit/48f51ea5d9443fb11e070eea094bf551c8ff22fc]. > Add ARRAY_SIZE and ARRAY_REPEAT for Spark dialect > - > > Key: CALCITE-5700 > URL: https://issues.apache.org/jira/browse/CALCITE-5700 > Project: Calcite > Issue Type: Improvement > Components: core >Affects Versions: 1.35.0 >Reporter: jackylau >Priority: Major > Labels: pull-request-available > Fix For: 1.35.0 > > > ARRAY_SIZE - Returns the size of an array. > ARRAY_REPEAT - Returns the array containing element count times. > > For more details > https://spark.apache.org/docs/latest/api/sql/index.html -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-5506) RelToSqlConverter should retain the aggregation logic when Project without RexInputRef on the Aggregate
[
https://issues.apache.org/jira/browse/CALCITE-5506?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17721584#comment-17721584
]
Jiajun Xie commented on CALCITE-5506:
-
It's a better improvement.
Thank you, [~julianhyde] .
> RelToSqlConverter should retain the aggregation logic when Project without
> RexInputRef on the Aggregate
> ---
>
> Key: CALCITE-5506
> URL: https://issues.apache.org/jira/browse/CALCITE-5506
> Project: Calcite
> Issue Type: Improvement
> Components: jdbc-adapter
>Reporter: Jiajun Xie
>Assignee: Jiajun Xie
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.35.0
>
> Time Spent: 20m
> Remaining Estimate: 0h
>
> Here is a sql
> {code:java}
> select l.v as l_cost
> from (
> select 0 as v,
> 1 as k
> ) l
> join (
> select sum("cost") as v,
> 1 as k
> from (
> select 1 as "cost"
> union
> select 2 as "cost"
> )
> ) r
> on l.k = r.k {code}
> Before trimming, the RelNode is
> {code:java}
> LogicalProject(L_COST=[$0])
> LogicalJoin(condition=[=($1, $3)], joinType=[inner])
> LogicalValues(tuples=[[{ 0, 1 }]])
> LogicalProject(V=[$0], K=[1])
> LogicalAggregate(group=[{}], V=[SUM($0)])
> LogicalUnion(all=[false])
> LogicalValues(tuples=[[{ 1 }]])
> LogicalValues(tuples=[[{ 2 }]]) {code}
> After trimming, the RelNode is
> {code:java}
> LogicalProject(L_COST=[$0])
> LogicalJoin(condition=[=($1, $2)], joinType=[inner])
> LogicalValues(tuples=[[{ 0, 1 }]])
> LogicalProject(K=[1])
> LogicalAggregate(group=[{}], DUMMY=[COUNT()])
> LogicalUnion(all=[false])
> LogicalValues(tuples=[[{ 1 }]])
> LogicalValues(tuples=[[{ 2 }]]){code}
> If we convert trimmed RelNode to sql, the sql will be
> {code:java}
> SELECT *
> FROM
> (VALUES (0, 1)) AS "t" ("V", "K")
> INNER JOIN
> (SELECT 1 AS "K" -- Missing SUM()
>FROM (SELECT *
> FROM (VALUES (1)) AS "t" ("cost")
> UNION
> SELECT *
> FROM (VALUES (2)) AS "t" ("cost")) AS "t2"
> ) AS "t4"
> ON "t"."K" = "t4"."K" {code}
> The origin sql only has one row result, but the new sql that be trimmed has
> two row result.
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-5506) RelToSqlConverter should retain the aggregation logic when Project without RexInputRef on the Aggregate
[
https://issues.apache.org/jira/browse/CALCITE-5506?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17720282#comment-17720282
]
Jiajun Xie edited comment on CALCITE-5506 at 5/7/23 6:35 AM:
-
Ok, I find a simple case.
* Before my patch.
{code:java}
@Test void testMissingAggregate() {
sql("select 1 from (select count(1) from \"product\")") // only one row
result.
.ok("SELECT 1\nFROM \"foodmart\".\"product\""); // maybe many results.
}{code}
* After my patch.
{code:java}
@Test void testMissingAggregate() {
sql("select 1 from (select count(1) from \"product\")")
.ok("SELECT 1\n"
+ "FROM (SELECT COUNT(*) AS \"EXPR$0\"\n"
+ "FROM \"foodmart\".\"product\") AS \"t\"");
}{code}
was (Author: jiajunbernoulli):
Ok, I find a simple case.
* Before my patch.
{code:java}
@Test void testMissingAggregate() {
sql("select 1 from (select count(1) from \"product\")") -- only one row
result.
.ok("SELECT 1\nFROM \"foodmart\".\"product\""); -- maybe many results.
}{code}
* After my patch.
{code:java}
@Test void testMissingAggregate() {
sql("select 1 from (select count(1) from \"product\")")
.ok("SELECT 1\n"
+ "FROM (SELECT COUNT(*) AS \"EXPR$0\"\n"
+ "FROM \"foodmart\".\"product\") AS \"t\"");
}{code}
> RelToSqlConverter should retain the aggregation logic when Project without
> RexInputRef on the Aggregate
> ---
>
> Key: CALCITE-5506
> URL: https://issues.apache.org/jira/browse/CALCITE-5506
> Project: Calcite
> Issue Type: Improvement
> Components: jdbc-adapter
>Reporter: Jiajun Xie
>Assignee: Jiajun Xie
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.35.0
>
> Time Spent: 20m
> Remaining Estimate: 0h
>
> Here is a sql
> {code:java}
> select l.v as l_cost
> from (
> select 0 as v,
> 1 as k
> ) l
> join (
> select sum("cost") as v,
> 1 as k
> from (
> select 1 as "cost"
> union
> select 2 as "cost"
> )
> ) r
> on l.k = r.k {code}
> Before trimming, the RelNode is
> {code:java}
> LogicalProject(L_COST=[$0])
> LogicalJoin(condition=[=($1, $3)], joinType=[inner])
> LogicalValues(tuples=[[{ 0, 1 }]])
> LogicalProject(V=[$0], K=[1])
> LogicalAggregate(group=[{}], V=[SUM($0)])
> LogicalUnion(all=[false])
> LogicalValues(tuples=[[{ 1 }]])
> LogicalValues(tuples=[[{ 2 }]]) {code}
> After trimming, the RelNode is
> {code:java}
> LogicalProject(L_COST=[$0])
> LogicalJoin(condition=[=($1, $2)], joinType=[inner])
> LogicalValues(tuples=[[{ 0, 1 }]])
> LogicalProject(K=[1])
> LogicalAggregate(group=[{}], DUMMY=[COUNT()])
> LogicalUnion(all=[false])
> LogicalValues(tuples=[[{ 1 }]])
> LogicalValues(tuples=[[{ 2 }]]){code}
> If we convert trimmed RelNode to sql, the sql will be
> {code:java}
> SELECT *
> FROM
> (VALUES (0, 1)) AS "t" ("V", "K")
> INNER JOIN
> (SELECT 1 AS "K" -- Missing SUM()
>FROM (SELECT *
> FROM (VALUES (1)) AS "t" ("cost")
> UNION
> SELECT *
> FROM (VALUES (2)) AS "t" ("cost")) AS "t2"
> ) AS "t4"
> ON "t"."K" = "t4"."K" {code}
> The origin sql only has one row result, but the new sql that be trimmed has
> two row result.
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5506) RelToSqlConverter should retain the aggregation logic when Project without RexInputRef on the Aggregate
[
https://issues.apache.org/jira/browse/CALCITE-5506?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17720282#comment-17720282
]
Jiajun Xie commented on CALCITE-5506:
-
Ok, I find a simple case.
* Before my patch.
{code:java}
@Test void testMissingAggregate() {
sql("select 1 from (select count(1) from \"product\")") -- only one row
result.
.ok("SELECT 1\nFROM \"foodmart\".\"product\""); -- maybe many results.
}{code}
* After my patch.
{code:java}
@Test void testMissingAggregate() {
sql("select 1 from (select count(1) from \"product\")")
.ok("SELECT 1\n"
+ "FROM (SELECT COUNT(*) AS \"EXPR$0\"\n"
+ "FROM \"foodmart\".\"product\") AS \"t\"");
}{code}
> RelToSqlConverter should retain the aggregation logic when Project without
> RexInputRef on the Aggregate
> ---
>
> Key: CALCITE-5506
> URL: https://issues.apache.org/jira/browse/CALCITE-5506
> Project: Calcite
> Issue Type: Improvement
> Components: jdbc-adapter
>Reporter: Jiajun Xie
>Assignee: Jiajun Xie
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.35.0
>
> Time Spent: 20m
> Remaining Estimate: 0h
>
> Here is a sql
> {code:java}
> select l.v as l_cost
> from (
> select 0 as v,
> 1 as k
> ) l
> join (
> select sum("cost") as v,
> 1 as k
> from (
> select 1 as "cost"
> union
> select 2 as "cost"
> )
> ) r
> on l.k = r.k {code}
> Before trimming, the RelNode is
> {code:java}
> LogicalProject(L_COST=[$0])
> LogicalJoin(condition=[=($1, $3)], joinType=[inner])
> LogicalValues(tuples=[[{ 0, 1 }]])
> LogicalProject(V=[$0], K=[1])
> LogicalAggregate(group=[{}], V=[SUM($0)])
> LogicalUnion(all=[false])
> LogicalValues(tuples=[[{ 1 }]])
> LogicalValues(tuples=[[{ 2 }]]) {code}
> After trimming, the RelNode is
> {code:java}
> LogicalProject(L_COST=[$0])
> LogicalJoin(condition=[=($1, $2)], joinType=[inner])
> LogicalValues(tuples=[[{ 0, 1 }]])
> LogicalProject(K=[1])
> LogicalAggregate(group=[{}], DUMMY=[COUNT()])
> LogicalUnion(all=[false])
> LogicalValues(tuples=[[{ 1 }]])
> LogicalValues(tuples=[[{ 2 }]]){code}
> If we convert trimmed RelNode to sql, the sql will be
> {code:java}
> SELECT *
> FROM
> (VALUES (0, 1)) AS "t" ("V", "K")
> INNER JOIN
> (SELECT 1 AS "K" -- Missing SUM()
>FROM (SELECT *
> FROM (VALUES (1)) AS "t" ("cost")
> UNION
> SELECT *
> FROM (VALUES (2)) AS "t" ("cost")) AS "t2"
> ) AS "t4"
> ON "t"."K" = "t4"."K" {code}
> The origin sql only has one row result, but the new sql that be trimmed has
> two row result.
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5688) Support TRUNCATE TABLE DDL statement in Babel parser
[ https://issues.apache.org/jira/browse/CALCITE-5688?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17720281#comment-17720281 ] Jiajun Xie commented on CALCITE-5688: - Besides the core parser, do you know the server parser? It is more suitable for implementing DDL: https://github.com/apache/calcite/blob/main/server/src/main/codegen/includes/parserImpls.ftl > Support TRUNCATE TABLE DDL statement in Babel parser > > > Key: CALCITE-5688 > URL: https://issues.apache.org/jira/browse/CALCITE-5688 > Project: Calcite > Issue Type: Task > Components: babel, core >Reporter: Sumeet >Priority: Minor > Labels: pull-request-available > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-5688) Support TRUNCATE TABLE DDL statement in Babel parser
[
https://issues.apache.org/jira/browse/CALCITE-5688?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17720280#comment-17720280
]
Jiajun Xie edited comment on CALCITE-5688 at 5/7/23 6:08 AM:
-
>From your PR, core parser support rather than babel parser.
We should change JIRA title and commit message.
Here is truncate syntax in SQL2011:
{code:java}
::=
TRUNCATE TABLE [ ]
::=
CONTINUE IDENTITY
| RESTART IDENTITY{code}
[PostgreSQL|https://www.postgresql.org/docs/current/sql-truncate.html] Similar
to it.
{code:java}
TRUNCATE [ TABLE ] [ ONLY ] name [ * ] [, ... ]
[ RESTART IDENTITY | CONTINUE IDENTITY ] [ CASCADE | RESTRICT ] {code}
[Oracle|https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/TRUNCATE-TABLE.html#GUID-B76E5846-75B5-4876-98EC-439E15E4D8A4]
more complicated.
{code:java}
TRUNCATE TABLE [schema.] table
[ {PRESERVE | PURGE} MATERIALIZED VIEW LOG ]
[ {DROP [ ALL ] | REUSE} STORAGE ] [ CASCADE ] ; {code}
[SQL
Server|https://learn.microsoft.com/en-us/sql/t-sql/statements/truncate-table-transact-sql?view=sql-server-ver16]
allow partitions.
{code:java}
TRUNCATE TABLE
{ database_name.schema_name.table_name | schema_name.table_name |
table_name }
[ WITH ( PARTITIONS ( { | }
[ , ...n ] ) ) ]
[ ; ]
::=
TO {code}
[Hive|https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-TruncateTable]
also allow partitions.
{code:java}
TRUNCATE [TABLE] table_name [PARTITION partition_spec];
partition_spec:
: (partition_column = partition_col_value, partition_column =
partition_col_value, ...) {code}
I suggest we add ` CONTINUE IDENTITY` and `RESTART IDENTITY`, core parser
should follow standard SQL as much as possible.
was (Author: jiajunbernoulli):
>From your PR, core parser support rather than babel parser.
We should change JIRA title and commit message.
Here is truncate syntax in SQL2011:
{code:java}
::=
TRUNCATE TABLE [ ]
::=
CONTINUE IDENTITY
| RESTART IDENTITY{code}
[PostgreSQL|https://www.postgresql.org/docs/current/sql-truncate.html] Similar
to it.
{code:java}
TRUNCATE [ TABLE ] [ ONLY ] name [ * ] [, ... ]
[ RESTART IDENTITY | CONTINUE IDENTITY ] [ CASCADE | RESTRICT ] {code}
[Oracle|https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/TRUNCATE-TABLE.html#GUID-B76E5846-75B5-4876-98EC-439E15E4D8A4]
more complicated.
{code:java}
TRUNCATE TABLE [schema.] table
[ {PRESERVE | PURGE} MATERIALIZED VIEW LOG ]
[ {DROP [ ALL ] | REUSE} STORAGE ] [ CASCADE ] ; {code}
[SQL
Server|https://learn.microsoft.com/en-us/sql/t-sql/statements/truncate-table-transact-sql?view=sql-server-ver16]
allow partitions.
{code:java}
TRUNCATE TABLE
{ database_name.schema_name.table_name | schema_name.table_name |
table_name }
[ WITH ( PARTITIONS ( { | }
[ , ...n ] ) ) ]
[ ; ]
::=
TO {code}
[Hive|https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-TruncateTable]
also allow partitions.
{code:java}
TRUNCATE [TABLE] table_name [PARTITION partition_spec];
partition_spec:
: (partition_column = partition_col_value, partition_column =
partition_col_value, ...) {code}
I suggest we add ` CONTINUE IDENTITY` and `RESTART IDENTITY`, core parser
should follow standard SQL as much as possible.
> Support TRUNCATE TABLE DDL statement in Babel parser
>
>
> Key: CALCITE-5688
> URL: https://issues.apache.org/jira/browse/CALCITE-5688
> Project: Calcite
> Issue Type: Task
> Components: babel, core
>Reporter: Sumeet
>Priority: Minor
> Labels: pull-request-available
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-5688) Support TRUNCATE TABLE DDL statement in Babel parser
[
https://issues.apache.org/jira/browse/CALCITE-5688?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17720280#comment-17720280
]
Jiajun Xie edited comment on CALCITE-5688 at 5/7/23 6:03 AM:
-
>From your PR, core parser support rather than babel parser.
We should change JIRA title and commit message.
Here is truncate syntax in SQL2011:
{code:java}
::=
TRUNCATE TABLE [ ]
::=
CONTINUE IDENTITY
| RESTART IDENTITY{code}
[PostgreSQL|https://www.postgresql.org/docs/current/sql-truncate.html] Similar
to it.
{code:java}
TRUNCATE [ TABLE ] [ ONLY ] name [ * ] [, ... ]
[ RESTART IDENTITY | CONTINUE IDENTITY ] [ CASCADE | RESTRICT ] {code}
[Oracle|https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/TRUNCATE-TABLE.html#GUID-B76E5846-75B5-4876-98EC-439E15E4D8A4]
more complicated.
{code:java}
TRUNCATE TABLE [schema.] table
[ {PRESERVE | PURGE} MATERIALIZED VIEW LOG ]
[ {DROP [ ALL ] | REUSE} STORAGE ] [ CASCADE ] ; {code}
[SQL
Server|https://learn.microsoft.com/en-us/sql/t-sql/statements/truncate-table-transact-sql?view=sql-server-ver16]
allow partitions.
{code:java}
TRUNCATE TABLE
{ database_name.schema_name.table_name | schema_name.table_name |
table_name }
[ WITH ( PARTITIONS ( { | }
[ , ...n ] ) ) ]
[ ; ]
::=
TO {code}
[Hive|https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-TruncateTable]
also allow partitions.
{code:java}
TRUNCATE [TABLE] table_name [PARTITION partition_spec];
partition_spec:
: (partition_column = partition_col_value, partition_column =
partition_col_value, ...) {code}
I suggest we add ` CONTINUE IDENTITY` and `RESTART IDENTITY`, core parser
should follow standard SQL as much as possible.
was (Author: jiajunbernoulli):
>From your PR, core parser support rather than babel parser.
We should change JIRA title and commit message.
Here is truncate syntax in SQL2011:
{code:java}
::=
TRUNCATE TABLE [ ]
::=
CONTINUE IDENTITY
| RESTART IDENTITY{code}
[PostgreSQL|https://www.postgresql.org/docs/current/sql-truncate.html] Similar
to it.
{code:java}
TRUNCATE [ TABLE ] [ ONLY ] name [ * ] [, ... ]
[ RESTART IDENTITY | CONTINUE IDENTITY ] [ CASCADE | RESTRICT ] {code}
[Oracle|https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/TRUNCATE-TABLE.html#GUID-B76E5846-75B5-4876-98EC-439E15E4D8A4]
more complicated.
{code:java}
TRUNCATE TABLE [schema.] table
[ {PRESERVE | PURGE} MATERIALIZED VIEW LOG ]
[ {DROP [ ALL ] | REUSE} STORAGE ] [ CASCADE ] ; {code}
[SQL
Server|https://learn.microsoft.com/en-us/sql/t-sql/statements/truncate-table-transact-sql?view=sql-server-ver16]
allow partitions.
{code:java}
TRUNCATE TABLE
{ database_name.schema_name.table_name | schema_name.table_name |
table_name }
[ WITH ( PARTITIONS ( { | }
[ , ...n ] ) ) ]
[ ; ]
::=
TO {code}
[Hive|https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-TruncateTable]
also allow partitions.
{code:java}
TRUNCATE [TABLE] table_name [PARTITION partition_spec];
partition_spec:
: (partition_column = partition_col_value, partition_column =
partition_col_value, ...) {code}
I suggest we add ` CONTINUE IDENTITY` and `RESTART IDENTITY`, core parser
should follow standard SQL as much as possible.
> Support TRUNCATE TABLE DDL statement in Babel parser
>
>
> Key: CALCITE-5688
> URL: https://issues.apache.org/jira/browse/CALCITE-5688
> Project: Calcite
> Issue Type: Task
> Components: babel, core
>Reporter: Sumeet
>Priority: Minor
> Labels: pull-request-available
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5688) Support TRUNCATE TABLE DDL statement in Babel parser
[
https://issues.apache.org/jira/browse/CALCITE-5688?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17720280#comment-17720280
]
Jiajun Xie commented on CALCITE-5688:
-
>From your PR, core parser support rather than babel parser.
We should change JIRA title and commit message.
Here is truncate syntax in SQL2011:
{code:java}
::=
TRUNCATE TABLE [ ]
::=
CONTINUE IDENTITY
| RESTART IDENTITY{code}
[PostgreSQL|https://www.postgresql.org/docs/current/sql-truncate.html] Similar
to it.
{code:java}
TRUNCATE [ TABLE ] [ ONLY ] name [ * ] [, ... ]
[ RESTART IDENTITY | CONTINUE IDENTITY ] [ CASCADE | RESTRICT ] {code}
[Oracle|https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/TRUNCATE-TABLE.html#GUID-B76E5846-75B5-4876-98EC-439E15E4D8A4]
more complicated.
{code:java}
TRUNCATE TABLE [schema.] table
[ {PRESERVE | PURGE} MATERIALIZED VIEW LOG ]
[ {DROP [ ALL ] | REUSE} STORAGE ] [ CASCADE ] ; {code}
[SQL
Server|https://learn.microsoft.com/en-us/sql/t-sql/statements/truncate-table-transact-sql?view=sql-server-ver16]
allow partitions.
{code:java}
TRUNCATE TABLE
{ database_name.schema_name.table_name | schema_name.table_name |
table_name }
[ WITH ( PARTITIONS ( { | }
[ , ...n ] ) ) ]
[ ; ]
::=
TO {code}
[Hive|https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-TruncateTable]
also allow partitions.
{code:java}
TRUNCATE [TABLE] table_name [PARTITION partition_spec];
partition_spec:
: (partition_column = partition_col_value, partition_column =
partition_col_value, ...) {code}
I suggest we add ` CONTINUE IDENTITY` and `RESTART IDENTITY`, core parser
should follow standard SQL as much as possible.
> Support TRUNCATE TABLE DDL statement in Babel parser
>
>
> Key: CALCITE-5688
> URL: https://issues.apache.org/jira/browse/CALCITE-5688
> Project: Calcite
> Issue Type: Task
> Components: babel, core
>Reporter: Sumeet
>Priority: Minor
> Labels: pull-request-available
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-5688) Support TRUNCATE TABLE DDL statement in Babel parser
[
https://issues.apache.org/jira/browse/CALCITE-5688?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17720280#comment-17720280
]
Jiajun Xie edited comment on CALCITE-5688 at 5/7/23 6:02 AM:
-
>From your PR, core parser support rather than babel parser.
We should change JIRA title and commit message.
Here is truncate syntax in SQL2011:
{code:java}
::=
TRUNCATE TABLE [ ]
::=
CONTINUE IDENTITY
| RESTART IDENTITY{code}
[PostgreSQL|https://www.postgresql.org/docs/current/sql-truncate.html] Similar
to it.
{code:java}
TRUNCATE [ TABLE ] [ ONLY ] name [ * ] [, ... ]
[ RESTART IDENTITY | CONTINUE IDENTITY ] [ CASCADE | RESTRICT ] {code}
[Oracle|https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/TRUNCATE-TABLE.html#GUID-B76E5846-75B5-4876-98EC-439E15E4D8A4]
more complicated.
{code:java}
TRUNCATE TABLE [schema.] table
[ {PRESERVE | PURGE} MATERIALIZED VIEW LOG ]
[ {DROP [ ALL ] | REUSE} STORAGE ] [ CASCADE ] ; {code}
[SQL
Server|https://learn.microsoft.com/en-us/sql/t-sql/statements/truncate-table-transact-sql?view=sql-server-ver16]
allow partitions.
{code:java}
TRUNCATE TABLE
{ database_name.schema_name.table_name | schema_name.table_name |
table_name }
[ WITH ( PARTITIONS ( { | }
[ , ...n ] ) ) ]
[ ; ]
::=
TO {code}
[Hive|https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-TruncateTable]
also allow partitions.
{code:java}
TRUNCATE [TABLE] table_name [PARTITION partition_spec];
partition_spec:
: (partition_column = partition_col_value, partition_column =
partition_col_value, ...) {code}
I suggest we add ` CONTINUE IDENTITY` and `RESTART IDENTITY`, core parser
should follow standard SQL as much as possible.
was (Author: jiajunbernoulli):
>From your PR, core parser support rather than babel parser.
We should change JIRA title and commit message.
Here is truncate syntax in SQL2011:
{code:java}
::=
TRUNCATE TABLE [ ]
::=
CONTINUE IDENTITY
| RESTART IDENTITY{code}
[PostgreSQL|https://www.postgresql.org/docs/current/sql-truncate.html] Similar
to it.
{code:java}
TRUNCATE [ TABLE ] [ ONLY ] name [ * ] [, ... ]
[ RESTART IDENTITY | CONTINUE IDENTITY ] [ CASCADE | RESTRICT ] {code}
[Oracle|https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/TRUNCATE-TABLE.html#GUID-B76E5846-75B5-4876-98EC-439E15E4D8A4]
more complicated.
{code:java}
TRUNCATE TABLE [schema.] table
[ {PRESERVE | PURGE} MATERIALIZED VIEW LOG ]
[ {DROP [ ALL ] | REUSE} STORAGE ] [ CASCADE ] ; {code}
[SQL
Server|https://learn.microsoft.com/en-us/sql/t-sql/statements/truncate-table-transact-sql?view=sql-server-ver16]
allow partitions.
{code:java}
TRUNCATE TABLE
{ database_name.schema_name.table_name | schema_name.table_name |
table_name }
[ WITH ( PARTITIONS ( { | }
[ , ...n ] ) ) ]
[ ; ]
::=
TO {code}
[Hive|https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-TruncateTable]
also allow partitions.
{code:java}
TRUNCATE [TABLE] table_name [PARTITION partition_spec];
partition_spec:
: (partition_column = partition_col_value, partition_column =
partition_col_value, ...) {code}
I suggest we add ` CONTINUE IDENTITY` and `RESTART IDENTITY`, core parser
should follow standard SQL as much as possible.
> Support TRUNCATE TABLE DDL statement in Babel parser
>
>
> Key: CALCITE-5688
> URL: https://issues.apache.org/jira/browse/CALCITE-5688
> Project: Calcite
> Issue Type: Task
> Components: babel, core
>Reporter: Sumeet
>Priority: Minor
> Labels: pull-request-available
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5547) JOIN USING returns incorrect column names
[
https://issues.apache.org/jira/browse/CALCITE-5547?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17719286#comment-17719286
]
Jiajun Xie commented on CALCITE-5547:
-
It's not difficult to fix.
{code:java}
select empno as two, * from emp natural join dept;
+--++---++---+--++-+-++--+
| TWO | DEPTNO | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL |
COMM | DNAME | LOC |
+--++---++---+--++-+-++--+
| 7369 | 20 | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 |
| RESEARCH | DALLAS |
{code}
New commit is
https://github.com/apache/calcite/pull/3183/commits/25f5cb7b0547ccd26cdb7c9358c3273a8133b0de
> JOIN USING returns incorrect column names
> -
>
> Key: CALCITE-5547
> URL: https://issues.apache.org/jira/browse/CALCITE-5547
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.33.0
>Reporter: Sean Broeder
>Assignee: Jiajun Xie
>Priority: Major
> Labels: pull-request-available
>
> The query
> {code:java}
> SELECT '' as \"xxx\", *
> FROM (values (1, 4, 'one')) as t1(i ,j ,t )
> LEFT JOIN (values (1, -1)) as t2(i ,k )
> USING (i) {code}
> returns columns (xxx, i, j, i0),
> but should return columns (xxx, i, j, t, k)
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5506) RelToSqlConverter should retain the aggregation logic when Project without RexInputRef on the Aggregate
[
https://issues.apache.org/jira/browse/CALCITE-5506?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17718786#comment-17718786
]
Jiajun Xie commented on CALCITE-5506:
-
[~libenchao] ，I used a more detailed description.
Will you have time to review it this month?
> RelToSqlConverter should retain the aggregation logic when Project without
> RexInputRef on the Aggregate
> ---
>
> Key: CALCITE-5506
> URL: https://issues.apache.org/jira/browse/CALCITE-5506
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Reporter: Jiajun Xie
>Assignee: Jiajun Xie
>Priority: Major
> Labels: pull-request-available
> Time Spent: 20m
> Remaining Estimate: 0h
>
> Here is a sql
> {code:java}
> select l.v as l_cost
> from (
> select 0 as v,
> 1 as k
> ) l
> join (
> select sum("cost") as v,
> 1 as k
> from (
> select 1 as "cost"
> union
> select 2 as "cost"
> )
> ) r
> on l.k = r.k {code}
> Before trimming, the RelNode is
> {code:java}
> LogicalProject(L_COST=[$0])
> LogicalJoin(condition=[=($1, $3)], joinType=[inner])
> LogicalValues(tuples=[[{ 0, 1 }]])
> LogicalProject(V=[$0], K=[1])
> LogicalAggregate(group=[{}], V=[SUM($0)])
> LogicalUnion(all=[false])
> LogicalValues(tuples=[[{ 1 }]])
> LogicalValues(tuples=[[{ 2 }]]) {code}
> After trimming, the RelNode is
> {code:java}
> LogicalProject(L_COST=[$0])
> LogicalJoin(condition=[=($1, $2)], joinType=[inner])
> LogicalValues(tuples=[[{ 0, 1 }]])
> LogicalProject(K=[1])
> LogicalAggregate(group=[{}], DUMMY=[COUNT()])
> LogicalUnion(all=[false])
> LogicalValues(tuples=[[{ 1 }]])
> LogicalValues(tuples=[[{ 2 }]]){code}
> If we convert trimmed RelNode to sql, the sql will be
> {code:java}
> SELECT *
> FROM
> (VALUES (0, 1)) AS "t" ("V", "K")
> INNER JOIN
> (SELECT 1 AS "K" -- Missing SUM()
>FROM (SELECT *
> FROM (VALUES (1)) AS "t" ("cost")
> UNION
> SELECT *
> FROM (VALUES (2)) AS "t" ("cost")) AS "t2"
> ) AS "t4"
> ON "t"."K" = "t4"."K" {code}
> The origin sql only has one row result, but the new sql that be trimmed has
> two row result.
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-5506) RelToSqlConverter should retain the aggregation logic when Project without RexInputRef on the Aggregate
[
https://issues.apache.org/jira/browse/CALCITE-5506?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Jiajun Xie updated CALCITE-5506:
Summary: RelToSqlConverter should retain the aggregation logic when Project
without RexInputRef on the Aggregate (was: RelToSqlConverter should retain the
aggregation logic)
> RelToSqlConverter should retain the aggregation logic when Project without
> RexInputRef on the Aggregate
> ---
>
> Key: CALCITE-5506
> URL: https://issues.apache.org/jira/browse/CALCITE-5506
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Reporter: Jiajun Xie
>Assignee: Jiajun Xie
>Priority: Major
> Labels: pull-request-available
> Time Spent: 20m
> Remaining Estimate: 0h
>
> Here is a sql
> {code:java}
> select l.v as l_cost
> from (
> select 0 as v,
> 1 as k
> ) l
> join (
> select sum("cost") as v,
> 1 as k
> from (
> select 1 as "cost"
> union
> select 2 as "cost"
> )
> ) r
> on l.k = r.k {code}
> Before trimming, the RelNode is
> {code:java}
> LogicalProject(L_COST=[$0])
> LogicalJoin(condition=[=($1, $3)], joinType=[inner])
> LogicalValues(tuples=[[{ 0, 1 }]])
> LogicalProject(V=[$0], K=[1])
> LogicalAggregate(group=[{}], V=[SUM($0)])
> LogicalUnion(all=[false])
> LogicalValues(tuples=[[{ 1 }]])
> LogicalValues(tuples=[[{ 2 }]]) {code}
> After trimming, the RelNode is
> {code:java}
> LogicalProject(L_COST=[$0])
> LogicalJoin(condition=[=($1, $2)], joinType=[inner])
> LogicalValues(tuples=[[{ 0, 1 }]])
> LogicalProject(K=[1])
> LogicalAggregate(group=[{}], DUMMY=[COUNT()])
> LogicalUnion(all=[false])
> LogicalValues(tuples=[[{ 1 }]])
> LogicalValues(tuples=[[{ 2 }]]){code}
> If we convert trimmed RelNode to sql, the sql will be
> {code:java}
> SELECT *
> FROM
> (VALUES (0, 1)) AS "t" ("V", "K")
> INNER JOIN
> (SELECT 1 AS "K" -- Missing SUM()
>FROM (SELECT *
> FROM (VALUES (1)) AS "t" ("cost")
> UNION
> SELECT *
> FROM (VALUES (2)) AS "t" ("cost")) AS "t2"
> ) AS "t4"
> ON "t"."K" = "t4"."K" {code}
> The origin sql only has one row result, but the new sql that be trimmed has
> two row result.
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-5547) JOIN USING returns incorrect column names
[
https://issues.apache.org/jira/browse/CALCITE-5547?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Jiajun Xie updated CALCITE-5547:
Description:
The query
{code:java}
SELECT '' as \"xxx\", *
FROM (values (1, 4, 'one')) as t1(i ,j ,t )
LEFT JOIN (values (1, -1)) as t2(i ,k )
USING (i) {code}
returns columns (xxx, i, j, i0),
but should return columns (xxx, i, j, t, k)
was:
The query SELECT '' as \"xxx\", *
FROM (values (1, 4, 'one')) as t1(i ,j ,t )
LEFT JOIN (values (1, -1)) as t2(i ,k )
USING (i)
returns columns (xxx, i, j, i0),
but should return columns (xxx, i, j, t, k)
Summary: JOIN USING returns incorrect column names (was: Join using
returns incorrect column names)
> JOIN USING returns incorrect column names
> -
>
> Key: CALCITE-5547
> URL: https://issues.apache.org/jira/browse/CALCITE-5547
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.33.0
>Reporter: Sean Broeder
>Assignee: Jiajun Xie
>Priority: Major
> Labels: pull-request-available
>
> The query
>
> {code:java}
> SELECT '' as \"xxx\", *
> FROM (values (1, 4, 'one')) as t1(i ,j ,t )
> LEFT JOIN (values (1, -1)) as t2(i ,k )
> USING (i) {code}
> returns columns (xxx, i, j, i0),
> but should return columns (xxx, i, j, t, k)
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-5547) JOIN USING returns incorrect column names
[
https://issues.apache.org/jira/browse/CALCITE-5547?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Jiajun Xie updated CALCITE-5547:
Description:
The query
{code:java}
SELECT '' as \"xxx\", *
FROM (values (1, 4, 'one')) as t1(i ,j ,t )
LEFT JOIN (values (1, -1)) as t2(i ,k )
USING (i) {code}
returns columns (xxx, i, j, i0),
but should return columns (xxx, i, j, t, k)
was:
The query
{code:java}
SELECT '' as \"xxx\", *
FROM (values (1, 4, 'one')) as t1(i ,j ,t )
LEFT JOIN (values (1, -1)) as t2(i ,k )
USING (i) {code}
returns columns (xxx, i, j, i0),
but should return columns (xxx, i, j, t, k)
> JOIN USING returns incorrect column names
> -
>
> Key: CALCITE-5547
> URL: https://issues.apache.org/jira/browse/CALCITE-5547
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.33.0
>Reporter: Sean Broeder
>Assignee: Jiajun Xie
>Priority: Major
> Labels: pull-request-available
>
> The query
> {code:java}
> SELECT '' as \"xxx\", *
> FROM (values (1, 4, 'one')) as t1(i ,j ,t )
> LEFT JOIN (values (1, -1)) as t2(i ,k )
> USING (i) {code}
> returns columns (xxx, i, j, i0),
> but should return columns (xxx, i, j, t, k)
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5547) Join using returns incorrect column names
[ https://issues.apache.org/jira/browse/CALCITE-5547?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17718018#comment-17718018 ] Jiajun Xie commented on CALCITE-5547: - The PR is ready: https://github.com/apache/calcite/pull/3183 > Join using returns incorrect column names > - > > Key: CALCITE-5547 > URL: https://issues.apache.org/jira/browse/CALCITE-5547 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.33.0 >Reporter: Sean Broeder >Assignee: Jiajun Xie >Priority: Major > Labels: pull-request-available > > The query SELECT '' as \"xxx\", * > FROM (values (1, 4, 'one')) as t1(i ,j ,t ) > LEFT JOIN (values (1, -1)) as t2(i ,k ) > USING (i) > returns columns (xxx, i, j, i0), > but should return columns (xxx, i, j, t, k) -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Assigned] (CALCITE-5547) Join using returns incorrect column names
[ https://issues.apache.org/jira/browse/CALCITE-5547?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Jiajun Xie reassigned CALCITE-5547: --- Assignee: Jiajun Xie > Join using returns incorrect column names > - > > Key: CALCITE-5547 > URL: https://issues.apache.org/jira/browse/CALCITE-5547 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.33.0 >Reporter: Sean Broeder >Assignee: Jiajun Xie >Priority: Major > Labels: pull-request-available > > The query SELECT '' as \"xxx\", * > FROM (values (1, 4, 'one')) as t1(i ,j ,t ) > LEFT JOIN (values (1, -1)) as t2(i ,k ) > USING (i) > returns columns (xxx, i, j, i0), > but should return columns (xxx, i, j, t, k) -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-5668) When parsing SQL in PostgreSQL dialect, allow unquoted table names to contain dollar sign
[ https://issues.apache.org/jira/browse/CALCITE-5668?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17718007#comment-17718007 ] Jiajun Xie commented on CALCITE-5668: - Should core allow dollar sign? I think babel can do this, here is a similar JIRA ticket: CALCITE-5029 > When parsing SQL in PostgreSQL dialect, allow unquoted table names to contain > dollar sign > - > > Key: CALCITE-5668 > URL: https://issues.apache.org/jira/browse/CALCITE-5668 > Project: Calcite > Issue Type: Bug > Components: babel >Affects Versions: 1.34.0 >Reporter: Dmitry Sysolyatin >Priority: Major > > According PostgreSQL documentation [1]: > ??SQL identifiers and key words must begin with a letter (a-z, but also > letters with diacritical marks and non-Latin letters) or an underscore (_). > Subsequent characters in an identifier or key word can be letters, > underscores, digits (0-9), or dollar signs ($).?? > To implement it, I suggest using the same approach as in CALCITE-4246. > [1] > https://www.postgresql.org/docs/15/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-5680) Wrong plan for multiple IN sub-queries with only literal operands
[
https://issues.apache.org/jira/browse/CALCITE-5680?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17717887#comment-17717887
]
Jiajun Xie commented on CALCITE-5680:
-
It it a bug, incorrect SQL is more obvious.
{code:java}
select "$cor0"."EMPNO"
from(
(
select "EMPNO",
"ENAME"
from"CATALOG"."SALES"."EMPNULLABLES"
) as "$cor0",
LATERAL (
select "DEPTNO" is not null as "cs"
from(
select "DEPTNO"
from"CATALOG"."SALES"."DEPTNULLABLES"
where "$cor0"."ENAME" = "NAME"
and "DEPTNO" > 10
) as "t1"
where 1 = "DEPTNO"
or "DEPTNO" is null
group by
"DEPTNO" is not null
) as "t4"
) as "$cor0",
LATERAL (
select "DEPTNO" is not null as "cs"
from(
select "DEPTNO"
from"CATALOG"."SALES"."DEPTNULLABLES"
where "$cor0"."ENAME" = "NAME"
and "DEPTNO" < 20
) as "t6"
where 2 = "DEPTNO"
or "DEPTNO" is null
group by
"DEPTNO" is not null
) as "t9"
where case when "$cor0"."cs" = FALSE then null
when "$cor0"."cs" is not null then true
else FALSE
end
or case when "$cor0"."cs" = FALSE then null -- t9 was missing
when "$cor0"."cs" is not null then true
else FALSE
end{code}
> Wrong plan for multiple IN sub-queries with only literal operands
> -
>
> Key: CALCITE-5680
> URL: https://issues.apache.org/jira/browse/CALCITE-5680
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.34.0
>Reporter: Runkang He
>Assignee: Runkang He
>Priority: Major
> Labels: pull-request-available
>
> When the query contains multiple IN sub-queries with only literal operands,
> and connected with OR predicate in WHERE clause, the result is wrong. The
> minimal reproducer is below:
> SQL:
> {code:sql}
> select empno from sales.empnullables
> where 1 in (
> select deptno from sales.deptnullables where name = 'dept1')
> or 2 in (
> select deptno from sales.deptnullables where name = 'dept2')
> {code}
> The Plan generated by calcite master branch: (Notice the bold part of IS
> NULL(*$2*) in the downstream LogicalFilter)
> {code:sql}
> LogicalProject(EMPNO=[$0])
> LogicalFilter(condition=[OR(CASE(IS NULL($2), false, =($1, false),
> null:BOOLEAN, IS NOT NULL($1), true, false), CASE(IS NULL(*$2*), false, =($1,
> false), null:BOOLEAN, IS NOT NULL($1), true, false))])
> LogicalJoin(condition=[true], joinType=[left])
> LogicalJoin(condition=[true], joinType=[left])
> LogicalProject(EMPNO=[$0])
> LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
> LogicalSort(sort0=[$0], dir0=[DESC], fetch=[1])
> LogicalAggregate(group=[{0}], c=[COUNT()])
> LogicalProject(cs=[IS NOT NULL($0)])
> LogicalFilter(condition=[OR(=(1, $0), IS NULL($0))])
> LogicalProject(DEPTNO=[$0])
> LogicalFilter(condition=[=($1, 'dept1')])
> LogicalTableScan(table=[[CATALOG, SALES, DEPTNULLABLES]])
> LogicalSort(sort0=[$0], dir0=[DESC], fetch=[1])
> LogicalAggregate(group=[{0}], c=[COUNT()])
> LogicalProject(cs=[IS NOT NULL($0)])
> LogicalFilter(condition=[OR(=(2, $0), IS NULL($0))])
> LogicalProject(DEPTNO=[$0])
> LogicalFilter(condition=[=($1, 'dept2')])
> LogicalTableScan(table=[[CATALOG, SALES, DEPTNULLABLES]])
> {code}
> The wrong part is that when build the downstream LogicalFilter for the two
> sub-queries, the filter for the second sub-query is CASE(IS NULL($2), false,
> =($1, false), null:BOOLEAN, IS NOT NULL($1), true, false), notice that *$2
> should be the second sub-query's intermediate table field dt.c(which field
> index is $4), and $1 should be the second sub-query's intermediate table
> field dt.cs(which field index is $3), but now the actual reference is the
> first sub-query's, this leads to wrong plan, and wrong result*.
> The root cause is that intermediate table alias is the same as the previous
> sub-query's, but when lookup intermediate table field, it always returns the
> previous one which is not belong to the current subquery.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5656) RelToSqlConverter has an error using ordinal for ORDER BY
[
https://issues.apache.org/jira/browse/CALCITE-5656?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17715214#comment-17715214
]
Jiajun Xie commented on CALCITE-5656:
-
The issue look like CALCITE-5530.
I think my PR can solve the problem.
https://github.com/apache/calcite/pull/3085
> RelToSqlConverter has an error using ordinal for ORDER BY
> -
>
> Key: CALCITE-5656
> URL: https://issues.apache.org/jira/browse/CALCITE-5656
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.34.0
>Reporter: luoping.zhang
>Assignee: luoping.zhang
>Priority: Critical
> Labels: pull-request-available
> Attachments: image-2023-04-19-12-26-32-719.png
>
> Time Spent: 50m
> Remaining Estimate: 0h
>
> For example:
> Query SQL is
> {code:java}
> select a from table order by coalesce(a,b){code}
> When we convert this sql to relNode and convert it back to SQL by
> RelToConverter, the result is:
> {code:java}
> select a from table order by 2{code}
> But this sql syntax is wrong because there is no ordinal 2
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5630) Window with rows equivalence error in volcano planner
[
https://issues.apache.org/jira/browse/CALCITE-5630?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17714012#comment-17714012
]
Jiajun Xie commented on CALCITE-5630:
-
HepPlanner has same issue
[https://github.com/apache/calcite/blob/2d0b3acb11169b307dc165c1fae8b7c92b888ae9/core/src/main/java/org/apache/calcite/plan/hep/HepPlanner.java#L830]
> Window with rows equivalence error in volcano planner
> -
>
> Key: CALCITE-5630
> URL: https://issues.apache.org/jira/browse/CALCITE-5630
> Project: Calcite
> Issue Type: Bug
>Reporter: Jiajun Xie
>Priority: Major
>
> Here is UT
> {code:java}
> @Test void testUnionWindow() {
> String selectSqlBase =
> "SELECT 'col{?}' as col, sum(\"salary\" ) OVER("
> + "PARTITION BY \"deptno\" "
> + "ORDER BY \"empid\" "
> + "ROWS BETWEEN CURRENT ROW AND {?} FOLLOWING) "
> + "FROM \"hr\".\"emps\"";
> String selectSql1 = selectSqlBase.replace("{?}", "1"); // window1
> String selectSql2 = selectSqlBase.replace("{?}", "2"); // window2
> String sql = selectSql1 + "\nunion all\n" + selectSql2;
> CalciteAssert.hr().query(sql).runs()
> .returns("COL=col1; EXPR$1=8000.0\n"
> + "COL=col1; EXPR$1=21500.0\n"
> + "COL=col1; EXPR$1=18500.0\n"
> + "COL=col1; EXPR$1=7000.0\n"
> + "COL=col2; EXPR$1=8000.0\n"
> + "COL=col2; EXPR$1=21500.0\n"
> + "COL=col2; EXPR$1=18500.0\n"
> + "COL=col2; EXPR$1=7000.0\n");
> // sum that for col1 is same as sum that for col2, this is error
> }
> {code}
> Because RelDigest is same between window1 and window2[1],
> volcano planner use window1 replace window2.
>
> [[1]
> https://github.com/apache/calcite/blob/2d0b3acb11169b307dc165c1fae8b7c92b888ae9/core/src/main/java/org/apache/calcite/plan/volcano/VolcanoPlanner.java#L1289|https://github.com/apache/calcite/blob/2d0b3acb11169b307dc165c1fae8b7c92b888ae9/core/src/main/java/org/apache/calcite/plan/volcano/VolcanoPlanner.java#L1289]
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5632) Query with ORDER BY and null values NullPointer exception
[
https://issues.apache.org/jira/browse/CALCITE-5632?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17710967#comment-17710967
]
Jiajun Xie commented on CALCITE-5632:
-
In my opinion, if a non-nullable column has a null value, an NPE will be thrown.
This issue is also useful for other users.
[~felych] , thanks for your clarification.
> Query with ORDER BY and null values NullPointer exception
> -
>
> Key: CALCITE-5632
> URL: https://issues.apache.org/jira/browse/CALCITE-5632
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.34.0
>Reporter: Kirill Kim
>Priority: Major
> Attachments: image-2023-04-09-11-49-05-991.png,
> image-2023-04-09-11-50-18-796.png
>
>
> I try to perform query over over some table data in Java using Calcite. I try
> to execute query:
> {code:java}
> select distinct tbl.col1 , tbl.col2 from infoset.SUBEKT tbl order by col1 asc
> nulls last , col2 asc nulls last{code}
> And the following exception occurs
> {code:java}
> java.lang.NullPointerException at
> org.apache.calcite.runtime.Utilities.compare(Utilities.java:202)
> at Baz$2.compare(ANONYMOUS.java:15)
> at Baz$2.compare(ANONYMOUS.java:27)
> at java.util.TreeMap.getEntryUsingComparator(TreeMap.java:376)
> at java.util.TreeMap.getEntry(TreeMap.java:345)
> at java.util.TreeMap.get(TreeMap.java:278)
> at
> org.apache.calcite.linq4j.EnumerableDefaults.toLookup_(EnumerableDefaults.java:3608)
>
> at
> org.apache.calcite.linq4j.EnumerableDefaults$14.enumerator(EnumerableDefaults.java:2660)
>
> at
> org.apache.calcite.linq4j.AbstractEnumerable.iterator(AbstractEnumerable.java:33)
>
> at org.apache.calcite.avatica.MetaImpl.createCursor(MetaImpl.java:90)
> at
> org.apache.calcite.avatica.AvaticaResultSet.execute(AvaticaResultSet.java:186)
>
> at org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:64)
> at org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:43)
> at
> org.apache.calcite.avatica.AvaticaConnection$1.execute(AvaticaConnection.java:669)
>
> at
> org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:636)
>
> at
> org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:677)
>
> at
> org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:157)
>
> at
> org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:228)
>
> at
> ru.spi2.modules.infosetquery.InfosetQueryPlugin.executeQuery(InfosetQueryPlugin.java:101)
> {code}
> The error occurs because col1 has some rows with null values. If I order only
> by col1 or col2, all works well.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5632) Query with ORDER BY and null values NullPointer exception
[
https://issues.apache.org/jira/browse/CALCITE-5632?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17709950#comment-17709950
]
Jiajun Xie commented on CALCITE-5632:
-
I cannot reproduce your problem in main branch, which calcite and avatica
version you using?
{code:java}
# [CALCITE-5632] Query with ORDER BY and null values NullPointer exception
select distinct MGR, empno
from emp
order by MGR asc nulls last ,
empno asc nulls last;
+--+---+
| MGR | EMPNO |
+--+---+
| 7566 | 7788 |
| 7566 | 7902 |
| 7698 | 7499 |
| 7698 | 7521 |
| 7698 | 7654 |
| 7698 | 7844 |
| 7698 | 7900 |
| 7782 | 7934 |
| 7788 | 7876 |
| 7839 | 7566 |
| 7839 | 7698 |
| 7839 | 7782 |
| 7902 | 7369 |
| | 7839 |
+--+---+
(14 rows)
!ok {code}
One null value in MGR and the result is right in agg.iq, not your exception.
> Query with ORDER BY and null values NullPointer exception
> -
>
> Key: CALCITE-5632
> URL: https://issues.apache.org/jira/browse/CALCITE-5632
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.34.0
>Reporter: Kirill Kim
>Priority: Major
>
> I try to perform query over over some table data in Java using Calcite. I try
> to execute query:
> {code:java}
> select distinct tbl.col1 , tbl.col2 from infoset.SUBEKT tbl order by col1 asc
> nulls last , col2 asc nulls last{code}
> And the following exception occurs
> {code:java}
> java.lang.NullPointerException at
> org.apache.calcite.runtime.Utilities.compare(Utilities.java:202)
> at Baz$2.compare(ANONYMOUS.java:15)
> at Baz$2.compare(ANONYMOUS.java:27)
> at java.util.TreeMap.getEntryUsingComparator(TreeMap.java:376)
> at java.util.TreeMap.getEntry(TreeMap.java:345)
> at java.util.TreeMap.get(TreeMap.java:278)
> at
> org.apache.calcite.linq4j.EnumerableDefaults.toLookup_(EnumerableDefaults.java:3608)
>
> at
> org.apache.calcite.linq4j.EnumerableDefaults$14.enumerator(EnumerableDefaults.java:2660)
>
> at
> org.apache.calcite.linq4j.AbstractEnumerable.iterator(AbstractEnumerable.java:33)
>
> at org.apache.calcite.avatica.MetaImpl.createCursor(MetaImpl.java:90)
> at
> org.apache.calcite.avatica.AvaticaResultSet.execute(AvaticaResultSet.java:186)
>
> at org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:64)
> at org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:43)
> at
> org.apache.calcite.avatica.AvaticaConnection$1.execute(AvaticaConnection.java:669)
>
> at
> org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:636)
>
> at
> org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:677)
>
> at
> org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:157)
>
> at
> org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:228)
>
> at
> ru.spi2.modules.infosetquery.InfosetQueryPlugin.executeQuery(InfosetQueryPlugin.java:101)
> {code}
> The error occurs because col1 has some rows with null values. If I order only
> by col1 or col2, all works well.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5637) RelOptRulesTest testPushAggregateThroughJoin7 optimized query not equivalent to the original one
[
https://issues.apache.org/jira/browse/CALCITE-5637?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17709948#comment-17709948
]
Jiajun Xie commented on CALCITE-5637:
-
The result in `agg.iq` is right
{code:java}
# [CALCITE-5637] RelOptRulesTest testPushAggregateThroughJoin7 optimized query
not equivalent to the original one
SELECT ANY_VALUE(t.SAL)
FROM EMP
INNER JOIN (SELECT SAL
FROM EMP
GROUP BY SAL) AS t
ON EMP.SAL = t.SAL;
+-+
| EXPR$0 |
+-+
| 5000.00 |
+-+
(1 row)
!ok {code}
so the issue is not a bug for Calcite.
> RelOptRulesTest testPushAggregateThroughJoin7 optimized query not equivalent
> to the original one
>
>
> Key: CALCITE-5637
> URL: https://issues.apache.org/jira/browse/CALCITE-5637
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Pinhan Zhao
>Priority: Major
>
> [https://github.com/apache/calcite/blob/fb340ece8c5e60ecfd6371950f8dcb665c85a712/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java#L5046]
> We first converted plans to SQL queries, resulting in two queries below:
> {code:sql}
> SELECT ANY_VALUE(t0.SAL) FROM EMP INNER JOIN (SELECT SAL FROM EMP GROUP BY
> SAL) AS t0 ON EMP.SAL = t0.SAL;{code}
> and
> {code:sql}
> SELECT ANY_VALUE(t4.SAL) FROM (SELECT SAL FROM EMP GROUP BY SAL) AS t3 INNER
> JOIN (SELECT SAL FROM EMP GROUP BY SAL) AS t4 ON t3.SAL = t4.SAL;{code}
> However, with the DB:
> {code:sql}
> CREATE TABLE EMP (
> EMPNO INTEGER,
> DEPTNO INTEGER,
> ENAME VARCHAR(20),
> JOB VARCHAR(20),
> MGR INTEGER,
> HIREDATE DATE,
> SAL INTEGER,
> COMM INTEGER,
> SLACKER INTEGER
> );
> INSERT INTO EMP VALUES (-1, 1, '2147483648', '-2147483649', 0, '1970-01-01',
> 0, 0, 1);
> INSERT INTO EMP VALUES (0, 1, '2147483648', '-2147483649', NULL,
> '1970-01-01', 0, 0, 1);{code}
>
> the outputs of these two queries running on MySQL are different:
> {code:sql}
> ANY_VALUE(t0.SAL)
> 0
> 0{code}
> vs
> {code:sql}
> ANY_VALUE(t4.SAL)
> 0{code}
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5637) RelOptRulesTest testPushAggregateThroughJoin7 optimized query not equivalent to the original one
[
https://issues.apache.org/jira/browse/CALCITE-5637?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17709947#comment-17709947
]
Jiajun Xie commented on CALCITE-5637:
-
I think MySQL result that contain two row is error.
If you use other aggregate function in mysql
{code:java}
SELECT MAX(t0.SAL) FROM EMP INNER JOIN (SELECT SAL FROM EMP GROUP BY SAL) AS t0
ON EMP.SAL = t0.SAL; {code}
the result is one row.
||MAX(t0.SAL)||
|0|
> RelOptRulesTest testPushAggregateThroughJoin7 optimized query not equivalent
> to the original one
>
>
> Key: CALCITE-5637
> URL: https://issues.apache.org/jira/browse/CALCITE-5637
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Pinhan Zhao
>Priority: Major
>
> [https://github.com/apache/calcite/blob/fb340ece8c5e60ecfd6371950f8dcb665c85a712/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java#L5046]
> We first converted plans to SQL queries, resulting in two queries below:
> {code:sql}
> SELECT ANY_VALUE(t0.SAL) FROM EMP INNER JOIN (SELECT SAL FROM EMP GROUP BY
> SAL) AS t0 ON EMP.SAL = t0.SAL;{code}
> and
> {code:sql}
> SELECT ANY_VALUE(t4.SAL) FROM (SELECT SAL FROM EMP GROUP BY SAL) AS t3 INNER
> JOIN (SELECT SAL FROM EMP GROUP BY SAL) AS t4 ON t3.SAL = t4.SAL;{code}
> However, with the DB:
> {code:sql}
> CREATE TABLE EMP (
> EMPNO INTEGER,
> DEPTNO INTEGER,
> ENAME VARCHAR(20),
> JOB VARCHAR(20),
> MGR INTEGER,
> HIREDATE DATE,
> SAL INTEGER,
> COMM INTEGER,
> SLACKER INTEGER
> );
> INSERT INTO EMP VALUES (-1, 1, '2147483648', '-2147483649', 0, '1970-01-01',
> 0, 0, 1);
> INSERT INTO EMP VALUES (0, 1, '2147483648', '-2147483649', NULL,
> '1970-01-01', 0, 0, 1);{code}
>
> the outputs of these two queries running on MySQL are different:
> {code:sql}
> ANY_VALUE(t0.SAL)
> 0
> 0{code}
> vs
> {code:sql}
> ANY_VALUE(t4.SAL)
> 0{code}
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-5630) Window with rows equivalence error in volcano planner
[
https://issues.apache.org/jira/browse/CALCITE-5630?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Jiajun Xie updated CALCITE-5630:
Description:
Here is UT
{code:java}
@Test void testUnionWindow() {
String selectSqlBase =
"SELECT 'col{?}' as col, sum(\"salary\" ) OVER("
+ "PARTITION BY \"deptno\" "
+ "ORDER BY \"empid\" "
+ "ROWS BETWEEN CURRENT ROW AND {?} FOLLOWING) "
+ "FROM \"hr\".\"emps\"";
String selectSql1 = selectSqlBase.replace("{?}", "1"); // window1
String selectSql2 = selectSqlBase.replace("{?}", "2"); // window2
String sql = selectSql1 + "\nunion all\n" + selectSql2;
CalciteAssert.hr().query(sql).runs()
.returns("COL=col1; EXPR$1=8000.0\n"
+ "COL=col1; EXPR$1=21500.0\n"
+ "COL=col1; EXPR$1=18500.0\n"
+ "COL=col1; EXPR$1=7000.0\n"
+ "COL=col2; EXPR$1=8000.0\n"
+ "COL=col2; EXPR$1=21500.0\n"
+ "COL=col2; EXPR$1=18500.0\n"
+ "COL=col2; EXPR$1=7000.0\n");
// sum that for col1 is same as sum that for col2, this is error
}
{code}
Because RelDigest is same between window1 and window2[1],
volcano planner use window1 replace window2.
[[1]
https://github.com/apache/calcite/blob/2d0b3acb11169b307dc165c1fae8b7c92b888ae9/core/src/main/java/org/apache/calcite/plan/volcano/VolcanoPlanner.java#L1289|https://github.com/apache/calcite/blob/2d0b3acb11169b307dc165c1fae8b7c92b888ae9/core/src/main/java/org/apache/calcite/plan/volcano/VolcanoPlanner.java#L1289]
was:
Here is UT
{code:java}
@Test void testUnionWindow() {
String selectSqlBase =
"SELECT 'col{?}' as col, sum(\"salary\" ) OVER("
+ "PARTITION BY \"deptno\" "
+ "ORDER BY \"empid\" "
+ "ROWS BETWEEN CURRENT ROW AND {?} FOLLOWING) "
+ "FROM \"hr\".\"emps\"";
String selectSql1 = selectSqlBase.replace("{?}", "1"); // window1
String selectSql2 = selectSqlBase.replace("{?}", "2"); // window2
String sql = selectSql1 + "\nunion all\n" + selectSql2;
CalciteAssert.hr().query(sql).runs()
.returns("COL=col1; EXPR$1=8000.0\n"
+ "COL=col1; EXPR$1=21500.0\n"
+ "COL=col1; EXPR$1=18500.0\n"
+ "COL=col1; EXPR$1=7000.0\n"
+ "COL=col2; EXPR$1=8000.0\n"
+ "COL=col2; EXPR$1=21500.0\n"
+ "COL=col2; EXPR$1=18500.0\n"
+ "COL=col2; EXPR$1=7000.0\n");
// sum that for col1 is same as sum that for col2, this is error
}
{code}
Because RelDigest is same between window1 and window2,
volcano planner use window1 replace window2.
> Window with rows equivalence error in volcano planner
> -
>
> Key: CALCITE-5630
> URL: https://issues.apache.org/jira/browse/CALCITE-5630
> Project: Calcite
> Issue Type: Bug
>Reporter: Jiajun Xie
>Priority: Major
>
> Here is UT
> {code:java}
> @Test void testUnionWindow() {
> String selectSqlBase =
> "SELECT 'col{?}' as col, sum(\"salary\" ) OVER("
> + "PARTITION BY \"deptno\" "
> + "ORDER BY \"empid\" "
> + "ROWS BETWEEN CURRENT ROW AND {?} FOLLOWING) "
> + "FROM \"hr\".\"emps\"";
> String selectSql1 = selectSqlBase.replace("{?}", "1"); // window1
> String selectSql2 = selectSqlBase.replace("{?}", "2"); // window2
> String sql = selectSql1 + "\nunion all\n" + selectSql2;
> CalciteAssert.hr().query(sql).runs()
> .returns("COL=col1; EXPR$1=8000.0\n"
> + "COL=col1; EXPR$1=21500.0\n"
> + "COL=col1; EXPR$1=18500.0\n"
> + "COL=col1; EXPR$1=7000.0\n"
> + "COL=col2; EXPR$1=8000.0\n"
> + "COL=col2; EXPR$1=21500.0\n"
> + "COL=col2; EXPR$1=18500.0\n"
> + "COL=col2; EXPR$1=7000.0\n");
> // sum that for col1 is same as sum that for col2, this is error
> }
> {code}
> Because RelDigest is same between window1 and window2[1],
> volcano planner use window1 replace window2.
>
> [[1]
> https://github.com/apache/calcite/blob/2d0b3acb11169b307dc165c1fae8b7c92b888ae9/core/src/main/java/org/apache/calcite/plan/volcano/VolcanoPlanner.java#L1289|https://github.com/apache/calcite/blob/2d0b3acb11169b307dc165c1fae8b7c92b888ae9/core/src/main/java/org/apache/calcite/plan/volcano/VolcanoPlanner.java#L1289]
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-5630) Window with rows equivalence error in volcano planner
Jiajun Xie created CALCITE-5630:
---
Summary: Window with rows equivalence error in volcano planner
Key: CALCITE-5630
URL: https://issues.apache.org/jira/browse/CALCITE-5630
Project: Calcite
Issue Type: Bug
Reporter: Jiajun Xie
Here is UT
{code:java}
@Test void testUnionWindow() {
String selectSqlBase =
"SELECT 'col{?}' as col, sum(\"salary\" ) OVER("
+ "PARTITION BY \"deptno\" "
+ "ORDER BY \"empid\" "
+ "ROWS BETWEEN CURRENT ROW AND {?} FOLLOWING) "
+ "FROM \"hr\".\"emps\"";
String selectSql1 = selectSqlBase.replace("{?}", "1"); // window1
String selectSql2 = selectSqlBase.replace("{?}", "2"); // window2
String sql = selectSql1 + "\nunion all\n" + selectSql2;
CalciteAssert.hr().query(sql).runs()
.returns("COL=col1; EXPR$1=8000.0\n"
+ "COL=col1; EXPR$1=21500.0\n"
+ "COL=col1; EXPR$1=18500.0\n"
+ "COL=col1; EXPR$1=7000.0\n"
+ "COL=col2; EXPR$1=8000.0\n"
+ "COL=col2; EXPR$1=21500.0\n"
+ "COL=col2; EXPR$1=18500.0\n"
+ "COL=col2; EXPR$1=7000.0\n");
// sum that for col1 is same as sum that for col2, this is error
}
{code}
Because RelDigest is same between window1 and window2,
volcano planner use window1 replace window2.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5597) SELECT DISTINCT query with ORDER BY column will get error result
[
https://issues.apache.org/jira/browse/CALCITE-5597?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17707620#comment-17707620
]
Jiajun Xie commented on CALCITE-5597:
-
Yes, I changed the summary.
> SELECT DISTINCT query with ORDER BY column will get error result
>
>
> Key: CALCITE-5597
> URL: https://issues.apache.org/jira/browse/CALCITE-5597
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Jiajun Xie
>Priority: Major
>
> Here is unit test
> {code:java}
> @Test void testDistinctOrderByRand() {
> final String sql = "select distinct deptno from emp order by rand()";
> sql(sql).ok();
> } {code}
> We will get error logical plan
> {code:java}
> LogicalProject(DEPTNO=[$0])
> LogicalSort(sort0=[$1], dir0=[ASC])
> LogicalAggregate(group=[{0, 1}]) //rand() in group, result will be error
> LogicalProject(DEPTNO=[$7], EXPR$1=[RAND()])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-5597) SELECT DISTINCT query with ORDER BY column will get error result
[
https://issues.apache.org/jira/browse/CALCITE-5597?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Jiajun Xie updated CALCITE-5597:
Summary: SELECT DISTINCT query with ORDER BY column will get error result
(was: Column that be order by should not be aggregated)
> SELECT DISTINCT query with ORDER BY column will get error result
>
>
> Key: CALCITE-5597
> URL: https://issues.apache.org/jira/browse/CALCITE-5597
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Jiajun Xie
>Priority: Major
>
> Here is unit test
> {code:java}
> @Test void testDistinctOrderByRand() {
> final String sql = "select distinct deptno from emp order by rand()";
> sql(sql).ok();
> } {code}
> We will get error logical plan
> {code:java}
> LogicalProject(DEPTNO=[$0])
> LogicalSort(sort0=[$1], dir0=[ASC])
> LogicalAggregate(group=[{0, 1}]) //rand() in group, result will be error
> LogicalProject(DEPTNO=[$7], EXPR$1=[RAND()])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5506) RelToSqlConverter should retain the aggregation logic
[
https://issues.apache.org/jira/browse/CALCITE-5506?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17707249#comment-17707249
]
Jiajun Xie commented on CALCITE-5506:
-
[~libenchao] , yes.
RelFieldTrimmer is correct.
I found the case after column trimming, and I think it is useful for other
people reproduce the issue because
it is difficult for us to directly build such a logical plan by writing SQL.
> RelToSqlConverter should retain the aggregation logic
> -
>
> Key: CALCITE-5506
> URL: https://issues.apache.org/jira/browse/CALCITE-5506
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Reporter: Jiajun Xie
>Assignee: Jiajun Xie
>Priority: Major
> Labels: pull-request-available
> Time Spent: 20m
> Remaining Estimate: 0h
>
> Here is a sql
> {code:java}
> select l.v as l_cost
> from (
> select 0 as v,
> 1 as k
> ) l
> join (
> select sum("cost") as v,
> 1 as k
> from (
> select 1 as "cost"
> union
> select 2 as "cost"
> )
> ) r
> on l.k = r.k {code}
> Before trimming, the RelNode is
> {code:java}
> LogicalProject(L_COST=[$0])
> LogicalJoin(condition=[=($1, $3)], joinType=[inner])
> LogicalValues(tuples=[[{ 0, 1 }]])
> LogicalProject(V=[$0], K=[1])
> LogicalAggregate(group=[{}], V=[SUM($0)])
> LogicalUnion(all=[false])
> LogicalValues(tuples=[[{ 1 }]])
> LogicalValues(tuples=[[{ 2 }]]) {code}
> After trimming, the RelNode is
> {code:java}
> LogicalProject(L_COST=[$0])
> LogicalJoin(condition=[=($1, $2)], joinType=[inner])
> LogicalValues(tuples=[[{ 0, 1 }]])
> LogicalProject(K=[1])
> LogicalAggregate(group=[{}], DUMMY=[COUNT()])
> LogicalUnion(all=[false])
> LogicalValues(tuples=[[{ 1 }]])
> LogicalValues(tuples=[[{ 2 }]]){code}
> If we convert trimmed RelNode to sql, the sql will be
> {code:java}
> SELECT *
> FROM
> (VALUES (0, 1)) AS "t" ("V", "K")
> INNER JOIN
> (SELECT 1 AS "K" -- Missing SUM()
>FROM (SELECT *
> FROM (VALUES (1)) AS "t" ("cost")
> UNION
> SELECT *
> FROM (VALUES (2)) AS "t" ("cost")) AS "t2"
> ) AS "t4"
> ON "t"."K" = "t4"."K" {code}
> The origin sql only has one row result, but the new sql that be trimmed has
> two row result.
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Resolved] (CALCITE-5605) Add BigQuery as supported library for CHR
[
https://issues.apache.org/jira/browse/CALCITE-5605?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Jiajun Xie resolved CALCITE-5605.
-
Fix Version/s: 1.35.0
Resolution: Fixed
> Add BigQuery as supported library for CHR
> -
>
> Key: CALCITE-5605
> URL: https://issues.apache.org/jira/browse/CALCITE-5605
> Project: Calcite
> Issue Type: Task
>Reporter: Tanner Clary
>Assignee: Tanner Clary
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.35.0
>
> Time Spent: 1h 50m
> Remaining Estimate: 0h
>
> Calcite already supports the {{CHR}} function for Oracle and PostgreSQL.
> BigQuery supports it as well
> ([link|https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#chr])
> so I would just like to enable it and add a couple more tests.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5605) Add BigQuery as supported library for CHR
[
https://issues.apache.org/jira/browse/CALCITE-5605?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17707085#comment-17707085
]
Jiajun Xie commented on CALCITE-5605:
-
Fixed in
[{{a10b858}}|https://github.com/apache/calcite/commit/a10b858664250f9005e32857e57a18b8c48ec931],
; thanks for the fix, [~tanclary]
> Add BigQuery as supported library for CHR
> -
>
> Key: CALCITE-5605
> URL: https://issues.apache.org/jira/browse/CALCITE-5605
> Project: Calcite
> Issue Type: Task
>Reporter: Tanner Clary
>Assignee: Tanner Clary
>Priority: Major
> Labels: pull-request-available
> Time Spent: 1h 50m
> Remaining Estimate: 0h
>
> Calcite already supports the {{CHR}} function for Oracle and PostgreSQL.
> BigQuery supports it as well
> ([link|https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#chr])
> so I would just like to enable it and add a couple more tests.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5588) ArrayIndexOutOfBoundsException on select
[
https://issues.apache.org/jira/browse/CALCITE-5588?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17705033#comment-17705033
]
Jiajun Xie commented on CALCITE-5588:
-
This error can be reproduced in join.iq
{code:java}
> Caused by: java.lang.ArrayIndexOutOfBoundsException: Index 2 out of bounds
> for length 2
> at
> com.google.common.collect.RegularImmutableList.get(RegularImmutableList.java:77)
> at
> org.apache.calcite.sql2rel.RelFieldTrimmer.trimFields(RelFieldTrimmer.java:1224)
> at jdk.internal.reflect.GeneratedMethodAccessor1.invoke(Unknown Source)
> at
> java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.base/java.lang.reflect.Method.invoke(Method.java:566)
> at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:532)
> at
> org.apache.calcite.sql2rel.RelFieldTrimmer.dispatchTrimFields(RelFieldTrimmer.java:283)
> at
> org.apache.calcite.sql2rel.RelFieldTrimmer.trimChild(RelFieldTrimmer.java:225)
> at
> org.apache.calcite.sql2rel.RelFieldTrimmer.trimFields(RelFieldTrimmer.java:824)
> {code}
In SqlToRelConverterTest, we will another error
{code:java}
All correlation variables should resolve to the same namespace. Prev
ns=org.apache.calcite.sql.validate.AliasNamespace@689d96f8, new
ns=org.apache.calcite.sql.validate.AliasNamespace@6b24b09e
java.lang.AssertionError: All correlation variables should resolve to the same
namespace. Prev ns=org.apache.calcite.sql.validate.AliasNamespace@689d96f8, new
ns=org.apache.calcite.sql.validate.AliasNamespace@6b24b09e
at
org.apache.calcite.sql2rel.SqlToRelConverter.getCorrelationUse(SqlToRelConverter.java:3042)
at
org.apache.calcite.sql2rel.SqlToRelConverter.createJoin(SqlToRelConverter.java:2952)
at
org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.register(SqlToRelConverter.java:5024)
at
org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.reRegister(SqlToRelConverter.java:5089)
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertOnCondition(SqlToRelConverter.java:3288)
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertJoin(SqlToRelConverter.java:3211)
{code}
> ArrayIndexOutOfBoundsException on select
>
>
> Key: CALCITE-5588
> URL: https://issues.apache.org/jira/browse/CALCITE-5588
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.33.0
>Reporter: Magnus Mogren
>Priority: Major
>
> Execute this SQL
> {code:java}
> select Header.Name from ( VALUES (1, 'A'), (2, 'B')) as Header(Id, Name) join
> (values (11, 1), (12, 1), (21, 2)) as Version(Id, Parent) on not exists
> (select 1 from (values (11, 1), (12, 1), (21, 2)) as Version2(Id, Parent)
> where Version2.Parent = Header.Id and Version2.Id > Version.Id) {code}
>
> *Expected result*
> A ResultSet containing this
> |*NAME*|
> |A|
> |A|
> |B|
>
> *Actual result*
> {color:#FF}ArrayIndexOutOfBoundsException: Index 2 out of bounds for
> length 2{color}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-5597) Column that be order by should not be aggregated
[
https://issues.apache.org/jira/browse/CALCITE-5597?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Jiajun Xie updated CALCITE-5597:
Summary: Column that be order by should not be aggregated (was: Column
that be order by should not be aggregate)
> Column that be order by should not be aggregated
>
>
> Key: CALCITE-5597
> URL: https://issues.apache.org/jira/browse/CALCITE-5597
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Jiajun Xie
>Priority: Major
>
> Here is unit test
> {code:java}
> @Test void testDistinctOrderByRand() {
> final String sql = "select distinct deptno from emp order by rand()";
> sql(sql).ok();
> } {code}
> We will get error logical plan
> {code:java}
> LogicalProject(DEPTNO=[$0])
> LogicalSort(sort0=[$1], dir0=[ASC])
> LogicalAggregate(group=[{0, 1}]) //rand() in group, result will be error
> LogicalProject(DEPTNO=[$7], EXPR$1=[RAND()])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-5597) Column that be order by should not be aggregate
Jiajun Xie created CALCITE-5597:
---
Summary: Column that be order by should not be aggregate
Key: CALCITE-5597
URL: https://issues.apache.org/jira/browse/CALCITE-5597
Project: Calcite
Issue Type: Bug
Components: core
Reporter: Jiajun Xie
Here is unit test
{code:java}
@Test void testDistinctOrderByRand() {
final String sql = "select distinct deptno from emp order by rand()";
sql(sql).ok();
} {code}
We will get error logical plan
{code:java}
LogicalProject(DEPTNO=[$0])
LogicalSort(sort0=[$1], dir0=[ASC])
LogicalAggregate(group=[{0, 1}]) //rand() in group, result will be error
LogicalProject(DEPTNO=[$7], EXPR$1=[RAND()])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
{code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-5590) NullPointerException when converting 'in' expression that is used inside select list and group by
Jiajun Xie created CALCITE-5590:
---
Summary: NullPointerException when converting 'in' expression
that is used inside select list and group by
Key: CALCITE-5590
URL: https://issues.apache.org/jira/browse/CALCITE-5590
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.33.0
Reporter: Jiajun Xie
In CALCITE-5209, we changed sub-query handle way.
It can solve some problems, but it also introduces some new problems.
Here is a simple case to reproduce, it can work before 1.33.0
{code:java}
@Test void testGroupByCaseWhen() {
final String sql = "select case when deptno in (1) then 1 end as col\n"
+ "from emp\n"
+ "group by deptno, case when deptno in (1) then 1 else 0 end";
sql(sql).ok();
} {code}
{code:java}
while converting CASE WHEN `EMP`.`DEPTNO` IN (1) THEN 1 ELSE NULL END
java.lang.RuntimeException: while converting CASE WHEN `EMP`.`DEPTNO` IN (1)
THEN 1 ELSE NULL END
at
org.apache.calcite.sql2rel.ReflectiveConvertletTable.lambda$registerNodeTypeMethod$0(ReflectiveConvertletTable.java:86)
at
org.apache.calcite.sql2rel.SqlNodeToRexConverterImpl.convertCall(SqlNodeToRexConverterImpl.java:63)
at
org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:5591)
at
org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:4875)
at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:139)
at
org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.convertExpression(SqlToRelConverter.java:5454)
at
org.apache.calcite.sql2rel.StandardConvertletTable.lambda$new$9(StandardConvertletTable.java:205)
at
org.apache.calcite.sql2rel.SqlNodeToRexConverterImpl.convertCall(SqlNodeToRexConverterImpl.java:63)
at
org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:5591)
at
org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:4875)
at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:139)
at
org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.convertExpression(SqlToRelConverter.java:5454)
at
org.apache.calcite.sql2rel.SqlToRelConverter.createAggImpl(SqlToRelConverter.java:3316)
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertAgg(SqlToRelConverter.java:3158)
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectImpl(SqlToRelConverter.java:784)
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertSelect(SqlToRelConverter.java:682)
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive(SqlToRelConverter.java:3680)
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:597)
at
org.apache.calcite.test.SqlToRelTestBase$TesterImpl.convertSqlToRel(SqlToRelTestBase.java:639)
at
org.apache.calcite.test.SqlToRelTestBase$TesterImpl.assertConvertsTo(SqlToRelTestBase.java:758)
at
org.apache.calcite.test.SqlToRelConverterTest$Sql.convertsTo(SqlToRelConverterTest.java:4502)
at
org.apache.calcite.test.SqlToRelConverterTest$Sql.ok(SqlToRelConverterTest.java:4494)
at
org.apache.calcite.test.SqlToRelConverterTest.testGroupByCaseWhen(SqlToRelConverterTest.java:4028){code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5570) Support nested map type for SqlDataTypeSpec
[
https://issues.apache.org/jira/browse/CALCITE-5570?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17699331#comment-17699331
]
Jiajun Xie commented on CALCITE-5570:
-
[~Sergey Nuyanzin] , thanks for your review.
I changed "<" to "(" and added one unit test in `misq.iq`
> Support nested map type for SqlDataTypeSpec
> ---
>
> Key: CALCITE-5570
> URL: https://issues.apache.org/jira/browse/CALCITE-5570
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Reporter: Sergey Nuyanzin
>Assignee: Jiajun Xie
>Priority: Major
> Labels: pull-request-available
> Time Spent: 40m
> Remaining Estimate: 0h
>
> There was added a similar support for arrays/multisets at
> https://issues.apache.org/jira/browse/CALCITE-3250
> however there is no support for maps so far.
> The issue is to add such support.
>
> I think I'd like to clarify is syntax for maps since it has 2 internal
> subtypes for keys and values may be something similar to ROW with delimiter
> like
> {code:sql}
> SELECT CAST(NULL AS MAP(INT, INT));
> -- or with square brackets similar to map constructor
> SELECT CAST(NULL AS MAP[INT, INT]);
> -- or with angle (Flink syntax)
> SELECT CAST(NULL AS MAP);
> {code}
>
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Assigned] (CALCITE-5570) Support nested map type for SqlDataTypeSpec
[
https://issues.apache.org/jira/browse/CALCITE-5570?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Jiajun Xie reassigned CALCITE-5570:
---
Assignee: Jiajun Xie
> Support nested map type for SqlDataTypeSpec
> ---
>
> Key: CALCITE-5570
> URL: https://issues.apache.org/jira/browse/CALCITE-5570
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Reporter: Sergey Nuyanzin
>Assignee: Jiajun Xie
>Priority: Major
>
> There was added a similar support for arrays/multisets at
> https://issues.apache.org/jira/browse/CALCITE-3250
> however there is no support for maps so far.
> The issue is to add such support.
>
> I think I'd like to clarify is syntax for maps since it has 2 internal
> subtypes for keys and values may be something similar to ROW with delimiter
> like
> {code:sql}
> SELECT CAST(NULL AS MAP(INT, INT));
> -- or with square brackets similar to map constructor
> SELECT CAST(NULL AS MAP[INT, INT]);
> -- or with angle (Flink syntax)
> SELECT CAST(NULL AS MAP);
> {code}
>
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-5506) RelToSqlConverter should retain the aggregation logic
[
https://issues.apache.org/jira/browse/CALCITE-5506?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Jiajun Xie updated CALCITE-5506:
Summary: RelToSqlConverter should retain the aggregation logic (was:
RelToSqlConverter should retain the aggregation logic even if the aggregation
function is trimmed)
> RelToSqlConverter should retain the aggregation logic
> -
>
> Key: CALCITE-5506
> URL: https://issues.apache.org/jira/browse/CALCITE-5506
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Reporter: Jiajun Xie
>Assignee: Jiajun Xie
>Priority: Major
> Labels: pull-request-available
> Time Spent: 20m
> Remaining Estimate: 0h
>
> Here is a sql
> {code:java}
> select l.v as l_cost
> from (
> select 0 as v,
> 1 as k
> ) l
> join (
> select sum("cost") as v,
> 1 as k
> from (
> select 1 as "cost"
> union
> select 2 as "cost"
> )
> ) r
> on l.k = r.k {code}
> Before trimming, the RelNode is
> {code:java}
> LogicalProject(L_COST=[$0])
> LogicalJoin(condition=[=($1, $3)], joinType=[inner])
> LogicalValues(tuples=[[{ 0, 1 }]])
> LogicalProject(V=[$0], K=[1])
> LogicalAggregate(group=[{}], V=[SUM($0)])
> LogicalUnion(all=[false])
> LogicalValues(tuples=[[{ 1 }]])
> LogicalValues(tuples=[[{ 2 }]]) {code}
> After trimming, the RelNode is
> {code:java}
> LogicalProject(L_COST=[$0])
> LogicalJoin(condition=[=($1, $2)], joinType=[inner])
> LogicalValues(tuples=[[{ 0, 1 }]])
> LogicalProject(K=[1])
> LogicalAggregate(group=[{}], DUMMY=[COUNT()])
> LogicalUnion(all=[false])
> LogicalValues(tuples=[[{ 1 }]])
> LogicalValues(tuples=[[{ 2 }]]){code}
> If we convert trimmed RelNode to sql, the sql will be
> {code:java}
> SELECT *
> FROM
> (VALUES (0, 1)) AS "t" ("V", "K")
> INNER JOIN
> (SELECT 1 AS "K" -- Missing SUM()
>FROM (SELECT *
> FROM (VALUES (1)) AS "t" ("cost")
> UNION
> SELECT *
> FROM (VALUES (2)) AS "t" ("cost")) AS "t2"
> ) AS "t4"
> ON "t"."K" = "t4"."K" {code}
> The origin sql only has one row result, but the new sql that be trimmed has
> two row result.
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Assigned] (CALCITE-5530) RelToSqlConverter[ORDER BY] generates an incorrect field alias when 2 projection fields have the same name
[
https://issues.apache.org/jira/browse/CALCITE-5530?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Jiajun Xie reassigned CALCITE-5530:
---
Assignee: Jiajun Xie
> RelToSqlConverter[ORDER BY] generates an incorrect field alias when 2
> projection fields have the same name
> --
>
> Key: CALCITE-5530
> URL: https://issues.apache.org/jira/browse/CALCITE-5530
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.33.0
>Reporter: Abbas Gadhia
>Assignee: Jiajun Xie
>Priority: Minor
>
> In queries typical of Teradata,
> if there exists an expression alias that is also a field in the underlying
> table, any references to that field in the ORDER BY, refer to the underlying
> physical column rather than the expression alias.
> For ex. in the following query and Rel
>
> {code:java}
> SELECT UPPER(ENAME) AS EMPNO FROM scott.EMP order by EMPNO;{code}
> {noformat}
> LogicalProject(EMPNO=[$0])
> LogicalSort(sort0=[$1], dir0=[ASC])
> LogicalProject(EMPNO=[UPPER($1)], EMPNO0=[$0])
> LogicalTableScan(table=[[scott, EMP]]){noformat}
> EMPNO actually refers to the underlying physical column.
>
> The output of RelToSqlConverter is the following
> {code:java}
> SELECT UPPER(ENAME) AS EMPNO FROM scott.EMP order by EMPNO0;
> SELECT UPPER(ENAME) AS EMPNO FROM scott.EMP ORDER BY 2;{code}
> Here EMPNO0 or ordinal 2 does not exist and fails.
>
> This issue, i believe was introduced due to the changes done as part of
> https://issues.apache.org/jira/browse/CALCITE-4901
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Resolved] (CALCITE-5518) RelToSql converter generates invalid order of ROLLUP fields
[
https://issues.apache.org/jira/browse/CALCITE-5518?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Jiajun Xie resolved CALCITE-5518.
-
Fix Version/s: 1.34.0
Resolution: Fixed
> RelToSql converter generates invalid order of ROLLUP fields
> ---
>
> Key: CALCITE-5518
> URL: https://issues.apache.org/jira/browse/CALCITE-5518
> Project: Calcite
> Issue Type: Bug
> Components: jdbc-adapter
>Affects Versions: 1.32.0
>Reporter: Leonid Chistov
>Assignee: Jiajun Xie
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.34.0
>
> Time Spent: 1h 20m
> Remaining Estimate: 0h
>
> RelToSqlConverter generates wrong code for Aggregate nodes with grouping sets
> classified as `ROLLUP` grouping but with order of rollup not matching order
> of grouping fields.
> This can be demonstrated by the following test, that would fail if added to
> RelToSqlConverterTest class:
> {code:java}
> @Test void testGroupingSetsRollupNonNaturalOrder() {
> final String query = "select \"product_class_id\", \"brand_name\"\n"
> + "from \"product\"\n"
> + "group by GROUPING SETS ((\"product_class_id\", \"brand_name\"),"
> + " (\"brand_name\"), ())\n";
> final String expected = "SELECT \"product_class_id\", \"brand_name\"\n"
> + "FROM \"foodmart\".\"product\"\n"
> + "GROUP BY ROLLUP (\"brand_name\", \"product_class_id\")";
> sql(query)
> .withPostgresql().ok(expected);
> }{code}
> As the result we get the following SQL code:
> {code:java}
> SELECT product_class_id, brand_name
> FROM foodmart.product
> GROUP BY ROLLUP(product_class_id, brand_name){code}
> While the correct code would be:
> {code:java}
> SELECT product_class_id, brand_name
> FROM foodmart.product
> GROUP BY ROLLUP(brand_name, product_class_id){code}
> Source of the bug is that we treat grouping sets \{0, 1}, \{1}, {} as rollup
> grouping, but right after that we generate SQL code as if grouping sets were
> \{0, 1}, \{0}, {}.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5518) RelToSql converter generates invalid order of ROLLUP fields
[
https://issues.apache.org/jira/browse/CALCITE-5518?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17693595#comment-17693595
]
Jiajun Xie commented on CALCITE-5518:
-
Fixed in
[90599a6|https://github.com/apache/calcite/commit/90599a6e9b0143eb3d9175af7b5ff374e1b95252].
> RelToSql converter generates invalid order of ROLLUP fields
> ---
>
> Key: CALCITE-5518
> URL: https://issues.apache.org/jira/browse/CALCITE-5518
> Project: Calcite
> Issue Type: Bug
> Components: jdbc-adapter
>Affects Versions: 1.32.0
>Reporter: Leonid Chistov
>Assignee: Jiajun Xie
>Priority: Major
> Labels: pull-request-available
> Time Spent: 1h 20m
> Remaining Estimate: 0h
>
> RelToSqlConverter generates wrong code for Aggregate nodes with grouping sets
> classified as `ROLLUP` grouping but with order of rollup not matching order
> of grouping fields.
> This can be demonstrated by the following test, that would fail if added to
> RelToSqlConverterTest class:
> {code:java}
> @Test void testGroupingSetsRollupNonNaturalOrder() {
> final String query = "select \"product_class_id\", \"brand_name\"\n"
> + "from \"product\"\n"
> + "group by GROUPING SETS ((\"product_class_id\", \"brand_name\"),"
> + " (\"brand_name\"), ())\n";
> final String expected = "SELECT \"product_class_id\", \"brand_name\"\n"
> + "FROM \"foodmart\".\"product\"\n"
> + "GROUP BY ROLLUP (\"brand_name\", \"product_class_id\")";
> sql(query)
> .withPostgresql().ok(expected);
> }{code}
> As the result we get the following SQL code:
> {code:java}
> SELECT product_class_id, brand_name
> FROM foodmart.product
> GROUP BY ROLLUP(product_class_id, brand_name){code}
> While the correct code would be:
> {code:java}
> SELECT product_class_id, brand_name
> FROM foodmart.product
> GROUP BY ROLLUP(brand_name, product_class_id){code}
> Source of the bug is that we treat grouping sets \{0, 1}, \{1}, {} as rollup
> grouping, but right after that we generate SQL code as if grouping sets were
> \{0, 1}, \{0}, {}.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5506) RelToSqlConverter should retain the aggregation logic even if the aggregation function is trimmed
[
https://issues.apache.org/jira/browse/CALCITE-5506?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17693594#comment-17693594
]
Jiajun Xie commented on CALCITE-5506:
-
[~libenchao], I changed the title.
Does it precise?
> RelToSqlConverter should retain the aggregation logic even if the aggregation
> function is trimmed
> -
>
> Key: CALCITE-5506
> URL: https://issues.apache.org/jira/browse/CALCITE-5506
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Reporter: Jiajun Xie
>Assignee: Jiajun Xie
>Priority: Major
> Labels: pull-request-available
> Time Spent: 20m
> Remaining Estimate: 0h
>
> Here is a sql
> {code:java}
> select l.v as l_cost
> from (
> select 0 as v,
> 1 as k
> ) l
> join (
> select sum("cost") as v,
> 1 as k
> from (
> select 1 as "cost"
> union
> select 2 as "cost"
> )
> ) r
> on l.k = r.k {code}
> Before trimming, the RelNode is
> {code:java}
> LogicalProject(L_COST=[$0])
> LogicalJoin(condition=[=($1, $3)], joinType=[inner])
> LogicalValues(tuples=[[{ 0, 1 }]])
> LogicalProject(V=[$0], K=[1])
> LogicalAggregate(group=[{}], V=[SUM($0)])
> LogicalUnion(all=[false])
> LogicalValues(tuples=[[{ 1 }]])
> LogicalValues(tuples=[[{ 2 }]]) {code}
> After trimming, the RelNode is
> {code:java}
> LogicalProject(L_COST=[$0])
> LogicalJoin(condition=[=($1, $2)], joinType=[inner])
> LogicalValues(tuples=[[{ 0, 1 }]])
> LogicalProject(K=[1])
> LogicalAggregate(group=[{}], DUMMY=[COUNT()])
> LogicalUnion(all=[false])
> LogicalValues(tuples=[[{ 1 }]])
> LogicalValues(tuples=[[{ 2 }]]){code}
> If we convert trimmed RelNode to sql, the sql will be
> {code:java}
> SELECT *
> FROM
> (VALUES (0, 1)) AS "t" ("V", "K")
> INNER JOIN
> (SELECT 1 AS "K" -- Missing SUM()
>FROM (SELECT *
> FROM (VALUES (1)) AS "t" ("cost")
> UNION
> SELECT *
> FROM (VALUES (2)) AS "t" ("cost")) AS "t2"
> ) AS "t4"
> ON "t"."K" = "t4"."K" {code}
> The origin sql only has one row result, but the new sql that be trimmed has
> two row result.
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-5506) RelToSqlConverter should retain the aggregation logic even if the aggregation function is trimmed
[
https://issues.apache.org/jira/browse/CALCITE-5506?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Jiajun Xie updated CALCITE-5506:
Summary: RelToSqlConverter should retain the aggregation logic even if the
aggregation function is trimmed (was: RelToSqlConverter get error result
because RelFieldTrimmer lost aggregate function)
> RelToSqlConverter should retain the aggregation logic even if the aggregation
> function is trimmed
> -
>
> Key: CALCITE-5506
> URL: https://issues.apache.org/jira/browse/CALCITE-5506
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Reporter: Jiajun Xie
>Assignee: Jiajun Xie
>Priority: Major
> Labels: pull-request-available
> Time Spent: 20m
> Remaining Estimate: 0h
>
> Here is a sql
> {code:java}
> select l.v as l_cost
> from (
> select 0 as v,
> 1 as k
> ) l
> join (
> select sum("cost") as v,
> 1 as k
> from (
> select 1 as "cost"
> union
> select 2 as "cost"
> )
> ) r
> on l.k = r.k {code}
> Before trimming, the RelNode is
> {code:java}
> LogicalProject(L_COST=[$0])
> LogicalJoin(condition=[=($1, $3)], joinType=[inner])
> LogicalValues(tuples=[[{ 0, 1 }]])
> LogicalProject(V=[$0], K=[1])
> LogicalAggregate(group=[{}], V=[SUM($0)])
> LogicalUnion(all=[false])
> LogicalValues(tuples=[[{ 1 }]])
> LogicalValues(tuples=[[{ 2 }]]) {code}
> After trimming, the RelNode is
> {code:java}
> LogicalProject(L_COST=[$0])
> LogicalJoin(condition=[=($1, $2)], joinType=[inner])
> LogicalValues(tuples=[[{ 0, 1 }]])
> LogicalProject(K=[1])
> LogicalAggregate(group=[{}], DUMMY=[COUNT()])
> LogicalUnion(all=[false])
> LogicalValues(tuples=[[{ 1 }]])
> LogicalValues(tuples=[[{ 2 }]]){code}
> If we convert trimmed RelNode to sql, the sql will be
> {code:java}
> SELECT *
> FROM
> (VALUES (0, 1)) AS "t" ("V", "K")
> INNER JOIN
> (SELECT 1 AS "K" -- Missing SUM()
>FROM (SELECT *
> FROM (VALUES (1)) AS "t" ("cost")
> UNION
> SELECT *
> FROM (VALUES (2)) AS "t" ("cost")) AS "t2"
> ) AS "t4"
> ON "t"."K" = "t4"."K" {code}
> The origin sql only has one row result, but the new sql that be trimmed has
> two row result.
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5486) SubQuery not support HAVING alias in where condition
[
https://issues.apache.org/jira/browse/CALCITE-5486?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17693592#comment-17693592
]
Jiajun Xie commented on CALCITE-5486:
-
[~libenchao], you are right.
My solution only for where condition.
If we want to achieve other situations,
I need to redesign and make major modifications.
> SubQuery not support HAVING alias in where condition
>
>
> Key: CALCITE-5486
> URL: https://issues.apache.org/jira/browse/CALCITE-5486
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Reporter: Jiajun Xie
>Assignee: Jiajun Xie
>Priority: Major
> Labels: pull-request-available
> Time Spent: 1.5h
> Remaining Estimate: 0h
>
> Spark can allow HAVING alias in condition.
> We also should allow HAVING alias in condition, here is the simple case.
> {code:java}
> sql("select * from emp where sal >\n"
> + " (select avg(sal) as s"
> + "from emp having ^s^ > 0"
> + " )")
> .withConformance(SqlConformanceEnum.LENIENT)
> .fails("Column 'S' not found in any table"); // should be ok, but now is
> fails{code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5518) RelToSql converter generates invalid order of ROLLUP fields
[
https://issues.apache.org/jira/browse/CALCITE-5518?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17690859#comment-17690859
]
Jiajun Xie commented on CALCITE-5518:
-
[~julianhyde], your advice is useful.
Thank you very much!
> RelToSql converter generates invalid order of ROLLUP fields
> ---
>
> Key: CALCITE-5518
> URL: https://issues.apache.org/jira/browse/CALCITE-5518
> Project: Calcite
> Issue Type: Bug
> Components: jdbc-adapter
>Affects Versions: 1.32.0
>Reporter: Leonid Chistov
>Assignee: Jiajun Xie
>Priority: Major
> Labels: pull-request-available
> Time Spent: 50m
> Remaining Estimate: 0h
>
> RelToSqlConverter generates wrong code for Aggregate nodes with grouping sets
> classified as `ROLLUP` grouping but with order of rollup not matching order
> of grouping fields.
> This can be demonstrated by the following test, that would fail if added to
> RelToSqlConverterTest class:
> {code:java}
> @Test void testGroupingSetsRollupNonNaturalOrder() {
> final String query = "select \"product_class_id\", \"brand_name\"\n"
> + "from \"product\"\n"
> + "group by GROUPING SETS ((\"product_class_id\", \"brand_name\"),"
> + " (\"brand_name\"), ())\n";
> final String expected = "SELECT \"product_class_id\", \"brand_name\"\n"
> + "FROM \"foodmart\".\"product\"\n"
> + "GROUP BY ROLLUP (\"brand_name\", \"product_class_id\")";
> sql(query)
> .withPostgresql().ok(expected);
> }{code}
> As the result we get the following SQL code:
> {code:java}
> SELECT product_class_id, brand_name
> FROM foodmart.product
> GROUP BY ROLLUP(product_class_id, brand_name){code}
> While the correct code would be:
> {code:java}
> SELECT product_class_id, brand_name
> FROM foodmart.product
> GROUP BY ROLLUP(brand_name, product_class_id){code}
> Source of the bug is that we treat grouping sets \{0, 1}, \{1}, {} as rollup
> grouping, but right after that we generate SQL code as if grouping sets were
> \{0, 1}, \{0}, {}.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5523) RelToSql converter generates GROUP BY clause with window expression
[
https://issues.apache.org/jira/browse/CALCITE-5523?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17687547#comment-17687547
]
Jiajun Xie commented on CALCITE-5523:
-
In my opinion, it is similar as CALCITE-4491.
I have simple way to fix it.
{code:java}
if (!dialect.supportsNestedAggregations()
&& agg.getInput() instanceof Project
&& ((Project) agg.getInput()).containsOver()) {
return true;
} {code}
But I think
`[hasNestedWindowedAgg|https://github.com/gooddata/calcite/blob/a1569b0dd67f4eda781d4b44956c475a91102c97/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java#L1811]`
should cover it, I'm trying.
> RelToSql converter generates GROUP BY clause with window expression
> ---
>
> Key: CALCITE-5523
> URL: https://issues.apache.org/jira/browse/CALCITE-5523
> Project: Calcite
> Issue Type: Bug
> Components: jdbc-adapter
>Reporter: Leonid Chistov
>Priority: Major
>
> Wrong SQL code is generated when aggregation is done on the field being a
> result of window expression evaluation.
> Example can be demonstrated by adding following code to
> RelToSqlConverterTest.java:
> {code:java}
> @Test void testConvertWindowGroupByToSql() {
> String query = "SELECT * FROM ("
> + "SELECT rank() over (order by \"hire_date\") \"rank\" FROM
> \"employee\""
> + ") GROUP BY \"rank\"";
> String expected ="SELECT * FROM ("
> + "SELECT rank() over (order by \"hire_date\") AS \"$0\" FROM
> \"employee\""
> + ") GROUP BY \"$0\"";
> sql(query).ok(expected);
> }
> {code}
> Generated SQL code will look like:
> {code:java}
> SELECT RANK() OVER (ORDER BY hire_date) AS rank
> FROM foodmart.employee
> GROUP BY RANK() OVER (ORDER BY hire_date){code}
> This is incorrect - window expressions are not allowed in GROUP BY clause by
> SQL standard and Calcite itself would produce following error message if this
> SQL code would be passed as input:
> {code:java}
> Windowed aggregate expression is illegal in GROUP BY clause {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Assigned] (CALCITE-5523) RelToSql converter generates GROUP BY clause with window expression
[
https://issues.apache.org/jira/browse/CALCITE-5523?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Jiajun Xie reassigned CALCITE-5523:
---
Assignee: Jiajun Xie
> RelToSql converter generates GROUP BY clause with window expression
> ---
>
> Key: CALCITE-5523
> URL: https://issues.apache.org/jira/browse/CALCITE-5523
> Project: Calcite
> Issue Type: Bug
> Components: jdbc-adapter
>Reporter: Leonid Chistov
>Assignee: Jiajun Xie
>Priority: Major
>
> Wrong SQL code is generated when aggregation is done on the field being a
> result of window expression evaluation.
> Example can be demonstrated by adding following code to
> RelToSqlConverterTest.java:
> {code:java}
> @Test void testConvertWindowGroupByToSql() {
> String query = "SELECT * FROM ("
> + "SELECT rank() over (order by \"hire_date\") \"rank\" FROM
> \"employee\""
> + ") GROUP BY \"rank\"";
> String expected ="SELECT * FROM ("
> + "SELECT rank() over (order by \"hire_date\") AS \"$0\" FROM
> \"employee\""
> + ") GROUP BY \"$0\"";
> sql(query).ok(expected);
> }
> {code}
> Generated SQL code will look like:
> {code:java}
> SELECT RANK() OVER (ORDER BY hire_date) AS rank
> FROM foodmart.employee
> GROUP BY RANK() OVER (ORDER BY hire_date){code}
> This is incorrect - window expressions are not allowed in GROUP BY clause by
> SQL standard and Calcite itself would produce following error message if this
> SQL code would be passed as input:
> {code:java}
> Windowed aggregate expression is illegal in GROUP BY clause {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
