You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by "silundong (via GitHub)" <gi...@apache.org> on 2026/01/18 09:45:44 UTC
[PR] [CALCITE-7382] The TopDownGeneralDecorrelator returns an error result when a subquery contains a LIMIT 1 [calcite]
silundong opened a new pull request, #4754:
URL: https://github.com/apache/calcite/pull/4754
This PR makes two changes:
1. During decorrelation, `Sort` will always be rewritten as `[Filter-]Window`, not only when both `ORDER BY` and `LIMIT/OFFSET` are present, because `ORDER BY`, `LIMIT` and `OFFSET` have to be enforced per value of the outer bindings instead of globally.
2. For the JIRA case, the bug was that when rewriting `Sort` into `Filter-Window`, the decorrelator forgot to set the frame clause for the window function, causing `ROW_NUMBER()` to be computed incorrectly. This has now been fixed.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-7382] The TopDownGeneralDecorrelator returns an error result when a subquery contains a LIMIT 1 [calcite]
Posted by "asolimando (via GitHub)" <gi...@apache.org>.
asolimando commented on code in PR #4754:
URL: https://github.com/apache/calcite/pull/4754#discussion_r2702335058
##########
core/src/test/resources/sql/new-decorr.iq:
##########
@@ -42,4 +42,29 @@ SELECT * FROM t0 WHERE t0a <
!ok
+# [CALCITE-7382] The TopDownGeneralDecorrelator returns an error result when a subquery contains a LIMIT 1
+!use scott
+SELECT dname, (SELECT emp.comm FROM "scott".emp where dept.deptno = emp.deptno ORDER BY emp.comm limit 1) FROM "scott".dept;
Review Comment:
```suggestion
SELECT dname, (SELECT emp.comm FROM "scott".emp where dept.deptno = emp.deptno ORDER BY emp.comm LIMIT 1) FROM "scott".dept;
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-7382] The TopDownGeneralDecorrelator returns an error result when a subquery contains a LIMIT 1 [calcite]
Posted by "rubenada (via GitHub)" <gi...@apache.org>.
rubenada commented on code in PR #4754:
URL: https://github.com/apache/calcite/pull/4754#discussion_r2702284996
##########
core/src/test/resources/sql/new-decorr.iq:
##########
@@ -42,4 +42,29 @@ SELECT * FROM t0 WHERE t0a <
!ok
+# [CALCITE-7382] The TopDownGeneralDecorrelator returns an error result when a subquery contains a LIMIT 1
+!use scott
+SELECT dname, (SELECT emp.comm FROM "scott".emp where dept.deptno = emp.deptno ORDER BY emp.comm limit 1) FROM "scott".dept;
++------------+--------+
+| DNAME | EXPR$1 |
++------------+--------+
+| ACCOUNTING | |
+| OPERATIONS | |
+| RESEARCH | |
+| SALES | 0.00 |
++------------+--------+
+(4 rows)
+
+!ok
+
+select dname, (select empno from emp where dept.deptno = emp.deptno limit 1) from dept where deptno = 10;
Review Comment:
Shouldn't we have an ORDER BY in the sub-query (before the LIMIT 1) to make sure that the query result is deterministic?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-7382] The TopDownGeneralDecorrelator returns an error result when a subquery contains a LIMIT 1 [calcite]
Posted by "silundong (via GitHub)" <gi...@apache.org>.
silundong commented on code in PR #4754:
URL: https://github.com/apache/calcite/pull/4754#discussion_r2702403825
##########
core/src/test/resources/sql/new-decorr.iq:
##########
@@ -42,4 +42,29 @@ SELECT * FROM t0 WHERE t0a <
!ok
+# [CALCITE-7382] The TopDownGeneralDecorrelator returns an error result when a subquery contains a LIMIT 1
+!use scott
+SELECT dname, (SELECT emp.comm FROM "scott".emp where dept.deptno = emp.deptno ORDER BY emp.comm limit 1) FROM "scott".dept;
++------------+--------+
+| DNAME | EXPR$1 |
++------------+--------+
+| ACCOUNTING | |
+| OPERATIONS | |
+| RESEARCH | |
+| SALES | 0.00 |
++------------+--------+
+(4 rows)
+
+!ok
+
+select dname, (select empno from emp where dept.deptno = emp.deptno limit 1) from dept where deptno = 10;
Review Comment:
This test is intended to demonstrate that `Sort` needs to be rewritten as a `[Filter-]Window` regardless of whether it has an `ORDER BY` or `LIMIT/OFFSET`. As I updated in the comment
> because now the order/limit/offset has to be enforced per value of the outer bindings instead of globally.
This is exactly what @asolimando was concerned about in his comment below. Perhaps in practical applications there's almost no need to write it this way; can I call this a edge case?
##########
core/src/main/java/org/apache/calcite/sql2rel/TopDownGeneralDecorrelator.java:
##########
@@ -598,50 +599,48 @@ public RelNode unnestInternal(Sort sort, boolean allowEmptyOutputFromRewrite) {
RelCollation shiftCollation = sort.getCollation().apply(targetMapping);
builder.push(newInput);
- if (!sort.collation.getFieldCollations().isEmpty()
- && (sort.offset != null || sort.fetch != null)) {
- // the Sort with ORDER BY and LIMIT or OFFSET have to be changed during rewriting because
- // now the limit has to be enforced per value of the outer bindings instead of globally.
- // It can be rewritten using ROW_NUMBER() window function and filtering on it,
- // see section 4.4 in paper Improving Unnesting of Complex Queries
- List<RexNode> partitionKeys = new ArrayList<>();
- for (CorDef corDef : corDefs) {
- int partitionKeyIndex = requireNonNull(inputInfo.corDefOutputs.get(corDef));
- partitionKeys.add(builder.field(partitionKeyIndex));
- }
- RexNode rowNumber = builder.aggregateCall(SqlStdOperatorTable.ROW_NUMBER)
- .over()
- .partitionBy(partitionKeys)
- .orderBy(builder.fields(shiftCollation))
- .toRex();
- List<RexNode> projectsWithRowNumber = new ArrayList<>(builder.fields());
- projectsWithRowNumber.add(rowNumber);
- builder.project(projectsWithRowNumber);
-
- List<RexNode> conditions = new ArrayList<>();
- if (sort.offset != null) {
- RexNode greaterThenLowerBound =
- builder.call(
- SqlStdOperatorTable.GREATER_THAN,
- builder.field(projectsWithRowNumber.size() - 1),
- sort.offset);
- conditions.add(greaterThenLowerBound);
- }
- if (sort.fetch != null) {
- RexNode upperBound = sort.offset == null
- ? sort.fetch
- : builder.call(SqlStdOperatorTable.PLUS, sort.offset, sort.fetch);
- RexNode lessThenOrEqualUpperBound =
- builder.call(
- SqlStdOperatorTable.LESS_THAN_OR_EQUAL,
- builder.field(projectsWithRowNumber.size() - 1),
- upperBound);
- conditions.add(lessThenOrEqualUpperBound);
- }
- builder.filter(conditions);
- } else {
- builder.sortLimit(sort.offset, sort.fetch, builder.fields(shiftCollation));
+ // the Sort have to be changed during rewriting because now the order/limit/offset has to be
Review Comment:
The paper's original wording is:
> Subqueries with ORDER BY and LIMIT or OFFSET have to be changed....
It doesn't say what to do when only LIMIT or OFFSET is present. However, I believe that even with only LIMIT or OFFSET, it should be rewritten as a window function without ORDER clause.
This matches the test case that @rubenada mentioned in his comment. I performed the same test in the [umbra-db interface](https://umbra-db.com/interface/), and it indeed rewrite the LIMIT as a window function. It seems our approach aligns with the paper's intent.
<img width="3840" height="1907" alt="image" src="https://github.com/user-attachments/assets/3cc2e127-20f7-4292-a030-ed8d4bf60e30" />
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-7382] The TopDownGeneralDecorrelator returns an error result when a subquery contains a LIMIT 1 [calcite]
Posted by "xiedeyantu (via GitHub)" <gi...@apache.org>.
xiedeyantu commented on PR #4754:
URL: https://github.com/apache/calcite/pull/4754#issuecomment-3765340687
> @xiedeyantu Hi, I don't think this is caused by the decorrelation algorithm. It looks like a loss of significant digits during type casting. The failure seems to come from the `Aggregate` when computing `SUM`. I tested:
>
> `SELECT SUM(empno) FROM emp GROUP BY deptno;`
>
> and it fails the same way. Perhaps a new ticket needs to be created to record it.
Thanks! I have file a new jira [CALCITE-7384](https://issues.apache.org/jira/browse/CALCITE-7384) to log this issue.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-7382] The TopDownGeneralDecorrelator returns an error result when a subquery contains a LIMIT 1 [calcite]
Posted by "xiedeyantu (via GitHub)" <gi...@apache.org>.
xiedeyantu commented on PR #4754:
URL: https://github.com/apache/calcite/pull/4754#issuecomment-3765437372
[CALCITE-7384](https://issues.apache.org/jira/browse/CALCITE-7384) This is an old problem that can be solved using cast(col as bigint), and it has been discussed many times. We can ignore this problem.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-7382] The TopDownGeneralDecorrelator returns an error result when a subquery contains a LIMIT 1 [calcite]
Posted by "asolimando (via GitHub)" <gi...@apache.org>.
asolimando commented on code in PR #4754:
URL: https://github.com/apache/calcite/pull/4754#discussion_r2702335347
##########
core/src/main/java/org/apache/calcite/sql2rel/TopDownGeneralDecorrelator.java:
##########
@@ -598,50 +599,48 @@ public RelNode unnestInternal(Sort sort, boolean allowEmptyOutputFromRewrite) {
RelCollation shiftCollation = sort.getCollation().apply(targetMapping);
builder.push(newInput);
- if (!sort.collation.getFieldCollations().isEmpty()
- && (sort.offset != null || sort.fetch != null)) {
- // the Sort with ORDER BY and LIMIT or OFFSET have to be changed during rewriting because
- // now the limit has to be enforced per value of the outer bindings instead of globally.
- // It can be rewritten using ROW_NUMBER() window function and filtering on it,
- // see section 4.4 in paper Improving Unnesting of Complex Queries
- List<RexNode> partitionKeys = new ArrayList<>();
- for (CorDef corDef : corDefs) {
- int partitionKeyIndex = requireNonNull(inputInfo.corDefOutputs.get(corDef));
- partitionKeys.add(builder.field(partitionKeyIndex));
- }
- RexNode rowNumber = builder.aggregateCall(SqlStdOperatorTable.ROW_NUMBER)
- .over()
- .partitionBy(partitionKeys)
- .orderBy(builder.fields(shiftCollation))
- .toRex();
- List<RexNode> projectsWithRowNumber = new ArrayList<>(builder.fields());
- projectsWithRowNumber.add(rowNumber);
- builder.project(projectsWithRowNumber);
-
- List<RexNode> conditions = new ArrayList<>();
- if (sort.offset != null) {
- RexNode greaterThenLowerBound =
- builder.call(
- SqlStdOperatorTable.GREATER_THAN,
- builder.field(projectsWithRowNumber.size() - 1),
- sort.offset);
- conditions.add(greaterThenLowerBound);
- }
- if (sort.fetch != null) {
- RexNode upperBound = sort.offset == null
- ? sort.fetch
- : builder.call(SqlStdOperatorTable.PLUS, sort.offset, sort.fetch);
- RexNode lessThenOrEqualUpperBound =
- builder.call(
- SqlStdOperatorTable.LESS_THAN_OR_EQUAL,
- builder.field(projectsWithRowNumber.size() - 1),
- upperBound);
- conditions.add(lessThenOrEqualUpperBound);
- }
- builder.filter(conditions);
- } else {
- builder.sortLimit(sort.offset, sort.fetch, builder.fields(shiftCollation));
+ // the Sort have to be changed during rewriting because now the order/limit/offset has to be
Review Comment:
Q: was this a shortcoming of the original paper or an implementation issue?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-7382] The TopDownGeneralDecorrelator returns an error result when a subquery contains a LIMIT 1 [calcite]
Posted by "silundong (via GitHub)" <gi...@apache.org>.
silundong commented on code in PR #4754:
URL: https://github.com/apache/calcite/pull/4754#discussion_r2702403859
##########
core/src/main/java/org/apache/calcite/sql2rel/TopDownGeneralDecorrelator.java:
##########
@@ -598,50 +599,48 @@ public RelNode unnestInternal(Sort sort, boolean allowEmptyOutputFromRewrite) {
RelCollation shiftCollation = sort.getCollation().apply(targetMapping);
builder.push(newInput);
- if (!sort.collation.getFieldCollations().isEmpty()
- && (sort.offset != null || sort.fetch != null)) {
- // the Sort with ORDER BY and LIMIT or OFFSET have to be changed during rewriting because
- // now the limit has to be enforced per value of the outer bindings instead of globally.
- // It can be rewritten using ROW_NUMBER() window function and filtering on it,
- // see section 4.4 in paper Improving Unnesting of Complex Queries
- List<RexNode> partitionKeys = new ArrayList<>();
- for (CorDef corDef : corDefs) {
- int partitionKeyIndex = requireNonNull(inputInfo.corDefOutputs.get(corDef));
- partitionKeys.add(builder.field(partitionKeyIndex));
- }
- RexNode rowNumber = builder.aggregateCall(SqlStdOperatorTable.ROW_NUMBER)
- .over()
- .partitionBy(partitionKeys)
- .orderBy(builder.fields(shiftCollation))
- .toRex();
- List<RexNode> projectsWithRowNumber = new ArrayList<>(builder.fields());
- projectsWithRowNumber.add(rowNumber);
- builder.project(projectsWithRowNumber);
-
- List<RexNode> conditions = new ArrayList<>();
- if (sort.offset != null) {
- RexNode greaterThenLowerBound =
- builder.call(
- SqlStdOperatorTable.GREATER_THAN,
- builder.field(projectsWithRowNumber.size() - 1),
- sort.offset);
- conditions.add(greaterThenLowerBound);
- }
- if (sort.fetch != null) {
- RexNode upperBound = sort.offset == null
- ? sort.fetch
- : builder.call(SqlStdOperatorTable.PLUS, sort.offset, sort.fetch);
- RexNode lessThenOrEqualUpperBound =
- builder.call(
- SqlStdOperatorTable.LESS_THAN_OR_EQUAL,
- builder.field(projectsWithRowNumber.size() - 1),
- upperBound);
- conditions.add(lessThenOrEqualUpperBound);
- }
- builder.filter(conditions);
- } else {
- builder.sortLimit(sort.offset, sort.fetch, builder.fields(shiftCollation));
+ // the Sort have to be changed during rewriting because now the order/limit/offset has to be
Review Comment:
The paper's original wording is:
> Subqueries with ORDER BY and LIMIT or OFFSET have to be changed....
It doesn't say what to do when only LIMIT or OFFSET is present. However, I believe that even with only LIMIT or OFFSET, it should be rewritten as a window function without ORDER clause.
This matches the test case that @rubenada mentioned in his comment. I performed the same test in the [umbra-db interface](https://umbra-db.com/interface/), and it indeed rewrite the LIMIT as a window function. It seems our approach aligns with the paper's intent.
<img width="3792" height="1841" alt="image" src="https://github.com/user-attachments/assets/35a21fd5-f784-464e-aedb-ce6696089d1f" />
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-7382] The TopDownGeneralDecorrelator returns an error result when a subquery contains a LIMIT 1 [calcite]
Posted by "xiedeyantu (via GitHub)" <gi...@apache.org>.
xiedeyantu commented on code in PR #4754:
URL: https://github.com/apache/calcite/pull/4754#discussion_r2702530372
##########
core/src/test/resources/sql/new-decorr.iq:
##########
@@ -42,4 +42,29 @@ SELECT * FROM t0 WHERE t0a <
!ok
+# [CALCITE-7382] The TopDownGeneralDecorrelator returns an error result when a subquery contains a LIMIT 1
+!use scott
Review Comment:
We can add a note to record where the currently processed case originated.
```
# This case comes from sub-query.iq [CALCITE-6652]
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-7382] The TopDownGeneralDecorrelator returns an error result when a subquery contains a LIMIT 1 [calcite]
Posted by "silundong (via GitHub)" <gi...@apache.org>.
silundong merged PR #4754:
URL: https://github.com/apache/calcite/pull/4754
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-7382] The TopDownGeneralDecorrelator returns an error result when a subquery contains a LIMIT 1 [calcite]
Posted by "sonarqubecloud[bot] (via GitHub)" <gi...@apache.org>.
sonarqubecloud[bot] commented on PR #4754:
URL: https://github.com/apache/calcite/pull/4754#issuecomment-3773305857
## [](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=4754) **Quality Gate passed**
Issues
 [0 New issues](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=4754&issueStatuses=OPEN,CONFIRMED&sinceLeakPeriod=true)
 [0 Accepted issues](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=4754&issueStatuses=ACCEPTED)
