[jira] [Commented] (FLINK-10795) STDDEV_POP error

["Hequn Cheng (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/FLINK-10795?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16676791#comment-16676791 ] 

Hequn Cheng commented on FLINK-10795:
-------------------------------------

Hi, I had looked into this issue. I think it is a precision issue.

As for sql, {{ROUND(STDDEV_POP(CAST(lon AS DOUBLE)),2)}},  {{STDDEV_POP}} will be converted to {{SQRT((SUM(x * x) - SUM( x ) * SUM( x ) / COUNT( x )) / COUNT( x ))}} in {{AggregateReduceFunctionsRule}} during optimization. However, due to precision issue, the result of {{SUM(x * x) - SUM( x ) * SUM( x ) / COUNT( x )}} would be negative which making sqrt return a value of NaN. {{ROUND}} throws an exception taking NaN as an input.

We may not be able to solve the precision problem. However, I think we can find ways to avoid the exception. For example, we can change  {{SQRT((SUM(x * x) - SUM( x ) * SUM( x ) / COUNT( x )) / COUNT( x ))}} to SQRT({color:red}ABS{color}((SUM(x * x) - SUM( x ) * SUM( x ) / COUNT( x ))) / COUNT( x )) by adding an abs(). What do you guys think?



> STDDEV_POP error
> ----------------
>
>                 Key: FLINK-10795
>                 URL: https://issues.apache.org/jira/browse/FLINK-10795
>             Project: Flink
>          Issue Type: Improvement
>          Components: Table API & SQL
>    Affects Versions: 1.6.2
>            Reporter: Flavio Pompermaier
>            Assignee: winifredtang
>            Priority: Major
>         Attachments: FlinkTableApiError.java, test.tsv
>
>
> if using STDDEV_POP in the attached job the following error is thrown (with Flink 1.6.1):
>  
> {code:java}
> Exception in thread "main" org.apache.flink.runtime.client.JobExecutionException: java.lang.NumberFormatException
>  at org.apache.flink.runtime.minicluster.MiniCluster.executeJobBlocking(MiniCluster.java:623)
>  at org.apache.flink.client.LocalExecutor.executePlan(LocalExecutor.java:235)
>  at org.apache.flink.api.java.LocalEnvironment.execute(LocalEnvironment.java:91)
>  at org.apache.flink.api.java.ExecutionEnvironment.execute(ExecutionEnvironment.java:816)
>  at org.apache.flink.api.java.DataSet.collect(DataSet.java:413)
>  at org.apache.flink.api.java.DataSet.print(DataSet.java:1652)
>  at it.okkam.datalinks.batch.flink.operations.FlinkTableApiError.main(FlinkTableApiError.java:466)
> Caused by: java.lang.NumberFormatException
>  at java.math.BigDecimal.<init>(BigDecimal.java:494)
>  at java.math.BigDecimal.<init>(BigDecimal.java:383)
>  at java.math.BigDecimal.<init>(BigDecimal.java:806)
>  at java.math.BigDecimal.valueOf(BigDecimal.java:1274)
>  at org.apache.calcite.runtime.SqlFunctions.sround(SqlFunctions.java:1242)
>  at DataSetCalcRule$6909.flatMap(Unknown Source)
>  at org.apache.flink.table.runtime.FlatMapRunner.flatMap(FlatMapRunner.scala:52)
>  at org.apache.flink.table.runtime.FlatMapRunner.flatMap(FlatMapRunner.scala:31)
>  at org.apache.flink.runtime.operators.chaining.ChainedFlatMapDriver.collect(ChainedFlatMapDriver.java:80)
>  at org.apache.flink.runtime.operators.util.metrics.CountingCollector.collect(CountingCollector.java:35)
>  at DataSetSingleRowJoinRule$6450.join(Unknown Source)
>  at org.apache.flink.table.runtime.MapJoinLeftRunner.flatMap(MapJoinLeftRunner.scala:35)
>  at org.apache.flink.runtime.operators.FlatMapDriver.run(FlatMapDriver.java:109)
>  at org.apache.flink.runtime.operators.BatchTask.run(BatchTask.java:503)
>  at org.apache.flink.runtime.operators.BatchTask.invoke(BatchTask.java:368)
>  at org.apache.flink.runtime.taskmanager.Task.run(Task.java:711)
>  at java.lang.Thread.run(Thread.java:748)
> {code}
>  



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)