[FLINK-7730] TableFunction LEFT OUTER joins with ON predicates are broken

• Type: Bug
• Status: Closed
• Priority: Critical
• Resolution: Fixed
• Affects Version/s: 1.3.2, 1.4.0
• Fix Version/s: 1.3.0, 1.4.0
• Component/s: Table SQL / API
• Assignee: Xingcan Cui
• Reporter: Fabian Hueske
• Created: 28/Sep/17 13:48
• Updated: 02/Apr/19 16:24
• Resolved: 19/Oct/17 19:39

Description

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.

Sub-Tasks

• Reject table function outer joins with predicates in Table API: Closed
• Reject lateral table outer joins with predicates in SQL: Closed