Failed to execute the sum function when has window function

Dremio University
1

The dremio version I’m using is 25.1.1 and I have an error executing an SQL query as per the documentation.The document I refer to is: SUM | Dremio Documentation
This is my SQL:

SELECT name1,date_a,id,SUM(id) OVER (ORDER BY name1 ROWS BETWEEN 0 PRECEDING AND 1 FOLLOWING) from mytable;

The error is:

While invoking method 'public com.dremio.common.rel2sql.DremioRelToSqlConverter$Result com.dremio.common.rel2sql.DremioRelToSqlConverter.visit(org.apache.calcite.rel.core.Project)'

debug:

Caused by: java.lang.reflect.InvocationTargetException: null
	at jdk.internal.reflect.GeneratedMethodAccessor398.invoke(Unknown Source)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:566)
	at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:524)
	... 49 common frames omitted
Caused by: java.lang.IndexOutOfBoundsException: Index 3 out of bounds for length 3
	at java.base/jdk.internal.util.Preconditions.outOfBounds(Preconditions.java:64)
	at java.base/jdk.internal.util.Preconditions.outOfBoundsCheckIndex(Preconditions.java:70)
	at java.base/jdk.internal.util.Preconditions.checkIndex(Preconditions.java:248)
	at java.base/java.util.Objects.checkIndex(Objects.java:374)
	at java.base/java.util.ArrayList.get(ArrayList.java:459)
	at org.apache.calcite.sql.SqlNodeList.get(SqlNodeList.java:107)
	at com.dremio.exec.store.jdbc.rel2sql.JdbcDremioRelToSqlConverter$JdbcDremioSelectListContext.field(JdbcDremioRelToSqlConverter.java:740)
	at com.dremio.common.rel2sql.SqlImplementor$Context.toSql(SqlImplementor.java:461)

Through debugging, I found that there were only 3 selectLists and did not contain the sum field,So the index is out of bounds.
But I don’t know how to solve this problem. Please help me. Thank you.

2

@chen Can you please clarify what “3 selectLists and did not contain the sum field,” means? Is there a chance you can filter out those rows in a view and then apply the Window function on the view?

3

@balaji.ramaswamy

image
image821×350 47.9 KB

The cause of the error is the index out-of-bounds exception. I debug it because in’com.dremio.common.rel2sql.SqlImplementor.Context#toSql(org.apache.calcite.rex.RexProgram, org.apache.calcite.rex.RexNode)',

image
image672×300 33.7 KB

There are only three select fields.but ((RexInputRef) rex).getIndex() is 3, That’s why it’s wrong.
image
image663×186 20.2 KB

I use the example in the document, and the same error is reported.