hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Frank Luo <j...@merkleinc.com>
Subject RE: multiple selects on a left join give incorrect result
Date Wed, 04 May 2016 00:04:58 GMT
Hdp 2.2 also works fine.

From: Markovitz, Dudu [mailto:dmarkovitz@paypal.com]
Sent: Tuesday, May 03, 2016 6:56 PM
To: user@hive.apache.org
Cc: Rebecca Yang <yiyang@merkleinc.com>
Subject: RE: multiple selects on a left join give incorrect result

There is no issue on Cloudera VM

Dudu


[cloudera@quickstart ~]$ hadoop version
Hadoop 2.6.0-cdh5.5.0
Subversion http://github.com/cloudera/hadoop -r fd21232cef7b8c1f536965897ce20f50b83ee7b2
Compiled by jenkins on 2015-11-09T20:37Z
Compiled with protoc 2.5.0
From source with checksum 98e07176d1787150a6a9c087627562c
This command was run using /usr/jars/hadoop-common-2.6.0-cdh5.5.0.jar

[cloudera@quickstart ~]$ hive --version
Hive 1.1.0-cdh5.5.0
Subversion file:///data/jenkins/workspace/generic-package-rhel64-6-0/topdir/BUILD/hive-1.1.0-cdh5.5.0<file:///\\data\jenkins\workspace\generic-package-rhel64-6-0\topdir\BUILD\hive-1.1.0-cdh5.5.0>
-r Unknown
Compiled by jenkins on Mon Nov 9 12:37:34 PST 2015
From source with checksum 8dfc2aac3731e4e5f0e8bd1b442be0e2

From: Frank Luo [mailto:jluo@merkleinc.com]
Sent: Wednesday, May 04, 2016 1:58 AM
To: user@hive.apache.org<mailto:user@hive.apache.org>
Cc: Rebecca Yang <yiyang@merkleinc.com<mailto:yiyang@merkleinc.com>>
Subject: multiple selects on a left join give incorrect result

All,

I have found that when doing a multiple selects on a left join, the “on” clause seems
to be ignored!!! (It is hard to believe).

Below is a very simple test case and please tell me I am crazy. I am on hdp 2.3.4.7.


CREATE TABLE T_A ( id    STRING, val   STRING );
CREATE TABLE T_B ( id    STRING, val   STRING );
CREATE TABLE join_result_1 ( ida    STRING, vala   STRING, idb    STRING, valb   STRING );
CREATE TABLE join_result_2 ( ida    STRING, vala   STRING, idb    STRING, valb   STRING );
CREATE TABLE join_result_3 ( ida    STRING, vala   STRING, idb    STRING, valb   STRING );

INSERT INTO TABLE T_A
VALUES ('Id_1', 'val_101'), ('Id_2', 'val_102'), ('Id_3', 'val_103');

INSERT INTO TABLE T_B
VALUES ('Id_1', 'val_103'), ('Id_2', 'val_104');

FROM T_A a LEFT JOIN T_B b ON a.id = b.id
INSERT OVERWRITE TABLE join_result_1
   SELECT a.*, b.*
    WHERE b.id = 'Id_1' AND b.val = 'val_103'
INSERT OVERWRITE TABLE join_result_2
   SELECT a.*, b.*
    WHERE b.val IS NULL OR (b.id = 'Id_3' AND b.val = 'val_101')
INSERT OVERWRITE TABLE join_result_3
   SELECT a.*, b.*
    WHERE b.val = 'val_104' AND b.id = 'Id_2' AND a.val <> b.val;


And here is the result:

0: jdbc:hive2 > select * from join_result_1;
+--------------------+---------------------+--------------------+---------------------+--+
| join_result_1.ida  | join_result_1.vala  | join_result_1.idb  | join_result_1.valb  |
+--------------------+---------------------+--------------------+---------------------+--+
| Id_1               | val_101             | Id_1               | val_103             |
| Id_2               | val_102             | Id_1               | val_103             |
| Id_3               | val_103             | Id_1               | val_103             |
+--------------------+---------------------+--------------------+---------------------+--+
3 rows selected (0.057 seconds)




I am expecting join_result_1 to have one row, but got three!!!

Has other people run into the same thing?

Join us at Merkle’s 2016 annual Performance Marketer Executive Summit – June 7 – 9 in
Memphis, TN
<http://www2.merkleinc.com/l/47252/2016-04-26/3lbfdc>

Download the latest installment of our annual Marketing Imperatives, “Winning with People-Based
Marketing”<http://www2.merkleinc.com/l/47252/2016-04-26/3lbfd1>

This email and any attachments transmitted with it are intended for use by the intended recipient(s)
only. If you have received this email in error, please notify the sender immediately and then
delete it. If you are not the intended recipient, you must not keep, use, disclose, copy or
distribute this email without the author’s prior permission. We take precautions to minimize
the risk of transmitting software viruses, but we advise you to perform your own virus checks
on any attachment to this message. We cannot accept liability for any loss or damage caused
by software viruses. The information contained in this communication may be confidential and
may be subject to the attorney-client privilege.

Join us at Merkle’s 2016 annual Performance Marketer Executive Summit – June 7 – 9 in
Memphis, TN
<http://www2.merkleinc.com/l/47252/2016-04-26/3lbfdc>

Download the latest installment of our annual Marketing Imperatives, “Winning with People-Based
Marketing”<http://www2.merkleinc.com/l/47252/2016-04-26/3lbfd1>

This email and any attachments transmitted with it are intended for use by the intended recipient(s)
only. If you have received this email in error, please notify the sender immediately and then
delete it. If you are not the intended recipient, you must not keep, use, disclose, copy or
distribute this email without the author’s prior permission. We take precautions to minimize
the risk of transmitting software viruses, but we advise you to perform your own virus checks
on any attachment to this message. We cannot accept liability for any loss or damage caused
by software viruses. The information contained in this communication may be confidential and
may be subject to the attorney-client privilege.
Mime
View raw message