Github user fhueske commented on a diff in the pull request:
https://github.com/apache/flink/pull/3673#discussion_r113666308
--- Diff:
flink-libraries/flink-table/src/test/scala/org/apache/flink/table/api/scala/batch/sql/JoinITCase.scala
---
@@ -369,10 +363,258 @@ 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")
+
+ val results = result.toDataSet[Row].collect()
+ TestBaseUtils.compareResultAsText(results.asJava, expected)
+ }
+
+ @Test
+ def testLeftNullLeftJoin (): Unit = {
+ val env = ExecutionEnvironment.getExecutionEnvironment
+ val tEnv = TableEnvironment.getTableEnvironment(env, config)
+
+ val sqlQuery =
+ "SELECT a, cnt " +
+ "FROM" +
+ " (SELECT cnt FROM (SELECT COUNT(*) AS cnt FROM B) WHERE cnt < 0)
" +
+ "LEFT JOIN A " +
+ "ON cnt = a"
+
+ val ds1 =
CollectionDataSets.get5TupleDataSet(env).toTable(tEnv).as('a, 'b, 'c, 'd, 'e)
+ val ds2 = CollectionDataSets.getSmall3TupleDataSet(env).toTable(tEnv)
+ tEnv.registerTable("A", ds1)
+ tEnv.registerTable("B", ds2)
+
+ val result = tEnv.sql(sqlQuery).collect()
+ val resultSize = result.size
+
+ Assert.assertEquals(
+ s"Expected empty result, but actual size result =
$resultSize;\n[${result.mkString(",")}]",
+ resultSize,0)
+ }
+
+ @Test
+ def testLeftNullRightJoin(): Unit = {
+ val env = ExecutionEnvironment.getExecutionEnvironment
+ val tEnv = TableEnvironment.getTableEnvironment(env, config)
+ val sqlQuery =
+ "SELECT a, cnt " +
+ "FROM" +
+ " (SELECT cnt FROM (SELECT COUNT(*) AS cnt FROM B) WHERE cnt < 0)
" +
+ "RIGHT JOIN A " +
+ "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("A", ds1)
+ tEnv.registerTable("B", ds2)
+
+
+ val result = tEnv.sql(sqlQuery)
+ val expected = Seq(
+ "1,null",
+ "2,null", "2,null",
+ "3,null", "3,null", "3,null",
+ "4,null", "4,null", "4,null", "4,null",
+ "5,null", "5,null", "5,null", "5,null", "5,null").mkString("\n")
+
+ val results = result.toDataSet[Row].collect()
+
+ TestBaseUtils.compareResultAsText(results.asJava, expected)
+ }
+
+ @Test
+ def testLeftSingleLeftJoin(): Unit = {
+ val env = ExecutionEnvironment.getExecutionEnvironment
+ val tEnv = TableEnvironment.getTableEnvironment(env, config)
+ val sqlQuery =
+ "SELECT a, cnt " +
+ "FROM" +
+ " (SELECT COUNT(*) AS cnt FROM A) " +
+ "LEFT JOIN B " +
+ "ON cnt = a"
+
+ val ds1 =
CollectionDataSets.get5TupleDataSet(env).toTable(tEnv).as('a, 'b, 'c, 'd, 'e)
+ val ds2 =
CollectionDataSets.getSmall3TupleDataSet(env).toTable(tEnv)as('a, 'b, 'c)
+ tEnv.registerTable("A", ds2)
+ tEnv.registerTable("B", ds1)
+
+ val result = tEnv.sql(sqlQuery)
+ val expected = Seq(
+ "1,null", "2,null", "2,null", "3,3", "3,3",
+ "3,3", "4,null", "4,null", "4,null",
+ "4,null", "5,null", "5,null", "5,null",
+ "5,null", "5,null").mkString("\n")
+
+ val results = result.toDataSet[Row].collect()
+
+ TestBaseUtils.compareResultAsText(results.asJava, expected)
+ }
- Assert.assertEquals(0, result)
+ @Test
+ def testLeftSingleRightJoin(): Unit = {
+ val env = ExecutionEnvironment.getExecutionEnvironment
+ val tEnv = TableEnvironment.getTableEnvironment(env, config)
+ val sqlQuery =
+ "SELECT a, cnt " +
+ "FROM" +
+ " (SELECT COUNT(*) AS cnt FROM B) " +
+ "RIGHT JOIN A " +
+ "ON cnt = a"
+
+ val ds1 =
CollectionDataSets.get5TupleDataSet(env).toTable(tEnv).as('a, 'b, 'c, 'd, 'e)
+ val ds2 = CollectionDataSets.getSmall3TupleDataSet(env).toTable(tEnv)
+ tEnv.registerTable("A", ds1)
+ tEnv.registerTable("B", ds2)
+
+ val result = tEnv.sql(sqlQuery)
+ val expected = Seq(
+ "1,null", "2,null", "2,null", "3,3", "3,3",
+ "3,3", "4,null", "4,null", "4,null",
+ "4,null", "5,null", "5,null", "5,null",
+ "5,null", "5,null").mkString("\n")
+
+ val results = result.toDataSet[Row].collect()
+
+ TestBaseUtils.compareResultAsText(results.asJava, expected)
+ }
+
+ @Test
+ def testRightNullLeftJoin(): Unit = {
+ val env = ExecutionEnvironment.getExecutionEnvironment
+ val tEnv = TableEnvironment.getTableEnvironment(env, config)
+ val sqlQuery =
+ "SELECT a, cnt " +
+ "FROM" +
+ " A " +
+ "LEFT JOIN (SELECT cnt FROM (SELECT COUNT(*) AS cnt FROM B) WHERE
cnt < 0) " +
+ "ON cnt = a"
+
+ val ds1 = CollectionDataSets.get5TupleDataSet(env).toTable(tEnv)
+ val ds2 =
CollectionDataSets.getSmall3TupleDataSet(env).toTable(tEnv).as('a, 'b, 'c)
+ tEnv.registerTable("A", ds2)
+ tEnv.registerTable("B", ds1)
+
+ val result = tEnv.sql(sqlQuery)
+
+ val expected = Seq(
+ "2,null", "3,null", "1,null").mkString("\n")
+
+ val results = result.toDataSet[Row].collect()
+
+ TestBaseUtils.compareResultAsText(results.asJava, expected)
+ }
+
+ @Test
+ def testRightNullRightJoin(): Unit = {
+ val env = ExecutionEnvironment.getExecutionEnvironment
+ val tEnv = TableEnvironment.getTableEnvironment(env, config)
+ val sqlQuery =
+ "SELECT a, cnt " +
+ "FROM A " +
+ "RIGHT JOIN" +
+ " (SELECT cnt FROM (SELECT COUNT(*) AS cnt FROM B) WHERE cnt < 0)
" +
+ "ON cnt = a"
+
+ val ds1 = CollectionDataSets.get5TupleDataSet(env).toTable(tEnv)
+ val ds2 =
CollectionDataSets.getSmall3TupleDataSet(env).toTable(tEnv).as('a, 'b, 'c)
+ tEnv.registerTable("A", ds2)
+ tEnv.registerTable("B", ds1)
+
+ val result = tEnv.sql(sqlQuery).collect()
+ val resultSize = result.size
+
+ Assert.assertEquals(
+ s"Expected empty result, but actual size result =
$resultSize;\n[${result.mkString(",")}]",
+ resultSize,0)
+ }
+
+ @Test
+ def testRightSingleLeftJoin(): Unit = {
+ val env = ExecutionEnvironment.getExecutionEnvironment
+ val tEnv = TableEnvironment.getTableEnvironment(env, config)
+ val sqlQuery =
+ "SELECT a, cnt " +
--- End diff --
Please format all test queries in a consistent style
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastruct...@apache.org or file a JIRA ticket
with INFRA.
---
