Leonard Xu created FLINK-14900:
----------------------------------
Summary: output contains null value when a is null where a <>
'String'
Key: FLINK-14900
URL: https://issues.apache.org/jira/browse/FLINK-14900
Project: Flink
Issue Type: Bug
Components: Table SQL / Planner
Affects Versions: 1.9.1
Reporter: Leonard Xu
Fix For: 1.10.0
the result of TPC-DS query 19 is incorrect, I analyze the simplify the sql as
following:
{code:java}
// simplify query
select top 100 ca_zip,s_zip,substring(ca_zip,1,5), substring(s_zip,1,5)
from customer_address,store
where substring(ca_zip,1,5) <> substring(s_zip,1,5)
order by ca_zip,s_zip{code}
flink output:
{code:java}
null|31904|null|31904
null|31904|null|31904
null|31904|null|31904
null|31904|null|31904{code}
SQL server output:
{code:java}
00601 31904 00601 31904
00601 31904 00601 31904
00601 31904 00601 31904
00601 31904 00601 31904
{code}
And this issue may influent the result of tpcds query 46、68.
I tested expression test and sql ITcase，they will return correct value.
{code:java}
@Test
def testSubStr(): Unit = {
checkQuery(
Seq[(String, String, Double)]((null, "00601" , 100d), ("00501", "00601",
50d)),
"select f0, substring(f0,1,5) from Table1 where substring(f0,1,5) <> f1 ",
Seq(("00501", "00501"))
)
}
{code}
So, I need to dig more about this issue.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
