[ https://issues.apache.org/jira/browse/FLINK-3943?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15356197#comment-15356197 ]
ASF GitHub Bot commented on FLINK-3943: --------------------------------------- Github user fhueske commented on a diff in the pull request: https://github.com/apache/flink/pull/2169#discussion_r69048368 --- Diff: flink-libraries/flink-table/src/test/scala/org/apache/flink/api/scala/batch/sql/SetOperationsITCase.scala --- @@ -121,4 +121,66 @@ class UnionITCase( TestBaseUtils.compareResultAsText(results.asJava, expected) } + @Test + def testExcept(): Unit = { + + val env = ExecutionEnvironment.getExecutionEnvironment + val tEnv = TableEnvironment.getTableEnvironment(env, config) + + val sqlQuery = "SELECT c FROM t1 EXCEPT (SELECT c FROM t2)" + + val ds1 = CollectionDataSets.getSmall3TupleDataSet(env) + val ds2 = CollectionDataSets.getOneElement3TupleDataSet(env) + tEnv.registerDataSet("t1", ds1, 'a, 'b, 'c) + tEnv.registerDataSet("t2", ds2, 'a, 'b, 'c) + + val result = tEnv.sql(sqlQuery) + + val expected = "Hello\n" + "Hello world\n" + val results = result.toDataSet[Row].collect() + TestBaseUtils.compareResultAsText(results.asJava, expected) + } + + @Test + def testExceptWithFilter(): Unit = { + + val env = ExecutionEnvironment.getExecutionEnvironment + val tEnv = TableEnvironment.getTableEnvironment(env, config) + + val sqlQuery = "SELECT c FROM (" + + "SELECT * FROM t1 EXCEPT (SELECT a, b, c FROM t2))" + + "WHERE b < 2" + + val ds1 = CollectionDataSets.getSmall3TupleDataSet(env) + val ds2 = CollectionDataSets.get5TupleDataSet(env) + tEnv.registerDataSet("t1", ds1, 'a, 'b, 'c) + tEnv.registerDataSet("t2", ds2, 'a, 'b, 'd, 'c, 'e) + + val result = tEnv.sql(sqlQuery) + + val expected = "Hi\n" + val results = result.toDataSet[Row].collect() + TestBaseUtils.compareResultAsText(results.asJava, expected) + } + + @Test + def testExceptWithAggregation(): Unit = { --- End diff -- Please remove this test. > Add support for EXCEPT (set minus) > ---------------------------------- > > Key: FLINK-3943 > URL: https://issues.apache.org/jira/browse/FLINK-3943 > Project: Flink > Issue Type: New Feature > Components: Table API & SQL > Affects Versions: 1.1.0 > Reporter: Fabian Hueske > Assignee: Ivan Mushketyk > Priority: Minor > > Currently, the Table API and SQL do not support EXCEPT. > EXCEPT can be executed as a coGroup on all fields that forwards records of > the first input if the second input is empty. > In order to add support for EXCEPT to the Table API and SQL we need to: > - Implement a {{DataSetMinus}} class that translates an EXCEPT into a DataSet > API program using a coGroup on all fields. > - Implement a {{DataSetMinusRule}} that translates a Calcite {{LogicalMinus}} > into a {{DataSetMinus}}. > - Extend the Table API (and validation phase) to provide an except() method. -- This message was sent by Atlassian JIRA (v6.3.4#6332)