Rockey Cui created FLINK-14961:
----------------------------------
Summary: 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
Attachments: 1574826850(1).jpg, 企业微信截图_15748270288763.png, 企业微信截图_157482739422.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)
Locked
