[
https://issues.apache.org/jira/browse/FLINK-5256?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15826134#comment-15826134
]
ASF GitHub Bot commented on FLINK-5256:
---------------------------------------
Github user fhueske commented on a diff in the pull request:
https://github.com/apache/flink/pull/3033#discussion_r96420110
--- Diff:
flink-libraries/flink-table/src/test/scala/org/apache/flink/table/api/scala/batch/sql/DataSetSingleRowJoinTest.scala
---
@@ -192,4 +192,145 @@ class SingleRowJoinTest extends TableTestBase {
util.verifySql(query, expected)
}
+
+ @Test
+ def testSingleRowJoinLeftOuterJoin(): Unit = {
+ val util = batchTestUtil()
+ util.addTable[(Int, Int)]("A", 'a1, 'a2)
+ util.addTable[(Int, Int)]("B", 'b1, 'b2)
+
+ val queryLeftJoin =
+ "SELECT a2 FROM A " +
+ "LEFT JOIN " +
+ "(SELECT COUNT(*) AS cnt FROM B) " +
+ "AS x " +
+ "ON a1 < cnt"
+
+ val expected =
+ unaryNode(
+ "DataSetCalc",
+ unaryNode(
+ "DataSetSingleRowJoin",
+ batchTableNode(0),
+ term("where", "<(a1, cnt)"),
+ term("join", "a1", "a2", "cnt"),
+ term("joinType", "NestedLoopJoin")
+ ),
+ term("select", "a2")
+ ) + "\n" +
+ unaryNode(
+ "DataSetAggregate",
+ unaryNode(
+ "DataSetUnion",
+ unaryNode(
+ "DataSetValues",
+ unaryNode(
+ "DataSetCalc",
+ batchTableNode(1),
+ term("select", "0 AS $f0")),
+ tuples(List(null)), term("values", "$f0")
+ ),
+ term("union", "$f0")
+ ),
+ term("select", "COUNT(*) AS cnt")
+ )
+
+ util.verifySql(queryLeftJoin, expected)
+ }
+
+ @Test
+ def testSingleRowJoinRightOuterJoin(): Unit = {
+ val util = batchTestUtil()
+ util.addTable[(Int, Int)]("A", 'a1, 'a2)
+ util.addTable[(Int, Int)]("B", 'b1, 'b2)
+
+ val queryRightJoin =
+ "SELECT a2 FROM A " +
+ "RIGHT JOIN " +
+ "(SELECT COUNT(*) AS cnt FROM B) " +
--- End diff --
This should not be allowed. It should rather be
```
SELECT a2
FROM
(SELECT COUNT(*) AS cnt FROM B)
RIGHT JOIN
A
```
The single input must always be on the inner side and the multi input on
the outer side.
We should add test cases for left and right outer join to check that this
condition is not violated.
> Extend DataSetSingleRowJoin to support Left and Right joins
> -----------------------------------------------------------
>
> Key: FLINK-5256
> URL: https://issues.apache.org/jira/browse/FLINK-5256
> Project: Flink
> Issue Type: Improvement
> Components: Table API & SQL
> Affects Versions: 1.2.0
> Reporter: Fabian Hueske
> Assignee: Anton Mushin
>
> The {{DataSetSingleRowJoin}} is a broadcast-map join that supports arbitrary
> inner joins where one input is a single row.
> I found that Calcite translates certain subqueries into non-equi left and
> right joins with single input. These cases can be handled if the
> {{DataSetSingleRowJoin}} is extended to support outer joins on the
> non-single-row input, i.e., left joins if the right side is single input and
> vice versa.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
