[jira] [Created] (FLINK-14900) output contains null value when a is null where a <> 'String'

[Shang Yuanchun (Jira)]

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)