flink-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "ASF GitHub Bot (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (FLINK-7730) TableFunction LEFT OUTER joins with ON predicates are broken
Date Mon, 16 Oct 2017 10:58:00 GMT

    [ 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:

    --- 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

> 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

View raw message