[jira] [Resolved] (CALCITE-6478) JSON functions should return NULL when input is NULL
[
https://issues.apache.org/jira/browse/CALCITE-6478?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
xiong duan resolved CALCITE-6478.
-
Fix Version/s: 1.38.0
Resolution: Fixed
> JSON functions should return NULL when input is NULL
> -
>
> Key: CALCITE-6478
> URL: https://issues.apache.org/jira/browse/CALCITE-6478
> Project: Calcite
> Issue Type: Improvement
>Reporter: xiong duan
>Assignee: xiong duan
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.38.0
>
>
> Here is the test in H2:
> {code:java}
> SELECT js,
>js IS JSON"json?",
>js IS JSON value "json?",
>js IS JSON SCALAR "scalar?",
>js IS JSON OBJECT "object?",
>js IS JSON ARRAY "array?"
> FROM (VALUES ('123'),
> ('"abc"'),
> ('{"a": "b"}'),
> ('[1,2]'),
> ('abc'), NULL) foo(js);
> {code}
> {code:java}
> +--+-+-+---+---+--+
> |JS|json?|json?|scalar?|object?|array?|
> +--+-+-+---+---+--+
> |123 |true |true |true |false |false |
> |"abc" |true |true |true |false |false |
> |{"a": "b"}|true |true |false |true |false |
> |[1,2] |true |true |false |false |true |
> |abc |false|false|false |false |false |
> |null |null |null |null |null |null |
> +--+-+-+---+---+--+
> {code}
> Same SQL in Calcite:
> {code:java}
> ++---+---+-+-++
> | JS | json? | json? | scalar? | object? | array? |
> ++---+---+-+-++
> | "abc" | true | true | true| false | false |
> | 123| true | true | true| false | false |
> | [1,2] | true | true | false | false | true |
> | abc| false | false | false | false | false |
> | {"a": "b"} | true | true | false | true| false |
> || false | false | false | false | false |
> ++---+---+-+-++
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6478) JSON functions should return NULL when input is NULL
[
https://issues.apache.org/jira/browse/CALCITE-6478?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17870311#comment-17870311
]
xiong duan commented on CALCITE-6478:
-
Fixed in
[3ef0999|https://github.com/apache/calcite/commit/3ef0999d392cef9484d17d761cd2dec56d7ff122].
Thanks for the review [~mbudiu] and [~caicancai].
> JSON functions should return NULL when input is NULL
> -
>
> Key: CALCITE-6478
> URL: https://issues.apache.org/jira/browse/CALCITE-6478
> Project: Calcite
> Issue Type: Improvement
>Reporter: xiong duan
>Assignee: xiong duan
>Priority: Major
> Labels: pull-request-available
>
> Here is the test in H2:
> {code:java}
> SELECT js,
>js IS JSON"json?",
>js IS JSON value "json?",
>js IS JSON SCALAR "scalar?",
>js IS JSON OBJECT "object?",
>js IS JSON ARRAY "array?"
> FROM (VALUES ('123'),
> ('"abc"'),
> ('{"a": "b"}'),
> ('[1,2]'),
> ('abc'), NULL) foo(js);
> {code}
> {code:java}
> +--+-+-+---+---+--+
> |JS|json?|json?|scalar?|object?|array?|
> +--+-+-+---+---+--+
> |123 |true |true |true |false |false |
> |"abc" |true |true |true |false |false |
> |{"a": "b"}|true |true |false |true |false |
> |[1,2] |true |true |false |false |true |
> |abc |false|false|false |false |false |
> |null |null |null |null |null |null |
> +--+-+-+---+---+--+
> {code}
> Same SQL in Calcite:
> {code:java}
> ++---+---+-+-++
> | JS | json? | json? | scalar? | object? | array? |
> ++---+---+-+-++
> | "abc" | true | true | true| false | false |
> | 123| true | true | true| false | false |
> | [1,2] | true | true | false | false | true |
> | abc| false | false | false | false | false |
> | {"a": "b"} | true | true | false | true| false |
> || false | false | false | false | false |
> ++---+---+-+-++
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Assigned] (CALCITE-6506) Incorrect RelDataType generated for IN Subquery
[
https://issues.apache.org/jira/browse/CALCITE-6506?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
xiong duan reassigned CALCITE-6506:
---
Assignee: xiong duan
> Incorrect RelDataType generated for IN Subquery
> ---
>
> Key: CALCITE-6506
> URL: https://issues.apache.org/jira/browse/CALCITE-6506
> Project: Calcite
> Issue Type: Bug
>Reporter: Brandon Chong
>Assignee: xiong duan
>Priority: Major
>
> If a user writes a query like:
>
> {{select * from cp."iceberg/orders/orders.parquet" where o_orderkey IN (1,
> 2, 3)}}
> It get's rewritten to use a LogicalValues with RelDataType nullable INTEGER
> row type. The problem is that it's actually NOT NULL.
> You can see the issue in SqlToRelConverter:
>
> {{final RelDataType targetRowType =
> SqlTypeUtil.promoteToRowType(typeFactory,
> validator.getValidatedNodeType(leftKeyNode), null);
> final boolean notIn = call.getOperator().kind == SqlKind.NOT_IN;
> converted =
> convertExists(query, RelOptUtil.SubQueryType.IN, subQuery.logic,
> notIn, targetRowType);}}
> Note that it's using the type of the leftKeyNode and not right key node.
> For example: A IN (B, C, D) ... it's using typeof(A) instead of typeof(B, C,
> D).
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Assigned] (CALCITE-6478) JSON functions should return NULL when input is NULL
[
https://issues.apache.org/jira/browse/CALCITE-6478?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
xiong duan reassigned CALCITE-6478:
---
Assignee: xiong duan
> JSON functions should return NULL when input is NULL
> -
>
> Key: CALCITE-6478
> URL: https://issues.apache.org/jira/browse/CALCITE-6478
> Project: Calcite
> Issue Type: Improvement
>Reporter: xiong duan
>Assignee: xiong duan
>Priority: Major
> Labels: pull-request-available
>
> Here is the test in H2:
> {code:java}
> SELECT js,
>js IS JSON"json?",
>js IS JSON value "json?",
>js IS JSON SCALAR "scalar?",
>js IS JSON OBJECT "object?",
>js IS JSON ARRAY "array?"
> FROM (VALUES ('123'),
> ('"abc"'),
> ('{"a": "b"}'),
> ('[1,2]'),
> ('abc'), NULL) foo(js);
> {code}
> {code:java}
> +--+-+-+---+---+--+
> |JS|json?|json?|scalar?|object?|array?|
> +--+-+-+---+---+--+
> |123 |true |true |true |false |false |
> |"abc" |true |true |true |false |false |
> |{"a": "b"}|true |true |false |true |false |
> |[1,2] |true |true |false |false |true |
> |abc |false|false|false |false |false |
> |null |null |null |null |null |null |
> +--+-+-+---+---+--+
> {code}
> Same SQL in Calcite:
> {code:java}
> ++---+---+-+-++
> | JS | json? | json? | scalar? | object? | array? |
> ++---+---+-+-++
> | "abc" | true | true | true| false | false |
> | 123| true | true | true| false | false |
> | [1,2] | true | true | false | false | true |
> | abc| false | false | false | false | false |
> | {"a": "b"} | true | true | false | true| false |
> || false | false | false | false | false |
> ++---+---+-+-++
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6506) Incorrect RelDataType generated for IN Subquery
[
https://issues.apache.org/jira/browse/CALCITE-6506?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17869813#comment-17869813
]
xiong duan commented on CALCITE-6506:
-
+1. It has the same reason as CALCITE-4889. We use the left datatype instead
of the right datatype.
> Incorrect RelDataType generated for IN Subquery
> ---
>
> Key: CALCITE-6506
> URL: https://issues.apache.org/jira/browse/CALCITE-6506
> Project: Calcite
> Issue Type: Bug
>Reporter: Brandon Chong
>Priority: Major
>
> If a user writes a query like:
>
> {{select * from cp."iceberg/orders/orders.parquet" where o_orderkey IN (1,
> 2, 3)}}
> It get's rewritten to use a LogicalValues with RelDataType nullable INTEGER
> row type. The problem is that it's actually NOT NULL.
> You can see the issue in SqlToRelConverter:
>
> {{final RelDataType targetRowType =
> SqlTypeUtil.promoteToRowType(typeFactory,
> validator.getValidatedNodeType(leftKeyNode), null);
> final boolean notIn = call.getOperator().kind == SqlKind.NOT_IN;
> converted =
> convertExists(query, RelOptUtil.SubQueryType.IN, subQuery.logic,
> notIn, targetRowType);}}
> Note that it's using the type of the leftKeyNode and not right key node.
> For example: A IN (B, C, D) ... it's using typeof(A) instead of typeof(B, C,
> D).
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6478) JSON functions should return NULL when input is NULL
[
https://issues.apache.org/jira/browse/CALCITE-6478?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17869805#comment-17869805
]
xiong duan commented on CALCITE-6478:
-
[~caicancai], According to the description in
[https://modern-sql.com/caniuse/is-json]. It is a standard SQL behavior.
> JSON functions should return NULL when input is NULL
> -
>
> Key: CALCITE-6478
> URL: https://issues.apache.org/jira/browse/CALCITE-6478
> Project: Calcite
> Issue Type: Improvement
>Reporter: xiong duan
>Priority: Major
> Labels: pull-request-available
>
> Here is the test in H2:
> {code:java}
> SELECT js,
>js IS JSON"json?",
>js IS JSON value "json?",
>js IS JSON SCALAR "scalar?",
>js IS JSON OBJECT "object?",
>js IS JSON ARRAY "array?"
> FROM (VALUES ('123'),
> ('"abc"'),
> ('{"a": "b"}'),
> ('[1,2]'),
> ('abc'), NULL) foo(js);
> {code}
> {code:java}
> +--+-+-+---+---+--+
> |JS|json?|json?|scalar?|object?|array?|
> +--+-+-+---+---+--+
> |123 |true |true |true |false |false |
> |"abc" |true |true |true |false |false |
> |{"a": "b"}|true |true |false |true |false |
> |[1,2] |true |true |false |false |true |
> |abc |false|false|false |false |false |
> |null |null |null |null |null |null |
> +--+-+-+---+---+--+
> {code}
> Same SQL in Calcite:
> {code:java}
> ++---+---+-+-++
> | JS | json? | json? | scalar? | object? | array? |
> ++---+---+-+-++
> | "abc" | true | true | true| false | false |
> | 123| true | true | true| false | false |
> | [1,2] | true | true | false | false | true |
> | abc| false | false | false | false | false |
> | {"a": "b"} | true | true | false | true| false |
> || false | false | false | false | false |
> ++---+---+-+-++
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6478) JSON functions should return NULL when input is NULL
[
https://issues.apache.org/jira/browse/CALCITE-6478?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17869806#comment-17869806
]
xiong duan commented on CALCITE-6478:
-
{code:java}
As is json never fails it can be used to test the contents of string values for
well-formed JSON contents. If the input is the SQL null value, the result is
also the SQL null (unknown) value. {code}
> JSON functions should return NULL when input is NULL
> -
>
> Key: CALCITE-6478
> URL: https://issues.apache.org/jira/browse/CALCITE-6478
> Project: Calcite
> Issue Type: Improvement
>Reporter: xiong duan
>Priority: Major
> Labels: pull-request-available
>
> Here is the test in H2:
> {code:java}
> SELECT js,
>js IS JSON"json?",
>js IS JSON value "json?",
>js IS JSON SCALAR "scalar?",
>js IS JSON OBJECT "object?",
>js IS JSON ARRAY "array?"
> FROM (VALUES ('123'),
> ('"abc"'),
> ('{"a": "b"}'),
> ('[1,2]'),
> ('abc'), NULL) foo(js);
> {code}
> {code:java}
> +--+-+-+---+---+--+
> |JS|json?|json?|scalar?|object?|array?|
> +--+-+-+---+---+--+
> |123 |true |true |true |false |false |
> |"abc" |true |true |true |false |false |
> |{"a": "b"}|true |true |false |true |false |
> |[1,2] |true |true |false |false |true |
> |abc |false|false|false |false |false |
> |null |null |null |null |null |null |
> +--+-+-+---+---+--+
> {code}
> Same SQL in Calcite:
> {code:java}
> ++---+---+-+-++
> | JS | json? | json? | scalar? | object? | array? |
> ++---+---+-+-++
> | "abc" | true | true | true| false | false |
> | 123| true | true | true| false | false |
> | [1,2] | true | true | false | false | true |
> | abc| false | false | false | false | false |
> | {"a": "b"} | true | true | false | true| false |
> || false | false | false | false | false |
> ++---+---+-+-++
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6505) Redshift JSON_OBJECT incorrectly rewritten
[ https://issues.apache.org/jira/browse/CALCITE-6505?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17869473#comment-17869473 ] xiong duan commented on CALCITE-6505: - [~ken_stott] , Hi, How about we change the Jira summary to 'JSON_OBJECT Support for Redshift Dialect' to keep consistent with the previous info? Please add the origin SQL and rewrite SQL in the description. > Redshift JSON_OBJECT incorrectly rewritten > -- > > Key: CALCITE-6505 > URL: https://issues.apache.org/jira/browse/CALCITE-6505 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.37.0 > Environment: AWS cloud >Reporter: Kenneth Stott >Priority: Major > > Redshift has no JSON_OBJECT function. This is currently being rewritten as > JSON_OBJECT function with the KEY/VALUE syntax. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6503) Simple `NOT IN` filter is not pushed via JdbcAdapter
[
https://issues.apache.org/jira/browse/CALCITE-6503?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17869459#comment-17869459
]
xiong duan commented on CALCITE-6503:
-
[~corvinkuebler], A part of the entire SQL logic plan can be pushed down to the
JDBC data source, as long as we can ensure the results are correct. So the
behavior is correct.
> Simple `NOT IN` filter is not pushed via JdbcAdapter
>
>
> Key: CALCITE-6503
> URL: https://issues.apache.org/jira/browse/CALCITE-6503
> Project: Calcite
> Issue Type: Bug
> Components: core, jdbc-adapter
>Affects Versions: 1.37.0
>Reporter: Corvin Kuebler
>Priority: Critical
>
> Hey all!
> Adding the following test to JdbcAdapterTests fails:
> {code:java}
> @Test void notInNotPushed() {
> CalciteAssert.model(JdbcTest.SCOTT_MODEL)
> .query("select * from dept where deptno not in (select deptno from
> emp)")
> .explainMatches("",
> CalciteAssert.checkResultContains("EnumerableMergeJoin", 0))
> .explainMatches("",
> CalciteAssert.checkResultContains("EnumerableAggregate", 0))
> .explainMatches("",
> CalciteAssert.checkResultContains("EnumerableSort", 0));
> }
> {code}
> Failure:
> {noformat}
> PLAN=EnumerableCalc(expr#0..6=[{inputs}], expr#7=[0], expr#8=[=($t3, $t7)],
> expr#9=[IS NULL($t6)], expr#10=[>=($t4, $t3)], expr#11=[AND($t9, $t10)],
> expr#12=[OR($t8, $t11)], proj#0..2=[{exprs}], $condition=[$t12])
> EnumerableMergeJoin(condition=[=($0, $5)], joinType=[left])
> JdbcToEnumerableConverter
> JdbcSort(sort0=[$0], dir0=[ASC])
> JdbcJoin(condition=[true], joinType=[inner])
> JdbcTableScan(table=[[SCOTT, DEPT]])
> JdbcAggregate(group=[{}], c=[COUNT()], ck=[COUNT($7)])
> JdbcTableScan(table=[[SCOTT, EMP]])
> EnumerableSort(sort0=[$0], dir0=[ASC])
> EnumerableAggregate(group=[{7}], i=[LITERAL_AGG(true)])
> JdbcToEnumerableConverter
> JdbcTableScan(table=[[SCOTT, EMP]])
> should have 0 occurrence of EnumerableMergeJoin
> Expected :0
> Actual :1
> {noformat}
> The test shows, that instead of pushing the statement to the database, it
> gets rewritten and then evaulated in memory.
> *Update:*
> The statement will be pushed if you add a limit that is exactly 1 to the not
> in condition
> e.g.
> {code:java}
> @Test void notInNotPushed() {
> CalciteAssert.model(JdbcTest.SCOTT_MODEL)
> .query("select * from dept where deptno not in (select deptno from
> emp limit 1)")
> .explainMatches("",
> CalciteAssert.checkResultContains("EnumerableMergeJoin", 0))
> .explainMatches("",
> CalciteAssert.checkResultContains("EnumerableAggregate", 0))
> .explainMatches("",
> CalciteAssert.checkResultContains("EnumerableSort", 0));
> }
> {code}
> works
> {code:java}
> @Test void notInNotPushed() {
> CalciteAssert.model(JdbcTest.SCOTT_MODEL)
> .query("select * from dept where deptno not in (select deptno from
> emp limit 2)")
> .explainMatches("",
> CalciteAssert.checkResultContains("EnumerableMergeJoin", 0))
> .explainMatches("",
> CalciteAssert.checkResultContains("EnumerableAggregate", 0))
> .explainMatches("",
> CalciteAssert.checkResultContains("EnumerableSort", 0));
> }
> {code}
> broken
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6503) Simple `NOT IN` filter is not pushed via JdbcAdapter
[
https://issues.apache.org/jira/browse/CALCITE-6503?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17869228#comment-17869228
]
xiong duan commented on CALCITE-6503:
-
[~corvinkuebler], maybe the LITERAL_AGG function prevents us from pushing down
this plan to Jdbc.
> Simple `NOT IN` filter is not pushed via JdbcAdapter
>
>
> Key: CALCITE-6503
> URL: https://issues.apache.org/jira/browse/CALCITE-6503
> Project: Calcite
> Issue Type: Bug
> Components: core, jdbc-adapter
>Affects Versions: 1.37.0
>Reporter: Corvin Kuebler
>Priority: Critical
>
> Hey all!
> Adding the following test to JdbcAdapterTests fails:
> {code:java}
> @Test void notInNotPushed() {
> CalciteAssert.model(JdbcTest.SCOTT_MODEL)
> .query("select * from dept where deptno not in (select deptno from
> emp)")
> .explainMatches("",
> CalciteAssert.checkResultContains("EnumerableMergeJoin", 0))
> .explainMatches("",
> CalciteAssert.checkResultContains("EnumerableAggregate", 0))
> .explainMatches("",
> CalciteAssert.checkResultContains("EnumerableSort", 0));
> }
> {code}
> Failure:
> {noformat}
> PLAN=EnumerableCalc(expr#0..6=[{inputs}], expr#7=[0], expr#8=[=($t3, $t7)],
> expr#9=[IS NULL($t6)], expr#10=[>=($t4, $t3)], expr#11=[AND($t9, $t10)],
> expr#12=[OR($t8, $t11)], proj#0..2=[{exprs}], $condition=[$t12])
> EnumerableMergeJoin(condition=[=($0, $5)], joinType=[left])
> JdbcToEnumerableConverter
> JdbcSort(sort0=[$0], dir0=[ASC])
> JdbcJoin(condition=[true], joinType=[inner])
> JdbcTableScan(table=[[SCOTT, DEPT]])
> JdbcAggregate(group=[{}], c=[COUNT()], ck=[COUNT($7)])
> JdbcTableScan(table=[[SCOTT, EMP]])
> EnumerableSort(sort0=[$0], dir0=[ASC])
> EnumerableAggregate(group=[{7}], i=[LITERAL_AGG(true)])
> JdbcToEnumerableConverter
> JdbcTableScan(table=[[SCOTT, EMP]])
> should have 0 occurrence of EnumerableMergeJoin
> Expected :0
> Actual :1
> {noformat}
> The test shows, that instead of pushing the statement to the database, it
> gets rewritten and then evaulated in memory.
> *Update:*
> The statement will be pushed if you add a limit that is exactly 1 to the not
> in condition
> e.g.
> {code:java}
> @Test void notInNotPushed() {
> CalciteAssert.model(JdbcTest.SCOTT_MODEL)
> .query("select * from dept where deptno not in (select deptno from
> emp limit 1)")
> .explainMatches("",
> CalciteAssert.checkResultContains("EnumerableMergeJoin", 0))
> .explainMatches("",
> CalciteAssert.checkResultContains("EnumerableAggregate", 0))
> .explainMatches("",
> CalciteAssert.checkResultContains("EnumerableSort", 0));
> }
> {code}
> works
> {code:java}
> @Test void notInNotPushed() {
> CalciteAssert.model(JdbcTest.SCOTT_MODEL)
> .query("select * from dept where deptno not in (select deptno from
> emp limit 2)")
> .explainMatches("",
> CalciteAssert.checkResultContains("EnumerableMergeJoin", 0))
> .explainMatches("",
> CalciteAssert.checkResultContains("EnumerableAggregate", 0))
> .explainMatches("",
> CalciteAssert.checkResultContains("EnumerableSort", 0));
> }
> {code}
> broken
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Resolved] (CALCITE-6482) Oracle dialect convert boolean literal when version < 23
[ https://issues.apache.org/jira/browse/CALCITE-6482?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] xiong duan resolved CALCITE-6482. - Fix Version/s: 1.38.0 Resolution: Fixed > Oracle dialect convert boolean literal when version < 23 > > > Key: CALCITE-6482 > URL: https://issues.apache.org/jira/browse/CALCITE-6482 > Project: Calcite > Issue Type: Improvement > Components: core >Reporter: kate >Priority: Minor > Labels: pull-request-available > Fix For: 1.38.0 > > > Lower versions of oracle do not support boolean type literal, in > OracleDialect, we can respectively boolean type true and false unparse for > (1=1) and (1=0) -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6482) Oracle dialect convert boolean literal when version < 23
[ https://issues.apache.org/jira/browse/CALCITE-6482?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17868207#comment-17868207 ] xiong duan commented on CALCITE-6482: - Fixed in [1947312|https://github.com/apache/calcite/commit/1947312e4ae792aa6ba6b4c48a2d868712d41dc5]. Thanks for the contribution [~suibianwanwan33] . Thanks for the review [~cancai] 、[~mbudiu] and [~asolimando]. > Oracle dialect convert boolean literal when version < 23 > > > Key: CALCITE-6482 > URL: https://issues.apache.org/jira/browse/CALCITE-6482 > Project: Calcite > Issue Type: Improvement > Components: core >Reporter: kate >Priority: Minor > Labels: pull-request-available > > Lower versions of oracle do not support boolean type literal, in > OracleDialect, we can respectively boolean type true and false unparse for > (1=1) and (1=0) -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6482) Oracle dialect convert boolean literal when version < 23
[ https://issues.apache.org/jira/browse/CALCITE-6482?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17868206#comment-17868206 ] xiong duan commented on CALCITE-6482: - [~suibianwanwan33] I guess we need to add your account to the contributor's list in JIRA, as described here [added-as-a-contributor|https://calcite.apache.org/develop/#i-already-have-an-asf-jira-account-and-want-to-be-added-as-a-contributor]. This is not a problem for us to merge this PR. So I will merge it. > Oracle dialect convert boolean literal when version < 23 > > > Key: CALCITE-6482 > URL: https://issues.apache.org/jira/browse/CALCITE-6482 > Project: Calcite > Issue Type: Improvement > Components: core >Reporter: kate >Priority: Minor > Labels: pull-request-available > > Lower versions of oracle do not support boolean type literal, in > OracleDialect, we can respectively boolean type true and false unparse for > (1=1) and (1=0) -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6482) Oracle dialect convert boolean literal when version < 23
[ https://issues.apache.org/jira/browse/CALCITE-6482?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17868045#comment-17868045 ] xiong duan commented on CALCITE-6482: - [~suibianwanwan33] Hi, The PR is about to be merged, and you can add a description here. Other developers can know the difference before and after resolving this PR without looking at the code.WDYT? > Oracle dialect convert boolean literal when version < 23 > > > Key: CALCITE-6482 > URL: https://issues.apache.org/jira/browse/CALCITE-6482 > Project: Calcite > Issue Type: Improvement > Components: core >Reporter: kate >Priority: Minor > Labels: pull-request-available > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Resolved] (CALCITE-6485) AssertionError When an IN list containing NULL has an implicit coercion type converter
[
https://issues.apache.org/jira/browse/CALCITE-6485?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
xiong duan resolved CALCITE-6485.
-
Fix Version/s: 1.38.0
Resolution: Fixed
> AssertionError When an IN list containing NULL has an implicit coercion type
> converter
> --
>
> Key: CALCITE-6485
> URL: https://issues.apache.org/jira/browse/CALCITE-6485
> Project: Calcite
> Issue Type: Improvement
>Reporter: xiong duan
>Assignee: xiong duan
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.38.0
>
>
> {code:java}
> @Test void testInOperation() {
> sql("select 1 in (null, '2', '3') as f0 from (values (true, true,
> true))").ok();
> }
> {code}
> {code:java}
> Conversion to relational algebra failed to preserve datatypes:
> validated type:
> RecordType(BOOLEAN NOT NULL F0) NOT NULL
> converted type:
> RecordType(BOOLEAN F0) NOT NULL
> rel:
> LogicalValues(tuples=[[{ null }]]) {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6485) AssertionError When an IN list containing NULL has an implicit coercion type converter
[
https://issues.apache.org/jira/browse/CALCITE-6485?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17867990#comment-17867990
]
xiong duan commented on CALCITE-6485:
-
Fixed in
[6164a037|https://github.com/apache/calcite/commit/ea1a255fd071a518fe8d30e361efa0696164a037].
Thanks for the review [~caicancai].
> AssertionError When an IN list containing NULL has an implicit coercion type
> converter
> --
>
> Key: CALCITE-6485
> URL: https://issues.apache.org/jira/browse/CALCITE-6485
> Project: Calcite
> Issue Type: Improvement
>Reporter: xiong duan
>Assignee: xiong duan
>Priority: Major
> Labels: pull-request-available
>
> {code:java}
> @Test void testInOperation() {
> sql("select 1 in (null, '2', '3') as f0 from (values (true, true,
> true))").ok();
> }
> {code}
> {code:java}
> Conversion to relational algebra failed to preserve datatypes:
> validated type:
> RecordType(BOOLEAN NOT NULL F0) NOT NULL
> converted type:
> RecordType(BOOLEAN F0) NOT NULL
> rel:
> LogicalValues(tuples=[[{ null }]]) {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6489) Add more data types to the Arrow test set
[ https://issues.apache.org/jira/browse/CALCITE-6489?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17867915#comment-17867915 ] xiong duan commented on CALCITE-6489: - [~caicancai] I review the PR. Maybe we can change the summary to 'Support TINYINT、SMALLINT type and add test cases for other data types in Arrow adapter' to make this PR more specific. > Add more data types to the Arrow test set > - > > Key: CALCITE-6489 > URL: https://issues.apache.org/jira/browse/CALCITE-6489 > Project: Calcite > Issue Type: Sub-task >Reporter: Caican Cai >Assignee: Caican Cai >Priority: Major > Labels: pull-request-available > > Currently, the Arrow test set only has basic data types, such as int, long, > etc., but does not have smallInt, tinyint, etc. > It is possible to improve the Arrow test set -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6490) Missing tests for SqlFunctions#overlay
[ https://issues.apache.org/jira/browse/CALCITE-6490?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17867686#comment-17867686 ] xiong duan commented on CALCITE-6490: - The test about OVERLAY in SqlOperatorTest has covered the case that you added in PR. But I am okay with this. > Missing tests for SqlFunctions#overlay > -- > > Key: CALCITE-6490 > URL: https://issues.apache.org/jira/browse/CALCITE-6490 > Project: Calcite > Issue Type: Improvement > Components: tests >Affects Versions: 1.37.0 >Reporter: Tim Grein >Assignee: Tim Grein >Priority: Minor > Labels: pull-request-available > Fix For: 1.38.0 > > > After making some improvements to the OVERLAY operator docs in [Fix error > message typo and improve docs for OVERLAY > operator|https://github.com/apache/calcite/pull/3877] I've noticed that there > are no tests for SqlFunctions#overlay in SqlFunctionsTest. As checkString in > SqlOperatorTest doesn't check the actual value against the expected result > (at least that's how I understood it) I thought it's even more important to > add tests for the OVERLAY operator in SqlFunctionsTest. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6490) Missing tests for SqlFunctions#overlay
[ https://issues.apache.org/jira/browse/CALCITE-6490?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17867667#comment-17867667 ] xiong duan commented on CALCITE-6490: - [~timgrein] Hi, Calcite checks the result in `CalciteSqlOperatorTest'. So we don't need to add another test for it. Maybe you can enrich it if you think there is insufficient coverage of test cases. > Missing tests for SqlFunctions#overlay > -- > > Key: CALCITE-6490 > URL: https://issues.apache.org/jira/browse/CALCITE-6490 > Project: Calcite > Issue Type: Improvement > Components: tests >Affects Versions: 1.37.0 >Reporter: Tim Grein >Assignee: Tim Grein >Priority: Minor > Labels: pull-request-available > Fix For: 1.38.0 > > > After making some improvements to the OVERLAY operator docs in [Fix error > message typo and improve docs for OVERLAY > operator|https://github.com/apache/calcite/pull/3877] I've noticed that there > are no tests for SqlFunctions#overlay in SqlFunctionsTest. As checkString in > SqlOperatorTest doesn't check the actual value against the expected result > (at least that's how I understood it) I thought it's even more important to > add tests for the OVERLAY operator in SqlFunctionsTest. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Closed] (CALCITE-6491) Incorrect test fixture used by SqlOperatorTest when need to check expression's result
[
https://issues.apache.org/jira/browse/CALCITE-6491?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
xiong duan closed CALCITE-6491.
---
Resolution: Not A Problem
The Calcite has another class 'CalciteSqlOperatorTest' to execute the
expression using Calcite. In
SqlOperatorTest we test the parameter type and result type.
> Incorrect test fixture used by SqlOperatorTest when need to check
> expression's result
> -
>
> Key: CALCITE-6491
> URL: https://issues.apache.org/jira/browse/CALCITE-6491
> Project: Calcite
> Issue Type: Improvement
>Reporter: xiong duan
>Assignee: xiong duan
>Priority: Major
>
> The Unit Test:
> {code:java}
> @Test void testOverlayFunc() {
> final SqlOperatorFixture f = fixture();
> f.setFor(SqlStdOperatorTable.OVERLAY, VmName.EXPAND);
> f.checkString("overlay('ABCdef' placing 'abc' from 1)",
> "abcd", "VARCHAR(9) NOT NULL");
> } {code}
> {code:java}
> @Test void testOverlayFunc() {
> final SqlOperatorFixture f = fixture();
> f.setFor(SqlStdOperatorTable.OVERLAY, VmName.EXPAND);
> f.checkString("overlay('ABCdef' placing 'abc' from 1)",
> "abcdef", "VARCHAR(9) NOT NULL");
> } {code}
> Both test cases can run successfully. I checked the code and the default
> implementation only verifies the result type.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-6491) Incorrect test fixture used by SqlOperatorTest when need to check expression's result
[
https://issues.apache.org/jira/browse/CALCITE-6491?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
xiong duan updated CALCITE-6491:
Summary: Incorrect test fixture used by SqlOperatorTest when need to check
expression's result (was: OVERLAY test functions’ checkString doesn't check
the actual value against the expected result)
> Incorrect test fixture used by SqlOperatorTest when need to check
> expression's result
> -
>
> Key: CALCITE-6491
> URL: https://issues.apache.org/jira/browse/CALCITE-6491
> Project: Calcite
> Issue Type: Improvement
>Reporter: xiong duan
>Assignee: xiong duan
>Priority: Major
>
> The Unit Test:
> {code:java}
> @Test void testOverlayFunc() {
> final SqlOperatorFixture f = fixture();
> f.setFor(SqlStdOperatorTable.OVERLAY, VmName.EXPAND);
> f.checkString("overlay('ABCdef' placing 'abc' from 1)",
> "abcd", "VARCHAR(9) NOT NULL");
> } {code}
> {code:java}
> @Test void testOverlayFunc() {
> final SqlOperatorFixture f = fixture();
> f.setFor(SqlStdOperatorTable.OVERLAY, VmName.EXPAND);
> f.checkString("overlay('ABCdef' placing 'abc' from 1)",
> "abcdef", "VARCHAR(9) NOT NULL");
> } {code}
> Both test cases can run successfully. I checked the code and the default
> implementation only verifies the result type.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6491) OVERLAY test functions’ checkString doesn't check the actual value against the expected result
[
https://issues.apache.org/jira/browse/CALCITE-6491?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17867629#comment-17867629
]
xiong duan commented on CALCITE-6491:
-
If we want to test the Result we need to use TesterImpl(create a JDBC
connection to execute the operator). Now we use the AbstractSqlTester(Only
check the return type).
> OVERLAY test functions’ checkString doesn't check the actual value against
> the expected result
> --
>
> Key: CALCITE-6491
> URL: https://issues.apache.org/jira/browse/CALCITE-6491
> Project: Calcite
> Issue Type: Improvement
>Reporter: xiong duan
>Assignee: xiong duan
>Priority: Major
>
> The Unit Test:
> {code:java}
> @Test void testOverlayFunc() {
> final SqlOperatorFixture f = fixture();
> f.setFor(SqlStdOperatorTable.OVERLAY, VmName.EXPAND);
> f.checkString("overlay('ABCdef' placing 'abc' from 1)",
> "abcd", "VARCHAR(9) NOT NULL");
> } {code}
> {code:java}
> @Test void testOverlayFunc() {
> final SqlOperatorFixture f = fixture();
> f.setFor(SqlStdOperatorTable.OVERLAY, VmName.EXPAND);
> f.checkString("overlay('ABCdef' placing 'abc' from 1)",
> "abcdef", "VARCHAR(9) NOT NULL");
> } {code}
> Both test cases can run successfully. I checked the code and the default
> implementation only verifies the result type.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-6491) OVERLAY test functions’ checkString doesn't check the actual value against the expected result
[
https://issues.apache.org/jira/browse/CALCITE-6491?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
xiong duan updated CALCITE-6491:
Summary: OVERLAY test functions’ checkString doesn't check the actual value
against the expected result (was: SqlOperatorFixtureImpl's checkString doesn't
check the actual value against the expected result)
> OVERLAY test functions’ checkString doesn't check the actual value against
> the expected result
> --
>
> Key: CALCITE-6491
> URL: https://issues.apache.org/jira/browse/CALCITE-6491
> Project: Calcite
> Issue Type: Improvement
>Reporter: xiong duan
>Assignee: xiong duan
>Priority: Major
>
> The Unit Test:
> {code:java}
> @Test void testOverlayFunc() {
> final SqlOperatorFixture f = fixture();
> f.setFor(SqlStdOperatorTable.OVERLAY, VmName.EXPAND);
> f.checkString("overlay('ABCdef' placing 'abc' from 1)",
> "abcd", "VARCHAR(9) NOT NULL");
> } {code}
> {code:java}
> @Test void testOverlayFunc() {
> final SqlOperatorFixture f = fixture();
> f.setFor(SqlStdOperatorTable.OVERLAY, VmName.EXPAND);
> f.checkString("overlay('ABCdef' placing 'abc' from 1)",
> "abcdef", "VARCHAR(9) NOT NULL");
> } {code}
> Both test cases can run successfully. I checked the code and the default
> implementation only verifies the result type.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Assigned] (CALCITE-6491) SqlOperatorFixtureImpl's checkString doesn't check the actual value against the expected result
[
https://issues.apache.org/jira/browse/CALCITE-6491?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
xiong duan reassigned CALCITE-6491:
---
Assignee: xiong duan
> SqlOperatorFixtureImpl's checkString doesn't check the actual value against
> the expected result
> ---
>
> Key: CALCITE-6491
> URL: https://issues.apache.org/jira/browse/CALCITE-6491
> Project: Calcite
> Issue Type: Improvement
>Reporter: xiong duan
>Assignee: xiong duan
>Priority: Major
>
> The Unit Test:
> {code:java}
> @Test void testOverlayFunc() {
> final SqlOperatorFixture f = fixture();
> f.setFor(SqlStdOperatorTable.OVERLAY, VmName.EXPAND);
> f.checkString("overlay('ABCdef' placing 'abc' from 1)",
> "abcd", "VARCHAR(9) NOT NULL");
> } {code}
> {code:java}
> @Test void testOverlayFunc() {
> final SqlOperatorFixture f = fixture();
> f.setFor(SqlStdOperatorTable.OVERLAY, VmName.EXPAND);
> f.checkString("overlay('ABCdef' placing 'abc' from 1)",
> "abcdef", "VARCHAR(9) NOT NULL");
> } {code}
> Both test cases can run successfully. I checked the code and the default
> implementation only verifies the result type.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6490) Missing tests for SqlFunctions#overlay
[ https://issues.apache.org/jira/browse/CALCITE-6490?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17867625#comment-17867625 ] xiong duan commented on CALCITE-6490: - [~timgrein] , As you describe in the Description, I think this is a bug in SqlOperatorFixtureImpl. I have created another issue CALCITE-6491. > Missing tests for SqlFunctions#overlay > -- > > Key: CALCITE-6490 > URL: https://issues.apache.org/jira/browse/CALCITE-6490 > Project: Calcite > Issue Type: Improvement > Components: tests >Affects Versions: 1.37.0 >Reporter: Tim Grein >Assignee: Tim Grein >Priority: Minor > Labels: pull-request-available > Fix For: 1.38.0 > > > After making some improvements to the OVERLAY operator docs in [Fix error > message typo and improve docs for OVERLAY > operator|https://github.com/apache/calcite/pull/3877] I've noticed that there > are no tests for SqlFunctions#overlay in SqlFunctionsTest. As checkString in > SqlOperatorTest doesn't check the actual value against the expected result > (at least that's how I understood it) I thought it's even more important to > add tests for the OVERLAY operator in SqlFunctionsTest. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6491) SqlOperatorFixtureImpl's checkString doesn't check the actual value against the expected result
[
https://issues.apache.org/jira/browse/CALCITE-6491?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
xiong duan updated CALCITE-6491:
Summary: SqlOperatorFixtureImpl's checkString doesn't check the actual
value against the expected result (was: SqlOperatorFixture's checkString
doesn't check the actual value against the expected result)
> SqlOperatorFixtureImpl's checkString doesn't check the actual value against
> the expected result
> ---
>
> Key: CALCITE-6491
> URL: https://issues.apache.org/jira/browse/CALCITE-6491
> Project: Calcite
> Issue Type: Improvement
>Reporter: xiong duan
>Priority: Major
>
> The Unit Test:
> {code:java}
> @Test void testOverlayFunc() {
> final SqlOperatorFixture f = fixture();
> f.setFor(SqlStdOperatorTable.OVERLAY, VmName.EXPAND);
> f.checkString("overlay('ABCdef' placing 'abc' from 1)",
> "abcd", "VARCHAR(9) NOT NULL");
> } {code}
> {code:java}
> @Test void testOverlayFunc() {
> final SqlOperatorFixture f = fixture();
> f.setFor(SqlStdOperatorTable.OVERLAY, VmName.EXPAND);
> f.checkString("overlay('ABCdef' placing 'abc' from 1)",
> "abcdef", "VARCHAR(9) NOT NULL");
> } {code}
> Both test cases can run successfully. I checked the code and the default
> implementation only verifies the result type.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-6491) SqlOperatorFixture's checkString doesn't check the actual value against the expected result
xiong duan created CALCITE-6491:
---
Summary: SqlOperatorFixture's checkString doesn't check the actual
value against the expected result
Key: CALCITE-6491
URL: https://issues.apache.org/jira/browse/CALCITE-6491
Project: Calcite
Issue Type: Improvement
Reporter: xiong duan
The Unit Test:
{code:java}
@Test void testOverlayFunc() {
final SqlOperatorFixture f = fixture();
f.setFor(SqlStdOperatorTable.OVERLAY, VmName.EXPAND);
f.checkString("overlay('ABCdef' placing 'abc' from 1)",
"abcd", "VARCHAR(9) NOT NULL");
} {code}
{code:java}
@Test void testOverlayFunc() {
final SqlOperatorFixture f = fixture();
f.setFor(SqlStdOperatorTable.OVERLAY, VmName.EXPAND);
f.checkString("overlay('ABCdef' placing 'abc' from 1)",
"abcdef", "VARCHAR(9) NOT NULL");
} {code}
Both test cases can run successfully. I checked the code and the default
implementation only verifies the result type.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-6485) AssertionError When an IN list containing NULL has an implicit coercion type converter
[
https://issues.apache.org/jira/browse/CALCITE-6485?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
xiong duan updated CALCITE-6485:
Summary: AssertionError When an IN list containing NULL has an implicit
coercion type converter (was: Query with IN-list contains NULL throws
Exception)
> AssertionError When an IN list containing NULL has an implicit coercion type
> converter
> --
>
> Key: CALCITE-6485
> URL: https://issues.apache.org/jira/browse/CALCITE-6485
> Project: Calcite
> Issue Type: Improvement
>Reporter: xiong duan
>Assignee: xiong duan
>Priority: Major
>
> {code:java}
> @Test void testInOperation() {
> sql("select 1 in (null, '2', '3') as f0 from (values (true, true,
> true))").ok();
> }
> {code}
> {code:java}
> Conversion to relational algebra failed to preserve datatypes:
> validated type:
> RecordType(BOOLEAN NOT NULL F0) NOT NULL
> converted type:
> RecordType(BOOLEAN F0) NOT NULL
> rel:
> LogicalValues(tuples=[[{ null }]]) {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6485) Query with IN-list contains NULL throws Exception
[
https://issues.apache.org/jira/browse/CALCITE-6485?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17867466#comment-17867466
]
xiong duan commented on CALCITE-6485:
-
[~julianhyde], this issue occurs when an IN list containing null values has an
implicit coercion type converter.
Here are some tests:
{code:java}
!use post
select 1 in (null, 2, 3) as f0 from (values (true, true, true));
F0
null
!ok
select 1 in (null, 2, 3) as f0;
F0
null
!ok
select 1 in (null, cast('2' as varchar), cast('3' as varchar)) as f0 from
(values (true, true, true));
java.lang.AssertionError: Conversion to relational algebra failed to preserve
datatypes:
validated type:
RecordType(BOOLEAN NOT NULL F0) NOT NULL
converted type:
RecordType(BOOLEAN F0) NOT NULL
rel:
LogicalValues(tuples=[[{ null }]]) {code}
> Query with IN-list contains NULL throws Exception
> -
>
> Key: CALCITE-6485
> URL: https://issues.apache.org/jira/browse/CALCITE-6485
> Project: Calcite
> Issue Type: Improvement
>Reporter: xiong duan
>Assignee: xiong duan
>Priority: Major
>
> {code:java}
> @Test void testInOperation() {
> sql("select 1 in (null, '2', '3') as f0 from (values (true, true,
> true))").ok();
> }
> {code}
> {code:java}
> Conversion to relational algebra failed to preserve datatypes:
> validated type:
> RecordType(BOOLEAN NOT NULL F0) NOT NULL
> converted type:
> RecordType(BOOLEAN F0) NOT NULL
> rel:
> LogicalValues(tuples=[[{ null }]]) {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Assigned] (CALCITE-6485) Query with IN-list contains NULL throws Exception
[
https://issues.apache.org/jira/browse/CALCITE-6485?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
xiong duan reassigned CALCITE-6485:
---
Assignee: xiong duan
> Query with IN-list contains NULL throws Exception
> -
>
> Key: CALCITE-6485
> URL: https://issues.apache.org/jira/browse/CALCITE-6485
> Project: Calcite
> Issue Type: Improvement
>Reporter: xiong duan
>Assignee: xiong duan
>Priority: Major
>
> {code:java}
> @Test void testInOperation() {
> sql("select 1 in (null, '2', '3') as f0 from (values (true, true,
> true))").ok();
> }
> {code}
> {code:java}
> Conversion to relational algebra failed to preserve datatypes:
> validated type:
> RecordType(BOOLEAN NOT NULL F0) NOT NULL
> converted type:
> RecordType(BOOLEAN F0) NOT NULL
> rel:
> LogicalValues(tuples=[[{ null }]]) {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-6485) Query with IN-list contains NULL throws Exception
[
https://issues.apache.org/jira/browse/CALCITE-6485?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
xiong duan updated CALCITE-6485:
Summary: Query with IN-list contains NULL throws Exception (was: IN-list
contains NULL throws Exception)
> Query with IN-list contains NULL throws Exception
> -
>
> Key: CALCITE-6485
> URL: https://issues.apache.org/jira/browse/CALCITE-6485
> Project: Calcite
> Issue Type: Improvement
>Reporter: xiong duan
>Priority: Major
>
> {code:java}
> @Test void testInOperation() {
> sql("select 1 in (null, '2', '3') as f0 from (values (true, true,
> true))").ok();
> }
> {code}
> {code:java}
> Conversion to relational algebra failed to preserve datatypes:
> validated type:
> RecordType(BOOLEAN NOT NULL F0) NOT NULL
> converted type:
> RecordType(BOOLEAN F0) NOT NULL
> rel:
> LogicalValues(tuples=[[{ null }]]) {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-6485) IN-list contains NULL throws Exception
xiong duan created CALCITE-6485:
---
Summary: IN-list contains NULL throws Exception
Key: CALCITE-6485
URL: https://issues.apache.org/jira/browse/CALCITE-6485
Project: Calcite
Issue Type: Improvement
Reporter: xiong duan
{code:java}
@Test void testInOperation() {
sql("select 1 in (null, '2', '3') as f0 from (values (true, true,
true))").ok();
}
{code}
{code:java}
Conversion to relational algebra failed to preserve datatypes:
validated type:
RecordType(BOOLEAN NOT NULL F0) NOT NULL
converted type:
RecordType(BOOLEAN F0) NOT NULL
rel:
LogicalValues(tuples=[[{ null }]]) {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-6481) Optimize 'VALUES...UNION ALL...VALUES' to a single 'VALUES' the IN-list contains NULL and it is converted to VALUES
[
https://issues.apache.org/jira/browse/CALCITE-6481?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
xiong duan updated CALCITE-6481:
Summary: Optimize 'VALUES...UNION ALL...VALUES' to a single 'VALUES' the
IN-list contains NULL and it is converted to VALUES (was: Optimize
'VALUES...UNION ALL...VALUES' to a single 'VALUES' when IN-list that the values
include NULL is converted to Values)
> Optimize 'VALUES...UNION ALL...VALUES' to a single 'VALUES' the IN-list
> contains NULL and it is converted to VALUES
> ---
>
> Key: CALCITE-6481
> URL: https://issues.apache.org/jira/browse/CALCITE-6481
> Project: Calcite
> Issue Type: Improvement
>Reporter: xiong duan
>Assignee: xiong duan
>Priority: Major
>
> The SQL:
> {code:java}
> with
> t1(a,y) as (select * from (values (1, 2), (3,
> null),(7369,null),(7499,30),(null, 20),(null, 5)) as t1)
> select *
> from t1
> where (t1.a,t1.y) in ((1, 2), (3, null),(7369,null),(7499,30),(null,
> 20),(null, 5));
> EnumerableHashJoin(condition=[AND(=($0, $2), =($1, $3))], joinType=[semi])
> EnumerableValues(tuples=[[{ 1, 2 }, { 3, null }, { 7369, null }, { 7499, 30
> }, { null, 20 }, { null, 5 }]])
> EnumerableUnion(all=[true])
> EnumerableValues(tuples=[[{ 3, null }]])
> EnumerableValues(tuples=[[{ 7369, null }]])
> EnumerableValues(tuples=[[{ null, 20 }]])
> EnumerableValues(tuples=[[{ null, 5 }]])
> EnumerableValues(tuples=[[{ 1, 2 }, { 7499, 30 }]])
> !plan
> with
> t1(a,y) as (select * from (values (1, 2), (3, 5),(7369,6),(7499,30),(2,
> 20),(3, 5)) as t1)
> select *
> from t1
> where (t1.a,t1.y) in ((1, 2), (3, 3),(7369,3),(7499,30),(1, 20),(3, 5));
> EnumerableHashJoin(condition=[AND(=($0, $2), =($1, $3))], joinType=[semi])
> EnumerableValues(tuples=[[{ 1, 2 }, { 3, 5 }, { 7369, 6 }, { 7499, 30 }, {
> 2, 20 }, { 3, 5 }]])
> EnumerableValues(tuples=[[{ 1, 2 }, { 3, 3 }, { 7369, 3 }, { 7499, 30 }, {
> 1, 20 }, { 3, 5 }]])
> !plan {code}
> If the IN-list includes NULL, Calcite will convert VALUES to UNION ALL.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Resolved] (CALCITE-6475) RelToSql converter fails when the IN-list contains NULL and it is converted to VALUES
[
https://issues.apache.org/jira/browse/CALCITE-6475?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
xiong duan resolved CALCITE-6475.
-
Fix Version/s: 1.38.0
Resolution: Fixed
> RelToSql converter fails when the IN-list contains NULL and it is converted
> to VALUES
> -
>
> Key: CALCITE-6475
> URL: https://issues.apache.org/jira/browse/CALCITE-6475
> Project: Calcite
> Issue Type: Bug
>Reporter: xiong duan
>Assignee: xiong duan
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.38.0
>
>
> The SQL
> {code:java}
> select * from "product" where ("product_id") in (12, null){code}
> When we convert the IN-list to OR condition, it can run successfully. But
> when we convert it to VALUES, it will throw NPE:
> {code:java}
> java.lang.NullPointerException
> at
> org.apache.calcite.rel.rel2sql.SqlImplementor.toSql(SqlImplementor.java:1468)
> at
> org.apache.calcite.rel.rel2sql.SqlImplementor.toSql(SqlImplementor.java:1416)
> at
> org.apache.calcite.rel.rel2sql.SqlImplementor$Context.toSql(SqlImplementor.java:702)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.lambda$exprList$17(RelToSqlConverter.java:1158){code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6475) RelToSql converter fails when the IN-list contains NULL and it is converted to VALUES
[
https://issues.apache.org/jira/browse/CALCITE-6475?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17867160#comment-17867160
]
xiong duan commented on CALCITE-6475:
-
Fixed in
[2d955bc9f7|https://github.com/apache/calcite/commit/cde430897240894eab1fdef999a7ca2d955bc9f7].
Thanks for the review [~asolimando], [~mbudiu], [~caicancai] .
> RelToSql converter fails when the IN-list contains NULL and it is converted
> to VALUES
> -
>
> Key: CALCITE-6475
> URL: https://issues.apache.org/jira/browse/CALCITE-6475
> Project: Calcite
> Issue Type: Bug
>Reporter: xiong duan
>Assignee: xiong duan
>Priority: Major
> Labels: pull-request-available
>
> The SQL
> {code:java}
> select * from "product" where ("product_id") in (12, null){code}
> When we convert the IN-list to OR condition, it can run successfully. But
> when we convert it to VALUES, it will throw NPE:
> {code:java}
> java.lang.NullPointerException
> at
> org.apache.calcite.rel.rel2sql.SqlImplementor.toSql(SqlImplementor.java:1468)
> at
> org.apache.calcite.rel.rel2sql.SqlImplementor.toSql(SqlImplementor.java:1416)
> at
> org.apache.calcite.rel.rel2sql.SqlImplementor$Context.toSql(SqlImplementor.java:702)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.lambda$exprList$17(RelToSqlConverter.java:1158){code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6475) RelToSql converter fails when the IN-list contains NULL and it is converted to VALUES
[
https://issues.apache.org/jira/browse/CALCITE-6475?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17866987#comment-17866987
]
xiong duan commented on CALCITE-6475:
-
+1 [~asolimando].
> RelToSql converter fails when the IN-list contains NULL and it is converted
> to VALUES
> -
>
> Key: CALCITE-6475
> URL: https://issues.apache.org/jira/browse/CALCITE-6475
> Project: Calcite
> Issue Type: Bug
>Reporter: xiong duan
>Assignee: xiong duan
>Priority: Major
>
> The SQL
> {code:java}
> select * from "product" where ("product_id") in (12, null){code}
> When we convert the IN-list to OR condition, it can run successfully. But
> when we convert it to VALUES, it will throw NPE:
> {code:java}
> java.lang.NullPointerException
> at
> org.apache.calcite.rel.rel2sql.SqlImplementor.toSql(SqlImplementor.java:1468)
> at
> org.apache.calcite.rel.rel2sql.SqlImplementor.toSql(SqlImplementor.java:1416)
> at
> org.apache.calcite.rel.rel2sql.SqlImplementor$Context.toSql(SqlImplementor.java:702)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.lambda$exprList$17(RelToSqlConverter.java:1158){code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-6475) RelToSql converter fails when the IN-list contains NULL and it is converted to VALUES
[
https://issues.apache.org/jira/browse/CALCITE-6475?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
xiong duan updated CALCITE-6475:
Summary: RelToSql converter fails when the IN-list contains NULL and it is
converted to VALUES (was: RelToSql converter fails when IN-list that the
values include NULL is converted to Values)
> RelToSql converter fails when the IN-list contains NULL and it is converted
> to VALUES
> -
>
> Key: CALCITE-6475
> URL: https://issues.apache.org/jira/browse/CALCITE-6475
> Project: Calcite
> Issue Type: Bug
>Reporter: xiong duan
>Assignee: xiong duan
>Priority: Major
>
> The SQL
> {code:java}
> select * from "product" where ("product_id") in (12, null){code}
> When we convert the IN-list to OR condition, it can run successfully. But
> when we convert it to VALUES, it will throw NPE:
> {code:java}
> java.lang.NullPointerException
> at
> org.apache.calcite.rel.rel2sql.SqlImplementor.toSql(SqlImplementor.java:1468)
> at
> org.apache.calcite.rel.rel2sql.SqlImplementor.toSql(SqlImplementor.java:1416)
> at
> org.apache.calcite.rel.rel2sql.SqlImplementor$Context.toSql(SqlImplementor.java:702)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.lambda$exprList$17(RelToSqlConverter.java:1158){code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Assigned] (CALCITE-6475) RelToSql converter fails when IN-list that the values include NULL is converted to Values
[
https://issues.apache.org/jira/browse/CALCITE-6475?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
xiong duan reassigned CALCITE-6475:
---
Assignee: xiong duan
> RelToSql converter fails when IN-list that the values include NULL is
> converted to Values
> -
>
> Key: CALCITE-6475
> URL: https://issues.apache.org/jira/browse/CALCITE-6475
> Project: Calcite
> Issue Type: Bug
>Reporter: xiong duan
>Assignee: xiong duan
>Priority: Major
>
> The SQL
> {code:java}
> select * from "product" where ("product_id") in (12, null){code}
> When we convert the IN-list to OR condition, it can run successfully. But
> when we convert it to VALUES, it will throw NPE:
> {code:java}
> java.lang.NullPointerException
> at
> org.apache.calcite.rel.rel2sql.SqlImplementor.toSql(SqlImplementor.java:1468)
> at
> org.apache.calcite.rel.rel2sql.SqlImplementor.toSql(SqlImplementor.java:1416)
> at
> org.apache.calcite.rel.rel2sql.SqlImplementor$Context.toSql(SqlImplementor.java:702)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.lambda$exprList$17(RelToSqlConverter.java:1158){code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6475) RelToSql converter fails when IN-list that the values include NULL is converted to Values
[
https://issues.apache.org/jira/browse/CALCITE-6475?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17866886#comment-17866886
]
xiong duan commented on CALCITE-6475:
-
[~asolimando] Hi, we use `inSubQueryThreshold` to control the IN-list to be
converted join against a VALUES or to OR predicate.
For example:
{code:java}
!use foodmart
!set outputformat mysql
!set insubquerythreshold 0
select * from "product" where ("product_id") in (12, null);
EnumerableHashJoin(condition=[=($1, $15)], joinType=[semi])
EnumerableTableScan(table=[[foodmart2, product]])
EnumerableValues(tuples=[[{ 12 }, { null }]])
!plan
!set insubquerythreshold 3
select * from "product" where ("product_id") in (12, null);
EnumerableCalc(expr#0..14=[{inputs}], expr#15=[12], expr#16=[=($t1, $t15)],
proj#0..14=[{exprs}], $condition=[$t16])
EnumerableTableScan(table=[[foodmart2, product]])
!plan
# End dummy.iq {code}
I hope it is helpful.
> RelToSql converter fails when IN-list that the values include NULL is
> converted to Values
> -
>
> Key: CALCITE-6475
> URL: https://issues.apache.org/jira/browse/CALCITE-6475
> Project: Calcite
> Issue Type: Bug
>Reporter: xiong duan
>Priority: Major
>
> The SQL
> {code:java}
> select * from "product" where ("product_id") in (12, null){code}
> When we convert the IN-list to OR condition, it can run successfully. But
> when we convert it to VALUES, it will throw NPE:
> {code:java}
> java.lang.NullPointerException
> at
> org.apache.calcite.rel.rel2sql.SqlImplementor.toSql(SqlImplementor.java:1468)
> at
> org.apache.calcite.rel.rel2sql.SqlImplementor.toSql(SqlImplementor.java:1416)
> at
> org.apache.calcite.rel.rel2sql.SqlImplementor$Context.toSql(SqlImplementor.java:702)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.lambda$exprList$17(RelToSqlConverter.java:1158){code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6472) Add degree based trigonometric functions to PostgreSQL function library
[ https://issues.apache.org/jira/browse/CALCITE-6472?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17866885#comment-17866885 ] xiong duan commented on CALCITE-6472: - +1 We use "trigonometric" instead of "trig". > Add degree based trigonometric functions to PostgreSQL function library > --- > > Key: CALCITE-6472 > URL: https://issues.apache.org/jira/browse/CALCITE-6472 > Project: Calcite > Issue Type: Improvement > Components: core >Reporter: Norman Jordan >Assignee: Norman Jordan >Priority: Minor > Labels: pull-request-available > > PostgreSQL supports the following trigonometric functions that are degree > based. > * COSD > * SIND > * TAND > * ACOSD > * ASIND > * ATAND -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] (CALCITE-6480) OracleDialect does not support CASE WHEN returning boolean
[ https://issues.apache.org/jira/browse/CALCITE-6480 ]
xiong duan deleted comment on CALCITE-6480:
-
was (Author: nobigo):
[~suibianwanwan33] What do you mean we need to parse TRUE to 1=1, FALSE =>1=0?
If the SQL is:
{code:java}
select * from table where true [false]{code}
The Calcite will optimize it. And `select 1=1` is invalid in Oracle. Others I
think we directly use 1 or 0. Did I miss something? Maybe we can use SQL to
illustrate it.
> OracleDialect does not support CASE WHEN returning boolean
> --
>
> Key: CALCITE-6480
> URL: https://issues.apache.org/jira/browse/CALCITE-6480
> Project: Calcite
> Issue Type: Wish
> Components: core
>Reporter: kate
>Priority: Minor
>
> Our requirement is to use Calcite to translate queries into different
> dialects. During validation in the TPC-DS scenario, we found that {{Oracle}}
> does not support SQL statements like
> {code:java}
> SELECT * FROM xxx
> WHERE CASE WHEN a > 10 THEN b < 5 ELSE c > 0 END;{code}
> Therefore, we hope to remove such predicates at the dialect like Oracle.
>
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6480) OracleDialect does not support CASE WHEN returning boolean
[
https://issues.apache.org/jira/browse/CALCITE-6480?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17866744#comment-17866744
]
xiong duan commented on CALCITE-6480:
-
[~suibianwanwan33] What do you mean we need to parse TRUE to 1=1, FALSE =>1=0?
If the SQL is:
{code:java}
select * from table where true [false]{code}
The Calcite will optimize it. And `select 1=1` is invalid in Oracle. Others I
think we directly use 1 or 0. Did I miss something? Maybe we can use SQL to
illustrate it.
> OracleDialect does not support CASE WHEN returning boolean
> --
>
> Key: CALCITE-6480
> URL: https://issues.apache.org/jira/browse/CALCITE-6480
> Project: Calcite
> Issue Type: Wish
> Components: core
>Reporter: kate
>Priority: Minor
>
> Our requirement is to use Calcite to translate queries into different
> dialects. During validation in the TPC-DS scenario, we found that {{Oracle}}
> does not support SQL statements like
> {code:java}
> SELECT * FROM xxx
> WHERE CASE WHEN a > 10 THEN b < 5 ELSE c > 0 END;{code}
> Therefore, we hope to remove such predicates at the dialect like Oracle.
>
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6480) OracleDialect does not support CASE WHEN returning boolean
[
https://issues.apache.org/jira/browse/CALCITE-6480?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17866735#comment-17866735
]
xiong duan commented on CALCITE-6480:
-
+1, I agree to use 1 and 0 to replease 'true' and 'false'.
> OracleDialect does not support CASE WHEN returning boolean
> --
>
> Key: CALCITE-6480
> URL: https://issues.apache.org/jira/browse/CALCITE-6480
> Project: Calcite
> Issue Type: Wish
> Components: core
>Reporter: kate
>Priority: Minor
>
> Our requirement is to use Calcite to translate queries into different
> dialects. During validation in the TPC-DS scenario, we found that {{Oracle}}
> does not support SQL statements like
> {code:java}
> SELECT * FROM xxx
> WHERE CASE WHEN a > 10 THEN b < 5 ELSE c > 0 END;{code}
> Therefore, we hope to remove such predicates at the dialect like Oracle.
>
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6480) OracleDialect does not support CASE WHEN returning boolean
[
https://issues.apache.org/jira/browse/CALCITE-6480?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17866631#comment-17866631
]
xiong duan commented on CALCITE-6480:
-
[~suibianwanwan33] We can rewrite CASE WHEN in the RelToSql phase maybe
OracleSqlDialect is a good choice. This SQL can run successfully:
{code:java}
select
"EMPNO",
"ENAME",
"JOB",
"MGR",
"HIREDATE",
"SAL",
"COMM",
"DEPTNO"
from SCOTT."EMP" WHERE (CASE
WHEN "ENAME" = 'Sales' THEN 'true'
WHEN "ENAME" = 'HR' THEN 'true'
ELSE 'false'
END) = 'true'; {code}
> OracleDialect does not support CASE WHEN returning boolean
> --
>
> Key: CALCITE-6480
> URL: https://issues.apache.org/jira/browse/CALCITE-6480
> Project: Calcite
> Issue Type: Wish
> Components: core
>Reporter: kate
>Priority: Minor
>
> Our requirement is to use Calcite to translate queries into different
> dialects. During validation in the TPC-DS scenario, we found that {{Oracle}}
> does not support SQL statements like
> {code:java}
> SELECT * FROM xxx
> WHERE CASE WHEN a > 10 THEN b < 5 ELSE c > 0 END;{code}
> Therefore, we hope to remove such predicates at the dialect like Oracle.
>
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-6481) Optimize 'VALUES...UNION ALL...VALUES' to a single 'VALUES' when IN-list that the values include NULL is converted to Values
xiong duan created CALCITE-6481:
---
Summary: Optimize 'VALUES...UNION ALL...VALUES' to a single
'VALUES' when IN-list that the values include NULL is converted to Values
Key: CALCITE-6481
URL: https://issues.apache.org/jira/browse/CALCITE-6481
Project: Calcite
Issue Type: Improvement
Reporter: xiong duan
The SQL:
{code:java}
with
t1(a,y) as (select * from (values (1, 2), (3,
null),(7369,null),(7499,30),(null, 20),(null, 5)) as t1)
select *
from t1
where (t1.a,t1.y) in ((1, 2), (3, null),(7369,null),(7499,30),(null, 20),(null,
5));
EnumerableHashJoin(condition=[AND(=($0, $2), =($1, $3))], joinType=[semi])
EnumerableValues(tuples=[[{ 1, 2 }, { 3, null }, { 7369, null }, { 7499, 30
}, { null, 20 }, { null, 5 }]])
EnumerableUnion(all=[true])
EnumerableValues(tuples=[[{ 3, null }]])
EnumerableValues(tuples=[[{ 7369, null }]])
EnumerableValues(tuples=[[{ null, 20 }]])
EnumerableValues(tuples=[[{ null, 5 }]])
EnumerableValues(tuples=[[{ 1, 2 }, { 7499, 30 }]])
!plan
with
t1(a,y) as (select * from (values (1, 2), (3, 5),(7369,6),(7499,30),(2, 20),(3,
5)) as t1)
select *
from t1
where (t1.a,t1.y) in ((1, 2), (3, 3),(7369,3),(7499,30),(1, 20),(3, 5));
EnumerableHashJoin(condition=[AND(=($0, $2), =($1, $3))], joinType=[semi])
EnumerableValues(tuples=[[{ 1, 2 }, { 3, 5 }, { 7369, 6 }, { 7499, 30 }, { 2,
20 }, { 3, 5 }]])
EnumerableValues(tuples=[[{ 1, 2 }, { 3, 3 }, { 7369, 3 }, { 7499, 30 }, { 1,
20 }, { 3, 5 }]])
!plan {code}
If the IN-list includes NULL, Calcite will convert VALUES to UNION ALL.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-6478) JSON functions should return NULL when input is NULL
xiong duan created CALCITE-6478:
---
Summary: JSON functions should return NULL when input is NULL
Key: CALCITE-6478
URL: https://issues.apache.org/jira/browse/CALCITE-6478
Project: Calcite
Issue Type: Improvement
Reporter: xiong duan
Here is the test in H2:
{code:java}
SELECT js,
js IS JSON"json?",
js IS JSON value "json?",
js IS JSON SCALAR "scalar?",
js IS JSON OBJECT "object?",
js IS JSON ARRAY "array?"
FROM (VALUES ('123'),
('"abc"'),
('{"a": "b"}'),
('[1,2]'),
('abc'), NULL) foo(js);
{code}
{code:java}
+--+-+-+---+---+--+
|JS|json?|json?|scalar?|object?|array?|
+--+-+-+---+---+--+
|123 |true |true |true |false |false |
|"abc" |true |true |true |false |false |
|{"a": "b"}|true |true |false |true |false |
|[1,2] |true |true |false |false |true |
|abc |false|false|false |false |false |
|null |null |null |null |null |null |
+--+-+-+---+---+--+
{code}
Same SQL in Calcite:
{code:java}
++---+---+-+-++
| JS | json? | json? | scalar? | object? | array? |
++---+---+-+-++
| "abc" | true | true | true| false | false |
| 123| true | true | true| false | false |
| [1,2] | true | true | false | false | true |
| abc| false | false | false | false | false |
| {"a": "b"} | true | true | false | true| false |
|| false | false | false | false | false |
++---+---+-+-++
{code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6473) HAVING clauses may not contain window functions
[
https://issues.apache.org/jira/browse/CALCITE-6473?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17866581#comment-17866581
]
xiong duan commented on CALCITE-6473:
-
[~kgyrtkirk] Please add the reference version and chapter about the SQL
standards.
> HAVING clauses may not contain window functions
> ---
>
> Key: CALCITE-6473
> URL: https://issues.apache.org/jira/browse/CALCITE-6473
> Project: Calcite
> Issue Type: Bug
>Reporter: Zoltan Haindrich
>Assignee: Zoltan Haindrich
>Priority: Major
> Labels: pull-request-available
>
> according to the standard:
> {code}
> The shall not contain a without an
> intervening .
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6475) RelToSql converter fails when IN-list that the values include NULL is converted to Values
[
https://issues.apache.org/jira/browse/CALCITE-6475?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17866578#comment-17866578
]
xiong duan commented on CALCITE-6475:
-
This SQL can run successfully both ways:
{code:java}
select * from "product" where ("product_id") in (12, cast(null as
integer)){code}
> RelToSql converter fails when IN-list that the values include NULL is
> converted to Values
> -
>
> Key: CALCITE-6475
> URL: https://issues.apache.org/jira/browse/CALCITE-6475
> Project: Calcite
> Issue Type: Bug
>Reporter: xiong duan
>Priority: Major
>
> The SQL
> {code:java}
> select * from "product" where ("product_id") in (12, null){code}
> When we convert the IN-list to OR condition, it can run successfully. But
> when we convert it to VALUES, it will throw NPE:
> {code:java}
> java.lang.NullPointerException
> at
> org.apache.calcite.rel.rel2sql.SqlImplementor.toSql(SqlImplementor.java:1468)
> at
> org.apache.calcite.rel.rel2sql.SqlImplementor.toSql(SqlImplementor.java:1416)
> at
> org.apache.calcite.rel.rel2sql.SqlImplementor$Context.toSql(SqlImplementor.java:702)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.lambda$exprList$17(RelToSqlConverter.java:1158){code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-6475) RelToSql converter fails when IN-list that the values include NULL is converted to Values
xiong duan created CALCITE-6475:
---
Summary: RelToSql converter fails when IN-list that the values
include NULL is converted to Values
Key: CALCITE-6475
URL: https://issues.apache.org/jira/browse/CALCITE-6475
Project: Calcite
Issue Type: Bug
Reporter: xiong duan
The SQL
{code:java}
select * from "product" where ("product_id") in (12, null){code}
When we convert the IN-list to OR condition, it can run successfully. But when
we convert it to VALUES, it will throw NPE:
{code:java}
java.lang.NullPointerException
at
org.apache.calcite.rel.rel2sql.SqlImplementor.toSql(SqlImplementor.java:1468)
at
org.apache.calcite.rel.rel2sql.SqlImplementor.toSql(SqlImplementor.java:1416)
at
org.apache.calcite.rel.rel2sql.SqlImplementor$Context.toSql(SqlImplementor.java:702)
at
org.apache.calcite.rel.rel2sql.RelToSqlConverter.lambda$exprList$17(RelToSqlConverter.java:1158){code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6472) Add degree based trig functions to PostgreSQL function library
[ https://issues.apache.org/jira/browse/CALCITE-6472?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17866574#comment-17866574 ] xiong duan commented on CALCITE-6472: - According to [PostgreSQL-functions-math|https://www.postgresql.org/docs/16/functions-math.html]. How about we use "Trigonometric Functions"? > Add degree based trig functions to PostgreSQL function library > -- > > Key: CALCITE-6472 > URL: https://issues.apache.org/jira/browse/CALCITE-6472 > Project: Calcite > Issue Type: Improvement > Components: core >Reporter: Norman Jordan >Assignee: Norman Jordan >Priority: Minor > Labels: pull-request-available > > PostgreSQL supports the following trigonometric functions that are degree > based. > * COSD > * SIND > * TAND > * ACOSD > * ASIND > * ATAND -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6464) Type inference for DECIMAL division seems incorrect
[
https://issues.apache.org/jira/browse/CALCITE-6464?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17866271#comment-17866271
]
xiong duan commented on CALCITE-6464:
-
I checked SQL-2003 and SQL-2016, here is the document:
{code:java}
If the declared type of both operands of a dyadic arithmetic operator is exact
numeric, then the declared
type of the result is an implementation-defined exact numeric type, with
precision and scale determined
as follows:
a) Let S1 and S2 be the scale of the first and second operands respectively.
b) The precision of the result of addition and subtraction is
implementation-defined, and the scale is the
maximum of S1 and S2.
c) The precision of the result of multiplication is implementation-defined, and
the scale is S1 + S2.
d) The precision and scale of the result of division are
implementation-defined.{code}
Now we know, Calcite is custom implementation.
> Type inference for DECIMAL division seems incorrect
> ---
>
> Key: CALCITE-6464
> URL: https://issues.apache.org/jira/browse/CALCITE-6464
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.37.0
>Reporter: Mihai Budiu
>Assignee: Tim Grein
>Priority: Minor
> Labels: pull-request-available
>
> This bug surfaces if one uses a custom type system, e.g., where DECIMAL is
> limited to (28, 10).
> The problem is in RelDataTypeSystem.deriveDecimalDivideType.
> The JavaDoc of this function gives the algorithm for deriving the division
> result type.
> According to these rules, if you divide two numbers of type DECIMAL(28, 10),
> you should get a result with type DECIMAL(28, 10).
> But the actual implementation infers a type of DECIMAL(28, 0), which seems
> incorrect.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Resolved] (CALCITE-6450) Postgres CONCAT_WS function throws exception when parameter type is (, )
[
https://issues.apache.org/jira/browse/CALCITE-6450?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
xiong duan resolved CALCITE-6450.
-
Fix Version/s: 1.38.0
Resolution: Fixed
> Postgres CONCAT_WS function throws exception when parameter type is
> (, )
>
>
> Key: CALCITE-6450
> URL: https://issues.apache.org/jira/browse/CALCITE-6450
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.37.0
>Reporter: xiong duan
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.38.0
>
> Attachments: image-2024-06-29-14-57-56-513.png
>
>
> The SQL can run success in Postgres:
> {code:java}
> select concat_ws(',',ARRAY[1, 1, 1, 1]); {code}
> But in Calcite, It will throw exception:
> {code:java}
> @Test void testConcatFunction() {
> final String sql = "select concat_ws(',',ARRAY[1, 1, 1, 1])
> as c";
> fixture()
> .withFactory(c ->
> c.withOperatorTable(t ->
> SqlValidatorTest.operatorTableFor(SqlLibrary.POSTGRESQL)))
> .withCatalogReader(MockCatalogReaderExtended::create)
> .withSql(sql)
> .ok();
> }{code}
> {code:java}
> From line 1, column 8 to line 1, column 55: Cannot apply 'CONCAT_WS' to
> arguments of type 'CONCAT_WS(, )'. Supported form(s):
> 'CONCAT_WS()'{code}
> This issue find in comment
> [CALCITE-6446|https://github.com/apache/calcite/pull/3831] .
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6450) Postgres CONCAT_WS function throws exception when parameter type is (, )
[
https://issues.apache.org/jira/browse/CALCITE-6450?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17866235#comment-17866235
]
xiong duan commented on CALCITE-6450:
-
Fixed in
[e355036e|https://github.com/apache/calcite/commit/ace3afeafb3212dc96fb00f72ccd1632e355036e].
Thanks for the PR [~eveywu] . Thanks for the review [~cancai] .
> Postgres CONCAT_WS function throws exception when parameter type is
> (, )
>
>
> Key: CALCITE-6450
> URL: https://issues.apache.org/jira/browse/CALCITE-6450
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.37.0
>Reporter: xiong duan
>Priority: Major
> Labels: pull-request-available
> Attachments: image-2024-06-29-14-57-56-513.png
>
>
> The SQL can run success in Postgres:
> {code:java}
> select concat_ws(',',ARRAY[1, 1, 1, 1]); {code}
> But in Calcite, It will throw exception:
> {code:java}
> @Test void testConcatFunction() {
> final String sql = "select concat_ws(',',ARRAY[1, 1, 1, 1])
> as c";
> fixture()
> .withFactory(c ->
> c.withOperatorTable(t ->
> SqlValidatorTest.operatorTableFor(SqlLibrary.POSTGRESQL)))
> .withCatalogReader(MockCatalogReaderExtended::create)
> .withSql(sql)
> .ok();
> }{code}
> {code:java}
> From line 1, column 8 to line 1, column 55: Cannot apply 'CONCAT_WS' to
> arguments of type 'CONCAT_WS(, )'. Supported form(s):
> 'CONCAT_WS()'{code}
> This issue find in comment
> [CALCITE-6446|https://github.com/apache/calcite/pull/3831] .
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Resolved] (CALCITE-6462) VolcanoPlanner internal valid may throw exception when log trace is enabled
[
https://issues.apache.org/jira/browse/CALCITE-6462?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
xiong duan resolved CALCITE-6462.
-
Fix Version/s: 1.38.0
Resolution: Fixed
> VolcanoPlanner internal valid may throw exception when log trace is enabled
> ---
>
> Key: CALCITE-6462
> URL: https://issues.apache.org/jira/browse/CALCITE-6462
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.37.0
>Reporter: xiong duan
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.38.0
>
>
> Unit test in JdbcAdapterTest:
> {code:java}
> @Test void testAioob_5() {
> CalciteAssert.model(JdbcTest.SCOTT_MODEL)
> .query("select *\n"
> + "from scott.emp e left join scott.dept d\n"
> + "on 'job' in (select job from scott.bonus b)")
> .runs();
> }{code}
> Exceptions:
> {code:java}
> RelSubset [rel#108:RelSubset#7.ENUMERABLE.[]] has wrong best cost {1254.5
> rows, 340.5 cpu, 0.0 io}. Correct cost is {1752.0 rows, 337.5 cpu, 0.0
> io}{code}
> The best RelNode and the bestCode is different.
> When log trace is enabled, Tte code will be run:
> {code:java}
> if (LOGGER.isDebugEnabled()) {
> assert isValid(Litmus.THROW);
> }{code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6462) VolcanoPlanner internal valid may throw exception when log trace is enabled
[
https://issues.apache.org/jira/browse/CALCITE-6462?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17866223#comment-17866223
]
xiong duan commented on CALCITE-6462:
-
Fixed in
[1049f9|https://github.com/apache/calcite/commit/61e9e2552fcbf75c4d611bea0c1c491d4c1049f9].
Thanks for the review [~mbudiu], [~cancai], [~eveywu]. Hi [~mbudiu], If you
think the comment is not good, Please submit another PR for it. Welcome to
improve it.
> VolcanoPlanner internal valid may throw exception when log trace is enabled
> ---
>
> Key: CALCITE-6462
> URL: https://issues.apache.org/jira/browse/CALCITE-6462
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.37.0
>Reporter: xiong duan
>Priority: Major
> Labels: pull-request-available
>
> Unit test in JdbcAdapterTest:
> {code:java}
> @Test void testAioob_5() {
> CalciteAssert.model(JdbcTest.SCOTT_MODEL)
> .query("select *\n"
> + "from scott.emp e left join scott.dept d\n"
> + "on 'job' in (select job from scott.bonus b)")
> .runs();
> }{code}
> Exceptions:
> {code:java}
> RelSubset [rel#108:RelSubset#7.ENUMERABLE.[]] has wrong best cost {1254.5
> rows, 340.5 cpu, 0.0 io}. Correct cost is {1752.0 rows, 337.5 cpu, 0.0
> io}{code}
> The best RelNode and the bestCode is different.
> When log trace is enabled, Tte code will be run:
> {code:java}
> if (LOGGER.isDebugEnabled()) {
> assert isValid(Litmus.THROW);
> }{code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6458) RexPermuteInputsShuttle produces ArrayIndexOutOfBoundsException for join conditions with subselect
[
https://issues.apache.org/jira/browse/CALCITE-6458?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17866025#comment-17866025
]
xiong duan commented on CALCITE-6458:
-
[~corvinkuebler] Hi, My preliminary judgment is that it was caused by rewriting
SQL in SubQueryRemoveRule:Join when generate the condition. The RexInputRef
which in condition don't have a proper offset shift.
> RexPermuteInputsShuttle produces ArrayIndexOutOfBoundsException for join
> conditions with subselect
> --
>
> Key: CALCITE-6458
> URL: https://issues.apache.org/jira/browse/CALCITE-6458
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.37.0
>Reporter: Corvin Kuebler
>Priority: Major
>
> Hey!
> Running the following unit test in JdbcAdapterTest fails with
> {code:java}
> @Test void testAioob() {
> CalciteAssert.model(JdbcTest.SCOTT_MODEL)
> .query("select job\n"
>+ "from scott.emp e left join scott.dept d\n"
>+ "on e.deptno = d.deptno and e.job not in (select distinct
> job from scott.bonus b)")
> .runs();
> }
> {code}
> {code:java}
> Caused by: java.lang.ArrayIndexOutOfBoundsException: Index 5 out of bounds
> for length 5
> at
> org.apache.calcite.util.mapping.Mappings$PartialMapping.getTargetOpt(Mappings.java:1324)
> at
> org.apache.calcite.util.mapping.Mappings$AbstractMapping.getTarget(Mappings.java:943)
> at
> org.apache.calcite.rex.RexPermuteInputsShuttle.visitInputRef(RexPermuteInputsShuttle.java:87)
> at
> org.apache.calcite.rex.RexPermuteInputsShuttle.visitInputRef(RexPermuteInputsShuttle.java:35)
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6469) Join on condition generates wrong plan when the condition is IN sub-query
[
https://issues.apache.org/jira/browse/CALCITE-6469?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17865698#comment-17865698
]
xiong duan edited comment on CALCITE-6469 at 7/14/24 10:52 PM:
---
CALCITE-5138 support SQL is:
{code:java}
SELECT emp.deptno, emp.sal
FROM dept
LEFT JOIN emp ON (SELECT AVG(emp.sal) > 0 FROM emp) {code}
That's different in the ON condition.
was (Author: nobigo):
CALCITE-5183 support SQL is:
{code:java}
SELECT emp.deptno, emp.sal
FROM dept
LEFT JOIN emp ON (SELECT AVG(emp.sal) > 0 FROM emp) {code}
That's different in the ON condition.
> Join on condition generates wrong plan when the condition is IN sub-query
> -
>
> Key: CALCITE-6469
> URL: https://issues.apache.org/jira/browse/CALCITE-6469
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.37.0
>Reporter: xiong duan
>Assignee: xiong duan
>Priority: Major
> Fix For: 1.38.0
>
>
> Now Calcite will throw an exception when we execute the SQL:
> {code:java}
> select * from scott.bonus b left join scott.emp e on e.deptno in (select
> deptno from scott.dept b){code}
> I will try to rewrite this SQL become:
> {code:java}
> SELECT *
> FROM "SCOTT"."BONUS"
> LEFT JOIN ("SCOTT"."EMP" INNER JOIN (SELECT "DEPTNO"
> FROM "SCOTT"."DEPT"
> GROUP BY "DEPTNO") AS "t" ON "EMP"."DEPTNO" = "t"."DEPTNO") ON TRUE{code}
> The SQL :
> {code:java}
> select * from scott.emp e left join scott.bonus b on e.deptno in (select
> deptno from scott.dept b) {code}
> I will try to rewrite this SQL become:
> {code:java}
> SELECT *
> FROM "SCOTT"."EMP"
> LEFT JOIN ("SCOTT"."BONUS" CROSS JOIN (SELECT "DEPTNO"
> FROM "SCOTT"."DEPT"
> GROUP BY "DEPTNO") AS "t") ON "EMP"."DEPTNO" = "t"."DEPTNO" {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6469) Join on condition generates wrong plan when the condition is IN sub-query
[
https://issues.apache.org/jira/browse/CALCITE-6469?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17865698#comment-17865698
]
xiong duan commented on CALCITE-6469:
-
CALCITE-5183 support SQL is:
SELECT emp.deptno, emp.sal
FROM dept
LEFT JOIN emp ON (SELECT AVG(emp.sal) > 0 FROM emp)
That's different in the ON condition.
> Join on condition generates wrong plan when the condition is IN sub-query
> -
>
> Key: CALCITE-6469
> URL: https://issues.apache.org/jira/browse/CALCITE-6469
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.37.0
>Reporter: xiong duan
>Assignee: xiong duan
>Priority: Major
> Fix For: 1.38.0
>
>
> Now Calcite will throw an exception when we execute the SQL:
> {code:java}
> select * from scott.bonus b left join scott.emp e on e.deptno in (select
> deptno from scott.dept b){code}
> I will try to rewrite this SQL become:
> {code:java}
> SELECT *
> FROM "SCOTT"."BONUS"
> LEFT JOIN ("SCOTT"."EMP" INNER JOIN (SELECT "DEPTNO"
> FROM "SCOTT"."DEPT"
> GROUP BY "DEPTNO") AS "t" ON "EMP"."DEPTNO" = "t"."DEPTNO") ON TRUE{code}
> The SQL :
> {code:java}
> select * from scott.emp e left join scott.bonus b on e.deptno in (select
> deptno from scott.dept b) {code}
> I will try to rewrite this SQL become:
> {code:java}
> SELECT *
> FROM "SCOTT"."EMP"
> LEFT JOIN ("SCOTT"."BONUS" CROSS JOIN (SELECT "DEPTNO"
> FROM "SCOTT"."DEPT"
> GROUP BY "DEPTNO") AS "t") ON "EMP"."DEPTNO" = "t"."DEPTNO" {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6469) Join on condition generates wrong plan when the condition is IN sub-query
[
https://issues.apache.org/jira/browse/CALCITE-6469?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17865698#comment-17865698
]
xiong duan edited comment on CALCITE-6469 at 7/13/24 3:20 PM:
--
CALCITE-5183 support SQL is:
{code:java}
SELECT emp.deptno, emp.sal
FROM dept
LEFT JOIN emp ON (SELECT AVG(emp.sal) > 0 FROM emp) {code}
That's different in the ON condition.
was (Author: nobigo):
CALCITE-5183 support SQL is:
SELECT emp.deptno, emp.sal
FROM dept
LEFT JOIN emp ON (SELECT AVG(emp.sal) > 0 FROM emp)
That's different in the ON condition.
> Join on condition generates wrong plan when the condition is IN sub-query
> -
>
> Key: CALCITE-6469
> URL: https://issues.apache.org/jira/browse/CALCITE-6469
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.37.0
>Reporter: xiong duan
>Assignee: xiong duan
>Priority: Major
> Fix For: 1.38.0
>
>
> Now Calcite will throw an exception when we execute the SQL:
> {code:java}
> select * from scott.bonus b left join scott.emp e on e.deptno in (select
> deptno from scott.dept b){code}
> I will try to rewrite this SQL become:
> {code:java}
> SELECT *
> FROM "SCOTT"."BONUS"
> LEFT JOIN ("SCOTT"."EMP" INNER JOIN (SELECT "DEPTNO"
> FROM "SCOTT"."DEPT"
> GROUP BY "DEPTNO") AS "t" ON "EMP"."DEPTNO" = "t"."DEPTNO") ON TRUE{code}
> The SQL :
> {code:java}
> select * from scott.emp e left join scott.bonus b on e.deptno in (select
> deptno from scott.dept b) {code}
> I will try to rewrite this SQL become:
> {code:java}
> SELECT *
> FROM "SCOTT"."EMP"
> LEFT JOIN ("SCOTT"."BONUS" CROSS JOIN (SELECT "DEPTNO"
> FROM "SCOTT"."DEPT"
> GROUP BY "DEPTNO") AS "t") ON "EMP"."DEPTNO" = "t"."DEPTNO" {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-6469) Join on condition generates wrong plan when the condition is IN sub-query
xiong duan created CALCITE-6469:
---
Summary: Join on condition generates wrong plan when the condition
is IN sub-query
Key: CALCITE-6469
URL: https://issues.apache.org/jira/browse/CALCITE-6469
Project: Calcite
Issue Type: Bug
Affects Versions: 1.37.0
Reporter: xiong duan
Assignee: xiong duan
Fix For: 1.38.0
Now Calcite will throw an exception when we execute the SQL:
{code:java}
select * from scott.bonus b left join scott.emp e on e.deptno in (select
deptno from scott.dept b){code}
I will try to rewrite this SQL become:
{code:java}
SELECT *
FROM "SCOTT"."BONUS"
LEFT JOIN ("SCOTT"."EMP" INNER JOIN (SELECT "DEPTNO"
FROM "SCOTT"."DEPT"
GROUP BY "DEPTNO") AS "t" ON "EMP"."DEPTNO" = "t"."DEPTNO") ON TRUE{code}
The SQL :
{code:java}
select * from scott.emp e left join scott.bonus b on e.deptno in (select
deptno from scott.dept b) {code}
I will try to rewrite this SQL become:
{code:java}
SELECT *
FROM "SCOTT"."EMP"
LEFT JOIN ("SCOTT"."BONUS" CROSS JOIN (SELECT "DEPTNO"
FROM "SCOTT"."DEPT"
GROUP BY "DEPTNO") AS "t") ON "EMP"."DEPTNO" = "t"."DEPTNO" {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-6469) Join on condition generates wrong plan when the condition is IN sub-query
[
https://issues.apache.org/jira/browse/CALCITE-6469?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
xiong duan updated CALCITE-6469:
Description:
Now Calcite will throw an exception when we execute the SQL:
{code:java}
select * from scott.bonus b left join scott.emp e on e.deptno in (select
deptno from scott.dept b){code}
I will try to rewrite this SQL become:
{code:java}
SELECT *
FROM "SCOTT"."BONUS"
LEFT JOIN ("SCOTT"."EMP" INNER JOIN (SELECT "DEPTNO"
FROM "SCOTT"."DEPT"
GROUP BY "DEPTNO") AS "t" ON "EMP"."DEPTNO" = "t"."DEPTNO") ON TRUE{code}
The SQL :
{code:java}
select * from scott.emp e left join scott.bonus b on e.deptno in (select
deptno from scott.dept b) {code}
I will try to rewrite this SQL become:
{code:java}
SELECT *
FROM "SCOTT"."EMP"
LEFT JOIN ("SCOTT"."BONUS" CROSS JOIN (SELECT "DEPTNO"
FROM "SCOTT"."DEPT"
GROUP BY "DEPTNO") AS "t") ON "EMP"."DEPTNO" = "t"."DEPTNO" {code}
was:
Now Calcite will throw an exception when we execute the SQL:
{code:java}
select * from scott.bonus b left join scott.emp e on e.deptno in (select
deptno from scott.dept b){code}
I will try to rewrite this SQL become:
{code:java}
SELECT *
FROM "SCOTT"."BONUS"
LEFT JOIN ("SCOTT"."EMP" INNER JOIN (SELECT "DEPTNO"
FROM "SCOTT"."DEPT"
GROUP BY "DEPTNO") AS "t" ON "EMP"."DEPTNO" = "t"."DEPTNO") ON TRUE{code}
The SQL :
{code:java}
select * from scott.emp e left join scott.bonus b on e.deptno in (select
deptno from scott.dept b) {code}
I will try to rewrite this SQL become:
{code:java}
SELECT *
FROM "SCOTT"."EMP"
LEFT JOIN ("SCOTT"."BONUS" CROSS JOIN (SELECT "DEPTNO"
FROM "SCOTT"."DEPT"
GROUP BY "DEPTNO") AS "t") ON "EMP"."DEPTNO" = "t"."DEPTNO" {code}
> Join on condition generates wrong plan when the condition is IN sub-query
> -
>
> Key: CALCITE-6469
> URL: https://issues.apache.org/jira/browse/CALCITE-6469
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.37.0
>Reporter: xiong duan
>Assignee: xiong duan
>Priority: Major
> Fix For: 1.38.0
>
>
> Now Calcite will throw an exception when we execute the SQL:
> {code:java}
> select * from scott.bonus b left join scott.emp e on e.deptno in (select
> deptno from scott.dept b){code}
> I will try to rewrite this SQL become:
> {code:java}
> SELECT *
> FROM "SCOTT"."BONUS"
> LEFT JOIN ("SCOTT"."EMP" INNER JOIN (SELECT "DEPTNO"
> FROM "SCOTT"."DEPT"
> GROUP BY "DEPTNO") AS "t" ON "EMP"."DEPTNO" = "t"."DEPTNO") ON TRUE{code}
> The SQL :
> {code:java}
> select * from scott.emp e left join scott.bonus b on e.deptno in (select
> deptno from scott.dept b) {code}
> I will try to rewrite this SQL become:
> {code:java}
> SELECT *
> FROM "SCOTT"."EMP"
> LEFT JOIN ("SCOTT"."BONUS" CROSS JOIN (SELECT "DEPTNO"
> FROM "SCOTT"."DEPT"
> GROUP BY "DEPTNO") AS "t") ON "EMP"."DEPTNO" = "t"."DEPTNO" {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Resolved] (CALCITE-6453) Simplify casts which are result of constant reduction
[
https://issues.apache.org/jira/browse/CALCITE-6453?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
xiong duan resolved CALCITE-6453.
-
Fix Version/s: 1.38.0
Resolution: Fixed
> Simplify casts which are result of constant reduction
> -
>
> Key: CALCITE-6453
> URL: https://issues.apache.org/jira/browse/CALCITE-6453
> Project: Calcite
> Issue Type: Improvement
>Reporter: Krisztian Kasa
>Assignee: Krisztian Kasa
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.38.0
>
>
> RexSimplify transforms nullable cast expressions with a non-nullable literal
> operand which type is different from the cast type to another cast expression
> with a literal operand with the same type as the cast.
> If simplify is called again using the result cast expression of the first
> simplification we get a literal only as a result.
> Example:
> Initial expression
> {code:java}
> CAST(_UTF-16LE'2020-10-30':VARCHAR(2147483647) CHARACTER SET "UTF-16LE"):DATE
> {code}
> where the operand is non-nullable varchar and the result of the cast is
> nullable date.
> After the first simplify call we get
> {code:java}
> CAST(2020-10-30:DATE):DATE
> {code}
> where the operand type is non-nullable date and the cast type is nullable
> date.
> https://github.com/apache/calcite/blob/8ab0b03326730aa2cc6b476b2cbd8f99799bdacb/core/src/main/java/org/apache/calcite/rex/RexSimplify.java#L2269-L2276
> After the second simplify call we get
> {code:java}
> 2020-10-30:DATE
> {code}
> where the literal type is non-nullable date
> https://github.com/apache/calcite/blob/8ab0b03326730aa2cc6b476b2cbd8f99799bdacb/core/src/main/java/org/apache/calcite/rex/RexSimplify.java#L2195-L2196
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6453) Simplify casts which are result of constant reduction
[
https://issues.apache.org/jira/browse/CALCITE-6453?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17865687#comment-17865687
]
xiong duan commented on CALCITE-6453:
-
Fixed in
[d21c5c9|https://github.com/apache/calcite/commit/d21c5c9c17d6733468800f652ab15932e84a012e].
Thanks for the PR [~kkasa] . Thanks for the review [~asolimando], [~cancai],
[~kgyrtkirk], [~eveywu].
> Simplify casts which are result of constant reduction
> -
>
> Key: CALCITE-6453
> URL: https://issues.apache.org/jira/browse/CALCITE-6453
> Project: Calcite
> Issue Type: Improvement
>Reporter: Krisztian Kasa
>Assignee: Krisztian Kasa
>Priority: Major
> Labels: pull-request-available
>
> RexSimplify transforms nullable cast expressions with a non-nullable literal
> operand which type is different from the cast type to another cast expression
> with a literal operand with the same type as the cast.
> If simplify is called again using the result cast expression of the first
> simplification we get a literal only as a result.
> Example:
> Initial expression
> {code:java}
> CAST(_UTF-16LE'2020-10-30':VARCHAR(2147483647) CHARACTER SET "UTF-16LE"):DATE
> {code}
> where the operand is non-nullable varchar and the result of the cast is
> nullable date.
> After the first simplify call we get
> {code:java}
> CAST(2020-10-30:DATE):DATE
> {code}
> where the operand type is non-nullable date and the cast type is nullable
> date.
> https://github.com/apache/calcite/blob/8ab0b03326730aa2cc6b476b2cbd8f99799bdacb/core/src/main/java/org/apache/calcite/rex/RexSimplify.java#L2269-L2276
> After the second simplify call we get
> {code:java}
> 2020-10-30:DATE
> {code}
> where the literal type is non-nullable date
> https://github.com/apache/calcite/blob/8ab0b03326730aa2cc6b476b2cbd8f99799bdacb/core/src/main/java/org/apache/calcite/rex/RexSimplify.java#L2195-L2196
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6463) alias generation issue about correlated subquery handling
[
https://issues.apache.org/jira/browse/CALCITE-6463?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17864554#comment-17864554
]
xiong duan commented on CALCITE-6463:
-
[~messa9e] Hi Please add code format tag in issue description.
> alias generation issue about correlated subquery handling
> -
>
> Key: CALCITE-6463
> URL: https://issues.apache.org/jira/browse/CALCITE-6463
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.37.0
>Reporter: shiji.mei
>Priority: Blocker
> Fix For: 1.38.0
>
>
> [dialect|#] is postgresql
> original query:
> SELECT MAX(distinct l_orderkey) FROM lineitem where exists( SELECT
> MAX(c_custkey) FROM customer where c_custkey = l_orderkey GROUP BY c_custkey);
> Rewritten query：
> SELECT MAX("l_orderkey")
> FROM "lineitem"
> WHERE EXISTS (SELECT "c_custkey", MAX("c_custkey")
> FROM "customer"
> WHERE "c_custkey" = "lineitem12"."l_orderkey"
> GROUP BY "c_custkey")
> lineitem becomes lineitem12，and it will become a different number every time
> it runs。
> thank you !
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-6463) alias generation issue about correlated subquery handling
[
https://issues.apache.org/jira/browse/CALCITE-6463?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
xiong duan updated CALCITE-6463:
Description:
original query:
{code:java}
SELECT MAX(distinct l_orderkey) FROM lineitem where exists( SELECT
MAX(c_custkey) FROM customer where c_custkey = l_orderkey GROUP BY
c_custkey);{code}
Rewritten query：
{code:java}
SELECT MAX("l_orderkey")
FROM "lineitem"
WHERE EXISTS (SELECT "c_custkey", MAX("c_custkey")
FROM "customer"
WHERE "c_custkey" = "lineitem12"."l_orderkey"
GROUP BY "c_custkey"){code}
lineitem becomes lineitem12，and it will become a different number every time it
runs。
thank you !
was:
original query:
SELECT MAX(distinct l_orderkey) FROM lineitem where exists( SELECT
MAX(c_custkey) FROM customer where c_custkey = l_orderkey GROUP BY c_custkey);
Rewritten query：
SELECT MAX("l_orderkey")
FROM "lineitem"
WHERE EXISTS (SELECT "c_custkey", MAX("c_custkey")
FROM "customer"
WHERE "c_custkey" = "lineitem12"."l_orderkey"
GROUP BY "c_custkey")
lineitem becomes lineitem12，and it will become a different number every time it
runs。
thank you !
> alias generation issue about correlated subquery handling
> -
>
> Key: CALCITE-6463
> URL: https://issues.apache.org/jira/browse/CALCITE-6463
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.37.0
>Reporter: shiji.mei
>Priority: Blocker
> Fix For: 1.38.0
>
>
> original query:
> {code:java}
> SELECT MAX(distinct l_orderkey) FROM lineitem where exists( SELECT
> MAX(c_custkey) FROM customer where c_custkey = l_orderkey GROUP BY
> c_custkey);{code}
> Rewritten query：
> {code:java}
> SELECT MAX("l_orderkey")
> FROM "lineitem"
> WHERE EXISTS (SELECT "c_custkey", MAX("c_custkey")
> FROM "customer"
> WHERE "c_custkey" = "lineitem12"."l_orderkey"
> GROUP BY "c_custkey"){code}
> lineitem becomes lineitem12，and it will become a different number every time
> it runs。
> thank you !
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-6462) VolcanoPlanner internal valid may throw exception when log trace is enabled
xiong duan created CALCITE-6462:
---
Summary: VolcanoPlanner internal valid may throw exception when
log trace is enabled
Key: CALCITE-6462
URL: https://issues.apache.org/jira/browse/CALCITE-6462
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.37.0
Reporter: xiong duan
Unit test in JdbcAdapterTest:
{code:java}
@Test void testAioob_5() {
CalciteAssert.model(JdbcTest.SCOTT_MODEL)
.query("select *\n"
+ "from scott.emp e left join scott.dept d\n"
+ "on 'job' in (select job from scott.bonus b)")
.runs();
}{code}
Exceptions:
{code:java}
RelSubset [rel#108:RelSubset#7.ENUMERABLE.[]] has wrong best cost {1254.5 rows,
340.5 cpu, 0.0 io}. Correct cost is {1752.0 rows, 337.5 cpu, 0.0 io}{code}
The best RelNode and the bestCode is different.
When log trace is enabled, Tte code will be run:
{code:java}
if (LOGGER.isDebugEnabled()) {
assert isValid(Litmus.THROW);
}{code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6458) RexPermuteInputsShuttle produces ArrayIndexOutOfBoundsException for join conditions with subselect
[
https://issues.apache.org/jira/browse/CALCITE-6458?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17863654#comment-17863654
]
xiong duan commented on CALCITE-6458:
-
This issue exception info looks same as CALCITE-6452.
> RexPermuteInputsShuttle produces ArrayIndexOutOfBoundsException for join
> conditions with subselect
> --
>
> Key: CALCITE-6458
> URL: https://issues.apache.org/jira/browse/CALCITE-6458
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.37.0
>Reporter: Corvin Kuebler
>Priority: Major
>
> Hey!
> Running the following unit test in JdbcAdapterTest fails with
> {code:java}
> @Test void testAioob() {
> CalciteAssert.model(JdbcTest.SCOTT_MODEL)
> .query("select job\n"
>+ "from scott.emp e left join scott.dept d\n"
>+ "on e.deptno = d.deptno and e.job not in (select distinct
> job from scott.bonus b)")
> .runs();
> }
> {code}
> {code:java}
> java.lang.ArrayIndexOutOfBoundsException: Index 5 out of bounds for length 5
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6450) Postgres CONCAT_WS function throws exception when parameter type is (, )
[
https://issues.apache.org/jira/browse/CALCITE-6450?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17863463#comment-17863463
]
xiong duan commented on CALCITE-6450:
-
[~eveywu] , Please take over it. Thanks.
> Postgres CONCAT_WS function throws exception when parameter type is
> (, )
>
>
> Key: CALCITE-6450
> URL: https://issues.apache.org/jira/browse/CALCITE-6450
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.37.0
>Reporter: xiong duan
>Priority: Major
> Labels: pull-request-available
> Attachments: image-2024-06-29-14-57-56-513.png
>
>
> The SQL can run success in Postgres:
> {code:java}
> select concat_ws(',',ARRAY[1, 1, 1, 1]); {code}
> But in Calcite, It will throw exception:
> {code:java}
> @Test void testConcatFunction() {
> final String sql = "select concat_ws(',',ARRAY[1, 1, 1, 1])
> as c";
> fixture()
> .withFactory(c ->
> c.withOperatorTable(t ->
> SqlValidatorTest.operatorTableFor(SqlLibrary.POSTGRESQL)))
> .withCatalogReader(MockCatalogReaderExtended::create)
> .withSql(sql)
> .ok();
> }{code}
> {code:java}
> From line 1, column 8 to line 1, column 55: Cannot apply 'CONCAT_WS' to
> arguments of type 'CONCAT_WS(, )'. Supported form(s):
> 'CONCAT_WS()'{code}
> This issue find in comment
> [CALCITE-6446|https://github.com/apache/calcite/pull/3831] .
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6453) Simplify casts which are result of constant reduction
[
https://issues.apache.org/jira/browse/CALCITE-6453?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17863423#comment-17863423
]
xiong duan commented on CALCITE-6453:
-
[~kkasa] Thanks for the explain. +1.
> Simplify casts which are result of constant reduction
> -
>
> Key: CALCITE-6453
> URL: https://issues.apache.org/jira/browse/CALCITE-6453
> Project: Calcite
> Issue Type: Improvement
>Reporter: Krisztian Kasa
>Assignee: Krisztian Kasa
>Priority: Major
> Labels: pull-request-available
>
> RexSimplify transforms nullable cast expressions with a non-nullable literal
> operand which type is different from the cast type to another cast expression
> with a literal operand with the same type as the cast.
> If simplify is called again using the result cast expression of the first
> simplification we get a literal only as a result.
> Example:
> Initial expression
> {code:java}
> CAST(_UTF-16LE'2020-10-30':VARCHAR(2147483647) CHARACTER SET "UTF-16LE"):DATE
> {code}
> where the operand is non-nullable varchar and the result of the cast is
> nullable date.
> After the first simplify call we get
> {code:java}
> CAST(2020-10-30:DATE):DATE
> {code}
> where the operand type is non-nullable date and the cast type is nullable
> date.
> https://github.com/apache/calcite/blob/8ab0b03326730aa2cc6b476b2cbd8f99799bdacb/core/src/main/java/org/apache/calcite/rex/RexSimplify.java#L2269-L2276
> After the second simplify call we get
> {code:java}
> 2020-10-30:DATE
> {code}
> where the literal type is non-nullable date
> https://github.com/apache/calcite/blob/8ab0b03326730aa2cc6b476b2cbd8f99799bdacb/core/src/main/java/org/apache/calcite/rex/RexSimplify.java#L2195-L2196
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6401) JDBC adapter cannot push down JOIN with condition includes IS TRUE、IS NULL、Dynamic parameter、CAST、Literal comparison
[
https://issues.apache.org/jira/browse/CALCITE-6401?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17863208#comment-17863208
]
xiong duan commented on CALCITE-6401:
-
Fixed in
[c5762b7|https://github.com/apache/calcite/commit/8a96095a64bc1cc955438d219d5f1fbcbc5762b7].
Thanks for the review [~caicancai] .
> JDBC adapter cannot push down JOIN with condition includes IS TRUE、IS
> NULL、Dynamic parameter、CAST、Literal comparison
>
>
> Key: CALCITE-6401
> URL: https://issues.apache.org/jira/browse/CALCITE-6401
> Project: Calcite
> Issue Type: Improvement
>Affects Versions: 1.36.0
>Reporter: Ulrich Kramer
>Priority: Major
> Labels: pull-request-available
>
> JDBC adapter (in JdbcJoinRule) cannot push down joins with conditions which
> include operations not listed in {{JdbcJoinRule::canJoinOnCondition}}.
> See also CALCITE-4907
> For example the following statement is executed using an
> {{EnumerableNestedLoopJoin}}
> {code:SQL}
> SELECT
> *
> FROM
>A
> JOIN (
> SELECT
> D."userId",
> MAX(D."id") as "id"
> FROM
>D
> GROUP BY
> D."userId"
> ) B ON (
> A."id" = B."id" AND A."userId" IS NOT NULL
> )
> OR (
> A."userId" = B."userId" AND A."id" IS NOT NULL
> )
> {code}
> Adding the cases {{IS_NULL}} and {{IS_NOT_NULL}} to
> {{JdbcJoinRule::canJoinOnCondition}} fixes the problem for this statement.
> But I was not able to find out which cases are also missing here. E.g. a join
> condition which compares a RexInputRef with a RexLiteral also fails.
> Where could I find the associated code in {{JdbcJoin::implement}} that makes
> it impossible to create an appropriate SQL statement if all operations were
> allowed?
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-6401) JDBC adapter cannot push down JOIN with condition includes IS TRUE、IS NULL、Dynamic parameter、CAST、Literal comparison
[
https://issues.apache.org/jira/browse/CALCITE-6401?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
xiong duan updated CALCITE-6401:
Summary: JDBC adapter cannot push down JOIN with condition includes IS
TRUE、IS NULL、Dynamic parameter、CAST、Literal comparison (was: JDBC adapter
cannot push down joins with complex JOIN condition)
> JDBC adapter cannot push down JOIN with condition includes IS TRUE、IS
> NULL、Dynamic parameter、CAST、Literal comparison
>
>
> Key: CALCITE-6401
> URL: https://issues.apache.org/jira/browse/CALCITE-6401
> Project: Calcite
> Issue Type: Improvement
>Affects Versions: 1.36.0
>Reporter: Ulrich Kramer
>Priority: Major
> Labels: pull-request-available
>
> JDBC adapter (in JdbcJoinRule) cannot push down joins with conditions which
> include operations not listed in {{JdbcJoinRule::canJoinOnCondition}}.
> See also CALCITE-4907
> For example the following statement is executed using an
> {{EnumerableNestedLoopJoin}}
> {code:SQL}
> SELECT
> *
> FROM
>A
> JOIN (
> SELECT
> D."userId",
> MAX(D."id") as "id"
> FROM
>D
> GROUP BY
> D."userId"
> ) B ON (
> A."id" = B."id" AND A."userId" IS NOT NULL
> )
> OR (
> A."userId" = B."userId" AND A."id" IS NOT NULL
> )
> {code}
> Adding the cases {{IS_NULL}} and {{IS_NOT_NULL}} to
> {{JdbcJoinRule::canJoinOnCondition}} fixes the problem for this statement.
> But I was not able to find out which cases are also missing here. E.g. a join
> condition which compares a RexInputRef with a RexLiteral also fails.
> Where could I find the associated code in {{JdbcJoin::implement}} that makes
> it impossible to create an appropriate SQL statement if all operations were
> allowed?
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Resolved] (CALCITE-6401) JDBC adapter cannot push down JOIN with condition includes IS TRUE、IS NULL、Dynamic parameter、CAST、Literal comparison
[
https://issues.apache.org/jira/browse/CALCITE-6401?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
xiong duan resolved CALCITE-6401.
-
Fix Version/s: 1.38.0
Assignee: xiong duan
Resolution: Fixed
> JDBC adapter cannot push down JOIN with condition includes IS TRUE、IS
> NULL、Dynamic parameter、CAST、Literal comparison
>
>
> Key: CALCITE-6401
> URL: https://issues.apache.org/jira/browse/CALCITE-6401
> Project: Calcite
> Issue Type: Improvement
>Affects Versions: 1.36.0
>Reporter: Ulrich Kramer
>Assignee: xiong duan
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.38.0
>
>
> JDBC adapter (in JdbcJoinRule) cannot push down joins with conditions which
> include operations not listed in {{JdbcJoinRule::canJoinOnCondition}}.
> See also CALCITE-4907
> For example the following statement is executed using an
> {{EnumerableNestedLoopJoin}}
> {code:SQL}
> SELECT
> *
> FROM
>A
> JOIN (
> SELECT
> D."userId",
> MAX(D."id") as "id"
> FROM
>D
> GROUP BY
> D."userId"
> ) B ON (
> A."id" = B."id" AND A."userId" IS NOT NULL
> )
> OR (
> A."userId" = B."userId" AND A."id" IS NOT NULL
> )
> {code}
> Adding the cases {{IS_NULL}} and {{IS_NOT_NULL}} to
> {{JdbcJoinRule::canJoinOnCondition}} fixes the problem for this statement.
> But I was not able to find out which cases are also missing here. E.g. a join
> condition which compares a RexInputRef with a RexLiteral also fails.
> Where could I find the associated code in {{JdbcJoin::implement}} that makes
> it impossible to create an appropriate SQL statement if all operations were
> allowed?
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6453) Simplify casts which are result of constant reduction
[
https://issues.apache.org/jira/browse/CALCITE-6453?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17863205#comment-17863205
]
xiong duan commented on CALCITE-6453:
-
Hi [~kkasa], If I didn't misunderstand this issue try to `Simplify
'cast(Literal as nullable)' to 'Literal' (when Literal is not nullable)`.
If this is a valid optimize, Do we think `Simplify 'cast(p as nullable)' to
'p' (when p is not nullable)` is valid too? I know this is strange.
Besides This PR looks only handle the DATE Type.
> Simplify casts which are result of constant reduction
> -
>
> Key: CALCITE-6453
> URL: https://issues.apache.org/jira/browse/CALCITE-6453
> Project: Calcite
> Issue Type: Improvement
>Reporter: Krisztian Kasa
>Assignee: Krisztian Kasa
>Priority: Major
> Labels: pull-request-available
>
> RexSimplify transforms nullable cast expressions with a non-nullable literal
> operand which type is different from the cast type to another cast expression
> with a literal operand with the same type as the cast.
> If simplify is called again using the result cast expression of the first
> simplification we get a literal only as a result.
> Example:
> Initial expression
> {code:java}
> CAST(_UTF-16LE'2020-10-30':VARCHAR(2147483647) CHARACTER SET "UTF-16LE"):DATE
> {code}
> where the operand is non-nullable varchar and the result of the cast is
> nullable date.
> After the first simplify call we get
> {code:java}
> CAST(2020-10-30:DATE):DATE
> {code}
> where the operand type is non-nullable date and the cast type is nullable
> date.
> https://github.com/apache/calcite/blob/8ab0b03326730aa2cc6b476b2cbd8f99799bdacb/core/src/main/java/org/apache/calcite/rex/RexSimplify.java#L2269-L2276
> After the second simplify call we get
> {code:java}
> 2020-10-30:DATE
> {code}
> where the literal type is non-nullable date
> https://github.com/apache/calcite/blob/8ab0b03326730aa2cc6b476b2cbd8f99799bdacb/core/src/main/java/org/apache/calcite/rex/RexSimplify.java#L2195-L2196
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6401) JDBC adapter cannot push down joins with complex JOIN condition
[
https://issues.apache.org/jira/browse/CALCITE-6401?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17863053#comment-17863053
]
xiong duan commented on CALCITE-6401:
-
[~kramerul] I test it. Maybe we should support IS TRUE、IS FALSE、IS
NULL、Dynamic parameter(?)、CAST、Literal comparison. That's what the unit test
can cover in this PR.
> JDBC adapter cannot push down joins with complex JOIN condition
> ---
>
> Key: CALCITE-6401
> URL: https://issues.apache.org/jira/browse/CALCITE-6401
> Project: Calcite
> Issue Type: Improvement
>Affects Versions: 1.36.0
>Reporter: Ulrich Kramer
>Priority: Major
>
> JDBC adapter (in JdbcJoinRule) cannot push down joins with conditions which
> include operations not listed in {{JdbcJoinRule::canJoinOnCondition}}.
> See also CALCITE-4907
> For example the following statement is executed using an
> {{EnumerableNestedLoopJoin}}
> {code:SQL}
> SELECT
> *
> FROM
>A
> JOIN (
> SELECT
> D."userId",
> MAX(D."id") as "id"
> FROM
>D
> GROUP BY
> D."userId"
> ) B ON (
> A."id" = B."id" AND A."userId" IS NOT NULL
> )
> OR (
> A."userId" = B."userId" AND A."id" IS NOT NULL
> )
> {code}
> Adding the cases {{IS_NULL}} and {{IS_NOT_NULL}} to
> {{JdbcJoinRule::canJoinOnCondition}} fixes the problem for this statement.
> But I was not able to find out which cases are also missing here. E.g. a join
> condition which compares a RexInputRef with a RexLiteral also fails.
> Where could I find the associated code in {{JdbcJoin::implement}} that makes
> it impossible to create an appropriate SQL statement if all operations were
> allowed?
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6436) JDBC adapter generates SQL missing parentheses when comparing 3 values with the same precedence like (a=b)=c
[
https://issues.apache.org/jira/browse/CALCITE-6436?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17862762#comment-17862762
]
xiong duan commented on CALCITE-6436:
-
Fixed in
[d2e3350|https://github.com/apache/calcite/commit/d2e335021184b7a30af459c1f2d587286d017e61].
Thanks for the review [~mbudiu] and [~caicancai].
> JDBC adapter generates SQL missing parentheses when comparing 3 values with
> the same precedence like (a=b)=c
>
>
> Key: CALCITE-6436
> URL: https://issues.apache.org/jira/browse/CALCITE-6436
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.37.0
>Reporter: Ulrich Kramer
>Assignee: xiong duan
>Priority: Major
> Labels: pull-request-available
>
> Running the following unit test in {{JdbcAdapterTest}} fails with
> {code:java}
> @Test void testOperatorInWhere() {
> CalciteAssert.model(FoodmartSchema.FOODMART_MODEL)
> .query("select * from \"sales_fact_1997\" "
> + "where (\"product_id\" = 1) = ?")
> .consumesPreparedStatement(p -> p.setBoolean(1, true))
> .runs();
> }
> {code}
> {noformat}
> Caused by: java.sql.SQLSyntaxErrorException: unexpected token: = : line: 3 in
> statement [SELECT *
> FROM "foodmart"."sales_fact_1997"
> WHERE "product_id" = 1 = ?
> {noformat}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Resolved] (CALCITE-6436) JDBC adapter generates SQL missing parentheses when comparing 3 values with the same precedence like (a=b)=c
[
https://issues.apache.org/jira/browse/CALCITE-6436?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
xiong duan resolved CALCITE-6436.
-
Fix Version/s: 1.38.0
Resolution: Fixed
> JDBC adapter generates SQL missing parentheses when comparing 3 values with
> the same precedence like (a=b)=c
>
>
> Key: CALCITE-6436
> URL: https://issues.apache.org/jira/browse/CALCITE-6436
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.37.0
>Reporter: Ulrich Kramer
>Assignee: xiong duan
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.38.0
>
>
> Running the following unit test in {{JdbcAdapterTest}} fails with
> {code:java}
> @Test void testOperatorInWhere() {
> CalciteAssert.model(FoodmartSchema.FOODMART_MODEL)
> .query("select * from \"sales_fact_1997\" "
> + "where (\"product_id\" = 1) = ?")
> .consumesPreparedStatement(p -> p.setBoolean(1, true))
> .runs();
> }
> {code}
> {noformat}
> Caused by: java.sql.SQLSyntaxErrorException: unexpected token: = : line: 3 in
> statement [SELECT *
> FROM "foodmart"."sales_fact_1997"
> WHERE "product_id" = 1 = ?
> {noformat}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6453) Simplify casts which are result of constant reduction
[
https://issues.apache.org/jira/browse/CALCITE-6453?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17862626#comment-17862626
]
xiong duan commented on CALCITE-6453:
-
Hi [~kkasa], I want to know do we have a SQL test for this? Maybe SQL can make
this issue more clearly.
> Simplify casts which are result of constant reduction
> -
>
> Key: CALCITE-6453
> URL: https://issues.apache.org/jira/browse/CALCITE-6453
> Project: Calcite
> Issue Type: Improvement
>Reporter: Krisztian Kasa
>Assignee: Krisztian Kasa
>Priority: Major
> Labels: pull-request-available
>
> RexSimplify transforms nullable cast expressions with a non-nullable literal
> operand which type is different from the cast type to another cast expression
> with a literal operand with the same type as the cast.
> If simplify is called again using the result cast expression of the first
> simplification we get a literal only as a result.
> Example:
> Initial expression
> {code:java}
> CAST(_UTF-16LE'2020-10-30':VARCHAR(2147483647) CHARACTER SET "UTF-16LE"):DATE
> {code}
> where the operand is non-nullable varchar and the result of the cast is
> nullable date.
> After the first simplify call we get
> {code:java}
> CAST(2020-10-30:DATE):DATE
> {code}
> where the operand type is non-nullable date and the cast type is nullable
> date.
> https://github.com/apache/calcite/blob/8ab0b03326730aa2cc6b476b2cbd8f99799bdacb/core/src/main/java/org/apache/calcite/rex/RexSimplify.java#L2269-L2276
> After the second simplify call we get
> {code:java}
> 2020-10-30:DATE
> {code}
> where the literal type is non-nullable date
> https://github.com/apache/calcite/blob/8ab0b03326730aa2cc6b476b2cbd8f99799bdacb/core/src/main/java/org/apache/calcite/rex/RexSimplify.java#L2195-L2196
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-6436) JDBC adapter generates SQL missing parentheses when comparing 3 values with the same precedence like (a=b)=c
[
https://issues.apache.org/jira/browse/CALCITE-6436?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
xiong duan updated CALCITE-6436:
Summary: JDBC adapter generates SQL missing parentheses when comparing 3
values with the same precedence like (a=b)=c (was: JDBC adapter generates SQL
with missing round brackets when WHERE clause contains parameter)
> JDBC adapter generates SQL missing parentheses when comparing 3 values with
> the same precedence like (a=b)=c
>
>
> Key: CALCITE-6436
> URL: https://issues.apache.org/jira/browse/CALCITE-6436
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.37.0
>Reporter: Ulrich Kramer
>Assignee: xiong duan
>Priority: Major
>
> Running the following unit test in {{JdbcAdapterTest}} fails with
> {code:java}
> @Test void testOperatorInWhere() {
> CalciteAssert.model(FoodmartSchema.FOODMART_MODEL)
> .query("select * from \"sales_fact_1997\" "
> + "where (\"product_id\" = 1) = ?")
> .consumesPreparedStatement(p -> p.setBoolean(1, true))
> .runs();
> }
> {code}
> {noformat}
> Caused by: java.sql.SQLSyntaxErrorException: unexpected token: = : line: 3 in
> statement [SELECT *
> FROM "foodmart"."sales_fact_1997"
> WHERE "product_id" = 1 = ?
> {noformat}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Assigned] (CALCITE-6436) JDBC adapter generates SQL with missing round brackets when WHERE clause contains parameter
[
https://issues.apache.org/jira/browse/CALCITE-6436?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
xiong duan reassigned CALCITE-6436:
---
Assignee: xiong duan
> JDBC adapter generates SQL with missing round brackets when WHERE clause
> contains parameter
> ---
>
> Key: CALCITE-6436
> URL: https://issues.apache.org/jira/browse/CALCITE-6436
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.37.0
>Reporter: Ulrich Kramer
>Assignee: xiong duan
>Priority: Major
>
> Running the following unit test in {{JdbcAdapterTest}} fails with
> {code:java}
> @Test void testOperatorInWhere() {
> CalciteAssert.model(FoodmartSchema.FOODMART_MODEL)
> .query("select * from \"sales_fact_1997\" "
> + "where (\"product_id\" = 1) = ?")
> .consumesPreparedStatement(p -> p.setBoolean(1, true))
> .runs();
> }
> {code}
> {noformat}
> Caused by: java.sql.SQLSyntaxErrorException: unexpected token: = : line: 3 in
> statement [SELECT *
> FROM "foodmart"."sales_fact_1997"
> WHERE "product_id" = 1 = ?
> {noformat}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6436) JDBC adapter generates SQL with missing round brackets when WHERE clause contains parameter
[
https://issues.apache.org/jira/browse/CALCITE-6436?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17861289#comment-17861289
]
xiong duan commented on CALCITE-6436:
-
[~kramerul]Hi, If you don't mind, I will take over this issue.
> JDBC adapter generates SQL with missing round brackets when WHERE clause
> contains parameter
> ---
>
> Key: CALCITE-6436
> URL: https://issues.apache.org/jira/browse/CALCITE-6436
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.37.0
>Reporter: Ulrich Kramer
>Priority: Major
>
> Running the following unit test in {{JdbcAdapterTest}} fails with
> {code:java}
> @Test void testOperatorInWhere() {
> CalciteAssert.model(FoodmartSchema.FOODMART_MODEL)
> .query("select * from \"sales_fact_1997\" "
> + "where (\"product_id\" = 1) = ?")
> .consumesPreparedStatement(p -> p.setBoolean(1, true))
> .runs();
> }
> {code}
> {noformat}
> Caused by: java.sql.SQLSyntaxErrorException: unexpected token: = : line: 3 in
> statement [SELECT *
> FROM "foodmart"."sales_fact_1997"
> WHERE "product_id" = 1 = ?
> {noformat}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6450) Postgres CONCAT_WS function throws exception when parameter type is (, )
[
https://issues.apache.org/jira/browse/CALCITE-6450?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17861086#comment-17861086
]
xiong duan commented on CALCITE-6450:
-
[~jhonye] Hi, Yes. In Calcite this func only support String parameter, But In
PostgreSQL, It can support ARRAY. So if we keep consistent with PG. We should
support it. That's why the issue.
> Postgres CONCAT_WS function throws exception when parameter type is
> (, )
>
>
> Key: CALCITE-6450
> URL: https://issues.apache.org/jira/browse/CALCITE-6450
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.37.0
>Reporter: xiong duan
>Priority: Major
> Attachments: image-2024-06-29-14-57-56-513.png
>
>
> The SQL can run success in Postgres:
> {code:java}
> select concat_ws(',',ARRAY[1, 1, 1, 1]); {code}
> But in Calcite, It will throw exception:
> {code:java}
> @Test void testConcatFunction() {
> final String sql = "select concat_ws(',',ARRAY[1, 1, 1, 1])
> as c";
> fixture()
> .withFactory(c ->
> c.withOperatorTable(t ->
> SqlValidatorTest.operatorTableFor(SqlLibrary.POSTGRESQL)))
> .withCatalogReader(MockCatalogReaderExtended::create)
> .withSql(sql)
> .ok();
> }{code}
> {code:java}
> From line 1, column 8 to line 1, column 55: Cannot apply 'CONCAT_WS' to
> arguments of type 'CONCAT_WS(, )'. Supported form(s):
> 'CONCAT_WS()'{code}
> This issue find in comment
> [CALCITE-6446|https://github.com/apache/calcite/pull/3831] .
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6325) Add LOG function (enabled in Mysql and Spark library)
[ https://issues.apache.org/jira/browse/CALCITE-6325?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17861085#comment-17861085 ] xiong duan commented on CALCITE-6325: - Fixed in [99799bdacb|https://github.com/apache/calcite/commit/8ab0b03326730aa2cc6b476b2cbd8f]. Thanks for contribution [~caicancai]. Thanks for review [~njordan]. > Add LOG function (enabled in Mysql and Spark library) > - > > Key: CALCITE-6325 > URL: https://issues.apache.org/jira/browse/CALCITE-6325 > Project: Calcite > Issue Type: Sub-task > Components: core >Affects Versions: 1.36.0 >Reporter: Caican Cai >Assignee: Caican Cai >Priority: Minor > Labels: pull-request-available > Fix For: 1.38.0 > > > Mysql and spark's log(0, 0), log(0) will return null, bigquery and postgres > will return an error -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Resolved] (CALCITE-6325) Add LOG function (enabled in Mysql and Spark library)
[ https://issues.apache.org/jira/browse/CALCITE-6325?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] xiong duan resolved CALCITE-6325. - Resolution: Fixed > Add LOG function (enabled in Mysql and Spark library) > - > > Key: CALCITE-6325 > URL: https://issues.apache.org/jira/browse/CALCITE-6325 > Project: Calcite > Issue Type: Sub-task > Components: core >Affects Versions: 1.36.0 >Reporter: Caican Cai >Assignee: Caican Cai >Priority: Minor > Labels: pull-request-available > Fix For: 1.38.0 > > > Mysql and spark's log(0, 0), log(0) will return null, bigquery and postgres > will return an error -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-5634) Enable GREATEST, LEAST functions in PostgreSQL library
[
https://issues.apache.org/jira/browse/CALCITE-5634?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17861082#comment-17861082
]
xiong duan commented on CALCITE-5634:
-
The PostgreSQL can support:
{code:java}
SELECT greatest(ARRAY [1, 1], ARRAY [500, 500], ARRAY [5000, 500],
ARRAY [1, 2],ARRAY [8000, 2, 1000] ) AS x;
SELECT least(ARRAY [1, 1], ARRAY [500, 500], ARRAY [5000, 500], ARRAY
[1, 2]) AS x;{code}
[~njordan] We plan to implement this in this PR ？ If don't maybe we can create
another issue for this.
> Enable GREATEST, LEAST functions in PostgreSQL library
> --
>
> Key: CALCITE-5634
> URL: https://issues.apache.org/jira/browse/CALCITE-5634
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.34.0
>Reporter: Dmitry Sysolyatin
>Assignee: Norman Jordan
>Priority: Major
> Labels: pull-request-available
> Time Spent: 1h
> Remaining Estimate: 0h
>
> The LEAST and GREATEST functions have been implemented for BigQuery and
> Oracle, but haven't been added for PostgreSQL. PostgreSQL supports LEAST,
> GREATEST as well [1].
> Also PostgreSQL's LEAST, GREATEST functions behave differently with NULL
> values than ORACLE or BigQuery.
> From PostgreSQL documentation [1]:
> ??The result will be NULL only if all the expressions evaluate to NULL.??
> From BigQuery documentation [2]:
> ??They return NULL if any of the input parameters is NULL.??
> [1]
> [https://www.postgresql.org/docs/15/functions-conditional.html#FUNCTIONS-GREATEST-LEAST]
> [2]
> [https://cloud.google.com/bigquery/docs/reference/standard-sql/mathematical_functions]
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6436) JDBC adapter generates SQL with missing round brackets when WHERE clause contains parameter
[
https://issues.apache.org/jira/browse/CALCITE-6436?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17860915#comment-17860915
]
xiong duan commented on CALCITE-6436:
-
[~julianhyde] [~kramerul] I did some tests in Calcite, This is an issue with
the JDBC adapter:
1: This is because different ways to handle a = b = c, regardless of whether
there are parameters or not.
2: Different databases have different support for a = b = c:
Oracle、PostgreSQL、SqlServer、HSQLDB only support:
{code:java}
SELECT * FROM Product WHERE (ProductID = 1) = (ProductID = 2);{code}
MySQL、SparkSQL、SqlLite support :
{code:java}
SELECT * FROM Product WHERE ProductID = 1 = (ProductID = 2);{code}
Now Calcite generates the SQL as:
{code:java}
SELECT * FROM Product WHERE ProductID = 1 = (ProductID = 2);{code}
> JDBC adapter generates SQL with missing round brackets when WHERE clause
> contains parameter
> ---
>
> Key: CALCITE-6436
> URL: https://issues.apache.org/jira/browse/CALCITE-6436
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.37.0
>Reporter: Ulrich Kramer
>Priority: Major
>
> Running the following unit test in {{JdbcAdapterTest}} fails with
> {code:java}
> @Test void testOperatorInWhere() {
> CalciteAssert.model(FoodmartSchema.FOODMART_MODEL)
> .query("select * from \"sales_fact_1997\" "
> + "where (\"product_id\" = 1) = ?")
> .consumesPreparedStatement(p -> p.setBoolean(1, true))
> .runs();
> }
> {code}
> {noformat}
> Caused by: java.sql.SQLSyntaxErrorException: unexpected token: = : line: 3 in
> statement [SELECT *
> FROM "foodmart"."sales_fact_1997"
> WHERE "product_id" = 1 = ?
> {noformat}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6435) SqlToRel conversion of IN expressions may lead to incorrect simplifications
[
https://issues.apache.org/jira/browse/CALCITE-6435?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17860913#comment-17860913
]
xiong duan commented on CALCITE-6435:
-
CALCITE-5156 solved the INT type problem, It Looks like this is a CAHR and
CHARACTER type problem. So after the CALCITE-5156 merge, this PR will find an
easy fix. If you have free time Please review CALCITE-5156.
> SqlToRel conversion of IN expressions may lead to incorrect simplifications
> ---
>
> Key: CALCITE-6435
> URL: https://issues.apache.org/jira/browse/CALCITE-6435
> Project: Calcite
> Issue Type: Bug
>Reporter: Zoltan Haindrich
>Assignee: Zoltan Haindrich
>Priority: Major
>
> the query must have the following features:
> * not all columns are selected
> ** to enable {{RelFieldTrimmer}} to start a cycle
> * two equivalent eq filters
> ** one in {{IN}} form ({{ename in ( 'Sebastian' )}})
> ** a regular {{=}} ({{ename = 'Sebastian'}})
> * an unrelated filter like {{deptno < 100}}
> the optimizer should more-or-less start with the `RelFieldTrimmer`
> the issue happens like:
> * at parse time both literals are parsed as {{CHAR( n )}}
> * the number of values in the `IN` is below `inSubqueryThreshold` - so it
> gets converted to a set of `=` filters
> ** expression is converted to OR form
> ** during conversion
> [SqlToRelConverter#ensureSqlType|https://github.com/apache/calcite/blob/fb15511e76c660cbd440578421645ebe63941bf7/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java#L1777-L1779]
> is called
> *** which skips the conversion for *CHAR / VARCHAR*
> * the *=* filter goes thru the "regular" rex conversion - which involves
> calling *rexBuilder#ensureType*
> * the filter condition contains *ename = 'Sebastian'* twice; however the
> types differ
> * *RelFieldTrimmer* starts a change cycle ; which induces the simplification
> of the filter condition
> * *RexSimplify* is executed with predicate elimination disabled (this will
> be important)
> * simplification compares the two literals with
> [equals|https://github.com/apache/calcite/blob/fb15511e76c660cbd440578421645ebe63941bf7/core/src/main/java/org/apache/calcite/rex/RexSimplify.java#L1685]
> and returns `false`
> workarounds:
> * disable the conversion by setting *inSubqueryThreshold=1*
> * run a rule which executes `RexSimplify` with predicate elimination enabled
> earlier than the trimmer (ex: *ReduceExpressionsRule*)
> ** I think this bug remained hidden because this might happen easily
> testcase for `RelOptRulesTest`
> {code:java}
> @Test void testIncorrectInType() {
> final String sql = "select ename from emp "
> + " where ename in ( 'Sebastian' ) and ename = 'Sebastian' and
> deptno < 100";
> sql(sql)
> .withTrim(true)
> .withRule()
> .checkUnchanged();
> }
> {code}
> results in plan
> {code:java}
> LogicalProject(ENAME=[$0])
> LogicalValues(tuples=[[]])
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5156) Support implicit number type cast for IN Sub-query
[
https://issues.apache.org/jira/browse/CALCITE-5156?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17860912#comment-17860912
]
xiong duan commented on CALCITE-5156:
-
[~julianhyde] [~mbudiu] Hi, I noticed another relative issue CALCITE-6435
submitted a PR. If we merge this, I think PR may have a simple fix. So If you
have free time, Please review this PR. Thanks.
> Support implicit number type cast for IN Sub-query
> --
>
> Key: CALCITE-5156
> URL: https://issues.apache.org/jira/browse/CALCITE-5156
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.30.0
>Reporter: xiong duan
>Assignee: xiong duan
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.38.0
>
> Time Spent: 0.5h
> Remaining Estimate: 0h
>
> The SQL:
> {code:java}
> select * from dept where deptno + 20 in (select deptno from dept);{code}
> Calcite returns the wrong answer.
> but the SQL
>
> {code:java}
> select * from dept where deptno + 20 in (select cast(deptno as integer) from
> dept);{code}
> Calcite returns the correct answer.
> So when we generate the RelNode, we can add the type cast.
> Before the type cast:
> {noformat}
> LogicalProject(DEPTNO=[$0], DNAME=[$1], LOC=[$2])
> LogicalFilter(condition=[IN(+($0, 20), {
> LogicalProject(DEPTNO=[$0])
> LogicalTableScan(table=[[scott, DEPT]])
> })])
> LogicalTableScan(table=[[scott, DEPT]]){noformat}
> After the type cast:
> {noformat}
> LogicalProject(DEPTNO=[$0], DNAME=[$1], LOC=[$2])
> LogicalFilter(condition=[IN(+($0, 20), {
> LogicalProject(EXPR$0=[CAST($0):INTEGER NOT NULL])
> LogicalTableScan(table=[[scott, DEPT]])
> })])
> LogicalTableScan(table=[[scott, DEPT]]){noformat}
> Same SQL includes:
> {code:java}
> select *
> from dept
> where deptno in (select sal-780 from emp){code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-6450) Postgres CONCAT_WS function throws exception when parameter type is (, )
[
https://issues.apache.org/jira/browse/CALCITE-6450?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
xiong duan updated CALCITE-6450:
Description:
The SQL can run success in Postgres:
{code:java}
select concat_ws(',',ARRAY[1, 1, 1, 1]); {code}
But in Calcite, It will throw exception:
{code:java}
@Test void testConcatFunction() {
final String sql = "select concat_ws(',',ARRAY[1, 1, 1, 1]) as
c";
fixture()
.withFactory(c ->
c.withOperatorTable(t ->
SqlValidatorTest.operatorTableFor(SqlLibrary.POSTGRESQL)))
.withCatalogReader(MockCatalogReaderExtended::create)
.withSql(sql)
.ok();
}{code}
{code:java}
>From line 1, column 8 to line 1, column 55: Cannot apply 'CONCAT_WS' to
>arguments of type 'CONCAT_WS(, )'. Supported form(s):
>'CONCAT_WS()'{code}
This issue find in comment
[CALCITE-6446|https://github.com/apache/calcite/pull/3831] .
was:
The SQL can run success in Postgres:
{code:java}
select concat_ws(',',ARRAY[1, 1, 1, 1]); {code}
But in Calcite, It will throw exception:
{code:java}
@Test void testConcatFunction() {
final String sql = "select concat_ws(',',ARRAY[1, 1, 1, 1]) as
c";
fixture()
.withFactory(c ->
c.withOperatorTable(t ->
SqlValidatorTest.operatorTableFor(SqlLibrary.POSTGRESQL)))
.withCatalogReader(MockCatalogReaderExtended::create)
.withSql(sql)
.ok();
}{code}
{code:java}
>From line 1, column 8 to line 1, column 55: Cannot apply 'CONCAT_WS' to
>arguments of type 'CONCAT_WS(, )'. Supported form(s):
>'CONCAT_WS()'{code}
> Postgres CONCAT_WS function throws exception when parameter type is
> (, )
>
>
> Key: CALCITE-6450
> URL: https://issues.apache.org/jira/browse/CALCITE-6450
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.37.0
>Reporter: xiong duan
>Priority: Major
>
> The SQL can run success in Postgres:
> {code:java}
> select concat_ws(',',ARRAY[1, 1, 1, 1]); {code}
> But in Calcite, It will throw exception:
> {code:java}
> @Test void testConcatFunction() {
> final String sql = "select concat_ws(',',ARRAY[1, 1, 1, 1])
> as c";
> fixture()
> .withFactory(c ->
> c.withOperatorTable(t ->
> SqlValidatorTest.operatorTableFor(SqlLibrary.POSTGRESQL)))
> .withCatalogReader(MockCatalogReaderExtended::create)
> .withSql(sql)
> .ok();
> }{code}
> {code:java}
> From line 1, column 8 to line 1, column 55: Cannot apply 'CONCAT_WS' to
> arguments of type 'CONCAT_WS(, )'. Supported form(s):
> 'CONCAT_WS()'{code}
> This issue find in comment
> [CALCITE-6446|https://github.com/apache/calcite/pull/3831] .
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-6450) Postgres CONCAT_WS function throws exception when parameter type is (, )
xiong duan created CALCITE-6450:
---
Summary: Postgres CONCAT_WS function throws exception when
parameter type is (, )
Key: CALCITE-6450
URL: https://issues.apache.org/jira/browse/CALCITE-6450
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.37.0
Reporter: xiong duan
The SQL can run success in Postgres:
{code:java}
select concat_ws(',',ARRAY[1, 1, 1, 1]); {code}
But in Calcite, It will throw exception:
{code:java}
@Test void testConcatFunction() {
final String sql = "select concat_ws(',',ARRAY[1, 1, 1, 1]) as
c";
fixture()
.withFactory(c ->
c.withOperatorTable(t ->
SqlValidatorTest.operatorTableFor(SqlLibrary.POSTGRESQL)))
.withCatalogReader(MockCatalogReaderExtended::create)
.withSql(sql)
.ok();
}{code}
{code:java}
>From line 1, column 8 to line 1, column 55: Cannot apply 'CONCAT_WS' to
>arguments of type 'CONCAT_WS(, )'. Supported form(s):
>'CONCAT_WS()'{code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Reopened] (CALCITE-6440) Add optimization to returns empty when The Order By parameter in the Sort operator is empty
[
https://issues.apache.org/jira/browse/CALCITE-6440?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
xiong duan reopened CALCITE-6440:
-
Looks the SortRemoveConstantKeysRule can't remove the sort key when the sort
key is NULL.
> Add optimization to returns empty when The Order By parameter in the Sort
> operator is empty
> ---
>
> Key: CALCITE-6440
> URL: https://issues.apache.org/jira/browse/CALCITE-6440
> Project: Calcite
> Issue Type: New Feature
> Components: core
>Affects Versions: 1.37.0
>Reporter: Caican Cai
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.38.0
>
>
> For the following SQL:
> {code:java}
> select * from task order by null; {code}
> We could rewrite it to:
> {code:java}
> select * from task;{code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6440) Add optimization to returns empty when The Order By parameter in the Sort operator is empty
[
https://issues.apache.org/jira/browse/CALCITE-6440?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17856071#comment-17856071
]
xiong duan commented on CALCITE-6440:
-
+1 reopen this Jira case.
> Add optimization to returns empty when The Order By parameter in the Sort
> operator is empty
> ---
>
> Key: CALCITE-6440
> URL: https://issues.apache.org/jira/browse/CALCITE-6440
> Project: Calcite
> Issue Type: New Feature
> Components: core
>Affects Versions: 1.37.0
>Reporter: Caican Cai
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.38.0
>
>
> For the following SQL:
> {code:java}
> select * from task order by null; {code}
> We could rewrite it to:
> {code:java}
> select * from task;{code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6431) Implement the SINGLE_VALUE aggregation in HiveSqlDialect And SparkSQLDialect
[
https://issues.apache.org/jira/browse/CALCITE-6431?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17855910#comment-17855910
]
xiong duan edited comment on CALCITE-6431 at 6/18/24 12:07 PM:
---
Maybe Spark or Hive can optimize CASE WHEN:
{code:java}
CASE COUNT WHEN 0 THEN NULL WHEN 1 THEN MIN(`product_class_id`) ELSE (SELECT
NULL UNION ALL SELECT NULL){code}
don't evaluate the subquery value first.
was (Author: nobigo):
Maybe Spark or Hive can optimize CASE WHEN:
CASE COUNT(*) WHEN 0 THEN NULL WHEN 1 THEN MIN(`product_class_id`) ELSE (SELECT
NULL UNION ALL SELECT NULL)
don't evaluate the subquery value first.
> Implement the SINGLE_VALUE aggregation in HiveSqlDialect And SparkSQLDialect
>
>
> Key: CALCITE-6431
> URL: https://issues.apache.org/jira/browse/CALCITE-6431
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.37.0
>Reporter: xiong duan
>Assignee: xiong duan
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.38.0
>
>
> When we use the SQL including the scalar query, converting this SQL Rel to
> HiveSQL will include the SINGLE_VALUE aggregation function(HiveSQL can't
> handle it) in the dialect SQL.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6431) Implement the SINGLE_VALUE aggregation in HiveSqlDialect And SparkSQLDialect
[ https://issues.apache.org/jira/browse/CALCITE-6431?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17855910#comment-17855910 ] xiong duan commented on CALCITE-6431: - Maybe Spark or Hive can optimize CASE WHEN: CASE COUNT(*) WHEN 0 THEN NULL WHEN 1 THEN MIN(`product_class_id`) ELSE (SELECT NULL UNION ALL SELECT NULL) don't evaluate the subquery value first. > Implement the SINGLE_VALUE aggregation in HiveSqlDialect And SparkSQLDialect > > > Key: CALCITE-6431 > URL: https://issues.apache.org/jira/browse/CALCITE-6431 > Project: Calcite > Issue Type: Improvement > Components: core >Affects Versions: 1.37.0 >Reporter: xiong duan >Assignee: xiong duan >Priority: Major > Labels: pull-request-available > Fix For: 1.38.0 > > > When we use the SQL including the scalar query, converting this SQL Rel to > HiveSQL will include the SINGLE_VALUE aggregation function(HiveSQL can't > handle it) in the dialect SQL. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6431) Implement the SINGLE_VALUE aggregation in HiveSqlDialect And SparkSQLDialect
[
https://issues.apache.org/jira/browse/CALCITE-6431?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17855907#comment-17855907
]
xiong duan commented on CALCITE-6431:
-
Spark and Hive will throw exception 'Scalar subquery expression returns more
than one row' :
{code:java}
SELECT CASE COUNT(*) WHEN 0 THEN NULL WHEN 1 THEN MIN(`product_class_id`) ELSE
(SELECT NULL UNION ALL SELECT NULL) END `$f0` FROM `foodmart`.`product`{code}
It doesn't matter how much this expression(count(*)) returns, it will not
affect the result.
> Implement the SINGLE_VALUE aggregation in HiveSqlDialect And SparkSQLDialect
>
>
> Key: CALCITE-6431
> URL: https://issues.apache.org/jira/browse/CALCITE-6431
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.37.0
>Reporter: xiong duan
>Assignee: xiong duan
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.38.0
>
>
> When we use the SQL including the scalar query, converting this SQL Rel to
> HiveSQL will include the SINGLE_VALUE aggregation function(HiveSQL can't
> handle it) in the dialect SQL.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6436) Missing round brackets in WHERE condition which contains calculations
[
https://issues.apache.org/jira/browse/CALCITE-6436?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17854903#comment-17854903
]
xiong duan commented on CALCITE-6436:
-
Make sense. How about we change the summary to 'JDBC adapter generates wrong
SQL when WHERE clause include bindable parameter' to make this issue more
clearly.
> Missing round brackets in WHERE condition which contains calculations
> -
>
> Key: CALCITE-6436
> URL: https://issues.apache.org/jira/browse/CALCITE-6436
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.37.0
>Reporter: Ulrich Kramer
>Priority: Major
>
> Running the following unit test in {{JdbcAdapterTest}} fails with
> {code:java}
> @Test void testOperatorInWhere() {
> CalciteAssert.model(FoodmartSchema.FOODMART_MODEL)
> .query("select * from \"sales_fact_1997\" "
> + "where (\"product_id\" = 1) = ?")
> .consumesPreparedStatement(p -> p.setBoolean(1, true))
> .runs();
> }
> {code}
> {noformat}
> Caused by: java.sql.SQLSyntaxErrorException: unexpected token: = : line: 3 in
> statement [SELECT *
> FROM "foodmart"."sales_fact_1997"
> WHERE "product_id" = 1 = ?
> {noformat}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6437) For druid sql JSON_OBJECT() function results in RUNTIME_FAILURE when querying INFORMATION_SCHEMA.COLUMNS
[
https://issues.apache.org/jira/browse/CALCITE-6437?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17854898#comment-17854898
]
xiong duan commented on CALCITE-6437:
-
Hi [~albericbyte] , I check this in the Calcite. It can all works well:
{code:java}
select JSON_OBJECT('name': dname, 'type': deptno) from dept;
select JSON_OBJECT('name': 'foo', 'type': 'VARCHAR');{code}
Can you try to reproduce this bug in the Calcite and Please add unit test for
this. We can make this issue more clearly.
> For druid sql JSON_OBJECT() function results in RUNTIME_FAILURE when querying
> INFORMATION_SCHEMA.COLUMNS
>
>
> Key: CALCITE-6437
> URL: https://issues.apache.org/jira/browse/CALCITE-6437
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.35.0
>Reporter: AlbericByte
>Priority: Critical
> Labels: pull-request-available
>
> there is but for druid sql :
> [https://github.com/apache/druid/issues/16356]
> h3. Description
> Running the following query
> {code:java}
> select JSON_OBJECT('name': COLUMN_NAME, 'type': DATA_TYPE) from
> INFORMATION_SCHEMA.COLUMNS{code}
> {{ }}
>
> produces
> {code:java}
> Error: RUNTIME_FAILURE (OPERATOR) cannot translate call json_object($t17,
> $t18, $t3, $t19, $t7) java.lang.RuntimeException {code}
>
> Note that the error message quotes the json_object call to receive five
> parameters, but maybe that's an unrelated internal detail.
> For comparison, the following works (but is useless):
> {code:java}
> select JSON_OBJECT('name': 'foo', 'type': 'VARCHAR'){code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6434) JDBC adapter generates wrong SQL for Spark and Hive because it fails to quote an identifier containing '$'
[
https://issues.apache.org/jira/browse/CALCITE-6434?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17854583#comment-17854583
]
xiong duan commented on CALCITE-6434:
-
Fixed in
[05923bf|https://github.com/apache/calcite/commit/05923bf4dbe1900b3b04939a8d254acad2faeff4].
> JDBC adapter generates wrong SQL for Spark and Hive because it fails to quote
> an identifier containing '$'
> --
>
> Key: CALCITE-6434
> URL: https://issues.apache.org/jira/browse/CALCITE-6434
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.37.0
>Reporter: xiong duan
>Assignee: xiong duan
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.38.0
>
>
> The SQL:
> {code:java}
> SELECT product.product_class_id C
> FROM foodmart.product
> LEFT JOIN (SELECT CASE COUNT(*) WHEN 0 THEN NULL WHEN 1 THEN
> MIN(product_class_id) ELSE (SELECT NULL
> UNION ALL
> SELECT NULL) END $f0
> FROM foodmart.product) t0 ON TRUE
> WHERE product.net_weight > t0.$f0{code}
> Generate by SINGLE_VALUE agg function.
> This SQL will parse failed in Spark Unless we add the identifier quoting like
> `t0`.`$f0`
> I will add identifier Quote String for Spark and Hive, Here is the document:
> * [https://spark.apache.org/docs/latest/sql-ref-identifier.html]
> *
> [https://cwiki.apache.org/confluence/display/Hive/Configuration+Properties#ConfigurationProperties-hive.support.quoted.identifiers]
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Resolved] (CALCITE-6434) JDBC adapter generates wrong SQL for Spark and Hive because it fails to quote an identifier containing '$'
[
https://issues.apache.org/jira/browse/CALCITE-6434?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
xiong duan resolved CALCITE-6434.
-
Resolution: Fixed
> JDBC adapter generates wrong SQL for Spark and Hive because it fails to quote
> an identifier containing '$'
> --
>
> Key: CALCITE-6434
> URL: https://issues.apache.org/jira/browse/CALCITE-6434
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.37.0
>Reporter: xiong duan
>Assignee: xiong duan
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.38.0
>
>
> The SQL:
> {code:java}
> SELECT product.product_class_id C
> FROM foodmart.product
> LEFT JOIN (SELECT CASE COUNT(*) WHEN 0 THEN NULL WHEN 1 THEN
> MIN(product_class_id) ELSE (SELECT NULL
> UNION ALL
> SELECT NULL) END $f0
> FROM foodmart.product) t0 ON TRUE
> WHERE product.net_weight > t0.$f0{code}
> Generate by SINGLE_VALUE agg function.
> This SQL will parse failed in Spark Unless we add the identifier quoting like
> `t0`.`$f0`
> I will add identifier Quote String for Spark and Hive, Here is the document:
> * [https://spark.apache.org/docs/latest/sql-ref-identifier.html]
> *
> [https://cwiki.apache.org/confluence/display/Hive/Configuration+Properties#ConfigurationProperties-hive.support.quoted.identifiers]
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Resolved] (CALCITE-6426) Invalid unparse for INT and BIGINT in StarRocksDialect
[ https://issues.apache.org/jira/browse/CALCITE-6426?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] xiong duan resolved CALCITE-6426. - Fix Version/s: 1.38.0 Resolution: Fixed > Invalid unparse for INT and BIGINT in StarRocksDialect > -- > > Key: CALCITE-6426 > URL: https://issues.apache.org/jira/browse/CALCITE-6426 > Project: Calcite > Issue Type: Bug >Affects Versions: 1.37.0 >Reporter: fanluo >Priority: Major > Labels: pull-request-available > Fix For: 1.38.0 > > Attachments: image-2024-06-03-19-57-22-686.png > > > When performing starrocks dialect conversion for cast (xxx as int) and cast > (xxx as bigint), it defaults to using MySQL dialect，like this: > !image-2024-06-03-19-57-22-686.png|width=839,height=123! > while starlocks does not explicitly declare support for the signed data type. > If conversion is performed according to the MySQL dialect by default, the > execution result will exceed expectations when the data value exceeds the > range [-2147483648,2147483647] -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6426) Invalid unparse for INT and BIGINT in StarRocksDialect
[ https://issues.apache.org/jira/browse/CALCITE-6426?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17854558#comment-17854558 ] xiong duan commented on CALCITE-6426: - Fixed in [db1e7b4c|http://example.com/]. Thanks for your contribution [~fanluoo] and Thanks for the review [~mbudiu] [~caicancai] and [~eveywu] . > Invalid unparse for INT and BIGINT in StarRocksDialect > -- > > Key: CALCITE-6426 > URL: https://issues.apache.org/jira/browse/CALCITE-6426 > Project: Calcite > Issue Type: Bug >Affects Versions: 1.37.0 >Reporter: fanluo >Priority: Major > Labels: pull-request-available > Attachments: image-2024-06-03-19-57-22-686.png > > > When performing starrocks dialect conversion for cast (xxx as int) and cast > (xxx as bigint), it defaults to using MySQL dialect，like this: > !image-2024-06-03-19-57-22-686.png|width=839,height=123! > while starlocks does not explicitly declare support for the signed data type. > If conversion is performed according to the MySQL dialect by default, the > execution result will exceed expectations when the data value exceeds the > range [-2147483648,2147483647] -- This message was sent by Atlassian Jira (v8.20.10#820010)
