You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@hive.apache.org by "Navis (JIRA)" <ji...@apache.org> on 2012/07/27 08:21:34 UTC
[jira] [Created] (HIVE-3306) SMBJoin/BucketMapJoin should be
allowed only when join key expression is exactly matches with sort/cluster
key
Navis created HIVE-3306:
---------------------------
Summary: SMBJoin/BucketMapJoin should be allowed only when join key expression is exactly matches with sort/cluster key
Key: HIVE-3306
URL: https://issues.apache.org/jira/browse/HIVE-3306
Project: Hive
Issue Type: Bug
Components: Query Processor
Affects Versions: 0.10.0
Reporter: Navis
Assignee: Navis
Priority: Minor
CREATE TABLE bucket_small (key int, value string) CLUSTERED BY (key) SORTED BY (key) INTO 2 BUCKETS STORED AS TEXTFILE;
load data local inpath '/home/navis/apache/oss-hive/data/files/srcsortbucket1outof4.txt' INTO TABLE bucket_small;
load data local inpath '/home/navis/apache/oss-hive/data/files/srcsortbucket2outof4.txt' INTO TABLE bucket_small;
CREATE TABLE bucket_big (key int, value string) CLUSTERED BY (key) SORTED BY (key) INTO 4 BUCKETS STORED AS TEXTFILE;
load data local inpath '/home/navis/apache/oss-hive/data/files/srcsortbucket1outof4.txt' INTO TABLE bucket_big;
load data local inpath '/home/navis/apache/oss-hive/data/files/srcsortbucket2outof4.txt' INTO TABLE bucket_big;
load data local inpath '/home/navis/apache/oss-hive/data/files/srcsortbucket3outof4.txt' INTO TABLE bucket_big;
load data local inpath '/home/navis/apache/oss-hive/data/files/srcsortbucket4outof4.txt' INTO TABLE bucket_big;
select count(*) FROM bucket_small a JOIN bucket_big b ON a.key + a.key = b.key;
select /* + MAPJOIN(a) */ count(*) FROM bucket_small a JOIN bucket_big b ON a.key + a.key = b.key;
returns 116 (same)
But with BucketMapJoin or SMBJoin, it returns 61. But this should not be allowed cause hash(a.key) != hash(a.key + a.key).
Bucket context should be utilized only with exact matching join expression with sort/cluster key.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Updated] (HIVE-3306) SMBJoin/BucketMapJoin should be
allowed only when join key expression is exactly matches with sort/cluster
key
Posted by "Navis (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/HIVE-3306?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Navis updated HIVE-3306:
------------------------
Status: Patch Available (was: Open)
https://reviews.facebook.net/D4461
> SMBJoin/BucketMapJoin should be allowed only when join key expression is exactly matches with sort/cluster key
> --------------------------------------------------------------------------------------------------------------
>
> Key: HIVE-3306
> URL: https://issues.apache.org/jira/browse/HIVE-3306
> Project: Hive
> Issue Type: Bug
> Components: Query Processor
> Affects Versions: 0.10.0
> Reporter: Navis
> Assignee: Navis
> Priority: Minor
>
> CREATE TABLE bucket_small (key int, value string) CLUSTERED BY (key) SORTED BY (key) INTO 2 BUCKETS STORED AS TEXTFILE;
> load data local inpath '/home/navis/apache/oss-hive/data/files/srcsortbucket1outof4.txt' INTO TABLE bucket_small;
> load data local inpath '/home/navis/apache/oss-hive/data/files/srcsortbucket2outof4.txt' INTO TABLE bucket_small;
> CREATE TABLE bucket_big (key int, value string) CLUSTERED BY (key) SORTED BY (key) INTO 4 BUCKETS STORED AS TEXTFILE;
> load data local inpath '/home/navis/apache/oss-hive/data/files/srcsortbucket1outof4.txt' INTO TABLE bucket_big;
> load data local inpath '/home/navis/apache/oss-hive/data/files/srcsortbucket2outof4.txt' INTO TABLE bucket_big;
> load data local inpath '/home/navis/apache/oss-hive/data/files/srcsortbucket3outof4.txt' INTO TABLE bucket_big;
> load data local inpath '/home/navis/apache/oss-hive/data/files/srcsortbucket4outof4.txt' INTO TABLE bucket_big;
> select count(*) FROM bucket_small a JOIN bucket_big b ON a.key + a.key = b.key;
> select /* + MAPJOIN(a) */ count(*) FROM bucket_small a JOIN bucket_big b ON a.key + a.key = b.key;
> returns 116 (same)
> But with BucketMapJoin or SMBJoin, it returns 61. But this should not be allowed cause hash(a.key) != hash(a.key + a.key).
> Bucket context should be utilized only with exact matching join expression with sort/cluster key.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (HIVE-3306) SMBJoin/BucketMapJoin should be
allowed only when join key expression is exactly matches with sort/cluster
key
Posted by "Lianhui Wang (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/HIVE-3306?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13427069#comment-13427069 ]
Lianhui Wang commented on HIVE-3306:
------------------------------------
also i think there have another context.
example:ON (a.key = b.key and a.key=10)
this should scan the 10 bucket's file.not all bucket's file in the table's path.
> SMBJoin/BucketMapJoin should be allowed only when join key expression is exactly matches with sort/cluster key
> --------------------------------------------------------------------------------------------------------------
>
> Key: HIVE-3306
> URL: https://issues.apache.org/jira/browse/HIVE-3306
> Project: Hive
> Issue Type: Bug
> Components: Query Processor
> Affects Versions: 0.10.0
> Reporter: Navis
> Assignee: Navis
> Priority: Minor
>
> CREATE TABLE bucket_small (key int, value string) CLUSTERED BY (key) SORTED BY (key) INTO 2 BUCKETS STORED AS TEXTFILE;
> load data local inpath '/home/navis/apache/oss-hive/data/files/srcsortbucket1outof4.txt' INTO TABLE bucket_small;
> load data local inpath '/home/navis/apache/oss-hive/data/files/srcsortbucket2outof4.txt' INTO TABLE bucket_small;
> CREATE TABLE bucket_big (key int, value string) CLUSTERED BY (key) SORTED BY (key) INTO 4 BUCKETS STORED AS TEXTFILE;
> load data local inpath '/home/navis/apache/oss-hive/data/files/srcsortbucket1outof4.txt' INTO TABLE bucket_big;
> load data local inpath '/home/navis/apache/oss-hive/data/files/srcsortbucket2outof4.txt' INTO TABLE bucket_big;
> load data local inpath '/home/navis/apache/oss-hive/data/files/srcsortbucket3outof4.txt' INTO TABLE bucket_big;
> load data local inpath '/home/navis/apache/oss-hive/data/files/srcsortbucket4outof4.txt' INTO TABLE bucket_big;
> select count(*) FROM bucket_small a JOIN bucket_big b ON a.key + a.key = b.key;
> select /* + MAPJOIN(a) */ count(*) FROM bucket_small a JOIN bucket_big b ON a.key + a.key = b.key;
> returns 116 (same)
> But with BucketMapJoin or SMBJoin, it returns 61. But this should not be allowed cause hash(a.key) != hash(a.key + a.key).
> Bucket context should be utilized only with exact matching join expression with sort/cluster key.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (HIVE-3306) SMBJoin/BucketMapJoin should be
allowed only when join key expression is exactly matches with sort/cluster
key
Posted by "Namit Jain (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/HIVE-3306?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13427110#comment-13427110 ]
Namit Jain commented on HIVE-3306:
----------------------------------
@Lianhui, that is a separate (and important) issue.
Can you file a new jira for that ?
> SMBJoin/BucketMapJoin should be allowed only when join key expression is exactly matches with sort/cluster key
> --------------------------------------------------------------------------------------------------------------
>
> Key: HIVE-3306
> URL: https://issues.apache.org/jira/browse/HIVE-3306
> Project: Hive
> Issue Type: Bug
> Components: Query Processor
> Affects Versions: 0.10.0
> Reporter: Navis
> Assignee: Navis
> Priority: Minor
>
> CREATE TABLE bucket_small (key int, value string) CLUSTERED BY (key) SORTED BY (key) INTO 2 BUCKETS STORED AS TEXTFILE;
> load data local inpath '/home/navis/apache/oss-hive/data/files/srcsortbucket1outof4.txt' INTO TABLE bucket_small;
> load data local inpath '/home/navis/apache/oss-hive/data/files/srcsortbucket2outof4.txt' INTO TABLE bucket_small;
> CREATE TABLE bucket_big (key int, value string) CLUSTERED BY (key) SORTED BY (key) INTO 4 BUCKETS STORED AS TEXTFILE;
> load data local inpath '/home/navis/apache/oss-hive/data/files/srcsortbucket1outof4.txt' INTO TABLE bucket_big;
> load data local inpath '/home/navis/apache/oss-hive/data/files/srcsortbucket2outof4.txt' INTO TABLE bucket_big;
> load data local inpath '/home/navis/apache/oss-hive/data/files/srcsortbucket3outof4.txt' INTO TABLE bucket_big;
> load data local inpath '/home/navis/apache/oss-hive/data/files/srcsortbucket4outof4.txt' INTO TABLE bucket_big;
> select count(*) FROM bucket_small a JOIN bucket_big b ON a.key + a.key = b.key;
> select /* + MAPJOIN(a) */ count(*) FROM bucket_small a JOIN bucket_big b ON a.key + a.key = b.key;
> returns 116 (same)
> But with BucketMapJoin or SMBJoin, it returns 61. But this should not be allowed cause hash(a.key) != hash(a.key + a.key).
> Bucket context should be utilized only with exact matching join expression with sort/cluster key.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (HIVE-3306) SMBJoin/BucketMapJoin should be
allowed only when join key expression is exactly matches with sort/cluster
key
Posted by "Lianhui Wang (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/HIVE-3306?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13427127#comment-13427127 ]
Lianhui Wang commented on HIVE-3306:
------------------------------------
@Namit, i created a new jira HIVE-3329, maybe there has some tasks.
now i finish the work that the table is not partition table.
next i will work for the partition table.
> SMBJoin/BucketMapJoin should be allowed only when join key expression is exactly matches with sort/cluster key
> --------------------------------------------------------------------------------------------------------------
>
> Key: HIVE-3306
> URL: https://issues.apache.org/jira/browse/HIVE-3306
> Project: Hive
> Issue Type: Bug
> Components: Query Processor
> Affects Versions: 0.10.0
> Reporter: Navis
> Assignee: Navis
> Priority: Minor
>
> CREATE TABLE bucket_small (key int, value string) CLUSTERED BY (key) SORTED BY (key) INTO 2 BUCKETS STORED AS TEXTFILE;
> load data local inpath '/home/navis/apache/oss-hive/data/files/srcsortbucket1outof4.txt' INTO TABLE bucket_small;
> load data local inpath '/home/navis/apache/oss-hive/data/files/srcsortbucket2outof4.txt' INTO TABLE bucket_small;
> CREATE TABLE bucket_big (key int, value string) CLUSTERED BY (key) SORTED BY (key) INTO 4 BUCKETS STORED AS TEXTFILE;
> load data local inpath '/home/navis/apache/oss-hive/data/files/srcsortbucket1outof4.txt' INTO TABLE bucket_big;
> load data local inpath '/home/navis/apache/oss-hive/data/files/srcsortbucket2outof4.txt' INTO TABLE bucket_big;
> load data local inpath '/home/navis/apache/oss-hive/data/files/srcsortbucket3outof4.txt' INTO TABLE bucket_big;
> load data local inpath '/home/navis/apache/oss-hive/data/files/srcsortbucket4outof4.txt' INTO TABLE bucket_big;
> select count(*) FROM bucket_small a JOIN bucket_big b ON a.key + a.key = b.key;
> select /* + MAPJOIN(a) */ count(*) FROM bucket_small a JOIN bucket_big b ON a.key + a.key = b.key;
> returns 116 (same)
> But with BucketMapJoin or SMBJoin, it returns 61. But this should not be allowed cause hash(a.key) != hash(a.key + a.key).
> Bucket context should be utilized only with exact matching join expression with sort/cluster key.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (HIVE-3306) SMBJoin/BucketMapJoin should be
allowed only when join key expression is exactly matches with sort/cluster
key
Posted by "Hudson (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/HIVE-3306?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13450184#comment-13450184 ]
Hudson commented on HIVE-3306:
------------------------------
Integrated in Hive-trunk-h0.21 #1652 (See [https://builds.apache.org/job/Hive-trunk-h0.21/1652/])
HIVE-3306 SMBJoin/BucketMapJoin should be allowed only when join key expression is exactly matches
with sort/cluster key (Navis via namit) (Revision 1381669)
Result = FAILURE
namit : http://svn.apache.org/viewcvs.cgi/?root=Apache-SVN&view=rev&rev=1381669
Files :
* /hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/optimizer/BucketMapJoinOptimizer.java
* /hive/trunk/ql/src/test/queries/clientpositive/bucket_map_join_1.q
* /hive/trunk/ql/src/test/queries/clientpositive/bucket_map_join_2.q
* /hive/trunk/ql/src/test/queries/clientpositive/bucketmapjoin_negative3.q
* /hive/trunk/ql/src/test/results/clientpositive/bucket_map_join_1.q.out
* /hive/trunk/ql/src/test/results/clientpositive/bucket_map_join_2.q.out
* /hive/trunk/ql/src/test/results/clientpositive/bucketmapjoin_negative3.q.out
> SMBJoin/BucketMapJoin should be allowed only when join key expression is exactly matches with sort/cluster key
> --------------------------------------------------------------------------------------------------------------
>
> Key: HIVE-3306
> URL: https://issues.apache.org/jira/browse/HIVE-3306
> Project: Hive
> Issue Type: Bug
> Components: Query Processor
> Affects Versions: 0.10.0
> Reporter: Navis
> Assignee: Navis
> Priority: Minor
> Fix For: 0.10.0
>
> Attachments: HIVE-3306.1.patch.txt
>
>
> CREATE TABLE bucket_small (key int, value string) CLUSTERED BY (key) SORTED BY (key) INTO 2 BUCKETS STORED AS TEXTFILE;
> load data local inpath '/home/navis/apache/oss-hive/data/files/srcsortbucket1outof4.txt' INTO TABLE bucket_small;
> load data local inpath '/home/navis/apache/oss-hive/data/files/srcsortbucket2outof4.txt' INTO TABLE bucket_small;
> CREATE TABLE bucket_big (key int, value string) CLUSTERED BY (key) SORTED BY (key) INTO 4 BUCKETS STORED AS TEXTFILE;
> load data local inpath '/home/navis/apache/oss-hive/data/files/srcsortbucket1outof4.txt' INTO TABLE bucket_big;
> load data local inpath '/home/navis/apache/oss-hive/data/files/srcsortbucket2outof4.txt' INTO TABLE bucket_big;
> load data local inpath '/home/navis/apache/oss-hive/data/files/srcsortbucket3outof4.txt' INTO TABLE bucket_big;
> load data local inpath '/home/navis/apache/oss-hive/data/files/srcsortbucket4outof4.txt' INTO TABLE bucket_big;
> select count(*) FROM bucket_small a JOIN bucket_big b ON a.key + a.key = b.key;
> select /* + MAPJOIN(a) */ count(*) FROM bucket_small a JOIN bucket_big b ON a.key + a.key = b.key;
> returns 116 (same)
> But with BucketMapJoin or SMBJoin, it returns 61. But this should not be allowed cause hash(a.key) != hash(a.key + a.key).
> Bucket context should be utilized only with exact matching join expression with sort/cluster key.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Updated] (HIVE-3306) SMBJoin/BucketMapJoin should be
allowed only when join key expression is exactly matches with sort/cluster
key
Posted by "Navis (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/HIVE-3306?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Navis updated HIVE-3306:
------------------------
Attachment: HIVE-3306.1.patch.txt
> SMBJoin/BucketMapJoin should be allowed only when join key expression is exactly matches with sort/cluster key
> --------------------------------------------------------------------------------------------------------------
>
> Key: HIVE-3306
> URL: https://issues.apache.org/jira/browse/HIVE-3306
> Project: Hive
> Issue Type: Bug
> Components: Query Processor
> Affects Versions: 0.10.0
> Reporter: Navis
> Assignee: Navis
> Priority: Minor
> Attachments: HIVE-3306.1.patch.txt
>
>
> CREATE TABLE bucket_small (key int, value string) CLUSTERED BY (key) SORTED BY (key) INTO 2 BUCKETS STORED AS TEXTFILE;
> load data local inpath '/home/navis/apache/oss-hive/data/files/srcsortbucket1outof4.txt' INTO TABLE bucket_small;
> load data local inpath '/home/navis/apache/oss-hive/data/files/srcsortbucket2outof4.txt' INTO TABLE bucket_small;
> CREATE TABLE bucket_big (key int, value string) CLUSTERED BY (key) SORTED BY (key) INTO 4 BUCKETS STORED AS TEXTFILE;
> load data local inpath '/home/navis/apache/oss-hive/data/files/srcsortbucket1outof4.txt' INTO TABLE bucket_big;
> load data local inpath '/home/navis/apache/oss-hive/data/files/srcsortbucket2outof4.txt' INTO TABLE bucket_big;
> load data local inpath '/home/navis/apache/oss-hive/data/files/srcsortbucket3outof4.txt' INTO TABLE bucket_big;
> load data local inpath '/home/navis/apache/oss-hive/data/files/srcsortbucket4outof4.txt' INTO TABLE bucket_big;
> select count(*) FROM bucket_small a JOIN bucket_big b ON a.key + a.key = b.key;
> select /* + MAPJOIN(a) */ count(*) FROM bucket_small a JOIN bucket_big b ON a.key + a.key = b.key;
> returns 116 (same)
> But with BucketMapJoin or SMBJoin, it returns 61. But this should not be allowed cause hash(a.key) != hash(a.key + a.key).
> Bucket context should be utilized only with exact matching join expression with sort/cluster key.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Updated] (HIVE-3306) SMBJoin/BucketMapJoin should be
allowed only when join key expression is exactly matches with sort/cluster
key
Posted by "Namit Jain (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/HIVE-3306?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Namit Jain updated HIVE-3306:
-----------------------------
Resolution: Fixed
Fix Version/s: 0.10.0
Hadoop Flags: Reviewed
Status: Resolved (was: Patch Available)
Committed. Thanks Navis
> SMBJoin/BucketMapJoin should be allowed only when join key expression is exactly matches with sort/cluster key
> --------------------------------------------------------------------------------------------------------------
>
> Key: HIVE-3306
> URL: https://issues.apache.org/jira/browse/HIVE-3306
> Project: Hive
> Issue Type: Bug
> Components: Query Processor
> Affects Versions: 0.10.0
> Reporter: Navis
> Assignee: Navis
> Priority: Minor
> Fix For: 0.10.0
>
> Attachments: HIVE-3306.1.patch.txt
>
>
> CREATE TABLE bucket_small (key int, value string) CLUSTERED BY (key) SORTED BY (key) INTO 2 BUCKETS STORED AS TEXTFILE;
> load data local inpath '/home/navis/apache/oss-hive/data/files/srcsortbucket1outof4.txt' INTO TABLE bucket_small;
> load data local inpath '/home/navis/apache/oss-hive/data/files/srcsortbucket2outof4.txt' INTO TABLE bucket_small;
> CREATE TABLE bucket_big (key int, value string) CLUSTERED BY (key) SORTED BY (key) INTO 4 BUCKETS STORED AS TEXTFILE;
> load data local inpath '/home/navis/apache/oss-hive/data/files/srcsortbucket1outof4.txt' INTO TABLE bucket_big;
> load data local inpath '/home/navis/apache/oss-hive/data/files/srcsortbucket2outof4.txt' INTO TABLE bucket_big;
> load data local inpath '/home/navis/apache/oss-hive/data/files/srcsortbucket3outof4.txt' INTO TABLE bucket_big;
> load data local inpath '/home/navis/apache/oss-hive/data/files/srcsortbucket4outof4.txt' INTO TABLE bucket_big;
> select count(*) FROM bucket_small a JOIN bucket_big b ON a.key + a.key = b.key;
> select /* + MAPJOIN(a) */ count(*) FROM bucket_small a JOIN bucket_big b ON a.key + a.key = b.key;
> returns 116 (same)
> But with BucketMapJoin or SMBJoin, it returns 61. But this should not be allowed cause hash(a.key) != hash(a.key + a.key).
> Bucket context should be utilized only with exact matching join expression with sort/cluster key.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (HIVE-3306) SMBJoin/BucketMapJoin should be
allowed only when join key expression is exactly matches with sort/cluster
key
Posted by "Namit Jain (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/HIVE-3306?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13449427#comment-13449427 ]
Namit Jain commented on HIVE-3306:
----------------------------------
+1
Running tests
> SMBJoin/BucketMapJoin should be allowed only when join key expression is exactly matches with sort/cluster key
> --------------------------------------------------------------------------------------------------------------
>
> Key: HIVE-3306
> URL: https://issues.apache.org/jira/browse/HIVE-3306
> Project: Hive
> Issue Type: Bug
> Components: Query Processor
> Affects Versions: 0.10.0
> Reporter: Navis
> Assignee: Navis
> Priority: Minor
> Attachments: HIVE-3306.1.patch.txt
>
>
> CREATE TABLE bucket_small (key int, value string) CLUSTERED BY (key) SORTED BY (key) INTO 2 BUCKETS STORED AS TEXTFILE;
> load data local inpath '/home/navis/apache/oss-hive/data/files/srcsortbucket1outof4.txt' INTO TABLE bucket_small;
> load data local inpath '/home/navis/apache/oss-hive/data/files/srcsortbucket2outof4.txt' INTO TABLE bucket_small;
> CREATE TABLE bucket_big (key int, value string) CLUSTERED BY (key) SORTED BY (key) INTO 4 BUCKETS STORED AS TEXTFILE;
> load data local inpath '/home/navis/apache/oss-hive/data/files/srcsortbucket1outof4.txt' INTO TABLE bucket_big;
> load data local inpath '/home/navis/apache/oss-hive/data/files/srcsortbucket2outof4.txt' INTO TABLE bucket_big;
> load data local inpath '/home/navis/apache/oss-hive/data/files/srcsortbucket3outof4.txt' INTO TABLE bucket_big;
> load data local inpath '/home/navis/apache/oss-hive/data/files/srcsortbucket4outof4.txt' INTO TABLE bucket_big;
> select count(*) FROM bucket_small a JOIN bucket_big b ON a.key + a.key = b.key;
> select /* + MAPJOIN(a) */ count(*) FROM bucket_small a JOIN bucket_big b ON a.key + a.key = b.key;
> returns 116 (same)
> But with BucketMapJoin or SMBJoin, it returns 61. But this should not be allowed cause hash(a.key) != hash(a.key + a.key).
> Bucket context should be utilized only with exact matching join expression with sort/cluster key.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Updated] (HIVE-3306) SMBJoin/BucketMapJoin should be
allowed only when join key expression is exactly matches with sort/cluster
key
Posted by "Namit Jain (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/HIVE-3306?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Namit Jain updated HIVE-3306:
-----------------------------
Status: Open (was: Patch Available)
comments
> SMBJoin/BucketMapJoin should be allowed only when join key expression is exactly matches with sort/cluster key
> --------------------------------------------------------------------------------------------------------------
>
> Key: HIVE-3306
> URL: https://issues.apache.org/jira/browse/HIVE-3306
> Project: Hive
> Issue Type: Bug
> Components: Query Processor
> Affects Versions: 0.10.0
> Reporter: Navis
> Assignee: Navis
> Priority: Minor
>
> CREATE TABLE bucket_small (key int, value string) CLUSTERED BY (key) SORTED BY (key) INTO 2 BUCKETS STORED AS TEXTFILE;
> load data local inpath '/home/navis/apache/oss-hive/data/files/srcsortbucket1outof4.txt' INTO TABLE bucket_small;
> load data local inpath '/home/navis/apache/oss-hive/data/files/srcsortbucket2outof4.txt' INTO TABLE bucket_small;
> CREATE TABLE bucket_big (key int, value string) CLUSTERED BY (key) SORTED BY (key) INTO 4 BUCKETS STORED AS TEXTFILE;
> load data local inpath '/home/navis/apache/oss-hive/data/files/srcsortbucket1outof4.txt' INTO TABLE bucket_big;
> load data local inpath '/home/navis/apache/oss-hive/data/files/srcsortbucket2outof4.txt' INTO TABLE bucket_big;
> load data local inpath '/home/navis/apache/oss-hive/data/files/srcsortbucket3outof4.txt' INTO TABLE bucket_big;
> load data local inpath '/home/navis/apache/oss-hive/data/files/srcsortbucket4outof4.txt' INTO TABLE bucket_big;
> select count(*) FROM bucket_small a JOIN bucket_big b ON a.key + a.key = b.key;
> select /* + MAPJOIN(a) */ count(*) FROM bucket_small a JOIN bucket_big b ON a.key + a.key = b.key;
> returns 116 (same)
> But with BucketMapJoin or SMBJoin, it returns 61. But this should not be allowed cause hash(a.key) != hash(a.key + a.key).
> Bucket context should be utilized only with exact matching join expression with sort/cluster key.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Updated] (HIVE-3306) SMBJoin/BucketMapJoin should be
allowed only when join key expression is exactly matches with sort/cluster
key
Posted by "Navis (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/HIVE-3306?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Navis updated HIVE-3306:
------------------------
Status: Patch Available (was: Open)
> SMBJoin/BucketMapJoin should be allowed only when join key expression is exactly matches with sort/cluster key
> --------------------------------------------------------------------------------------------------------------
>
> Key: HIVE-3306
> URL: https://issues.apache.org/jira/browse/HIVE-3306
> Project: Hive
> Issue Type: Bug
> Components: Query Processor
> Affects Versions: 0.10.0
> Reporter: Navis
> Assignee: Navis
> Priority: Minor
>
> CREATE TABLE bucket_small (key int, value string) CLUSTERED BY (key) SORTED BY (key) INTO 2 BUCKETS STORED AS TEXTFILE;
> load data local inpath '/home/navis/apache/oss-hive/data/files/srcsortbucket1outof4.txt' INTO TABLE bucket_small;
> load data local inpath '/home/navis/apache/oss-hive/data/files/srcsortbucket2outof4.txt' INTO TABLE bucket_small;
> CREATE TABLE bucket_big (key int, value string) CLUSTERED BY (key) SORTED BY (key) INTO 4 BUCKETS STORED AS TEXTFILE;
> load data local inpath '/home/navis/apache/oss-hive/data/files/srcsortbucket1outof4.txt' INTO TABLE bucket_big;
> load data local inpath '/home/navis/apache/oss-hive/data/files/srcsortbucket2outof4.txt' INTO TABLE bucket_big;
> load data local inpath '/home/navis/apache/oss-hive/data/files/srcsortbucket3outof4.txt' INTO TABLE bucket_big;
> load data local inpath '/home/navis/apache/oss-hive/data/files/srcsortbucket4outof4.txt' INTO TABLE bucket_big;
> select count(*) FROM bucket_small a JOIN bucket_big b ON a.key + a.key = b.key;
> select /* + MAPJOIN(a) */ count(*) FROM bucket_small a JOIN bucket_big b ON a.key + a.key = b.key;
> returns 116 (same)
> But with BucketMapJoin or SMBJoin, it returns 61. But this should not be allowed cause hash(a.key) != hash(a.key + a.key).
> Bucket context should be utilized only with exact matching join expression with sort/cluster key.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira