[
https://issues.apache.org/jira/browse/FLINK-14886?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16979088#comment-16979088
]
Leonard Xu edited comment on FLINK-14886 at 11/21/19 8:51 AM:
--------------------------------------------------------------
use legacy planner, return correct result.
{code:java}
// org.apache.flink.table.runtime.batch.sql.JavaSqlITCase
@Test
public void testScalarQuery() throws Exception {
ExecutionEnvironment env = ExecutionEnvironment.getExecutionEnvironment();
BatchTableEnvironment tableEnv = BatchTableEnvironment.create(env, config());
DataSet<Tuple2<String, Double>> ds =
CollectionDataSets.getSmall2TupleDataSet(env);
tableEnv.registerDataSet("t", ds, "a, b");
String sqlQuery =
"select t1.a, t1.b, \n" +
" (select avg(t2.b) \n" +
" from t t2\n" +
" where t2.a = t1.a)\n" +
"from t t1 order by t1.a, t1.b\n";
Table result = tableEnv.sqlQuery(sqlQuery);
System.out.println(tableEnv.explain(result));
DataSet<Row> resultSet = tableEnv.toDataSet(result, Row.class);
List<Row> results = resultSet.collect();
String expected = "boy,100.0,100.0\n" + "null,50.0,null\n";
compareResultAsText(results, expected);
}
{code}
use Blink planner, return wrong result(test failed):
{code:java}
// org.apache.flink.table.planner.runtime.batch.sql.MiscITCase
@Test
def testScalarQueryJoin(): Unit = {
checkQuery(
Seq[(String, Double)](("boy", 100d), (null, 50d)),
"select t1.f0, t1.f1,(select avg(t2.f1) \n from Table1 t2\n where t2.f0
= t1.f0)\nfrom Table1 t1",
Seq(("boy", 100, 100), ("boy", 100, null))
)
} {code}
was (Author: leonard xu):
use legacy planner, return correct result.
{code:java}
// org.apache.flink.table.runtime.batch.sql.JavaSqlITCase
@Test
public void testScalarQuery() throws Exception {
ExecutionEnvironment env = ExecutionEnvironment.getExecutionEnvironment();
BatchTableEnvironment tableEnv = BatchTableEnvironment.create(env, config());
DataSet<Tuple2<String, Double>> ds =
CollectionDataSets.getSmall2TupleDataSet(env);
tableEnv.registerDataSet("t", ds, "a, b");
String sqlQuery =
"select t1.a, t1.b, \n" +
" (select avg(t2.b) \n" +
" from t t2\n" +
" where t2.a = t1.a)\n" +
"from t t1 order by t1.a, t1.b\n";
Table result = tableEnv.sqlQuery(sqlQuery);
System.out.println(tableEnv.explain(result));
DataSet<Row> resultSet = tableEnv.toDataSet(result, Row.class);
List<Row> results = resultSet.collect();
String expected = "boy,100.0,100.0\n" + "null,50.0,null\n";
compareResultAsText(results, expected);
}
{code}
use Blink planner, return wrong result:
{code:java}
// org.apache.flink.table.planner.runtime.batch.sql.MiscITCase
@Test
def testScalarQueryJoin(): Unit = {
checkQuery(
Seq[(String, Double)](("boy", 100d), (null, 50d)),
"select t1.f0, t1.f1,(select avg(t2.f1) \n from Table1 t2\n where t2.f0
= t1.f0)\nfrom Table1 t1",
Seq(("boy", 100, 100), ("boy", 100, null))
)
}
{code}
> Wrong result in scalar query using blink planner
> ------------------------------------------------
>
> Key: FLINK-14886
> URL: https://issues.apache.org/jira/browse/FLINK-14886
> Project: Flink
> Issue Type: Bug
> Components: Table SQL / Planner
> Affects Versions: 1.9.1
> Reporter: Leonard Xu
> Priority: Blocker
> Fix For: 1.10.0
>
>
>
> In TPC-DS query6.sql， I found the query result is incorrect, I analyze the
> root cause and simplify the issue as following:
> {code:java}
> create table t(
> a varchar(100),
> b float
> );
> insert into t(a,b) values('boy', 100);
> insert into t(a,b) values(null, 50);
> select t1.a, t1.b, (select avg(t2.b)
> from t t2
> where t2.a = t1.a)
> from t t1;
> {code}
> the result when use blink planner is:
> {code:java}
> | a | b | EXP |
> |-----|-----|-----|
> | boy| 100| 100|
> | null| 50| null|
> {code}
> but correct result should be :
> {code:java}
> | a | b | EXP |
> |-----|-----|-----|
> | boy| 100| null|
> | null| 50| null|
> {code}
> Both flink legacy planner and other DB system can produce the correct
> result.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
