[jira] [Resolved] (CALCITE-5906) JDBC adapter should generate TABLESAMPLE
[
https://issues.apache.org/jira/browse/CALCITE-5906?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Jiajun Xie resolved CALCITE-5906.
-
Resolution: Fixed
> JDBC adapter should generate TABLESAMPLE
>
>
> Key: CALCITE-5906
> URL: https://issues.apache.org/jira/browse/CALCITE-5906
> Project: Calcite
> Issue Type: Bug
>Reporter: LakeShen
>Assignee: LakeShen
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.36.0
>
> Attachments: image-2023-08-08-20-16-27-188.png,
> image-2023-08-08-20-19-01-269.png
>
>
> Currently in the RelToSqlConverter class, there is no visit method to handle
> the Sample RelNode.If a SQL plan has Sample RelNode, RelToSqlConverter will
> throw a AssertionError like this:
> {code:java}
> java.lang.AssertionError: Need to implement
> org.apache.calcite.rel.core.Sample
> {code}
> This will cause the Sample RelNode plan to fail to translate to the
> corresponding dialect.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5906) JDBC adapter should generate TABLESAMPLE
[
https://issues.apache.org/jira/browse/CALCITE-5906?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17754828#comment-17754828
]
Jiajun Xie commented on CALCITE-5906:
-
Fixed via
[51da34b.|https://github.com/apache/calcite/commit/51da34bdf91e602f32f965b3490bace026d51ef4]
Thank [~shenlang] for your PR.
Thank [~julianhyde] for your review.
> JDBC adapter should generate TABLESAMPLE
>
>
> Key: CALCITE-5906
> URL: https://issues.apache.org/jira/browse/CALCITE-5906
> Project: Calcite
> Issue Type: Bug
>Reporter: LakeShen
>Assignee: LakeShen
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.36.0
>
> Attachments: image-2023-08-08-20-16-27-188.png,
> image-2023-08-08-20-19-01-269.png
>
>
> Currently in the RelToSqlConverter class, there is no visit method to handle
> the Sample RelNode.If a SQL plan has Sample RelNode, RelToSqlConverter will
> throw a AssertionError like this:
> {code:java}
> java.lang.AssertionError: Need to implement
> org.apache.calcite.rel.core.Sample
> {code}
> This will cause the Sample RelNode plan to fail to translate to the
> corresponding dialect.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5873) Add REGEXP_CONTAINS function (enabled in BigQuery library)
[ https://issues.apache.org/jira/browse/CALCITE-5873?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17754753#comment-17754753 ] Julian Hyde commented on CALCITE-5873: -- The decision is made. We use Java regex (not re2) for now. > Add REGEXP_CONTAINS function (enabled in BigQuery library) > -- > > Key: CALCITE-5873 > URL: https://issues.apache.org/jira/browse/CALCITE-5873 > Project: Calcite > Issue Type: Task >Reporter: Jerin John >Assignee: Jerin John >Priority: Major > Labels: pull-request-available > > Add support for REGEXP_CONTAINS function from BigQuery. > Function returns TRUE if input value is a partial match for the regular > expression. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-5910) Add REGEXP_EXTRACT and REGEXP_SUBSTR functions (enabled in BigQuery library)
[
https://issues.apache.org/jira/browse/CALCITE-5910?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17754752#comment-17754752
]
Julian Hyde commented on CALCITE-5910:
--
No one has complained in five days, so we have a decision:
* All {{REGEXP_xxx}} functions (including those originating in BigQuery) will
use Java regular expressions.
* We can revisit this decision later, if necessary. If we change to re2 it will
not be a major change in specification.
> Add REGEXP_EXTRACT and REGEXP_SUBSTR functions (enabled in BigQuery library)
>
>
> Key: CALCITE-5910
> URL: https://issues.apache.org/jira/browse/CALCITE-5910
> Project: Calcite
> Issue Type: Task
>Reporter: Jerin John
>Assignee: Jerin John
>Priority: Major
> Labels: pull-request-available
>
> Add support for
> [REGEXP_EXTRACT|https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#regexp_extract]
> and
> [REGEXP_SUBSTR|https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#regexp_substr]
> functions from BigQuery.
> *{{REGEXP_EXTRACT(value, regexp[, position[, occurrence]])}}*
> Returns the substring in {{value}} that matches the regular expression
> {{{}regexp{}}}. Returns {{NULL}} if there is no match.
> * If the regular expression contains a capturing group ({{{}(...){}}}), and
> there is a match for that capturing group, that match is returned. If there
> are multiple matches for a capturing group, the last match is returned.
> * If {{position}} is specified, the search starts at this position in
> {{{}value{}}}, otherwise it starts at the beginning of {{{}value{}}}.
> * If {{occurrence}} is specified, the search returns a specific occurrence
> of the {{regexp}} in {{{}value{}}}, otherwise returns the first match.
>
> *{{REGEXP_SUBSTR(value, regexp[, position[, occurrence]])}}*
> Synonym for REGEXP_EXTRACT
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5743) Query gives incorrect result when COUNT appears in the correlated subquery select list
[
https://issues.apache.org/jira/browse/CALCITE-5743?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17754670#comment-17754670
]
Julian Hyde commented on CALCITE-5743:
--
[~leepb], Ok, I agree, that’s a different issue, because the subquery is
uncorrelated.
Can you give a repro case on the standard EMP and DEPT tables (not arbitrary
tables that happen to be named EMP and DEPT). I believe [~shenlang] wanted to
repro this without doing DDL.
> Query gives incorrect result when COUNT appears in the correlated subquery
> select list
> --
>
> Key: CALCITE-5743
> URL: https://issues.apache.org/jira/browse/CALCITE-5743
> Project: Calcite
> Issue Type: Bug
>Reporter: libopeng
>Priority: Major
>
> {code:java}
> SELECT a
> FROM t1 t1
> WHERE b IN (SELECT COUNT (*) FROM t2 WHERE t1.a=t2.a); {code}
> {code:java}
> t1 | t2
> +--+ | +-+
> | a | b | | | a |
> +--+ | +-+
> | 3 | 6 | | | 3 |
> | 10 | 1 | | | 3 |
> | 8 | 0 | | | 10 |
>|
> {code}
> correct result
> {code:java}
> +--+
> | a |
> +--+
> | 10 |
> | 8 |{code}
> after decorrelate
> {code:java}
> LogicalProject(A=[$0])
> LogicalJoin(condition=[AND(=($0, $3), =($1, $2))], joinType=[inner])
> LogicalTableScan(table=[[t1]])
> LogicalFilter(condition=[=($0, $0)])
> LogicalProject(EXPR$0=[$1], a=[$0])
> LogicalAggregate(group=[{0}], EXPR$0=[COUNT()])
> LogicalProject(a=[$0])
> LogicalFilter(condition=[=($0, $0)])
> LogicalTableScan(table=[[t2]]) {code}
> error result
> {code:java}
> +--+
> | a |
> +--+
> | 10 | {code}
> Data with count=0 will be lost
> This issue was discovered in [this
> issue|https://issues.apache.org/jira/projects/CALCITE/issues/CALCITE-5568]
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5921) SqlOperatorFixture.checkFails and checkAggFails don't check runtime failure
[ https://issues.apache.org/jira/browse/CALCITE-5921?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17754666#comment-17754666 ] Runkang He commented on CALCITE-5921: - [~julianhyde] Thanks for linking the related issue. I have quickly read the two related issues, and after that, I think this issue may be not directly related to them. It looks like it was introduced by test fixture framework in CALCITE-4885. For more, the basic idea to fix this is to add `tester.checkFails(factory, sap, expectedError, true)` in SqlOperatorFixture.checkFails when runtime is true. > SqlOperatorFixture.checkFails and checkAggFails don't check runtime failure > --- > > Key: CALCITE-5921 > URL: https://issues.apache.org/jira/browse/CALCITE-5921 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.35.0 >Reporter: Runkang He >Assignee: Runkang He >Priority: Major > > SqlOperatorFixture.checkFails and checkAggFails don't check runtime failure. > See more in [code > line|https://github.com/apache/calcite/blob/50c3edfc3d6630528ab51fe836bd50df82cc7db8/testkit/src/main/java/org/apache/calcite/test/SqlOperatorFixtureImpl.java#L160]. > When the parameter `runtime` of SqlOperatorFixture.checkFails is true, it > should execute the query and check runtime failure, but currently it ignores > this, and only checks the parse and validation failure. > When fix this, there are 4 failed test cases in CalciteSqlOperatorTest. > At last, this issue was found when to implement `BIT_GET` function in > CALCITE-5848. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-5921) SqlOperatorFixture.checkFails and checkAggFails don't check runtime failure
[ https://issues.apache.org/jira/browse/CALCITE-5921?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17754666#comment-17754666 ] Runkang He edited comment on CALCITE-5921 at 8/15/23 2:52 PM: -- [~julianhyde] Thanks for linking the related issue. I have quickly read the two related issues, and after that, I think this issue may be not directly related to them. It looks like it was introduced by test fixture framework in CALCITE-4885. For more, the basic idea to fix this is to add `tester.checkFails(factory, sap, expectedError, true)` in SqlOperatorFixture.checkFails when runtime is true. was (Author: JIRAUSER280488): [~julianhyde] Thanks for linking the related issue. I have quickly read the two related issues, and after that, I think this issue may be not directly related to them. It looks like it was introduced by test fixture framework in CALCITE-4885. For more, the basic idea to fix this is to add `tester.checkFails(factory, sap, expectedError, true)` in SqlOperatorFixture.checkFails when runtime is true. > SqlOperatorFixture.checkFails and checkAggFails don't check runtime failure > --- > > Key: CALCITE-5921 > URL: https://issues.apache.org/jira/browse/CALCITE-5921 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.35.0 >Reporter: Runkang He >Assignee: Runkang He >Priority: Major > > SqlOperatorFixture.checkFails and checkAggFails don't check runtime failure. > See more in [code > line|https://github.com/apache/calcite/blob/50c3edfc3d6630528ab51fe836bd50df82cc7db8/testkit/src/main/java/org/apache/calcite/test/SqlOperatorFixtureImpl.java#L160]. > When the parameter `runtime` of SqlOperatorFixture.checkFails is true, it > should execute the query and check runtime failure, but currently it ignores > this, and only checks the parse and validation failure. > When fix this, there are 4 failed test cases in CalciteSqlOperatorTest. > At last, this issue was found when to implement `BIT_GET` function in > CALCITE-5848. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-5930) When data types do not match, calcite may make a calculation error
[
https://issues.apache.org/jira/browse/CALCITE-5930?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
libopeng updated CALCITE-5930:
--
Description:
Wrong calculation result
{code:java}
select *
from dept
where deptno in (
select count(*)
from emp
where comm is null);
+++--+
| DEPTNO | DNAME| LOC|
+++--+
+++--+ {code}
Correct calculation results
{code:java}
select *
from dept
where cast(deptno as bigint) in (
select count(*)
from emp
where comm is null);
+++--+
| DEPTNO | DNAME | LOC |
+++--+
| 10 | ACCOUNTING | NEW YORK |
+++--+{code}
The above two cases can be reproduced in agg.iq.
Or look at this submission
https://github.com/apache/calcite/commit/6cef259f29884fbda3f165d579232285998d1b46.
was:
Wrong calculation result
{code:java}
select *
from dept
where deptno in (
select count(*)
from emp
where comm is null);
+++--+
| DEPTNO | DNAME| LOC|
+++--+
+++--+ {code}
Correct calculation results
{code:java}
select *
from dept
where cast(deptno as bigint) in (
select count(*)
from emp
where comm is null);
+++--+
| DEPTNO | DNAME | LOC |
+++--+
| 10 | ACCOUNTING | NEW YORK |
+++--+{code}
The above two cases can be reproduced in agg.iq.
> When data types do not match, calcite may make a calculation error
> --
>
> Key: CALCITE-5930
> URL: https://issues.apache.org/jira/browse/CALCITE-5930
> Project: Calcite
> Issue Type: Bug
>Reporter: libopeng
>Priority: Major
>
> Wrong calculation result
> {code:java}
> select *
> from dept
> where deptno in (
> select count(*)
> from emp
> where comm is null);
> +++--+
> | DEPTNO | DNAME| LOC|
> +++--+
> +++--+ {code}
> Correct calculation results
> {code:java}
> select *
> from dept
> where cast(deptno as bigint) in (
> select count(*)
> from emp
> where comm is null);
> +++--+
> | DEPTNO | DNAME | LOC |
> +++--+
> | 10 | ACCOUNTING | NEW YORK |
> +++--+{code}
> The above two cases can be reproduced in agg.iq.
> Or look at this submission
> https://github.com/apache/calcite/commit/6cef259f29884fbda3f165d579232285998d1b46.
>
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-5930) When data types do not match, calcite may make a calculation error
[
https://issues.apache.org/jira/browse/CALCITE-5930?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17754661#comment-17754661
]
libopeng edited comment on CALCITE-5930 at 8/15/23 2:33 PM:
[~shenlang] I have refined some information which I hope will be helpful to you.
was (Author: JIRAUSER298961):
[~leepb] I have refined some information which I hope will be helpful to you.
> When data types do not match, calcite may make a calculation error
> --
>
> Key: CALCITE-5930
> URL: https://issues.apache.org/jira/browse/CALCITE-5930
> Project: Calcite
> Issue Type: Bug
>Reporter: libopeng
>Priority: Major
>
> Wrong calculation result
> {code:java}
> select *
> from dept
> where deptno in (
> select count(*)
> from emp
> where comm is null);
> +++--+
> | DEPTNO | DNAME| LOC|
> +++--+
> +++--+ {code}
> Correct calculation results
> {code:java}
> select *
> from dept
> where cast(deptno as bigint) in (
> select count(*)
> from emp
> where comm is null);
> +++--+
> | DEPTNO | DNAME | LOC |
> +++--+
> | 10 | ACCOUNTING | NEW YORK |
> +++--+{code}
> The above two cases can be reproduced in agg.iq.
>
>
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5930) When data types do not match, calcite may make a calculation error
[
https://issues.apache.org/jira/browse/CALCITE-5930?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17754661#comment-17754661
]
libopeng commented on CALCITE-5930:
---
[~leepb] I have refined some information which I hope will be helpful to you.
> When data types do not match, calcite may make a calculation error
> --
>
> Key: CALCITE-5930
> URL: https://issues.apache.org/jira/browse/CALCITE-5930
> Project: Calcite
> Issue Type: Bug
>Reporter: libopeng
>Priority: Major
>
> Wrong calculation result
> {code:java}
> select *
> from dept
> where deptno in (
> select count(*)
> from emp
> where comm is null);
> +++--+
> | DEPTNO | DNAME| LOC|
> +++--+
> +++--+ {code}
> Correct calculation results
> {code:java}
> select *
> from dept
> where cast(deptno as bigint) in (
> select count(*)
> from emp
> where comm is null);
> +++--+
> | DEPTNO | DNAME | LOC |
> +++--+
> | 10 | ACCOUNTING | NEW YORK |
> +++--+{code}
> The above two cases can be reproduced in agg.iq.
>
>
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-5930) When data types do not match, calcite may make a calculation error
[
https://issues.apache.org/jira/browse/CALCITE-5930?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
libopeng updated CALCITE-5930:
--
Description:
Wrong calculation result
{code:java}
select *
from dept
where deptno in (
select count(*)
from emp
where comm is null);
+++--+
| DEPTNO | DNAME| LOC|
+++--+
+++--+ {code}
Correct calculation results
{code:java}
select *
from dept
where cast(deptno as bigint) in (
select count(*)
from emp
where comm is null);
+++--+
| DEPTNO | DNAME | LOC |
+++--+
| 10 | ACCOUNTING | NEW YORK |
+++--+{code}
The above two cases can be reproduced in agg.iq.
was:
Wrong calculation result
{code:java}
select *
from dept
where deptno in (
select count(*)
from emp
where comm is null);
+++--+
| DEPTNO | DNAME| LOC|
+++--+
+++--+ {code}
Correct calculation results
{code:java}
select *
from dept
where cast(deptno as bigint) in (
select count(*)
from emp
where comm is null);
+++--+
| DEPTNO | DNAME | LOC |
+++--+
| 10 | ACCOUNTING | NEW YORK |
+++--+{code}
The above two cases can be reproduced in agg.iq.
> When data types do not match, calcite may make a calculation error
> --
>
> Key: CALCITE-5930
> URL: https://issues.apache.org/jira/browse/CALCITE-5930
> Project: Calcite
> Issue Type: Bug
>Reporter: libopeng
>Priority: Major
>
> Wrong calculation result
> {code:java}
> select *
> from dept
> where deptno in (
> select count(*)
> from emp
> where comm is null);
> +++--+
> | DEPTNO | DNAME| LOC|
> +++--+
> +++--+ {code}
> Correct calculation results
> {code:java}
> select *
> from dept
> where cast(deptno as bigint) in (
> select count(*)
> from emp
> where comm is null);
> +++--+
> | DEPTNO | DNAME | LOC |
> +++--+
> | 10 | ACCOUNTING | NEW YORK |
> +++--+{code}
> The above two cases can be reproduced in agg.iq.
>
>
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-5930) When data types do not match, calcite may make a calculation error
[
https://issues.apache.org/jira/browse/CALCITE-5930?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
libopeng updated CALCITE-5930:
--
Description:
Wrong calculation result
{code:java}
select *
from dept
where deptno in (
select count(*)
from emp
where comm is null);
+++--+
| DEPTNO | DNAME| LOC|
+++--+
+++--+ {code}
Correct calculation results
{code:java}
select *
from dept
where cast(deptno as bigint) in (
select count(*)
from emp
where comm is null);
+++--+
| DEPTNO | DNAME | LOC |
+++--+
| 10 | ACCOUNTING | NEW YORK |
+++--+{code}
The above two cases can be reproduced in agg.iq.
was:
{code:java}
select *
from dept
where deptno in (
select count(*)
from emp
where comm is null);
+++--+
| DEPTNO | DNAME| LOC|
+++--+
+++--+ {code}
{code:java}
select *
from dept
where cast(deptno as bigint) in (
select count(*)
from emp
where comm is null);
+++--+
| DEPTNO | DNAME | LOC |
+++--+
| 10 | ACCOUNTING | NEW YORK |
+++--+{code}
> When data types do not match, calcite may make a calculation error
> --
>
> Key: CALCITE-5930
> URL: https://issues.apache.org/jira/browse/CALCITE-5930
> Project: Calcite
> Issue Type: Bug
>Reporter: libopeng
>Priority: Major
>
> Wrong calculation result
> {code:java}
> select *
> from dept
> where deptno in (
> select count(*)
> from emp
> where comm is null);
> +++--+
> | DEPTNO | DNAME| LOC|
> +++--+
> +++--+ {code}
> Correct calculation results
> {code:java}
> select *
> from dept
> where cast(deptno as bigint) in (
> select count(*)
> from emp
> where comm is null);
> +++--+
> | DEPTNO | DNAME | LOC |
> +++--+
> | 10 | ACCOUNTING | NEW YORK |
> +++--+{code}
> The above two cases can be reproduced in agg.iq.
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5930) When data types do not match, calcite may make a calculation error
[
https://issues.apache.org/jira/browse/CALCITE-5930?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17754657#comment-17754657
]
LakeShen commented on CALCITE-5930:
---
Hi [~leepb] ,could you make the description more clearly,like which one is
right, which one is wrong,and a few other details.
So others could be better to understand the problem.
> When data types do not match, calcite may make a calculation error
> --
>
> Key: CALCITE-5930
> URL: https://issues.apache.org/jira/browse/CALCITE-5930
> Project: Calcite
> Issue Type: Bug
>Reporter: libopeng
>Priority: Major
>
> {code:java}
> select *
> from dept
> where deptno in (
> select count(*)
> from emp
> where comm is null);
> +++--+
> | DEPTNO | DNAME| LOC|
> +++--+
> +++--+ {code}
> {code:java}
> select *
> from dept
> where cast(deptno as bigint) in (
> select count(*)
> from emp
> where comm is null);
> +++--+
> | DEPTNO | DNAME | LOC |
> +++--+
> | 10 | ACCOUNTING | NEW YORK |
> +++--+{code}
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-5930) When data types do not match, calcite may make a calculation error
[
https://issues.apache.org/jira/browse/CALCITE-5930?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
libopeng updated CALCITE-5930:
--
Description:
{code:java}
select *
from dept
where deptno in (
select count(*)
from emp
where comm is null);
+++--+
| DEPTNO | DNAME| LOC|
+++--+
+++--+ {code}
{code:java}
select *
from dept
where cast(deptno as bigint) in (
select count(*)
from emp
where comm is null);
+++--+
| DEPTNO | DNAME | LOC |
+++--+
| 10 | ACCOUNTING | NEW YORK |
+++--+{code}
was:
select *
from dept
where deptno in (
select count(*)
from emp
where comm is null);
+++--+
| DEPTNO | DNAME| LOC|
+++--+
+++--+
{code:java}
select *
from dept
where cast(deptno as bigint) in (
select count(*)
from emp
where comm is null);
+++--+
| DEPTNO | DNAME | LOC |
+++--+
| 10 | ACCOUNTING | NEW YORK |
+++--+{code}
> When data types do not match, calcite may make a calculation error
> --
>
> Key: CALCITE-5930
> URL: https://issues.apache.org/jira/browse/CALCITE-5930
> Project: Calcite
> Issue Type: Bug
>Reporter: libopeng
>Priority: Major
>
> {code:java}
> select *
> from dept
> where deptno in (
> select count(*)
> from emp
> where comm is null);
> +++--+
> | DEPTNO | DNAME| LOC|
> +++--+
> +++--+ {code}
> {code:java}
> select *
> from dept
> where cast(deptno as bigint) in (
> select count(*)
> from emp
> where comm is null);
> +++--+
> | DEPTNO | DNAME | LOC |
> +++--+
> | 10 | ACCOUNTING | NEW YORK |
> +++--+{code}
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5743) Query gives incorrect result when COUNT appears in the correlated subquery select list
[
https://issues.apache.org/jira/browse/CALCITE-5743?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17754647#comment-17754647
]
libopeng commented on CALCITE-5743:
---
Look at this issue [CALCITE-5930]
> Query gives incorrect result when COUNT appears in the correlated subquery
> select list
> --
>
> Key: CALCITE-5743
> URL: https://issues.apache.org/jira/browse/CALCITE-5743
> Project: Calcite
> Issue Type: Bug
>Reporter: libopeng
>Priority: Major
>
> {code:java}
> SELECT a
> FROM t1 t1
> WHERE b IN (SELECT COUNT (*) FROM t2 WHERE t1.a=t2.a); {code}
> {code:java}
> t1 | t2
> +--+ | +-+
> | a | b | | | a |
> +--+ | +-+
> | 3 | 6 | | | 3 |
> | 10 | 1 | | | 3 |
> | 8 | 0 | | | 10 |
>|
> {code}
> correct result
> {code:java}
> +--+
> | a |
> +--+
> | 10 |
> | 8 |{code}
> after decorrelate
> {code:java}
> LogicalProject(A=[$0])
> LogicalJoin(condition=[AND(=($0, $3), =($1, $2))], joinType=[inner])
> LogicalTableScan(table=[[t1]])
> LogicalFilter(condition=[=($0, $0)])
> LogicalProject(EXPR$0=[$1], a=[$0])
> LogicalAggregate(group=[{0}], EXPR$0=[COUNT()])
> LogicalProject(a=[$0])
> LogicalFilter(condition=[=($0, $0)])
> LogicalTableScan(table=[[t2]]) {code}
> error result
> {code:java}
> +--+
> | a |
> +--+
> | 10 | {code}
> Data with count=0 will be lost
> This issue was discovered in [this
> issue|https://issues.apache.org/jira/projects/CALCITE/issues/CALCITE-5568]
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-5930) When data types do not match, calcite may make a calculation error
libopeng created CALCITE-5930:
-
Summary: When data types do not match, calcite may make a
calculation error
Key: CALCITE-5930
URL: https://issues.apache.org/jira/browse/CALCITE-5930
Project: Calcite
Issue Type: Bug
Reporter: libopeng
select *
from dept
where deptno in (
select count(*)
from emp
where comm is null);
+++--+
| DEPTNO | DNAME| LOC|
+++--+
+++--+
{code:java}
select *
from dept
where cast(deptno as bigint) in (
select count(*)
from emp
where comm is null);
+++--+
| DEPTNO | DNAME | LOC |
+++--+
| 10 | ACCOUNTING | NEW YORK |
+++--+{code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5743) Query gives incorrect result when COUNT appears in the correlated subquery select list
[
https://issues.apache.org/jira/browse/CALCITE-5743?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17754530#comment-17754530
]
libopeng commented on CALCITE-5743:
---
[~julianhyde] I think your case is caused by other bugs, and what is discussed
in this issue is the decorrelation bug, there is no associated subquery in your
case.
> Query gives incorrect result when COUNT appears in the correlated subquery
> select list
> --
>
> Key: CALCITE-5743
> URL: https://issues.apache.org/jira/browse/CALCITE-5743
> Project: Calcite
> Issue Type: Bug
>Reporter: libopeng
>Priority: Major
>
> {code:java}
> SELECT a
> FROM t1 t1
> WHERE b IN (SELECT COUNT (*) FROM t2 WHERE t1.a=t2.a); {code}
> {code:java}
> t1 | t2
> +--+ | +-+
> | a | b | | | a |
> +--+ | +-+
> | 3 | 6 | | | 3 |
> | 10 | 1 | | | 3 |
> | 8 | 0 | | | 10 |
>|
> {code}
> correct result
> {code:java}
> +--+
> | a |
> +--+
> | 10 |
> | 8 |{code}
> after decorrelate
> {code:java}
> LogicalProject(A=[$0])
> LogicalJoin(condition=[AND(=($0, $3), =($1, $2))], joinType=[inner])
> LogicalTableScan(table=[[t1]])
> LogicalFilter(condition=[=($0, $0)])
> LogicalProject(EXPR$0=[$1], a=[$0])
> LogicalAggregate(group=[{0}], EXPR$0=[COUNT()])
> LogicalProject(a=[$0])
> LogicalFilter(condition=[=($0, $0)])
> LogicalTableScan(table=[[t2]]) {code}
> error result
> {code:java}
> +--+
> | a |
> +--+
> | 10 | {code}
> Data with count=0 will be lost
> This issue was discovered in [this
> issue|https://issues.apache.org/jira/projects/CALCITE/issues/CALCITE-5568]
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5743) Query gives incorrect result when COUNT appears in the correlated subquery select list
[
https://issues.apache.org/jira/browse/CALCITE-5743?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17754512#comment-17754512
]
LakeShen commented on CALCITE-5743:
---
Hi [~julianhyde] ,thanks for your reply,and I will debug with your sql example
later.
> Query gives incorrect result when COUNT appears in the correlated subquery
> select list
> --
>
> Key: CALCITE-5743
> URL: https://issues.apache.org/jira/browse/CALCITE-5743
> Project: Calcite
> Issue Type: Bug
>Reporter: libopeng
>Priority: Major
>
> {code:java}
> SELECT a
> FROM t1 t1
> WHERE b IN (SELECT COUNT (*) FROM t2 WHERE t1.a=t2.a); {code}
> {code:java}
> t1 | t2
> +--+ | +-+
> | a | b | | | a |
> +--+ | +-+
> | 3 | 6 | | | 3 |
> | 10 | 1 | | | 3 |
> | 8 | 0 | | | 10 |
>|
> {code}
> correct result
> {code:java}
> +--+
> | a |
> +--+
> | 10 |
> | 8 |{code}
> after decorrelate
> {code:java}
> LogicalProject(A=[$0])
> LogicalJoin(condition=[AND(=($0, $3), =($1, $2))], joinType=[inner])
> LogicalTableScan(table=[[t1]])
> LogicalFilter(condition=[=($0, $0)])
> LogicalProject(EXPR$0=[$1], a=[$0])
> LogicalAggregate(group=[{0}], EXPR$0=[COUNT()])
> LogicalProject(a=[$0])
> LogicalFilter(condition=[=($0, $0)])
> LogicalTableScan(table=[[t2]]) {code}
> error result
> {code:java}
> +--+
> | a |
> +--+
> | 10 | {code}
> Data with count=0 will be lost
> This issue was discovered in [this
> issue|https://issues.apache.org/jira/projects/CALCITE/issues/CALCITE-5568]
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5889) Add the RelRule that converts Minus into UNION ALL..GROUP BY...WHERE
[ https://issues.apache.org/jira/browse/CALCITE-5889?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17754442#comment-17754442 ] LakeShen commented on CALCITE-5889: --- The PR is ready,link:[https://github.com/apache/calcite/pull/3367] if someone have time, please help me to review it, thank you very much:) > Add the RelRule that converts Minus into UNION ALL..GROUP BY...WHERE > > > Key: CALCITE-5889 > URL: https://issues.apache.org/jira/browse/CALCITE-5889 > Project: Calcite > Issue Type: New Feature > Components: core >Reporter: LakeShen >Assignee: LakeShen >Priority: Major > Labels: pull-request-available > Fix For: 1.36.0 > > > There are many projects that implement optimizers based on Calcite,our > optimizer is also based on Calcite. > Calcite has a lot of good rules in CoreRules.It has UnionToDistinctRule and > IntersectToDistinctRule RelRule ,UnionToDistinctRule is that converts > Union(all = false) to Union(all=true) + Aggregate,IntersectToDistinctRule is > that converts Intersect(all=false) to Union(all=true) + Aggregate + > Filter.None of these rules translate Minus to other RelNode combinations. > Normally, a computation engine does not have a Minus operator, so it is > common to convert Minus to some other relational algebra combination in the > optimizer.For example,in presto,it has the ImplementIntersectAndExceptAsUnion > PlanOptimizer that converts Minus to into UNION ALL..GROUP BY...WHERE. In > flink,it has the ReplaceMinusWithAntiJoinRule RelOptRule. In dremio-oss,it > has MinusToJoin RelOptRule.All of this rules,converts the Minus to other > composition of relational algebra. > Since there are no optimization rules for dealing with minus in calcite, > users of calcite generally need to write their own optimization rules for > dealing with Minus. > I think it makes sense to add the RelRule that converts Minus to other > composition of relational algebranto ,like UNION ALL.. GROUP BY... WHERE. So > for users of calcite,they don't have to go through the extra work of writing > rules for Minus to convert it to something else. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-5922) The SQL generated for the POSITION function(with 3 input arguments) by the SparkSqlDialect is not recognized by Spark SQL
[
https://issues.apache.org/jira/browse/CALCITE-5922?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
hongyu guo updated CALCITE-5922:
Summary: The SQL generated for the POSITION function(with 3 input
arguments) by the SparkSqlDialect is not recognized by Spark SQL (was: The SQL
generated for the POSITION function(with 3 input arguments) by the
SparkSqlDialect is not recognized by Spark SQL.)
> The SQL generated for the POSITION function(with 3 input arguments) by the
> SparkSqlDialect is not recognized by Spark SQL
> -
>
> Key: CALCITE-5922
> URL: https://issues.apache.org/jira/browse/CALCITE-5922
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.35.0
>Reporter: hongyu guo
>Assignee: hongyu guo
>Priority: Minor
> Labels: pull-request-available
> Fix For: 1.36.0
>
>
> In SparkSQL, POSITION(substr, str[, pos]) function only accept
> comma-separated when there are 3 arguments.
> For example:
> {code:java}
> // SparkSQL accepted SQL
> select POSITION('a', 'abc', 1);{code}
> Calcite will use the IN and FROM keyword to separate the input arguments when
> unparsing.
> {code:java}
> // Calcite accepted and unparsed SQL
> select POSITION('a' IN 'abc' FROM 1){code}
> For 2 augument inputs, SparkSQL accept both syntaxes. So I think we should
> write a rule in SparkSqlDialect to convert keyword-separated syntax to
> comma-separted syntax for POSITION function.
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-5922) The SQL generated for the POSITION function(with 3 input arguments) by the SparkSqlDialect is not recognized by Spark SQL.
[
https://issues.apache.org/jira/browse/CALCITE-5922?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17754436#comment-17754436
]
hongyu guo edited comment on CALCITE-5922 at 8/15/23 6:30 AM:
--
Hi, Julian. Thank you for you replay.
I change the summary to
_The SQL generated for the POSITION function(with 3 input arguments) by the
SparkSqlDialect is not recognized by Spark SQL._
was (Author: JIRAUSER300840):
Hi Julian~
Thank you for you replay,
I change the summary to
_The SQL generated for the POSITION function(with 3 input arguments) by the
SparkSqlDialect is not recognized by Spark SQL._
> The SQL generated for the POSITION function(with 3 input arguments) by the
> SparkSqlDialect is not recognized by Spark SQL.
> --
>
> Key: CALCITE-5922
> URL: https://issues.apache.org/jira/browse/CALCITE-5922
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.35.0
>Reporter: hongyu guo
>Assignee: hongyu guo
>Priority: Minor
> Labels: pull-request-available
> Fix For: 1.36.0
>
>
> In SparkSQL, POSITION(substr, str[, pos]) function only accept
> comma-separated when there are 3 arguments.
> For example:
> {code:java}
> // SparkSQL accepted SQL
> select POSITION('a', 'abc', 1);{code}
> Calcite will use the IN and FROM keyword to separate the input arguments when
> unparsing.
> {code:java}
> // Calcite accepted and unparsed SQL
> select POSITION('a' IN 'abc' FROM 1){code}
> For 2 augument inputs, SparkSQL accept both syntaxes. So I think we should
> write a rule in SparkSqlDialect to convert keyword-separated syntax to
> comma-separted syntax for POSITION function.
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5922) The SQL generated for the POSITION function(with 3 input arguments) by the SparkSqlDialect is not recognized by Spark SQL.
[
https://issues.apache.org/jira/browse/CALCITE-5922?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17754436#comment-17754436
]
hongyu guo commented on CALCITE-5922:
-
Hi Julian~
Thank you for you replay,
I change the summary to
_The SQL generated for the POSITION function(with 3 input arguments) by the
SparkSqlDialect is not recognized by Spark SQL._
> The SQL generated for the POSITION function(with 3 input arguments) by the
> SparkSqlDialect is not recognized by Spark SQL.
> --
>
> Key: CALCITE-5922
> URL: https://issues.apache.org/jira/browse/CALCITE-5922
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.35.0
>Reporter: hongyu guo
>Assignee: hongyu guo
>Priority: Minor
> Labels: pull-request-available
> Fix For: 1.36.0
>
>
> In SparkSQL, POSITION(substr, str[, pos]) function only accept
> comma-separated when there are 3 arguments.
> For example:
> {code:java}
> // SparkSQL accepted SQL
> select POSITION('a', 'abc', 1);{code}
> Calcite will use the IN and FROM keyword to separate the input arguments when
> unparsing.
> {code:java}
> // Calcite accepted and unparsed SQL
> select POSITION('a' IN 'abc' FROM 1){code}
> For 2 augument inputs, SparkSQL accept both syntaxes. So I think we should
> write a rule in SparkSqlDialect to convert keyword-separated syntax to
> comma-separted syntax for POSITION function.
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Assigned] (CALCITE-5922) The SQL generated for the POSITION function(with 3 input arguments) by the SparkSqlDialect is not recognized by Spark SQL.
[
https://issues.apache.org/jira/browse/CALCITE-5922?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
hongyu guo reassigned CALCITE-5922:
---
Assignee: hongyu guo
> The SQL generated for the POSITION function(with 3 input arguments) by the
> SparkSqlDialect is not recognized by Spark SQL.
> --
>
> Key: CALCITE-5922
> URL: https://issues.apache.org/jira/browse/CALCITE-5922
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.35.0
>Reporter: hongyu guo
>Assignee: hongyu guo
>Priority: Minor
> Labels: pull-request-available
> Fix For: 1.36.0
>
>
> In SparkSQL, POSITION(substr, str[, pos]) function only accept
> comma-separated when there are 3 arguments.
> For example:
> {code:java}
> // SparkSQL accepted SQL
> select POSITION('a', 'abc', 1);{code}
> Calcite will use the IN and FROM keyword to separate the input arguments when
> unparsing.
> {code:java}
> // Calcite accepted and unparsed SQL
> select POSITION('a' IN 'abc' FROM 1){code}
> For 2 augument inputs, SparkSQL accept both syntaxes. So I think we should
> write a rule in SparkSqlDialect to convert keyword-separated syntax to
> comma-separted syntax for POSITION function.
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-5922) The SQL generated for the POSITION function(with 3 input arguments) by the SparkSqlDialect is not recognized by Spark SQL.
[
https://issues.apache.org/jira/browse/CALCITE-5922?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
hongyu guo updated CALCITE-5922:
Summary: The SQL generated for the POSITION function(with 3 input
arguments) by the SparkSqlDialect is not recognized by Spark SQL. (was:
POSITION signature incorrect for SparkSQL)
> The SQL generated for the POSITION function(with 3 input arguments) by the
> SparkSqlDialect is not recognized by Spark SQL.
> --
>
> Key: CALCITE-5922
> URL: https://issues.apache.org/jira/browse/CALCITE-5922
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.35.0
>Reporter: hongyu guo
>Priority: Minor
> Labels: pull-request-available
> Fix For: 1.36.0
>
>
> In SparkSQL, POSITION(substr, str[, pos]) function only accept
> comma-separated when there are 3 arguments.
> For example:
> {code:java}
> // SparkSQL accepted SQL
> select POSITION('a', 'abc', 1);{code}
> Calcite will use the IN and FROM keyword to separate the input arguments when
> unparsing.
> {code:java}
> // Calcite accepted and unparsed SQL
> select POSITION('a' IN 'abc' FROM 1){code}
> For 2 augument inputs, SparkSQL accept both syntaxes. So I think we should
> write a rule in SparkSqlDialect to convert keyword-separated syntax to
> comma-separted syntax for POSITION function.
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-5889) Add the RelRule that converts Minus into UNION ALL..GROUP BY...WHERE
[ https://issues.apache.org/jira/browse/CALCITE-5889?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] ASF GitHub Bot updated CALCITE-5889: Labels: pull-request-available (was: ) > Add the RelRule that converts Minus into UNION ALL..GROUP BY...WHERE > > > Key: CALCITE-5889 > URL: https://issues.apache.org/jira/browse/CALCITE-5889 > Project: Calcite > Issue Type: New Feature > Components: core >Reporter: LakeShen >Assignee: LakeShen >Priority: Major > Labels: pull-request-available > Fix For: 1.36.0 > > > There are many projects that implement optimizers based on Calcite,our > optimizer is also based on Calcite. > Calcite has a lot of good rules in CoreRules.It has UnionToDistinctRule and > IntersectToDistinctRule RelRule ,UnionToDistinctRule is that converts > Union(all = false) to Union(all=true) + Aggregate,IntersectToDistinctRule is > that converts Intersect(all=false) to Union(all=true) + Aggregate + > Filter.None of these rules translate Minus to other RelNode combinations. > Normally, a computation engine does not have a Minus operator, so it is > common to convert Minus to some other relational algebra combination in the > optimizer.For example,in presto,it has the ImplementIntersectAndExceptAsUnion > PlanOptimizer that converts Minus to into UNION ALL..GROUP BY...WHERE. In > flink,it has the ReplaceMinusWithAntiJoinRule RelOptRule. In dremio-oss,it > has MinusToJoin RelOptRule.All of this rules,converts the Minus to other > composition of relational algebra. > Since there are no optimization rules for dealing with minus in calcite, > users of calcite generally need to write their own optimization rules for > dealing with Minus. > I think it makes sense to add the RelRule that converts Minus to other > composition of relational algebranto ,like UNION ALL.. GROUP BY... WHERE. So > for users of calcite,they don't have to go through the extra work of writing > rules for Minus to convert it to something else. -- This message was sent by Atlassian Jira (v8.20.10#820010)
