Return-Path: X-Original-To: apmail-hive-issues-archive@minotaur.apache.org Delivered-To: apmail-hive-issues-archive@minotaur.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 05D91177A3 for ; Wed, 23 Sep 2015 06:34:08 +0000 (UTC) Received: (qmail 50463 invoked by uid 500); 23 Sep 2015 06:34:05 -0000 Delivered-To: apmail-hive-issues-archive@hive.apache.org Received: (qmail 50437 invoked by uid 500); 23 Sep 2015 06:34:04 -0000 Mailing-List: contact issues-help@hive.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: dev@hive.apache.org Delivered-To: mailing list issues@hive.apache.org Received: (qmail 50427 invoked by uid 99); 23 Sep 2015 06:34:04 -0000 Received: from arcas.apache.org (HELO arcas.apache.org) (140.211.11.28) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 23 Sep 2015 06:34:04 +0000 Date: Wed, 23 Sep 2015 06:34:04 +0000 (UTC) From: "NING DING (JIRA)" To: issues@hive.apache.org Message-ID: In-Reply-To: References: Subject: [jira] [Updated] (HIVE-11931) Join sql cannot get result MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394 [ https://issues.apache.org/jira/browse/HIVE-11931?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] NING DING updated HIVE-11931: ----------------------------- Attachment: 000000_0 sample data file > Join sql cannot get result > -------------------------- > > Key: HIVE-11931 > URL: https://issues.apache.org/jira/browse/HIVE-11931 > Project: Hive > Issue Type: Bug > Components: Hive, Query Planning, Query Processor, SQL > Affects Versions: 1.1.1, 1.2.1 > Reporter: NING DING > Attachments: 000000_0 > > > I found a join issue in hive-1.2.1 and hive-1.1.1. > The create table sql is as below. > {code} > 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; > {code} > Please put attached sample data file 000000_0 in /tmp/join_case folder. > Then load data. > {code} > LOAD DATA LOCAL INPATH '/tmp/join_case/000000_0' OVERWRITE INTO TABLE join_case; > {code} > Run the following sql, but cannot get searching result. > {code} > 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; > {code} > 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. > {code} > 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; > {code} > Also, the following sql can get results in hive-1.2.1 and hive-1.1.1. > {code} > 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; > {code} > Anyone can take a look at this issue? > Thanks. -- This message was sent by Atlassian JIRA (v6.3.4#6332)