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

Join sql cannot get result

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Major
    • Resolution: Duplicate
    • Affects Version/s: 1.1.1, 1.2.1
    • Fix Version/s: None
    • Labels:
      None

      Description

      I found a join issue in hive-1.2.1 and hive-1.1.1.
      The create table sql is as below.

      CREATE TABLE IF NOT EXISTS join_case(
          orderid  bigint,
          tradeitemid bigint,
          id bigint
      ) ROW FORMAT DELIMITED
      FIELDS TERMINATED BY ',' 
      LINES TERMINATED BY '\n'
      STORED AS TEXTFILE;
      

      Please put attached sample data file 000000_0 in /tmp/join_case folder.
      Then load data.

      LOAD DATA LOCAL INPATH '/tmp/join_case/000000_0' OVERWRITE INTO TABLE join_case;
      

      Run the following sql, but cannot get searching result.

      select a.id from 
      (
      select orderid as orderid, max(id) as id from join_case group by orderid
      ) a 
      join 
      (
      select id as id , orderid as orderid from join_case
      ) b
      on a.id = b.id limit 10;
      

      This issue also occurs in hive-1.1.0-cdh5.4.5.
      But in apache hive-1.0.1 the above sql can return 10 rows.

      After exchanging the sequence of "orderid as orderid" and "max(id) as id", the following sql can get result in hive-1.2.1 and hive-1.1.1.

      select a.id from 
      (
      select max(id) as id, orderid as orderid from join_case group by orderid
      ) a 
      join 
      (
      select id as id , orderid as orderid from join_case
      ) b
      on a.id = b.id limit 10;
      

      Also, the following sql can get results in hive-1.2.1 and hive-1.1.1.

      select a.id from 
      (
      select orderid as orderid, id as id from join_case group by orderid, id
      ) a 
      join 
      (
      select id as id , orderid as orderid from join_case
      ) b
      on a.id = b.id limit 10; 
      

      Anyone can take a look at this issue?
      Thanks.

        Attachments

        1. 000000_0
          7 kB
          NING DING

          Issue Links

            Activity

              People

              • Assignee:
                Huang Xiaomeng Xiaomeng Huang
                Reporter:
                iceberg565 NING DING
              • Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: