[jira] [Comment Edited] (FLINK-20887) Non-deterministic functions return different values even if it is referred with the same column name

["Caizhi Weng (Jira)" <ji...@apache.org>]

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

Caizhi Weng edited comment on FLINK-20887 at 1/7/21, 12:00 PM:
---------------------------------------------------------------

[~Leonard Xu] please take a look. It might be that {{FlinkCalcMergeRule}} does not work correctly with non-deterministic functions.


was (Author: tsreaper):
[~Leonard Xu] please take a look.

> Non-deterministic functions return different values even if it is referred with the same column name
> ----------------------------------------------------------------------------------------------------
>
>                 Key: FLINK-20887
>                 URL: https://issues.apache.org/jira/browse/FLINK-20887
>             Project: Flink
>          Issue Type: Bug
>          Components: Table SQL / Planner
>    Affects Versions: 1.12.0
>            Reporter: Caizhi Weng
>            Priority: Major
>
> Add the following test case to {{CalcITCase.scala}}
> {code:scala}
> @Test
> def testRand(): Unit = {
>   checkResult(
>     s"""
>        |SELECT b - a FROM (
>        |  SELECT r + 5 AS a, r + 7 AS b FROM (
>        |    SELECT RAND() AS r FROM SmallTable3
>        |  ) t1
>        |) t2
>        |""".stripMargin,
>     Seq(row(2), row(2), row(2))
>   )
> }
> {code}
> Failure messages are
> {code}
> Results
>  == Correct Result - 3 ==   == Actual Result - 3 ==
> !2                          1.051329250417921
> !2                          1.3649146677814379
> !2                          1.787784536771345
>         
> Plan:
> == Abstract Syntax Tree ==
> LogicalProject(EXPR$0=[-($1, $0)])
> +- LogicalProject(a=[+($0, 5)], b=[+($0, 7)])
>    +- LogicalProject(r=[RAND()])
>       +- LogicalTableScan(table=[[default_catalog, default_database, SmallTable3]])
> == Optimized Logical Plan ==
> Calc(select=[-(+(RAND(), 7), +(RAND(), 5)) AS EXPR$0])
> +- BoundedStreamScan(table=[[default_catalog, default_database, SmallTable3]], fields=[a, b, c])
> {code}
> It seems that the projections are merged incorrectly. However if you run the following test case in {{FlinkCalcMergeRuleTest.scala}}
> {code:scala}
> @Test
> def testCalcMergeWithRandomUdf(): Unit = {
>   val sqlQuery = "SELECT ts + a, ts + b FROM " +
>     "(SELECT a, b, random_udf(a) AS ts FROM MyTable WHERE a = b) t"
>   util.verifyRelPlan(sqlQuery)
> }
> {code}
> The result is
> {code:xml}
> <Root>
>   <TestCase name="testCalcMergeWithRandomUdf">
>     <Resource name="sql">
>       <![CDATA[SELECT ts + a, ts + b FROM (SELECT a, b, random_udf(a) AS ts FROM MyTable WHERE a = b) t]]>
>     </Resource>
>     <Resource name="ast">
>       <![CDATA[
> LogicalProject(EXPR$0=[+(random_udf($0), $0)], EXPR$1=[+(random_udf($0), $1)])
> +- LogicalFilter(condition=[=($0, $1)])
>    +- LogicalTableScan(table=[[default_catalog, default_database, MyTable, source: [TestTableSource(a, b, c)]]])
> ]]>
>     </Resource>
>     <Resource name="optimized rel plan">
>       <![CDATA[
> FlinkLogicalCalc(select=[+(random_udf(a), a) AS EXPR$0, +(random_udf(a), b) AS EXPR$1], where=[=(a, b)])
> +- FlinkLogicalLegacyTableSourceScan(table=[[default_catalog, default_database, MyTable, source: [TestTableSource(a, b, c)]]], fields=[a, b, c])
> ]]>
>     </Resource>
>   </TestCase>
> </Root>
> {code}
> It seems that the plan is incorrect from the AST. So this seems to be a bug in Calcite?



--
This message was sent by Atlassian Jira
(v8.3.4#803005)