Measures
 [0 Security Hotspots](https://sonarcloud.io/project/security_hotspots?id=apache_calcite&pullRequest=4754&issueStatuses=OPEN,CONFIRMED&sinceLeakPeriod=true)
 [100.0% Coverage on New Code](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=4754&metric=new_coverage&view=list)
 [0.0% Duplication on New Code](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=4754&metric=new_duplicated_lines_density&view=list)
[See analysis details on SonarQube Cloud](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=4754)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-7382] The TopDownGeneralDecorrelator returns an error result when a subquery contains a LIMIT 1 [calcite]
Posted by "sonarqubecloud[bot] (via GitHub)" <gi...@apache.org>.
sonarqubecloud[bot] commented on PR #4754:
URL: https://github.com/apache/calcite/pull/4754#issuecomment-3768508638
## [](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=4754) **Quality Gate passed**
Issues
 [0 New issues](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=4754&issueStatuses=OPEN,CONFIRMED&sinceLeakPeriod=true)
 [0 Accepted issues](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=4754&issueStatuses=ACCEPTED)
Measures
 [0 Security Hotspots](https://sonarcloud.io/project/security_hotspots?id=apache_calcite&pullRequest=4754&issueStatuses=OPEN,CONFIRMED&sinceLeakPeriod=true)
 [100.0% Coverage on New Code](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=4754&metric=new_coverage&view=list)
 [0.0% Duplication on New Code](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=4754&metric=new_duplicated_lines_density&view=list)
[See analysis details on SonarQube Cloud](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=4754)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-7382] The TopDownGeneralDecorrelator returns an error result when a subquery contains a LIMIT 1 [calcite]
Posted by "sonarqubecloud[bot] (via GitHub)" <gi...@apache.org>.
sonarqubecloud[bot] commented on PR #4754:
URL: https://github.com/apache/calcite/pull/4754#issuecomment-3765123352
## [](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=4754) **Quality Gate passed**
Issues
 [0 New issues](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=4754&issueStatuses=OPEN,CONFIRMED&sinceLeakPeriod=true)
 [0 Accepted issues](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=4754&issueStatuses=ACCEPTED)
