[ https://issues.apache.org/jira/browse/FLINK-7730?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16205674#comment-16205674 ]
ASF GitHub Bot commented on FLINK-7730: --------------------------------------- Github user fhueske commented on a diff in the pull request: https://github.com/apache/flink/pull/4830#discussion_r144809617 --- Diff: flink-libraries/flink-table/src/test/scala/org/apache/flink/table/api/stream/table/CorrelateTest.scala --- @@ -99,6 +104,21 @@ class CorrelateTest extends TableTestBase { util.verifyTable(result, expected) } + /** + * Due to the improper translation of TableFunction left outer join (see CALCITE-2004), we could + * only accept local join predicates on the left table. + */ + @Test (expected = classOf[TableException]) + def testLeftOuterJoinWithPredicates(): Unit = { + val util = streamTestUtil() + val table = util.addTable[(Int, Long, String)]("MyTable", 'a, 'b, 'c) + val function = util.addFunction("func1", new TableFunc1) + + val result = table.leftOuterJoin(function('c) as 's).select('c, 's).where('c === 's)// forbidden --- End diff -- This case is OK. > TableFunction LEFT OUTER joins with ON predicates are broken > ------------------------------------------------------------ > > Key: FLINK-7730 > URL: https://issues.apache.org/jira/browse/FLINK-7730 > Project: Flink > Issue Type: Bug > Components: Table API & SQL > Affects Versions: 1.4.0, 1.3.2 > Reporter: Fabian Hueske > Assignee: Xingcan Cui > Priority: Critical > > TableFunction left outer joins with predicates in the ON clause are broken. > Apparently, the are no tests for this and it has never worked. I observed > issues on several layers: > - Table Function does not correctly validate equality predicate: > {{leftOuterJoin(func1('c) as 'd, 'a.cast(Types.STRING) === 'd)}} is rejected > because the predicate is not considered as an equality predicate (the cast > needs to be pushed down). > - Plans cannot be correctly translated: {{leftOuterJoin(func1('c) as 'd, 'c > === 'd)}} gives an optimizer exception. > - SQL queries get translated but produce incorrect results. For example > {{SELECT a, b, c, d FROM MyTable LEFT OUTER JOIN LATERAL TABLE(tfunc(c)) AS > T(d) ON d = c}} returns an empty result if the condition {{d = c}} never > returns true. However, the outer side should be preserved and padded with > nulls. > So there seem to be many issues with table function outer joins. Especially, > the wrong result produced by SQL queries need to be quickly fixed. -- This message was sent by Atlassian JIRA (v6.4.14#64029)