Seems like IGNORE NULL is specified in SQL spec 2008 (paragraph 6.10), the opposite is called RESPECT NULLS:
<null treatment> ::= RESPECT NULLS | IGNORE NULLS Perhaps this is worth supporting, I've opened an issue for that FLINK-24499 <https://issues.apache.org/jira/browse/FLINK-24499>. @Adrian are you interested in contributing to this issue? On Sat, Oct 9, 2021 at 4:32 AM Caizhi Weng <tsreape...@gmail.com> wrote: > 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 >> >
