[ 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)