[jira] [Created] (TEZ-4377) Got wrong result when using inner join and left join and window function

["inza9hi (Jira)" <ji...@apache.org>]

inza9hi created TEZ-4377:
----------------------------

             Summary: Got wrong result when using inner join and left join and window function
                 Key: TEZ-4377
                 URL: https://issues.apache.org/jira/browse/TEZ-4377
             Project: Apache Tez
          Issue Type: Bug
            Reporter: inza9hi


I am using Hive 3.1.0 and Tez 0.9.1.

The sql looks just like this:

 
{code:java}
select 
*
from 
(
  select t1.secuCode
        ,t1.innercode
    from
        (select innerCode
            ,secuCode
            ,cate
        from tmp.test_a1
        where  cate in (8,13)
        ) t1
    inner join
        (
        select secuCode
        from tmp.test_a2
        where type not in ('pb','pbf','licai')
        ) t2
    on t1.secuCode = t2.secuCode
) a 

left join 
    (
    select innercode
        ,esdate
    
    from tmp.test_b
    ) e on a.innerCode = e.innercode

left join 
  (
    select innerCode
        ,1 as if_gold_prize
    from tmp.test_c
    where winnertype = 1
    group by innerCode
  ) f1 on a.innerCode = f1.innerCode


left join 
      (
      select innercode
          ,endate enddate1
      from
         (
          select *
              ,row_number() over (partition by innercode order by endate desc) max_date
          from tmp.test_d
         ) t
      where max_date = 1
      ) m on a.innerCode = m.innercode

left join 
      (
      select innercode
          ,endate enddate2
      from
         (
          select *
              ,row_number() over (partition by innercode order by endate desc) max_date
          from tmp.test_e
          ) t
      where max_date = 1
      ) n on a.innerCode = n.innercode {code}
The num of the records is right, but  there are lots of null *esdate* ( from the first join) which should not be null. 

 

1. Most of the tables are small (about 20MB)

 

 

Sorry it can not reproduces in



--
This message was sent by Atlassian Jira
(v8.20.1#820001)