[jira] [Commented] (CALCITE-6362) JSON_OBJECT. Internal representation of DATE values leaks into resulting JSON object
[
https://issues.apache.org/jira/browse/CALCITE-6362?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17836420#comment-17836420
]
Alessandro Solimando commented on CALCITE-6362:
---
[~mzhuravkov] if you consider the behavior acceptable (days without cast and a
date with the cast), please consider resolving the ticket accordingly.
> JSON_OBJECT. Internal representation of DATE values leaks into resulting JSON
> object
>
>
> Key: CALCITE-6362
> URL: https://issues.apache.org/jira/browse/CALCITE-6362
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Maksim Zhuravkov
>Priority: Minor
>
> When a CAST function is used inside a JSON_OBJECT, then resulting JSON
> contains a number of days instead
> {code}
> SELECT JSON_OBJECT('a': CAST('2010-01-01' AS DATE)) as c1
> {"a":14610}
> {code}
> I expected to get a date in a string form as many databases do in such case:
> Bigquery:
> {code}
> SELECT JSON_OBJECT('a', CAST('2010-01-10' AS DATE))
> {"a":"2010-01-10"}
> {code}
> PostgreSQL
> {code}
> SELECT json_build_object('a', '2010-01-01'::DATE)
> {"a":"2010-01-01"}
> {code}
> MySql
> {code}
> SELECT json_object('a', DATE '2010-01-01')
> {"a": "2010-01-01"}
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6362) JSON_OBJECT. Internal representation of DATE values leaks into resulting JSON object
[
https://issues.apache.org/jira/browse/CALCITE-6362?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17836413#comment-17836413
]
Maksim Zhuravkov commented on CALCITE-6362:
---
[~mbudiu], thank you. The expression with an explicit cast produces what I
would expect:
{"a": "2010-01-01"}
> JSON_OBJECT. Internal representation of DATE values leaks into resulting JSON
> object
>
>
> Key: CALCITE-6362
> URL: https://issues.apache.org/jira/browse/CALCITE-6362
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Maksim Zhuravkov
>Priority: Minor
>
> When a CAST function is used inside a JSON_OBJECT, then resulting JSON
> contains a number of days instead
> {code}
> SELECT JSON_OBJECT('a': CAST('2010-01-01' AS DATE)) as c1
> {"a":14610}
> {code}
> I expected to get a date in a string form as many databases do in such case:
> Bigquery:
> {code}
> SELECT JSON_OBJECT('a', CAST('2010-01-10' AS DATE))
> {"a":"2010-01-10"}
> {code}
> PostgreSQL
> {code}
> SELECT json_build_object('a', '2010-01-01'::DATE)
> {"a":"2010-01-01"}
> {code}
> MySql
> {code}
> SELECT json_object('a', DATE '2010-01-01')
> {"a": "2010-01-01"}
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-6362) JSON_OBJECT. Internal representation of DATE values leaks into resulting JSON object
[
https://issues.apache.org/jira/browse/CALCITE-6362?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Maksim Zhuravkov updated CALCITE-6362:
--
Summary: JSON_OBJECT. Internal representation of DATE values leaks into
resulting JSON object (was: JSON_OBJECT. Internal representation DATE values
leaks into resulting JSON object)
> JSON_OBJECT. Internal representation of DATE values leaks into resulting JSON
> object
>
>
> Key: CALCITE-6362
> URL: https://issues.apache.org/jira/browse/CALCITE-6362
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Maksim Zhuravkov
>Priority: Minor
>
> When a CAST function is used inside a JSON_OBJECT, then resulting JSON
> contains a number of days instead
> {code}
> SELECT JSON_OBJECT('a': CAST('2010-01-01' AS DATE)) as c1
> {"a":14610}
> {code}
> I expected to get a date in a string form as many databases do in such case:
> Bigquery:
> {code}
> SELECT JSON_OBJECT('a', CAST('2010-01-10' AS DATE))
> {"a":"2010-01-10"}
> {code}
> PostgreSQL
> {code}
> SELECT json_build_object('a', '2010-01-01'::DATE)
> {"a":"2010-01-01"}
> {code}
> MySql
> {code}
> SELECT json_object('a', DATE '2010-01-01')
> {"a": "2010-01-01"}
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5743) Query gives incorrect result when COUNT appears in the correlated subquery select list
[
https://issues.apache.org/jira/browse/CALCITE-5743?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17836376#comment-17836376
]
James Starr commented on CALCITE-5743:
--
[~leepb]Yes, that looks correct follow my suggestion, but I think you dropped
one of the equalities and some or your offsets are off.
I believe the following would be the correct plan.
{noformat}
LogicalProject(A=[$0])
LogicalJoin(condition=[AND(=($0, $2), =($1, $3) ), joinType=[inner])
LogicalTableScan(table=[[t1]])
LogicalProject(EXPR$0=if($2 is null,0,$2), a=[$0]) # Replace null
with 0
LogicalJoin(condition=[AND(=($0, $1)], joinType=[left])
LogicalAggregate(group($0))
LogicalTableScan(table=[[t1]])
LogicalAggregate(group=[{0}], EXPR$0=[COUNT()])
LogicalProject(a=[$0])
LogicalTableScan(table=[[t2]])
{noformat}
> Query gives incorrect result when COUNT appears in the correlated subquery
> select list
> --
>
> Key: CALCITE-5743
> URL: https://issues.apache.org/jira/browse/CALCITE-5743
> Project: Calcite
> Issue Type: Bug
>Reporter: libopeng
>Priority: Major
>
> {code:java}
> SELECT a
> FROM t1 t1
> WHERE b IN (SELECT COUNT (*) FROM t2 WHERE t1.a=t2.a); {code}
> {code:java}
> t1 | t2
> +--+ | +-+
> | a | b | | | a |
> +--+ | +-+
> | 3 | 6 | | | 3 |
> | 10 | 1 | | | 3 |
> | 8 | 0 | | | 10 |
>|
> {code}
> correct result
> {code:java}
> +--+
> | a |
> +--+
> | 10 |
> | 8 |{code}
> after decorrelate
> {code:java}
> LogicalProject(A=[$0])
> LogicalJoin(condition=[AND(=($0, $3), =($1, $2))], joinType=[inner])
> LogicalTableScan(table=[[t1]])
> LogicalFilter(condition=[=($0, $0)])
> LogicalProject(EXPR$0=[$1], a=[$0])
> LogicalAggregate(group=[{0}], EXPR$0=[COUNT()])
> LogicalProject(a=[$0])
> LogicalFilter(condition=[=($0, $0)])
> LogicalTableScan(table=[[t2]]) {code}
> error result
> {code:java}
> +--+
> | a |
> +--+
> | 10 | {code}
> Data with count=0 will be lost
> This issue was discovered in [this
> issue|https://issues.apache.org/jira/projects/CALCITE/issues/CALCITE-5568]
>
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Resolved] (CALCITE-2980) Implement the FORMAT clause of the CAST operator
[
https://issues.apache.org/jira/browse/CALCITE-2980?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Tanner Clary resolved CALCITE-2980.
---
Fix Version/s: 1.37.0
Resolution: Fixed
Merged via
[cc1d46a|https://github.com/apache/calcite/commit/cc1d46a4c4f88962c059e4ad0689ddfbb784ea96],
thanks for the fix, [~jerin_john]!
> Implement the FORMAT clause of the CAST operator
>
>
> Key: CALCITE-2980
> URL: https://issues.apache.org/jira/browse/CALCITE-2980
> Project: Calcite
> Issue Type: Bug
>Reporter: Julian Hyde
>Assignee: Jerin John
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.37.0
>
>
> SQL:2016 adds an optional {{FORMAT format}} clause to the {{CAST}} operator.
> It is a standard way to do what functions like {{TO_DATE}}, {{TO_NUMBER}},
> {{TO_CHAR}}, {{TO_TIMESTAMP}} have done in an ad hoc way (and with differing
> specifications among databases).
> Here is an example:
> {code:java}
> cast('01-05-2017' as date format 'DD-MM-')
> {code}
> The following paragraphs are copied from IMPALA-4018, which describes
> implementing this in Impala. (That case also describes cases where the
> implementations of {{TO_TIMESTAMP}} etc. in Hive, Impala, Oracle and
> PostgreSQL are not consistent with each other. We should take note as we
> implement these functions in Calcite.)
> SQL:2016 defines the following datetime templates
> {noformat}
> ::=
> { }...
> ::=
>
> |
> ::=
>
> |
> |
> |
> |
> |
> |
> |
> |
> |
> |
> |
> |
> |
> ::=
>
> |
> |
> |
> |
> |
> |
> |
> ::=
> | YYY | YY | Y
> ::=
> | RR
> ::=
> MM
> ::=
> DD
> ::=
> DDD
> ::=
> HH | HH12
> ::=
> HH24
> ::=
> MI
> ::=
> SS
> ::=
> S
> ::=
> FF1 | FF2 | FF3 | FF4 | FF5 | FF6 | FF7 | FF8 | FF9
> ::=
> A.M. | P.M.
> ::=
> TZH
> ::=
> TZM
> {noformat}
> SQL:2016 also introduced the {{FORMAT}} clause for {{CAST}} which is the
> standard way to do string <> datetime conversions
> {noformat}
> ::=
> CAST
>AS
> [ FORMAT ]
>
> ::=
>
> |
> ::=
>
> |
> ::=
>
> {noformat}
> For example:
> {noformat}
> CAST( AS [FORMAT ])
> CAST( AS [FORMAT ])
> cast(dt as string format 'DD-MM-')
> {noformat}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6313) Add POWER (enabled in Postgres library)
[ https://issues.apache.org/jira/browse/CALCITE-6313?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17836374#comment-17836374 ] James Duong commented on CALCITE-6313: -- You can have multiple SqlOperator objects that map to the same name and provide each with a different signature. Then enable them based on the dialect. > Add POWER (enabled in Postgres library) > --- > > Key: CALCITE-6313 > URL: https://issues.apache.org/jira/browse/CALCITE-6313 > Project: Calcite > Issue Type: Sub-task >Reporter: James Duong >Assignee: Norman Jordan >Priority: Minor > > * Our existing implementation always returns double. > * PostgreSQL allows returning a numeric instead of a double when inputs are > numeric. Not sure if this makes sense though. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6313) Add POWER (enabled in Postgres library)
[ https://issues.apache.org/jira/browse/CALCITE-6313?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17836374#comment-17836374 ] James Duong edited comment on CALCITE-6313 at 4/11/24 10:58 PM: You can have multiple SqlOperator objects that map to the same name and provide each with a different signature. Then enable them based on the Library. was (Author: jduong): You can have multiple SqlOperator objects that map to the same name and provide each with a different signature. Then enable them based on the dialect. > Add POWER (enabled in Postgres library) > --- > > Key: CALCITE-6313 > URL: https://issues.apache.org/jira/browse/CALCITE-6313 > Project: Calcite > Issue Type: Sub-task >Reporter: James Duong >Assignee: Norman Jordan >Priority: Minor > > * Our existing implementation always returns double. > * PostgreSQL allows returning a numeric instead of a double when inputs are > numeric. Not sure if this makes sense though. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6313) Add POWER (enabled in Postgres library)
[ https://issues.apache.org/jira/browse/CALCITE-6313?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17836373#comment-17836373 ] Norman Jordan commented on CALCITE-6313: [~tanclary] There is already a POWER function that always returns a double. [https://github.com/apache/calcite/blob/main/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java#L1664] Is there a way to allow the PostgreSQL library to have a different POWER function that returns either a decimal or double? > Add POWER (enabled in Postgres library) > --- > > Key: CALCITE-6313 > URL: https://issues.apache.org/jira/browse/CALCITE-6313 > Project: Calcite > Issue Type: Sub-task >Reporter: James Duong >Assignee: Norman Jordan >Priority: Minor > > * Our existing implementation always returns double. > * PostgreSQL allows returning a numeric instead of a double when inputs are > numeric. Not sure if this makes sense though. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6340) RelBuilder always creates Project with Convention.NONE during aggregate_
[
https://issues.apache.org/jira/browse/CALCITE-6340?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ASF GitHub Bot updated CALCITE-6340:
Labels: pull-request-available (was: )
> RelBuilder always creates Project with Convention.NONE during aggregate_
>
>
> Key: CALCITE-6340
> URL: https://issues.apache.org/jira/browse/CALCITE-6340
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.36.0
>Reporter: Adam Kennedy
>Assignee: James Duong
>Priority: Major
> Labels: pull-request-available
>
> In the RelBuilder method aggregate_, when (config.pruneInputOfAggregate() &&
> r instanceof Project) line 2443 the Project will be rewritten to remove
> unused columns.
> When this happens, the new Project will be created with the following line
> {{{}2487: r =
> {}}}{{{}2488: project.copy(cluster.traitSet(), project.getInput(),
> newProjects,{}}}
> {{2489: builder.build());}}
>
> The use of cluster.traitSet() returns emptyTraitSet which is always going to
> use Convention.NONE regardless of the Rebuilder's ProjectFactory.
> In the case of a query plan using a non-Logical convention FOO, with
> FooProject nodes that require the FOO convention, RelBuilder will normally
> happily produce FooProject nodes with FOO convention, allowing many CoreRules
> to be easily reused for custom Conventions.
> However, while RelBuilder will produce FooProject with FOO convention in the
> majority of cases, for the one specific case of column pruning a Project
> input to an aggregate, it will instead product a FooProject with NONE
> convention.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Assigned] (CALCITE-6313) Add POWER (enabled in Postgres library)
[ https://issues.apache.org/jira/browse/CALCITE-6313?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Norman Jordan reassigned CALCITE-6313: -- Assignee: Norman Jordan > Add POWER (enabled in Postgres library) > --- > > Key: CALCITE-6313 > URL: https://issues.apache.org/jira/browse/CALCITE-6313 > Project: Calcite > Issue Type: Sub-task >Reporter: James Duong >Assignee: Norman Jordan >Priority: Minor > > * Our existing implementation always returns double. > * PostgreSQL allows returning a numeric instead of a double when inputs are > numeric. Not sure if this makes sense though. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6313) Add POWER (enabled in Postgres library)
[ https://issues.apache.org/jira/browse/CALCITE-6313?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Norman Jordan updated CALCITE-6313: --- Summary: Add POWER (enabled in Postgres library) (was: Support PostgreSQL POW) > Add POWER (enabled in Postgres library) > --- > > Key: CALCITE-6313 > URL: https://issues.apache.org/jira/browse/CALCITE-6313 > Project: Calcite > Issue Type: Sub-task >Reporter: James Duong >Priority: Minor > > * Our existing implementation always returns double. > * PostgreSQL allows returning a numeric instead of a double when inputs are > numeric. Not sure if this makes sense though. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6361) Uncollect.deriveUncollectRowType crashes if the input data is not a collection
[
https://issues.apache.org/jira/browse/CALCITE-6361?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17836363#comment-17836363
]
Mihai Budiu commented on CALCITE-6361:
--
This is an assertion failure:
{code:java}
RelDataType ret = field.getType().getComponentType();
assert null != ret;
{code}
The top of the stack trace is:
{code:java}
at
org.apache.calcite.rel.core.Uncollect.deriveUncollectRowType(Uncollect.java:171)
at
org.apache.calcite.rel.core.Uncollect.deriveRowType(Uncollect.java:127)
at org.apache.calcite.rel.core.Uncollect.(Uncollect.java:78)
at org.apache.calcite.tools.RelBuilder.uncollect(RelBuilder.java:2254)
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertUnnest(SqlToRelConverter.java:2480)
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2436)
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2334)
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2296)
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertJoin(SqlToRelConverter.java:3182)
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2417)
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2296)
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectImpl(SqlToRelConverter.java:709)
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertSelect(SqlToRelConverter.java:690)
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive(SqlToRelConverter.java:3769)
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:610)
{code}
I will try to post here a test to reproduce. Mine is currently a bit involved
using several "CREATE TYPE statements"
> Uncollect.deriveUncollectRowType crashes if the input data is not a collection
> --
>
> Key: CALCITE-6361
> URL: https://issues.apache.org/jira/browse/CALCITE-6361
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.37.0
>Reporter: Mihai Budiu
>Priority: Minor
>
> This happens because the type checker calls getComponentType() without
> checking first that the field type has components. It should report an error
> in such a case.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6357) Calcite enforces select arguments count to be same as row schema fields which causes aliases to be ignored
[
https://issues.apache.org/jira/browse/CALCITE-6357?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17836338#comment-17836338
]
Julian Hyde commented on CALCITE-6357:
--
We added {{RelRoot}} way back in CALCITE-819 to express the fact that you want
columns to have particular names, and that you want the relation to be sorted
by columns that are not returned in the result.
If you need that, use {{RelRoot}}. But it is not a {{RelNode}} because adding
those capabilities to {{RelNode}} would hamper the operations of the planner.
> Calcite enforces select arguments count to be same as row schema fields which
> causes aliases to be ignored
> --
>
> Key: CALCITE-6357
> URL: https://issues.apache.org/jira/browse/CALCITE-6357
> Project: Calcite
> Issue Type: Bug
>Reporter: Brachi Packter
>Priority: Major
>
> Calcite RelBuilder.ProjectNamed checks if row size in the select is identical
> to schema fields, if no, it creates a project with fields as they appear in
> the select , meaning if they have aliases, they are returning with their
> aliases.
> Here, it checks if they are identical:
> https://github.com/apache/calcite/blob/f14cf4c32b9079984a988bbad40230aa6a59b127/core/src/main/java/org/apache/calcite/tools/RelBuilder.java#L2063
> using RexUtil.isIdentity method:
> ```
> public static boolean isIdentity(List exps,
> RelDataType inputRowType) {
> return inputRowType.getFieldCount() == exps.size()
> && containIdentity(exps, inputRowType, Litmus.IGNORE);
> }
> ```
> This is the problematic part `inputRowType.getFieldCount() == exps.size()`
> If they are identical, and return with their aliases, it is ignored in the
> "rename" method later on
> https://github.com/apache/calcite/blob/f14cf4c32b9079984a988bbad40230aa6a59b127/core/src/main/java/org/apache/calcite/tools/RelBuilder.java#L2125
> and alias is skipped
> https://github.com/apache/calcite/blob/f14cf4c32b9079984a988bbad40230aa6a59b127/core/src/main/java/org/apache/calcite/tools/RelBuilder.java#L2137
> This doesn't impact calcite queries, but in Apache Beam they are doing some
> optimization on top of it,
> https://github.com/apache/beam/blob/master/sdks/java/extensions/sql/src/main/java/org/apache/beam/sdk/extensions/sql/impl/rule/BeamAggregateProjectMergeRule.java
> which causes aliases to be ignored, and data is returning suddenly without
> correct column field.
> I believe the isIdentity check can causes more issues if not fixed, we need
> to understand why is it enforced? isn't it valid to have different size of
> fields in select from what we have in the schema?
> In our case we have a one big row and we run on it different queries, each
> with different fields in the select.
> Beam issue
> https://github.com/apache/beam/issues/30498
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6357) Calcite enforces select arguments count to be same as row schema fields which causes aliases to be ignored
[
https://issues.apache.org/jira/browse/CALCITE-6357?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17836303#comment-17836303
]
Kenneth Knowles commented on CALCITE-6357:
--
I'm still stuck on the idea that if I write "select x as col1, y as col2 from
aTableWithAHundredColumns" it could be valid to return a table with columns x
and y. I would be shocked if there exists an RDMS in the world that fails to
give output with columns col1 and col2. So I just mean to ask how we (Beam) can
invoke calcite to achieve that. I'm sure it works, since of course all the
other users of Calcite haven't hit this.
> Calcite enforces select arguments count to be same as row schema fields which
> causes aliases to be ignored
> --
>
> Key: CALCITE-6357
> URL: https://issues.apache.org/jira/browse/CALCITE-6357
> Project: Calcite
> Issue Type: Bug
>Reporter: Brachi Packter
>Priority: Major
>
> Calcite RelBuilder.ProjectNamed checks if row size in the select is identical
> to schema fields, if no, it creates a project with fields as they appear in
> the select , meaning if they have aliases, they are returning with their
> aliases.
> Here, it checks if they are identical:
> https://github.com/apache/calcite/blob/f14cf4c32b9079984a988bbad40230aa6a59b127/core/src/main/java/org/apache/calcite/tools/RelBuilder.java#L2063
> using RexUtil.isIdentity method:
> ```
> public static boolean isIdentity(List exps,
> RelDataType inputRowType) {
> return inputRowType.getFieldCount() == exps.size()
> && containIdentity(exps, inputRowType, Litmus.IGNORE);
> }
> ```
> This is the problematic part `inputRowType.getFieldCount() == exps.size()`
> If they are identical, and return with their aliases, it is ignored in the
> "rename" method later on
> https://github.com/apache/calcite/blob/f14cf4c32b9079984a988bbad40230aa6a59b127/core/src/main/java/org/apache/calcite/tools/RelBuilder.java#L2125
> and alias is skipped
> https://github.com/apache/calcite/blob/f14cf4c32b9079984a988bbad40230aa6a59b127/core/src/main/java/org/apache/calcite/tools/RelBuilder.java#L2137
> This doesn't impact calcite queries, but in Apache Beam they are doing some
> optimization on top of it,
> https://github.com/apache/beam/blob/master/sdks/java/extensions/sql/src/main/java/org/apache/beam/sdk/extensions/sql/impl/rule/BeamAggregateProjectMergeRule.java
> which causes aliases to be ignored, and data is returning suddenly without
> correct column field.
> I believe the isIdentity check can causes more issues if not fixed, we need
> to understand why is it enforced? isn't it valid to have different size of
> fields in select from what we have in the schema?
> In our case we have a one big row and we run on it different queries, each
> with different fields in the select.
> Beam issue
> https://github.com/apache/beam/issues/30498
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6358) Support all PostgreSQL 14 date/time patterns
[ https://issues.apache.org/jira/browse/CALCITE-6358?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17836284#comment-17836284 ] Julian Hyde commented on CALCITE-6358: -- Yes, in theory, it might. I don't have time to look at the code, but you can probably construct an example that behaves differently in "Postgres functions" than "BigQuery functions". And you probably should, so that we all remember that there is a level of indirection. > Support all PostgreSQL 14 date/time patterns > > > Key: CALCITE-6358 > URL: https://issues.apache.org/jira/browse/CALCITE-6358 > Project: Calcite > Issue Type: Sub-task >Reporter: Norman Jordan >Priority: Minor > > Many of the date/time format patterns supported by PostgreSQL 14 are not > supported in Calcite. > * HH > * US > * > * S > * AM > * A.M. > * am > * a.m. > * PM > * P.M. > * pm > * p.m. > * Y,YYY > * YYY > * Y > * IYYY > * IYY > * IY > * I > * BC > * B.C. > * bc > * b.c. > * AD > * A.D. > * ad > * a.d. > * MONTH > * month > * MON > * mon > * DAY > * day > * Dy > * dy > * IDDD > * ID > * TZH > * TZM > * OF > There are also template pattern modifiers that need to be supported. > * FM (prefix) > * TH (suffix) > * th (suffix) > * FX (prefix) > * TM (prefix) > Some format patterns in Calcite behave differently from PostgreSQL 14. > * FF1 > * FF2 > * FF4 > * FF5 > * FF6 > Also verify that the other existing format strings produce results that match > PostgreSQL 14. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6362) JSON_OBJECT. Internal representation DATE values leaks into resulting JSON object
[
https://issues.apache.org/jira/browse/CALCITE-6362?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17836274#comment-17836274
]
Mihai Budiu commented on CALCITE-6362:
--
I guess that the spec for JSON_OBJECT is not really clear about what happens to
data types that do not "exist" in JSON. And Calcite warns about relying on
implicit casts. Perhaps the following would fare better (haven't tried it, but
I assume the behavior for strings is clearer):
SELECT JSON_OBJECT('a': CAST(CAST('2010-01-01' AS DATE) AS VARCHAR)) as c1
> JSON_OBJECT. Internal representation DATE values leaks into resulting JSON
> object
> -
>
> Key: CALCITE-6362
> URL: https://issues.apache.org/jira/browse/CALCITE-6362
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Maksim Zhuravkov
>Priority: Minor
>
> When a CAST function is used inside a JSON_OBJECT, then resulting JSON
> contains a number of days instead
> {code}
> SELECT JSON_OBJECT('a': CAST('2010-01-01' AS DATE)) as c1
> {"a":14610}
> {code}
> I expected to get a date in a string form as many databases do in such case:
> Bigquery:
> {code}
> SELECT JSON_OBJECT('a', CAST('2010-01-10' AS DATE))
> {"a":"2010-01-10"}
> {code}
> PostgreSQL
> {code}
> SELECT json_build_object('a', '2010-01-01'::DATE)
> {"a":"2010-01-01"}
> {code}
> MySql
> {code}
> SELECT json_object('a', DATE '2010-01-01')
> {"a": "2010-01-01"}
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6358) Support all PostgreSQL 14 date/time patterns
[
https://issues.apache.org/jira/browse/CALCITE-6358?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17836270#comment-17836270
]
Norman Jordan commented on CALCITE-6358:
[~julianhyde] The request on my side is for {*}TO_CHAR(){*}, {*}TO_DATE(){*},
and *TO_TIMESTAMP()* to match PostgreSQL 14 behaviour. I added *CAST()* for
consistency, but I can leave it as is.
Will any of this interfere with BigQuery functionality?
> Support all PostgreSQL 14 date/time patterns
>
>
> Key: CALCITE-6358
> URL: https://issues.apache.org/jira/browse/CALCITE-6358
> Project: Calcite
> Issue Type: Sub-task
>Reporter: Norman Jordan
>Priority: Minor
>
> Many of the date/time format patterns supported by PostgreSQL 14 are not
> supported in Calcite.
> * HH
> * US
> *
> * S
> * AM
> * A.M.
> * am
> * a.m.
> * PM
> * P.M.
> * pm
> * p.m.
> * Y,YYY
> * YYY
> * Y
> * IYYY
> * IYY
> * IY
> * I
> * BC
> * B.C.
> * bc
> * b.c.
> * AD
> * A.D.
> * ad
> * a.d.
> * MONTH
> * month
> * MON
> * mon
> * DAY
> * day
> * Dy
> * dy
> * IDDD
> * ID
> * TZH
> * TZM
> * OF
> There are also template pattern modifiers that need to be supported.
> * FM (prefix)
> * TH (suffix)
> * th (suffix)
> * FX (prefix)
> * TM (prefix)
> Some format patterns in Calcite behave differently from PostgreSQL 14.
> * FF1
> * FF2
> * FF4
> * FF5
> * FF6
> Also verify that the other existing format strings produce results that match
> PostgreSQL 14.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6358) Support all PostgreSQL 14 date/time patterns
[ https://issues.apache.org/jira/browse/CALCITE-6358?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17836261#comment-17836261 ] Julian Hyde commented on CALCITE-6358: -- In Calcite, the set of format elements is tied to particular functions, not set system-wide. You could, say, have a TO_CHAR function that uses Postgres and a TO_TIMESTAMP function that uses BigQuery elements. So, my question is: which functions do you intend to test as part of this case? > Support all PostgreSQL 14 date/time patterns > > > Key: CALCITE-6358 > URL: https://issues.apache.org/jira/browse/CALCITE-6358 > Project: Calcite > Issue Type: Sub-task >Reporter: Norman Jordan >Priority: Minor > > Many of the date/time format patterns supported by PostgreSQL 14 are not > supported in Calcite. > * HH > * US > * > * S > * AM > * A.M. > * am > * a.m. > * PM > * P.M. > * pm > * p.m. > * Y,YYY > * YYY > * Y > * IYYY > * IYY > * IY > * I > * BC > * B.C. > * bc > * b.c. > * AD > * A.D. > * ad > * a.d. > * MONTH > * month > * MON > * mon > * DAY > * day > * Dy > * dy > * IDDD > * ID > * TZH > * TZM > * OF > There are also template pattern modifiers that need to be supported. > * FM (prefix) > * TH (suffix) > * th (suffix) > * FX (prefix) > * TM (prefix) > Some format patterns in Calcite behave differently from PostgreSQL 14. > * FF1 > * FF2 > * FF4 > * FF5 > * FF6 > Also verify that the other existing format strings produce results that match > PostgreSQL 14. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6265) Type coercion is failing for numeric values in prepared statements
[
https://issues.apache.org/jira/browse/CALCITE-6265?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17836260#comment-17836260
]
Mihai Budiu commented on CALCITE-6265:
--
It looks to me like the right course of action is to file a new issue with the
bug that you noticed, which we should then fix.
It looks like you have a pretty good diagnosis of what's going wrong.
> Type coercion is failing for numeric values in prepared statements
> --
>
> Key: CALCITE-6265
> URL: https://issues.apache.org/jira/browse/CALCITE-6265
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Tim Nieradzik
>Assignee: Tim Nieradzik
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.37.0
>
>
> Given a column of type {{{}INT{}}}. When providing a {{short}} value as a
> placeholder in a prepared statement, a {{ClassCastException}} is thrown.
> h2. Test case
> {{final String sql =}}
> {{ "select \"empid\" from \"hr\".\"emps\" where \"empid\" in (?, ?)";}}{{
> CalciteAssert.hr()}}
> {{ .query(sql)}}
> {{ .consumesPreparedStatement(p -> {}}
> {{ p.setShort(1, (short) 100);}}
> {{ p.setShort(2, (short) 110);}}
> {{ })}}
> {{ .returnsUnordered("empid=100", "empid=110");}}
> h2. Stack trace
> {{java.lang.ClassCastException: class java.lang.Short cannot be cast to class
> java.lang.Integer (java.lang.Short and java.lang.Integer are in module
> java.base of loader 'bootstrap')}}
> {{ at Baz$1$1.moveNext(Unknown Source)}}
> {{ at
> org.apache.calcite.linq4j.Linq4j$EnumeratorIterator.(Linq4j.java:679)}}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6358) Support all PostgreSQL 14 date/time patterns
[
https://issues.apache.org/jira/browse/CALCITE-6358?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17836258#comment-17836258
]
Norman Jordan commented on CALCITE-6358:
[~julianhyde] {*}TO_CHAR(){*}, {*}TO_DATE(){*}, {*}TO_TIMESTAMP(){*}, and
{*}CAST(... FORMAT ...){*}.
In general anywhere the user can specify a date/time format should support the
same format patterns when possible.
> Support all PostgreSQL 14 date/time patterns
>
>
> Key: CALCITE-6358
> URL: https://issues.apache.org/jira/browse/CALCITE-6358
> Project: Calcite
> Issue Type: Sub-task
>Reporter: Norman Jordan
>Priority: Minor
>
> Many of the date/time format patterns supported by PostgreSQL 14 are not
> supported in Calcite.
> * HH
> * US
> *
> * S
> * AM
> * A.M.
> * am
> * a.m.
> * PM
> * P.M.
> * pm
> * p.m.
> * Y,YYY
> * YYY
> * Y
> * IYYY
> * IYY
> * IY
> * I
> * BC
> * B.C.
> * bc
> * b.c.
> * AD
> * A.D.
> * ad
> * a.d.
> * MONTH
> * month
> * MON
> * mon
> * DAY
> * day
> * Dy
> * dy
> * IDDD
> * ID
> * TZH
> * TZM
> * OF
> There are also template pattern modifiers that need to be supported.
> * FM (prefix)
> * TH (suffix)
> * th (suffix)
> * FX (prefix)
> * TM (prefix)
> Some format patterns in Calcite behave differently from PostgreSQL 14.
> * FF1
> * FF2
> * FF4
> * FF5
> * FF6
> Also verify that the other existing format strings produce results that match
> PostgreSQL 14.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6265) Type coercion is failing for numeric values in prepared statements
[
https://issues.apache.org/jira/browse/CALCITE-6265?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17836187#comment-17836187
]
Ruben Q L edited comment on CALCITE-6265 at 4/11/24 2:06 PM:
-
FYI, this fix caused a few regressions on my project:
A) one test that used to return 1 row now returns 0 rows
B) a couple of tests that used to work fine now fail with a ClassCastException
on the dynamic code
After some investigation, I'd say A seems a real regression on Calcite, whereas
B could be arguably on my side; but I share this info because probably both
could happen to other projects if we release the next version with this patch
as it is.
A) The problem with the test that no longer returns the expected 1 row is as
follows:
- A table with a decimal field, there is one column with value {{BigDecimal
DECIMAL1 = new BigDecimal("299792.45799985");}}
- A simple query: SELECT t.id FROM myTable t WHERE t.myDecimalField = ?
- I set the parameter value to DECIMAL1
- Execute the query, should get 1 row, it gets nothing.
The problem is that, before the patch, the dynamic code would simply "read" the
parameter value from the root context and use it, [but now with the new code
there is this cast logic into Number and back to
BigDecimal|https://github.com/apache/calcite/blob/e7c1f0c9baecef62289facf9aaa03bdff950b938/core/src/main/java/org/apache/calcite/adapter/enumerable/RexToLixTranslator.java#L1396]
(unnecessary in this case, since everything is BigDecimal), which leads to the
following dynamic code:
{code}
(Number) root.get("?1") == null ? (java.math.BigDecimal) null :
java.math.BigDecimal.valueOf(((Number) root.get("?1")).longValue())
{code}
The problem is that doing {{BigDecimal.valueOf(myBigDecimal.longValue())}} does
not necessarily bring back the original value of myBigDecimal, since
BigDecimal#longValue can lose information, and I guess this is what is
happening here.
B) The issue with the ClassCastException (BigDecimal cannot be cast to Integer)
is also related to the new casting in dynamic code, but it is a bit more
subtle, and it seems related to some internal Hoisting that we have in place on
my project (transforming literals into parameters to increase the chances of
internal cache hits when converting a logical plan into physical plan). The
thing is, in the literal=>parameter conversion, we use
[RexLiteral#getValue3|https://github.com/apache/calcite/blob/e7c1f0c9baecef62289facf9aaa03bdff950b938/core/src/main/java/org/apache/calcite/rex/RexLiteral.java#L989]
(which as stated on its javadoc returns "the value of this literal, in the
form that the rex-to-lix translator wants it") to extract the value (and use it
as parameter value). So far it worked like charm. But with this change, the
ClassCastException arises because actually getValue3 of a RexLiteral with type
= BasicSqlTypeINTEGER, returns the value as a BigDecimal (as it is stored in
RexLiteral#value field), because its typeName is SqlTypeName#DECIMAL; btw this
RexLiteral is created by the standard behavior that we get if we create a
RexLiteral from an Integer via Calcite's
[RelBuilder#literal(IntegerValue)|https://github.com/apache/calcite/blob/e7c1f0c9baecef62289facf9aaa03bdff950b938/core/src/main/java/org/apache/calcite/tools/RelBuilder.java#L481].
PS: Also, I know I'm late to the party here, but one could argue if this bug is
_really_ a bug, I mean, if there is an INT field, then the user should use the
appropriate [setInt method; and not
setShort|https://github.com/apache/calcite-avatica/blob/275a082d364e00a9f2e9d50a4468fef0782e3c81/core/src/main/java/org/apache/calcite/avatica/AvaticaSite.java#L93].
I agree than having an exception at runtime in the dynamic code in this case
is ugly; but maybe a check-and-fail could be added earlier? Or maybe it should
be Avatica's responsibility to perform the necessary conversions (as
[~julianhyde] suggested) ?
Is this bug fixing an error on Calcite side, which only happens on a certain
scenario triggered by Avatica?
was (Author: rubenql):
FYI, this fix caused a few regressions on my project:
A) one test that used to return 1 row now returns 0 rows
B) a couple of tests that used to work fine now fail with a ClassCastException
on the dynamic code
After some investigation, I'd say A seems a real regression on Calcite, whereas
B could be arguably on my side; but I share this info because probably both
could happen to other projects if we release the next version with this patch
as it is.
A) The problem with the test that no longer returns the expected 1 row is as
follows:
- A table with a decimal field, there is one column with value {{BigDecimal
DECIMAL1 = new BigDecimal("299792.45799985");}}
- A simple query: SELECT t.id FROM myTable t WHERE t.myDecimalField = ?
- I set the parameter value to DECIMAL1
- Execute the query, should get 1 row, it gets
[jira] [Comment Edited] (CALCITE-6265) Type coercion is failing for numeric values in prepared statements
[
https://issues.apache.org/jira/browse/CALCITE-6265?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17836187#comment-17836187
]
Ruben Q L edited comment on CALCITE-6265 at 4/11/24 1:47 PM:
-
FYI, this fix caused a few regressions on my project:
A) one test that used to return 1 row now returns 0 rows
B) a couple of tests that used to work fine now fail with a ClassCastException
on the dynamic code
After some investigation, I'd say A seems a real regression on Calcite, whereas
B could be arguably on my side; but I share this info because probably both
could happen to other projects if we release the next version with this patch
as it is.
A) The problem with the test that no longer returns the expected 1 row is as
follows:
- A table with a decimal field, there is one column with value {{BigDecimal
DECIMAL1 = new BigDecimal("299792.45799985");}}
- A simple query: SELECT t.id FROM myTable t WHERE t.myDecimalField = ?
- I set the parameter value to DECIMAL1
- Execute the query, should get 1 row, it gets nothing.
The problem is that, before the patch, the dynamic code would simply "read" the
parameter value from the root context and use it, [but now with the new code
there is this cast logic into Number and back to
BigDecimal|https://github.com/apache/calcite/blob/e7c1f0c9baecef62289facf9aaa03bdff950b938/core/src/main/java/org/apache/calcite/adapter/enumerable/RexToLixTranslator.java#L1396]
(unnecessary in this case, since everything is BigDecimal), which leads to the
following dynamic code:
{code}
(Number) root.get("?1") == null ? (java.math.BigDecimal) null :
java.math.BigDecimal.valueOf(((Number) root.get("?1")).longValue())
{code}
The problem is that doing {{BigDecimal.valueOf(myBigDecimal.longValue())}} does
not necessarily bring back the original value of myBigDecimal, since
BigDecimal#longValue can lose information, and I guess this is what is
happening here.
B) The issue with the ClassCastException (BigDecimal cannot be cast to Integer)
is also related to the new casting in dynamic code, but it is a bit more
subtle, and it seems related to some internal Hoisting that we have in place on
my project (transforming literals into parameters to increase the chances of
internal cache hits when converting a logical plan into physical plan). The
thing is, in the literal=>parameter conversion, we use
[RexLiteral#getValue3|https://github.com/apache/calcite/blob/e7c1f0c9baecef62289facf9aaa03bdff950b938/core/src/main/java/org/apache/calcite/rex/RexLiteral.java#L989]
(which as stated on its javadoc returns "the value of this literal, in the
form that the rex-to-lix translator wants it") to extract the value (and use it
as parameter value). So far it worked like charm. But with this change, the
ClassCastException arises because actually getValue3 of a RexLiteral with type
= BasicSqlTypeINTEGER, returns the value as a BigDecimal (as it is stored in
RexLiteral#value field), because its typeName is SqlTypeName#DECIMAL; btw this
RexLiteral is created by the standard behavior that we get if we create a
RexLiteral from an Integer via Calcite's
[RelBuilder#literal(IntegerValue)|https://github.com/apache/calcite/blob/e7c1f0c9baecef62289facf9aaa03bdff950b938/core/src/main/java/org/apache/calcite/tools/RelBuilder.java#L481].
was (Author: rubenql):
FYI, this fix caused a few regressions on my project:
A) one test that used to return 1 row now returns 0 rows
B) a couple of tests that used to work fine now fail with a ClassCastException
on the dynamic code
After some investigation, I'd say A seems a real regression on Calcite, whereas
B could be arguably on my side; but I share this info because probably both
could happen to other projects if we release the next version with this patch
as it is.
A) The problem with the test that no longer returns the expected 1 row is as
follows:
- A table with a decimal field, there is one column with value {{BigDecimal
DECIMAL1 = new BigDecimal("299792.45799985");}}
- A simple query: SELECT t.id FROM myTable t WHERE t.myDecimalField = ?
- I set the parameter value to DECIMAL1
- Execute the query, should get 1 row, it gets nothing.
The problem is that, before the patch, the dynamic code would simply "read" the
parameter value from the root context and use it, [but now with the new code
there is this cast logic into Number and back to
BigDecimal|https://github.com/apache/calcite/blob/e7c1f0c9baecef62289facf9aaa03bdff950b938/core/src/main/java/org/apache/calcite/adapter/enumerable/RexToLixTranslator.java#L1396]
(unnecessary in this case, since everything is BigDecimal), which leads to the
following dynamic code:
{code}
(Number) root.get("?1") == null ? (java.math.BigDecimal) null :
java.math.BigDecimal.valueOf(((Number) root.get("?1")).longValue())
{code}
The problem is that doing {{BigDecimal.valueOf(myBigDecimal.longValue())}} does
not
[jira] [Comment Edited] (CALCITE-6265) Type coercion is failing for numeric values in prepared statements
[
https://issues.apache.org/jira/browse/CALCITE-6265?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17836187#comment-17836187
]
Ruben Q L edited comment on CALCITE-6265 at 4/11/24 1:45 PM:
-
FYI, this fix caused a few regressions on my project:
A) one test that used to return 1 row now returns 0 rows
B) a couple of tests that used to work fine now fail with a ClassCastException
on the dynamic code
After some investigation, I'd say A seems a real regression on Calcite, whereas
B could be arguably on my side; but I share this info because probably both
could happen to other projects if we release the next version with this patch
as it is.
A) The problem with the test that no longer returns the expected 1 row is as
follows:
- A table with a decimal field, there is one column with value {{BigDecimal
DECIMAL1 = new BigDecimal("299792.45799985");}}
- A simple query: SELECT t.id FROM myTable t WHERE t.myDecimalField = ?
- I set the parameter value to DECIMAL1
- Execute the query, should get 1 row, it gets nothing.
The problem is that, before the patch, the dynamic code would simply "read" the
parameter value from the root context and use it, [but now with the new code
there is this cast logic into Number and back to
BigDecimal|https://github.com/apache/calcite/blob/e7c1f0c9baecef62289facf9aaa03bdff950b938/core/src/main/java/org/apache/calcite/adapter/enumerable/RexToLixTranslator.java#L1396]
(unnecessary in this case, since everything is BigDecimal), which leads to the
following dynamic code:
{code}
(Number) root.get("?1") == null ? (java.math.BigDecimal) null :
java.math.BigDecimal.valueOf(((Number) root.get("?1")).longValue())
{code}
The problem is that doing {{BigDecimal.valueOf(myBigDecimal.longValue())}} does
not necessarily bring back the original value of myBigDecimal, since
BigDecimal#longValue can lose information, and I guess this is what is
happening here.
B) The issue with the ClassCastException (BigDecimal cannot be cast to Integer)
is also related to the new casting in dynamic code, but it is a bit more
subtle, and it seems related to some internal Hoisting that we have in place on
my project (transforming literals into parameters to increase the chances of
internal cache hits when converting a logical plan into physical plan). The
thing is, in the literal=>parameter conversion, we use
[RexLiteral#getValue3|https://github.com/apache/calcite/blob/e7c1f0c9baecef62289facf9aaa03bdff950b938/core/src/main/java/org/apache/calcite/rex/RexLiteral.java#L989]
(which as stated on its javadoc returns "the value of this literal, in the
form that the rex-to-lix translator wants it") to extract the value (and use it
as parameter value). So far it worked like charm. But with this change, the
ClassCastException arises because actually getValue3 of a RexLiteral with type
= BasicSqlTypeINTEGER, returns the value as a BigDecimal (as it is stored in
RexLiteral#value field), because its typeName is SqlTypeName#DECIMAL; btw this
RexLiteral is created by the standard behavior that we get if we create a
RexLiteral from an Integer via Calcite's RelBuilder#literal(IntegerValue).
was (Author: rubenql):
FYI, this fix caused a few regressions on my project:
A) one test that used to return 1 row now returns 0 rows
B) a couple of tests that used to work fine now fail with a ClassCastException
on the dynamic code
After some investigation, I'd say A seems a real regression on Calcite, whereas
B could be arguably on my side; but I share this info because probably both
could happen to other projects if we release the next version with this patch
as it is.
A) The problem with the test that no longer returns the expected 1 row is as
follows:
- A table with a decimal field, there is one column with value {{BigDecimal
DECIMAL1 = new BigDecimal("299792.45799985");}}
- A simple query: SELECT t.id FROM myTable t WHERE t.myDecimalValue = ?
- I set the parameter value to DECIMAL1
- Execute the query, should get 1 row, it gets nothing.
The problem is that, before the patch, the dynamic code would simply "read" the
parameter value from the root context and use it, [but now with the new code
there is this cast logic into Number and back to
BigDecimal|https://github.com/apache/calcite/blob/e7c1f0c9baecef62289facf9aaa03bdff950b938/core/src/main/java/org/apache/calcite/adapter/enumerable/RexToLixTranslator.java#L1396]
(unnecessary in this case, since everything is BigDecimal), which leads to the
following dynamic code:
{code}
(Number) root.get("?1") == null ? (java.math.BigDecimal) null :
java.math.BigDecimal.valueOf(((Number) root.get("?1")).longValue())
{code}
The problem is that doing {{BigDecimal.valueOf(myBigDecimal.longValue())}} does
not necessarily bring back the original value of myBigDecimal, since
BigDecimal#longValue can lose information, and I guess this is what is
happening
[jira] [Commented] (CALCITE-6265) Type coercion is failing for numeric values in prepared statements
[
https://issues.apache.org/jira/browse/CALCITE-6265?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17836187#comment-17836187
]
Ruben Q L commented on CALCITE-6265:
FYI, this fix caused a few regressions on my project:
A) one test that used to return 1 row now returns 0 rows
B) a couple of tests that used to work fine now fail with a ClassCastException
on the dynamic code
After some investigation, I'd say A seems a real regression on Calcite, whereas
B could be arguably on my side; but I share this info because probably both
could happen to other projects if we release the next version with this patch
as it is.
A) The problem with the test that no longer returns the expected 1 row is as
follows:
- A table with a decimal field, there is one column with value {{BigDecimal
DECIMAL1 = new BigDecimal("299792.45799985");}}
- A simple query: SELECT t.id FROM myTable t WHERE t.myDecimalValue = ?
- I set the parameter value to DECIMAL1
- Execute the query, should get 1 row, it gets nothing.
The problem is that, before the patch, the dynamic code would simply "read" the
parameter value from the root context and use it, [but now with the new code
there is this cast logic into Number and back to
BigDecimal|https://github.com/apache/calcite/blob/e7c1f0c9baecef62289facf9aaa03bdff950b938/core/src/main/java/org/apache/calcite/adapter/enumerable/RexToLixTranslator.java#L1396]
(unnecessary in this case, since everything is BigDecimal), which leads to the
following dynamic code:
{code}
(Number) root.get("?1") == null ? (java.math.BigDecimal) null :
java.math.BigDecimal.valueOf(((Number) root.get("?1")).longValue())
{code}
The problem is that doing {{BigDecimal.valueOf(myBigDecimal.longValue())}} does
not necessarily bring back the original value of myBigDecimal, since
BigDecimal#longValue can lose information, and I guess this is what is
happening here.
B) The issue with the ClassCastException (BigDecimal cannot be cast to Integer)
is also related to the new casting in dynamic code, but it is a bit more
subtle, and it seems related to some internal Hoisting that we have in place on
my project (transforming literals into parameters to increase the chances of
internal cache hits when converting a logical plan into physical plan). The
thing is, in the literal=>parameter conversion, we use
[RexLiteral#getValue3|https://github.com/apache/calcite/blob/e7c1f0c9baecef62289facf9aaa03bdff950b938/core/src/main/java/org/apache/calcite/rex/RexLiteral.java#L989]
(which as stated on its javadoc returns "the value of this literal, in the
form that the rex-to-lix translator wants it") to extract the value (and use it
as parameter value). So far it worked like charm. But with this change, the
ClassCastException arises because actually getValue3 of a RexLiteral with type
= BasicSqlTypeINTEGER, returns the value as a BigDecimal (as it is stored in
RexLiteral#value field), because its typeName is SqlTypeName#DECIMAL; btw this
RexLiteral is created by the standard behavior that we get if we create a
RexLiteral from an Integer via Calcite's RelBuilder#literal(IntegerValue).
> Type coercion is failing for numeric values in prepared statements
> --
>
> Key: CALCITE-6265
> URL: https://issues.apache.org/jira/browse/CALCITE-6265
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Tim Nieradzik
>Assignee: Tim Nieradzik
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.37.0
>
>
> Given a column of type {{{}INT{}}}. When providing a {{short}} value as a
> placeholder in a prepared statement, a {{ClassCastException}} is thrown.
> h2. Test case
> {{final String sql =}}
> {{ "select \"empid\" from \"hr\".\"emps\" where \"empid\" in (?, ?)";}}{{
> CalciteAssert.hr()}}
> {{ .query(sql)}}
> {{ .consumesPreparedStatement(p -> {}}
> {{ p.setShort(1, (short) 100);}}
> {{ p.setShort(2, (short) 110);}}
> {{ })}}
> {{ .returnsUnordered("empid=100", "empid=110");}}
> h2. Stack trace
> {{java.lang.ClassCastException: class java.lang.Short cannot be cast to class
> java.lang.Integer (java.lang.Short and java.lang.Integer are in module
> java.base of loader 'bootstrap')}}
> {{ at Baz$1$1.moveNext(Unknown Source)}}
> {{ at
> org.apache.calcite.linq4j.Linq4j$EnumeratorIterator.(Linq4j.java:679)}}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6362) JSON_OBJECT. Internal representation DATE values leaks into resulting JSON object
[
https://issues.apache.org/jira/browse/CALCITE-6362?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17836181#comment-17836181
]
Alessandro Solimando commented on CALCITE-6362:
---
I agree that getting a string with the date would be the expected output,
instead of what we are returning now.
> JSON_OBJECT. Internal representation DATE values leaks into resulting JSON
> object
> -
>
> Key: CALCITE-6362
> URL: https://issues.apache.org/jira/browse/CALCITE-6362
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Maksim Zhuravkov
>Priority: Minor
>
> When a CAST function is used inside a JSON_OBJECT, then resulting JSON
> contains a number of days instead
> {code}
> SELECT JSON_OBJECT('a': CAST('2010-01-01' AS DATE)) as c1
> {"a":14610}
> {code}
> I expected to get a date in a string form as many databases do in such case:
> Bigquery:
> {code}
> SELECT JSON_OBJECT('a', CAST('2010-01-10' AS DATE))
> {"a":"2010-01-10"}
> {code}
> PostgreSQL
> {code}
> SELECT json_build_object('a', '2010-01-01'::DATE)
> {"a":"2010-01-01"}
> {code}
> MySql
> {code}
> SELECT json_object('a', DATE '2010-01-01')
> {"a": "2010-01-01"}
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-6362) JSON_OBJECT. Internal representation DATE values leaks into resulting JSON object
[
https://issues.apache.org/jira/browse/CALCITE-6362?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Maksim Zhuravkov updated CALCITE-6362:
--
Description:
When a CAST function is used inside a JSON_OBJECT, then resulting JSON contains
a number of days instead
{code}
SELECT JSON_OBJECT('a': CAST('2010-01-01' AS DATE)) as c1
{"a":14610}
{code}
I expected to get a date in a string form as many databases do in such case:
Bigquery:
{code}
SELECT JSON_OBJECT('a', CAST('2010-01-10' AS DATE))
{"a":"2010-01-10"}
{code}
PostgreSQL
{code}
SELECT json_build_object('a', '2010-01-01'::DATE)
{"a":"2010-01-01"}
{code}
MySql
{code}
SELECT json_object('a', DATE '2010-01-01')
{"a": "2010-01-01"}
{code}
was:
When a CAST function is used inside a JSON_OBJECT, then resulting JSON contains
a number of days instead
{code}
SELECT JSON_OBJECT('a': CAST('2010-01-01' AS DATE)) as c1;
{"a":14610}
{code}
I expected to get a date in a string form as many databases do in such case:
Bigquery:
{code}
SELECT JSON_OBJECT('a', CAST('2010-01-10' AS DATE))
{"a":"2010-01-10"}
{code}
PostgreSQL
{code}
SELECT json_build_object('a', '2010-01-01'::DATE)
{"a":"2010-01-01"}
{code}
MySql
{code}
SELECT json_object('a', DATE '2010-01-01')
{"a": "2010-01-01"}
{code}
> JSON_OBJECT. Internal representation DATE values leaks into resulting JSON
> object
> -
>
> Key: CALCITE-6362
> URL: https://issues.apache.org/jira/browse/CALCITE-6362
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Maksim Zhuravkov
>Priority: Minor
>
> When a CAST function is used inside a JSON_OBJECT, then resulting JSON
> contains a number of days instead
> {code}
> SELECT JSON_OBJECT('a': CAST('2010-01-01' AS DATE)) as c1
> {"a":14610}
> {code}
> I expected to get a date in a string form as many databases do in such case:
> Bigquery:
> {code}
> SELECT JSON_OBJECT('a', CAST('2010-01-10' AS DATE))
> {"a":"2010-01-10"}
> {code}
> PostgreSQL
> {code}
> SELECT json_build_object('a', '2010-01-01'::DATE)
> {"a":"2010-01-01"}
> {code}
> MySql
> {code}
> SELECT json_object('a', DATE '2010-01-01')
> {"a": "2010-01-01"}
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-6362) JSON_OBJECT. Internal representation DATE values leaks into resulting JSON object
[
https://issues.apache.org/jira/browse/CALCITE-6362?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Maksim Zhuravkov updated CALCITE-6362:
--
Component/s: core
> JSON_OBJECT. Internal representation DATE values leaks into resulting JSON
> object
> -
>
> Key: CALCITE-6362
> URL: https://issues.apache.org/jira/browse/CALCITE-6362
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Maksim Zhuravkov
>Priority: Minor
>
> When a CAST function is used inside a JSON_OBJECT, then resulting JSON
> contains a number of days instead
> {code}
> SELECT JSON_OBJECT('a': CAST('2010-01-01' AS DATE)) as c1;
> {"a":14610}
> {code}
> I expected to get a date in a string form as many databases do in such case:
> Bigquery:
> {code}
> SELECT JSON_OBJECT('a', CAST('2010-01-10' AS DATE))
> {"a":"2010-01-10"}
> {code}
> PostgreSQL
> {code}
> SELECT json_build_object('a', '2010-01-01'::DATE)
> {"a":"2010-01-01"}
> {code}
> MySql
> {code}
> SELECT json_object('a', DATE '2010-01-01')
> {"a": "2010-01-01"}
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-6362) JSON_OBJECT. Internal representation DATE values leaks into resulting JSON object
Maksim Zhuravkov created CALCITE-6362:
-
Summary: JSON_OBJECT. Internal representation DATE values leaks
into resulting JSON object
Key: CALCITE-6362
URL: https://issues.apache.org/jira/browse/CALCITE-6362
Project: Calcite
Issue Type: Bug
Reporter: Maksim Zhuravkov
When a CAST function is used inside a JSON_OBJECT, then resulting JSON contains
a number of days instead
{code}
SELECT JSON_OBJECT('a': CAST('2010-01-01' AS DATE)) as c1;
{"a":14610}
{code}
I expected to get a date in a string form as many databases do in such case:
Bigquery:
{code}
SELECT JSON_OBJECT('a', CAST('2010-01-10' AS DATE))
{"a":"2010-01-10"}
{code}
PostgreSQL
{code}
SELECT json_build_object('a', '2010-01-01'::DATE)
{"a":"2010-01-01"}
{code}
MySql
{code}
SELECT json_object('a', DATE '2010-01-01')
{"a": "2010-01-01"}
{code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5743) Query gives incorrect result when COUNT appears in the correlated subquery select list
[
https://issues.apache.org/jira/browse/CALCITE-5743?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17836172#comment-17836172
]
libopeng commented on CALCITE-5743:
---
{code:java}
LogicalProject(A=[$0])
LogicalJoin(condition=[AND(=($0, $1), joinType=[inner])
LogicalTableScan(table=[[t1]])
LogicalProject(EXPR$0=if($2 is null,0,$2), a=[$0]) # Replace null
with 0
LogicalJoin(condition=[AND(=($0, $1)], joinType=[left])
LogicalAggregate(group($0))
LogicalTableScan(table=[[t1]])
LogicalAggregate(group=[{0}], EXPR$0=[COUNT()])
LogicalProject(a=[$0])
LogicalTableScan(table=[[t2]]) {code}
[~jamesstarr] Using the case described above, do you mean to generate this
result?
> Query gives incorrect result when COUNT appears in the correlated subquery
> select list
> --
>
> Key: CALCITE-5743
> URL: https://issues.apache.org/jira/browse/CALCITE-5743
> Project: Calcite
> Issue Type: Bug
>Reporter: libopeng
>Priority: Major
>
> {code:java}
> SELECT a
> FROM t1 t1
> WHERE b IN (SELECT COUNT (*) FROM t2 WHERE t1.a=t2.a); {code}
> {code:java}
> t1 | t2
> +--+ | +-+
> | a | b | | | a |
> +--+ | +-+
> | 3 | 6 | | | 3 |
> | 10 | 1 | | | 3 |
> | 8 | 0 | | | 10 |
>|
> {code}
> correct result
> {code:java}
> +--+
> | a |
> +--+
> | 10 |
> | 8 |{code}
> after decorrelate
> {code:java}
> LogicalProject(A=[$0])
> LogicalJoin(condition=[AND(=($0, $3), =($1, $2))], joinType=[inner])
> LogicalTableScan(table=[[t1]])
> LogicalFilter(condition=[=($0, $0)])
> LogicalProject(EXPR$0=[$1], a=[$0])
> LogicalAggregate(group=[{0}], EXPR$0=[COUNT()])
> LogicalProject(a=[$0])
> LogicalFilter(condition=[=($0, $0)])
> LogicalTableScan(table=[[t2]]) {code}
> error result
> {code:java}
> +--+
> | a |
> +--+
> | 10 | {code}
> Data with count=0 will be lost
> This issue was discovered in [this
> issue|https://issues.apache.org/jira/projects/CALCITE/issues/CALCITE-5568]
>
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
