[
https://issues.apache.org/jira/browse/FLINK-5256?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15826143#comment-15826143
]
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_r96423406
--- Diff:
flink-libraries/flink-table/src/test/scala/org/apache/flink/table/api/scala/batch/sql/JoinITCase.scala
---
@@ -372,9 +372,163 @@ class JoinITCase(
val table =
CollectionDataSets.getSmall3TupleDataSet(env).toTable(tEnv).as('a1, 'a2, 'a3)
tEnv.registerTable("A", table)
- val sqlQuery1 = "SELECT * FROM A CROSS JOIN (SELECT count(*) FROM A
HAVING count(*) < 0)"
- val result = tEnv.sql(sqlQuery1).count()
+ val sqlQuery1 = "SELECT * FROM A CROSS JOIN " +
+ "(SELECT count(*) FROM A HAVING count(*) < 0)"
+ val result = tEnv.sql(sqlQuery1)
+ val expected =Seq(
+ "2,2,Hello,null",
+ "1,1,Hi,null",
+ "3,2,Hello world,null").mkString("\n")
- Assert.assertEquals(0, result)
+ val results = result.toDataSet[Row].collect()
+ TestBaseUtils.compareResultAsText(results.asJava, expected)
+ }
+
+ @Test
+ def testSingleRowLeftOuterJoin(): Unit = {
+
+ val env = ExecutionEnvironment.getExecutionEnvironment
+ val tEnv = TableEnvironment.getTableEnvironment(env, config)
+
+ val sqlQuery =
+ "SELECT a, cnt FROM t1 " +
+ "LEFT JOIN (" +
+ "SELECT COUNT(*) AS cnt FROM t2" +
+ ") AS x " +
+ "ON a > cnt"
+
+ val ds1 =
CollectionDataSets.get5TupleDataSet(env).toTable(tEnv).as('a, 'b, 'c, 'd, 'e)
+ val ds2 = CollectionDataSets.getSmall3TupleDataSet(env).toTable(tEnv)
+ tEnv.registerTable("t1", ds1)
+ tEnv.registerTable("t2", ds2)
+
+ val result = tEnv.sql(sqlQuery)
+
+ val expected = Seq(
+ "1,null",
+ "2,null", "2,null",
+ "3,null", "3,null", "3,null",
+ "4,3", "4,3", "4,3", "4,3",
+ "5,3", "5,3", "5,3", "5,3", "5,3").mkString("\n")
+
+ val results = result.toDataSet[Row].collect()
+ TestBaseUtils.compareResultAsText(results.asJava, expected)
+ }
+
+ @Test
+ def testSingleRowRightOuterJoin(): Unit = {
+
+ val env = ExecutionEnvironment.getExecutionEnvironment
+ val tEnv = TableEnvironment.getTableEnvironment(env, config)
+
+ val sqlQuery =
+ "SELECT a, cnt FROM t1 " +
+ "RIGHT JOIN (" +
--- End diff --
the joined tables `A` and `SELECT COUNT(*) AS cnt FROM t2` need to be
switched.
> 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)