Measures
 [0 Security Hotspots](https://sonarcloud.io/project/security_hotspots?id=apache_calcite&pullRequest=4754&issueStatuses=OPEN,CONFIRMED&sinceLeakPeriod=true)
 [100.0% Coverage on New Code](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=4754&metric=new_coverage&view=list)
 [0.0% Duplication on New Code](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=4754&metric=new_duplicated_lines_density&view=list)
[See analysis details on SonarQube Cloud](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=4754)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-7382] The TopDownGeneralDecorrelator returns an error result when a subquery contains a LIMIT 1 [calcite]
Posted by "xiedeyantu (via GitHub)" <gi...@apache.org>.
xiedeyantu commented on PR #4754:
URL: https://github.com/apache/calcite/pull/4754#issuecomment-3765285350
After using the changes made in this PR, this case no longer works properly.
```
# [CALCITE-709] Errors with LIMIT inside scalar sub-query
!use scott
select deptno, (select sum(empno) from "scott".emp where deptno = dept.deptno limit 0) as x from "scott".dept;
+--------+---+
| DEPTNO | X |
+--------+---+
| 10 | |
| 20 | |
| 30 | |
| 40 | |
+--------+---+
(4 rows)
!ok
```
> Caused by: java.lang.ArithmeticException: Value 38216 out of range
> at org.apache.calcite.linq4j.tree.Primitive.checkRoundedRange(Primitive.java:387)
> at org.apache.calcite.linq4j.tree.Primitive.numberValue(Primitive.java:564)
> at org.apache.calcite.linq4j.tree.Primitive.numberValueRoundDown(Primitive.java:539)
> at Baz$3.apply(Unknown Source)
> at Baz$3.apply(Unknown Source)
> at Baz$3.apply(Unknown Source)
> at org.apache.calcite.adapter.enumerable.BasicAggregateLambdaFactory$AccumulatorAdderSeq.apply(BasicAggregateLambdaFactory.java:81)
> at org.apache.calcite.linq4j.EnumerableDefaults.groupBy_(EnumerableDefaults.java:1177)
> at org.apache.calcite.linq4j.EnumerableDefaults.groupBy(EnumerableDefaults.java:782)
> at org.apache.calcite.linq4j.DefaultEnumerable.groupBy(DefaultEnumerable.java:312)
> at Baz.bind(Unknown Source)
> at org.apache.calcite.jdbc.CalcitePrepare$CalciteSignature.enumerable(CalcitePrepare.java:367)
> at org.apache.calcite.jdbc.CalciteConnectionImpl.enumerable(CalciteConnectionImpl.java:335)
> at org.apache.calcite.jdbc.CalciteMetaImpl._createIterable(CalciteMetaImpl.java:609)
> at org.apache.calcite.jdbc.CalciteMetaImpl.createIterable(CalciteMetaImpl.java:600)
> at org.apache.calcite.avatica.AvaticaResultSet.execute(AvaticaResultSet.java:184)
> at org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:64)
> at org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:43)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-7382] The TopDownGeneralDecorrelator returns an error result when a subquery contains a LIMIT 1 [calcite]
Posted by "silundong (via GitHub)" <gi...@apache.org>.
silundong commented on PR #4754:
URL: https://github.com/apache/calcite/pull/4754#issuecomment-3765312589
@xiedeyantu Hi, I don't think this is caused by the decorrelation algorithm. It looks like a loss of significant digits during type casting. The failure seems to come from the `Aggregate` when computing `SUM`. I tested:
`SELECT SUM(empno) FROM emp GROUP BY deptno;`
and it fails the same way. Perhaps a new ticket needs to be created to record it.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org