You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@flink.apache.org by "Jark Wu (Jira)" <ji...@apache.org> on 2020/01/03 02:52:00 UTC
[jira] [Closed] (FLINK-14961) Join with a LookupableTableSource： Expected results are inconsistent when using SQL expressions or UDF
[ https://issues.apache.org/jira/browse/FLINK-14961?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Jark Wu closed FLINK-14961.
---------------------------
Resolution: Duplicate
> Join with a LookupableTableSource： Expected results are inconsistent when using SQL expressions or UDF
> ------------------------------------------------------------------------------------------------------
>
> Key: FLINK-14961
> URL: https://issues.apache.org/jira/browse/FLINK-14961
> Project: Flink
> Issue Type: Bug
> Components: Table SQL / Planner
> Affects Versions: 1.9.1
> Environment: jdk1.8.0_211
> windows7
> IDEA 2019.2.4
> Reporter: Rockey Cui
> Priority: Major
> Labels: test
> Attachments: EVAL.png, FUNC.jpg, TASKINFO.png
>
>
> I implements LookupableTableSource，When I used the join syntax, I found that the expected result was not consistent with the SQL. Looking at the execution plan, I found the following problems:
> Stream Schema-->
> |-- id: BIGINT
> |-- cityId: INT
> |-- url: STRING
> |-- cDate: DATE
> |-- cTimeStamp: TIMESTAMP(3)
> |-- proctime: TIMESTAMP(3) *PROCTIME*
> DATA-->
> "1001,1002,adc0,2019-11-11,2019-11-11 00:00:00.001000001",
> "1002,1003,adc1,2019-11-11,2019-11-11 00:00:00.002000002",
> "1003,1004,adc2,2019-11-11,2019-11-11 00:00:00.003000003",
> "1004,1005,adc3,2019-11-11,2019-11-11 00:00:00.004000004",
> "1005,1006,adc4,2019-11-11,2019-11-11 00:00:00.005000005",
> "8888,6666,adc5,2019-11-11,2019-11-11 00:00:00.006000006"
> LookupableTableSource Schema-->
> |-- USERID: BIGINT
> |-- CITYID: INT
> |-- AGE: DOUBLE
> |-- USERNAME: STRING
> |-- USERSEX: STRING
> |-- CREATEDATE: TIMESTAMP(3)
> |-- CREATETIMESTAMP: TIMESTAMP(3)
> DATA-->
> 1001,1002,1001.00,赵俊峰,男,2019-11-26,2019-11-26 20:49:20.000527
> 1002,1003,1002.01,华宏言,男,2019-11-26,2019-11-26 20:49:20.000527
> 1003,1004,1003.02,姜艺,女,2019-11-26,2019-11-26 20:49:20.000527
> 1004,1005,1004.03,唐鸣,男,2019-11-26,2019-11-26 20:49:20.000527
> 1005,1006,1005.04,苗斌,男,2019-11-26,2019-11-26 20:49:20.000527
> SQL -->
> select a.*,i.* from user_click_info a left join info FOR SYSTEM_TIME AS OF a.proctime i on i.CITYID = a.cityId and{color:#FF0000} i.USERID - 1 = a.id {color}
> FlinkTaskInfo -->
> Source: Collection Source -> Map -> SourceConversion(table=[Unregistered_DataStream_2], fields=[id, cityId, url, cDate, cTimeStamp, proctime]) -> LookupJoin(table=[MyLookupableTableSource(USERID, CITYID, AGE, USERNAME, USERSEX, CREATEDATE, CREATETIMESTAMP)], joinType=[LeftOuterJoin], async=[false], on=[cityId=CITYID, {color:#FF0000}id=$f7{color}], where=[], select=[id, cityId, url, cDate, cTimeStamp, proctime, USERID, CITYID, AGE, USERNAME, USERSEX, CREATEDATE, CREATETIMESTAMP, $f7]) -> Calc(select=[id, cityId, url, cDate, cTimeStamp, PROCTIME_MATERIALIZE(proctime) AS proctime, USERID, CITYID, AGE, USERNAME, USERSEX, CREATEDATE, CREATETIMESTAMP]) -> SinkConversionToRow -> Sink: Print to Std. Out (1/1)
> Result->
> {color:#FF0000}1001,1002{color},adc0,2019-11-11,2019-11-11T00:00:00.001,2019-11-27T03:52:18.156,{color:#FF0000}1001,1002{color},1001.0,赵俊峰,男,2019-11-26T00:00,2019-11-26T20:49:20
> {color:#FF0000}1002,1003{color},adc1,2019-11-11,2019-11-11T00:00:00.002,2019-11-27T03:52:18.263,{color:#FF0000}1002,1003{color},1002.01,华宏言,男,2019-11-26T00:00,2019-11-26T20:49:20
> 1003,1004,adc2,2019-11-11,2019-11-11T00:00:00.003,2019-11-27T03:52:18.351,1003,1004,1003.02,姜艺,女,2019-11-26T00:00,2019-11-26T20:49:20
> 1004,1005,adc3,2019-11-11,2019-11-11T00:00:00.004,2019-11-27T03:52:18.433,1004,1005,1004.03,唐鸣,男,2019-11-26T00:00,2019-11-26T20:49:20
> 1005,1006,adc4,2019-11-11,2019-11-11T00:00:00.005,2019-11-27T03:52:18.534,1005,1006,1005.04,苗斌,男,2019-11-26T00:00,2019-11-26T20:49:20
> 8888,6666,adc5,2019-11-11,2019-11-11T00:00:00.006,2019-11-27T03:52:18.636,null,null,null,null,null,null,null
>
> It looks like the expression doesn't work！！！
> TableFunction<Row> getLookupFunction(String[] strings) only receive CITYID,
> public void eval(Object... params) only receive cityId value.
>
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)