You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Jiajun Xie (Jira)" <ji...@apache.org> on 2023/04/03 08:43:00 UTC
[jira] [Updated] (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:all-tabpanel ]
Jiajun Xie updated CALCITE-5630:
--------------------------------
Description:
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]
was:
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,
volcano planner use window1 replace window2.
> 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)