[
https://issues.apache.org/jira/browse/FLINK-7730?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16205720#comment-16205720
]
ASF GitHub Bot commented on FLINK-7730:
---------------------------------------
Github user xccui commented on a diff in the pull request:
https://github.com/apache/flink/pull/4830#discussion_r144815350
--- Diff:
flink-libraries/flink-table/src/test/scala/org/apache/flink/table/api/batch/table/CorrelateTest.scala
---
@@ -98,4 +103,17 @@ 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 = batchTestUtil()
+ 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('s === "")// forbidden
--- End diff --
Yes, you are right. In outer joins, the `ON` and the `WHERE` clauses should
be treated differently.
> 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)
