Uploaded image for project: 'Hive'
  1. Hive
  2. HIVE-24078

result rows not equal in mr and tez

Log workAgile BoardRank to TopRank to BottomAdd voteVotersWatch issueWatchersCreate sub-taskConvert to sub-taskMoveLinkCloneLabelsUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Open
    • Priority: Blocker
    • Resolution: Unresolved
    • Affects Version/s: 3.1.2
    • Fix Version/s: None
    • Component/s: HiveServer2, Tez
    • Labels:
      None

      Description

      select
      rank_num,
      province_name,
      programset_id,
      programset_name,
      programset_type,
      cv,
      uv,
      pt,
      rank_num2,
      rank_num3,
      city_name,
      level,
      cp_code,
      cp_name,
      version_type,
      zz.city_code,
      zz.province_alias,
      '20200815' dt
      from
      (SELECT row_number() over(partition BY a1.province_alias,a1.city_code,a1.version_type
      ORDER BY cast(a1.cv AS bigint) DESC) AS rank_num,
      province_name(a1.province_alias) AS province_name,
      a1.program_set_id AS programset_id,
      a2.programset_name,
      a2.type_name AS programset_type,
      a1.cv,
      a1.uv,
      cast(a1.pt/3600000 as decimal(20,2)) pt,
      row_number() over (partition by a1.province_alias,a1.city_code,a1.version_type order by cast(a1.uv as bigint) desc ) as rank_num2,
      row_number() over (partition by a1.province_alias,a1.city_code,a1.version_type order by cast(a1.pt as bigint) desc ) as rank_num3,
      a1.city_code,
      a1.city_name,
      '3' as level,
      a2.cp_code,
      a2.cp_name,
      '20200815'as dt,
      a1.province_alias,
      a1.version_type
      FROM temp.dmp_device_vod_valid_day_v1_20200815_hn a1
      LEFT JOIN temp.dmp_device_vod_valid_day_v2_20200815_hn a2 ON a1.program_set_id=a2.programset_id
      WHERE a2.programset_name IS NOT NULL ) zz
      where rank_num<1000 or rank_num2<1000 or rank_num3<1000
      ;

       

      This sql gets 76742 rows in mr, but 76681 rows in tez.How to fix it?

      I think the problem maybe lies in row_number.

        Attachments

          Activity

          $i18n.getText('security.level.explanation', $currentSelection) Viewable by All Users
          Cancel

            People

              Dates

              • Due:
                Created:
                Updated:

                Issue deployment