Hi! Currently all built-in aggregate functions ignore null input values, so I guess this is the reason why Flink didn't support this syntax.
I'm sort of curious about this syntax. Does it come from the SQL standard? What's the opposite of IGNORE NULLS? Is there a NOT IGNORE NULLS and if the user specifies this an exception will be thrown when a null value is encountered? Adrian Bednarz <adrianbedn...@gmail.com> 于2021年10月8日周五 下午9:22写道： > Hi, > > we've been trying to run a query similar to > SELECT id, type, LAG(id) IGNORE NULLS OVER (PARTITION BY type ORDER BY ts) > AS lastId > FROM Events > > A query without IGNORE NULLS clause executes just fine. This syntax is > supported by Calcite and our clients expect it to work. Our platform uses > FlinkSQL to execute certain types of queries and currently such syntax > causes jobs to fail with NPE. Here's a stack trace > > Exception in thread "main" org.apache.flink.table.api.ValidationException: > SQL validation failed. null > at org.apache.flink.table.planner.calcite.FlinkPlannerImpl.org > $apache$flink$table$planner$calcite$FlinkPlannerImpl$$validate(FlinkPlannerImpl.scala:164) > at > org.apache.flink.table.planner.calcite.FlinkPlannerImpl.validate(FlinkPlannerImpl.scala:107) > at > org.apache.flink.table.planner.operations.SqlToOperationConverter.convert(SqlToOperationConverter.java:215) > at > org.apache.flink.table.planner.delegation.ParserImpl.parse(ParserImpl.java:101) > at > org.apache.flink.table.api.internal.TableEnvironmentImpl.executeSql(TableEnvironmentImpl.java:736) > at com.example.OverIgnoreNullsJob.main(OverIgnoreNullsJob.java:37) > Caused by: java.lang.NullPointerException > at java.base/java.util.Objects.requireNonNull(Objects.java:221) > at org.apache.calcite.sql.SqlBasicCall.setOperator(SqlBasicCall.java:67) > at org.apache.calcite.sql.SqlOperator.deriveType(SqlOperator.java:530) > at > org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:5710) > at > org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:5697) > at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:139) > at > org.apache.calcite.sql.validate.SqlValidatorImpl.deriveTypeImpl(SqlValidatorImpl.java:1736) > at > org.apache.calcite.sql.validate.SqlValidatorImpl.deriveType(SqlValidatorImpl.java:1727) > at org.apache.calcite.sql.type.SqlTypeUtil.deriveType(SqlTypeUtil.java:178) > at > org.apache.calcite.sql.type.FamilyOperandTypeChecker.checkSingleOperandType(FamilyOperandTypeChecker.java:71) > at > org.apache.calcite.sql.type.FamilyOperandTypeChecker.checkOperandTypes(FamilyOperandTypeChecker.java:122) > at > org.apache.calcite.sql.SqlOperator.checkOperandTypes(SqlOperator.java:679) > at > org.apache.calcite.sql.SqlOperator.validateOperands(SqlOperator.java:444) > at > org.apache.calcite.sql.SqlOverOperator.deriveType(SqlOverOperator.java:86) > at > org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:5710) > at > org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:5697) > at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:139) > at > org.apache.calcite.sql.validate.SqlValidatorImpl.deriveTypeImpl(SqlValidatorImpl.java:1736) > at > org.apache.calcite.sql.validate.SqlValidatorImpl.deriveType(SqlValidatorImpl.java:1727) > at org.apache.calcite.sql.SqlAsOperator.deriveType(SqlAsOperator.java:133) > at > org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:5710) > at > org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:5697) > at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:139) > at > org.apache.calcite.sql.validate.SqlValidatorImpl.deriveTypeImpl(SqlValidatorImpl.java:1736) > at > org.apache.calcite.sql.validate.SqlValidatorImpl.deriveType(SqlValidatorImpl.java:1727) > at > org.apache.calcite.sql.validate.SqlValidatorImpl.expandSelectItem(SqlValidatorImpl.java:421) > at > org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelectList(SqlValidatorImpl.java:4061) > at > org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3347) > at > org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:60) > at > org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:84) > at > org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:997) > at > org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:975) > at org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:232) > at > org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:952) > at > org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:704) > at org.apache.flink.table.planner.calcite.FlinkPlannerImpl.org > $apache$flink$table$planner$calcite$FlinkPlannerImpl$$validate(FlinkPlannerImpl.scala:159) > ... 5 more > > By looking through the codebase I concluded that such syntax is not > implemented by Flink engine. Have you considered including this syntax to > Flink? Or is it a deliberate decision not to have it? > > Regards, > Adrian >
