This is an automated email from the ASF dual-hosted git repository.
zabetak pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/master by this push:
new 4066a34 [CALCITE-4675] Error executing query with SUM and
multiplication via JdbcAdapter
4066a34 is described below
commit 4066a34b12d1619c1dcb9199988f758a6fc08082
Author: Stamatis Zampetakis <[email protected]>
AuthorDate: Tue Jul 6 08:33:24 2021 +0200
[CALCITE-4675] Error executing query with SUM and multiplication via
JdbcAdapter
Use EXPR$ prefix for naming columns when pushing projections
to avoid creating aliases with weird symbols such as '*'.
Such aliases may create problems in some DBMS such as Redshift
when the plan is converted to SQL via RelToSqlConverter.
---
.../apache/calcite/rel/rules/PushProjector.java | 4 +-
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 16 ++++++++
.../org/apache/calcite/test/RelOptRulesTest.xml | 46 +++++++++++-----------
3 files changed, 42 insertions(+), 24 deletions(-)
diff --git a/core/src/main/java/org/apache/calcite/rel/rules/PushProjector.java
b/core/src/main/java/org/apache/calcite/rel/rules/PushProjector.java
index a9341fa..00f4ce3 100644
--- a/core/src/main/java/org/apache/calcite/rel/rules/PushProjector.java
+++ b/core/src/main/java/org/apache/calcite/rel/rules/PushProjector.java
@@ -34,6 +34,7 @@ import org.apache.calcite.rex.RexNode;
import org.apache.calcite.rex.RexUtil;
import org.apache.calcite.rex.RexVisitorImpl;
import org.apache.calcite.sql.SqlOperator;
+import org.apache.calcite.sql.SqlUtil;
import org.apache.calcite.tools.RelBuilder;
import org.apache.calcite.util.BitSets;
import org.apache.calcite.util.ImmutableBitSet;
@@ -540,6 +541,7 @@ public class PushProjector {
adjustments[idx] = -offset;
}
}
+ int preserveExpOrdinal = 0;
for (RexNode projExpr : preserveExprs) {
RexNode newExpr;
if (adjust) {
@@ -564,7 +566,7 @@ public class PushProjector {
newProjects.add(
Pair.of(
newExpr,
- ((RexCall) projExpr).getOperator().getName()));
+ SqlUtil.deriveAliasFromOrdinal(preserveExpOrdinal++)));
}
return (Project) relBuilder.push(projChild)
diff --git
a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
index 54ab21b..f272e7c 100644
---
a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
+++
b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
@@ -3256,6 +3256,22 @@ class RelToSqlConverterTest {
sql(query).withPostgresql().optimize(rules, hepPlanner).ok(expect);
}
+ @Test void testMultiplicationNotAliasedToStar() {
+ final String sql = "select s.\"customer_id\", sum(s.\"store_sales\" *
s.\"store_cost\")"
+ + "from \"sales_fact_1997\" as s\n"
+ + "join \"customer\" as c\n"
+ + " on s.\"customer_id\" = c.\"customer_id\"\n"
+ + "group by s.\"customer_id\"";
+ final String expected = "SELECT \"t\".\"customer_id\",
SUM(\"t\".\"EXPR$0\")\n"
+ + "FROM (SELECT \"customer_id\", \"store_sales\" * \"store_cost\" AS
\"EXPR$0\"\n"
+ + "FROM \"foodmart\".\"sales_fact_1997\") AS \"t\"\n"
+ + "INNER JOIN (SELECT \"customer_id\"\n"
+ + "FROM \"foodmart\".\"customer\") AS \"t0\" ON \"t\".\"customer_id\"
= \"t0\".\"customer_id\"\n"
+ + "GROUP BY \"t\".\"customer_id\"";
+ RuleSet rules = RuleSets.ofList(CoreRules.PROJECT_JOIN_TRANSPOSE);
+ sql(sql).optimize(rules, null).ok(expected);
+ }
+
@Test void testRankFunctionForPrintingOfFrameBoundary() {
String query = "SELECT rank() over (order by \"hire_date\") FROM
\"employee\"";
String expected = "SELECT RANK() OVER (ORDER BY \"hire_date\")\n"
diff --git
a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
index c60297b..f811fba 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -4181,7 +4181,7 @@ LogicalProject(NAME=[$0])
LogicalJoin(condition=[$4], joinType=[left])
LogicalProject(DEPTNO=[$0], NAME=[$1])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
- LogicalProject(DEPTNO=[$0], NAME=[$1], $f2=[>($0, 10)], >=[>($0, 10)])
+ LogicalProject(DEPTNO=[$0], NAME=[$1], $f2=[>($0, 10)], EXPR$0=[>($0,
10)])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
LogicalProject(DEPTNO=[$0])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
@@ -5763,7 +5763,7 @@ LogicalProject(EXPR$0=[SUM(+($0, 1)) OVER (PARTITION BY
$1)], EXPR$1=[COUNT($4)
<![CDATA[
LogicalProject(EXPR$0=[SUM($2) OVER (PARTITION BY $0)], EXPR$1=[COUNT($3) OVER
()])
LogicalCorrelate(correlation=[$cor1], joinType=[inner],
requiredColumns=[{1}])
- LogicalProject(NAME=[$1], EMPLOYEES=[$3], +=[+($0, 1)])
+ LogicalProject(NAME=[$1], EMPLOYEES=[$3], EXPR$0=[+($0, 1)])
LogicalTableScan(table=[[CATALOG, SALES, DEPT_NESTED]])
LogicalProject(EMPNO=[$0])
Uncollect
@@ -6047,10 +6047,10 @@ LogicalProject(EXPR$0=[ITEM($0, 0)], EXPR$1=[ITEM($2,
0)])
<![CDATA[
LogicalProject(EXPR$0=[$1], EXPR$1=[$3])
LogicalJoin(condition=[=($0, $2)], joinType=[left])
- LogicalProject($f1=[$1], ITEM=[ITEM($0, 0)])
+ LogicalProject($f1=[$1], EXPR$0=[ITEM($0, 0)])
LogicalProject(C_NATIONKEY=[$0], $f1=[ITEM($0, 0)])
LogicalTableScan(table=[[CATALOG, SALES, CUSTOMER]])
- LogicalProject($f1=[$1], ITEM=[ITEM($0, 0)])
+ LogicalProject($f1=[$1], EXPR$0=[ITEM($0, 0)])
LogicalProject(C_NATIONKEY=[$0], $f1=[ITEM($0, 0)])
LogicalTableScan(table=[[CATALOG, SALES, CUSTOMER]])
]]>
@@ -6124,10 +6124,10 @@ LogicalProject(JOB=[$2], EXPR$1=[SUM(+($5, 100)) OVER
(PARTITION BY $7)])
<![CDATA[
LogicalProject(JOB=[$0], EXPR$1=[SUM($2) OVER (PARTITION BY $1)])
LogicalUnion(all=[true])
- LogicalProject(JOB=[$2], DEPTNO=[$7], +=[+($5, 100)])
+ LogicalProject(JOB=[$2], DEPTNO=[$7], EXPR$0=[+($5, 100)])
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3],
HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
- LogicalProject(JOB=[$2], DEPTNO=[$7], +=[+($5, 100)])
+ LogicalProject(JOB=[$2], DEPTNO=[$7], EXPR$0=[+($5, 100)])
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3],
HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
@@ -8713,7 +8713,7 @@ LogicalProject(EXPR$0=[$1], EXPR$1=[$0])
LogicalAggregate(group=[{0}], EXPR$0=[COUNT()])
LogicalProject(EXPR$1=[CASE($1, 11, $2)])
LogicalJoin(condition=[=($0, $3)], joinType=[full])
- LogicalProject(ENAME=[$1], <=[<($5, 11)], *=[*(-1, $5)])
+ LogicalProject(ENAME=[$1], EXPR$0=[<($5, 11)], EXPR$1=[*(-1, $5)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalProject(ENAME=[$0])
LogicalTableScan(table=[[CATALOG, SALES, BONUS]])
@@ -8742,7 +8742,7 @@ LogicalProject(EXPR$0=[$1], EXPR$1=[$0])
LogicalAggregate(group=[{0}], EXPR$0=[COUNT()])
LogicalProject(EXPR$1=[$1])
LogicalJoin(condition=[=($0, $2)], joinType=[full])
- LogicalProject(ENAME=[$1], CASE=[CASE(<($5, 11), *(-1, $5), $5)])
+ LogicalProject(ENAME=[$1], EXPR$0=[CASE(<($5, 11), *(-1, $5), $5)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalProject(ENAME=[$0])
LogicalTableScan(table=[[CATALOG, SALES, BONUS]])
@@ -8771,7 +8771,7 @@ LogicalProject(EXPR$0=[$1], EXPR$1=[$0])
LogicalAggregate(group=[{0}], EXPR$0=[COUNT()])
LogicalProject(EXPR$1=[$1])
LogicalJoin(condition=[=($0, $2)], joinType=[inner])
- LogicalProject(ENAME=[$1], CASE=[CASE(<($5, 11), 11, *(-1, $5))])
+ LogicalProject(ENAME=[$1], EXPR$0=[CASE(<($5, 11), 11, *(-1, $5))])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalProject(ENAME=[$0])
LogicalTableScan(table=[[CATALOG, SALES, BONUS]])
@@ -8800,7 +8800,7 @@ LogicalProject(EXPR$0=[$1], EXPR$1=[$0])
LogicalAggregate(group=[{0}], EXPR$0=[COUNT()])
LogicalProject(EXPR$1=[$1])
LogicalJoin(condition=[=($0, $2)], joinType=[inner])
- LogicalProject(ENAME=[$1], CASE=[CASE(<($5, 11), *(-1, $5), $5)])
+ LogicalProject(ENAME=[$1], EXPR$0=[CASE(<($5, 11), *(-1, $5), $5)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalProject(ENAME=[$0])
LogicalTableScan(table=[[CATALOG, SALES, BONUS]])
@@ -8824,7 +8824,7 @@ LogicalProject(EXPR$0=[+($5, $12)])
<![CDATA[
LogicalProject(EXPR$0=[+($1, $4)])
LogicalJoin(condition=[AND(=($0, $3), $2)], joinType=[inner])
- LogicalProject(ENAME=[$1], SAL=[$5], ==[=($7, 10)])
+ LogicalProject(ENAME=[$1], SAL=[$5], EXPR$0=[=($7, 10)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalProject(ENAME=[$0], COMM=[$3])
LogicalTableScan(table=[[CATALOG, SALES, BONUS]])
@@ -8853,7 +8853,7 @@ LogicalProject(EXPR$0=[$1], EXPR$1=[$0])
LogicalAggregate(group=[{0}], EXPR$0=[COUNT()])
LogicalProject(EXPR$1=[$1])
LogicalJoin(condition=[=($0, $2)], joinType=[left])
- LogicalProject(ENAME=[$1], CASE=[CASE(<($5, 11), 11, *(-1, $5))])
+ LogicalProject(ENAME=[$1], EXPR$0=[CASE(<($5, 11), 11, *(-1, $5))])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalProject(ENAME=[$0])
LogicalTableScan(table=[[CATALOG, SALES, BONUS]])
@@ -8913,7 +8913,7 @@ LogicalProject(EXPR$0=[$1], EXPR$1=[$0])
LogicalJoin(condition=[=($1, $0)], joinType=[left])
LogicalProject(ENAME=[$0])
LogicalTableScan(table=[[CATALOG, SALES, BONUS]])
- LogicalProject(ENAME=[$1], <=[<($5, 11)], *=[*(-1, $5)])
+ LogicalProject(ENAME=[$1], EXPR$0=[<($5, 11)], EXPR$1=[*(-1, $5)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
@@ -8942,7 +8942,7 @@ LogicalProject(EXPR$0=[$1], EXPR$1=[$0])
LogicalJoin(condition=[=($1, $0)], joinType=[left])
LogicalProject(ENAME=[$0])
LogicalTableScan(table=[[CATALOG, SALES, BONUS]])
- LogicalProject(ENAME=[$1], CASE=[CASE(<($5, 11), *(-1, $5), $5)])
+ LogicalProject(ENAME=[$1], EXPR$0=[CASE(<($5, 11), *(-1, $5), $5)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
@@ -8969,7 +8969,7 @@ LogicalProject(EXPR$0=[$1], EXPR$1=[$0])
LogicalAggregate(group=[{0}], EXPR$0=[COUNT()])
LogicalProject(EXPR$1=[CASE($1, 11, $2)])
LogicalJoin(condition=[=($0, $3)], joinType=[right])
- LogicalProject(ENAME=[$1], <=[<($5, 11)], *=[*(-1, $5)])
+ LogicalProject(ENAME=[$1], EXPR$0=[<($5, 11)], EXPR$1=[*(-1, $5)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalProject(ENAME=[$0])
LogicalTableScan(table=[[CATALOG, SALES, BONUS]])
@@ -8999,7 +8999,7 @@ LogicalProject(EXPR$0=[$1], EXPR$1=[$0])
LogicalAggregate(group=[{0}], EXPR$0=[COUNT()])
LogicalProject(EXPR$1=[$1])
LogicalJoin(condition=[=($0, $2)], joinType=[right])
- LogicalProject(ENAME=[$1], CASE=[CASE(<($5, 11), *(-1, $5), $5)])
+ LogicalProject(ENAME=[$1], EXPR$0=[CASE(<($5, 11), *(-1, $5), $5)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalProject(ENAME=[$0])
LogicalTableScan(table=[[CATALOG, SALES, BONUS]])
@@ -9030,7 +9030,7 @@ LogicalProject(EXPR$0=[$1], EXPR$1=[$0])
LogicalJoin(condition=[=($1, $0)], joinType=[right])
LogicalProject(ENAME=[$0])
LogicalTableScan(table=[[CATALOG, SALES, BONUS]])
- LogicalProject(ENAME=[$1], CASE=[CASE(<($5, 11), 11, *(-1, $5))])
+ LogicalProject(ENAME=[$1], EXPR$0=[CASE(<($5, 11), 11, *(-1, $5))])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
@@ -9059,7 +9059,7 @@ LogicalProject(EXPR$0=[$1], EXPR$1=[$0])
LogicalJoin(condition=[=($1, $0)], joinType=[right])
LogicalProject(ENAME=[$0])
LogicalTableScan(table=[[CATALOG, SALES, BONUS]])
- LogicalProject(ENAME=[$1], CASE=[CASE(<($5, 11), *(-1, $5), $5)])
+ LogicalProject(ENAME=[$1], EXPR$0=[CASE(<($5, 11), *(-1, $5), $5)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
@@ -9110,7 +9110,7 @@ LogicalProject(EXPR$0=[+($5, $13)])
<![CDATA[
LogicalProject(EXPR$0=[+($0, $3)])
LogicalJoin(condition=[AND(IS NOT DISTINCT FROM($1, $4), $2)],
joinType=[inner])
- LogicalProject(SAL=[$5], $f9=[$9], ==[=($7, 10)])
+ LogicalProject(SAL=[$5], $f9=[$9], EXPR$0=[=($7, 10)])
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3],
HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], $f9=[||($1, $2)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalProject(COMM=[$3], $f4=[$4])
@@ -9138,7 +9138,7 @@ LogicalProject(EXPR$0=[+($5, $12)], EXPR$1=[COUNT($0)
OVER (PARTITION BY $7)])
<![CDATA[
LogicalProject(EXPR$0=[+($2, $6)], EXPR$1=[COUNT($0) OVER (PARTITION BY $3)])
LogicalJoin(condition=[AND(=($1, $5), $4)], joinType=[inner])
- LogicalProject(EMPNO=[$0], ENAME=[$1], SAL=[$5], DEPTNO=[$7], ==[=($7,
10)])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], SAL=[$5], DEPTNO=[$7],
EXPR$0=[=($7, 10)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalProject(ENAME=[$0], COMM=[$3])
LogicalTableScan(table=[[CATALOG, SALES, BONUS]])
@@ -9164,7 +9164,7 @@ LogicalProject(EXPR$0=[+($5, $12)], EXPR$1=[COUNT($11)
OVER (PARTITION BY $10)])
<![CDATA[
LogicalProject(EXPR$0=[+($1, $6)], EXPR$1=[COUNT($5) OVER (PARTITION BY $4)])
LogicalJoin(condition=[AND(=($0, $3), $2)], joinType=[inner])
- LogicalProject(ENAME=[$1], SAL=[$5], ==[=($7, 10)])
+ LogicalProject(ENAME=[$1], SAL=[$5], EXPR$0=[=($7, 10)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalProject(ENAME=[$0], JOB=[$1], SAL=[$2], COMM=[$3])
LogicalTableScan(table=[[CATALOG, SALES, BONUS]])
@@ -9190,9 +9190,9 @@ LogicalProject(EXPR$0=[+($5, $12)], EXPR$1=[SUM(+(+($11,
$11), 100)) OVER (PARTI
<![CDATA[
LogicalProject(EXPR$0=[+($1, $5)], EXPR$1=[SUM($6) OVER (PARTITION BY $4)])
LogicalJoin(condition=[AND(=($0, $3), $2)], joinType=[inner])
- LogicalProject(ENAME=[$1], SAL=[$5], ==[=($7, 10)])
+ LogicalProject(ENAME=[$1], SAL=[$5], EXPR$0=[=($7, 10)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
- LogicalProject(ENAME=[$0], JOB=[$1], COMM=[$3], +=[+(+($2, $2), 100)])
+ LogicalProject(ENAME=[$0], JOB=[$1], COMM=[$3], EXPR$0=[+(+($2, $2), 100)])
LogicalTableScan(table=[[CATALOG, SALES, BONUS]])
]]>
</Resource>
