[jira] [Updated] (CALCITE-5655) Intermediate table alias should be different to avoid wrong field reference lookup in subquery remove phase
[
https://issues.apache.org/jira/browse/CALCITE-5655?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Runkang He updated CALCITE-5655:
Summary: Intermediate table alias should be different to avoid wrong field
reference lookup in subquery remove phase (was: Wrong plan for multiple
IN/SOME sub-queries with OR predicate)
> Intermediate table alias should be different to avoid wrong field reference
> lookup in subquery remove phase
> ---
>
> Key: CALCITE-5655
> URL: https://issues.apache.org/jira/browse/CALCITE-5655
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.34.0
>Reporter: Runkang He
>Assignee: Runkang He
>Priority: Major
> Labels: pull-request-available
> Time Spent: 1h 40m
> Remaining Estimate: 0h
>
> When the query contains multiple IN/SOME sub-queries connected with OR
> predicate in WHERE clause, the result is wrong. The minimal reproducer is
> below:
> SQL:
> {code:sql}
> select empno from sales.empnullables
> where deptno in (
> select deptno from sales.deptnullables where name = 'dept1')
> or deptno in (
> select deptno from sales.deptnullables where name = 'dept2')
> {code}
> The Plan generated by calcite master branch: (Notice the bold part of *<>($2,
> 0)* in the downstream LogicalFilter)
> {code:sql}
> LogicalProject(EMPNO=[$0])
> LogicalProject(EMPNO=[$0], DEPTNO=[$1])
> LogicalFilter(condition=[OR(AND(<>($2, 0), IS NOT NULL($5), IS NOT
> NULL($1)), AND(***<>($2, 0)***, IS NOT NULL($9), IS NOT NULL($1)))])
> LogicalJoin(condition=[=($1, $8)], joinType=[left])
> LogicalJoin(condition=[true], joinType=[inner])
> LogicalJoin(condition=[=($1, $4)], joinType=[left])
> LogicalJoin(condition=[true], joinType=[inner])
> LogicalProject(EMPNO=[$0], DEPTNO=[$7])
> LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
> LogicalAggregate(group=[{}], c=[COUNT()], ck=[COUNT($0)])
> LogicalProject(DEPTNO=[$0])
> LogicalFilter(condition=[=($1, 'dept1')])
> LogicalTableScan(table=[[CATALOG, SALES, DEPTNULLABLES]])
> LogicalProject(DEPTNO=[$0], i=[true])
> LogicalFilter(condition=[=($1, 'dept1')])
> LogicalTableScan(table=[[CATALOG, SALES, DEPTNULLABLES]])
> LogicalAggregate(group=[{}], c=[COUNT()], ck=[COUNT($0)])
> LogicalProject(DEPTNO=[$0])
> LogicalFilter(condition=[=($1, 'dept2')])
> LogicalTableScan(table=[[CATALOG, SALES, DEPTNULLABLES]])
> LogicalProject(DEPTNO=[$0], i=[true])
> LogicalFilter(condition=[=($1, 'dept2')])
> LogicalTableScan(table=[[CATALOG, SALES, DEPTNULLABLES]])
> {code}
> The wrong part is that when build the downstream LogicalFilter for the two
> sub-queries, the filter for the second sub-query is AND(<>($2, 0), IS NOT
> NULL($9), IS NOT NULL($1)), notice that *$2 should be the second sub-query's
> intermediate table field ct.c(which field index is $6), but now the actual
> reference is the first sub-query's*, this leads to wrong plan, and wrong
> result.
> The root cause is that intermediate table alias is the same as the previous
> sub-query's, but when lookup intermediate table field, it always returns the
> previous one which is not belong to the current subquery.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-5663) [TestKit] RelOptFixture does not enforce the collation
[
https://issues.apache.org/jira/browse/CALCITE-5663?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Marieke Gueye updated CALCITE-5663:
---
Description:
In RelOptFixture, we currently change the traitsets to enforce
EnumerableConvention.INSTANCE, however, in this instance we forget to port the
collation in the traitset.
{code:java}
if (planner instanceof VolcanoPlanner)
{ r2 = planner.changeTraits(relBefore,
relBefore.getTraitSet().replace(EnumerableConvention.INSTANCE)); }
else
{ r2 = relBefore; }
{code}
The problem goes even deeper, as of now, there is no way to get access to the
collation as it lives in the relRoot, and we currently only access the relNode
through the relSupplier
The consequence of this can be pretty dire as it may mean that some rules might
be incorrectly tested.
was:
In RelOptFixture, we currently change the traitsets to enforce
EnumerableConvention.INSTANCE, however, in this instance we forget to port the
collation in the traitset.
```
if (planner instanceof VolcanoPlanner) {
r2 =
planner.changeTraits(relBefore,
relBefore.getTraitSet().replace(EnumerableConvention.INSTANCE));
} else {
r2 = relBefore;
}
```
The problem goes even deeper, as of now, there is no way to get access to the
collation as it lives in the relRoot, and we currently only access the relNode
through the `relSupplier`
The consequence of this can be pretty dire as it may mean that some rules might
be incorrectly tested.
> [TestKit] RelOptFixture does not enforce the collation
> --
>
> Key: CALCITE-5663
> URL: https://issues.apache.org/jira/browse/CALCITE-5663
> Project: Calcite
> Issue Type: Bug
>Reporter: Marieke Gueye
>Priority: Minor
>
> In RelOptFixture, we currently change the traitsets to enforce
> EnumerableConvention.INSTANCE, however, in this instance we forget to port
> the collation in the traitset.
> {code:java}
> if (planner instanceof VolcanoPlanner)
> { r2 = planner.changeTraits(relBefore,
> relBefore.getTraitSet().replace(EnumerableConvention.INSTANCE)); }
> else
> { r2 = relBefore; }
>
> {code}
> The problem goes even deeper, as of now, there is no way to get access to the
> collation as it lives in the relRoot, and we currently only access the
> relNode through the relSupplier
>
> The consequence of this can be pretty dire as it may mean that some rules
> might be incorrectly tested.
>
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-5663) [TestKit] RelOptFixture does not enforce the collation
Marieke Gueye created CALCITE-5663:
--
Summary: [TestKit] RelOptFixture does not enforce the collation
Key: CALCITE-5663
URL: https://issues.apache.org/jira/browse/CALCITE-5663
Project: Calcite
Issue Type: Bug
Reporter: Marieke Gueye
In RelOptFixture, we currently change the traitsets to enforce
EnumerableConvention.INSTANCE, however, in this instance we forget to port the
collation in the traitset.
```
if (planner instanceof VolcanoPlanner) {
r2 =
planner.changeTraits(relBefore,
relBefore.getTraitSet().replace(EnumerableConvention.INSTANCE));
} else {
r2 = relBefore;
}
```
The problem goes even deeper, as of now, there is no way to get access to the
collation as it lives in the relRoot, and we currently only access the relNode
through the `relSupplier`
The consequence of this can be pretty dire as it may mean that some rules might
be incorrectly tested.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Resolved] (CALCITE-111) Support CONVERT function, for changing character sets
[
https://issues.apache.org/jira/browse/CALCITE-111?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde resolved CALCITE-111.
-
Resolution: Fixed
Fixed in
[b2917b33|https://github.com/apache/calcite/commit/b2917b33e8dda434d2866d8a327f8a48520afb48];
thanks for the PR, [~VAE]!
> Support CONVERT function, for changing character sets
> -
>
> Key: CALCITE-111
> URL: https://issues.apache.org/jira/browse/CALCITE-111
> Project: Calcite
> Issue Type: Improvement
>Reporter: GitHub Import
>Assignee: ZheHu
>Priority: Major
> Labels: github-import, pull-request-available
> Fix For: 1.35.0
>
> Time Spent: 40m
> Remaining Estimate: 0h
>
> Support the CONVERT function, to convert strings from one character set to
> another.
> CONVERT is defined in SQL:2011 as a feature called {{ transliteration>}}. Here is an example:
> {code}
> SELECT * FROM emp WHERE CONVERT(name USING UTF16) = u&'\82f1\56fd'
> {code}
> The [MySQL
> site|https://dev.mysql.com/doc/refman/8.0/en/cast-functions.html#function_convert]
> has other examples.
> {noformat}
> Imported from GitHub
> Url: https://github.com/julianhyde/optiq/issues/111
> Created by: [julianhyde|https://github.com/julianhyde]
> Labels: enhancement,
> Created at: Tue Jan 14 03:40:53 CET 2014
> State: open
> {noformat}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-5642) Add SHA256, SHA512 functions
[
https://issues.apache.org/jira/browse/CALCITE-5642?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17714398#comment-17714398
]
Dan Zou edited comment on CALCITE-5642 at 4/20/23 4:03 AM:
---
[~njiang] Thanks for the information, and I found a Q&A in the docs [Handling
cryptography within an ASF release|https://infra.apache.org/crypto.html].
{quote}Q: Do digest algorithms such as MD5 and SHA1 require notification?
A: No. One-way algorithms such as MD5 or SHA1, or more sophisticated
implementations, do not require notification. Only encryption algorithms do.
{quote}
And also, the tool 'DigestUtils' is imported from 'Apache Commons Codec' which
is not listed in [this doc|https://www.apache.org/licenses/exports/].
According to all of these, I think there is no risk of violating export control
restrictions.
was (Author: frankzou):
[~njiang] Thanks for the information, and I found a Q&A in the docs [Handling
cryptography within an ASF release|https://infra.apache.org/crypto.html].
{quote}Q: Do digest algorithms such as MD5 and SHA1 require notification?
A: No. One-way algorithms such as MD5 or SHA1, or more sophisticated
implementations, do not require notification. Only encryption algorithms do.
{quote}
According to this, I think there is no risk of violating export control
restrictions.
> Add SHA256, SHA512 functions
>
>
> Key: CALCITE-5642
> URL: https://issues.apache.org/jira/browse/CALCITE-5642
> Project: Calcite
> Issue Type: New Feature
>Reporter: Dan Zou
>Assignee: Dan Zou
>Priority: Major
> Labels: pull-request-available
> Time Spent: 0.5h
> Remaining Estimate: 0h
>
> Add SHA256, SHA512 functions and enable them for BigQuery.
> SHA256: Computes the hash of the input using the [SHA-256
> algorithm|https://en.wikipedia.org/wiki/SHA-2]. This function returns 32
> bytes.
> SHA512: Computes the hash of the input using the [SHA-512
> algorithm|https://en.wikipedia.org/wiki/SHA-2]. This function returns 64
> bytes.
> See more details in [BigQuery
> Doc|https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#sha256]
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-5642) Add SHA256, SHA512 functions
[
https://issues.apache.org/jira/browse/CALCITE-5642?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17714398#comment-17714398
]
Dan Zou edited comment on CALCITE-5642 at 4/20/23 3:55 AM:
---
[~njiang] Thanks for the information, and I found a Q&A in the docs [Handling
cryptography within an ASF release|https://infra.apache.org/crypto.html].
{quote}Q: Do digest algorithms such as MD5 and SHA1 require notification?
A: No. One-way algorithms such as MD5 or SHA1, or more sophisticated
implementations, do not require notification. Only encryption algorithms do.
{quote}
According to this, I think there is no risk of violating export control
restrictions.
was (Author: frankzou):
[~njiang] Thanks for the information, and I found a Q&A in the docs [Handling
cryptography within an ASF release|https://infra.apache.org/crypto.html].
{quote}
Do digest algorithms such as MD5 and SHA1 require notification?
No. One-way algorithms such as MD5 or SHA1, or more sophisticated
implementations, do not require notification. Only encryption algorithms do.
{quote}
According to this, I think there is no risk of violating export control
restrictions.
> Add SHA256, SHA512 functions
>
>
> Key: CALCITE-5642
> URL: https://issues.apache.org/jira/browse/CALCITE-5642
> Project: Calcite
> Issue Type: New Feature
>Reporter: Dan Zou
>Assignee: Dan Zou
>Priority: Major
> Labels: pull-request-available
> Time Spent: 0.5h
> Remaining Estimate: 0h
>
> Add SHA256, SHA512 functions and enable them for BigQuery.
> SHA256: Computes the hash of the input using the [SHA-256
> algorithm|https://en.wikipedia.org/wiki/SHA-2]. This function returns 32
> bytes.
> SHA512: Computes the hash of the input using the [SHA-512
> algorithm|https://en.wikipedia.org/wiki/SHA-2]. This function returns 64
> bytes.
> See more details in [BigQuery
> Doc|https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#sha256]
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5642) Add SHA256, SHA512 functions
[
https://issues.apache.org/jira/browse/CALCITE-5642?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17714398#comment-17714398
]
Dan Zou commented on CALCITE-5642:
--
[~njiang] Thanks for the information, and I found a Q&A in the docs [Handling
cryptography within an ASF release|https://infra.apache.org/crypto.html].
{quote}
Do digest algorithms such as MD5 and SHA1 require notification?
No. One-way algorithms such as MD5 or SHA1, or more sophisticated
implementations, do not require notification. Only encryption algorithms do.
{quote}
According to this, I think there is no risk of violating export control
restrictions.
> Add SHA256, SHA512 functions
>
>
> Key: CALCITE-5642
> URL: https://issues.apache.org/jira/browse/CALCITE-5642
> Project: Calcite
> Issue Type: New Feature
>Reporter: Dan Zou
>Assignee: Dan Zou
>Priority: Major
> Labels: pull-request-available
> Time Spent: 0.5h
> Remaining Estimate: 0h
>
> Add SHA256, SHA512 functions and enable them for BigQuery.
> SHA256: Computes the hash of the input using the [SHA-256
> algorithm|https://en.wikipedia.org/wiki/SHA-2]. This function returns 32
> bytes.
> SHA512: Computes the hash of the input using the [SHA-512
> algorithm|https://en.wikipedia.org/wiki/SHA-2]. This function returns 64
> bytes.
> See more details in [BigQuery
> Doc|https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#sha256]
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5642) Add SHA256, SHA512 functions
[ https://issues.apache.org/jira/browse/CALCITE-5642?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17714389#comment-17714389 ] Willem Jiang commented on CALCITE-5642: --- [~FrankZou] Please check out [handling cryptography within an ASF release|https://infra.apache.org/crypto.html ] for more information about the cryptography export control. I just checked the changes you made. DigestUtils leverage some tools to do the digesting work. You may double-check if it uses the Bouncy Castle library as [this page|https://www.apache.org/licenses/exports/] shows. > Add SHA256, SHA512 functions > > > Key: CALCITE-5642 > URL: https://issues.apache.org/jira/browse/CALCITE-5642 > Project: Calcite > Issue Type: New Feature >Reporter: Dan Zou >Assignee: Dan Zou >Priority: Major > Labels: pull-request-available > Time Spent: 0.5h > Remaining Estimate: 0h > > Add SHA256, SHA512 functions and enable them for BigQuery. > SHA256: Computes the hash of the input using the [SHA-256 > algorithm|https://en.wikipedia.org/wiki/SHA-2]. This function returns 32 > bytes. > SHA512: Computes the hash of the input using the [SHA-512 > algorithm|https://en.wikipedia.org/wiki/SHA-2]. This function returns 64 > bytes. > See more details in [BigQuery > Doc|https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#sha256] -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-5642) Add SHA256, SHA512 functions
[ https://issues.apache.org/jira/browse/CALCITE-5642?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17714378#comment-17714378 ] Dan Zou commented on CALCITE-5642: -- It seems that I have no permission to tag anyone as a reviewer, is this expected behavior? > Add SHA256, SHA512 functions > > > Key: CALCITE-5642 > URL: https://issues.apache.org/jira/browse/CALCITE-5642 > Project: Calcite > Issue Type: New Feature >Reporter: Dan Zou >Assignee: Dan Zou >Priority: Major > Labels: pull-request-available > Time Spent: 10m > Remaining Estimate: 0h > > Add SHA256, SHA512 functions and enable them for BigQuery. > SHA256: Computes the hash of the input using the [SHA-256 > algorithm|https://en.wikipedia.org/wiki/SHA-2]. This function returns 32 > bytes. > SHA512: Computes the hash of the input using the [SHA-512 > algorithm|https://en.wikipedia.org/wiki/SHA-2]. This function returns 64 > bytes. > See more details in [BigQuery > Doc|https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#sha256] -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-5642) Add SHA256, SHA512 functions
[ https://issues.apache.org/jira/browse/CALCITE-5642?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17714373#comment-17714373 ] Dan Zou commented on CALCITE-5642: -- [~tanclary] I have opened a PR for this, please help review it if you are interested in it. > Add SHA256, SHA512 functions > > > Key: CALCITE-5642 > URL: https://issues.apache.org/jira/browse/CALCITE-5642 > Project: Calcite > Issue Type: New Feature >Reporter: Dan Zou >Assignee: Dan Zou >Priority: Major > Labels: pull-request-available > Time Spent: 10m > Remaining Estimate: 0h > > Add SHA256, SHA512 functions and enable them for BigQuery. > SHA256: Computes the hash of the input using the [SHA-256 > algorithm|https://en.wikipedia.org/wiki/SHA-2]. This function returns 32 > bytes. > SHA512: Computes the hash of the input using the [SHA-512 > algorithm|https://en.wikipedia.org/wiki/SHA-2]. This function returns 64 > bytes. > See more details in [BigQuery > Doc|https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#sha256] -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-5449) Allow EXTRACT() to accept ISOWEEK and WEEK(WEEKDAY)
[
https://issues.apache.org/jira/browse/CALCITE-5449?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17714355#comment-17714355
]
TJ Banghart commented on CALCITE-5449:
--
We could keep these as custom time unit if we had a way to unparse them as time
unit identifiers. They are treated as string literals so something like
{{MINUTE15}} would unparse as {{'MINUTE15'}}.
I'm not sure how we'd translate something like {{MINUTE15}} into an equivalent
expression the target dialect would recognize but it would be a pretty neat
feature for Calcite. The translation from {{ISOWEEK}} and {{WEEK(WEEKDAY)}} to
SQL would be simpler as those are more common - and can be solved by overriding
{{unparseCall}} for the dialect.
I'd imagine custom timeframes to SQL translation warrants its own Jira case.
> Allow EXTRACT() to accept ISOWEEK and WEEK(WEEKDAY)
> ---
>
> Key: CALCITE-5449
> URL: https://issues.apache.org/jira/browse/CALCITE-5449
> Project: Calcite
> Issue Type: Sub-task
>Reporter: Tanner Clary
>Assignee: Tanner Clary
>Priority: Major
> Labels: pull-request-available
> Time Spent: 1h 10m
> Remaining Estimate: 0h
>
> Implement BigQuery {{EXTRACT()}} Function. {{EXTRACT(part FROM
> date_expression)}} returns the value from the date_expression corresponding
> to the specified part. Calcite currently supports PostgreSQL' {{DATE_PART}}
> which has similar behavior.
> Example: {{SELECT EXTRACT(DAY FROM DATE '2013-12-25')}} would return 25.
> [BigQuery
> Docs|https://g3doc.corp.google.com/company/teams/googlesql/reference/date_functions.md#extract
> ]
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-5449) Allow EXTRACT() to accept ISOWEEK and WEEK(WEEKDAY)
[
https://issues.apache.org/jira/browse/CALCITE-5449?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17714355#comment-17714355
]
TJ Banghart edited comment on CALCITE-5449 at 4/20/23 1:09 AM:
---
We could keep these as custom time unit if we had a way to unparse them as time
unit identifiers. They are treated as string literals so something like
{{MINUTE15}} would unparse as {{'MINUTE15'}}.
I'm not sure how we'd translate something like {{MINUTE15}} into an equivalent
expression the target dialect would recognize but it would be a pretty neat
feature for Calcite. The translation from {{ISOWEEK}} and {{WEEK(WEEKDAY)}} to
SQL would be simpler as those are more common and can be solved by overriding
{{unparseCall}} for the dialect.
I'd imagine custom timeframes to SQL translation warrants its own Jira case.
was (Author: tjbanghart):
We could keep these as custom time unit if we had a way to unparse them as time
unit identifiers. They are treated as string literals so something like
{{MINUTE15}} would unparse as {{'MINUTE15'}}.
I'm not sure how we'd translate something like {{MINUTE15}} into an equivalent
expression the target dialect would recognize but it would be a pretty neat
feature for Calcite. The translation from {{ISOWEEK}} and {{WEEK(WEEKDAY)}} to
SQL would be simpler as those are more common - and can be solved by overriding
{{unparseCall}} for the dialect.
I'd imagine custom timeframes to SQL translation warrants its own Jira case.
> Allow EXTRACT() to accept ISOWEEK and WEEK(WEEKDAY)
> ---
>
> Key: CALCITE-5449
> URL: https://issues.apache.org/jira/browse/CALCITE-5449
> Project: Calcite
> Issue Type: Sub-task
>Reporter: Tanner Clary
>Assignee: Tanner Clary
>Priority: Major
> Labels: pull-request-available
> Time Spent: 1h 10m
> Remaining Estimate: 0h
>
> Implement BigQuery {{EXTRACT()}} Function. {{EXTRACT(part FROM
> date_expression)}} returns the value from the date_expression corresponding
> to the specified part. Calcite currently supports PostgreSQL' {{DATE_PART}}
> which has similar behavior.
> Example: {{SELECT EXTRACT(DAY FROM DATE '2013-12-25')}} would return 25.
> [BigQuery
> Docs|https://g3doc.corp.google.com/company/teams/googlesql/reference/date_functions.md#extract
> ]
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-4698) Respect input types and precision for datetime_plus and timestampadd
[
https://issues.apache.org/jira/browse/CALCITE-4698?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17714345#comment-17714345
]
Julian Hyde commented on CALCITE-4698:
--
Re. your question, I don't have a strong opinion. Add tests where you think
coverage is weak, and organize (or reorganize) tests however makes sense.
> Respect input types and precision for datetime_plus and timestampadd
>
>
> Key: CALCITE-4698
> URL: https://issues.apache.org/jira/browse/CALCITE-4698
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Caizhi Weng
>Priority: Major
> Labels: pull-request-available
> Time Spent: 1h 50m
> Remaining Estimate: 0h
>
> Currently {{SqlTimestampAddFunction#deduceType}} does not deal with
> timestamp_ltz type correctly. Return type of {{timestampadd(..., ...,
> timestamp_ltz)}} will be timestamp but it should be timestamp_ltz.
> Same issue is for precision datetime_plus.
> Also precision is lost.
> A number of queries highlighting the issue
> {code:sql}
> SELECT timestamp '2003-08-02 12:54:01' - INTERVAL '1.123' SECOND(1, 3);
> SELECT time '12:12:12' + INTERVAL '3.456' SECOND(1, 3);
> SELECT timestampadd(SQL_TSI_FRAC_SECOND, 2, timestamp with local time zone
> '2016-02-24 12:42:25.000');
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5611) Show SQL query for failed tests for SqlOperatorTest
[
https://issues.apache.org/jira/browse/CALCITE-5611?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17714344#comment-17714344
]
Julian Hyde commented on CALCITE-5611:
--
Looks good.
Do you think there would be any benefit to some or all of the arguments being
{{Supplier}} rather than {{String}}? Maybe it would reduce the
profusion of {{"Query: " + sql}} arguments.
I'll leave it up to you. LGTM as is.
> Show SQL query for failed tests for SqlOperatorTest
> ---
>
> Key: CALCITE-5611
> URL: https://issues.apache.org/jira/browse/CALCITE-5611
> Project: Calcite
> Issue Type: Improvement
> Components: tests
>Affects Versions: 1.34.0
>Reporter: Sergey Nuyanzin
>Priority: Major
> Labels: pull-request-available
> Time Spent: 1h 20m
> Remaining Estimate: 0h
>
> The issue is that there are lots of cases under each test in
> {{SqlOperatorTest}} and it is not clear which of them is failing.
> The idea is to print sql query from the test if it's failed besides
> type/result comparison
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5543) Implement BigQuery functions for parsing DATE, TIME, TIMESTAMP, DATETIME
[ https://issues.apache.org/jira/browse/CALCITE-5543?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17714340#comment-17714340 ] TJ Banghart commented on CALCITE-5543: -- Took longer for me to get around to this than I would have liked but here's a first pass on an implementation: https://github.com/apache/calcite/pull/3132/ > Implement BigQuery functions for parsing DATE, TIME, TIMESTAMP, DATETIME > > > Key: CALCITE-5543 > URL: https://issues.apache.org/jira/browse/CALCITE-5543 > Project: Calcite > Issue Type: Wish >Reporter: Marieke Gueye >Assignee: TJ Banghart >Priority: Major > Labels: pull-request-available > Time Spent: 20m > Remaining Estimate: 0h > > Implement PARSE_DATE, PARSE_DATETIME, PARSE_TIME, PARSE_TIMESTAMP functions, > as specified by BigQuery. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-5662) CAST(BOOLEAN as INTEGER) throws a NumberFormatException
[
https://issues.apache.org/jira/browse/CALCITE-5662?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17714335#comment-17714335
]
Oliver Lee edited comment on CALCITE-5662 at 4/19/23 11:13 PM:
---
I've fixed the title of the JIRA ticket.
I have a PR to enable it but I just saw your comment about the SQL standard, I
went to verify and yes, the current behavior follows that (not allowing
cast(boolean as integer)
(PR for reference: https://github.com/apache/calcite/pull/3168)
Let me look into how we can toggle whether to enable it for certain dialects,
I'll comment again when I have the final PR ready
was (Author: JIRAUSER297744):
I've fixed the title of the JIRA ticket.
I have a PR to enable it but I just saw your comment about the SQL standard, I
went to verify and yes, the current behavior follows that.
(PR for reference: https://github.com/apache/calcite/pull/3168)
Let me look into how we can toggle whether to enable it for certain dialects,
I'll comment again when I have the final PR ready
> CAST(BOOLEAN as INTEGER) throws a NumberFormatException
> ---
>
> Key: CALCITE-5662
> URL: https://issues.apache.org/jira/browse/CALCITE-5662
> Project: Calcite
> Issue Type: Bug
>Reporter: Oliver Lee
>Assignee: Oliver Lee
>Priority: Minor
> Labels: pull-request-available
> Time Spent: 10m
> Remaining Estimate: 0h
>
> Currently attempting to run {{SELECT CAST(BOOLEAN as INTEGER)}} will throw a
> {{NumberFormatException}}.
> The BigQuery dialect allows casting {{boolean}} literals to the following:
> {{string}}, {{int64}} , {{tinyint}}, {{smallint}}, {{bigint}}
> src:
> https://cloud.google.com/bigquery/docs/reference/standard-sql/conversion_functions
>
> Desired behavior:
> {{SELECT CAST(TRUE as INTEGER)}} -> {{1}}
> {{SELECT CAST(TRUE as BIGINT)}} -> {{1}}
> {{SELECT CAST(FALSE as INTEGER)}} -> {{0}}
> {{SELECT CAST(null as INTEGER)}} -> {{null}}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5662) CAST(BOOLEAN as INTEGER) throws a NumberFormatException
[
https://issues.apache.org/jira/browse/CALCITE-5662?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17714335#comment-17714335
]
Oliver Lee commented on CALCITE-5662:
-
I've fixed the title of the JIRA ticket.
I have a PR to enable it but I just saw your comment about the SQL standard, I
went to verify and yes, the current behavior follows that.
(PR for reference: https://github.com/apache/calcite/pull/3168)
Let me look into how we can toggle whether to enable it for certain dialects,
I'll comment again when I have the final PR ready
> CAST(BOOLEAN as INTEGER) throws a NumberFormatException
> ---
>
> Key: CALCITE-5662
> URL: https://issues.apache.org/jira/browse/CALCITE-5662
> Project: Calcite
> Issue Type: Bug
>Reporter: Oliver Lee
>Assignee: Oliver Lee
>Priority: Minor
> Labels: pull-request-available
> Time Spent: 10m
> Remaining Estimate: 0h
>
> Currently attempting to run {{SELECT CAST(BOOLEAN as INTEGER)}} will throw a
> {{NumberFormatException}}.
> The BigQuery dialect allows casting {{boolean}} literals to the following:
> {{string}}, {{int64}} , {{tinyint}}, {{smallint}}, {{bigint}}
> src:
> https://cloud.google.com/bigquery/docs/reference/standard-sql/conversion_functions
>
> Desired behavior:
> {{SELECT CAST(TRUE as INTEGER)}} -> {{1}}
> {{SELECT CAST(TRUE as BIGINT)}} -> {{1}}
> {{SELECT CAST(FALSE as INTEGER)}} -> {{0}}
> {{SELECT CAST(null as INTEGER)}} -> {{null}}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-5662) CAST(BOOLEAN as INTEGER) throws a NumberFormatException
[
https://issues.apache.org/jira/browse/CALCITE-5662?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Oliver Lee updated CALCITE-5662:
Description:
Currently attempting to run {{SELECT CAST(BOOLEAN as INTEGER)}} will throw a
{{NumberFormatException}}.
The BigQuery dialect allows casting {{boolean}} literals to the following:
{{string}}, {{int64}} , {{tinyint}}, {{smallint}}, {{bigint}}
src:
https://cloud.google.com/bigquery/docs/reference/standard-sql/conversion_functions
Desired behavior:
{{SELECT CAST(TRUE as INTEGER)}} -> {{1}}
{{SELECT CAST(TRUE as BIGINT)}} -> {{1}}
{{SELECT CAST(FALSE as INTEGER)}} -> {{0}}
{{SELECT CAST(null as INTEGER)}} -> {{null}}
was:
Currently attempting to run {{SELECT CAST(BOOLEAN as INTEGER)}} will throw a
{{NumberFormatException}}.
The BigQuery dialect allows casting {{boolean}} to the following: {{string}},
{{int64}} , {{tinyint}}, {{smallint}}, {{bigint}}
src:
https://cloud.google.com/bigquery/docs/reference/standard-sql/conversion_functions
Desired behavior:
{{SELECT CAST(TRUE as INTEGER)}} -> {{1}}
{{SELECT CAST(TRUE as BIGINT)}} -> {{1}}
{{SELECT CAST(FALSE as INTEGER)}} -> {{0}}
{{SELECT CAST(null as INTEGER)}} -> {{null}}
> CAST(BOOLEAN as INTEGER) throws a NumberFormatException
> ---
>
> Key: CALCITE-5662
> URL: https://issues.apache.org/jira/browse/CALCITE-5662
> Project: Calcite
> Issue Type: Bug
>Reporter: Oliver Lee
>Assignee: Oliver Lee
>Priority: Minor
> Labels: pull-request-available
> Time Spent: 10m
> Remaining Estimate: 0h
>
> Currently attempting to run {{SELECT CAST(BOOLEAN as INTEGER)}} will throw a
> {{NumberFormatException}}.
> The BigQuery dialect allows casting {{boolean}} literals to the following:
> {{string}}, {{int64}} , {{tinyint}}, {{smallint}}, {{bigint}}
> src:
> https://cloud.google.com/bigquery/docs/reference/standard-sql/conversion_functions
>
> Desired behavior:
> {{SELECT CAST(TRUE as INTEGER)}} -> {{1}}
> {{SELECT CAST(TRUE as BIGINT)}} -> {{1}}
> {{SELECT CAST(FALSE as INTEGER)}} -> {{0}}
> {{SELECT CAST(null as INTEGER)}} -> {{null}}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-5662) CAST(BOOLEAN as INTEGER) throws a NumberFormatException
[
https://issues.apache.org/jira/browse/CALCITE-5662?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ASF GitHub Bot updated CALCITE-5662:
Labels: pull-request-available (was: )
> CAST(BOOLEAN as INTEGER) throws a NumberFormatException
> ---
>
> Key: CALCITE-5662
> URL: https://issues.apache.org/jira/browse/CALCITE-5662
> Project: Calcite
> Issue Type: Bug
>Reporter: Oliver Lee
>Assignee: Oliver Lee
>Priority: Minor
> Labels: pull-request-available
> Time Spent: 10m
> Remaining Estimate: 0h
>
> Currently attempting to run {{SELECT CAST(BOOLEAN as INTEGER)}} will throw a
> {{NumberFormatException}}.
> The BigQuery dialect allows casting {{boolean}} to the following:
> {{string}}, {{int64}} , {{tinyint}}, {{smallint}}, {{bigint}}
> src:
> https://cloud.google.com/bigquery/docs/reference/standard-sql/conversion_functions
>
> Desired behavior:
> {{SELECT CAST(TRUE as INTEGER)}} -> {{1}}
> {{SELECT CAST(TRUE as BIGINT)}} -> {{1}}
> {{SELECT CAST(FALSE as INTEGER)}} -> {{0}}
> {{SELECT CAST(null as INTEGER)}} -> {{null}}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5662) CAST(BOOLEAN as INTEGER) throws a NumberFormatException
[
https://issues.apache.org/jira/browse/CALCITE-5662?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17714329#comment-17714329
]
Julian Hyde commented on CALCITE-5662:
--
The current behavior is correct per the SQL standard. (Actually we should give
a validation error, rather than throwing NumberFormatException.)
If you allow the type conversions above it should be enabled via some flag. I'm
not sure whether conformance or type system is the right place to put that
flag. Maybe you can suggest something.
You will need to update the [SQL
reference|https://calcite.apache.org/docs/reference.html#implicit-type-conversion].
> CAST(BOOLEAN as INTEGER) throws a NumberFormatException
> ---
>
> Key: CALCITE-5662
> URL: https://issues.apache.org/jira/browse/CALCITE-5662
> Project: Calcite
> Issue Type: Bug
>Reporter: Oliver Lee
>Assignee: Oliver Lee
>Priority: Minor
>
> Currently attempting to run {{SELECT CAST(BOOLEAN as INTEGER)}} will throw a
> {{NumberFormatException}}.
> The BigQuery dialect allows casting {{boolean}} to the following:
> {{string}}, {{int64}} , {{tinyint}}, {{smallint}}, {{bigint}}
> src:
> https://cloud.google.com/bigquery/docs/reference/standard-sql/conversion_functions
>
> Desired behavior:
> {{SELECT CAST(TRUE as INTEGER)}} -> {{1}}
> {{SELECT CAST(TRUE as BIGINT)}} -> {{1}}
> {{SELECT CAST(FALSE as INTEGER)}} -> {{0}}
> {{SELECT CAST(null as INTEGER)}} -> {{null}}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5449) Allow EXTRACT() to accept ISOWEEK and WEEK(WEEKDAY)
[
https://issues.apache.org/jira/browse/CALCITE-5449?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17714326#comment-17714326
]
Julian Hyde commented on CALCITE-5449:
--
If we did, we would probably want the 7 'WEEK(weekday)' units as well (or maybe
5, since Sunday and Monday are already covered). And we would have to wait for
a new release of Avatica. If you're confident that this would solve the
problem, let's do it.
I have a small concern that if we make ISOWEEK and WEEK(weekday) official units
then custom time units will be left out in the cold - say, some database
supports QUARTER and another doesn't.
> Allow EXTRACT() to accept ISOWEEK and WEEK(WEEKDAY)
> ---
>
> Key: CALCITE-5449
> URL: https://issues.apache.org/jira/browse/CALCITE-5449
> Project: Calcite
> Issue Type: Sub-task
>Reporter: Tanner Clary
>Assignee: Tanner Clary
>Priority: Major
> Labels: pull-request-available
> Time Spent: 1h 10m
> Remaining Estimate: 0h
>
> Implement BigQuery {{EXTRACT()}} Function. {{EXTRACT(part FROM
> date_expression)}} returns the value from the date_expression corresponding
> to the specified part. Calcite currently supports PostgreSQL' {{DATE_PART}}
> which has similar behavior.
> Example: {{SELECT EXTRACT(DAY FROM DATE '2013-12-25')}} would return 25.
> [BigQuery
> Docs|https://g3doc.corp.google.com/company/teams/googlesql/reference/date_functions.md#extract
> ]
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5646) JoinDeriveIsNotNullFilterRule incorrectly handles COALESCE in join condition
[
https://issues.apache.org/jira/browse/CALCITE-5646?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17714324#comment-17714324
]
Julian Hyde commented on CALCITE-5646:
--
You're right, I was getting confused about the details of this case.
However, my role here is to make sure that the high-level design of Calcite
goes in the right direction. You imply that "Strong ... is out of scope of this
case". That is not true. This case relates to null semantics, null semantics
are reasoned about by the Strong class, and a good fix will leave the Strong
class in a better state.
You pointed to one method in Strong that was poorly documented and said - in
essence - "this class is shit, so I'm not going to use it". Sorry, you can't do
that. The Strong class is the right place for this functionality. You need to
make it work.
I just don't have time to get into the details. I have reviewed probably a
dozen other cases today. As you've noticed, when I get into the details, I make
mistakes, like confusing IS NULL with IS NOT NULL. But I am right about the
high-level abstraction, the Strong class. As part of this fix, you need to
extend and fix that abstraction.
> JoinDeriveIsNotNullFilterRule incorrectly handles COALESCE in join condition
>
>
> Key: CALCITE-5646
> URL: https://issues.apache.org/jira/browse/CALCITE-5646
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.34.0
>Reporter: Leonid Chistov
>Assignee: Leonid Chistov
>Priority: Major
> Labels: pull-request-available
> Time Spent: 20m
> Remaining Estimate: 0h
>
> Consider query
> {code:java}
> select t1.deptno from empnullables t1 inner join
> empnullables t2 on coalesce(t1.ename, t2.ename) = 'abc' {code}
> When JoinDeriveIsNotNullFilterRule is applied to it, it is incorrectly
> transformed to query plan
> {code:java}
> LogicalProject(DEPTNO=[$7])
> LogicalJoin(condition=[=(CASE(IS NOT NULL($1), $1, $10), 'abc')],
> joinType=[inner])
> LogicalFilter(condition=[IS NOT NULL($1)])
> LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
> LogicalFilter(condition=[IS NOT NULL($1)])
> LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]]) {code}
> It is not valid to deduce that join keys from the both sides cannot have null
> values. All that we can deduce from the join condition, is that they cannot
> be null in the same time.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-5662) CAST(BOOLEAN as INTEGER) throws a NumberFormatException
[
https://issues.apache.org/jira/browse/CALCITE-5662?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Oliver Lee updated CALCITE-5662:
Description:
Currently attempting to run {{SELECT CAST(BOOLEAN as INTEGER)}} will throw a
{{NumberFormatException}}.
The BigQuery dialect allows casting {{boolean}} to the following: {{string}},
{{int64}} , {{tinyint}}, {{smallint}}, {{bigint}}
src:
https://cloud.google.com/bigquery/docs/reference/standard-sql/conversion_functions
Desired behavior:
{{SELECT CAST(TRUE as INTEGER)}} -> {{1}}
{{SELECT CAST(TRUE as BIGINT)}} -> {{1}}
{{SELECT CAST(FALSE as INTEGER)}} -> {{0}}
{{SELECT CAST(null as INTEGER)}} -> {{null}}
was:
Currently attempting to run {{SELECT CAST(BOOLEAN as INTEGER)}} will throw a
{{NumberFormatException}}.
The BigQuery dialect allows casting {{boolean}} to {{int64}} and {{string}},
and not for decimal, bigdecimal, numeric, etc.
src:
https://cloud.google.com/bigquery/docs/reference/standard-sql/conversion_functions
Desired behavior:
{{SELECT CAST(TRUE as INTEGER)}} -> {{1}}
{{SELECT CAST(FALSE as INTEGER)}} -> {{0}}
{{SELECT CAST(null as INTEGER)}} -> {{null}}
> CAST(BOOLEAN as INTEGER) throws a NumberFormatException
> ---
>
> Key: CALCITE-5662
> URL: https://issues.apache.org/jira/browse/CALCITE-5662
> Project: Calcite
> Issue Type: Bug
>Reporter: Oliver Lee
>Assignee: Oliver Lee
>Priority: Minor
>
> Currently attempting to run {{SELECT CAST(BOOLEAN as INTEGER)}} will throw a
> {{NumberFormatException}}.
> The BigQuery dialect allows casting {{boolean}} to the following:
> {{string}}, {{int64}} , {{tinyint}}, {{smallint}}, {{bigint}}
> src:
> https://cloud.google.com/bigquery/docs/reference/standard-sql/conversion_functions
>
> Desired behavior:
> {{SELECT CAST(TRUE as INTEGER)}} -> {{1}}
> {{SELECT CAST(TRUE as BIGINT)}} -> {{1}}
> {{SELECT CAST(FALSE as INTEGER)}} -> {{0}}
> {{SELECT CAST(null as INTEGER)}} -> {{null}}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-5660) Implement BigQuery Array Subscript Operators
[
https://issues.apache.org/jira/browse/CALCITE-5660?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ASF GitHub Bot updated CALCITE-5660:
Labels: pull-request-available (was: )
> Implement BigQuery Array Subscript Operators
>
>
> Key: CALCITE-5660
> URL: https://issues.apache.org/jira/browse/CALCITE-5660
> Project: Calcite
> Issue Type: Task
>Reporter: Tanner Clary
>Assignee: Tanner Clary
>Priority: Major
> Labels: pull-request-available
> Time Spent: 10m
> Remaining Estimate: 0h
>
> When indexing an array in BigQuery, you are required to wrap the index in one
> of four position keywords: {{OFFSET}}, {{ORDINAL}}, {{SAFE_OFFSET}}, and
> {{SAFE_ORDINAL}}.
> {{OFFSET(index)}}: The index starts at zero. Produces an error if the index
> is out of range.
> {{SAFE_OFFSET(index)}}: The index starts at zero. Returns NULL if the index
> is out of range.
> {{ORDINAL(index)}}: The index starts at one. Produces an error if the index
> is out of range.
> {{SAFE_ORDINAL(index)}}: The index starts at one. Returns NULL if the index
> is out of range.
> These operators are very similar to the existing {{ITEM}} operator currently
> used to index an array in Calcite. I believe the offset and out of bounds
> behavior for the existing implementation could be parameterized to support
> the above operators.
> [Link to BigQuery
> docs|https://cloud.google.com/bigquery/docs/reference/standard-sql/operators#array_subscript_operator]
> I will have a PR open for this in the next day or two, in the meantime any
> comments, questions, or suggestions are welcome as always.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-5646) JoinDeriveIsNotNullFilterRule incorrectly handles COALESCE in join condition
[
https://issues.apache.org/jira/browse/CALCITE-5646?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17714124#comment-17714124
]
Leonid Chistov edited comment on CALCITE-5646 at 4/19/23 9:11 PM:
--
[~zabetak]
I think that if we know, that setting input *I* (of some inner join predicate
{*}JP{*}) to NULL will make *JP* equal to either NULL or FALSE, we may conclude
that we can push down IS NOT NULL check.
I don't see a difference here between cases when *JP* turns NULL or FALSE, I
think that previous implementation just missed that opportunity.
{*}UPDATE{*}: Having said this, I cannot come up with join condition that turns
to FALSE and does not look stupid.
Example would be something like coalesce(x, FALSE) or coalesce(a.x * b.y,
FALSE).
was (Author: JIRAUSER298393):
[~zabetak]
I think that if we know, that setting input *I* (of some inner join predicate
{*}JP{*}) to NULL will make *JP* equal to either NULL or FALSE, we may conclude
that we can push down IS NOT NULL check.
I don't see a difference here between cases when *JP* turns NULL or FALSE, I
think that previous implementation just missed that opportunity.
UPDATE: Having said this, I cannot come up with join condition that turns to
FALSE and does not look stupid.
Example would be something like coalesce(x, FALSE) or coalesce(a.x * b.y,
FALSE).
> JoinDeriveIsNotNullFilterRule incorrectly handles COALESCE in join condition
>
>
> Key: CALCITE-5646
> URL: https://issues.apache.org/jira/browse/CALCITE-5646
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.34.0
>Reporter: Leonid Chistov
>Assignee: Leonid Chistov
>Priority: Major
> Labels: pull-request-available
> Time Spent: 20m
> Remaining Estimate: 0h
>
> Consider query
> {code:java}
> select t1.deptno from empnullables t1 inner join
> empnullables t2 on coalesce(t1.ename, t2.ename) = 'abc' {code}
> When JoinDeriveIsNotNullFilterRule is applied to it, it is incorrectly
> transformed to query plan
> {code:java}
> LogicalProject(DEPTNO=[$7])
> LogicalJoin(condition=[=(CASE(IS NOT NULL($1), $1, $10), 'abc')],
> joinType=[inner])
> LogicalFilter(condition=[IS NOT NULL($1)])
> LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
> LogicalFilter(condition=[IS NOT NULL($1)])
> LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]]) {code}
> It is not valid to deduce that join keys from the both sides cannot have null
> values. All that we can deduce from the join condition, is that they cannot
> be null in the same time.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5646) JoinDeriveIsNotNullFilterRule incorrectly handles COALESCE in join condition
[
https://issues.apache.org/jira/browse/CALCITE-5646?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17714310#comment-17714310
]
Leonid Chistov commented on CALCITE-5646:
-
Sorry, I don't really understand your point. It looks like you propose new
potential optimization rule "pushdown of IS NULL filter from Join condition",
while this Jira Issue describes bug in "pushdown of IS NOT NULL filter from
Join condition"".
> JoinDeriveIsNotNullFilterRule incorrectly handles COALESCE in join condition
>
>
> Key: CALCITE-5646
> URL: https://issues.apache.org/jira/browse/CALCITE-5646
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.34.0
>Reporter: Leonid Chistov
>Assignee: Leonid Chistov
>Priority: Major
> Labels: pull-request-available
> Time Spent: 20m
> Remaining Estimate: 0h
>
> Consider query
> {code:java}
> select t1.deptno from empnullables t1 inner join
> empnullables t2 on coalesce(t1.ename, t2.ename) = 'abc' {code}
> When JoinDeriveIsNotNullFilterRule is applied to it, it is incorrectly
> transformed to query plan
> {code:java}
> LogicalProject(DEPTNO=[$7])
> LogicalJoin(condition=[=(CASE(IS NOT NULL($1), $1, $10), 'abc')],
> joinType=[inner])
> LogicalFilter(condition=[IS NOT NULL($1)])
> LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
> LogicalFilter(condition=[IS NOT NULL($1)])
> LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]]) {code}
> It is not valid to deduce that join keys from the both sides cannot have null
> values. All that we can deduce from the join condition, is that they cannot
> be null in the same time.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-5646) JoinDeriveIsNotNullFilterRule incorrectly handles COALESCE in join condition
[
https://issues.apache.org/jira/browse/CALCITE-5646?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17714124#comment-17714124
]
Leonid Chistov edited comment on CALCITE-5646 at 4/19/23 9:09 PM:
--
[~zabetak]
I think that if we know, that setting input *I* (of some inner join predicate
{*}JP{*}) to NULL will make *JP* equal to either NULL or FALSE, we may conclude
that we can push down IS NOT NULL check.
I don't see a difference here between cases when *JP* turns NULL or FALSE, I
think that previous implementation just missed that opportunity.
UPDATE: Having said this, I cannot come up with join condition that turns to
FALSE and does not look stupid.
Example would be something like coalesce(x, FALSE) or coalesce(a.x * b.y,
FALSE).
was (Author: JIRAUSER298393):
[~zabetak]
I think that if we know, that setting input *I* (of some inner join predicate
{*}JP{*}) to NULL will make *JP* equal to either NULL or FALSE, we may conclude
that we can push down IS NOT NULL check.
I don't see a difference here between cases when *JP* turns NULL or FALSE, I
think that previous implementation just missed that opportunity.
> JoinDeriveIsNotNullFilterRule incorrectly handles COALESCE in join condition
>
>
> Key: CALCITE-5646
> URL: https://issues.apache.org/jira/browse/CALCITE-5646
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.34.0
>Reporter: Leonid Chistov
>Assignee: Leonid Chistov
>Priority: Major
> Labels: pull-request-available
> Time Spent: 20m
> Remaining Estimate: 0h
>
> Consider query
> {code:java}
> select t1.deptno from empnullables t1 inner join
> empnullables t2 on coalesce(t1.ename, t2.ename) = 'abc' {code}
> When JoinDeriveIsNotNullFilterRule is applied to it, it is incorrectly
> transformed to query plan
> {code:java}
> LogicalProject(DEPTNO=[$7])
> LogicalJoin(condition=[=(CASE(IS NOT NULL($1), $1, $10), 'abc')],
> joinType=[inner])
> LogicalFilter(condition=[IS NOT NULL($1)])
> LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
> LogicalFilter(condition=[IS NOT NULL($1)])
> LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]]) {code}
> It is not valid to deduce that join keys from the both sides cannot have null
> values. All that we can deduce from the join condition, is that they cannot
> be null in the same time.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5449) Allow EXTRACT() to accept ISOWEEK and WEEK(WEEKDAY)
[
https://issues.apache.org/jira/browse/CALCITE-5449?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17714306#comment-17714306
]
Tanner Clary commented on CALCITE-5449:
---
[~julianhyde] As I revisit this, I am wondering whether ISOWEEK could be added
to the {{TimeUnit}} enum in Avatica. Other iso units (such is ISOYEAR and
ISODOW) are included so I believe it makes sense to add ISOWEEK. What do you
think?
> Allow EXTRACT() to accept ISOWEEK and WEEK(WEEKDAY)
> ---
>
> Key: CALCITE-5449
> URL: https://issues.apache.org/jira/browse/CALCITE-5449
> Project: Calcite
> Issue Type: Sub-task
>Reporter: Tanner Clary
>Assignee: Tanner Clary
>Priority: Major
> Labels: pull-request-available
> Time Spent: 1h 10m
> Remaining Estimate: 0h
>
> Implement BigQuery {{EXTRACT()}} Function. {{EXTRACT(part FROM
> date_expression)}} returns the value from the date_expression corresponding
> to the specified part. Calcite currently supports PostgreSQL' {{DATE_PART}}
> which has similar behavior.
> Example: {{SELECT EXTRACT(DAY FROM DATE '2013-12-25')}} would return 25.
> [BigQuery
> Docs|https://g3doc.corp.google.com/company/teams/googlesql/reference/date_functions.md#extract
> ]
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-5662) CAST(BOOLEAN as INTEGER) throws a NumberFormatException
[
https://issues.apache.org/jira/browse/CALCITE-5662?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Oliver Lee updated CALCITE-5662:
Description:
Currently attempting to run {{SELECT CAST(BOOLEAN as INTEGER)}} will throw a
{{NumberFormatException}}.
The BigQuery dialect allows casting {{boolean}} to {{int64}} and {{string}},
and not for decimal, bigdecimal, numeric, etc.
src:
https://cloud.google.com/bigquery/docs/reference/standard-sql/conversion_functions
Desired behavior:
{{SELECT CAST(TRUE as INTEGER)}} -> {{1}}
{{SELECT CAST(FALSE as INTEGER)}} -> {{0}}
was:
Currently attempting to run {{SELECT CAST(BOOLEAN as INTEGER)}} will throw a
{{NumberFormatException}}.
The BigQuery dialect allows casting {{boolean}} to {{int64}} and {{string}},
and not for decimal, bigdecimal, numeric, etc.
src:
https://cloud.google.com/bigquery/docs/reference/standard-sql/conversion_functions
> CAST(BOOLEAN as INTEGER) throws a NumberFormatException
> ---
>
> Key: CALCITE-5662
> URL: https://issues.apache.org/jira/browse/CALCITE-5662
> Project: Calcite
> Issue Type: Bug
>Reporter: Oliver Lee
>Assignee: Oliver Lee
>Priority: Minor
>
> Currently attempting to run {{SELECT CAST(BOOLEAN as INTEGER)}} will throw a
> {{NumberFormatException}}.
> The BigQuery dialect allows casting {{boolean}} to {{int64}} and {{string}},
> and not for decimal, bigdecimal, numeric, etc.
> src:
> https://cloud.google.com/bigquery/docs/reference/standard-sql/conversion_functions
>
> Desired behavior:
> {{SELECT CAST(TRUE as INTEGER)}} -> {{1}}
> {{SELECT CAST(FALSE as INTEGER)}} -> {{0}}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-5662) CAST(BOOLEAN as INTEGER) throws a NumberFormatException
[
https://issues.apache.org/jira/browse/CALCITE-5662?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Oliver Lee updated CALCITE-5662:
Description:
Currently attempting to run {{SELECT CAST(BOOLEAN as INTEGER)}} will throw a
{{NumberFormatException}}.
The BigQuery dialect allows casting {{boolean}} to {{int64}} and {{string}},
and not for decimal, bigdecimal, numeric, etc.
src:
https://cloud.google.com/bigquery/docs/reference/standard-sql/conversion_functions
Desired behavior:
{{SELECT CAST(TRUE as INTEGER)}} -> {{1}}
{{SELECT CAST(FALSE as INTEGER)}} -> {{0}}
{{SELECT CAST(null as INTEGER)}} -> {{null}}
was:
Currently attempting to run {{SELECT CAST(BOOLEAN as INTEGER)}} will throw a
{{NumberFormatException}}.
The BigQuery dialect allows casting {{boolean}} to {{int64}} and {{string}},
and not for decimal, bigdecimal, numeric, etc.
src:
https://cloud.google.com/bigquery/docs/reference/standard-sql/conversion_functions
Desired behavior:
{{SELECT CAST(TRUE as INTEGER)}} -> {{1}}
{{SELECT CAST(FALSE as INTEGER)}} -> {{0}}
> CAST(BOOLEAN as INTEGER) throws a NumberFormatException
> ---
>
> Key: CALCITE-5662
> URL: https://issues.apache.org/jira/browse/CALCITE-5662
> Project: Calcite
> Issue Type: Bug
>Reporter: Oliver Lee
>Assignee: Oliver Lee
>Priority: Minor
>
> Currently attempting to run {{SELECT CAST(BOOLEAN as INTEGER)}} will throw a
> {{NumberFormatException}}.
> The BigQuery dialect allows casting {{boolean}} to {{int64}} and {{string}},
> and not for decimal, bigdecimal, numeric, etc.
> src:
> https://cloud.google.com/bigquery/docs/reference/standard-sql/conversion_functions
>
> Desired behavior:
> {{SELECT CAST(TRUE as INTEGER)}} -> {{1}}
> {{SELECT CAST(FALSE as INTEGER)}} -> {{0}}
> {{SELECT CAST(null as INTEGER)}} -> {{null}}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-5662) CAST(BOOLEAN as INTEGER) throws a NumberFormatException
[
https://issues.apache.org/jira/browse/CALCITE-5662?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Oliver Lee updated CALCITE-5662:
Summary: CAST(BOOLEAN as INTEGER) throws a NumberFormatException (was: Fix
CAST(BOOLEAN as INTEGER))
> CAST(BOOLEAN as INTEGER) throws a NumberFormatException
> ---
>
> Key: CALCITE-5662
> URL: https://issues.apache.org/jira/browse/CALCITE-5662
> Project: Calcite
> Issue Type: Bug
>Reporter: Oliver Lee
>Assignee: Oliver Lee
>Priority: Minor
>
> Currently attempting to run {{SELECT CAST(BOOLEAN as INTEGER)}} will throw a
> {{NumberFormatException}}.
> The BigQuery dialect allows casting {{boolean}} to {{int64}} and {{string}},
> and not for decimal, bigdecimal, numeric, etc.
> src:
> https://cloud.google.com/bigquery/docs/reference/standard-sql/conversion_functions
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5662) Fix CAST(BOOLEAN as INTEGER)
[
https://issues.apache.org/jira/browse/CALCITE-5662?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17714290#comment-17714290
]
Julian Hyde commented on CALCITE-5662:
--
Never start a summary (or commit message) with "fix". Describe the problem.
> Fix CAST(BOOLEAN as INTEGER)
>
>
> Key: CALCITE-5662
> URL: https://issues.apache.org/jira/browse/CALCITE-5662
> Project: Calcite
> Issue Type: Bug
>Reporter: Oliver Lee
>Assignee: Oliver Lee
>Priority: Minor
>
> Currently attempting to run {{SELECT CAST(BOOLEAN as INTEGER)}} will throw a
> {{NumberFormatException}}.
> The BigQuery dialect allows casting {{boolean}} to {{int64}} and {{string}},
> and not for decimal, bigdecimal, numeric, etc.
> src:
> https://cloud.google.com/bigquery/docs/reference/standard-sql/conversion_functions
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5651) Type system decimal scale in inferred types may exceed allowed scale
[
https://issues.apache.org/jira/browse/CALCITE-5651?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17714289#comment-17714289
]
Julian Hyde commented on CALCITE-5651:
--
Calcite's type system should support systems that exactly adhere to the
standard and also to those, such as Postgres, that go beyond it. And it should
be configurable to model the limits of a particular DBMS' type system.
But I digress. I agree that the current behavior is wrong. It is reasonable
that the type system "clamps" precision to the allowed range.
> Type system decimal scale in inferred types may exceed allowed scale
>
>
> Key: CALCITE-5651
> URL: https://issues.apache.org/jira/browse/CALCITE-5651
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.34.0
>Reporter: Mihai Budiu
>Priority: Major
> Labels: pull-request-available
> Time Spent: 1h 50m
> Remaining Estimate: 0h
>
> If one uses a type system with a scale which is < precision/2 the calcite
> compiler may crash with an assertion failure. This can happen in the
> RelDataTypeSystem.deriveDecimalPlusType method, where this assertion fails:
> assert scale <= getMaxNumericScale();
> The root cause is the function SqlTypeUtil.getMaxPrecisionScaleDecimal, which
> does the following:
> int scale = maxPrecision / 2;
> This was introduced in Calcite 1.27.
> I think the a better version should be
> int scale = Math.min(maxPrecision/2,
> factory.getTypeSystem().getMaxNumericScale());
> To reproduce this bug I had to do a bit of juggling. First I had to fix the
> bug reported in CALCITE-5650. Then I added the following two functions in
> RelToSqlConverterTest.java:
> {code}
> @Test void testNumericScale() {
> String q = "WITH v(x) AS (VALUES('4.2')) " +
> " SELECT x1 + x2 FROM v AS v1(x1), v AS V2(x2)";
> sql(q)
> .withPostgresqlModifiedDecimalTypeSystem()
> .ok("");
> }
> {code}
> and in the static class Sql I added this method:
> {code}
> Sql withPostgresqlModifiedDecimalTypeSystem() {
> final PostgresqlSqlDialect postgresqlSqlDialect =
> new PostgresqlSqlDialect(PostgresqlSqlDialect.DEFAULT_CONTEXT
> .withDataTypeSystem(new RelDataTypeSystemImpl() {
> @Override
> public int getMaxNumericScale() {
> return 10;
> }
> @Override
> public int getMaxNumericPrecision() {
> return 39;
> }
> }));
> return dialect(postgresqlSqlDialect);
> }
> {code}
> This is the exception stack trace:
> {noformat}
> java.lang.AssertionError
> at
> org.apache.calcite.rel.type.RelDataTypeSystem.deriveDecimalPlusType(RelDataTypeSystem.java:167)
> at
> org.apache.calcite.sql.type.ReturnTypes.lambda$static$8(ReturnTypes.java:653)
> at
> org.apache.calcite.sql.type.SqlTypeTransformCascade.inferReturnType(SqlTypeTransformCascade.java:58)
> at
> org.apache.calcite.sql.type.SqlReturnTypeInferenceChain.inferReturnType(SqlReturnTypeInferenceChain.java:55)
> at
> org.apache.calcite.sql.SqlOperator.inferReturnType(SqlOperator.java:537)
> at
> org.apache.calcite.sql.SqlOperator.validateOperands(SqlOperator.java:504)
> at org.apache.calcite.sql.SqlOperator.deriveType(SqlOperator.java:605)
> at
> org.apache.calcite.sql.SqlBinaryOperator.deriveType(SqlBinaryOperator.java:178)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:6521)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:6508)
> at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:161)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.deriveTypeImpl(SqlValidatorImpl.java:1897)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.deriveType(SqlValidatorImpl.java:1882)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.expandSelectItem(SqlValidatorImpl.java:489)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelectList(SqlValidatorImpl.java:4608)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3780)
> at
> org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:61)
> at
> org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:88)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:1135)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:1106)
> at
> org.apache.calcite.sql.validate.WithNamespace.validateImpl(WithNamespace.java:59)
>
[jira] [Commented] (CALCITE-5390) RelDecorrelator throws NullPointerException
[
https://issues.apache.org/jira/browse/CALCITE-5390?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17714269#comment-17714269
]
Julian Hyde commented on CALCITE-5390:
--
The latter query (with "CASE") throws
{noformat}
java.lang.AssertionError: Required columns {3} not subset of left columns {0, 1}
at org.apache.calcite.util.Litmus$1.fail(Litmus.java:32)
at org.apache.calcite.util.Litmus$1.check(Litmus.java:44)
at org.apache.calcite.rel.core.Correlate.isValid(Correlate.java:145)
at org.apache.calcite.rel.core.Correlate.(Correlate.java:109)
at
org.apache.calcite.rel.logical.LogicalCorrelate.(LogicalCorrelate.java:72)
at
org.apache.calcite.rel.logical.LogicalCorrelate.create(LogicalCorrelate.java:115)
at
org.apache.calcite.rel.core.RelFactories$CorrelateFactoryImpl.createCorrelate(RelFactories.java:440)
at org.apache.calcite.tools.RelBuilder.join(RelBuilder.java:2861)
at
org.apache.calcite.rel.rules.SubQueryRemoveRule.rewriteIn(SubQueryRemoveRule.java:698)
{noformat}
and is therefore similar to CALCITE-5638 and CALCITE-5429.
> RelDecorrelator throws NullPointerException
> ---
>
> Key: CALCITE-5390
> URL: https://issues.apache.org/jira/browse/CALCITE-5390
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Dmitry Sysolyatin
>Assignee: Dan Zou
>Priority: Major
>
> The current query throws NullPointerException
> {code:java}
> SELECT
> (SELECT 1 FROM emp d WHERE d.job = a.job LIMIT 1) AS t1,
> (SELECT a.job = 'PRESIDENT' FROM emp s LIMIT 1) as t2
> FROM emp a;
> {code}
> Test case -
> [https://github.com/apache/calcite/commit/46fe9bc456f2d34cf7dccd29829c9e85abe69d5f]
> Logical plan before it fails:
> {code:java}
> LogicalProject(T1=[$8], T2=[$9])
> LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
> SAL=[$5], COMM=[$6], DEPTNO=[$7], $f0=[$8], $f09=[$9])
> LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
> SAL=[$5], COMM=[$6], DEPTNO=[$7], $f0=[$8], $f00=[$10])
> LogicalCorrelate(correlation=[$cor0], joinType=[left],
> requiredColumns=[{9}])
> LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3],
> HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], $f0=[$8], $f9=[=($2,
> 'PRESIDENT')])
> LogicalCorrelate(correlation=[$cor0], joinType=[left],
> requiredColumns=[{2}])
> LogicalTableScan(table=[[scott, EMP]])
> LogicalAggregate(group=[{}], agg#0=[SINGLE_VALUE($0)])
> LogicalSort(fetch=[1])
> LogicalProject(EXPR$0=[1])
> LogicalFilter(condition=[=($2, $cor0.JOB)])
> LogicalTableScan(table=[[scott, EMP]])
> LogicalAggregate(group=[{}], agg#0=[SINGLE_VALUE($0)])
> LogicalSort(fetch=[1])
> LogicalProject(EXPR$0=[$cor0.$f9])
> LogicalTableScan(table=[[scott, EMP]]) {code}
> Stack trace:
> {code:java}
> Caused by: java.lang.NullPointerException
> at java.util.Objects.requireNonNull(Objects.java:203)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.createValueGenerator(RelDecorrelator.java:833)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.decorrelateInputWithValueGenerator(RelDecorrelator.java:1028)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:764)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:738)
> at sun.reflect.GeneratedMethodAccessor9.invoke(Unknown Source)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:531)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.getInvoke(RelDecorrelator.java:707)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:464)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:531)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.getInvoke(RelDecorrelator.java:707)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:512)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:495)
> at sun.reflect.GeneratedMethodAccessor13.invoke(Unknown Source)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invo
[jira] [Commented] (CALCITE-5638) Assertion Failure during planning correlated query with orderby
[
https://issues.apache.org/jira/browse/CALCITE-5638?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17714266#comment-17714266
]
Julian Hyde commented on CALCITE-5638:
--
Same error as CALCITE-5429, and may be related.
> Assertion Failure during planning correlated query with orderby
> ---
>
> Key: CALCITE-5638
> URL: https://issues.apache.org/jira/browse/CALCITE-5638
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.34.0
>Reporter: Mihai Budiu
>Priority: Major
>
> Here is a test which fails if pasted in jdbcTest.java:
> {code}
> @Test void testCrash() throws Exception {
> String hsqldbMemUrl = "jdbc:hsqldb:mem:.";
> Connection baseConnection = DriverManager.getConnection(hsqldbMemUrl);
> Statement baseStmt = baseConnection.createStatement();
> baseStmt.execute("CREATE TABLE t1(a INTEGER, b INTEGER, c INTEGER, d
> INTEGER, e INTEGER)");
> baseStmt.close();
> baseConnection.commit();
> Properties info = new Properties();
> info.put("model",
> "inline:"
> + "{\n"
> + " version: '1.0',\n"
> + " defaultSchema: 'BASEJDBC',\n"
> + " schemas: [\n"
> + " {\n"
> + " type: 'jdbc',\n"
> + " name: 'BASEJDBC',\n"
> + " jdbcDriver: '" + jdbcDriver.class.getName() + "',\n"
> + " jdbcUrl: '" + hsqldbMemUrl + "',\n"
> + " jdbcCatalog: null,\n"
> + " jdbcSchema: null\n"
> + " }\n"
> + " ]\n"
> + "}");
> Connection calciteConnection =
> DriverManager.getConnection("jdbc:calcite:", info);
> String statement = "SELECT b, d, (SELECT count(*) FROM t1 AS x WHERE
> x.c>t1.c AND x.dc ORDER BY 1,2,3";
> ResultSet rs =
> calciteConnection.prepareStatement(statement).executeQuery();
> rs.close();
> calciteConnection.close();
> }
> {code}
> The stack trace is:
> {noformat}
> Required columns \{2, 3} not subset of left columns \{0, 1, 2}
> java.lang.AssertionError: Required columns \{2, 3} not subset of left columns
> \{0, 1, 2}
> at org.apache.calcite.util.Litmus.lambda$static$0(Litmus.java:31)
> at org.apache.calcite.util.Litmus.check(Litmus.java:76)
> at org.apache.calcite.rel.core.Correlate.isValid(Correlate.java:145)
> at org.apache.calcite.rel.core.Correlate.(Correlate.java:109)
> at
> org.apache.calcite.rel.logical.LogicalCorrelate.(LogicalCorrelate.java:72)
> at
> org.apache.calcite.rel.logical.LogicalCorrelate.create(LogicalCorrelate.java:115)
> at
> org.apache.calcite.rel.core.RelFactories$CorrelateFactoryImpl.createCorrelate(RelFactories.java:440)
> at org.apache.calcite.tools.RelBuilder.join(RelBuilder.java:2865)
> at
> org.apache.calcite.rel.rules.SubQueryRemoveRule.rewriteScalarQuery(SubQueryRemoveRule.java:136)
> at
> org.apache.calcite.rel.rules.SubQueryRemoveRule.apply(SubQueryRemoveRule.java:94)
> at
> org.apache.calcite.rel.rules.SubQueryRemoveRule.matchProject(SubQueryRemoveRule.java:828)
> at
> org.apache.calcite.rel.rules.SubQueryRemoveRule.access$200(SubQueryRemoveRule.java:75)
> at
> org.apache.calcite.rel.rules.SubQueryRemoveRule$Config.lambda$static$0(SubQueryRemoveRule.java:906)
> at
> org.apache.calcite.rel.rules.SubQueryRemoveRule.onMatch(SubQueryRemoveRule.java:86)
> at
> org.apache.calcite.plan.AbstractRelOptPlanner.fireRule(AbstractRelOptPlanner.java:337)
> at org.apache.calcite.plan.hep.HepPlanner.applyRule(HepPlanner.java:556)
> at org.apache.calcite.plan.hep.HepPlanner.applyRules(HepPlanner.java:420)
> at
> org.apache.calcite.plan.hep.HepPlanner.executeRuleCollection(HepPlanner.java:286)
> at
> org.apache.calcite.plan.hep.HepInstruction$RuleCollection$State.execute(HepInstruction.java:105)
> at
> org.apache.calcite.plan.hep.HepPlanner.lambda$executeProgram$0(HepPlanner.java:211)
> at com.google.common.collect.ImmutableList.forEach(ImmutableList.java:422)
> at
> org.apache.calcite.plan.hep.HepPlanner.executeProgram(HepPlanner.java:210)
> at
> org.apache.calcite.plan.hep.HepProgram$State.execute(HepProgram.java:118)
> at
> org.apache.calcite.plan.hep.HepPlanner.executeProgram(HepPlanner.java:205)
> at org.apache.calcite.plan.hep.HepPlanner.findBestExp(HepPlanner.java:191)
> at org.apache.calcite.tools.Programs.lambda$of$0(Programs.java:177)
> at
> org.apache.calcite.tools.Programs$SequenceProgram.run(Programs.java:337)
> at org.apache.calcite.prepare.Prepare.optimize(Prepare.java:177)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:312)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:220)
>
[jira] [Commented] (CALCITE-5651) Type system decimal scale in inferred types may exceed allowed scale
[
https://issues.apache.org/jira/browse/CALCITE-5651?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17714264#comment-17714264
]
Mihai Budiu commented on CALCITE-5651:
--
My understanding was that "precision" is the total number of (decimal) digits
represented, while "scale" is the number of digits after the decimal point. In
this case scale cannot exceed precision. I expect this is true for the
DECIMAL/NUMERIC data type at least.
> Type system decimal scale in inferred types may exceed allowed scale
>
>
> Key: CALCITE-5651
> URL: https://issues.apache.org/jira/browse/CALCITE-5651
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.34.0
>Reporter: Mihai Budiu
>Priority: Major
> Labels: pull-request-available
> Time Spent: 1h 50m
> Remaining Estimate: 0h
>
> If one uses a type system with a scale which is < precision/2 the calcite
> compiler may crash with an assertion failure. This can happen in the
> RelDataTypeSystem.deriveDecimalPlusType method, where this assertion fails:
> assert scale <= getMaxNumericScale();
> The root cause is the function SqlTypeUtil.getMaxPrecisionScaleDecimal, which
> does the following:
> int scale = maxPrecision / 2;
> This was introduced in Calcite 1.27.
> I think the a better version should be
> int scale = Math.min(maxPrecision/2,
> factory.getTypeSystem().getMaxNumericScale());
> To reproduce this bug I had to do a bit of juggling. First I had to fix the
> bug reported in CALCITE-5650. Then I added the following two functions in
> RelToSqlConverterTest.java:
> {code}
> @Test void testNumericScale() {
> String q = "WITH v(x) AS (VALUES('4.2')) " +
> " SELECT x1 + x2 FROM v AS v1(x1), v AS V2(x2)";
> sql(q)
> .withPostgresqlModifiedDecimalTypeSystem()
> .ok("");
> }
> {code}
> and in the static class Sql I added this method:
> {code}
> Sql withPostgresqlModifiedDecimalTypeSystem() {
> final PostgresqlSqlDialect postgresqlSqlDialect =
> new PostgresqlSqlDialect(PostgresqlSqlDialect.DEFAULT_CONTEXT
> .withDataTypeSystem(new RelDataTypeSystemImpl() {
> @Override
> public int getMaxNumericScale() {
> return 10;
> }
> @Override
> public int getMaxNumericPrecision() {
> return 39;
> }
> }));
> return dialect(postgresqlSqlDialect);
> }
> {code}
> This is the exception stack trace:
> {noformat}
> java.lang.AssertionError
> at
> org.apache.calcite.rel.type.RelDataTypeSystem.deriveDecimalPlusType(RelDataTypeSystem.java:167)
> at
> org.apache.calcite.sql.type.ReturnTypes.lambda$static$8(ReturnTypes.java:653)
> at
> org.apache.calcite.sql.type.SqlTypeTransformCascade.inferReturnType(SqlTypeTransformCascade.java:58)
> at
> org.apache.calcite.sql.type.SqlReturnTypeInferenceChain.inferReturnType(SqlReturnTypeInferenceChain.java:55)
> at
> org.apache.calcite.sql.SqlOperator.inferReturnType(SqlOperator.java:537)
> at
> org.apache.calcite.sql.SqlOperator.validateOperands(SqlOperator.java:504)
> at org.apache.calcite.sql.SqlOperator.deriveType(SqlOperator.java:605)
> at
> org.apache.calcite.sql.SqlBinaryOperator.deriveType(SqlBinaryOperator.java:178)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:6521)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:6508)
> at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:161)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.deriveTypeImpl(SqlValidatorImpl.java:1897)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.deriveType(SqlValidatorImpl.java:1882)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.expandSelectItem(SqlValidatorImpl.java:489)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelectList(SqlValidatorImpl.java:4608)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3780)
> at
> org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:61)
> at
> org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:88)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:1135)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:1106)
> at
> org.apache.calcite.sql.validate.WithNamespace.validateImpl(WithNamespace.java:59)
> at
> org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:88)
>
[jira] [Commented] (CALCITE-5651) Type system decimal scale in inferred types may exceed allowed scale
[
https://issues.apache.org/jira/browse/CALCITE-5651?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17714261#comment-17714261
]
Mihai Budiu commented on CALCITE-5651:
--
And a quote from the SQL92 standard (I don't have a newer version available):
https://www.andrew.cmu.edu/user/shadow/sql/sql1992.txt
15) The of an shall not be greater than the
of the .
> Type system decimal scale in inferred types may exceed allowed scale
>
>
> Key: CALCITE-5651
> URL: https://issues.apache.org/jira/browse/CALCITE-5651
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.34.0
>Reporter: Mihai Budiu
>Priority: Major
> Labels: pull-request-available
> Time Spent: 1h 50m
> Remaining Estimate: 0h
>
> If one uses a type system with a scale which is < precision/2 the calcite
> compiler may crash with an assertion failure. This can happen in the
> RelDataTypeSystem.deriveDecimalPlusType method, where this assertion fails:
> assert scale <= getMaxNumericScale();
> The root cause is the function SqlTypeUtil.getMaxPrecisionScaleDecimal, which
> does the following:
> int scale = maxPrecision / 2;
> This was introduced in Calcite 1.27.
> I think the a better version should be
> int scale = Math.min(maxPrecision/2,
> factory.getTypeSystem().getMaxNumericScale());
> To reproduce this bug I had to do a bit of juggling. First I had to fix the
> bug reported in CALCITE-5650. Then I added the following two functions in
> RelToSqlConverterTest.java:
> {code}
> @Test void testNumericScale() {
> String q = "WITH v(x) AS (VALUES('4.2')) " +
> " SELECT x1 + x2 FROM v AS v1(x1), v AS V2(x2)";
> sql(q)
> .withPostgresqlModifiedDecimalTypeSystem()
> .ok("");
> }
> {code}
> and in the static class Sql I added this method:
> {code}
> Sql withPostgresqlModifiedDecimalTypeSystem() {
> final PostgresqlSqlDialect postgresqlSqlDialect =
> new PostgresqlSqlDialect(PostgresqlSqlDialect.DEFAULT_CONTEXT
> .withDataTypeSystem(new RelDataTypeSystemImpl() {
> @Override
> public int getMaxNumericScale() {
> return 10;
> }
> @Override
> public int getMaxNumericPrecision() {
> return 39;
> }
> }));
> return dialect(postgresqlSqlDialect);
> }
> {code}
> This is the exception stack trace:
> {noformat}
> java.lang.AssertionError
> at
> org.apache.calcite.rel.type.RelDataTypeSystem.deriveDecimalPlusType(RelDataTypeSystem.java:167)
> at
> org.apache.calcite.sql.type.ReturnTypes.lambda$static$8(ReturnTypes.java:653)
> at
> org.apache.calcite.sql.type.SqlTypeTransformCascade.inferReturnType(SqlTypeTransformCascade.java:58)
> at
> org.apache.calcite.sql.type.SqlReturnTypeInferenceChain.inferReturnType(SqlReturnTypeInferenceChain.java:55)
> at
> org.apache.calcite.sql.SqlOperator.inferReturnType(SqlOperator.java:537)
> at
> org.apache.calcite.sql.SqlOperator.validateOperands(SqlOperator.java:504)
> at org.apache.calcite.sql.SqlOperator.deriveType(SqlOperator.java:605)
> at
> org.apache.calcite.sql.SqlBinaryOperator.deriveType(SqlBinaryOperator.java:178)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:6521)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:6508)
> at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:161)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.deriveTypeImpl(SqlValidatorImpl.java:1897)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.deriveType(SqlValidatorImpl.java:1882)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.expandSelectItem(SqlValidatorImpl.java:489)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelectList(SqlValidatorImpl.java:4608)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3780)
> at
> org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:61)
> at
> org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:88)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:1135)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:1106)
> at
> org.apache.calcite.sql.validate.WithNamespace.validateImpl(WithNamespace.java:59)
> at
> org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:88)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(S
[jira] [Commented] (CALCITE-5651) Type system decimal scale in inferred types may exceed allowed scale
[
https://issues.apache.org/jira/browse/CALCITE-5651?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17714260#comment-17714260
]
Mihai Budiu commented on CALCITE-5651:
--
Here are two quotes from the Postgres documentation here [PostgreSQL:
Documentation: 15: 8.1. Numeric
Types|https://www.postgresql.org/docs/current/datatype-numeric.html]
Beginning in PostgreSQL 15, it is allowed to declare a {{numeric}} column with
a negative scale. Then values will be rounded to the left of the decimal point.
The precision still represents the maximum number of non-rounded digits.
PostgreSQL permits the scale in a {{numeric}} type declaration to be any value
in the range -1000 to 1000. However, the SQL standard requires the scale to be
in the range 0 to {_}{{precision}}{_}. Using scales outside that range may not
be portable to other database systems.
> Type system decimal scale in inferred types may exceed allowed scale
>
>
> Key: CALCITE-5651
> URL: https://issues.apache.org/jira/browse/CALCITE-5651
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.34.0
>Reporter: Mihai Budiu
>Priority: Major
> Labels: pull-request-available
> Time Spent: 1h 50m
> Remaining Estimate: 0h
>
> If one uses a type system with a scale which is < precision/2 the calcite
> compiler may crash with an assertion failure. This can happen in the
> RelDataTypeSystem.deriveDecimalPlusType method, where this assertion fails:
> assert scale <= getMaxNumericScale();
> The root cause is the function SqlTypeUtil.getMaxPrecisionScaleDecimal, which
> does the following:
> int scale = maxPrecision / 2;
> This was introduced in Calcite 1.27.
> I think the a better version should be
> int scale = Math.min(maxPrecision/2,
> factory.getTypeSystem().getMaxNumericScale());
> To reproduce this bug I had to do a bit of juggling. First I had to fix the
> bug reported in CALCITE-5650. Then I added the following two functions in
> RelToSqlConverterTest.java:
> {code}
> @Test void testNumericScale() {
> String q = "WITH v(x) AS (VALUES('4.2')) " +
> " SELECT x1 + x2 FROM v AS v1(x1), v AS V2(x2)";
> sql(q)
> .withPostgresqlModifiedDecimalTypeSystem()
> .ok("");
> }
> {code}
> and in the static class Sql I added this method:
> {code}
> Sql withPostgresqlModifiedDecimalTypeSystem() {
> final PostgresqlSqlDialect postgresqlSqlDialect =
> new PostgresqlSqlDialect(PostgresqlSqlDialect.DEFAULT_CONTEXT
> .withDataTypeSystem(new RelDataTypeSystemImpl() {
> @Override
> public int getMaxNumericScale() {
> return 10;
> }
> @Override
> public int getMaxNumericPrecision() {
> return 39;
> }
> }));
> return dialect(postgresqlSqlDialect);
> }
> {code}
> This is the exception stack trace:
> {noformat}
> java.lang.AssertionError
> at
> org.apache.calcite.rel.type.RelDataTypeSystem.deriveDecimalPlusType(RelDataTypeSystem.java:167)
> at
> org.apache.calcite.sql.type.ReturnTypes.lambda$static$8(ReturnTypes.java:653)
> at
> org.apache.calcite.sql.type.SqlTypeTransformCascade.inferReturnType(SqlTypeTransformCascade.java:58)
> at
> org.apache.calcite.sql.type.SqlReturnTypeInferenceChain.inferReturnType(SqlReturnTypeInferenceChain.java:55)
> at
> org.apache.calcite.sql.SqlOperator.inferReturnType(SqlOperator.java:537)
> at
> org.apache.calcite.sql.SqlOperator.validateOperands(SqlOperator.java:504)
> at org.apache.calcite.sql.SqlOperator.deriveType(SqlOperator.java:605)
> at
> org.apache.calcite.sql.SqlBinaryOperator.deriveType(SqlBinaryOperator.java:178)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:6521)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:6508)
> at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:161)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.deriveTypeImpl(SqlValidatorImpl.java:1897)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.deriveType(SqlValidatorImpl.java:1882)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.expandSelectItem(SqlValidatorImpl.java:489)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelectList(SqlValidatorImpl.java:4608)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3780)
> at
> org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:61)
> at
> org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNames
[jira] [Comment Edited] (CALCITE-5642) Add SHA256, SHA512 functions
[ https://issues.apache.org/jira/browse/CALCITE-5642?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17714258#comment-17714258 ] Julian Hyde edited comment on CALCITE-5642 at 4/19/23 6:34 PM: --- [~FrankZou]. I don't agree with your first point. (The ASF is registered as a U.S. legal entity, and the bytes are stored in U.S.-based data centers and therefore a release or a download may be construed to be an 'export' under U.S. law.) But I agree with your second point, and I agree that we would not be exporting an encryption algorithm here. So, we're good. was (Author: julianhyde): [~FrankZou]. I don't agree with your first point. (The ASF is registered at a U.S. organization, and the bytes are stored in U.S.-based data centers and therefore a release or a download may be construed to be an 'export' under U.S. law.) But I agree with your second point, and I agree that we would not be exporting an encryption algorithm here. So, we're good. > Add SHA256, SHA512 functions > > > Key: CALCITE-5642 > URL: https://issues.apache.org/jira/browse/CALCITE-5642 > Project: Calcite > Issue Type: New Feature >Reporter: Dan Zou >Assignee: Dan Zou >Priority: Major > Labels: pull-request-available > Time Spent: 10m > Remaining Estimate: 0h > > Add SHA256, SHA512 functions and enable them for BigQuery. > SHA256: Computes the hash of the input using the [SHA-256 > algorithm|https://en.wikipedia.org/wiki/SHA-2]. This function returns 32 > bytes. > SHA512: Computes the hash of the input using the [SHA-512 > algorithm|https://en.wikipedia.org/wiki/SHA-2]. This function returns 64 > bytes. > See more details in [BigQuery > Doc|https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#sha256] -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-5642) Add SHA256, SHA512 functions
[ https://issues.apache.org/jira/browse/CALCITE-5642?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17714258#comment-17714258 ] Julian Hyde commented on CALCITE-5642: -- [~FrankZou]. I don't agree with your first point. (The ASF is registered at a U.S. organization, and the bytes are stored in U.S.-based data centers and therefore a release or a download may be construed to be an 'export' under U.S. law.) But I agree with your second point, and I agree that we would not be exporting an encryption algorithm here. So, we're good. > Add SHA256, SHA512 functions > > > Key: CALCITE-5642 > URL: https://issues.apache.org/jira/browse/CALCITE-5642 > Project: Calcite > Issue Type: New Feature >Reporter: Dan Zou >Assignee: Dan Zou >Priority: Major > Labels: pull-request-available > Time Spent: 10m > Remaining Estimate: 0h > > Add SHA256, SHA512 functions and enable them for BigQuery. > SHA256: Computes the hash of the input using the [SHA-256 > algorithm|https://en.wikipedia.org/wiki/SHA-2]. This function returns 32 > bytes. > SHA512: Computes the hash of the input using the [SHA-512 > algorithm|https://en.wikipedia.org/wiki/SHA-2]. This function returns 64 > bytes. > See more details in [BigQuery > Doc|https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#sha256] -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-5651) Type system decimal scale in inferred types may exceed allowed scale
[
https://issues.apache.org/jira/browse/CALCITE-5651?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17714257#comment-17714257
]
Julian Hyde commented on CALCITE-5651:
--
Some DBs may not allow it, but it is reasonable to allow scale larger than
precision. A value of type {{NUMBER(3, 10)}} can store values such as '0.000
000 000 1' and '0.000 000 099 9' exactly.
It is also reasonable to allow negative scales. If you want to store the number
of atoms to 3 significant figures, you could use a type {{{}NUMBER(3, -23){}}},
bearing in mind that Avogadro's number is 6e23.
> Type system decimal scale in inferred types may exceed allowed scale
>
>
> Key: CALCITE-5651
> URL: https://issues.apache.org/jira/browse/CALCITE-5651
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.34.0
>Reporter: Mihai Budiu
>Priority: Major
> Labels: pull-request-available
> Time Spent: 1h 50m
> Remaining Estimate: 0h
>
> If one uses a type system with a scale which is < precision/2 the calcite
> compiler may crash with an assertion failure. This can happen in the
> RelDataTypeSystem.deriveDecimalPlusType method, where this assertion fails:
> assert scale <= getMaxNumericScale();
> The root cause is the function SqlTypeUtil.getMaxPrecisionScaleDecimal, which
> does the following:
> int scale = maxPrecision / 2;
> This was introduced in Calcite 1.27.
> I think the a better version should be
> int scale = Math.min(maxPrecision/2,
> factory.getTypeSystem().getMaxNumericScale());
> To reproduce this bug I had to do a bit of juggling. First I had to fix the
> bug reported in CALCITE-5650. Then I added the following two functions in
> RelToSqlConverterTest.java:
> {code}
> @Test void testNumericScale() {
> String q = "WITH v(x) AS (VALUES('4.2')) " +
> " SELECT x1 + x2 FROM v AS v1(x1), v AS V2(x2)";
> sql(q)
> .withPostgresqlModifiedDecimalTypeSystem()
> .ok("");
> }
> {code}
> and in the static class Sql I added this method:
> {code}
> Sql withPostgresqlModifiedDecimalTypeSystem() {
> final PostgresqlSqlDialect postgresqlSqlDialect =
> new PostgresqlSqlDialect(PostgresqlSqlDialect.DEFAULT_CONTEXT
> .withDataTypeSystem(new RelDataTypeSystemImpl() {
> @Override
> public int getMaxNumericScale() {
> return 10;
> }
> @Override
> public int getMaxNumericPrecision() {
> return 39;
> }
> }));
> return dialect(postgresqlSqlDialect);
> }
> {code}
> This is the exception stack trace:
> {noformat}
> java.lang.AssertionError
> at
> org.apache.calcite.rel.type.RelDataTypeSystem.deriveDecimalPlusType(RelDataTypeSystem.java:167)
> at
> org.apache.calcite.sql.type.ReturnTypes.lambda$static$8(ReturnTypes.java:653)
> at
> org.apache.calcite.sql.type.SqlTypeTransformCascade.inferReturnType(SqlTypeTransformCascade.java:58)
> at
> org.apache.calcite.sql.type.SqlReturnTypeInferenceChain.inferReturnType(SqlReturnTypeInferenceChain.java:55)
> at
> org.apache.calcite.sql.SqlOperator.inferReturnType(SqlOperator.java:537)
> at
> org.apache.calcite.sql.SqlOperator.validateOperands(SqlOperator.java:504)
> at org.apache.calcite.sql.SqlOperator.deriveType(SqlOperator.java:605)
> at
> org.apache.calcite.sql.SqlBinaryOperator.deriveType(SqlBinaryOperator.java:178)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:6521)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:6508)
> at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:161)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.deriveTypeImpl(SqlValidatorImpl.java:1897)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.deriveType(SqlValidatorImpl.java:1882)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.expandSelectItem(SqlValidatorImpl.java:489)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelectList(SqlValidatorImpl.java:4608)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3780)
> at
> org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:61)
> at
> org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:88)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:1135)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:1106)
> at
> org.apache.calcite.sql.validate.WithNamespace
[jira] [Commented] (CALCITE-5631) Optimization to merge redundant joins
[
https://issues.apache.org/jira/browse/CALCITE-5631?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17714253#comment-17714253
]
Julian Hyde commented on CALCITE-5631:
--
See [email thread "Rewrite rule to convert self-joins into
scans"|https://lists.apache.org/thread/hqf6m89zgdhf8y4pwstzh2f5b3oz41sl].
> Optimization to merge redundant joins
> -
>
> Key: CALCITE-5631
> URL: https://issues.apache.org/jira/browse/CALCITE-5631
> Project: Calcite
> Issue Type: Wish
>Reporter: Ian Bertolacci
>Assignee: Hanumath Rao Maduri
>Priority: Major
>
> There are situations where multiple joins can be merged into one.
> For example:
> {code:sql}
> select
>(select numarrayagg(C5633_203) from T893 where C5633_586 = T895.id),
>(select numarrayagg(C5633_170) from T893 where C5633_586 = T895.id)
> from T895
> {code}
> having this RelNode tree (after decorrelation):
> {code:java}
> 01:LogicalProject(EXPR$0=[$70], EXPR$1=[$72])
> └─ 02:LogicalJoin(condition=[=($0, $71)], joinType=[left])
>├─ 03:LogicalProject(...)
>| └─ 04:LogicalJoin(condition=[=($0, $70)], joinType=[left])
>| ├─ 05:QueryTableScan(table=[[QUERY, T895]])
>| └─ 06:LogicalAggregate(group=[{0}], EXPR$0=[ARRAY_AGG($1)])
>|└─ 07:LogicalProject(C5633_586=[$85], C5633_203=[$45])
>| └─ 08:LogicalFilter(condition=[=($85, $85)])
>| └─ 09:QueryTableScan(table=[[QUERY, T893]])
>└─ 10:LogicalAggregate(group=[{0}], EXPR$0=[ARRAY_AGG($1)])
> └─ 12:LogicalProject(C5633_586=[$85], C5633_170=[$12])
> └─ 12:LogicalFilter(condition=[=($85, $85)])
> └─ 13:QueryTableScan(table=[[QUERY, T893]])
> {code}
> can be simplified as this SQL:
> {code:sql}
> select
> agg.agg1,
> agg.agg2
> from
>T895 left join
>(select
> C5633_586,
> numarrayagg(C5633_203) as agg1,
> numarrayagg(C5633_170) as agg2
> from T893 group by C5633_586
> where C5633_586 is not null
>) as agg
> on agg.C5633_586 = T895.id
> {code}
> with this RelNode tree:
> {code:java}
> 20:LogicalProject(agg1=[$71], agg2=[$72])
> └─ 21:LogicalJoin(condition=[=($0, $70)], joinType=[left])
>├─ 23:QueryTableScan(table=[[QUERY, T895]])
>└─ 24:LogicalAggregate(group=[{0}], agg1=[ARRAY_AGG($1)],
> agg2=[ARRAY_AGG($2)])
> └─ 25:LogicalProject(C5633_586=[$85], C5633_203=[$45], C5633_170=[$12])
> └─ 26:LogicalFilter(condition=[IS_NOT_NULL($85)])
> └─ 27:QueryTableScan(table=[[QUERY, T893]])
> {code}
> This can be done by identifying joins on the same underlying columns with
> "mergeable" strings on of nodes on the same sides of the target joins
> (I wish I had a precise definition of "mergable", but I don't. but it is easy
> to see that the two RHSs in the first example can be combined (merged) into
> the RHS in the second example without changing the result.)
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-5662) Fix CAST(BOOLEAN as INTEGER)
Oliver Lee created CALCITE-5662:
---
Summary: Fix CAST(BOOLEAN as INTEGER)
Key: CALCITE-5662
URL: https://issues.apache.org/jira/browse/CALCITE-5662
Project: Calcite
Issue Type: Bug
Reporter: Oliver Lee
Assignee: Oliver Lee
Currently attempting to run {{SELECT CAST(BOOLEAN as INTEGER)}} will throw a
{{NumberFormatException}}.
The BigQuery dialect allows casting {{boolean}} to {{int64}} and {{string}},
and not for decimal, bigdecimal, numeric, etc.
src:
https://cloud.google.com/bigquery/docs/reference/standard-sql/conversion_functions
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5309) Accept cursors as inputs of table functions
[
https://issues.apache.org/jira/browse/CALCITE-5309?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17714252#comment-17714252
]
Julian Hyde commented on CALCITE-5309:
--
Many thanks for doing this research. I agree with everything except that Cursor
should be a new type of relational expression. I think cursor is just a *use*
of a relational expression, so any existing relational expression will do.
I expect there will need to be a method something like 'RelNode
implementCursor(int)'.
(Why is the cursor concept needed in the SQL syntax and the AST but not needed
in the relational algebra? I'm not totally sure, but I think it has something
to do with namespaces. Relational algebra doesn't need namespaces, because each
relational expression just refers to its inputs. The {{Spool}} operator - see
CALCITE-481 - deals with the outlier case that referring to an input is not
sufficient.)
> Accept cursors as inputs of table functions
> ---
>
> Key: CALCITE-5309
> URL: https://issues.apache.org/jira/browse/CALCITE-5309
> Project: Calcite
> Issue Type: New Feature
>Reporter: Bertil Chapuis
>Assignee: Bertil Chapuis
>Priority: Major
>
> The TableFunctionTest.testTableFunctionCursorsInputs is currently disabled
> and a note says that it could not be implemented. This feature is necessary
> to implement table functions that take a query or a table as an input.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5127) Support correlation variables in Project
[
https://issues.apache.org/jira/browse/CALCITE-5127?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17714242#comment-17714242
]
Julian Hyde commented on CALCITE-5127:
--
[~dmsysolyatin], Ok thanks. I wish you'd added a comment back then, so that we
new this was covered.
> Support correlation variables in Project
>
>
> Key: CALCITE-5127
> URL: https://issues.apache.org/jira/browse/CALCITE-5127
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Dmitry Sysolyatin
>Assignee: Benchao Li
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.33.0
>
> Time Spent: 3h 50m
> Remaining Estimate: 0h
>
> The following queries fail:
> {code}
> SELECT ARRAY(SELECT s.x) FROM (SELECT ARRAY[1,2,3] as x) s;
> SELECT ARRAY(SELECT * FROM UNNEST(s.x) y) FROM (SELECT ARRAY[1,2,3] as x) s;
> SELECT (SELECT CARDINALITY(s.x) LIMIT 1) FROM (SELECT ARRAY[1,2,3] as x) s;
>
> {code}
> With exception:
> {code}
> Caused by: java.lang.ClassCastException: java.lang.Integer cannot be cast to
> java.util.List
> {code}
> You can find test cases for this task in
> https://github.com/apache/calcite/commit/27e68ded2c3bea7d7af73dd1dc156e46fb3591a8
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-4771) Add TRY_CAST (MSSQL compatibility)
[
https://issues.apache.org/jira/browse/CALCITE-4771?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17714239#comment-17714239
]
Julian Hyde commented on CALCITE-4771:
--
Because I think type conversions are a more important area of functionality
than just a few functions. SAFE_CAST and TRY_CAST are really the same thing.
> Add TRY_CAST (MSSQL compatibility)
> --
>
> Key: CALCITE-4771
> URL: https://issues.apache.org/jira/browse/CALCITE-4771
> Project: Calcite
> Issue Type: Improvement
>Reporter: xuyang
>Assignee: Dan Zou
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.35.0
>
> Attachments: image-2021-09-16-11-43-55-743.png
>
> Time Spent: 2h 10m
> Remaining Estimate: 0h
>
> In the sql "SELECT CAST('haha' AS INT)",the value the function CAST returns
> will be parsed into NOT NULL, because when parsing, the type CAST returns is
> from the INT and the nullable attribute is from the 'haha', which doesn't
> consider the condition that parsing a string to an int could be invalid and
> return NULL values.
> I think there are two ways to improve this question:
> * One is to change the value of the CAST function to be nullable, which
> avoids the invalid parsing.
> * The other way is to introduce a function named TRY_CAST, which is used in
> SQL Server.If the parsing fails, TRY_CAST will return NULL instead of throws
> exception that a NOT NULL field will be set with our unexpected value NULL.
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5653) expended casts are missing when IdentifierExpansion is false
[
https://issues.apache.org/jira/browse/CALCITE-5653?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17714238#comment-17714238
]
Julian Hyde commented on CALCITE-5653:
--
I see. Can you change the summary and description to describe the bug rather
than the fix. The fact that your PR doesn't include a test is telling.
> expended casts are missing when IdentifierExpansion is false
>
>
> Key: CALCITE-5653
> URL: https://issues.apache.org/jira/browse/CALCITE-5653
> Project: Calcite
> Issue Type: Bug
>Reporter: Itiel Sadeh
>Priority: Major
> Labels: pull-request-available
> Time Spent: 20m
> Remaining Estimate: 0h
>
> When casts are added in {{{}SqlValidator{}}}, they are not part of the select
> if the configuration option {{identifierExpansion}} is false. This will cause
> the following issue
> {code:java}
> SELECT distinct sum(deptno + '1') FROM dept ORDER BY 1
> {code}
> will lead to:
> {code:java}
> From line 1, column 21 to line 1, column 54: Expression 'SUM(`DEPT`.`DEPTNO`
> + CAST('1' AS INTEGER))' is not in the select clause
> org.apache.calcite.runtime.CalciteContextException: From line 1, column 21 to
> line 1, column 54: Expression 'SUM(`DEPT`.`DEPTNO` + CAST('1' AS INTEGER))'
> is not in the select clause
> at
> java.base@17.0.5/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native
> Method)
> at
> java.base@17.0.5/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:77)
> at
> java.base@17.0.5/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
> at
> java.base@17.0.5/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:499)
> at
> java.base@17.0.5/java.lang.reflect.Constructor.newInstance(Constructor.java:480)
> at
> app//org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:505)
> at
> app//org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:945)
> at
> app//org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:930)
> at
> app//org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:5464)
> at
> app//org.apache.calcite.sql.validate.AggChecker.visit(AggChecker.java:168)
> at
> app//org.apache.calcite.sql.validate.AggChecker.visit(AggChecker.java:45)
> at app//org.apache.calcite.sql.SqlCall.accept(SqlCall.java:161)
> at
> app//org.apache.calcite.sql.validate.AggregatingSelectScope.checkAggregateExpr(AggregatingSelectScope.java:233)
> at
> app//org.apache.calcite.sql.validate.AggregatingSelectScope.validateExpr(AggregatingSelectScope.java:242)
> at
> app//org.apache.calcite.sql.validate.OrderByScope.validateExpr(OrderByScope.java:128)
> at
> app//org.apache.calcite.sql.validate.SqlValidatorImpl.validateExpr(SqlValidatorImpl.java:4676)
> at
> app//org.apache.calcite.sql.validate.SqlValidatorImpl.validateOrderItem(SqlValidatorImpl.java:4398)
> at
> app//org.apache.calcite.sql.validate.SqlValidatorImpl.validateOrderList(SqlValidatorImpl.java:4341)
> at
> app//org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3786)
> at
> app//org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:61)
> at
> app//org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:88)
> at
> app//org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:1135)
> at
> app//org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:1106)
> at app//org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:282)
> at
> app//org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:1081)
> at
> app//org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:787)
> {code}
> The issue is that the code in{{ AggChecker}} will search for the expanded
> node (with the CAST) in the select list, but the select list contains only
> the unexpanded node.
> This can be solved through setting{{ identifierExpansion }}to true, but I
> don't think it should be related, as it's not an identifier expansion here.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5660) Implement BigQuery Array Subscript Operators
[
https://issues.apache.org/jira/browse/CALCITE-5660?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17714236#comment-17714236
]
Tanner Clary commented on CALCITE-5660:
---
[~julianhyde] No I don't believe so either. My initial approach has been to
just assign each operator an offset and flag denoting whether they are "SAFE"
or not (since these are the two differences between all the operators).
> Implement BigQuery Array Subscript Operators
>
>
> Key: CALCITE-5660
> URL: https://issues.apache.org/jira/browse/CALCITE-5660
> Project: Calcite
> Issue Type: Task
>Reporter: Tanner Clary
>Assignee: Tanner Clary
>Priority: Major
>
> When indexing an array in BigQuery, you are required to wrap the index in one
> of four position keywords: {{OFFSET}}, {{ORDINAL}}, {{SAFE_OFFSET}}, and
> {{SAFE_ORDINAL}}.
> {{OFFSET(index)}}: The index starts at zero. Produces an error if the index
> is out of range.
> {{SAFE_OFFSET(index)}}: The index starts at zero. Returns NULL if the index
> is out of range.
> {{ORDINAL(index)}}: The index starts at one. Produces an error if the index
> is out of range.
> {{SAFE_ORDINAL(index)}}: The index starts at one. Returns NULL if the index
> is out of range.
> These operators are very similar to the existing {{ITEM}} operator currently
> used to index an array in Calcite. I believe the offset and out of bounds
> behavior for the existing implementation could be parameterized to support
> the above operators.
> [Link to BigQuery
> docs|https://cloud.google.com/bigquery/docs/reference/standard-sql/operators#array_subscript_operator]
> I will have a PR open for this in the next day or two, in the meantime any
> comments, questions, or suggestions are welcome as always.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5659) Allow CAST in an INTERVAL definition
[
https://issues.apache.org/jira/browse/CALCITE-5659?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17714235#comment-17714235
]
Julian Hyde commented on CALCITE-5659:
--
{quote}
What are the complications if we relax the requirement for parentheses?
{quote}
Try changing the parser. You may find that the grammar becomes ambiguous - the
parser doesn't know whether to wait for the following {{YEAR}} token. But I
don't know. Try it.
> Allow CAST in an INTERVAL definition
> -
>
> Key: CALCITE-5659
> URL: https://issues.apache.org/jira/browse/CALCITE-5659
> Project: Calcite
> Issue Type: Bug
>Reporter: Oliver Lee
>Assignee: Oliver Lee
>Priority: Major
> Labels: pull-request-available
> Time Spent: 10m
> Remaining Estimate: 0h
>
> Running queries that contain a CAST as part of the INTERVAL definition is
> throwing a parser error.
> i.e.
> {{INTERVAL CAST(5 as int64) YEAR}}
> We can make parser changes to allow the Cast function tokens to be accepted
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5660) Implement BigQuery Array Subscript Operators
[
https://issues.apache.org/jira/browse/CALCITE-5660?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17714233#comment-17714233
]
Julian Hyde commented on CALCITE-5660:
--
FWIW, for {{SAFE_OFFSET}} and {{SAFE_ORDINAL}}, I don't think you will need to
use the complex lambda-based mechanism that [~oliverlee] had to use for
{{SAFE_CAST}}.
If {{OFFSET}} and {{ORDINAL}} are keywords I hope that doesn't cause too much
mess in the parser.
> Implement BigQuery Array Subscript Operators
>
>
> Key: CALCITE-5660
> URL: https://issues.apache.org/jira/browse/CALCITE-5660
> Project: Calcite
> Issue Type: Task
>Reporter: Tanner Clary
>Assignee: Tanner Clary
>Priority: Major
>
> When indexing an array in BigQuery, you are required to wrap the index in one
> of four position keywords: {{OFFSET}}, {{ORDINAL}}, {{SAFE_OFFSET}}, and
> {{SAFE_ORDINAL}}.
> {{OFFSET(index)}}: The index starts at zero. Produces an error if the index
> is out of range.
> {{SAFE_OFFSET(index)}}: The index starts at zero. Returns NULL if the index
> is out of range.
> {{ORDINAL(index)}}: The index starts at one. Produces an error if the index
> is out of range.
> {{SAFE_ORDINAL(index)}}: The index starts at one. Returns NULL if the index
> is out of range.
> These operators are very similar to the existing {{ITEM}} operator currently
> used to index an array in Calcite. I believe the offset and out of bounds
> behavior for the existing implementation could be parameterized to support
> the above operators.
> [Link to BigQuery
> docs|https://cloud.google.com/bigquery/docs/reference/standard-sql/operators#array_subscript_operator]
> I will have a PR open for this in the next day or two, in the meantime any
> comments, questions, or suggestions are welcome as always.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5659) Allow CAST in an INTERVAL definition
[
https://issues.apache.org/jira/browse/CALCITE-5659?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17714231#comment-17714231
]
Oliver Lee commented on CALCITE-5659:
-
That's a good point Julian.
Based on the comment above about requiring parentheses around expressions it
seems that was put in place intentionally.
We could close this PR+ticket.
What are the complications if we relax the requirement for parentheses?
> Allow CAST in an INTERVAL definition
> -
>
> Key: CALCITE-5659
> URL: https://issues.apache.org/jira/browse/CALCITE-5659
> Project: Calcite
> Issue Type: Bug
>Reporter: Oliver Lee
>Assignee: Oliver Lee
>Priority: Major
> Labels: pull-request-available
> Time Spent: 10m
> Remaining Estimate: 0h
>
> Running queries that contain a CAST as part of the INTERVAL definition is
> throwing a parser error.
> i.e.
> {{INTERVAL CAST(5 as int64) YEAR}}
> We can make parser changes to allow the Cast function tokens to be accepted
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5661) Introduce another way to convert IN predicate to RelNode when IN list is large
[
https://issues.apache.org/jira/browse/CALCITE-5661?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17714230#comment-17714230
]
Julian Hyde commented on CALCITE-5661:
--
If that doesn't work, also consider using Sarg. It is an efficient
implementation of IN lists (in terms of the number of heap objects and heap
bytes to represent an IN-list of a given size) and may be able to convert dense
integer lists to ranges, e.g. 'x in (1, 2, 3, 4, 5, 10)' becomes 'x between 1
and 5 or x = 10'.
> Introduce another way to convert IN predicate to RelNode when IN list is large
> --
>
> Key: CALCITE-5661
> URL: https://issues.apache.org/jira/browse/CALCITE-5661
> Project: Calcite
> Issue Type: Improvement
>Affects Versions: 1.34.0
>Reporter: Runkang He
>Assignee: Runkang He
>Priority: Major
>
> When IN list is large, the plan generation is time-consuming, after
> benchmark, when the IN value list size was 3w, it took 2 minutes to generate
> the final plan.
> {code:sql}
> select empno from emp where deptno in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12,
> ..., 3){code}
> We find that in sql-to-rel phase, there are two methods to convert IN
> predicate to RelNode:
> 1.IN list size is below InSubQueryThreshold, convert IN to OR;
> 2.IN list size is over InSubQueryThreshold, convert IN to VALUES + JOIN.
> The first one will be very time-consuming in the expression simplification
> stage for the large OR predicate. As mentioned before, when the IN value list
> size was 3w, it took 2 minutes, which is not acceptable in OLAP scenarios.
> The second one will not be able to apply predicate pushdown, which it is very
> important in OLAP scenarios.
> So maybe we need to support converting IN to RexCall directly to avoid the
> disadvantages of the above two methods.
> After POC, when convert IN to RexCall directly, it takes less than 1 second
> to generate the final plan.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5646) JoinDeriveIsNotNullFilterRule incorrectly handles COALESCE in join condition
[
https://issues.apache.org/jira/browse/CALCITE-5646?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17714223#comment-17714223
]
Julian Hyde commented on CALCITE-5646:
--
[~lchistov1987],
{quote}
I think that if we know, that setting input I (of some inner join predicate JP)
to NULL will make JP equal to either NULL or FALSE, we may conclude that we can
push down IS NOT NULL check.
{quote}
That statement is valid, but it does not apply to COALESCE. We do not know that
setting x to null in join predicate "coalesce(x, y) = 'a'" will cause the
predicate to become null.
However, we know something else. If "coalesce(x, y) = 'a'" evaluates to
UNKNOWN, then x and y must both be UNKNOWN. This "backwards reasoning" is what
Strong does.
> JoinDeriveIsNotNullFilterRule incorrectly handles COALESCE in join condition
>
>
> Key: CALCITE-5646
> URL: https://issues.apache.org/jira/browse/CALCITE-5646
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.34.0
>Reporter: Leonid Chistov
>Assignee: Leonid Chistov
>Priority: Major
> Labels: pull-request-available
> Time Spent: 20m
> Remaining Estimate: 0h
>
> Consider query
> {code:java}
> select t1.deptno from empnullables t1 inner join
> empnullables t2 on coalesce(t1.ename, t2.ename) = 'abc' {code}
> When JoinDeriveIsNotNullFilterRule is applied to it, it is incorrectly
> transformed to query plan
> {code:java}
> LogicalProject(DEPTNO=[$7])
> LogicalJoin(condition=[=(CASE(IS NOT NULL($1), $1, $10), 'abc')],
> joinType=[inner])
> LogicalFilter(condition=[IS NOT NULL($1)])
> LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
> LogicalFilter(condition=[IS NOT NULL($1)])
> LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]]) {code}
> It is not valid to deduce that join keys from the both sides cannot have null
> values. All that we can deduce from the join condition, is that they cannot
> be null in the same time.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5646) JoinDeriveIsNotNullFilterRule incorrectly handles COALESCE in join condition
[
https://issues.apache.org/jira/browse/CALCITE-5646?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17714152#comment-17714152
]
Julian Hyde commented on CALCITE-5646:
--
I wonder whether the solution is to make COALESCE(x, y) strong in x and,
separately, strong in y.
Because if COALESCE(x, y) is always null, we know that x is always null, and y
is always null. Thus we can push down “select * from t1 join t2 on
coalesce(t1.a, t2.b) is null”.
But it’s not strong in (x, y).
> JoinDeriveIsNotNullFilterRule incorrectly handles COALESCE in join condition
>
>
> Key: CALCITE-5646
> URL: https://issues.apache.org/jira/browse/CALCITE-5646
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.34.0
>Reporter: Leonid Chistov
>Assignee: Leonid Chistov
>Priority: Major
> Labels: pull-request-available
> Time Spent: 20m
> Remaining Estimate: 0h
>
> Consider query
> {code:java}
> select t1.deptno from empnullables t1 inner join
> empnullables t2 on coalesce(t1.ename, t2.ename) = 'abc' {code}
> When JoinDeriveIsNotNullFilterRule is applied to it, it is incorrectly
> transformed to query plan
> {code:java}
> LogicalProject(DEPTNO=[$7])
> LogicalJoin(condition=[=(CASE(IS NOT NULL($1), $1, $10), 'abc')],
> joinType=[inner])
> LogicalFilter(condition=[IS NOT NULL($1)])
> LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
> LogicalFilter(condition=[IS NOT NULL($1)])
> LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]]) {code}
> It is not valid to deduce that join keys from the both sides cannot have null
> values. All that we can deduce from the join condition, is that they cannot
> be null in the same time.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5646) JoinDeriveIsNotNullFilterRule incorrectly handles COALESCE in join condition
[
https://issues.apache.org/jira/browse/CALCITE-5646?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17714124#comment-17714124
]
Leonid Chistov commented on CALCITE-5646:
-
[~zabetak]
I think that if we know, that setting input *I* (of some inner join predicate
{*}JP{*}) to NULL will make *JP* equal to either NULL or FALSE, we may conclude
that we can push down IS NOT NULL check.
I don't see a difference here between cases when *JP* turns NULL or FALSE, I
think that previous implementation just missed that opportunity.
> JoinDeriveIsNotNullFilterRule incorrectly handles COALESCE in join condition
>
>
> Key: CALCITE-5646
> URL: https://issues.apache.org/jira/browse/CALCITE-5646
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.34.0
>Reporter: Leonid Chistov
>Assignee: Leonid Chistov
>Priority: Major
> Labels: pull-request-available
> Time Spent: 20m
> Remaining Estimate: 0h
>
> Consider query
> {code:java}
> select t1.deptno from empnullables t1 inner join
> empnullables t2 on coalesce(t1.ename, t2.ename) = 'abc' {code}
> When JoinDeriveIsNotNullFilterRule is applied to it, it is incorrectly
> transformed to query plan
> {code:java}
> LogicalProject(DEPTNO=[$7])
> LogicalJoin(condition=[=(CASE(IS NOT NULL($1), $1, $10), 'abc')],
> joinType=[inner])
> LogicalFilter(condition=[IS NOT NULL($1)])
> LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
> LogicalFilter(condition=[IS NOT NULL($1)])
> LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]]) {code}
> It is not valid to deduce that join keys from the both sides cannot have null
> values. All that we can deduce from the join condition, is that they cannot
> be null in the same time.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Assigned] (CALCITE-5650) Obtain typesystem from dialect in RelToSqlConverterTest
[
https://issues.apache.org/jira/browse/CALCITE-5650?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Stamatis Zampetakis reassigned CALCITE-5650:
Assignee: Mihai Budiu
> Obtain typesystem from dialect in RelToSqlConverterTest
> ---
>
> Key: CALCITE-5650
> URL: https://issues.apache.org/jira/browse/CALCITE-5650
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.34.0
>Reporter: Mihai Budiu
>Assignee: Mihai Budiu
>Priority: Minor
> Labels: pull-request-available
> Time Spent: 1h
> Remaining Estimate: 0h
>
> The problem is in the file SqlToRelConverterTest.java, in the static class
> Sql, in the following method:
> Sql dialect(SqlDialect dialect) {
> return new Sql(schemaSpec, sql, dialect, parserConfig, librarySet,
> config,
> relFn, transforms, typeSystem);
> }
> The problem is that the dialect may have a different type system, which is
> ignored in this function. I think the function should read:
> Sql dialect(SqlDialect dialect) {
> return new Sql(schemaSpec, sql, dialect, parserConfig, librarySet,
> config,
> relFn, transforms, dialect.getTypeSystem());
> }
> Several tests in this file do change the dialect type system.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-5650) Obtain typesystem from dialect in RelToSqlConverterTest
[
https://issues.apache.org/jira/browse/CALCITE-5650?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Stamatis Zampetakis updated CALCITE-5650:
-
Summary: Obtain typesystem from dialect in RelToSqlConverterTest (was:
Sql.dialect method in RelToSqlConverterTests ignores dialect type system)
> Obtain typesystem from dialect in RelToSqlConverterTest
> ---
>
> Key: CALCITE-5650
> URL: https://issues.apache.org/jira/browse/CALCITE-5650
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.34.0
>Reporter: Mihai Budiu
>Priority: Minor
> Labels: pull-request-available
> Time Spent: 1h
> Remaining Estimate: 0h
>
> The problem is in the file SqlToRelConverterTest.java, in the static class
> Sql, in the following method:
> Sql dialect(SqlDialect dialect) {
> return new Sql(schemaSpec, sql, dialect, parserConfig, librarySet,
> config,
> relFn, transforms, typeSystem);
> }
> The problem is that the dialect may have a different type system, which is
> ignored in this function. I think the function should read:
> Sql dialect(SqlDialect dialect) {
> return new Sql(schemaSpec, sql, dialect, parserConfig, librarySet,
> config,
> relFn, transforms, dialect.getTypeSystem());
> }
> Several tests in this file do change the dialect type system.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Resolved] (CALCITE-5650) Obtain typesystem from dialect in RelToSqlConverterTest
[
https://issues.apache.org/jira/browse/CALCITE-5650?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Stamatis Zampetakis resolved CALCITE-5650.
--
Fix Version/s: 1.35.0
Resolution: Fixed
Fixed in
https://github.com/apache/calcite/commit/e7375ae745ec18ce9df68b4945bb521ae49a053c.
Thanks for the PR [~mbudiu] and for the review [~julianhyde]!
> Obtain typesystem from dialect in RelToSqlConverterTest
> ---
>
> Key: CALCITE-5650
> URL: https://issues.apache.org/jira/browse/CALCITE-5650
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.34.0
>Reporter: Mihai Budiu
>Assignee: Mihai Budiu
>Priority: Minor
> Labels: pull-request-available
> Fix For: 1.35.0
>
> Time Spent: 1h
> Remaining Estimate: 0h
>
> The problem is in the file SqlToRelConverterTest.java, in the static class
> Sql, in the following method:
> Sql dialect(SqlDialect dialect) {
> return new Sql(schemaSpec, sql, dialect, parserConfig, librarySet,
> config,
> relFn, transforms, typeSystem);
> }
> The problem is that the dialect may have a different type system, which is
> ignored in this function. I think the function should read:
> Sql dialect(SqlDialect dialect) {
> return new Sql(schemaSpec, sql, dialect, parserConfig, librarySet,
> config,
> relFn, transforms, dialect.getTypeSystem());
> }
> Several tests in this file do change the dialect type system.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5646) JoinDeriveIsNotNullFilterRule incorrectly handles COALESCE in join condition
[
https://issues.apache.org/jira/browse/CALCITE-5646?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17714066#comment-17714066
]
Stamatis Zampetakis commented on CALCITE-5646:
--
{{Strong.isStrong}} is a static util on top of {{Strong.isNull}}; I find it a
bit unfortunate that they have different names but anyways I agree that's not
the main point here.
There are two changes in the current PR:
* Consider nullability of inputs in isolation for introducing the {{IS NOT
NULL}} predicate; previously the rule considered that all inputs are NULL
* Introduce the IS NOT NULL predicate when the join predicate expression is
NULL or FALSE; previously the rule only checked for the join predicate to be
NULL (didn't care for FALSE)
I understand the first change and the PR provides a test demonstrating the
problem.
[~lchistov1987] what is the motivation behind the second change? Is there an
example/test case for this?
> JoinDeriveIsNotNullFilterRule incorrectly handles COALESCE in join condition
>
>
> Key: CALCITE-5646
> URL: https://issues.apache.org/jira/browse/CALCITE-5646
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.34.0
>Reporter: Leonid Chistov
>Assignee: Leonid Chistov
>Priority: Major
> Labels: pull-request-available
> Time Spent: 20m
> Remaining Estimate: 0h
>
> Consider query
> {code:java}
> select t1.deptno from empnullables t1 inner join
> empnullables t2 on coalesce(t1.ename, t2.ename) = 'abc' {code}
> When JoinDeriveIsNotNullFilterRule is applied to it, it is incorrectly
> transformed to query plan
> {code:java}
> LogicalProject(DEPTNO=[$7])
> LogicalJoin(condition=[=(CASE(IS NOT NULL($1), $1, $10), 'abc')],
> joinType=[inner])
> LogicalFilter(condition=[IS NOT NULL($1)])
> LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
> LogicalFilter(condition=[IS NOT NULL($1)])
> LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]]) {code}
> It is not valid to deduce that join keys from the both sides cannot have null
> values. All that we can deduce from the join condition, is that they cannot
> be null in the same time.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5615) Run SQLLogicTests using Calcite
[
https://issues.apache.org/jira/browse/CALCITE-5615?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17714053#comment-17714053
]
Stamatis Zampetakis commented on CALCITE-5615:
--
I raised PR (https://github.com/hydromatic/sql-logic-test/pull/3) for the item
below.
{quote}* Physically include the files from master.zip, rather than downloading
each time.{quote}
> Run SQLLogicTests using Calcite
> ---
>
> Key: CALCITE-5615
> URL: https://issues.apache.org/jira/browse/CALCITE-5615
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.34.0
>Reporter: Mihai Budiu
>Assignee: Mihai Budiu
>Priority: Minor
> Labels: pull-request-available
> Time Spent: 1h 20m
> Remaining Estimate: 0h
>
> Sqllogictest is a program designed to verify that an SQL database engine
> computes correct results by comparing the results to identical queries from
> other SQL database engines.
> https://www.sqlite.org/sqllogictest/doc/trunk/about.wiki
> The nice thing about SLT is that it contains more than 7 million tests. The
> tests only cover the core of SQL, ideally the portable part across all
> engines. They only test integers, doubles, and strings. So they could
> probably be part of the Calcite slow tests.
> The tests should be structured so that any query execution engine can be used.
> I plan to contribute such an implementation if people think it is useful, but
> I haven't yet worked out all the details.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5630) Window with rows equivalence error in volcano planner
[
https://issues.apache.org/jira/browse/CALCITE-5630?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17714012#comment-17714012
]
Jiajun Xie commented on CALCITE-5630:
-
HepPlanner has same issue
[https://github.com/apache/calcite/blob/2d0b3acb11169b307dc165c1fae8b7c92b888ae9/core/src/main/java/org/apache/calcite/plan/hep/HepPlanner.java#L830]
> Window with rows equivalence error in volcano planner
> -
>
> Key: CALCITE-5630
> URL: https://issues.apache.org/jira/browse/CALCITE-5630
> Project: Calcite
> Issue Type: Bug
>Reporter: Jiajun Xie
>Priority: Major
>
> Here is UT
> {code:java}
> @Test void testUnionWindow() {
> String selectSqlBase =
> "SELECT 'col{?}' as col, sum(\"salary\" ) OVER("
> + "PARTITION BY \"deptno\" "
> + "ORDER BY \"empid\" "
> + "ROWS BETWEEN CURRENT ROW AND {?} FOLLOWING) "
> + "FROM \"hr\".\"emps\"";
> String selectSql1 = selectSqlBase.replace("{?}", "1"); // window1
> String selectSql2 = selectSqlBase.replace("{?}", "2"); // window2
> String sql = selectSql1 + "\nunion all\n" + selectSql2;
> CalciteAssert.hr().query(sql).runs()
> .returns("COL=col1; EXPR$1=8000.0\n"
> + "COL=col1; EXPR$1=21500.0\n"
> + "COL=col1; EXPR$1=18500.0\n"
> + "COL=col1; EXPR$1=7000.0\n"
> + "COL=col2; EXPR$1=8000.0\n"
> + "COL=col2; EXPR$1=21500.0\n"
> + "COL=col2; EXPR$1=18500.0\n"
> + "COL=col2; EXPR$1=7000.0\n");
> // sum that for col1 is same as sum that for col2, this is error
> }
> {code}
> Because RelDigest is same between window1 and window2[1],
> volcano planner use window1 replace window2.
>
> [[1]
> https://github.com/apache/calcite/blob/2d0b3acb11169b307dc165c1fae8b7c92b888ae9/core/src/main/java/org/apache/calcite/plan/volcano/VolcanoPlanner.java#L1289|https://github.com/apache/calcite/blob/2d0b3acb11169b307dc165c1fae8b7c92b888ae9/core/src/main/java/org/apache/calcite/plan/volcano/VolcanoPlanner.java#L1289]
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
